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;

posted on 2012-06-05 19:09  疯狂的石头陈  阅读(827)  评论(0编辑  收藏  举报

导航