甲骨文：向驳回器添加新行

发布于 2025-01-17 23:36:54 字数 420 浏览 2 评论 0原文

我有一个程序，该过程将避难所作为OUT参数返回。

我需要将其包装在一个新的过程中，并添加新的行，这取决于一个反驳者字段。由于所有光标都是不可变的，因此我遇到了这个问题。也许创建临时表？

DECLARE
initial_cursor SYS_REFCURSOR;  
result_cursor SYS_REFCURSOR;  
BEGIN
initial_procedure(initial_cursor);
-- add a new row which depends on initial cursors row and wrap it into result cursor.

END;

让我们考虑到初始光标仅由布尔值组成，而新的行将为varchar“ true”或“ false”（如果光标值为0，则“ false”，否则“ true”）

原文

I have a procedure which returns refcursor as OUT parameter.

I need to wrap it inside a new procedure and add new row which depends on one of the refcursors field.
As all cursors are immutable I am stuck with this problem. Maybe create temporary table?

DECLARE
initial_cursor SYS_REFCURSOR;  
result_cursor SYS_REFCURSOR;  
BEGIN
initial_procedure(initial_cursor);
-- add a new row which depends on initial cursors row and wrap it into result cursor.

END;

Lets consider that initial cursor will consist only of boolean values and new row will be varchar 'TRUE' or 'FALSE' (if cursor value is 0 then 'FALSE', else 'TRUE')

分享到QQ

分享到微博

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

发布评论

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

深巷少女 2025-01-24 23:36:54

您可以创建一个 PIPELINED 函数来读取游标并输出带有额外行的行：

CREATE FUNCTION add_row(
  i_cur   IN SYS_REFCURSOR,
  i_value IN NUMBER
) RETURN SYS.ODCIVARCHAR2LIST PIPELINED
IS
  v_value NUMBER;
  v_flag  BOOLEAN := TRUE;
BEGIN
  LOOP
    FETCH i_cur INTO v_value;
    EXIT WHEN i_cur%NOTFOUND;
    PIPE ROW (v_value);
    IF v_value = 0 THEN
      v_flag := FALSE;
    END IF;
  END LOOP;
  IF v_flag THEN
    PIPE ROW ('TRUE');
  ELSE
    PIPE ROW ('FALSE');
  END IF;
END;
/

然后您可以使用它来创建第二个游标：

DECLARE
  initial_cursor SYS_REFCURSOR;  
  result_cursor  SYS_REFCURSOR;
  v_value VARCHAR2(20);
  
  PROCEDURE initial_procedure(
    cur OUT SYS_REFCURSOR
  )
  IS
  BEGIN
    OPEN cur FOR
      SELECT 1 AS value FROM DUAL;
  END;
BEGIN
  initial_procedure(initial_cursor);
  -- add a new row which depends on initial cursors row and wrap it into result cursor.

  OPEN result_cursor FOR
    SELECT COLUMN_VALUE AS value
    FROM   TABLE(add_row(initial_cursor, 2));
  
  LOOP
    FETCH result_cursor INTO v_value;
    EXIT WHEN result_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_value);
  END LOOP;
END;
/

输出：

1
真的

db<>fiddle 此处

You can create a PIPELINED function to read the cursor and output the rows with an extra row:

CREATE FUNCTION add_row(
  i_cur   IN SYS_REFCURSOR,
  i_value IN NUMBER
) RETURN SYS.ODCIVARCHAR2LIST PIPELINED
IS
  v_value NUMBER;
  v_flag  BOOLEAN := TRUE;
BEGIN
  LOOP
    FETCH i_cur INTO v_value;
    EXIT WHEN i_cur%NOTFOUND;
    PIPE ROW (v_value);
    IF v_value = 0 THEN
      v_flag := FALSE;
    END IF;
  END LOOP;
  IF v_flag THEN
    PIPE ROW ('TRUE');
  ELSE
    PIPE ROW ('FALSE');
  END IF;
END;
/

and then you can use that to make the second cursor:

DECLARE
  initial_cursor SYS_REFCURSOR;  
  result_cursor  SYS_REFCURSOR;
  v_value VARCHAR2(20);
  
  PROCEDURE initial_procedure(
    cur OUT SYS_REFCURSOR
  )
  IS
  BEGIN
    OPEN cur FOR
      SELECT 1 AS value FROM DUAL;
  END;
BEGIN
  initial_procedure(initial_cursor);
  -- add a new row which depends on initial cursors row and wrap it into result cursor.

  OPEN result_cursor FOR
    SELECT COLUMN_VALUE AS value
    FROM   TABLE(add_row(initial_cursor, 2));
  
  LOOP
    FETCH result_cursor INTO v_value;
    EXIT WHEN result_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_value);
  END LOOP;
END;
/

Which outputs: