Oracle PLSQL 从变量设置游标

发布于 2024-09-11 11:20:48 字数 326 浏览 3 评论 0原文

我对 Oracle 中的游标不熟悉。我有一段 SQL 包含在变量中。我想用这个sql打开一个游标。我该怎么做？看起来很简单，但我发现的所有示例都只是在“open cursor_name for”语句下方直接键入 SQL。

这是我想要运行的内容（假设我的 sql 查询有变量 v_sql）：

open my_cursor for v_sql;

但 Oracle 不喜欢这样。我也尝试过

open my_cursor for 
  execute immediate v_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.

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 doesnt like this though. I also tried

open my_cursor for 
  execute immediate v_sql;

Help please.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

天荒地未老 2024-09-18 11:20:48

您需要将其声明为引用游标，然后为 SQL 语句打开它。请看下面的例子。当然，这是假设您的 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/文档/cd/B14117_01/appdev.101/b10807/11_dynam.htm#i13057

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

Check this link...
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/11_dynam.htm#i13057

回复收藏 0 原文

友谊不毕业 2024-09-18 11:20:48

只要 v_sql 是 VARCHAR 并且 my_cursor 声明为 REF CURSOR，您的第一个片段就可以正常工作。然后您可以像使用静态游标一样从中进行 FETCH 操作。

但正如 OMG Ponies 所说，您必须小心 SQL 的来源。

回复收藏 0 原文

眼泪都笑了 2024-09-18 11:20:48

OMG Ponies 是完全正确的，

但这里只是做同样事情的不同方法

Var X Refcursor;
Begin
Open :X For
   Select 1 Num, 'b' Co
    From Dual
    Union
   Select 2 Num, 'c' Co
    From Dual;

end;

/
print x;

注意，当您在 Oracle 中执行任何操作（例如打开游标或其他操作）时，您将需要位于 BEGIN/END 内，并且不能简单地执行以下操作：

Var X Refcursor;
Open X For
   Select 1 Num, 'b' Co
    From Dual
    Union
   Select 2 Num, 'c' Co
    From Dual;

这不起作用！您必须将 OPEN 游标包含在 BEGIN/END 块中（无论是匿名块还是过程...），

Create or replace Procedure Ccc(X Out sys_Refcursor) 
As
begin
Open X For
   Select 1 Num, 'b' Co
    From Dual
    Union
   Select 2 Num, 'c' Co
    From Dual;
End Ccc;
/

Var X Refcursor;
Begin
Ccc(:X);
End;
/
print x;

请注意，匿名块中开始/结束中的 :x 是告诉 sql 引擎您正在使用创建的变量块外。在包/过程中这是不必要的。

OMG Ponies is completely correct,

but here is just a different way to do the same thing

Var X Refcursor;
Begin
Open :X For
   Select 1 Num, 'b' Co
    From Dual
    Union
   Select 2 Num, 'c' Co
    From Dual;

end;

/
print x;

Note when you do anything in Oracle like opening cursors or whatnot you will need to be within a BEGIN/END and you cannot simply do:

Var X Refcursor;
Open X For
   Select 1 Num, 'b' Co
    From Dual
    Union
   Select 2 Num, 'c' Co
    From Dual;

This will not Work! You must enclose the OPEN cursor within a BEGIN/END block (be it an anonomous block or a procedure...)

Create or replace Procedure Ccc(X Out sys_Refcursor) 
As
begin
Open X For
   Select 1 Num, 'b' Co
    From Dual
    Union
   Select 2 Num, 'c' Co
    From Dual;
End Ccc;
/

Var X Refcursor;
Begin
Ccc(:X);
End;
/
print x;

note the :x in the begin/end in the anonomous blocks is to tell the sql engine you are utilizing a variable created outside the block. within packages/procs it is unnecessary.

回复收藏 0 原文

~没有更多了~