17,377
社区成员
发帖
与我相关
我的任务
分享建函数问题_如何返回父id？
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数据库）？
...全文
请发表友善的回复…
发表回复
fuyou001 2009-05-27
- 打赏
- 举报
学习
hebo2005 2008-09-16
- 打赏
- 举报
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
oracledbalgtu 2008-07-17
- 打赏
- 举报
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
[Quote=引用楼主 mimanginging 的帖子:]
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返回…
[/Quote]
tianhuo_soft 2008-07-17
- 打赏
- 举报
Create Procedure Search
@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
hebo2005 2008-07-17
- 打赏
- 举报
不过动态SQL写起来太麻烦了,容易写错'
所以当初我写这个函数里,就想写个通用性强点的字符串拆分语句,这样写其它语句的时候,可以直接COPY
而且可以直接用在普通的SQL语句里
比如有些字符串是放在字表的字段里,这样可以直接SQL里把字段分拆
所以当初我写这个函数里,就想写个通用性强点的字符串拆分语句,这样写其它语句的时候,可以直接COPY
而且可以直接用在普通的SQL语句里
比如有些字符串是放在字表的字段里,这样可以直接SQL里把字段分拆
hebo2005 2008-07-17
- 打赏
- 举报
其实函数里可以用另一种更简单的方法,就是动态sql
temp_sql :='select wmsys.wm_concat (pid) from test where cid in('||in_cid||')';
execute immediate temp_sql into out_pid;
temp_sql :='select wmsys.wm_concat (pid) from test where cid in('||in_cid||')';
execute immediate temp_sql into out_pid;
hebo2005 2008-07-17
- 打赏
- 举报
如果是9I的,就不能用wmsys.wm_concat
就要用SYS_CONNECT_BY_PATH来实现
当然你也可以用游标来一个拼接
关于SYS_CONNECT_BY_PATH 可以参考此贴用法
http://topic.csdn.net/u/20080505/11/a0958b42-d938-465f-972a-0f61a2969c97.html?seed=491226048
就要用SYS_CONNECT_BY_PATH来实现
当然你也可以用游标来一个拼接
关于SYS_CONNECT_BY_PATH 可以参考此贴用法
http://topic.csdn.net/u/20080505/11/a0958b42-d938-465f-972a-0f61a2969c97.html?seed=491226048
hebo2005 2008-07-17
- 打赏
- 举报
我改了下,可以拆分任意字符串,不需要固定长度的,你去试试看
任意输入1,2,4
都能查出来
任意输入1,2,4
都能查出来
hebo2005 2008-07-17
- 打赏
- 举报
我帮你写一下
/* 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;
oraclelogan 2008-07-17
- 打赏
- 举报
[Quote=引用 4 楼 hebo2005 的回复:]
其中
SQL codeSELECTTO_NUMBER
(DECODE
(LENGTH (in_promo_id),6, in_promo_id,
SUBSTR
(in_promo_id,
DECODE
(…
[/Quote]
关注之中啊。
其中
SQL codeSELECTTO_NUMBER
(DECODE
(LENGTH (in_promo_id),6, in_promo_id,
SUBSTR
(in_promo_id,
DECODE
(…
[/Quote]
关注之中啊。
wudileiyu 2008-07-17
- 打赏
- 举报
好长啊
hebo2005 2008-07-17
- 打赏
- 举报
其中
这段SQL是把传进来的字符串分拆
比如我传进去是100000,100001,100002
分拆后,变成一张表
promo_id
100000
100001
100002
然后再与要查的表关联
而身你这种情况,比如说传进去是1,5,6
分拆后就是
cid
1
5
6
然后再把这张表与你的目标表关联查询,查出来就是
PID
10
50
60
然后用
wmsys.wm_concat再合并
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,
',',
''
)
这段SQL是把传进来的字符串分拆
比如我传进去是100000,100001,100002
分拆后,变成一张表
promo_id
100000
100001
100002
然后再与要查的表关联
而身你这种情况,比如说传进去是1,5,6
分拆后就是
cid
1
5
6
然后再把这张表与你的目标表关联查询,查出来就是
PID
10
50
60
然后用
wmsys.wm_concat再合并
hebo2005 2008-07-17
- 打赏
- 举报
10G 的话可以参考我这个
我这个传进去是一个字符串,不过固定是6位一个ID的,比如100000,100001,100002
传出来是固定2位一个的,比如是10,20,30
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
wudileiyu 2008-07-17
- 打赏
- 举报
关注
hebo2005 2008-07-17
- 打赏
- 举报
主要是要把字符串分拆
