我在Oracle中有一个带有varchar2参数的过程.根据该参数的值,我需要定义一个游标.光标将根据参数的值在不同的表上运行.
我想做类似下面的事情,但它在CURSOR定义代码段中抛出一个错误.有任何想法吗？
PROCEDURE GET_RECORDS(v_action IN VARCHAR2)
IS
CURSOR get_records
IS
IF(v_action = 'DO THIS') THEN
SELECT * from <THIS>;
ELSE
SELECT * from <THAT>;
END IF;
BEGIN
OPEN get_records;
FETCH get_records
INTO v_thing;
v_loop := 0;
WHILE get_records%FOUND
LOOP
FETCH get_records
INTO v_thing;
END LOOP;
CLOSE get_records;
END;
你需要一个REF CURSOR并打开它,例如：
sql> CREATE OR REPLACE PROCEDURE GET_RECORDS(v_action IN VARCHAR2) IS 2 v_thing VARCHAR2(10); 3 get_records SYS_REFCURSOR; 4 BEGIN 5 IF (v_action = 'DO THIS') THEN 6 OPEN get_records FOR 7 SELECT 1 FROM dual; 8 ELSE 9 OPEN get_records FOR 10 SELECT 2 FROM dual; 11 END IF; 12 13 LOOP 14 FETCH get_records INTO v_thing; 15 EXIT WHEN get_records%NOTFOUND; 16 /* do things */ 17 dbms_output.put_line(v_thing); 18 END LOOP; 19 CLOSE get_records; 20 END; 21 / Procedure created sql> exec get_records ('DO THIS'); 1 PL/sql procedure successfully completed sql> exec get_records ('DO THAT'); 2 PL/sql procedure successfully completed
