1.存储过程简述

存储过程：它是大型数据库中常用的、一组为了完成特定功能的SQL语句集。

存储过程在Oracl中是procedure。

2.存储过程优点

2.1.性能高（效率高）

存储过程相较于普通的SQL语句来说，它的性能非常的好，这是因为存储过程在第一次编译之后，是存储在数据库的，用的时候直接使用存储过程名就可以（第一次之后不需要再编译），普通的SQL语句每次执行都需要先编译再执行。

2.2.低流量

存储过程在编译好之后直接存放在数据库，因此不需要再传输大量字符串类型的SQL语句。

2.3.高复用

存储过程在写好之后，需要使用这个特定功能的都可以调用。

2.4.易维护

存储过程在编写好之后，如果需要修改需求，也很容易。

2.5.高安全

完成某个特定功能的存储过程一般只有特定身份的人才能使用，所以具有身份限制

3.存储过程结构

3.1.存储过程的基本结构

基本结构包含三大部分：声明过程，执行部分，存储过程异常（多用于增强代码的容错性和健壮性）。

3.2.无参存储过程

CREATE OR REPLACE PROCEDURE 存储过程名 IS/AS //IS和AS选择哪个均可，无区别

变量1 DATE;

变量2 NUMBER;

BEGIN

//需要执行的SQL语句

EXCEPTION; //存储过程异常

END

3.3.有参存储过程

CREATE OR REPLACE PROCEDURE 存储过程名(param student.id%TYPE) AS/IS

name student.name%TYPE;

age number :=20;

BEGIN

//需要执行的SQL语句

EXCEPTION; //存储过程异常

END

3.4.进行赋值的有参存储过程

CREATE OR REPLACE PROCEDURE 存储过程名(sno in varchar, //in代表的传入参数

sname out varchar, //out代表的是返回值

sage number) AS //没有指定是in或out的时候，默认是in

total NUMBER := 0;

BEGIN

SELECT COUNT(1) INTO total FROM student s WHERE s.age = sage;

dbms_output.put_line("符合该区间的学生有:"|| total || "人");

EXCEPTION

WHEN two_money_throws THEN

DBMS_OUTPUT.PUT_LINE("返回值大于1行！");

END

3.5.存储过程语法

运算符：

SELECT ... INTO ...赋值

SELECT INTO语句可以将select到的结果赋值给一个或多个变量。

CREATE OR REPLACE PROCEDURE 存储过程名 IS

sname VARCHAR2; //学生姓名

sage NUMBER; //学生年龄

saddress VARCHAR2; //学生籍贯

BEGIN

select s_address into saddress from student where s_grade = 100;

select s_name,s_age into sname,sage from student where s_grade = 100;

END

IF...END IF/IF...ELSE...END IF 选择语句

IF...END IF

IF sex=1 THEN

dbms_output.put_line("男");

END IF

IF...ELSE IF...END IF

IF sex=1 THEN

dbms_output.put_line("男");

ELSE

dbms_output.put_line("女");

END IF

基本循环/while循环/for循环

基本循环

LOOP

IF 表达式 THEN

需要执行的操作

END IF

END LOOP

while循环

WHILE 表达式 LOOP

需要执行的操作

END LOOP

for循环

FOR x IN 20..30 LOOP

需要执行的操作

END LOOP

游标

游标是SQL的一个工作区，由系统或者用户以变量的形式来定义的。

游标的类型：显式游标 / 隐式游标。

游标的作用：它用来临时存储从数据库读取出来的数据块。

游标的好处：游标可以把读取出来的数据临时存放在计算机内存中，使用的时候不需要频繁的和磁盘进行数据交换，提高了效率和速度。

游标的特点：正常的DML操作（增删改）和只从数据库中读取一行数据的查操作，系统会使用一个隐式游标，但是对于读取多行数据，就需要定义一个显式游标，并通过与游标有关的语句进行处理（所以，显式游标通常对应一条返回多行多列的查询语句）。 一旦打开游标，语句结果就会传到游标变量中，最后应用程序在从游标变量中分解出需要的数据进行处理。

隐式游标：隐式游标对应的是DML操作和读取单行数据的查询操作，可以通过名字SQL的方式去访问，但是隐式游标只能访问上一个执行的DML和单行读取的查询操作，所以在刚执行完操作之后，需要马上使用SQL游标名进行访问属性。

隐式游标的四个属性：

隐式游标属性返回值意义

1. SQL%ROWCOUNT 整型表示DML执行成功影响的数据行数

2. SQL%FOUND 布尔值值为TRUE表示DML或查询操作成功

3. SQL%NOTFOUND 布尔值和SQL%FOUND相反

4. SQL%ISOPEN 布尔值 DML执行时为TRUE，执行结束为FALSE

例子：

