具有游标参数oracle的流水线函数 [英] pipelined function with cursor parameter oracle

问题描述

例如，如果我具有这种功能

for example if i have this kind of function

function test_pipe(p_source in t_cursor)
return t_tab
pipelined
as --some code goes here

t_cursor是一个引用游标. 我知道我可以像

t_cursor is a ref cursor. i know i can call this function like

select * from table(test_pipe(cursor(select 1 from dual)));

但是如果我在包中声明游标并想将其作为参数传递该怎么办. 像这样的东西.

but what if i declare cursor in a package and want to pass it as an argument. something like this.

procedure test is
v_ct pls_integer;
cursor main_cur is select 1 from dual;
begin
select count(*) into v_ct from table(test_pipe(main_cur));
--some code
end;

并且我得到main_cur无效标识符-pl/sql:ORA00904错误. 我应该如何编码才能将main_cur作为参数传递给test_pipe?

And i get main_cur invalid identifier-- pl/sql:ORA00904 error. How should i code to be able to pass the main_cur as an argument to test_pipe?

推荐答案

光标main_cur从双精度中选择1；

cursor main_cur is select 1 from dual;

游标是用于从结果集中获取行的指针.

A cursor is a pointer used to fetch rows from a result set.

因此，当您执行table(test_pipe(main_cur))时，没有将行源传递给流水线函数.您需要先获取行，然后再传递行源.

So, when you do table(test_pipe(main_cur)), you are not passing a rowsource to the pipelined function. you need to first fetch the rows and then pass the rowsource.

测试用例:

SQL> CREATE or replace TYPE target_table_row
  2  AS
  3    OBJECT
  4    ( EMPNO NUMBER(4) ,
  5      ENAME VARCHAR2(10)
  6      )
  7  /

Type created.

SQL>
SQL> sho err
No errors.
SQL>
SQL> CREATE or replace TYPE target_table_rows
  2  AS
  3    TABLE OF target_table_row;
  4  /

Type created.

SQL>
SQL> sho err
No errors.
SQL>

管道功能

SQL> CREATE OR REPLACE FUNCTION pipelined_fx(
  2      p_cursor IN SYS_REFCURSOR)
  3    RETURN target_table_rows PIPELINED PARALLEL_ENABLE(
  4      PARTITION p_cursor BY ANY)
  5  IS
  6  TYPE cursor_ntt
  7  IS
  8    TABLE OF emp%ROWTYPE;
  9    nt_src_data cursor_ntt;
 10  BEGIN
 11    LOOP
 12      FETCH p_cursor BULK COLLECT INTO nt_src_data LIMIT 100;
 13      FOR i IN 1 .. nt_src_data.COUNT
 14      LOOP
 15        PIPE ROW (target_table_row( nt_src_data(i).empno, nt_src_data(i).ename ));
 16      END LOOP;
 17      EXIT
 18    WHEN p_cursor%NOTFOUND;
 19    END LOOP;
 20    CLOSE p_cursor;
 21    RETURN;
 22  END pipelined_fx;
 23  /

Function created.

SQL>
SQL> show errors
No errors.
SQL>

现在，让我们测试流水线功能:

SQL> DECLARE
  2    rc SYS_REFCURSOR;
  3    num NUMBER;
  4  BEGIN
  5    OPEN RC FOR SELECT * FROM emp;
  6    SELECT count(*) INTO num FROM TABLE(pipelined_fx(rc));
  7    DBMS_OUTPUT.PUT_LINE( num || ' rows in total.' );
  8  END;
  9  /
14 rows in total.

PL/SQL procedure successfully completed.

SQL>

这篇关于具有游标参数oracle的流水线函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！