Oracle 数据库基础学习：游标、触发器
一、游标
- lPL/SQL用游标来管理SQL的SELECT语句。游标是为处理这些语句而分配的一大块内存。
- 游标机制处理多行记录结果集；类似于指针，使程序一次可以处理其中的一行记录。
1.显式游标
- 当查询结果返回多于一行时，必须使用显式游标。
- 声明->打开->检索->关闭
1. 定义游标；
- 语法格式：
CURSOR cursor_name[(parameter1 datatype[，parameter2 datatype...])]
IS SELECT statement;
2. 打开游标
- 语法格式：
OPEN cursor_name [（parameter1[，parameter2...]）];
3. 取值到变量
- 语法格式：
FETCH cursor_name INTO variable 1 [，variable 2...];
4. 关闭游标
- 显式打开的游标需要显式关闭。游标关闭后，系统释放与该游标关联的资源，并使该游标的工作区变成无效。
- 语法格式
CLOSE cursor_name;
5. 显式游标的属性
| 游标属性 | 描 述 |
|---|---|
| 游标名%ISOPEN | 值为布尔型，如果游标已打开，取值为TRUE，否则为FALSE。 |
| 游标名%NOTFOUND | 值为布尔型，如果最近一次FETCH操作没有返回结果，则取值为TRUE，否则为FALSE。 |
| 游标名%FOUND | 值为布尔型，与%NOTFOUND属性相反，如果最近一次FETCH操作没有返回结果，则取值为FALSE，否则为 TRUE。 |
| 游标名%ROWCOUNT | 值为数字型，值是到当前为止返回的记录数。 |
5. 循环游标
- 由于FETCH语句每次只能读取一行数据，因此必须使用循环才能遍历更多的数据。
- l游标FOR循环的语法格式如下：
FOR record_name IN cursor_name LOOP
statement1;
statement2;
...
END LOOP;
- l使用子查询的FOR循环的语法格式如下：
FOR record_name IN subquery LOOP
statement1;
statement2;
...
END LOOP;
6.游标 更新和删除操作
- 语法格式：
CURSOR cursor_name IS
SELECT statement FOR UPDATE [OF column] [NOWAIT];
2. 隐式游标
l隐式游标是由PL/SQL自动创建和管理的游标，每次执行SELECT和DML语句时PL/SQL就会打开一个隐式游标，在关联的语句运行结束之后就关闭了，隐式游标又被称为SQL游标。
1. 隐式游标属性
| 游标属性 | 描 述 |
|---|---|
| SQL%ISOPEN | 值为布尔型，总为FALSE，因为关联的语句运行结束之后隐式游标就关闭了。 |
| SQL %NOTFOUND | 值为布尔型，如果没有SELECT和DML语句执行则返回NULL，如果SELECT语句返回一条或多条记录或DML语句影响一条或多条记录则返回FALSE，否则返回TRUE。 |
| SQL %FOUND | 值为布尔型，与SQL %FOUND相反。如果没有SELECT和DML语句执行则返回NULL，如果SELECT语句返回一条或多条记录或DML语句影响一条或多条记录则返回TRUE，否则返回FALSE。 |
| SQL %ROWCOUNT | 值为数字型，如果没有SELECT和DML语句执行则返回NULL，否则返回SELECT语句或DML语句影响的记录数。 |
3 .游标实例
--32：将没有参加课程编号为060151期末考试（期末成绩为空值）的学生的成绩更改为0分。
SELECT S_ID,S_FINALGRADE FROM SCORE WHERE C_NUM='060151';
SELECT * FROM SCORE;
DECLARE
V_NUM SCORE.C_NUM%TYPE :='060151';
CURSOR C_SCORE IS SELECT * FROM SCORE WHERE C_NUM=V_NUM FOR UPDATE OF S_FINALGRADE;
BEGIN
FOR SCORE_RECORD IN C_SCORE LOOP
IF SCORE_RECORD.S_FINALGRADE IS NULL THEN
UPDATE SCORE SET S_FINALGRADE=0 WHERE CURRENT OF C_SCORE;
END IF;
END LOOP;
COMMIT;
END;
--33：使用游标删除职务为“组织委员”的学生信息。
SELECT *FROM STUDENT WHERE S_DUTY='班长';
DECLARE
CURSOR C_STUDENT IS SELECT * FROM STUDENT WHERE S_DUTY='班长' FOR UPDATE;
BEGIN
FOR STU_RECORD IN C_STUDENT LOOP
DELETE FROM STUDENT WHERE CURRENT OF C_STUDENT;
END LOOP;
COMMIT;
END;
--34：创建带2个参数的游标，并使用游标从教师表中查询系号为1且研究方向为“数据挖掘”的教师的编号、姓名和研究方向。
--SELECT * FROM
set SERVEROUTPUT ON;
DECLARE
CURSOR C_TEACHER(V_DRPARTMENTID NUMBER,V_RESEARCH VARCHAR2)
IS SELECT T_ID,T_NAME,T_RESEARCH FROM TEACHER WHERE T_DEPARTMENTID=V_DRPARTMENTID AND T_RESEARCH LIKE '%'||V_RESEARCH||'%';
T_RECORD C_TEACHER%ROWTYPE;
BEGIN
OPEN C_TEACHER(1,'数据挖掘');
LOOP
FETCH C_TEACHER INTO T_RECORD;
EXIT WHEN C_TEACHER%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('教师的编号为：'||T_RECORD.T_ID||' 教师的姓名为：'||T_RECORD.T_NAME ||' 教师的研究方向为：'||T_RECORD.T_RESEARCH);
END LOOP;
END;
--36：用带参数游标的FOR循环依次输出每一个系别编号，在系别编号的下面输出该系的教师编号和姓名。
DECLARE
CURSOR C_DEPARTMENTID IS SELECT DISTINCT T_DEPARTMENTID FROM TEACHER;
CURSOR C_TEA(V_DEPARTMENTID NUMBER) IS SELECT T_ID,T_NAME FROM TEACHER WHERE T_DEPARTMENTID=V_DEPARTMENTID;
BEGIN
FOR DEPARTMENTID_RECORD IN C_DEPARTMENTID LOOP
DBMS_OUTPUT.PUT_LINE('系别号为：'||DEPARTMENTID_RECORD.T_DEPARTMENTID);
FOR TEACHER_RECORD IN C_TEA(DEPARTMENTID_RECORD.T_DEPARTMENTID) LOOP
DBMS_OUTPUT.PUT_LINE('教师编号为：'||TEACHER_RECORD.T_ID||' 教师名称号为：'||TEACHER_RECORD.T_NAME);
END LOOP;
END LOOP;
END;
--37：更新职称表，如果没有找到更新的记录，则向职称表中插入一条记录。
BEGIN
UPDATE TITLE SET TITLE_NAME ='教授' WHERE TITLE_ID=15;
IF SQL%NOTFOUND THEN
INSERT INTO TITLE VALUES(15,'教授');
COMMIT;
END IF;
END;
SELECT * FROM TITLE;
--使用SQL%ROWCOUNT可以完成与上例相同的功能。
BEGIN
UPDATE TITLE SET TITLE_NAME ='教授' WHERE TITLE_ID=15;
IF SQL%ROWCOUNT=0 THEN
INSERT INTO TITLE VALUES(15,'教授');
COMMIT;
END IF;
END;
--39：使用DUP_VAL_ON_INDEX异常处理更新重复值的问题。
BEGIN
UPDATE TEACHER SET T_IDCARD =' ' WHERE T_ID='060001';
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('省份证号重复');
END;
--42：判断教师编号为060001的教师名是否为“张三”。如果不是，触发自定义异常，如果该编号不存在，则触发系统预定义异常。
DECLARE
V_NAME TEACHER.T_NAME%TYPE;
EX_NAME EXCEPTION;
BEGIN
SELECT T_NAME INTO V_NAME FROM TEACHER WHERE T_ID='060001';
IF V_NAME <> '张三' THEN
RAISE EX_NAME;
END IF;
EXCEPTION
WHEN EX_NAME THEN
DBMS_OUTPUT.PUT_LINE('该教师不叫张三');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('编号不存在');
END;
二、触发器
触发器就是指执行由某个事件引起或激活操作的对象;
触发器是一种特殊的存储过程，也是由声明部分、语句执行部分和异常处理部分组成的PL/SQL命名块，并存储在数据库数据字典中。
1. 触发器的组成
- 触发对象：包括表、视图、模式、数据库等。
- 触发事件：引起触发器被触发的事件;比如DML语句、DDL语句、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）等。
- 触发时间：是指触发器触发的时机，触发器可以在触发事件发生之前或之后触发。
- 触发条件：由WHEN子句指定一个逻辑表达式，只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作
- 触发级别：触发器分语句级触发器和行级发器两个级别。
2. 触发器的类型
- DML触发器;
- 替代触发器;
- 系统触发器;
语法格式：
CREATE OR REPLACE TRIGGER 触发器名 触发时间 触发事件
on 表名
[for each row]
BEGIN
PL/SQL语句
END 触发器名;
注：触发事件不仅可以是一个DML操作，还可以由多个DML操作组成，当在触发器中包含多个触发事件时，为了分别针对不同的事件进行不同的处理，需要使用条件谓词（INSERTING，UPDATING及 DELETING）来判断是哪个触发事件触发了触发器。
3.触发器条件谓词的取值及其含义
| 取值 | 含义 |
|---|---|
| INSERTING | 如果触发事件是INSERT操作，则谓词的值为TRUE，否则为FALSE |
| UPDATING | 如果触发事件是UPDATE操作，则谓词的值为TRUE，否则为FALSE |
| DELETING | 如果触发事件是DELETE操作，则谓词的值为TRUE，否则为FALSE |
4.行级触发器标识符
- 在行级触发器中，如果需要引用操作之前和操作之后的数据，可以使用:old和:new标识符，分别表示该列变化前的值和该列变化后的值。
| 触发事件 | :old.列名 | :new.列名 |
|---|---|---|
| INSERT | 所有字段都是NULL | 当该语句完成时将要插入的数值 |
| UPDATE | 在更新之前该列的原始值 | 当该语句完成时将要更新的新值 |
| DELETE | 在删除行之前该列的原始值 | 所有字段都是NULL |
5. 在行级触发器中使用WHEN子句
- 语法格式：
CREATE OR REPLACE TRIGGER 触发器名 触发时间 触发事件
on 表名
[for each row]
[WHEN trigger_condition]
BEGIN
PL/SQL语句
END 触发器名;
6. 删除触发器
DROP TRIGGER 触发器名;
7. 实例
--在paper表上创建一个语句级后触发器，当向paper表执行INSERT操作时会执行触发器。
CREATE OR REPLACE TRIGGER PAPER_TRI AFTER INSERT ON PAPER
BEGIN
DBMS_OUTPUT.PUT_LINE('执行插入操作');
END PAPER_TRI;
SELECT * FROM PAPER;
INSERT INTO PAPER(PAPER_ID,PAPER_TITLE,PAPER_JOURNAL,PAPER_TIME) VALUES(12,'红楼梦','文学','2020-02');
--创建一个语句级前的触发器,要求禁止在2017年4月23日之前修改成绩
CREATE OR REPLACE TRIGGER SCORE_TRI BEFORE UPDATE OR DELETE ON SCORE
BEGIN
IF SYSDATE<'23-4月 -27' THEN
RAISE_APPLICATION_ERROR(-20001,'当前时间不可修改成绩');
END IF;
END SCORE_TRI;
SELECT * FROM SCORE;
INSERT INTO SCORE VALUES('11111','2020-2021(1)','060123',0,30,65);
UPDATE SCORE SET S_FINALGRADE=100 WHERE S_ID='0807070301';
260
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
