问题描述
create or replace package body pck_imp_puryewu is procedure searchList( i_FLDPURCONTRACTNO in varchar2, i_JK_DDH in varchar2, i_FLDCREATEDATE in varchar2, i_VERIFY in varchar2, i_userId in varchar2, o_retCode out varchar2, o_tableHead out varchar2, o_tablewidth out varchar2, o_resultRef out REF_SEARCH ) is v_sql varchar2(3000); begin o_retCode := 0; v_sql := ' select FLDPURAGEID,FLDPURAGEID||''!''||JK_KHDDH||''!''||CONTRACTNO||''!''||Jk_Dntype||''!''||Fldcreatedate||''!''||FLDPURCHASER||''!''||'; v_sql := v_sql || 'FLDAGENTNAME||''!''||FLDCURCODE||''!''||FLDCOMPANY||''!''||FLDAGENTDATE||''!''||'; v_sql := v_sql || 'FLDIMPORTPORT||''!''||FLDBANKSTYLE||''!''||FLDCurrency||''!''||jk_rate'; v_sql := v_sql || ' from TBLPURPLANINFO where VERIFY=' || i_VERIFY; if length(i_FLDPURCONTRACTNO) > 0 then v_Sql := v_Sql || ' and FLDPURAGEID = ''' || i_FLDPURCONTRACTNO || ''''; end if; if length( i_FLDCREATEDATE) > 0 then v_sql := v_Sql || ' and FLDCREATEDATE = ''' || i_FLDCREATEDATE || ''''; end if; if length(i_JK_DDH) > 0 then v_sql := v_Sql || ' and JK_KHDDH = ''' || i_JK_DDH || ''''; end if; if length(i_userId) > 0 then v_sql := v_Sql || ' and FLDPURCHASER = ''' || i_userId || ''''; end if; o_tableHead := '委托书号#客户订单号#合同协议号#单据类型#录入日期#采购员#委托方名称#供应商名称#事业部名称#委托日期#进口口岸#付款方式#币制#汇率'; o_tablewidth := '100#100#100#100#100#100#300#300#100#100#100#100#100#100'; open o_resultRef for v_sql; exception WHEN OTHERS THEN o_retCode := -1; --操作异常 DBMS_OUTPUT.PUT_LINE(SQLERRM); rollback; end;
解决方案
||的作用是拼接字符串!的作用是分隔符一般会用123,123,123来表示一条记录的3个字段的值，这里的都好起到了分隔符的作用。而写这个sql的人，没用逗号来做分隔符，而是用了叹号做分隔符，上面的那个例子就变成了123!123!123