mysql工作中用的比较多了，但是总觉得做为2年半的开发人员 对存储过程不太掌握和了解的话未免有些说不过去，所以最近几天学习了一下存储过程，发现还是比较简单的，下面就简单的记录和总结一下。

1.存储过程的语法结构

create procedure 存储过程名称(参数);//可以无参，也可以带参数，和java的方法名差不多

有参数的设置：（in|out|inout 参数名称 数据类型）

in:输入参数，表示该参数的值必须调用存储过程时指定

out:输出参数，表示可以被存储过程改变，并且可以返回

inout：输入输出参数，在调用时指定，可以被改变和返回

2.调用存储过程

call 存储过程名称();//如果定义了有参数这里也需要传入参数，和java调用方法一样

3.案例1（创建无参的存储过程并调用）

delimiter $$

create procedure pro1()

begin

select id from 表名；

end $$

delimiter ;

call pro1();

释：a.delimiter 定义结束符号开始定义了以$$作为结束，后面再次使用恢复为默认结束符分号，为了防止存储过程体中的分号对其造成影响，因为系统会默认以分号作为结束。

b.创建存储过程的名称后面不能带分号，会出现语法错误。

c.call调用存储过程时的参数必须一致，有参给参，无参穿孔即可，和java调用方法一样。

4.案例2（传入一个id，删除该数据，并查看剩余的数据条数）

delimiter $$

create procedure pro2(in cid int,out num int)

begin 

delete from 表名 where id=cid;

select count(id) into num form 表名；

end $$

delimiter ;

call pro2(id参数值，@num);

select @num;

释：1.into num  表示将查询出来的count(id) 赋值到该变量中。

       2.@num 变量声明。

       3.select @num; 表示查看该变量的值，也就是删除了数据之后总数还剩多少条。

 

5.案例3（输入2个数，计算出最后的结果）

DELIMITER //

CREATE PROCEDURE pro1(IN n1 INT,IN n2 INT,OUT result INT)

BEGIN 

SET result=n1+n2;

END //

DELIMITER ;

SET @n1=10,@n2=28;

CALL pro1(@n1,@n2,@result);

SELECT @result;

释：1.SET @n1=10,@n2=28;  表示声明了2个变量

7.案例（计算2张表开始时间到结束时间总天数）

 

DELIMITER $$

CREATE PROCEDURE pro_day(OUT total INT)

BEGIN 

DECLARE n1 INT DEFAULT 0;

DECLARE n1 INT DEFAULT 0;

SELECT SUM(DATEDIFF('2018-3-9','2018-3-8'))INTO n1 FROM sett_dailyaccountbalance 

SELECT SUM(DATEDIFF('2018-3-9','2018-3-8'))INTO n2 FROM  Balancestatement  

SET total=n1+n2;

END$$

delimiter ;

CALL pro_day(@total)

SELECT @total