SET SERVEROUTPUT ON //设置环境变量，否则无法正常输出返回信息

BEGIN

UPDATE emp SET sal=sal+100 WHERE empno=1234;

IF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE("修改成功！");

COMMIT;

ELSE

DBMS_OUTPUT.PUT_LINE("修改失败！");

END IF;

END;

显式游标：

声明：CURSOR 游标名[(参1 类型 [参2 类型...])]

IS SELECT语句;

注意：参数为可选的，但是在使用游标的时候，必须传入实际参数；SELECT语句除了INTO子句不可写之外，其他的都可以；在SELECT语句中，可以使用在游标之前定义的变量。

打开游标：OPEN 游标名[(参1 类型 [参2 类型...])];

注意：打开游标的时候，SELECT的查询结果就会被传到游标中。

提取数据：FETCH 游标名 INTO 变量名1 [变量名2];

或：FETCH 游标名 INTO 记录变量；

注意：游标中有一个指针指向游标数据区，但是指针一次只能指向一行数据，返回多行数据需要重复执行，可以搭配使用循环。循环控制可以通过访问游标属性来实现。第一种方式：变量名是用于从游标中接收数据的变量，需要事先定义。参数的数量和类型需要和select语句中的一致。第二种方式：一次将一行数据记录到变量中，需要通过%ROWTYPE事先定义记录变量。CLOSE

关闭游标：CLOSE 游标名；

注意：显式游标必须显示关闭，一旦关闭就会释放游标的资源，想要使用必须再次打开。

例子：

//采用第一种方法：

SET SERVEROUTPUT ON //设置环境变量

DECLARE //定义变量(局部变量，作用类似BEGIN)

name VARCHAR2(10);

job VARCHAR2(10);

CURSOR emp_cursor IS //声明显式游标emp_cursor

SELECT name,job FROM emp WHERE enpno = 100; //执行查询语句

BEGIN

OPEN emp_cursor; //打开游标，select语句执行结果返回到游标数据区

FETCH emp_cursor INTO name,job ; //读取游标中的数据

DBMS_OUTPUT.PUT_LINE(name || "," || job); //输出获取的数据

CLOSE emp_cursor; //关闭游标

END;

//采用第二种方法：

SET SERVEROUTPUT ON //设置环境变量

DECLARE

CURSOR emp_cursor IS //定义游标

SELECT name,job,sal FROM emp WHERE empno = 7788;

emp_record emp_cursor %ROWTYPE; //定义变量emp_record的类型

BEGIN

OPEN emp_cursor ; //打开游标

FETCH emp_cursor INTO emp_record; //将查询到的数据传给变量emp_record

DBMS_OUTPUT.PUT_LINE(emp_record.name || "," || emp_record.age || "," || emp_record.sal);

CLOSE emp_cursor; //关闭游标

//游标搭配循环

SET SERVEROUTPUT ON

        DECLARE

           V_ename VARCHAR2(10);

           V_sal NUMBER(5);

           CURSOR emp_cursor IS

           SELECT ename,sal FROM emp ORDER BY sal DESC;

          BEGIN

              OPEN emp_cursor;

              FOR I IN 1..3 LOOP

     FETCH emp_cursor INTO v_ename,v_sal;

                   DBMS_OUTPUT.PUT_LINE(v_ename||','||v_sal);

END LOOP;

CLOSE emp_cursor;

END;

4.存储过程高级

存储过程的执行过程：存储过程在编译之后，每遇到一条语句都会判断是pl/sql还是sql，然后给对应的引擎去处理。

存储过程存在的问题：当遇到大数据量的处理时，执行焦点就会不断地切换。过多的上下文切换会（pl/sql和sql引擎的来回切换成为上下文切换）造成沉重的负担，导致效率的下降。

解决问题的方法：Oracl8i之后PL/SQL引入两个新数据操纵语句——FORALL和BUIK COLLECT。这两个语句减少了上下文切换（一次切换多次执行）。

4.1.BUIK COLLECT

BUIK COLLECT的特点是批量检索，然后将检索结果绑定到一个集合变量中，和游标cursor一条条的检索是不同的。BUIK COLLECT可以在SELECT INTO、FETCH INTO、RETURNING INTO中使用。

//SELSECT INTO 查询出来的结果集合赋值给另一个集合变量

语法：SELECT 查询字段 BUIK COLLECT INTO 存值集合变量 FROM 表名 WHERE 条件;

例：SELECT s_name BUIK COLLECT INTO arr_name FROM student WHERE s_age>10;

//FETCH INTO 将集合中的一部分数据赋值给另一个集合

语法：FETCH 数据集合 BUIK COLLECT INTO 数据集合 [ LIMIT 每次获取的行数 ];

例：

//声明一个游标，并存放年龄大于10的学生编号

