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

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;