《Oracle大型数据库基础开发教程》（微课视频版）课件 第9章 存储过程与函数

第9章存储过程与函数9.1存储过程和函数概述存储过程和函数也是一种PL/SQL块，是存入数据库的PL/SQL块。但其不同于已经介绍过的PL/SQL程序，通常把PL/SQL程序称为未命名块，而存储过程和函数是以命名的方式存储于数据库中的。和PL/SQL程序未命名块相比，存储过程和函数等命名块有很多优点，主要有几下几点：（1）存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的，因为代码不保存在本地，用户可以在任何客户机上登录到数据库，并调用或修改代码。（2）存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要由存储过程和函数的所有者的授权，只有被授权的用户或创建者本身才能执行存储过程或调用函数。（3）存储过程和函数的信息是写入数据字典的，所以存储过程可以看作是一个公用模块，用户编写的PL/SQL程序或其它存储过程都可以调用它(但存储过程和函数不能调用PL/SQL程序)。一个重复使用的功能，可以设计成为存储过程，比如：显示一张工资统计表，可以设计成为存储过程；一个经常调用的计算，可以设计成为存储函数；根据雇员编号返回雇员的姓名，可以设计成存储函数。（4）像其他高级语言的过程和函数一样，可以传递参数给存储过程或函数，参数的传递也有多种方式。存储过程和函数的区别是存储过程没有返回值，而函数有且仅有一个返回值。9.2存储过程1创建存储过程用户创建存储过程，需要有CREATEPROCEDURE或CREATEANYPROCEDURE的系统权限。该权限通常由系统管理员授予。创建存储过程的语法如下：Create[orreplace]procedureproc_name [(参数名[in|out|inout]type[,…..])]Is|as声明部分;begin执行部分;EXCEPTION异常处理部分;endproc_name;9.2存储过程1创建存储过程用户创建存储过程，需要有CREATEPROCEDURE或CREATEANYPROCEDURE的系统权限。该权限通常由系统管理员授予。创建存储过程的语法如下：Create[orreplace]procedureproc_name [(参数名[in|out|inout]type[,…..])]Is|as声明部分;begin执行部分;EXCEPTION异常处理部分;endproc_name;9.2存储过程1创建存储过程例1：写一个存储过程，显示雇员表EMP雇员人数。CREATEORREPLACEPROCEDUREPROC_COUNTASV_TOTALNUMBER(3);--声明一个变量，用于存储雇员人数

BEGINSELECTCOUNT(*)INTOV_TOTALFROMEMP;--把人数存入变量DBMS_OUTPUT.PUT_LINE('雇员总人数为：'||V_TOTAL);--输出人数信息END;9.2存储过程2调用存储过程创建过程后，该过程就存储在数据库中，用户可以调用执行。存储过程的执行有两种方式：一是使用EXECUTE执行过程，另一种是在其它块（未命名块或者命名块）中调用执行。执行(或调用)存储过程的用户可以是过程的创建者或是拥有EXECUTEANYPROCEDURE系统权限的可以或是被拥有者授予EXECUTE权限的用户。EXECUTE执行存储过程使用EXECUTE执行存储过程的语法为：EXECUTEPROC_NAME[(参数值,,,)];例:执行PROC_COUNT过程;SQL>SETSERVEROUTPUTONSQL>EXECUTEPROC_COUNT;9.2存储过程块中调用执行存储过程可以在块中调用已创建的存储过程。例2：写一个存储过程PROC_INFO，查询雇员的表所有员工的工号、姓名和参加工作时间，并调用PROC_NAME存储过程显示员工总人数。---创建PROC_INFO存储过程，在存储过程内部调用了已创建的存储过程---CReaTEORREPLACEPROCEDUREPROC_INFOISCURSORCUR1ISSELECTEMPNO,ENAME,HireDATEFROMEMP;BEGINFORREC_CUR1INCUR1LOOPDBMS_OUTPUT.PUT_LINE('员工工号：'||rec_cur1.empno||''||'姓名：'||rec_cur1.ename||''||'参加工作时间：'||rec_cur1.hiredate);endloop;Proc_count;--注意存储过程的调用方式，不能作为其它表达式的一部分end;9.2存储过程带有参数的存储过程PL/SQL中可以定义带有参数的存储过程，通过参数向存储过程传递数据或从存储过程内部向外传出值。正确的使用参数可以大大增加存储过程的灵活性和通用性。存储过程参数的类型有三种，分别为IN、OUT和INOUT参数，默认是IN参数，具体说明如表9-1所示。9.2存储过程例3：带有输入参数的存储过程举例：写一个存储过程PROC_ENAME，输出特定员工的姓名。createorreplaceprocedureproc_Ename(v_empnoinemp.empno%type)isv_enameemp.ename%type;beginselectenameintov_enamefromempwhereempno=v_empno;dbms_output.put_line(v_empno||'的姓名：'||v_ename);exceptionwhenno_data_foundthendbms_output.put_line(v_empno||'不存在');end;9.2存储过程例4：带有输出参数的存储过程举例：写一个存储过程PROC_OUT1，利用输出参数输出特定员工的姓名。createorreplaceprocedureproc_OUT1(v_empnoinemp.empno%type,v_enameoutemp.ename%type)isbeginselectenameintov_enamefromempwhereempno=v_empno;exceptionwhenno_data_foundthendbms_output.put_line(v_empno||'不存在');end;9.2存储过程9.3函数函数用于计算和返回特定的数据，可以将经常需要进行的计算写成函数，函数的调用是表达式的一部分，而过程的调用是一条PL/SQL语句。函数也是存储过程的一种，它与存储过程的区别在于：函数必须向调用者返回一个执行结果，而存储过程没有返回值。1创建函数创建函数的语法如下：create[orreplace]functionfun_name([参数名[in|out|inout)type[,….]])return函数返回值类型Is|as

