12.1 子程序
运行在服务器端,快;简化应用程序开发;提高数据库安全
12.1.1 过程
1.定义过程
例12.1_1
以系号为参数,输出该系的平均工资、最高工资、最低工资
CREATE OR REPLACE PROCEDURE display_teacher(v_no teachers.department_id%TYPE)
AS
v_wage teachers.wage%TYPE;
v_maxwage teachers.wage%TYPE;
v_minwage teachers.wage%TYPE;
BEGIN
SELECT AVG(wage) INTO v_wage
FROM teachers WHERE department_id = v_no;
SELECT MAX(wage) INTO v_maxwage
FROM teachers WHERE department_id = v_no;
SELECT MIN(wage) INTO v_minwage
FROM teachers WHERE department_id = v_no;
DBMS_OUTPUT.PUT_LINE
('该系平均工资为:'||v_wage);
DBMS_OUTPUT.PUT_LINE
('该系最高工资为:'||v_maxwage);
DBMS_OUTPUT.PUT_LINE
('该系最低工资为:'||v_minwage);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('该系不存在。');
END display_teacher;
2.调用过程
例12.1_2
调用过程display_teacher。
SET SERVEROUTPUT ON
CALL display_teacher(101);
3.过程的管理
例12.1_3
(1)查看过程
SELECT object_name, created, status
FROM user_objects
WHERE object_name = 'DISPLAY_TEACHER';
例12.1_4
(2)查看源程序
SELECT text FROM user_source
WHERE name = 'DISPLAY_TEACHER';
(2)删除过程
例12.1_6
DROP PROCEDURE display_teacher;
4.参数及其传递方式
(1)无参数过程
例12.1_7
CREATE OR REPLACE PROCEDURE display_systime
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('系统时间为:'||SYSDATE);
END display_systime;
SET SERVEROUTPUT ON
CALL display_systime();
EXECUTE display_systime();
(2)有参数过程
根据级别给教师增加工资
例12.1_9
CREATE OR REPLACE PROCEDURE display_edited(
v_id IN teachers.teacher_id%TYPE,
v_name OUT teachers.name%TYPE,
v_wage OUT teachers.wage%TYPE)
AS
v_title teachers.title%TYPE;
BEGIN
SELECT title INTO v_title
FROM teachers WHERE teacher_id = v_id;
CASE
WHEN v_title = '教授' THEN
UPDATE Teachers
SET wage = 1.1*wage WHERE teacher_id = v_id;
WHEN v_title = '高工' OR v_title = '副教授' THEN
UPDATE teachers
SET wage = 1.05*wage WHERE teacher_id = v_id;
ELSE
UPDATE teachers
SET wage = wage+100 WHERE teacher_id = v_id;
END CASE;
SELECT name, wage INTO v_name, v_wage
FROM teachers WHERE teacher_id = v_id;
END display_edited;
VARIABLE v_name VARCHAR2(10)
VARIABLE v_wage NUMBER
CALL display_edited(10101,:v_name, :v_wage);
PRINT v_name v_wage
例12.1_10
在departments插入一条记录,并显示前一条记录
CREATE OR REPLACE PROCEDURE app_disp(
v_id IN OUT departments.department_id%TYPE,
v_name IN OUT departments.department_name%TYPE,
v_address IN OUT departments.address%TYPE)
AS
BEGIN
INSERT INTO departments
VALUES(v_id, v_name, v_address);
v_id := v_id - 1;
SELECT department_id, department_name, address
INTO v_id, v_name, v_address
FROM departments WHERE department_id = v_id;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('插入系部信息时,系部号不能重复。');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('查询系部信息时,该系不存在。');
END app_disp;
VARIABLE v_id NUMBER
VARIABLE v_name VARCHAR2(8)
VARIABLE v_address VARCHAR2(40)
EXECUTE :v_id := 111
EXECUTE :v_name := '地球物理'
EXECUTE :v_address := 'X号教学楼'
CALL app_disp(:v_id, :v_name, :v_address);
PRINT :v_id :v_name :v_address
EXECUTE app_disp(:v_id, :v_name, :v_address);
12.1.2 函数
1.定义函数
例12.1_14
定义函数,以教师号为参数,返回该教师的月总收入
CREATE OR REPLACE FUNCTION total(v_no NUMBER)
RETURN NUMBER
AS
v_wage teachers.wage%TYPE;
v_bonus teachers.bonus%TYPE;
v_total teachers.wage%TYPE;
BEGIN
SELECT wage, bonus INTO v_wage, v_bonus
FROM teachers WHERE teacher_id = v_no;
v_total := v_wage + v_bonus;
RETURN v_total;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('该教师不存在。');
END total;
2.调用函数
例12.1_15
调用函数total,计算教师号为***的月总收入。
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('该教师月总收入为:'||total(10101));
END;
3.函数的管理
例12.1_16
查看函数的有关信息
SELECT object_name, created, status
from user_objects WHERE object_name = 'TOTAL';
例12.1_17
通过视图user_source查看函数total的源程序。
SELECT text FROM user_source WHERE name = 'TOTAL';
例12.1_19
删除函数total。
DROP FUNCTION total;
4.参数及其传递方式
(1)无参数函数
无参数调用显示系统时间
例12.1_20
CREATE OR REPLACE FUNCTION show_systime
RETURN DATE
AS
BEGIN
RETURN SYSDATE;
END show_systime;
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('系统时间为:'||show_systime);
END;
(2)有参数函数
例12.1_21
某学生某课程的成绩
CREATE OR REPLACE FUNCTION display_grade(
v_sno IN students_grade.student_id%TYPE,
v_cno IN students_grade.course_id%TYPE)
RETURN NUMBER
AS
v_score students_grade.score%TYPE;
BEGIN
SELECT score INTO v_score FROM students_grade
WHERE student_id = v_sno AND course_id = v_cno;
RETURN v_score;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('该生或该门课程不存在。');
END display_grade;
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE
('成绩为:'||display_grade(10101,10201));
END;
VARIABLE grade NUMBER
EXECUTE :grade := display_grade(10101, 10201);
PRINT :grade
例12.1_22
CREATE OR REPLACE FUNCTION show_grade(
v_sno IN students_grade.student_id%TYPE,
v_cno IN students_grade.course_id%TYPE,
v_sname OUT students.name%TYPE,
v_cname OUT courses.course_name%TYPE)
RETURN NUMBER
AS
v_score students_grade.score%TYPE;
BEGIN
SELECT name INTO v_sname
FROM students WHERE student_id = v_sno;
SELECT course_name INTO v_cname
FROM courses WHERE course_id = v_cno;
SELECT score INTO v_score
FROM students_grade
WHERE student_id = v_sno AND course_id = v_cno;
RETURN v_score;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('该生或该门课程不存在。');
END show_grade;
VARIABLE grade NUMBER
VARIABLE v_sname VARCHAR2(10)
VARIABLE v_cname VARCHAR2(30)
EXECUTE :grade := show_grade(10101, 10201, :v_sname, :v_cname);
PRINT :v_sname :v_cname
例12.1_23
CREATE OR REPLACE FUNCTION app_show(
v_id IN OUT departments.department_id%TYPE,
v_name IN OUT departments.department_name%TYPE,
v_address IN OUT departments.address%TYPE)
RETURN NUMBER
AS
BEGIN
INSERT INTO departments
VALUES(v_id, v_name, v_address);
v_id := v_id - 1;
SELECT department_id, department_name, address
INTO v_id, v_name, v_address
FROM departments WHERE department_id = v_id;
RETURN 1;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('插入系部信息时,系部号不能重复。');
RETURN 0;
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('查询系部信息时,该系不存在。');
RETURN 0;
END app_show;
VARIABLE flag NUMBER
VARIABLE v_id NUMBER
VARIABLE v_name VARCHAR2(8)
VARIABLE v_address VARCHAR2(40)
EXECUTE :v_id := 222
EXECUTE :v_name := '航空机械'
EXECUTE :v_address := 'Y号教学楼'
EXECUTE :flag := app_show(:v_id, :v_name, :v_address);
PRINT :v_id :v_name :v_address
(3)参数传递方式
例12.1_24 调用过程display_grade时,参数传递使用位置传递方式。参见例12.1_21。
例12.1_25 调用过程display_grade时,参数传递使用名字传递方式。
VARIABLE grade NUMBER
EXECUTE :grade := display_grade(v_cno=>10201,v_sno=>10101);
PRINT :grade
例12.1_26 调用过程display_grade时,参数传递使用混合传递方式。
VARIABLE grade NUMBER
EXECUTE :grade := display_grade(10101, v_cno=>10201);
PRINT :grade
12.2 包
12.2.1 定义包
1.定义包规范
例12.2_1
CREATE OR REPLACE PACKAGE jiaoxue_package IS
FUNCTION display_grade(v_sno NUMBER, v_cno NUMBER)
RETURN NUMBER;
PROCEDURE app_department
(v_id NUMBER, v_name VARCHAR2, v_address VARCHAR2);
END jiaoxue_package;
2.定义包体
例12.2_2
CREATE OR REPLACE PACKAGE BODY jiaoxue_package IS
FUNCTION display_grade(v_sno NUMBER, v_cno NUMBER)
RETURN NUMBER
AS
v_score students_grade.score%TYPE;
BEGIN
SELECT score INTO v_score FROM students_grade
WHERE student_id = v_sno AND course_id = v_cno;
RETURN v_score;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('该生或该门课程不存在。');
END display_grade;
PROCEDURE app_department
(v_id NUMBER, v_name VARCHAR2, v_address VARCHAR2)
AS
BEGIN
INSERT INTO departments VALUES(v_id, v_name, v_address);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('插入系部信息时,系部号不能重复。');
END app_department;
END jiaoxue_package;
12.2.2 包的管理
1.查看包的有关信息
例12.2_3
SELECT object_name, created, status from user_objects
WHERE object_name = 'JIAOXUE_PACKAGE';
例12.2_4
SELECT text FROM user_source
WHERE name = 'JIAOXUE_PACKAGE';
2.查看与修改包中的错误
例12.2_5
CREATE OR REPLACE PACKAGE BODY jiaoxue_package IS
FUNCTION display_garde(v_sno NUMBER, v_cno NUMBER)
RETURN NUMBER
AS
v_score students_grade.score%TYPE;
BEGIN
SELECT score INTO v_score FROM students_grade
WHERE student_id = v_sno AND course_id = v_cno;
RETURN v_score;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('该生或该门课程不存在。');
END display_grade;
PROCEDURE app_department
(v_id NUMBER, v_name VARCHAR2, v_address VARCHAR2)
AS
BEGIN
INSERT INTO departments VALUES(v_id, v_name, v_address);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('插入系部信息时,系部号不能重复。');
END app_department;
END jiaoxue_package;
3.删除包
例12.2_6
DROP PACKAGE BODY jiaoxue_package;
例12.2_7
DROP PACKAGE jiaoxue_package;
12.2.3 调用包
1.调用包中的函数
例12.2_8 调用包jiaoxue_package中的函数display_grade。
VARIABLE grade NUMBER
exec :grade :=jiaoxue_package.display_grade(10101, 10201)
PRINT :grade
2.调用包中的过程
例12.2_9 调用包jiaoxue_package中的过程app_department。
exec jiaoxue_package.app_department(333, '建筑工程', 'Z号教学楼')
12.2.3 包中子程序的重载
1.定义具有重载特征的包
例12.2_10
包规范:
CREATE OR REPLACE PACKAGE jiaoxue_package IS
FUNCTION display_grade(v_sno NUMBER, v_cno NUMBER)
RETURN NUMBER;
PROCEDURE app_department
(v_id NUMBER, v_name VARCHAR2, v_address VARCHAR2);
PROCEDURE erase_department(v_id NUMBER);
PROCEDURE erase_department(v_name VARCHAR2);
END jiaoxue_package;
包体:
CREATE OR REPLACE PACKAGE BODY jiaoxue_package IS
FUNCTION display_grade(v_sno NUMBER, v_cno NUMBER)
RETURN NUMBER
AS
v_score students_grade.score%TYPE;
BEGIN
SELECT score INTO v_score FROM students_grade
WHERE student_id = v_sno AND course_id = v_cno;
RETURN v_score;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('该生或该门课程不存在。');
END display_grade;
PROCEDURE app_department
(v_id NUMBER, v_name VARCHAR2, v_address VARCHAR2)
AS
BEGIN
INSERT INTO departments VALUES(v_id, v_name, v_address);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('插入系部信息时,系部号不能重复。');
END app_department;
PROCEDURE erase_department(v_id NUMBER)
AS
BEGIN
DELETE FROM departments WHERE department_id = v_id;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('系部号指定的系部不存在。');
END IF;
END erase_department;
PROCEDURE erase_department(v_name VARCHAR2)
AS
BEGIN
DELETE FROM departments WHERE department_name = v_name;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('系部号指定的系部不存在。');
END IF;
END erase_department;
END jiaoxue_package;
2.调用重载子程序
例12.2_11 调用重载子程序erase_department。
exec jiaoxue_package.erase_department(111)
exec jiaoxue_package.erase_department('航空机械')
12.3 触发器
12.3.1 概述
系统自动调用的特殊子程序,分三类:DML类,INSTEAD OF类,系统事件或DDL类
2.一个简单的触发器示例
例12.3_1
禁止用户在非工作时间内改变教师信息
CREATE OR REPLACE TRIGGER change_teacher
BEFORE INSERT OR UPDATE OR DELETE ON teachers
BEGIN
IF (TO_CHAR(SYSDATE, 'HH24') NOT BETWEEN '8' AND '17') OR
(TO_CHAR(SYSDATE, 'DY', 'nls date_langudage = american') IN ('SAT', 'SUN'))
THEN
RAISE_APPLICATION_ERROR(-20000, '在非工作时间不能改变教师信息。');
END IF;
END change_teacher;
3.触发器的管理
例12.3_3
查看触发器的有关信息
SELECT * from user_triggers
WHERE trigger_name = 'CHANGE_TEACHER';
例12.3_5 禁用触发器change_teacher。
ALTER TRIGGER change_teacher DISABLE/ENABLE;
例12.3_7 删除触发器change_teacher。
DROP TRIGGER change_teacher;
12.3.2 DML触发器
定义语句格式及说明(分类)
1.单一触发事件的DML触发器
例12.3_8
保存学生修改前后值及修改时间
建立触发器s_g_change
CREATE OR REPLACE TRIGGER s_g_change
AFTER UPDATE OF score ON students_grade
FOR EACH ROW
BEGIN
INSERT INTO students_grade_change
VALUES(:old.student_id,
:old.course_id, :old.score, :new.score, SYSDATE);
END s_g_change;
2.多个触发事件的DML触发器
例12.3_9
禁止用户在非工作时间内改变教师信息,根据操作情况输出信息
CREATE OR REPLACE TRIGGER change_teacher
BEFORE INSERT OR UPDATE OR DELETE ON teachers
BEGIN
IF (TO_CHAR(SYSDATE, 'HH24') NOT BETWEEN '8' AND '17')
OR (TO_CHAR(SYSDATE, 'DY', 'nls date_langudage = american') IN ('SAT', 'SUN'))
THEN
CASE
WHEN INSERTING THEN
RAISE_APPLICATION_ERROR
(-20001, '在非工作时间不能增加教师信息。');
WHEN UPDATING THEN
RAISE_APPLICATION_ERROR
(-20002, '在非工作时间不能修改教师信息。');
WHEN DELETING THEN
RAISE_APPLICATION_ERROR
(-20003, '在非工作时间不能删除教师信息。');
END CASE;
END IF;
END change_teacher;
12.3.3 INSTEAD OF触发器
例12.3_10
创建触发器,使能向教师和部门的视图插入数据
CREATE OR REPLACE TRIGGER t_d_change
INSTEAD OF INSERT ON teachers_view2
FOR EACH ROW
DECLARE
v_counter INT;
BEGIN
SELECT count(*) INTO v_counter FROM departments
WHERE department_id = :new.department_id;
IF v_counter = 0 THEN
INSERT INTO departments(department_id, department_name)
VALUES(:new.department_id, :new.department_name);
END IF;
SELECT count(*) INTO v_counter FROM teachers
WHERE teacher_id = :new.teacher_id;
IF v_counter = 0 THEN
INSERT INTO teachers(teacher_id, name, department_id)
VALUES(:new.teacher_id, :new.name, :new.department_id);
END IF;
END t_d_change;
12.3.3 系统事件触发器
例12.3_11
保存删除的对象信息
CREATE OR REPLACE TRIGGER sys_event
AFTER DROP ON SCHEMA
BEGIN
INSERT INTO event_drop VALUES
(USER, ORA_DICT_OBJ_NAME,
ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_OWNER, SYSDATE);
END sys_event;