oracle動態資料指標的簡單實現方法

procedure mx_print_common(pd_id in mx_pd_syn.pd_id%type,
                   p_pd_mxb_id IN mx_pd_mxb_syn.p_mxb_id%type,
                   p_dept_no IN sc_mxk.dept_code%type,
                   p1 sc_bz_syn.bz_code%type,
                   p2 sc_cjjc_syn.cjjc_code%type,
                   p3 sc_mxk.warehouse_num%type)
is
  sql2 varchar2(500);             --儲存查詢語句
  sql3 varchar2(500);             --儲存查詢條件
  str1 sc_print_syn.a%type;   --儲存車間進程
  str2 sc_print_syn.b%type;   --儲存班組（工藝、工序）進程
  s_ip sc_print_syn.ip%type;
  type cursor_type is ref cursor;
  c1 cursor_type;
  type record_type is record(
        pbom_id sc_mxk.pbom_id%type
  );
  r_c1 record_type;
 /*

注意上面紅色的兩行和藍色的兩行

紅色的兩行定義一個遊標

藍色的兩行定義一個遊標中將要返回的資料的資料結構

*/
      
    cursor c2(p_pbom_id sc_mxk.pbom_id%type) is
        select a.dd_count,b.gx_name,c.bz_name,d.cjjc_name
         from sc_p_gx_syn a,sc_gx_syn b,sc_bz_syn c,sc_cjjc_syn d
          where pbom_id = p_pbom_id
          and a.gx_code=b.gx_code(+) and b.dept_code=p_dept_no
          and a.bz_code=c.bz_code(+)  and b.dept_code=p_dept_no
          and a.cjjc_code=d.cjjc_code(+)  and b.dept_code=p_dept_no;
   
    r_c2 c2%rowtype;
BEGIN
      s_ip :=sys_context('USERENV','IP_ADDRESS');
      delete from sc_print_syn where ip=s_ip and p_id=pd_id;
      commit;
     --下面開始構造查詢語句
      sql2:='select distinct a.pbom_id from sc_mxk a';
      sql3:=' where a.p_id=' || pd_id || ' and a.dept_code= ''' || p_dept_no || '''';
  
      if  p_pd_mxb_id >0 then
         sql2:=sql3 || ',mxk c ';
         sql3:=sql3 || ' and c.m_mxb_id= ' || p_pd_mxb_id || ' and a.mxb_id = c.mxb_id';
      end if;
     
      if p1 is not null then
         sql2:=sql2 || ',sc_p_gx_syn b';
         sql3:=sql3 || ' and a.pbom_id=b.pbom_id  and b.bz_code = ''' || p1 || '''';
      end if;
      if p2 is not null then
         sql2:=sql2 || ',sc_p_gx_syn b';
         sql3:=sql3 || ' and a.pbom_id=b.pbom_id  and b.cjjc_code = '''  || p2 || '''';
      end if;
      if p3 is not null then
         sql3:=sql3 || ' and a.warehouse_num = ''' || p3 || '''';
      end if;
      sql2:=sql2 || sql3;

--開啟動態資料指標，再往下就都一樣了
      open c1 for sql2;
        loop
            fetch c1 into r_c1;
            exit when c1%notfound;
            str1:='';
            str2:='';
            --開啟工序表進行處理
            open c2(r_c1.pbom_id);
            loop              
                fetch c2 into r_c2;
                exit when c2%notfound; --沒有記錄退出
                if r_c2.cjjc_name is not null then
                   str1 :=str1 || to_char(r_c2.cjjc_name);
                end if;
                if r_c2.bz_name is not null then
                   str2 := str2  || r_c2.bz_name  ||  to_char(r_c2.dd_count);
                elsif r_c2.gx_name is not null then
                   str2 := str2  || to_char(r_c2.gx_name)  ||  to_char(r_c2.dd_count);
                end if;
       
                
            end loop;
            close c2;
            insert into sc_print_syn(a,b,ip,p_id,r_id)
               values(str1,str2,s_ip,pd_id,r_c1.pbom_id);
                  COMMIT;
        end loop;
        close c1;
END mx_print_common;

當然，實現的方法一定很多，甚至可以用隱式遊標。但是隱式遊標中用動態查詢語句也要費一些周折的。