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

我会带着你远行 2022-04-02 07:49 227阅读 0赞

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

*  **博客分类：** 
 *  [oracle][oracle 1]

[oracle][oracle 2][proc][][临时表][Link 1][游标][Link 2]

参考资料   
1 ORACLE 存储过程返回临时表结果集   
[http://hi.baidu.com/h\_sn999/blog/item/4211810f4d7542fdaa645738.html][http_hi.baidu.com_h_sn999_blog_item_4211810f4d7542fdaa645738.html]   
2 ORACLE 在存储过程中使用临时表   
[http://blog.csdn.net/wekily/article/details/6120900][http_blog.csdn.net_wekily_article_details_6120900]   
3 Oracle存储过程中创建临时表<原创>   
[http://blog.sina.com.cn/s/blog\_4c7ae2a80100bki3.html][http_blog.sina.com.cn_s_blog_4c7ae2a80100bki3.html]   
4 在ORACLE存储过程中创建临时表   
[http://huqiji.iteye.com/blog/782067][http_huqiji.iteye.com_blog_782067]   
总结如下：   
**DDL是一种消耗资源非常大的操作，运行时尽量不要使用DDL语句，应用程序需要的临时表应在运行之前就开始创建。不必在每个存储过程中创建一次。临时表总是存在的，他们作为对象存在于数据字典中，并且总是保持为空，直到有会话在其中放入数据**   
*1 创建临时表*   
Sql代码

![收藏代码][20181225195652504]

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

2 创建存储过程   
Sql代码

![收藏代码][20181225195652504]

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代码

![收藏代码][20181225195652504]

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代码

![收藏代码][20181225195652504]

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]

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

2011-09-22 14:49:10

收藏   [我要投稿][Link 3]

总结如下：  
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][https_www.2cto.com_database_201109_105306.html]

[oracle]: https://liuzidong.iteye.com/blog/1177894
[oracle 1]: https://liuzidong.iteye.com/category/97326
[oracle 2]: http://www.iteye.com/blogs/tag/oracle
[proc]: http://www.iteye.com/blogs/tag/proc
[Link 1]: http://www.iteye.com/blogs/tag/%E4%B8%B4%E6%97%B6%E8%A1%A8
[Link 2]: http://www.iteye.com/blogs/tag/%E6%B8%B8%E6%A0%87
[http_hi.baidu.com_h_sn999_blog_item_4211810f4d7542fdaa645738.html]: http://hi.baidu.com/h_sn999/blog/item/4211810f4d7542fdaa645738.html
[http_blog.csdn.net_wekily_article_details_6120900]: http://blog.csdn.net/wekily/article/details/6120900
[http_blog.sina.com.cn_s_blog_4c7ae2a80100bki3.html]: http://blog.sina.com.cn/s/blog_4c7ae2a80100bki3.html
[http_huqiji.iteye.com_blog_782067]: http://huqiji.iteye.com/blog/782067
[20181225195652504]: /images/20220402/ae5ef60c07544d45aac09033c3a51bc8.png
[Link 3]: https://www.2cto.com/index.php?m=member&c=content&a=publish&modid=1&siteid=1
[https_www.2cto.com_database_201109_105306.html]: https://www.2cto.com/database/201109/105306.html