IT分销/经销数据库
db2用户自定义函数（表值函数），求高人指点！！
此问题困扰我好久了，本来答案为两条记录，但我这里执行结果为一条，我用临时表弄了好久都没弄出来，一直都报错！！在网上也找了很多资料，有的说自定义函数不能用临时表，有的又说可以，我不知道到底能用不？？如果可以，请告知我解决方法，如果不行，能用别的发案吗？？谢谢!!我的db2 version是9.5的，平台是windows平台！！
create FUNCTION FN_StrToTable(ParamStr varchar(5000))
RETURNS TABLE (
Id Integer, --排序的ID
Param varchar(30)
)
BEGIN ATOMIC
declare Params varchar(30);
declare splits varchar(2);--分隔的符號
DECLARE paramStr1 VARCHAR(5000);
DECLARE id2 Integer;
set splits=',';
SET paramStr1=ParamStr;
SET id2=0;
--对 AgentID 传进来的参数进行处理 因为AgentID是需要INT型
IF (length(paramStr1)>0) THEN
IF(substr(paramStr1,1,length(paramStr1))<>splits) THEN
set paramStr1=paramStr1||splits;
WHILE(Locate(splits,paramStr1)<>0) DO
set id2=id2+1;
set Params=substr(paramStr1,1,Locate(splits,paramStr1)-1);
set paramStr1 =REPLACE(paramStr1,SUBSTR(paramStr1,1,Locate(splits,paramStr1)),'');
END WHILE;
END IF;
END IF;
return
SELECT * FROM (values(id2,Params));
END
DROP function FN_StrToTable
SELECT * FROM TABLE(FN_StrToTable('8,9'))
create FUNCTION FN_StrToTable(ParamStr varchar(5000))
RETURNS TABLE (
Id Integer, --排序的ID
Param varchar(30)
)
BEGIN ATOMIC
declare Params varchar(30);
declare splits varchar(2);--分隔的符號
DECLARE paramStr1 VARCHAR(5000);
DECLARE id2 Integer;
set splits=',';
SET paramStr1=ParamStr;
SET id2=0;
--对 AgentID 传进来的参数进行处理 因为AgentID是需要INT型
IF (length(paramStr1)>0) THEN
IF(substr(paramStr1,1,length(paramStr1))<>splits) THEN
set paramStr1=paramStr1||splits;
WHILE(Locate(splits,paramStr1)<>0) DO
set id2=id2+1;
set Params=substr(paramStr1,1,Locate(splits,paramStr1)-1);
set paramStr1 =REPLACE(paramStr1,SUBSTR(paramStr1,1,Locate(splits,paramStr1)),'');
END WHILE;
END IF;
END IF;
return
SELECT * FROM (values(id2,Params));
END
DROP function FN_StrToTable
SELECT * FROM TABLE(FN_StrToTable('8,9'))
15同行回答
呵呵，小弟在此谢谢大家的回答！！收起
浏览1239
create FUNCTION STT(ParamStr varchar(100))
RETURNS TABLE (
Id Integer,
Param varchar(10)
)
BEGIN ATOMIC
declare Params varchar(10);
declare splits char(1);
DECLARE paramStr1 VARCHAR(100);
set splits=',';
SET paramStr1=ParamStr;
if SUBSTR(paramStr1, length(paramStr1)-1, length(paramStr1)) <> splits then
set paramStr1 = paramStr1 ||splits;
end if;
return
with vt (id, statement, word) as (
values(0, paramStr1, cast('' as varchar(10)))
union all
select id+1, SUBSTR(vt.statement, locate(splits, vt.statement)+1), substr(vt.statement,1, locate(splits, vt.statement)-1) from vt where length(vt.statement) >1 and id < 100000
)
select id, word from vt where id <> 0;
END
C:\Documents and Settings\Administrator>db2 select * from table(stt('5,2'))
ID PARAM
----------- ----------
1 5
2 2
2 条记录已选择。收起
RETURNS TABLE (
Id Integer,
Param varchar(10)
)
BEGIN ATOMIC
declare Params varchar(10);
declare splits char(1);
DECLARE paramStr1 VARCHAR(100);
set splits=',';
SET paramStr1=ParamStr;
if SUBSTR(paramStr1, length(paramStr1)-1, length(paramStr1)) <> splits then
set paramStr1 = paramStr1 ||splits;
end if;
return
with vt (id, statement, word) as (
values(0, paramStr1, cast('' as varchar(10)))
union all
select id+1, SUBSTR(vt.statement, locate(splits, vt.statement)+1), substr(vt.statement,1, locate(splits, vt.statement)-1) from vt where length(vt.statement) >1 and id < 100000
)
select id, word from vt where id <> 0;
END
C:\Documents and Settings\Administrator>db2 select * from table(stt('5,2'))
ID PARAM
----------- ----------
1 5
2 2
2 条记录已选择。收起
浏览1298
浏览1277
哦，那我看看！！收起
浏览1261
浏览1317
with vt (id, statement, word) as (
values(0, '8#9#10#11#15'||'#', '')
union all
select id+1, SUBSTR(vt.statement, locate('#', vt.statement)+1), substr(vt.statement,1, locate('#', vt.statement)-1) from vt where length(vt.statement) >1
)
select id, word from vt where word <> ''
C:\Documents and Settings\Administrator>db2 -td@ -f c:\44.sql
ID WORD
----------- -------------
SQL0347W 递归公共表表达式 "ADMINISTRATOR.VT" 可能包含无限循环。
SQLSTATE=01605
1 8
2 9
3 10
4 11
5 15
已选择 5 条记录，打印 1 条警告消息。收起
values(0, '8#9#10#11#15'||'#', '')
union all
select id+1, SUBSTR(vt.statement, locate('#', vt.statement)+1), substr(vt.statement,1, locate('#', vt.statement)-1) from vt where length(vt.statement) >1
)
select id, word from vt where word <> ''
C:\Documents and Settings\Administrator>db2 -td@ -f c:\44.sql
ID WORD
----------- -------------
SQL0347W 递归公共表表达式 "ADMINISTRATOR.VT" 可能包含无限循环。
SQLSTATE=01605
1 8
2 9
3 10
4 11
5 15
已选择 5 条记录，打印 1 条警告消息。收起
浏览1488
浏览1322
浏览1344
可以利用递归来实现。
参考这个例子改改就可以实现。
drop function split;
create function split (str varchar(100) )
returns table (name varchar(10))
language sql reads sql data no external action deterministic
return
with t ( name ,name1,pos,lay )
as
(
values (str,str , 1 ,1 )
union all
select
case
when LOCATE(',',name1,pos ) =0 then
substr (name1,1)
else
substr(name1 ,pos,LOCATE(',',name1,pos ) - pos )
end case,
case when LOCATE(',',name1,pos ) =0 then
NULL
else
substr(name1 ,LOCATE(',',name1,pos ) +1 )
end case,
1,
lay + 1
from t
where pos<=length(name1)
)
select name from t where lay > 1 ;
select * from table(split('a,v,c,v')) as t;收起
参考这个例子改改就可以实现。
drop function split;
create function split (str varchar(100) )
returns table (name varchar(10))
language sql reads sql data no external action deterministic
return
with t ( name ,name1,pos,lay )
as
(
values (str,str , 1 ,1 )
union all
select
case
when LOCATE(',',name1,pos ) =0 then
substr (name1,1)
else
substr(name1 ,pos,LOCATE(',',name1,pos ) - pos )
end case,
case when LOCATE(',',name1,pos ) =0 then
NULL
else
substr(name1 ,LOCATE(',',name1,pos ) +1 )
end case,
1,
lay + 1
from t
where pos<=length(name1)
)
select name from t where lay > 1 ;
select * from table(split('a,v,c,v')) as t;收起
浏览1369