第12章 存储过程

第12章存储过程

12.1 存储过程概述

存储过程是一种命名PL/SQL程序块，它将一些相关的SQL语句，流程控制语句组合在一起，用于执行某些特定的操作或者任务。将经常需要执行的特定的操作写成过程，通过过程名，就可以多次调用过程，从而实现程序的模块化设计，这种方式提高了程序的效率，节省了用户的时间。

存储过程具有以下特点：

●存储过程在服务器端运行，执行速度快。

●存储过程增强了数据库的安全性。

●存储过程允许模块化程序设计。

●存储过程可以提高系统性能。

12.2 存储过程的创建和调用

12.2.1 创建存储过程

1. 通过PL/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. 通过SQL 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.2 存储过程的调用

存储过程的调用可采用PL/SQL语句，通过EXECUTE（或EXEC）语句可以调用一个已定义的存储过程。

语法格式：

[ { EXEC | EXECUTE } ] <过程名>

[ ( [<参数名> =>] <实参> | @<实参变量> [,…n]) ] [;]

12.2.2 存储过程的调用

说明：

（1）可以使用EXECUTE（或EXEC）语句调用已定义的存储过程。但在PL/SQL块中，可以直接使用过程名调用。

（2）对于带参数的存储过程，有以下三种调用方式：

●名称表示法：调用时按形参的名称和实参的名称对应调用。

●位置表示法：调用时按形参的排列顺序调用。

●混合表示法：将名称表示法和位置表示法混合使用。

1）使用EXECUTE语句调用和使用PL/SQL语句块调用存储过程

【例12.4】调用存储过程spTest。

（1）使用EXECUTE语句调用

EXECUTE spTest;

运行结果：

Hello Oracle

（2）使用PL/SQL语句块调用

BEGIN

sptest;

END;

运行结果：

Hello Oracle

2）在带参数的存储过程中，使用位置表示法调用和使用名称表示法调用

【例12.5】调用带参数的存储过程sptc。

（1）使用位置表示法调用带参数的存储过程

EXECUTE spTc('121001');

该语句使用位置表示法调用带参数的存储过程spTc，省略了”<参数名>=>”格式，但后面的实参顺序必须和过程定义时的形参顺序一致。

运行结果：

52

（2）使用名称表示法调用带参数的存储过程

EXECUTE spTc(p_sno=>'121001');

该语句使用名称表示法调用带参数的存储过程spTc，使用了”<参数名>=><实参>”格式。

运行结果：

52

12.2.3 存储过程的删除

语法格式：

DROP PROCEDURE [<用户方案名>.] <过程名>;

【例12.6】删除存储过程spTc。

DROP PROCEDURE spTc;

12.3 存储过程的参数

12.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

FROM student a, course b, score c

WHERE a.sno=c.sno AND https://www.360docs.net/doc/1111641530.html,o=https://www.360docs.net/doc/1111641530.html,o AND a.sno=p_sno

GROUP BY a.sno;

DBMS_OUTPUT.PUT_LINE(p_sno||'学生的最高分是'||v_max);

END;

12.3.1 带输入参数存储过程的使用

（2）调用存储过程

EXECUTE spCourseMax ('121001');

在调用存储过程时，采用按位置传递参数，将实参值’121001’传递给输入参数p_sno并输出该学号学生的所有课程中的最高分。

运行结果：

121001学生的最高分是94

【例12.8】设st2表结构已创建，含有4列stno、stname、stage、stsex，创建一个带输入参数存储过程spInsert，为输入参数设置默认值，在st2表中添加学号1001~1008。

（1）创建存储过程

CREATE OR REPLACE PROCEDURE spInsert(p_low IN INT:=1001,p_high IN INT:=1008)

/*创建存储过程spInsert, 输入参数p_low设置默认值1001, 输入参数p_ high设置默认值1008*/

AS

v_n int;

BEGIN

v_n:=p_low;

