游标和触发器

Oracle
  1、游标
  2、利用游标，读取所有数据
  3、利用存储过程，读取所有数据
  4、触发器
  
----------------------------------------------------

什么是存储过程?
答:存储过程是提前编写好,并且存储在数据库中的语句块,只要调用,即可马上运行

什么存储过程的好处?
答:  1、信息存储在数据库中,所以安全性会更好
     2、由于语句块本就存在数据库中可以直接运行,所以,速度会更快
     3、复用性会更好
     
--------------------------------------------------------
create or replace procedure my_pc(in_out_id in out number,out_name out varchar2,out_score out number)
as
begin
      select *  into in_out_id,out_name,out_score from inf where  id=in_out_id;
end;

------------------------------------
declare
      myid number(11):=1;
      myname varchar2(20);
      myscore number(11);
begin
      
      my_pc(myid,myname,myscore);
      dbms_output.put_line('编号:'||myid);
      dbms_output.put_line('姓名:'||myname);
      dbms_output.put_line('成绩:'||myscore);
end;


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


cursor---游标

游标:  它是指向数据表中,某一条数据的一个指针,游标指向哪一行,这一行数据才可以读取

游标的分类:  静态游标  与  动态游标

        静态游标:--在创建游标时,就必须指定该游标关联的是哪一张表. (并且关联以后,就不能再改变)
        
        动态游标:--在创建的时候,不必指定关联的是哪一张表,用的时候再指定(并且可以改变)



游标

      静态游标
        
           隐式游标: --不需要用户创建,系统内置一些静态游标
           
                 *****隐式游标主要有三种:
                 sql%found  ---语句执行成功,返回true,执行失败返回false
                
                 sql%notfound---语句执行成功,返回false,执行失败返回true
                 
                 sql%rowcount --返回成功执行的语句的行数
                                        
                                        
           显示游标: --需要用户自己创建的静态游标
      
      动态游标
select * from inf ORDER BY ID;
----------------------------------------------------------
--静态游标--->隐式游标

declare
      myid number(11);
begin
      myid:=&请输入你要删除的学生编号;
      delete from inf where id>myid;
      
      if(sql%found)
             then
                   dbms_output.put_line('删除成功');
                   dbms_output.put_line('被删除的行数为:'||sql%rowcount);
      else 
             dbms_output.put_line('删除失败');                    
      end if;       
      
end;
-------------------------------------------------------


静态游标--->显示游标    

--静态游标:  在声明游标的时候,就必须指定关联的是哪一张表

使用步骤:
      1、声明游标
      2、打开游标
      3、利用游标提取数据
      4、关闭游标
--------------------------------------------------------------
declare
      cursor my_cr is select * from inf order by id; --声明游标读取inf表的数据
      r inf%rowtype;
begin
      open my_cr; --打开游标
      fetch my_cr into r; --让游标向下移动一位,并且把游标指向的这一行数据,赋值给r
      dbms_output.put_line(r.id||'  '||r.name||'   '||r.score);
      close my_cr;--关闭游标
end;

------------------------------------------------------------  ************静态游标-----显示游标
declare
      cursor my_cr is select * from inf order by id;  --声明游标
      r inf%rowtype;
begin
      open my_cr; --打开游标
      fetch my_cr into r; --游标向下移动一位,并且把这一行数据赋值给变量r
      
      while(my_cr%found)   --my_cr%found 如果游标指向的这一行存在数据,返回true,没有数据,返回false
            loop
                           dbms_output.put_line(r.id||'  '||r.name||'   '||r.score);
                           fetch my_cr into r;                       
            end loop;
      close my_cr; --关闭游标            
end;

----------------------------------------------------------------------------------------------------------------
动态游标 : --这种游标,在声明游标的时候,不需要指定关联的是哪一张表,在打开游标的时候,再指定

declare
     type mycr is ref cursor;  --声明一种类型,这种类型为游标类型
     c_r  mycr;  --声明一个变量,变量为c_r,它是一种游标类型的变量
     
     r inf%rowtype;  --声明行变量,用于保存读取的数据
begin
     open c_r for select * from inf order by id;  --打开游标,并且指定关联的表
     
     fetch c_r into r; --利用游标提取数据
     
     while(c_r%found)
           loop
                dbms_output.put_line(r.id||'  '||r.name||'   '||r.score);
                 fetch c_r into r;--指向下一行
           end loop;
     close c_r;
end;


---------------------------------------------------------
oracle的 PL/SQL语句块中,声明的变量游标这些对象,使用完成以后,就会马上销毁,它们自身无法保存

