MySQL进阶（五）——存储过程

存储过程

       存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程的语句只有在第一次才进行预编译，以后再调用就可以省略编译这个步骤，对于常用的SQL语句可以采用，可以提高效率。

存储过程的优点

       （1）增强SQL语句的功能和灵活性

       （2）实现较快的执行速度

       （3）减少网络流量

创建存储过程

             CREATE

         [DEFINER = { user | CURRENT_USER }]

         PROCEDURE sp_name ([proc_parameter[,...]])

         [characteristic ...] routine_body

         注：

         proc_parameter:

         [IN | OUT | INOUT] param_name type

        参数有三种类型

              IN,表示该参数的值必须在调用存储过程时指定

              OUT,表示该参数的值可以被存储过程改变，并且可以返回

              INOUT,表示该参数在调用时指定，并且可以被改变和返回

        特性(characteristic )

              COMMENT ‘string’ | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY {DEFINER | INVOKER}

              注：

              COMMENT:注释

              CONTAINS SQL:包含SQL语句，但不包含读或写数据的语句

              NO SQL:不包含SQL语句

              READS SQL DATA:包含读数据时的语句

             MODIFIES SQL DATA:包含些数据的语句

             SQL SECURITY {DEFINER | INVOKER}指明谁有权限来执行

过程体

      （1）过程体由合法的SQL语句构成；

      （2）过程体可以是任意SQL语句；

      （3）过程体如果为复合结构则使用BEGIN...END语句；

      （4）复合结构可以包含声明，循环，控制结构；

创建不带参数的存储过程

          CREATE PROCEDURE sp1() SELECT VERSION();

          调用存储过程

          CALL sp_name([parameter[,...]])  如果存储过程带有参数，则需要写上() 和参数

          CALL sp_name[()] 如果存储过程不带有参数，则 () 可以省略

       调用：

       CALL sp1;

       CALL sp1();

创建带有IN类型参数的存储过程

       DELIMITER //             //修改结束符，从  " ; "  改为  " // "

       CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)

       BEGIN

       DELETE FROM users WHERE id = p_id;

       END

       //

      DELIMITER ;           //把结束符从  " // "  改为  " ; "

      调用

      CALL removeUserById(3);

 

修改存储过程

       只能修改特性、注释等简单内容，不能修改过程体，如果需要修改过程体，只能先删除再重新创建。

       ALTER PROCEDURE sp_name [characteristic ...]

       COMMENT ‘string’ | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY { DEFINER | INVOKER }

 

删除存储过程

          DROP PROCEDURE [IF EXISTS] sp_name

       例子：

               DROP PROCEDURE removeUserById;

创建带有IN和OUT类型参数的存储过程

       DELIMITER //                           //修改结束符，从  " ; "  改为  " // "

       CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)

       BEGIN

       DELETE FROM users WHERE id = p_id;

       SELECT count(id) FROM users INTO userNums;

       END

       //                                               

      DELIMITER ;                       //把结束符从  " // "  改为  " ; "

      CALL removeUserAndReturnUserNums(27,@nums); //@nums为用户变量，调用存储过程

      SELECT @nums;         // 查看返回变量

创建带有多个OUT类型参数的存储过程

        DELIMITER //                                //修改结束符，从  " ; "  改为  " // "

        CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)

        BEGIN

        DELETE FROM users WHERE age = p_age;

        SELECT ROW_COUNT() INTO deleteUsers;

        SELECT COUNT(id) FROM users INTO userCounts;

        END

        //

       DELIMITER ;                    //把结束符从  " // "  改为  " ; "

       CALL removeUserByAgeAndReturnInfos(20,@a,@b);       //调用存储过程

存储过程与自定义函数的区别

        （一）存储过程实现的功能要复杂一些；而函数的针对性更强；

        （二）存储过程可以返回多个值；函数只能有一个返回值；

        （三）存储过程一般独立地来执行；而函数可以作为其他SQL语句的组成部分来出现。

           注意事项：

                （1）创建存储过程或者自定义函数时需要通过delimiter语句修改定界符。

                （2）如果函数体或过程体有多个语句，需要包含在BEGIN......END语句块中。

                （3）存储过程通过call来调用。