从sql脚本中提取模式

Question

我试图通过在unix框中读取sql脚本或shell脚本来生成输出，输出文件是用语句功能（Create、drop、update、delete、merge、insert）生成的，后跟tablename。我尝试以一种通用的方式来完成这个输出，以读取任何代码并生成输出。这可以通过使用awk编程来实现。你知道吗

输出

MERGE|temp_st_rx_wk_str_ip_rpt
SELECT|rx_ov_ord_excep_str_sku 
SELECT|ndc
SELECT|fiscal_week
SELECT|store
SELECT|dss_saf_user01.rx_ov_ord_exclu_str
SELECT|rx_osv_invoice_str_ndc
DROP|temp_extract
CREATE|temp_build_extract
SELECT|temp_st_rx_wk_str_ip_rpt

代码

merge into temp_st_rx_wk_str_ip_rpt s
    USING (SELECT b.week_nbr,
                  b.store_nbr,
                  SUM (NVL (a.orig_on_ord_qty, 0)) AS mnd_ov_ord_orig_qty,
                  SUM (NVL (b.inv_qty, 0)) AS mnd_ov_inv_qty
             FROM (SELECT /*+ PARALLEL (s,8) */  w.week_nbr, s.store_nbr, s.ndc_nbr,
                          SUM (s.orig_on_ord_qty) AS orig_on_ord_qty
                     FROM rx_ov_ord_excep_str_sku s,
                          ndc n,
                          fiscal_week w,
                          store st
                    WHERE s.ndc_nbr = n.ndc_nbr
                      AND s.store_nbr = st.store_nbr
                      AND s.ord_dt BETWEEN w.start_dt AND w.end_dt
                      AND n.schd_drug_cd NOT IN (''02'', ''07'')
                      AND n.gen_brand_ind <> ''Y''
                      AND s.orig_on_ord_qty < 1000 -- Arbitrary value used to exclude bad data
                      AND w.week_nbr = &P_WEEK_NBR
                      AND st.area_nbr NOT IN (0, 10, 11)
                      AND st.pharm_ind = ''Y''
                      AND s.store_nbr NOT IN
                                (SELECT store_nbr
                                   FROM dss_saf_user01.rx_ov_ord_exclu_str
                                  WHERE rx_ov_ord_exclu_cd = ''CP'')
                   GROUP BY w.week_nbr, s.store_nbr, s.ndc_nbr) a,
                  (SELECT /*+ INDEX (s,RX_OSV_INVOICE_STR_NDC_PK) */
                          w.week_nbr, s.store_nbr, s.ndc_nbr,
                          SUM (s.inv_qty) AS inv_qty
                     FROM rx_osv_invoice_str_ndc s,
                          ndc n,
                          store st,
                          fiscal_week w
                    WHERE s.ndc_nbr = n.ndc_nbr
                      AND s.store_nbr = st.store_nbr
                      AND s.ord_dt BETWEEN w.start_dt AND w.end_dt
                      AND s.ord_type_cd <> ''F''
                      AND n.schd_drug_cd NOT IN (''02'', ''07'')
                      AND n.gen_brand_ind <> ''Y''
                      AND s.inv_qty > 0
                      AND w.week_nbr = &P_WEEK_NBR
                      AND st.area_nbr NOT IN (0, 10, 11)
                      AND st.pharm_ind = ''Y''
                      AND s.store_nbr NOT IN
                                (SELECT store_nbr
                                   FROM dss_saf_user01.rx_ov_ord_exclu_str
                                  WHERE rx_ov_ord_exclu_cd = ''CP'')
                   GROUP BY w.week_nbr, s.store_nbr, s.ndc_nbr) b
            WHERE a.week_nbr (+) = b.week_nbr
              AND a.store_nbr (+) = b.store_nbr
              AND a.ndc_nbr (+) = b.ndc_nbr
           GROUP BY b.week_nbr, b.store_nbr) t
    ON (t.week_nbr = s.week_nbr
    AND t.store_nbr = s.store_nbr)
    WHEN NOT MATCHED
    THEN
       INSERT (week_nbr, store_nbr, mnd_ov_ord_orig_qty, mnd_ov_inv_qty)
       VALUES (t.week_nbr, t.store_nbr, t.mnd_ov_ord_orig_qty, t.mnd_ov_inv_qty)
    WHEN MATCHED
    THEN
       UPDATE SET
          s.mnd_ov_ord_orig_qty = t.mnd_ov_ord_orig_qty,
          s.mnd_ov_inv_qty = t.mnd_ov_inv_qty';

commit;

drop table temp_extract;

create table temp_build_extract as select * from temp_st_rx_wk_Str_ip_rpt;

Answer 1

您可以尝试：

awk -f e.awk input.txt

其中input.txt是您的输入文件（代码），而e.awk是：

/^merge / {
    if (match($0,/merge into ([^[:blank:]]+)/,a)) {
        print "MERGE|"a[1]
        next
    }
}

/FROM [^(]/ {
    getFromTabs()
    if (match(from,/FROM ([^[:blank:]]+)/,a)) {
        printKey(a[1])
        do {
            ind=index(from,",")
            if (ind) {
                from=substr(from,ind+1)
                match(from,/[[:space:]]*([[:alnum:]]+)/,a)
                printKey(a[1])
            }
        }
        while (ind)
    }
}    

/^drop/ {
    if (match($0,/drop table ([^[:blank:]]+)/,a)) {
        print "DROP|"a[1]
        next
    }

}

/^create/ {
    if (match($0,/create table ([^[:blank:]]+)/,a)) {
        print "CREATE|"a[1]
    }
    if (match($0,/select.*[[:blank:]]([^[:blank:]]+);/,a)) {
        print "SELECT|"a[1]
    }
}

function printKey(key) {
    if (!(key in T)) {
        print "SELECT|"key
        T[key]++
    }
}

function getFromTabs(p) {
    p=0
    from=""
    do {
        from=(p++==0)?$0:(from ORS $0)
        getline
    }
    while (!/WHERE/)
}

对于上面的示例代码，这将生成输出：

MERGE|temp_st_rx_wk_str_ip_rpt
SELECT|rx_ov_ord_excep_str_sku
SELECT|ndc
SELECT|fiscal
SELECT|store
SELECT|dss_saf_user01.rx_ov_ord_exclu_str
SELECT|rx_osv_invoice_str_ndc
DROP|temp_extract;
CREATE|temp_build_extract
SELECT|temp_st_rx_wk_Str_ip_rpt

（请注意，我对SQL一无所知，因此您必须检查这看起来是否正常。）