一、存储过程
 1.定义：
 一般在大型的数据库系统中，一组为了完成特定功能的sql语句集，存储在数据库中，经过第一次编译后，再次调用时就不需要再次编译了，用户只需要指定存储过程名称并给定参数就可以执行完成任务。
 在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭（连接和关闭是很耗费资源的），需要对数据库进行多次I/O读写，性能比较低。如果把这些放到存储过程中，就可以实现连接关闭一次数据库来实现业务，大大提高了效率。
 2.语法：
 （1）创建存储过程
 create procedure 存储过程名（参数）
 begin
 sql语句…
 sql语句…
 end；
 
   例：
   create procedure pro_b()
   begin
        select * from t_man;
   end;
   注意：sql语句是以“;”结尾的，这样“end;”就不会执行，造成存储过程不完整，需要重新定义结束符，一般
      使用delimiter定义，如：delimiter #，表示遇到#算结束。
   create procedure pro_b()
   begin
        select * from t_man;
   end#
 
（2）调用存储过程
 call pro_b() #
 （3）删除存储过程
 drop procedure (if exists) 存储过程名；
 3.带参数的存储过程：in out inout
 （1）in类型：in可以省略不写，默认是in类型，即入参
 例：给存储过程一个学号，存储过程显示学生信息
 create procedure pro_c(in id int)
 begin
 select * from t_student where sid=id;
 end#
 
   调用：call pro_c(12)#
   例：给存储过程一个班级编号和英文名，查询这些学生信息（or）（多参数）
   create procedure pro_d(in class int,in ename varchar(20))
   begin 
        select * from t_student where sclass=class or sename=ename;
   end;
   注意：存储过程中limit后面不能使用参数，应写明具体值，下面写法错误：
   create procedure pro_e(in n int,in m int)
   begin 
        select * from t_student limit m,n;
   end;
（2）out类型
       例：写一个存储过程，返回3班的最高分
   create procedure pro_r1(out x int)
   begin
        select max(sscore) into x from t_student where sclass=3;
   end;
   注意：执行后最高分放到变量x里，调用out存储过程前，需要先定义一个变量：set @变量名=值；
         call 存储过程名(@变量名)；
		 
	例：给存储过程一个系编号，获取对应的系名称；
	create procedure pro_r2(in x int,out name varchar(20))
	begin 
	     select dname into name from t_dept where dclass=x;
    end#
	执行过程：
    ①执行存储过程；
	②定义输出变量：set @n=‘abc’#
	③调用存储过程：call pro_r2(3,@n)#
	④显示@n结果：select @n #
（3）inout类型：入参和出参使用同一个参数
           例：传入一个班级编号，返回这个班级的最高分
     create procedure pro_r3(inout x int)
     begin  
         select max(sscore) into x from t_student where sclass=x;
      end#
       执行过程：
       ①执行存储过程；
       ②定义输出变量：set @a=1 #
       ③调用存储过程：call pro_r2(3,@a) #
       ④显示@n结果：select @a #
 
二、PLSQL编程：
 1.定义：PLSQL(Procedure Language SQL),是Oracle对sql语句的过程化扩展，在SQL语言中添加过程处理语句(如分支、循环等)，使SQL语言具有过程处理能力。
 PLSQL语言的基本结构： 
 
 2.变量：常见变量分为两大类
 ①普通数据类型：char、varchar2、date、number、boolean、long；
 ②特殊变量类型：引用型变量、记录型变量
 声明变量的方式：
 变量名 变量类型（变量长度）
 name varchar2(20)；
 (1)普通变量：赋值的方式有两种
 ①直接赋值语句：使用：= 赋值
 ②语句赋值：使用select…into…赋值，即select 值 into 变量
 例：
 
 (2)引用型变量：变量的类型和长度取决于表中字段的类型和长度。通过
 表名.列名%TYPE指定变量的类型和长度，例如：name emp.ename%TYPE
 例：
 
 (3)记录型变量：接受表中的一整行记录，相当于Java中的一个对象
 语法：变量名称 表名%ROWTYPE,例如：v_emp emp%ROWTYPE
 例：
 
 3.流程控制：
 (1)条件分支：
 语法：
 BEGIN
 IF 条件1 THEN 执行1
 ELSIF 条件2 THEN 执行2
 ELSE 执行3
 END IF
 END;
 例：
 
 (2)循环：在Oracle中有3种循环方式，这里介绍：loop循环
 语法：
 BEGIN
 LOOP
 EXIT WHEN 退出循环条件
 END LOOP;
 END;
 例：
 
 4.游标：
 (1)定义：用于临时存储一个查询返回的多行数据（结果集，类似于Java的 JDBC连接返回的ResultSet集合），通过遍历游标逐行访问处理该结果集的数据 。
 (2)使用方式：声明—>打开—>读取—>关闭
 (3)语法：
 ●游标声明：
 CURSOR 游标名[(参数列表)] IS 查询语句；
 ●游标打开：
 OPEN 游标名；
 ●游标取值：
 FETCH 游标名 INTO 变量列表；
 ●游标关闭：
 CLOSE 游标名；
 (4)游标的属性：
 
 (5)无参游标：
 例：
 
 
 (6)有参游标：
 
 
 存储过程：
 
 三、在Java程序中调用存储过程（JDBC连接中）：
 
 

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字给出参数来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好 的数据库应用程序都应该用到存储过程。
 优点：
 （1）允许模块化程序设计，就是说只要创建一次过程，以后在程序中就可以调用该过程任意次。
 （2）允许更快执行，如果某操作需要执行大量sql语句或重复执行，存储过程比sql语句执行的要快。
 （3）减少网络流量，例如需要数百行的sql代码的操作有一条执行语句完成，不需要在网络中发送数百行代码。
 （4）更好的安全机制，对于没有权限执行存储过程的用户，也可授权他们的执行存储过程。

                                              oracle存储过程
 
