Oracle数据库基础及应用第13章 存储过程与函数和触发器

• 有0个或多个IN \OUT\IN OUT类型的参数。 • 不能被SQL语句直接调用，只能通过EXECUT命令或者 PL/SQL/程序块内部调用。 • 已经编译好的，所以在调用时不必再次进行编译，提高 了程序的运行效率。
3
存储过程的创建
• • • • • • • • • • CREATE [OR REPLACE] PROCEDURE Procedure_name [ (argument [ { IN | OUT |IN OUT }] Type, argument [ { IN | OUT | IN OUT } ] Type ] { IS | AS } <声明部分> BEGIN <执行部分> EXCEPTION <可选的异常处理程序> END;
• 示例代码如下：
CREATE OR REPLACE FUNCTION get_dname(p_deptno dept.deptno%TYPE) RETURN VARCHAR2 IS v_dname dept.dname%TYPE; BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno; RETURN v_dname; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20003,'指定的部门不存在'); END;
在OEM中管理函数
添加函数
在OEM中管理函数
修改函数
在OEM中管理函数
删除函数
13.3 触发器（trigger）
13.3.1 13.3.2 13.3.3 13.3.4 触发器简介 DML触发器 INSTEAD OF触发器 在OEM中管理触发器
13.3.1 触发器简介
触发器也是一数据库对象，是命了名PL/SQL程序 块，被存储在数据库中。 常被用来完成由数据库的完整性约束难以完成 的复杂业务规则的约束。实现数据库数据一致性。 触发器和普通的过程、函数的执行机理不同： 函数、过程是需要用户显示调用才执行的； 触发器则是当某些事件发生时，由Oracle自动执 行。即：被某些事件触发而自动执行的。
相关概念
触发事件：引起触发器执行的事件。常是DML语句。 触发条件：由When子句指定的一个逻辑表达式。 触发对象：指触发器是创建在哪些表、视图上。 触发操作：触发器所要执行的PL/SQL程序。 触发时机：
• • • • BEFORE：在指定的事件发生之前执行触发器。 AFTER：在指定的事件发生之后执行触发器。 语句触发：以语句为单位。对于多行数据而言，只会执行一次。 行触发：以数据行为单位，符合触发条件时，对DML影响的每一行 都会执行一次。
Page

