存储过程

　　存储过程（Stored Procedure）是一种在数据库中存储复杂程序（命名PL/SQL块），以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行（可反复调用执行）。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。 

优点

• 存储过程可封装，并隐藏复杂的商业逻辑。
• 存储过程可以回传值，并可以接受参数。
• 在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。
• 存储过程可以用在数据检验，强制实行商业逻辑等。

创建存储过程

CREATE [OR REPLACE] PROCEDURE pro_name [(parameter1[,parameter2]…)] IS|AS
BEGIN
  plsql_sentences;
[EXCEPTION]
  [dowith _ sentences;]
END[pro_name];

-- pro_name:存储过程名称，如果数据库中已经存在了此名称，则可以指定"or replace"关键字覆盖掉原来的存储过程
-- parameter1:存储过程参数，若是输入参数，则在其后指定in关键字；若是输出参数，则在其后指定out关键字。在in/out/inout后是参数的数据类型，但不能指定该类型的长度。

　　例：创建一个存储过程，向课程表插入一条记录

CREATE PROCEDURE pro_insert_course IS
BEGIN
    INSERT INTO tb_course VALUES(666,'Oracle从删库到跑路',8,'大神级讲师授课需抢座');
    COMMIT;
    dbms_output.put_line('插入新记录成功');
END pro_insert_course;

　　在当前模式下如果已经存在同名存储过程，可以使用OR REPLACE覆盖已经存在的存储过程；如果不存在，则直接创建即可

　　调用存储过程

　　　　1、在sql plus环境中，使用exec或者execute进行调用

execute pro_insert_course

　　　　2、在pl/sql代码块中调用

begin
    pro_insert_course；
end;

存储过程参数

　　为了增加灵活性，可以实现向存储过程传入参数。参数是一种向程序单元输入和输出数据的机制，存储过程可以接收多个参数，参数模式包括IN、OUT和INOUT

1．IN模式参数
　　这是一种输入类型的参数，参数值由调用方传入，并且只能被存储过程读取。最常用也是默认的参数模式

　　案例：创建一个存储过程，向课程表插入一条记录，课程信息由用户指定

CREATE PROCEDURE pro_insert_course(
    p_cosid IN NUMBER,
    p_cosname IN VARCHAR2,
    p_credit IN INT,
    p_intro IN VARCHAR2) IS
BEGIN
    INSERT INTO tb_course VALUES(p_cosid,p_cosname,p_credit,p_intro);
    COMMIT;
    dbms_output.put_line('插入新记录成功');
END pro_insert_course;

--注：参数的类型不能指定长度，在调用存储过程时，用户需要向存储过程传递相应参数值

（1）指定名称传递
　　指定名称传递是指在向存储过程传递参数时需要指定参数名称，即参数名称在左侧，中间是赋值符号“=>”，右侧是参数值，其语法格式如下：

pro_name(parameter1=>value1[,parameter2=>value2]…)
-- 调用上面存储过程
begin
　　pro_insert_course(p_credit=>10,p_cosid=>666,p_cosname=>'java从入门到放弃',p_intro=>'');
end;

　　可以看出，使用指定名称的方式传递参数值与参数的定义顺序无关，但与参数个数有关

（2）按位置传递
　　指定名称传递参数虽然直观易读，但也有缺点，就是参数过多时，会显得代码冗长，反而变得不容易阅读。这样用户就可以采取按位置传递参数，采用这种方式时，用户提供的参数值顺序必须与存储过程中定义的参数顺序相同。

（3）混合方式传递

　　混合方式就是将前两种方式结合到一起，这样就可以兼顾二者的优点.

exec pro_insert_course(60,p_cosname=>'HTML',p_intro=>'',p_credit=>2);

2．OUT模式参数
　　这是一种输出类型的参数，表示这个参数在存储过程中已经被赋值，并且这个参数值可以传递到当前存储过程以外的环境中，关键字out位于参数名称之后。

　　案例：创建一个存储过程，按传入的课程编号得到课程的名称和学分信息

CREATE OR REPLACE PROCEDURE pro_search_course(
    p_cosid IN NUMBER,
    p_cosname OUT VARCHAR2,
    p_credit OUT INT) IS
BEGIN
    SELECT cos_name,credit INTO p_cosname,p_credit FROM tb_course WHERE cos_id = p_cosid;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        dbms_output.put_line('该课程编号不存在');
END pro_search_course;

　　调用：

　　1、在pl/sql块中调用

DECLARE
    v_name tb_course.cos_name%type;
    v_credit tb_course.credit%type;
BEGIN
    pro_search_course(666,v_name,v_credit);
    --输出参数值
END;

　　在sql plus中声明变量调用

variable v_name varchar2(20);
variable v_credit int;
exec pro_search_course(666,:v_name,:v_credit);

--使用print命令打印输出绑定的变量值
print v_name v_credit;
--使用select语句检索绑定的变量值
select :v_name,:v_credit from dual;

INOUT模式参数

　　在执行存储过程时，IN参数不能被修改，只能根据被传入的值（或默认值）为存储过程提供数据，而OUT类型的参数只能等待被赋值，而不能像IN那样为存储过程提供数据。INOUT模式参数可以兼顾两种参数的特点，在调用存储过程时，可以从外界向该类型的参数传入值；在执行完存储过程后，可以将该类型的参数值返回给外界。

create or replace procedure pro_square(
  num in out number,--计算它的平方或平方根
  flag in boolean) is  --计算平方或平方根的标识
  i int := 2; --计算平方的参数
begin
  if flag then --若为true
    num := power(num,i);--计算平方
  else
    num:=sqrt(num);--计算平方根
  end if;
end;

调用

declare
  var_number number;--存储要进行运算的值和运算后的结果
  var_temp number;--存储要进行运算的值
  boo_flag boolean;--平方或平方根的逻辑标记
begin
  var_temp :=3;--变量赋值
  var_number :=var_temp;
  boo_flag := false;--false表示计算平方根；true表示计算平方
  pro_square(var_number,boo_flag);--调用存储过程
  if boo_flag then
    dbms_output.put_line(var_temp ||'的平方是：'||var_number);--输出计算结果
  else
    dbms_output.put_line(var_temp ||'平方根是：'||var_number);
  end if;
end;

IN参数的默认值

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

CREATE PROCEDURE pro_insert_course(
    p_cosid IN NUMBER,
    p_cosname IN VARCHAR2,
    p_credit IN INT,
    p_intro DEFAULT '大神级讲师授课需抢座' IN VARCHAR2) IS
BEGIN
    INSERT INTO tb_course VALUES(p_cosid,p_cosname,p_credit,p_intro);
    COMMIT;
    dbms_output.put_line('插入新记录成功');
END pro_insert_course;