目录
 
         一.什么是存储过程
 
二.为什么要写存储过程
 
三.存储过程基础
 
1.存储过程结构
 
2.存储过程语法
 
3.pl/sql处理存储过程
 
四.存储过程进阶
 
1.BUIK COLLECT
 
2.FORALL
 
3.pl/sql调试存储过程
 
4.案例实战
 
附.参考资料
 

一.什么是存储过程
 
存储过程，百度百科上是这样解释的，存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。
 
简单的说就是专门干一件事一段sql语句。
 
可以由数据库自己去调用，也可以由java程序去调用。
 
在oracle数据库中存储过程是procedure。
 
二.为什么要写存储过程
 
1.效率高
 
  存储过程编译一次后，就会存到数据库，每次调用时都直接执行。而普通的sql语句我们要保存到其他地方（例如：记事本  上），都要先分析编译才会执行。所以想对而言存储过程效率更高。
 
2.降低网络流量
 
存储过程编译好会放在数据库，我们在远程调用时，不会传输大量的字符串类型的sql语句。
 
3.复用性高
 
存储过程往往是针对一个特定的功能编写的，当再需要完成这个特定的功能时，可以再次调用该存储过程。
 
4.可维护性高
 
当功能要求发生小的变化时，修改之前的存储过程比较容易，花费精力少。
 
5.安全性高
 
完成某个特定功能的存储过程一般只有特定的用户可以使用，具有使用身份限制，更安全。
 
三.存储过程基础
 
1.存储过程结构
 
(1).基本结构
 
Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常（可写可不写，要增强脚本的容错性和调试的方便性那就写上异常处理）
 
(2).无参存储过程
 
CREATE OR REPLACE PROCEDURE demo AS/IS
	变量2 DATE;
	变量3 NUMBER;
BEGIN
	--要处理的业务逻辑
	EXCEPTION    --存储过程异常
END 
 
这里的as和is一样任选一个，在这里没有区别，其中demo是存储过程名称。
 
(3).有参存储过程
 
a.带参数的存储过程
 
CREATE OR REPLACE PROCEDURE 存储过程名称(param1 student.id%TYPE)
AS/IS
name student.name%TYPE;
age number :=20;
BEGIN
  --业务处理.....
END
 
上面脚本中，
 
第1行：param1 是参数，类型和student表id字段的类型一样。
 
第3行：声明变量name，类型是student表name字段的类型（同上）。
 
第4行：声明变量age，类型数数字，初始化为20
 
 
 
b.带参数的存储过程并且进行赋值
 
CREATE OR REPLACE PROCEDURE 存储过程名称(
       s_no in varchar,
       s_name out varchar,
       s_age number) AS
total NUMBER := 0;
BEGIN
  SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
  dbms_output.put_line('符合该年龄的学生有'||total||'人');
  EXCEPTION
    WHEN too_many_rows THEN 
    DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
END
 
上面脚本中：
 
其中参数IN表示输入参数，是参数的默认模式。
 OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
 OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
 IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去
 
第7行：查询语句，把参数s_age作为过滤条件，INTO关键字，把查到的结果赋给total变量。
 
第8行：输出查询结果，在数据库中“||”用来连接字符串
 
第9—11行：做异常处理
 
2.存储过程语法
 
(1).运算符
 
这里s，m，n是变量，类型是number；
 
 
分类
 
 
运算符
 
 
含义
 
 
示例表达式
 
 
 
 
 
 
 
 
算术运算符
 
 
+
 
 
加
 
 
s := 2 + 2;
 
 
-
 
 
减
 
 
s := 3 – 1;
 
 
*
 
 
乘
 
 
s := 2 * 3;
 
 
/
 
 
除
 
 
s := 6 / 2;
 
 
mod(,)
 
 
取模，取余
 
 
m : = mod(3,2)
 
 
**
 
 
乘方
 
 
10**2 =100
 
 
 
 
 
 
 
 
关系运算符
 
 
=
 
 
等于 
 
 
s = 2
 
 
<>或!=或~=
 
 
不等于
 
 
s != 2
 
 
<
 
 
小于
 
 
s < 3
 
 
>
 
 
大于
 
 
s > 0
 
 
<=
 
 
小于等于
 
 
s <= 9
 
 
>=
 
 
大于等于
 
 
s >= 1
 
 
 
 
 
 
比较运算符
 
 
LIKE
 
 
满足匹配为true
 
 
‘li’ like ‘%i’返回true
 
 
BETWEEN
 
 
是否处于一个范围中
 
 
2 between 1 and 3 返回true
 
 
IN
 
 
是否处于一个集合中
 
 
‘x’ in (‘x’,’y’) 返回true
 
 
IS NULL
 
 
判断变量是否为空
 
 
若：n:=3,n is null,返回false
 
 
 
 
逻辑运算符
 
 
AND
 
 
逻辑与
 
 
s=3 and c is null
 
 
OR
 
 
逻辑或
 
 
s=3 or c is null
 
 
NOT
 
 
逻辑非
 
 
not c is null
 
 
 
 
其他
 
 
:=
 
 
赋值
 
 
s := 0;
 
 
..
 
 
范围
 
 
1..9，即1至9范围
 
 
||
 
 
字符串连接
 
 
‘hello’||’world’
 
 
 
