第23章函数精选文档.ppt

《第23章函数精选文档.ppt》由会员分享，可在线阅读，更多相关《第23章函数精选文档.ppt（46页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第第23章章函数函数本讲稿第一页，共四十六页第第2323章章 函数函数函数的创建；函数的创建；函数的调用；函数的调用；参数传递的方法；参数传递的方法；函数在函数在SQL语句中的应用；语句中的应用；函数的删除；函数的删除；存储过程与函数的比较。存储过程与函数的比较。本讲稿第二页，共四十六页23.1 23.1 创建函数创建函数在在PL/SQL中，函数用来返回特定的结果值。一个函数中，函数用来返回特定的结果值。一个函数包括函数说明和函数体两个部分。其中，函数体可以包括声包括函数说明和函数体两个部分。其中，函数体可以包括声明部分、可执行部分和异常处理部分。本节就来介绍函数创明部分、可执行部分和异常处理

2、部分。本节就来介绍函数创建的语法规则以及如何创建无参数和有参数的函数。建的语法规则以及如何创建无参数和有参数的函数。本讲稿第三页，共四十六页23.1.1 23.1.1 创建函数的语法规则创建函数的语法规则使用使用CREATE OR REPLACE FUNCTION关键字可关键字可以创建一个函数。以关键字以创建一个函数。以关键字FUNCTION开头的是说明部分；开头的是说明部分；以关键字以关键字IS或者或者AS开头，以关键字开头，以关键字END结尾的是函数体部分。结尾的是函数体部分。其创建存储函数的语法规则如下：其创建存储函数的语法规则如下：本讲稿第四页，共四十六页23.1.1 23.1.1 创

3、建函数的语法规则创建函数的语法规则CREATE OR REPLACE FUNCTION function_name(argument1model datatype1,argument2model datatype2.)RETURN return_datatypeIS|ASlocal declarationsBEGIN executable statementsEXCEPTION exception handlersEND function_name;本讲稿第五页，共四十六页23.1.1 23.1.1 创建函数的语法规则创建函数的语法规则例例23.1创建一个用来显示当前系统日期和时间的函数创建一

4、个用来显示当前系统日期和时间的函数CREATEORREPLACEFUNCTIONsysdata_timeRETURNVARCHAR2ASBEGINRETURNTO_CHAR(SYSDATE,YYYY-MM-DDHH24:MI:SS);ENDsysdata_time;本讲稿第六页，共四十六页23.1.2 23.1.2 函数体中的函数体中的RETURNRETURN子句子句在上一节中，介绍了创建函数的语法规则。可以看到，在上一节中，介绍了创建函数的语法规则。可以看到，在函数的创建中需要使用一个在函数的创建中需要使用一个RETURN子句，其余的语法规子句，其余的语法规则与创建函数的语法规则是非常相似。

5、正如在则与创建函数的语法规则是非常相似。正如在23.1.1小节中小节中最后在注意中提到的，除了在函数声明中需要使用最后在注意中提到的，除了在函数声明中需要使用RETURN子句用来指定函数返回的数据类型之外，在函数体内还需要子句用来指定函数返回的数据类型之外，在函数体内还需要使用一个使用一个RETURN子句返回一个特定的数据值，在函数体内子句返回一个特定的数据值，在函数体内RETURN子句的语法规则如下：子句的语法规则如下：RETURN exception;本讲稿第七页，共四十六页23.1.2 23.1.2 函数体中的函数体中的RETURNRETURN子句子句其中，其中，exception是一个

6、表达式，用来表示函数要返回的是一个表达式，用来表示函数要返回的数据值。当程序执行到函数体内的数据值。当程序执行到函数体内的RETURN语句子句后，程语句子句后，程序会将其控制权立即返回给调用环境。执行函数调用之后的序会将其控制权立即返回给调用环境。执行函数调用之后的语句。如果语句。如果exception返回的数据值的数据类型与函数声明中返回的数据值的数据类型与函数声明中的的RETURN子句指定的返回值的数据类型不相符，则子句指定的返回值的数据类型不相符，则PL/SQL会将其转换为会将其转换为RETURN子句指定的返回值的数据类子句指定的返回值的数据类型。型。本讲稿第八页，共四十六页23.1.3

