oracle纯SQL更新插入clob类型字段

男娘i 2023-01-22 05:50 357阅读 0赞

句式:

declare
      bname clob := 'select * from (SELECTorder by ad_code';
    begin
     update dsy_t_query set Q_TABLE = bname where q_id = 'DEBT_ZW_YE_ZJYT';
    commit;
    end;

例子(注意转换单引号):

--2021051717_wuyc_截止政府债务余额分资金用途汇总表_修改条件
    declare
      bname clob := 'select * from (SELECT AD.CODE AD_CODE, max(LPAD('' '',(AD.levelno-2)*2,'' '')|| AD.NAME) AD_NAME, AD.ISLEAF, SUM(ye_qm) ye_qm, SUM(tl_ye) tl_ye, SUM(gl_ye) gl_ye, SUM(gl_gsgl_ye) gl_gsgl_ye, SUM(jc_ye) jc_ye, SUM(szjs_ye) szjs_ye, SUM(szjs_gdjt_ye) szjs_gdjt_ye, SUM(szjs_dxgx_ye) szjs_dxgx_ye, SUM(tdcb_ye) tdcb_ye, SUM(bzxzf_ye) bzxzf_ye, SUM(bzxzf_phgz) bzxzf_phgz, SUM(st_hj_ye) st_hj_ye, SUM(zqjs_ye) zqjs_ye, SUM(jy_amt) jy_amt, SUM(kx_ye) kx_ye, SUM(wh_ye) wh_ye, SUM(ylws_ye) ylws_ye, SUM(shbz_ye) shbz_ye, SUM(lycb_ye) lycb_ye, SUM(nlsl_ye) nlsl_ye, SUM(nlsl_ydfp_ye) nlsl_ydfp_ye, SUM(gk_ye) gk_ye, SUM(syjc_ye) syjc_ye, SUM(wlss_ye) wlss_ye, SUM(nyjc_ye) nyjc_ye, SUM(zrzh_ye) zrzh_ye, SUM(qtxm_ye) qtxm_ye, SUM(fzbxzc_ye) fzbxzc_ye, SUM(wzczq_ye) wzczq_ye FROM DSY_V_ELE_AD AD left join ( SELECT YE.ad_code, YE.ye_qm ye_qm, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''01'' then nvl(YE.ye_qm,0) else 0 end) as tl_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''02'' then nvl(YE.ye_qm,0) else 0 end) as gl_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,4)= ''0201'' then nvl(YE.ye_qm,0) else 0 end) as gl_gsgl_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''03'' then nvl(YE.ye_qm,0) else 0 end) as jc_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''04'' then nvl(YE.ye_qm,0) else 0 end) as szjs_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,4)= ''0401'' then nvl(YE.ye_qm,0) else 0 end) as szjs_gdjt_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,4)= ''0405'' then nvl(YE.ye_qm,0) else 0 end) as szjs_dxgx_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''05'' then nvl(YE.ye_qm,0) else 0 end) as tdcb_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''06'' then nvl(YE.ye_qm,0) else 0 end) as bzxzf_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,4)= ''0604'' then nvl(YE.ye_qm,0) else 0 end) as bzxzf_phgz, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''07'' then nvl(YE.ye_qm,0) else 0 end) as st_hj_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''08'' then nvl(YE.ye_qm,0) else 0 end) as zqjs_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''09'' then nvl(YE.ye_qm,0) else 0 end) as jy_amt, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''10'' then nvl(YE.ye_qm,0) else 0 end) as kx_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''11'' then nvl(YE.ye_qm,0) else 0 end) as wh_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''12'' then nvl(YE.ye_qm,0) else 0 end) as ylws_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''13'' then nvl(YE.ye_qm,0) else 0 end) as shbz_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''14'' then nvl(YE.ye_qm,0) else 0 end) as lycb_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''15'' then nvl(YE.ye_qm,0) else 0 end) as nlsl_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''16'' then nvl(YE.ye_qm,0) else 0 end) as gk_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,3)= ''817'' then nvl(YE.ye_qm,0) else 0 end) as syjc_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,3)= ''818'' then nvl(YE.ye_qm,0) else 0 end) as wlss_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''19'' then nvl(YE.ye_qm,0) else 0 end) as nyjc_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,2)= ''20'' then nvl(YE.ye_qm,0) else 0 end) as zrzh_ye, (case when YE.ZJYT_ID LIKE ''01%'' and substr(ye.zjyt_id,1,4)<>''0102'' AND substr(YE.XMFL_ID,0,8)= ''15010101'' then nvl(YE.ye_qm,0) else 0 end) as nlsl_ydfp_ye, (case when substr(ye.zjyt_id,1,4)=''0102'' or (ye.zjyt_id is not null and substr(ye.zjyt_id,1,2)=''01'' and substr(ye.zjyt_id,1,4)<>''0102'' and (ye.xmfl_id is null or substr(YE.XMFL_ID,0,2)= ''99'')) then nvl(YE.ye_qm,0) else 0 end) as qtxm_ye, (CASE WHEN YE.ZJYT_ID LIKE ''02%'' THEN nvl(YE.ye_qm,0) ELSE 0 END) as fzbxzc_ye, (CASE WHEN YE.ZJYT_ID is null THEN nvl(YE.ye_qm,0) ELSE 0 END) as wzczq_ye FROM Debt_t_Fact_Zqzwye ye LEFT JOIN DSY_V_ELE_AG AG ON YE.AG_ID = AG.guid AND YE.ad_code = ag.PROVINCE where 1=1 and ye.pe_id = ''${peid}'' ${if(zwlx_id=='''',""," and ye.zwlb_id like ''"+zwlx_id+"%''")} ${if(zwlx=='''',""," and ye.zwlb_id like ''"+zwlx+"%''")} ${if(dataType=='''',""," and ye.data_type= ''"+dataType+"''")} ${if(len(adid) == 0,""," and ye.ad_code in (''" + REPLACE(adid ,",","'',''") + "'')")} ) T ON T.AD_CODE like AD.CODE||''%'' WHERE 1=1 ${if(len(adid) == 0," "," and AD.code in (''" + REPLACE(adid ,",","'',''") + "'')")} group by AD.code,ad.name,AD.ISLEAF) where abs(nvl(ye_qm,0)) <> 0 order by ad_code';
    begin
     update dsy_t_query set Q_TABLE = bname where q_id = 'DEBT_ZW_YE_ZJYT';
    commit;
    end;