oracle 函数带游标,带有游标参数oracle的流水线函数

cursor main_cur is select 1 from dual;

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

因此,当您执行表(test_pipe(main_cur))时,您没有将行源传递给流水线函数.您需要先获取行然后传递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>