Oracle存储过程和程序包

一、为什么要用存储过程？

如果在应用程序中经常需要执行特定的操作，可以基于这些操作简历一个特定的过程。通过使用过程可以简化客户端程序的开发和维护，而且还能提高客户端程序的运行性能。

二、过程的优点？

1、预编译：存储过程预先编译好放在数据库内，减少编译语句所花的时间。

2、缓存：预编译的存储过程会进入缓存，所以对于经常执行的存储过程，除了第一次执行外，其它次数的执行速度会明显提高。

3、减少网络传输：特别是对于一些处理数据的存储过程，不必像直接使用SQL语句那样多次传送数据到客户端。

4 、可维护性高：更新存储过程通常要比更改、测试和部署应用程序需要的时间和精力要少。

5、代码的重用：一个可以重用的存储过程可以应用到应用程序的多个位置。

6、增强安全性：通过对用户授权对存储过程的访问权限，它们可以提供对特定数据的访问；提高数据安全性，来防止SQL注入。

三、缺点：

1、如果需要对存储过程的输入输出参数做更改的话，还要更改程序。

2、可移植性差：因为存储过程将应用程序的业务处理绑定到数据库中，以此使用存储过程来处理业务逻辑限制了应用程序的可移植性。

四、创建存储过程

--1、简单的存储过程

create or replace procedure procedure_test

(p_id in varchar,p_status out varchar) --p_id为输入参数,p_status为输出参数

as

t_name varchar2(20);

t_count number:=0;

begin

select votetitle,vatesum into t_name,t_count from votemasterwhere id=p_id; --注意：此处没有：来赋值

if t_count <=0 then

p_status:= t_name||':差';

elsif t_count >0 and t_count <3then

p_status:= t_name||':良好';

else

p_status:= t_name||':优秀';

end if;

end;

--执行

declare

out_param varchar2(50);

begin

procedure_test('1',out_param);

dbms_output.put_line(out_param);

end;

--2、带游标的存储过程

create or replace procedure procedure_cursor_test

(p_id in varchar2,p_status out varchar2)

as

vote votemaster%rowtype; --声明一个对象(votemaster)类型的对象

cursor my_cur is select * from votemaster; --声明一个游标并填充数据

begin

open my_cur; --打开游标

loop

fetch my_cur into vote ; --循环游标，并放入对象

exit when my_cur%notfound; --如果没有数据，则直接exit

if vote.id=p_id then

p_status := vote.votetitle||'：'||vote.vatesum;

--如果想终止循环，可以直接exit;

end if;

end loop;

close my_cur; --关闭游标

end;

--执行

declare

out_param varchar2(50);

begin

procedure_cursor_test('1',out_param);

dbms_output.put_line(out_param);

end;

 

 

 

五、程序包

 

官方文档地址：http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/packages.htm#i2412

Understanding ThePackage Body

The package body contains the implementation ofevery cursor and subprogram declared in the package spec.Subprograms defined in a package body are accessible outside thepackage only if their specs also appear in the package spec.If a subprogram spec is not included in the package spec,that subprogram can only be called by other subprograms in the samepackage. A package body must be in the same schema as the packagespec.

To match subprogram specs and bodies, PL/SQL does atoken-by-token comparison of their headers. Except for white space,the headers must match word for word. Otherwise, PL/SQL raises anexception, as Example 9-2 shows.

Example 9-2 Matching Package Specifications andBodies

CREATE PACKAGE emp_bonus AS
   PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE);
END emp_bonus;
/
CREATE PACKAGE BODY emp_bonus AS
-- the following parameter declaration raises an exception 
-- because 'DATE' does not match employees.hire_date%TYPE
-- PROCEDURE calc_bonus (date_hired DATE) IS
-- the following is correct because there is an exact match
   PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE) IS
   BEGIN
     DBMS_OUTPUT.PUT_LINE('Employees hired on ' || date_hired || ' get bonus.');
   END;
END emp_bonus;
/

The package body can also contain private declarations,which define types and items necessary for the internal workings ofthe package. The scope of these declarations is local to thepackage body. Therefore, the declared types and items areinaccessible except from within the package body. Unlike apackage spec, the declarative part of a package body can containsubprogram bodies.

Following the declarative part of a packagebody is the optional initialization part, which typically holdsstatements that initialize some of the variables previouslydeclared in the package.

The initialization part of a package plays a minor role because,unlike subprograms, a package cannot be called or passedparameters. As a result, the initialization part of a package isrun only once, the first time you reference the package.

Remember, if a package spec declares only types, constants,variables, exceptions, and call specs, the package body isunnecessary. However, the body can still be used to initializeitems declared in the package spec.

 

来源于10gConcepts的文档说明：

Better performance

An entire package is loaded into memory when a procedure within thepackage is
called for the first time. This load is completed in one operation,as opposed to the
separate loads required for standalone procedures. Therefore, whencalls to related
packaged procedures occur, no disk I/O is necessary to run thecompiled code
already in memory.
A package body can be replaced and recompiled withoutaffecting the
specification. As a result, schema objects that referencea package's constructs
(always through the specification) need not be recompiled unlessthe package
specification is also replaced. By using packages, unnecessaryrecompilations can
be minimized, resulting in less impact on overall databaseperformance.

specification--翻译为：规范。

 

 

1、程序包：包是一组相关过程、函数、变量、游标、常量等PL/SQL程序设计元素的组合。它具有面向对象程序设计语言的特点，是对这些PL/SQL程序设计元素的封装。包类似于C++或Java程序中的类，而变量相当于类中的成员变量，过程和函数相当于方法，把相关的模块归类成为包，可使开发人员利用面向对象的方法进行存储过程的开发，从而提高系统性能。与类相同，包中的程序元素也分为公用元素和私有元素两种，这两种元素的区别是他们允许访问的程序范围不同，即他们的作用域不同。公用元素不仅可以被包中的函数、过程调用，也可以被包外的PL/SQl块调用。而私有元素只能被该包内部的函数或过程调用。

2、使用程序包的优点：在PL/SQL设计中，使用包不仅可以使程序模块化，对外隐藏包内所使用的信息，而写程序包可以提高程序的运行效率。因为，当程序首次调用程序包内部的函数或过程时，Oracle将整个程序包调入内存，当再次调用程序包中的元素时，Oracle直接从内存中读取，而不需要进行磁盘的IO操作，从而使程序的执行效率提高。

3、一个程序包分为两部分组成：

(1)、包定义：包定义部分声明包内数据类型、变量、常量、游标、子程序和函数等元素，这些元素为包的共有元素。

(2)、包主体：包主题则定义了包定义部分的具体实现，在包主体中还可以声明和实现私有元素。

--包定义

create or replace package t_package

is

--定义过程

procedure append_proc(t varchar2,a out varchar2);

--过程的重载

procedure append_proc(t number,a out varchar2);

--定义函数

function append_fun(t varchar2) return varchar2;

end;

--包主题

create or replace package body t_package

is

v_t varchar2(30);

--私有成员函数

function private_fun(t varchar2) return varchar2is

begin

v_t := t||'hello';

return v_t;

end;

--实现过程

procedure append_proc(t varchar2,a out varchar2) is

begin

a := t||'hello';

end;

--过程的重载

procedure append_proc(t number,a out varchar2) is

begin

a := t||'hello';

end;

--实现函数

function append_fun(t varchar2)

return varchar2 is

begin

v_t := t||'hello';

return v_t;

end;

end;