7、 23.1.3 创建有输入参数的函数创建有输入参数的函数如果在创建函数时，其参数模式的默认值为如果在创建函数时，其参数模式的默认值为IN模式。如模式。如果该函数中的参数模式为果该函数中的参数模式为IN模式，那么调用该函数时，实际模式，那么调用该函数时，实际参数的值将传递给被调用的函数。在该函数中，被指定为参数的值将传递给被调用的函数。在该函数中，被指定为IN模式的参数的作用相当于一个常量，其值只能被读取，不能模式的参数的作用相当于一个常量，其值只能被读取，不能为其进行赋值操作。为其进行赋值操作。本讲稿第九页，共四十六页23.1.3 23.1.3 创建有输入参数的函数创建有输入参数的函数例例23

8、.2创建有输入参数的函数，返回学生的课程成绩。创建有输入参数的函数，返回学生的课程成绩。CREATEORREPLACEFUNCTIONget_result(p_stuIDINt_t_result.stuID%TYPE,p_curIDINt_result.curID%TYPE)RETURNINTEGERASv_resultINT;-学生成绩学生成绩BEGIN/*查询指定学生编号和课程编号的学生成绩查询指定学生编号和课程编号的学生成绩*/SELECTresultINTOv_resultFROMt_resultWHEREstuID=p_stuIDANDcurID=p_curID;/*当查询到的学生成

9、绩大于当查询到的学生成绩大于100或者小于或者小于0时，返回错误信息时，返回错误信息*/IFv_result100ORv_result将实参和形参关联起来。将实参和形参关联起来。本讲稿第三十三页，共四十六页23.3.2 23.3.2 使用参照位置传递参数值使用参照位置传递参数值DECLAREv_stuIDVARCHAR2(15):=s281234;v_curIDVARCHAR2(15):=t321;v_resultNUMBER;BEGINv_result:=get_result(p_stuID=v_stuID,p_curID=v_curID);END;。本讲稿第三十四页，共四十六页23.3.3

10、 23.3.3 使用参数名称和位置传递参数值使用参数名称和位置传递参数值在在PL/SQL中，也可以混合使用参数名称和参数位置来中，也可以混合使用参数名称和参数位置来传递参数变量或者数据。在混合使用参数名称和参数位置来传递参数变量或者数据。在混合使用参数名称和参数位置来传递参数变量或者数据时，参数位置传递参数值的方法需要传递参数变量或者数据时，参数位置传递参数值的方法需要在参数名称传递参数值的方法之前使用。在参数名称传递参数值的方法之前使用。本讲稿第三十五页，共四十六页23.3.3 23.3.3 使用参数名称和位置传递参数值使用参数名称和位置传递参数值DECLAREv_stuIDVARCHAR2

11、(15):=s281234;v_curIDVARCHAR2(15):=t321;v_resultNUMBER;BEGINv_result:=get_result(p_stuID=v_stuID,p_curID=v_curID);END;本讲稿第三十六页，共四十六页23.4 23.4 函数在函数在SQLSQL中的应用中的应用一般情况下，子程序（包括存储过程和函数）是不能在一般情况下，子程序（包括存储过程和函数）是不能在SQL语句中被调用的，但是如果一个函数满足某些条件的限语句中被调用的，但是如果一个函数满足某些条件的限制，那么该函数是可以在制，那么该函数是可以在SQL语句中被调用的。这一节就来语

12、句中被调用的。这一节就来介绍在介绍在SQL语句中如何使用函数。语句中如何使用函数。本讲稿第三十七页，共四十六页23.4.1 23.4.1 纯度规则纯度规则如果希望函数可以在如果希望函数可以在SQL语句中被调用，函数就必须要语句中被调用，函数就必须要满足一些不同的限制条件。在满足一些不同的限制条件。在SQL语句中调用函数必须遵守语句中调用函数必须遵守以下的纯度规则。以下的纯度规则。SQL语句中调用的函数函数必须是无参函数或者是带语句中调用的函数函数必须是无参函数或者是带IN模式参数的函数。在模式参数的函数。在SQL语句中不能调用都有语句中不能调用都有OUT模式或模式或者是者是IN OUT模式参数

