游标+异常

--游标
游标是一种能从多条记录的结果集中每次提取一条记录的机制
可以充当指针 能遍历结果集中所有的行 但是每次只能指向一行
游标提供了在逐行的基础上操作表中数据的方法
游标的结果集是由查询语句定义
--游标的分类
静态游标：显式游标：普通的
                    带参数的
          隐式游标
动态游标：强类型
          弱类型
          动态游标 --重点
--静态和动态游标的区别：
静态游标的结果集是固定的 中间不能改变
动态的结果集是可以改变的 每次打开都可以更换结果集
--显式游标和隐式游标的区别：
显式游标的结果集在declare部分声明 中途不能改变
隐式游标的结果集不用声明 且游标无需打开关闭 这些由系统完成
--静态--显式--普通的
声明一个显式游标
语法：
declare
cursor 游标名 is select 语句;
--打印dept表的数据
declare
cursor cur_1 is select * from dept;
v_1 dept%rowtype;
begin
  open cur_1;--打开游标
  fetch cur_1 into v_1;--提取记录给变量
  dbms_output.put_line(v_1.dname||' '||v_1.deptno||' '||v_1.loc);
  fetch cur_1 into v_1;--fetch一次打一行
  dbms_output.put_line(v_1.dname||' '||v_1.deptno||' '||v_1.loc);
  fetch cur_1 into v_1;--表内容,每列空格隔开
  dbms_output.put_line(v_1.dname||' '||v_1.deptno||' '||v_1.loc);
  fetch cur_1 into v_1;--游标命名cur_
  dbms_output.put_line(v_1.dname||' '||v_1.deptno||' '||v_1.loc);
  fetch cur_1 into v_1;--不够行会一直打印最后一行
  dbms_output.put_line(v_1.dname||' '||v_1.deptno||' '||v_1.loc);
  close cur_1;--关闭游标
end;
游标的四个属性：
cur_name%isopen   判断游标是否打开(对，错)--布尔型
cur_name%found    判断游标是否有值(对，错)--布尔型
cur_name%notfound 判断游标是否没值(对，错)--布尔型
cur_name%rowcount 返回游标指针指过的函数  --数值型
游标的循环：
普通循环+cur_name%notfound
while + cur_name%found
游标+ for 循环
--打印dept表的数据
declare
cursor cur_1 is select * from dept;
v_1 dept%rowtype;
begin
  open cur_1;--打开游标
  fetch cur_1 into v_1;--第一次提取
  while cur_1%found
  loop
    dbms_output.put_line(v_1.dname||' '||v_1.deptno||' '||v_1.loc);
    fetch cur_1 into v_1;
    end loop;
  close cur_1;
end;
普通循环的步骤：begin
open 游标->loop->fetch 提取->退出条件%notfound->打印->end loop->close
while 循环步骤：
open 游标->fetch 提取->while+执行条件%found->loop->打印->提取->end loop->close
--静态--显示--带参
declare
cursor cur_2(v_d number,v_j varchar2) is
   select * from emp where deptno=v_d and job=v_j;
v_e emp%rowtype;
begin
  open cur_2(20,'MANAGER');--打开游标的时候赋参
  loop
    fetch cur_2 into v_e;
    exit when cur_2%notfound;
    dbms_output.put_line(v_e.ename||' '||v_e.deptno||' '||v_e.job);
    end loop;
    close cur_2;
-------------------------------
  open cur_2(20,'ANALYST');--重新打开游标的时候赋参
  loop
    fetch cur_2 into v_e;
    exit when cur_2%notfound;
    dbms_output.put_line(v_e.ename||' '||v_e.deptno||' '||v_e.job);
    end loop;
    close cur_2;
-------------------------------
  open cur_2(30,'MANAGER');--重新打开游标的时候赋参
  loop
    fetch cur_2 into v_e;
    exit when cur_2%notfound;
    dbms_output.put_line(v_e.ename||' '||v_e.deptno||' '||v_e.job);
    end loop;
    close cur_2;
end;
--打印工作是MANAGER的姓名和工作
--工作是SALESMAN的姓名和佣金
--工作是CLERK的姓名和入职日期
declare
cursor cur_3(v_j varchar2) is
   select * from emp where job=v_j;--游标内赋参条件和类型
v_e emp%rowtype;
begin
  open cur_3('MANAGER');--打开游标赋参
  loop--循环
    fetch cur_3 into v_e;--提取
    exit when cur_3%notfound;--退出
    dbms_output.put_line(v_e.ename||' '||v_e.job);--打印表内对应列
    end loop;--结束循环
    close cur_3;
-------------------------------
  open cur_3('SALESMAN');--重新打开赋参
  loop
    fetch cur_3 into v_e;
    exit when cur_3%notfound;
    dbms_output.put_line(v_e.ename||' '||v_e.comm);
    end loop;
    close cur_3;
