oracle 触发器、存储过程、函数、包

juqtwwgnpm

1. oracle触发器的使用

　　

create or replace trigger tri_userid
before insert on t_user
for each row
declare new_key number;
begin
select seq_userid.nextval into new_key from dual;
:new.id:=new_key;
end;
　　
　　
　　



2. 存储过程

　　

create or replace procedure insert_user
(parm_name varchar2,
parm_pwd varchar2,
parm_rid number,
parm_result out number )
as
n number;
begin
select count(*) into n from t_user where username=parm_name;
if n=0 then
parm_result:=1;
insert into t_user(username,password,roleid) values(parm_name,parm_pwd,parm_rid);
commit;
else
parm_result:=0;
end if;
end;
--插入数据时先判断数据库里是否存在
--调用如下：

　　



3. 函数定义


 
create or replace function checklogin
(parm_name varchar2,
parm_pwd varchar2)
return number
as
n number;
begin
select count(*) into n from t_user t where t.username=parm_name and t.password=parm_pwd;
if n>0 then
return 1;
else
return 0;
end if;
end;
 


4.包


 
create or replace package pkg_page is
type type_cur is ref cursor;       --declare return record
procedure Pagination(
Pindex in number,
Psql in varchar2,
Psize in number,
Pcount out number,
v_cur out type_cur
);
procedure PageRecordCount(
Psqlcount in varchar2,
Prcount out number
);
end pkg_page;
 
create or replace package body pkg_page is
procedure Pagination(
Pindex in number,
Psql in varchar2,
Psize in number,
Pcount out number,
v_cur out type_cur
)
as
v_sql varchar2(1000);
v_count number;
v_plow number;
v_phei number;
begin
v_sql:='select count(*) from ('||Psql||')';
execute immediate v_sql into v_count;
Pcount:=ceil(v_count/Psize);
v_phei:=Pindex*Psize+Psize;
v_plow:=v_phei-Psize+1;
v_sql:='select * from ('||Psql||') where rn between '||v_plow||' and '||v_phei;
open v_cur for v_sql;
end Pagination;
procedure PageRecordCount(
Psqlcount in varchar2,
Prcount out number
)
as
v_sql varchar2(1000);
v_prcount number;
begin
v_sql:='select count(*) from ('||Psqlcount||')';
execute immediate v_sql into v_prcount;
Prcount:=v_prcount;
end PageRecordCount;
end pkg_page;