存储过程

一、什么是存储过程
存储过程：就是一段存储在数据库中执行某种功能的程序，其中包含一条或者多条SQL语句。
也可以通俗的理解为是存储在数据库服务器中的封装了一段或多段的sql语句的PL/SQL代码块。

二、存储过程的特点（优点）：
1、简化复杂的操作。存储过程可以把需要执行的多条sql语句封装到一个独立的单元，
用户只需要调用这一个单元就能达到目的，这样就实现了一个人编程多人调用，同时缩短了
平均开发周期。
2、增加数据的独立性。与视图的效果类似，利用存储过程可以把数据库基础数据和程序
（或者用户）隔离开来，当基础数据的结构发生变化时，可以修改存储过程，这样对程序
来说基础数据的变化时不可见的，也就不需要修改程序代码了。
3、提高安全性。有效的降低了出错的几率，如果不使用存储过程，而想要实现某些操作可能需要执行多条单独的sql语句，
这样出错的概率也就会增加，而存储过程只需要编译一次，而且存储过程还可以进行权限的设置。
4、提高了性能。在执行比较复杂的的功能时，减少了访问数据库的次数。

5、PL/SQL的数据类型
%TYPE 和 %ROWTYPE
当不知道表中变了的数据类型时，要定义一个与某个变量的数据类型或者数据库表中的某个列的数据类型一致的变量时，可以使用%TYPE。

%ROWTYPE
如果要定义一个与数据库中某                                                                      个表结构一致的记录类型的变量，可以使用%ROWTYPE、

%TYPE  定义和表中某个变量或者某个列数据类型一致的数据类型    v_sal scott.emp.sal%TYPE
%ROWTYPE  定义与表结构一致的数据类型。                       v_emp scott.emp.%TYPE

6、定义变量和常量
var_name [constant] datatype [not null] [default:=expression];

  每一行只能定义一个变量或者常量
  如果加上constant 关键字，就表示为常量，必须为他赋值
  如果使用了NOT NULL，那么必须要赋初始值，
  

7、局部变量和全局变量

三、存储过程的创建

create procedure 存储过程的名称
as
begin
   .....
end;
/

比如在sql*plus中创建一个存储过程
create procedure test
as
begin
     dbms_output.put_line('我的第一个过程')
end;
/

执行之前我们应该先查看我们的输出函数是否打开，因为函数语句
dbms_output.put_line('我的第一个过程')表示的是输出一个‘我的第一个过程’这样的一个语句

serveroutput  函数的设置
show serveroutput
会提示serveroutput off 或者serveroutput on

如果提示是关闭的我们就需要运行一下代码
set serveroutput on
设置成功之后我们会看到serveroutput onsize unlimited format word_wrapped  表示设置成功。

之后我们就可以执行存储语句：

begin
     text;
end;
/
成功之后就会显示输出的语句

也可以通过 exec text 来执行存储过程

四、查看存储过程
存储过程一旦被创建就会存储到数据库服务上，oracle允许开发人员查看已经存在的存储过程的脚本
查看的方式可以通过user_source这个视图来查看

如：
select * 
from user_source where name='TEST'   注意这里用user_source这一个视图查看我们的存储过程和函数时，一定要用大写的名称不然无法实现
order by line;

如果要查看oracle所有的存储过程时，需要使用到视图ALL_SOURCE，视图USER_SOURCE是当前用户的视图不是oracle所有的视图。

五、查看存储过程的错误
在实际操作是可以运用下面的语句来检查我们所写的存储过程所出现的错误

show errors procedure 存储过程的名称

六、存储过程的参数
（1）无参数存储过程
就是不带任何参数的存储过程

比如：把表中productinfo中价格最低的3件产品的desperation字段设置成“促销商品”字样

1、第一步，首先要查找出productinfo中商品价格最低的3件商品
2、第二步，把价格最低的3件商品的desperation字段写上“促销商品”字样

create procedure 商品修改
as
begin
update productinfo set desperation = "促销商品"
where productid in
(
select productid from
(select * from productinfo order by productprice asc)
where rownum < 4

);
commit;
end;
/

（2）PL/SQL子程序的参数模式分为IN,OUT,IN OUT三种。

IN (默认的参数模式)表示当存储过程被调用时，实参值被传递给形参；在过程内，形参起常量作用，只能读该参数，不能修改该参数，当子程序调用结束之后，该参数的值没有改变。所有IN模式的参数可以是常量或者表达式。

