机械网站建设栏目内容,中关村手机网站建设,seo网站营销推广,wordpress分类图标存储过程(特定功能的 SQL 语句集)
一组为了完成特定功能的 SQL 语句集#xff0c;存储在数据库中#xff0c;经过第一次编译后再次调用不需要再次编译#xff0c;用户通过指定存储过程的名字并给出参数#xff08;如果该存储过程带有参数#xff09;来执行它。存储过程是…存储过程(特定功能的 SQL 语句集)
一组为了完成特定功能的 SQL 语句集存储在数据库中经过第一次编译后再次调用不需要再次编译用户通过指定存储过程的名字并给出参数如果该存储过程带有参数来执行它。存储过程是数据库中的一个重要对象。 一.什么是存储过程
存储过程百度百科上是这样解释的存储过程Stored Procedure是在大型数据库系统中一组为了完成特定功能的SQL 语句集存储在数据库中经过第一次编译后再次调用不需要再次编译用户通过指定存储过程的名字并给出参数如果该存储过程带有参数来调用存储过程。
简单的说就是专门干一件事一段sql语句。
可以由数据库自己去调用也可以由java程序去调用。
在oracle数据库中存储过程是procedure。
二.为什么要写存储过程
1.效率高
存储过程编译一次后就会存到数据库每次调用时都直接执行。而普通的sql语句我们要保存到其他地方例如记事本 上都要先分析编译才会执行。所以想对而言存储过程效率更高。
2.降低网络流量
存储过程编译好会放在数据库我们在远程调用时不会传输大量的字符串类型的sql语句。
3.复用性高
存储过程往往是针对一个特定的功能编写的当再需要完成这个特定的功能时可以再次调用该存储过程。
4.可维护性高
当功能要求发生小的变化时修改之前的存储过程比较容易花费精力少。
5.安全性高
完成某个特定功能的存储过程一般只有特定的用户可以使用具有使用身份限制更安全。
三.存储过程基础
1.存储过程结构
(1).基本结构
Oracle存储过程包含三部分过程声明执行过程部分存储过程异常可写可不写要增强脚本的容错性和调试的方便性那就写上异常处理
(2).无参存储过程 这里的as和is一样任选一个在这里没有区别其中demo是存储过程名称。
(3).有参存储过程
a.带参数的存储过程 上面脚本中
第1行param1 是参数类型和student表id字段的类型一样。
第3行声明变量name类型是student表name字段的类型同上。
第4行声明变量age类型数数字初始化为20
b.带参数的存储过程并且进行赋值 上面脚本中
其中参数IN表示输入参数是参数的默认模式。
OUT表示返回值参数类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值也可以将某个值传出去
第7行查询语句把参数s_age作为过滤条件INTO关键字把查到的结果赋给total变量。
第8行输出查询结果在数据库中“||”用来连接字符串
第9—11行做异常处理————————————————
2.存储过程语法
(1).运算符
这里smn是变量类型是number (2).SELECT INTO STATEMENT语句
该语句将select到的结果赋值给一个或多个变量例如 上面脚本中
存储过程名称DEMO_CDD1, student是学生表要求查出成绩为100分的那个学生的姓名年龄籍贯
(3).游标
Oracle会创建一个存储区域被称为上下文区域用于处理SQL语句其中包含需要处理的语句例如所有的信息行数处理等等。
游标是指向这一上下文的区域。 PL/SQL通过控制光标在上下文区域。游标持有的行(一个或多个)由SQL语句返回。行集合光标保持的被称为活动集合。
a.下表是常用的游标属性 b.使用游标
声明游标定义游标的名称和相关的SELECT语句
CURSOR cur_cdd IS SELECT s_id, s_name FROM student;
打开游标游标分配内存使得它准备取的SQL语句转换成它返回的行
OPEN cur_cdd;
抓取游标中的数据可用LIMIT关键字来限制条数如果没有默认每次抓取一条
FETCH cur_cdd INTO id, name ;
关闭游标来释放分配的内存:
CLOSE cur_cdd;
四.存储过程进阶
在上面的案例中我们的存储过程处理完所有数据要多长时间呢事实我没有等到它执行完在我可接受的时间范围内它没有完成。那么对于处理这种千万级数据量的情况存储过程是不是束手无策呢答案是否定的接下来我们看看其他绝招。
我们先来分析下执行过程的执行过程一个存储过程编译后在一条语句一条语句的执行时如果遇到pl/sql语句就拿去给pl/sql引擎执行如果遇到sql语句就送到sql引擎执行然后把执行结果再返回给pl/sql引擎。遇到一个大数据量的更新则执行焦点正在执行的状态处于ACTIVE会不断的来回切换。
Pl/SQL与SQL引擎之间的通信则称之为上下文切换过多的上下文切换将带来过量的性能负载。最终导致效率降低处理速度缓慢。
从Oracle8i开始PL/SQL引入了两个新的数据操纵语句FORALL、BUIK COLLECT这些语句大大滴减少了上下文切换次数一次切换多次执行同时提高DML性能因此运用了这些语句的存储过程在处理大量数据时速度简直和飞一样。
1.BUIK COLLECT
Oracle8i中首次引入了Bulk Collect特性Bulk Collect会能进行批量检索会将检索结果结果一次性绑定到一个集合变量中而不是通过游标cursor一条一条的检索处理。可以在SELECT INTO、FETCH INTO、RETURNING INTO语句中使用BULK COLLECT接下来我们一起看看这些语句中是如何使用BULK COLLECT的。
(2).FETCH INTO
从一个集合中抓取一部分数据赋值给一个集合变量。
(3).RETURNING
BULK COLLECT除了与SELECTFETCH进行批量绑定之外还可以与INSERTDELETEUPDATE语句结合使用可以返回这些DML语句执行后所影响的记录内容某些字段。
(4).注意事项
a.不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。
b.只能在服务器端的程序中使用BULK COLLECT如果在客户端使用就会产生一个不支持这个特性的错误。
c.BULK COLLECT INTO 的目标对象必须是集合类型。
d.复合目标(如对象类型)不能在RETURNING INTO 子句中使用。
e.如果有多个隐式的数据类型转换的情况存在多重复合目标就不能在BULK COLLECT INTO 子句中使用。
f.如果有一个隐式的数据类型转换复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。
五.存储过程优化思路 尽量利用一些 sql 语句来替代一些小循环例如聚合函数求平均函数等。 中间结果存放于临时表加索引。 少使用游标。sql 是个集合语言对于集合运算具有较高性能。而 cursors 是过程运算。比如对一个 100 万行的数据进行查询。游标需要读表 100 万次而不使用游标则只需要少量几次读取。 事务越短越好。sqlserver 支持并发操作。如果事务过多过长或者隔离级别过高都会造成并发操作的阻塞死锁。导致查询极慢cpu 占用率极地。 使用 try-catch 处理错误异常。 查找语句尽量不要放在循环内。
