oracle存储过程的详细列子说明加分析

文章分类：数据库存储过程创建语法:

（1）无参

create or replace procedure 存储过程名as

变量1类型（值范围）；

变量2类型（值范围）；

Begin

Excepti on

End;

（2）带参

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;

Excepti on

When others the n

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 在等待资源时超时

例子：

1 create or replace procedure run byparmeters (isal in emp.sal%type,

sn ame out varchar,sjob in out varchar)

2 as ico unt nu mber;

3 begin

4 select coun t(*) into ico unt from emp where sal>isal and job=sjob;

5 if ico un t=1 the n

6 ....

9 else

10 ....

12 end i f;

13 exceptio n

14 whe n too_many_rows the n

15 DBMS OUTPUT.PUT LINE('返回值多于1 行');

16 whe n others the n

17 DBMS OUTPUT.PUT LINE('在RUNBYPARMETERS 过程中出错！');

18 en d;

过程调用

方式一

1 declare

2 realsal emp.sal%type;

3 realn ame varchar(40);

4 realjob varchar(40);

5 begin

6 realsal:=1100;

7 realn ame:=";

8 realjob:='CLERK：

9 run byparmeters(realsal,rea In ame,realjob); ---------- 必须按顺序

10 DBMS_OUTPUT.PUT_LINE(REALNAME『’||REALJOB);

11 END;

12

方式二

1 declare

2 realsal emp.sal%type;

3 realn ame varchar(40);

4 realjob varchar(40);

5 begin

6 realsal:=1100;

7 realn ame:=";

8 realjob:='CLERK';

9 run byparmeters(s name=>rea In ame,isal=>realsal,sjob=>realjob); ------ 指定值对应变量顺序可变

10 DBMS OUTPUT.PUT LINE(REALNAME『'||REALJOB);

11 END;

说明:

(1)使用%TYPE

在许多情况下，PL/SQL变量可以用来存储在数据库表中的数据。在这种情况下，变量应该

拥有与表列相同的类型。例如，students表的first_name列的类型为VARCHAR2(20),我们可以按照下述方式声明一个变量

DECLARE

v_FirstName VARCHAR2(20);

但是如果first_name列的定义改变了会发生什么(比如说表改变了，first_name现在的类型变为VARCHAR2(25) )?那就会导致所有使用这个列的PL/SQL代码都必须进行修改。如