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;
v_emp1 emp1;
是不是应该这样的?