释：DATEDIFF(）函数用来计算2个时间的天数，sum（）用来统计全部的总天数

     

 

6.删除存储过程

drop procedure 存储过程名称；

drop procedure if exists 存储过程名称；

释：以上2条的区别在于如果不存在该存储过程第一条会报错，第二条则不会

 

 

       

 

使用存储过程
使用存储过程需要知道如何执行(运行)它们。存储过程的执行远比其定义更经常遇到，因此，我们将从执行存储过程开始介绍。然后再介绍创建和使用存储过程。
执行存储过程
MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为 CALL 。 CALL 接受存储过程的名字以及需要传递给它的任意参数。请看以下例子：
输入：call productpricing(@pricelow,@pricehigh,@priceaverage);
分析：其中，执行名为 productpricing 的存储过程，它计算并返回产品的最低、最高和平均价格。
存储过程可以显示结果，也可以不显示结果，如稍后所述。
创建存储过程
正如所述，编写存储过程并不是微不足道的事情。为让你了解这个过程，请看一个例子——一个返回产品平均价格的存储过程。以下是其代码：
输入：create procedure productpricing() begin select avg(prod_price) as priceaverage from products;end;
分析：我们稍后介绍第一条和最后一条语句。此存储过程名为productpricing ，用 CREATE PROCEDURE productpricing() 语句定义。如果存储过程接受参数，它们将在 () 中列举出来。此存储过程没有参数，但后跟的 () 仍然需要。 BEGIN 和 END 语句用来限定存储过程体，过程体本身仅是一个简单的 SELECT 语句(使用Avg() 函数)。
在MySQL处理这段代码时，它创建一个新的存储过程 product-pricing 。没有返回数据，因为这段代码并未调用存储过程，这里只是为以后使用而创建它。
mysql 命令行客户机的分隔符 如果你使用的是 mysql 命令行实用程序，应该仔细阅读此说明。
默认的MySQL语句分隔符为 ; (正如你已经在迄今为止所使用的MySQL语句中所看到的那样)。 mysql 命令行实用程序也使用 ; 作为语句分隔符。如果命令行实用程序要解释存储过程自身内的 ; 字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误。
解决办法是临时更改命令行实用程序的语句分隔符，如下所示：delimiter//
create procedure productpricing()
begin
select avg(prod_price) as priceaverage from products;
end  //
delimiter;
其中， DELIMITER // 告诉命令行实用程序使用 // 作为新的语句结束分隔符，可以看到标志存储过程结束的 END 定义为 END// 而不是 END; 。这样，存储过程体内的 ; 仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可使用 DELIMITER ; 。除 \ 符号外，任何字符都可以用作语句分隔符。
如果你使用的是 mysql 命令行实用程序，在阅读本章时请记住这里的内容。
那么，如何使用这个存储过程？如下所示：
输入：call productpricing();
输出：
分析：CALL productpricing(); 执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后需要有 () 符号(即使不传递参数也需要)。

《实验十二、 MySQL存储过程的创建和使用》由会员分享，可在线阅读，更多相关《实验十二、 MySQL存储过程的创建和使用(2页珍藏版)》请在人人文库网上搜索。
1、实验十二、 MySQL存储过程的创建和使用一、实验目的1了解MySQL PL/SQL的基本数据类型、函数；2掌握局部变量的定义和赋值；3掌握存储过程的定义及调用二、实验知识准备前期要求掌握的知识：1MySQL基本数据类型2MySQL 变量的定义3MySQL存储过程定义4MySQL流程控制语句5.控制流程语句三、实验内容：对STUDENTDB数据库(在调用过程时输入参数的值自行确定)1.创建带输出参数的存储过程simplep1，求选课的学生人数并返回。调用(执行)此过程。2.创建带输入参数的存储过程simplep2，根据学生的学号查询该学生的性别和出生年份。调用此过程。3.创建一个名为insrectoc的存储过程，向COURSE表插入一条记录，新记录值由参数提供，当学分在1-6之间时完成插入。调用此过程。4.创建一个名为 select_zl的存储过程，根据输入的学号查询此生的姓名，所选课程名，成绩。调用此过程。5.创建一个名为 select_dv的存储过程，根据输入的系名和课程号查询某系学生某门课的平均成绩并返回。调用此过程。6.编写存储过程njc，求n!。(n为任意值)调用此过程。7.查看在STUDENTDB中创建的所有存储过程。删除存储过程simplep1。2 / 2。

前言
本文简单记录下oracle数据库中，如何用plsql编程、以及存储过程的创建和使用。

相关的概念我就不列举了，大家不清楚的可以自行搜索，本篇主要列举实际的sql语法。

那plsql编程，大家可以使用windows的命令行，也可以使用工具进行编写调试，我这边主要使用Oracle Sql Developer来举例。
1 plsql编程

1.1 程序结构

首先说说plsql的程序，可以分为3个部分：声明部分、可执行部分、异常处理部分。其中declare部分用来声明变量或者游标（结果集类型变量），如果程序中没有变量声明则可以省略。
DECLARE
  --声明变量、游标
  -- 相当于java中的 public class A{}
BEGIN
  --执行部分，相当于java中的main方法
  --可以进行异常处理
  dbms_output.put_line('hello world');
END;

这段程序中，dbms_output是oracle的内置程序包，相当于java中的System.out，而put_line()是调用的方法，相当于println()方法。执行结果为：



那如果是在命令行执行这段程序，则需要在执行程序之前，打开控制台的输出（set serveroutput on），并在结尾加“/”标识程序已结束，然后回车即可看到打印的结果。



1.2 变量

在plsql中，常见的变量分为两大类：

1、普通数据类型（char,varchar2,date,number,boolean,long）；

2、特殊变量类型（引用型变量、记录型变量）；

变量的声明方式为：变量名 变量类型（变量长度） 例如：v_name varchar2(20)
1.2.1 普通变量

普通变量的赋值方式有两种：

1）使用“:=”直接赋值，比如：v_name :=‘zhangsan’

2）语句赋值，语法为：select 值 into 变量名

– 声明一个人的信息，姓名、薪水、地址
DECLARE
  -- 姓名
  v_name VARCHAR2(50) := 'zhangsan';
  -- 薪水
  v_sal NUMBER;
  -- 地址
  v_addr VARCHAR2(200);
begin
  -- 直接赋值
  v_sal := 10000;
  -- 语句赋值
  select '中国' into v_addr from dual;
  -- 打印变量
  DBMS_OUTPUT.PUT_LINE('姓名：' || v_name || '薪水：' || v_sal || '地址：' || v_addr);
end;

执行结果为：



1.2.2 引用型变量

变量的类型和长度取决于表中字段的类型和长度;

通过表名.列名%type指定变量的类型和长度，例如：v_name emp.ename%type;
引用型变量的好处：

使用普通变量定义方式，需要知道表中列的类型，而使用引用类型，不需要考虑列的类型，使用%type是非常好的编程风格，因为它使得plsql更加灵活，更加适应于对数据库定义的更新。

建表语句：
CREATE TABLE "GD_JIANGUAN"."EMP" 
   (	"ENAME" VARCHAR2(20 BYTE), 
	"ESAL" NUMBER, 
	"EADDR" VARCHAR2(20 BYTE), 
	"ENO" VARCHAR2(20 BYTE)
   ) ;
   COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."ENAME" IS '用户名';
   COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."ESAL" IS '薪水';
   COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."EADDR" IS '地址';
   COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."ENO" IS '编号';
Insert into emp (ENAME,ESAL,EADDR,ENO) values ('张三',10000,'中国','1');
Insert into emp (ENAME,ESAL,EADDR,ENO) values ('李四',8000,'美国','2');
Insert into emp (ENAME,ESAL,EADDR,ENO) values ('王五',12000,'广州','3');
Insert into emp (ENAME,ESAL,EADDR,ENO) values ('赵六',7800,'深圳','4');
Insert into emp (ENAME,ESAL,EADDR,ENO) values ('钱七',9000,'上海','5');

举例
--查询emp表中赵六的个人信息，打印姓名、薪水和地址
DECLARE
  --姓名 声明变量并直接赋值
  v_name   emp.ename%TYPE;
  --薪水
  v_sal    emp.esal%TYPE;
  --地址
  v_addr   emp.eaddr%TYPE;
BEGIN
  --查询表中的姓名和薪水并赋值给变量
  --注意查询的字段和赋值的变量的顺序、个数、类型需要一致
  SELECT ename,esal,eaddr INTO v_name,v_sal,v_addr FROM emp WHERE ename = '赵六';
  --打印结果
  dbms_output.put_line('姓名：' || v_name || ' 薪水：' || v_sal || ' 地址：' || v_addr);
END;

结果为：



1.2.3 记录型变量

接收表中的一整行记录，相当于java中的一个对象

语法：变量名称 表名%rowtype，例如：v_emp emp%rowtype;

如果一张表有几十个字段，程序中需要使用这些字段，那定义一个记录型变量则可以方便的解决这个问题。

注：在使用记录型变量查询一行数据时，不能指定列，需用select * into 查询

获取取查询结果方法:变量名.列名

举例：
--查询emp表中赵六的个人信息，打印姓名、薪水和地址
DECLARE
  --定义记录型变量
  v_emp emp%ROWTYPE;
BEGIN
  --记录型变量默认接收表中的一行数据，不能指定字段
  SELECT * INTO v_emp FROM emp WHERE ename = '赵六';
  --打印结果，通过变量名.属性的方式获取变量中的值
  dbms_output.put_line('姓名：' || v_emp.ename || ' 薪水：' || v_emp.esal || ' 地址：' || v_emp.eaddr);
END;

结果为：



使用记录型变量常见的问题及注意点：

1）记录型变量只能存储一条完整的行数据，不支持存储部分字段



2）返回的行太多，记录型变量也接收不了



1.3 流程控制

1.3.1 条件分支

条件分支的语法一般为
begin
    if 条件1 then 执行1        
	elsif 条件2 then 执行2
	else 执行3
    end if;
end;

举例：
-- 判断emp表中记录数是否超过5条，3-5条，小于3条
DECLARE
  --声明变量接收emp表中的记录数
  v_count NUMBER;
BEGIN
  SELECT COUNT(1) INTO v_count FROM emp;
  IF v_count > 5 THEN
    dbms_output.put_line('记录数超过5条，具体数量为：' || v_count);
  ELSIF v_count >= 3 THEN
    dbms_output.put_line('记录数为3~5条，具体数量为：' || v_count);
  ELSE
    dbms_output.put_line('记录数小于3条，具体数量为：' || v_count);
  END IF;
END;

结果为：



注意：oracle中所有的字符拼接符都是“||”，而不是“+”。如果报错“数字或值错误”，则检查程序是否使用了“+”号作为拼接符使用。
1.3.2 循环

oracle中，循环的方式有3钟，for、while、loop，当然，使用goto语法，也能进行循环下面分别列举这4种循环的用法。
-- goto循环
DECLARE
  x number;
BEGIN
  x := 0;
  <<repeat_loop>> --循环点
  x := x + 1;
  DBMS_OUTPUT.PUT_LINE(X);
  IF X < 5 THEN
    GOTO repeat_loop; --当x的值小于9时,就goto到repeat_loop
  END IF;
END;
/
--for循环
DECLARE
  X number; --声明变量
BEGIN
  x := 1; --给初值
  FOR X IN REVERSE 1 .. 10 LOOP
    --reverse由大到小
    DBMS_OUTPUT.PUT_LINE('内:x=' || x);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('end loop:x=' || X); --x=1
END;
/
--while循环
DECLARE
  x number;
BEGIN
  x := 0;
  WHILE x < 9 LOOP
    x := x + 1;
    DBMS_OUTPUT.PUT_LINE('内:x=' || x);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('外:x=' || x);
END;
--loop循环
DECLARE
  x number;
BEGIN
  x := 0;
  LOOP
    x := x + 1;
    EXIT WHEN x > 9;
    DBMS_OUTPUT.PUT_LINE('内:x=' || x);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('外:x=' || x);
END;

2 游标

2.1 什么是游标

游标用于临时存储一个查询返回的多行数据（结果集，类似于java中jdbc连接返回的ResultSet集合），通过遍历游标，可以逐行访问处理该结果集的数据。

游标的使用方式：申明 --> 打开 --> 读取 --> 关闭
2.2 语法

游标的声明：cursor 游标名[{参数列表}] is 查询语句;

游标打开： open 游标名;

游标读取：fetch 游标名 into 变量列表;

游标关闭：close 游标名;
2.3 游标的属性




属性
类型
说明




%rowcount
整型
获得fetch语句返回的数据行数


%found
布尔型
最近的fetch语句返回一行数据则为真，否则为假


%notfound
布尔型
与%found返回值相反


%isopen
布尔型
游标已经开启时值为真，否则为假


其中%notfound是在游标中找不到元素的时候返回true，通常用来判断退出循环。
2.4 创建和使用

2.4.1 不带参数
-- 查询EMP表中所有员工姓名和工资，并依次打印出来
DECLARE
  -- 声明游标
  CURSOR C_EMP IS SELECT ENAME, ESAL FROM EMP;
  --申明变量接收游标中的数据
  V_NAME EMP.ENAME%TYPE;
  V_SAL  EMP.ESAL%TYPE;
BEGIN
  --打开游标
  OPEN C_EMP;
  --遍历游标
  LOOP
    -- 获取游标中的数据，如果有的话就赋值给变量
    FETCH C_EMP INTO V_NAME, V_SAL;
    EXIT WHEN C_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || ' 薪水：' || V_SAL);
  END LOOP;
  --关闭游标
  CLOSE C_EMP; 