(2).SELECT INTO STATEMENT语句
 
该语句将select到的结果赋值给一个或多个变量，例如：
 
CREATE OR REPLACE PROCEDURE DEMO_CDD1 IS
s_name VARCHAR2;   --学生名称
s_age NUMBER;      --学生年龄
s_address VARCHAR2; --学生籍贯
BEGIN
  --给单个变量赋值
  SELECT student_address INTO s_address
  FROM student where student_grade=100;
   --给多个变量赋值
  SELECT student_name,student_age INTO s_name,s_age
  FROM student where student_grade=100;
  --输出成绩为100分的那个学生信息
  dbms_output.put_line('姓名：'||s_name||',年龄：'||s_age||',籍贯：'||s_address);
END
 
上面脚本中：
 
存储过程名称：DEMO_CDD1, student是学生表，要求查出成绩为100分的那个学生的姓名，年龄，籍贯
 
(3).选择语句
 
a.IF..END IF
 
学生表的sex字段：1-男生；0-女生
 
IF s_sex=1 THEN
  dbms_output.put_line('这个学生是男生');
END IF
 
b.IF..ELSE..END IF
 
IF s_sex=1 THEN
  dbms_output.put_line('这个学生是男生');
ELSE
  dbms_output.put_line('这个学生是女生');
END IF
 
(4).循环语句
 
a.基本循环
 
LOOP
  IF 表达式 THEN
    EXIT;
  END IF
END LOOP;
 
b.while循环
 
WHILE 表达式 LOOP
  dbms_output.put_line('haha');
END LOOP;
 
c.for循环
 
FOR a in 10 .. 20 LOOP
  dbms_output.put_line('value of a: ' || a);
END LOOP;
 
(5).游标
 
    Oracle会创建一个存储区域，被称为上下文区域，用于处理SQL语句，其中包含需要处理的语句，例如所有的信息，行数处理，等等。
 
    游标是指向这一上下文的区域。 PL/SQL通过控制光标在上下文区域。游标持有的行(一个或多个)由SQL语句返回。行集合光标保持的被称为活动集合。
 
a.下表是常用的游标属性：
 
 
属性
 
 
描述
 
 
%FOUND
 
 
如果DML语句执行后影响有数据被更新或DQL查到了结果，返回true。否则，返回false。
 
 
%NOTFOUND
 
 
如果DML语句执行后影响有数据被更新或DQL查到了结果，返回false。否则，返回true。
 
 
%ISOPEN
 
 
游标打开时返回true，反之，返回false。
 
 
%ROWCOUNT
 
 
返回DML执行后影响的行数。
 
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;
 
3.pl/sql处理存储过程
 
(1).新建存储过程：右键procedures，点击new，弹出PROCEDURE框，再点击OK，如下图：
 
 
 
 
(2).在下面的编辑区，编写存储过程脚本
 
 
 
 
(3).在这里我们编写一个demo_cdd存储过程，要求输出“hello world”，如下图：
 
 
 
 
(4).右键刚才新建的存储过程名称，点击“Test”，在点击执行按钮
 
 
 
 
4.案例实战
 
场景：
 
有表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;
 
四.存储过程进阶
 
       在上面的案例中，我们的存储过程处理完所有数据要多长时间呢？事实我没有等到它执行完，在我可接受的时间范围内它没有完成。那么对于处理这种千万级数据量的情况，存储过程是不是束手无策呢？答案是否定的，接下来我们看看其他绝招。
 
       我们先来分析下执行过程的执行过程：一个存储过程编译后，在一条语句一条语句的执行时，如果遇到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的。
 
(1).SELECT INTO
 
查出来一个结果集合赋值给一个集合变量。
 
语法结构是：
 
SELECT field BULK COLLECT INTO var_conllect FROM table where colStatement;
 
说明：
 
       field：要查询的字段，可以是一个或多个（要保证和后面的集合变量要向对应）。
 
       var_collect：集合变量（联合数组等），用来存放查到的结果。
 
       table：表名，要查询的表。
 
       colStatement：后面过滤条件语句。比如s_age < 10;
 
例子：查出年龄小于10岁的学生姓名赋值给数组arr_name变量
 
SELECT s_name BULK COLLECT INTO arr_name FROM s_age < 10;
 
(2).FETCH INTO
 
从一个集合中抓取一部分数据赋值给一个集合变量。
 
语法结构如下：
 
FETCH cur1 BULK COLLECT INTO var_collect [LIMIT rows]
 
说明：
 
        cur1：是个数据集合，例如是个游标。
 
        var_collect：含义同上。
 
        [LIMIT rows]：可有可无，限制每次抓取的数据量。不写的话，默认每次一条数据。
 
例子：给年龄小于10岁的学生的年级降一级。
 
--查询年龄小于10岁的学生的学号放在游标cur_no里
CURSOR cur_no IS 
		SELECT s_no FROM student WHERE s_age < 10;

