Oracle入门-2存储过程

1.转自：http://log-cd.javaeye.com/blog/288585#

2.转自：http://www.cnblogs.com/chinafine/articles/1776094.html

1

一、过程(PROCEDURE )
   过程是作为一个单独的程序编译到Oracle数据库模式中的。过程能够接收参数。在编译过程时，Create Procedure语句的过程标识符在数据字典中成为对象名。
过程结构如下：
    CREATE  OR  REPLACE  PROCEDURE   过程名  (可选参数)   IS
          声明部分
    BEGIN
          程序体
    EXCEPTION
          异常处理程序
    END  过程名
过程的命名应该用动词。因为过程通常是执行某种动作，比如，更新数据库、写文件，或者发送消息。
过程并不一定具有参数。当创建的过程没有参数时，就不需要使用圆括号。当调用过程时空括号是可选的。
可以编码为IS或AS，两种语法都是合法的。

PROCEDURE INSERT_TEMP IS (| AS)

PROCEDURE INSERT_TEMP IS (| AS)

尽管将过程名追加到END子句后是可选的，但强烈推荐这么做。
例如：

Create table temp(n number);

Create table temp(n number);

定义一个过程：

PROCEDURE INSERT_TEMP IS
BEGIN
   INSER INTO TEMP (n) VALUES (0);
END INSERT_TEMP;

PROCEDURE INSERT_TEMP IS
BEGIN
   INSER INTO TEMP (n) VALUES (0);
END INSERT_TEMP;

    常见的过程样式是将IS、BEGIN、EXCEPTION和END对齐。这些关键字作用域内的全部代码都要进行缩进。

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;

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

CREATE  OR  REPLACE  PACKAGE  TEMP_OP  IS
       PROCEDURE  INSERT_TEMP;
END  TEMP_OP;

CREATE  OR  REPLACE  PACKAGE  TEMP_OP  IS
       PROCEDURE  INSERT_TEMP;
END  TEMP_OP;

2. 建立PACKAGE BODY

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;

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语句。
例如：

CREATE  OR  REPLACE  FUNCTION   tomorrow   RETURN   DATE
IS
     next_day  DATE;
BEGIN
     next_day := SYSDATE +1 ;
     RETURN  next_day;
END  tomorrow;

CREATE  OR  REPLACE  FUNCTION   tomorrow   RETURN   DATE 
IS
     next_day  DATE;  
BEGIN
	 next_day := SYSDATE +1 ;
     RETURN  next_day;
END  tomorrow;

不声明变量，可以直接简写为：

CREATE  OR  REPLACE  FUNCTION   tomorrow   RETURN   DATE  IS
BEGIN
     RETURN  SYSDATE +1 ;
END  tomorrow;

CREATE  OR  REPLACE  FUNCTION   tomorrow   RETURN   DATE  IS
BEGIN
	 RETURN  SYSDATE +1 ;
END  tomorrow;

如果函数没有参数，则不要在函数定义时使用空括号。这规则同样适用于过程。
使用函数：

CREATE  OR  REPLACE   sample
IS
    today   DATE;
BEGIN
    today := tomorrow – 1;
dbms_output.put_line(tomorrow - 1);
END sample;

CREATE  OR  REPLACE   sample
IS
    today   DATE;
BEGIN
    today := tomorrow – 1;
dbms_output.put_line(tomorrow - 1);
END sample;

三、包(PACKAGE)
    利用包提供这样一套机制：将较小的程序单元在逻辑上组合在一起。这种由过程到包的组合就是代码的模块化。包的使用意味着只需要管理更少的文件和更少的模块。对于程序员来说更容易做到模块的重用。
迁移过程包括将这些过程体分别复制到同一个包体中。过程接口定义成为包规范。最后可以通过添加新的过程和函数来加强包的整体功能。
完成包的合并后，首先编译包规范，然后编译包体。
例如：

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;

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.语法与格式
最基本的包规范语法是：

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;

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;
包规范可以声明异常。异常或者全部声明在规范开头，或者全部声明在规范结尾。例如：

CREATE  PACKAGE  package_name  IS
    Invalie_operation   EXCEPTOIN;
    PROCEDURE procedure_name_1 ( parameters  &  types);
    …
END   package_name;

CREATE  PACKAGE  package_name  IS
	Invalie_operation   EXCEPTOIN;
	PROCEDURE procedure_name_1 ( parameters  &  types);
	…
END   package_name;

处理异常的应用程序代码类似于：

BEGIN
    other  code, etc
    package_name.procedure_name_1(parameters);
         other  code, etc
EXCEPTION
    WHEN  package_name.invalid_operation  THEN  do something;
END;

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模式变量进行了写操作。

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;

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部分的要求。
例如：

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;

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;

块调用

DECLARE
    my_data  INTEGER :=0;--不能为常量
BEGIN
    change_data(my_data);
    dbms_output.put_line(‘block print:’  ||  my_data);--10
END;

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模式参数的目的在于传递关于接口的信息。调用过程不必为被调过程传递参数。被调过程完成对数据结构的读写操作。
例如：

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;

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;

块中调用

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;

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是需要的数据。

FUNCTION   next_rec( arg1   IN   type,  next_record  OUT   type)
RETURN BOOLEAN;

FUNCTION   next_rec( arg1   IN   type,  next_record  OUT   type)
RETURN BOOLEAN;

这种设计允许用户编写如下代码：

WHILE ( next_rec(arg1,my_record_structure) )
LOOP
    process  my_record_structure;
END  LOOP;

WHILE ( next_rec(arg1,my_record_structure) )
LOOP
	process  my_record_structure;
END  LOOP;

可以通过下面的过程来代替函数:

