Oracle procedure返回数据集小结

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;