Oracle學習筆記二十四：自定義函式之回傳結果集-有解無憂

一、基本說明

? Oracle 中的函式可以回傳表型別，但是這個表型別實際上是集合型別（與陣列類似），從 Oracle 9i 開始，提供了一個叫做"管道化表函式"來解決此問題，

? 管道化表函式，必須回傳一個集合型別，且標明 pipelined，它不能回傳具體變數，必須以一個空 return 回傳，通過 pipe row () 陳述句來送出要回傳的表中的每一行，呼叫這個函式的時候，通過 table() 關鍵字把管道流仿真為一個資料集，

二、常見形式

2.1、以游標形式回傳結果集

1）創建函式

create or replace function fn_GetEmpnoByCursor(p_ename in varchar2)
    return sys_refcursor 
is
    c_empno sys_refcursor;
begin
    open c_empno for
        select empno,ename from emp where ename=p_ename;
    return(c_empno);
end;

2）呼叫函式

select fn_GetEmpnoByCursor(p_ename => 'James') from dual;

2.2、以Table形式回傳結果集

1）創建函式

--定義一個行型別
create or replace type "emp_row_type" as object(empno varchar2(10),ename varchar2(20));
 
--定義一個表型別
create or replace type "emp_table_type" as table of "emp_row_type";
 
--創建函式
create or replace function fn_GetEmpnoByTable (p_ename in varchar2)
    return "emp_table_type" 
is
    emp_row "emp_row_type";                             --定義單行
    emp_table "emp_table_type" :="emp_table_type"();    --定義回傳結果，并初始化，
begin
    for currow in (select empno,ename from emp where ename=p_ename) 
    loop
        emp_row := "emp_row_type"(currow.empno,currow.ename);
        emp_table.extend;
        emp_table(emp_table.count) := emp_row;
    end loop;
    return(emp_table);
end;

2）呼叫函式

select * from table(fn_GetEmpnoByTable(p_ename => 'James'));

2.3、以管道形式回傳結果集

1）創建函式

--定義一個行型別
create or replace type "emp_row_type" as object(empno varchar2(10),ename varchar2(20));
 
--定義一個表型別
create or replace type "emp_table_type" as table of "emp_row_type";

--創建函式
create or replace function fn_GetEmpnoByPipe(p_ename in varchar2)
    return "emp_table_type" pipelined
is
    emp_row "emp_row_type"; --定義emp_row為行物件型別
begin
    for currow in (select empno,ename from emp where ename=p_ename)
    loop
        emp_row := "emp_row_type"(currow.empno,currow.ename);
        pipe row (emp_row);
    end loop;
    return;
end;

2）呼叫函式

select * from table(fn_GetEmpnoByPipe(p_ename => 'James'));

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/499134.html

標籤：Oracle

上一篇：Oracle學習筆記二十三：自定義函式

下一篇：Oracle學習筆記二十五：包的定義與呼叫