声明部分;begin执行部分;Return表达式;endfun_name;9.3函数例5：编写一个PL/SQL函数fun_count，返回dept表的部门个数。createorreplacefunctionfun_countreturnnumber--此时不能指定宽度asv_countnumber(3);beginselectcount(*)intov_countfromdept;return(v_count);end;9.3函数调用函数函数创建后，就可以对其进行调用了。与存储过程不同，函数调用时必须作为表达式的一部分，函数调用方法与存储过程相比，函数还可以出现在SELECT语句中。下面以FUN_ename函数的调用为例，介绍函数的调用方法。1.在select语句中调用函数Selectfun_ename(7788)fromdual;--函数作为查询表达式的一部分。2.在块中调用函数BeginDbms_output.put_line(fun_ename(7788));--函数作为输出表达式的一部分。End;3.使用execute调用函数由于函数有一个返回值，在调用时必须作为表达式的一部分，当使用execute调用函数时，同样需要使用结合变量，把函数的返回值存储到结合变量中。Variablev_enamevarchar2(20);Execute:v_ename:=fun_ename(7788);--函数作为赋值表达式的一部分。Print:v_ename;9.4程序包程序包的概念和组成程序包(PACKAGE，简称包)是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合，作为一个完整的单元存储在数据库中，用名称来标识包。它具有面向对象程序设计语言的特点，是对这些PL/SQL

程序设计元素的封装。包类似于c#和JAVA语言中的类，其中变量相当于类中的成员变量，过程和函数相当于类方法。把相关的模块归类成为包，可使开发人员利用面向对象的方法进行存储过程的开发，从而提高系统性能。与高级语言中的类相同，包中的程序元素也分为公用元素和私用元素两种，这两种元素的区别是他们允许访问的程序范围不同，即它们的作用域不同。公用元素不仅可以被包中的函数、过程所调用，也可以被包外的PL/SQL程序访问，而私有元素只能被包内的函数和过程序所访问。9.4程序包程序包的创建1.程序包说明程序包说明用于声明包的公用组件，如变量、常量、自定义数据类型、异常、过程、函数、游标等。包说明中定义的公有组件不仅可以在包内使用，还可以由包外其他过程、函数。包说明语法如下：CREATE[ORREPLACE]PACKAGEpackage_nameIS|AS[PRAGMASERIALLY_REUSABLE;]

公有数据类型定义

公有变量声明

公有常量声明

公有异常错误声明

公有游标，函数，存储过程声明ENDpackage_name;9.4程序包程序包的创建2.程序包体包体是包的具体实现细节，其实现在包说明中声明的所有公有过程、函数、游标等。当然也可以在包体中声明仅属于自己的私有过程、