Oracle procedure返回資料集小結

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

1.動態Sql返回：

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

1. CREATE OR REPLACE PROCEDURE  sp_getcurrentstockpallet (   
2.    startdate           DATE,   
3.    enddate             DATE,   
4.    status              CHAR,   
5.    material_no         VARCHAR2,   
6.    pallet_id           VARCHAR2,   
7.    box_id              VARCHAR2,   
8.    plant               VARCHAR2,   
9.    stloc               VARCHAR2,   
10.    customer            VARCHAR2,   
11.    creator             VARCHAR2,   
12.    mat_doc             VARCHAR2,   
13.    box_count           NUMBER,   
14.    RESULT        OUT   sys_refcursor   
15. )   
16. IS   
17.    v_sql   VARCHAR2 (1000);   
18. BEGIN   
19.    v_sql :=   
20.       '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,   
21.   b.grade,a.plant,a.stloc,a.area,a.bin,b.customer,b.product_type,b.CREATE_TIME,b.remark   
22.   from sd_current_pallet a,sd_current_box b   
23.   where a.pallet_id=b.pallet_id ';   
24.   
25.    IF material_no IS NOT NULL   
26.    THEN   
27.       v_sql := v_sql || ' and b.material_no =' || '''' || material_no || '''';   
28.          
29.    END IF;   
30.   
31.    v_sql :=   
32.          v_sql   
33.       || 'group by b.status,b.pallet_id,b.wm_pallet_id,b.unit,b.material_no,   
34.   b.grade,a.plant,a.stloc,a.area,a.bin,b.customer,b.product_type,b.CREATE_TIME,b.remark';   
35.   
36.    OPEN RESULT FOR v_sql ;   
37. EXCEPTION   
38.    WHEN NO_DATA_FOUND   
39.    THEN   
40.       NULL;   
41.    WHEN OTHERS   
42.    THEN   
43.       RAISE;   
44. END sp_getcurrentstockpallet;  

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

1. CREATE OR REPLACE TYPE stockPallet_type as object  
2. (   
3.   status              CHAR(1),   
4.    material_no         VARCHAR2(20),   
5.    pallet_id           VARCHAR2(30),   
6.    box_id              VARCHAR2(30),   
7.    plant               VARCHAR2(4),   
8.    stloc               VARCHAR2(4),   
9.    customer            VARCHAR2(10),   
10.    creator             VARCHAR2(10),   
11.    mat_doc             VARCHAR2(20),   
12.    box_count           NUMBER(10)   
13. )  

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

1. CREATE OR REPLACE TYPE .t_stockpallet_nest as TABLE OF stockpallet_type;  

1 2 3 下一頁