Oracle游标和触发器

--1.游标

--什么是游标

--游标是数据库在内存中开辟的数据缓冲区
--作用：用于遍历查询返回之后的结果集（多条数据结果）

--游标分类：隐式游标，显示游标，REF游标（动态游标）

--游标的状态

--  %found    -- sql语句影响了一行或多行时为true；
--  %notfound -- sql语句没有影响任何行时为true(常用，没找到为T，就退出)
--  %rowcount -- sql语句影响的行数；
--  %isopen   -- 游标是否打开，始终为false。

--游标变量的优点和限制：

--1.游标变量功能强大，可以简化数据处理；

--2.游标变量优点：

  --  a.可从不同的select语句中提取结果集；
    --b.可以作为过程的参数进行传递；
    --c.可以引用游标的所有属性；
    --d.可以进行赋值运算；

--3.游标变量的限制：

  --  a.不能在程序包中声明游标变量；
  --  b.for update子句不能与游标变量一起使用；
  --  c.不能使用比较运算符。

--1）隐式游标：

在pl\sql中执行DML SQL(Insert/delete/update/select)语句时自动创建，自动关闭，自动打开，自动声明
select * from t_student;
begin
   update t_student set sname ='美女' where sid =12;
   dbms_output.put_line('好漂亮');
   else
     dbms_output.put_line('最漂亮');
     end if;
     if sql%found then 
       dbms_output.put_line('美丽等级：'||sql%rowcount);
       else
         dbms_output.put_line('不好看');
         end if;
         end;

--2）显示游标

--    用于处理Select时返回多行的查询；
--      增/删/改时不会用显示游标；
--      需要手动的去做声明、打开、提取、关闭操作。
--查询所以人的名字
declare
--步骤一：声明游标(仅定义，未执行）
cursor s_name from t_student;
select sname from t_studnet;
my_name t_studnet.sname%type;
begin
  --步骤二：打开游标
  open s_name;
  --循环遍历游标
  loop
  --步骤三：提取游标（提取每一行游标返回的数据结果）
  fetch s_name into my_name;
  --设置循环终端条件
  exit when s_name%notfound;
    --打印结果
  dbms_output.put_line('学生姓名'||my_name);
    end loop;
  open s_name;
  --步骤4：关闭游标
  close s_name;
end;

--实列2：查询所以学生
cursor c_stu from t_student;
select sname from t_studnet;
my_stu t_studnet%rowtype;
begin
  --步骤二：打开游标
  open c_stu;
  --循环遍历游标
 -- loop
  --步骤三：提取游标（提取每一行游标返回的数据结果）
 -- fetch c_stu into my_stu;
  --设置循环终端条件
  --exit when c_stu%notfound;
    --打印结果
 -- dbms_output.put_line('编号：'||my_stu.sid||',姓名：'||my_stu.sname);
  --  end loop;
 -- open s_name;
  --步骤4：关闭游标
 -- close c_stu;
 

 --for:自动打开，关闭及提取
 --r变量表示默认行为类型（%rowtype)

 for r in c_stu loop
   dbms_output.put_line('编号：'||r.sid||',姓名：'||r.sname);
 end loop;
  end;
--实列三：查询所以性别男的学员（带参数的游标）
declare 
  cursorc_stu(sex nvarchar2) is
  select * from t_student where trim(ssex)=sex;
  begin 
    for r in c_stu('男')loop
      dbms_output.put_line('编号：'||r.sid||',姓名：'||r.sname);
      end loop;
      end;
--3）REF游标（动态游标）
declare 
 --声明游标类型
 type ref_stu is ref cursor
 return t_studnet%rowtype;
 --定义变量使用刚刚声明的游标类型
 my_stu ref_stu;
 --定义变量接受结果
 my_stu t-studnet%rowtype;
 begin
 --打开游标
 open c_stu for select * from t_studnet;  
 --循环
 loop
   --提取游标记录
   fetch c_stu into my_stu;
   exit when c_stu%notfound;
  dbms_output.put_line('编号：'||r.sid||',姓名：'||r.sname);
 end loop;
 close c_stu;
 end;

--2.触发器

--触发器：一种数据库对象，在该数据库中特定条件发生时所执行的动作
--触发器与数据表是相关的，当数据表发生了DML sql操作时将会触发对应的操作
--增删改查
--触发器事件
--insert：插入数据时触发

--update：更新数据时触发

--delete：删除数据时触发

UPDATE OF column_name：特定列更新时触发

--语法格式：

--create or replace trigger 触发器名称
--{before|after}{insert|delete|update}
--{of 列名}on 表名
--[for rachc row]
--declare
--变量声明
--begin
--pl/sql块
--end;
--参数说明：
--before:触发时间：在数据库操作之前触发
--after:触发时间：在数据库操作之后触发
--insert:在数据表发生新增时触发
--update:在数据表更新时触发
--delete:在数据表发生删除时触发
--表名：触发器相关连的表，
--for each row;
---写了表示行触发，影响多少行，触发多少次
--不写：表示整个触发器，不管影响多少行，只会触发一次

--基本语法

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} 
{INSERT | UPDATE | DELETE | [OF column_name]}
ON table_name
[REFERENCING OLD AS old NEW AS new]
[FOR EACH ROW]
[WHEN (condition)]
DECLARE
    -- 声明部分
BEGIN
    -- 触发器代码
EXCEPTION
    -- 异常处理
END;

create or replace trigger tri_emp
after update of sal on emp
for each row
when(NEW.sal>OLD.sal)
declare
   diff emp.sal%type;
begin
  diff:=:NEW.sal-:OLD.sal;
  dbms_output.put_line('差额为:'||diff);
end tri_emp;

create or replace trigger tri_emp2
after update of sal on emp
declare
   diff emp.sal%type;
begin
  dbms_output.put_line('调用');
end tri_emp;

update emp set sal=sal+1 where deptno=10;

create table test_seq
(
   id int primary key,
   tname varchar2(20)
);

create sequence test_seq_seq;

select * from test_seq;
insert into test_seq values(test_seq_seq.nextval,'jack');

create or replace trigger test_trig
before insert or update of id
on test_seq
for each row
begin
  if inserting then
    select test_seq_seq.nextval into :NEW.id from dual;
  end if;
  if updating then
    raise_application_error(-20020,'id不允许更新');
  end if;
end test_trig;

--测试

insert into test_seq(tname) values('dav');
select * from test_seq;
update test_seq set id=4 where id=3;