Oracle第6章 存储过程和触发器

使用存储过程的优点：
(1) 过程在服务器端运行，执行速度快。 (2) 过程执行一次后代码就驻留在高速缓冲存储器， 提高了系统性能。 (3) 确保数据库的安全。 非表的授权用户除非通过过程，否则就不能访 问这些表。 (4) 自动完成需要预先执行的任务。
6.1.1
存储过程的创建和执行
用户存储过程只能定义在当前数据库中。 缺省情况下，用户创建的存储过程归登录数据库的 用户所拥有，DBA可以把许可授权给其他用户。
(2) 包体
CREATE OR REPLACE PACKAGE BODY [schema.]package_name IS∣AS pl/sql_package_body 说明： schema：指定将要创建的包所属用户方案。 pl/sql_package_body：游标、函数、过程的具体定义。
包体是一个独立于包头的数据字典对象。包体只 能在包头完成编译后才能进行编译。 包体中带有实现包头中描述的前向子程序的代码 段。 包体还可以包括具有包体全局属性的附加声明部 分，但这些附加说明对于说明部分是不可见的。
当函数不再使用时，用drop命令将其从内存中删除。 语法格式： DROP FUNCTION [schema.]function_name Schema是函数的拥有者， function_name是函数名。 例如，把函数count_num删除： DROP FUNCTION count_num;
6.2.2
Hale Waihona Puke 【例】调用函数count_num统计表XS中有多少男同学。 DECLARE man_num NUMBER; BEGIN man_num:=count_num(‘男’); dbms_output.put_line(‘表中男性学生的人数是：' ||to_char(man_num)); END;
3. 函数的释放
parameter参数
parameter：过程的参数。 Parameter_mode是参数的类型： ① IN：表示参数是输入给过程的； ② OUT：表示参数在过程中将被赋值，可以传给过 程体的外部； ③ IN OUT：表示该类型的参数既可以向过程体传 值，也可以在过程体中赋值。
【例】从XSCJ数据库的XS表中查询某人的总学分，根据总 学分写评语。 create or replace procedure update_info(v_xm in char) is xf number; begin select zxf into xf from xs where xm=v_xm; if xf>=60 then update xs set bz='三好学生' where xm=v_xm; end if; if xf<=35 then update xs set bz='学分未修满' where xm=v_xm; end if; end update_info;
2.
调用存储过程
语法格式： EXEC[UTE] procedure_name[(parameter,…n)] procedure_name为要调用的存储过程的名字， parameter为参数值。
调用EXEC update_info('李明');
存储过程也可以被另外的PL/SQL块调用，调 用的语句是： declare par1,par2; begin proc_name(par1,par2…); end; 在调用前要声明变量par1，par2
(2) 包体部分 CREATE OR REPLACE PACKAGE BODY test_package AS FUNCTION average(cnum IN char) RETURN number AS avger number; BEGIN select avg(cj) into avger from xs_kc where kch=cnum group by kch; RETURN(avger); END average;
2.函数的调用
无论在命令行还是在程序语句中，函数都可以通 过函数名称直接在表达式中调用。
语法格式： variable_name:=function_name
【例】创建一个统计数据库中xs表不同性别人数的函数。 CREATE OR REPLACE FUNCTION count_num (in_sex IN char) RETURN number AS v_num number; BEGIN if in_sex='男' then select count(*) into v_num from xs where xb='男' ; else select count(*) into v_num from xs where xb='女' ; end if; RETURN v_num; END count_num;

第6章
存储过程和触发器
教学目标
理解过程、函数和程序包，触发器的基本概 念和特点 掌握存储过程、函数的应用 掌握包的的规范和应用 掌握触发器的类型和应用
6.1 存储过程
在Oracle中，可以在数据库中定义子程序，这 种程序块称为存储过程(procedure)。 它存放在数据字典中，可以在不同用户和应用 程序之间共享，并可实现程序的优化和重用。 存储过程不仅是一个PL/SQL块，而且还是一 个数据库对象，用于在数据库中完成特定的操作或 者任务。它接收零个或者多个参数作为输入或输出， 没有返回值。
1.过程可以使用参数 2.创建过程需要相应的权限 3.查看相关错误信息：SHOW ERROR;
1.
SQL命令创建存储过程
语法格式： CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name /*定义过程名*/ [ (parameter parameter_mode date_type , …n)] /*定义参数类型及属性*/ IS | AS BEGIN sql_statement /*PL/SQL过程体，要执行的操作*/ END procedure_name
PROCEDURE student_grade(cur OUT cur_xs_kc_cj) AS BEGIN OPEN cur FOR select xs.xh,xs.xm,kc.kcm,xs_kc.cj from xs,xs_kc,kc where xs.xh=xs_kc.xh and xs_kc.kch=kc.kch; END student_grade;
function_name：用户定义的函数名。函数名必 须符合标识符的定义规则，对其所有者来说，该 名在数据库中是唯一的。 parameter：用户定义的参数。用户可以定义一 个或多个参数。 mode：参数类型。 datatyep：用户定义参数的数据类型。 Return_datatype：函数返回值的数据类型。 函数返回scalar_expression表达式的值。
6.3.1
SQL语句方式创建
【例】创建包TEST_PACKGE。 要求： 应用前面统计全体同学的平均成绩的函数 定义一个人过程，要求查询XSCJ数据库中每个同学 各门功课的成绩。
(1) 包头 CREATE OR REPLACE PACKAGE test_package AS TYPE tab_xs_kc_cj IS RECORD (itnum_xh varchar2(20), itnum_xm varchar2(20), itnum_kcm varchar2(20), itnum_cj varchar2(20) ); TYPE cur_xs_kc_cj IS REF CURSOR RETURN tab_xs_kc_cj; function average(cnum IN char) RETURN number; procedure student_grade(cur OUT cur_xs_kc_cj); END test_package;
【例】计算指定的学生已选课程的总学分。 CREATE OR REPLACE PROCEDURE totalcredit(name IN varchar2,total OUT number) AS BEGIN select sum(xf) into total from xs,xs_kc where xm=name and xs.xh=xs_kc.xh; END;
6.1.2
存储过程的编辑修改
想进行修改，则可以重新执行修改后的创建 过程的程序块，但此时必须在声明时加上“OR REPLACE”关键词。
6.1.3
存储过程的删除
当某个过程不再需要时，应将其从内存中删除， 以释放它占用的内存资源。 语法格式： DROP PROCEDURE [schema.] procedure_name; schema是包含过程的用户； procedure_name是将要删除的存储过程名称。 【例】删除XSCJ数据库中的count_sex存储过程。 DROP PROCEDURE count_sex; 也可在OEM中选择要删除的存储过程
OEM方式创建
6.3包
在Oracle中，对于逻辑上相关的类型、变量及子程 序等可以集成在一起，组成命名的PL/SQL程序块， 这种特殊的程序块称为包 包含有两个分离的部件：包说明(规范、包头)和包 体(主体)。包说明和包体都存储在数据字典中。 包仅能存储在非本地的数据库中。包所受的限制较 少，效率比较高。
(2)
单击“创建”按钮，进入过程创建界面。
【例】计算指定系总学分大于40的人数。 源PL/SQL代码： (vzym IN char,person_num OUT number) AS BEGIN select count(zxf) into person_num from xs where zym=vzym and zxf>40; END count_grade;