CREATE OR REPLACE PROCEDURE test(x IN VARCHAR2, z IN VARCHAR2) IS
TYPE mycur IS REF CURSOR;
cur mycur;
sqls VARCHAR2(4000) := 'SELECT b, MIN(a) a FROM (SELECT regexp_replace(t.record_id, ''[^0-9]'') b,t.record_id a FROM bl_record_sanction_ref t WHERE t.reference_value IN(' || x ||
')) GROUP BY b';
a VARCHAR2(30);
b VARCHAR2(30);
emp bl_record_address%ROWTYPE; --表名字如何参数化?
BEGIN
OPEN cur FOR sqls;
LOOP
FETCH cur
INTO b, a;
EXIT WHEN cur%NOTFOUND;
DECLARE
CURSOR crs2 IS
SELECT * FROM bl_record_address WHERE record_id = a;
emp bl_record_address%ROWTYPE;
BEGIN
FOR y IN crs2
LOOP
emp := y;
emp.id := seq_bl_record_address.nextval; --此处调用的序列参数化?
emp.record_id := z || '_' || b;
emp.list_name := z;
INSERT INTO bl_record_address VALUES emp;
END LOOP;
END;
END LOOP;
END test;
TYPE mycur IS REF CURSOR;
cur mycur;
sqls VARCHAR2(4000) := 'SELECT b, MIN(a) a FROM (SELECT regexp_replace(t.record_id, ''[^0-9]'') b,t.record_id a FROM bl_record_sanction_ref t WHERE t.reference_value IN(' || x ||
')) GROUP BY b';
a VARCHAR2(30);
b VARCHAR2(30);
emp bl_record_address%ROWTYPE; --表名字如何参数化?
BEGIN
OPEN cur FOR sqls;
LOOP
FETCH cur
INTO b, a;
EXIT WHEN cur%NOTFOUND;
DECLARE
CURSOR crs2 IS
SELECT * FROM bl_record_address WHERE record_id = a;
emp bl_record_address%ROWTYPE;
BEGIN
FOR y IN crs2
LOOP
emp := y;
emp.id := seq_bl_record_address.nextval; --此处调用的序列参数化?
emp.record_id := z || '_' || b;
emp.list_name := z;
INSERT INTO bl_record_address VALUES emp;
END LOOP;
END;
END LOOP;
END test;
解决方案 »
- Win7+Oracle8.1.7客户端+PL/SQL7.0 中文竖排问题如何解决?
- Oracle触发器创建问题!跪求高手指点啊!
- 读取远程SQLSERVER的2进制字段在本地生成文件后,插入到本地ORACLE的LONG RAW字段报"数据大小超出此类型的最大值"错
- 学期oracle从几开始啊?
- 我写了这么点就报错了,帮我看看吧。
- 数据库连接的疑问?
- 如何查询重复字段的行?
- 请问在Solaris8下安装Oracle9i后,如何将数据库的启动和关闭加入到系统的启动和关闭脚本中去
- 困惑:关于登录OTN的问题
- 为什么我的正版的8.16总是说没有监听器。我配置了没反映啊!!!
- select 查询速度快,而insert into 变慢的问题。
- 问一下这两个表的结果怎么用SQL关联在一起
v_emp1 emp1;
是不是应该这样的?