- 浏览: 29386 次
- 性别:
- 来自: 北京
最新评论
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;
发表评论
-
SQL语句 第11章 复合数据类型
2009-12-03 17:08 82011.1.2 在SELECT语句中使用记录1.使用%ROWT ... -
SQL语句 第10章 PLSQL编程基础
2009-12-03 17:08 112810.1 PL/SQL语言基础 10.1.1 PL/SQ ... -
SQL语句 第9章 数据描述语言(DDL)与数据控制语言(DCL)
2009-12-03 17:08 10299.1 数据控制语言(DCL)9.1.1 数据库权限3.用 ... -
SQL语句 第8章 SQL函数
2009-12-03 17:07 9518.1 数字函数8.1.1 数字函数概述1.ABS(x)例 ... -
SQL语句 第7章 DML语句与事物处理
2009-12-03 17:07 7797.1 数据操作语言(DML)7.1.1 插入数据(INS ... -
SQL语句 第6章 连接查询
2009-12-03 17:06 6316.1 内连接查询 6.1.1 简单内连接 ... -
SQL语句 第5章 子查询和集合操作
2009-12-03 02:37 11465.1 子查询 5.1.1 单行子查询 1.在WHE ... -
SQL语句 第4章 单表查询
2009-12-03 00:45 9414.1 简单查询 4.1.1 查询指定列 例4.1_ ... -
数据控制语言DCL和数据定义语言DDL
2009-12-02 15:48 1598数据控制语言DCL 系统权限: create sess ... -
SQL数据操作语言DML和事物控制语言TC
2009-12-01 16:34 1034数据操作语言DML 操作数据库时注意数据库的完整 ... -
数据库系统工程师试题
2009-11-24 18:06 13842004--2009数据库系统工程师试题及答案
相关推荐
SQL语句,存储过程,触发器
经典SQL语句 触发器 存储过程 函数等
SQL SERVER为每个触发器都创建了两个临时表INSERTED表和DELETED表,这两个表的逻辑结构与被触发器作用的表一样,用户可以读取这两个表的内容,但不能对它们进行修改,触发器执行完后,这两个表也会自动删除。...
用户定义函数是由用户根据应用程序的需要而定义的可以完成特定操作的函数。 本章将全面研究存储过程、触发器、用户定义函数等特点和使用方式。 【要点】 存储过程的特点、类型和作用 使用CREATE PROCEDURE语句创建...
通过使用这些pl/sql系统包,应用开发人员可以开发出功能更强大的数据库应用程序。本书不仅适合sql和pl/sql初学者,也适合于有经验的oracle应用开发人员。 前言 第一部分 sql和pl/sql相关工具 第1章 在windows ...
通过使用这些PL/SQL系统包,应用开发人员可以开发出功能更强大的数据库应用程序。本书不仅适合SQL和PL/SQL初学者,也适合于有经验的Oracle应用开发人员。 前言 第一部分SQL和PL/SQL相关工具 第1章在Windows平台...
7.6.7 数学运算符与集合运算符的组合应用 7.7 SQL中的表达式 7.7.1 CAST表达式的应用 7.7.2 CASE表达式的应用 7.8 小结第8章 SQL函数 8.1 SQL函数基础 8.1.1 SQL语句函数 8.1.2 常用的SQL语句函数 8.2 日期函数和...
第十九章 游标、函数 79 第二十章 存储过程 86 第二十一章 触发器 90 第二十二章 事务(数据库系统概论) 99 第二十三章 用户管理 100 第二十四章 备份 恢复 SQLLoader 104 第二十五章 数据库设计范式 106 第...
oracle知识点笔记,语法,触发器,存储过程,存储函数,流程控制,游标,异常处理,记录类型,视图,控制用户权限,高级子查询,set运算符,基本的sql_Select语句等等
通过使用这些pl/sql系统包,应用开发人员可以开发出功能更强大的数据库应用程序。本书不仅适合sql和pl/sql初学者,也适合于有经验的oracle应用开发人员。 前言 第一部分 sql和pl/sql相关工具 第1章 在windows ...
通过使用这些pl/sql系统包,应用开发人员可以开发出功能更强大的数据库应用程序。本书不仅适合sql和pl/sql初学者,也适合于有经验的oracle应用开发人员。 前言 第一部分 sql和pl/sql相关工具 第1章 在windows ...
SQL的存储过程、触发器等建立视图存储过程触发器函数(自定义函数)索引 视图 视图是从一个或几个基本表(或视图)导出的表。不同的是,它是一虚表,数据库中只存放视图的定义,而不存放视图对应的数据,这些数据...
这些是平时个人兴趣爱好收集到的易飞ERP系统触发器/函数/储存过程/视图集。适合人群:系统管理员,运维专员或鼎捷顾问等
第5部分 构造PL/SQL应用程序 第17章 过程、函数与参数 543 第18章 包 593 第19章 触发器 626 第20章 管理PL/SQL代码 685 第21章 PL/SQL的性能优化 753 第22章 I/O操作和PL/SQL 843 第6部分 高级PL/SQL主题 第23章 ...
通过使用这些pl/sql系统包,应用开发人员可以开发出功能更强大的数据库应用程序。本书不仅适合sql和pl/sql初学者,也适合于有经验的oracle应用开发人员。 前言 第一部分 sql和pl/sql相关工具 第1章 在windows ...
第12章 更新和删除数据 第13章 创建、操纵数据库和表 第14章 使用视图 第15章 使用存储过程和函数 第16章 使用游标 第17章 使用触发器 第18章 事务处理 第19章 安全性控制 ...
第12章 插入数据记录 第13章 修改数据记录 第14章 删除数据记录 第15章 权限的授予与回收 第16章 事务的控制与管理 第17章 PLSQL概述 第18章 PLSQL基础 第19章 PLSQL中的控制结构 第20章 使用游标 第21章 异常处理 ...
第十二章 存储过程的使用与管理 第十三章 存储过程的高级应用 第十四章 触发器的使用与管理 第十五章 数据库的安全 第十六章 备份、恢复、数据传输管理 第十七章 数据库设计考虑事项 第十八章 数据库设计
SQL中的while语句 14 Case-End多分支语句 14 子查询 15 视图(虚拟表)和索引 16 视图 16 索引 16 事务管理 17 数据库的安全性 18 建立权限 19 存储过程(procedure) 20 无参数存储过程 20 带参数 20 带...
3、要求使用sqlplus,pl/sql或developer建立存储过程,触发器和程序包等代码,其中要求用到游标,异常,各种语句等对表中数据进行处理; 4、使用自己熟悉的开发语言,加入界面,连接数据库; 5、报告要有操作截图...