Oracle数据库教程（第3版 微课视频版） 课件 第9章 存储过程和函数

1第9章存储过程和函数9.1存储过程概述9.2存储过程的创建、调用和删除9.3存储过程的参数9.4函数概述9.5函数的创建、调用和删除Oracle数据库教程(第3版•微课视频版)29.1存储过程概述存储过程(StoredProcedure)是一种命名PL/SQL程序块，它将一些相关的SQL语句，流程控制语句组合在一起，用于执行某些特定的操作或者任务。将经常需要执行的特定的操作写成过程，通过过程名，就可以多次调用过程，从而实现程序的模块化设计，这种方式提高了程序的效率，节省了用户的时间。存储过程具有以下特点：●

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

存储过程增强了数据库的安全性。●存储过程允许模块化程序设计。●存储过程可以提高系统性能。

Oracle数据库教程(第3版•微课视频版)39.2存储过程的创建、调用和删除9.2.1存储过程的创建和调用1.创建存储过程语法格式：CREATE[ORREPLACE]PROCEDURE<过程名>/*定义过程名*/[(<参数名><参数类型><数据类型>[DEFAULT<默认值>][,…n])]/*定义参数类型及属性*/{IS|AS}[<变量声明>]

/*变量声明部分*/BEGIN<过程体>

/*PL/SQL过程体*/END[<过程名>][;]

Oracle数据库教程(第3版•微课视频版)49.2存储过程的创建、调用和删除说明：●ORREPLACE：如果指定的过程已存在，则覆盖同名的存储过程。●过程名：定义的存储过程的名称。●参数名：存储过程的参数名必须符合有关标识符的规则，存储过程中的参数称为形式参数(简称形参)，可以声明一个或多个形参，调用带参数的存储过程则应提供相应的实际参数(简称实参)。●参数类型：存储过程的参数类型有IN、OUT和INOUT三种模式，默认的模式是IN模式。

■IN：向存储过程传递参数，只能将实参的值传递给形参，在存储过程内部只能读不能写，对应IN模式的实参可以是常量或变量。

■OUT：从存储过程输出参数，存储过程结束时形参的值会赋给实参，在存储过程内部可以读或写，对应OUT模式的实参必须是变量。

■

INOUT：具有前面两种模式的特性，调用时，实参的值传递给形参，结束时，形参的值传递给实参，对应INOUT模式的实参必须是变量。

Oracle数据库教程(第3版•微课视频版)59.2存储过程的创建、调用和删除●

DEFAULT：指定IN参数的默认值，默认值必须是常量。●过程体：包含在过程中的PL/SQL语句。2.调用存储过程（1）在PL/SQL块中直接使用过程名调用存储过程语法格式：BEGIN<存储过程名(参数)>;END;（2）通过EXECUTE（或EXEC）语句调用一个已定义的存储过程语法格式：[{EXEC|EXECUTE}]<存储过程名(参数)>;

Oracle数据库教程(第3版•微课视频版)69.2存储过程的创建、调用和删除【例9.1】创建一个不带参数的存储过程P_Test，输出HelloOracle。（1）创建存储过程CREATEORREPLACEPROCEDUREP_Test--(a)ASBEGINDBMS_OUTPUT.PUT_LINE('HelloOracle');--(b.1)END;（2）调用存储过程BEGINP_Test;

--(b)END;运行结果：HelloOracle

Oracle数据库教程(第3版•微课视频版)79.2存储过程的创建、调用和删除【例9.2】创建带参数的存储过程P_Tc，查询指定学号学生的总学分。（1）创建存储过程CREATEORREPLACEPROCEDUREP_Tc(p_sidINCHAR)--(a)(b.1)ASv_tcnumber;

--(b.2)BEGINSELECTtcINTOv_tc

--(b.3)FROMstudentWHEREsid=p_sid;DBMS_OUTPUT.PUT_LINE(v_tc);

--(b.4)END;（2）调用存储过程BEGINP_Tc('224001');

--(b)END;运行结果：52

Oracle数据库教程(第3版•微课视频版)89.2存储过程的创建、调用和删除9.2.2存储过程的删除语法格式：DROPPROCEDURE[<用户方案名>.]<过程名>;【例9.3】删除存储过程P_Tc。DROPPROCEDUREP_Tc;

Oracle数据库教程(第3版•微课视频版)99.3存储过程的参数9.3.1带输入参数存储过程的使用【例9.4】创建一个带输入参数存储过程P_CourseMax，输出指定学号学生的所有课程中的最高分。（1）创建存储过程CREATEORREPLACEPROCEDUREP_CourseMax(p_sidINCHAR)--(a)(b.1)ASv_maxnumber;

--(b.2)BEGINSELECTMAX(grade)INTOv_max

--(b.3)FROMscoreWHEREsid=p_sid;DBMS_OUTPUT.PUT_LINE(p_sid||'学生的最高分是'||v_max);

--(b.4)END;（2）调用存储过程BEGINP_CourseMax('221004');--(b)END;

Oracle数据库教程(第3版•微课视频版)109.3存储过程的参数运行结果：221004学生的最高分是92【例9.5】创建st2表，含有4列stid、stname、stsex、stage；创建一个带输入参数存储过程P_Insert，为输入参数设置默认值，在st2表中添加学号100001~100010。CREATETABLEst2(stidchar(6)NOTNULLPRIMARYKEY,stnamechar(12)NULL,stsexchar(3)NULL,stagenumberNULL);

