存储在数据库中的PL/SQL程序分为两种，procedures 、functions。

functions有一个返回值，procedures 没有。

functions由于有返回值，所以他们可以被用作表达式，但是procedures 不可以。

一个包Packages 由两部分组成，包声明package specification部分和包体package body。

简单的创建一个包：

CREATE OR REPLACE PACKAGE my_pkg AS

PROCEDURE my_proc(arg1 IN VARCHAR2);

FUNCTION my_func(arg1 IN NUMBER) RETURN VARCHAR2;

END my_pkg;

CREATE OR REPLACE PACKAGE BODY my_pkg AS

FUNCTION my_private_func(arg1 IN NUMBER) RETURN VARCHAR2 IS

return_val VARCHAR2(20);

BEGIN

SELECT col1 INTO return_val

FROM tab2

WHERE col2 = arg1;

RETURN return_val;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN 'NOT FOUND';

END my_private_func;

PROCEDURE my_proc(arg1 IN VARCHAR2) IS

BEGIN

UPDATE tab1 SET col1 = col1 + 1

WHERE col2 = arg1;

END my_proc;

FUNCTION my_func(arg1 IN NUMBER) RETURN VARCHAR2 IS

BEGIN

RETURN my_private_func(arg1);

END my_func;

END my_pkg; 

这样就可以创建一个包，其中包含一个function一个procedure以及一个private function。

 

因为function可以作为一个表达式，所以可以使用在：

select子句

where子句

group by和having子句

order by子句

START WITH/CONNECT BY 子句（分等级查询） 

from子句

 

Deterministic Functions--输入相同值，可以得到相同的返回结果时，可以将函数声明

 

为DETERMINISTIC，oracle会进行一些优化，将返回值写入内存，避免函数的重新运算。

CREATE OR REPLACE PACKAGE BODY pkg_util AS 

  FUNCTION translate_date(dt IN DATE) RETURN NUMBER DETERMINISTIC;

  FUNCTION translate_date(dt IN NUMBER) RETURN DATE DETERMINISTIC;

END pkg_util;

 

从sql中调用pl/sql的限制 纯净度--Purity Level

 

CREATE OR REPLACE PACKAGE my_pkg AS

FUNCTION my_func(arg1 IN NUMBER) RETURN VARCHAR2;

PRAGMA RESTRICT_REFERENCES(my_func, RNPS, WNPS, WNDS);

END my_pkg;

 

RESTRICT_REFERENCES

总的来说，它是一个程序辅助检验码，检查子程序的纯度（PURITY），

帮助检验子程序是否有违反规则的地方。

一般用在函数上，但当函数调用过程时，也要作相应的设置检查。这是为了避免当在

DML语句上调用函数时正常执行不至于产生错误。

 

语法，PRAGMA RESTRICT_REFERENCES(function_name | default , )RNDS, WNDS, RNPS, WNPS) | , TRUST);

     RNDS，WNDS，RNPS，WNPS可以同时指定。但当TRUST指定是，其它的被忽略。

     DEFAUT是指作用在该程序包上的所有子程序，函数。

     RNDS（Read No Database State），规定子程序不能读取任何的数据库状态信息。(即不会查询数据库的任何表，包括DUAL虚表）

     RNPS（Read No Package State），规定子程序不能读取任何程序包的状态信息，如变量等。

     WNDS（Write No Database State），规定子程序不能向数据库写入任何信息。（即不能修改数据库表）

     WNPS（Write No Package State），规定子程序不能向程序包写入任何信息。（即不能修改程序包变量的值）

     TRUST，指出子程序是可以相信的不会违反一个或多个规则。这个选项是需要的当用C或JAVA写的

     函数通过PL/SQL调用时，因为PL/SQL在运行是对它们不能检查。

     这个编译指令只能在程序包及对象类型说明部分指定。

 

除了WNDS限制外，还有一些限制条件：

function不能使用COMMIT或者ROLLBACK结束事务。

function不能创建savepoints

function不能使用ALTER SYSTEM或者ALTER SESSION语句。

所有参数类型，包括返回值，必须是标准sql类型：varchar2，number和date。pl/sql类型不允许使用。

所有参数必须是in。out和in out不允许。