PLSQL_异常处理、触发器

布满荆棘的人生 2021-09-30 00:20 311阅读 0赞

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 004 PL/SQL 异常处理、触发器 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/

/\*\*  
异常处理  
异常有俩种类型  
预定义异常 - 当PL/SQL 程序违反Oracle规则或超越系统限制时隐式引发  
用户定义异常 - 用户可以在PL/SQL块的声明部分定义异常,自定义的异常通过RAISE语句显式引发  
命名的系统异常  
NO\_DATA\_FOUND  
TOO\_MANY\_ROWS  
VALUE\_ERROR  
CURSOR\_ALREADY\_OPEN  
COLLECTION\_IS\_NULL  
ACCESS\_INTO\_NULL  
TIMEOUT\_ON\_RESOURCE  
ZERO\_DIVIDE  
...  
  
RAISE APPLICATION\_ERROR 过程  
用于创建用户自定义的错误信息  
可以在执行部分和异常处理部分使用  
错误编号必须介于 -20000 和 - 20999 之间  
错误消息长度最多 2048个字节  
引发应用程序错误的语法:  
RASIE\_APPLICATION\_ERROR(-20001,'身份证信息与出生日期不符');  
  
注意RAISE 异常会使程序停止,不会进行后续任何操作  
\*/

/\*\*  
\-- 实验准备  
CREATE TABLE STUDENT (  
SID CHAR(18) PRIMARY KEY,  
SNAME CHAR(10),  
SBIRTH DATE  
);  
\*/

CREATE OR REPLACE PROCEDURE PROC4(P1 CHAR, P2 CHAR, P3 DATE)  
IS  
\-- 自定义异常  
INVALID\_DATE EXCEPTION;  
V\_A CHAR(8);  
V\_B CHAR(8);  
BEGIN  
INSERT INTO STUDENT VALUES (P1, P2, P3);  
SELECT SUBSTR(P1, 7, 8) INTO V\_A FROM STUDENT S WHERE S.SID = P1;  
SELECT TO\_CHAR(P3,'yyyymmdd') INTO V\_B FROM STUDENT S WHERE S.SID = P1;  
IF V\_A = V\_B THEN  
COMMIT;  
DBMS\_OUTPUT.PUT\_LINE('插入成功');  
ELSE  
ROLLBACK;  
\--升起异常  
RAISE INVALID\_DATE;  
END IF;  
EXCEPTION  
WHEN INVALID\_DATE THEN  
\--DBMS\_OUTPUT.PUT\_LINE('身份证信息与出生日期不符！');  
RAISE\_APPLICATION\_ERROR(-20001,'身份证信息与出生日期不符');  
END;

DECLARE  
V\_SID CHAR(18);  
V\_SNAME CHAR(10);  
V\_SBIRTH DATE;  
BEGIN  
V\_SID := '220104196708274110';  
V\_SNAME := 'BHZ';  
V\_SBIRTH := TO\_DATE('19770827','yyyymmdd');  
PROC4(V\_SID, V\_SNAME, V\_SBIRTH);  
END;

/\*\*  
触发器  
触发器由三部分组成:  
1 触发器语句(事件)  
定义集合触发器的DML事件和DDL事件  
2 触发器限制  
执行触发器的条件 该条件必须为真才能激活触发器  
3 触发器操作(主体)  
包含一些SQL语句和代码,他们在发出了触发器语句且触发限制的值为真时运行  
CREATE \[OR REPLACE\] TRIGGER trigger\_name  
AFTER | BEFORE | INSTEAD OF  
\[INSERT\] \[ \[OR\] UPDATE \[OF column\_list\] \]  
\[\[OR\] DELETE\]  
ON table\_or\_view\_name  
\[REFERENCING \{ OLD \[AS\] OLD/NEW \[AS\] new\}\]  
\[FOR EACH ROW\]  
\[WHEN (condition)\]  
PL/SQL\_BLOCK  
  
1 DDL 触发器：在模式中执行 DDL语句时执行  
2 数据库触发器：在发生打开、关闭、登陆和退出数据库等系统事件时执行  
3 DML 触发器：正在对表或视图执行DML语句时执行  
语句级触发器：无论受影响的行数是多少，都只执行一次  
行级触发器：对DML语句修改的每个执行一次  
INSTEAD OF 触发器：用于用户不能直接使用DML语句修改的视图  
4 注意: 触发器里面不能使用(TCL、DDL语句)：  
rollback、commit、savepoint、create、drop、alter等..  
  
\*/

/\*\*  
\-- 001 当用户插入、更新EMP1表中的记录时,就输入一个提示"TRIG1 BEGIN TO WORK"  
实验准备  
CREATE TABLE EMP1 AS SELECT \* FROM EMP WHERE DEPTNO = 10;  
SELECT \* FROM EMP1;  
\*/

CREATE OR REPLACE TRIGGER TRIG1  
AFTER INSERT OR UPDATE ON EMP1  
BEGIN  
DBMS\_OUTPUT.PUT\_LINE('TRIG1 RESPONDED!');  
END;

\-- 新增  
INSERT INTO EMP1 SELECT \* FROM EMP E WHERE E.EMPNO = '7788';  
\-- 更新  
UPDATE EMP1 SET SAL = 3000 WHERE EMPNO = '7782';

