oracle从入门到精通(9)------游标，包，存储过程，触发器

oracle从入门到精通(9)

------ 游标，包，存储过程，触发器

select * from inf;

--静态游标---->显式游标

1  声明游标

2  打开游标

3  提取数据

4  关闭游标

declare 

      cursor my_cur is select * from inf;

      r inf%rowtype;

begin

      open my_cur;

      

      fetch my_cur into r;

      

      while(my_cur%found)

            loop

                   dbms_output.put_line(r.id||'   '||r.name||'  '||r.age);

                   fetch my_cur into r;

            end loop;

      close my_cur;

end;

-----------------------------------------------------------------------------------------------

动态游标:  在声明的时候不需要关联到表,在打开的时候才指定,与哪一张表建立关联

---动态游标的语法:

declare

       type my_cur is ref cursor;   --声明一种动态游标类型,名称为 :   my_cur

       abc my_cur; --声明一个动态游标类型的变量,名称为: abc

       r inf%rowtype; --声明一个变量r,它代表inf表中的一行数据

begin

       open abc for select * from inf;

       fetch abc into r;

       

            while(abc%found)

            loop

                   dbms_output.put_line(r.id||'   '||r.name||'  '||r.age);

                   fetch abc into r;

            end loop;

       close abc;

end;

---------------------------------------------------------------------------------------------

create or replace procedure my_pc(in_age in number)

as

begin

       declare

       type my_cur is ref cursor;   --声明一种动态游标类型,名称为 :   my_cur

       abc my_cur; --声明一个动态游标类型的变量,名称为: abc

       r inf%rowtype; --声明一个变量r,它代表inf表中的一行数据

      begin

             open abc for select * from inf where age >in_age ;

             fetch abc into r;

             

                  while(abc%found)

                  loop

                         dbms_output.put_line(r.id||'   '||r.name||'  '||r.age);

                         fetch abc into r;

                  end loop;

             close abc;

      end;

       

end;

----------------------------------

begin

      

      my_pc(in_age => 29);

end;

---包--package

 作用:创建程序包以后,可以把定义好的游标类型,放到程序包中,要使用这种游标的时候,只需要从程序包中取得该游标

 类型即可使用

-----------------------------------

创建程序包的语法:

--创建一个程序包,程序包中包含一个动态游标

create or replace package my_pk   

as

       type my_cur is ref cursor;

end;

----------------------------------------------------------------------

--创建一个存储过程,返回游标

create or replace procedure my_pc(out_cur out my_pk.my_cur)

as

begin

       open out_cur for select * from inf;

end;

----------------------------------------------------------------

trigger ----触发器

什么是触发器?

答：存在于数据库中,当满足一定条件时,自动运行的语句块,就是触发器

触发器,用户不能调用,只有当满足一定条件时,系统会自动调用

触发器的分类:

--  1    行级触发器        (当操作数据表中的某一行时,行级触发器就会自动运行)

--  2    表级触发器        (当操作某一张表的时候,表级触发器就会自动运行)

--  3    instead触发器     (当操作视图的时候,instead触发器就会自动运行)

--  4    模式触发器        (当创建或删除对象的时候,它会自动运行)

***********触发器中的代码,都是自动运行.用户无法调用

触发器的基本语法:

create or replace trigger 触发器名称

在什么情况下   做什么事情;

---------------------------------------------------------

--行级触发器: 当向数据表中添加一行数据的时候,自动触发

create or replace trigger tr_insert_inf

before insert on inf for each row

begin

       dbms_output.put_line('用户向数据表中添加了一行数据');

end;

----------------------------------------------------------------

代码分解:

    create or replace trigger tr_insert_inf    --创建一个触发器名称为:  tr_insert_inf  

    before insert on inf   --当向inf表中添加一行数据之前,先要运行触发器

    for each row  --表示当前触发器,是一个行级触器

 

    begin       --触发器要完成的操作

       dbms_output.put_line('用户向数据表中添加了一行数据');

    end;

------------

create table workInfo

(

       id number primary key,  --员工编号

       name varchar2(20),  --员工名称

       work_years number  --员工在本公司的工作年限

);

--新员工在公司的工作年限都应该是0

create or replace trigger  tr_insert_workInfo

before insert on workInfo for each row

begin

       :new.work_years:=0;  --给work_years字段要添加的新值为0

end;

--------------------------------------------------

insert into workInfo values (1,'tom',0);

insert into workInfo values (2,'bruce',999);

select * from workInfo;

update workInfo set work_years =3 where id=2;

------------------------------

       :new.work_years:=0;  --要赋值给work_years字段的新值是0

       :old.work_years ---取得work_years字段旧的数据

       

    :new 与 :old只能用于行级触发器

-------------------------------------------------

利用触发器,自动删除表中的数据

------------------------------------------------

create or replace trigger tr_del_province

before delete on province for each row

begin

       delete from city where pid=:old.pid;

end;

---------------------------------------------------

select * from province;

delete from province where pid=1;

create table province

(

       pid number primary key,

       pname varchar2(20)

)

insert into province values(1,'湖北省');

insert into province values(2,'广东省');

create table city

(

       cid number primary key,

       cname varchar2(20),

       pid number references province(pid)

)

insert into city values(1,'武汉市',1);

insert into city values(2,'广州市',2);

-----------------------------------------------------------------------

表级触发器:

----判断用户执行了哪些操作,是否有权限对表做这些操作

create or replace trigger tr_insert_update_delete_inf

before insert or delete or update on inf

begin

       if(user!='CINDY')

              then raise_application_error(-20001,'错误,你无权限对表完成这些操作');

       end if;       

           

end;

--错误的编号必须是 :          -20001------------       -20999  之间

select * from inf;

insert into inf values (2,'chris',34);

grant all on inf to crm;

select * from cindy.inf;

insert into cindy.inf values (3,'bruce',34);

select user from dual;--user 为当前用户

--------------------------------------------------------

利用表级触器做日志记录

create table inf_log

(

       id number primary key,--日志编号

       name varchar2(20),--哪个用户在操作

       op_time date,---操作时间

       op_desc varchar2(20) --做了哪些操作

)

create sequence inf_log_seq;--创建序列做为主键

---

create or replace trigger tr_insert_update_delete_inf

before insert or delete or update on inf

begin

       if inserting

              then insert into inf_log values(inf_log_seq.nextval,user,sysdate,'向inf表中添加了数据');

       end if;                     

       if updating

              then insert into inf_log values(inf_log_seq.nextval,user,sysdate,'修改inf表的数据');

       end if; 

       if deleting

              then insert into inf_log values(inf_log_seq.nextval,user,sysdate,'删除inf表的数据');

       end if;        

end;

insert into inf values(10,'lee',22);

update cindy.inf set name='yyy' where id=9;

select * from inf_log;

grant all on inf to crm;