动态执行sql
oracle游标的使用：
首先声明一个包，在包中定义一个游标，然后再存储过程中调用即可。
CREATE OR REPLACE PACKAGE ZF_PACKAGE AS
TYPE PB_CURSOR IS REF CURSOR;
end ZF_PACKAGE ;
动态拼接sql
create or replace procedure ZF_XXXX_XXX( p_bspl in varchar2, p_sjqj in varchar2, p_bblx in varchar2, p_tzlx in varchar2, p_xmjl in varchar2, p_gm in varchar2, p_jjmc in varchar2, p_maxSize out varchar2, p_error out varchar2, p_error_info out varchar2, p_CURSOR out PB_PACKAGE.PB_CURSOR ) is v_yb_date varchar2(20); v_tz_tzlx varchar2(200); v_sql varchar2(4000); v_str2 varchar2(4000); v_str long; begin if p_bspl='01' then select to_char(add_months(to_date(p_sjqj,'YYYYMMDD'),1)-1 ,'YYYYMMDD')into v_yb_date from dual; end if; if p_tzlx ='01' then v_str2 := ' and A.Amac_Fund_Type in(1,2,3,4,5,6,7,8,9,10,11,12)'; end if; if p_tzlx ='02' then v_str2 := ' and A.Amac_Fund_Type in(1)'; end if; if p_tzlx ='03' then v_str2 := ' and A.Amac_Fund_Type in(2)'; end if; if p_tzlx ='04' then v_str2 := ' and A.Amac_Fund_Type in(3,4,5,6,7,8,9,10,11,12)'; end if; execute immediate 'SELECT count(1) from t_fund_info A left join tb_yygl_xxplbbgl_log tyxl on tyxl.fund_code=A.fund_code and substr(tyxl.bb_qj,0,6)=substr('''||20170417||''',0,6) left join (select fund_id,telephone,email,user_name from temp_xxpl_lxr a where not exists ( select 1 from temp_xxpl_lxr b where a.fund_id = b.fund_id and b.id < a.id )) h on a.fund_code = h.fund_id ,t_service_valu_rela tsvr ,VB_PORT_BASEINFO vpb ,vf_report_val val_811 ,vf_report_val val_841 ,t_fm B ,st_code_value C , amac_fund_info f , t_sys_user_id g WHERE A.fm_id = B.fm_id and a.fund_code = tsvr.fund_code and tsvr.fund_code_valu_web = vpb.c_port_code and val_811.c_port_code = vpb.c_port_code and val_811.c_subj_code = ''811'' and val_841.c_port_code = vpb.c_port_code and val_841.c_subj_code = ''841'' AND A.fund_status = C.valu and c.grup = ''FUND_STATUS'' AND A.fund_status IN (''8'', ''9'') and a.xmjl = g.id and a.fundno = f.fundno and val_841.d_biz = to_date('''||20170417||''', ''YYYYMMDD'') and val_811.d_biz = to_date('''||20170417||''', ''YYYYMMDD'') and ('''||p_xmjl||''' is null or A.XMJL='''||p_xmjl||''') and (a.fund_code= '''||p_jjmc||''' or '''||p_jjmc||''' is null ) '|| v_str2 ||' AND ('''||p_gm||''' != ''01'' or (val_811.N_hldcst_locl >= 50000000 OR val_841.N_HLDMKV_LOCL >= 50000000)) AND A.fund_code NOT IN (SELECT A.fund_code FROM t_fund_info A, t_SERVICE_VALUE B WHERE A.fund_code = B.FUNDID AND B.SERVICE_ID = 8) and ('''||p_matchedCloumn||''' is null or A.FUND_NAME like ''%'||p_matchedCloumn||'%'' or B.fm_name like ''%'||p_matchedCloumn||'%'' )' into p_maxSize ; open p_CURSOR for 'select * from ( select rownum rn, ff.* from ( SELECT A.fund_code, b.cont_name cont_name, b.record_number recordnnumber, decode(tyxl.beforstatus,''0'',''0'','''') beforedxstatus, decode(tyxl.afterstatus,''1'',''1'','''') afterdxstatus, A.FUND_NAME fundname, A.SHORT_NAME, B.fm_name fmname, C.name status, f.fundtype fundtype, f.fundno fundno, to_char(vpb.d_establish, ''YYYY-MM-DD'') destablish, A.XMJL, g.username username, trim(to_char(nvl(val_811.N_hldcst_locl, 0),''9,999,999,999,990.99'')) as ssjj, trim(to_char(nvl(val_841.N_hldmkv_locl, 0),''9,999,999,999,990.99'')) as dqjzc, h.telephone mobile, h.email email, h.user_name xp_lxr_name, case when (h.telephone is not null and h.email is not null) then ''1'' else ''0'' end isxpstatus FROM t_fund_info A left join tb_yygl_xxplbbgl_log tyxl on tyxl.fund_code=A.fund_code and substr(tyxl.bb_qj,0,6)=substr('''||20170417||''',0,6) left join (select fund_id,telephone,email,user_name from temp_xxpl_lxr a where not exists ( select 1 from temp_xxpl_lxr b where a.fund_id = b.fund_id and b.id < a.id )) h on a.fund_code = h.fund_id ,t_service_valu_rela tsvr ,VB_PORT_BASEINFO vpb ,vf_report_val val_811 ,vf_report_val val_841 ,t_fm B ,st_code_value C , amac_fund_info f , t_sys_user_id g WHERE A.fm_id = B.fm_id and a.fund_code = tsvr.fund_code and tsvr.fund_code_valu_web = vpb.c_port_code and val_811.c_port_code = vpb.c_port_code and val_811.c_subj_code = ''811'' and val_841.c_port_code = vpb.c_port_code and val_841.c_subj_code = ''841'' AND A.fund_status = C.valu and c.grup = ''FUND_STATUS'' AND A.fund_status IN (''8'', ''9'') and a.xmjl = g.id and val_841.d_biz = to_date('''||20170417||''', ''YYYYMMDD'') and val_811.d_biz = to_date('''||20170417||''', ''YYYYMMDD'') and ('''||p_xmjl||''' is null or A.XMJL='''||p_xmjl||''') and (a.fund_code= '''||p_jjmc||''' or '''||p_jjmc||''' is null ) '|| v_str2 ||' and a.fundno = f.fundno AND ('''||p_gm||''' != ''01'' or (val_811.N_hldcst_locl >= 50000000 OR val_841.N_HLDMKV_LOCL >= 5000000)) AND A.fund_code NOT IN (SELECT A.fund_code FROM t_fund_info A, t_SERVICE_VALUE B WHERE A.fund_code = B.FUNDID AND B.SERVICE_ID = 8) and ('''||p_matchedCloumn||''' is null or A.FUND_NAME like ''%'||p_matchedCloumn||'%'' or B.fm_name like ''%'||p_matchedCloumn||'%'' ) ) ff where rownum <= '||p_maxNUM||') where rn >= '||p_minNUM||''; exception when others then rollback; p_error := sqlcode; p_error_info := sqlerrm; end ZF_XXXX_XXX;
