存储过程

  存储过程是一种命名的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中调用

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;

 

上一篇:设计模式-迭代器模式


下一篇:SQL多表连接查询(详细实例)