pl/sql 存储过程
复制于https://my.oschina.net/u/3872757/blog/1848658，以防后续找不到
首先要了解一下oracle的存储过程，都有哪些结构，而plsql是一个辅助工具，是能帮助我们更轻松的实现存储过程。
上述就是一个无参的存储过程实例，一个存储过程大体分为这么几个部分：
1）、创建语句：create or replace procedure 存储过程名称 [authid current_user] ；
“”[]“”中括号的内容是可选的，其表示修改存储过程，加入authid current_user时存储过程可以使用role权限。否则会报ORA-01031权限不足。
如果没有or replace语句，那只是新建一个存储过程，如果系统中存在相同的存储过程，则会报错，Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程。
存储过程名定义：包括存储过程名和参数列表、参数名和参数类型。参数列表可不写，如例子所示。参数名不能重复，并且每个参数之间用分号“ ；” 隔开， 参数传递方式:IN, OUT, IN OUT。如下面例子所示：
下面说明一下参数传递方式：
in：表示输入参数，调用存储过程时从外面传进来的，它的值不能修改。
out：表示输出参数，当一个参数被指定为OUT类型时，如果还未调用存储过程之前对该参数进行了赋值，那么在存储过程中该参数的值仍然是null，但是如果在调用过程中对该参数进行赋值，那么值不为null。
in out：表示输入输出参数，它的值可以修改。
参数的数据类型只需要指明类型名即可，不需要指定宽度。参数的宽度由外部调用者决定。过程可以有参数，也可以没有参数。
我们看到例子中存在一个“”as“”，它表示变量声明块，可以理解为plsql中的declare关键字，用于声明变量。除了as外，还有is。变量声明块用于声明该存储过程需要用到的变量，它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。
其中，as和is的区别：在视图(VIEW)中只能用AS不能用IS；
在游标(CURSOR)中只能用IS不能用AS。
过程语句块：从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。
异常处理块：关键字为exception ，为处理语句产生的异常。该部分为可选
结束块：由end关键字结果。
2)、下面讲解一下参数列表中参数的默认值
通过default 关键字为存储过程的参数指定默认值。在对存储过程调用时，就可以省略默认值。
值得注意的是：默认值仅仅支持IN传输类型的参数。OUT 和 IN OUT不能指定默认值
上述情况是default关键字修饰的是最后一个参数，如果是修饰第一个参数呢？
如果我们想使用第一个参数的默认值时，exec procdefault2('aa'); 这样是会报错的。
那怎么变呢？可以指定参数的值。
SQL> exec procdefault2(p2 =>'aa'); 这样就OK了，指定aa传给参数p2。
3）、继续讲解存储过程内部块
我们知道了存储过程的结构，语句块由begin开始，以end结束。这些块是可以嵌套。在语句块中可以嵌套任何以下的块：Declare … begin … exception … end;
需要注意变量的作用域。
4）、存储过程中的循环
存储过程的循环语句块有：for...in...loop、while和loop循环。下面分别给予相关实例。
（1）、for...in...loop
实例一 循环遍历游标：
create or replace procedure proc_test
as
cursor c1
is
select * from dat_trade;
begin
for x in c1
loop
dbms_output.put_line (x.id);
end loop;
end proc_test;实例二 根据数值进行循环：
create or replace procedure proc_test (v_num in NUMBER)
as
begin
for x in 1..100 loop
dbms_output.put_line (x);
end loop;
end proc_test;实例三 在过程里指定输入参数 v_num. 在调用过程时指定循环次数：
create or replace procedure proc_test (v_num IN NUMBER)
as
begin
for x in 1 .. v_num
loop
dbms_output.put_line (x);
end loop;
end proc_test;（2）、loop循环
loop
delete from orders
where senddate < to_char (add_months (sysdate, -3),'yyyy-mm-dd') and rownum < 1000;
exit when SQL%ROWCOUNT < 1;
commit;
end loop;这里的 SQL%ROWCOUNT 是隐士游标。 除了这个，还有其他几
个：%found，%notfound， %isopen。
（3）while循环：
create or replace procedure proc_test (v_num in number)
as
i number := 1;
begin
while i < v_num
loop
begin
i := i + 1;
dbms_output.put_line (i);
end;
end loop;
end proc_test;5）、 存储过程中的判断
存储过程的判断语句块有：if 条件语句、case ... when ... end case两种
下面给出实例：
（1）、单if实例（if...then...end if; && if...then...else...end if;）
实例一：
create or replace procedure pro_test is
--逻辑判断变量
exit_table_data varchar2(40); --判断表数据是否存在
--sql语句执行变量
execu_sql varchar2(2000);
begin
execu_sql := 'select count(*) from user';
execute immediate execu_sql into exit_table_data;
if exit_table_data=0 then
execu_sql := 'insert into user values('大明')';
execute immediate execu_sql;
commit;
end if;
end pro_test;实例二：
create or replace procedure pro_test is
--逻辑判断变量
exit_table_data varchar2(40); --判断表数据是否存在
--sql语句执行变量
execu_sql varchar2(2000);
begin
execu_sql := 'select count(*) from user';
execute immediate execu_sql into exit_table_data;
if exit_table_data=0 then
execu_sql := 'insert into user values('大明')';
execute immediate execu_sql;
commit;
else
execu_sql:= 'update user set username='大华'';
execute immediate execu_sql;
commit;
end if;
end pro_test;（2）、多if实例（if...then...elseif...then...else...end if;）
create or replace procedure proc_test (v_num in number)
as
begin
if v_num < 10
then
dbms_output.put_line (v_num);
elseif v_num > 10 and v_num < 50
then
dbms_output.put_line (v_num - 10);
else
dbms_output.put_line (v_num - 50);
end if;
end proc_test;（2）、case ... when ... end case
实例一：
create or replace procedure proc_test (v_num in number)
as
begin
case v_num
when 1 then
dbms_output.put_line (v_num);
when 2 then
dbms_output.put_line (v_num);
when 3 then
dbms_output.put_line (v_num);
else null;
end case;
end proc_test;6）、给变量赋值
我们在参数列表定义输出参数、输入输出参数yi，以及在参数名位置定义参数，可能都需要一个赋值操作，让查询sql的结果赋值或者定义输入参数赋值等等，那么我们可以使用什么方法给这些参数赋值呢？
下面列举出一些常用的为变量赋值的方法：
1、直接法
使用“ := ” 的符号为变量赋值，例如: v_pare := "0"；
2、select into
假如变量为v_pare,那么为它赋值的语句为：select count(*) into v_pare from user；
3、execute immediate 变量名（查询sql语句结果赋值给它的变量）into 变量名
例如：
v_sqlfalg := 'select count(*) from user_tables where table_name='''||v_tablename || '''';
execute immediate v_sqlfalg into v_flag;
其中，v_tablename、v_sqlfalg、v_flag都是变量；
select into和execute immediate的区别：
1、execute immediate 赋值的变量是通过select语句查询出来的，而select into是直接赋值给变量的。
7）、存储过程跳出循环
oracle存储过程可以使用3种方法跳出循环，分别是return、exit、continue；
它们的区别为：
1、return是直接跳出存储过程；
2、如果存在多层循环，exit是直接跳出存储过程的本次循环，而去执行上一级循环的循环条件；
3、continue是本次循环后面的代码部分不再执行，转而执行本循环的下一次循环。
8）、Oracle存储过程中是否需要写commit的问题
是否需要在存储过程中写commit主要依据需求:
(1) 如果是不需要在存储过程中进行提交，而是由调用程序负责提交或者回滚，那么不需要在存储过程中commit或者rollback。
(2) 如果不想由调用程序负责提交或者回滚，那么就应该在存储过程中进行commit或rollback;
另外,如果是纯后台数据库开发，一定要写.只是写的时机同样是分为两种,一种是写在过程里面;另一种是写在调用存储过程之后. 而之所以要写commit的原因是,Oracle的默认事务级别是READ COMMITED;默认情况下，Oracle是不会自动提交的,需要手动提交才ok.
9）、使用plsql创建存储过程步骤
1、登录plsql后，在对象框中找到“Procedures”,点击右键，找到新建，如图所示：
2、进入到新建界面，如图所示：
3、最终就进入到存储过程结构中，你要做的就是编写存储过程逻辑。
10）、使用plsql对存储过程进行调试
1、在“Procedures”下拉列表中找到已经编写好的存储过程，点击右键，找到“测试”，如图所示：
2、PL\SQL会打开调试界面，图中位置1的按钮就是开始调试的按钮，在调试之前要填写输入参数的值，位置2就是填写参数的地方，如果有多个参数，会有多行参数框，按参数名填写相应的参数即可，如果没有参数，可以不填。
3、填写完参数，单击开始调试按钮后，调试的界面会发生一些变化。图中位置1的变化，说明存过已经处于执行状态，别人不能再编译或者执行。位置2的按钮就是执行按钮，单击这个按钮存过会执行完成或者遇到bug跳出，否则是不会停下来的，调试时不会用这个按钮的。位置3的按钮才是关键——单步执行，就是让代码一行一行的执行，位置4的按钮是跳出单步执行，等待下一个指令。
