Oracle存储过程

 

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 

 

 

 

 

存储过程创建语法：

create or replace procedure 存储过程名（param1 in type，param2 out type）

as

变量1 类型（值范围）;

变量2 类型（值范围）;

Begin

    Select count(*) into 变量1 from 表A where列名=param1；

    If (判断条件) then

       Select 列名 into 变量2 from 表A where列名=param1；

       Dbms_output。Put_line(‘打印信息’);

    Elseif (判断条件) then

       Dbms_output。Put_line(‘打印信息’);

    Else

       Raise 异常名（NO_DATA_FOUND）;

    End if;

Exception

    When others then

       Rollback;

End;

 

注意事项：

1、存储过程参数不带取值范围，in表示传入，out表示输出

2、变量带取值范围，后面接分号

3、在判断语句前最好先用count（*）函数判断是否存在该条操作记录

4、用select 。。。into。。。给变量赋值

5、在代码中抛异常用 raise+异常名

 

 

 

 

以命名的异常

命名的系统异常                     产生原因

ACCESS_INTO_NULL                   未定义对象

CASE_NOT_FOUND                     CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时

COLLECTION_IS_NULL                集合元素未初始化

CURSER_ALREADY_OPEN               游标已经打开

DUP_VAL_ON_INDEX                  唯一索引对应的列上有重复的值

INVALID_CURSOR                    在不合法的游标上进行操作

INVALID_NUMBER                    内嵌的 SQL 语句不能将字符转换为数字

NO_DATA_FOUND                     使用select into未返回行，或应用索引表未初始化 

TOO_MANY_ROWS                     执行 select into 时，结果集超过一行

ZERO_DIVIDE                       除数为 0

SUBSCRIPT_BEYOND_COUNT            元素下标超过嵌套表或 VARRAY 的最大值

SUBSCRIPT_OUTSIDE_LIMIT           使用嵌套表或 VARRAY 时，将下标指定为负数

VALUE_ERROR                       赋值时，变量长度不足以容纳实际数据

LOGIN_DENIED                      PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码

NOT_LOGGED_ON                     PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据

PROGRAM_ERROR                     PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包

ROWTYPE_MISMATCH                  宿主游标变量与PL/SQL游标变量的返回类型不兼容

SELF_IS_NULL                      使用对象类型时，在 null 对象上调用对象方法

STORAGE_ERROR                     运行 PL/SQL 时，超出内存空间

SYS_INVALID_ID                    无效的 ROWID 字符串

TIMEOUT_ON_RESOURCE               Oracle 在等待资源时超时