OUT 表示当过程被调用时，实参值被忽略；在过程内，形参起为初始化的PL/SQL变量的作用，初始值为NULL，可以进行读/写操作，当子程序调用结束之后，形参值被赋给实参。所以OUT模式只能是变量，不能是常量或者表达式。

IN OUT表示当过程被调用时，实参值被传递给形参；在过程内，形参起已经初始化PL/SQL变量的作用，可读可写；当子程序调用结束之后。形参值被给实参。所以IN OUT模式只能是变量，不能是常量或者表达式。

 

如下实例可用来区分三种参数的区别：

create or replace procedure paramodetest(

p_inparameter IN NUMBER,

p_outparameter OUT NUMBER,

p_inoutparameter IN OUT NUMBER,

)

is

v_localvar number:=0;

dbms_output.put_line('in mode');

if(p_inparameter is null) then

    dbms_output.put_line('p_inparameter is null');

  else

   dbms_output.put_line('p_inparameter='||p_inparameter);

 end if;

if(p_outparameter is null) then

    dbms_output.put_line('p_outparameter is null');

  else

   dbms_output.put_line('p_outparameter='||p_outparameter);

 end if;

if(p_inoutparameter is null) then

    dbms_output.put_line('p_inoutparameter is null');

  else

   dbms_output.put_line('p_inoutparameter='||p_inoutparameter);

 end if;

--read and wirte in mode parameter

v_localvar:=p_inparameter;  --合法

--p_inparameter:=7;             --不合法

--out mode

p_outparameter:=7;             --合法

v_localvar:=p_outparameter;  --合法

--inout mode

p_inparameter:=8;             --合法

v_localvar:=p_inparameter;  --合法

--the end of vaues

if(p_inparameter is null) then

    dbms_output.put_line('p_inparameter is null');

  else

   dbms_output.put_line('p_inparameter='||p_inparameter);

 end if;

if(p_outparameter is null) then

    dbms_output.put_line('p_outparameter is null');

  else

   dbms_output.put_line('p_outparameter='||p_outparameter);

 end if;

if(p_inoutparameter is null) then

    dbms_output.put_line('p_inoutparameter is null');

  else

   dbms_output.put_line('p_inoutparameter='||p_inoutparameter);

 end if;

end;

/

 

调用该存储过程

declare

v_in number:=1;

v_out number:=2;

v_inout number:=3;

begin

dbms_output.put_line('before get it parameter');

dbms_output.put_line('v_in='||v_in||'v_out'||v_out||'v_inout'||v_inout);

paramodetest(v_in,v_out,v_inout);     --调用存储过程，传入参数

dbms_output.put_line('传入参数之后得到的值');

dbms_output.put_line('v_in='||v_in||'v_out'||v_out||'v_inout'||v_inout);

end;

/

运行结果：

传入参数之前：

v_in=1,v_out=2,v_inout=3

传入参数之后

inside modetest;

p_inparameter=1,p_outparamter is null,p_outparameter=3  --应为out类型的参数不能修改所以为空

at the of modetest

p_inparameter=1,p_outparamter =7,p_outparameter=8

传入参数之后得到的值:

v_in=1,v_out=7,v_inout=8

 

（3）定义参数时的限制

在声明参数时，不能定义形参的长度或者精度、刻度，他们是作为参数传递机制的一部分被传递的，是由实参决定的。

（4）参数的传递，当子程序被调用时，实参和形参之间值得传递方式取决于参数的模式。IN参数为应用传递，即实参的指针被传递给形参.OUT/IN OUT参数为值传递，即参数的值被复制给形参。

（5）参数的默认值

可以为参数设置默认值，这样存储过程调用时如果没有给参数传递数值，则采用默认值。注意：由默认值得参数应该放在参数列表的最后。

如：

create or replace procedure defaultparameter(

p_a number;

p_b number default 10

)

as

begin

...

end;

/

 

(6)存储过程的返回值

通常情况下，存储过程不需要返回值，如果需要返回值，可以通过函数调用来实现；但是如果希望返回多个值，则可以用OUT或者IN OUT模式参数来实现。

 

 

（7）存储过程的调用

OUT 和IN OUT类型的参数：由于他们的值在程序结束的时候需要将形参的值赋给实参，因此必须是变量，而不能是常量。

IN 参数：可以是常量，也可以是变量