有条件地定义一个游标 [英] Conditionally define a Cursor

问题描述

我在Oracle中有一个使用varchar2参数的过程.基于该参数的值，我需要定义一个游标.游标将根据参数的值在不同的表上进行操作.

I have a Procedure in Oracle that takes a varchar2 paramater. Based on the value of that parameter, I need to define a cursor. The cursor will operate on different tables based on the value of the parameter.

我想做以下类似的事情，但是它在CURSOR定义代码段中引发了错误.有什么想法吗?

I wanted to do something like below but it throws an error in the CURSOR definition piece of code. Any ideas?

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并有条件地将其打开，例如:

you will need a REF CURSOR and open it conditionaly, for example:

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

这篇关于有条件地定义一个游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！