ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
查看: 5678|回复: 21

[PL/SQL] 暴力方式获取存储过程中所有用到的表

[复制链接]
求职 : 研发工程师
论坛徽章:
3
优秀写手
日期:2014-02-13 06:00:122014年新春福章
日期:2014-02-18 16:48:49马上加薪
日期:2014-02-18 16:48:49
跳转到指定楼层
1#
发表于 2014-1-12 22:28 | 只看该作者 |只看大图 回帖奖励 |倒序浏览 |阅读模式
       上上周学习原先项目组遗留下来的报表存储过程,打开一看,3000+行,用了N多的游标,瞬间头大。打算先整理一下都用到了哪些个源表。自从接触数据库以来,一直的习惯就是,能自动化的坚决不人工,本着这种精神,写了一个存储过程以暴力的方式来找出存储过程中用到的所有源表,效果还算可以。分享代码,与大家共同进步!

原理简述:利用正则表达式匹配存储过程代码中的 user.table_name 模式,其中 user. 为可选匹配,尝试对每一个匹配进行查询操作,成功则视为一个表,失败则忽略进行下一组校验。

注意事项:该存储过程可以找出所有在指定存储过程中出现的表名,无论是使用了的还是出现在注释中的,甚至如果一个表的别名 a 如果也刚好是数据库的一个表,也会被记录下来。

-------------------------------------------------下面是代码----------------------------------------------------------------
首先是建立一个用于存放结果的表:
  1. create table FIND_RESULT
  2. (
  3.   procedure_name VARCHAR2(100),
  4.   v_last_word    VARCHAR2(100),
  5.   table_name     VARCHAR2(30),
  6.   table_comment  VARCHAR2(200),
  7.   line_num       NUMBER(12)
  8. )
  9. ;
复制代码

字段释义:
procedure_name -- 解析的存储过程名
v_last_word -- 寻找到的源表的前一个单词
table_name -- 寻找到的源表
table_comment -- 源表的表注释
line_num -- 源表在存储过程中第一次出现的行号

