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,他们之间有什么区别?
(
@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,他们之间有什么区别?
(
p_dept_id number,
p_user_id number,
p_searchfield varchar2
) return varchar2 as
v_dept_id number := p_dept_id;
v_user_id number := p_user_id;
v_searchfield varchar2(30) := p_serachfield; v_list varchar2(2000) default null;
begin
for v_c in (select * from tbdeptinfo a,tbdeptuser b
where b.dudept_id = a.dept_id
and dept_state=1 --这里应该指定表的别名, 如: a.dept_state=1 ,下同.
and dept_id = decode(v_dept_id,-1,dept_id,v_dept_id)
and b.duUsr_id = decode(v_user_id,-1,b.duUsr_id,v_user_id)
) loop
select v_list || decode(v_searchfield,'ID',to_char(dept_id),dept_name) || ','
into v_list
from dual;
end loop;
if length(v_list) >0 then
v_list := substr(v_list,1,length(v_list)-1);
end if;
return v_list;
end ;
strUsr_Id in number,
SearchField in varchar2)
return varchar2 is
List varchar2(20);
Begin
select Decode(SearchField,'Id',Trim(Dept_Id),Dept_Name) into List
from TbDeptInfo a,TbDeptUser b where b.DuDept_Id=a.Dept_Id and dept_state=1
and ( Dept_Id=-1 or Dept_Id=strDept_Id)
and ( @Usr_Id=-1 or b.DuUsr_Id=strUsr_Id);
If length(List) >0 Then
List:=substr(List,1,length(List)-1);
End if;
End;
strUsr_Id in number,
SearchField in varchar2)
return varchar2 is
List varchar2(20);
Begin
select Decode(SearchField, 'Id', Trim(Dept_Id), Dept_Name)
into List
from TbDeptInfo a, TbDeptUser b
where b.DuDept_Id = a.Dept_Id
and dept_state = 1
and (Dept_Id = -1 or Dept_Id = strDept_Id)
and (@Usr_Id = -1 or b.DuUsr_Id = strUsr_Id);
If length(List) > 0 Then
List := substr(List, 1, length(List) - 1);
End if;
End;