开发者社区> 游客gsgxy6yg6ydlm> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL高阶技能:游标的使用

简介: SQL高阶技能:游标的使用
+关注继续查看

节前工作中遇到一个比较棘手的问题,拿出来和大家分享一下。



问题内容

有一批Oracle数据库中的表(超过1000张),表中的一些字段的指定字符需要修改成其他指定字符。表名是类似的,例如TABLE0001,TABLE0002,TABLE0003等等,但是表名并不是连续的,有可能没有表TABLE0100,TABLE0200,TABLE0300等等。字段名称也是类似的,例如FIELD001,FIELD002,FIELD003以此类推。表的字段名也不固定,有的可能只有1个字段,有的可能有100多个字段,而且有的表有可能没有数据。现在就是要把所有这些表里的字段中包含“张三”字符的内容全部修改成“李四”,字段中的其他字符不变,例如:“张三是个好学生”改成“李四是个好学生”。


看到这样的题目,脑海中想到的第一个办法就是写个游标让它重复执行。但是随着我仔细的深入,发现并没那么简单,问题分解如下:


1表名不连续的问题

表名不连续就要判断这个表是否存在数据库当中,这时候想到的是从Oracle的系统表USER_TABLES(用户表)中找到这些表,然后再插入到一张临时表中,并且将表中的记录数也一起插入,方便过滤记录为0的表,减少后期的内容更新。

查询语句如下:

DECLARE 
CURSOR CURS ISSELECT TABLE_NAME FROM USER_TABLES
WHERE TABLE_NAME LIKE 'TABLE%';
v_sql VARCHAR2(4000);
BEGIN
    FOR CUR IN CURS LOOP
    v_sql :=' SELECT  '||''CUR.TABLE_NAME''||',COUNT(1) T_CNT
    INTO TEMP_TABLE FROM '||CUR.TABLE_NAME;
    EXECUTE IMMEDIATE v_sql;
    END LOOP;
END;
END;


很遗憾的是这里一直报错,错误原因是我想把变量的表名CUR.TABLE_NAME作为字符插入到临时表中,这样我查询临时表TEMP_TABLE就可以直接知道每个表的具体记录数,但是在动态SQL语句中就是不成功,只能另辟蹊径。

将遍历出来的表放在EXCEL里面自动生成我需要的查询语句,如下图:

image.png

image


EXCEL完成查询语句拼接

然后将这些拼接好的查询语句开始执行,即可将每张表的数据量查询出来并插入到临时表TEMP_TABLE中。第一步算顺利完成啦~


2字段不固定

这个问题和表名不连续有点类似,但是也有不同的地方,我开始的做法是把所有列都给列出来,说做就做。

具体代码如下:


DECLARE
CURSOR mycur ISSELECT T_NAME
FROM TEMP_TABLE WHERE T_CNT<>0 ;
myrecord mycur%ROWTYPE;
v_sql1 VARCHAR2(4000);
v_sql2 VARCHAR2(4000);
v_sql3 VARCHAR2(4000);
v_sql4 VARCHAR2(4000);
v_sql5 VARCHAR2(4000);
BEGINOPEN mycur;
LOOP
FETCH mycur INTO myrecord;
EXIT WHEN mycur%NOTFOUND;
v_sql1 :='UPDATE '||myrecord.T_NAME||' SET
FIELD001=REPLACE(FIELD001,''张三'',''李四'')';
v_sql2 :='UPDATE '||myrecord.T_NAME||' SET 
FIELD002=REPLACE(FIELD002,''张三'',''李四'')';
v_sql3 :='UPDATE '||myrecord.T_NAME||' SET 
FIELD003=REPLACE(FIELD003,''张三'',''李四'')';
v_sql4 :='UPDATE '||myrecord.T_NAME||' SET 
FIELD004=REPLACE(FIELD004,''张三'',''李四'')';
v_sql5 :='UPDATE '||myrecord.T_NAME||' SET 
FIELD005=REPLACE(FIELD005,''张三'',''李四'')';
EXECUTE IMMEDIATE v_sql1;
EXECUTE IMMEDIATE v_sql2;
EXECUTE IMMEDIATE v_sql3;
EXECUTE IMMEDIATE v_sql4;
EXECUTE IMMEDIATE v_sql5;
END LOOP;
CLOSE mycur;
END;

代码可以顺利执行,但是里面发现了个问题,它这个列不一定存在,如果执行中没有这个列,肯定会报错,数据库报错是不会自动帮你跳过的,就直接停止了。这样达不到效果!