/\*\*  
\-- 002 行级触发器  
\*/  
CREATE OR REPLACE TRIGGER TRIG2  
AFTER UPDATE ON EMP1  
FOR EACH ROW  
BEGIN  
DBMS\_OUTPUT.PUT\_LINE('TRIG2 ROW UPDATE!');  
END;

UPDATE EMP1 SET SAL = 4000;  
ROLLBACK;

/\*\*  
\-- 003 :OLD.FIELD :NEW.FIELD 使用,级联触发更新

\--准备实验  
create table PRODUCTS  
(  
product\_id NUMBER(38),  
product\_name VARCHAR2(60),  
category VARCHAR2(60),  
CONSTRAINT PK\_PRODUCTS PRIMARY KEY (product\_id)  
);

create table TLOG  
(  
CHANGE\_ID VARCHAR2(32) NOT NULL,  
DATA\_CODE VARCHAR2(40) NOT NULL,  
CHANGE\_TYPE VARCHAR2(10) NOT NULL,  
CHANGE\_BY VARCHAR2(80) NOT NULL,  
CHANGE\_TIME TIMESTAMP NOT NULL,  
CONSTRAINT PK\_MST\_CHANGE PRIMARY KEY (CHANGE\_ID)  
);

create table TCOL  
(  
CHANGE\_ID VARCHAR2(32) not null,  
COLNAME VARCHAR2(40),  
BEFOREVALUE VARCHAR2(256),  
AFTERVALUE VARCHAR2(256),  
CONSTRAINT PK\_TCOL PRIMARY KEY (CHANGE\_ID, COLNAME)  
);  
ALTER TABLE TCOL  
ADD CONSTRAINT FK\_TCOL FOREIGN KEY (CHANGE\_ID)  
REFERENCES TLOG (CHANGE\_ID);

insert into PRODUCTS (product\_id, product\_name, category) values (1501, 'VIVITAR 35MM', 'ELECTRNCS');  
insert into PRODUCTS (product\_id, product\_name, category) values (1502, 'OLYMPUS IS50', 'ELECTRNCS');  
insert into PRODUCTS (product\_id, product\_name, category) values (1600, 'PLAY GYM', 'TOYS');  
insert into PRODUCTS (product\_id, product\_name, category) values (1601, 'LAMAZE', 'TOYS');  
insert into PRODUCTS (product\_id, product\_name, category) values (1717, 'HARRY POTTER', 'DVD');  
insert into PRODUCTS (product\_id, product\_name, category) values (1666, 'HARRY POTTER', 'DVD');  
insert into PRODUCTS (product\_id, product\_name, category) values (1718, 'AAA', 'TTT');  
commit;

\*/

CREATE OR REPLACE TRIGGER t\_case\_change  
AFTER INSERT OR UPDATE  
ON PRODUCTS  
FOR EACH ROW  
DECLARE  
v\_uid TLOG. CHANGE\_ID%TYPE;  
v\_type TLOG.CHANGE\_TYPE%TYPE;  
BEGIN  
SELECT sys\_guid() INTO v\_uid FROM DUAL;  
IF INSERTING THEN  
v\_type := 'INSERT';  
INSERT INTO TLOG VALUES (v\_uid, 'PRODUCTS', v\_type, :NEW.PRODUCT\_ID, SYSTIMESTAMP);  
INSERT INTO TCOL VALUES (v\_uid, 'PRODUCT\_NAME', :NEW.PRODUCT\_NAME, :NEW.PRODUCT\_NAME);  
INSERT INTO TCOL VALUES (v\_uid, 'CATEGORY', :NEW.CATEGORY, :NEW.CATEGORY);  
END IF;  
IF UPDATING THEN  
v\_type := 'UPDATE';  
INSERT INTO TLOG VALUES (v\_uid, 'PRODUCTS', v\_type, :OLD.PRODUCT\_ID, SYSTIMESTAMP);  
IF UPDATING('PRODUCT\_NAME') THEN  
INSERT INTO TCOL VALUES (v\_uid, 'PRODUCT\_NAME', :OLD.PRODUCT\_NAME, :NEW.PRODUCT\_NAME);  
END IF;  
IF UPDATING('CATEGORY') THEN  
INSERT INTO TCOL VALUES (v\_uid, 'CATEGORY', :OLD.CATEGORY, :NEW.CATEGORY);  
END IF;  
END IF;  
END;

/\*\*  
\--测试数据  
insert into products values(2001, '新增数据2001', '新增版块2001');  
insert into products values(2002, '新增数据2002', '新增版块2002');  
update products set PRODUCT\_NAME = '更新PRODUCT\_NAME字段-1' where PRODUCT\_ID = '1717' ;  
update products set PRODUCT\_NAME = '更新PRODUCT\_NAME字段-2', CATEGORY = '更新CATEGORY字段-2' where PRODUCT\_ID = '1718';  
\--查询语句  
select \* from products  
select \* from TLOG  
select \* from TCOL

truncate table PRODUCTS  
truncate table TLOG  
truncate table TCOL  
\*/

转载于:https://www.cnblogs.com/bymcyc/p/8888836.html