oracle 触发器、存储过程、函数、包-优快云博客

本文详细介绍了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;