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; -