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)
  • 17
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值