END;

结果为：



2.4.2 带参数
-- 查询EMP表中编号为5的员工姓名和工资，并依次打印出来
DECLARE
  -- 声明游标
  CURSOR C_EMP(V_NO EMP.ENO%TYPE) IS
    SELECT ENAME, ESAL FROM EMP WHERE ENO = V_NO;
  --申明变量接收游标中的数据
  V_NAME EMP.ENAME%TYPE;
  V_SAL  EMP.ESAL%TYPE;
BEGIN
  --打开游标
  OPEN C_EMP(5);
  --遍历游标
  LOOP
    -- 获取游标中的数据，如果有的话就赋值给变量
    FETCH C_EMP INTO V_NAME, V_SAL;
    EXIT WHEN C_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || ' 薪水：' || V_SAL);
  END LOOP;
  --关闭游标
  CLOSE C_WMP;
END;

结果为：


3 存储过程
3.1 概念

之前编写的plsql程序可以进行表的操作、判断、循环逻辑处理的工作，但是没有办法重复调用。这可以理解为之前的代码全部编写在了main方法中，是匿名的程序，那在java中可以通过封装对象和方法来解决复用的问题。那plsql是将一个个的程序处理过程存储起来进行复用，那这些被存储起来的plsql程序称之为存储过程，相当于java中的方法。
存储过程的作用：

