我在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
