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

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

• 博客分类： 
• oracle

oracleproc临时表游标 

参考资料 
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代码 

 

1. create global temporary table 表名  
2. (  
3.   ID               VARCHAR2(100 CHAR),  
4.   NAME         VARCHAR2(100 CHAR)   
5. )  
6. on commit preserve rows;  

2 创建存储过程 
Sql代码 

 

1. create or replace procedure proc_XXX(  
2. mycur out SYS_REFCURSOR  
3. as  
4. TYPE My_CurType IS REF CURSOR;  
5. CUR_1 My_CurType;  
6. tempa varchar2;  
7. tempb varchar2;  
8. --此处可声明更多变更^_^  
9. begin  
10.    
11.     OPEN CUR_1 FOR  select * from 表名;  
12.     
13.     --使用前先清空  
14.     execute immediate 'truncate table  临时表表名';  
15.   
16.   LOOP  
17.   FETCH CUR_1 INTO  tempa;  
18.   EXIT WHEN CUR_1%NOTFOUND;  
19.       
20.       --进行相关的业务查询,将结果返回于更多变量上,插入临时表数据        
21.      tempa:='1';  
22.      tempb:='jack';  
23.      insert into 临时表表名(ID,NAME)values(tempa,tempb);   
24.      commit;    
25. end loop;      
26.   open mycur for  select * from  临时表表名;        
27.   CLOSE  CUR_1;  
28.   message :='查询临时表成功';  
29.   EXCEPTION  
30.   WHEN OTHERS THEN  
31.    message :='查询临时表失败';  
32. end  proc_XXX;  

参考更多 
1 创建临时表,插入数据,返回结果集 
Sql代码 

 

1. CREATE OR REPLACE PROCEDURE Report_Month_Responsibility(  
2.  o_cur OUT SYS_REFCURSOR  
3. )  
4. IS  
5. STR VARCHAR2(200);  
6. tb_count INT;  
7. BEGIN  
8.   --先判断全局临时表是否存在，没存在则重新建立：  
9.   select count(*) into tb_count from dba_tables where table_name='REPROTTEST';  
10.   if tb_count=0 then  
11.     STR:=' CREATE GLOBAL TEMPORARY TABLE REPROTTEST(  
12.            ID INT,  
13.            ANAME VARCHAR2(20)  
14.     ) ON COMMIT PRESERVE ROWS';  
15.      execute immediate STR;  
16.   end if;  
17.     
18.   STR:='INSERT INTO REPROTTEST(ID,ANAME)  VALUES(1,''1'')';  
19.   execute immediate STR;  
20.   COMMIT;  
21.   STR:='SELECT * FROM REPROTTEST';  
22.   OPEN o_cur FOR STR; -- 给游标变量赋值  
23. END Report_Month_Responsibility;  

2 调用存储过程 
Sql代码 

 

1. CREATE OR REPLACE PROCEDURE proc_X()  
2. IS  
3.   v_ID INT;  
4.   v_ANAME VARCHAR2(20);        
5.    --定义游标:  
6.    v_account_cur SYS_REFCURSOR;  
7. BEGIN  
8.      --调用存储过程：  
9.        Report_Month_Responsibility(v_account_cur);         
10.        fetch v_account_cur into v_ID,v_ANAME;  
11.       --用循环显示游标中的记录：  
12.        while v_account_cur%found loop  
13.              dbms_output.put_line('The value of column ID is: '||v_ID);--打引列ID  
14.              dbms_output.put_line('The value of column ANAME is: '||v_ANAME);    
15.              --打引列ANAME            
16.              fetch v_account_cur into v_ID,v_ANAME;  
17.        end loop;  
18.        close v_account_cur;      
19.        execute immediate 'truncate TABLE REPROTTEST';     
20. 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