--声明了一个联合数组类型，元素类型和游标cur_no每个元素的类型一致
TYPE ARR_NO IS VARRAY(10) OF cur_no%ROWTYPE;

--声明一个该数组类型的变量no
no ARR_NO;
BEGIN
  FETCH cur_no BULK COLLECT INTO no LIMIT 100;
  FORALL i IN 1..no.count SAVE EXCEPTONS
	UPDATE student SET s_grade=s_grade-1 WHERE no(i);
END;
 
说明：先查出年龄小于10岁的学生的学号放在游标里，再每次从游标里拿出100个学号，进行更新，给他们的年级降一级。
 
(3).RETURNING
 
BULK COLLECT除了与SELECT，FETCH进行批量绑定之外，还可以与INSERT，DELETE，UPDATE语句结合使用，可以返回这些DML语句执行后所影响的记录内容（某些字段）。
 
再看一眼学生表的字段情况：student(s_no, s_name, s_age, s_grade)
 
语法结构如下：
 
DMLStatement
       RETURNING field BULK COLLECT INTO var_field;
 
说明：
 
        DMLStatement：是一个DML语句。
 
        field：是这个表的某个字段，当然也可以写多个逗号隔开（field1，field2, field3）。
 
        var_field：一个类型为该字段类型的集合，多个的话用逗号隔开，如下：
 
        （var_field1, var_field2, var_field3）
 
 
 
例子：获取那些因为年龄小于10岁而年级被将一级的学生的姓名集合。
 
TYPE NAME_COLLECT IS TABLE OF student.s_name%TYPE;
names NAME_COLLECT;
BEGIN
  UPDATE student SET s_grade=s_grade-1 WHERE s_age < 10
  RETURNING s_name BULK COLLECT INTO names;
END;
 
说明：
 
       NAME_COLLECT：是一个集合类型，类型是student表的name字段的类型。
 
       names：定义了一个NAME_COLLECT类型的变量。
 
(4).注意事项
 
a.不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。
 
b.只能在服务器端的程序中使用BULK COLLECT，如果在客户端使用，就会产生一个不支持这个特性的错误。
 
c.BULK COLLECT INTO 的目标对象必须是集合类型。
 
d.复合目标(如对象类型)不能在RETURNING INTO 子句中使用。
 
e.如果有多个隐式的数据类型转换的情况存在，多重复合目标就不能在BULK COLLECT INTO 子句中使用。
 
f.如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。
 
2.FORALL
 
(1).语法
 
FORALL index IN bounds [SAVE EXCEPTIONS]     
     sqlStatement；
 
说明：
 
        index是指下标；
 
        bounds是一个边界，形式是start..end
 
        [SAVE EXCEPTIONS] 可写可不写，这个下面介绍；
 
        sqlStatement是一个DML语句，这里有且仅有一个sql语句；
 
例子：
 
--例子1：移除年级是5到10之间的学生
FORALL i IN 5..10
       DELETE FROM student where s_grade=i;
 
--例子：2，arr是一个数组,存着要升高一年级的学生名称
FORALL s IN 1..arr.count SAVE EXCEPTIONS
       UPDATE student SET s_grade=s_grade+1 WHERE s_name=arr(i);
 
(2).SAVE EXCEPTIONS
 
通常情况写我们在执行DML语句时，可能会遇到异常，可能致使某个语句或整个事务回滚。如果我们写FORALL语句时没有用SAVE EXCEPTIONS语句，那么DML语句会在执行到一半的时候停下来。
 
       如果我们的FORALL语句后使用了SAVE EXCEPTIONS语句，当在执行过程中如果遇到异常，数据处理会继续向下进行，发生的异常信息会保存到SQL%BULK_EXCEPTONS的游标属性中，该游标属性是个记录集合，每条记录有两个字段，例如：（1, 02300）;
 
       ERROR_INDEX：该字段会存储发生异常的FORALL语句的迭代编号；
 
       ERROR_CODE：存储对应异常的，oracle错误代码；
 
SQL%BULK_EXCEPTONS这个异常信息总是存储着最近一次执行的FORALL语句可能发生的异常。而这个异常记录集合异常的个数则由它的COUNT属性表示，即:
 
       SQL%BULK_EXCEPTONS.COUNT，SQL%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。
 
(3). INDICES OF
 
在Oracle数据库10g之前有一个重要的限制，该数据库从IN范围子句中的第一行到最后一行，依次读取集合的内容，如果在该范围内遇到一个未定义的行，Oracle数据库将引发ORA-22160异常事件：ORA-22160: element at index [N] does not exist。针对这一问题，Oracle后续又提供了两个新语句：INDICES OF 和 VALUES OF。
 
接下来我们来看看这个INDICES OF语句，用于处理稀疏数组或包含有间隙的数组（例如：一个集合的某些元素被删除了）。
 
该语句语法结构是：
 
FORALL i INDICES OF collection [SAVE EXCEPTIONS]

       sqlStatement;
 
说明：
 
i：集合（嵌套表或联合数组）下标。
 
collection：是这个集合。
 
[SAVE EXCEPTIONS]和sqlStatement上面已经解释过。
 
例子：arr_std是一个联合数组，每个元素包含（name,age,grade）,现在要向student表插入数据。
 
FORALL i IN INDICES OF arr_stu
       INSERT INTO student VALUES(
           arr_stu(i).name,
              arr_stu(i).age,
              arr_stu(i).grade
       );
 
(4). VALUES OF
 
