扁平化、可标注的编程语言语法解析结果，目的是让用户更加方便地使用解析结果。
### 举例说明(1)：
# 解析内容(文件：debug .code)：
{ name : '张三', age : 23 }
# 解析配置(文件：test.syn):
__DEF_STR__ __NAME__ <1,100> [1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_?? [0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_?? __DEF_STR__ __INT__ <1,100> [1,100]0123456789 __DEF_SUB_PATH__ __STRING__ 1 : 左引号 | ' : 内容 | __ANY__ : 右引号 | ' __DEF_PATH__ __DICT__ 1 : 左括号 | { N : 键值 | __NAME__ : 冒号 | : : 整数值 | __INT__ : 字符值 + __STRING__ e : 逗号 | , 1 : 右括号 | }
# 解析命令 :
ZGLanguage -e test.syn -c debug.code -o out.zgl > log.log
# 解析结果(文件：out.zgl):
1 P 2 1 __DICT__ 1 左括号 { 2 P 3 bb 1 __DICT__ 1 键值 name 3 P 3 bb 1 __DICT__ 1 冒号 : 4 P 3 bb 2 __STRING__ 1 左引号 ' 5 P 3 bb 2 __STRING__ 1 内容 张三 6 P 3 bb 2 __STRING__ 1 右引号 ' 7 P 2 __STRING__ 4 8 P 3 bb 1 __DICT__ 1 逗号 , 9 P 4 bb 1 __DICT__ 1 键值 age 10 P 4 bb 1 __DICT__ 1 冒号 : 11 P 4 bb 1 __DICT__ 1 整数值 23 12 P 5 1 __DICT__ 1 右括号 } 13 P 1 __DICT__ 1
# 将以上解析结果放到excel表格中展示其字段含义（其中红色一列显示了解析配置的标注信息）：
了解了“扁平化”和“可标注”的含义后，接下来，我们解析一个稍微复杂一点的代码。
### 举例说明(2)：
# 解析内容（一段简单的SQL代码）：
CREATE TABLE TMP_O_SS_CL_1 AS SELECT CL.OBJECTNO , PP.PAYDATE FROM NYBDP.O_SS_CL_LOAN_ACCT_STATIS CL LEFT JOIN (SELECT AL.PAYDATE , BC.BUSS_NO FROM O_CWWS_ACCT_LOAN AL INNER JOIN NYBDP.O_CWWS_BUSINESS_DUEBILL BD ON 1=1 LEFT JOIN O_CWWS_BUSINESS_CONTRACT BC ON 1=1 ) PP ON 1=1 ;
# 解析配置(标识“目标表”和“源表”):
__DEF_FUZZY__ Y __DEF_DEBUG__ N __DEF_CASE_SENSITIVE__ N __DEF_LINE_COMMENT__ -- __DEF_LINES_COMMENT__ /* */ __DEF_PATH__ __CREATE_TABLE_SELECT__ 1 : x1 @ | create : x2 @ | table 1 : 目标表 @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : 目标表_2 @ | __NAME__ 1 : x3 @ | as : x4 @ | __SELECT__ : x5 @ | ; __DEF_SUB_PATH__ __SELECT__ 1 : x1 @ | select N : x2 @ | __OTH_COL__ e : x3 @ | , 1 : x4 @ | from : x5 @ | __TABLE_NAME__ : x5s @ + __SUB_SELECT__ N : x6 @ | __JOIN_TABLE__ __DEF_SUB_PATH__ __OTH_COL__ 1 : x1 | __NAME__ : x2 | . : x3 | __NAME__ __DEF_SUB_PATH__ __TABLE_NAME__ 1 : 源表 @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : 源表_2 @ | __NAME__ 1 : as @ CAN_SKIP | as : 源表别名 @ CAN_SKIP | __NAME__ __DEF_SUB_PATH__ __SUB_SELECT__ 1 : x1 | ( : x2 | __SELECT__ : x3 | ) : x4 @ CAN_SKIP | as : x5 @ CAN_SKIP | __NAME__ __DEF_SUB_PATH__ __JOIN_TABLE__ 1 : x0 @ | join + : x1 @ | inner : x2 @ | join + : x5 @ | left : x6 @ CAN_SKIP | out : x7 @ | join + : x12 @ | right : x13 @ CAN_SKIP | out : x14 @ | join + : x17 @ | full : x18 @ CAN_SKIP | out : x19 @ | join + : x20 @ | , 1 : x21 @ | __TABLE_NAME__ : x2s @ + __SUB_SELECT__ : x22 @ | on : x23 @ | 1=1 __DEF_STR__ __NAME__ <1,100> [1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_?? [0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_?? [NO] select inner left join on from where group order by having union all with as table
# 将解析结果(导入Excel)如下所示：
根据以上红色标注部分，我们可以提取出“源表”和“目标表”。
# 我们可以编写程序(Python代码下面会给出)，对以上解析结果做进一步转换成 echarts图形化 所需的树形结构信息 ：
[ { "name": "debug.code", "children": [ { "name": "TMP_O_SS_CL_1", "children": [ { "name": "O_SS_CL_LOAN_ACCT_STATIS", "children": [] }, { "name": "__SUB_SELECT_11__", "children": [ { "name": "O_CWWS_ACCT_LOAN", "children": [] }, { "name": "O_CWWS_BUSINESS_DUEBILL", "children": [] }, { "name": "O_CWWS_BUSINESS_CONTRACT", "children": [] } ] } ] } ] } ]
# 然后使用echarts进行展示：
以上我们得到了这段代码的结构图。
以此类推，我们扩展 解析配置，让其可以解析更多SQL语句，比如 insert，update，merge，union 等，让其可以对一个较为复杂的SQL脚本进行解析。
### 举例说明(3)：
# 先展示一个较为复杂的存储过程的解析结构图：
# 结构图对应的 存储过程代码 (源代码过长，已做部分删除) 如下：
CREATE OR REPLACE PROCEDURE PROC_F_CWWS_LOAN ( P_AS_OF_DATE IN DATE, RET_FLG OUT VARCHAR2, RET_MSG OUT VARCHAR2 ) IS -- 声明变量并初始化 V_COUNT NUMBER := 0; V_PROC_NAME VARCHAR2(200) := 'PROC_F_CWWS_LOAN'; V_PROC_DESC VARCHAR2(100) := 'xxxx业务数据ETL处理'; V_P_FREQ VARCHAR2(4) := ''; BEGIN --写入初始日志 INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 1'); COMMIT; --设置会话日期格式 EXECUTE IMMEDIATE ' ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD'''; --查询参数表中，该程序对应的频率值 SELECT P_FREQ INTO V_P_FREQ FROM ETL_PROC_STATUS_DEF WHERE PROC_NAME = V_PROC_NAME; --判断是调度频率 IF P_AS_OF_DATE = FUNC_GET_FREQ_DAYS(P_AS_OF_DATE, V_P_FREQ) THEN --调用分区维护程序 ETL.ETL_ADD_PARTITION('MA_F_LOAN', P_AS_OF_DATE, 'ETL'); --删除取上下次支付日临时表 DELETE TMP_XD_LAST_PAYDATE; COMMIT; DELETE TMP_XD_NEXT_PAYDATE; COMMIT; --从还款计划表中取每笔账户最近一次小于等于数据日期还款日，作为上次还款日 INSERT INTO ETL.TMP_XD_LAST_PAYDATE (OBJECTNO, LAST_PAYDATE) SELECT OBJECTNO, LAST_PAYDATE FROM (SELECT T.OBJECTNO, MAX(TO_DATE(PAYDATE, 'YYYY-MM-DD')) LAST_PAYDATE FROM NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE T WHERE T.AS_OF_DATE = P_AS_OF_DATE AND T.SEQID <> '999' AND TO_DATE(T.PAYDATE, 'YYYY-MM-DD') < P_AS_OF_DATE GROUP BY T.OBJECTNO); INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 3'); COMMIT; --从还款计划表中取每笔账户最近一次大于数据日期还款日，作为下次还款日 INSERT INTO ETL.TMP_XD_NEXT_PAYDATE (OBJECTNO, NEXT_PAYDATE) SELECT OBJECTNO, NEXT_PAYDATE FROM (SELECT T.OBJECTNO, MIN(TO_DATE(PAYDATE, 'YYYY-MM-DD')) NEXT_PAYDATE FROM NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE T WHERE T.AS_OF_DATE = P_AS_OF_DATE AND T.SEQID <> '999' AND TO_DATE(T.PAYDATE, 'YYYY-MM-DD') >= P_AS_OF_DATE GROUP BY T.OBJECTNO); INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 4'); COMMIT; --xxxx业务数据ETL处理 INSERT INTO MA_F_LOAN SELECT CASE WHEN CL.LOAN_STATUS = '0' AND CL.DUE_BALANCE <> 0 THEN 'AK-' || AL.ACCOUNTNO || 'YQ' ELSE AL.ACCOUNTNO END ACCOUNT_NUMBER, --20180319 -- AL.ACCOUNTNO ACCOUNT_NUMBER, --账号 CL.DRAW_NO ACCOUNT_ID, --账号 P_AS_OF_DATE AS_OF_DATE, --数据日期 CL.LOAN_AMT LRD_BALANCE, --上次重定价日余额 NVL(SL.INT_ADJUSTMENT, 0) INT_ADJUSTMENT, --利息调整 TO_DATE(AL.PUTOUTDATE, 'YYYY/MM/DD') ACCOUNT_OPEN_DATE, --开户日期 NVL(CI.MFCUSTOMERID, CI.CUSTOMERID) CIF_KEY, --客户号 CI.CUSTOMERTYPE CIF_TYPE, --客户类型 B.ACCRUED_INTEREST ACCRUED_INTEREST, --利息收入 'LOAN_CWWS_ACCT_LOAN' DATA_SOURCE, --数据来源 0 INT_IE, --实收利息(账户的实际收息、付息)（累计值） CL.CLIENT_INDUSTRY INDUSTRYTYPE, --行业 CL.CMIS_FIVE_CLASS CLASSIFY, --五级分类 CI.C_EVALUATE C_EVALUATE, --信用等级 AL.VOUCHTYPE VOUCHTYPE, --担保方式 BC.MANAGEUSERID ACCOUNT_MGR, --客户经理 NULL CHANNEL, --渠道 NVL(SL.PRVN_AMT, 0) PRVN_AMT, --减值准备金额 CASE WHEN BC.USEORGLIST = '1' THEN '1' ELSE '2' END BUSINESS_DEPARTMENT, --员工贷标识 1-是 2-否 modify by 盛夏冰 NULL INTEREST_SUM_M, --应计利息区间累积值(月） NULL INTEREST_SUM_Y, --应计利息区间累积值（年） CL.CORP_SIZE MINENT_FLAG, --企业规模 NULL GUARD_BAL, --担保金额 NULL GUARD_BAL_PART, --分户承担的担保金额 C.PRVN_GL_ACCOUNT_ID PRVN_GL_ACCOUNT_ID, --减值损失准备科目 AL.REPRICETYPE REPRICE_TYPE FROM NYBDP.O_SS_CL_LOAN_ACCT_STATIS CL --xxxx账户状态表 LEFT JOIN NYBDP.O_CWWS_ACCT_LOAN AL --xxxx账户统计表 ON (CL.DRAW_NO = AL.SERIALNO AND P_AS_OF_DATE = AL.AS_OF_DATE) LEFT JOIN NYBDP.O_CWWS_BUSINESS_DUEBILL BD --借据表 ON (AL.SERIALNO = BD.SERIALNO AND P_AS_OF_DATE = BD.AS_OF_DATE) LEFT JOIN NYBDP.O_CWWS_BUSINESS_CONTRACT BC --合同表 ON (BD.RELATIVESERIALNO2 = BC.SERIALNO AND BC.AS_OF_DATE = P_AS_OF_DATE) --EDIT BY MSW20160302 -- ON (BD.RELATIVESERIALNO2=BC.SERIALNO AND BD.AS_OF_DATE=BC.AS_OF_DATE) LEFT JOIN NYBDP.O_CWWS_BUSINESS_PUTOUT BP --业务出账表 ON (BD.RELATIVESERIALNO1 = BP.SERIALNO AND BP.AS_OF_DATE = P_AS_OF_DATE) --EDIT BY MSW20160302 -- ON (BD.RELATIVESERIALNO1=BP.SERIALNO AND BD.AS_OF_DATE=BP.AS_OF_DATE) --关联客户信息表，取客户信息 LEFT JOIN (SELECT T6.CUSTOMERID, T6.MFCUSTOMERID, T6.CUSTOMERTYPE, T7.CREDITLEVEL C_EVALUATE FROM (SELECT DISTINCT CUSTOMERID, CUSTOMERTYPE, MFCUSTOMERID FROM NYBDP.O_CWWS_CUSTOMER_INFO WHERE AS_OF_DATE = P_AS_OF_DATE) T6 LEFT JOIN (SELECT CUSTOMERID, CREDITLEVEL, OTHERCREDITLEVEL, EVALUATELEVEL, INDUSTRYTYPE FROM NYBDP.O_CWWS_ENT_INFO WHERE AS_OF_DATE = P_AS_OF_DATE) T7 ON T6.CUSTOMERID = T7.CUSTOMERID) CI ON AL.CUSTOMERID = CI.CUSTOMERID LEFT JOIN --取减值准备金额和利息调整金额 (SELECT ACCOUNTNO, SUM(CASE WHEN B.SUB_NAME LIKE '%准备%' THEN DECODE(DIRECTION, 'D', DEBITBALANCE - CREDITBALANCE, CREDITBALANCE - DEBITBALANCE) ELSE 0 END) PRVN_AMT, SUM(CASE WHEN B.SUB_NAME LIKE '%利息调整%' THEN DECODE(DIRECTION, 'D', DEBITBALANCE - CREDITBALANCE, CREDITBALANCE - DEBITBALANCE) ELSE 0 END) INT_ADJUSTMENT, SUM(CASE WHEN B.SUB_NAME LIKE '%利息收入%' THEN DECODE(DIRECTION, 'D', DEBITBALANCE - CREDITBALANCE, CREDITBALANCE - DEBITBALANCE) ELSE 0 END) ACCRUED_INTEREST FROM ETL.O_CWWS_ACCT_SUBSIDIARY_LEDGER A, (SELECT DISTINCT SUB_CODE, SUB_NAME FROM ETL.O_CAS_COMC_SUBJECT WHERE AS_OF_DATE = P_AS_OF_DATE) B WHERE A.CORESUBJECTNO = B.SUB_CODE AND DECODE(DIRECTION, 'D', DEBITBALANCE - CREDITBALANCE, CREDITBALANCE - DEBITBALANCE) <> 0 AND A.AS_OF_DATE = P_AS_OF_DATE GROUP BY A.ACCOUNTNO) SL ON AL.ACCOUNTNO = SL.ACCOUNTNO LEFT JOIN NYBDP.O_CAS_COMC_CURR CC --币种表 ON (CL.CCY = CC.CUR_CODE AND P_AS_OF_DATE = CC.AS_OF_DATE) LEFT JOIN ETL.TMP_XD_LAST_PAYDATE LP --上次支付日临时表 ON CL.DRAW_NO = LP.OBJECTNO LEFT JOIN ETL.TMP_XD_NEXT_PAYDATE NP --下次支付日临时表 ON CL.DRAW_NO = NP.OBJECTNO LEFT JOIN --关联还款计划表，取下次支付本金和下次支付利息 (SELECT A.OBJECTNO, A.PAYCORPUSAMT, A.PAYINTEAMT FROM NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE A WHERE A.AS_OF_DATE = P_AS_OF_DATE AND A.SEQID <> '999' AND (A.OBJECTNO, TO_DATE(A.PAYDATE, 'YYYY/MM/DD')) IN (SELECT B.OBJECTNO, B.NEXT_PAYDATE FROM ETL.TMP_XD_NEXT_PAYDATE B) AND A.SDATE <= TO_CHAR(P_AS_OF_DATE, 'YYYYMMDD') --add by msw 2016-03-14 AND A.EDATE >= TO_CHAR(P_AS_OF_DATE, 'YYYYMMDD') --add by msw 2016-03-14 ) T ON CL.DRAW_NO = T.OBJECTNO LEFT JOIN --取利息收入和利息科目信息 (SELECT /*+PARALLEL(8)*/ T.ACCOUNTNO, T.CORESUBJECTNO INT_SUB_CODE, T1.SUB_NAME INT_SUB_NAME, SUM(CASE WHEN DIRECTION = 'C' THEN T.CREDITAMT WHEN DIRECTION = 'D' THEN T.DEBITAMT --MODIFY BY ZYF 20180124 ELSE 0 END) ACCRUED_INTEREST FROM ETL.O_CWWS_ACCT_SUBLEDGER_DETAIL T JOIN ETL.O_CAS_COMC_SUBJECT T1 ON T.CORESUBJECTNO = T1.SUB_CODE WHERE (T1.SUB_NAME LIKE '%利息收入%') AND T.SENDFLAG = '1' AND T.AS_OF_DATE = P_AS_OF_DATE AND T1.AS_OF_DATE = P_AS_OF_DATE GROUP BY T.ACCOUNTNO, T.CORESUBJECTNO, T1.SUB_NAME) B ON AL.ACCOUNTNO = B.ACCOUNTNO LEFT JOIN --取减值损失准备科目 (SELECT T.ACCOUNTNO, T.CORESUBJECTNO PRVN_GL_ACCOUNT_ID, T.SUB_NAME, T.OBJECTNO FROM (SELECT SL.ACCOUNTNO, SL.SERIALNO, SL.CORESUBJECTNO, SL.OBJECTNO, SUB.SUB_NAME, RANK() OVER(PARTITION BY SL.ACCOUNTNO ORDER BY SL.SERIALNO DESC) SEQ FROM ETL.O_CWWS_ACCT_SUBSIDIARY_LEDGER SL JOIN ETL.O_CAS_COMC_SUBJECT SUB ON SL.CORESUBJECTNO = SUB.SUB_CODE WHERE SUB.SUB_NAME LIKE '%准备%' AND SUB.AS_OF_DATE = P_AS_OF_DATE AND SL.AS_OF_DATE = P_AS_OF_DATE) T WHERE T.SEQ = 1) C ON CL.DRAW_NO = C.ACCOUNTNO /*WHERE CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010') --xxxx类型 AND CL.AGG_YTD <> 0 AND CL.AS_OF_DATE = P_AS_OF_DATE AND AL.ACCOUNTNO IS NOT NULL;*/ WHERE (CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010') --xxxx类型 AND CL.AGG_YTD <> 0 AND CL.AS_OF_DATE = P_AS_OF_DATE AND AL.ACCOUNTNO IS NOT NULL) OR (CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010') --xxxx类型 AND CL.AGG_YTD = 0 AND CL.AS_OF_DATE = P_AS_OF_DATE AND AL.ACCOUNTNO IS NOT NULL AND (CL.LOAN_BALANCE <> 0 OR B.ACCRUED_INTEREST <> 0)); --将原来因为年积数为0导致过滤掉的提前还款的数据在不影响原来数据的情况下重新获取 --所以将原来的查询结果过滤条件改为其中一种情况，另一种情况为年积数为0， --但当天余额或者当天发生额不为0的请况，两种情况只要符合其中一种就取数据插入表 --WZG 20170622 INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 6'); COMMIT; MERGE INTO ETL.MA_F_LOAN A USING (SELECT /*+PARALLEL(8)*/ T.ACCOUNT_NUMBER, T.GL_ACCOUNT_ID, T.INT_GL_ACCOUNT_ID FROM ETL.MA_F_LOAN T INNER JOIN ETL.MA_D_GL_SUBJECT T1 ON T.INT_GL_ACCOUNT_ID = T1.SUBJECT_NO3 AND T1.SUBJECT_NAME3 LIKE '%已减值%' AND T1.AS_OF_DATE = P_AS_OF_DATE WHERE T.AS_OF_DATE = P_AS_OF_DATE AND T.ACCOUNT_NUMBER IN (SELECT ACCOUNT_NUMBER FROM (SELECT /*+PARALLEL(8)*/ T2.ACCOUNT_NUMBER, COUNT(1) FROM ETL.MA_F_LOAN T2 WHERE T2.AS_OF_DATE = P_AS_OF_DATE GROUP BY T2.ACCOUNT_NUMBER HAVING COUNT(1) > 1))) B ON (A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER AND A.AS_OF_DATE = P_AS_OF_DATE AND A.GL_ACCOUNT_ID = B.GL_ACCOUNT_ID AND A.INT_GL_ACCOUNT_ID = B.INT_GL_ACCOUNT_ID) WHEN MATCHED THEN UPDATE SET A.CUR_BOOK_BAL = 0, A.OVERDUE_BAL = 0; COMMIT; --信贷系统12月14日上线新逻辑，导致原核销xxxx数据余额为0的上线后余额不再为0，需要更新核销xxxx数据的特定金额字段值更新为0。 WZG 20171220 UPDATE MA_F_LOAN T SET T.CUR_BOOK_BAL = 0, T.SUM_BOOK_BAL_M = 0, T.AVG_BOOK_BAL_M = 0 WHERE T.AS_OF_DATE = P_AS_OF_DATE AND EXISTS (SELECT 1 FROM NYBDP.O_CWWS_BUSINESS_DUEBILL BD WHERE T.ACCOUNT_ID = BD.SERIALNO AND BD.AS_OF_DATE = T.AS_OF_DATE AND BD.BUSINESSSTATUS = '6'); INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 6.2'); COMMIT; --更新逾期xxxx上下次重订价日及重订价频率为起息日、到期日 UPDATE MA_F_LOAN A SET LAST_REPRICE_DATE = A.ORIGINATION_DATE, NEXT_REPRICE_DATE = A.MATURITY_DATE, REPRICE_FREQ = A.ORG_TERM, REPRICE_FREQ_MULT = A.ORG_TERM_MULT, ADJUSTABLE_TYPE_CD = 0 WHERE A.MATURITY_DATE <= P_AS_OF_DATE AND A.CUR_BOOK_BAL <> 0; /*AND A.ADJUSTABLE_TYPE_CD = '250'*/ INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 7'); --资产腾挪数据 UPDATE MA_F_LOAN SET ACCOUNT_ID = ACCOUNT_ID || 'A' WHERE AS_OF_DATE = P_AS_OF_DATE AND GL_ACCOUNT_ID IN ('9990101', '9990102', '9990103', '9990104'); --增加9990103科目，所以新增一个条件 WZG 20170427 --增加9990104科目，所以新增一个条件 WZG 20170630 INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 8'); COMMIT; --更新资产腾挪xxxx为固定利率xxxx,计息方式为act/360 UPDATE MA_F_LOAN T SET T.LAST_REPRICE_DATE = T.ORIGINATION_DATE, T.NEXT_REPRICE_DATE = T.MATURITY_DATE, T.ORG_TERM = T.MATURITY_DATE - T.ORIGINATION_DATE, T.REPRICE_FREQ = T.MATURITY_DATE - T.ORIGINATION_DATE, T.ADJUSTABLE_TYPE_CD = '0', T.INT_GL_ACCOUNT_ID = '972301', T.ACCRUED_INTEREST = ROUND(T.CUR_BOOK_BAL * T.CUR_NET_RATE / 360 / 100.00, 2) WHERE T.AS_OF_DATE = P_AS_OF_DATE AND T.GL_ACCOUNT_ID = '9990101'; INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 9'); COMMIT; END IF; INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 10'); COMMIT; RET_FLG := '0'; RET_MSG := '执行成功'; EXCEPTION WHEN OTHERS THEN --写入异常日志 ETL.PROC_ETL_LOG(P_AS_OF_DATE,V_PROC_NAME,V_PROC_DESC,V_COUNT,-1,SQLCODE,SQLERRM); RET_FLG := '1'; RET_MSG := SQLCODE || ':' || SQLERRM; END; /
# 解析配置 如下：
__DEF_FUZZY__ Y __DEF_DEBUG__ N __DEF_CASE_SENSITIVE__ N __DEF_LINE_COMMENT__ -- __DEF_LINES_COMMENT__ /* */ __DEF_STR__ __IF_KW__ <1,100> [1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz [0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_ __DEF_PATH__ __CREATE_TABLE_SELECT__ 1 : x1 @ | create : temp @ CAN_SKIP | temp : x2 @ | table 1 : 目标表 @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : 目标表_2 @ | __NAME__ 1 : x3 @ | as : with @ CAN_SKIP | __WITH_AS_SELECT__ : x4 @ | __SELECT__ N : x5 @ CAN_SKIP | __UNION_SELECT__ 1 : x6 @ | ; __DEF_PATH__ __CREATE_VIEW__ 1 : x1 @ | create : x2 @ | view 1 : 目标表 @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : 目标表_2 @ | __NAME__ 1 : x3 @ | as : with @ CAN_SKIP | __WITH_AS_SELECT__ : x4 @ | __SELECT__ N : x5 @ CAN_SKIP | __UNION_SELECT__ 1 : x6 @ | ; __DEF_PATH__ __INSERT_INTO_SELECT__ 11 : x1 @ | insert : x2 @ | into 11 : 目标表 @ | __NAME__ ++ : schema @ | __NAME__ : PP @ | . : 目标表_2 @ | __NAME__ 01 : s1 @ | ( 0N : tar_col @ | __NAME__ 0e : tar_col_spl @ | , 01 : s4 @ | ) 11 : with @ CAN_SKIP | __WITH_AS_SELECT__ 1 : n1 @ | __SELECT__ NN : n2 @ CAN_SKIP | __UNION_SELECT__ 11 : x6 @ | ; __DEF_PATH__ __INSERT_INTO_VALUES__ 1 : x1 @ | insert : x2 @ | into 1 : 目标表 @ | __NAME__ + : schema @ | __NAME__ : PP @ | . : 目标表_2 @ | __NAME_ 1 : values @ | values : x5 @ | __SUB_PATH_4_EXPR__ 1 : x6 @ | ; __DEF_PATH__ __JUST_SELECT__ 01 : with @ | __WITH_AS_SELECT__ 1 : x1 @ | __SELECT__ NN : x2 @ CAN_SKIP | __UNION_SELECT__ 11 : x3 @ | ; __DEF_PATH__ __UPDATE_TABLE__ 11 : x1 @ | update 11 : 目标表 @ | __NAME__ ++ : schema @ | __NAME__ : pp @ | . : 目标表_2 @ | __NAME__ 11 : as @ CAN_SKIP | as : other_name @ CAN_SKIP | __NAME__ : set @ | set NN : x5 @ | __PATH_4_EXPR__ ee : x6 @ | , 01 : x7 @ | from 0 : x8 @ | __TABLE_NAME__ : x9 @ + __SUB_SELECT__ 0N : xa @ CAN_SKIP | __JOIN_TABLE__ 11 : xb @ CAN_SKIP | __WHERE__ 0 : xc @ CAN_SKIP | __GROUP_BY__ 1 : xfh @ | ; __DEF_PATH__ __MERGE_TABLE__ 1 : x1 @ | merge : x2 @ | into 1 : 目标表 @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : 目标表_2 @ | __NAME__ 1 : as @ CAN_SKIP | as : other_name @ CAN_SKIP | __NAME__ : using @ | using : x8 @ | __TABLE_NAME__ : x9 @ + __SUB_SELECT__ : on1 @ | on : on2 @ | ( : on3 @ | __PATH_4_EXPR__ : on4 @ | ) : mu @ CAN_SKIP | __MERGE_UPDATE__ : mi @ CAN_SKIP | __MERGE_INSERT__ : xfh @ | ; __//__ ================================================================= __DEF_SUB_PATH__ __MERGE_UPDATE__ 1 : when @ | when : matched @ | matched : then @ | then : update @ | update : set @ | set N : x5 @ | __PATH_4_EXPR__ e : x6 @ | , __DEF_SUB_PATH__ __MERGE_INSERT__ 1 : when @ | when : not @ | not : matched @ | matched : then @ | then : insert @ | insert : set @ | values : x5 @ | __SUB_PATH_4_EXPR__ __DEF_SUB_PATH__ __WITH_AS_SELECT__ 1 : x1 @ | __FIRST_WITH_AS_SELECT__ N : x2 @ CAN_SKIP | __NEXT_WITH_AS_SELECT__ __DEF_SUB_PATH__ __FIRST_WITH_AS_SELECT__ 1 : x1 @ | with : 目标表 @ | __NAME__ : x3 @ | as : x4 @ | ( : x5 @ | __SELECT__ N : x6 @ CAN_SKIP | __UNION_SELECT__ 1 : with_end @ | ) __DEF_SUB_PATH__ __NEXT_WITH_AS_SELECT__ 1 : x1 @ | , : 目标表 @ | __NAME__ : x3 @ | as : x4 @ | ( : x5 @ | __SELECT__ N : x6 @ CAN_SKIP | __UNION_SELECT__ 1 : with_end @ | ) __DEF_SUB_PATH__ __UNION_SELECT__ 01 : union @ %__IF_KW__ | union 0 : x2 @ CAN_SKIP | all 0 : x3 @ | __SELECT__ 10 : x4 @ | ( 10 : x5 @ | __UNION_SELECT__ 10 : x6 @ | ) __DEF_SUB_PATH__ __SUB_SELECT__ 1 : x1 @ | ( : with @ CAN_SKIP | __WITH_AS_SELECT__ : x2 @ | __SELECT__ N : xU @ CAN_SKIP | __UNION_SELECT__ 1 : x3 @ | ) : x4 @ CAN_SKIP | as : 子查询别名 @ CAN_SKIP | __NAME__ __DEF_SUB_PATH__ __VALUE_SELECT__ 1 : x1 @ | ( : x2 @ | __SELECT__ : x3 @ | ) __DEF_SUB_PATH__ __SELECT__ 01 : x1 @ | select 0 : x2 @ CAN_SKIP | distinct 0N : x3 @ | __ONE_COL_EXPR__ 0 : as @ CAN_SKIP | as 0 : 字段别名 @ CAN_SKIP | __NAME__ 0e : 字段分割 @ | , 01 : x7 @ | from 0 : x8 @ | __TABLE_NAME__ : x9 @ + __SUB_SELECT__ 0N : xa @ CAN_SKIP | __JOIN_TABLE__ 01 : xb @ CAN_SKIP | __WHERE__ 0 : xc @ CAN_SKIP | __GROUP_BY__ 0 : xd @ CAN_SKIP | __ORDER_BY__ 0 : xe @ CAN_SKIP | __DISTRIBUTED_BY__ 10 : y1 @ | ( 0 : y2 @ | __SELECT__ 0 : y3 @ | ) __DEF_SUB_PATH__ __TABLE_NAME__ 1 : 源表 @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : 源表_2 @ | __NAME__ 1 : as @ CAN_SKIP | as : 源表别名 @ CAN_SKIP | __NAME__ __DEF_SUB_PATH__ __JOIN_TABLE__ 11 : x0 @ | join ++ : x1 @ | inner : x2 @ | join ++ : x5 @ | left : x6 @ CAN_SKIP | out : x7 @ | join ++ : x12 @ | right : x13 @ CAN_SKIP | out : x14 @ | join ++ : x17 @ | full : x18 @ CAN_SKIP | out : x19 @ | join ++ : x20 @ | , 11 : x21 @ | __TABLE_NAME__ : x2s @ + __SUB_SELECT__ 0 : x22 @ | on 0 : x23 @ | __PATH_4_EXPR__ __DEF_SUB_PATH__ __WHERE__ 1 : x1 | where N : x2 | __PATH_4_EXPR__ : x3 + __EXISTS_SELECT__ __DEF_SUB_PATH__ __GROUP_BY__ 11 : x11 | group : x12 | by NN : x13 | __PATH_4_EXPR__ ee : x14 | , 01 : x15 | having 0 : x16 | __PATH_4_EXPR__ __DEF_SUB_PATH__ __ORDER_BY__ 1 : x11 @ | order : x12 @ | by N : x13 @ | __PATH_4_EXPR__ : x14 @ CAN_SKIP | desc : x15 @ + asc e : x16 @ | , __DEF_SUB_PATH__ __PARTITION_BY__ 1 : x11 | partition : x12 | by N : x13 | __PATH_4_EXPR__ e : x16 | , __DEF_SUB_PATH__ __DISTRIBUTED_BY__ 1 : x11 | distributed : x12 | by : n12 | ( N : x13 | __PATH_4_EXPR__ e : x16 | , 1 : x17 | ) __DEF_SUB_PATH__ __EXISTS_SELECT__ 01 : x1 | not 1 : x2 | exists : x3 | ( : x4 | __SELECT__ : x5 | ) __//__ ===================================================================== __DEF_SUB_PATH__ __ONE_COL_EXPR__ 1 : x1 | __PATH_4_EXPR__ __//__ ===================================================================== __DEF_SUB_PATH__ __SUB_PATH_4_EXPR__ 1 : x1 | ( N : x2 | __PATH_4_EXPR__ e : x3 | , 1 : x4 | ) __DEF_SUB_PATH__ __PATH_4_EXPR__ N : x1 @ | __NAME__ : x2 @ + __INT__ : x3 @ + __FLOAT__ : x4 @ + __CASE_WHEN__ : x5 @ + __STRING__ : x6 @ + __CAST_AS__ : x8 @ + __SUB_PATH_4_EXPR__ : VS @ + __VALUE_SELECT__ : xa @ + __FUNCTION__ : xon @ + __OTH_NAME__ : x7 @ + __OTH_FUN__ : x9 @ + = : x10 @ + <> : x11 @ + != : x12 @ + > : x13 @ + >= : x14 @ + < : x15 @ + <= : x18 @ + + : x19 @ + - : x21 @ + * : x22 @ + / : x23 @ + || : x25 @ %__IF_KW__ + between : x26 @ %__IF_KW__ + and : x27 @ %__IF_KW__ + or : x28 @ %__IF_KW__ + like : x29 @ %__IF_KW__ + in : x30 @ %__IF_KW__ + is : x31 @ %__IF_KW__ + not : x32 @ %__IF_KW__ + null __DEF_SUB_PATH__ __OTH_NAME__ 1 : other_name @ | __NAME__ : pp @ | . : column @ | __NAME__ : all_col @ + * __DEF_SUB_PATH__ __OTH_FUN__ N : schema | __NAME__ : pp | . 1 : fun | __FUNCTION__ __DEF_SUB_PATH__ __FUNCTION__ 11 : 函数名 @ | __NAME__ : x2 @ | ( : y2 @ CAN_SKIP | distinct NN : x3 @ CAN_SKIP | __PATH_4_EXPR__ ee : x4 @ CAN_SKIP | , 11 : x5 @ | ) 0 : v1 @ | over 0 : v2 @ | ( 0 : v3 @ CAN_SKIP | __PARTITION_BY__ 0 : v4 @ CAN_SKIP | __ORDER_BY__ 0 : v5 @ | ) __DEF_SUB_PATH__ __CAST_AS__ 1 : x1 @ | cast : x2 @ | ( 1 : x3 @ | __PATH_4_EXPR__ : x4 @ | as : x5 @ | date : x6 @ + int : n1 @ + double : n2 @ + float : n3 @ + bigint : x7 @ + __X_CHAR__ : x8 @ + __DECIMAL__ 1 : xx @ | ) __DEF_SUB_PATH__ __CASE_WHEN__ 1 : x1 @ | case N : x2 @ | when : x3 @ | __PATH_4_EXPR__ : x4 @ | then : x5 @ | __PATH_4_EXPR__ 1 : x6 @ CAN_SKIP | else : x7 @ CAN_SKIP | __PATH_4_EXPR__ : x8 @ | end __/*__ ------------------------------ __DEF_SUB_PATH__ __SET__ 1 : x1 @ | ( N : x2 @ | __STRING__ : x3 @ + __INT__ : x4 @ + __FLOAT__ e : x5 @ | , 1 : x6 @ | ) --------------------------- __*/__ __DEF_SUB_PATH__ __DECIMAL__ 111 : x1 | decimal 0 : x2 | ( 01 : x3 | __INT__ 00 : x4 | , 00 : x5 | __INT__ 01 : x6 | ) __DEF_SUB_PATH__ __X_CHAR__ 11 : x1 | varchar : n1 + nvarchar : n2 + char : n3 + varchar2 : n4 + nvarchar2 0 : x2 | ( 0 : x3 | __INT__ 0 : x6 | ) __DEF_SUB_PATH__ __VAR_NAME__ 1 : x1 | $ : x2 | { : x3 | __NAME__ : x4 | } __DEF_STR__ __NAME__ <1,100> [1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_?? [0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_?? [NO] select inner left join on from where group order partition by having union all with as table set between and or like in is not null __DEF_STR__ __FLOAT__ <1,100> [1,50]0123456789 [1,1]. [1,50]0123456789 __DEF_STR__ __INT__ <1,100> [1,100]0123456789 __DEF_SUB_PATH__ __STRING__ 1 : x1 | ' : x2 | __ANY__ : x3 | '
# 解析结果转换Python代码 如下 (draw_sql_struct.py)：
# -*- coding: utf-8 -*- import sys import subprocess import json def go2OneLevel(fs, curr_level_node, one_row) : # one_row = fs.readline().strip().split('\t') while one_row[0] : # 层级结束判断 if not one_row[2] and one_row[4] == curr_level_node['level'] : return # print('2222=', one_row) if one_row[5] not in ( '__CREATE_TABLE_SELECT__', '__CREATE_VIEW__', '__INSERT_INTO_SELECT__' , '__UPDATE_TABLE__', '__MERGE_TABLE__' , '__JUST_SELECT__' , '__FIRST_WITH_AS_SELECT__', '__NEXT_WITH_AS_SELECT__' , '__SUB_SELECT__', '__UNION_SELECT__', '__TABLE_NAME__' ) : one_row = fs.readline().strip().split('\t') continue if not curr_level_node['value'] : if curr_level_node['node_type'] in ('__CREATE_TABLE_SELECT__', '__CREATE_VIEW__', '__INSERT_INTO_SELECT__' , '__UPDATE_TABLE__', '__MERGE_TABLE__', '__FIRST_WITH_AS_SELECT__' , '__NEXT_WITH_AS_SELECT__') and one_row[7] in ('目标表','目标表_2') \ or curr_level_node['node_type'] == '__TABLE_NAME__' and one_row[7] in ('源表','源表_2') : curr_level_node['value'] = one_row[8] elif curr_level_node['node_type'] == '__JUST_SELECT__' and one_row[7] in ('x1') : curr_level_node['value'] = '__JUST_SELECT_' + one_row[4] + '__' elif curr_level_node['node_type'] == '__SUB_SELECT__' and one_row[7] in ('x1') : curr_level_node['value'] = '__SUB_SELECT_' + one_row[4] + '__' elif curr_level_node['node_type'] == '__UNION_SELECT__' and one_row[7] in ('union'): curr_level_node['value'] = '__UNION_SELECT__' + one_row[4] + '__' if curr_level_node['level'] != one_row[4] : new_level_node = {'level':one_row[4], 'node_type':one_row[5], 'value':'', 'children':[]} curr_level_node['children'].append(new_level_node) go2OneLevel(fs, new_level_node, one_row) one_row = fs.readline().strip().split('\t') return def getTableTree(zgl_lvl_info, one_tree) : for one_node in zgl_lvl_info : # print(one_node['value']) new_node = {'name':one_node['value'], 'children':[]} one_tree.append(new_node) getTableTree(one_node['children'], new_node['children']) return if __name__ == "__main__" : if len(sys.argv) == 1 : print('Miss sql file !') sys.exit(-1) run_result = subprocess.run(['ZGLanguage', '-e', 'SQL_DATA_LINEAGE/SQL_DATA_LINEAGE.syn' , '-c', sys.argv[1] , '-o', 'info_4_sql_struct.zgl'] , capture_output=True # , stdout=log_file , encoding='utf-8' , text=True ) # print('111111=', run_result.stdout) with open("run_zgl.log", "w") as log_file: log_file.write(run_result.stdout) last_log = run_result.stdout[0:500] if ' ERROR !!!' in last_log or ' WARNING !!!' in last_log: print(run_result.stdout[0:500]) sys.exit(-1) ######################################### data_info = [{'level':'0', 'node_type':'', 'value':sys.argv[1], 'children':[]}] with open('./info_4_sql_struct.zgl', 'r', encoding='utf-8') as fs : one_row = fs.readline().strip().split('\t') while one_row[0] : # print('1111=', one_row) if one_row[5] not in ( '__CREATE_TABLE_SELECT__', '__CREATE_VIEW__' , '__INSERT_INTO_SELECT__', '__JUST_SELECT__' , '__UPDATE_TABLE__', '__MERGE_TABLE__' ) : one_row = fs.readline().strip().split('\t') continue new_level_node = {'level':one_row[4], 'node_type':one_row[5], 'value':'', 'children':[]} # print(new_level_node) go2OneLevel(fs, new_level_node, one_row) data_info[0]['children'].append(new_level_node) one_row = fs.readline().strip().split('\t') # print(data_info) ################################################ # 加工提取 echarts 图数据 tree_date = [] getTableTree(data_info, tree_date) print(tree_date) tree_str = json.dumps(tree_date, indent=4, ensure_ascii=False) # print(tree_str) html = """ <html> <head> <meta charset="UTF-8"> <title>SQL结构图</title> <style> </style> <script type="text/javascript" src="https://fastly.jsdelivr.net/npm/echarts@5/dist/echarts.min.js"></script> </head> <body> <!-- 为ECharts准备一个具备大小（宽高）的Dom --> <div style="padding:20px;width:100%;height:100%;"> <div id="main" style="width: 1300px;height:1464px;"> </div> </div> <script type="text/javascript"> //定义echarts容器 var myChart = echarts.init(document.getElementById('main'), 'macarons'); option = { title : { text: 'SQL结构图', <!-- subtext: '树形图' --> }, //鼠标滚动缩放 tooltip: { trigger: 'item', triggerOn: 'mousemove' }, series : [ { type:'tree', initialTreeDepth: 5, // 默认展开层级 orient: 'horizontal', // vertical(竖向显示) horizontal（横向显示） edgeShape: "polyline", //修改为折线, rootLocation: {x: 100, y: '80%'}, // 根节点位置 {x: 'center',y: 10} nodePadding: 20, symbol: 'circle', symbolSize: 10, left: '8%', right: '15%', top: '15%', bottom: '20%', expandAndCollapse: true, roam:true, itemStyle: { normal: { label: { show: true, position: 'top', textStyle: { color: 'black', fontSize: 12, // fontWeight: 'bolder', borderColor: '#06c', padding: [0, 0, 0, 0], // 上、右、下、左的行间距 } /*, formatter: function (params) { let name = params.name; let children = params.data.children; let childrenCount = children ? children.length : 0; return name + '(' + childrenCount + ')'; }*/ }, //连接线样式 lineStyle: { color: 'green', width: 0.5, curveness:0.6, type: 'broken' // 'curve'|'broken'|'solid'|'dotted'|'dashed' } }, emphasis: { label: { show: true } } }, data: """ + tree_str + """ } ] }; myChart.setOption(option); </script> </body> </html> """ # print(html) with open('sql_struct_map_echarts.html', 'w', encoding='utf-8') as fs : fs.write(html)
# Python代码 运行说明：
1、 部署 ： Python3 和 ZGLanguage
2、 执行命令： python draw_sql_struct.py test.code
3、 得到结果 ： sql_struct_map_echarts.html ，使用浏览器打开(需要联网下载js文件)
