Oracle存储过程

存储过程是一种命名的PL/SQL程序块，它既可以没有参数，也可以有若干个输入、输出参数，甚至可以有多个既作输入又作输出的参数，但它通常没有返回值。存储过程被保存在数据库中，它不可以被SQL语句直接执行或调用，只能通过EXECUT命令执行或在PL/SQL程序块内部被调用。由于存储过程是已经编译好的代码，所以其被调用或引用时，执行效率非常高。

1、创建存储过程

创建存储过程需要使用PROCEDURE关键字，其基本语法格式如下：

create [or replace] procedure pro_name [(parameter1[,parameter2]…)] is|as
begin
  plsql_sentences;
[exception]
  [dowith_sentences;]
end [pro_name];

pro_name：存储过程名称。

parameter1：存储过程的参数，若是输入参数，则需要在其后面指定“in”关键字；若是输出参数，则需要在其后面指定“out”关键字。在in或out关键字的后面是参数的数据类型，但不能指定该类型的长度。

plsql_sentences：PL/SQL语句，它是存储过程功能实现的主体。

dowith_sentences：异常处理语句，也是PL/SQL语句，这是一个可选项。

【实例】创建一个存储过程，定义1个in模式的变量，用于传人部门编号；定义2个out模式的字符类型的参数，分别用于返回部门名称和部门地址。

create or replace procedure pro_dept(num_deptno in number,var_dname out dept.dname%type,var_loc out dept.loc%type) is   
  /*这里定义存储过程的内部变量*/
  var_msg varchar2(100);
begin 
  select dname,loc
  into var_dname,var_loc
  from dept
  where deptno = num_deptno;
exception
  when no_data_found then    --若查询语句无返回记录
    var_msg:='未找到部门编号为'||num_deptno||'的记录';
    dbms_output.put_line(var_msg);
end;

2、调用存储过程

【实例】在SQL*Plus环境中，使用execute（exec）命令执行pro_dept存储过程。

SQL> variable var_dname varchar2(50);
SQL> variable var_loc varchar2(50);
SQL> exec pro_dept(10,var_dname,var_loc);

【实例】在PL/SQL块中调用pro_dept存储过程

declare
  var_dname dept.dname%type; 
  var_loc dept.loc%type;
begin
  pro_dept(10,var_dname,var_loc);
  dbms_output.put_line('部门名称：'||var_dname||' 部门地址：'||var_loc);
end;

3、存储过程的参数

存储过程可以接受多个参数，参数迷失包括IN、OUT、IN OUT这3中。

IN模式参数：这是一种输入类型的参数，参数值由调用方传人，并且只能被存储过程读取。

OUT模式参数：这一个输出类型的参数，表示这个参数在存储过程中已经被赋值，并且这个参数值可以传递到当前存储过程以外的环境中。

IN OUT模式参数：可以兼顾其他两种参数的特点，在调用存储过程时，可以从外界向该类型的参数传入值；在执行完存储过程之后，可以将该参数返回值传给外界。

4、IN参数的默认值

Oracle支持在声明IN参数的同时给其初始化默认值，这样在存储过程调用时，如果没有向IN参数传入值，则存储过程可以使用默认值进行操作。

【实例】创建一个存储过程，定义1个带默认值得IN参数。

create or replace procedure pro_seldept(num_deptno in number default 10) is
  var_dname dept.dname%type;  --定义存储过程内部变量，保存部门名称
  var_loc dept.loc%type;      --定义存储过程内部变量，保存部门地址
begin
  select dname,loc
  into var_dname,var_loc
  from dept
  where deptno = num_deptno;
  dbms_output.put_line('部门名称：'||var_dname||' 部门地址：'||var_loc);
end;

调用存储过程：

begin
  pro_seldept();     --查询默认部门编号的信息
  pro_seldept(20);   --查询编号为20的部门信息
end;