犀利的存储过程 用来实现分页

create or replace procedure pageinfo
(v_sql varchar2,curpage number,pageunit number,ordercolumn varchar2,ordertype varchar2,rs out itfuture.r_cursor,v_count out number)
as
begin
--存储过程调用存储过程
--page(v_sql,curpage,pageunit,ordercolumn,ordertype,rs);
--querycount(v_sql,v_count);
--存储过程调用函数
v_count:=countrecord(v_sql);
rs:=pageByFun(v_sql,curpage,pageunit,ordercolumn,ordertype);
end;


create or replace procedure page
(v_sql varchar2,curpage number,pageunit number,ordercolumn varchar2,ordertype varchar2,rs out itfuture.r_cursor)
as
all_sql varchar2(200);
startpos number;
endpos number;
begin
if(curpage is not null) then
if(pageunit is not null)then
startpos:=(curpage-1)*pageunit;
endpos:=curpage*pageunit;
end if;
end if;
all_sql:=v_sql;
if(ordercolumn is not null) then
all_sql:=all_sql||' order by '||ordercolumn;
if(ordertype is not null and ordertype in('desc','asc'))then
all_sql:=all_sql||' '||ordertype;
end if;
end if;
all_sql:='select t2.* from (select t1.*,rownum r_id from ('||all_sql||') t1 where rownum<='||endpos||') t2 where t2.r_id>='||startpos;
open rs for all_sql;
exception ---扑捉异常
when others then
dbms_output.put_line('查询出错！');
raise;---把异常继续抛出
end;
---函数
create or replace function getValue(i number,j number) return number
as
m number;
begin
m:=i+j;
return m;
end;

create or replace function print return number
as
begin
dbms_output.put_line(sysdate);
return 5;
end;

create or replace function getValue2(i number,j number,result1 out number,result2 out number) return number
as
m number;
begin
result1:=i+j;
result2:=i-j;
m:=i*j;
return m;
end;
--输出和/差/乘积
create or replace function getValue3(i number,j number,result1 out number,result2 out number) return number
as
m number;
begin
--函数调用函数
result1:=getValue(i,j);
result2:=i-j;
m:=i*j;
return m;
end;

--实现分页
--得到总记录数
create or replace function countRecord(v_sql varchar2)return number
as
all_sql varchar2(200);
countRecord number;
begin
all_sql:='select count(1) from ('||v_sql||')';
execute immediate all_sql into countRecord;
return countRecord;
end;

select count(1000000) from (select * from dept where deptnum>10);

create or replace function pageByFun
(v_sql varchar2,curpage number,pageunit number,ordercolumn varchar2,ordertype varchar2)return itfuture.r_cursor
as
all_sql varchar2(200);
startpos number;
endpos number;
rs itfuture.r_cursor;
begin
if(curpage is not null) then
if(pageunit is not null)then
startpos:=(curpage-1)*pageunit;
endpos:=curpage*pageunit;
end if;
end if;
all_sql:=v_sql;
if(ordercolumn is not null) then
all_sql:=all_sql||' order by '||ordercolumn;
if(ordertype is not null and ordertype in('desc','asc'))then
all_sql:=all_sql||' '||ordertype;
end if;
end if;
all_sql:='select t2.* from (select t1.*,rownum r_id from ('||all_sql||') t1 where rownum<='||endpos||') t2 where t2.r_id>='||startpos;
open rs for all_sql;

return rs;

exception ---扑捉异常
when others then
dbms_output.put_line('查询出错！');
raise;---把异常继续抛出

end;


create or replace function pageinfoByFun
(v_sql varchar2,curpage number,pageunit number,ordercolumn varchar2,ordertype varchar2,allrecords out number)return itfuture.r_cursor
as
rs itfuture.r_cursor;
begin
---函数调用函数
--allrecords:=countRecord(v_sql);
--return pageByFun(v_sql,curpage,pageunit,ordercolumn,ordertype);
---函数调用存储过程
querycount(v_sql,allrecords);
page(v_sql,curpage,pageunit,ordercolumn,ordertype,rs);
return rs;
end;

--关于存储过程和函数的封装(程序包和程序包体)
create or replace package itfuture
as
type r_cursor is ref cursor;
procedure querycount(v_sql varchar2,v_count out number);
procedure page(v_sql varchar2,curpage number,pageunit number,ordercolumn varchar2,ordertype varchar2,rs out itfuture.r_cursor);
function countRecord(v_sql varchar2)return number;
function pageByFun(v_sql varchar2,curpage number,pageunit number,ordercolumn varchar2,ordertype varchar2)return itfuture.r_cursor;
end;

