过程示例

create or replace procedure PROC_TASK_QUERY
is
       g_name          task_company_stock_list.g_name%type;
       code_t          task_company_stock_list.code_t%type;
       firm_code       task_company_stock_list.firm_code%type;
       firm_name       task_company_stock_list.firm_name%type;
       unit_name       task_company_stock_list.unit_name%type;
       in_qty          task_company_stock_list.in_qty%type;
       out_qty         task_company_stock_list.out_qty%type;

       id_d              task_company_stock_detail.id%type;
       eml_id_d          task_company_stock_detail.eml_id%type;
       busi_type_d       task_company_stock_detail.busi_type%type;
       refer_doc_d       task_company_stock_detail.refer_doc%type;
       pass_gate_time_d  task_company_stock_detail.pass_gate_time%type;
       trade_name_d      task_company_stock_detail.trade_name%type;
       name_d            task_company_stock_detail.name%type;
       move_name_d       task_company_stock_detail.move_name%type;
       g_qty_d           task_company_stock_detail.g_qty%type;
       g_name_d          task_company_stock_detail.g_name%type;
       unit_name_d       task_company_stock_detail.unit_name%type;
       code_t_d          task_company_stock_detail.code_t%type;
       g_no_d            task_company_stock_detail.g_no%type;

       sqlstr     long;
       moduleno  varchar2(100);
       userid    varchar2(100);
       queryp    varchar2(10);

       insqlmain  long;
       insqldetail long;
       insqldetail2 long;
       insqldetail3 long;

       startposition number(10);
       len number(10);
       type sql_array is table of task_schedule_query.sqlstr%type index by binary_integer;
       sqlarray sql_array;
       lpindex number(10);
       delimiter varchar2(10);

       type cur is ref cursor ;
       sqlcur cur;
       incur cur;
       indetailcur cur;
begin

   insqlmain := '';
   insqldetail :='';
   insqldetail2 :='';
   insqldetail3 :='';
     
   sqlarray(1):='';
   sqlarray(2):='';
   sqlarray(3):='';
   sqlarray(4):='';
   
   open sqlcur for select user_id,moduleno,sqlstr,query_param from task_schedule_query where status=1;
   loop
     fetch sqlcur into userid,moduleno,sqlstr,queryp;
     exit when sqlcur%notfound;

     --构造查询字符串数组开始
     delimiter:='~';
     startposition:=1;
     lpindex:= 1;
      loop
        select instr(sqlstr,delimiter,startposition ) into len from dual;
        --dbms_output.put_line(startposition);
        --dbms_output.put_line(len);
        if len!=0 then
           select substr(sqlstr,startposition,len-startposition) into sqlarray(lpindex) from dual;
        else
          select substr(sqlstr,startposition) into sqlarray(lpindex) from dual;
          --dbms_output.put_line(sqlarray(lpindex));
          exit;
          end if;
          --dbms_output.put_line(sqlarray(lpindex));
          startposition:=len+1;
         lpindex:=lpindex+1;
      end loop;
     /*
     for i in 1..sqlarray.count
     loop
       dbms_output.put_line(sqlarray(i));
     end loop;
     */

     --构造查询字符串数组结束
     
     insqlmain := sqlarray(1);
     insqldetail:=sqlarray(2);
     insqldetail2 :=sqlarray(3);
     insqldetail3 :=sqlarray(4);

     --分模块执行：企业进出仓库存查询开始
     IF(moduleno='compStockSearch') THEN
        BEGIN       
          update task_schedule_query set status=2,start_time=sysdate where user_id=userid and moduleno=moduleno;                              
          IF(queryp='1' OR queryp='2') THEN                                     
             open incur for insqlmain;
             loop
               fetch incur into g_name,code_t,firm_code,firm_name,unit_name,in_qty,out_qty;
               exit when incur%notfound;
               insert into task_company_stock_list(g_name,code_t,firm_code,firm_name,unit_name,in_qty,out_qty,user_id)
               values(g_name,code_t,firm_code,firm_name,unit_name,in_qty,out_qty,userid);
             end lo