请高手帮忙将oracle函数改写成sql server函数
0
悬赏园豆：10
[已解决问题]
解决于 2013-12-06 10:28
函数1：
create or replace
FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN str_split --CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000);写在创建函数之前
--函数功能：本函数可以将“目标字符串”以“指定字符串”进行拆分，并通过表结构返回结果
--日期：2009-10-14
PIPELINED--pipelined必须返回一个集合类型
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start);
IF v_index = 0
THEN
PIPE ROW(SUBSTR(p_string, v_start));-- pipe row () 语句来送出要返回的表中的每一行
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP;
RETURN;
END splitstr;
函数2：
create or replace
function fn_splitData_jqzhxx(p_yearFlag IN VARCHAR2)
return temp_Table
--创建函数之前执行 :
--create or replace type Row_type as object(qyid int,qccpzl varchar2(10),qyssdy int,
--yearflag varchar2(10),nmcyry int,qnzcz int,xcpcz int,gyzjz int,gyxscz int,ckjhz int);--定义行对象
--create or replace type temp_Table as table of Row_type; --定义表对象
--参数说明:p_yearFlag 年份
--函数功能：用自定义的分割函数splitstr将每行的字符串分割，并且插入到temp_Table表中.用于集群综合信息统计
--日期：2009-10-16
PIPELINED
as
tabRow Row_type;--定义v为行对象类型
begin
for thisrow in (select jj.QYID,NVL(qccpzl,'06') qccpzl,jb.QYSSDY,yearflag,
nvl(nmcyry,0) nmcyry,nvl(qnzcz,0) qnzcz,nvl(xcpcz,0) xcpcz,
nvl(gyzjz,0) gyzjz,nvl(gyxscz,0) gyxscz,nvl(ckjhz,0) ckjhz
from QY_JBXX jb,QY_JJXX jj
where jb.QYID=jj.QYID and jj.yearflag =p_yearFlag
and (length(qccpzl)>2 or qccpzl is null)) loop
--第一层循环，查询出企业产品种类有多个的数据
for innerRow in (select * from table(splitstr(thisrow.qccpzl,','))) loop
--第二层循环，用自定义的分割函数splitstr将每行的qccpzl字符串分割，并且插入到temp_Table表中。
--比如01,02就会生成两行数据,除了产品种类不同外，其它数据都相同，这样会将一个企业的数据统计两次
tabRow:=Row_type(thisrow.QYID,innerRow.column_value,thisrow.QYSSDY,thisrow.yearflag,
thisrow.nmcyry,thisrow.qnzcz,thisrow.xcpcz,thisrow.gyzjz,thisrow.gyxscz,thisrow.ckjhz);
pipe row (tabRow);
end loop;
end loop;
return;
end;
最佳答案
0
在MSSQL 中类似这样写 分割函数
--@strings 为要分割的字符串，@splitChar 分隔符。
create function fn_split(@strings nvarchar(4000),@splitChar nvarchar(100))
returns @splitTbl table (col nvarchar(1000))
as
begin
declare @CI int;
declare @splitItem nvarchar(1000);
set @CI=charIndex(@splitChar,@strings);
while @CI>0
begin
set @splitItem=substring(@strings, 1, @CI-1);
set @strings=substring(@strings, @CI+len(@splitChar),len(@strings)-@CI);
set @CI=charIndex(@splitChar,@strings);
insert @splitTbl select @splitItem;
end
insert @splitTbl select @strings;
return;
end
收获园豆：10
感谢您的回答。请问第二个函数的for循环部分改成sqlserver中while循环呢，其中的thisrow、innerRow该如何理解，帮忙解释一下，谢谢啊！
@myselfnow: 其中的thisrow、innerRow 应该类似sqlserver中的游标吧 也就是取表中的每一行数据。
游标，怎么也没有预先定义呢，循环这部分不理解啊，对应的都是表，能不能帮改下，分先给你了
@myselfnow: 你是想把一个表中满足条件的多行数据中的某列数据根据特定的分隔符分隔后返回一个表形式的数据集吗
也就是二层for循环。
1：第一层是取满足条件的所有行的数据集。
2：第二层是包在第一层里面的，用第一层的每一行数据 thisrow；再把thisrow.qccpzl的值传到 splitstr(thisrow.qccpzl,',')函数里，把thisrow.qccpzl分割后形成一个表集合。
3：再把第二层for分割后的数据与第一层数据组成多个行。调用代码：Row_type(thisrow.QYID,innerRow.column_value,thisrow.QYSSDY,thisrow.yearflag, thisrow.nmcyry,thisrow.qnzcz,thisrow.xcpcz,thisrow.gyzjz,thisrow.gyxscz,thisrow.ckjhz);
4：最后把生成的总的表集合返回。
for thisrow in (select jj.QYID,NVL(qccpzl,'06') qccpzl,jb.QYSSDY,yearflag,
nvl(nmcyry,0) nmcyry,nvl(qnzcz,0) qnzcz,nvl(xcpcz,0) xcpcz,
nvl(gyzjz,0) gyzjz,nvl(gyxscz,0) gyxscz,nvl(ckjhz,0) ckjhz
from QY_JBXX jb,QY_JJXX jj
where jb.QYID=jj.QYID and jj.yearflag =p_yearFlag
and (length(qccpzl)>2 or qccpzl is null)) loop
for innerRow in (select * from table(splitstr(thisrow.qccpzl,','))) loop tabRow:=Row_type(thisrow.QYID,innerRow.column_value,thisrow.QYSSDY,thisrow.yearflag, thisrow.nmcyry,thisrow.qnzcz,thisrow.xcpcz,thisrow.gyzjz,thisrow.gyxscz,thisrow.ckjhz);
pipe row (tabRow);
end loop;
end loop;
是的，改写成sql server函数写法
@myselfnow: 我写了一下，其实我解析已很清楚了， 不过我也没有oracle的经验，但代码一看就能看懂的，和sqlserver写法差不多。
如下代码在sqlserver 2005 上运行测试通过
create table tbl2
(
c1 varchar(100),
c2 varchar(100)
)
insert into tbl2
values('01','g,x,z');
insert into tbl2
values('02','m,f,k');
create function fn_split(@strings nvarchar(4000),@splitChar nvarchar(100))
returns @splitTbl table (col nvarchar(1000))
as
begin
declare @CI int;
declare @splitItem nvarchar(1000);
set @CI=charIndex(@splitChar,@strings);
while @CI>0
begin
set @splitItem=substring(@strings, 1, @CI-1);
set @strings=substring(@strings, @CI+len(@splitChar),len(@strings)-@CI);
set @CI=charIndex(@splitChar,@strings);
insert @splitTbl select @splitItem;
end
insert @splitTbl select @strings;
return;
end
create function fn_GetSplitList()
returns @splitTable table(c1 varchar(100), c2 varchar(100))
as
begin
declare @c1 varchar(100),@c2 varchar(100);
declare c_cursor1 cursor
for select c1,c2 from tbl2;
open c_cursor1;
fetch next from c_cursor1 into @c1,@c2;
while @@fetch_status=0
begin
insert into @splitTable
select @c1, col from dbo.fn_split(@c2,',');
fetch next from c_cursor1 into @c1,@c2;
end
close c_cursor1;
deallocate c_cursor1;
return;
end
select * from dbo.fn_GetSplitList();
你看一下，就会明白了。
非常感谢，感谢您的热心回答。