----
如果将这些变量游标存放一个对象中,它们就可以永久保存下来-------------------该对象称为:   程序包 package

--创建程序包

create or replace package my_pk
as

       type my_cr is ref cursor;
end;

----
--创建存储过程,返回游标
create or replace procedure my_pc(in_score in number,out_cr out my_pk.my_cr)
as
begin
      open out_cr for select * from inf where score>=in_score order by score desc;
end;
select user from dual;

-----------------------------------------------------------------------------------------------------------------------
*******************************************************

触发器  trigger

什么是触发器?它有什么用?

答:触发器是存储在数据库中代码块,当用户的操作满足某一条件时,系统将自动调用触发器的中代码;


*********注意:其实触发器只能被系统调用,不能被用户调用

--触发器的分类:

       1、行级触发器
       2、表级触发器
       3、instead of 触发器(视图触发器)
       4、模式触发器

------------------------------------------------------------------------------------------------------
行级触发器:  当用户对表中的某一行数据进行操作时,触发了设置的触发条件时,触发器中的代码就会自动运行

--行级触发器语法:
create or replace trigger 触发器的名称
before 动作名称 on 表 for each row
begin
       触发器要执行的代码;
end;

----------------------------------------------
示例: --对表添加的数据时候,触发

create or replace trigger tr_inf_insert

before insert on inf for each row

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

--如果对表进行了修改,删除,添加,都触发

create or replace trigger tr_inf_insert

before insert or update or delete on inf for each row

begin
       dbms_output.put_line('用户操作了表中的数据');
end;



--------------------------------------------------------------------
利用行级触发器,限制新员工在本公司的工作年限都为0

create table empInfo
(
       eid number(11) primary key,
       ename varchar2(20) not null,
       work_years number(11) default 0   --所有新入职的员工在本公司的工作年限都应该为0
);

--------------------------------------
--   :new.work_years  表示:即将赋给inf表中work_years字段的新值

create or replace trigger tr_empInfo_insert
before insert on empInfo for each row
begin
       if(:new.work_years>0)                --如果要添加的工作年限大于0,就把它改为0
              then 
                 :new.work_years:=0;
       end if;          
end;

insert into empInfo values(1,'tom',10);

update empInfo set work_years=5 where eid=1;
select * from empInfo;


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

    create table province
    (
           pid number primary key,
           pname varchar2(20)
    );
    insert into province values(1,'湖北省');
    
    create table city
    (
           cid number primary key,
           cname varchar2(20),
           pid number,
           foreign key (pid) references province(pid)
    );
    insert into city values(1,'武汉市',1);
---------------------------------------------------------
select * from province;
select * from city;
delete from province where pid=1;

-------------------------------------------------------------
** 通过触发器,在删除主键表数据的时候,由触发器提前把关联的外键数据删除
create  or replace trigger tr_province_delete
before delete  on province for each row
begin
       delete from city where pid=:old.pid;  --如果城市表中的pid字段等于要删除的省份表中pid字段,就这对应城市信息删除
end;

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

********注意:   

:new.xxx

:old.xxx
这两个属性,只有在行级触发中才可以使用

-------------------------------------------------------------------------------------------------------------------------
*************表级触发器
--不包含  for each row 的触发器就是表级触发器

select user,sysdate from dual;  -- user  获得当前用户          sysdate   获得系统时间

--------------------****************表级触发器

******创建表,实现日志记录
create table inf_log
(
       log_id number primary key,
       op_name varchar2(20),--用户名
       op_time date,--操作时间,
       op_details varchar2(100) --做了什么操作
)
create sequence inf_log_seq; --创建序列

--如果执行的是insert操作, 系统的inserting属性会返回true

--如果执行的是update操作, 系统的updating属性会返回true

--如果执行的是delete操作, 系统的deleting属性会返回true


create or replace trigger tr_inf_operation
before insert or update or delete on inf
begin
       if inserting 
          then  insert into inf_log values(inf_log_seq.nextval,user,sysdate,'向数据表添加了数据');
       elsif updating
          then  insert into inf_log values(inf_log_seq.nextval,user,sysdate,'修改了数据表中的数据');    
       elsif deleting
          then  insert into inf_log values(inf_log_seq.nextval,user,sysdate,'删除了数据表中的数据'); 
       end if;   
end;

select * from inf_log;

delete from inf where id=13;
update inf set name='other' where id=3;
insert into inf values(13,'aaa',99);