VALUES OF适用情况：绑定数组可以是稀疏数组，也可以不是，但我只想使用该数组中元素的一个子集。VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。但是，VALUES OF在使用时有一些限制：
 
       如果VALUES OF子句中所使用的集合是联合数组，则必须使用PLS_INTEGER和BINARY_INTEGER进行索引，VALUES OF 子句中所使用的元素必须是PLS_INTEGER或BINARY_INTEGER；
 
       当VALUES OF 子句所引用的集合为空，则FORALL语句会导致异常；
 
该语句的语法结构是：
 
FORALL i IN VALUES OF collection [SAVE EXCEPTIONS]
       sqlStatement;
 
说明：i和collection含义如上
 
联合数组请看文章（或自行百度）：https://blog.csdn.net/leshami/article/details/7372061
 
3.pl/sql调试存储过程
 
首先，当前这个用户得有能调试存储过程的权限，如果没有的话，以数据库管理员身份给你这个用户授权：
 
--userName是你要拿到调试存储过程权限的用户名
GRANT DEBUG ANY PROCEDURE,DEBUG CONNECT SESSION TO username;
 
(1).右键一个存储过程名称，点击测试，如下图：
 
这里我用的pl/sql是12.0.4版本的，下面截图中与低版本的pl/sql按钮位置都相同，只是图标不一样。
 
 
 
 
(2).点击两次step into按钮，进入语句调试，如下图：
 
 
 
 
(3).每点击一次step into按钮，会想下执行一条语句，也可以查看变量和表达式的值，如下图：
 
 
 
 
查看变量值：在查看变量区域，在Variable列输入变量i，在Value列点击下，该变量的值就显示出来了。
 
4.案例实战
 
场景和上面的案例实战是同一个，如下：
 
有表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 
 
编写存储过程：
 
(1).存储过程1
 
名称为：process_student1，student表的s_no字段类型为varchar2(16)。
 
CREATE OR REPLACE PROCEDURE process_student1 AS
    CURSOR CUR_STUDENT IS SELECT s_no FROM student;
    TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
    students REC_STUDENT;
BEGIN
  OPEN CUR_STUDENT;
  WHILE (TRUE) LOOP
    FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
    FORALL i IN 1..students.count SAVE EXCEPTIONS
      UPDATE student SET s_grade=s_grade+1 WHERE s_no=students(i);
    COMMIT;
    EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
  END LOO;
  dbms_output.put_line('finished');
END;
 
说明：
 
        把student表中要更新的记录的学号拿出来放在游标CUR_STUDENT，每次从这个游标里抓取10万条数据赋值给数组students，每次更新这10万条记录。循环进行直到游标里的数据全部抓取完。
 
        FETCH .. BULK COLLECT INTO .. LIMIT rows语句中：这个rows我测试目前最大可以为10万条。
 
(2).存储过程2（ROWID）
 
       如果我们这个student表没有主键，也没有索引呢，该怎么来做呢？
 
分析下：
 
       ROWNUM是伪列，每次获取结果后，然后在结果集里会产生一列，从1开始排，每次都是从1开始排。
 
        ROWID在每个表中，每条记录的ROWID都是唯一的。在这种情况下，我们可以用ROWID。但要注意的是，ROWID是一个类型，注意它和VARCHAR2之间的转换。有两个方法：ROWIDTOCHAR（）是把ROWID类型转换为CHAR类型；CHARTOROWID()是把CAHR类型转换为ROWID类型。
 
接下来我们编写存储过程process_student2，脚本如下：
 
CREATE OR REPLACE PROCEDURE process_student1 AS
    CURSOR CUR_STUDENT IS SELECT ROWIDTOCHAR(ROWID) FROM student;
    TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
    students REC_STUDENT;
BEGIN
  OPEN CUR_STUDENT;
  WHILE (TRUE) LOOP
    FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
    FORALL i IN 1..students.count SAVE EXCEPTIONS
      UPDATE student SET s_grade=s_grade+1 WHERE ROWID=CHARTOROWID(students(i));
    COMMIT;
    EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
  END LOO;
  dbms_output.put_line('finished');
END;
 
说明：
 
       我们首先查到记录的ROWID并把它转换为CHAR类型，存放到游标CUR_STUDENT里，
 
再每次抓取10万条数据赋值给数组进行更新，更新语句的WHERE条件时，又把数组元素是CAHR类型的rowid串转换为ROWID类型。
 
附.参考资料
 
存储过程基础：
 
       https://www.yiibai.com/plsql/plsql_basic_syntax.html
 
存储过程进阶之FORALL：
 
       https://blog.csdn.net/leshami/article/details/7536926
 
       https://blog.csdn.net/jie1336950707/article/details/49966753
 
存储过程进阶之BUIL COLLECT：
 
       https://blog.csdn.net/leeboy_wang/article/details/7991021
 
       https://blog.csdn.net/leshami/article/details/7545597
 
联合数组：
 
       https://blog.csdn.net/leshami/article/details/7372061

本文主要记录了本人学习mysql存储过程时的笔记，文档中用到的表结构在文档结尾处有说明。
 
一、定义
 
       存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。
 
二、存储过程的特点
 
    1、能完成较复杂的判断和运算
     2、可编程行强，灵活
     3、SQL编程的代码可重复使用
     4、执行的速度相对快一些
     5、减少网络之间的数据传输，节省开销    
 