1）在开发程序的过程中，为了一个特定的业务功能，会向数据库进行多次请求查询，需要对数据库进行多次I/O读写，性能比较低，而且数据库的连接关闭是非常耗资源的。如果把这些业务处理放到plsql中，在应用程序中调用plsql就可以做到连接关闭一次数据库就可以实现我们的业务，那将可以大大提升效率。

2）oracle官方建议：能够让数据库操作的，就不要放到程序中，在数据库中实现基本不会出现错误，而在程序中可能会出错（如果在数据库中操作数据，有一定的日志恢复等功能）。
3.2 语法
create or replace procedure 过程名称[{参数列表}] is
begin

end [过程名称];

根据参数的类型，可以分为以下3种：

1）不带参数的
create or REPLACE PROCEDURE p_hello is
  --这里声明变量，declare被省略
begin
  DBMS_OUTPUT.PUT_LINE('hello');
end p_hello;
/
--调用存储过程
exec p_hello;

2）带输入参数的
--创建一个查询并打印某个员工的姓名和薪水的存储过程，要求调用的时候传入员工的编号，自动在控制台打印
CREATE OR REPLACE PROCEDURE QUERYNAMEANDSAL(I_NO EMP.ENO%TYPE) AS
  -- 声明变量
  V_ENAME EMP.ENAME%TYPE;
  V_ESAL  EMP.ESAL%TYPE;
