Oracle 存储过程（带游标）如何工作？

发布于 2024-07-19 05:37:52 字数 473 浏览 5 评论 0原文

我有一个以下的 Oracle 存储过程

    CREATE OR REPLACE
PROCEDURE getRejectedReasons
  (
    p_cursor IN OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN p_cursor FOR SELECT * FROM reasons_for_rejection;
END;

但是，当我在 sql-developer 中运行这个存储过程时，我什么也看不到。我只是看到这样的内容：

Connecting to the database oracleLocal.
Process exited.
Disconnecting from the database oracleLocal.

我来自 MS sql 服务器，习惯于在运行这样的存储过程时看到实际结果。这个存储过程不返回结果是因为我使用了游标吗？

原文

I have a following oracle stored procedure

    CREATE OR REPLACE
PROCEDURE getRejectedReasons
  (
    p_cursor IN OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN p_cursor FOR SELECT * FROM reasons_for_rejection;
END;

However, when I run this stored procedure in sql-developer then I dont see anything. I just see something like this:

Connecting to the database oracleLocal.
Process exited.
Disconnecting from the database oracleLocal.

I'm coming from MS sql server and am used to seeing actual results when running a stored procedure like this. Is this stored procedure not returning results because I am using a cursor??

分享到QQ

分享到微博

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

发布评论

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

一页 2024-07-26 05:37:52

存储过程正在返回一些内容，只是您没有对结果执行任何操作。

您只需在 SQLDeveloper 中运行以下脚本即可完成此操作：


VARIABLE csr REFCURSOR;
EXEC getRejectedReasons(:csr); -- the colon identifies the parameter as a variable
PRINT csr;

另一种方法是获取每一行并进行某种处理：


DECLARE
  -- sys_refcursor is weakly typed
  refcsr  SYS_REFCURSOR;
  -- define a record so we can reference the fields
  rej_rec Reasons_for_Rejection%ROWTYPE;
BEGIN

  getRejectedReasons(refcsr);

   -- loop through the results  
   LOOP
      -- gets one row at a time
      FETCH refcsr INTO rej_rec;
      -- if the fetch doesn't find any more rows exit the loop
      EXIT WHEN refcsr%NOTFOUND;
      -- Do something here.  
      -- For example : DBMS_OUTPUT.PUT_LINE(rej_rec.reason_desc);
    END LOOP;

END;

The stored procedure is returning something it's just you aren't doing anything with the results.

You can do this simply by running the following script in SQLDeveloper:


VARIABLE csr REFCURSOR;
EXEC getRejectedReasons(:csr); -- the colon identifies the parameter as a variable
PRINT csr;

Another method is to fetch each row and do some sort of processing:


DECLARE
  -- sys_refcursor is weakly typed
  refcsr  SYS_REFCURSOR;
  -- define a record so we can reference the fields
  rej_rec Reasons_for_Rejection%ROWTYPE;
BEGIN

  getRejectedReasons(refcsr);

   -- loop through the results  
   LOOP
      -- gets one row at a time
      FETCH refcsr INTO rej_rec;
      -- if the fetch doesn't find any more rows exit the loop
      EXIT WHEN refcsr%NOTFOUND;
      -- Do something here.  
      -- For example : DBMS_OUTPUT.PUT_LINE(rej_rec.reason_desc);
    END LOOP;

END;

回复收藏 0 原文

浮世清欢 2024-07-26 05:37:52

您打开了光标。您没有从中选择任何内容、更新它或推进它。

所有打开操作都会有效地将匹配的行选择到临时内存中，因此您可以逐行前进光标。你没有这样做。

回复收藏 0 原文

箜明 2024-07-26 05:37:52

Oracle 和 SQL Server 的区别之一是后者自然地返回结果集。顺便说一下，我会使用一个函数。

在 Oracle 中，函数通常返回单个元素。光标是后来出现的。

有一些在线文档可以帮助您了解 refcursor 绑定变量的使用。下面是 SQL*Plus 的一个例子：

http: //download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch5.htm#sthref1122

我认为在 SQL Developer 中，您可以通过打开自动打印来执行相同的操作，尽管我还没有测试过那。

发现一个博客也讨论了类似的内容：

http://vadimtropashko.wordpress.com/cursors/

回复收藏 0 原文

德意的啸 2024-07-26 05:37:52

预计到达时间：好的。请忽略我写的。听别人说。显然这是错误的，因为我投了反对票。

tpdi说的是对的。声明游标后，您必须对其进行一些操作。

这是在嵌套循环中使用两个游标的示例

   PROCEDURE update_insert_tree (exid_in IN NUMBER, outvar_out OUT VARCHAR2)
   IS
      nxtid         NUMBER;
      phaseid       NUMBER;
      rowcounter1   NUMBER;
   BEGIN
      rowcounter1 := 0;
      outvar_out := 0;

      FOR acur IN (SELECT dept_exercise_id, phase
                     FROM ep_dept_exercise
                    WHERE exercise_id = exid_in)
      LOOP

         <<dept_loop>>
         FOR thecur IN (SELECT document_name, thelevel, sortnum, type_flag,
                               ex_save_id
                          FROM ep_exercise_save
                         WHERE exercise_id = exid_in)
         LOOP
            phaseid := acur.phase;

            IF phaseid = 0
            THEN
               phaseid := 10;

               UPDATE ep_dept_exercise
                  SET phase = 10
                WHERE dept_exercise_id = acur.dept_exercise_id;
            END IF;

            <<doc_loop>>

ETA: Ok. Ignore what I wrote. Listen to someone else. Apparently it's wrong, as I got down voted.

What tpdi said is correct. You have to do something with the cursor after you declare it.

Here's an example using two cursors in nested loops

   PROCEDURE update_insert_tree (exid_in IN NUMBER, outvar_out OUT VARCHAR2)
   IS
      nxtid         NUMBER;
      phaseid       NUMBER;
      rowcounter1   NUMBER;
   BEGIN
      rowcounter1 := 0;
      outvar_out := 0;

      FOR acur IN (SELECT dept_exercise_id, phase
                     FROM ep_dept_exercise
                    WHERE exercise_id = exid_in)
      LOOP

         <<dept_loop>>
         FOR thecur IN (SELECT document_name, thelevel, sortnum, type_flag,
                               ex_save_id
                          FROM ep_exercise_save
                         WHERE exercise_id = exid_in)
         LOOP
            phaseid := acur.phase;

            IF phaseid = 0
            THEN
               phaseid := 10;

               UPDATE ep_dept_exercise
                  SET phase = 10
                WHERE dept_exercise_id = acur.dept_exercise_id;
            END IF;

            <<doc_loop>>

回复收藏 0 原文

~没有更多了~