create table test (cid number(20),pid number(20))
insert into test values ('1','10');
insert into test values ('2','20');
insert into test values ('3','30');
insert into test values ('4','40');
insert into test values ('5','50');
insert into test values ('6','60');
commit;
------------------------------------------
这个表建成以后，如何写一个函数，返回它们的父id，例如：传递1返回10，传递1,2返回10,20，传递1,5,6返回10,50,60呢（Oracle数据库）？
insert into test values ('1','10');
insert into test values ('2','20');
insert into test values ('3','30');
insert into test values ('4','40');
insert into test values ('5','50');
insert into test values ('6','60');
commit;
------------------------------------------
这个表建成以后，如何写一个函数，返回它们的父id，例如：传递1返回10，传递1,2返回10,20，传递1,5,6返回10,50,60呢（Oracle数据库）？
CREATE OR REPLACE FUNCTION fn_chk_pay (in_promo_id VARCHAR2)
RETURN VARCHAR2
IS
out_pay VARCHAR2 (200);
now_pay_num NUMBER (3);
limit_num NUMBER (3);
BEGIN
SELECT COUNT (*)
INTO now_pay_num
FROM tcode a
WHERE code_lgroup = 'J016'
AND use_yn = '1'
AND code_mgroup NOT IN ('50', '51'); SELECT wmsys.wm_concat (value_1), COUNT (*) num
INTO out_pay, limit_num
FROM (SELECT DISTINCT a.value_1
FROM tnew_promo_d a,
(SELECT TO_NUMBER
(DECODE
(LENGTH (in_promo_id),
6, in_promo_id,
SUBSTR
(in_promo_id,
DECODE
(ROWNUM,
1, 1,
INSTR
(in_promo_id,
',',
1,
ROWNUM - 1
)
+ 1
),
6
)
)
) promo_id
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH (in_promo_id)
- LENGTH (REPLACE (in_promo_id,
',',
''
)
)
+ 1) b
WHERE a.data_type = 'B310'
AND a.data_gb = '10'
AND a.use_yn = '1'
AND a.promo_id = b.promo_id
ORDER BY 1); RETURN out_pay;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
NULL;
END fn_chk_pay;
/
我这个传进去是一个字符串,不过固定是6位一个ID的,比如100000,100001,100002
传出来是固定2位一个的,比如是10,20,30
(DECODE
(LENGTH (in_promo_id),
6, in_promo_id,
SUBSTR
(in_promo_id,
DECODE
(ROWNUM,
1, 1,
INSTR
(in_promo_id,
',',
1,
ROWNUM - 1
)
+ 1
),
6
)
)
) promo_id
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH (in_promo_id)
- LENGTH (REPLACE (in_promo_id,
',',
''
)
这段SQL是把传进来的字符串分拆
比如我传进去是100000,100001,100002
分拆后,变成一张表
promo_id
100000
100001
100002
然后再与要查的表关联而身你这种情况,比如说传进去是1,5,6
分拆后就是
cid
1
5
6
然后再把这张表与你的目标表关联查询,查出来就是
PID
10
50
60
然后用
wmsys.wm_concat再合并
/* Formatted on 2008/07/17 15:47 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FUNCTION fn_chk_pid (in_cid VARCHAR2)
RETURN VARCHAR2
IS
out_pid VARCHAR2 (4000);
BEGIN
SELECT wmsys.wm_concat (pid)
INTO out_pid
FROM TEST a,
(SELECT DECODE (INSTR (in_cid, ',', 1, 1),
0, in_cid,
SUBSTR (in_cid,
DECODE (ROWNUM,
1, 1,
INSTR (in_cid,
',',
1,
ROWNUM - 1
)
+ 1
),
DECODE (ROWNUM,
1, INSTR (in_cid, ',', 1, 1) - 1,
LENGTH (in_cid)
- LENGTH (REPLACE (in_cid,
',',
''
)
)
+ 1, LENGTH (in_cid),
INSTR (in_cid, ',', 1, ROWNUM)
- 1
- INSTR (in_cid,
',',
1,
ROWNUM - 1
)
)
)
) cid
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH (in_cid)
- LENGTH (REPLACE (in_cid, ',', ''))
+ 1) b
WHERE a.cid = b.cid; RETURN out_pid;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
NULL;
END fn_chk_pid;
就要用SYS_CONNECT_BY_PATH来实现
当然你也可以用游标来一个拼接
关于SYS_CONNECT_BY_PATH 可以参考此贴用法
http://topic.csdn.net/u/20080505/11/a0958b42-d938-465f-972a-0f61a2969c97.html?seed=491226048
execute immediate temp_sql into out_pid;
所以当初我写这个函数里,就想写个通用性强点的字符串拆分语句,这样写其它语句的时候,可以直接COPY
而且可以直接用在普通的SQL语句里
比如有些字符串是放在字表的字段里,这样可以直接SQL里把字段分拆
@s varchar
as
set @s1='select pid from select pid from test where cid in ('
declare @i int
while charindex(',',@s)=0
begin
set @s1=@s1+substring(@s,1,charindex(',',@s)-1)
set @s=substring(@s,1,charindex(',',@s)-1)
end
set @s1=@s1+')'
exec(@s1)
go
CREATE OR REPLACE FUNCTION GET_STR(STR_IN VARCHAR2) RETURN VARCHAR2 IS
STR_OUT VARCHAR2(4000);
X VARCHAR2(5);
TYPE REFCURSOR IS REF CURSOR;
C_V REFCURSOR;
BEGIN
IF (LENGTH(STR_IN) - LENGTH(REPLACE(STR_IN, ',', ''))) > 999 THEN
RAISE_APPLICATION_ERROR(-20001, 'list too much,limited 1000!pls change it!');
ELSE
OPEN C_V FOR 'SELECT PID FROM TEST WHERE CID IN (' || STR_IN || ')';
LOOP
FETCH C_V
INTO X;
EXIT WHEN C_V%NOTFOUND;
IF LENGTH(STR_OUT) > 0 THEN
STR_OUT := STR_OUT || ',' || X;
ELSE
STR_OUT := X;
END IF;
END LOOP;
CLOSE C_V;
END IF;
RETURN STR_OUT;
END;
/SELECT GET_STR('1,2,3') FROM dual;
返回结果:
10,20,30
SELECT DECODE (INSTR (in_cid, ';', 1, 1),
0, in_cid,
SUBSTR (in_cid,
DECODE (ROWNUM,
1, 1,
INSTR (in_cid,
';',
1,
ROWNUM - 1
)
+ 1
),
DECODE (ROWNUM,
1, INSTR (in_cid, ';', 1, 1) - 1,
LENGTH (in_cid)
- LENGTH (REPLACE (in_cid,
';',
''
)
)
+ 1, LENGTH (in_cid),
INSTR (in_cid, ';', 1, ROWNUM)
- 1
- INSTR (in_cid,
';',
1,
ROWNUM - 1
)
)
)
) cid
FROM DUAL
CONNECT BY ROWNUM <=
LENGTH (in_cid)
- LENGTH (REPLACE (in_cid, ';', ''))
+ 1