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 创建临时表
Sql代码
- create global temporary table 表名
- (
- ID VARCHAR2(100 CHAR),
- NAME VARCHAR2(100 CHAR)
- )
- on commit preserve rows;
2 创建存储过程
Sql代码
- 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 创建临时表,插入数据,返回结果集
Sql代码
- 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 调用存储过程
Sql代码
- 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;
https://liuzidong.iteye.com/blog/1177894
oracle之存储过程,临时表,游标示例
2011-09-22 14:49:10
收藏 我要投稿
总结如下:
DDL是一种消耗资源非常大的操作,运行时尽量不要使用DDL语句,应用程序需要的临时表应在运行之前就开始创建。不必在每个存储过程中创建一次。临时表总是存在的,他们作为对象存在于数据字典中,并且总是保持为空,直到有会话在其中放入数据
1 创建临时表
Sql代码
create global temporary table 表名
(
ID VARCHAR2(100 CHAR),
NAME VARCHAR2(100 CHAR)
)
on commit preserve rows;
2 创建存储过程
Sql代码
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 创建临时表,插入数据,返回结果集
Sql代码
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 调用存储过程
Sql代码
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;
作者“咫尺天涯”
https://www.2cto.com/database/201109/105306.html