After years of development and accumulation, the company's code repository has gradually become bloated.
These mountains of code often originate from the hands of multiple programmers, whose skills vary greatly and styles differ.
How can we uniformly manage these mountains of code, allowing them to be monitored, evaluated, and modified in bulk?
The first challenge in establishing a "code management system" lies in how to quickly locate code segments with certain characteristics within a vast code repository.
Since we are looking for code segments rather than individual lines of code, it is difficult to achieve using traditional regular expressions. Instead, we need to customize the syntax configuration through a syntax parser and then perform code search.
Example illustration ：
Instance 1: find out functions in JAVA code where the number of input parameters exceeds 4
The search rule configuration (Code_manage.syn) is shown below:
__DEF_CASE_SENSITIVE__ Y
__DEF_FUZZY__ Y
__DEF_DEBUG__ N
__DEF_LINE_COMMENT__ //
__DEF_LINES_COMMENT__ /* */
__DEF_STR__ __NAME__
<1,200>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_$??
[0,199]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??
[NO] import if else for while break continue class return try except finally final static public private
__DEF_PATH__ __FUNCTION_DEF__
0101 : x1 @ | public
: x2 @ + private
0 0 : x3 @ CAN_SKIP | static
1 1 : x4 @ | __NAME__
: x5 @ | __NAME__
: x6 @ | (
1111 : p1 @ CAN_SKIP | final
: p11 @ | __NAME__
: p111 @ | __NAME__
: p2 @ | ,
: p22 @ CAN_SKIP | final
: p222 @ | __NAME__
: p2222 @ | __NAME__
: p3 @ | ,
: p33 @ CAN_SKIP | final
: p333 @ | __NAME__
: p3333 @ | __NAME__
NNNN : p4 @ | ,
: p44 @ CAN_SKIP | final
: x444 @ | __NAME__
: x4444 @ | __NAME__
1111 : xx @ | )
Suppose java code (MyCode.java) is shown below:
private int alreadyBufferedSize = 0;
// The index in the byte[] found at buffers.getLast() to be written next
private int index = 0;
// Is the stream closed?
private boolean closed = false;
public FastByteArrayOutputStream(int initialBlockSize) {
Assert.isTrue(initialBlockSize > 0, "Initial block size must be greater than 0");
this.initialBlockSize = initialBlockSize;
this.nextBlockSize = initialBlockSize;
}
@Override
public void applyBeanPropertyValues(Object existingBean, String beanName, int autowireMode, boolean dependencyCheck, int initSize)
throws BeansException {
markBeanAsCreated(beanName);
BeanDefinition bd = getMergedBeanDefinition(beanName);
BeanWrapper bw = new BeanWrapperImpl(existingBean);
initBeanWrapper(bw);
applyPropertyValues(beanName, bd, bw, bd.getPropertyValues());
}
@Override
public Object initializeBean(Object existingBean, String beanName) {
return initializeBean(beanName, existingBean, null);
}
According to the configuration rules, execute the search command :
ZGLanguage -e Code_manage.syn -f MyCode.java
The result can be obtained as follows :
C:\>ZGLanguage -e Code_manage.syn -f MyCode.java
Run type : Find
Syntax file : Code_manage.syn
code file : MyCode.java
Output file : out.zgl
--------------------------------------------------------------------
### Found code by : __FUNCTION_DEF__ | Lines : 17 ~ 17 :
--------------------------------------------------------------------
public void applyBeanPropertyValues(Object existingBean, String beanName, int autowireMode, boolean dependencyCheck, int initSize)
It can be seen that the search result only outputs the function 'applyBeanPropertyValues', which has 5 input parameters. The input parameters of the other two functions are both no more than 4, and thus are ignored.
Instance 2: Check whether there is any repeated calculation in the judgment of the for loop:
For example :
'for(int i=0; i < list.size(); i++)' is repeated calculation.
'for(int i=0; i < 100; i++)' is not repeated calculation.
The syntax configuration for checking is shown below (for.syn):
__DEF_FUZZY__ Y
__DEF_DEBUG__ N
__DEF_CASE_SENSITIVE__ Y
__DEF_LINE_COMMENT__ //
__DEF_LINES_COMMENT__ /* */
__DEF_PATH__ __FOR__
1 : for @ | for
: lkh @ | (
: lex @ | __ANY__
: fh1 @ | ;
1 : mex @ | __ANY__
: fh2 @ | ;
+ : mex1 @ | __NORM_NAME__
: op1 @ | >
: op2 @ + >=
: op3 @ + <
: op4 @ + <=
: op5 @ + ==
: op6 @ + !=
: va1 @ | __NORM_NAME__
: va2 @ + __STRING__
: va3 @ + __FLOAT__
: va4 @ + __INT__
: fh22 @ | ;
+ : mex2 @ | __NORM_NAME__
: fh23 @ | ;
+ : fh24 @ | ;
1 : rex @ | __ANY__
: rkh @ | )
-------------------------------------------------------------
1 : for @ | __\n__
1 : fh2 @ STRING | BAD | Line :
: fh22 @ STRING | GOOD | Line :
: fh23 @ STRING | GOOD | Line :
: fh24 @ STRING | GOOD | Line :
: fh2 @ | __LINE__
: fh22 @ | __LINE__
: fh23 @ | __LINE__
: fh24 @ | __LINE__
1 : for @ | |
: for @ | for
: lkh @ | (
: lex @ | __ANY__
: fh1 @ | ;
: mex @ | __ANY__
: fh2 @ | ;
: mex1 @ | __NORM_NAME__
: op1 @ | >
: op2 @ | >=
: op3 @ | <
: op4 @ | <=
: op5 @ | ==
: op6 @ | !=
: va1 @ | __NORM_NAME__
: va2 @ | __STRING__
: va3 @ | __FLOAT__
: va4 @ | __INT__
: fh22 @ | ;
: mex2 @ | __NORM_NAME__
: fh23 @ | ;
: fh24 @ | ;
: rex @ | __ANY__
: rkh @ | )
__DEF_STR__ __NORM_NAME__
<1,100>
[0,100]0123456789
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_??
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??
[NO] for
__DEF_SUB_PATH__ __STRING__
1 : x1 | "
: x2 | __ANY__
: x3 | "
__DEF_STR__ __FLOAT__
<1,100>
[1,50]0123456789
[1,1].
[1,50]0123456789
__DEF_STR__ __INT__
<1,100>
[1,100]0123456789
Suppose code file content is shown below (debug.code):
for(int i=0; i < 100; i++)
{
for(int i=0; i >= j + 1; i++)
{
for(int i=0; i == "abc"; i++)
{....}
}
}
for(; fun(a) != i; i++ )
{....}
for( ; ; )
{....}
for( ; i; i -- )
{....}
for(int i=0; i < list.size(); i++)
{....}
for(int i=0, int length=list.size(); i < length; i++)
{....}
Execute command:
ZGLanguage -e for.syn -t debug.code -o out.zgl > log.log
Obtain the result (out.zgl)：
GOOD | Line : 1 | for ( int i=0 ; i < 100 ; i++ )
BAD | Line : 3 | for ( int i=0 ; i >= j + 1 ; i++ )
GOOD | Line : 5 | for ( int i=0 ; i == "abc" ; i++ )
BAD | Line : 10 | for ( ; fun(a) != i ; i++ )
GOOD | Line : 13 | for ( ; ; )
GOOD | Line : 16 | for ( ; i ; i -- )
BAD | Line : 19 | for ( int i=0 ; i < list.size() ; i++ )
GOOD | Line : 22 | for ( int i=0, int length=list.size() ; i < length ; i++ )
Instance 3: Extract the 'where/on' code segments of the SQL code, and categorize the conditional statements within them:
The syntax configuration of extracting the 'where/on' code segments is shown below :
__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__ __WHERE__
1 : x1 @ | __\b__
: x2 @ + __\t__
: x3 @ + __\n__
: x4 @ %__IF_KW__ | where
: x5 @ | __PATH_4_EXPR__
------------------------------------------------------------
1 : x4 @ | __\n__
: x4 @ | __\n__
: x4 @ | ################
: x4 @ STRING / Line :
: x4 @ / __LINE__
: x4 @ / ################
: x4 @ | __\n__
: x4 @ | __\n__
: x4 @ | where
: x5 @ | __PATH_4_EXPR__
__DEF_PATH__ __ON__
1 : x1 @ | __\b__
: x2 @ + __\t__
: x3 @ + __\n__
: x4 @ %__IF_KW__ | on
: x5 @ | __PATH_4_EXPR__
------------------------------------------------------------
1 : x4 @ | __\n__
: x4 @ | __\n__
: x4 @ | ################
: x4 @ STRING / Line :
: x4 @ / __LINE__
: x4 @ / ################
: x4 @ | __\n__
: x4 @ | __\n__
: x4 @ | on
: x5 @ | __PATH_4_EXPR__
__DEF_SUB_PATH__ __PATH_4_EXPR__
N : int @ | __INT__
: flo @ + __FLOAT__
: nm @ + __NAME__
: caw @ + __CASE_WHEN__
: str @ + __STRING__
: vsl @ + __SUB__
: fun @ + __FUNCTION__
: otn @ + __OTH_NAME__
: otf @ + __OTH_FUN__
: 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
: exi @ %__IF_KW__ + exists
__DEF_SUB_PATH__ __OTH_NAME__
1 : otrnm @ | __NAME__
: pp @ | .
: column @ | __NAME__
__DEF_SUB_PATH__ __OTH_FUN__
N : schema | __NAME__
: pp | .
1 : fun | __FUNCTION__
__DEF_SUB_PATH__ __FUNCTION__
1 : fun @ | __NAME__
: lkh @ | __SUB__
__DEF_STR__ __ALL_STR__
<1,20000>
[1,20000]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789`~!@#$%^&*-_+={}[]\|:;'"<,>.?/??
__DEF_SUB_PATH__ __SUB__
1 : | (
N : | __ALL_STR__
: + __SUB__
1 : | )
__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__ __NAME__
1 : lsy @ | "
: nm1 @ | __ANY__
: rsy @ | "
+ : ldy @ | `
: nm2 @ | __ANY__
: rdy @ | `
+ : dol @ | $
: lkh @ | {
: nm3 @ | __NORM_NAME__
: rkh @ | }
+ : nm4 @ | __NORM_NAME__
__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
__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 | '
__DEF_STR__ __FLOAT__
<1,100>
[1,50]0123456789
[1,1].
[1,50]0123456789
__DEF_STR__ __INT__
<1,100>
[1,100]0123456789
Suppose SQL code (debug.code) is as follows ：
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;
/
Execute command：
ZGLanguage -e GET_SQL_ON_WHERE.syn -t debug.code -o on_where.zgl > log.log
Obtain extraction results（on_where.zgl）：
################ Line : 26 ################
WHERE PROC_NAME = V_PROC_NAME
################ Line : 46 ################
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
################ Line : 59 ################
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 --xxxx账户统计表
################ Line : 107 ################
ON (CL.DRAW_NO = AL.SERIALNO AND P_AS_OF_DATE = AL.AS_OF_DATE) --借据表
################ Line : 109 ################
ON (AL.SERIALNO = BD.SERIALNO AND P_AS_OF_DATE = BD.AS_OF_DATE) --合同表
################ Line : 111 ################
ON (BD.RELATIVESERIALNO2 = BC.SERIALNO AND
BC.AS_OF_DATE = P_AS_OF_DATE) --业务出账表
################ Line : 115 ################
ON (BD.RELATIVESERIALNO1 = BP.SERIALNO AND
BP.AS_OF_DATE = P_AS_OF_DATE)
################ Line : 127 ################
WHERE AS_OF_DATE = P_AS_OF_DATE
################ Line : 134 ################
WHERE AS_OF_DATE = P_AS_OF_DATE
################ Line : 135 ################
ON T6.CUSTOMERID = T7.CUSTOMERID
################ Line : 136 ################
ON AL.CUSTOMERID = CI.CUSTOMERID
################ Line : 169 ################
WHERE AS_OF_DATE = P_AS_OF_DATE
################ Line : 170 ################
WHERE A.CORESUBJECTNO = B.SUB_CODE
AND DECODE(DIRECTION,
'D',
DEBITBALANCE - CREDITBALANCE,
CREDITBALANCE - DEBITBALANCE) <> 0
AND A.AS_OF_DATE = P_AS_OF_DATE
################ Line : 177 ################
ON AL.ACCOUNTNO = SL.ACCOUNTNO --币种表
################ Line : 179 ################
ON (CL.CCY = CC.CUR_CODE AND P_AS_OF_DATE = CC.AS_OF_DATE) --上次支付日临时表
################ Line : 181 ################
ON CL.DRAW_NO = LP.OBJECTNO --下次支付日临时表
################ Line : 183 ################
ON CL.DRAW_NO = NP.OBJECTNO
################ Line : 187 ################
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')
################ Line : 195 ################
ON CL.DRAW_NO = T.OBJECTNO
################ Line : 211 ################
ON T.CORESUBJECTNO = T1.SUB_CODE
################ Line : 212 ################
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
################ Line : 218 ################
ON AL.ACCOUNTNO = B.ACCOUNTNO
################ Line : 232 ################
ON SL.CORESUBJECTNO = SUB.SUB_CODE
################ Line : 233 ################
WHERE SUB.SUB_NAME LIKE '%准备%'
AND SUB.AS_OF_DATE = P_AS_OF_DATE
AND SL.AS_OF_DATE = P_AS_OF_DATE
################ Line : 236 ################
WHERE T.SEQ = 1
################ Line : 237 ################
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;*/
################ Line : 242 ################
WHERE (CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010')
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')
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))
################ Line : 260 ################
ON T.INT_GL_ACCOUNT_ID = T1.SUBJECT_NO3
AND T1.SUBJECT_NAME3 LIKE '%已减值%'
AND T1.AS_OF_DATE = P_AS_OF_DATE
################ Line : 263 ################
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))
################ Line : 272 ################
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)
################ Line : 280 ################
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')
################ Line : 296 ################
WHERE A.MATURITY_DATE <= P_AS_OF_DATE
AND A.CUR_BOOK_BAL <> 0
################ Line : 304 ################
WHERE AS_OF_DATE = P_AS_OF_DATE
AND GL_ACCOUNT_ID IN ('9990101', '9990102', '9990103', '9990104')
################ Line : 321 ################
WHERE T.AS_OF_DATE = P_AS_OF_DATE
AND T.GL_ACCOUNT_ID = '9990101'
Next, identify and mark the conditions of the above results, the syntax configuration is as follows (MARK_SQL_ON_WHERE.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__ __MARK_CONDITION__
1 : blk @ %__IF_KW__ | __BLANK__
1 : whr @ %__IF_KW__ | where
: on @ %__IF_KW__ + on
: and1 @ %__IF_KW__ + and
: or1 @ %__IF_KW__ + or
1 : exp1 @ | __L_PATH_4_EXPR__
: exp11 @ + __PATH_4_EXPR__
1 : eq @ | =
: neq @ + <>
: beq @ + !=
: dyh @ + >
: ddy @ + >=
: xyh @ + <
: xdy @ + <=
+ : lik @ %__IF_KW__ | like
: in @ %__IF_KW__ + in
: is @ %__IF_KW__ + is
: btw @ %__IF_KW__ + between
+ : not @ %__IF_KW__ | not
: in2 @ %__IF_KW__ | in
+ : not3 @ %__IF_KW__ | not
: lik2 @ %__IF_KW__ | like
+ : is2 @ %__IF_KW__ | is
: not2 @ %__IF_KW__ | not
1 : asb @ | __ANY_SUB__
: exp2 @ + __PATH_4_EXPR__
-----------------------------------------------------------------------------------
1 : whr @ | where
: on @ | on
: and1 @ | and
: or1 @ | or
1 : exp1 @ | __L_PATH_4_EXPR__
: exp11 @ | <condition>
: exp11 @ | __PATH_4_EXPR__
: eq @ | =
: neq @ | <>
: beq @ | !=
: dyh @ | >
: ddy @ | >=
: xyh @ | <
: xdy @ | <=
: lik @ | like
: in @ | in
: is @ | is
: btw @ | between
: not @ | not
: in2 @ | in
: not3 @ | not
: lik2 @ | like
: is2 @ | is
: not2 @ | not
: asb @ | __ANY_SUB__
: exp2 @ | __PATH_4_EXPR__
1 : asb @ | </condition>
: exp2 @ | </condition>
__DEF_PATH__ __SIMPLE_CONDITION__
1 : cdt @ | <condition>
: int @ | __INT__
: flo @ + __FLOAT__
: nm @ + __NAME__
: str @ + __STRING__
: otn @ + __OTH_NAME__
: wh @ + ?
: nul @ %__IF_KW__ + null
1 : eq @ | =
: neq @ + <>
: beq @ + !=
: dyh @ + >
: ddy @ + >=
: xyh @ + <
: xdy @ + <=
+ : lik @ %__IF_KW__ | like
: in @ %__IF_KW__ + in
: is @ %__IF_KW__ + is
: btw @ %__IF_KW__ + between
+ : not @ %__IF_KW__ | not
: in2 @ %__IF_KW__ | in
+ : not3 @ %__IF_KW__ | not
: lik2 @ %__IF_KW__ | like
+ : is2 @ %__IF_KW__ | is
: not2 @ %__IF_KW__ | not
1 : int2 @ | __INT__
: flo2 @ + __FLOAT__
: nm2 @ + __NAME__
: str2 @ + __STRING__
: otn2 @ + __OTH_NAME__
: sps @ + __SIMPLE_SET__
: wh2 @ + ?
: nul2 @ %__IF_KW__ + null
: cdte @ | </condition>
---------------------------------------------------------------------
1 : cdt @ | <simple_condition>
: int @ | __INT__
: flo @ | __FLOAT__
: nm @ | __NAME__
: str @ | __STRING__
: otn @ | __OTH_NAME__
: wh @ | ?
: nul @ | null
: eq @ | =
: neq @ | <>
: beq @ | !=
: dyh @ | >
: ddy @ | >=
: xyh @ | <
: xdy @ | <=
: lik @ | like
: in @ | in
: is @ | is
: btw @ | between
: not @ | not
: in2 @ | in
: not3 @ | not
: lik2 @ | like
: is2 @ | is
: not2 @ | not
: int2 @ | __INT__
: flo2 @ | __FLOAT__
: nm2 @ | __NAME__
: str2 @ | __STRING__
: otn2 @ | __OTH_NAME__
: sps @ | __SIMPLE_SET__
: wh2 @ | ?
: nul2 @ | null
: cdte @ | </simple_condition>
__DEF_PATH__ __FOCUS_CONDITION__
1 : cdt @ | <condition>
: any @ | __ANY__
: cdte @ | </condition>
---------------------------------------------------------------------
1 : cdt @ | <focus_condition>
N : any @ | __ANY__
1 : cdte @ | </focus_condition>
__DEF_SUB_PATH__ __L_PATH_4_EXPR__
1 : lkh @ | (
: exp11 @ | __PATH_4_EXPR__
--------------------------------------------------------------
1 : lkh @ | (
: exp11 @ | <condition>
: exp11 @ | __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 : int @ | __INT__
: flo @ + __FLOAT__
: nm @ + __NAME__
: otn @ + __OTH_NAME__
: caw @ + __CASE_WHEN__
: str @ + __STRING__
: vsl @ + __SUB_PATH_4_EXPR__
: fun @ + __FUNCTION__
: otf @ + __OTH_FUN__
: wh @ + ?
: nul @ %__IF_KW__ + null
: add @ + +
: sub @ + -
: cyh @ + *
: dev @ + /
: ssx @ + ||
: bfh @ + %
: bd1 @ + &
: bd2 @ + ~
: mh @ + :
: mh2 @ + ::
__DEF_SUB_PATH__ __SIMPLE_SET__
1 : lkh | (
N : int | __INT__
: flo + __FLOAT__
: nam + __NAME__
: str + __STRING__
e : dh | ,
1 : rkh | )
__DEF_SUB_PATH__ __OTH_NAME__
1 : otrnm @ | __NAME__
: pp @ | .
: column @ | __NAME__
__DEF_SUB_PATH__ __OTH_FUN__
N : schema | __NAME__
: pp | .
1 : fun | __FUNCTION__
__DEF_SUB_PATH__ __FUNCTION__
1 : fun @ | __NAME__
: lkh @ | __ANY_SUB__
__DEF_STR__ __ALL_STR__
<1,20000>
[1,20000]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789`~!@#$%^&*-_+={}[]\|:;'"<,>.?/
__DEF_SUB_PATH__ __ANY_SUB__
1 : | (
N : | __ALL_STR__
: + __ANY_SUB__
1 : | )
__DEF_SUB_PATH__ __CASE_WHEN__
1 : cas @ %__IF_KW__ | case
: exp1 @ | __ANY__
N : whe @ %__IF_KW__ | when
: exp2 @ | __ANY__
: the @ %__IF_KW__ | then
: exp3 @ | __ANY__
1 : els @ %__IF_KW__ | else
: exp4 @ | __ANY__
: end @ %__IF_KW__ | end
__DEF_SUB_PATH__ __NAME__
1 : lsy @ | "
: nm1 @ | __ANY__
: rsy @ | "
+ : ldy @ | `
: nm2 @ | __ANY__
: rdy @ | `
+ : dol @ | $
: lkh @ | {
: nm3 @ | __NORM_NAME__
: rkh @ | }
+ : nm4 @ | __NORM_NAME__
__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
__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 | '
__DEF_STR__ __FLOAT__
<1,100>
[1,50]0123456789
[1,1].
[1,50]0123456789
__DEF_STR__ __INT__
<1,100>
[1,100]0123456789
Execute command for the first time to mark all conditions.
ZGLanguage -e MARK_SQL_ON_WHERE.syn -r on_where.zgl -o mark_sql.zgl > log.log
A part of the result (mark_sql.zgl) :
################ Line : 26 ################
WHERE <condition> PROC_NAME = V_PROC_NAME </condition>
################ Line : 46 ################
WHERE <condition> T.AS_OF_DATE = P_AS_OF_DATE </condition>
AND <condition> T.SEQID <> '999' </condition>
AND <condition> TO_DATE(T.PAYDATE, 'YYYY-MM-DD') < P_AS_OF_DATE </condition>
################ Line : 59 ################
WHERE <condition> T.AS_OF_DATE = P_AS_OF_DATE </condition>
AND <condition> T.SEQID <> '999' </condition>
AND <condition> TO_DATE(T.PAYDATE, 'YYYY-MM-DD') >= P_AS_OF_DATE </condition> --xxxx账户统计表
.................
Execute command for second time to judge the above marked conditions, divide into <simple_condition> and <focus_condition> :
ZGLanguage -e MARK_SQL_ON_WHERE.syn -r mark_sql.zgl -o out.zgl > log.log
The result（out.zgl）：
################ Line : 26 ################
WHERE <simple_condition> PROC_NAME = V_PROC_NAME </simple_condition>
################ Line : 46 ################
WHERE <simple_condition> T.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
AND <simple_condition> T.SEQID <> '999' </simple_condition>
AND <focus_condition> TO_DATE(T.PAYDATE, 'YYYY-MM-DD') < P_AS_OF_DATE </focus_condition>
################ Line : 59 ################
WHERE <simple_condition> T.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
AND <simple_condition> T.SEQID <> '999' </simple_condition>
AND <focus_condition> TO_DATE(T.PAYDATE, 'YYYY-MM-DD') >= P_AS_OF_DATE </focus_condition> --xxxx账户统计表
################ Line : 107 ################
ON ( <simple_condition>CL.DRAW_NO = AL.SERIALNO </simple_condition> AND <simple_condition> P_AS_OF_DATE = AL.AS_OF_DATE </simple_condition>) --借据表
################ Line : 109 ################
ON ( <simple_condition>AL.SERIALNO = BD.SERIALNO </simple_condition> AND <simple_condition> P_AS_OF_DATE = BD.AS_OF_DATE </simple_condition>) --合同表
################ Line : 111 ################
ON ( <simple_condition>BD.RELATIVESERIALNO2 = BC.SERIALNO </simple_condition> AND <simple_condition>
BC.AS_OF_DATE = P_AS_OF_DATE </simple_condition>) --业务出账表
################ Line : 115 ################
ON ( <simple_condition>BD.RELATIVESERIALNO1 = BP.SERIALNO </simple_condition> AND <simple_condition>
BP.AS_OF_DATE = P_AS_OF_DATE </simple_condition>)
################ Line : 127 ################
WHERE <simple_condition> AS_OF_DATE = P_AS_OF_DATE </simple_condition>
################ Line : 134 ################
WHERE <simple_condition> AS_OF_DATE = P_AS_OF_DATE </simple_condition>
################ Line : 135 ################
ON <simple_condition> T6.CUSTOMERID = T7.CUSTOMERID </simple_condition>
################ Line : 136 ################
ON <simple_condition> AL.CUSTOMERID = CI.CUSTOMERID </simple_condition>
################ Line : 169 ################
WHERE <simple_condition> AS_OF_DATE = P_AS_OF_DATE </simple_condition>
################ Line : 170 ################
WHERE <simple_condition> A.CORESUBJECTNO = B.SUB_CODE </simple_condition>
AND <focus_condition> DECODE(DIRECTION,
'D',
DEBITBALANCE - CREDITBALANCE,
CREDITBALANCE - DEBITBALANCE) <> 0 </focus_condition>
AND <simple_condition> A.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
################ Line : 177 ################
ON <simple_condition> AL.ACCOUNTNO = SL.ACCOUNTNO </simple_condition> --币种表
################ Line : 179 ################
ON ( <simple_condition>CL.CCY = CC.CUR_CODE </simple_condition> AND <simple_condition> P_AS_OF_DATE = CC.AS_OF_DATE </simple_condition>) --上次支付日临时表
################ Line : 181 ################
ON <simple_condition> CL.DRAW_NO = LP.OBJECTNO </simple_condition> --下次支付日临时表
################ Line : 183 ################
ON <simple_condition> CL.DRAW_NO = NP.OBJECTNO </simple_condition>
################ Line : 187 ################
WHERE <simple_condition> A.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
AND <simple_condition> A.SEQID <> '999' </simple_condition>
AND <focus_condition> (A.OBJECTNO, TO_DATE(A.PAYDATE, 'YYYY/MM/DD')) IN
(SELECT B.OBJECTNO, B.NEXT_PAYDATE
FROM ETL.TMP_XD_NEXT_PAYDATE B) </focus_condition>
AND <focus_condition> A.SDATE <= TO_CHAR(P_AS_OF_DATE, 'YYYYMMDD') </focus_condition>
AND <focus_condition> A.EDATE >= TO_CHAR(P_AS_OF_DATE, 'YYYYMMDD') </focus_condition>
################ Line : 195 ################
ON <simple_condition> CL.DRAW_NO = T.OBJECTNO </simple_condition>
################ Line : 211 ################
ON <simple_condition> T.CORESUBJECTNO = T1.SUB_CODE </simple_condition>
################ Line : 212 ################
WHERE ( <simple_condition>T1.SUB_NAME LIKE '%利息收入%' </simple_condition>)
AND <simple_condition> T.SENDFLAG = '1' </simple_condition>
AND <simple_condition> T.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
AND <simple_condition> T1.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
################ Line : 218 ################
ON <simple_condition> AL.ACCOUNTNO = B.ACCOUNTNO </simple_condition>
################ Line : 232 ################
ON <simple_condition> SL.CORESUBJECTNO = SUB.SUB_CODE </simple_condition>
################ Line : 233 ################
WHERE <simple_condition> SUB.SUB_NAME LIKE '%准备%' </simple_condition>
AND <simple_condition> SUB.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
AND <simple_condition> SL.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
################ Line : 236 ################
WHERE <simple_condition> T.SEQ = 1 </simple_condition>
################ Line : 237 ################
ON <simple_condition> CL.DRAW_NO = C.ACCOUNTNO </simple_condition>
/*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;*/
################ Line : 242 ################
WHERE ( <simple_condition>CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010') </simple_condition>
AND <simple_condition> CL.AGG_YTD <> 0 </simple_condition> AND <simple_condition> CL.AS_OF_DATE = P_AS_OF_DATE </simple_condition> AND
AL.ACCOUNTNO IS NOT NULL)
OR ( <simple_condition>CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010') </simple_condition>
AND <simple_condition> CL.AGG_YTD = 0 </simple_condition> AND <simple_condition> CL.AS_OF_DATE = P_AS_OF_DATE </simple_condition> AND
AL.ACCOUNTNO IS NOT NULL AND
( <simple_condition>CL.LOAN_BALANCE <> 0 </simple_condition> OR <simple_condition> B.ACCRUED_INTEREST <> 0 </simple_condition>))
################ Line : 260 ################
ON <simple_condition> T.INT_GL_ACCOUNT_ID = T1.SUBJECT_NO3 </simple_condition>
AND <simple_condition> T1.SUBJECT_NAME3 LIKE '%已减值%' </simple_condition>
AND <simple_condition> T1.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
################ Line : 263 ################
WHERE <simple_condition> T.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
AND <focus_condition> 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)) </focus_condition>
################ Line : 272 ################
ON ( <simple_condition>A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER </simple_condition> AND <simple_condition> A.AS_OF_DATE = P_AS_OF_DATE </simple_condition> AND <simple_condition> A.GL_ACCOUNT_ID = B.GL_ACCOUNT_ID </simple_condition> AND <simple_condition> A.INT_GL_ACCOUNT_ID = B.INT_GL_ACCOUNT_ID </simple_condition>)
################ Line : 280 ################
WHERE <simple_condition> T.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
AND EXISTS (SELECT 1
FROM NYBDP.O_CWWS_BUSINESS_DUEBILL BD
WHERE <simple_condition> T.ACCOUNT_ID = BD.SERIALNO </simple_condition>
AND <simple_condition> BD.AS_OF_DATE = T.AS_OF_DATE </simple_condition>
AND <simple_condition> BD.BUSINESSSTATUS = '6' </simple_condition>)
################ Line : 296 ################
WHERE <simple_condition> A.MATURITY_DATE <= P_AS_OF_DATE </simple_condition>
AND <simple_condition> A.CUR_BOOK_BAL <> 0 </simple_condition>
################ Line : 304 ################
WHERE <simple_condition> AS_OF_DATE = P_AS_OF_DATE </simple_condition>
AND <simple_condition> GL_ACCOUNT_ID IN ('9990101', '9990102', '9990103', '9990104') </simple_condition>
################ Line : 321 ################
WHERE <simple_condition> T.AS_OF_DATE = P_AS_OF_DATE </simple_condition>
AND <simple_condition> T.GL_ACCOUNT_ID = '9990101' </simple_condition>
Top comments (0)