Oracle PLSQL Demo - 18.01管道function[查詢零散的欄位組成list管道返回]

標籤：

--PACKAGECREATE OR REPLACE PACKAGE test_141213 is    TYPE type_ref IS record(        ENAME VARCHAR2(20),        WORK_CITY VARCHAR2(20),        SAL NUMBER(10));    TYPE t_type_ref IS TABLE OF type_ref;        FUNCTION retrieve(v_name varchar2) RETURN t_type_ref        PIPELINED;END test_141213;-- PACKAGE BODYCREATE OR REPLACE PACKAGE BODY test_141213 IS    FUNCTION retrieve(v_name varchar2) RETURN t_type_ref        PIPELINED IS        cur_type_ref type_ref;                Type ref_cur_variable IS REF cursor;        cur_variable ref_cur_variable;        --rec_emp type_ref%RowType;        v_sql varchar2(100) := ‘select e.ename, d.loc as work_city, e.sal from scott.emp e, scott.dept d where e.deptno = d.deptno‘;    BEGIN              Open cur_variable For v_sql;            Loop            fetch cur_variable                InTo cur_type_ref;            Exit When cur_variable%NotFound;                        dbms_output.put_line(cur_variable%rowcount || ‘ -> ‘ || cur_type_ref.ename || ‘   ‘ || cur_type_ref.work_city || ‘   ‘ || cur_type_ref.sal);            PIPE ROW(cur_type_ref);        End Loop;        Close cur_variable;            RETURN;    END;END test_141213;--Test retrieveselect * from table(test_141213.retrieve(‘Robin‘));

 

Oracle PLSQL Demo - 18.01管道function[查詢零散的欄位組成list管道返回]