Oracle第十课

一、异常

自定义异常和使用

declare 
  -- Local variables here
  i integer;
  -- 声明一个自定义异常
  myex EXCEPTION;
begin
  -- Test statements here
  IF i IS NULL THEN
    RAISE myex;
  END IF;
  EXCEPTION
    WHEN myex THEN
      dbms_output.put_line('i没有初始化');
      WHEN OTHERS THEN
        dbms_output.put_line('未知异常');
end;

输出错误编号：sqlcode和错误信息:sqlerrm

declare 
  -- Local variables here
  i integer;
begin
  -- Test statements here
  SELECT COUNT(1) INTO i FROM emp WHERE empno=1111111;
  EXCEPTION
      WHEN OTHERS THEN
        -- 输出错误编号和错误信息
        dbms_output.put_line('未知异常'||SQLCODE||'-'||SQLERRM);
end;

Oracle自定义异常的编号从-20000开始到-29999
自定义抛出一个自定义异常，有编号和信息:raise_application_error
```
RAISE_application_error(-20000,'变量没有初始化');
```
可以同时捕获多个异常:when ex1 or ex2 then
异常不能重复捕获
```
when ex1 or ex2 then
...
when ex1 then
...
```
others then必须在其他异常处理后面

二、游标

定义

游标是数据的集合，游标也是数据集合的指针，可以从游标中获取集合中的值
分类
- 自定义游标：静态的和动态的
- 系统游标（隐式的）
游标的操作：打开，遍历，关闭

静态游标

显示打开，显示关闭

declare 
  -- Local variables here
  -- 定义静态游标
  CURSOR c_emp IS SELECT * FROM emp WHERE deptno=10;
  -- 定义变量，存储数据
  v_emp emp%ROWTYPE;
begin
  -- Test statements here
  -- 打开游标
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_emp;-- 取游标中的记录，此时游标指针移动
    dbms_output.put_line(v_emp.empno||'-'||v_emp.ename);
    EXIT WHEN c_emp%NOTFOUND;-- %notfound没有数据
    END LOOP;
  -- 为了避免遗忘，先写关闭游标
  IF c_emp%ISOPEN THEN -- %isopen打开状态属性
    CLOSE c_emp;
  END IF;
  EXCEPTION 
    when OTHERS THEN
      dbms_output.put_line(SQLCODE||'-'||SQLERRM);
end;

-- 优化后的代码
-- Created on 2021/1/26 by 96093 
declare 
  -- Local variables here
  -- 定义静态游标
  CURSOR c_emp IS SELECT * FROM emp WHERE deptno=10;
  -- 定义变量，存储数据
  v_emp emp%ROWTYPE;
begin
  -- Test statements here
  -- 打开游标
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_emp;-- 取游标中的记录，此时游标指针移动
    IF c_emp%NOTFOUND THEN-- %notfound没有数据
      EXIT;
    ELSE
    dbms_output.put_line(v_emp.empno||'-'||v_emp.ename);
    END IF;
    END LOOP;
  -- 为了避免遗忘，先写关闭游标
  IF c_emp%ISOPEN THEN -- %isopen打开状态属性
    CLOSE c_emp;
  END IF;
  EXCEPTION 
    when OTHERS THEN
      dbms_output.put_line(SQLCODE||'-'||SQLERRM);
end;

自动打开和关闭

-- Created on 2021/1/26 by 96093 
declare 
  -- Local variables here
  i integer;
  CURSOR c_emp IS SELECT * FROM emp WHERE deptno=10;
  v_emp emp%ROWTYPE;
begin
  -- Test statements here
  -- 简单操作
  FOR v_emp IN c_emp LOOP
    dbms_output.put_line(v_emp.empno||'-'||v_emp.ename);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE||'-'||SQLERRM);
end;

动态游标

-- Created on 2021/1/26 by 96093 
declare 
  -- Local variables here
  v_deptno emp.deptno%TYPE;
  TYPE dync IS REF CURSOR;-- 定义动态游标类型
  c_emp dync;
  v_emp emp%ROWTYPE;
  v_flag NUMBER :=0; -- 是否有数据，0为无，1为有
begin
  -- Test statements here
  SELECT deptno INTO v_deptno FROM emp WHERE empno=7788;
  OPEN c_emp FOR SELECT * FROM emp WHERE deptno=v_deptno;
  LOOP
    FETCH c_emp INTO v_emp;
    IF c_emp%NOTFOUND THEN
      IF v_flag=0 THEN
         dbms_output.put_line('no data');
        END IF;
      EXIT;
    ELSE
      dbms_output.put_line(v_emp.empno||'-'||v_emp.ename);
      v_flag:=1;
    END IF;
    END LOOP;
    IF c_emp%ISOPEN THEN
      CLOSE c_emp;
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE||'-'||SQLERRM);
  
