存储过程

第第 1212 章章存储过程存储过程 12.112.1 存储过程概述存储过程概述存储过程是一种命名 PL/SQL 程序块，它将一些相关的 SQL 语句，流程控制语句组合在一起，用于执行某些特定的操作或者任务。将经常需要执行的特定的操作写成过程，通过过程名，就可以多次调用过程，从而实现程序的模块化设计，这种方式提高了程序的效率，节省了用户的时间。存储过程具有以下特点： ●●存储过程在服务器端运行，执行速度快。 ●●存储过程增强了数据库的安全性。 ●●存储过程允许模块化程序设计。 ●●存储过程可以提高系统性能。 12.212.2 存储过程的创建和调用存储过程的创建和调用 12.2.112.2.1 创建存储过程创建存储过程 1. 1. 通过通过 PL/SQLPL/SQL 语句创建存储过程语句创建存储过程 PL/SQL 创建存储过程使用的语句是CREATE PROCEDURE。语法格式：语法格式： CREATE [OR REPLACE] PROCEDURE /*定义过程名*/ [ ( [ DEFAULT ] [, …n])] /*定义参数类型及属性*/ { IS | AS } []/*变量声明部分*/ BEGIN /*PL/SQL 过程体*/ END [][;] 说明：说明：（1）OR REPLACE：如果指定的过程已存在，则覆盖同名的存储过程。（2）过程名：定义的存储过程的名称。（3）参数名：存储过程的参数名必须符合有关标识符的规则，存储过程中的参数称为形式参数(简称形参)，可以声明一个或多个形参，调用带参数的存储过程则应提供相应的实际参数(简称实参)。（4）参数类型：存储过程的参数类型有IN、OUT 和 IN OUT 三种模式，默认的模式是IN 模式。 ●●IN：向存储过程传递参数，只能将实参的值传递给形参，在存储过程内部只能读不能写，对应 IN 模式的实参可以是常量或变量。 ●● OUT：从存储过程输出参数，存储过程结束时形参的值会赋给实参，在存储过程内部可以读或写，对应 OUT 模式的实参必须是变量。 ●● IN OUT：具有前面两种模式的特性，调用时，实参的值传递给形参，结束时，形参的值传递给实参，对应 IN OUT 模式的实参必须是变量。（5）DEFAULT：指定 IN 参数的默认值，默认值必须是常量。（6）过程体：包含在过程中的PL/SQL 语句。存储过程可以带参数，也可以不带参数。【例 12.1】创建一个不带参数的存储过程spTest，输出 Hello Oracle。 CREATE OR REPLACE PROCEDURE spTest/*创建不带参数的存储过程*/ AS BEGIN DBMS_OUTPUT.PUT_LINE( Hello Oracle ); END; 【例 12.2】创建一个带参数的存储过程spTc，查询指定学号学生的总学分。 CREATE OR REPLACE PROCEDURE spTc(p_sno IN CHAR) /*创建带参数的存储过程, p_sno 参数为 IN 模式*/ AS credit number; BEGIN SELECT tc INTO credit FROM student WHERE sno=p_sno; DBMS_OUTPUT.PUT_LINE(credit); END; 2. 2. 通过通过 SQL DeveloperSQL Developer图形界面方式创建存储过程图形界面方式创建存储过程【例 12.3】通过图形界面方式创建存储过程spTc，用于求 102 课程的平均分。（1）启动”SQL Developer”，在”连接”节点下打开数据库连接” sys_stsys”，选择并展开“过程”节点，右单击该节点，在弹出的快捷菜单中选择”创建过程”命令，出现”创建 PL/SQL 过程”对话框，如图 12.1 所示。（2）在“名称”文本框中输入存储过程的名称，这里是 spTc，单击“+”按钮添加一个参数，在“Name”栏输入参数名称 p_sno，在“Type”栏选择参数的类型 CHAR，在“Mode”栏选择参数的模式 IN （3）单击“确定”按钮，在 spTc过程的编辑框中编写 PL/SQL 语句，完成后单击“编译”按钮完成过程的创建。 12.2.212.2.2 存储过程的调用存储过程的调用存储过程的调用可采用 PL/SQL 语句，通过 CUTE（或 C）语句可以调用一个已定义的存储过程。语法格式：语法格式： [ { C | CUTE } ] [ ( [ =] | @ [,…n]) ] [;] 12.2.212.2.2 存储过程的调用存储过程的调用说明：说明：（1）可以使用CUTE（或 C）语句调用已定义的存储过程。但在PL/SQL 块中，可以直接使用过程名调用。（2）对于带参数的存储过程，有以下三种调用方式： ●●名称表示法：调用时按形参的名称和实参的名称对应调用。 ●●位置表示法：调用时按形参的排列顺序调用。 ●●混合表示法：将名称表示法和位置表示法混合使用。 1）使用 CUTE 语句调用和使用 PL/SQL 语句块调用存储过程【例 12.4】调用存储过程 spTest。（1）使用 CUTE 语句调用 CUTE spTest; 运行结果：运行结果： Hello Oracle （2）使用 PL/SQL 语句块调用 BEGIN sptest; END; 运行结果：运行结果： Hello Oracle 2）在带参数的存储过程中，使用位置表示法调用和使用名称表示法调用【例 12.5】调用带参数的存储过程sptc。（1）使用位置表示法调用带参数的存储过程 CUTE spTc( 121001 ); 该语句使用位置表示法调用带参数的存储过程 spTc，省略了”=”格式，但后面的实参顺序必须和过程定义时的形参顺序一致。运行结果：运行结果： 52 （2）使用名称表示法调用带参数的存储过程 CUTE spTc(p_sno= 121001 ); 该语句使用名称表示法调用带参数的存储过程spTc，使用了”=”格式。运行结果：运行结果： 52 12.2.312.2.3 存储过程的删除存储过程的删除语法格式：语法格式： DROP PROCEDURE [.] ; 【例 12.6】删除存储过程 spTc。 DROP PROCEDURE spTc; 12.312.3 存储过程的参数存储过程的参数 12.3.112.3.1 带输入参数存储过程的使用带输入参数存储过程的使用【例 12.7】创建一个带输入参数存储过程 spCourseMax，输出指定学号学生的所有课程中的最高分。（1）创建存储过程 CREATE OR REPLACE PROCEDURE spCourseMax (p_sno IN CHAR) /*创建存储过程 spCourseMax, 参数 p_sno 是输入参数*/ AS v_max number; BEGIN SELECT MAX(c.grade) INTO v_max FR