三、创建一个简单的存储过程
 
    1、创建存储过程的简单语法
 
create procedure 名称()
begin
.........
end
 
    2、创建一个简单的存储过程
 
create procedure testa()
begin
    select * from users;
    select * from orders;
end;
 
    3、调用存储过程
 
call testa();   
 
运行结果如图（1）和图（2）：
 
 
                                 图（1）
 
 
 
 
                                 图（2）
 
四、存储过程的变量
 
    1、先通过一个简单的例子来学习变量的声明和赋值
 
create procedure test2()
begin
  -- 使用 declare语句声明一个变量
  declare username varchar(32) default '';
  -- 使用set语句给变量赋值
  set username='xiaoxiao';
  -- 将users表中id=1的名称赋值给username
  select name into username from users where id=1;
  -- 返回变量
  select username;
end;
 
    2、概括
         (1)、变量的声明使用declare,一句declare只声明一个变量，变量必须先声明后使用；
 
        (2)、变量具有数据类型和长度，与mysql的SQL数据类型保持一致，因此甚至还能制定默认值、字符集和排序规则等；
         (3)、变量可以通过set来赋值，也可以通过select into的方式赋值；
         (4)、变量需要返回，可以使用select语句，如：select 变量名。
        
 
五、变量的作用域
 
    1、变量作用域说明：
         (1)、存储过程中变量是有作用域的，作用范围在begin和end块之间，end结束变量的作用范围即结束。
         (2)、需要多个块之间传值，可以使用全局变量，即放在所有代码块之前
         (3)、传参变量是全局的，可以在多个块之间起作用
     2、通过一个实例来验证变量的作用域
 
         需求: 创建一个存储过程，用来统计表users、orders表中行数数量和orders表中的最大金额和最小金额
 
create procedure test3()
begin
  begin
    declare userscount int default 0; -- 用户表中的数量
    declare ordercount int default 0; -- 订单表中的数量
    select count(*) into userscount from users;
    select count(*) into ordercount from orders;
    select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量
  end;
  begin 
    declare maxmoney int default 0; -- 最大金额
    declare minmoney int default 0; -- 最小金额
    select max(money) into maxmoney from orders;
    select min(money) into minmoney from orders;
    select maxmoney,minmoney; -- 返回最金额、最小金额
   end;
end;
 
调用以上存储过程，结果如图（3）和图（4）：
 
           
 
                   （3）    
 
     
                        （4）
 
    3、我将过程test(3)改为如下：
 
    create procedure test3()
     begin
       begin
         declare userscount int default 0; -- 用户表中的数量
         declare ordercount int default 0; -- 订单表中的数量
         select count(*) into userscount from users;
         select count(*) into ordercount from orders;
         select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量
       end;
       begin 
         declare maxmoney int default 0; -- 最大金额
         declare minmoney int default 0; -- 最小金额
         select max(money) into maxmoney from orders;
         select min(money) into minmoney from orders;
         select userscount,ordercount，maxmoney,minmoney; -- 返回最金额、最小金额
        end;
     end;
 
再次调用call test3(); 会报错如图（5）：
 
 
                               图（5）
 
    4、将userscount,ordercount改为全局变量，再次验证
 
    create procedure test3()
     begin
 
        declare userscount int default 0; -- 用户表中的数量
         declare ordercount int default 0; -- 订单表中的数量
         begin
             select count(*) into userscount from users;
             select count(*) into ordercount from orders;
             select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量
       end;
       begin 
         declare maxmoney int default 0; -- 最大金额
         declare minmoney int default 0; -- 最小金额
         select max(money) into maxmoney from orders;
         select min(money) into minmoney from orders;
         select userscount,ordercount，maxmoney,minmoney; -- 返回最金额、最小金额
        end;
     end;
 
再次调用call test3(); 会报错如图（6）和图（7）：
 
       
 
                    图（6）       
 
                                                              
 
                    图（7）
 
因此，存储过程中变量的作用域，作用范围在begin和end块之间，end结束变量的作用范围即结束
     
 
六、存储过程参数
 

   1、基本语法
 
create procedure 名称([IN|OUT|INOUT] 参数名 参数数据类型 )
begin
.........
end
 
 存储过程的参数类型有：IN,OUT,INOUT，下面分别介绍这个三种类型：
     
   2、存储过程的传出参数IN
 
     说明：        
 
        （1）、传入参数：类型为in,表示该参数的值必须在调用存储过程事指定，如果不显示指定为in,那么默认就是in类型。
         （2）、IN类型参数一般只用于传入，在调用过程中一般不作为修改和返回
         （3）、如果调用存储过程中需要修改和返回值，可以使用OUT类型参数
 
通过一个实例来演示：
 
需求：编写存储过程，传入id,根据id返回name
 
 create procedure test4(userId int)
    begin
            declare username varchar(32) default '';
            declare ordercount int default 0;
            select name into username from users where id=userId;
            select username;
    end;
 
运行如图(8)
 

     
 
                                                       图(8)
   
     
    3、存储过程的传出参数out
     
         需求：调用存储过程时，传入userId返回该用户的name
         create procedure test5(in userId int,out username varchar(32))
         begin
             select name into username from users where id=userId;
         end;
 
        调用及运行结果如图(9)：
 
 
                                   图(9)
         
   概括：
         1、传出参数：在调用存储过程中，可以改变其值，并可返回；
         2、out是传出参数，不能用于传入参数值；
         3、调用存储过程时，out参数也需要指定，但必须是变量，不能是常量；
         4、如果既需要传入，同时又需要传出，则可以使用INOUT类型参数
     
     (3).存储过程的可变参数INOUT
     
         需求：调用存储过程时，传入userId和userName,即使传入，也是传出参数。
 