WHILE v_n<=p_high

LOOP

INSERT INTO st2(stno) VALUES(v_n);

v_n:=v_n+1;

END LOOP;

COMMIT;

END;

（2）调用存储过程

EXECUTE spInsert;

在调用存储过程时未指定实参值，自动用输入参数p_low、p_ high对应的默认值代替，并在st2表中添加学号1001~1008。

使用SELECT语句进行测试：

SELECT *

FROM st2;

运行结果：

STNO STNAME STAGE STSEX

----------------------------------------------

1001

1002

1003

1004

1005

1006

1007

1008

12.3.2 带输出参数存储过程的使用

【例12.9】创建一个带输出参数的存储过程spNumber，查找指定专业的学生人数。

（1）创建存储过程

CREATE OR REPLACE PROCEDURE spNumber(p_speciality IN char, p_num OUT number)

/*创建存储过程spNumber, 参数p_speciality是输入参数, 参数p_num是输出参数*/

AS

BEGIN

SELECT COUNT(speciality) INTO p_num

FROM student

WHERE speciality=p_speciality;

END;

（2）调用存储过程

DECLARE

v_num number;

BEGIN

spnumber('计算机', v_num);

DBMS_OUTPUT.PUT_LINE('计算机专业的学生人数是：'||v_num);

END;

在调用存储过程时，将实参值'计算机'传递给输入参数p_speciality；在过程体中，使用SELECT-INTO语句将查询结果存入输出参数p_num；结束时，将输出参数p_num的值传递给实参v_num并输出计算机专业的学生人数。

运行结果：

计算机专业的学生人数是：3

12.3.3 带输入输出参数存储过程的使用

【例12.10】创建一个存储过程spSwap，交换两个变量的值。

（1）创建存储过程

CREATE OR REPLACE PROCEDURE spSwap(p_t1 IN OUT NUMBER, p_t2 IN OUT NUMBER)

/*创建存储过程spSwap, 参数p_t1和p_t2都是输入输出参数*/

AS

v_temp number;

BEGIN

v_temp:=p_t1;

p_t1:=p_t2;

p_t2:=v_temp;

END;

（2）调用存储过程

DECLARE

v_1 number:=70;

v_2 number:=90;

BEGIN

spSwap(v_1,v_2);

DBMS_OUTPUT.PUT_LINE('v_1='||v_1);

DBMS_OUTPUT.PUT_LINE('v_2='||v_2);

END;

在调用存储过程时，将实参的值传递给输入输出参数p_t1和p_t2。在过程体中，

p_t1的值和p_t2的值进行了交换。结束时，已交换值的输入输出参数p_t1和p_t2，分别将它们的值传递给实参，完成两个变量(实参)的值的交换。

运行结果：

v_1=90

v_2=70

12.4 应用举例

【例12.11】创建一个存储过程spAvgGrade，输入学生姓名后，将查询出的平均分存人输出参数内。

（1）创建存储过程

题目分析：

将学生姓名设置为输入参数、平均分设置为输出参数，在过程体中设置SELECT-INTO语句。编写程序：

CREATE OR REPLACE PROCEDURE spAvgGrade (p_sname IN CHAR, p_avg OUT NUMBER)

/*创建存储过程spAvgGrade, 参数p_sname是输入参数, 参数p_avg是输出参数*/

AS

BEGIN

SELECT AVG(grade) INTO p_avg

FROM student a, score b

WHERE a.sno=b.sno AND a.sname=p_sname;

END;

程序分析：

姓名设置为输入参数p_sname、平均分设置为输出参数p_avg。

在过程体中通过SELECT-INTO语句，将查询结果存入输出参数p_avg。

（2）调用存储过程

DECLARE

v_avg number;

BEGIN

spAvgGrade ('徐良成', v_avg);

DBMS_OUTPUT.PUT_LINE('徐良成的平均分是：'||v_avg);

END;