PROCEDURE  get_next_rec(
arg1         IN   type,
next_record  OUT  type,
status       OUT  BOOLEAN
);

PROCEDURE  get_next_rec(
arg1         IN   type,
next_record  OUT  type,
status       OUT  BOOLEAN
);

使用：

LOOP
    get_next_rec(arg1,my_record_structure,status);
    EXIT  WHEN  NOT  status;
    process   my_record_structure;
END   LOOP;

LOOP
	get_next_rec(arg1,my_record_structure,status);
    EXIT  WHEN  NOT  status;
	process   my_record_structure;
END   LOOP;

    从块角度来看，没有什么大的不同。在概念上，函数是一个返回值为下一个记录的选择器，但实际上只是随意提供了关于获取操作的状态。过程的作用更像是一个服务，获取一条记录，并返回该记录和状态。
七、命名表示法与位置表示法
考虑到如下接口定义的过程：

PROCEDURE  proc_name (arg1  mode  and type, arg2  mode  and  type);

PROCEDURE  proc_name (arg1  mode  and type, arg2  mode  and  type);

用户有两种语法可供选择。第一种是POSITIONAL表示法，第二种是NAMED表示法：

1.proc_name(variable_1,varialble_2);
2.proc_name(arg1 => variable_1,arg2 => variable_2);

1.proc_name(variable_1,varialble_2);
2.proc_name(arg1 => variable_1,arg2 => variable_2);

形参名（Formal parameter name）指的是在过程或者函数接口定义中所使用的名称。对于前面的proc_name过程来说，它的形参名是ARG1和ARG2。
形参名应该是泛化的，而且应该表达出该参数的用途。
定义一过程：

PROCEDURE  get_record(
    file_id      IN   INTEGER,
    Record_read  OUT  VARCHAR2
);

PROCEDURE  get_record(
	file_id      IN   INTEGER,
	Record_read  OUT  VARCHAR2
);

用户可以用两种不同格式来调用该过程。
位置表示法：

DECLARE
    file_id  INTEGER;
         next_payroll_record  VARCHAR2(100);
BEGIN
    get_record(field_id,next_payroll_record);
END;

DECLARE
	file_id  INTEGER;
         next_payroll_record  VARCHAR2(100);
BEGIN
	get_record(field_id,next_payroll_record);
END;

第二种命名表示法：

DECLARE
    file_id  INTEGER;
         next_payroll_record  VARCHAR2(100);
BEGIN
    get_record(file_id=>file_id,
                    record_read=>next_payroll_record);
END;

DECLARE
	file_id  INTEGER;
         next_payroll_record  VARCHAR2(100);
BEGIN
	get_record(file_id=>file_id,
                    record_read=>next_payroll_record);
END;

适合命名表示法的情况：
(1) 如果所选定的变量名称不能充分表达它们的用途，则适合采用命名表示法。
(2) 如果子程序的编码要用到默认值而且只用到某些默认值，则适合采用命名表示法。
考虑如下合计薪水的过程。该子程序的第个参数都有默认值:

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;

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;

调用块：

DECLARE
    no_of_months  INTEGER :=10;
    aggregate   NUMBER;
BEGIN
    --salary for 10 months
aggregate := aggregate_salary(no_of_months=>no_of_months);
END;

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的默认值。下面给出了两种语法形式：

PROCEDURE  name(argument   mode   datatype  :=  a_default_value);
PROCEDURE  name(argument   mode   datatype  DEFAULT  a_default_value);

PROCEDURE  name(argument   mode   datatype  :=  a_default_value);
PROCEDURE  name(argument   mode   datatype  DEFAULT  a_default_value);

例如：

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;

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语法用于声明一个变量，该变量的类型是从数据库表中某列的类型派生而来的。这种类型定义的语法如下所示：

Variable_name   table_name.column_name%TYPE;

Variable_name   table_name.column_name%TYPE;

十、取结果集

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;

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;

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;

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;或鼠标点中该行的行号边缘。

 

 

2.Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

Oracle存储过程可以有无参数存储过程和带参数存储过程。 
一、无参程序过程语法

1 create or replace procedure NoParPro
2 as  ;
3 begin
4 ;
5 exception     //存储过程异常
6     ;
7 end;
8 

        二、带参存储过程实例

 1 create or replace procedure queryempname(sfindno emp.empno%type) as
 2        sName emp.ename%type;
 3        sjob emp.job%type;
 4 begin
 5        ....
 7 exception
          ....
14 end;
15 

    三、 带参数存储过程含赋值方式

 1 create or replace procedure runbyparmeters  (isal in emp.sal%type,
                            sname out varchar,sjob in out varchar)
 2  as icount number;
 3  begin
 4       select count(*) into icount from emp where sal>isal and job=sjob;
 5       if icount=1 then
 6         ....
 9       else
10         ....
12       end if;
13  exception
14       when too_many_rows then
15       DBMS_OUTPUT.PUT_LINE('返回值多于1行');
16       when others then
17       DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');
18  end;
19 

  四、在Oracle中对存储过程的调用
  过程调用方式一

 1 declare
 2        realsal emp.sal%type;
 3        realname varchar(40);
 4        realjob varchar(40);
 5  begin   //存储过程调用开始
 6        realsal:=1100;
 7        realname:='';
 8        realjob:='CLERK';
 9        runbyparmeters(realsal,realname,realjob);     －－必须按顺序
10        DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);
11  END;  //过程调用结束
12 

  过程调用方式二

 1 declare
 2       realsal emp.sal%type;
 3       realname varchar(40);
 4       realjob varchar(40);
 5 begin    //过程调用开始
 6       realsal:=1100;
 7       realname:='';
 8       realjob:='CLERK';
 9       runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);  －－指定值对应变量顺序可变
10       DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);
11 END;  //过程调用结束
12