13、的函数。模式参数的函数。SQL语句中调用的函数中的形式参数不能使用语句中调用的函数中的形式参数不能使用PL/SQL中特有的数据类型（例如，中特有的数据类型（例如，BOOLEAN、RECORD、TABLE等），而只能使用等），而只能使用SQL支持的标准数据类型（例如，支持的标准数据类型（例如，NUMBER、CHAR、VARCHAR2、DATE、ROW等）。等）。本讲稿第三十八页，共四十六页23.4.1 23.4.1 纯度规则纯度规则SQL语句中调用的函数的返回值类型也必须是语句中调用的函数的返回值类型也必须是SQL支持的标准数据类型支持的标准数据类型（例如，（例如，NUMBER、CHAR、VAR

14、CHAR2、DATE、ROW等）。等）。SQL语句中调用的函数不能包含语句中调用的函数不能包含SQL事务控制语句（例如，事务控制语句（例如，COMMIT、ROLLBACK等）、系统控制语句等）、系统控制语句(例如，例如，ALTER SYSTEM等等)、会话控制语句、会话控制语句(例如，例如，SET ROLE等等)以及数据定义语句（例如，以及数据定义语句（例如，CREATE等）。等）。SQL语句中调用的函数必须是存储在数据库中的函数，该函数或者是独立语句中调用的函数必须是存储在数据库中的函数，该函数或者是独立存储，或者是作为包的一部分。存储，或者是作为包的一部分。SQL语句中调用的函数不能修改数

15、据库中的任何数据表。语句中调用的函数不能修改数据库中的任何数据表。在在INSERT、UPDATE或或DELETE语句中调用函数时，不能对这些语句所语句中调用函数时，不能对这些语句所能影响到的数据表进行查询或者修改操作。能影响到的数据表进行查询或者修改操作。本讲稿第三十九页，共四十六页23.4.2 23.4.2 在在SQLSQL中调用函数中调用函数在在23.4.1节中，了解了在节中，了解了在SQL语句中调用函数必须遵守语句中调用函数必须遵守的纯度规则。这一小节通过一个例子来看一下如何在的纯度规则。这一小节通过一个例子来看一下如何在SQL语语句中调用函数。在句中调用函数。在SQL语句调用函数之前，

16、首先需要创建一语句调用函数之前，首先需要创建一个函数，这里创建一个个函数，这里创建一个get_curName函数，通过课程编号编函数，通过课程编号编号作为输入参数，查询该课程编号对应的课程名。号作为输入参数，查询该课程编号对应的课程名。本讲稿第四十页，共四十六页23.4.2 23.4.2 在在SQLSQL中调用函数中调用函数这个函数满足上面的在这个函数满足上面的在SQL语句中调用函数必须遵守的语句中调用函数必须遵守的纯度规则，因此可以在纯度规则，因此可以在SQL语句中对该函数进行调用。语句中对该函数进行调用。SELECTcurID,get_curName(curID)AScurNameFROM

17、t_curriculum本讲稿第四十一页，共四十六页23.5 23.5 删除函数删除函数与在与在SQL语句中删除一个数据表类似，函数也可以删除。语句中删除一个数据表类似，函数也可以删除。使用使用DROP命令可以将函数从数据字典中删除。其删除函数命令可以将函数从数据字典中删除。其删除函数的语法格式如下：的语法格式如下：DROP function_name;其中，其中，function _name为要删除函数的名字，这个函数为要删除函数的名字，这个函数一定是一个已经存在的函数。例如，要删除函数一定是一个已经存在的函数。例如，要删除函数get_result，就可以使用下面的语句来完成。就可以使用下面

