oracle triggers stored procedures. functions. package

2010-09-09  来源:本站原创  分类:Database  人气:50 

1. Oracle trigger to use

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 Stored Procedures

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;
-- Insert first determine whether the database exists
-- Call is as follows :

3 function definition

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 pack
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;

相关文章