3进一步修改

既然这个列有可能不存在,那我先给它来个判断如何?

顺着这个思路,对代码加以修改,从系统表USER_TAB_COLUMNS中查询被更新的表中是否存在定义的列名,如果存在则执行更新,不存在就跳过。

具体代码如下:


DECLARE
--定义游标
CURSOR mycur IS  
--将临时表TEMP_TABLE中记录数不为0的表查询出来
SELECT T_NAME FROM TEMP_TABLE WHERE T_CNT<>0 ;
myrecord mycur%ROWTYPE;
--定义变量
v_sql1 VARCHAR2(4000);
v_count INTEGER;
--定义列变量,
v_name VARCHAR2(20) := 'TABLE0001';
BEGIN
--打开游标
OPEN mycur;
LOOP
FETCH mycur INTO myrecord;
EXIT WHEN mycur%NOTFOUND;
--从系统表USER_TAB_COLUMNS中查询被更新的表中是否存在定义的列名
SELECT COUNT(*) INTO v_count
FROM USER_TAB_COLUMNS
WHERE table_name=myrecord.T_NAME
and column_name=v_name;
--如果存在执行更新
IF v_count>0 THEN
v_sql1 :='UPDATE '||myrecord.T_NAME||' SET 
'||v_name||'=REPLACE('||v_name||',''张三'',''李四'')';
EXECUTE IMMEDIATE v_sql1;
END IF;
END LOOP;
--结束游标
CLOSE mycur;
END;


结果成功了,只需要修改变量v_name即可。执行一次挺快的,只需要7秒左右。

4后续优化

还有最后一步可以完全自动化执行,就是把所有列都放到一个表,循环执行这个游标,前提是知道最大列有多少个,或者直接取最大值FIELD999,这样在执行的时候可能会比较耗时间,因为每一轮他都要判断999次列是否存在。

至此,整改批量修改部分字段内容的功能全部完成。

5一点思考

从这个题目中引发我的一点思考,有好的也有坏的。

好的是遇到问题能用其他办法解决就不要一棵树上吊死,第一步出错的地方至今未找到。如果把时间全放在这一步肯定得不偿失。工作中更多讲究的是效率,能用其他方法解决问题就尽快换方法解决。此外将问题细化也能让思维更优逻辑性。坏的是遇到问题就一股脑的写代码,也不考虑写的结果是怎么样,这样其实还是很费时间的,磨刀不误砍柴工,将问题思考清楚再处理会更好。

6结语

工作和生活中难免会遇到一些难题,当你能把一道题或者一件事通过自己思考做出来,那种成就感不言而喻。可能这就是程序员的快乐吧!

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Sql 存储过程加游标结合的使用
Sql 存储过程加游标结合的使用
49 0
SQLSERVER 游标的使用
sql server 的游标使用
6160 0
如何在微服务之间共享使用数据库
本文讲的是如何在微服务之间共享使用数据库,【编者的话】如何在微服务之间共享使用数据库?本文介绍了一个该领域很容易犯错的架构问题,并且提出了解决方案和反思。
1874 0
摩拜单车使用
本文的原文连接是: http://blog.csdn.net/freewebsys/article/details/52984942 未经博主允许不得转载。 博主地址是:http://blog.csdn.net/freewebsys 1,关于摩拜 摩拜单车 http://mobike.com/ 做的很上心的自行车,自己做硬件也做软件。 2,关于优点
1062 0
高性能Javascript--高效的数据访问
  接上一篇,希望能写一个高性能Javascript专题。   第一篇:高性能Javascript--脚本的无阻塞加载策略。   参考摘录《高性能Javascript》。   经典计算机科学的一个问题是,数据应当存放在什么地方,以实现最佳的读写效率。
784 0
SQL Server游标的使用
原文地址:http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html 游标是邪恶的!        在关系数据库中,我们对于查询的思考是面向集合的。而游标打破了这一规则,游标使得我们思考方式变为逐行进行.对于类C的开发人员来着,这样的思考方式会更加舒服。        正常面向集合的思维方式是:      
915 0
SQL Server游标的使用
原文地址:http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html 游标是邪恶的!        在关系数据库中,我们对于查询的思考是面向集合的。而游标打破了这一规则,游标使得我们思考方式变为逐行进行.对于类C的开发人员来着,这样的思考方式会更加舒服。        正常面向集合的思维方式是:      
953 0
61
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载