基础
什么是存储过程呢？
简而言之就是过程语言+SQL，也就是在原来SQL语言的非过程化基础上，增加了过程化的元素。包括变量、添加选择、循环控制、错误处理、集合、子程序、包、触发器等。
存储过程程序以块为单位，每个语句块由声明部分、执行部分、异常处理部分构成。在语句块中可以定义过程化的变量、常量、数据类型、异常、子程序等，可以使用各自控制结构，可以嵌入各种SQL语句，从而实现复杂的业务逻辑处理。
declare -- 声明部分
lob text := 'abc';
siz int;
v constant int default 100;
begin -- 执行部分
siz = length(lob);
dbms_output.put_line(v);
end;
/
复制匿名块与命名块
Oracle中，块分为两类，匿名块和命名块。
匿名块是指没有名称，不在数据库中存储，不能被其他程序调用，但可以调用其他子程序的程序。匿名块每次执行时都需要程序的源代码（因为没有在数据库中存储），先编译后执行。
begin
dbms_output.put_line('anonymous block');
end;
复制命名块是指一次编译多次执行的plorasql程序，包括函数、存储过程、包等，它们编译后放在数据库服务器中，由应用程序或系统在特定条件下调用执行。命名块是一次编译多次执行的PL/SQL程序。
create package pkg_showemp as
cursor c_emp is select * from t1;
procedure show_fiveemp;
end;
create package body pkg_showemp as
procedure show_fiveemp
as
v_emp c_emp%ROWTYPE;
begin
open c_emp;
for i in 1..3 loop
fetch c_emp into v_emp;
dbms_output.put_line(v_emp.a);
end loop;
end;
end;
复制%TYPE 、 %ROWTYPE属性
可以看到上面的例子引用了%ROWTYPE来声明变量的类型。那具体是什么意思呢？
%TYPE属性，如果要声明一个变量、常量、集合元素、记录字段、子程序参数，其数据类型与某个之前声明的变量的数据类型相同，或者与数据库表中某个列的数据类型一致，则可以利用%TYPE来实现，此时，不需要知道参照变量或列的数据类型。
declare
vid mystudent.id%TYPE;
begin
select id into vid from mystudent where rownum < 2;
dbms_output.put_line(vid);
end;
/
复制%ROWTYPE属性，与%TYPE属性类似，可以使用%ROWTYPE属性声明与显示游标、游标变量、数据库表或视图结构相一致的记录类型。使用%ROWTYPE属性返回的记录类型中的字段名称、数据类型、字段顺序与被引用的游标、数据库表等被引用的对象的结构完全一致。
declare
v mystudent%ROWTYPE;
begin
select * into v from mystudent where rownum < 2;
end;
/
复制-- 上述示例中用的表结构为：
postgres@postgres=# \d mystudent ;
Table "public.mystudent"
Column | Type | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+---------
id | integer | | |
name | character varying(20) | | |
teachername | character varying(20) | | |
复制变量与常量的声明
如果要在PL/SQL程序中使用变量或常量，则必须在PL/SQL块中的声明部分声明该变量或者常量。示例如下：
declare
v mystudent%ROWTYPE; --变量声明
va CONSTANT int := 2; -- 常量声明
begin
select * into v from mystudent where rownum < va;
end;
/
复制常量必须加上CONSTANT，并且必须赋初值。
变量作用域与可见性
变量的作用域是指变量的有效作用范围从变量声明开始，到块结束。如果PL/SQL块相互嵌套，则在内部块中声明的变量时局部的，只能在内部块中引用。而在外部块中声明的变量是全局的，既可以在外部块中引用，也可以在内部块中引用。
变量的可见性是指在PL/SQL单元中可以直接引用的变量。如果PL/SQL单元相互嵌套，并且在内部块中声明了与外部块中声明的变量同名，则在内部块中，同名的外部变量与内部变量都有效，但外部变量不可见。
declare
va CONSTANT int := 2; -- 常量
begin
dbms_output.put_line(va);
declare
va int := 100; -- := 为赋值运算符
begin
dbms_output.put_line(va);
end;
end;
/
复制控制语句
条件选择语句
包括IF语句和CASE语句两类
IF语句
IF语句包括以下三种情况：
- IF THEN
- IF THEN ELSE
- IF THEN ELSEIF
非常容易理解，举个例子：
declare -- 声明部分
v constant int default 100;
begin -- 执行部分
IF V > 100 then
dbms_output.put_line('v > 100');
else
dbms_output.put_line('v <= 100');
end if;
end;
/
复制CASE语句
可以理解为C语言中的switch case语句。也很容易理解，示例如下：
declare -- 声明部分
v constant int default 100;
begin -- 执行部分
case v
when 1 then dbms_output.put_line('1');
when 2 then dbms_output.put_line('2');
else dbms_output.put_line(v);
end case;
end;
/
复制循环语句
类似C语言中的FOR循环，while循环，示例如下：
declare -- 声明部分
v constant int default 100;
begin -- 执行部分
for a in 0..3 loop
dbms_output.put_line(a);
end loop;
end;
/
复制GOTO语句
略…
静态SQL语句。
什么是静态SQL语句呢？
PL/SQL中的静态SQL语句是指可以直接在PL/SQL中使用的SQL语句，其语法与标准的SQL语义完全一致。
由于PL/SQL程序执行时采用早期绑定，即在编译阶段对变量进行绑定，识别程序中标识符的位置，检查用户权限、数据库对象等信息，因此在PL/SQL中并不是所有语句就可以出现的，下列类型的SQL语句是可以出现的，因为它们不会修改数据库模式对象及其权限。
- 查询语句： SELECT
- DML语句： INSERT、UPATE、DELETE、MERGE （其中MERGE为lightdb22.1版本新增特性）
- 事务控制语句： COMMIT、ROLLBACK、BEGIN、SAVEPOINT
declare
vv mystudent%ROWTYPE;
begin
begin;
update mystudent set teachername = 'changan';
select * into vv from mystudent where rownum < 2;
commit;
end;
/
复制PL/SQL中的SELECT语句
为什么在这里单独把SELECT语句列出来了呢？因为与标准SELECT语句不同，在PL/SQL程序中，SELECT语句需要与INTO或BULK COLLECT INTO短语结合使用，将查询的结果保存到变量中。
- SELECT … INTO :单条记录查询
- SELECT … BULK COLLECT INTO ： 多条记录查询
示例如下：
-- 示例1
create procedure shenlan
as
declare
vid mystudent.id%TYPE;
v mystudent%ROWTYPE;
begin
select id into vid from mystudent where rownum < 2; -- 需要注意：SELECT ... INTO 语句只能查询一条记录的信息
select * into v from mystudent where rownum < 2;
end;
/
-- 示例2
declare
type c_cursor is ref cursor;
type type_a is table of char(1) index by binary_integer;
c_test c_cursor;
v_a type_a;
begin
open c_test for select * from dual where false;
loop
fetch c_test bulk collect into v_a;
dbms.put_line(v_a(1));
exit when c_test%notfound;
end loop;
v_a.DELETE; -- delete Associative array
end;
复制PL/SQL中的DML语句
PL/SQL中的DML语句对标准SQL语句中的DML语句进行扩展，允许使用变量，语法与标准SQL完全相同。
-- 示例程序
declare -- 声明部分
v constant int default 100;
begin -- 执行部分
update myteacher set id = v where rownum < 2;
end;
/
复制动态SQL语句。
什么是动态SQL语义呢？
PL/SQL中的静态SQL语句是指可以直接在PL/SQL中使用的SQL语句，其语法与标准的SQL语义完全一致。而动态SQL语句是指在执行时进行解析的SQL语句，通常PL/SQL程序中只可以执行静态的SQL语句，为了支持PL/SQL中动态SQL语句的执行，Oracle提供了本地动态SQL技术。
由于PL/SQL程序执行时采用早期绑定，即在编译阶段对变量进行绑定，识别程序中标识符的位置，检查用户权限、数据库对象等信息，因此在PL/SQL中可以直接执行静态SQL语句。但是在编写SQL语句时经常需要根据程序运行需要、客户选择等决定要操作的数据库对象，而这些操作都是在SQL语句运行时完成的，因此，这样的SQL语句是无法通过编译的，即静态SQL无法满足这类要求。
为了在PL/SQL中支持DDL语句、DCL语句已经更加灵活的SQL语句，在PL/SQL中引入动态SQL语句技术。动态SQL将包含不确定数据库对象、创建数据库对象等SQL语句封装成一个字符串，在编译阶段只进行字符串的语法检查，在运行时才进行SQL语句的分析与执行。
我们先看一下，直接在存储过程中使用建表语句创建表行不行呢？答案是不行：
create or replace procedure shenlan
as
declare
v int;
begin
select count(*) into v from pg_class;
if v <= 514 then
create table sl03(a int, b int);
end if;
select count(*) into v from pg_class;
end;
/
--执行结果
ERROR: syntax error at or near "create"
LINE 8: create table sl03(a int, b int);
^
复制怎么解决这个问题呢？动态SQL来解决，先看一个示例：
create or replace procedure shenlan
as
declare
v int;
vstr varchar(100);
begin
select count(*) into v from pg_class;
if v <= 514 then
vstr := 'create table sl03(a int, b int)';
execute immediate vstr;
end if;
select count(*) into v from pg_class;
end;
/
-- 创建表成功
复制这就是动态SQL的意义。
动态SQL与静态SQL的比较
在进行应用程序开发时，在某些情况下，只能使用动态SQL语句而不能使用静态SQL语句。
- 在程序编译阶段不能提供完整的SQL语句的情况。比如，在一些复杂的应用中需要根据用户的选择或输入决定数据的查询，此时在编译阶段是不能确定SQL语句的，只有执行时才知道，此时只能使用动态SQL语句。静态SQL语句无法实现功能。
- 执行非静态SQL语句，比如DDL语句，只能使用动态SQL。
还是要注意其核心的区别，就是动态SQL在编译阶段只进行字符串的语法检查，在运行时才进行SQL语句的分析与执行。
EXECUTE IMMEDIATE语句
本地动态SQL使用EXECUTE IMMEDIATE实现非查询语句、单行查询语句以及批绑定查询。示例如下：
create or replace procedure shenlan
as
declare
v int;
vstr varchar(100);
begin
select count(*) into v from pg_class;
if v <= 514 then
vstr := 'create table sl03(a int, b int)';
execute immediate vstr;
else
vstr := 'insert into sl03 values(0,0)';
execute immediate vstr;
end if;
select count(*) into v from sl03;
end;
/
复制记录类型。
什么是记录类型（Record）呢？
记录类型时PL/SQL中的复合类型，内部组件可以具有不同的数据类型。可以简单理解为是C语言中的结构体。因此记录类型有着广泛的应用。复合数据类型时指包含内部组件的数据类型。
PL/SQL支持两种复合数据类型：集合类型和记录类型。他们的区别是：集合类型中所有内部组件具有相同的数据类型，而记录类型内部组件可以具有不同的数据类型。集合类型后续文章会讲，本文主要讲述记录类型。
记录类型的声明与使用
可以使用RECORD语句在PL/SQL块中声明记录类型，该记录类型是局部的数据类型，只能在当前块中使用。如果记录类型是在独立子程序或包中声明的，则记录类型存储在数据库中。在包的规范中声明的记录类型是包的公共元素，可以在包外部以"packagename.typename"的形式引用。当删除包时，包中的记录也会被删除。
好了，我们看一下如何声明一个记录类型，如何去使用记录类型。举个具体的例子：
create or replace procedure shenlan
as
declare
type mytype is record (a int, b varchar(10)); -- 在存储过程中声明一个记录类型
v mytype;
begin
select id,name into v from mystudent where rownum < 2;
end;
/
drop procedure shenlan; -- 同时会删掉记录类型mytype、
复制当然，声明记录类型时也可以使用%TYPE，举个例子：
create or replace procedure shenlan
as
declare
type mytype is record (a mystudent.id%TYPE, b mystudent.name%TYPE); -- 在存储过程中声明一个记录类型
v mytype;
begin
select id,name into v from mystudent where rownum < 2;
end;
/
复制另外，实际使用中，还可以利用%ROWTYPE声明记录类型变量。可以通过%ROWTYPE声明与已知表、视图相一致的记录类型变量，该变量的各个字段的名称、顺序、数据类型与相应表、视图中列的名称、顺序、数据类型一致。举个例子：
create or replace procedure shenlan
as
declare
v mystudent%ROWTYPE; -- 利用%ROWTYPE声明记录类型变量
begin
select * into v from mystudent where rownum < 2;
end;
/
复制记录类型还可以在INSERT语句中应用，举个示例如下：
create or replace procedure shenlan
as
declare
v mystudent%ROWTYPE; -- 利用%ROWTYPE声明记录类型变量
begin
select * into v from mystudent where rownum < 2;
v.id := 10000;
insert into mystudent values v; -- 还可以这么用
end;
/
复制集合类型。
什么是集合类型呢？
前面我们讲过，PL/SQL支持两种复合数据类型：集合类型和记录类型。他们的区别是：集合类型中所有内部组件具有相同的数据类型，而记录类型内部组件可以具有不同的数据类型。
集合类型类似于其他高级语言中的数组，是相同类型元素的集合。PL/SQL中的集合类型分为联合数组类型（Associative Array）、嵌套表类型（Nested Table）和可变数组类型（Variable-Size Array）三种。
- 联合数组： 又称索引表，通过数字或字符串作为下标来查找集合中的元素，元素数量不限。
- 嵌套表：与联合数组类似，使用有序数字作为元素下标，元素最大数量不限。
- 可变数组： 使用有序数字作为元素下标，元素的最大数量在定义时确定。
联合数组
联合数组，又称索引表，是键值对的集合，每个键是唯一的索引值，没有固定的上下限，即元素的个数是不受限制的。而且其索引值可以是无序的。索引值类型可以是字符类型也可以是数字类型。举个例子：
create or replace procedure shenlan
as
declare
type mytype is table of int index by integer; -- 在存储过程中声明一个联合数组类型
v mytype;
begin
select id bulk collect into v from mystudent where rownum < 5;
end;
/
drop procedure shenlan; -- 同时会删掉类型mytype
复制联合数组可使用下标访问，举个例子：
create or replace procedure shenlan
as
declare
type mytype is table of int index by integer; -- 在存储过程中声明一个联合数组类型
v mytype;
begin
select id bulk collect into v from mystudent where rownum < 5;
for i in 1..4 loop
dbms.put_line(v(i)); -- 通过下标访问联合数组
end loop;
end;
/
复制嵌套表、可变数组
嵌套表和可变数组，暂不细述。
子程序。
子程序
子程序是命名的PL/SQL块，相比与匿名块，其优点再有可以反复调用，类似于C语言中的函数。
PL/SQL中的子程序包括过程和函数两种。通常，过程用于执行特定的操作，不需要返回值，而函数则有返回值。在调用时，过程可以作为一个独立的表达式被调用，而函数只能作为表达式的一部分被调用。
创建存储过程
创建存储过程可通过CREATE PROCEDURE语法创建，示例如下：
create or replace procedure shenlan
as
declare
type mytype is table of int index by integer; -- 在存储过程中声明一个联合数组类型
v mytype;
begin
select id bulk collect into v from mystudent where rownum < 5;
end;
/
drop procedure shenlan; -- 同时会删掉类型mytype
复制其中 OR REPLACE表示如果存储过程已经存在，则重建该存储过程。另外也可以指定存储过程的模式，示例如下：
create or replace procedure changan.shenlan -- changan为模式名，shenlan为存储过程名
as
declare
type mytype is table of int index by integer; -- 在存储过程中声明一个联合数组类型
v mytype;
begin
select id bulk collect into v from mystudent where rownum < 5;
for i in 1..4 loop
dbms.put_line(v(i)); -- 通过下标访问联合数组
end loop;
end;
/
-- 可通过call changan.shenlan进行调用。
复制删除可用drop procedure xxx语法删除存储过程。
创建函数
函数的创建与存储过程相似，不同之处在于函数有一个返回值。示例如下：
create or replace function sl03(a int) return int
as
declare
type mytype is table of int index by integer;
v mytype;
begin
select id bulk collect into v from mystudent where rownum < a;
for i in v.first..v.last loop
null;
end loop;
return v.count;
end;
/
复制调用函数/存储过程
调用函数与存储过程是不同的，调用存储过程可用CALL命令调用，调用函数可用SELECT进行调用。
调用函数的示例：
create or replace function sl03(a int) return int
as
declare
type mytype is table of int index by integer;
v mytype;
begin
select id bulk collect into v from mystudent where rownum < a;
for i in v.first..v.last loop
null;
end loop;
return v.count;
end;
/
复制在块中调用函数示例如下：
create or replace function sl03(a int) return int
as
declare
type mytype is table of int index by integer;
v mytype;
begin
select id bulk collect into v from mystudent where rownum < a;
return v.count;
end;
/
begin
update mystudent set id = sl03(2); -- 作为表达式的一部分被调用
end;
/
复制包
什么是包（package）？
包用于将相关的PL/SQL元素（过程、函数、变量、常量、自定义数据类型、游标等）组织在一起，作为一个完整的逻辑单元，编译后存储在数据库服务器中，作为一种全局的模式对象，供应用程序共享。
包由包规范和包体两部分组成，在数据库中独立存储。其中包规范声明了包中的所有公共元素，这些公共元素可以在包的外部被引用。如果包规范中声明了存储过程，则需要在包体中实现。在包体中，还可以定义私有元素，这些私有元素只能在包内部引用，而不能在包的外部引用。
除了包规范和包体的概念，还有内置包、用户包的区别，内置包不是用户定义的包，而是数据库本身提供的，用户可以不用创建，直接调用内置包。
好了，下面我们看一下如何创建包吧。
创建包
创建包可通过CREATE PACKAGE 语法创建，包括创建包规范，创建包体两部分。其中，包规范声明了包中的公共元素，如过程、函数、游标、数据类型、变量等。这些公共元素的作用域为包所在的模式。在包规范中声明的元素不仅可以在包的内部使用，也可以被应用程序调用。另外包可以支持重载子程序（即包中的子程序允许具有相同的名称，但具有不同的形式参数）。
示例如下：
create or replace package changan -- CREATE PACKAGE 创建包规范
is
procedure shenlan; -- 声明一个存储过程
end;
/
create or replace package body changan -- CREATE PACKAGE BODY 创建包体
is
procedure shenlan
is
begin
raise notice 'hello changan shenlan sl03';
end;
end;
end;
/
复制包中支持重载子程序：
create or replace package changan -- CREATE PACKAGE 创建包规范
is
procedure shenlan; -- 声明一个存储过程
procedure shenlan(a int); -- 重载子程序
end;
/
create or replace package body changan -- CREATE PACKAGE BODY 创建包体
is
procedure shenlan
is
begin
dbms.put_line('hello changan shenlan sl03');
end;
end;
procedure shenlan(a int) -- changan为模式名，shenlan为存储过程名
is
begin
dbms.put_line(a); -- 通过下标访问联合数组
end;
end;
/
复制其中， OR REPLACE表示如果包已经存在，则重建包。如果包规范中声明了游标或子程序，那么必须创建对应的包体，否则包体是可选的。包体与包规范必须同名且放在同一个模式中。
create or replace package changan
is
TYPE xxRec IS RECORD (x char, y int); -- 类型定义
-- 没有子程序以及游标，不必创建包体
end;
/
复制包体中还可以包含包规范中没有声明的变量、游标、类型、过程和函数等，但他们是私有元素，只能由同一包中的过程或函数使用。示例如下：
create or replace package pkgtb
as
procedure show;
procedure show(a int);
end;
/
create or replace package body pkgtb
as
v int := 10;
procedure show
is
begin
for v in (select b from t1) loop
dbms.put_line(v);
end loop;
end;
procedure show(a int)
is
begin
for v in (select b from t1) loop
dbms.put_line(v);
end loop;
end;
procedure privshow
is
begin
dbms.put_line('package private procedure');
end;
end;
/
-- 不能在包外调用私有元素
复制创建包体需要注意下面几项：
- 包体中函数和过程的原型必须与包规范中的声明完全一致
- 只有在已经创建包规范的情况下，才可以创建包体
- 如果包规范中不包含任何游标、函数或过程的声明，则可以不创建包体
最后，还有一个细节，就是包的构造函数，如果没有声明构造函数，则会默认添加如下为构造函数。
begin
null;
end;
复制也可以用户自己定义构造函数：
create or replace package changan -- CREATE PACKAGE 创建包规范
is
procedure shenlan; -- 声明一个存储过程
end;
/
create or replace package body changan -- CREATE PACKAGE BODY 创建包体
is
procedure shenlan
is
begin
dbms.put_line('hello changan shenlan sl03');
end;
end;
-- 构造函数
begin
dbms.put_line('package constructor function.');
end;
end;
/
复制包的调用
包的调用很简单，与存储过程/函数的调用类似。这里不再细述，
这里需要注意：在包规范中声明的任何元素都是公有的，在包外部可见，可通过package-name.element的形式进行调用，在包体中可以直接通过元素名调用。但是，在包体中定义而没有在包规范中声明的元素是私有的，包外无法访问，只能包内调用。
游标
什么是游标
当在PL/SQL块中执行查询语句或DML语句时，会在内存中分配一个私有的SQL缓冲区，缓冲区中包含了处理过程的必须信息，包括已经处理完的行数、指向被分析行的指针和查询情况下的活动集，即查询语句返回的数据集。该缓冲区域称为上下文。游标是指向该缓冲区的句柄或指针。
在PL/SQL程序中，根据游标的创建与管理的不同，可以分为两类：
- 隐式游标： 由系统创建并管理的游标。主要用于处理DML语句以及单行的SELECT…INTO语句。
- 显示游标： 由用户创建并管理的游标。用户需要声明并定义显示游标，为游标命名，并将游标与一个SELECT查询相关联，然后可以采用下列方式之一处理游标缓冲区中的查询结果集：
- 使用OPEN语句打开游标，使用FETCH语句检索游标，使用CLOSE语句关闭游标。
- 使用FOR LOOP语句处理查询结果集。
显示游标
可以在一个PL/SQL块、子程序或包中先声明一个显式游标，然后定义该显式游标，也可以同时进行显式游标的声明与定义。
定义游标的示例如下：
declare
cursor c1 return mystudent%rowtype; --声明游标c1
cursor c1 is select * from mystudent; -- 定义游标c1
r1 mystudent%rowtype;
begin
open c1;
fetch c1 into r1;
close c1;
end;
/
复制声明与定义一个显式游标：
declare
cursor c2 is select * from mystudent where rownum < 4; -- 显式游标必须在PL/SQL块、子程序、包的声明部分进行声明与定义
r2 mystudent%rowtype;
r3 c2%rowtype; -- 显式游标定义后，可以使用cursor_name%ROWTYPE定义记录类型的变量
begin
open c2; -- 打开游标
fetch c2 into r2;
fetch c2 into r3;
close c2;
end;
/
复制声明与定义一个显式游标需要注意下列事项：
- 显式游标必须在PL/SQL块、子程序、包的声明部分进行声明与定义
- 显式游标定义时可以引用PL/SQL变量，但变量必须在游标定义之前声明
- 定义显式游标时并没有生成数据，只是将显式游标定义信息保存到数据字典中
- 显式游标定义后，可以使用cursor_name%ROWTYPE定义记录类型的变量
定义好游标后如何使用游标呢？我们先看一下如何打开与关闭显式游标。显式游标声明和定义后，需要使用OPEN语句打开游标，才能对查询结果集进行处理。
create or replace procedure shenlan
is
cursor c2 is select * from mystudent where rownum < 4; -- 存储过程中定义游标
r2 mystudent%rowtype;
r3 c2%rowtype;
begin
open c2; -- 打开游标
fetch c2 into r2;
fetch c2 into r3;
close c2; -- 关闭游标
end;
/
复制当执行OPEN语句打开显式游标时，系统完成下列操作：
- 为显式游标定义中的查询语句的执行分配资源，如内存缓冲区。
- 处理显式游标定义中的查询语句。如果查询语句中包含变量或游标参数，需要确定变量或参数的值，以便确定最终的结果集。
- 把显式游标指针指向查询结果集的第一条记录之前的位置。
打开游标需要注意下列事项：
- 只有在打开显式游标时，系统才真正创建缓冲区，并从数据库中检索数据。
- 显式游标一旦打开，就无法再次打开，除非先关闭。
- 如果显式游标定义的查询语句中的变量或游标参数发生编号，则只能在下次打开游标时才生效。
显式游标对应的查询结果集处理完后，应该使用CLOSE语句及时关闭显式游标，以释放显式游标所占用的系统资源。显式游标关闭后，不能再检索显式游标对应的查询结果集，也不能引用显式游标的属性。
打开游标后，可以通过FETCH检索显式游标。打开游标，将查询结果集放入缓冲区后，需要将游标缓冲区的数据以记录为单位检索出来，然后在PL/SQL中进行过程化的处理。
create or replace package changan -- CREATE PACKAGE 创建包规范
is
cursor c2 is select * from mystudent where rownum < 4; -- 包中定义游标
r2 mystudent%rowtype;
r3 c2%rowtype;
procedure shenlan; -- 声明一个存储过程
end;
/
create or replace package body changan -- CREATE PACKAGE BODY 创建包体
is
procedure shenlan
is
begin
open c2; -- 打开游标
fetch c2 into r2; -- 检索显式游标
fetch c2 into r3;
close c2; -- 关闭游标
end;
end;
/
复制对游标第一次使用FETCH语句时，游标指针指向结果集中的第一条记录，因此操作的对象是结果集中的第一条记录，操作完成后，游标指针指向下一条记录。另外需要注意，游标指针只能向下移动，不能回退。如果想检索完第二条记录后又返回到第一条记录，则必须关闭游标，然后重新打开游标。
FOR循环检索游标
另外可使用FOR循环检索游标，利用FOR循环检索游标时，系统会自动打开游标、检索游标和关闭游标。用户只需要考虑如何声明与定义游标，以及进行业务处理。
declare
cursor cursor_name is select ...; -- 声明与定义游标
begin
for loop_variable in cursor_name loop
...
end loop;
end;
复制利用FOR循环检索游标时，系统首先隐含地定义一个数据类型为cursor_name%ROWTYPE的循环变量loop_variable，然后自动打开游标，从查询结果集中提取数据并放入loop_variable变量中，同时进行%FOUND属性检查以确定是否检索到数据。当查询结果集中所有的数据都检索完毕或循环中断时，系统自动关闭游标。
declare
cursor c3 is select * from mystudent where rownum < 4;
r3 c3%rowtype;
begin
for v in c3 loop -- 使用FOR循环检索游标
null;
end loop;
end;
/
复制显示游标的属性
可以通过显示游标的属性返回显示游标的状态。显式游标具有下列4个属性：
- %ISOPEN: 判断显式游标是否打开
- %FOUND： 判断是否检索到数据
- %NOTFOUND：判断是否没有检索到数据
- %ROWCOUNT： 返回已经检索到了多少条记录。
declare
cursor c3 is select * from mystudent where rownum < 4;
r3 c3%rowtype;
begin
if not c3%ISOPEN then
open c3;
end if;
fetch c3 into r3;
if c3%FOUND then
dbms.put_line('found');
end if;
if c3%NOTFOUND then
dbms.put_line('not found');
end if;
dbms.put_line(c3%ROWCOUNT);
if c3%ISOPEN then
close c3;
end if;
end;
/
复制隐式游标
隐式游标是由PL/SQL自动构建并管理的会话游标，又称为SQL游标。当用户每次执行SELECT语句或DML语句时，PL/SQL打开一个隐式游标。用户不能控制隐式游标，但可以通过隐式游标属性获取隐式游标的状态信息。隐式游标属性返回的是最近执行的SELECT语句或DML语句的信息。
declare
r3 mystudent%rowtype;
begin
select * into r3 from mystudent where rownum < 2;
if SQL%FOUND then -- 隐式游标属性
dbms.put_line(r3);
end if;
end;
/
复制游标变量
显式游标在定义时与特定的查询相关联，其结构是不变的，因此又称为静态游标。游标变量是一个指向查询结果集的指针，不与特定的查询关联，因此具有更大的灵活性。
需要注意的是检索游标变量时只能使用简单循环或WHILE循环，不能使用FOR循环。
declare
type refcur is ref cursor return mystudent%rowtype;
v refcur;
r mystudent%rowtype;
begin
open v for select * from mystudent where rownum < 5;
loop
fetch v into r;
exit when v%NOTFOUND;
end loop;
close v;
end;
/
复制批绑定
批绑定是PL/SQL中引入的提高程序运行性能的重要技术，可以实现SQL引擎和PL/SQL引擎之间数据的批量传递。我们下面进行详细的学习。
批绑定
PL/SQL中的SQL语句是由PL/SQL引擎发送到SQL引擎执行的，SQL引擎将执行的结果返回给PL/SQL引擎。这种在PL/SQL引擎与SQL引擎之间传送信息的过程称为上下文切换。
在数据库DML操作过程中，很多情况下是先将数据放入一个PL/SQL集合中，然后对该集合使用FOR循环进行迭代计算，把信息从PL/SQL引擎发送到SQL引擎执行。对于集合中的每一个元素，都将产生一个PL/SQL引擎到SQL引擎的上下文切换。频繁的上下文切换需要系统较大的开销，甚至导致通信阻塞，代码执行性能下降。
为了解决这种性能问题，对此，引入了批绑定技术。所谓批绑定是指将集合中的元素一次性地从PL/SQL引擎传递给SQL引擎，从而只需要在PL/SQL引擎与SQL引擎之间进行一次上下文切换。在PL/SQL中，批绑定特性是通过FORALL语句和BULK COLLECT短语实现的。FORALL语句将DML语句从PL/SQL引擎批量传送到SQL引擎，而不是每次传送一条语句。BULK COLLECT短语将SQL引擎的执行结果批量传送到PL/SQL引擎，而不是每次传送一行数据。如果查询操作或DML操作涉及多行数据，采用批绑定可以显著提高程序的运行性能。
FORALL
在PL/SQL应用程序中执行批量DML操作时可以使用FORALL语句来完成。
示例如下：
begin
forall i in 1..5
insert into vid(id) values (i); -- 批量插入5条数据
end;
end;
/
复制我们再举一个批量删除的例子
begin
forall i in 1..5
delete from vid where id = i; -- 批量删除数据
end;
end;
/
复制BULK COLLECT
利用BULK COLLECT可以将数据批量的从SQL引擎传送给PL/SQL引擎，而不是每次传送一行数据。可以在下列语句中使用：
- SELECT INTO
- FETCH INTO
- INSERT、DELETE、UPDATE以及EXECUTE IMMEDIATE语句的 RETURNING INTO短语
利用BULK COLLECT语句，每个处理语句返回一个完整的结果集，一次性的存储到一个或多个集合变量中，相比于利用LOOP循环每次返回一条记录的操作方式，效率明显更高。
示例如下：
declare
type car is table of int index by integer;
sl03 car;
begin
select id bulk collect into sl03 from mystudent;
for i in sl03.first..sl03.last loop
dbms.put_line(i);
end loop;
end;
/
复制个人思考
到这里我们基本上将存储过程大致过了一遍。对于用户来讲，不推荐使用存储过程，因为目前各数据库的存储过程基本互相不兼容，后期如果迁移数据库的话，存储过程是较难迁移的。一旦使用存储过程，基本就被数据库绑死了。并且存储过程代码的开发，维护，测试都不是很友好。 目前国产数据库中有一个很大的问题就是兼容Oracle，其中就包括兼容Oracle的存储过程，即使能做兼容，也无法做到100%兼容Oracle数据库。因为数据库底层的实现以及体系都存在不同，难以做到100%，除非按照Oracle数据的原理以及机制重新开发设计，而国产数据库纯自研的少，多数是在开源数据库基础上做兼容，这样就基本不可能实现100%兼容了。