create procedure test6(inout userId int,inout username varchar(32))
begin
    set userId=2;
    set username='';
    select id,name into userId,username from users where id=userId;
end;
 
 调用及运行结果如图(10)
 

         
 
                                       图(10)
     概括：
         1、可变变量INOUT:调用时可传入值，在调用过程中，可修改其值，同时也可返回值；
         2、INOUT参数集合了IN和OUT类型的参数功能；
         3、INOUT调用时传入的是变量，而不是常量；
 
 
 
七、存储过程条件语句
 
   1、基本结构
 
   （1）、条件语句基本结构：
 
if() then...else...end if;
 
   （2）、多条件判断语句：
 
if() then...
elseif() then...
else ...
end if;
 
   2、实例
     实例1：编写存储过程，如果用户userId是偶数则返回username,否则返回userId
 
create procedure test7(in userId int)
begin
   declare username varchar(32) default '';
   if(userId%2=0)
   then 
      select name into username from users where id=userId;
      select username;
   else
      select userId;
      end if;
end;
 
    调用及运行结果如图(11)和图(12)：
 
       
 
                        图(11)           
 
                                      
 
                               图(12)
     
     2、存储过程的多条件语句应用示例
         需求：根据用户传入的uid参数判断
         （1）、如果用户状态status为1，则给用户score加10分；
         （2）、 如果用户状态status为2，则给用户score加20分；
         （3）、 其他情况加30分
 
create procedure test8(in userid int)
begin
   declare my_status int default 0;
   select status into my_status from users where id=userid;
   if(my_status=1)
   then 
       update users set score=score+10 where id=userid;
    elseif(my_status=2)
    then 
       update users set score=score+20 where id=userid;
    else 
       update users set score=score+30 where id=userid;
    end if;
end;
 
调用过程之前的users表的数据如图(13)，调用 call test8(1); 及运行结果图(14)：
 

            
                                    图(13)       
 
     
 
                                    图(14)
       
 
八、存储过程循环语句
 

     1、while语句
 
       （1）、while语句的基本结构
 
while(表达式) do 
   ......  
end while;
 
         （2）、示例
     需求：使用循环语句，向表test1(id)中插入10条连续的记录
 
create procedure test9()
begin
  declare i int default 0;
  while(i<10) do 
    begin 
        select i;
        set i=i+1;
        insert into test1(id) values(i);
     end;
  end while;
end;
 
    调用及运行结果结果如图(15)和图(16)：
 

     
 
                                                                    图(15)
 
   
 
         图(16)
 

     2、repeat语句
     （1）、repeat语句基本的结构：
 
repeat...until...end repeat;
 
     （2）、示例
 
需求：给test1表中的id字段插入数据，从1到10
 
create procedure test10()
begin
    declare i int default 0;
    repeat 
    begin 
        select i;
        set i=i+1;
        insert into test1(id) values(i);
    end;
    until i>=10 -- 如果i>=10,则跳出循环
    end repeat;
end;
 
   调用及运行结果结果如图(17)和图(18)
 
   
 
    图(17)             
 
   
 
              图(18)
         
     概括：
         until判断返回逻辑真或者假，表达式可以是任意返回真或者假的表达式，只有当until语句为真是，循环结束。
         
 
九、存储过程游标的使用
 

     1、什么是游标
         游标是保存查询结果的临时区域
     2、示例
     需求：编写存储过程，使用游标，把users表中 id为偶数的记录逐一更新用户名
     
 
create procedure test11()
    begin
        declare stopflag int default 0;
        declare username VARCHAR(32);
        -- 创建一个游标变量，declare 变量名 cursor ...
        declare username_cur cursor for select name from users where id%2=0;
        -- 游标是保存查询结果的临时区域
        -- 游标变量username_cur保存了查询的临时结果，实际上就是结果集
        -- 当游标变量中保存的结果都查询一遍(遍历),到达结尾，将变量stopflag设置为1，用于循环中判断是否结束
        declare continue handler for not found set stopflag=1;

        open username_cur; -- 打卡游标
        fetch username_cur into username; -- 游标向前走一步，取出一条记录放到变量username中
        while(stopflag=0) do -- 如果游标还没有结尾，就继续
            begin 
                -- 在用户名前门拼接 '_cur' 字符串
                update users set name=CONCAT(username,'_cur') where name=username;
                fetch username_cur into username;
            end;
        end while; -- 结束循环
        close username_cur; -- 关闭游标
    end;
 
调用结果如图(19)：
 
 
                                     图(19)
 

 十、自定义函数
 

     函数与存储过程最大的区别是函数必须有返回值，否则会报错
     
     1、一个简单的函数
 
