oracle执行拼接的sql,oracle中拼接sql过长的解决方法

--定义一个变量游标v_cursor   number;

if v_begin_audit_date < v_end_audit_date then     --申报书、合同书、验收书状态推送到微信

v_sql:='insert into we_chat_push_data(id,key_code,old_status,old_status_name,status,status_name,op_type,psn_code,psn_name,op_org_code,op_org_name,op_psn_code,

op_psn_name,op_role,comment_type,comment_date,audit_comment,zh_name,mobile,email) select ctr_seq.nextval,ac.key_code,ac.status,cd.zh_cn_caption,c.status,wcpc.remark,ac.op_type,

c.submit_psn_code,p1.zh_name,o.org_code,o.name,p2.psn_code,p2.zh_name,ac.role_id,ac.comment_type,ac.comment_date,ac.audit_comment,p.zh_title,p2.mobile,p2.email

from audit_comment ac inner join we_chat_push_const wcpc on ac.comment_type = wcpc.key_type and ac.status = wcpc.status and ac.op_type = wcpc.op_type

left join const_dictionary cd on ac.status = cd.code ';

if v_push_type='proposal' then

v_sql:=v_sql||'and cd.category = ''proposal_status'' left join proposal c left join proposal_cached p on c.pos_code=p.pos_code on ac.key_code = c.prp_code left join const_dictionary cdd on c.status = cdd.code and cdd.category = ''proposal_status''';

v_sql:=v_sql||' left join person p1 on c.psn_code = p1.psn_code ';

elsif v_push_type='contract' then

v_sql:=v_sql||'and cd.category = ''contract_status'' left join contract c on ac.key_code = c.ctr_code left join project p on c.prj_code = p.prj_code left join const_dictionary cdd on c.status = cdd.code and cdd.category = ''contract_status''';

v_sql:=v_sql||'left join person p1 on c.psn_code = p1.psn_code ';

elsif v_push_type='acceptance' then

v_sql:=v_sql||'and cd.category = ''acceptance_appstatus'' left join acceptance c on ac.key_code = c.act_code  left join project p on c.prj_code = p.prj_code left join const_dictionary cdd on c.status = cdd.code and cdd.category = ''acceptance_appstatus''';

v_sql:=v_sql||'left join person p1 on c.submit_psn_code = p1.psn_code ';

elsif v_push_type='acceptance_final' then

v_sql:=v_sql||'and cd.category = ''acceptance_status'' left join acceptance c on ac.key_code = c.act_code  left join project p on c.prj_code = p.prj_code left join const_dictionary cdd on c.status = cdd.code and cdd.category = ''acceptance_status''';

v_sql:=v_sql||'left join person p1 on c.submit_psn_code = p1.psn_code ';

end if;

v_sql:=v_sql||'left join person p2 on ac.psn_code = p2.psn_code left join organization o on p2.org_code = o.org_code where ac.comment_date > :v_begin_audit_date and ac.comment_date <= :v_end_audit_date and ac.comment_type =:v_push_type order by ac.comment_date';

v_cursor := dbms_sql.open_cursor;--打开游标

dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);--解析sql

dbms_sql.bind_variable(v_cursor, ':v_begin_audit_date', v_begin_audit_date);   --绑定变量

dbms_sql.bind_variable(v_cursor, ':v_end_audit_date', v_end_audit_date);

dbms_sql.bind_variable(v_cursor, ':v_push_type', v_push_type);

--dbms_output.put_line(v_cursor);

v_stat :=dbms_sql.execute(v_cursor);--执行

dbms_sql.close_cursor(v_cursor);--关闭游标

--execute immediate (v_sql);

commit;

update we_chat_push_index wcpi

set wcpi.max_date = v_end_audit_date

where wcpi.key_type = 'max_audit_to_wechat' and wcpi.push_type=v_push_type;

commit;

主要是使用oracle中dbms_sql包;