oracle动态游标的简单实现方法.docVIP

oracle动态游标的简单实现方法 [转] 2008年07月30日 星期三 08:46 --明细表打印予处理 通用报表：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)issql2 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 sql