create or replace package body itfuture
as
procedure querycount(v_sql varchar2,v_count out number)
as
all_sql varchar2(2000);--声明长度
begin
all_sql:= 'select count(*) from ( '||v_sql||')';
execute immediate all_sql into v_count;
end;

procedure page
(v_sql varchar2,curpage number,pageunit number,ordercolumn varchar2,ordertype varchar2,rs out itfuture.r_cursor)
as
all_sql varchar2(200);
startpos number;
endpos number;
begin
if(curpage is not null) then
if(pageunit is not null)then
startpos:=(curpage-1)*pageunit;
endpos:=curpage*pageunit;
end if;
end if;
all_sql:=v_sql;
if(ordercolumn is not null) then
all_sql:=all_sql||' order by '||ordercolumn;
if(ordertype is not null and ordertype in('desc','asc'))then
all_sql:=all_sql||' '||ordertype;
end if;
end if;
all_sql:='select t2.* from (select t1.*,rownum r_id from ('||all_sql||') t1 where rownum<='||endpos||') t2 where t2.r_id>='||startpos;
open rs for all_sql;
exception
when others then
dbms_output.put_line('查询出错！');
raise;---把异常继续抛出
end;

function countRecord(v_sql varchar2)return number
as
all_sql varchar2(200);
countRecord number;
begin
all_sql:='select count(1) from ('||v_sql||')';
execute immediate all_sql into countRecord;
return countRecord;
end;

function pageByFun(v_sql varchar2,curpage number,pageunit number,ordercolumn varchar2,ordertype varchar2)return itfuture.r_cursor
as
all_sql varchar2(200);
startpos number;
endpos number;
rs itfuture.r_cursor;
begin
if(curpage is not null) then
if(pageunit is not null)then
startpos:=(curpage-1)*pageunit;
endpos:=curpage*pageunit;
end if;
end if;
all_sql:=v_sql;
if(ordercolumn is not null) then
all_sql:=all_sql||' order by '||ordercolumn;
if(ordertype is not null and ordertype in('desc','asc'))then
all_sql:=all_sql||' '||ordertype;
end if;
end if;
all_sql:='select t2.* from (select t1.*,rownum r_id from ('||all_sql||') t1 where rownum<='||endpos||') t2 where t2.r_id>='||startpos;
open rs for all_sql;

return rs;

exception ---扑捉异常
when others then
dbms_output.put_line('查询出错！');
raise;---把异常继续抛出

end;


end;



--程序块
begin
itfuture.

end;
--员工新增/修改/删除的时候实现动态更新隶属部门的编制 :new :old
create trigger editdeptnum1
after insert on empinfo
for each row
begin
update dept set deptnum=deptnum+1 where deptid=:new.deptid;--新增员工的部门字段的值
end;

create trigger editdeptnum2
after delete on empinfo
for each row
begin
update dept set deptnum=deptnum-1 where deptid=:old.deptid;--删除员工的部门字段的值
end;

create trigger editdeptnum3
after update on empinfo
for each row
begin
update dept set deptnum=deptnum-1 where deptid=:old.deptid;--员工原来部门字段的值
update dept set deptnum=deptnum+1 where deptid=:new.deptid;--员工新隶属部门字段的值
end;
--综合的触发器
create trigger editdeptnum
after insert or delete or update of deptid on empinfo
for each row
begin
case
when(inserting)then
update dept set deptnum=deptnum+1 where deptid=:new.deptid;--员工新隶属部门字段的值
when(deleting)then
update dept set deptnum=deptnum-1 where deptid=:old.deptid;--员工原来部门字段的值
when(updating)then
if(:new.deptid!=:old.deptid) then
update dept set deptnum=deptnum-1 where deptid=:old.deptid;--员工原来部门字段的值
update dept set deptnum=deptnum+1 where deptid=:new.deptid;--员工新隶属部门字段的值
end if;
end case;
end;
--使用触发器实现新增时候的主键的自增
create trigger pkincrement
before insert on dept
for each row
declare --如果声明变量要加declare
pk number:=0;
begin
select max(deptid)+1 into pk from dept;
:new.deptid:=pk;--该新增记录的deptid赋值
end;

insert into dept(deptname,deptnum,deptdesc) values('test',2,'test');

create table loginfo(
logid number,
logtable varchar2(20),
operatedate date,
recordid number,
constraint loginfo_PK primary key(logid));

create trigger logTrigger
after delete on roleinfo
--for each row 去掉后表示是语句级触发
begin
insert into loginfo values(p_id.nextval,'roleinfo',sysdate,null);
end;


delete from roleinfo;