Oracle学习7
1 PLSQL程序单元
PL/SQL程序,也叫PL/SQL程序单元,是数据库中命名的PL/SQL 块，主要有四类：
1、过程 执行特定操作
2、函数 进行复杂计算,返回计算的结果
3、包 将逻辑上相关的过程和函数组织在一起
4、触发器 事件触发，执行相应操作
程序结构：
块类型:
创建程序：
编码基本准则
1、代码应该易于维护
2、为源代码添加合适的注释
3、设置代码的大小写规则
4、为标识符和其他的对象设置命名规则
5、通过空格或缩进，提高代码的可读性
命名规范：
| 标识符 | 命名约定 | 例子 |
|---|---|---|
| 变量 | v_name | v_sal |
| 常量 | c_name | c_company_name |
| 游标 | name_cursor | emp_cursor |
| 异常 | e_name | e_too_many |
| TABLE类型 | name_table_type | amount_table_type |
| TABLE | name_table | order_total_table |
| RECORD类型 | name_record_type | emp_record_type |
| RECORD | name_record | customer_record |
2 匿名块
1、匿名块是只使用一次的PL/SQL程序块
2、匿名块没有名称，也不被存储在数据库中,它不是PL/SQL存储程序单元
3、由PL/SQL的四个基本组成部分构成
4、在SQL*PLUS中通过“/”编译并且执行
5、不能被重复使用,当下要执行的时候,需要重新编写并编译执行
匿名块语法
DECLARE
变量、常量声明;
BEGIN
pl/sql_block;
EXCEPTIONS
异常捕捉;
END;
3 Oracle中的程序存储单元
1、在Oracle中,除了单次执行的PL/SQL匿名块以外,更多的使用的是PL/SQL程序(也就是PL/SQL的子程序).
2、PL/SQL子程序就是数据库中命名的PL/SQL块,可以接受参数,也可以传出计算结果
3、PL/SQL子程序主要有两种类型
（1）. 存储过程(PROCEDUER):用来完成某些操作的任务
（2）. 函数(FUNCTION):用来作复杂的计算
3、PL/SQL子程序,是基于PL/SQL块的结构的,只是比PL/SQL块多了子程序头部的定义.使用PL/SQL子程序,使的PL/SQL程序易于维护,而且可以重复的使用
4 存储过程
存储过程：
用于在数据库中完成特定的操作或者任务
CREATE OR REPLACE PROCEDURE name
[(parameter,...)]
IS
pl/sql_block;
存储过程的基本准则：
1、在Sql*Plus 中使用CREATE OR REPLACE子句创建存储程序单元
2、在头部定义所有参数
3、IS表示PL/SQL块的开始,在IS之后,不需要再使用DECLARE开始声明部分,IS就表示了声明部分的开始。
4、在IS之后，声明在过程中使用的本地变量(包括变量和常量)
5、在声明完成后,以BEGIN开始程序的执行主体
--=============================存储过程==============================
-- 创建了一个过程之后,相当与我们在Java中创建了一个方法
-- 为了让过程得以执行,需要调用这个过程
CREATE OR REPLACE PROCEDURE CUR_EMP IS
--declare
CURSOR CUR_EMP IS
SELECT * FROM EMP;
V_REC_EMP EMP%ROWTYPE;
BEGIN
OPEN CUR_EMP;
FETCH CUR_EMP
INTO V_REC_EMP; -- 取出游标中的一条数据装入记录类型的变量中
WHILE (CUR_EMP%FOUND) LOOP
DBMS_OUTPUT.put_line(V_REC_EMP.EMPNO || ',' || V_REC_EMP.ENAME || ',' ||
V_REC_EMP.JOB || ',' || V_REC_EMP.MGR || ',' ||
V_REC_EMP.HIREDATE || ',' || V_REC_EMP.SAL || ',' ||
NVL(V_REC_EMP.COMM, 0) || ',' || V_REC_EMP.DEPTNO);
FETCH CUR_EMP
INTO V_REC_EMP; -- 取出游标中的一条数据装入记录类型的变量中
END LOOP;
dbms_output.put_line('查询数据的总条数是:' || CUR_EMP%ROWCOUNT);
CLOSE CUR_EMP;
END;
-- 调用过程
-- 在命令行中调用
SET SERVEROUTPUT ON
EXEC CUR_EMP;
-- 在匿名块中调用
BEGIN
CUR_EMP;
END;
5 存储过程-参数模式
-- 创建带有参数的存储过程
CREATE OR REPLACE PROCEDURE ADD_DEPT(P_DEPTNO NUMBER,P_DNAME VARCHAR2,P_LOC VARCHAR2) IS
BEGIN
INSERT INTO DEPT VALUES(P_DEPTNO,P_DNAME,P_LOC);
COMMIT;
END;
-- 在匿名块中调用存储过程
-- 调用有参数的过程的不同方式
--1.按参数名称调用
--2.按参数的位置进行调用
--3.按照混合方式调用
DECLARE
V_DEPTNO NUMBER(4) := 50;
V_DNAME VARCHAR2(20) := '销售部';
V_LOC VARCHAR2(10) := '北京';
BEGIN
ADD_DEPT(P_LOC => V_LOC, P_DNAME => V_DNAME ,P_DEPTNO => V_DEPTNO);
END;
DECLARE
V_DEPTNO NUMBER(4) := 50;
V_DNAME VARCHAR2(20) := '销售部';
V_LOC VARCHAR2(10) := '北京';
BEGIN
ADD_DEPT(V_DEPTNO,V_DNAME,V_LOC);
END;
DECLARE
V_DEPTNO NUMBER(4) := 50;
V_DNAME VARCHAR2(20) := '销售部';
V_LOC VARCHAR2(10) := '北京';
BEGIN
ADD_DEPT(V_DEPTNO, P_LOC => V_LOC, P_DNAME => V_DNAME );
END;
SELECT * FROM DEPT FOR UPDATE;
--作为Oracle中过程的参数,除了有数据类型之外,还有一种特殊的类型,既输入输出类型(IN ,OUT,IN OUT)
CREATE OR REPLACE PROCEDURE PARAM_TEST(P_IN IN VARCHAR2,P_OUT OUT VARCHAR2,P_IN_OUT IN OUT VARCHAR2 ) IS
BEGIN
DBMS_OUTPUT.put_line('在过程中,P_IN=' ||P_IN);
DBMS_OUTPUT.put_line('在过程中,P_OUT=' ||P_OUT);
DBMS_OUTPUT.put_line('在过程中,P_IN_OUT=' ||P_IN_OUT);
-- 作为OUT类型的参数,可以在过程中被重新赋值,并且会被返回给调用者
P_OUT := 'OUT类型的参数在过程中被重新赋值';
P_IN_OUT := 'IN OUT类型的参数在过程中被重新赋值';
END;
---------------------------------------------------------------
DECLARE
V_IN VARCHAR2(100) := 'IN类型参数的初始值';
V_OUT VARCHAR2(100) := 'OUT 类型参数的初始值';
V_IN_OUT VARCHAR2(100) := 'IN OUT类型参数的初始值';
BEGIN
PARAM_TEST(V_IN,V_OUT,V_IN_OUT);
DBMS_OUTPUT.put_line('在过程外,V_IN=' || V_IN);
DBMS_OUTPUT.put_line('在过程外,V_OUT=' || V_OUT);
DBMS_OUTPUT.put_line('在过程外,V_IN_OUT=' || V_IN_OUT);
END;
在过程中,P_IN=IN类型参数的初始值
在过程中,P_OUT=
在过程中,P_IN_OUT=IN OUT类型参数的初始值
在过程外,V_IN=IN类型参数的初始值
在过程外,V_OUT=OUT类型的参数在过程中被重新赋值
在过程外,V_IN_OUT=IN OUT类型的参数在过程中被重新赋值
CREATE OR REPLACE PROCEDURE ADD_COMM IS
CURSOR CUR_UPDATE_COMM IS
SELECT * FROM EMP FOR UPDATE;
V_COMM EMP.COMM%TYPE;
BEGIN
FOR V_REC_EMP IN CUR_UPDATE_COMM LOOP
IF (V_REC_EMP.JOB = 'CLERK') THEN
V_COMM := V_REC_EMP.SAL * 0.5;
ELSIF (V_REC_EMP.JOB = 'SALESMAN') THEN
V_COMM := V_REC_EMP.SAL * 0.6;
ELSIF (V_REC_EMP.JOB = 'PRESIDENT') THEN
V_COMM := V_REC_EMP.SAL * 0.7;
ELSIF (V_REC_EMP.JOB = 'ANALYST') THEN
V_COMM := V_REC_EMP.SAL * 0.8;
ELSE
V_COMM := V_REC_EMP.SAL * 1.0;
END IF;
-- 更新游标正在分析的这条数据
UPDATE EMP SET COMM = V_COMM WHERE CURRENT OF CUR_UPDATE_COMM;
END LOOP;
COMMIT;
END;
SELECT * FROM EMP;
UPDATE EMP SET COMM = NULL;
-- 存储过程完成的是一系列的操作,我们可以单独的调用一个过程,来完成某些操作
BEGIN
ADD_COMM;
END;
6 函数
1、函数是一种数据库对象,同样也是一个命名的PL/SQL程序块,被存储在数据库中,可以被反复的使用.在调用的时候,可以被作为表达式的一部分。
2、必须要有返回值
3、函数用来执行复杂的计算，并返回计算的结果
4、在PL/SQL块中至少包含一个有效的RETURN语句
FUNCTION name
[(parameter,...)]
RETURN datatype
IS
pl/sql_block;
1、创建一个PL/SQL函数，只返回计算的结果值
2、能使用in模式参数传入参数值,也可以使用out/in out类型的参数带出值
3、在函数的声明中，必须包括一个带有数据类型的RETURN 子句，表示返回数据的类型
4、在PL/SQL块中至少包括一个有效的RETURN语句
-- 函数:可以接受一个或多个参数,在函数中完成运算,最终返给用户一个结果(Oracle中的函数必须有返回结果)
-- 根据职位和薪金,计算佣金的值
-- 函数的参数也有输入输入类型,但是OUT类型的参数使用不方便,所以在函数中只使用IN类型的参数
CREATE OR REPLACE FUNCTION ADD_COMM(P_JOB VARCHAR2,P_SAL EMP.SAL%TYPE) RETURN NUMBER IS
V_COMM EMP.COMM%TYPE;
BEGIN
IF (P_JOB = 'CLERK') THEN
V_COMM := P_SAL * 0.5;
ELSIF (P_JOB = 'SALESMAN') THEN
V_COMM := P_SAL * 0.6;
ELSIF (P_JOB = 'PRESIDENT') THEN
V_COMM := P_SAL * 0.7;
ELSIF (P_JOB = 'ANALYST') THEN
V_COMM := P_SAL * 0.8;
ELSE
V_COMM := P_SAL * 1.0;
END IF;
RETURN V_COMM; -- 在函数中一定要有一个有效的RETURN语句
END;
-- 调用函数
-- 1.无法在命令行中调用函数
-- 2.在匿名块中调用函数
DECLARE
V_RES NUMBER(8);
V_STR VARCHAR2(30) := '房间里司法局警方对';
V_LENGTH NUMBER(3);
BEGIN
V_RES := ADD_COMM('SALESMAN',2000);
V_LENGTH := LENGTH(V_STR);
DBMS_OUTPUT.put_line('V_RES=' || V_RES);
DBMS_OUTPUT.put_line('V_LENGTH=' || V_LENGTH);
END;
-- 3.在SQL语句中使用函数
SELECT ENAME ,LENGTH(ENAME) 名字长度,JOB,SAL ,ADD_COMM(JOB,SAL) "佣金" FROM EMP
V_RES=1200
V_LENGTH=9
7 函数与过程的比较
函数的好处：
1、可以实现用简单的SQL语句不能实现的计算
2、提高查询的效率
函数和存储过程使用规则
1.在SQL语句中只能使用函数，而不是过程
2.函数中不允许DML语句,在过程中可以使用DML语句
3.函数的形参可以为为IN,(out,in out)
4.在函数中必须返回Oracle支持的数据类型,而不是PL/SQL支持的数据类型
在SQL语句中调用函数：
任何有效的SQL子句中
1、SELECT命令的选择列表
2、WHERE和HAVING条件子句
3、ORDER BY, 和GROUP BY子句
4、INSERT命令的VALUES 子句
5、UPDATE 命令的SET 子句
8 包
9 触发器
1、触发器类似于函数和过程，同样是具有声明部分、执行部分和异常处理部分的命名PL/SQL块
2、但与过程、函数不同的是，触发器是在事件发生时隐式的运行的，并且触发器不能接收参数;而过程/函数是用户显示调用的，可以接受参数
3、运行触发器的方式叫做触动（ firing），指在特定的事件发生的时候（前或后）自动运行定义好的PL/SQL程序
4、触发的事件可以是对数据库表的DML操作（INSERT、UPDATE或DELETE）或某个视图的操作
5、触发的事件也可以是系统事件，例如数据库的启动和关闭，以及一些DDL操作
6、触发器被作为触动触发器的事务的一部分，所以在触发器中不可以使用结束事务的事务控制语句
触发器主要用于下列情况：
1、安全性方面，确定用户的操作是否可以继续执行
2、产生对数据值修改的审计，将修改的信息记录下来，产生数据改动记录
3、提供更灵活的完整性校验规则，能根据复杂的规则校验数据
4、提供表数据的同步复制，使多个表的数据同步
5、事件日志记录，记录数据库的重要操作
Oracle数据库中主要有二种触发器类型：
1、 DML触发器
2、系统触发器
10 DML触发器
1、DML触发器由DML语句触发，并且语句类型决定了DML触发器的类型
2、DML触发器类型主要包括INSERT，UPDATE，DELETE三种触发器
3、操作对象：表或者视图
4、触发的时机包括：对表来说有before或after触发，对视图来说有INSTEAD OF
5、触发范围包括：行级触发或语句级触发。行级触发是在每行数据操作时都会触发执行；
6、可以设置WHEN子句，决定触发后是否执行触发器的执行部分；如果不设置WHEN字句，那么只要事件触发，就执行程序体
--------------------------触发器--------------------------
-- 语句级触发器
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON EMP
BEGIN
DBMS_OUTPUT.put_line('===============触发器工作了=====================');
-- 如果当前的时间是星期六或星期日 ,或时间不在8:00~18:00之间
IF (TO_CHAR(SYSDATE, 'DY') IN ('星期六', '星期日') OR
(TO_CHAR(SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00')) THEN
-- 提升一条用户自定义的错误信息,通过这个错误信息,中断用户的操作
RAISE_APPLICATION_ERROR(-20500, '你只能在工作时间对表进行操作');
END IF;
END;
-- 向EMP表插入一条数据,会自动激活触发器的工作
INSERT INTO EMP VALUES (8001,'张1','销售员',7902,'12-9月-1987',1500,200,30);
SELECT * FROM EMP;
DELETE FROM DEPT WHERE DEPTNO = 10;
---------------------------------------------------------------
-- 在插入或更新或删除员工表之前,这个触发器自动执行
CREATE OR REPLACE TRIGGER secure_emp_2
BEFORE INSERT OR UPDATE OR DELETE ON EMP
BEGIN
--如果当前时间是周六或周日 或者时间不在8:00-18:00之间
IF (TO_CHAR (SYSDATE,'DY') IN ('星期六','星期日')) OR (TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '18') THEN
IF DELETING THEN
RAISE_APPLICATION_ERROR (-20501,'你只能在工作时间删除员工表的数据');
ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR (-20502,'你只能在工作时间插入员工表的数据.');
ELSIF UPDATING ('SAL') THEN
RAISE_APPLICATION_ERROR (-20503,'你只能在工作时间更新员工表的数据');
ELSE
RAISE_APPLICATION_ERROR (-20504,'你只能在工作事件操作员工表的数据.');
END IF;
END IF;
--COMMIT;
END;
INSERT INTO EMP VALUES (8001,'张1','销售员',7902,'12-9月-1987',1500,200,30);
DELETE FROM EMP WHERE EMPNO = 8000;
UPDATE EMP SET JOB = 'MANAGER',SAL= 3000 WHERE EMPNO = 8000;
SELECT * FROM EMP FOR UPDATE
-- 行级触发器
CREATE OR REPLACE TRIGGER restrict_salary
BEFORE INSERT OR UPDATE OF SAL ON EMP
FOR EACH ROW
BEGIN
-- 如果新插入的工作不是'AD_PRES', 'AD_VP',并且新的工资 > 15000
IF NOT (:NEW.JOB IN ('AD_PRES', 'AD_VP')) AND :NEW.SAL > 15000 THEN
RAISE_APPLICATION_ERROR (-20202,'员工 '|| :NEW.JOB ||' 不能赚到' || :NEW.SAL ||'这么多薪水');
END IF;
END;
--INSERT INTO EMP VALUES (8001,'张1','销售员',7902,'12-9月-1987',16000,200,30);
UPDATE EMP SET SAL = 20000 WHERE EMPNO = 7369;
-- 写一个触发器,不允许降低员工的工资
CREATE OR REPLACE TRIGGER restrict_salary
BEFORE UPDATE OF SAL ON EMP
FOR EACH ROW
BEGIN
-- 新的工资小于老的工资
IF ( :NEW.SAL < :OLD.SAL) THEN
RAISE_APPLICATION_ERROR (-20202,'黑心的资本家,你不能降低员工的工资');
END IF;
END;
UPDATE EMP SET SAL = 2000 WHERE EMPNO = 7369;
SELECT * FROM EMP FOR UPDATE
INSERT INTO EMP VALUES (8001,'张1','销售员',7902,'12-9月-1987',1500,200,60);
SELECT COUNT(*) FROM DEPT WHERE DEPTNO = 10;
-- 创建触发器,解决外键约束问题
-- 在插入员工表记录之前,首先判断新插入的部门编号是否存在;
-- 如果新的部门编号不存在,则首先向部门表中插入该部门
CREATE OR REPLACE TRIGGER restrict_salary
BEFORE INSERT ON EMP
FOR EACH ROW
DECLARE -- 在触发器中要向声明变量,必须要加入DECLARE;
V_COUNT NUMBER(4);
V_DNAME VARCHAR2(10);
BEGIN
SELECT COUNT(*) INTO V_COUNT FROM DEPT WHERE DEPTNO = :NEW.DEPTNO;
IF (V_COUNT < 1) THEN
V_DNAME := '部门-'||:NEW.DEPTNO;
INSERT INTO DEPT VALUES (:NEW.DEPTNO,V_DNAME,'北京');
END IF;
END;
SELECT * FROM DEPT;
SELECT * FROM EMP;
INSERT INTO EMP VALUES (8002,'张1','销售员',7902,'12-9月-1987',1500,200,60);
-- 通过触发器,来实现级联删除的操作
-- 在删除部门的时候,如果该部门有员工,把该部门的所有员工也删除掉
CREATE OR REPLACE TRIGGER DELETE_DEPT
BEFORE DELETE ON DEPT
FOR EACH ROW
BEGIN
--删除部门之前,首先删除员工表中的该部门的所属员工
DELETE FROM EMP WHERE DEPTNO = :OLD.DEPTNO;
END;
DELETE FROM DEPT WHERE DEPTNO = 10;
DELETE FROM EMP WHERE DEPTNO = 50;
-- 创建视图
CREATE OR REPLACE VIEW VIEW_EMPINFO
AS
SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL,E.COMM,E.DEPTNO,D.DNAME,D.LOC
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO;
-- 从视图中查询数据
SELECT * FROM VIEW_EMPINFO;
SELECT COUNT(*) FROM DEPT WHERE DEPTNO = 70;
-- 创建触发器,把对视图执行的INSERT操作,替换成对表的操作
CREATE OR REPLACE TRIGGER INSERT_VIEW
INSTEAD OF INSERT ON VIEW_EMPINFO
FOR EACH ROW
DECLARE
V_COUNT NUMBER(1);
BEGIN
-- 判断向视图插入的部门是否存在,如果该部门不存在,则向部门表中插入新数据
-- 如果该部门存在,则更新部门表的部门名称和位置
SELECT COUNT(*) INTO V_COUNT FROM DEPT WHERE DEPTNO = :NEW.DEPTNO;
IF (V_COUNT < 1) THEN
-- 把一部分数据插入到DEPT表中
INSERT INTO DEPT VALUES (:NEW.DEPTNO,:NEW.DNAME,:NEW.LOC);
ELSE
UPDATE DEPT SET DNAME=:NEW.DNAME,LOC=:NEW.LOC WHERE DEPTNO = :NEW.DEPTNO;
END IF;
-- 把一部分数据插入到EMP表中
INSERT INTO EMP VALUES (:NEW.EMPNO,:NEW.ENAME,:NEW.JOB,:NEW.MGR,:NEW.HIREDATE,:NEW.SAL,:NEW.COMM,:NEW.DEPTNO);
END;
SELECT * FROM DEPT;
SELECT * FROM EMP;
INSERT INTO VIEW_EMPINFO VALUES (8001,'张1','工程师',7902,'12-9月-1987',3000,1000,10,'开发部','北京');
12 管理触发器
启用或者禁用某个触发器
ALTER TRIGGER trigger_name DISABLE | ENABLE
启用或者禁用某个对象上的所有触发器
ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS
重编译触发器
ALTER TRIGGER trigger_name COMPILE
数据库系统触发器
用户触发事件:
CREATE, ALTER,或者DROP命令
登录或者退出数据库连接
系统触发事件:
启动、关闭数据库
特殊错误发生
13 系统触发器
-- 系统触发器
-- 创建一个日志表,记录用户操作的时间
DROP TABLE log_trig_table;
CREATE TABLE log_trig_table (
user_id VARCHAR2(20),
log_date DATE,
action VARCHAR2(20)
);
SELECT * FROM log_trig_table;
-- 用户登录之后,自动记录日志
CREATE OR REPLACE TRIGGER logon_trig
AFTER LOGON ON SCHEMA
BEGIN
INSERT INTO log_trig_table(user_id, log_date, action)
VALUES (USER, SYSDATE, 'Logging on');
END;
-- 用户退出之前,自动记录日志
CREATE OR REPLACE TRIGGER logoff_trig
BEFORE LOGOFF ON SCHEMA
BEGIN
INSERT INTO log_trig_table(user_id, log_date, action)
VALUES (USER, SYSDATE, 'Logging off');
END;
1、根据需求划分功能，选择合适的存储程序单元完成相应功能
过程： 执行某种任务
函数： 进行复杂计算
触发器：事件触发
2、在程序中需要取出多行数据进行处理时，使用游标完成，如果有多个游标具有相类似的功能，可以采用带参数的游标
3、在程序代码中加入注释
4、在程序中加入异常处理部分，捕捉并解决常见的异常，对非常见异常可以采用记录错误日志的方法
5、通过DBMS_OUTPUT包调试PL/SQL程序
6、将具有逻辑相关功能的过程、函数、变量组织在一起构建成包
扫一扫
5353
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