create function getusername(userid int) returns varchar(32)
    reads sql data  -- 从数据库中读取数据，但不修改数据
    begin
        declare username varchar(32) default '';
        select name into username from users where id=userid;
        return username;
    end;
 
    
     调用及运行结果如图(20)：
 
 
                图(20)
     
     概括：
     1.创建函数使用create function 函数名(参数) returns 返回类型；
     2.函数体放在begin和end之间；
     3.returns指定函数的返回值；
     4.函数调用使用select getusername()。
     
     2、示例
     需求：根据userid，获取accoutid,id,name组合成UUID作为用户的唯一标识
 
  create function getuuid(userid int) returns varchar(64)
    reads sql data  -- 从数据库中读取数据，但不修改数据
    begin
        declare uuid varchar(64) default '';
        select concat(accontid,'_',id,'_',name) into uuid from users where id=userid;
        return uuid;
    end;
 
调用及运行结果如图(21)
 
 
             图(21)
 
   
 十一、触发器
 

     1、什么是触发器
 

     触发器与函数、存储过程一样，触发器是一种对象，它能根据对表的操作时间，触发一些动作，这些动作可以是insert,update,delete等修改操作。
 
    2、示例1
 
（1）、需求：出于审计目的，当有人往表users插入一条记录时，把插入的userid,username,插入动作和操作时间记录下来。
     
 
create trigger tr_users_insert after insert on users
    for each row 
    begin 
        insert into oplog(userid,username,action,optime)
        values(NEW.id,NEW.name,'insert',now());
    end;
 

     创建成功后，给uses表中插入一条记录：
     
 
insert into users(id,name,age,status,score,accontid)
    values(6,'小周',23,1,'60','10001')；
 
  执行成功后，打开oplog表，可以看到oplog表中插入了一条记录如图(22)
     
 
                                                     图(22)
   
 
  （2）、总结
 
        1、创建触发器使用create trigger 触发器名
         2、什么时候触发？after insert on users,除了after还有before,是在对表操作之前（before）或者之后（after）触发动作的。
         3、对什么操作事件触发？ after insert on users,操作事件包括insert,update,delete等修改操作；
         4、对什么表触发? after insert on users
         5、影响的范围？for each row
  
 
3、示例2
 

     需求：出于审计目的，当删除users表时，记录删除前该记录的主要字段值
     
 
create trigger tr_users_delete before delete on users
    for each row 
    begin 
        insert into oplog(userid,username,action,optime)
        values(OLD.id,OLD.name,'delete',now());
    end;
 

     删除users表中的一条记录
     
 
delete from users where id=6;
 
    执行成功后，打开oplog表，可以看到oplog表中插入了一条记录如图（23）
 
 
                                              图（23）
 

          
 十二、流程控制
 
 1、case分支
 
   （1）、基本语法结构
 
case ...
when ... then....
when.... then....
else ... 
end case;
 
（2）、示例
 
users表中，根据userid获取status值，如果status为1，则修改score为10；如果status为2，则修改为20，如果status3，则修改为30；否则修改为40。
   
 
 create procedure testcate(userid int)
    begin 
        declare my_status int default 0;
        select status into my_status from users where id=userid;

        case my_status
            when 1 then update users set score=10 where id=userid;
            when 2 then update users set score=20 where id=userid;
            when 3 then update users set score=30 where id=userid;
            else update users set score=40 where id=userid;
        end case;
    end;
 

     调用过程 call testcate(1); ，执行结果如图（24）；
 
 
                             图（24）
 
 
 
十四、存储过程+event（事件）
 
 
 
     1、使用存储过程+事件事件一个简单的实现福彩3D开奖
         
         需求：设计一个福彩的开奖过程，没3分钟开奖一次
             第一步：先编写一个存储过程open_lottery,产生3个随机数，生成一条开奖记录
             第二步：编写一个时间调度器，每3分钟调用一次这个过程
           
 
create procedure open_lottery()
        begin 
            insert into lottery(num1,num2,num3,ctime)
            select FLOOR(rand()*9)+1,FLOOR(rand()*9)+1,FLOOR(rand()*9)+1,now();
        end;
 
 
 
create event if not exists lottery_event -- 创建一个事件
        on schedule every  3 minute  -- on schedule 什么时候来执行，没三分钟执行一次
        on completion preserve 
        do call open_lottery;
 

         运行结果如图(25)
 

         
 
                                                         图(25)
 
注意，如果event之一没有运行，请按照以下办法解决：
 
（1）、 show variables like '%event_scheduler%';
         set global event_scheduler=on;
 
（2）、 alert event lottery_event enable;
 

     2、解析event的创建格式
     （1）、基本语法
 
create event[IF NOT EXISTS]event_name -- 创建使用create event
    ON SCHEDULE schedule -- on schedule 什么时候来执行
    [ON COMPLETION [NOT] PRESERVE] -- 调度计划执行完成后是否还保留
    [ENABLE | DISABLE] -- 是否开启事件，默认开启
    [COMMENT 'comment'] -- 事件的注释
    DO sql_statement; -- 这个调度计划要做什么？
 
（2）、执行时间说明
 
    1.单次计划任务示例
         在2019年2月1日4点执行一次
 
        on schedule at    '2019-02-01 04:00:00'
          
     2. 重复计划执行
         on schedule every 1 second 每秒执行一次
         on schedule every 1 minute 每分钟执行一次
         on schedule every 1 day 没天执行一次
         
     3.指定时间范围的重复计划任务
         每天在20:00:00执行一次
         on schedule every 1 day starts '2019-02-01 20:00:00'
         
 
十五、本文所用到的表
 
 
 
1、lottery表
 
 
2、oplog表
 
 
3、orders表
 
 
4、test1表
 
 
5、user表