18、的语句来完成。DROP get_result;在执行该在执行该DROP命令时，程序会隐式执行一个命令时，程序会隐式执行一个COMMIT命令。如果函数不存在，则在使用命令。如果函数不存在，则在使用DROP命令时会命令时会引发引发“Object does not exist”本讲稿第四十二页，共四十六页23.6 23.6 存储过程与函数存储过程与函数PL/SQL中的子程序包括存储过程和函数。这里用来两中的子程序包括存储过程和函数。这里用来两章的篇幅分别介绍了存储过程和函数的使用。它们在许多地章的篇幅分别介绍了存储过程和函数的使用。它们在许多地方都有其相同的特性。方都有其相同的特性。存储过程和函数的

19、程序代码都是由存储过程和函数的程序代码都是由SQL语句或者过程语语句或者过程语句组成的。句组成的。在其过程体和函数体中都可以包含声明部分、可执行部在其过程体和函数体中都可以包含声明部分、可执行部分和异常处理部分。分和异常处理部分。存储过程和函数都可以由应用程序进行调用。存储过程和函数都可以由应用程序进行调用。存储过程和函数都可以通过使用参数位置、参数名称或存储过程和函数都可以通过使用参数位置、参数名称或者参数名称和位置组合的方式进行参数传递。者参数名称和位置组合的方式进行参数传递。本讲稿第四十三页，共四十六页23.6 23.6 存储过程与函数存储过程与函数存储过程和函数都可以使用默认值或是存储

20、过程和函数都可以使用默认值或是NOCOPY编译编译提示的方式传递参数。（有关使用默认值或是提示的方式传递参数。（有关使用默认值或是NOCOPY编译编译提示的方式传递参数的方法可以参考提示的方式传递参数的方法可以参考22.4.4小节使用小节使用NOCOPY编译提示传递参数和编译提示传递参数和22.4.5小节使用参数的默认值小节使用参数的默认值的内容，函数在使用默认值或是的内容，函数在使用默认值或是NOCOPY编译提示的方式传编译提示的方式传递参数与存储过程的相同）。递参数与存储过程的相同）。本讲稿第四十四页，共四十六页23.6 23.6 存储过程与函数存储过程与函数虽然存储过程和函数在许多地方都

21、有其相同的特性，但虽然存储过程和函数在许多地方都有其相同的特性，但是它们之间在一些方面还是存在不同的。存储过程和函数的是它们之间在一些方面还是存在不同的。存储过程和函数的不同主要表现在以下几个方面。不同主要表现在以下几个方面。存储过程调用本身就是一个存储过程调用本身就是一个PL/SQL语句，作为一个独语句，作为一个独立的部分来执行；而函数调用只能是作为表达式的一部分被立的部分来执行；而函数调用只能是作为表达式的一部分被调用，它既可以在调用，它既可以在PL/SQL语句块中被调用，也可以在语句块中被调用，也可以在SQL语句中调用。语句中调用。存储过程的存储过程的RETURN子句没有返回值，在存储过

22、程中子句没有返回值，在存储过程中的的RETURN子句的作用只是用来表示程序执行是否成功，它子句的作用只是用来表示程序执行是否成功，它并不会返回任何的值或者表达式。而函数体中的并不会返回任何的值或者表达式。而函数体中的RETURN子子句需要返回一个特定的数据值。句需要返回一个特定的数据值。如果返回的值有多个，可以通过如果返回的值有多个，可以通过OUT参数，使用存储过参数，使用存储过程来完成；如果返回值只有一个，可以通过函数体中的程来完成；如果返回值只有一个，可以通过函数体中的RETURN子句，使用函数来完成。子句，使用函数来完成。本讲稿第四十五页，共四十六页23.6 23.6 存储过程与函数存储过程与函数说明：函数也可以通过说明：函数也可以通过OUT参数返回多个值，但是在实参数返回多个值，但是在实际应用中，并不建议使用这种方式。如果要返回多个值，则际应用中，并不建议使用这种方式。如果要返回多个值，则建议使用存储过程，通过指定建议使用存储过程，通过指定OUT参数来完成。（可以参看参数来完成。（可以参看例例22.2.2小节小节OUT模式以及模式以及22.3.3小节调用带有输出参数的小节调用带有输出参数的存储过程的内容）存储过程的内容）本讲稿第四十六页，共四十六页