通过pl/sql来格式化sql(r4笔记第63天)

在之前的一篇博文中分享了通过java来格式化sql,http://blog.itpub.net/23718752/viewspace-1444910/ 今天突然想试试通过sql来格式化一把pl/sql试试,想起来容易,做起来难,自己捣鼓了半天,总算是弄出点雏形了。简单分享一下。 我们还是格式化同一段sql语句。

select /*+ leading(s) index(s TABLE_BPM_STEP_INST_5IX) use_nl(s p
p step) */ s.ALLOW_CREATE, s.ASSIGNEE, s.ASYNC_RETURNED_PARAMS, s
.ATTACHER2STEP_INST, s.COMMITTER, s.CONTROL_COUNT, s.CURR_FAULT2F
AULT_INFO, s.DO_AVAIL_ON_RESUM, s.DO_FIN_ON_RESUM, s.HAS_DEPENDEN
TS, s.HAS_MARCH_REND, s.HAS_REND, s.INFLOW_BITS, s.ITER_COUNT, s.
NUM_OR_PREREQS, s.NUM_PENDING, s.NUM_PENDING_PREREQS, s.OBJID, s.
OUTFLOW_BITS, s.PARAMS, s.PARENT2PROC_INST, s.ROOT2PROC_INST, s.S
TART_TIME, s.STATUS, s.STATUS_CHANGE_TIME, s.STEP2STEP, s.TARGETE
D_BY_ALARMS, s.TRIGGERS_ALARMS, s.WAIT_TIME, s.WORKER FROM TABLE_
BPM_PROC_INST p, TABLE_BPM_STEP_INST s, TABLE_BPM_STEP step WHERE
 s.root2proc_inst = p.objid AND s.step2step = step.objid AND ( NO
T (step.step_type = 4)) AND p.root_status in (0, 14) AND s.commit
ter = :1 AND s.assignee in ('BpmInServer', 'BpmInServerSmThr', 'B
pmJms') AND s.status in (50, 55) AND s.curr_fault2fault_info is n
ull and ( p.EXEC_DOMAIN like :2 ) 

自己尝试通过创建一个临时用的表,然后通过pl/sql来简单分析,从这个过程来看,pl/sql处理的思路和java还是差别很大。 create table tmp_format_sql(text varchar2(200)); 首先得到一个sql文件,内容如上,我们假设为test.sql 格式化成为insert 语句。

[ora11g@rac1 ~]$  awk '{print "insert into tmp_format_sql values(" "'\''"$0"'\''" " );"}' test.sh
insert into tmp_format_sql values('select /*+ leading(s) index(s TABLE_BPM_STEP_INST_5IX) use_nl(s p' );
insert into tmp_format_sql values('p step) */ s.ALLOW_CREATE, s.ASSIGNEE, s.ASYNC_RETURNED_PARAMS, s' );
insert into tmp_format_sql values('.ATTACHER2STEP_INST, s.COMMITTER, s.CONTROL_COUNT, s.CURR_FAULT2F' );
insert into tmp_format_sql values('AULT_INFO, s.DO_AVAIL_ON_RESUM, s.DO_FIN_ON_RESUM, s.HAS_DEPENDEN' );
insert into tmp_format_sql values('TS, s.HAS_MARCH_REND, s.HAS_REND, s.INFLOW_BITS, s.ITER_COUNT, s.' );
insert into tmp_format_sql values('NUM_OR_PREREQS, s.NUM_PENDING, s.NUM_PENDING_PREREQS, s.OBJID, s.' );
insert into tmp_format_sql values('OUTFLOW_BITS, s.PARAMS, s.PARENT2PROC_INST, s.ROOT2PROC_INST, s.S' );
insert into tmp_format_sql values('TART_TIME, s.STATUS, s.STATUS_CHANGE_TIME, s.STEP2STEP, s.TARGETE' );
insert into tmp_format_sql values('D_BY_ALARMS, s.TRIGGERS_ALARMS, s.WAIT_TIME, s.WORKER FROM TABLE_' );
insert into tmp_format_sql values('BPM_PROC_INST p, TABLE_BPM_STEP_INST s, TABLE_BPM_STEP step WHERE' );
insert into tmp_format_sql values(' s.root2proc_inst = p.objid AND s.step2step = step.objid AND ( NO' );
insert into tmp_format_sql values('T (step.step_type = 4)) AND p.root_status in (0, 14) AND s.commit' );
insert into tmp_format_sql values('ter = :1 AND s.assignee in ('BpmInServer', 'BpmInServerSmThr', 'B' );
insert into tmp_format_sql values('pmJms') AND s.status in (50, 55) AND s.curr_fault2fault_info is n' );
insert into tmp_format_sql values('ull and ( p.EXEC_DOMAIN like :2 )' );

直接运行生成的Insert语句即可,使用sed先来把单引号‘替换成为'' 然后通过awk来拼接成需要的sql语句。 然后使用pl/sql来直接解析tmp_format_sql中的数据即可,pl/sql的内容很简单,相比java的处理来说要简化很多

set feedback off
set serveroutput on
declare
a varchar2(200);
b varchar2(200);
cursor tmp_sql is select substr(text,1,instr(text,' ',-1,1))left_part,substr(text,instr(text,' ',-1,1)) right_part from tmp_format_sql;  --这是最重要的语句,以每行最后的一个空格为界,把每一行分成两部分,在循环中拼接。
begin
for i in tmp_sql loop
i.left_part:=a||i.left_part;
a:=i.right_part;
dbms_output.put_line(i.left_part);
end loop;
end;
/

得到的结果如下:

select /*+ leading(s) index(s TABLE_BPM_STEP_INST_5IX) use_nl(s
pp step) */ s.ALLOW_CREATE, s.ASSIGNEE, s.ASYNC_RETURNED_PARAMS,
s.ATTACHER2STEP_INST, s.COMMITTER, s.CONTROL_COUNT,
s.CURR_FAULT2FAULT_INFO, s.DO_AVAIL_ON_RESUM, s.DO_FIN_ON_RESUM,
s.HAS_DEPENDENTS, s.HAS_MARCH_REND, s.HAS_REND, s.INFLOW_BITS, s.ITER_COUNT,
s.NUM_OR_PREREQS, s.NUM_PENDING, s.NUM_PENDING_PREREQS, s.OBJID,
s.OUTFLOW_BITS, s.PARAMS, s.PARENT2PROC_INST, s.ROOT2PROC_INST,
s.START_TIME, s.STATUS, s.STATUS_CHANGE_TIME, s.STEP2STEP,
s.TARGETED_BY_ALARMS, s.TRIGGERS_ALARMS, s.WAIT_TIME, s.WORKER FROM
TABLE_BPM_PROC_INST p, TABLE_BPM_STEP_INST s, TABLE_BPM_STEP step
WHERE s.root2proc_inst = p.objid AND s.step2step = step.objid AND (
NOT (step.step_type = 4)) AND p.root_status in (0, 14) AND
s.committer = :1 AND s.assignee in ('BpmInServer', 'BpmInServerSmThr',
'BpmJms') AND s.status in (50, 55) AND s.curr_fault2fault_info is
null and ( p.EXEC_DOMAIN like :2

预期结果和java格式化的一致,可以从这个过程中看出来,同一个功能有多种实现方式,oracle中对于字符处理的功能还是很强大的,可以根据自己的需要来灵活使用。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-03-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

通过shell脚本得到数据字典的信息 (r2笔记72天)

在平时的工作中,可能需要查询一些数据字典的信息,比如数据字典对应的基表信息,可以得到更多数据库内部的一些详细信息。 比如user_objects这个数据字典视图...

2867
来自专栏文渊之博

初识SQL Server2017 图数据库(一)

背景:   图数据库对于表现和遍历复杂的实体之间关系是很有效果的。而这些在传统的关系型数据库中尤其是对于报表而言很难实现。如果把传统关系型数据库比做火车的话,那...

2948
来自专栏一个会写诗的程序员的博客

图解 SQL join 语句内联合(inner join)全外联合(full outer join)左外联合(left outer join)笛卡尔积 (交叉联合(cross join))

我们用过name字段用几种不同方式把这些表联合起来,看能否得到和那些漂亮的韦恩图在概念上的匹配。

942
来自专栏乐沙弥的世界

SQL server 2005 PIVOT运算符的使用

        PIVOT,UNPIVOT运算符是SQL server 2005支持的新功能之一,主要用来实现行到列的转换。本文主要介绍PIVOT运算符的操作,...

702
来自专栏向治洪

百度地图之收索视野内的建筑物

根据用户移动地图的位置,显示在视野范围内的建筑物,简单的思路是,添加地图监听,当地图移动结束之后,计算出当前屏幕四个角的GeoPoint,根据这4个点,通过my...

1769
来自专栏数据处理

CEdit只能输入16进制数

1375
来自专栏landv

金蝶K/3 审批相关SQL语句

731
来自专栏数据和云

Oracle Hints - 先知的提示

在上周恩墨微信大讲堂的讨论中,几个有趣的视图跃入我们的视野,可以分享给大家。 在Oracle 11g中,新增的视图V$SQL_HINT记录了Oracle数据库中...

2896
来自专栏杨建荣的学习笔记

探索ASH 第一篇

老是在用ASH,对它的依赖感觉已经大于AWR,昨天心血来潮,想看看ash视图里面是怎么样的,过程也算曲折,不过也算抛砖引玉。 先看看v$active_sessi...

4318
来自专栏乐沙弥的世界

SQL server 2005 UNPIVOT运算符的使用

      UNPIVOT运算符相对于PIVOT运算符,它执行与PIVOT相反的操作,即将列转换到行。需要注意的是UNPIVOT运算符并不完全是PIVOT的逆向...

721

扫码关注云+社区

领取腾讯云代金券