.net+oracle 实现分页和菜单 (三) -CSDN博客

本文链接：https://blog.csdn.net/sql_xep/article/details/5411294

说明得为xep分配相应的访问表的权限

sql> conn /as sysdba

sql> grant select on dba_objects to xep;

sql>

接下来是最最最重要的存储过程

       --验证用户
       create or replace procedure p_checkuser (UserName in out varchar2 ,password varchar2)
        as
       var_name varchar2(20);
       begin
       select u_name into var_name from user_info where u_name=UserName and u_pwd=password;
       exception
       when no_data_found then
       var_name:='';
       UserName:=var_name;
       end;
       /

--建用户表
       create table user_info(u_id number(5),u_name varchar2(20),u_pwd varchar2(30));
--加主键
       alter table user_info add primary key (u_id);
--加数据
       insert into user_info values(1,'xep','xep');

insert into user_info values(2,'wp','wp');

--建序列
create sequence s_num increment by 1 start with 1 maxvalue 999999999;

--创建让U_id自动增长的触发器
        create or replace trigger t_autoid_userinfo
        before insert on user_info
        for each row
        declare nextid number;
        begin
        select s_num.nextval into nextid from dual;
        :new.u_id:=nextid;
        end;
/
--注册用户过程
create or replace procedure p_insertuser(username varchar2 ,password varchar2) as
var_count number(3):=0;
begin
select count(u_name) into var_count from user_info where u_name = username;
if var_count >0 then
null;
else
insert into user_info(u_name,u_pwd) values(username,password);
end if;
end;
/

--创建模式表
create table user_schema as select username,user_id,account_status from dba_users where account_status='OPEN';

   --查询用户模式
   Create or replace procedure p_SelectSchema(mycur out sys_refcursor ) as
   begin
      open mycur for select username,user_id,account_status from user_schema ;
   end;
   /

   --创建类型表
create table user_type (type_id varchar2(20),type_name varchar2(20));

     insert into user_type values('VIEW','视图');

insert into user_type values('FUNCTION','函数');

insert into user_type values('PROCEDURE','存储过程');

commit;

   --查询对像类型
   Create or replace procedure p_SelectType(mycur out sys_refcursor ) as
   begin
      open mycur for select type_id,type_name from user_type ;
   end;
   /

   --查询菜单目录信息
   create or replace procedure P_SelectMenuInfo(mycur out sys_refcursor) as
    begin
    open mycur for select nodeid,nodename,nodepid,nodeorder,urlpath from menutree;
    end;
    /

--根据条件查询用户模式对像

create or replace procedure P_SelectUserObjectSchemaInfo(currentPage number, recount number,userSchema varchar2,
                            userType varchar2,objectName varchar2,mycur out sys_refcursor )
                                                         as
--当前页数 currPage, 每页显示数 count, 用户名 userSchema,对象类型 userType, 对像名称 objectName
allCount number(10):=0;--总行数
pageCount number(7) :=0;--总页数
rowlow    number(10):=0;--要取行数的下限
rowupp    number(10):=0;--要取行数的上限
begin
   --先根据条件查询出结果的总行数
   select count(1) into allCount from dba_objects
   where (userSchema = '0' or owner like '%'||upper(userSchema)||'%')
     and (userType='0' or object_type like '%' ||upper(userType)||'%')
     and (objectname='0' or object_name like '%' || upper(objectname)||'%');

    --查询出总页数
    select allCount/recount into pageCount from dual;
    if pageCount = 0 then pageCount :=1;end if;
    if mod(allCount,recount) != 0 then
       pageCount:=pageCount+1;
    end if;
    if recount = 1 then
      open mycur for select owner,object_name,object_type,status,created,pageCount pageCount from dba_objects
      where (userSchema = '0' or owner like '%'||upper(userSchema)||'%')
       and (userType='0' or object_type like '%'||upper(userType)||'%')
       and (objectname='0' or object_name like '%'|| upper(objectname)||'%')
       and rownum <= recount;
   else
   select (currentPage-1)*recount into rowlow from dual;
   select (currentPage-1)*recount + recount into rowupp from dual;
      open mycur for select owner,object_name,object_type,status,created,pageCount pageCount from (
       select rownum rc,owner,object_name,object_type,status,created from dba_objects
        where (userSchema = '0' or owner like '%'||upper(userSchema)||'%')
         and (userType='0' or object_type like '%'||upper(userType)||'%')
         and (objectname='0' or object_name like '%'|| upper(objectname)||'%')
         and rownum <= rowupp
     )a where a.rc> rowlow and a.rc<=rowupp;
  end if;
end;