Oracle数据库开发实用教程 刘竹林 第6章新.ppt

第6章 存储过程与函数 本章主要内容 存储过程 PL/SQL中的SQL命令 函数 过程与函数的区别 PL/SQL的调用 6.1 存储过程 存储过程不仅是一个PL/SQL块，而且还是一个数据库对象，用于在数据库中完成特定的操作或者任务。它接收零个或者多个参数作为输入或输出，没有返回值。 存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，使用时只要调用即可。 6.1.2 创建存储过程 创建存储过程的语法如下： CREATE [OR REPLACE] PROCEDURE [schema.] procedure_name [parameter_list] {AS|IS} declaration_section BEGIN executable_section [EXCEPTION exception_section] END [procedure_name] 其中： （1）procedure_name：过程名字。 （2）parameter_list：参数列表。 每个参数的语法如下： MODE datatype [(:=|default) VALUE] MODE有三种形式：IN、OUT、INOUT。 IN表示在调用过程的时候，实际参数的取值被传递给该过程，形式参数被认为是只读的，当过程结束时，实际参数的值不会改变。 OUT在调用过程时实际参数的取值都将被忽略，在过程内部形式参数只能是被赋值，而不能从中读取数据，在过程结束后形式参数的内容将被赋予实际参数。 INOUT这种模式是IN和OUT的组合。在过程内部实际参数的值会传递给形式参数，形式参数的值可读也可写，过程结束后，形式参数的值将赋予实际参数。 （3）OR REPLACE：可以写也可以不写。如果是第一次创建此过程可以不写。如果写上，代表的含义是如果以前已经有旧的相同名字的过程名，那么就覆盖旧的过程。 2．存储过程的调用 【例】在SQL*Plus中调用过程。 SQLSET SERVEROUTPUT ON; SQLCREATE OR REPLACE PROCEDURE Procdemo AS 2 v_String1 VARCHAR2(50) := Hello World!; 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(v_String1); 5 END Procdemo; 6 / 过程已创建。 SQLEXEC procdemo; Hello World! PL/SQL 过程已成功完成。 6.2 PL/SQL中的SQL命令 在PL/SQL程序块中可以使用各种SQL命令，但是使用的方法根据命令不同也各不相同。 （1）使用SELECT 命令，可以从数据库中检索数据。 但要注意以下几个方面： 1）在PL/SQL中使用SELECT语句，必须使用INTO子句把数据库检索出来的数据存储到一个局部变量（在PL/SQL定义）中。 2）查询必须并且只能返回一行。 3）可以使用完整的SELECT 语法。 （2）使用DML命令（INSERT，UPDATE，DELETE），修改数据库中的行。 （3）使用COMMIT 或ROLLBACK 命令控制事务。 （4）通过EXECUTE IMMEDIATE，执行DDL和DCL语句。 【例】删除指定的部门。 PROCEDURE del_dept (v_dept_id departments.department_id%TYPE) IS BEGIN DELETE departments WHERE department_id = v_dept_id; END; 注意： （1）DDL和DCL语句不能直接出现在PL/SQL块中。 （2）通过EXECUTE IMMEDIATE可以在PL/SQL中直接执行DDL和DCL语句。 6.3 函数 6.3.1 创建函数 函数用来执行复杂的计算，并返回计算的结果。它的格式如下： CREATE [OR REPLACE] FUNCTION [schema.] function_name [parameter_list] RETURN returning_datatype {AS|IS} declaration_section BEGIN executable_section [EXCEPTION] exception_section END [procedure_name 6.3.2 调用函数 （1）在SQL*Plus中调用函数。 【例6-12】基于一个特定的值给出税额。 SQLVAR x NUMBER SQLEXEC x := tax(10