Oracle存储过程与函数
CREATE PACKAGE package_name IS
Invalie_operation EXCEPTOIN;
PROCEDURE procedure_name_1 ( parameters & types);
…
END package_name; 一、过程(PROCEDURE )
过程是作为一个单独的程序编译到Oracle数据库模式中的。过程能够接收参数。在编译过程时，Create Procedure语句的过程标识符在数据字典中成为对象名。
过程结构如下：
CREATE OR REPLACE PROCEDURE 过程名 (可选参数) IS
声明部分
BEGIN
程序体
EXCEPTION
异常处理程序
END 过程名
过程并不一定具有参数。当创建的过程没有参数时，就不需要使用圆括号。当调用过程时空括号是可选的。
可以编码为IS或AS，两种语法都是合法的。
Sql代码 
PROCEDURE INSERT_TEMP IS (| AS)
尽管将过程名追加到END子句后是可选的，但强烈推荐这么做。
例如：
Sql代码
Create table temp(n number);
定义一个过程：
Sql代码
PROCEDURE INSERT_TEMP IS
BEGIN
INSER INTO TEMP (n) VALUES (0);
END INSERT_TEMP; 常见的过程样式是将IS、BEGIN、EXCEPTION和END对齐。这些关键字作用域内的全部代码都要进行缩进。
Sql代码
PROCEDURE PRINT_TEMP
IS
v_average NUMBER;
v_sum NUMBER;
BEGIN
SELECT AVG(N), SUM(N) INTO v_average,v_sum FROM TEMP;
dbms_output.put_line(‘Average:’ || v_average );
dbms_output.put_line(‘Sum:’ || v_sum);
END PRINT_TEMP; 单独的过程常会开发成为一个新包或者一个已有的包合并。将INSERT_TEMP合并到一个包中，仅需要进行下面简单的编辑工作：
1. 首先在ORACLE建立PACKAGE
Sql代码
CREATE OR REPLACE PACKAGE TEMP_OP IS
PROCEDURE INSERT_TEMP;
END TEMP_OP; 2. 建立PACKAGE BODY
Sql代码
CREATE OR REPLACE PACKAGE BODY TEMP_OP IS
PROCEDURE INSERT_TEMP IS
BEGIN
INSERT INTO temp(n) VALUES (0);
END INSERT_TEMP;
END TEMP_OP; 二、函数(FUNCTION)
包通常扮演API的角色，隐藏对象，提供对象上的操作。而函数常扮演对象状态信息的选择器。
设想一个要计算对象的某个属性值的函数。函数不是动作者，而是状态的计算值。所以应该用名词对函数进行命名。
FUNCTION student_status (可选参数) RETURN VARCHAR2 IS
声明部分
BEGIN
子程序体Program body
RETURN expression;
EXCEPTION
异常处理程序，其中应该包括一条RETURN语句
END student_status;参数是可选的，但是RETURN 语句却是必须具备的，FUNCTION语句必须包括一个RETURN 和类型。
说明：
1.声明部分
声明变量，需要返回的变量也是在这里声明的。函数必须具有返回值。如果函数的返回值是一个NUMBER，则该NUMBER变量就在这里声明。该变量应该出现在RETURN语句中。
2.子程序体
支持循环、if-then-else结构、case语句和declare-block 结构。程序体必须包括RETURN语句。
3.异常处理程序
可选的，可以编写用于特定类型错误的异常处理程序或者是通用的异常处理程序，确定异常处理程序中包含了RETURN语句。
例如：
Sql代码
CREATE OR REPLACE FUNCTION tomorrow RETURN DATE
IS
next_day DATE;
BEGIN
next_day := SYSDATE +1 ;
RETURN next_day;
END tomorrow; 不声明变量，可以直接简写为：
Sql代码
CREATE OR REPLACE FUNCTION tomorrow RETURN DATE IS
BEGIN
RETURN SYSDATE +1 ;
END tomorrow; 如果函数没有参数，则不要在函数定义时使用空括号。这规则同样适用于过程。
使用函数：
Sql代码
CREATE OR REPLACE sample
IS
today DATE;
BEGIN
today := tomorrow – 1;
dbms_output.put_line(tomorrow - 1);
END sample; 三、包(PACKAGE)
利用包提供这样一套机制：将较小的程序单元在逻辑上组合在一起。这种由过程到包的组合就是代码的模块化。包的使用意味着只需要管理更少的文件和更少的模块。对于程序员来说更容易做到模块的重用。
迁移过程包括将这些过程体分别复制到同一个包体中。过程接口定义成为包规范。最后可以通过添加新的过程和函数来加强包的整体功能。
完成包的合并后，首先编译包规范，然后编译包体。
例如：
Sql代码
PACKAGE application_name IS
PROCEDURE p1;
PROCEDURE p2;
END application_name;
PACKAGE BODY application_name IS
PROCEDURE p1 IS
BEGIN
PL/SQL code
END p1;
PROCEDURE p2 IS
BEGIN
PL/SQL code
END p2;
END application_name; 四、包规范
PL/SQL语言要求将一个程序集合的接口编译成为单一的程序单元。这个单元，也就是包规范。这只是定义了API接口。而应用逻辑的具体实现则包含在包体中。
包规范可以是一个单独的ASCII文本文件，能编译成单一程序单元。包体也可以是一个单独的ASCII文本文件。必须首先成功编译包规范，然后才能编译包体。可以把包规范和包体放入同一个文件中。
1.语法与格式
最基本的包规范语法是：
Sql代码
CREATE PACKAGE package_name IS
Type definitions for records, index-by tables, varrays, nested tables
Constants
Exceptions
Global variable declarations
PROCEDURE procedure_name_1 (parameters & types);
FUNCTION function_name_1 (parameters & types) RETURN type;
END package_name; 包规范对过程和函数出现的顺序没有要求。而且包规范中的每个子程序都必须有一个与之相对应的子程序体。
包规范可以声明数据类型，数据声明和异常。在包规范中声明的所有数据对象都是全局的。所以在包规范声明的变量只是那些作用域是全局的变量。
包体中的PROCEDURE语句必须与相应包规范中的PROCEDURE语句相匹配。包括子程序名称、参数名称、参数模式和参数类型等。这一要求同样适用于FUNCTION;
包规范可以声明异常。异常或者全部声明在规范开头，或者全部声明在规范结尾。例如：
Sql代码
处理异常的应用程序代码类似于：
Sql代码
BEGIN
other code, etc
package_name.procedure_name_1(parameters);
other code, etc
EXCEPTION
WHEN package_name.invalid_operation THEN do something;
END; 五、参数与模式
PL/SQL有三种模式：
1. IN（默认）
传给子程序的IN模式参数表明 了子程序只能将该参数作为一个常量来使用。这是只读的。作为IN模式的参数可以是一个文字表达式、常量声明或者变量声明。当参数为变量时，该模式提供了安全措施保证正确的程序调用。调用程序能够了解在完成调用后，该变量的值没有发生改变。
下面的过程不能编译，原因是对IN模式变量进行了写操作。
Sql代码
PROCEDURE print_next_value (
v_data IN INTEGER
)
BEGIN
v_data := v_data +1 ;--compile error
dbms_output.put_line(v_data);
dbms_output.put_line(v_data + 1); --compile correct
END; 2. IN OUT
能够通过这种模式传递的参数只能是变量类型，不允许为文字或者常量。前提是被调用的过程将会改变传递的内容。被调过程也能对其进行读写操作。
当查看一个具有IN OUT模式参数的过程时，要求调用程序在调用该过程时必须提供数据。这是IN OUT参数中IN部分的要求。
例如：
Sql代码
PROCEDURE change_data (
v_data IN OUT INTEGER
) IS
BGIN
for i in 1..10 loop
v_data := v_data +1;
end loop;
END change_data; 块调用
Sql代码
DECLARE
my_data INTEGER :=0;--不能为常量
BEGIN
change_data(my_data);
dbms_output.put_line(‘block print:’ || my_data);--10
END; 3. OUT
能够通过这种模式传递参数只能是变量类型。不允许为文字或者常量。在子程序中，一个OUT模式参数的初始值为NULL。使用OUT模式参数的目的在于传递关于接口的信息。调用过程不必为被调过程传递参数。被调过程完成对数据结构的读写操作。
例如：
Sql代码
PROCEDURE provide_data(
v_data OUT INTEGER
) IS
BEGIN
v_data := 100;
for i in 1..10 loop
v_data := v_data +1;
end loop;
END provide_data; 块中调用
Sql代码
DECLARE
my_data INTEGER :=0;
BEGIN
insert into temp values(my_data);--0
provide_data(my_data) ;
insert into temp values(my_data);--110
END; 六、函数与模式
函数常常用名词来命名，而过程则常用动词来命名。在所有应用程序中，绝大多数的函数参数都是IN 模式的。但是函数参数的模式可以是所有这3种模式。
下面展示了关于一个函数的设计，该函数返回数据和状态信息。对于这个接口，假定ARG——1是主键，用来精确确定需要获取的记录。参数next_rec是需要的数据。
Sql代码
FUNCTION next_rec( arg1 IN type, next_record OUT type)
RETURN BOOLEAN; 这种设计允许用户编写如下代码：
Sql代码
WHILE ( next_rec(arg1,my_record_structure) )
LOOP
process my_record_structure;
END LOOP; 可以通过下面的过程来代替函数:
Sql代码
PROCEDURE get_next_rec(
arg1 IN type,
next_record OUT type,
status OUT BOOLEAN
); 使用：
Sql代码
LOOP
get_next_rec(arg1,my_record_structure,status);
EXIT WHEN NOT status;
process my_record_structure;
END LOOP; 从块角度来看，没有什么大的不同。在概念上，函数是一个返回值为下一个记录的选择器，但实际上只是随意提供了关于获取操作的状态。过程的作用更像是一个服务，获取一条记录，并返回该记录和状态。
七、命名表示法与位置表示法
考虑到如下接口定义的过程：
Sql代码
PROCEDURE proc_name (arg1 mode and type, arg2 mode and type);
用户有两种语法可供选择。第一种是POSITIONAL表示法，第二种是NAMED表示法：
Sql代码
1.proc_name(variable_1,varialble_2);
2.proc_name(arg1 => variable_1,arg2 => variable_2); 形参名（Formal parameter name）指的是在过程或者函数接口定义中所使用的名称。对于前面的proc_name过程来说，它的形参名是ARG1和ARG2。
形参名应该是泛化的，而且应该表达出该参数的用途。
定义一过程：
Sql代码
PROCEDURE get_record(
file_id IN INTEGER,
Record_read OUT VARCHAR2
); 用户可以用两种不同格式来调用该过程。
位置表示法：
Sql代码
DECLARE
file_id INTEGER;
next_payroll_record VARCHAR2(100);
BEGIN
get_record(field_id,next_payroll_record);
END; 第二种命名表示法：
Sql代码
DECLARE
file_id INTEGER;
next_payroll_record VARCHAR2(100);
BEGIN
get_record(file_id=>file_id,
record_read=>next_payroll_record);
END; 适合命名表示法的情况：
(1) 如果所选定的变量名称不能充分表达它们的用途，则适合采用命名表示法。
(2) 如果子程序的编码要用到默认值而且只用到某些默认值，则适合采用命名表示法。
考虑如下合计薪水的过程。该子程序的第个参数都有默认值:
Sql代码
CREATE OR REPLACE FUNCTION aggregate_salary(
Monthly_base NUMBER :=10000,
No_of_months INTEGER :=12
) RETURN NUMBER
IS
BEGIN
return (monthly_base * no_of_months);
END; 调用块：
Sql代码
DECLARE
no_of_months INTEGER :=10;
aggregate NUMBER;
BEGIN
--salary for 10 months
aggregate := aggregate_salary(no_of_months=>no_of_months);
END; 八、默认参数
过程或者函数的规范可以为参数定义一个模式类型为IN或者IN OUT的默认值。下面给出了两种语法形式：
Sql代码
PROCEDURE name(argument mode datatype := a_default_value); PROCEDURE name(argument mode datatype DEFAULT a_default_value);
例如：
Sql代码
FUNCTION circle(radius IN NUMBER := 1) RETURN NUMBER IS
BEGIN
RETURN 3.14 * radius ** 2;
END;
FUNCTION circle(radius IN NUMBER DEFAULT 1) RETURN NUMBER IS
BEGIN
RETURN 3.14 * radius ** 2;
END; 当子程序包含多个默认的参数时，用户可以选择其中任何一个参数的默认值，只是可能需要采用命名表示法。
九、%TYPE
%TYPE语法用于声明一个变量，该变量的类型是从数据库表中某列的类型派生而来的。这种类型定义的语法如下所示：
Sql代码
Variable_name table_name.column_name%TYPE;
十、取结果集
Sql代码
create or replace package CQ_SJ_OA as
TYPE RESULTSET IS REF CURSOR;
PROCEDURE PW_CQSJOA_DW_LIST(
PN_PARENT_ID INTEGER, -- 父单位Id
P_RESULT OUT RESULTSET --子单位列表
);
end CQ_SJ_OA; Sql代码
create or replace package body CQ_SJ_OA as
PROCEDURE PW_CQSJOA_DW_LIST
(
PN_PARENT_ID INTEGER, --父单位
P_RESULT OUT RESULTSET --子单位列表
)
AS
BEGIN
OPEN P_RESULT FOR
SELECT * FROM MAG_COMPANY WHERE PARENT_ID = PN_PARENT_ID;
END;
end CQ_SJ_OA; 十一、PL/SQL中调试oracle存储过程
点击要调试的存储过程，右键选择TEST。如果需要查看变量，当然调试都需要。在右键菜单中选择Add debug information。start debugger(F9)开始我们的测试，Run（Ctrl+R) 随时在varible List中输入我们想查看的变量
其它：
Step into(Ctrl+N):单步调试，进入下一步
Step over(Ctrl+O)：结束该语句
Step out(Ctrl+T)：跳出该过程
Run to next exception：进入下一个断点Breakpoint
Toggle Breakpoint设置断点：光标指向一行，Ctrl+B;或鼠标点中该行的行号边缘。
