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;
当然，实现的方法一定很多，甚至可以用隐式游标。但是隐式游标中用动态查询语句也要费一些周折的。 评论:
#re: oracle动态游标的简单实现方法 2008-05-27 09:18 | xzc
----定义
type cursor_type is ref cursor; c1 cursor_type; ----使用
--打开动态游标，再往下就都一样了 open c1 for sql2; loop
fetch c1 into r_c1;
exit when c1%notfound; 回复更多评论
#re: oracle动态游标的简单实现方法 [未登录] 2008-05-27 11:43 | xzc
TYPE cursor_type IS REF CURSOR; c1 cursor_type; --
OPEN c1 FOR lc_sql; LOOP FETCH c1
INTO lc_source_column_pk_value, lc_source_column_npk_value; EXIT WHEN c1%NOTFOUND; null; END LOOP; <
CLOSE c1; 回复更多评论
#re: oracle动态游标的简单实现方法 [未登录] 2008-05-30 19:32 | xzc
DECLARE
v_cursor NUMBER; v_stat NUMBER; v_row NUMBER;
v_id NUMBER; v_no VARCHAR(100); v_date DATE; v_sql VARCHAR(200); s_id NUMBER; s_date DATE; BEGIN
s_id := 3000; s_date := SYSDATE;
v_sql := 'SELECT id,qan_no,sample_date FROM \WHERE id > :sid and sample_date < :sdate';
v_cursor := dbms_sql.open_cursor; --打开游标；
dbms_sql.parse(v_cursor, v_sql, dbms_sql.native); --解析动态SQL语句； dbms_sql.bind_variable(v_cursor, ':sid', s_id); --绑定输入参数； dbms_sql.bind_variable(v_cursor, ':sdate', s_date);
dbms_sql.define_column(v_cursor, 1, v_id); --定义列 dbms_sql.define_column(v_cursor, 2, v_no, 100); dbms_sql.define_column(v_cursor, 3, v_date);
v_stat := dbms_sql.execute(v_cursor); --执行动态SQL语句。 LOOP
EXIT WHEN dbms_sql.fetch_rows(v_cursor)<=0; --fetch_rows在结果集中移动游标，如果未抵达末尾，返回1。
dbms_sql.column_value(v_cursor, 1, v_id); --将当前行的查询结果写入上面定义的列中。