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

第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_COUNT

AS

V_TOTALNUMBER(3);--声明一个变量，用于存储雇员人数

BEGIN

SELECTCOUNT(*)INTOV_TOTALFROMEMP;--把人数存入变量

DBMS_OUTPUT.PUT_LINE(雇员总人数为：||V_TOTAL);--输出人数信息

END;

9.2存储过程

2调用存储过程

创建过程后，该过程就存储在数据库中，用户可以调用执行。存储过程的执行有两种方式：一是使用EXECUTE执行过程，另一种是在其它块（未命名块或者命名块）中调用执行。执行(或调用)存储过程的用户可以是过程的创建者或是拥有EXECUTEANYPROCEDURE系统权限的可以或是被拥有者授予EXECUTE权限的用户。

EXECUTE执行存储过程

使用EXECUTE执行存储过程的语法为：

EXECUTEPROC_NAME[(参数值,,,)];

例:执行PROC_COUNT过程;

SQLSETSERVEROUTPUTON

SQLEXECUTEPROC_COUNT;

9.2存储过程

块中调用执行存储过程

可以在块中调用已创建的存储过程。

例2：写一个存储过程PROC_INFO，查询雇员的表所有员工的工号、姓名和参加工作时间，并调用PROC_NAME存储过程显示员工总人数。

---创建PROC_INFO存储过程，在存储过程内部调用了已创建的存储过程---

CReaTEORREPLACEPROCEDUREPROC_INFO

IS

CURSORCUR1ISSELECTEMPNO,ENAME,HireDATEFROMEMP;

BEGIN

FORREC_CUR1INCUR1LOOP

DBMS_OUTPUT.PUT_LINE(员工工号：||rec_cur1.empno||||姓名：||rec_cur1.ename||||参加工作时间：||rec_cur1.hiredate);