Mysql 存储过程小记

#Mysql 存储过程

前几天开发的项目中遇到了一些涉及到计算的问题, 于是开启了存储过程的学习, 现将学习的知识点记录下来:

数据库为 mysql 5.2.27

先简单介绍一下存储过程:

MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

优点

• 存储过程可封装，并隐藏复杂的商业逻辑。

• 存储过程可以回传值，并可以接受参数。

• 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。

• 存储过程可以用在数据检验，强制实行商业逻辑等。

缺点

• 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。

• 存储过程的性能调校与撰写，受限于各种数据库系统。可移植性差。

基本语法

---

    DELIMITER  $                    -- 定义过程结束符
    CREATE PROCEDURE p_users()      -- 创建存储过程
    BEGIN                           -- 存储过程开始
        ......                          -- 存储逻辑
    END                             -- 结束
    $                               -- 结束符
    delimiter ;                     -- 还原过程结束符

上面是一个基本的存储过程函数, 使用关键字 PROCEDURE 创建存储过程, 在数据库中预编译之后就可以运行了
如:

    CALL p_users;

函数参数

上面的例子中并没有参数, 单实际应用过程中会需要不止一个参数, 写法如下

    ...
    CREATE PROCEDURE p_users(       -- 创建函数
        IN 参数名 参数类型,
        OUT 参数名 参数类型,
        INOUT 参数名 参数类型
    )
    ...

上面的例子中出现了三种参数 输入参数 IN 输出参数 OUT 输入输出参数 INOUT

IN 参数

该类型参数是输入参数, 其作用是向函数内部传递值, 特点是无法修改(修改了也没用), 类似java中用final修饰的参数

OUT 参数

该类型参数是输出参数, 可以多次赋值. 函数运行的结果可以存储在里面.
如 :

    DELIMITER  $                    
    CREATE PROCEDURE p_a(
        OUT exp INT
    )      
    BEGIN                           
        SET exp = 200;                    
    END                            
    $                               
    delimiter ;

调用该过程

    CALL p_a(@exp);
    select @exp;

输出结果为 200

INOUT 参数

兼IN 和 OUT

函数变量

定义变量使用到关键字 DECLARE 如 : DECLARE p INT; 使用这种方式定义的变量为局部变量, 局部变量声明一定要放在存储过程体的开始.

还可以使用 SET 关键字定义变量 SET @q INT 使用这种变量定义的为全局变量.

给变量赋值, 如: SET p = 1;
或者在SQL语句中为其赋值 如:

    ...
    DECLARE p INT;

    SELECT count(*) INTO p FROM user u WHERE u.id = 1;
    ...

if-then-else 语句

其基本语法规则为:

   ...
   declare var int;
   set var= 1; 
   if var=0 then                -- 注意此处不能有分号 
   insert into t values(17); 
   end if;
   ...

case 语句

基本语法规则为:

    ...
    DECLARE var int;
    SET var=parameter+1;  
    CASE var
    WHEN 0 THEN  
    INSERT INTO t VALUES(17); 
    when 1 then 
    INSERT INTO t VALUES(18);  
    ELSE  
    INSERT INTO t VALUES(19);  
    END CASE;  
    ...

循环 while ···· end while

在存储过程中可以使用循环, 类似java 中的 for 循环, 关键字 WHILE如 :

    ...
    declare var int;
    set var=0;
    while var<6 do
    insert into t values(var);
    set var=var+1;
    end while;
    ...

结果集循环(游标)

有些情况下我们需要对查询的结果进行循环, 类似java中的 iterator, 如:

    ...
    DECLARE s int DEFAULT 0;        -- 定义一个循环截止的标识符

    DECLARE _name VARCHAR(20);       -- 定义一些遍历过程用到 参数
    DECLARE _phone VARCHAR(11);
    DECLARE _password VARCHAR(50);

    DECLARE user CURSOR FOR SELECT a.name,a.phone,a.password from user_info a;      -- 从用户表中查出结果

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;      -- 设置当游标不存在内容时终止遍历

    OPEN user;       -- 打开游标

        WHILE s <> 1 DO             -- 开始循环(和上面的s对应, 当s等于1的时候就终止循环)

            FETCH proj INTO _name, _phone, _password;   -- 将结果集中的每一条数据赋值给变量
                                                        -- 注意: 这里的赋值需要将全部的查询结果列都赋值

            ...                     -- Do Something...

        END WHILE;                  -- 终止循环
    CLOSE user;                     -- 关闭游标
    ...

函数调用

在存储过程中也可以像java一样存在函数之间的调用; 如:

    ...
    CALL p_a;               -- 无参调用
    CALL p_a( [参数] )    -- 入参调用
    CALL p_a( @[参数] )   -- 出参调用
    ...

对于函数中的返回值问题, 有类似的例子, 如 :

    ...
    DECLARE num INT DEFAULT 100;        -- 定义一个入参变量, 默认值为200

    CALL p_a(num, @result)              -- 该函数需要两个参数, 一个入参 IN num INT, 一个出参 OUT res INT;

    select @result;                     -- 使用上个函数的出参
    ...

---

不积跬步, 无以至千里

本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名,转载请标明出处
最后编辑时间为: 2019/07/30 05:09:25