oracle游标循环的嵌套 - 冬天开通的博客 - ITeye技术网站
完成批量修改user_tables中的所有表的栏位名(从MS SQL导入过来，发现大小写问题，造成很多麻烦)
存储过程见下：
-- Created on
by FREE
declare
& -- Local variables here
& Cursor tbl_cur is select table_name from user_
& --col_cur C
&
& tbl_name varchar2(50);
& col_name varchar2(50);
begin
& -- Test statements here
& for tbl_name in tbl_cur LOOP
&&&&& for col_cur in (select COLUMN_NAME from all_tab_columns where table_name=tbl_name.table_name) LOOP
&&&&&&&&& dbms_output.put_line('alter table '||tbl_name.table_name||' rename column "'||col_cur.column_name||'" to '||col_cur.column_name);
&&&&& end LOOP;
& end LOOP;
(从以下转载文章中收获很多，谢谢作者。转自：/wang90627/blog/item/394e7d019d5cd5d.html)
创建存储过程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建一个存储过程的基本语句如下：
CREATE [OR REPLACE] PROCEDURE 存储过程名
&& [(参数[IN|OUT|IN OUT]数据类型…)]
&& {AS|IS}
&&&&& [说明部分]
&&&&& 可执行部分
&& [EXCEPTION
&&&&& 错误处理部分]
&& END [过程名]；
&&& 可选关键字OR REPLACE表示如果存储过程已经存在，则用新的存储过程覆盖，通常用于存储过程的重建。
&&& 参数部分用于定义多个参数(如果没有参数，就可以省略)。参数有三种形式：IN、OUT和IN OUT。如果没有指明参数的形式，则默认为IN。
&&& 关键字AS也可以写成IS，后跟过程的说明部分，可以在此定义过程的局部变量。
&&& 编写存储过程可以使用任何文本编辑器或直接在SQL *Plus环境下进行，编写好的存储过程必须要在SQL *Plus环境下进行编译，生成编译代码，原代码和编译代码在编译过程中都会被存入数据库。编译成功的存储过程就可以在Oracle环境下进行调用了。
&&& 一个存储过程在不需要时可以删除。删除存储过程的人是过程的创建者或者拥有DROP ANY PROCEDURE系统权限的人。删除存储过程的语法如下：
DROP PROCEDURE 存储过程名；
&&& 如果要重新编译一个存储过程，则只能是过程的创建者或者拥有ALTER ANY PROCEDURE系统权限的人。语法如下：
ALTER PROCEDURE 存储过程名 COMPILE；
&&& 执行(或调用)存储过程的人是过程的创建者或是拥有EXECUTE ANY PROCEDURE系统权限的人或是被拥有者授予EXECUTE权限的人。执行的方法如下：
&&& 方法1：
EXECUTE 模式名.存储过程名[(参数…)];
&&& 方法2：
&& 模式名.存储过程名 [(参数…)]；
&&& 传递的参数必须与定义的参数类型、个数和顺序一致(如果参数定义了默认值，则调用时可以省略参数)。参数可以是变量、常量或表达式，用法参见下一节。
&&& 如果是调用本账户下的存储过程，则模式名可以省略。要调用其他账户编写的存储过程，则模式名必须要添加。
下面是我自己的实验：
注：游标可以“显式”声明，也可以“隐式”声明。“隐式”声明的意思是，不用声明游标，在for循环中直接用游标的record就行。例一是内外层循环游标都是“隐式”声明的情况（内层循环的游标，需要用到外层循环的游标值，所以不能两个游标都在第一个循环外声明）。(一般：游标名是xxx__cursor，游标中的每行记录名是xxx_record 。并且，内层循环要用游标，内层的游标应该要用“隐式”声明，外层游标可用可不用。)
CREATE OR REPLACE PROCEDUREmain_mx
&&&&&&&&&& AS
&&&&&&&&&&& BEGIN
&&&&&&&&&&&& FOR mainout_recordIN(& SELECT& /*这里的mainout_record直接是游标的每行记录了。另外IN后面如果是select语句，就要加括号，并且select语句结束不用;结尾，IN后面如果是游标名，就不要括号*/ ID,smscontent,allcode,cjr,pretongdaoid,clientid,shr,pretime,cjsj,shstatus,shsj
from t_busi_main_presend
where shstatus in('1','9') and klstatus='2') LOOP
&&&&&&&&&&&& /* insert into T_BUSI_PRESEND_MX*/
&&&&&&&&&&&&&&& /*& CURSOR mainmobile_cursor IS
&&&&&&&&&&&&&&&&&&&&&& SELECT& * FROM TABLE(fn_split(mainout_record.allcode,','));& */
&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&& FOR mainmobile_record IN (SELECT& column_value cv FROM TABLE(fn_split(mainout_record.allcode,',')))& LOOP /*SELECT& column_value cv FROM TABLE(fn_split(mainout_record.allcode,',')，这句话是执行fn_split这个函数，函数功能类似java中的split功能，但是oracle本身没有这个函数，所以要自己写，代码是我在网上拷的，注意因为mainout_record.allcode这个记录集就只有“一列”记录，allcode是外层游标查出来的一个列名，所以前面是selectcolumn_value，后面打印值的时候也是写.column_value：
功能描述：用指定分隔符切割输入的字符串，返回一维数组，每个数组元素为一个子串。
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);
CREATE OR REPLACE FUNCTION fn_split(p_str IN CLOB, p_delimiter IN VARCHAR2)
RETURN ty_str_split
IS
& j INT := 0;
& i INT := 1;
& len INT := 0;
& len1 INT := 0;
& str VARCHAR2 (4000);
& str_split ty_str_split := ty_str_split ();
BEGIN
& len := LENGTH (p_str);
& len1 := LENGTH (p_delimiter);
& WHILE j & len
& LOOP
&&& j := INSTR (p_str, p_delimiter, i);
&&& IF j = 0
&&& THEN
&&&&&&& j :=
&&&&&&& str := SUBSTR (p_str, i);
&&&&&&& str_split.EXTEND;
&&&&&&& str_split (str_split.COUNT) :=
&&&&&&& IF i &= len
&&&&&&& THEN
&&&&&&&&& EXIT;
&&&&&&& END IF;
&&& ELSE
&&&&&&& str := SUBSTR (p_str, i, j - i);
&&&&&&& i := j + len1;
&&&&&&& str_split.EXTEND;
&&&&&&& str_split (str_split.COUNT) :=
&&& END IF;
& END LOOP;
& RETURN str_
END fn_
*/
&&&&&&&&&&&&&&&&&&&&&&&& DBMS_OUTPUT.PUT_LINE(mainmobile_record.cv);/*打印结果的时候，直接反键存储过程名--&测试有时是出不来结果的，这时应该"新建"--&"命令窗口"--SQL&set serveroutput on&&& --&execute main_mx(这是存储过程名)，这样才能打印结果 */
&&&&&&&&&&&&&&&&&&&&& END LOOP;
&&&&&&&&&&&&& END LOOP;&&&&
&&&&&&&&&& END;
例二（外层for循环游标是“显式”声明）：
CREATE OR REPLACE PROCEDUREmain_mx_1
&&&&&& as
&&&&&&&&&& CURSOR mainout_cursor IS SELECT ID,smscontent,allcode,cjr,pretongdaoid,clientid,shr,pretime,cjsj,shstatus,shsj from t_busi_main_presend where shstatus in('1','9') and klstatus='2';
&&&&&&&&&&& begin
&&&&&&&&&&&& FOR mainout_record IN& mainout_cursor LOOP
&&&&&&&&&&&& /*&&& insert into T_BUSI_PRESEND_MX*/
&&&&&&&&&&&&&& /*& CURSOR mainmobile_cursor IS
&&&&&&&&&&&&&&&&&&&&&& SELECT& * FROM TABLE(fn_split(mainout_record.allcode,','));
&&&&&&&&&&&&&&&& BEGIN*/
&&&&&&&&&&&&&&&&&&& FOR mainmobile_record IN (SELECT& column_value cv FROM TABLE(fn_split(mainout_record.allcode,',')))& LOOP
&&&&&&&&&&&&&&&&&&&&&&&&& DBMS_OUTPUT.PUT_LINE(mainmobile_record.cv);
&&&&&&&&&&&&&&&&&&&&& END LOOP;
&&&&&&&&&&&&&& END LOOP;
&&&&&&&&&& END;
aloneworld
浏览: 48558 次
来自: 广州博客访问： 346266
博文数量： 920
博客积分： 4000
博客等级： 上校
技术积分： 5020
注册时间：
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
这篇文章，是最近在写一个项目中所用的过程，由于是第一次接触oracle，其间花费了许多功夫，才把功能实现！特记录下来，以供参考！create or replace package PSH_GPRSSTREAMSTAT is
-- Author : ADMINISTRATOR-- Created :
10:56:01-- Purpose : GPRS流量统计状态-- 统计GPRS流量
typeC_Cfunction Calcu_GPRSSTREAMend PSH_GPRSSTREAMSTAT;-----------------------------------------------------------------------------create or replace package body PSH_GPRSSTREAMSTAT isfunction Calcu_GPRSSTREAM return numberisc_IPPackHeadLen constant number := 40; -- 定义IP包头长度CURSOR c_SPINFO isselect distinct spid from sh_spinfo where isactive = '0';
c_MDTINFO C_Cv_MDTINFO
v_UpTransContentLens number(20,0); -- 存放当前GPRS终端上传转发的信息内容长度 v_UpContentLens number(20,0);v_UpTotalLens number(20,0); -- 累计GPRS终端上传的信息内容长度 v_DownContentLens number(20,0); v_DownTotalLens number(20,0);newID number(20,0);
begin-- 初始化select max(statid) into newID from sh_if (newID is null) thennewID := 1;
for v_SPINFO In c_SPINFO loop -- 首先获取SPID-- 其次遍历出与当前SPID对应的所有MDTopen c_MDTINFo for select distinct mdtid from sh_mdtinfo where (isactive = '0') and (spid = v_SPINFO.spid);loopfetch c_MDTINFO into v_MDTINFO;exit when c_MDTINFO%
v_UpContentLens := 0;v_UpTransContentLens := 0;v_UpTotalLens := 0;v_DownContentLens := 0;v_DownTotalLens := 0; -- 下面两个select语句是用来获得GPRS终端上传的信息流量select sum(length(content) + c_IPPackHeadLen) into v_UpContentLens from sh_gprsmdtupinfo where (MDTID = v_MDTINFO) and (spid = v_SPINFO.spid) ;select sum(length(content) + c_IPPackHeadLen) into v_UpTransContentLens from sh_gprsmdttransinfo where (issuccess = '1') and (MDTID = v_MDTINFO) and (spid = v_SPINFO.spid) ;if (v_UpContentLens is null) thenv_UpContentLens := 0; if (v_UpTransContentLens is null) thenv_UpTransContentLens := 0; v_UpTotalLens := v_UpTotalLens + v_UpContentLens + v_UpTransContentL
-- 下面的Select语句是用来获得服务商下发的信息流量select sum(length(content) + c_IPPackHeadLen) into v_DownContentLens from sh_gprsspdowninfo where (MDTID = v_MDTINFO) and (spid = v_SPINFO.spid) ;if (v_DownContentLens is null) thenv_DownContentLens := 0;v_DownTotalLens := v_DownTotalLens + v_DownContentL
-- 将统计出的累计值存放到流量状态统计表中 if (v_UpTotalLens >0) or (v_DownTotalLens > 0) theninsert into sh_gprsstreamstat (statid,spid,mdtid,starttime,endtime,mdtupstream,spdownstream)values(newID,v_SPINFO.spid,v_MDTINFO,sysdate,sysdate,v_UpTotalLens,v_DownTotalLens);-- 自增量加1newID := newID + 1;
close c_MDTINFO;
beginend PSH_GPRSSTREAMSTAT;
【责编:landss】
--------------------next---------------------
阅读(113) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。查看: 2955|回复: 7
请教如何不使用游标cursor实现循环？
论坛徽章:0
在oracle pl/sql下，如何可以不使用游标cursor实现循环？ 考虑到cursor的效率问题，想用其他方法替换掉它。我将循环游标大概的逻辑描述如下：
初步想法是将游标的结果集合插入的一个临时物理表里，通过rowno来控制。但是不知道pl/sql如何插入一个自增长id去标示每一行。
不知道各位大牛是否有更好的方法或者建议，麻烦不吝赐教。非常感谢！！
& &&&declare cursor userinfo_cur is & &&&select a.user_id,a.user_name,a.address,b.acct_num,b.acct_class,b.bal & &&&from userinfo_table a,account_table b where a.user_id=b.user_& &&&begin& && & FOR userinfo_rec in userinfo_cur& && & loop& && && & select value_tem_a into tem_exp_a from trans_acct where acct_num= userinfo_rec.acct_& && && & select value_tem_b into tem_exp_b from trans_user where user_id= userinfo_rec.user_& && && & insert into table userinfo_tmp& && && & (a,b,c,d,e)& && && & values
& && && & (value_tem_a, value_tem_b, userinfo_rec.address, userinfo_rec .acct_class);& && && && && && & end& &&&end;
论坛徽章:452
循环里面的两个SELECT 加到游标里面，变成JOIN;
去掉游标，变成INSERT .... SELECT ...
论坛徽章:0
newkid 发表于
循环里面的两个SELECT 加到游标里面，变成JOIN;
去掉游标，变成INSERT .... SELECT ...
可否举个例子呀？insert。。。。select。。。。怎么实现？
论坛徽章:452
& && && &&&insert into table userinfo_tmp& && && & (a,b,c,d,e) ------ 怎么多了一列？？？
& && && && &select trans_acct.value_tem_a
& && && && && && &,trans_user.value_tem_b
& && && && && && &,a.address
& && && && && && &,b.acct_class
& && && && & from userinfo_table a,account_table b ,trans_acct, trans_user
& && && && & where a.user_id=b.user_id
& && && && && && & and trans_acct.acct_num = b.acct_num
& && && && && && & and trans_user.user_id = a.user_id.user_& &&&
论坛徽章:0
如果觉得游标效率低，可以一次性将游标sql中的数据插入到type table变量里，然后循环处理type table变量。
&&cursor c_cursor is
& & select trans_acct.value_tem_a
& && && && && && &,trans_user.value_tem_b
& && && && && && &,a.address
& && && && && && &,b.acct_class
& && && && & from userinfo_table a,account_table b ,trans_acct, trans_user
& && && && & where a.user_id=b.user_id
& && && && && && & and trans_acct.acct_num = b.acct_num
& && && && && && & and trans_user.user_id = a.user_id.user_
& &TYPE cur_tbl IS TABLE OF c_cursor%ROWTYPE INDEX BY BINARY_INTEGER;
& &l_cur_tbl cur_
& & OPEN c_
& & FETCH c_cursor BULK COLLECT INTO l_cur_
& & CLOSE c_
& & FOR i IN 1..l_cur_tbl.count LOOP
& & END LOOP;
&&WHEN OTHERS THEN
& && &IF c_cursor%ISOPEN THEN
& && && && &CLOSE c_
& && &END IF;
论坛徽章:0
newkid 发表于
insert into table userinfo_tmp& && && & (a,b,c,d,e) ------ 怎么多了一列？？？
& && && &&&...
非常感谢！！我去做个十万级别的数据量的测试看看效果：）
论坛徽章:0
aking1220 发表于
如果觉得游标效率低，可以一次性将游标sql中的数据插入到type table变量里，然后循环处理type table变量。
非常非常感谢你的详细建议。我会测试一下速度看看如何：）
认证徽章论坛徽章:275
你这个简单的逻辑，，insert.....select 是 最好的方法了
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号利用Oracle动态游标实现动态SQL循环遍历_数据库技术_Linux公社-Linux系统门户网站
你好，游客
利用Oracle动态游标实现动态SQL循环遍历
来源：Linux社区&
作者：qinyingxiong
利用动态游标实现动态SQL循环遍历
create&or&replace&procedure&P_TEST_SQL&is&&
TYPE&ref_cursor_type&IS&REF&CURSOR;&&&&
tablename&varchar2(200)&default&'ess_client';&&
v_sql&varchar2(1000);&&
mobile&varchar2(15);&&
usrs&ref_cursor_&&
&&v_sql&:=&'select&usrmsisdn&from&'||tablename||'&where&rownum&&&11';&&
&&open&usrs&for&v_sql&;&&
&&&&&&fetch&usrs&into&&&&
&&&&&&exit&when&usrs%&&
&&&&&&insert&into&tmp(usrmsisdn)&values(mobile);&&
&&close&&&
&&commit;&&
end&P_TEST_SQL;&&
相关资讯 & & &
& (11/10/:58)
& (08/22/:00)
& (06/18/:32)
& (10/22/:50)
& (07/18/:00)
& (02/16/:35)
　　　同意评论声明
　　　发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款
moxiaoyis 发表于 含有占位符的动态SQL呢
(0) oracle 发表于 多谢&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
还没回到阿？
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
The article is great. I got the same error and keep scratching my head till I found your page. Keep it up!
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
继续下去啊。这个问题应该是一个比较典型的问题了吧，继续讨论阿
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
pst.close();
rs.close();
只要及时关闭应该就可以了吧？
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
首先结果集resultset在你的java程序处。其中有个fetchsize设置，这个表示每次从数据库处取多少条记录到resultset.
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
当通过ResultSet.next()，移动结果集指针时，此时是否还与数据库发生交互？
不发生交互，数据库执行完查询后，已经把查询结果交给ResultSet了，以后的操作，和数据库无关。
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
交互，如果1个查询有10000条记录，resultset中只有fetchsize条，当next时还会在一定时机去交互
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
白是对的。
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
晕。。。。。。。。。
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
一搜索这个问题竟然到了你的blog，真是冤孽啊。。：）
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
不是将查询结果一次性放到ResultSet中, 而是分批放入ResultSet中,一般情况下是每次10条记录.
当通过ResultSet.next()，移动结果集指针时，此时还会与数据库发生交互.
导致maximum open cursors exceeded这个问题一般是代码本身的问题.
比如执行一个查询没有关闭游标，或是在存储过程中打开了游标而没有关闭．
特别是出现异常情况是没有关闭游标．严重时会导致系统崩掉
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
我也是同样的问题，但我开发语言是用ＤＥＬＰＨＩ，使用组件是ＡＤＯＱＵＥＲＹ，游标超过最大数困扰我好久了，如果有谁知道，请告之jxprovince@
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
啊~!~`我刚也犯了这种错误!!``看到这里的贴子,小妹又明白了:&哦!``原来conn.createStatement()和conn.prepareStatement()的时候实际上都是相当与在数据库中打开了一个cursor。!!&明白了这点过后,我就在循环里新建一个prepareStatement对象,操作完一次,成功过后就马上关掉!这样,循环里每次一个insert都新new 一个prepareStatement 操作成功完成之后再马上关闭.嘎嘎.~这样,cursor就不会溢出拉.
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
我遇到的就是这个问题，看了你的文章终于明白了
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
有时候出于性能方面的考虑，多个用户使用一个数据库连接，这时一定要及时关闭cursor，否则就会报这个错。
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
当通过ResultSet.next()，移动结果集指针时，此时是否还与数据库发生交互？
不与数据库交互，此时数据集拿到一个collection中，ResultSet就是一个collectin是实现
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
谢谢
犯了跟你一样的错
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
你是對的...
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)[未登录]&&&&
我的问题还是这个，但是估计你没有回答到点子上。我在开发过程中，本身用Hibernate3应该完全不用我们干预Session的工作吧，要不还要它作甚？我的测试系统在MYSQL数据库上从来不会出现这个问题，但是切换到生产系统，除了数据库URL和方言配置改成ORACLE外，其他全部不变，但是它就是出了这个ORA-01000，这不是逼着我不用这个破Hibernate嘛
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)[未登录]&&&&
好像是这样的，出现了同样的报错，看程序也是在循环里面prepareStatement
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
ResultSet不是与数据库的一个映射关系吗，rs.close()之后，ResultSet.next()不行吧....
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
刚好碰到了这个问题，谢了、、、
&re: 关于ORACLE游标的问题(ORA-01000: maximum open cursors exceeded)&&&&
只要不循环或者过多的创建statement就行了，一般写循环逻辑会出现这类问题。
有些人提到jpa类的框架，这种情况如果出现不是框架的问题，而是使用方法不得当造成的，statement的类型选择或者sql的结构优化不对，导致statement创建过多。
next是否跟数据库交互，取决于你的fetchsize
阅读排行榜
评论排行榜