项目中一个有用的函数

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


create FUNCTION dbo.fun_sys_GetTableBySplit --返回表
(
 @str varchar(8000),  --字符串 如：  ",1,2,3,"
 @splitstr nvarchar(10) --分隔符 如： ","
)
RETURNS @retTables TABLE 
(
   No int, 
   MyName nvarchar(50)
)
AS
BEGIN  
 declare  @i int, @j int,@count int,@splitstrlen int
  set @i=1
  set @j=1 
  set @count=1  
  set @splitstrlen=len(@splitstr)
  if left(@str,len(@splitstr))!=@splitstr
set @str= substring(@str,CHARINDEX(@splitstr,@str,@i+@splitstrlen),len(@str)-CHARINDEX(@splitstr,@str,@i+@splitstrlen)+1)
 while(@i>0)
  begin
  set @j=@i+@splitstrlen;   
   set @i=CHARINDEX(@splitstr,@str,@i+@splitstrlen)
   if(@i-@j>=0)
     begin       
       insert @retTables values(@count,substring(@str,@j,@i-@j))
       set @count=@count+1
     end     
  end
  RETURN
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE OR REPLACE FUNCTION rowtocol( p_slct IN VARCHAR2,

     p_dlmtr IN VARCHAR2 DEFAULT ', ' ) RETURN VARCHAR2

     AUTHID CURRENT_USER AS


     TYPE c_refcur IS REF CURSOR;

     lc_str VARCHAR2(4000);

     lc_colval VARCHAR2(4000);

     c_dummy c_refcur;

     l number;


     BEGIN


     OPEN c_dummy FOR p_slct;


     LOOP

     FETCH c_dummy INTO lc_colval;

     EXIT WHEN c_dummy%NOTFOUND;

     lc_str := lc_str || p_dlmtr || lc_colval;

     END LOOP;

     CLOSE c_dummy;


     RETURN SUBSTR(lc_str,2);

     EXCEPTION

     WHEN OTHERS THEN


     lc_str := SQLERRM;

     IF c_dummy%ISOPEN THEN

     CLOSE c_dummy;

     END IF;

     RETURN lc_str;


     END;