在写存储过程中,要把客户的‘科目编码’先拆分出来比如说1234,1212,1234这几个拆分成单个的,然后把这单个的字符用一个串能拼起来,可以使用LIKE在查询条件中同事使用,在使用中,每个单个字符要加单引号,如‘1234’,‘1212’等,中间要加逗号,请高手指教,存储过程中拆分的编码如下,
starttimes:=1;
sposition1:=1;
loop
sposition2:=instr(Acclist,',',1,starttimes);
mysubstr:=substr(Acclist,sposition1,sposition2-sposition1);
sposition1:=sposition2+1;
starttimes:=starttimes+1;
--mysubstr=
-- sqlwhere:=b.subjcode like '||'||sqlwhere||mysubstr;
exit when sposition2=0;
end loop;
麻烦看怎么拼出来,谢谢大家了
starttimes:=1;
sposition1:=1;
loop
sposition2:=instr(Acclist,',',1,starttimes);
mysubstr:=substr(Acclist,sposition1,sposition2-sposition1);
sposition1:=sposition2+1;
starttimes:=starttimes+1;
--mysubstr=
-- sqlwhere:=b.subjcode like '||'||sqlwhere||mysubstr;
exit when sposition2=0;
end loop;
麻烦看怎么拼出来,谢谢大家了
----你这样处理不行吗?干嘛要去拼接呢?
with tt as
(
select 1234 科目编码 from dual union all
select 1256 科目编码 from dual union all
select 1814 科目编码 from dual
)
select k.*
from (select wm_concat(to_char(科目编码)) km from tt) k
where k.km like '%1%'
--创建函数就可以了:
CREATE OR REPLACE FUNCTION get_code(p_code IN VARCHAR2 )
RETURN VARCHAR2
IS
result VARCHAR2(4000):='''';
BEGIN
FOR i IN 1..length(p_code) LOOP
IF SubStr(p_code,i,1) = Chr(44) THEN
result:=result||''''||','||'''';
GOTO continue;
ELSE
result:=result||SubStr(p_code,i,1);
END IF;
<<continue>>
NULL ;
END LOOP ;
RETURN result||'''';
END;--测试:
SELECT get_code('1234,1212,12') FROM dual;GET_CODE('1234,1212,12')
-----------------------
'1234','1212','12'
''''||REPLACE('1234,1212,1234'
------------------------------
'1234','1212','1234'
SQL>
我咋没想到用replace呢???
结果是:,'1234','456','789',
如果需要可以用trim将两端的','去除
--经验证两端不加,也可以使用
select regexp_replace('1234,456,5890,789', '(,?)(\d+)(,?)', '\1''\2''\3') from dual
create or replace procedure PRO_WLACC_I_EndToNOAHARK(BeginTime varchar2,EndTime varchar2,SupType varchar2,Acclist varchar2,cEntityCode varchar2)
as
e_year VARCHAR2(6);
e_month VARCHAR2(4);
k_pk_corp VARCHAR2(20);
k_pk_glorgbook VARCHAR2(100);
k_pk_glbook VARCHAR2(100);
sposition1 INTEGER;
sposition2 INTEGER;
mysubstr VARCHAR2(10);
starttimes INTEGER;
sqlwhere VARCHAR2(500);
begin
e_year := SUBSTR(EndTime,1,4);
e_month := SUBSTR(EndTime,6,2);
starttimes:=1;
sposition1:=1;
loop
sposition2:=instr(srcStr,',',1,starttimes);
mysubstr:=substr(srcStr,sposition1,sposition2-sposition1);
sposition1:=sposition2+1;
starttimes:=starttimes+1;
mysubstr:=' '''||mysubstr||'%''';
sqlwhere:= ' or b.subjcode like ' || mysubstr;
exit when sposition2=0;
end loop;
select corp.pk_corp,gb.pk_glorgbook,gb.pk_glbook into k_pk_corp,k_pk_glorgbook,k_pk_glbook
from bd_corp corp,bd_glorg gl, bd_glorgbook gb
where corp.unitcode =cEntityCode and NVL (gl.dr, 0) = 0 AND NVL (gb.dr, 0) = 0
AND corp.pk_corp = gl.pk_entityorg AND gl.pk_glorg = gb.pk_glorg and gb.type = 0;
delete from TBL_Trans_inter_data_ict where Entity=cEntityCode;
if SupType='0000' then
insert into TBL_Trans_inter_data_ict
select 1 as datatype,'' as vouchid,'' as vouno ,lrzxbm as profit,ywfbbm as bustype,
'' as sap3,'' as TR_FenPei,'' as TR_ShortText,'' as TR_LongText, case when balanorient=1 then '借' else '贷' end as Direct,
'0' as SAPFJZ,'' as VouDate,''
as IsReturned ,'' as BookDate ,ksbm as SupCode,corp.unitcode as Entity,'' expensesAcc,code as AcctCode,
currtypecode as TR_CUR,'' as TR_AMT,
'' as TR_LAMT, amount_yb as TR_UnOffAMT,amount_bb as TR_LUnOffAMT,
'' as TR_RATE,'' as AgingDate,'' as OffGroup,'' as OffDate,'' as IsDelete,'' as isInvalid,'' as IsOffed, '' as VouUser,'NC' as FinType,'' as UpdateTime
from (select substr(b.subjcode, 1, 6) as code,
tz.valuecode as ksbm,
tz.valuename as ksmc,
sum(debitamount) - sum(creditamount)
as amount_yb,
sum(localdebitamount) - sum(localcreditamount)
as amount_bb,
tz1.valuecode as ywfbbm,
tz1.valuename as ywfbmc,
tz2.valuecode as lrzxbm,
tz2.valuename as lrzxmc,
b.balanorient,
curr.currtypecode,
a.pk_corp
from (SELECT debitamount, creditamount, localdebitamount, localcreditamount, assid, pk_accsubj,pk_currtype,pk_corp
from gl_balance
where period = '00'
and year = e_year
and nvl(dr, 0) = 0
and pk_corp =k_pk_corp
and pk_glorgbook=k_pk_glorgbook
union all
select
b.debitamount,
b.creditamount,
b.localdebitamount,
b.localcreditamount,
b.assid,
pk_accsubj,
b.pk_currtype,a.pk_corp
from gl_voucher a, GL_DETAIL b
where a.PK_VOUCHER = b.PK_VOUCHER
and nvl(a.dr, 0) = 0
and nvl(b.dr, 0) = 0
and a.period > '00'
下面这里的条件要用到上面科目编码
所以必须要先拆分,然后在做连接,能在后面的条件中拼出来!
-- 说实话还是不大明白,只知道你初始的字符串是1234,456,5890,789这种格式,要转换成什么格式的你
-- 还没有表达明白,我列出两种转换方式你看下符合你的需求不-- 1. 拆分
select regexp_substr(col,'\d+', 1, level) from
(select '1234,456,5890,789' col from dual t)
connect by level <= length(regexp_replace(col, ',?\d+,?', '@'));-- 2. 每个数字外面增加单引号
select regexp_replace('1234,456,5890,789', '(,?)(\d+)(,?)', '\1''\2''\3') from dual