调用存储过程时，将实参值’徐良成’传递给输入参数p_sname；在过程体中，通过SELECT-INTO语句将查询结果存入输出参数p_avg；调用结束时，将输出参数p_avg的值传递给实参v_avg并输出徐良成的平均分。

运行结果：

徐良成的平均分是：86.33333333333333333333333333333333333333

【例12.12】创建一个存储过程spNumberAvg，输入学号后，将该生所选课程数和平均分存人输出参数内。

（1）创建存储过程

题目分析：

将学号设置为输入参数、所选课程数和平均分分别设置为输出参数，在过程体中设置两个SELECT-INTO语句。

编写程序：

CREATE OR REPLACE PROCEDURE spNumberAvg(p_sno IN CHAR, p_num OUT NUMBER, p_avg OUT NUMBER)

/*创建存储过程spNumberAvg, 参数p_sno是输入参数, 参数p_num和p_avg是输出参数*/

AS

BEGIN

SELECT COUNT(cno) INTO p_num

FROM score

WHERE sno =p_sno;

SELECT AVG(grade) INTO p_avg

FROM score

WHERE sno =p_sno;

END;

程序分析：

学号设置为输入参数p_sno、所选课程数和平均分分别设置为输出参数p_num和p_avg。在过程体中通过第一个SELECT-INTO语句，将查询结果存入输出参数p_num，通过第二个SELECT-INTO语句，将查询结果存入输出参数p_avg。

（2）调用存储过程

DECLARE

v_num number;

v_avg number;

BEGIN

spNumberAvg('121002', v_num, v_avg);

DBMS_OUTPUT.PUT_LINE('学号121002的学生的选课数是：'||v_num||', 平均分是：'||v_avg);

END;

调用存储过程时，将实参’121002’传递给输入参数p_sno；在过程体中，使用两条SELECT-INTO 语句分别将查询结果存入输出参数p_num和p_avg；调用结束时，将p_num和p_avg的值分别传递给实参v_num和v_avg并输出结果。

运行结果：

学号121002的学生的选课数是：3, 平均分是：86.66666666666666666666666666666666666667

【例12.13】创建一个存储过程spNumMax，输入学号后，将该生姓名、最高分存人输出参数内。

（1）创建存储过程

题目分析：

将学号设置为输入参数、姓名和最高分分别设置为输出参数，在过程体中设置两个SELECT-INTO语句。

编写程序：

CREATE OR REPLACE PROCEDURE spNumberMax(p_sno IN student.sno%TYPE, p_sname OUT student.sname%TYPE, p_max OUT NUMBER)

/*创建存储过程spNumberMax, 参数p_sno是输入参数, 参数p_sname和p_max是输出参数*/

AS

BEGIN

SELECT sname INTO p_sname

FROM student

WHERE sno=p_sno;

SELECT MAX(grade) INTO p_max

FROM student a, score b

WHERE a.sno=b.sno AND a.sno=p_sno;

END;

程序分析：

学号设置为输入参数p_sno、姓名和最高分分别设置为输出参数p_sname和p_max。

在过程体中通过第一个SELECT-INTO语句，将查询结果存入输出参数p_sname，通过第二个SELECT-INTO语句，将查询结果存入输出参数p_max。

（2）调用存储过程

DECLARE

v_sname student.sname%TYPE;

v_max number;

BEGIN

spNumberMax('121002', v_sname, v_max);

DBMS_OUTPUT.PUT_LINE('学号124001的学生姓名是：'||v_sname||' 最高分是：'||v_max);

END;

调用存储过程时，将实参’121002’传递给输入参数p_sno；在过程体中，使用两条SELECT-INTO 语句分别将查询结果存入输出参数p_sname和p_max；调用结束时，将输出参数p_sname 和p_max的值分别传递给实参v_sname和v_max并输出结果

运行结果：

学号124001的学生姓名是：李佳慧最高分是：88