存储过程

　　存储过程是一种命名的PL/SQL块，它既可以没有参数，也可以有若干个输入、输出参数，甚至可以有多个既可以作为输入又作输出的参数，一般没有返回值。存储过程被保存在数据库中，它不可以被SQL语句直接执行或调用，只能通过execut命令执行或在PL/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关键字的后面是参数的数据类型，该类型不能指定长度。

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

　　dowith _ sentences：异常处理语句，也是PL/SQL语句，可选项。

注意

案例：创建一个存储过程，该过程实现向课程表中插入一条记录

-- 创建一个存储过程，用于向课程表添加一条课程记录
CREATE PROCEDURE pro_insert_course IS
BEGIN
  INSERT INTO COURSE VALUES(10,'JAVA',8,'大神级讲师授课，需抢座');
  COMMIT;
  DBMS_OUTPUT.put_line('添加课程成功');
END pro_insert_course;

案例：在当前模式下，如果数据库中存在同名的存储过程

-- 创建一个存储过程，用于向课程表添加一条课程记录
CREATE OR REPLACE PROCEDURE pro_insert_course IS
BEGIN
  INSERT INTO COURSE VALUES(10,'JAVA',8,'大神级讲师授课，需抢座');
  COMMIT;
  DBMS_OUTPUT.put_line('添加课程成功');
END pro_insert_course;

调用存储过程

1.在sql plus环境中，使用execute命令执行

exec pro_insert_course; --execute pro_insert_course;

2.在PL/SQL代码块中调用

-- 调用存储过程
BEGIN
  pro_insert_course;
END;

存储过程的参数

　　前面创建的存储过程都没有涉及参数。oracle为了增强存储过程的灵活性（前面的存储过程每次都是添加相同的数据，这样会违反相关约束），可以实现向存储过程传入参数。参数是一种向程序单元输入和输出数据的机制，存储过程可以接收多个参数，参数模式包括IN、OUT和IN OUT3种。

IN模式

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

CREATE OR REPLACE PROCEDURE pro_insert_course(p_cosid IN INT,p_cosname IN VARCHAR2,p_credit IN INT,p_intro IN VARCHAR2) AS
BEGIN
  INSERT INTO COURSE VALUES(p_cosid,p_cosname,p_credit,p_intro);
  COMMIT;
  DBMS_OUTPUT.put_line('添加课程成功');
END;

-- 1、指定名称传递(可以不按照参数定义的顺序传递)
BEGIN
  pro_insert_course(p_cosname=>'ORACLE',p_credit=>6,p_cosid=>20,p_intro=>null);
END;
-- 2、按位置传递

BEGIN
  pro_insert_course(20，'ORACLE',6,20,null);
END;

-- 3、混合传递

BEGIN
  pro_insert_course(20，p_cosname=>'ORACLE',p_credit=>6,p_intro=>null); 
END;

OUT模式

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

-- 创建一个存储过程，根据课程编号得到课程名称和课程学分信息
CREATE OR REPLACE PROCEDURE PRO_GET_COURSE(p_cosid IN INT,
       p_cosname OUT VARCHAR2,p_credit OUT NUMBER) IS
BEGIN
  SELECT COS_NAME,CREDIT INTO p_cosname,p_credit FROM COURSE WHERE COS_ID=p_cosid;
END;

DECLARE
  v_cosid INT:=10;
  v_cosname VARCHAR2(20);
  v_credit NUMBER;
BEGIN
  PRO_GET_COURSE(v_cosid,v_cosname,v_credit);
  DBMS_OUTPUT.put_line('课程名称:' || v_cosname || ',课程学分:' || v_credit);
END;

在 sql plus中调用

-- 在sql plus中声明变量
var v_name varchar2(12);
var v_credit Integer;
-- 调用存储过程，将存储过程输出结果赋值给
exec pro_get_course(10,:v_name ,:v_credit);
-- 查看变量值（结果）
SELECT :v_name,:v_credit FROM DUAL;
PRINT v_name,v_credit ;

IN OUT模式

　　该类型参数，可以在调用时传入值，调用后可以写入值传递到当前存储过程以外的环境中

-- 创建一个存储过程，计算一个数的平方或平方根
CREATE OR REPLACE PROCEDURE PRO_SUQARE(p_num IN OUT INT,p_flag IN BOOLEAN) IS
   v_i INT := 2;
BEGIN
  IF p_flag THEN
    p_num := POWER(p_num,v_i);
  ELSE
    p_num := SQRT(p_num);
  END IF;
END;

-- 调用存储过程
DECLARE
   v_num number := 3;
   v_temp number;
   v_flag boolean;
BEGIN
  v_temp := v_num;
  v_flag := FALSE;
  PRO_SUQARE(v_num,v_flag);
  IF v_flag THEN
    dbms_output.put_line(v_temp || '的平方=' || v_num);
  ELSE
    dbms_output.put_line(v_temp || '的平方根=' || v_num);
  END IF;
END;

IN参数的默认值

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

CREATE OR REPLACE PROCEDURE pro_insert_course(p_cosid IN INT,
       p_cosname IN VARCHAR2,
       p_credit IN INT,
       p_intro IN VARCHAR2 DEFAULT TO_CHAR(SYSDATE,'yyyy-MM-dd') || '开课') AS
BEGIN
  INSERT INTO COURSE VALUES(p_cosid,p_cosname,p_credit,p_intro);
  COMMIT;
  DBMS_OUTPUT.put_line('添加课程成功');
END;
select * from course;

删除存储过程

DROP PROCEDURE pro_insert_course;