Oracle第十课课后作业

一、作业

用自定义函数解决问题，显示所有部门的平均工资和总工资和人数，可以用三个函数

-- 求平均工资
CREATE OR REPLACE FUNCTION fn_avgsal(dno emp.deptno%TYPE)
RETURN NUMBER
IS
avgsal NUMBER;
BEGIN
  SELECT trunc(AVG(sal)) INTO avgsal FROM emp WHERE deptno=dno;
  RETURN avgsal;
END;
-- 求总工资
CREATE OR REPLACE FUNCTION fn_sumsal(dno emp.deptno%TYPE)
RETURN NUMBER
IS
sumsal NUMBER;
BEGIN
  SELECT trunc(SUM(sal)) INTO sumsal FROM emp WHERE deptno=dno;
  RETURN sumsal;
END;
-- 求部门人数
CREATE OR REPLACE FUNCTION fn_total(dno emp.deptno%TYPE)
RETURN NUMBER
IS 
total NUMBER;
BEGIN
  SELECT COUNT(*) INTO total FROM emp WHERE deptno=dno;
  RETURN total;
END;
-- 显示
SELECT deptno 部门号,fn_avgsal(deptno) 平均工资,fn_sumsal(deptno) 总工资,fn_total(deptno) 总人数 FROM emp WHERE deptno IS NOT NULL GROUP BY deptno;

给一个字符串，'7788,5566,2233'员工编号，传入存储过程，删除这些员工记录，并给出这些员工删除的结果，'true,false'，删除成功true,删除失败false，以字符串形式返回。

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

CREATE OR REPLACE PROCEDURE p1(s VARCHAR)
IS
-- n变量来记录两个逗号的位置
n1 INTEGER;
n2 INTEGER;
-- v_emp来记录提取到的员工的编号
v_emp1 NUMBER;
v_emp2 NUMBER;
v_emp3 NUMBER;
BEGIN
  -- 找到两个逗号的位置
  SELECT INSTR(s,',',1,1) INTO n1 FROM dual;
  SELECT INSTR(s,',',1,2) INTO n2 FROM dual;
  SELECT to_number(SUBSTR(s,1,4)) INTO v_emp1 FROM dual;
  SELECT to_number(SUBSTR(s,n1+1,4)) INTO v_emp2 FROM dual;
  SELECT to_number(SUBSTR(s,n2+1,4)) INTO v_emp3 FROM dual;
  DELETE  FROM emp WHERE empno=v_emp1;
  IF SQL%ROWCOUNT>0 THEN
    dbms_output.put_line('true');
  ELSE
    dbms_output.put_line('false');
  END IF;
  -- 删除员工2
    DELETE  FROM emp WHERE empno=v_emp2;
  IF SQL%ROWCOUNT>0 THEN
    dbms_output.put_line('true');
  ELSE
    dbms_output.put_line('false');
  END IF;
  -- 删除员工3
    DELETE  FROM emp WHERE empno=v_emp3;
  IF SQL%ROWCOUNT>0 THEN
    dbms_output.put_line('true');
  ELSE
    dbms_output.put_line('false');
  END IF;
END p1;

-- 在test窗口测试
declare 
  -- Local variables here
  i integer;
  s VARCHAR(30);
begin
  -- Test statements here
  s:='7788,5566,2233';
  p1(s);
  
end;

二、异常

自定义一个异常，当i未初始化时抛出异常，处理异常

declare 
  -- Local variables here
  i INTEGER :=0;
  myex EXCEPTION;
BEGIN
  -- 抛出一个系统自己编号的异常
  IF i IS NULL THEN
    RAISE myex;
  END IF;
  -- 抛出一个带有编号和信息的异常,自己定义的编号范围为（-20000,-29999)
  IF i=0 THEN
    raise_application_error(-20000,'i值不能为0');
  END IF;
  EXCEPTION
    WHEN myex THEN
      dbms_output.put_line('i未初始化');
    WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE||'-'||SQLERRM);
  -- Test statements here
end;

异常不能重复抛出，但是异常可以同时捕获
```
when ex1 or ex2 or ex3 then
...
```

三、游标:cursor

定义

游标是数据的集合，也可以说是数据集合的指针，可以从游标中获取集合中的值，一般在程序中使用，如：存储过程，函数，触发器
类型：
- 系统游标（也叫隐式游标）
- 用户游标：包含静态游标和动态游标
游标的操作：打开游标，遍历游标，关闭游标

用户游标

静态游标

手动打开游标，关闭游标，需要写指针移动的语句

declare 
  -- Local variables here
  -- 静态游标：后面的select语句是固定的
  CURSOR c_emp IS SELECT * FROM emp WHERE deptno=10;
  -- 定义一个变量，接收游标所指向的记录
  v_row emp%ROWTYPE;
