### 背景：
1、 如今关系型数据库种类繁多，虽都使用SQL语言进行操作，但各数据库之间的SQL依然存在一些语法差异
2、 企业的ETL作业大量使用SQL脚本，脚本的运行往往存在依赖关系，而脚本间依赖关系往往需要人工识别
3、 管理或运维人员在分析SQL脚本时，只能用文本编辑器打开整个SQL脚本文件，在杂乱的屎山代码中分析问题
4、 一个SQL脚本文件是最小的运行单位，且一般只能按照从上倒下的顺序，逐个运行脚本中的代码段
### 问题：
1、 能否对不同关系型数据库的SQL语法进行兼容解析？
2、 能否通过对SQL脚本的解析，自动获得脚本间的依赖关系？
3、 分析SQL脚本时，能否先将脚本拆解成代码块，然后层级化、图形化展示，提升代码可读性？
4、 能否对某些SQL大脚本进行自动化步骤分析，拆解成若干个可“并行运行”的执行步骤，提升大脚本的执行效率？
5、 扩展思考：若SQL大脚本的上游依赖，只有个别变动，能否只选择执行大脚本中的某个“分支”步骤？
### 方案：
为了解决以上问题，可以使用ZGLanguage对各关系型数据库的SQL进行语法配置，配置文件为 MARK_SQLS.syn 。
目前已经完成对几款主流数据库(Hive、Greenplum、DWS、Oracle、Mysql、Hana)的主要SQL语法的配置，不仅可识别“增删改查”等基础语法，还可以识别“判断、循环、游标、赋值”等语法。
在支持跨数据库SQL语法解析的同时，还提供了SQL代码拆解标注功能，通过此功能，用户可以对任意SQL脚本进行拆解分析。
这里提供了一个SQL脚本拆解分析的案例，使用Python语言编写(split_etl.py)，通过对ZGLanguage标注后的结果拆解分析，得到一个Json结果，说明如下：
{ SQL_FILE_NAME : SQL脚本文件名 , DEPEND_TABLES : SQL脚本依赖表清单 , CREATE_TABLES : SQL脚本创建表清单 , UPDATE_INSERT_TABLES : SQL脚本更改表清单 , SQL_SPLIT_INFO : [ SQL脚本里每个SQL代码段拆解信息 ： { SQL_SEQ : 序号 ： 1，2，3 , SQL_TYPE : 类型 ： __CREATE_TABLE_SELECT__, __INSERT_TABLE_SELECT__, __DELETE_TABLE__, __IF__ . , SQL_CODE : 完整代码 , LVL_STRUCT : 代码的层级结构 , LVL_CODE : 层级结构的代码 , TAR_TAB : 目标表 , SRC_TAB : 来源表 } , { .. } ] , SQL_DEPEND_LVL : SQL脚本代码段步骤(序号)依赖层级 }
以上参数使用建议：
1、 使用 DEPEND_TABLES、 CREATE_TABLES、 UPDATE_INSERT_TABLES，可以建立起各SQL脚本(包括存储过程)之间的依赖关系，辅助实现调度工具依赖自动化配置
2、 使用 SQL_SPLIT_INFO，可以对SQL脚本进行层级化、图形化展示，提升代码可读性
3、 使用 SQL_DEPEND_LVL，可以辅助实现大脚本中的： a、代码段并行; b、选择“分支”步骤运行功能
### 案例代码如下 ：
# 假设存储过程代码（proc_test.prc）：
CREATE OR REPLACE PROCEDURE PROC_F_CWWS_LOAN ( P_AS_OF_DATE IN DATE default date'20200101', RET_FLG OUT VARCHAR2, RET_MSG OUT VARCHAR2 ) IS /****************************************************************************** 功能描述：xxxx业务数据ETL处理 源 表： 目 标 表：MA_F_LOAN 备 注： ******************************************************************************/ -- 声明变量并初始化 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 --设置会话日期格式 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; 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 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); 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; --更新逾期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 ; END IF; INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 10'); COMMIT; EXCEPTION WHEN OTHERS THEN --写入异常日志 call ETL.PROC_ETL_LOG(P_AS_OF_DATE,V_PROC_NAME,V_PROC_DESC,V_COUNT,-1,SQLCODE,SQLERRM); RET_MSG := SQLCODE || ':' || SQLERRM; END; /
# 执行标注命令：
ZGLanguage -e SPLIT_ETL/MARK_SQLS.syn -t proc_test.prc -o mark_sql.zgl > log.log
得到标注结果 mark_sql.zgl 内容如下 ：
__CREATE_PROCEDURE_HEAD__{:::}||PROC_F_CWWS_LOAN{:::} CREATE OR REPLACE PROCEDURE PROC_F_CWWS_LOAN ( P_AS_OF_DATE IN DATE default date '20200101' , 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 {;;;} __EXECUTE__{:::} EXECUTE IMMEDIATE ' ALTER SESSION SET NLS_DATE_FORMAT = ' 'YYYY-MM-DD' '' {;;;} __SELECT_INTO__{:::}V_P_FREQ{:::} SELECT P_FREQ INTO V_P_FREQ FROM {###}<srctab>{###}ETL_PROC_STATUS_DEF{###}</srctab>{###} WHERE PROC_NAME = V_PROC_NAME {;;;} __IF__{:::} IF P_AS_OF_DATE = FUNC_GET_FREQ_DAYS(P_AS_OF_DATE, V_P_FREQ) THEN {;;;} __RUN_PROC_FUN__{:::} ETL.ETL_ADD_PARTITION( 'MA_F_LOAN' , P_AS_OF_DATE , 'ETL' ) {;;;} __DELETE_TABLE__{:::}||TMP_XD_LAST_PAYDATE{:::} DELETE TMP_XD_LAST_PAYDATE {;;;} __DELETE_TABLE__{:::}||TMP_XD_NEXT_PAYDATE{:::} DELETE TMP_XD_NEXT_PAYDATE {;;;} __COMMIT__{:::}commit{;;;} __INSERT_TABLE_SELECT__{:::}ETL||TMP_XD_LAST_PAYDATE{:::} INSERT INTO ETL.TMP_XD_LAST_PAYDATE (OBJECTNO, LAST_PAYDATE) SELECT OBJECTNO, LAST_PAYDATE FROM {###}<subsel>{###} SELECT T.OBJECTNO, MAX(TO_DATE(PAYDATE, 'YYYY-MM-DD')) LAST_PAYDATE FROM {###}<srctab>{###}NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE{###}</srctab>{###} 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 {###}</subsel>{###} {;;;} __INSERT_TABLE_SELECT__{:::}ETL||TMP_XD_NEXT_PAYDATE{:::} INSERT INTO ETL.TMP_XD_NEXT_PAYDATE (OBJECTNO, NEXT_PAYDATE) SELECT OBJECTNO, NEXT_PAYDATE FROM {###}<subsel>{###} SELECT T.OBJECTNO, MIN(TO_DATE(PAYDATE, 'YYYY-MM-DD')) NEXT_PAYDATE FROM {###}<srctab>{###}NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE{###}</srctab>{###} 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 {###}</subsel>{###} {;;;} __COMMIT__{:::}commit{;;;} __MERGE_TABLE__{:::}ETL||MA_F_LOAN{:::} MERGE INTO ETL.MA_F_LOAN A USING {###}<subsel>{###} SELECT T.ACCOUNT_NUMBER, T.GL_ACCOUNT_ID, T.INT_GL_ACCOUNT_ID FROM {###}<srctab>{###}ETL.MA_F_LOAN{###}</srctab>{###} T INNER JOIN {###}<srctab>{###}ETL.MA_D_GL_SUBJECT{###}</srctab>{###} 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 {###}<subsel>{###} SELECT T2.ACCOUNT_NUMBER, COUNT(1) FROM {###}<srctab>{###}ETL.MA_F_LOAN{###}</srctab>{###} T2 WHERE T2.AS_OF_DATE = P_AS_OF_DATE GROUP BY T2.ACCOUNT_NUMBER HAVING COUNT(1) > 1 {###}</subsel>{###} ) {###}</subsel>{###} 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 {;;;} __UPDATE_TABLE__{:::}||MA_F_LOAN{:::} 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 {;;;} __END_IF__{:::}END IF{;;;} __INSERT_INTO_VALUES__{:::}||M_RUNLOG{:::} INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 10') {;;;} __COMMIT__{:::}commit{;;;} __EXCEPTION_WHEN__{:::}EXCEPTION WHEN OTHERS THEN{;;;} __RUN_PROC_FUN__{:::}call ETL.PROC_ETL_LOG(P_AS_OF_DATE ,V_PROC_NAME ,V_PROC_DESC ,V_COUNT ,-1 ,SQLCODE ,SQLERRM ) {;;;} __DECLARE_VAR__{:::}RET_MSG{:::}RET_MSG := SQLCODE || ':' || SQLERRM {;;;} __END__{:::}end{;;;}
# 使用 Python 对以上标注代码进行解析，获得JSON结果如下 ：
{'SQL_FILE_NAME': 'proc_test.prc', 'DEPEND_TABLES': [{'SCH': 'NYBDP', 'NAME': 'O_CWWS_ACCT_PAYMENT_SCHEDULE'}, {'SCH': 'ETL', 'NAME': 'MA_D_GL_SUBJECT'}, {'SCH': 'ETL', 'NAME': 'MA_F_LOAN'}], 'CREATE_TABLES': [], 'UPDATE_INSERT_TABLES': [{'SCH': 'ETL', 'NAME': 'TMP_XD_LAST_PAYDATE'}, {'SCH': 'ETL', 'NAME': 'TMP_XD_NEXT_PAYDATE'}, {'SCH': 'ETL', 'NAME': 'MA_F_LOAN'}, {'SCH': '', 'NAME': 'M_RUNLOG'}], 'SQL_SPLIT_INFO': [{'SQL_SEQ': 1, 'SQL_TYPE': '__CREATE_PROCEDURE_HEAD__'}, {'SQL_SEQ': 2, 'SQL_TYPE': '__EXECUTE__'}, {'SQL_SEQ': 3, 'SQL_TYPE': '__SELECT_INTO__'}, {'SQL_SEQ': 4, 'SQL_TYPE': '__IF__'}, {'SQL_SEQ': 5, 'SQL_TYPE': '__RUN_PROC_FUN__'}, {'SQL_SEQ': 6, 'SQL_TYPE': '__DELETE_TABLE__', 'SQL_CODE': 'DELETE TMP_XD_LAST_PAYDATE', 'LVL_STRUCT': {'TMP_XD_LAST_PAYDATE': []}, 'LVL_CODE': {'TMP_XD_LAST_PAYDATE': 'DELETE TMP_XD_LAST_PAYDATE'}, 'TAR_TAB': [{'SCH': '', 'NAME': 'TMP_XD_LAST_PAYDATE'}], 'SRC_TAB': []}, {'SQL_SEQ': 7, 'SQL_TYPE': '__DELETE_TABLE__', 'SQL_CODE': 'DELETE TMP_XD_NEXT_PAYDATE', 'LVL_STRUCT': {'TMP_XD_NEXT_PAYDATE': []}, 'LVL_CODE': {'TMP_XD_NEXT_PAYDATE': 'DELETE TMP_XD_NEXT_PAYDATE'}, 'TAR_TAB': [{'SCH': '', 'NAME': 'TMP_XD_NEXT_PAYDATE'}], 'SRC_TAB': []}, {'SQL_SEQ': 8, 'SQL_TYPE': '__COMMIT__'}, {'SQL_SEQ': 9, 'SQL_TYPE': '__INSERT_TABLE_SELECT__', 'SQL_CODE': "INSERT INTO ETL.TMP_XD_LAST_PAYDATE\n (OBJECTNO, LAST_PAYDATE) \n SELECT OBJECTNO, LAST_PAYDATE\n FROM ( SELECT T.OBJECTNO,\n MAX(TO_DATE(PAYDATE, 'YYYY-MM-DD')) LAST_PAYDATE\n FROM NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE T\n WHERE T.AS_OF_DATE = P_AS_OF_DATE\n AND T.SEQID <> '999'\n AND TO_DATE(T.PAYDATE, 'YYYY-MM-DD') < P_AS_OF_DATE\n GROUP BY T.OBJECTNO )", 'LVL_STRUCT': {'__SUB_SELECT_1__': ['NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE'], 'ETL.TMP_XD_LAST_PAYDATE': ['__SUB_SELECT_1__']}, 'LVL_CODE': {'__SUB_SELECT_1__': " SELECT T.OBJECTNO,\n MAX(TO_DATE(PAYDATE, 'YYYY-MM-DD')) LAST_PAYDATE\n FROM NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE T\n WHERE T.AS_OF_DATE = P_AS_OF_DATE\n AND T.SEQID <> '999'\n AND TO_DATE(T.PAYDATE, 'YYYY-MM-DD') < P_AS_OF_DATE\n GROUP BY T.OBJECTNO ", 'ETL.TMP_XD_LAST_PAYDATE': 'INSERT INTO ETL.TMP_XD_LAST_PAYDATE\n (OBJECTNO, LAST_PAYDATE) \n SELECT OBJECTNO, LAST_PAYDATE\n FROM __SUB_SELECT_1__'}, 'TAR_TAB': [{'SCH': 'ETL', 'NAME': 'TMP_XD_LAST_PAYDATE'}], 'SRC_TAB': [{'SCH': 'NYBDP', 'NAME': 'O_CWWS_ACCT_PAYMENT_SCHEDULE'}]}, {'SQL_SEQ': 10, 'SQL_TYPE': '__INSERT_TABLE_SELECT__', 'SQL_CODE': "INSERT INTO ETL.TMP_XD_NEXT_PAYDATE\n (OBJECTNO, NEXT_PAYDATE) \n SELECT OBJECTNO, NEXT_PAYDATE\n FROM ( SELECT T.OBJECTNO,\n MIN(TO_DATE(PAYDATE, 'YYYY-MM-DD')) NEXT_PAYDATE\n FROM NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE T\n WHERE T.AS_OF_DATE = P_AS_OF_DATE\n AND T.SEQID <> '999'\n AND TO_DATE(T.PAYDATE, 'YYYY-MM-DD') >= P_AS_OF_DATE\n GROUP BY T.OBJECTNO )", 'LVL_STRUCT': {'__SUB_SELECT_2__': ['NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE'], 'ETL.TMP_XD_NEXT_PAYDATE': ['__SUB_SELECT_2__']}, 'LVL_CODE': {'__SUB_SELECT_2__': " SELECT T.OBJECTNO,\n MIN(TO_DATE(PAYDATE, 'YYYY-MM-DD')) NEXT_PAYDATE\n FROM NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE T\n WHERE T.AS_OF_DATE = P_AS_OF_DATE\n AND T.SEQID <> '999'\n AND TO_DATE(T.PAYDATE, 'YYYY-MM-DD') >= P_AS_OF_DATE\n GROUP BY T.OBJECTNO ", 'ETL.TMP_XD_NEXT_PAYDATE': 'INSERT INTO ETL.TMP_XD_NEXT_PAYDATE\n (OBJECTNO, NEXT_PAYDATE) \n SELECT OBJECTNO, NEXT_PAYDATE\n FROM __SUB_SELECT_2__'}, 'TAR_TAB': [{'SCH': 'ETL', 'NAME': 'TMP_XD_NEXT_PAYDATE'}], 'SRC_TAB': [{'SCH': 'NYBDP', 'NAME': 'O_CWWS_ACCT_PAYMENT_SCHEDULE'}]}, {'SQL_SEQ': 11, 'SQL_TYPE': '__COMMIT__'}, {'SQL_SEQ': 12, 'SQL_TYPE': '__MERGE_TABLE__', 'SQL_CODE': "MERGE INTO ETL.MA_F_LOAN A\n USING ( SELECT\n T.ACCOUNT_NUMBER, T.GL_ACCOUNT_ID, T.INT_GL_ACCOUNT_ID\n FROM ETL.MA_F_LOAN T\n INNER JOIN ETL.MA_D_GL_SUBJECT T1\n ON T.INT_GL_ACCOUNT_ID = T1.SUBJECT_NO3\n AND T1.SUBJECT_NAME3 LIKE '%已减值%'\n AND T1.AS_OF_DATE = P_AS_OF_DATE\n WHERE T.AS_OF_DATE = P_AS_OF_DATE\n AND T.ACCOUNT_NUMBER IN\n (SELECT ACCOUNT_NUMBER\n FROM ( SELECT\n T2.ACCOUNT_NUMBER, COUNT(1)\n FROM ETL.MA_F_LOAN T2\n WHERE T2.AS_OF_DATE = P_AS_OF_DATE\n GROUP BY T2.ACCOUNT_NUMBER\n HAVING COUNT(1) > 1 ) ) ) B\n 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 )\n WHEN MATCHED THEN\n UPDATE SET A.CUR_BOOK_BAL = 0, A.OVERDUE_BAL = 0", 'LVL_STRUCT': {'__SUB_SELECT_3__': ['ETL.MA_F_LOAN'], '__SUB_SELECT_4__': ['ETL.MA_F_LOAN', 'ETL.MA_D_GL_SUBJECT', '__SUB_SELECT_3__'], 'ETL.MA_F_LOAN': ['__SUB_SELECT_4__']}, 'LVL_CODE': {'__SUB_SELECT_3__': ' SELECT\n T2.ACCOUNT_NUMBER, COUNT(1)\n FROM ETL.MA_F_LOAN T2\n WHERE T2.AS_OF_DATE = P_AS_OF_DATE\n GROUP BY T2.ACCOUNT_NUMBER\n HAVING COUNT(1) > 1 ', '__SUB_SELECT_4__': " SELECT\n T.ACCOUNT_NUMBER, T.GL_ACCOUNT_ID, T.INT_GL_ACCOUNT_ID\n FROM ETL.MA_F_LOAN T\n INNER JOIN ETL.MA_D_GL_SUBJECT T1\n ON T.INT_GL_ACCOUNT_ID = T1.SUBJECT_NO3\n AND T1.SUBJECT_NAME3 LIKE '%已减值%'\n AND T1.AS_OF_DATE = P_AS_OF_DATE\n WHERE T.AS_OF_DATE = P_AS_OF_DATE\n AND T.ACCOUNT_NUMBER IN\n (SELECT ACCOUNT_NUMBER\n FROM __SUB_SELECT_3__ ) ", 'ETL.MA_F_LOAN': 'MERGE INTO ETL.MA_F_LOAN A\n USING __SUB_SELECT_4__ B\n 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 )\n WHEN MATCHED THEN\n UPDATE SET A.CUR_BOOK_BAL = 0, A.OVERDUE_BAL = 0'}, 'TAR_TAB': [{'SCH': 'ETL', 'NAME': 'MA_F_LOAN'}], 'SRC_TAB': [{'SCH': 'ETL', 'NAME': 'MA_D_GL_SUBJECT'}, {'SCH': 'ETL', 'NAME': 'MA_F_LOAN'}]}, {'SQL_SEQ': 13, 'SQL_TYPE': '__UPDATE_TABLE__', 'SQL_CODE': 'UPDATE MA_F_LOAN A\n SET LAST_REPRICE_DATE = A.ORIGINATION_DATE ,\n NEXT_REPRICE_DATE = A.MATURITY_DATE ,\n REPRICE_FREQ = A.ORG_TERM ,\n REPRICE_FREQ_MULT = A.ORG_TERM_MULT ,\n ADJUSTABLE_TYPE_CD = 0\n WHERE A.MATURITY_DATE <= P_AS_OF_DATE\n AND A.CUR_BOOK_BAL <> 0', 'LVL_STRUCT': {'MA_F_LOAN': []}, 'LVL_CODE': {'MA_F_LOAN': 'UPDATE MA_F_LOAN A\n SET LAST_REPRICE_DATE = A.ORIGINATION_DATE ,\n NEXT_REPRICE_DATE = A.MATURITY_DATE ,\n REPRICE_FREQ = A.ORG_TERM ,\n REPRICE_FREQ_MULT = A.ORG_TERM_MULT ,\n ADJUSTABLE_TYPE_CD = 0\n WHERE A.MATURITY_DATE <= P_AS_OF_DATE\n AND A.CUR_BOOK_BAL <> 0'}, 'TAR_TAB': [{'SCH': '', 'NAME': 'MA_F_LOAN'}], 'SRC_TAB': []}, {'SQL_SEQ': 14, 'SQL_TYPE': '__END_IF__'}, {'SQL_SEQ': 15, 'SQL_TYPE': '__INSERT_INTO_VALUES__', 'SQL_CODE': "INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 10')", 'LVL_STRUCT': {'M_RUNLOG': []}, 'LVL_CODE': {'M_RUNLOG': "INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 10')"}, 'TAR_TAB': [{'SCH': '', 'NAME': 'M_RUNLOG'}], 'SRC_TAB': []}, {'SQL_SEQ': 16, 'SQL_TYPE': '__COMMIT__'}, {'SQL_SEQ': 17, 'SQL_TYPE': '__EXCEPTION_WHEN__'}, {'SQL_SEQ': 18, 'SQL_TYPE': '__RUN_PROC_FUN__'}, {'SQL_SEQ': 19, 'SQL_TYPE': '__DECLARE_VAR__'}, {'SQL_SEQ': 20, 'SQL_TYPE': '__END__'}], 'SQL_DEPEND_LVL': [{6: {}, 7: {}, 12: {}, 15: {}}, {9: {6}, 10: {7}, 13: {12}} ] }
# 将以上两个步骤(1、执行标注命令；2、解析标注结果)封装成一个Python代码文件(split_etl.py)，可得：
# -*- coding: utf-8 -*- import sys import subprocess import json ############################################################### id_new_select = 0 def make_sql_struct(mark_sql, tartab) : sql_info_list = mark_sql.split('{###}') srctab_4_levels = [] onelvl_srctab = [] lvl_struct = {} mid_sql_list = [] lvl_code = {} all_srctab = set() global id_new_select while sql_info_list: one_node = sql_info_list.pop(0) ################################################# # with select if one_node == '<withas>' : srctab_4_levels.append(onelvl_srctab) onelvl_srctab = [] mid_sql_list.append(one_node) elif one_node == '</withas>': sql_text = '' tmp_pop_one = mid_sql_list.pop(-1) while tmp_pop_one != '<as/>' : # '<withas>': sql_text = tmp_pop_one + sql_text tmp_pop_one = mid_sql_list.pop(-1) withname = mid_sql_list.pop(-1) mid_sql_list.pop(-1) # pass lvl_struct[withname] = onelvl_srctab onelvl_srctab = srctab_4_levels.pop(-1) lvl_code[withname] = sql_text ################################################# # sub select elif one_node == '<subsel>' : srctab_4_levels.append(onelvl_srctab) onelvl_srctab = [] mid_sql_list.append(one_node) elif one_node == '</subsel>' : id_new_select += 1 new_sub_select_id = '__SUB_SELECT_%d__' % id_new_select # 新增子查询ID添加到上层SQL的源表 srctab_4_levels[-1].append(new_sub_select_id) sql_text = '' tmp_pop_one = mid_sql_list.pop(-1) while tmp_pop_one != '<subsel>': sql_text = tmp_pop_one + sql_text tmp_pop_one = mid_sql_list.pop(-1) lvl_struct[new_sub_select_id] = onelvl_srctab onelvl_srctab = srctab_4_levels.pop(-1) lvl_code[new_sub_select_id] = sql_text mid_sql_list[-1] += new_sub_select_id ################################################# # union select elif one_node == '<union>' : srctab_4_levels.append(onelvl_srctab) onelvl_srctab = [] mid_sql_list.append(one_node) elif one_node == '</union>' : id_new_select += 1 new_union_select_id = '__UNION_SELECT_%d__' % id_new_select # 新增子查询ID添加到上层SQL的源表 srctab_4_levels[-1].append(new_union_select_id) sql_text = '' tmp_pop_one = mid_sql_list.pop(-1) while tmp_pop_one != '<union>': sql_text = tmp_pop_one + sql_text tmp_pop_one = mid_sql_list.pop(-1) lvl_struct[new_union_select_id] = onelvl_srctab onelvl_srctab = srctab_4_levels.pop(-1) lvl_code[new_union_select_id] = sql_text mid_sql_list[-1] += '\nSELECT * FROM ' + new_union_select_id ################################################# # sourc table elif one_node == '<srctab>': src_tab_name = sql_info_list.pop(0) onelvl_srctab.append(src_tab_name) sql_info_list.pop(0) # </srctab> mid_sql_list[-1] += src_tab_name all_srctab.add(src_tab_name.upper()) ################################################# # sourc function elif one_node == '<srcfun>': src_fun_name = sql_info_list.pop(0).split('(')[0] onelvl_srctab.append(src_fun_name) sql_info_list.pop(0) # </srcfun> mid_sql_list[-1] += src_fun_name all_srctab.add(src_fun_name.upper()) else: mid_sql_list.append(one_node) lvl_struct[tartab] = onelvl_srctab lvl_code[tartab] = ''.join(mid_sql_list) ############################################### tartablist = [] for one in tartab.split(',') : schema_tartab = one.split('.') # print('schema_tartab=', schema_tartab) if len(schema_tartab) == 1 : tartablist.append( {'SCH': '', 'NAME': schema_tartab[0]} ) else : tartablist.append( {'SCH': schema_tartab[0], 'NAME': schema_tartab[-1]} ) srctablist = [] for one in all_srctab : schema_tartab = one.split('.') # print('schema_tartab=', schema_tartab) if len(schema_tartab) == 1 : srctablist.append( {'SCH': '', 'NAME': schema_tartab[0]} ) else : srctablist.append( {'SCH': schema_tartab[0], 'NAME': schema_tartab[-1]} ) # print('lvl_struct = ', lvl_struct) # print('lvl_code = ', lvl_code) # print('tartablist = ', tartablist) # print('srctablist = ', srctablist) return { 'LVL_STRUCT' : lvl_struct , 'LVL_CODE' : lvl_code , 'TAR_TAB' : tartablist , 'SRC_TAB' : srctablist } def split_etl(etl_file) : #################################### ### 对 SQL 文件进行标注转换 run_result = subprocess.run(['ZGLanguage', '-e', 'SPLIT_ETL/MARK_SQLS.syn' , '-t', etl_file , '-o', 'mark_sql.zgl'] , capture_output=True # , stdout=log_file , encoding='utf-8' , text=True ) with open("run_zgl.log", "w", encoding='utf-8') 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) ######################################### sql_file_info = { 'SQL_FILE_NAME': etl_file.replace('/', ' ').replace('\\', ' ').split(' ')[-1] , 'SQL_SPLIT_INFO': [] , 'DEPEND_TABLES': [] , 'CREATE_TABLES': [] , 'UPDATE_INSERT_TABLES': [] , 'SQL_DEPEND_LVL': [] } ######################################### ### get SQL_SPLIT_INFO sql_seq = 0 with (open('mark_sql.zgl', 'r', encoding='utf-8') as fs) : sql_tran_list = fs.read().split('{;;;}') for one_tran_sql in sql_tran_list : # print('one_tran_sql=', one_tran_sql) if not one_tran_sql : continue ############################# # 语句顺序ID sql_seq += 1 one_split_info = {'SQL_SEQ' : sql_seq} ############################# # 语句类型 tran_info = one_tran_sql.strip().split('{:::}') one_split_info['SQL_TYPE'] = tran_info[0] ############################# if tran_info[0] in ( '__CREATE_TABLE_STRUCT__', '__CREATE_TABLE_LIKE__', '__CREATE_TABLE_SELECT__', '__NAME_EQ_SELECT__' , '__INSERT_TABLE_SELECT__', '__INSERT_INTO_VALUES__', '__UPDATE_TABLE__', '__MERGE_TABLE__' , '__DELETE_TABLE__', '__TRUNCATE_TABLE__', '__DROP_TABLE__' ) : schema_tartab = tran_info[1].split('||') # print("schema_tartab = ", schema_tartab) tartab = '' if schema_tartab[0] : tartab = (schema_tartab[0] +'.'+ schema_tartab[1]).upper() else : tartab = schema_tartab[1].upper() # 还原语句代码 sql_code = tran_info[2].replace('{###}<union>{###}','').replace('{###}</union>{###}','' ).replace('{###}<intersect>{###}','').replace('{###}</intersect>{###}','' ).replace('{###}<minus>{###}','').replace('{###}</minus>{###}','' ).replace('{###}<except>{###}','').replace('{###}</except>{###}','' ).replace('{###}<subsel>{###}','(').replace('{###}</subsel>{###}',')' ).replace('{###}<withas>{###}','WHIT ').replace('{###}<as/>{###}',' AS (').replace('{###}</withas>{###}',')' ).replace('{###}<srctab>{###}','').replace('{###}</srctab>{###}','' ).replace('{###}<srcfun>{###}','').replace('{###}</srcfun>{###}','') # print('SQL_CODE :\n', sql_code) one_split_info['SQL_CODE'] = sql_code # 拆解生成 SQL 代码块信息 one_split_info.update(make_sql_struct(tran_info[2], tartab)) elif tran_info[0] == '__JUST_SELECT__' : tartab = '__JUST_SELECT_%d__' % id_new_select # 还原语句代码 sql_code = tran_info[1].replace('{###}<union>{###}','').replace('{###}</union>{###}','' ).replace('{###}<intersect>{###}','').replace('{###}</intersect>{###}','' ).replace('{###}<minus>{###}','').replace('{###}</minus>{###}','' ).replace('{###}<except>{###}','').replace('{###}</except>{###}','' ).replace('{###}<subsel>{###}','(').replace('{###}</subsel>{###}',')' ).replace('{###}<withas>{###}','WHIT ').replace('{###}<as/>{###}',' AS (').replace('{###}</withas>{###}',')' ).replace('{###}<srctab>{###}','').replace('{###}</srctab>{###}','' ).replace('{###}<srcfun>{###}','').replace('{###}</srcfun>{###}','') # print('SQL_CODE :\n', sql_code) one_split_info['SQL_CODE'] = sql_code # 拆解生成 SQL 代码块信息 one_split_info.update(make_sql_struct(tran_info[1], tartab)) elif tran_info[0] == '__FROM_INSERT__' : tran_info.pop(0) sql_code = tran_info.pop(0) tartab_list = set() while tran_info : schema_tartab = tran_info.pop(0).split('||') tartab = '' if schema_tartab[0] : tartab = schema_tartab[0] +'.'+ schema_tartab[1] else : tartab = schema_tartab[1] tartab_list.add(tartab.upper()) ################################## sql_code += tran_info.pop(0) # print('sql_code = ', sql_code) one_split_info['SQL_CODE'] = sql_code.replace('{###}<union>{###}','').replace('{###}</union>{###}','' ).replace('{###}<intersect>{###}','').replace('{###}</intersect>{###}','' ).replace('{###}<minus>{###}','').replace('{###}</minus>{###}','' ).replace('{###}<except>{###}','').replace('{###}</except>{###}','' ).replace('{###}<subsel>{###}','(').replace('{###}</subsel>{###}',')' ).replace('{###}<withas>{###}','WHIT ').replace('{###}<as/>{###}',' AS (').replace('{###}</withas>{###}',')' ).replace('{###}<srctab>{###}','').replace('{###}</srctab>{###}','' ).replace('{###}<srcfun>{###}','').replace('{###}</srcfun>{###}','') tartab = ','.join(tartab_list) # print('tartab = ', tartab_list) # 拆解生成 SQL 代码块信息 one_split_info.update(make_sql_struct(sql_code, tartab)) elif tran_info[0] in ('__DROP_PROCEDURE__', '__DROP_FUNCTION__', '__CREATE_PROCEDURE_HEAD__', '__CREATE_FUNCTION_HEAD__'): pass elif tran_info[0] in ( '__SELECT_INTO__' , '__DECLARE_MYSQL_CURSOR__', '__DECLARE_ORACLE_CURSOR__', '__FETCH__' , '__EXECUTE__', '__SET_CONF_VAR__', '__RESET_CONF__', '__DECLARE_VAR__' , '__IF__', '__CONTROL_IF__', '__CONTROL_CASE_WHEN__', '__END_IF__' , '__EXCEPTION_WHEN__' , '__CONTROL_WHEN__', '__CONTROL_ELSE__' , '__CONTROL_MYSQL_WHILE__', '__CONTROL_ORACLE_WHILE__' , '__CONTROL_ORACLE_FOR__', '__CONTROL_REPEAT__' , '__CONTROL_MYSQL_LOOP__', '__CONTROL_ORACLE_LOOP__' , '__OPEN__', '__CLOSE__' , '__RUN_PROC_FUN__', '__COMMIT__', '__END__' , '__ALTER_TABLE__', '__GRANT__', '__ANALYZE__', '__COMMENT_ON__', '__VACUUM__' ): pass else : print("SQL TYPE [%s] ????" % (tran_info[0])) sql_file_info['SQL_SPLIT_INFO'].append(one_split_info) ######################################### ### get DEPEND_TABLES CREATE_TABLES UPDATE_INSERT_TABLES all_tar_tab_name = set() all_src_tab_name = set() depend_tables = [] create_tables = [] update_insert_tables = [] for one_sql_info in sql_file_info['SQL_SPLIT_INFO'] : # print('SQL_TYPE = ', one_sql_info['SQL_TYPE']) if one_sql_info['SQL_TYPE'] not in ('__CREATE_TABLE_STRUCT__', '__CREATE_TABLE_LIKE__', '__CREATE_TABLE_SELECT__', '__NAME_EQ_SELECT__' ,'__INSERT_TABLE_SELECT__', '__INSERT_INTO_VALUES__', '__FROM_INSERT__' ,'__UPDATE_TABLE__', '__MERGE_TABLE__' ,'__JUST_SELECT__') : continue for one in one_sql_info['TAR_TAB'] : # print('TAR_TAB = ', one['NAME']) if one['NAME'] not in all_tar_tab_name : all_tar_tab_name.add(one['NAME']) if one_sql_info['SQL_TYPE'] in ('__CREATE_TABLE_STRUCT__','__CREATE_TABLE_LIKE__' ,'__CREATE_TABLE_SELECT__','__NAME_EQ_SELECT__') : create_tables.append(one) elif one_sql_info['SQL_TYPE'] in ('__INSERT_TABLE_SELECT__', '__INSERT_INTO_VALUES__', '__FROM_INSERT__' ,'__UPDATE_TABLE__', '__MERGE_TABLE__') : update_insert_tables.append(one) for one in one_sql_info['SRC_TAB'] : # print('SRC_TAB = ', one['NAME']) # 允许源表和目标表存在相同情况 # if one['NAME'] not in all_tar_tab_name and one['NAME'] not in all_src_tab_name : if one['NAME'] not in all_src_tab_name : depend_tables.append(one) all_src_tab_name.add(one['NAME']) # print('depend_tables = ', depend_tables) # print('create_tables = ', create_tables) # print('update_insert_tables = ', update_insert_tables) sql_file_info['DEPEND_TABLES'] = depend_tables sql_file_info['CREATE_TABLES'] = create_tables sql_file_info['UPDATE_INSERT_TABLES'] = update_insert_tables ######################################### ### get SQL_DEPEND_LVL sql_depend_lvl = [] # 选择要处理的 SQL 类型序号 sql_seq_ids = [] for one_sql_info in sql_file_info['SQL_SPLIT_INFO'] : # print('SQL_SEQ = ', one_sql_info['SQL_SEQ']) # print('SQL_TYPE = ', one_sql_info['SQL_TYPE']) # 只处理部分主要类型的SQL，后续需要可以扩展 if one_sql_info['SQL_TYPE'] in ( '__CREATE_TABLE_STRUCT__', '__CREATE_TABLE_LIKE__', '__CREATE_TABLE_SELECT__', '__NAME_EQ_SELECT__' , '__INSERT_TABLE_SELECT__', '__INSERT_INTO_VALUES__', '__FROM_INSERT__', '__JUST_SELECT__' , '__UPDATE_TABLE__', '__MERGE_TABLE__' , '__DELETE_TABLE__', '__TRUNCATE_TABLE__', '__DROP_TABLE__' ) : sql_seq_ids.append(one_sql_info['SQL_SEQ'] - 1) # 减一当下标 # print('sql_seq_ids = ', sql_seq_ids) ############################### tab2seq = { tab : 0 for tab in all_src_tab_name } # print('tab2seq = ', tab2seq) while sql_seq_ids : curr_lvl_ids = {} next_lvl_ids = [] tab2seq4add = {} tab2seq4check = set() for seq in sql_seq_ids : is_curr_lvl = True depd_seqs = set() # 检查依赖表是否全满足 for one in sql_file_info['SQL_SPLIT_INFO'][seq]['SRC_TAB'] : srctab = one['NAME'] if srctab not in tab2seq or srctab in tab2seq4add : # print("srctab = ", srctab) is_curr_lvl = False break if tab2seq[srctab] != 0 : depd_seqs.add( tab2seq[srctab] ) if not is_curr_lvl : next_lvl_ids.append(seq) # 记录每个步骤的目标表 for one in sql_file_info['SQL_SPLIT_INFO'][seq]['TAR_TAB'] : # print('TAR_TAB = ', one['NAME']) tab2seq4check.add(one['NAME']) continue # 检查目标表是否冲突 for one in sql_file_info['SQL_SPLIT_INFO'][seq]['TAR_TAB'] : # print('TAR_TAB = ', one['NAME']) tartab = one['NAME'] if tartab not in tab2seq4check : tab2seq4add[tartab] = seq + 1 tab2seq4check.add(tartab) if not depd_seqs and tartab in tab2seq: depd_seqs.add( tab2seq[tartab] ) else : # print('冲突表：', tartab) is_curr_lvl = False break if is_curr_lvl : curr_lvl_ids[seq+1] = depd_seqs else : next_lvl_ids.append(seq) # print('curr_lvl_ids = ', curr_lvl_ids) # print('next_lvl_ids = ', next_lvl_ids) sql_depend_lvl.append(curr_lvl_ids) sql_seq_ids = next_lvl_ids tab2seq.update(tab2seq4add) sql_file_info['SQL_DEPEND_LVL'] = sql_depend_lvl print('##################################################') print(sql_file_info) return if __name__ == "__main__" : if len(sys.argv) == 1 : print('Miss sql file !') sys.exit(-1) split_etl(sys.argv[1])
# 封装后 Python 代码执行命令：
python split_etl.py proc_test.prc > log.log
# 通用SQL语法解析配置文件 MARK_SQLS.syn 内容如下：
__DEF_FUZZY__ Y __DEF_DEBUG__ N __DEF_CASE_SENSITIVE__ N __DEF_LINE_COMMENT__ -- __DEF_LINES_COMMENT__ /* */ __DEF_TRY_KEEP_COMMENT__ N __DEF_STR__ __IF_KW__ <1,100> [1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz [0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_ __DEF_PATH__ __TRUNCATE_TABLE__ 1 : tru @ | truncate : tab @ | table 1 : tartab @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : tartab2 @ | __NAME__ 1 : end @ | ; ------------------------------------------------------------------- 1 : tru @ | __TRUNCATE_TABLE__{:::} : schema @ \ __NAME__ : tru @ \ || : tartab @ \ __NAME__ : tartab2 @ \ __NAME__ : tru @ \ {:::} 1 : tru @ \ truncate : tab @ | table : tartab @ | __NAME__ : schema @ | __NAME__ : pp @ \ . : tartab2 @ \ __NAME__ : end @ | {;;;} __DEF_PATH__ __DROP_TABLE__ 1 : drp @ | drop : tab @ | table : if @ CAN_SKIP | if : exists @ CAN_SKIP | exists 1 : tartab @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : tartab2 @ | __NAME__ 1 : end @ | ; ------------------------------------------------------------------- 1 : drp @ | __DROP_TABLE__{:::} : schema @ \ __NAME__ : drp @ \ || : tartab @ \ __NAME__ : tartab2 @ \ __NAME__ : drp @ \ {:::} 1 : drp @ \ drop : tab @ | table : if @ | if : exists @ | exists : tartab @ | __NAME__ : schema @ | __NAME__ : pp @ \ . : tartab2 @ \ __NAME__ : end @ | {;;;} __DEF_PATH__ __DELETE_TABLE__ 1 : del @ | delete : frm @ CAN_SKIP | from 1 : tartab @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : tartab2 @ | __NAME__ 1 : as @ CAN_SKIP | as : asn @ CAN_SKIP | __NAME__ 1 : where @ CAN_SKIP | __WHERE__ : end @ | ; ------------------------------------------------------------------- 1 : del @ | __DELETE_TABLE__{:::} : schema @ \ __NAME__ : del @ \ || : tartab @ \ __NAME__ : tartab2 @ \ __NAME__ : del @ \ {:::} 1 : del @ \ delete : frm @ | from : tartab @ | __NAME__ : schema @ | __NAME__ : pp @ \ . : tartab2 @ \ __NAME__ : as @ | as : asn @ | __NAME__ : where @ | __WHERE__ : end @ | {;;;} __DEF_PATH__ __CREATE_TABLE_STRUCT__ 1 : cre @ | create : glo @ %__IF_KW__ CAN_SKIP | global : loc @ %__IF_KW__ CAN_SKIP | local : vol @ %__IF_KW__ CAN_SKIP | volatile : tmp @ %__IF_KW__ CAN_SKIP | temp : temp @ %__IF_KW__ CAN_SKIP | temporary : ext @ %__IF_KW__ CAN_SKIP | external : tab @ | table : if @ %__IF_KW__ CAN_SKIP | if : not @ %__IF_KW__ CAN_SKIP | not : exi @ %__IF_KW__ CAN_SKIP | exists 1 : tartab @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : tartab2 @ | __NAME__ 1 : tabp1 @ CAN_SKIP | __TAB_CONSTRAINT__ : lkh @ | ( N : col @ | __NAME__ : dat @ | __DATA_TYPE__ : colp @ CAN_SKIP | __COL_CONSTRAINT__ e : dh @ | , 1 : rkh @ | ) : tabp2 @ CAN_SKIP | __TAB_CONSTRAINT__ : fh @ | ; ---------------------------------------------------------------------------- 1 : cre @ | __CREATE_TABLE_STRUCT__{:::} : schema @ \ __NAME__ : cre @ \ || : tartab @ \ __NAME__ : tartab2 @ \ __NAME__ : cre @ \ {:::} 1 : cre @ \ create : glo @ | global : loc @ | local : vol @ | volatile : tmp @ | temp : temp @ | temporary : ext @ | external : tab @ | table : if @ | if : not @ | not : exi @ | exists 1 : tartab @ | __NAME__ : schema @ | __NAME__ : pp @ \ . : tartab2 @ \ __NAME__ 1 : tabp1 @ | __TAB_CONSTRAINT__ : lkh @ | ( N : col @ | __NAME__ : dat @ | __DATA_TYPE__ : colp @ | __COL_CONSTRAINT__ e : dh @ | , 1 : rkh @ | ) : tabp2 @ | __TAB_CONSTRAINT__ : fh @ | {;;;} __DEF_PATH__ __CREATE_TABLE_LIKE__ 1 : cre @ | create : glo @ %__IF_KW__ CAN_SKIP | global : loc @ %__IF_KW__ CAN_SKIP | local : vol @ %__IF_KW__ CAN_SKIP | volatile : tmp @ %__IF_KW__ CAN_SKIP | temp : temp @ %__IF_KW__ CAN_SKIP | temporary : ext @ %__IF_KW__ CAN_SKIP | external : tab @ | table : if @ %__IF_KW__ CAN_SKIP | if : not @ %__IF_KW__ CAN_SKIP | not : exi @ %__IF_KW__ CAN_SKIP | exists 1 : tartab @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : tartab2 @ | __NAME__ 1 : lik @ %__IF_KW__ | like 1 : srctab @ | __NAME__ + : srcshm @ | __NAME__ : pp2 @ | . : srctab2 @ | __NAME__ : fh @ | ; -------------------------------------------------------------------- 1 : cre @ | __CREATE_TABLE_LIKE__{:::} : schema @ \ __NAME__ : cre @ \ || : tartab @ \ __NAME__ : tartab2 @ \ __NAME__ : cre @ \ {:::} 1 : cre @ \ create : tab @ | table : glo @ | global : loc @ | local : vol @ | volatile : tmp @ | temp : temp @ | temporary : ext @ | external : if @ | if : not @ | not : exi @ | exists : tartab @ | __NAME__ : schema @ | __NAME__ : pp @ \ . : tartab2 @ \ __NAME__ : lik @ | like __//__ : srctab @ | __NAME__ __//__ : srcshm @ | __NAME__ __//__ : pp2 @ \ . __//__ : srctab2 @ \ __NAME__ : srctab @ | {###}<srctab>{###} : srctab @ \ __NAME__ : srctab @ \ {###}</srctab>{###} : srctab2 @ | {###}<srctab>{###} : srcshm @ \ __NAME__ : pp2 @ \ . : srctab2 @ \ __NAME__ : srctab2 @ \ {###}</srctab>{###} : fh @ | {;;;} __DEF_PATH__ __CREATE_TABLE_SELECT__ 11 : cre @ | create : glo @ %__IF_KW__ CAN_SKIP | global : loc @ %__IF_KW__ CAN_SKIP | local : ulg @ %__IF_KW__ CAN_SKIP | unlogged : vol @ %__IF_KW__ CAN_SKIP | volatile : tmp @ %__IF_KW__ CAN_SKIP | temp : temp @ %__IF_KW__ CAN_SKIP | temporary : ext @ %__IF_KW__ CAN_SKIP | external : tab @ | table : if @ %__IF_KW__ CAN_SKIP | if : not @ %__IF_KW__ CAN_SKIP | not : exi @ %__IF_KW__ CAN_SKIP | exists 11 : tartab @ | __NAME__ ++ : schema @ | __NAME__ : pp @ | . : tartab2 @ | __NAME__ : tabp @ CAN_SKIP | __TAB_CONSTRAINT__ 11 : as @ | as : with @ CAN_SKIP | __WITH_AS_SELECT__ : sel @ | __SELECT__ 0N : uni @ | __UNION_SELECT__ : ins @ + __INTERSECT_SELECT__ : mis @ + __MINUS_SELECT__ : exc @ + __EXCEPT_SELECT__ 1 : tabp2 @ CAN_SKIP | __TAB_CONSTRAINT__ : end @ | ; ------------------------------------------------------------------- 1 : cre @ | __CREATE_TABLE_SELECT__{:::} : schema @ \ __NAME__ : cre @ \ || : tartab @ \ __NAME__ : tartab2 @ \ __NAME__ : cre @ \ {:::} 1 : cre @ \ create : tab @ | table : glo @ | global : loc @ | local : vol @ | volatile : tmp @ | temp : temp @ | temporary : ext @ | external : if @ | if : not @ | not : exi @ | exists : tartab @ | __NAME__ : schema @ | __NAME__ : pp @ \ . : tartab2 @ \ __NAME__ : tabp @ | __TAB_CONSTRAINT__ : as @ | as : with @ | __WITH_AS_SELECT__ : sel @ | __SELECT__ : tabp2 @ | __TAB_CONSTRAINT__ : end @ | {;;;} ------------------------------------------------------------------- 1 : cre @ | __CREATE_TABLE_SELECT__{:::} : schema @ \ __NAME__ : cre @ \ || : tartab @ \ __NAME__ : tartab2 @ \ __NAME__ : cre @ \ {:::} 1 : cre @ \ create : tab @ | table : glo @ | global : loc @ | local : vol @ | volatile : tmp @ | temp : temp @ | temporary : ext @ | external : if @ | if : not @ | not : exi @ | exists : tartab @ | __NAME__ : schema @ | __NAME__ : pp @ \ . : tartab2 @ \ __NAME__ : tabp @ | __TAB_CONSTRAINT__ : as @ | as : with @ | __WITH_AS_SELECT__ : uni @ \ {###}<union>{###} : ins @ \ {###}<intersect>{###} : mis @ \ {###}<minus>{###} : exc @ \ {###}<except>{###} : sel @ | __SELECT__ : uni @ \ {###}</union>{###} : ins @ \ {###}</intersect>{###} : mis @ \ {###}</minus>{###} : exc @ \ {###}</except>{###} N : uni @ | __UNION_SELECT__ : ins @ | __INTERSECT_SELECT__ : mis @ | __MINUS_SELECT__ : exc @ | __EXCEPT_SELECT__ 1 : tabp2 @ | __TAB_CONSTRAINT__ : end @ | {;;;} __DEF_PATH__ __NAME_EQ_SELECT__ 11 : tartab @ | __NAME__ : eq @ | = : with @ CAN_SKIP | __WITH_AS_SELECT__ : sel @ | __SELECT__ 0N : uni @ | __UNION_SELECT__ : ins @ + __INTERSECT_SELECT__ : mis @ + __MINUS_SELECT__ : exc @ + __EXCEPT_SELECT__ 1 : end @ | ; ------------------------------------------------------------------- 1 : eq @ | __NAME_EQ_SELECT__{:::}|| : tartab @ \ __NAME__ : eq @ \ {:::} 1 : tartab @ \ __NAME__ : eq @ | = : with @ | __WITH_AS_SELECT__ : sel @ | __SELECT__ : end @ | {;;;} ------------------------------------------------------------------- 1 : eq @ | __NAME_EQ_SELECT__{:::}|| : tartab @ \ __NAME__ : eq @ \ {:::} 1 : tartab @ \ __NAME__ : eq @ | = : with @ | __WITH_AS_SELECT__ : uni @ \ {###}<union>{###} : ins @ \ {###}<intersect>{###} : mis @ \ {###}<minus>{###} : exc @ \ {###}<except>{###} : sel @ | __SELECT__ : uni @ \ {###}</union>{###} : ins @ \ {###}</intersect>{###} : mis @ \ {###}</minus>{###} : exc @ \ {###}</except>{###} N : uni @ | __UNION_SELECT__ : ins @ | __INTERSECT_SELECT__ : mis @ | __MINUS_SELECT__ : exc @ | __EXCEPT_SELECT__ 1 : end @ | {;;;} __DEF_PATH__ __INSERT_TABLE_SELECT__ 11 : inst @ | insert : into @ | into : overw @ + overwrite 11 : tartab @ | __NAME__ ++ : schema @ | __NAME__ : pp @ | . : tartab2 @ | __NAME__ : part @ CAN_SKIP | __PARTITION_4_INSERT__ 11 : collist @ CAN_SKIP | __COL_LIST__ : with @ CAN_SKIP | __WITH_AS_SELECT__ : sel @ | __SELECT__ 0N : uni @ | __UNION_SELECT__ : ins @ + __INTERSECT_SELECT__ : mis @ + __MINUS_SELECT__ : exc @ + __EXCEPT_SELECT__ 1 : end @ | ; ------------------------------------------------------------------- 1 : inst @ | __INSERT_TABLE_SELECT__{:::} : schema @ \ __NAME__ : inst @ \ || : tartab @ \ __NAME__ : tartab2 @ \ __NAME__ : inst @ \ {:::} 1 : inst @ \ insert : into @ | into : overw @ | overwrite : tartab @ | __NAME__ : schema @ | __NAME__ : pp @ \ . : tartab2 @ \ __NAME__ : part @ | __PARTITION_4_INSERT__ : collist @ | __COL_LIST__ : with @ | __WITH_AS_SELECT__ : sel @ | __SELECT__ : end @ | {;;;} ------------------------------------------------------------------- 1 : inst @ | __INSERT_TABLE_SELECT__{:::} : schema @ \ __NAME__ : inst @ \ || : tartab @ \ __NAME__ : tartab2 @ \ __NAME__ : inst @ \ {:::} 1 : inst @ \ insert : into @ | into : overw @ | overwrite : tartab @ | __NAME__ : schema @ | __NAME__ : pp @ \ . : tartab2 @ \ __NAME__ : part @ | __PARTITION_4_INSERT__ : collist @ | __COL_LIST__ : with @ | __WITH_AS_SELECT__ : uni @ \ {###}<union>{###} : ins @ \ {###}<intersect>{###} : mis @ \ {###}<minus>{###} : exc @ \ {###}<except>{###} : sel @ | __SELECT__ : uni @ \ {###}</union>{###} : ins @ \ {###}</intersect>{###} : mis @ \ {###}</minus>{###} : exc @ \ {###}</except>{###} N : uni @ | __UNION_SELECT__ : ins @ | __INTERSECT_SELECT__ : mis @ | __MINUS_SELECT__ : exc @ | __EXCEPT_SELECT__ 1 : end @ | {;;;} __DEF_PATH__ __FROM_INSERT__ 1 : frm @ %__IF_KW__ | from : x8 @ | __TABLE_NAME__ : x9 @ + __SUB_SELECT__ N : xa @ CAN_SKIP | __JOIN_TABLE__ N : ins @ | __INSERT_SELECT_4_FROM_INSERT__ 1 : end @ | ; ------------------------------------------------------------------------ 1 : frm @ | __FROM_INSERT__{:::} 1 : frm @ \ from : x8 @ | __TABLE_NAME__ : x9 @ | __SUB_SELECT__ N : xa @ | __JOIN_TABLE__ N : ins @ | __INSERT_SELECT_4_FROM_INSERT__ 1 : end @ | {;;;} __DEF_PATH__ __INSERT_INTO_VALUES__ 1 : ins @ | insert : x2 @ | into 1 : tartab @ | __NAME__ + : schema @ | __NAME__ : PP @ | . : tartab2 @ | __NAME__ : part @ CAN_SKIP | __PARTITION_4_INSERT__ 1 : values @ | values : x5 @ | __SUB_PATH_4_EXPR__ 1 : x6 @ | ; ------------------------------------------------------ 1 : ins @ | __INSERT_INTO_VALUES__{:::} : schema @ \ __NAME__ : ins @ \ || : tartab @ \ __NAME__ : tartab2 @ \ __NAME__ : ins @ \ {:::} 1 : ins @ \ insert : x2 @ | into : tartab @ | __NAME__ : schema @ \ __NAME__ : PP @ \ . : tartab2 @ \ __NAME__ : part @ | __PARTITION_4_INSERT__ : values @ | values : x5 @ | __SUB_PATH_4_EXPR__ : x6 @ | {;;;} __DEF_PATH__ __UPDATE_TABLE__ 11 : upd @ | update 11 : tartab @ | __NAME__ ++ : schema @ | __NAME__ : pp @ | . : tartab2 @ | __NAME__ 11 : as @ CAN_SKIP | as : otrnm @ 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 @ | ; ---------------------------------------------------------------- 1 : upd @ | __UPDATE_TABLE__{:::} : schema @ \ __NAME__ : upd @ \ || : tartab @ \ __NAME__ : tartab2 @ \ __NAME__ : upd @ \ {:::} 1 : upd @ \ update : tartab @ | __NAME__ : schema @ | __NAME__ : pp @ \ . : tartab2 @ \ __NAME__ : as @ | as : otrnm @ | __NAME__ : set @ | set N : x5 @ | __PATH_4_EXPR__ e : x6 @ | , 1 : xb @ | __WHERE__ 1 : xfh @ | {;;;} ---------------------------------------------------------------- 1 : upd @ | __UPDATE_TABLE__{:::} : schema @ \ __NAME__ : upd @ \ || : tartab @ \ __NAME__ : tartab2 @ \ __NAME__ : upd @ \ {:::} 1 : upd @ \ update : tartab @ | __NAME__ : schema @ | __NAME__ : pp @ \ . : tartab2 @ \ __NAME__ : as @ | as : otrnm @ | __NAME__ : set @ | set N : x5 @ | __PATH_4_EXPR__ e : x6 @ | , 1 : x7 @ | from : x8 @ | __TABLE_NAME__ : x9 @ | __SUB_SELECT__ N : xa @ | __JOIN_TABLE__ 1 : xb @ | __WHERE__ : xc @ | __GROUP_BY__ : xfh @ | {;;;} __DEF_PATH__ __MERGE_TABLE__ 1 : meg @ | merge : x2 @ | into 1 : tartab @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : tartab2 @ | __NAME__ 1 : as @ CAN_SKIP | as : otrnm @ 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 @ | ; ----------------------------------------------------------------- 1 : meg @ | __MERGE_TABLE__{:::} : schema @ \ __NAME__ : meg @ \ || : tartab @ \ __NAME__ : tartab2 @ \ __NAME__ : meg @ \ {:::} 1 : meg @ \ merge : x2 @ | into : tartab @ | __NAME__ : schema @ | __NAME__ : pp @ \ . : tartab2 @ \ __NAME__ : as @ | as : otrnm @ | __NAME__ : using @ | using : x8 @ | __TABLE_NAME__ : x9 @ | __SUB_SELECT__ : on1 @ | on : on2 @ | ( : on3 @ | __PATH_4_EXPR__ : on4 @ | ) : mu @ | __MERGE_UPDATE__ : mi @ | __MERGE_INSERT__ : xfh @ | {;;;} __//__ ------------------------------------------- __//__ For PROCEDURE or FUNCTION other steps __//__ ------------------------------------------- __DEF_PATH__ __DROP_PROCEDURE__ 1 : drp @ %__IF_KW__ | drop : prc @ %__IF_KW__ | procedure : if @ %__IF_KW__ CAN_SKIP | if : exi @ %__IF_KW__ CAN_SKIP | exists 1 : pron @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : pron2 @ | __NAME__ : fh @ | ; ------------------------------------------------------------------- 1 : drp @ | __DROP_PROCEDURE__{:::} : schema @ \ __NAME__ : drp @ \ || : pron @ \ __NAME__ : pron2 @ \ __NAME__ : fh @ \ {;;;} 1 : drp @ \ drop : prc @ | procedure : if @ | if : exi @ | exists : pron @ | __NAME__ : schema @ | __NAME__ : pp @ | . : pron2 @ | __NAME__ : fh @ | {;;;} __DEF_PATH__ __DROP_FUNCTION__ 1 : drp @ %__IF_KW__ | drop : fun @ %__IF_KW__ | function : if @ %__IF_KW__ CAN_SKIP | if : exi @ %__IF_KW__ CAN_SKIP | exists 1 : funn @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : funn2 @ | __NAME__ : fh @ | ; ------------------------------------------------------------------- 1 : drp @ | __DROP_FUNCTION__{:::} : schema @ \ __NAME__ : drp @ \ || : funn @ \ __NAME__ : funn2 @ \ __NAME__ : fh @ \ {;;;} 1 : drp @ \ drop : fun @ | function : if @ | if : exi @ | exists : funn @ | __NAME__ : schema @ | __NAME__ : pp @ | . : funn2 @ | __NAME__ : fh @ | {;;;} __DEF_PATH__ __CREATE_PROCEDURE_HEAD__ 1 : cre @ %__IF_KW__ | create : alt @ %__IF_KW__ + alter : or @ CAN_SKIP %__IF_KW__ | or : rep @ CAN_SKIP %__IF_KW__ | replace : prc @ %__IF_KW__ | procedure : if @ CAN_SKIP %__IF_KW__ | if : not @ CAN_SKIP %__IF_KW__ | not : exi @ CAN_SKIP %__IF_KW__ | exists 1 : pron @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : pron2 @ | __NAME__ 1 : lkh @ CAN_SKIP | ( N : in @ CAN_SKIP %__IF_KW__ | in : out @ CAN_SKIP %__IF_KW__ | out : io @ CAN_SKIP %__IF_KW__ | inout : parm @ CAN_SKIP | __NAME__ : in2 @ CAN_SKIP %__IF_KW__ | in : out2 @ CAN_SKIP %__IF_KW__ | out : io2 @ CAN_SKIP %__IF_KW__ | inout : dat @ CAN_SKIP | __DATA_TYPE__ : dfl @ CAN_SKIP | default : exp3 @ CAN_SKIP | __PATH_4_EXPR__ e : dh @ CAN_SKIP | , 1 : rkh @ CAN_SKIP | ) : is @ | is : as @ + as : dla @ CAN_SKIP | declare N : var @ | __NAME__ : eq @ CAN_SKIP | = : eq2 @ + := : exp @ | __PATH_4_EXPR__ : fh @ | ; 1 : beg @ | begin ------------------------------------------------------------------- 1 : cre @ | __CREATE_PROCEDURE_HEAD__{:::} : schema @ \ __NAME__ : cre @ \ || : pron @ \ __NAME__ : pron2 @ \ __NAME__ : cre @ \ {:::} 1 : cre @ \ create : or @ | or : rep @ | replace : prc @ | procedure : if @ | if : not @ | not : exi @ | exists : pron @ | __NAME__ : schema @ | __NAME__ : pp @ | . : pron2 @ | __NAME__ : lkh @ | ( N : in @ | in : out @ | out : io @ | inout : parm @ | __NAME__ : in2 @ | in : out2 @ | out : io2 @ | inout : dat @ | __DATA_TYPE__ : dfl @ | default : exp3 @ | __PATH_4_EXPR__ e : dh @ | , 1 : rkh @ | ) : is @ | is : as @ | as : dla @ | declare N : var @ | __NAME__ : eq @ | = : eq2 @ | := : exp @ | __PATH_4_EXPR__ : fh @ | ; 1 : beg @ | begin : beg @ | {;;;} __DEF_PATH__ __CREATE_FUNCTION_HEAD__ 1 : cre @ %__IF_KW__ | create : alt @ %__IF_KW__ + alter : or @ CAN_SKIP %__IF_KW__ | or : rep @ CAN_SKIP %__IF_KW__ | replace : fun @ %__IF_KW__ | function : if @ CAN_SKIP %__IF_KW__ | if : not @ CAN_SKIP %__IF_KW__ | not : exi @ CAN_SKIP %__IF_KW__ | exists 1 : funn @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : funn2 @ | __NAME__ 1 : lkh @ CAN_SKIP | ( N : in @ CAN_SKIP %__IF_KW__ | in : out @ CAN_SKIP %__IF_KW__ | out : io @ CAN_SKIP %__IF_KW__ | inout : parm @ CAN_SKIP | __NAME__ : in2 @ CAN_SKIP %__IF_KW__ | in : out2 @ CAN_SKIP %__IF_KW__ | out : io2 @ CAN_SKIP %__IF_KW__ | inout : dat @ CAN_SKIP | __DATA_TYPE__ : dfl @ CAN_SKIP | default : exp3 @ CAN_SKIP | __PATH_4_EXPR__ e : dh @ CAN_SKIP | , 1 : rkh @ CAN_SKIP | ) : ret @ CAN_SKIP | returns : sof @ CAN_SKIP | setof : dat2 @ CAN_SKIP | __DATA_TYPE__ : tdat @ + __TABLE_TYPE__ : lag @ CAN_SKIP | language : lagn @ CAN_SKIP | __NAME__ : imm @ CAN_SKIP | immutable : sta @ + stable : vol @ + volatile : is @ | is : as @ + as : dla @ CAN_SKIP | declare N : var @ | __NAME__ : eq @ CAN_SKIP | = : eq2 @ + := : exp @ | __PATH_4_EXPR__ : fh @ | ; 1 : beg @ | begin ------------------------------------------------------------------- 1 : cre @ | __CREATE_FUNCTION_HEAD__{:::} : schema @ \ __NAME__ : cre @ \ || : funn @ \ __NAME__ : funn2 @ \ __NAME__ : cre @ \ {:::} 1 : cre @ | create : alt @ | alter : or @ | or : rep @ | replace : fun @ | function : if @ | if : not @ | not : exi @ | exists : funn @ | __NAME__ : schema @ | __NAME__ : pp @ \ . : funn2 @ \ __NAME__ : lkh @ | ( N : in @ | in : out @ | out : io @ | inout : parm @ | __NAME__ : in2 @ | in : out2 @ | out : io2 @ | inout : dat @ | __DATA_TYPE__ : dfl @ | default : exp3 @ | __PATH_4_EXPR__ e : dh @ | , 1 : rkh @ | ) : ret @ | returns : sof @ | setof : dat2 @ | __DATA_TYPE__ : tdat @ | __TABLE_TYPE__ : lag @ | language : lagn @ | __NAME__ : imm @ | immutable : sta @ | stable : vol @ | volatile : is @ | is : as @ | as : dla @ | declare N : var @ | __NAME__ : eq @ | = : eq2 @ | := : exp @ | __PATH_4_EXPR__ : fh @ | ; 1 : beg @ | begin : beg @ | {;;;} __DEF_PATH__ __SELECT_INTO__ 1 : sel @ | select N : colexp @ | __PATH_4_EXPR__ : as @ CAN_SKIP | as : colname @ CAN_SKIP | __NAME__ e : colspl @ | , 1 : into @ | into N : var @ CAN_SKIP | __NAME__ e : dh @ | , 1 : frm @ | from : tab @ | __TABLE_NAME__ : sbs @ + __SUB_SELECT__ N : jt @ CAN_SKIP | __JOIN_TABLE__ 1 : wh @ CAN_SKIP | __WHERE__ : fh @ | ; --------------------------------------------------------------- 1 : sel @ | __SELECT_INTO__{:::} N : var @ \ __NAME__ e : dh @ \ || 1 : sel @ \ {:::} : sel @ \ select N : colexp @ | __PATH_4_EXPR__ : as @ | as : colname @ | __NAME__ e : colspl @ | , 1 : into @ | into N : var @ | __NAME__ e : dh @ | , 1 : frm @ | from : tab @ | __TABLE_NAME__ : sbs @ | __SUB_SELECT__ N : jt @ | __JOIN_TABLE__ 1 : wh @ | __WHERE__ : fh @ | {;;;} __DEF_PATH__ __JUST_SELECT__ 0011 : with @ | __WITH_AS_SELECT__ 1111 : sel @ | __SELECT__ 0N0N : uni @ | __UNION_SELECT__ : ins @ + __INTERSECT_SELECT__ : mis @ + __MINUS_SELECT__ : exc @ + __EXCEPT_SELECT__ 1 1 : end @ | ; ------------------------------------------------------------------- 1 : sel @ | __JUST_SELECT__{:::} 1 : sel @ \ __SELECT__ : end @ \ {;;;} ------------------------------------------------------------------- 1 : sel @ | __JUST_SELECT__{:::} 1 : uni @ \ {###}<union>{###} : ins @ \ {###}<intersect>{###} : mis @ \ {###}<minus>{###} : exc @ \ {###}<except>{###} : sel @ | __SELECT__ : uni @ \ {###}</union>{###} : ins @ \ {###}</intersect>{###} : mis @ \ {###}</minus>{###} : exc @ \ {###}</except>{###} N : uni @ | __UNION_SELECT__ : ins @ | __INTERSECT_SELECT__ : mis @ | __MINUS_SELECT__ : exc @ | __EXCEPT_SELECT__ 1 : end @ | {;;;} ------------------------------------------------------------------- 1 : with @ | __JUST_SELECT__{:::} 1 : with @ \ __WITH_AS_SELECT__ : sel @ | __SELECT__ : end @ | {;;;} ------------------------------------------------------------------- 1 : with @ | __JUST_SELECT__{:::} 1 : with @ \ __WITH_AS_SELECT__ : uni @ \ {###}<union>{###} : ins @ \ {###}<intersect>{###} : mis @ \ {###}<minus>{###} : exc @ \ {###}<except>{###} : sel @ | __SELECT__ : uni @ \ {###}</union>{###} : ins @ \ {###}</intersect>{###} : mis @ \ {###}</minus>{###} : exc @ \ {###}</except>{###} N : uni @ | __UNION_SELECT__ : ins @ | __INTERSECT_SELECT__ : mis @ | __MINUS_SELECT__ : exc @ | __EXCEPT_SELECT__ 1 : end @ | {;;;} __DEF_PATH__ __SET_CONF_VAR__ 1 : set @ %__IF_KW__ | set : at @ CAN_SKIP | @ : p1 @ | __NAME_WITH_KW__ N : pp @ CAN_SKIP | . : p2 @ CAN_SKIP | __NAME_WITH_KW__ 1 : dy @ | = : dy2 @ + := : p3 @ | __NAME_WITH_KW__ : p4 @ + __PATH_4_EXPR__ : fh @ | ; ------------------------------------------------------------------- 1 : set @ | __SET_CONF_VAR__{:::} 1 : set @ \ set : at @ | @ : p1 @ | __NAME_WITH_KW__ N : pp @ \ . : p2 @ \ __NAME_WITH_KW__ 1 : dy @ | = : dy2 @ | := : p3 @ | __NAME_WITH_KW__ : p4 @ | __PATH_4_EXPR__ : fh @ | {;;;} __DEF_PATH__ __RESET_CONF__ 1 : rst @ %__IF_KW__ | reset : p1 @ | __NAME_WITH_KW__ N : pp @ CAN_SKIP | . : p2 @ CAN_SKIP | __NAME_WITH_KW__ 1 : dy @ CAN_SKIP | = : dy2 @ + := : p3 @ CAN_SKIP | __NAME_WITH_KW__ : p4 @ + __PATH_4_EXPR__ : fh @ | ; ------------------------------------------------------------------- 1 : rst @ | __RESET_CONF__{:::} 1 : rst @ \ reset : p1 @ | __NAME_WITH_KW__ N : pp @ \ . : p2 @ \ __NAME_WITH_KW__ 1 : dy @ | = : dy2 @ | := : p3 @ | __NAME_WITH_KW__ : p4 @ | __PATH_4_EXPR__ : fh @ | {;;;} __DEF_PATH__ __DECLARE_VAR__ 011 : dcl @ %__IF_KW__ | declare 1 : var @ | __NORM_NAME__ : dat @ CAN_SKIP | __DATA_TYPE__ 0 : eq @ | = : eq2 @ + := : dfl @ + default 0 : exp @ | __PATH_4_EXPR__ : fh @ | ; ------------------------------------------------------ 1 : var @ | __DECLARE_VAR__{:::} : var @ \ __NORM_NAME__ : var @ \ {:::} 1 : var @ \ __NORM_NAME__ : dat @ | __DATA_TYPE__ : eq @ | = : eq2 @ | := : dfl @ | default : exp @ | __PATH_4_EXPR__ : fh @ | {;;;} ------------------------------------------------------ 1 : var @ | __DECLARE_VAR__{:::} : var @ \ __NORM_NAME__ : var @ \ {:::} 1 : var @ \ __NORM_NAME__ : dat @ | __DATA_TYPE__ : fh @ | {;;;} ------------------------------------------------------ 1 : var @ | __DECLARE_VAR__{:::} : var @ \ __NORM_NAME__ : var @ \ {:::} 1 : dcl @ \ declare : var @ | __NORM_NAME__ : dat @ | __DATA_TYPE__ : eq @ | = : eq2 @ | := : dfl @ | default : exp @ | __PATH_4_EXPR__ : fh @ | {;;;} __DEF_PATH__ __DECLARE_MYSQL_CURSOR__ 1 : dcl @ %__IF_KW__ | declare : cnm @ | __NORM_NAME__ : cur @ %__IF_KW__ | cursor : for @ %__IF_KW__ | for : dat @ | __SELECT__ : fh @ | ; -------------------------------------------------- 1 : dcl @ | __MYSQL_CURSOR__{:::} : cnm @ \ __NORM_NAME__ : dcl @ \ {:::} 1 : dcl @ \ declare : cnm @ | __NORM_NAME__ : cur @ | cursor : for @ | for : dat @ | __SELECT__ : fh @ | {;;;} __DEF_PATH__ __DECLARE_ORACLE_CURSOR__ 1 : cur @ %__IF_KW__ | cursor : cnm @ | __NORM_NAME__ : lkh @ CAN_SKIP | ( N : var @ CAN_SKIP | __NORM_NAME__ : dat @ CAN_SKIP | __DATA_TYPE__ e : dh @ CAN_SKIP | , 1 : rkh @ CAN_SKIP | ) : is @ %__IF_KW__ | is : sel @ | __SELECT__ : fh @ | ; -------------------------------------------------- 1 : cur @ | __ORACLE_CURSOR__{:::} : cnm @ \ __NORM_NAME__ : cur @ \ {:::} 1 : cur @ \ cursor : cnm @ | __NORM_NAME__ : lkh @ \ ( N : var @ | __NORM_NAME__ : dat @ | __DATA_TYPE__ e : dh @ | , 1 : rkh @ | ) : is @ | is : sel @ | __SELECT__ : fh @ \ {;;;} __DEF_PATH__ __FETCH__ 1 : fth @ %__IF_KW__ | fetch : cur @ | __NORM_NAME__ : int @ %__IF_KW__ | into N : var @ | __NORM_NAME__ e : dh @ | , 1 : fh @ | ; -------------------------------------------------------------- 1 : fth @ | __FETCH__{:::} 1 : fth @ \ fetch : cur @ | __NORM_NAME__ : int @ | into N : var @ | __NORM_NAME__ e : dh @ | , 1 : fh @ \ {;;;} __DEF_PATH__ __CONTROL_IF__ 1 : if @ %__IF_KW__ | if : eif @ %__IF_KW__ + elseif : eif2 @ %__IF_KW__ + elsif : exp @ | __PATH_4_EXPR__ : thn @ %__IF_KW__ | then + : end @ %__IF_KW__ | end : if2 @ %__IF_KW__ | if : fh @ | ; ------------------------------------------------------------------- 1 : if @ | __IF__{:::} : eif @ | __ELSEIF__{:::} : eif2 @ | __ELSEIF__{:::} : end @ | __END_IF__{:::} 1 : if @ \ if : eif @ \ elseif : eif2 @ \ elsif : exp @ | __PATH_4_EXPR__ : thn @ | then : end @ \ end : if2 @ | if : if @ \ {;;;} : eif @ \ {;;;} : eif2 @ \ {;;;} : fh @ \ {;;;} __DEF_PATH__ __CONTROL_CASE_WHEN__ 1 : case @ %__IF_KW__ | case : var @ CAN_SKIP | __PATH_4_EXPR__ : when @ %__IF_KW__ | when : exp @ | __PATH_4_EXPR__ : thn @ %__IF_KW__ | then + : end @ %__IF_KW__ | end : cas2 @ %__IF_KW__ | case : fh @ | ; ------------------------------------------------------------------- 1 : case @ | __CASE_WHEN__{:::} : end @ | __END_CASE__{:::} 1 : case @ \ case : var @ | __PATH_4_EXPR__ : when @ | when : exp @ | __PATH_4_EXPR__ : thn @ | then : end @ \ end : cas2 @ | case : case @ \ {;;;} : fh @ \ {;;;} __DEF_PATH__ __EXCEPTION_WHEN__ 1 : ecp @ %__IF_KW__ | exception : when @ %__IF_KW__ | when : errn @ | __NORM_NAME__ : thn @ %__IF_KW__ | then ------------------------------------------------------------------- 1 : ecp @ | __EXCEPTION_WHEN__{:::} 1 : ecp @ \ exception : when @ | when : errn @ | __NORM_NAME__ : thn @ | then : ecp @ \ {;;;} __DEF_PATH__ __CONTROL_WHEN__ 1 : when @ %__IF_KW__ | when : exp @ | __PATH_4_EXPR__ : thn @ %__IF_KW__ | then ------------------------------------------------------------------- 1 : when @ | __WHEN__{:::} 1 : when @ \ when : exp @ | __PATH_4_EXPR__ : thn @ | then : when @ \ {;;;} __DEF_PATH__ __CONTROL_ELSE__ 1 : else @ %__IF_KW__ | else ------------------------------------------------------------------- 1 : else @ | __ELSE__{:::}else{;;;} __DEF_PATH__ __CONTROL_MYSQL_WHILE__ 1 : whi @ %__IF_KW__ | while : exp @ | __PATH_4_EXPR__ : do @ %__IF_KW__ | do + : end @ %__IF_KW__ | end : whi2 @ %__IF_KW__ | while : fh @ | ; ------------------------------------------------------------------- 1 : whi @ | __MYSQL_WHILE__{:::} : end @ | __END_MYSQL_WHILE__{:::} 1 : whi @ \ while : exp @ | __PATH_4_EXPR__ : do @ | do : end @ \ end : whi2 @ | while : do @ \ {;;;} : fh @ \ {;;;} __DEF_PATH__ __CONTROL_ORACLE_WHILE__ 1 : whi @ %__IF_KW__ | while : exp @ | __PATH_4_EXPR__ : lop @ %__IF_KW__ | loop ------------------------------------------------------------------- 1 : whi @ | __ORACLE_WHILE__{:::} 1 : whi @ \ while : exp @ | __PATH_4_EXPR__ : lop @ | loop : lop @ \ {;;;} __DEF_PATH__ __CONTROL_ORACLE_FOR__ 1 : for @ %__IF_KW__ | for : var @ | __NORM_NAME__ 1 : in @ %__IF_KW__ | in : rev @ %__IF_KW__ CAN_SKIP | reverse : i1 @ | __INT__ : dd @ | .. : i2 @ | __INT__ + : in2 @ | in : lkh @ | ( : sel @ | __SELECT__ : rkh @ | ) + : in3 @ | in : cur @ | __NORM_NAME__ : parm @ | __SUB_PATH_4_EXPR__ 1 : lop @ | loop ------------------------------------------------------------------- 1 : for @ | __ORACLE_FOR__{:::} : var @ \ __NORM_NAME__ : var @ \ {:::} 1 : for @ \ for : var @ | __NORM_NAME__ : in @ | in : rev @ | reverse : i1 @ | __INT__ : dd @ \ .. : i2 @ \ __INT__ : in2 @ | in : lkh @ | ( : sel @ \ __SELECT__ : rkh @ \ ) : in3 @ | in : cur @ | __NORM_NAME__ : parm @ | __SUB_PATH_4_EXPR__ : lop @ | loop : lop @ \ {;;;} __DEF_PATH__ __CONTROL_REPEAT__ 1 : rep @ %__IF_KW__ | repeat + : unt @ %__IF_KW__ | until : exp @ | __PATH_4_EXPR__ : end @ %__IF_KW__ | end : rep2 @ %__IF_KW__ | repeat : fh @ | ; ------------------------------------------------------------------- 1 : rep @ | __REPEAT__{:::} : unt @ | __UNTIL__{:::} 1 : rep @ \ repeat : unt @ \ until : exp @ | __PATH_4_EXPR__ : end @ | end : rep2 @ | repeat : rep @ \ {;;;} : fh @ \ {;;;} __DEF_PATH__ __CONTROL_MYSQL_LOOP__ 1 : lnm @ | __NAME__ : mh @ | : : lop @ %__IF_KW__ | loop + : lev @ %__IF_KW__ | leave : lnm1 @ | __NAME__ : fh1 @ | ; + : end @ %__IF_KW__ | end : lop2 @ %__IF_KW__ | loop : lnm2 @ | __NAME__ : fh @ | ; ------------------------------------------------------------------- 1 : lnm @ | __MYSQL_LOOP__{:::} : lev @ | __LEAVE__{:::} : end @ | __END_MYSQL_LOOP__{:::} : lnm @ \ __NAME__ : lnm1 @ \ __NAME__ : lnm2 @ \ __NAME__ : lnm @ \ {:::} : lnm1 @ \ {:::} : lnm2 @ \ {:::} 1 : lnm @ \ __NAME__ : mh @ | : : lop @ | loop : lev @ \ leave : lnm1 @ | __NAME__ : end @ \ end : lop2 @ | loop : lnm2 @ | __NAME__ : lnm @ \ {;;;} : fh1 @ \ {;;;} : fh @ \ {;;;} __DEF_PATH__ __CONTROL_ORACLE_LOOP__ 1 : lop @ %__IF_KW__ | loop + : exi @ %__IF_KW__ | exit : ctn @ %__IF_KW__ + continue : whe @ %__IF_KW__ | when : exp @ | __PATH_4_EXPR__ : fh @ | ; + : end @ %__IF_KW__ | end : lop2 @ %__IF_KW__ | loop : fh2 @ | ; ------------------------------------------------------------------- 1 : lop @ | __ORACLE_LOOP__{:::} : exi @ | __EXIT_LOOP__{:::} : ctn @ | __CONTINUE_LOOP__{:::} : end @ | __END_ORACLE_LOOP__{:::} 1 : lop @ \ loop : exi @ \ exit : ctn @ \ continue : whe @ | when : exp @ | __PATH_4_EXPR__ : lop @ \ {;;;} : fh @ \ {;;;} : fh2 @ \ {;;;} __DEF_PATH__ __EXECUTE__ 1 : exe @ %__IF_KW__ | execute : imm @ %__IF_KW__ CAN_SKIP | immediate : exp @ | __PATH_4_EXPR__ : fh @ | ; ------------------------------------------------------------------------ 1 : exe @ | __EXECUTE__{:::} 1 : exe @ \ execute : imm @ | immediate : exp @ | __PATH_4_EXPR__ : fh @ \ {;;;} __DEF_PATH__ __OPEN__ 1 : opn @ %__IF_KW__ | open : exp @ | __PATH_4_EXPR__ : fh @ | ; ------------------------------------------------------------------------ 1 : opn @ | __OPEN__{:::} 1 : opn @ \ open : exp @ | __PATH_4_EXPR__ : fh @ \ {;;;} __DEF_PATH__ __CLOSE__ 1 : clo @ %__IF_KW__ | close : exp @ | __PATH_4_EXPR__ : fh @ | ; ------------------------------------------------------------------------ 1 : clo @ | __CLOSE__{:::} 1 : clo @ \ close : exp @ | __PATH_4_EXPR__ : fh @ \ {;;;} __DEF_PATH__ __RUN_PROC_FUN__ 001 : call @ %__IF_KW__ | call 0NN : schema @ | __NORM_NAME__ 0 : pp @ | . 111 : fun @ | __NORM_NAME__ : lkh @ | ( NNN : parm @ CAN_SKIP | __PATH_4_EXPR__ eee : dh @ CAN_SKIP | , 111 : rkh @ | ) : fh @ | ; ---------------------------------------------------- 1 : fun @ | __RUN_PROC_FUN__{:::} 1 : fun @ \ __NORM_NAME__ : lkh @ \ ( N : parm @ | __PATH_4_EXPR__ e : dh @ | , 1 : rkh @ | ) : fh @ | {;;;} ---------------------------------------------------- 1 : fun @ | __RUN_PROC_FUN__{:::} N : schema @ \ __NORM_NAME__ : pp @ \ . 1 : fun @ \ __NORM_NAME__ : lkh @ \ ( N : parm @ | __PATH_4_EXPR__ e : dh @ | , 1 : rkh @ | ) : fh @ | {;;;} ---------------------------------------------------- 1 : fun @ | __RUN_PROC_FUN__{:::} 1 : call @ \ call N : schema @ | __NORM_NAME__ : pp @ \ . 1 : fun @ \ __NORM_NAME__ : lkh @ \ ( N : parm @ | __PATH_4_EXPR__ e : dh @ | , 1 : rkh @ | ) : fh @ | {;;;} __DEF_PATH__ __COMMIT__ 1 : cmt @ | commit : fh @ | ; ------------------------------------------------------------ 1 : cmt @ | __COMMIT__{:::}commit{;;;} __DEF_PATH__ __END__ 1 : end @ | end : fh @ | ; ------------------------------------------------------------ 1 : end @ | __END__{:::}end{;;;} __DEF_PATH__ __ALTER_TABLE__ 1 : alt @ %__IF_KW__ | alter : tab @ %__IF_KW__ | table : any @ | __ANY__ : fh @ | ; ------------------------------------------------------------ 1 : alt @ | __ALTER_TABLE__{:::} 1 : alt @ \ alter : tab @ | table N : any @ | __ANY__ 1 : fh @ | {;;;} __DEF_PATH__ __GRANT__ 1 : gra @ %__IF_KW__ | grant : any @ | __ANY__ : fh @ | ; ------------------------------------------------------------ 1 : gra @ | __GRANT__{:::} 1 : gra @ \ grant N : any @ | __ANY__ 1 : fh @ \ {;;;} __DEF_PATH__ __ANALYZE__ 1 : anl @ %__IF_KW__ | analyze : any @ | __ANY__ : fh @ | ; ------------------------------------------------------------ 1 : anl @ | __ANALYZE__{:::} 1 : anl @ \ analyze N : any @ | __ANY__ 1 : fh @ \ {;;;} __DEF_PATH__ __COMMENT_ON__ 1 : anl @ %__IF_KW__ | comment : on @ %__IF_KW__ | on : any @ | __ANY__ : fh @ | ; ------------------------------------------------------------ 1 : anl @ | __COMMENT_ON__{:::} 1 : anl @ \ comment : on @ | on N : any @ | __ANY__ 1 : fh @ \ {;;;} __DEF_PATH__ __VACUUM__ 1 : vac @ %__IF_KW__ | vacuum : any @ | __ANY__ : fh @ | ; ------------------------------------------------------------ 1 : vac @ | __VACUUM__{:::} 1 : vac @ \ vacuum N : any @ | __ANY__ 1 : fh @ \ {;;;} __//__ ================================================================== __DEF_SUB_PATH__ __INSERT_SELECT_4_FROM_INSERT__ 1 : ins @ %__IF_KW__ | insert : into @ | into : overw @ + overwrite 1 : tartab @ | __NAME__ + : schema @ | __NAME__ : pp @ | . : tartab2 @ | __NAME__ : part @ CAN_SKIP | __PARTITION_4_INSERT__ 1 : x1 @ | select : x2 @ CAN_SKIP | distinct N : x3 @ | __PATH_4_EXPR__ : as @ CAN_SKIP | as : colname @ CAN_SKIP | __NAME__ e : colspl @ | , 1 : xb @ CAN_SKIP | __WHERE__ -------------------------------------------------------------- 1 : ins @ | {:::} : schema @ \ __NAME__ : ins @ \ || : tartab @ \ __NAME__ : tartab2 @ \ __NAME__ : ins @ \ {:::} 1 : ins @ \ insert : into @ | into : overw @ | overwrite 1 : tartab @ | __NAME__ : schema @ | __NAME__ : pp @ \ . : tartab2 @ \ __NAME__ : part @ | __PARTITION_4_INSERT__ 1 : x1 @ | select : x2 @ | distinct N : x3 @ | __PATH_4_EXPR__ : as @ | as : colname @ | __NAME__ e : colspl @ | , 1 : xb @ | __WHERE__ __DEF_SUB_PATH__ __START_WITH_CONNECT__ 01 : sta @ %__IF_KW__ | start 0 : wth @ %__IF_KW__ | with 0 : swp @ | __PATH_4_EXPR__ 11 : cnn @ %__IF_KW__ | connect : by @ %__IF_KW__ | by : ncy @ CAN_SKIP %__IF_KW__ | nocycle : prr1 @ CAN_SKIP %__IF_KW__ | prior 11 : col1 @ | __NAME__ ++ : sch1 @ | __NAME__ : p1 @ | . : col2 @ | __NAME__ 11 : dy @ | = : prr2 @ CAN_SKIP %__IF_KW__ | prior 11 : col3 @ | __NAME__ ++ : sch2 @ | __NAME__ : p2 @ | . : col4 @ | __NAME__ 11 : cnt @ CAN_SKIP | __PATH_4_EXPR__ __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__ 11 : wit @ | with : rec @ CAN_SKIP | recursive : wtab @ | __NAME__ : as @ | as : lkh @ | ( : sel @ | __SELECT__ 0N : uni @ | __UNION_SELECT__ : ins @ + __INTERSECT_SELECT__ : mis @ + __MINUS_SELECT__ : exc @ + __EXCEPT_SELECT__ 1 : rkh @ | ) ------------------------------------------------------------------------ 1 : wit @ | {###}<withas>{###} : rec @ STRING \ recursive : wtab @ \ __NAME__ : as @ \ {###}<as/>{###} : sel @ | __SELECT__ : rkh @ | {###}</withas>{###} ------------------------------------------------------------------------ 1 : wit @ | {###}<withas>{###} : rec @ STRING \ recursive : wtab @ \ __NAME__ : as @ \ {###}<as/>{###} : uni @ \ {###}<union>{###} : ins @ \ {###}<intersect>{###} : mis @ \ {###}<minus>{###} : exc @ \ {###}<except>{###} : sel @ | __SELECT__ : uni @ | {###}</union>{###} : ins @ \ {###}</intersect>{###} : mis @ \ {###}</minus>{###} : exc @ \ {###}</except>{###} N : uni @ | __UNION_SELECT__ : ins @ | __INTERSECT_SELECT__ : mis @ | __MINUS_SELECT__ : exc @ | __EXCEPT_SELECT__ 1 : rkh @ | {###}</withas>{###} __DEF_SUB_PATH__ __NEXT_WITH_AS_SELECT__ 11 : wit @ | , : wtab @ | __NAME__ : as @ | as : lkh @ | ( : sel @ | __SELECT__ 0N : uni @ | __UNION_SELECT__ : ins @ + __INTERSECT_SELECT__ : mis @ + __MINUS_SELECT__ : exc @ + __EXCEPT_SELECT__ 1 : rkh @ | ) ------------------------------------------------------------------------ 1 : wit @ | {###}<withas>{###} : wtab @ \ __NAME__ : as @ \ {###}<as/>{###} : sel @ | __SELECT__ : rkh @ | {###}</withas>{###} ------------------------------------------------------------------------ 1 : wit @ | {###}<withas>{###} : wtab @ \ __NAME__ : as @ \ {###}<as/>{###} : uni @ \ {###}<union>{###} : ins @ \ {###}<intersect>{###} : mis @ \ {###}<minus>{###} : exc @ \ {###}<except>{###} : sel @ | __SELECT__ : uni @ | {###}</union>{###} : ins @ \ {###}</intersect>{###} : mis @ \ {###}</minus>{###} : exc @ \ {###}</except>{###} N : uni @ | __UNION_SELECT__ : ins @ | __INTERSECT_SELECT__ : mis @ | __MINUS_SELECT__ : exc @ | __EXCEPT_SELECT__ 1 : rkh @ | {###}</withas>{###} __DEF_SUB_PATH__ __UNION_SELECT__ 1 : uni @ %__IF_KW__ | union : all @ CAN_SKIP | all : sel @ | __SELECT__ --------------------------------------------------------------------------- 1 : uni @ | union : all @ | all : uni @ | {###}<union>{###} : sel @ | __SELECT__ : uni @ | {###}</union>{###} __DEF_SUB_PATH__ __INTERSECT_SELECT__ 1 : uni @ %__IF_KW__ | intersect : all @ CAN_SKIP | all : sel @ | __SELECT__ --------------------------------------------------------------------------- 1 : uni @ | intersect : all @ | all : uni @ | {###}<intersect>{###} : sel @ | __SELECT__ : uni @ | {###}</intersect>{###} __DEF_SUB_PATH__ __MINUS_SELECT__ 1 : uni @ %__IF_KW__ | minus : all @ CAN_SKIP | all : sel @ | __SELECT__ --------------------------------------------------------------------------- 1 : uni @ | minus : all @ | all : uni @ | {###}<minus>{###} : sel @ | __SELECT__ : uni @ | {###}</minus>{###} __DEF_SUB_PATH__ __EXCEPT_SELECT__ 1 : uni @ %__IF_KW__ | except : all @ CAN_SKIP | all : sel @ | __SELECT__ --------------------------------------------------------------------------- 1 : uni @ | except : all @ | all : uni @ | {###}<except>{###} : sel @ | __SELECT__ : uni @ | {###}</except>{###} __DEF_SUB_PATH__ __TABLE_NAME__ 1 : srctab @ | __NAME__ : srcfun @ + __FUNCTION__ + : schema @ | __NAME__ : pp @ | . : srctab2 @ | __NAME__ : srcfun2 @ + __FUNCTION__ 1 : as @ CAN_SKIP | as : srctabas @ CAN_SKIP | __NAME__ ----------------------------------------------------------- 1 : srctab @ | {###}<srctab>{###} : srctab @ \ __NAME__ : srctab @ \ {###}</srctab>{###} : srcfun @ | {###}<srcfun>{###} : srcfun @ \ __FUNCTION__ : srcfun @ \ {###}</srcfun>{###} : srctab2 @ | {###}<srctab>{###} : srcfun2 @ | {###}<srcfun>{###} : schema @ \ __NAME__ : pp @ \ . : srctab2 @ \ __NAME__ : srcfun2 @ \ __FUNCTION__ : srctab2 @ \ {###}</srctab>{###} : srcfun2 @ \ {###}</srcfun>{###} : as @ | as : srctabas @ | __NAME__ __DEF_SUB_PATH__ __SUB_SELECT__ 11 : lkh @ | ( : with @ CAN_SKIP | __WITH_AS_SELECT__ : sel @ | __SELECT__ 0N : uni @ | __UNION_SELECT__ : ins @ + __INTERSECT_SELECT__ : mis @ + __MINUS_SELECT__ : exc @ + __EXCEPT_SELECT__ 1 : rkh @ | ) : as @ CAN_SKIP | as : son @ CAN_SKIP | __NAME__ ---------------------------------------------------- 1 : lkh @ | {###}<subsel>{###} : with @ | __WITH_AS_SELECT__ : sel @ | __SELECT__ : rkh @ | {###}</subsel>{###} : as @ | as : son @ | __NAME__ ---------------------------------------------------- 1 : lkh @ | {###}<subsel>{###} : with @ | __WITH_AS_SELECT__ : uni @ | {###}<union>{###} : ins @ | {###}<intersect>{###} : mis @ | {###}<minus>{###} : exc @ | {###}<except>{###} : sel @ | __SELECT__ : uni @ | {###}</union>{###} : ins @ | {###}</intersect>{###} : mis @ | {###}</minus>{###} : exc @ | {###}</except>{###} N : uni @ | __UNION_SELECT__ : ins @ | __INTERSECT_SELECT__ : mis @ | __MINUS_SELECT__ : exc @ | __EXCEPT_SELECT__ 1 : rkh @ | {###}</subsel>{###} : as @ | as : son @ | __NAME__ __DEF_SUB_PATH__ __VALUE_SELECT__ 1 : x1 @ | ( : x2 @ | __SELECT__ : x3 @ | ) __DEF_SUB_PATH__ __SELECT__ 1 : sel @ %__IF_KW__ | select : dit @ %__IF_KW__ CAN_SKIP | distinct N : exp @ | __PATH_4_EXPR__ : as @ CAN_SKIP | as : colname @ CAN_SKIP | __NAME__ e : colspl @ | , 1 : frm @ CAN_SKIP | __FROM_TABLE__ N : jt @ CAN_SKIP | __JOIN_TABLE__ : lv @ + __LATERAL_VIEW__ : pv @ + __PIVOT__ 1 : wh @ CAN_SKIP | __WHERE__ : gy @ CAN_SKIP | __GROUP_BY__ : sw @ CAN_SKIP | __START_WITH_CONNECT__ : oy @ CAN_SKIP | __ORDER_BY__ : lim @ CAN_SKIP | __LIMIT__ : dy @ CAN_SKIP | __DISTRIBUTED_BY__ __DEF_SUB_PATH__ __FROM_TABLE__ 1 : frm @ %__IF_KW__ | from : tab @ | __TABLE_NAME__ : sbs @ + __SUB_SELECT__ __DEF_SUB_PATH__ __JOIN_TABLE__ 11 : x0 @ %__IF_KW__ | join ++ : x1 @ %__IF_KW__ | inner : x2 @ %__IF_KW__ | join ++ : x5 @ %__IF_KW__ | left : x6 @ %__IF_KW__ CAN_SKIP | outer : x7 @ %__IF_KW__ | join ++ : x12 @ %__IF_KW__ | right : x13 @ %__IF_KW__ CAN_SKIP | outer : x14 @ %__IF_KW__ | join ++ : x17 @ %__IF_KW__ | full : x18 @ %__IF_KW__ CAN_SKIP | outer : x19 @ %__IF_KW__ | join ++ : x20 @ | , 11 : x21 @ | __TABLE_NAME__ : x2s @ + __SUB_SELECT__ 0 : x22 @ %__IF_KW__ | on 0 : x23 @ | __PATH_4_EXPR__ __DEF_SUB_PATH__ __LATERAL_VIEW__ 1 : lat @ %__IF_KW__ | lateral : vw @ %__IF_KW__ | view : x2 @ CAN_SKIP | outer : f1 @ | __NAME__ __//__ explode ... : x4 @ | ( N : x5 @ | __PATH_4_EXPR__ e : x6 @ | , 1 : x7 @ | ) : tabas @ | __NAME__ : as @ | as N : colas @ | __NAME__ e : dd @ | , __DEF_SUB_PATH__ __PIVOT__ 11 : pvt @ | pivot : x1 @ | ( NN : fun @ | __NAME__ __//__ sum .... : fs @ | ( : col1 @ | __NAME__ : fe @ | ) : as1 @ CAN_SKIP %__IF_KW__ | as : colas @ | __NAME__ ee : dh1 @ | , 01 : for @ %__IF_KW__ | for 0 : col2 @ | __NAME__ 0 : in @ | in 0 : x3 @ | ( 0N : val1 @ | __INT__ : val2 @ + __STRING__ 0 : as2 @ CAN_SKIP | as 0 : coln @ | __NAME__ 0e : dh @ | , 01 : x4 @ | ) 10 : for2 @ %__IF_KW__ | for 0 : y1 @ | ( N0 : cols @ | __NAME__ e0 : dh3 @ | , 10 : y2 @ | ) 0 : in2 @ | in 0 : y5 @ | ( N0 : y3 @ | ( 0 : vi0 @ | __INT__ : vs0 @ + __STRING__ 0 : d1 @ CAN_SKIP | , 0 : vi1 @ CAN_SKIP | __INT__ : vs1 @ + __STRING__ 0 : d2 @ CAN_SKIP | , 0 : vi2 @ CAN_SKIP | __INT__ : vs2 @ + __STRING__ 0 : d3 @ CAN_SKIP | , 0 : vi3 @ CAN_SKIP | __INT__ : vs3 @ + __STRING__ 0 : y4 @ | ) e0 : dh7 @ | , 10 : y6 @ | ) : x2 @ | ) : as @ CAN_SKIP %__IF_KW__ | as : tabas @ | __NAME__ __DEF_SUB_PATH__ __WHERE__ 1 : x1 @ %__IF_KW__ | where N : x2 @ | __PATH_4_EXPR__ : x3 @ + __EXISTS_SELECT__ __DEF_SUB_PATH__ __GROUP_BY__ 11 : x1 @ %__IF_KW__ | group : x2 @ | by NN : x3 @ | __PATH_4_EXPR__ ee : x4 @ | , 01 : x5 @ | having 0 : x6 @ | __PATH_4_EXPR__ __DEF_SUB_PATH__ __ORDER_BY__ 1 : x1 @ %__IF_KW__ | order : x2 @ | by N : x3 @ | __PATH_4_EXPR__ : x4 @ CAN_SKIP | desc : x5 @ + asc e : x6 @ | , __DEF_SUB_PATH__ __LIMIT__ 1 : x1 @ %__IF_KW__ | limit : x2 @ | __INT__ __DEF_SUB_PATH__ __PARTITION_BY__ 1 : x11 @ %__IF_KW__ | partition : x12 @ | by N : x13 @ | __PATH_4_EXPR__ e : x16 @ | , __DEF_SUB_PATH__ __PARTITION_4_INSERT__ 1 : x1 @ %__IF_KW__ | partition : x2 @ | ( N : x3 @ | __PATH_4_EXPR__ e : x4 @ | , 1 : x5 @ | ) __DEF_SUB_PATH__ __DISTRIBUTED_BY__ 1 : did @ %__IF_KW__ | distributed : die @ %__IF_KW__ + distribute : by @ | by : hsh @ CAN_SKIP | hash : rag @ + range : lkh @ | ( N : pex @ | __PATH_4_EXPR__ e : dh @ | , 1 : rkh @ | ) __DEF_SUB_PATH__ __EXISTS_SELECT__ 01 : x1 @ %__IF_KW__ | not 1 : x2 @ | exists : x3 @ | ( : x4 @ | __SELECT__ : x5 @ | ) __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 : int @ | __INT__ : flo @ + __FLOAT__ : nm @ + __NAME__ : dap @ + __DATA_TYPE__ : caw @ + __CASE_WHEN__ : str @ + __STRING__ : caa @ + __CAST_AS__ : fun @ + __FUNCTION__ : fex @ + __FUN_EXTRACT__ : otn @ + __OTH_NAME__ : otf @ + __OTH_FUN__ : sex @ + __SUB_PATH_4_EXPR__ : vsl @ + __VALUE_SELECT__ : eq @ + = : eq2 @ + := : neq @ + <> : beq @ + != : dyh @ + > : ddy @ + >= : xyh @ + < : xdy @ + <= : add @ + + : sub @ + - : cyh @ + * : dev @ + / : ssx @ + || : bfh @ + % : bd1 @ + & : bd2 @ + ~ : mh @ + : : mh2 @ + :: : wh @ + ? : btw @ %__IF_KW__ + between : and @ %__IF_KW__ + and : or @ %__IF_KW__ + or : lik @ %__IF_KW__ + like : in @ %__IF_KW__ + in : is @ %__IF_KW__ + is : not @ %__IF_KW__ + not : nul @ %__IF_KW__ + null __DEF_SUB_PATH__ __COL_CONSTRAINT__ N : pri @ %__IF_KW__ | primary : key @ %__IF_KW__ | key + : uni @ %__IF_KW__ | unique + : nul @ %__IF_KW__ | null + : not @ %__IF_KW__ | not : nul2 @ %__IF_KW__ | null + : def @ %__IF_KW__ | default : int @ | __PATH_4_EXPR__ + : com @ %__IF_KW__ | comment : str2 @ | __STRING__ __DEF_SUB_PATH__ __TAB_CONSTRAINT__ N : com @ %__IF_KW__ | comment : str @ | __STRING__ + : pri @ %__IF_KW__ | primary : for @ %__IF_KW__ + foreign : key @ %__IF_KW__ | key : col1 @ | __COL_LIST__ + : part @ %__IF_KW__ | partitioned : part2 @ %__IF_KW__ + partition : clus @ %__IF_KW__ + clustered : ske @ %__IF_KW__ + skewed : dist @ %__IF_KW__ + distributed : dist2 @ %__IF_KW__ + distribute : by1 @ %__IF_KW__ CAN_SKIP | by : has @ %__IF_KW__ CAN_SKIP | hash : rag @ %__IF_KW__ + range : col2 @ | __COL_LIST__ : rdm @ %__IF_KW__ + randomly + : rw @ %__IF_KW__ | row : fmt @ %__IF_KW__ | format : dem @ %__IF_KW__ | delimited : fie @ %__IF_KW__ | fields : ter @ %__IF_KW__ | terminated : by2 @ %__IF_KW__ | by : str1 @ | __STRING__ + : sto @ %__IF_KW__ | stored : as @ %__IF_KW__ | as : ff @ | __NAME_WITH_KW__ + : loc @ %__IF_KW__ | location : str2 @ | __STRING__ + : wit @ %__IF_KW__ | with : wexp @ | __SUB_PATH_4_EXPR__ + : on @ %__IF_KW__ | on : cit @ %__IF_KW__ | commit : pre @ %__IF_KW__ | preserve : del @ %__IF_KW__ + delete : row @ %__IF_KW__ | rows __DEF_SUB_PATH__ __OTH_NAME__ 1 : otrnm @ | __NAME__ : pp @ | . : column @ | __NAME__ : all_col @ + * __DEF_SUB_PATH__ __OTH_FUN__ N : schema | __NAME__ : pp | . 1 : fun | __FUNCTION__ __DEF_SUB_PATH__ __COL_LIST__ 1 : x1 @ | ( N : colname @ | __NAME__ e : x2 @ | , 1 : x3 @ | ) __DEF_SUB_PATH__ __FUNCTION__ 11 : fun @ | __NAME__ : lkh @ | ( : dit @ CAN_SKIP | distinct NN : exp @ CAN_SKIP | __PATH_4_EXPR__ : ody @ CAN_SKIP | __ORDER_BY__ ee : dh @ CAN_SKIP | , 11 : rkh @ | ) 0 : ovr @ | over 0 : lkh2 @ | ( 0 : pty @ CAN_SKIP | __PARTITION_BY__ 0 : ody2 @ CAN_SKIP | __ORDER_BY__ 0 : rkh2 @ | ) __DEF_SUB_PATH__ __FUN_EXTRACT__ 1 : ext @ | extract : lkh @ | ( : tim @ | __NAME_WITH_KW__ : frm @ %__IF_KW__ | from : exp @ | __PATH_4_EXPR__ : rkh @ | ) __DEF_SUB_PATH__ __CAST_AS__ 1 : cast @ | cast : lkh @ | ( : expr @ | __PATH_4_EXPR__ : as @ | as : dat @ | __DATA_TYPE__ : rkh @ | ) __DEF_SUB_PATH__ __TABLE_TYPE__ 1 : tab @ | table : lkh @ | ( N : col @ | __NAME__ : dat @ | __DATA_TYPE__ e : dh @ | , 1 : rkh @ | ) __DEF_SUB_PATH__ __DATA_TYPE__ 1 : dat @ %__IF_KW__ | date : int @ %__IF_KW__ + int : int4 @ %__IF_KW__ + int4 : int8 @ %__IF_KW__ + int8 : int1 @ %__IF_KW__ + integer : dbl @ %__IF_KW__ + double : flo @ %__IF_KW__ + float : flo4 @ %__IF_KW__ + float4 : flo8 @ %__IF_KW__ + float8 : bint @ %__IF_KW__ + bigint : str @ %__IF_KW__ + string : txt @ %__IF_KW__ + text : tint @ %__IF_KW__ + tinyint : sint @ %__IF_KW__ + smallint : bool @ %__IF_KW__ + boolean : tim @ %__IF_KW__ + time : timt @ %__IF_KW__ + timestamp : arr @ %__IF_KW__ + array : map @ %__IF_KW__ + map : bin @ %__IF_KW__ + binary : rcd @ %__IF_KW__ + record : ser @ %__IF_KW__ + serial : xcha @ + __X_CHAR__ : dec @ + __DECIMAL__ + : nm1 @ | __NORM_NAME__ : pp @ | . : nm2 @ | __NORM_NAME__ : pp2 @ CAN_SKIP | . : nm3 @ CAN_SKIP | __NORM_NAME__ : bfh @ | % : typ @ %__IF_KW__ | type __DEF_SUB_PATH__ __CASE_WHEN__ 1 : cas @ %__IF_KW__ | case : exp1 @ CAN_SKIP | __PATH_4_EXPR__ N : whe @ %__IF_KW__ | when : exp2 @ | __PATH_4_EXPR__ : the @ %__IF_KW__ | then : exp3 @ | __PATH_4_EXPR__ 1 : els @ %__IF_KW__ CAN_SKIP | else : exp4 @ CAN_SKIP | __PATH_4_EXPR__ : end @ %__IF_KW__ | end __DEF_SUB_PATH__ __DECIMAL__ 111 : x1 @ | decimal : x11 @ + number : x12 @ + numeric 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__ : n @ + N 0 : x6 @ | ) __DEF_SUB_PATH__ __NAME__ 1 : lsy @ | " : nm1 @ | __ANY__ : rsy @ | " + : ldy @ | ` : nm2 @ | __ANY__ : rdy @ | ` + : dol @ | $ : lkh @ | { : nm3 @ | __NORM_NAME__ : rkh @ | } + : nm4 @ | __NORM_NAME__ __//__ __DEF_SUB_PATH__ __VAR_NAME__ __//__ 1 : x1 @ | $ __//__ : x2 @ | { __//__ : x3 @ | __NORM_NAME__ __//__ : x4 @ | } __DEF_STR__ __NORM_NAME__ <1,100> [0,100]0123456789 [1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_?? [0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_?? [NO] create insert into update delete truncate drop merge table select inner left join on from where exists group distribute distributed order partition by having union minus intersect except all with as set reset between and or like in is not null case when then else end pivot lateral view start connect while do repeat until loop call open close cursor fetch analyze vacuum begin execute __DEF_STR__ __NAME_WITH_KW__ <1,100> [0,100]0123456789 [1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_?? [0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_?? __DEF_SUB_PATH__ __STRING__ 1 : x1 | ' : x2 | __ANY__ : x3 | ' ------------------------------------ 1 : x1 | ' N : x2 \ __ANY__ 1 : x3 \ ' __DEF_STR__ __FLOAT__ <1,100> [1,50]0123456789 [1,1]. [1,50]0123456789 __DEF_STR__ __INT__ <1,100> [1,100]0123456789
