Plsql An example of a function

------ Chen Liwen Statistical report optimization one afternoon

1 Create or ReplaceType T_test asObject2 (3HDIDvarchar2( -),4Provincesvarchar2( -),5Number of accredited prosecutors ' officesvarchar2( -),6Inspection Institute 1varchar2( -),7Inspection Institute 2varchar2( -),8Inspection Institute 3varchar2( -),9Inspection Institute 4varchar2( -)Ten ) One / A Create or ReplaceType t_test_table as Table  oft_test; - / - CREATE OR REPLACE FUNCTIONFn_get_administrative_leafRETURNT_test_table as the  -   cursorShengji_c is -     SelectT1. Pindex, T1. B00, T1. B0101 -        fromB01 T1 +      whereT1. ZDYXB0132= 'M03' -         and(T1.flag is NULL orT1.flag= '0'); +  ATYPE Refcur_type isREFCURSOR; at book_refcur Refcur_type; -  -V_pindexvarchar2( -); -V_b00varchar2( -); -V_b0101varchar2( -); -  inv_zdyxb0131varchar2( -); -  toV_js01 Number:= 0; +V_js02 Number:= 0; -V_js03 Number:= 0; theV_js04 Number:= 0; *  $V_index Number:= 1;Panax Notoginseng  -V_test t_test_table:=t_test_table (); the begin +  A   OpenShengji_c; the Loop +     FetchShengji_c -        intoV_pindex, v_b00, v_b0101; $     Exit  whenShengji_c%NotFound; $    -V_JS01:= 0; -V_JS02:= 0; theV_JS03:= 0; -V_JS04:= 0;Wuyi    the     OpenBook_refcur for -       SELECTZDYXB0131 Wu          fromB01 T -        where(t.zdyxb0131 like '%170%' ort.zdyxb0131 like '%171%' or Aboutt.zdyxb0131 like '%172%' ort.zdyxb0131 like '%173%') $Start withB00=v_b00 -CONNECT byPRIOR B00=b0144b -               and(T.flag is NULL orT.flag= '0'); -    A Loop +       FetchBook_refcur the          intov_zdyxb0131; -       Exit  whenBook_refcur%NotFound; $       if(v_zdyxb0131 like '%170%') Then theV_JS01:=V_js01+ 1; the       End if; the       if(v_zdyxb0131 like '%171%') Then theV_JS02:=V_js02+ 1; -       End if; in       if(v_zdyxb0131 like '%172%') Then theV_JS03:=V_js03+ 1; the       End if; About       if(v_zdyxb0131 like '%173%') Then theV_JS04:=V_js04+ 1; the       End if; the     EndLoop; +    - v_test.extend (); theV_test (V_index):=t_test (v_b00,Bayi v_b0101, the                               'AAA', the V_js01, - V_js02, - V_js03, the v_js04); theV_index:=V_index+ 1; the    the   EndLoop; -   CloseShengji_c; the  the   RETURNv_test; the END;

Plsql An example of a function