Oracle PLSQL从变量设置游标 [英] Oracle PLSQL setting a cursor from a variable

问题描述

我对Oracle中的游标不熟悉.我有一个包含在变量中的SQL.我想用此sql打开一个游标.我该怎么做呢?看起来很简单，但是我发现的所有示例都只是在"open cursor_name for"语句的下面直接键入了sql.

Im new to cursors in Oracle. I have a piece of SQL that is contained in a variable. I want to open a cursor with this sql. How do I do this? Seems simple but all the examples I find just have the sql typed directly below the "open cursor_name for" statement.

这是我要运行的内容(假设我的sql查询中有变量v_sql):

Here is what I would like to run (assume I have variable v_sql with my sql query):

open my_cursor for v_sql;

Oracle却不喜欢这样.我也尝试过

Oracle doesnt like this though. I also tried

open my_cursor for 
  execute immediate v_sql;

请帮助.

推荐答案

您需要将其声明为ref游标，然后为您的SQL语句打开它.请看下面的例子.当然，这是假设您没有对sql的任何输入绑定.

You need to declare it as a ref cursor and then open it for the your SQL statement. Please look at the example below. This, of course, is assuming you do not have any input bindings to your sql.

sql> ed
Wrote file afiedt.buf

  1  declare
  2     c1 sys_refcursor;
  3     v_empno number;
  4     v_ename varchar2(30);
  5  begin
  6    open c1 for 'select empno, ename from emp';
  7    loop
  8      fetch c1 into v_empno, v_ename;
  9      dbms_output.put_line(v_empno || '--' || v_ename);
 10      exit when c1%notfound;
 11    end loop;
 12    close c1;
 13* end;
sql> /
7369--SMITH
7499--ALLEN
7521--WARD
7566--JONES
7654--MARTIN
7698--BLAKE
7782--CLARK
7788--SCOTT
7839--KING
7844--TURNER
7876--ADAMS
7900--JAMES
7902--FORD
7934--MILLER
7934--MILLER

检查此链接... http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/11_dynam.htm#i13057

这篇关于Oracle PLSQL从变量设置游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！