ORACLE进阶（七）存储过程详解_oracle存储过程实例详解

—声明cursor,创建和命名一个sql工作区
cursor cursor_name is
select real_name from account_hcz;
v_realname varchar2(20);
begin
open cursor_name;—打开cursor,执行sql语句产生的结果集
fetch cursor_name into v_realname;–提取cursor,提取结果集中的记录
dbms_output.put_line(v_realname);
close cursor_name;–关闭cursor
end;

### 三、调用存储过程


#### 3.1 过程调用方式一

declare
realsal emp.sal%type;
realname varchar(40);
realjob varchar(40);
begin //过程调用开始
realsal:=1100;
realname:=‘’;
realjob:=‘CLERK’;
runbyparmeters(realsal,realname,realjob);－－必须按顺序
DBMS_OUTPUT.PUT_LINE(REALNAME||’ '||REALJOB);
END; //过程调用结束

#### 3.2 过程调用方式二

declare
realsal emp.sal%type;
realname varchar(40);
realjob varchar(40);
begin //过程调用开始
realsal:=1100;
realname:=‘’;
realjob:=‘CLERK’;
－－指定值对应变量顺序可变
runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);
DBMS_OUTPUT.PUT_LINE(REALNAME||’ '||REALJOB);
END; //过程调用结束

#### 3.3 过程调用方式三（SQL命令行方式下）

SQL>exec proc_emp(‘参数1’,‘参数2’);//无返回值过程调用

SQL>var vsal number
SQL> exec proc_emp (‘参数1’,:vsal);// 有返回值过程调用

或者：

call proc_emp (‘参数1’,:vsal);// 有返回值过程调用

### 四、存储过程创建语法

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(‘打印信息’);
Elsif (判断条件) 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`+异常名;
> 
> 
> 


#### 5.1 已命名的异常


命名的系统异常 产生原因




---


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


### 六、基本语法


#### 6.1 基本结构

CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
–执行体
END 存储过程名字;

#### 6.2 SELECT INTO STATEMENT


将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出`NO_DATA_FOUND`)  
 例子：

BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;

#### 6.3 IF 判断

IF V_TEST = 1 THEN
BEGIN
do something
END;
END IF;

#### 6.4 while 循环

WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;

#### 6.5 变量赋值

V_TEST := 123;

#### 6.6 用for in 使用cursor

IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;

#### 6.7 带参数的cursor

CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(变量值);
FETCH C_USER INTO V_NAME;
EXIT WHEN FETCH C_USER%NOTFOUND;
CLOSE C_USER;

#### 6.8 用pl/sql developer debug


连接数据库后建立一个Test WINDOW,在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试


### 七、关于oracle存储过程的若干问题备忘


1. 在oracle中，数据表别名不能加as，如：

 

select a.appname from appinfo a;-- 正确
select a.appname from appinfo as a;-- 错误

2. 在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。

 

select af.keynode into kn from APPFOUNDATION af
where af.appid=aid and af.foundationid=fid;-- 有into，正确编译
select af.keynode from APPFOUNDATION af
where af.appid=aid and af.foundationid=fid;-- 没有into，编译报错，提示：Compilation Error: PLS-00428: an INTO clause is expected in this SELECT statement

3. 在利用select…into…语法时，必须先确保数据库中有该条记录，否则会报出”no data found”异常。  
 可以在该语法之前，先利用select count(\*) from 查看数据库中是否存在该记录，如果存在，再利用select…into…
4. 在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错

 

–正确
select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;
–错误
select af.keynode into kn from APPFOUNDATION af
where af.appid=appid and af.foundationid=foundationid;
– 运行阶段报错，提示ORA-01422:exact fetch returns more than requested number of rows

### 最后

全网独播-价值千万金融项目前端架构实战

![](https://imgconvert.csdnimg.cn/aHR0cHM6Ly9waWMxLnpoaW1nLmNvbS84MC92Mi1iMzExOWVhYzM3ZjkyNWE3NjMyNTFkNWE5ZWY5Njc3MF9oZC5qcGc?x-oss-process=image/format,png)



从两道网易面试题-分析JavaScript底层机制

![](https://imgconvert.csdnimg.cn/aHR0cHM6Ly9waWMyLnpoaW1nLmNvbS84MC92Mi0xMGE3MzBlOTc1ZmVjOTFjMDcwOTE1OWIwMTdjNTliMV9oZC5qcGc?x-oss-process=image/format,png)



RESTful架构在Nodejs下的最佳实践

**[开源分享：【大厂前端面试题解析+核心总结学习笔记+真实项目实战+最新讲解视频】](https://bbs.csdn.net/topics/618166371)**

![](https://imgconvert.csdnimg.cn/aHR0cHM6Ly9waWMyLnpoaW1nLmNvbS84MC92Mi1hY2UyZjVjNjQ1YjhkMTE1MzA4YzcyZDM1ZGNkZGYzNV9oZC5qcGc?x-oss-process=image/format,png)

一线互联网企业如何初始化项目-做一个自己的vue-cli

![](https://imgconvert.csdnimg.cn/aHR0cHM6Ly9waWMxLnpoaW1nLmNvbS84MC92Mi04MGU5MWQ0NGY3NTUzZTA5OTJhOWEzN2Y2OGFhYTAwNF9oZC5qcGc?x-oss-process=image/format,png)



思维无价，看我用Nodejs实现MVC

![](https://imgconvert.csdnimg.cn/aHR0cHM6Ly9waWM0LnpoaW1nLmNvbS84MC92Mi0yZGI4MTZiY2JlODkwNjBiMDY1NWIyODdlM2Y4NWVlM19oZC5qcGc?x-oss-process=image/format,png)



代码优雅的秘诀-用观察者模式深度解耦模块

![](https://imgconvert.csdnimg.cn/aHR0cHM6Ly9waWMxLnpoaW1nLmNvbS84MC92Mi04YTY0YTU3YTdlNDFmZDc3OTRiZWYzNjVkYjNlYzQxMF9oZC5qcGc?x-oss-process=image/format,png)

前端高级实战，如何封装属于自己的JS库

![](https://imgconvert.csdnimg.cn/aHR0cHM6Ly9waWM0LnpoaW1nLmNvbS84MC92Mi1lOTdlMTZkMWYyNDZhNjUxZjA4ZThlZjdjZjRhZWRjZl9oZC5qcGc?x-oss-process=image/format,png)

VUE组件库级组件封装-高复用弹窗组件

![](https://imgconvert.csdnimg.cn/aHR0cHM6Ly9waWMyLnpoaW1nLmNvbS84MC92Mi00NWJjMGI2OWU4YzY2YTcxYzBkNWFiNjczZTkzM2MyZF9oZC5qcGc?x-oss-process=image/format,png)