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

最新推荐文章于 2024-09-09 08:52:02 发布

阿里高级程序

最新推荐文章于 2024-09-09 08:52:02 发布

阅读量1k

点赞数 28

分类专栏： 2024年程序员学习文章标签： oracle 数据库

本文链接：https://blog.csdn.net/m0_61331573/article/details/138048742

版权

2024年程序员学习专栏收录该内容

282 篇文章 0 订阅

订阅专栏

本文详细介绍了Oracle存储过程，包括存储过程中的游标使用、调用方式，如通过SQL命令行调用，以及创建语法和注意事项。文章还强调了在PL/SQL中使用IF判断、WHILE循环、变量赋值等基本语法，并提供了面试准备建议，鼓励开发者通过系统化学习提升技术能力。

摘要由CSDN通过智能技术生成

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Web前端全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上前端开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024c （备注前端）

正文

end if;
exception
when too_many_rows then
DBMS_OUTPUT.PUT_LINE(‘返回值多于1行’);
when others then
DBMS_OUTPUT.PUT_LINE(‘在RUNBYPARMETERS过程中出错！’);
end;

其中参数IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去。

2.4 存储过程中游标定义使用

as //定义(游标一个可以遍历的结果集)
CURSOR cur_1 IS
SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,
SUM(usd_amt)/10000 usd_amt_sn
FROM BGD_AREA_CM_M_BASE_T
WHERE ym >= vs_ym_sn_beg
AND ym <= vs_ym_sn_end
GROUP BY area_code,CMCODE;

begin //执行（常用For语句遍历游标）
FOR rec IN cur_1 LOOP
UPDATE xxxxxxxxxxx_T
SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn
WHERE area_code = rec.area_code
AND CMCODE = rec.CMCODE
AND ym = is_ym;
END LOOP;

2.5 游标的定义

–显示cursor的处理
declare
—声明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;

五、注意事项

存储过程参数不带取值范围，in表示传入，out表示输出;
变量带取值范围，后面接分号;
在判断语句前最好先用count(*)函数判断是否存在该条操作记录;
用select … into … 给变量赋值;
在代码中抛异常用 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;
跳槽是每个人的职业生涯中都要经历的过程，不论你是搜索到的这篇文章还是无意中浏览到的这篇文章，希望你没有白白浪费停留在这里的时间，能给你接下来或者以后的笔试面试带来一些帮助。

也许是互联网未来10年中最好的一年。WINTER IS COMING。但是如果你不真正的自己去尝试尝试，你永远不知道市面上的行情如何。这次找工作下来，我自身感觉市场并没有那么可怕，也拿到了几个大厂的offer。在此进行一个总结，给自己，也希望能帮助到需要的同学。

面试准备

面试准备根据每个人掌握的知识不同，准备的时间也不一样。现在对于前端岗位，以前也许不是很重视算法这块，但是现在很多公司也都会考。建议大家平时有空的时候多刷刷leetcode。算法的准备时间比较长，是一个长期的过程。需要在掌握了大部分前端基础知识的情况下，再有针对性的去复习算法。面试的时候算法能做出来肯定加分，但做不出来也不会一票否决，面试官也会给你提供一些思路。

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024c （备注前端）