游标和触发器

游标和触发器

游标

游标：字面理解：游动的光标

pl/sql 块中：查询操作时select，执行DMl命令是，oracle会将结果缓存到一块内存中

游标：临时表（将结果集中的数据缓存在内存中）+指向该内存中的数据指针。

作用：可以单独对数据进行定位。逐行去操作数据

读数据过程分析：从数据文件（磁盘中存储）–》内存中–》显示

需求

根据每个员工的工资进行加薪

sql命令操作数据是，针对列集合进行操作，无法单独对行进行定位处理，使用游标解决

游标分类

1.显示游标：针对select命令，，返回多行结果

2.隐式游标：自动使用，执行：insert，uodate，delete，select…into命令是

游标的状态属性

%ISOPEN：布尔属性，true/false，判断游标是否打开

%FOUND：布尔属性，true/false，判断游标中是否取到数据

%BOTFOUND：布尔属性，true/false，判断游标中是否没有找到数据，没有找到ture找到false

%ROWCOUNT：整形；获取到游标中实际读取到的行数

隐式游标：sql+%属性

显示游标：名字+%属性

用作条件的一部分

显示游标

游标（cursor） ：不是数据对象，无需create

基本步骤

1.声明游标

declare
    cursor 游标名（参数） is select 命令；

2.打开游标

open 游标名

将select查询得到的结果存入内存中，游标会定位到首行，准备操作

3.提取数据

提取
fetch 游标名 into 变量名1，变量名2.。。

逐行提取

执行业务操作

4.关闭游标

close 游标名称

释放内存

显示游标实例

实例1

查询员工表中，部门编号为20的信息，工资降序排列；

declare
   -- 1.声明游标
   cursor c1 is select empno,ename,sal from emp where deptno=20 order  by sal desc;
      v_no emp.empno%type;
      v_name emp.ename%type;
      v_sal emp.sal%type;
begin 
  -- 2.打开游标
  open c1;
  -- 3.提交数据
  loop
      fetch c1 into v_no,v_name,v_sal;
      exit when c1%notfound;-- 退出
        if v_sal <3000 then
          dbms_output.put_line('需要加：'||v_no||v_name||v_sal);
        else
          dbms_output.put_line(v_no||v_name||v_sal);
          end if;
        end loop;
        --4.关闭游标
        close c1;
        end;

带参数的游标

1.根据部门编号查询部门的工资信息，按工资降序排列

       declare
   -- 1.声明游标
   cursor c1(v_deptno number) is select empno,ename,sal from emp where deptno=v_deptno order by sal desc;
      v_no emp.empno%type;
      v_name emp.ename%type;
      v_sal emp.sal%type;
begin 
  -- 2.打开游标
  open c1(10);
  -- 3.提交数据
  loop
      fetch c1 into v_no,v_name,v_sal;
      exit when c1%notfound;-- 退出
        if v_sal <3000 then
          dbms_output.put_line('需要加：'||v_no||v_name||v_sal);
        else
          dbms_output.put_line(v_no||v_name||v_sal);
          end if;
        end loop;
        --4.关闭游标
        close c1;
        end;

游标类型的变量

把游标当类型来使用，声明游标类型的变量

--1.游标类型 名称 is 引用 cursor;
type cur_type is ref cursor;

--2.声明变量
--变量名 类型
cur1 cur_type;

实例

declare 
  type cur_type is ref cursor;  --声明游标类型
    cur1 cur_type; --声明变量
    v_no emp.empno%type;
    v_name emp.ename%type;
    v_sal emp.sal%type;
begin 
  -- 打开游标是执行查询命令
  open cur1 for select empno,ename,sal from emp where deptno=20 order by sal desc;
      loop
        fetch cur1 into v_no,V_name,v_sal;
        exit when cur1%notfound;
        dbms_output.put_line(v_no||v_name||v_sal);
        end loop;
        close cur1;
    end;

for循环简化游标操作

declare
   cursor c1 is select * from emp; --声明游标
begin
for 变量名（行数据）in 游标名
 for row1 in c1 loop
   dbms_output.put_line(row1.empno||row1.ename||row1.sal);
  end loop;
    end;
    
    
begin
   for row1 in (select * from emp) loop
     dbms_output.put_line(row1.empno||row1.wname||row1.sal);
     end loop;
     end;

实例

根据每个员工的工资水准进行加薪

1.8000 > 0.05

2.5000 >0.03

3.3000 >0.01

分析：

1.游标
2.获取每一个员工的工号、工资
2.逐行判断
4.更新，commit

begin
  for row1 in(select * from emp) loop
    if row1.sal>=8000 then
      --只更新当前员工的工资信息
      update emp set sal=sal*(1+0.05) where empno=row1.empno;
    elsif row1.sal>=5000 then
      update emp set sal=sal*(1+0.03) where empno=row1.empno;
    else
      update emp set sal=sal*(1+0.01) where empno=row1.empno;
      end if;
      commit;
      end loop;
      end;

隐式游标

