create FUNCTION [dbo].[F_TbUserInfo_GetDept]
(
@Dept_Id int,
@Usr_Id int,
@SearchField varchar(20)
)
RETURNS varchar(2000)
AS
BEGIN
declare @List varchar(2000)
set @List=''
select @List=@List+case when @SearchField='Id' then ltrim(str(Dept_Id)) else Dept_Name end+','
from TbDeptInfo a,TbDeptUser b where b.DuDept_Id=a.Dept_Id and dept_state=1
and ( @Dept_Id=-1 or Dept_Id=@Dept_Id )
and ( @Usr_Id=-1 or b.DuUsr_Id=@Usr_Id )
if(len(@List)>0)
set @List=left(@List,len(@List)-1)
Return (@List)
END
将上面的sql写的函数转为oracle
------解决方案--------------------
- SQL code
create or replace function F_TbUserInfo_GetDept( V_Dept_Id int, V_Usr_Id int, V_SearchField varchar2) return varchar2is v_list varchar2(2000):='';begin for rec in ( select decode(V_SearchField,'ID',ltrim(str(Dept_Id)),Dept_Name end+',') CNAME from TbDeptInfo a,TbDeptUser b where b.DuDept_Id=a.Dept_Id and dept_state=1 and ( V_Dept_Id=-1 or Dept_Id=V_Dept_Id ) and ( V_Usr_Id=-1 or b.DuUsr_Id=V_Usr_Id ) ) loop v_list := v_list|| REC.CNAME; end loop; IF LENGTH(V_LIST)>0 THEN V_LIST := SUBSTR(V_LIST,1,LENGTH(V_LIST)-1); END IF; return v_list;end;/
------解决方案--------------------
主要是：
去掉变量前的@
等号从＝ 到 :＝
------解决方案--------------------
CREATE OR REPLACE FUNCTION F_TbUserInfo_GetDept
(
v_Dept_Id IN NUMBER,
v_Usr_Id IN NUMBER,
v_SearchField IN VARCHAR2
)
RETURN VARCHAR2
AS
v_List VARCHAR2(2000);
BEGIN
v_List := '';
SELECT v_List || CASE
WHEN v_SearchField = 'Id' THEN LTRIM(sqlserver_utilities.str(Dept_Id))
ELSE Dept_Name
END || ','
INTO v_List
FROM TbDeptInfo a,
TbDeptUser b
WHERE b.DuDept_Id = a.Dept_Id
AND dept_state = 1
AND ( v_Dept_Id = -1
OR Dept_Id = v_Dept_Id )
AND ( v_Usr_Id = -1
OR b.DuUsr_Id = v_Usr_Id );
IF ( LENGTH(v_List) > 0 ) THEN
v_List := SUBSTR(v_List, 0, LENGTH(v_List) - 1);
END IF;
RETURN v_List;
END;