oracle存储过程系统游标,oracle之存储过程,临时表,游标示例

参考资料

1 ORACLE 存储过程返回临时表结果集

http://hi.baidu.com/h_sn999/blog/item/4211810f4d7542fdaa645738.html

2 ORACLE 在存储过程中使用临时表

http://blog.csdn.net/wekily/article/details/6120900

3 Oracle存储过程中创建临时表

http://blog.sina.com.cn/s/blog_4c7ae2a80100bki3.html

4 在ORACLE存储过程中创建临时表

http://huqiji.iteye.com/blog/782067

总结如下：

DDL是一种消耗资源非常大的操作，运行时尽量不要使用DDL语句，应用程序需要的临时表应在运行之前就开始创建。不必在每个存储过程中创建一次。临时表总是存在的，他们作为对象存在于数据字典中，并且总是保持为空，直到有会话在其中放入数据

1 创建临时表

create global temporary table 表名

(

ID VARCHAR2(100 CHAR),

NAME VARCHAR2(100 CHAR)

)

on commit preserve rows;

2 创建存储过程

create or replace procedure proc_XXX(

mycur out SYS_REFCURSOR

as

TYPE My_CurType IS REF CURSOR;

CUR_1 My_CurType;

tempa varchar2;

tempb varchar2;

--此处可声明更多变更^_^

begin

OPEN CUR_1 FOR select * from 表名;

--使用前先清空

execute immediate 'truncate table 临时表表名';

LOOP

FETCH CUR_1 INTO tempa;

EXIT WHEN CUR_1%NOTFOUND;

--进行相关的业务查询,将结果返回于更多变量上,插入临时表数据

tempa:='1';

tempb:='jack';

insert into 临时表表名(ID,NAME)values(tempa,tempb);

commit;

end loop;

open mycur for select * from 临时表表名;

CLOSE CUR_1;

message :='查询临时表成功';

EXCEPTION

WHEN OTHERS THEN

message :='查询临时表失败';

end proc_XXX;

参考更多

1 创建临时表,插入数据,返回结果集

CREATE OR REPLACE PROCEDURE Report_Month_Responsibility(

o_cur OUT SYS_REFCURSOR

)

IS

STR VARCHAR2(200);

tb_count INT;

BEGIN

--先判断全局临时表是否存在，没存在则重新建立：

select count(*) into tb_count from dba_tables where table_name='REPROTTEST';

if tb_count=0 then

STR:=' CREATE GLOBAL TEMPORARY TABLE REPROTTEST(

ID INT,

ANAME VARCHAR2(20)

) ON COMMIT PRESERVE ROWS';

execute immediate STR;

end if;

STR:='INSERT INTO REPROTTEST(ID,ANAME) VALUES(1,''1'')';

execute immediate STR;

COMMIT;

STR:='SELECT * FROM REPROTTEST';

OPEN o_cur FOR STR; -- 给游标变量赋值

END Report_Month_Responsibility;

2 调用存储过程

CREATE OR REPLACE PROCEDURE proc_X()

IS

v_ID INT;

v_ANAME VARCHAR2(20);

--定义游标:

v_account_cur SYS_REFCURSOR;

BEGIN

--调用存储过程：

Report_Month_Responsibility(v_account_cur);

fetch v_account_cur into v_ID,v_ANAME;

--用循环显示游标中的记录：

while v_account_cur%found loop

dbms_output.put_line('The value of column ID is: '||v_ID);--打引列ID

dbms_output.put_line('The value of column ANAME is: '||v_ANAME);

--打引列ANAME

fetch v_account_cur into v_ID,v_ANAME;

end loop;

close v_account_cur;

execute immediate 'truncate TABLE REPROTTEST';

end proc_X;