通过pl/sql来格式化sql(r4笔记第63天) |
您所在的位置：网站首页 › plsql如何格式化sql › 通过pl/sql来格式化sql(r4笔记第63天) |
|
在之前的一篇博文中分享了通过java来格式化sql,http://blog.itpub.net/23718752/viewspace-1444910/ 今天突然想试试通过sql来格式化一把pl/sql试试，想起来容易，做起来难，自己捣鼓了半天，总算是弄出点雏形了。简单分享一下。 我们还是格式化同一段sql语句。 代码语言：javascript复制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 语句。 代码语言：javascript复制[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的处理来说要简化很多 代码语言：javascript复制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; /得到的结果如下： 代码语言：javascript复制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中对于字符处理的功能还是很强大的，可以根据自己的需要来灵活使用。 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |