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;