begin add_dept(60,'FINANCE','CHICAGO'); add_dept(DEPTNO=>70,dname=>'FINANCE',loc=>'CHICAGO'); add_dept(&deptno,'&dname','&loc'); --COMMIT; end;
Page 13
异常
Page
14
3. 带输出参数的存储过程
• 通过在过程中使用输出参数，可以将处理结 果返回到应用程序或调用环境。在过程中定 义输出参数时，需要OUT关键字修饰参数。 • 存储过程的输出参数可以为：标量类型、记 录类型和集合类型。 示例代码如下：
CREATE OR REPLACE PROCEDURE get_dept(p_deptno dept.deptno%TYPE, dname OUT dept.dname%TYPE,loc OUT dept.loc%TYPE) IS BEGIN SELECT dname,loc INTO dname,loc FROM dept WHERE deptno = p_deptno; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('不存在该部门！'); END;
• 参数说明
– 参数的模式
• IN（默认参数模式）表示当过程被调用时，实参值 被传递给形参；IN模式参数可以是常量或表达式。 • OUT返回调用环境时，形参值被赋给实参。OUT模式 参数只能是变量，不能是常量或表达式。 • IN OUT表示当过程被调用时，实参值被传递给形参； 返回调用环境时，形参值被赋给实参。IN OUT模式 参数只能是变量，不能是常量或表达式。
Page 10
2. 带输入参数的存储过程
• 通过使用输入参数，可以将动态数据传递到存 储过程。定义存储过程时，可以使用IN关键字 显式指定输入参数，也可省略IN关键字。 • 存储过程的输入参数可以为：标量类型、记录 类型和集合类型。示例代码如下：
CREATE OR REPLACE PROCEDURE add_dept(deptno in dept.deptno%TYPE, dname dept.dname%TYPE,loc dept.loc%TYPE) IS BEGIN INSERT INTO dept VALUES(deptno,dname,loc); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN 标量类型 dbms_output.put_line('主键冲突，重新指定主键值'); END;
【例】创建示例过程ResetPwd，此过程的功能是 将表Users中指定用户的密码重置为111111：
CREATE OR REPLACE PROCEDURE ResetPwd ( VUserId IN NUMBER) AS BEGIN UPDATE Users SET UserPwd = ' 111111' WHERE UserId = VUserId; END;
Page
29
执行函数: BEGIN dbms_output.put_line('部门名: '|| get_dname(10)); END;
Page
30
Page
31
函数的删除
函数的删除 删除函数用DROP语句 语法格式为： DROP FUNCTION <函数名> 【例】删除函数GetPwd; DROP FUNCTION get_dname；
第13章 存储过程、函数和触发器
介绍Oracle数据库程序设计中经常会用到的3个概念，即存储过 程、函数和触发器。
13.1 存储过程
• • • • 存储过程的创建 存储过程的调用 存储过程的查看及删除 在OEM中管理存储过程
存储过程的特点
• 存储子程序是被命名的PL/SQL块，以编译的形式存储在数据库 服务器中，可以在应用程序中进行调用，是PL/SQL程序模块化 的一种体现。 • PL/SQL中的存储子程序包括存储过程和（存储）函数两种。 • 存储子程序是以独立对象的形式存储在数据库服务器中，因此 是一种全局结构，与之对应的是局部子程序，即嵌套在PL/SQL 块中的局部过程和函数，其存储位置取决于其所在的父块的位 置。 • 没有返回值。
• 对于func1的调用语句如下：
BEGIN dbms_output.put_line(func1); END;
Page
27
Page
28
13.2.3 函数返回类型
• 在函数的定义过程中，可以指定函数参数：输入（IN）、输出（OUT） 和输入输出（IN OUT）参数，函数参数的使用方式与过程参数完全一 致，允许的参数类型有：标量类型、记录类型和集合类型。
• 确定过程状态
• 查看过程文本
Page
20
在OEM中管理存储过程
添加存储过程
在OEM中管理存储过程
修改存储过程
在OEM中管理存储过程
删除存储过程
13.2 函数
• • • • 13.2.1 13.2.2 13.2.3 13.2.4 函数的创建 函数的调用 函数的查看及删除 在OEM中管理函数
1. 无参存储过程
• 下述代码创建无参存储过程，打印当前登 录用户的名字和系统时间。
CREATE OR REPLACE PROCEDURE proc_1 IS BEGIN dbms_output.put_line('欢迎你 '||USER); dbms_output.put_line('现在是： '||TO_CHAR(sysdate,'YYYY-mm-DD hh:MM:ss')); END;
Page 15
Page
16
• 调用带输出参数的过程时，需要使用变量 接收输出参数的数据值。
Page
17
4. 带输入输出参数的存储过程
• 通过在存储过程中使用输入输出参数，可以在调用 存储过程时输入数据到过程，在执行结束后返回结 果数据到调用环境或应用程序。当定义输入输出参 数时，需要指定参数模式为IN OUT。 • 下述代码通过定义带输入输出参数的过程，计算并 返回所输入两个数的和与差。
注意：在当前方案： 用户必须拥有CREATE PROCEDURE系统权限。 在其他方案：用户必须拥有CREATE ANY PROCEDURE系统权限。
Page
8
Page
9
• 调用无参存储过程： • EXECUTE. exec proc_1; • 在PL/SQL块中： 直接引用存储过程名。 BEGIN proc_1; END;
存储过程的调用
过程的调用
可以使用EXECUTE命令调用过程。如 EXECUTE ResetPwd(1);---将编号为1的用户密码重置 SELECT UserName,UserPwd FROM Users;