BEGIN
  SELECT ENAME, ESAL INTO V_ENAME, V_ESAL FROM EMP WHERE ENO = I_NO;
  DBMS_OUTPUT.PUT_LINE(V_ENAME || ' ' || V_ESAL);
END QUERYNAMEANDSAL;
/
--在窗口命令栏里调用存储过程
exec QUERYNAMEANDSAL(5);

3）带输入参数和输出参数（返回值）的
--创建一个查询并打印某个员工薪水的存储过程，要求调用的时候传入员工的编号,程序中获取输出
CREATE OR REPLACE PROCEDURE QUERYNAMEANDSAL02(I_NO in EMP.ENO%TYPE ,O_SAL out EMP.ESAL%TYPE) AS
BEGIN
  SELECT ESAL INTO O_SAL FROM EMP WHERE ENO = I_NO;
END QUERYNAMEANDSAL02;
/

--调用存储过程
declare
	--设置变量
	O_SAL EMP.ESAL%type;
begin
	--执行过程
	QUERYNAMEANDSAL02(1024,O_SAL);
	dbms_output.put_line(O_SAL);
/

--也可以通过java程序调用存储过程
-- 以下程序在java中编写
public static void main(String[] args) {
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL";
        String userName = "gd_jianguan";
        String userpwd = "gd_jianguan";
        Connection conn = DriverManager.getConnection(url, userName, userpwd);
        //获取语句对象
        String sql = "{call QUERYNAMEANDSAL02(?,?)}";
        CallableStatement call = conn.prepareCall(sql);
        //设置输入参数
        call.setInt(1, 5);
        //注册输出参数
        call.registerOutParameter(2, OracleTypes.DOUBLE);
        //执行存储过程
        call.execute();
        //获取输出参数
        double sal = call.getDouble(2);
        System.out.println("薪水：" + sal);
        //释放资源
        call.close();
        conn.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}