函数与存储过程

函数与存储过程第1页，共25页，2023年，2月20日，星期三8.1函数函数是Oracle数据库中常用对象之一，与其他编程语言的函数一样，Oracle中的函数也必须返回一个值。这也是函数区别于存储过程的重要特征。第2页，共25页，2023年，2月20日，星期三8.1.1函数简介1．函数与功能的划分2．函数的参数3．函数的返回值第3页，共25页，2023年，2月20日，星期三8.1.2创建函数1．创建函数createorreplacefunctionget_hello_msgreturnvarchar2asbeginreturn'helloworld';endget_hello_msg;2．在数据字典中查看函数的信息selectobject_name,object_type,statusfromuser_objectswherelower(object_name)='get_hello_msg'3．查看函数的返回值

setserverouton;declaremsgvarchar2(20);beginmsg:=get_hello_msg;dbms_output.put_line(msg);end;第4页，共25页，2023年，2月20日，星期三8.1.3函数中的括号其他标准编程语言中，函数的括号都是必需的，但是函数get_hello_msg并没有使用小括号。当函数需要传入参数时，参数列表必须使用小括号括起来，但是当函数没有参数时，小括号可以省略。当函数没有小括号时，在形式上和变量相同，那么有可能会产生变量冲突。第5页，共25页，2023年，2月20日，星期三8.1.4函数的参数在8.1.2节中创建的函数get_hello_msg是无参数函数，本节将演示带参数函数的创建和使用。createorreplacefunctionget_tax(p_salarynumber)returnnumberasbegindeclaretax_salarynumber;begintax_salary:=p_salary-2000;

iftax_salary<=0thenreturn0;endif;

returntax_salary*5/100;endendget_tax第6页，共25页，2023年，2月20日，星期三8.1.5函数的确定性每次调用函数，Oracle总是根据传入的参数，执行相同的步骤，并返回最终值。函数的确定性是指，传入的参数一定，无论函数被调用多少次，都会返回相同的值。例如，对于get_tax函数，每次输入相同的工资额，那么，返回值都不会改变。createorreplacefunctionget_tax(p_salarynumber)returnnumberdeterministicas

begindeclaretax_salarynumber;endget_tax;对于具有确定性的函数，在定义时，可以使用deterministic选项，以告知Oracle创建确定性函数。第7页，共25页，2023年，2月20日，星期三8.1.6典型函数举例行转列问题是一个常见的问题，即将多行数据转换为一列。例如，在学生表中，存储了很多学生资料，现欲获得所有学生的姓名列表，常见做法是将所有学生姓名串联起来，即多行转一列。【范例8-7】selectget_student_string()fromdual第8页，共25页，2023年，2月20日，星期三8.2存储过程存储过程（StoreProcedure）对应于其他编程语言中的过程。存储过程不必返回值，但是可以有参数。本节将详细讲述存储过程的创建及使用。第9页，共25页，2023年，2月20日，星期三8.2.1存储过程简介1．提高数据库执行效率2．提高安全性3．可复用第10页，共25页，2023年，2月20日，星期三8.2.2创建存储过程1．创建存储过程createorreplaceprocedureupdate_studentsasbeginupdatestudentssetSTUDENT_AGE=10commit;endupdate_students;

2．查看存储过程在数据字典中的信息selectobject_name,object_type,statusfromuser_objectswherelower(object_name)='update_students'select*fromuser_sourcewherelower(name)='update_students'

