通过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 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 ) 自己尝试通过创建一个临时用的表，然后通过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.shinsert 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 offset serveroutput ondeclarea 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; --这是最重要的语句，以每行最后的一个空格为界，把每一行分成两部分，在循环中拼接。beginfor i in tmp_sql loopi.left_part:=a||i.left_part;a:=i.right_part;dbms_output.put_line(i.left_part);end loop;end;/

得到的结果如下：

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