begin
  -- Test statements here
  -- 1.打开游标
  OPEN c_emp;
  -- 2. 遍历游标
  LOOP
    -- 游标指针的移动,取出游标所指向的记录，赋值给变量
    FETCH c_emp INTO v_row;  dbms_output.put_line(v_row.empno||'-'||v_row.ename);
    -- 当遍历完成的时候，跳出循环
    EXIT WHEN c_emp%NOTFOUND;
  END LOOP;
  -- 3.关闭游标
  IF c_emp%ISOPEN THEN
    CLOSE c_emp;
  END IF;
  -- 4.异常处理
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE||'-'||SQLERRM);
end;

简单操作，不用打开和关闭游标

declare 
  -- Local variables here
  i integer;
  -- 定义一个静态游标
  CURSOR c_emp IS SELECT * FROM emp WHERE deptno=10;
  -- 定义一个变量，用来循环
  v_row emp%ROWTYPE;
begin
  -- Test statements here
  -- 使用for循环
  FOR v_row IN c_emp LOOP
    dbms_output.put_line(v_row.empno||'-'||v_row.ename);
  END LOOP;
end;

带参数的静态游标

-- 方式一
declare 
  -- Local variables here
  -- 定义游标
  CURSOR c_emp(dno emp.deptno%TYPE) IS SELECT * FROM emp WHERE deptno=dno;
  -- 定义一个行变量接收游标指向的值
  v_row emp%ROWTYPE;
begin
  -- Test statements here
  -- 1.打开游标
  OPEN c_emp(10);
  -- 2.遍历游标
  LOOP
    FETCH c_emp INTO v_row;
    dbms_output.put_line(v_row.empno||'-'||v_row.ename);
    -- 跳出条件
    EXIT WHEN c_emp%NOTFOUND;
  END LOOP;
  -- 3.关闭游标
  IF c_emp%ISOPEN THEN
    CLOSE c_emp;
  END IF;
end;
-- 方式二
declare 
  -- Local variables here
  -- 定义一个游标
  CURSOR c_emp(dno emp.deptno%TYPE) IS SELECT * FROM emp WHERE deptno=dno;
  -- 定义一个行变量，用来实现for循环
  v_row emp%ROWTYPE;
begin
  -- Test statements here
  FOR v_row IN c_emp(10) LOOP
    dbms_output.put_line(v_row.empno||'-'||v_row.ename);
  END LOOP;
end;

动态游标

declare 
  -- Local variables here
  -- 1.定义动态游标的类型
  TYPE dync IS REF CURSOR;
  -- 2.定义游标
  c_emp dync;
  -- 5.定义一个行变量，接收游标所指向的记录
  v_row emp%ROWTYPE;
  -- 定义一个sql语句
  v_sql VARCHAR(100);
  v_dno emp.deptno%TYPE;
begin
  -- Test statements here
  -- 3.打开游标
  SELECT deptno INTO v_dno FROM emp WHERE empno=7369;
  v_sql :='SELECT * FROM emp WHERE deptno='||v_dno;
  OPEN c_emp FOR v_sql;
  -- 4.遍历游标
  LOOP
    FETCH c_emp INTO v_row;
    dbms_output.put_line(v_row.empno||'-'||v_row.ename);
    -- 跳出循环
    EXIT WHEN c_emp%NOTFOUND;
  END LOOP;
  -- 6.关闭游标
  IF c_emp%ISOPEN THEN
    CLOSE c_emp;
  END IF;
end;

静态游标和动态游标在定义和打开有区别，动态游标后面的select语句可以用变量来实现拼接

系统游标（隐式游标）

系统已经定义好的，在做DML操作时会触发，输出影响到的行数的信息

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;

四、存储过程:procedure

定义

存储过程是一段已经编译好，并且已经有名称的程序，可以通过名称来调用他

简单的存储过程

-- 1.在sql窗口定义
CREATE OR REPLACE PROCEDURE p1
IS 
BEGIN
  dbms_output.put_line('hello world');
END p1;
-- 2.调用，可以在test窗口使用名称直接调用，也可以在sql窗口右键p1，执行

带参存储过程:in表示入参，可以省略，out表示出参

案例一

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;

存储过程处理业务逻辑，如果有返回通过出参返回
拓展
- 集群：尽量把业务或操作分开执行，就像以前一个人做事情，现在分成了多个部门，自己做好自己的事情
- 如果把逻辑都给数据库，则数据库处理就成了瓶颈，影响系统的性能，我们会把工作交由应用服务器来处理，数据库只管数据，不管业务，给数据库松绑

五、自定义函数

定义

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

简单的自定义函数及使用

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

带参的自定义函数：in入参，out出参，和存储过程一样

软件下载提取码：qwer