Oracle数据库教程(第3版•微课视频版)119.3存储过程的参数（1）创建存储过程CREATEORREPLACEPROCEDUREP_Insert(p_lowININT:=100001,p_highININT:=100010)--(a)(b.1)ASv_nint;--(b.2)BEGINv_n:=p_low;--(b.3)WHILEv_n<=p_high--(b.4)LOOPINSERTINTOst2(stid)VALUES(v_n);v_n:=v_n+1;ENDLOOP;COMMIT;END;（2）调用存储过程BEGINP_Insert;--(b)END;

Oracle数据库教程(第3版•微课视频版)129.3存储过程的参数使用SELECT语句进行测试：SELECT*FROMst2;运行结果：STIDSTNAMESTSEXSTAGE------------------------------------------------100001100002100003100004100005100006100007100008100009100010

Oracle数据库教程(第3版•微课视频版)139.3存储过程的参数9.3.2带输出参数存储过程的使用【例9.6】创建一个带输出参数的存储过程P_Number，查找指定专业的学生人数。（1）创建存储过程CREATEORREPLACEPROCEDUREP_Number(p_specialityINchar,p_numOUTnumber)

--(a)(b.1)ASBEGINSELECTCOUNT(speciality)INTOp_num--(b.2)FROMstudentWHEREspeciality=p_speciality;END;

Oracle数据库教程(第3版•微课视频版)149.3存储过程的参数（2）调用存储过程DECLAREv_numnumber;BEGINP_Number('计算机',v_num);

--(b)(c)DBMS_OUTPUT.PUT_LINE('计算机专业的学生人数是：'||v_num);--(c.1)END;运行结果：计算机专业的学生人数是：3

Oracle数据库教程(第3版•微课视频版)159.3存储过程的参数9.3.3带输入输出参数存储过程的使用【例9.7】创建一个存储过程P_Swap，交换两个变量的值。（1）创建存储过程CREATEORREPLACEPROCEDUREP_Swap(p_t1INOUTNUMBER,p_t2INOUTNUMBER)

--(a)(b.1)ASv_tempnumber;--(b.2)BEGINv_temp:=p_t1;--(b.3)p_t1:=p_t2;p_t2:=v_temp;END;

Oracle数据库教程(第3版•微课视频版)169.3存储过程的参数（2）调用存储过程DECLAREv_1number:=90;v_2number:=380;BEGINP_Swap(v_1,v_2);

--(b)(c)DBMS_OUTPUT.PUT_LINE('v_1='||v_1);--(c.1)DBMS_OUTPUT.PUT_LINE('v_2='||v_2);END;运行结果：v_1=380v_2=90

Oracle数据库教程(第3版•微课视频版)179.4函数概述函数是存储在数据库中并编译过的PL/SQL程序块，调用函数要用表达式，并将返回值返回到调用程序。函数和存储过程都是命名PL/SQL程序块，预编译后放在数据库服务器端供用户使用，在创建的形式上有些相似，不同之处如下：（1）调用函数使用表达式，调用存储过程使用过程名。（2）函数有返回值，存储过程通常不需要返回值。

Oracle数据库教程(第3版•微课视频版)189.5函数的创建、调用和删除9.5.1函数的创建和调用1.创建用户定义函数语法格式：CREATE[ORREPLACE]FUNCTION<函数名>

/*函数名称*/(<参数名1><参数类型><数据类型>,

/*参数定义部分*/<参数名2><参数类型><数据类型>,<参数名3><参数类型><数据类型>,…)RETURN<返回值类型>

/*定义返回值类型*/{IS|AS}[声明变量]BEGIN<函数体>;/*函数体部分*/[RETURN(<返回表达式>);]

/*返回语句*/END[<函数名>];

Oracle数据库教程(第3版•微课视频版)199.5函数的创建、调用和删除说明：●

函数名：定义函数的函数名必须符合标识符的规则，且名称在数据库中是唯一的。●

形参和实参：在函数中，在函数名称后面的括号中定义的参数称为形参(形式参数)。在调用函数的程序中，在表达式中函数名称后面的括号中的参数称为实参(实际参数)。●

参数类型：参数类型有IN、OUT、INOUT三种模式，默认为IN模式。■

IN模式：表示传递给IN模式的形参，只能将实参的值传递给形参，对应IN模式的实参可以是常量或变量。■

OUT模式：表示OUT模式的形参将在函数中被赋值，可以将形参的值传给实参，对应OUT模式的实参必须是变量。■

INOUT模式：INOUT模式的形参既可以传值也可以被赋值，对应INOUT模式的实参必须是变量。

Oracle数据库教程(第3版•微课视频版)209.5函数的创建、调用和删除●数据类型：定义参数的数据类型，不需要指定数据类型的长度。●

RETURN<返回值类型>：指定返回值的数据类型。●

函数体：由PL/SQL语句组成，它是实现函数功能的主要部分。●

RETURN语句：将返回表达式的值返回给函数调用程序。2.调用函数语法格式：<变量名>:=<函数名>[(<实参1>,<实参2>,…)]

Oracle数据库教程(第3版•微课视频版)219.5函数的创建、调用和删除【例9.8】创建选修某门课程的学生人数的函数F_Number，调用该函数查询选修数据库系统的学生人数。（1）创建函数CREATEORREPLACEFUNCTIONF_Number(p_cnameINchar)--(a)(b.1)RETURN