存储过程代码:
  1. procedure find_tables(v_procedure_name varchar2) as
  2.   v_word       varchar2(100) := ''; --当前查找到的单词
  3.   v_ind        int; --索引
  4.   v_last_word  varchar2(100); --上一个单词
  5.   v_is_table   boolean;
  6.   v_dbuser_pos int;
  7.   v_dbuser     varchar2(100);
  8.   v_dblink_pos int;
  9.   v_dblink     varchar2(100);
  10.   v_table      varchar2(100);

  11.   v_insert_sql varchar2(4000);
  12. begin
  13.   --查找存储过程中用到的tables
  14.   delete from find_result where procedure_name = upper(v_procedure_name);

  15.   commit;
  16.   for line in (select *
  17.                  from user_source
  18.                 where lower(name) = lower(v_procedure_name)) loop
  19.     --dbms_output.put_line('line:' || line.text || '.');
  20.     v_ind := 1;
  21.     --处理每行中的符合变量命名规则(字母开头,只有字母数字下划线)的标识符
  22.     loop
  23.       v_last_word := v_word;
  24.       v_word      := nvl(regexp_substr(lower(line.text),
  25.                                        '([a-z0-9_]+\.)*[a-z][a-z0-9_]*(@[a-z0-9_]+)*',
  26.                                        1,
  27.                                        v_ind),
  28.                          '***');
  29.       exit when v_word = '***';
  30.       --dbms_output.put_line('word:' || v_word || '.');
  31.       begin
  32.         execute immediate 'select count(*) from ' || v_word ||
  33.                           ' where rownum=1';
  34.         v_is_table := True;
  35.       exception
  36.         when others then
  37.           --dbms_output.put_line(sqlerrm(sqlcode));
  38.           v_is_table := False;
  39.       end;
  40.       if v_is_table then
  41.         v_dbuser_pos := instr(v_word, '.');
  42.         v_dblink_pos := instr(v_word, '@');
  43.         select case
  44.                  when v_dbuser_pos = 0 then
  45.                   (select username from user_users where rownum = 1)
  46.                  else
  47.                   substr(v_word, 1, v_dbuser_pos - 1)
  48.                end as dbuser,
  49.                substr(v_word,
  50.                       v_dbuser_pos + 1,
  51.                       case
  52.                         when v_dblink_pos = 0 then
  53.                          length(v_word) - v_dbuser_pos
  54.                         else
  55.                          v_dblink_pos - v_dbuser_pos - 1
  56.                       end) as table_name,
  57.                case
  58.                  when v_dbuser_pos > 0 then
  59.                   ''
  60.                  when v_dblink_pos = 0 then
  61.                   ''
  62.                  else
  63.                   '@' || substr(v_word, v_dblink_pos + 1)
  64.                end as dblink
  65.           into v_dbuser, v_table, v_dblink
  66.           from dual;
  67.         v_insert_sql := 'insert into find_result' ||
  68.                         ' select upper(''' || v_procedure_name || '''),' || '''' ||
  69.                         v_last_word || ''' as last_word,' ||
  70.                         'a.table_name,' || 'b.comments,' || line.line ||
  71.                         ' as line_num' || ' from ' || 'all_tables' ||
  72.                         v_dblink || ' a' || ' left join ' ||
  73.                         'all_tab_comments' || v_dblink || ' b' ||
  74.                         ' on a.table_name = b.table_name' ||
  75.                         ' and a.owner=b.owner' ||
  76.                         ' where a.table_name = upper(''' || v_table ||
  77.                         ''')' || ' and a.owner=upper(''' || v_dbuser ||
  78.                         ''')' || ' and a.table_name not in' ||
  79.                         '(select table_name' ||
  80.                         ' from find_result' ||
  81.                         ' where procedure_name = upper(''' ||
  82.                         v_procedure_name || '''))';
  83.         --dbms_output.put_line(v_insert_sql);
  84.         execute immediate v_insert_sql;
  85.       end if;
  86.       v_ind := v_ind + 1;
  87.     end loop;
  88.   end loop;
  89.   commit;
  90. end;
复制代码
thanks for reading
论坛徽章:
740
红钻
日期:2014-12-16 17:51:41布鲁克林篮网
日期:2016-09-23 08:17:18达拉斯小牛
日期:2016-09-23 08:18:15季节之章:冬
日期:2015-07-31 17:16:14ITPUB季度 技术新星
日期:2014-07-17 14:37:00华盛顿奇才
日期:2016-09-23 08:18:15季节之章:夏
日期:2015-07-31 17:16:29绿钻
日期:2015-08-15 13:20:11最佳人气徽章
日期:2015-03-19 09:44:03洛杉矶湖人
日期:2016-09-23 08:18:15
2#
发表于 2014-1-12 23:00 来自手机 | 只看该作者
select *from dba_dependencies. t where t.refer..name ='包名'

使用道具 举报

回复
认证徽章
论坛徽章:
168
SQL数据库编程大师
日期:2016-01-13 10:30:43SQL极客
日期:2013-12-09 14:13:35SQL大赛参与纪念
日期:2013-12-06 14:03:45最佳人气徽章
日期:2015-03-19 09:44:03现任管理团队成员
日期:2015-08-26 02:10:00秀才
日期:2015-07-28 09:12:12举人
日期:2015-07-13 15:30:15进士
日期:2015-07-28 09:12:58探花
日期:2015-07-28 09:12:58榜眼
日期:2015-08-18 09:48:03
3#
发表于 2014-1-12 23:05 | 只看该作者
oracle_cj 发表于 2014-1-12 23:00
select *from dba_dependencies. t where t.refer..name ='包名'

使用道具 举报

回复
求职 : 研发工程师
论坛徽章:
3
优秀写手
日期:2014-02-13 06:00:122014年新春福章
日期:2014-02-18 16:48:49马上加薪
日期:2014-02-18 16:48:49
4#
 楼主| 发表于 2014-1-12 23:21 | 只看该作者
oracle_cj 发表于 2014-1-12 23:00
select *from dba_dependencies. t where t.refer..name ='包名'

又学习了一个新东西,非常感谢~

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
479
法拉利
日期:2014-01-25 00:27:172014年世界杯参赛球队: 伊朗
日期:2014-05-23 10:41:312014年世界杯参赛球队: 比利时
日期:2014-06-17 12:09:43itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38
5#
发表于 2014-1-13 01:18 | 只看该作者
多了解一些系统视图是能避免重复造车轮的

使用道具 举报

回复
论坛徽章:
2
2013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2014-06-18 06:00:13
6#
发表于 2014-1-13 09:19 | 只看该作者
佩服

使用道具 举报

回复
论坛徽章:
0
7#
发表于 2014-1-13 09:22 | 只看该作者
学习了

使用道具 举报

回复
论坛徽章:
0
8#
发表于 2014-1-13 09:24 | 只看该作者
oracle_cj 发表于 2014-1-12 23:00
select *from dba_dependencies. t where t.refer..name ='包名'

学习了。。

使用道具 举报

回复
求职 : 数据库管理员
认证徽章
论坛徽章:
11
ITPUB社区千里马徽章
日期:2013-06-09 10:15:34懒羊羊
日期:2015-03-04 14:52:11懒羊羊
日期:2015-02-10 13:36:05马上有对象
日期:2015-02-02 12:29:02红宝石
日期:2015-01-19 09:44:10马上有车
日期:2014-11-11 14:16:07马上有车
日期:2014-03-27 15:59:39优秀写手
日期:2014-03-12 06:00:13马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09
9#
发表于 2014-1-13 09:27 | 只看该作者
oracle_cj 发表于 2014-1-12 23:00
select *from dba_dependencies. t where t.refer..name ='包名'

如果是用的动态SQL的话找不出来吧?

使用道具 举报

回复
论坛徽章:
1089
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30
10#
发表于 2014-1-13 10:13 | 只看该作者
moseslin 发表于 2014-1-13 09:27
如果是用的动态SQL的话找不出来吧?

是的,用正则有一定优势,但是有的也找不出来。。。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表