1、创建存储过程has_Permission:
create or replace procedure has_Permission(contentId in varchar2,contentType in number,privilege in number,userId in number,returnCode out number)
is
maxPrivilege number default -1; --最大权限级别
dirId varchar2(50);--文件的目录ID
stdType number;--标准图/标准工具/标准模块 类型
begin
--查找用户最大的权限数据--
--如果是 预审库文件
if contentType =1 then
select max(f.dir_id) into dirId from SJ_PRE_FILE f where f.id = contentId;
select max(d.privilege) into maxPrivilege from sj_dac d,sys_user_role ur
where d.owner_id = ur.role_id
and d.owner_type = 3
and ur.user_id = userId
and (
(d.content_type= 2 and d.content_id in (select j.id from SJ_PRE_CATEGORY j start with j.id = dirId connect by prior j.parent_id = j.id))
or
(d.content_type= contentType and d.content_id = contentId)
);
end if;
--如果是 预审库目录
if contentType =2 then
select max(d.privilege) into maxPrivilege from sj_dac d,sys_user_role ur
where d.owner_id = ur.role_id
and d.owner_type = 3
and ur.user_id = userId
and d.content_type= contentType
and d.content_id in (select j.id from SJ_PRE_CATEGORY j start with j.id = contentId connect by prior j.parent_id = j.id);
end if;
--如果是 成果库文件
if contentType =3 then
select max(f.dir_id) into dirId from SJ_LIB_FILE f where f.id = contentId;
select max(d.privilege) into maxPrivilege from sj_dac d,sys_user_role ur
where d.owner_id = ur.role_id
and d.owner_type = 3
and ur.user_id = userId
and (
(d.content_type = 4 and d.content_id in (select j.id from SJ_LIB_CATEGORY j start with j.id = dirId connect by prior j.parent_id = j.id))
or
(d.content_type = contentType and d.content_id = contentId )
);
end if;
--如果是 成果库目录
if contentType =4 then
select max(d.privilege) into maxPrivilege from sj_dac d,sys_user_role ur
where d.owner_id = ur.role_id
and d.owner_type = 3
and ur.user_id = userId
and d.content_type= contentType
and d.content_id in (select j.id from SJ_LIB_CATEGORY j start with j.id = contentId connect by prior j.parent_id = j.id);
end if;
--如果是 标准库目录
if contentType =6 then
select max(d.privilege) into maxPrivilege from sj_dac d,sys_user_role ur
where d.owner_id = ur.role_id
and d.owner_type = 3
and ur.user_id = userId
and d.content_type= contentType
and d.content_id in (select j.id from sj_std_type j start with j.id = contentId connect by prior j.parent_id = j.id);
end if;
--如果是 标准图
if contentType =7 then
select max(chart.type) into stdType from SJ_STD_CHART chart where chart.id = contentId;
select max(d.privilege) into maxPrivilege from sj_dac d,sys_user_role ur
where d.owner_id = ur.role_id
and d.owner_type = 3
and ur.user_id = userId
and
(
(d.content_type= 6 and d.content_id in (select j.id from sj_std_type j start with j.id = stdType connect by prior j.parent_id = j.id))
or
(d.content_type= contentType and d.content_id = contentId)
);
end if;
--如果是 标准工具
if contentType =8 then
select max(tools.type) into stdType from SJ_STD_TOOLS tools where tools.id = contentId;
select max(d.privilege) into maxPrivilege from sj_dac d,sys_user_role ur
where d.owner_id = ur.role_id
and d.owner_type = 3
and ur.user_id = userId
and
(
(d.content_type= 6 and d.content_id in (select j.id from sj_std_type j start with j.id = stdType connect by prior j.parent_id = j.id))
or
(d.content_type= contentType and d.content_id = contentId)
);
end if;
--如果是 标准模块
if contentType =9 then
select max(module.type) into stdType from SJ_STD_MODULE module where module.id = contentId;
select max(d.privilege) into maxPrivilege from sj_dac d,sys_user_role ur
where d.owner_id = ur.role_id
and d.owner_type = 3
and ur.user_id = userId
and
(
(d.content_type= 6 and d.content_id in (select j.id from sj_std_type j start with j.id = stdType connect by prior j.parent_id = j.id))
or
(d.content_type= contentType and d.content_id = contentId)
);
end if;
--判断用户是否存在指定的权限(1:存在,0:不存在)
if maxPrivilege >= privilege then
returnCode := 1;
end if;
if maxPrivilege < privilege then
returnCode := 0;
end if;
end has_Permission;
DECLARE
returncode number;
begin
-- Call the procedure
has_permission('6567',2,1,19791,returncode);
dbms_output.put_line('returncode=' || returncode);
end;