end;

动态游标和静态游标在定义和打开有区别

隐式游标:sql%rowcount

declare 
  -- Local variables here
  i integer;
begin
  -- Test statements here
  UPDATE emp SET sal=sal+0;
  dbms_output.put_line('影响的行数'||SQL%ROWCOUNT);
  IF SQL%ROWCOUNT>0 THEN
    dbms_output.put_line('update success');
    ELSE
      dbms_output.put_line('update failure');
      END IF;
end;

定义一个带参数的游标

declare 
  -- Local variables here
  v_row emp%ROWTYPE;
  CURSOR c_emp2(dno emp.deptno%TYPE) IS SELECT * FROM emp WHERE deptno=dno;
begin
  -- Test statements here
  OPEN c_emp2(10);
  LOOP
    FETCH c_emp2 INTO v_row;
    EXIT WHEN c_emp2%NOTFOUND;
    dbms_output.put_line(v_row.empno);
    END LOOP;
    IF c_emp2%ISOPEN THEN
      CLOSE c_emp2;
    END IF;
end;

-- 简单操作
declare 
  -- Local variables here
  v_row emp%ROWTYPE;
  CURSOR c_emp2(dno emp.deptno%TYPE) IS SELECT * FROM emp WHERE deptno=dno;
begin
  -- Test statements here
 FOR v_row IN c_emp2(10) LOOP
   dbms_output.put_line(v_row.empno);
   END LOOP;
end;

%isopen,%notfound,%rowcount

三、存储过程

存储过程是一段有名称的代码

简单的存储过程

CREATE OR REPLACE PROCEDURE p1
IS
BEGIN
  dbms_output.put_line('hello world');
  END p1;

带参的存储过程

带入参的存储过程

CREATE OR REPLACE PROCEDURE p1(v_dno emp.deptno%TYPE)
IS
CURSOR c_emp IS SELECT * FROM emp WHERE deptno=v_dno;
v_row emp%ROWTYPE;
BEGIN
  FOR v_row IN c_emp LOOP
    dbms_output.put_line(v_row.empno||'-'||v_row.ename);
    END LOOP;
  END p1;

带出参的存储过程

CREATE OR REPLACE PROCEDURE p1(v_dno IN emp.deptno%TYPE,v_count OUT NUMBER)
IS
BEGIN
  SELECT COUNT(*) INTO v_count FROM emp WHERE deptno=v_dno;
  END p1;
-- 测试
DECLARE
v_count NUMBER;
begin
  -- Call the procedure
/*  p1(v_dno => :v_dno,
     v_count => :v_count);*/
     p1(10,v_count);
     dbms_output.put_line(v_count);
end;

procedure 入参in ,出参out,in可以省略
执行存储过程直接写存储过程名称
p1(参数...)
如果把逻辑都给数据库，则数据库处理就成了瓶颈，影响系统的性能，我们会把工作交由应用服务器来处理，数据库只管数据，不管业务，给数据库松绑
集群：尽量把业务或操作分开执行，就像以前一个人做事情，现在若干个人做事情
存储过程处理业务逻辑，如果有返回通过出参返回

四、自定义函数

自定义函数也是一段编译好的有名称的程序，和存储过程的区别主要是为了得到一些数据（强调的是返回的东西），存储过程强调的是做什么事情，以及过程

自定义函数和使用

-- 获取某部门的员工人数
CREATE OR REPLACE FUNCTION f1(v_dno emp.deptno%TYPE)
RETURN NUMBER IS -- 1.return 类型
v_count NUMBER; -- 2.定义返回类型的变量
BEGIN
  SELECT COUNT(*) INTO v_count FROM emp WHERE deptno=v_dno;
  RETURN v_count; -- 3.返回变量
  END;
-- 使用
SELECT f1(20) FROM dual;

对于一些操作，可以集中在数据库执行，比如批量操作，提供所有信息，不用反复连接数据库
删除10条记录，把id传到存储过程中，一次连接，内部执行
如果不是存储过程，则连一次执行一次，要连10次，这个效率比较低

五、作业

用自定义函数解决问题，显示所有部门的平均工资和总工资和人数，可以用三个函数
给一个字符串，'7788,5566,2233'员工编号，传入存储过程，删除这些员工记录，并给出这些员工删除的结果，'true,false'，删除成功true,删除失败false，以字符串形式返回。

比如：5566删除失败（原因可能是不存在该记录,判断依据用系统隐式游标的rowcount属性

软件下载提取码：qwer