-------------------------------
  open cur_3('CLERK');--重新打开赋参
  loop
    fetch cur_3 into v_e;
    exit when cur_3%notfound;
    dbms_output.put_line(v_e.ename||' '||v_e.hiredate);
    end loop;
    close cur_3;
end;
--静态--隐式游标
不用声明 不同打开 不用赋值 不用关闭
常见方式有 delete update insert select...into..赋值
隐式游标的四个属性：
sql%isopen   判断游标是否打开 永远返回错
sql%found    判断指针是否有值 如果最近一次有值返回对 否则返回错
sql%notfound 判断指针是否没值 最近一次没有 返回对
sql%rowcount 返回指针指过的行数 返回最近一次的记录数
--创建emp1数据同emp;
--drop table emp1;
create table emp1 as select * from emp;
--
declare
v_e emp%rowtype;
begin
---------更新10部门数据-------------------
  update emp1 set ename=initcap(ename) where deptno=10;
  if sql%found then
  dbms_output.put_line('真的更新了');
  elsif sql%notfound then
  dbms_output.put_line('没有更新');
  end if;
  dbms_output.put_line('更新了'||sql%rowcount||'行');
----------20部门删除-------------------
  delete from emp1 where deptno=20;
  if sql%found then
  dbms_output.put_line('真的删除了');
  elsif sql%notfound then
  dbms_output.put_line('没有删除');
  end if;
  dbms_output.put_line('删除了'||sql%rowcount||'条');
-----------插入数据----------------------
  insert into emp1 select * from emp;
  if sql%found then
  dbms_output.put_line('真的插入了');
  elsif sql%notfound then
  dbms_output.put_line('没有插入');
  end if;
  dbms_output.put_line('插入了'||sql%rowcount||'条');
-----------赋值------------------------
  select * into v_e from emp where ename='SMITH';
  if sql%found then
  dbms_output.put_line('真的赋值了');
  elsif sql%notfound then
  dbms_output.put_line('没有赋值');
  end if;
  dbms_output.put_line('赋值了'||sql%rowcount||'行');
----------------------------------
  if sql%isopen then
  dbms_output.put_line('打开了');
  else dbms_output.put_line('没有打开');
  end if;
end;
--强类型和弱类型的区别
强类型有return open时for后面的结果集必须和return后面的保持一致
弱类型没有return open时for后面的结果集比较自由

--动态--动态
--打印今年所有的周末 每个人的名字和他上级的名字
declare
cur_1 sys_refcursor;--声明一个动态游标
v_m date;
v_1 varchar(20);
v_2 varchar(20);
begin
  --今年所有周末
  open cur_1 for
  select * from (
  select trunc(sysdate,'yyyy')-1+level ll
  from dual
  connect by level <=365)
  where to_char(ll,'dy') in ('星期六','星期日');
    loop
      fetch cur_1 into v_m;
      exit when cur_1%notfound;
      dbms_output.put_line(v_m);
      end loop;
      close cur_1;
----每个人的名字和他上级的名字-------------
  open cur_1 for
  select a.ename,b.ename
  from emp a
  join emp b
  on a.mgr=b.empno;
  loop
      fetch cur_1 into v_1,v_2;
      exit when cur_1%notfound;
      dbms_output.put_line(v_1||'经理是'||v_2);
      end loop;
      close cur_1;
end;
--游标+for循环
1.带游标名的
声明一个显式游标 不用打开 不用赋值 不用关闭
declare
cursor cur_1 is select * from dept;
begin
  for i in cur_1
    loop
      dbms_output.put_line(i.dname||' '||i.deptno||' '||i.loc);
    end loop;
end;
2.不带游标名的
不用声明 不用打开 不用赋值 不用关闭
begin
  for i in (select * from dept)
    loop
      dbms_output.put_line(i.dname||' '||i.deptno||' '||i.loc);
    end loop;
end;
--打印每个部门每种职位的人数
begin
  for i in (select deptno,job,count(job) 人数 from emp group by deptno,job)
    loop
      dbms_output.put_line(i.deptno||' '||rpad(i.job,9,' ')||' '||i.人数);
    end loop;
end;

异常：error
分类：预定义异常(系统中已经有名字(名字 错误代码 错误描述))
      非预定义异常(系统已经有但没有名字 只有错误代码和描述)
      自定义异常(自己定义的异常)
--预定义异常
      违反了唯一键限制  dup_val_on_index
      没有找到数据  no_data_found
      返回多行  too_many_rows
      视图用0做除数  zero_divide
      其他异常  others
创建一张表用来存放异常信息
create table t_err(code number,errm varchar(500),err_date date);
错误代码
错误描述

declare
v_e varchar(200);
v_c number;
v_err varchar(200);
begin
  select ename into v_e from emp where empno=&empno;
  dbms_output.put_line(v_e);
exception
  when others then  dbms_output.put_line(sqlcode||sqlerrm);
  v_c:=sqlcode;
  v_err:=sqlerrm;
  insert into t_err values(v_c,v_err,sysdate);
  --在表中插入数据的时候需要借助变量 不能直接插入
  --处理异常数据的时候要先打印后插入
