oracle学习篇(四）

oracle学习篇(四）

1 PL/SQL异常处理

1.1 预定义异常

1.1.1 内容

oracle里面已经存在的异常
如果是自定义异常,一般写的编号是20000-20999之间

1.1.2 处理异常语法

exception
     when 异常类型1 then
         输出异常类型信息1;
     when 异常信息2 then
         输出异常类型信息2;
     --以上都不符合的时候,就去执行后续的代码   
     when others then   
        输出以上异常类型都不满足时的信息;

1.1.3 处理异常代码

declare 
   vid number:=8888;
   vrow emp%rowtype;
begin
  SELECT * into vrow FROM emp;
  --dbms_output.put_line(1/0);
  --异常 大于2000开始 弹出来的好一点
  exception
     when ZERO_DIVIDE then
        dbms_output.put_line('除数不能为0');
     when NO_DATA_FOUND then
         dbms_output.put_line('into时某一查询到任何数据');
     --否则的意思    
     when others then   
       dbms_output.put_line('发生未知异常');   
end;

1.1.4 运行截图

1.2 自定义异常

1.2.1 以弹窗的方式进行抛出

a 语法

raise_application_error(自定义异常编号,'工资太低异常');
-- 自定义异常编号范围通常是在[-20001,-20999]

b 示例代码

declare
 vsal number;
begin
  SELECT sal into vsal from emp where empno=7369;
  if vsal<3000 then
      --弹窗形式 无法捕获
    raise_application_error(-20001,'工资太低异常');
  else
    dbms_output.put_line('工资是:'||vsal);  
  end if;  
end;

c 示例代码运行截图

1.2.2 抛出异常,可以通过exception去进行捕获的

a 语法

declare
 自定义异常名 exception;
begin
  raise 自定义异常名;  
  exception 
    when 自定义异常名 then
      异常处理语句; 
en

b 示例代码

-- 抛出异常的方式 可以通过exception去捕获,myex是异常对象
declare
 vsal number;
 myex exception;
begin
  SELECT sal into vsal from emp where empno=7369;
  if vsal<3000 then
      --抛出异常 捕获的方式去写
    raise myex;  
  else
    dbms_output.put_line('工资是:'||vsal);  
  end if;
  -- 捕获产生的异常
  exception 
    when myex then
    dbms_output.put_line('工资低了');      
end;

c 示例代码运行截图

2 游标

2.1 游标初识

游标cursor 等同java中的ResultSet结果集对象 存储的就是一个表,存储的是多行多列的数据,
解决了之前%rowtype仅能存储一行的尴尬局面
   能够存储多行多列的数据
   1.隐式游标
      oracle自带的游标,是不需要去声明的
      sql%rowcount 得到最近DML的受影响行数
   2  显式游标
    1 静态游标 cursor 游标的结果集在定义是就确定了
   2  ref游标 动态游标 sys_refcursor 能够在开启时动态赋值结果集内容
   sys可以作为参数去传递的

2.2 显示游标

2.2.1 静态游标

a 语法

declare
   --静态时 定义时确定好了游标的值
   cursor 游标值 is select * from dept;

b 示例代码

declare
   --静态时 定义时确定好了游标的值
   cursor vdept is select * from dept;
   vrow dept%rowtype;-- 用于接收游标的每一行数据
begin
     -- 开启游标 
     open vdept;
     --遍历游标
     loop
        --每次提取一行数据
       fetch vdept into vrow; 
       exit when vdept%notfound;--当游标没有数据时退出
       dbms_output.put_line('编号:'||vrow.deptno||'部门名'||vrow.dname);  
     end loop;  
end;

c 示例代码运行截图

2.2.2 动态游标

a 语法

-- 动态游标三步骤:①通过sys_refcursor,②  open cemp for去开启游标③ fetch cemp into 取出游标中的值
declare
   cemp sys_refcursor;--定义游标
   --自定义变量
   a varchar2(20);
   b varchar2(20);
   -- 自定义变量类型
begin
     -- 开启游标 用for去开启
     -- open cemp for select * from emp where deptno=vdeptno; --关闭是close
     open 游标名 for select语句
end;     

b 示例代码

declare
   -- 游标的值是根据输入的内容来决定的
   vdeptno number:=&请输入部门编号;
   cemp sys_refcursor;--定义游标
   vrow emp%rowtype;
   --自定义变量
   a varchar2(20);
   b varchar2(20);
   -- 自定义变量类型
begin
     -- 开启游标 用for去开启
     -- open cemp for select * from emp where deptno=vdeptno; --关闭是close
     open cemp for select ename,job from emp where deptno=vdeptno;
     --遍历游标 loop-end loop
     loop
       --fetch cemp into vrow; 
       fetch cemp into a,b;
       exit when cemp%notfound;--当游标没有数据时退出
       --dbms_output.put_line(vrow.empno||'--'||vrow.ename);  
       dbms_output.put_line(a||'--'||b); 
     end loop;  
end;

c 示例代码运行截图

c.1 输入部门编号前

c.2 输入部门编号后

2.3 隐式游标

2.3.1 语法

--sql%rowcount 返回的是最近的一次的受影响行数,如果变动的是单条数据,就可以使用等值比较==
sql%rowcount

2.3.2 示例代码

begin
     delete from emp where empno=10;
     --拿到上一次的操作结果
     delete from emp where empno=7369;
     if sql%rowcount>0 then
        dbms_output.put_line('操作成功'); 
        commit;
     else
        dbms_output.put_line('操作失败');   
        rollback; 
     end if;
end;

2.3.3 示例代码运行截图

2.4 sql%rowcount小例子练习

2.4.1 思路

对于一个完整的转账业务而言
至少存在两个更新sql语句 也就是有两个受影响行数
可以用两个变量接收sql%rowcount
如果两个变量都大于0,就代表整个业务是完整的,可以通过
否则,需要进行回滚业务

2.4.2 示例代码

declare
  vaccount1 number:=&请输入转出账号;
  vaccount2 number:=&请输入收款账号;
  vmoney number:=100;--转账固定100
  vresult1 number;
  vresult2 number;
begin
  --实现账号1给账号2转账
  update emp set sal=sal-vmoney WHERE empno=vaccount1;
  vresult1:=sql%rowcount;
  update emp set sal=sal+vmoney WHERE empno=vaccount2;
  vresult2:=sql%rowcount;
  --判断是否转账成功
  if vresult1>0 AND vresult2>0 then
     dbms_output.put_line('操作成功');
     commit;
  else
     dbms_output.put_line('操作失败');
     rollback;
  end if;
end;

2.4.3 示例代码运行截图

a 相互转账的账户

b 控制台截图

c 更新前

d 更新后

3 触发器

3.0 理解

A本质是一个存储过程,发生特定事件时,oracle会执行里面的代码
这里所写的两个例子都是dml(增删改)的触发器
B 写触发器需要注意的是:是在该执行语句前触发,还是在该执行语句之后触发

3.1 语句级触发器

3.1.1 语法

create or replace trigger 触发器名字
before insert or delete or update on emp
--没有for each row,就代表是语句级触发器
begin
   -- 触发语句时执行的代码
end;

3.1.2 示例代码

create or replace trigger tg_empdml
before insert or delete or update on emp
begin
   if to_char(sysdate,'day') in('星期六','星期五') then
      -- raise_application_error 弹出错误弹窗
      raise_application_error(-20888,'周末不上班 搞啥呢');
   end if;
end;

3.1.3 示例代码运行截图

3.2 行级触发器

3.2.0 语法

CREATE OR replace trigger 触发器名字
-- OR replace指的是存在一样名字的触发器时,是直接去覆盖掉,
--若不写这个,然后又存在相同的,运行就会报错
after/before （新增|删除|修改）
for each row --行级触发器,每一次都触发
declare
  -- 变量定义位置
begin
  -- :new是修改后的值,行级修改值 :old修改之前的值
  --每次触发时执行的语句
end;

3.2.1 三个例子

a 记录表删除、修改的日志信息

a.1 示例代码

CREATE OR replace trigger tg_emplog
after update or delete on emp
for each row --行级触发器,每一次都触发
declare
 vtime varchar2(20):=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');  
begin
  -- :new是修改后的值,行级修改值 :old修改之前的值
  if updating then
   dbms_output.put_line(vtime||'修改了员工:'||:new.ename);
  --elsif是else..if的意思 
  elsif deleting then
    dbms_output.put_line(vtime||'删除了员工:'||:old.ename); 
  end if;   
end;

a.2 示例代码运行截图

b 设置员工只能涨薪

b.1 示例代码

create or replace trigger tg_empsal
before update on emp
for each row
declare
begin
  if :new.sal<:old.sal then
     raise_application_error(-20999,'扣我工资,砍死你'); 
  end if;
end;

b.2 示例代码运行截图

c 实现主键自动递增

c.1 示例代码

CREATE or replace trigger tg_dept
before insert on dept
  for each row when(new.deptno is null) -- 添加 这里不用写冒号 添加部门号是空值就触发
declare 
  vid number;
begin
  select sq_dept.nextval into vid from dual;
  --新设置的编号设置成我的序列值
  :new.deptno:=vid;
end;

c.2 示例代码运行截图

执行前

执行后