3．执行存储过程executeupdate_students;第11页，共25页，2023年，2月20日，星期三8.2.3存储过程的参数——IN参数IN参数，顾名思义，是指传入参数，即只进不出的参数。它由调用者传递给存储过程之后，存储过程在执行过程中，无论怎样使用该参数，都无法改变该参数的值。该参数对于存储过程来说，是只读的。例如，在更新学生信息的存储过程update_stduents中，可以传入一个年龄参数，用以标识需要将学生年龄修改为多少岁。createorreplaceprocedureupdate_students(in_ageinnumber)asbeginupdatestudentssetstudent_age=in_age;---in_age:=in_age+10;commit;endupdate_students;executeupdate_students(12);第12页，共25页，2023年，2月20日，星期三8.2.4存储过程的参数——OUT参数函数可以有返回值，存储过程并没有显式的返回值。但是可以通过OUT参数获得存储过程的处理结果。在范例8-11中，更新了表students中的学生年龄，可以通过OUT参数返回更新之后的值，以便验证更新是否成功。createorreplaceprocedureupdate_students(in_ageinnumber,out_ageoutnumber)asbeginupdatestudentssetstudent_age=in_age;selectstudent_ageintoout_agefromstudentswherestudent_id=1;commit;endupdate_students;declareupdated_agenumber;beginupdate_students(20,updated_age);dbms_output.put_line(updated_age);end;第13页，共25页，2023年，2月20日，星期三8.2.5存储过程的参数——INOUT参数INOUT参数既可以作为输入参数，也可以作为输出参数。因此，INOUT参数一般用于对参数的值进行处理，并处理结果输出。一个典型实例就是交换两个变量的值。createorreplaceprocedureswap(in_out_param1inoutnumber,in_out_param2inoutnumber)asbegindeclareparamnumber;beginparam:=in_out_param1;in_out_param1:=in_out_param2;in_out_param2:=param;end;end;declareparam1number:=25;param2number:=35;beginswap(param1,param2);dbms_output.put_line('param1='||param1);dbms_output.put_line('param2='||param2);end;第14页，共25页，2023年，2月20日，星期三8.2.6存储过程的参数——参数顺序像其他编程语言一样，存储过程的参数顺序同样重要。在以上范例中，所有参数在调用时的值都是按照顺序分配给存储过程。那么顺序就显得格外重要，如果顺序颠倒，不仅得不到正确结果，而且有可能返回不可预知的错误。createorreplaceprocedureupdate_students(in_ageinnumber,in_nameinvarchar2)asbeginupdatestudentssetstudent_age=in_agewherestudent_name=in_name;commit;endupdate_students;名称表示法：beginupdate_students(in_name=>'柳青',in_age=>19);end;第15页，共25页，2023年，2月20日，星期三8.2.7存储过程的参数——参数的默认值有时，存储过程的参数有很多个。对于用户来说，部分参数并非必需，那么，在定义存储过程时应该为可选参数设定默认值，以允许用户不为该参数传值。需要注意的是，默认值是仅对IN参数而言，OUT和INOUT参数没有默认值。范例8-17演示了如何使用IN参数的默认值。createorreplaceprocedureinsert_student(in_student_idinnumber,in_student_nameinvarchar2,in_student_ageinnumberdefault20)asbegininsertintostudentsvalues(in_student_id,in_student_name,in_student_age);commit;endinsert_student;begininsert_student(11,'王蒙');end;第16页，共25页，2023年，2月20日，星期三8.2.8存储过程的参数——参数顺序总结在讲解了三种参数的用法之后，可以对参数的顺序总结如下：具有默认值的参数应该置于参数列表的末尾，因为有时用户需要省略该参数；没有默认值的参数可以遵循“IN参数”——>“OUT参数”——>“INOUT参数”。第17页，共25页，2023年，2月20日，星期三8.3程序包程序包可以将若干个函数或者存储过程组织起来，作为一个对象进行存储。程序包通常由两部分构成：规范（specification）和主体（body）。程序包也可以包含常量和变量，包中的所有函数和存储过程都能够使用这些变量和常量。第18页，共25页，2023年，2月20日，星期三8.3.1规范1．创建程序包规范createorreplacepackagepkg_studentsasstudentStringvarchar2(500);studentAgenumber:=18;functionget_student_stringreturnvarchar2;procedureupdate_student(in_student_idinnumber);procedureinsert_student(in_student_idinnumber,in_student_nameinvarchar2,in_student_ageinvarchar2);proceduredelete_student(in_student_idinnumber);endpkg_students;2．在数据字典中查看程序包规范的信息selectobject_name,object_type,statusfromuser_objectswherelower(OBJECT_NAME)='pkg_students'第19页，共25页，2023年，2月20日，星期三8.3.2主体1．创建程序包主体createorreplacepackagebodypkg_studentsasendpkg_students;范例：createorreplacepackagebodypkg_studentsasfunctionget_student_stringreturnvarchar2isbeginreturn'students';endget_student_string;endpkg_students;2．在数据字典中查看该程序包主体的信息selectobject_name,object_type,statusfromuser_objectswherelower(OBJEC