Oracle proceduce返回資料集小結

要從Oracle Procedure獲得資料集合，通常採用Ref Cursor的方式，要獲得此Cursor,有以下幾種方式：

1.動態Sql返回：

這種情況下，Procedure的運算通常比較簡單，比如根據參數組合sql或者多個Table的Join操作，但都可以通過一個sql陳述式完成查詢。

CREATE OR REPLACE PROCEDURE  sp_getcurrentstockpallet (
   startdate           DATE,
   enddate             DATE,
   status              CHAR,
   material_no         VARCHAR2,
   pallet_id           VARCHAR2,
   box_id              VARCHAR2,
   plant               VARCHAR2,
   stloc               VARCHAR2,
   customer            VARCHAR2,
   creator             VARCHAR2,
   mat_doc             VARCHAR2,
   box_count           NUMBER,
   RESULT        OUT   sys_refcursor
)
IS
   v_sql   VARCHAR2 (1000);
BEGIN
   v_sql :=
      'select b.status,b.pallet_id,b.wm_pallet_id,count(b.box_id) box_count,sum(b.glass_qty) total_qty,b.unit,b.material_no,
  b.grade,a.plant,a.stloc,a.area,a.bin,b.customer,b.product_type,b.CREATE_TIME,b.remark
  from sd_current_pallet a,sd_current_box b
  where a.pallet_id=b.pallet_id ';
   IF material_no IS NOT NULL
   THEN
      v_sql := v_sql || ' and b.material_no =' || '''' || material_no || '''';
   END IF;
   v_sql :=
         v_sql
      || 'group by b.status,b.pallet_id,b.wm_pallet_id,b.unit,b.material_no,
  b.grade,a.plant,a.stloc,a.area,a.bin,b.customer,b.product_type,b.CREATE_TIME,b.remark';
   OPEN RESULT FOR v_sql ;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      RAISE;
END sp_getcurrentstockpallet;

2.如果Procedure邏輯運算比較複雜，沒辦法在一個Sql中完成。通常運算過程中需要臨時儲存中間運算資料等等。
這種情況下，可以採取的方式：
1）使用巢狀表格動態產生資料集，並運用Table()函數返回資料集。此種方式需要在DB中建立Object,並要建立巢狀表格，本地範圍中定義的Type不能被識別。

建立Object.

CREATE OR REPLACE TYPE stockPallet_type as object
(
  status              CHAR(1),
   material_no         VARCHAR2(20),
   pallet_id           VARCHAR2(30),
   box_id              VARCHAR2(30),
   plant               VARCHAR2(4),
   stloc               VARCHAR2(4),
   customer            VARCHAR2(10),
   creator             VARCHAR2(10),
   mat_doc             VARCHAR2(20),
   box_count           NUMBER(10)
)

建立巢狀表格，類型為上面建立的Object stockPallet_type

CREATE OR REPLACE TYPE t_stockpallet_nest as TABLE OF stockpallet_type;

建立Procedure,輸出類型為Sys_refcursor

CREATE OR REPLACE PROCEDURE sp_getpalletbynesttable_v2 (
   RESULT   OUT   sys_refcursor
)
AS
   CURSOR c_box
   IS
      SELECT material_no, pallet_id, box_id
        FROM sd_current_box
       WHERE ROWNUM < 10;
--初始化巢狀表格
   box_array      t_stockpallet_nest := t_stockpallet_nest ();
   i              NUMBER             := 0;
   --v_sql_return   VARCHAR2 (200);
BEGIN
   FOR curbox IN c_box
   LOOP
   i := i + 1;
   box_array.EXTEND; --運用extend方法聲明在集合最後面添加一個元素
   box_array (i) :=
      stockpallet_type ('0',
                        curbox.material_no,
                        curbox.pallet_id,
                        curbox.box_id,
                        't001',
                        '0210',
                        'hp',
                        'ivav',
                        '5000',
                        10
                       ); --執行個體化一個Object,並賦予巢狀表格中
   END LOOP;
   --v_sql_return := 'select *  from table(box_array)'; -- 注意，這樣不行
   OPEN RESULT FOR select *  from table(box_array); --Table()函數返回
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;

2）另外一種常用的方式就是採用暫存資料表。

可以採用動態建立的方式（註：查了一些資料說，盡量避免在procedure中動態建立和刪除暫存資料表，但是有一種情況是，暫存資料表的欄位元量是不定的，需要根據邏輯動態產生。這種情況可能用上面的集合比較靠譜？），也可以在建立procedure前就建立好暫存資料表備用（注意跟建立普通表的區別？暫存資料表中資料給當前session<會話級暫存資料表>/transaction<事務級暫存資料表>所有）。注意DDL語句需要用Execute  Immediate語句執行
如果出現“許可權不足”的錯誤，可以考慮用下面的兩種方式解決：
1.grant create any table to userName
2.在Oracle的預存程序中，如果涉及到操作不同schema下的對象的時候，可以在不同的schema下寫相同的procedure，但這樣帶來的問題是維護和同步帶來了麻煩，可以在procedure中加上authid current_user，來說明procedure中操作的對象是當前串連使用者的對象而並不是procedure所屬使用者下的對象。

1. Create the structure of the global temporary table once, outside of pl/sql.
2. Utilize that table in your procedures. The contents of the table will be local to your session and will automatically disappear when you either log out or commit, depending on how the table is configured.

Multiple sessions can use the same GTT at the same time, but they will not be able to see or interact with each others data. They will also not block each other for any action against that table

CREATE OR REPLACE PROCEDURE sp_getpalletbytemptable_v1 (
   RESULT   OUT   sys_refcursor
)
AUTHID CURRENT_USER 
IS
   tb_count   INT;
   v_sql      VARCHAR2 (300);
BEGIN
   v_sql := 'drop table box_tmp_table';
   EXECUTE IMMEDIATE v_sql;
   v_sql :=
      'create global temporary table box_tmp_table
       (
        box_id varchar2(30),
        pallet_id varchar2(30),
        material_no varchar2(30)
       ) on commit preserve rows';
   EXECUTE IMMEDIATE v_sql;
   v_sql :=
      'insert into box_tmp_table 
   (select box_id,pallet_id,material_no from sd_current_box where rownum<10)';
   EXECUTE IMMEDIATE v_sql;
   COMMIT;
   v_sql := 'select * from  box_tmp_table';
   OPEN RESULT FOR v_sql;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END sp_getpalletbytemptable_v1;