end;

非预定义异常(了解)
declare
err exception;
v_c number;
v_e varchar(20);
pragma exception_init(err,-2291);--把异常代码是2291的起了一个名
begin
  insert into emp(empno,deptno) values(1234,50);
  exception
    when err then
      v_c:=sqlcode;
      v_e:=sqlerrm;
      dbms_output.put_line(sqlcode||sqlerrm);
  insert into t_err values(v_c,v_e,sysdate);
end;--底层架构了解一下，方便之后优化
数据库的锁：
读锁(共享锁)
写锁(排他锁)
死锁:多个用户互相占用资源 导致谁也不能完成操作
     陷入了无穷无尽的等待
--自定义异常
--往emp_1插入一条数据 要求必须是7开头的并且是4位
declare
v_empno number:=&empno;
err exception;
begin
  if v_empno not like '7___' then
    raise err;--不满足业务需求 抛出异常
  end if;
  insert into emp_1(empno) values(v_empno);
  exception--最后写
    when err then
    dbms_output.put_line('编号不合规');
    raise_application_error(-20001,'员工编号不满足');
    --带内容的弹框报错
    --错误代码的取值范围-20000~-20999之间
    --raise;--不带内容的弹框报错
end;
--创建一个emp_2表 格式同emp
--插入一条数据 要求员工编号7开头并且4位 工资大于300 工作不能为空
--如果不符合规范 异常信息插入t_err中 并且弹窗报错
create table emp_2 as select * from emp where 1=2;
declare
v_empno number:=&empno;
v_sal number:=&sal;
v_job varchar(20):='&job';
err_1 exception;
err_2 exception;
err_3 exception;
begin
  if v_empno not like '7___' then raise err_1;
  elsif v_sal<300 then raise err_2;
  elsif v_job is null then raise err_3;
  end if;
  insert into emp_2(empno,sal,job) values(v_empno,v_sal,v_job);
  exception
    when err_1 then
    dbms_output.put_line('编号不合规');
    insert into t_err values(-20001,'编码错误',sysdate);
    commit;--有这个才会继续运行下面的
    raise_application_error(-20001,'编号不满足');
    when err_2 then
    dbms_output.put_line('工资不合规');
    insert into t_err values(-20002,'工资不满足',sysdate);
    commit;
    raise_application_error(-20002,'工资不满足');
    when err_3 then
    dbms_output.put_line('工作不合规');
    insert into t_err values(-20003,'工作不满足',sysdate);
    commit;
    raise_application_error(-20003,'工作不满足');
end;
注:异常处理时既要插入又要弹窗  必须先插入提交事务后再去弹窗
select * from t_err;
自定义函数
语法:
pl/sql 匿名块
       有名块:自定义函数 function
       存储过程 procedure
       包  package
       触发器  trigger
       定时器
自定义函数
语法:
create [or replace] function 函数名[参数1 [in]out 类型,参数2..]
return 类型 is|as
[声明的变量]
  begin
  ...
  return 值|变量;
  [exception]
end;
1.没有参数
--制作一个Π函数
create or replace function fun1 return number is
v_a number;
begin
  v_a:=acos(-1);
  return v_a;
end;
--
函数调用
select acos(-1) from dual;
2.有 in
--输入一个员工编号 返回工资
create or replace function fun2(v_empno number) return number is
v_s number;
begin
  select sal into v_s from emp where empno=v_empno;
  return v_s;
  exception
    when no_data_found then
      return 0;
end;
select fun2(7788) from dual;
--输入一个部门编号和一个工作返回这个部门这种职位的人数 没有返回0
create or replace function fun3(v_deptno number,v_job varchar2) return number is
v_d number(5);
begin
  select count(1) into v_d from emp
  where deptno=v_deptno and job=v_job;
  return v_d;
  exception
    when no_data_found then
      return 0;
end;
select fun3(20,'CLERK') from dual;
--输入一个部门 返回这个部门所有人名用,隔开
create or replace function fun4(v_deptno number) return varchar is
v_de varchar(50);
begin
  select WM_CONCAT(ename) into v_de from emp
  where deptno=v_deptno
  group by deptno;
  return v_de;
  exception
    when no_data_found then
      return '错误';
end;
select fun4(50) from dual;
--输入一个年份 返回平年还是闰年
create or replace function fun6(v_y number) return varchar2 is
v_a varchar(20);
begin
  select case when to_char(to_date(v_y||'-12-31','yyyy-mm-dd'),'ddd')=366 then '闰年'
              else '平年' end
  into v_a from dual;
  return v_a;
end;
select fun6(8) from dual;
--随机函数
dbms_random.value(小数,大数) 生成小数和大数之间的随机数
dbms_random.string('格式',长度) 生成给定长度的随机字符串
U   大写
L   小写
A   字母
X   大写字母和数字
P   可打印字符
select trunc(dbms_random.value(1,100)) 随机数字,
       dbms_random.string('X',6)
from dual;