Oracle proceduce返回数据集小结
要从Oracle Procedure获得数据集合，通常采用Ref Cursor的方式，要获得此Cursor,有以下几种方式：
1.动态Sql返回：
这种情况下，Procedure的运算通常比较简单，比如根据参数组合sql或者多个Table的Join操作，但都可以通过一个sql语句完成查询。
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.
(
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)
)
创建Procedure,输出类型为Sys_refcursor
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
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;