begin 
    update emp set sal=sal+1 where empno=7396;
    --使用隐式游标：对前面执行的sql命令进行各种判断
    if SQL%FOUND then
      dbms_output.put_line('加薪成功');
    else
      dbms_output.put_line('加薪失败');
      end if;
      end;

触发器

触发器：自动执行的存储过程；当执行数据操作是（DML操作是，数据库时间（登录登出）等；会自动激活触发器去执行。一种数据库对象（create）

触发器分类

1.触发DML器：执行insert，update，dete时会激活触发器

2.代替触发器instead of，视图

3.数据库事件触发器：执行特定的数据库时间（登录登出

创建触发器

语法：

create or replace trigger 名字
--触发时间，在操作之前或之后触发
before | after
-- 触发事件 （操作） ；会引发（激活） 触发器自动执行的操作
insert| update| delete
--触发对象，触发器依赖的数据库对象
on 表|视图【 for each row】
as
   --声明
 begin
   --业务代码：DML命令；不能是DDL，事务控制语句
   end

构成说明

触发时间：before|after在操作之前或之后触发

触发时间（操作）：会引发（激活）触发器支佛那个执行的操作

触发对象：触发器依赖的数据库对象

触发频率：

语句级触发器：执行insert，update，delete

行级触发器：每一行数据都会受到影响

注意事项

1.触发器中能写的：DML命令不能是DDL，数据控制语句

2.触发器中最多32k，如果业务代码多，将业务代码封装到过程中，触发器可以调用过程

DML 触发器实例

before触发器

实例：不允许在指定的入去修改某表中是数据

create or replace trigger tg_check
before update or delete
on emp
begin
    if to_char(Sysdate,'by')='星期二' then
      --raiase_application_error:引发自定义的错误，抛给客户端程序（java程序）
      --参数 1：错误号 -20000到-20999之间
      --参数2：错误信息，自定义
      --触发器，使用过程中使用
    raiase_application_error(-20001,'周二不适合修改数据');
    end if;
    end;      

行触发器：

触发器内部使用的临时表

：old：存放旧数据；执行update，delete是会用到；

update=delete+insert

：new ：存放新数据；执行update，insert时会用到；update=delete+insert

实例不允许对员工进行降薪操作

分析

1.如果修改数据后的数据小于修改Ian的数据

2.修改后的数据

3.修改前的数据

create or replace trigger tg_sal
after --在操作执行后
update
on emp for each row
begin 
  if(:old.sal>:new.sal)then
  raise_application_error(-20002,'不允许降薪');
  end if ;
  end;

实例对不同的操作输出日志记录

触发器内部使用

INSERTING：判断是否执行插入操作 ； true/false

UPDATING:判断是否执行修改操作 true/false

DELETING：判断是否执行删除操作; true/false

create or replace trigger tg6
after insert or update or delete 
on emp
begin
  if INSERTING then 
    dbms_output.put_line('insert');
  elsif updating then 
    dbms_output.pit_line('修改');
    else
      dbms_output.put_line('delete');
      end if;
      end;

after触发器

实例：记录删除数据的日志

1.将删除的数据插入到日志表中

create or replace trigger tg2
after delete on emp for each row 
begin
--写过程
insert into tmp1(empno,ename) values(:old.empno,:old.ename);
end;

视图

虚拟表：查询命令

授权：

使用管理员

grant create view to scott;

创建视图

create view v11 
as 
select deptno,avg(sal) avgsal,max(sal) maxsal,min(sal) minsal 
from emp group by 
deptno;

--使用
select * from v11

替代触发器

数据库时间触发器

执行相应的数据库事件时会激活的触发器（启动、加载、登录、登出）

系统事件

事件属性函数 功能
ora_client_ip_address 返回客户端的ip地址
ora_database_name 返回当前数据库名
ora_dict_obj_name 返回DDL操作所对应的数据库对象名
ora_dict_obj_owner 返回DDL操作所对应的对象的说有者名
ora_dict_obj_type 返回DDL操作对应的数据库对象的类型
ora_instance_num 返回列程号
ora_is_alter_column(column_name IN VARCHAR2) 检测特定列是否别修改
ora_is_drop_column(column_name IN VARCHAR2) 检测特定列是否被删除
ora_login_user 返回登录用户名
ora_sysevent 返回触发触发器的系统事件名

需要管理员（DBA）的权限

1用管理员登录创建实验表

2.登录触发器

3.登出触发器

-- 模拟创建了实验表
--模拟创建实验表
create table logs(
     loginname varchar2(100),--登录的用户名
     eventname varchar2(100),--事件名
     logindate date--事件
);
--logon登录事件
create or replace trigger tg_login
after logon on database
begin 
  lnser ino logs values(ora_login_user,ora_sysevent,sysdate)
  end;
  --logoff登出事件
  create or replace trigger tg_login
before logoff on database
begin 
  lnser ino logs values(ora_login_user,ora_sysevent,sysdate)
  end;

触发器禁止和启用

--禁止触发器
alter trigger tg_insertEmp3 disable;
alter trigger tg_insertEmp3 enable;