存储过程的 创建 和 调用

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;

2、调用存储过程：

DECLARE
  returncode number;
begin
  -- Call the procedure
  has_permission('6567',2,1,19791,returncode);
 
  dbms_output.put_line('returncode=' || returncode);
end;