源代码： 基于扁平化、可标注的编程语言语法解析结果的应用实例(SQL代码结构图/表级血缘分析)
原创
源代码： 基于扁平化、可标注的编程语言语法解析结果的应用实例(SQL代码结构图/表级血缘分析)
原创
用户12032828
发布于 2026-03-07 16:38:39
发布于 2026-03-07 16:38:39
扁平化、可标注的编程语言语法解析结果，目的是让用户更加方便地使用解析结果。
### 举例说明(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)如下所示：
SQL解析
根据以上红色标注部分，我们可以提取出“源表”和“目标表”。
# 我们可以编写程序(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结构图
以上我们得到了这段代码的结构图。
以此类推，我们扩展 解析配置，让其可以解析更多SQL语句，比如 insert，update，merge，union 等，让其可以对一个较为复杂的SQL脚本进行解析。
### 举例说明(3)：
# 先展示一个较为复杂的存储过程的解析结构图：
SQL脚本结构图
# 结构图对应的 存储过程代码 (源代码过长，已做部分删除) 如下：
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文件)
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号