怎么来写ORACLE的函数

Question

表
create table SYS_USER_JOB
(
  JOBCODE  NVARCHAR2(5) not null,
  JOBNAME  NVARCHAR2(50) not null,
  ROLELIST NVARCHAR2(100) not null
)SYS_USER_JOB表有下数据：
JOBCODE   JOBNAME       ROLELIST 
-----------------------------------
Job01   分局局长 Js01,Js02
Job02   分局副局长 Js01create table SYS_ROLE_ROLELIST
(
  ROLECODE NVARCHAR2(4) not null,
  ROLENAME NVARCHAR2(50) not null
)SYS_ROLE_ROLELIST表有下数据：
ROLECODE           ROLENAME 
-----------------------------------
Js01            数据审核员
Js02               综合管理员
我们要查询SYS_USER_JOB获取下面显示数据：
JOBCODE   JOBNAME       ROLELIST      ROLENAMELIST
-------------------------------------------------------
Job01   分局局长 Js01,Js02     数据审核员，综合管理员
Job02   分局副局长 Js01          数据审核员1.如果是SQL Server 数据库只要增加一个函数就可以了：
CREATE function Fun_GetRoleNameList(@RoleLIst varchar(1000))
returns varchar(8000)
as
begin
    declare @SqlText varchar(8000)
    set @SqlText = ''
    select @SqlText = @SqlText+','+RoleName from Sys_Role_RoleList where ','+@RoleList+',' like '%,' +RoleCode+',%'
    set @SqlText = stuff(@SqlText,1,1,'')
    return @SqlText 
end然后 select *,dbo.Fun_GetRoleNameList(RoleList) as RoleNameList from SYS_USER_JOB就可以了2.单在Oracle中怎么创建一个类似SQLServer：Fun_GetRoleNameList的函数啊。

Answer 1

你是oracle9i还是oracle10g的?
实现你这个功能9i版本要自己字函数,10g用函数wmsys.wm_concat()可以达到你的要求

Answer 2

oracle10 ,但不知道怎么使用这个wmsys.wm_concat()

Answer 3

select WMSYS.WM_CONCAT(rolename) AS ENAMES from sys_role_rolelist怎么查出来的ENAMES 是乱吗

Answer 4

/*表 */
create table SYS_USER_JOB 
( 
  JOBCODE  NVARCHAR2(5) not null, 
  JOBNAME  NVARCHAR2(50) not null, 
  ROLELIST NVARCHAR2(100) not null 
) 
/*
SYS_USER_JOB表有下数据： 
JOBCODE  JOBNAME      ROLELIST 
----------------------------------- 
Job01   分局局长 Js01,Js02 
Job02   分局副局长 Js01 */insert into sys_user_job(jobcode,jobname,rolelist)
select 'Job01','分局局长','Js01,Js02' from dual
union all
select 'Job02','分局副局长','Js01' from dual;
commit;create table SYS_ROLE_ROLELIST 
( 
  ROLECODE NVARCHAR2(4) not null, 
  ROLENAME NVARCHAR2(50) not null 
) /*SYS_ROLE_ROLELIST表有下数据： 
ROLECODE          ROLENAME 
----------------------------------- 
Js01           数据审核员 
Js02              综合管理员 */
insert into sys_role_rolelist
select 'Js01','数据审核员' from dual
union all
select 'Js02','综合管理员' from dual;
commit;/*
我们要查询SYS_USER_JOB获取下面显示数据： 
JOBCODE  JOBNAME      ROLELIST      ROLENAMELIST 
------------------------------------------------------- 
Job01   分局局长 Js01,Js02    数据审核员，综合管理员 
Job02   分局副局长 Js01          数据审核员 */create or replace function fun_getrolenamelist(
rolelist varchar2
)
return varchar2
is
  resultstr varchar2(200);
  tempstr varchar2(40);
begin
  for myrec in (select  rolename from sys_role_rolelist
                where  'Js01,Js02' like '%'||rolecode||'%')  loop
    resultstr := resultstr||','||myrec.rolename;
  end loop;
   
  resultstr := substr(resultstr,2,length(resultstr)-1);
  
  return resultstr;
exception
  when others then
    return '异常数据';
end;
  
  select jobcode,jobname,rolelist,fun_getrolenamelist(rolelist) from sys_user_job;
  
  

Answer 5

数据类型改为双字节,varchar2-->nvarchar2

Answer 6

试试这个函数:CREATE OR REPLACE FUNCTION replace_rolecode(l_list IN VARCHAR2) 
RETURN VARCHAR2 AS
   l_tab      DBMS_UTILITY.uncl_array;
   l_tablen   NUMBER;
   l_rolename VARCHAR2(1000)
BEGIN
   DBMS_UTILITY.comma_to_table (l_list, l_tablen, l_tab);  for i in 1..l_tablen loop
    select rolename
     into l_tab(i)
     from sys_role_rolelist
    where rolecode = l_tab(i);
  end loop;   DBMS_UTILITY.table_to_comma (l_tab, l_tablen, l_rolename);
   RETURN l_rolename;
END;
/

Answer 7

CREATE OR REPLACE FUNCTION Fun_GetRoleNameList(P_ROLELIST IN VARCHAR2) RETURN VARCHAR2
AS
    V_ROLENAME SYS_ROLE_ROLELIST.ROLENAME%TYPE;
    V_ROLELIST1 SYS_USER_JOB.ROLELIST%TYPE;
    V_ROLELIST2 VARCHAR2(100);
    V_ROLENAME2 VARCHAR2(1000);
    V_POS NUMBER;
BEGIN
    IF P_ROLELIST='' THEN 
        RETURN '';
    END IF;
    V_ROLELIST1:=P_ROLELIST;
    V_ROLELIST2:='';
    V_ROLENAME2:='';
    V_POS:=INSTR(V_ROLELIST1,',');
    Loop
        IF V_POS=0 THEN
            V_ROLELIST2:=V_ROLELIST1;
            SELECT ROLENAME INTO V_ROLENAME
            FROM SYS_ROLE_ROLELIST
            WHERE ROLELIST=V_ROLELIST2;
            V_ROLENAME2:=V_ROLENAME2||','||V_ROLENAME;
            EXIT;
        ELSE
            V_ROLELIST2:=SUBSTR(V_ROLELIST1,1,V_POS-1);
            SELECT ROLENAME INTO V_ROLENAME
            FROM SYS_ROLE_ROLELIST
            WHERE ROLELIST=V_ROLELIST2;
            V_ROLENAME2:=V_ROLENAME2||','||V_ROLENAME;
            V_ROLELIST1:=SUBSTR(V_ROLELIST1,V_POS+1);
            V_POS:=INSTR(V_ROLELIST1,',');
        END IF;
    End Loop;
    V_ROLENAME2:=SUBSTR(V_ROLENAME2,2);
    RETURN V_ROLNAME2;
END;