Oracle的存储过程
2021/10/15 2:15:41
本文主要是介绍Oracle的存储过程,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
一、什么是存储过程?
存储过程就是一组为了完成特定功能的SQL语句集,存储在数据库中;这样经过第一次编译后再次调用不需要再次编译,直接调用或者通过java掉用(就是个SQL语句集)
在Oracle中存储过程是procedure
优势:
1. 相比普通的sql语句,每次都要先编译在执行,相对而言存储过程效率更高
2. 降低网络流量(存储过程编译好后直接存在数据库中,远程调用时,不会传输大量的字符串类型的sql语句)
3. 复用性高:一次编译后,以后直接调用
4. 可维护性更高:修改比较容易
5. 安全性高:可以指定用户进行存储过程的调用
二、存储过程的创建方式:
2.1 无参
CREATE OR REPLACE PROCEDURE 存储过程名称 AS/IS 变量2 DATE; 变量3 NUMBER; BEGIN --要处理的业务逻辑 EXCEPTION --存储过程异常(可写可不写) END
2.2 有参
2.2.1 带参数的存储过程(输入参数:id ; 输出参数:name)
1 CREATE OR REPLACE PROCEDURE 存储过程名称(param1 student.id%TYPE) 2 AS/IS 3 name student.name%TYPE; 4 age number :=20; 5 BEGIN 6 --业务处理..... 7 END
上面脚本中,
第1行:param1 是参数,类型和student表id字段的类型一样。
第3行:声明变量name,类型是student表name字段的类型(同上)。
第4行:声明变量age,类型数数字,初始化为20
2.2.2 带参数的存储过程并且进行赋值
1 CREATE OR REPLACE PROCEDURE 存储过程名称( 2 s_no in varchar, 3 s_name out varchar, 4 s_age number) AS 5 total NUMBER := 0; 6 BEGIN 7 SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age; 8 dbms_output.put_line('符合该年龄的学生有'||total||'人'); 9 EXCEPTION 10 WHEN too_many_rows THEN 11 DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 12 END
上面脚本中:
其中参数IN表示输入参数,是参数的默认模式。
OUT表示返回值参数,类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值,表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值,也可以将某个值传出去
第7行:查询语句,把参数s_age作为过滤条件,INTO关键字,把查到的结果赋给total变量。
第8行:输出查询结果,在数据库中“||”用来连接字符串
第9—11行:做异常处理
三、存储过程的语法
3.1 将结果放入一个或多个变量中:
1 CREATE OR REPLACE PROCEDURE DEMO_CDD1 IS 2 s_name VARCHAR2; --学生名称 3 s_age NUMBER; --学生年龄 4 s_address VARCHAR2; --学生籍贯 5 BEGIN 6 --给单个变量赋值 7 SELECT student_address INTO s_address 8 FROM student where student_grade=100; 9 --给多个变量赋值 10 SELECT student_name,student_age INTO s_name,s_age 11 FROM student where student_grade=100; 12 --输出成绩为100分的那个学生信息 13 dbms_output.put_line('姓名:'||s_name||',年龄:'||s_age||',籍贯:'||s_address); 14 END
3.2 选择语句:
IF s_sex=1 THEN dbms_output.put_line('这个学生是男生'); END IF IF s_sex=1 THEN dbms_output.put_line('这个学生是男生'); ELSE dbms_output.put_line('这个学生是女生'); END IF
3.3 循环语句
1 -- 基本循环 2 LOOP 3 IF 表达式 THEN 4 EXIT; 5 END IF 6 END LOOP; 7 8 -- while循环 9 WHILE 表达式 LOOP 10 dbms_output.put_line('haha'); 11 END LOOP; 12 13 -- for循环 14 FOR a in 10 .. 20 LOOP 15 dbms_output.put_line('value of a: ' || a); 16 END LOOP;
练习:
有表student(s_no, s_name, s_age, s_grade),其中s_no-学号,也是主键,是从1开始向上排的(例如:第一个学生学号是1,第二个是2,一次类推);s_name-学生姓名;s_age-学生年龄;s_grade-年级;这张表的数据量有几千万甚至上亿。一个学年结束了,我要让这些学生全部升一年级,即,让s_grade字段加1。
这条sql,写出来如下:
update student set s_grade=s_grade+1
分析:
如果我们直接运行运行这条sql,因数据量太大会把数据库undo表空间撑爆,从而发生异常。那我们来写个存储过程,进行批量更新,我们每10万条提交一次。
CREATE OR REPLACE PROCEDURE process_student is total NUMBER := 0; i NUMBER := 0; BEGIN SELECT COUNT(1) INTO total FROM student; WHILE i<=total LOOP UPDATE student SET grade=grade+1 WHERE s_no=i; i := i + 1; IF i >= 100000 THEN COMMIT; END IF; END LOOP; dbms_output.put_line('finished!'); END;
这篇关于Oracle的存储过程的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-10-04el-table 开启定时器下,表格的选中状态会消失是什么原因-icode9专业技术文章分享
- 2024-10-03如何安装和初始化飞牛私有云 fnOS?-icode9专业技术文章分享
- 2024-10-03如何安装 App 并连接到飞牛 NAS?-icode9专业技术文章分享
- 2024-10-03如何安装飞牛 TV 并连接到影视服务器?-icode9专业技术文章分享
- 2024-10-03如何在PVE和ESXI上安装飞牛私有云 fnOS?-icode9专业技术文章分享
- 2024-10-03fnOS国产最强NAS安装系统异常情况处理-icode9专业技术文章分享
- 2024-10-03飞牛NAS如何创建存储空间?-icode9专业技术文章分享
- 2024-10-03fnOS国产最强NAS硬盘会自动休眠吗?-icode9专业技术文章分享
- 2024-10-03fnOS国产最强NAS如何安装飞牛影视和创建媒体库?-icode9专业技术文章分享
- 2024-10-03fnOS国产最强NAS如何为家人朋友开通影视账号?-icode9专业技术文章分享