CURSOR cur_no IS SELECT s_no FROM student WHERE s_age>10;

//声明一个数组，类型和游标的每个元素一样

TYPE arr_no IS VARRY(10) OF cur_no%ROWTYPE;

//声明一个类型为arr_no类型的变量

no arr_no;

BEGIN

FETCH cur_no BUIK COLLECT INTO no LIMIT 100; //每次获取100条数据给该变量

FORALL i IN 1..no.count SAVE EXCEPTIONS

UPDATE student SET s_grade=s_grade-1 WHERE no(i);

END

//RETURNING 将进行DML操作影响到的数据行的列值保存进指定的PL/SQL变量中

语法：DML语句 RETURNING 表字段1[,表字段2... ] BUIK COLLECT INTO 字段同类型集合;

例：

TYPE name_collect IS TABLE student.s_name%TYPE;

names name_collect;

BEGIN

UPDATE student SET s_grade=s_grade-1 WHERE s_age<10

RETURNING s_name BUIK COLLECT INTO names;

END

注意：

1.不能对键为字符串类型的关联数组使用BUIK COLLECT子句

2.只能在服务器端的程序中使用BUIK COLLECT子句，在客户端使用会报错（不支持）

3.BUIK COLLECT INTO子句的目标对象必须是集合类型

4.RETURNING中不能使用复合目标（对象类型）

5.如果有一个或多个隐式数据类型转换，复合对象不能在BUIK COLLECT INTO中作为目标对象使用

4.2.FORALL

FORALL主要的作用就是增强DML的操作性，简化代码。

语法：FORALL 下标 IN 范围 [ SAVE EXCEPIONS ] DML语句；

例：FORALL i IN 5..10 DELETE FROM student WHERE s_grade=i;

在执行DML语句的时候，会可能遇到异常，可能会导致事件的回滚。如果在FORALL的后面没有加上SAVE EXCEPTIONS语句，DML语句会在执行到一半的时候停下来。如果加上了SAVE EXCEPTIONS语句，那么DML语句会继续向下执行，异常信息则会记录在SQL%BULK_EXCEPTIONS游标属性中，这个游标属性是一个信息记录集合，每条记录里面有两个字段，分别是发生异常的FORALL语句的迭代编号和错误代码，例：（1,03400）。SQL%BULK_EXCEPTIONS这个集合保存着最近一次可能发生异常的信息，而异常个数则由它的COUNT属性表示，即：SQL%BULK_EXCEPTIONS.COUNT

4.3.INDICES OF

INDICES OF是用于处理稀疏数组或包含间隙的数组的。因为Oracl数据库在10g之前有一个限制：在IN范围中，会从第一行到最后一行依次读取数据库的内容，如果遇到了一个未定义的行或者是被删除的行，那么就会引发ORA-22160的异常(ORA-22160: element at index [N] does not exist).

语法：FORALL i INDICES OF 集合 [ SAVE EXCEPTIONS ] sql语句;

例：FORALL i INDICES OF arr_stu

INSERT INTO student VALUES(arr_stu(i).name,arr_stu(i).age,arr_stu(i).grade) ;

4.4.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 集合 [ SAVE EXCEPTIONS ] sql语句;

5.联合数组和嵌套表

5.1.联合数组

联合数组类似于一张简单的SQL表，可以按照主键检索数据，且数据元素个数无限制。

存储的数据是没有顺序的，当使用变量来检索数据的时候，每个数据会分配一个从1开始的下标。

下标可以为负，且下标的数据类型支持BINARY_INTEGER,PLS_INTEGER,VARCHAR2。

不能作为表列的数据类型使用，只能作为PL/SQL复合数据类型使用

存放的数据是临时数据，所以不支持insert,select into等SQL语句，等同于sql server中的表变量

语法：

//element_type为联合数组元数据指定数据类型(先使用TYPE声明表结构

TYPE type_name IS TABLE OF element_type [ NOT NULL ]

INDEX BY key_type; //元素下标的使用类型

table_name TYPE_NAME; //使用声明的TYPE类型来声明实际数组名

5.2.嵌套表

元素下标从1开始，长度可以动态增长，没有限制。

嵌套表的数组元素可以是稀疏数组，它的语法和联合语法相似，但是没有index by子句

嵌套表必须先初始化才能引用其中元素，若初始化值为空，则后面需要使用extend来扩展其大小

嵌套表初始化的时候是密集的，但是允许有空隙，所以支持使用内置过程delete来从嵌套表删除元素

嵌套表类型可以作为表列的数据类型来使用。

语法：TYPE type_name IS TABLE OF element_type [ NOT NULL ]

table_name TYPE_NAME;

参考文章：https://blog.csdn.net/weixin_41968788/article/details/83659164

Oracle数据库存储过程