开发文章

MySQL初始存储过程

 一存储过程简介

       在学习存储过程之前我们先来看看MySQL数据库中的命令执行流程:

MySQL数据库中的命令执行流程.png

   (1)存储过程

       存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理。

       (2)存储过程的优点

       1)增强SQL语句的功能和灵活性。

       2)实现较快的执行速度。

       3)减少网络流量。

       (3)存储过程语法结构解析

       MySQL数据库创建存储过程的语法结构为:

       CREATE [DEFINER = { user | CURRENT_USER} ] PROCEDURE sp_name ([proc_parameter[,...]]) 

[characteristic ...] routine_body;

       proc_parameter代表的含义:

       [ IN | OUT | INOUT ] param_name type

       参数含义:

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

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

       3)INOUT,表示该参数的值在调用时指定,并且可以被改变和返回。

       (4)存储过程特性

       COMMENT 'string'

       | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} 
       | SQL SECURITY { DEFINER | INVOKER }

       特性释义:

       1)COMMENT:注释。

       2)CONTAINS SQL:包含SQL语句,但不包含读或写数据的语句。

       3)NO SQL:不包含SQL语句。

       4)READS SQL DATA :包含读数据的语句。

       5)MODIFIES SQL DATA:包含写数据的语句。

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

       (5)过程体

       1)过程体由合法的SQL语句构成。

       2)过程体可以是任意SQL语句。

       3)过程体如果为复合结构则使用BEGIN...AND语句。

       4)复合结构也可以包含声明、循环和控制结构。

       (6)调用存储过程

       MySQL数据库调用存储过程的语法为:

       1)调用有参数的存储过程

       CALL sp_name([parameter[,...]]);

       2)调用无参数的存储过程

       CALL sp_name[()];

       二创建存储过程

       (1)创建不带参数的存储过程

       创建查询MySQL数据库版本号的存储过程

       CREATE PROCEDURE sp1() SELECT VERSION();

       调用存储过程

       CALL sp1;

       CALL sp1();

MySQL调用存储过程.png

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

       DESC users3;

       SELECT * FROM users3;

带有IN类型参数的存储过程.jpg

创建一个删除数据表中全部记录的存储过程

       修改MySQL句的结束符:

       DELIMITER //

       CREATE PROCEDURE removeUserByID(IN id INT UNSIGNED) BEGIN DELETE FROM users3 WHERE

 id=id; END//

       DELIMITER ;

       调用存储过程

       CALL removeUserByID(3);

       SELECT * FROM users3;

删除数据表中全部记录的存储过程.jpg

 修改存储过程的语法结构:

       ALTER PROCEDURE sp_name [chatacteristic ...] 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;

       重新向users3数据表中插入记录

       INSERT users3 VALUES(NULL,'Tom','123',25,1);

       INSERT users3 VALUES(NULL,'John','223',DEFAULT,0);

       INSERT users3 VALUES(DEFAULT,'Rose','323',25,1);

       INSERT users3 VALUES(DEFAULT,'Paul','123',23,1);

       INSERT users3 VALUES(DEFAULT,'Jord','123',23,1);

       INSERT users3 VALUES(DEFAULT,'Lee','123',23,1);

       INSERT users3 VALUES(DEFAULT,'Jams','123',23,1);

       INSERT users3 VALUES(NULL,'Dave','456',23,0);

       INSERT users3 VALUES(NULL,'Jack','456',24,1);

修改存储过程的语法.jpg

 创建一个删除数据表中一条记录的存储过程

        DELIMITER //

        CREATE PROCEDURE removeUserByID(IN p_id INT UNSIGNED) BEGIN DELETE FROM users3 WHERE 

id=p_id; END//

       DELIMITER ;

       SELECT * FROM users3;

 

删除数据表中一条记录的存储过程.png

 

 调用存储过程

       CALL removeUserByID(12);

       SELECT * FROM users3;

MySQL调用存储过程.png

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

       创建一个删除一条记录并且返回剩余记录总数的存储过程

       DELIMITER //

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

UNSIGNED) BEGIN DELETE FROM users3 WHERE id=p_id; SELECT count(id) FROM users3 INTO userNums;

 END//

       DELIMITER ;

       SELECT count(id) FROM users3;

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

调用存储过程        CALL removeUserAndReturnUserNums(14,@nums);

       SELECT @nums;

调用存储过程.jpg

 

 以@符号开头的字符序列是指用户变量,也就是用户在MySQL客户端定义的变量,一般用在BEGIN AND块中,

对存储过程传入的参数进行声明、定义等。

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

       得到被影响的行数的命令是:SELECT ROW_COUNT():

       创建通过age字段删除记录并且返回删除的记录数和剩余记录数的存储过程

       DELIMITER //

       CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age INT UNSIGNED,OUT deleteUsers 

SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED) BEGIN DELETE FROM users3 WHERE

 age=p_age; SELECT ROW_COUNT()  INTO deleteUsers; SELECT COUNT(id) FROM users3 INTO userCounts;

 END//

       DELIMITER ;

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

 SELECT * FROM users3;

       SELECT COUNT(id) FROM users3 WHERE age=23;

 

MySQL-SELECT语句使用.jpg

调用存储过程(删除age=23的记录)

       CALL removeUserByAgeAndReturnInfos(23,@a,@b);

       SELECT @a;

       SELECT @b;

MySQL调用存储过程删除记录.jpg

SELECT * FROM users3;

       SELECT @a,@b;

MySQL-SELECT.jpg

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

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

       1)存储过程实现的功能要复杂一些;而函数的针对性更强。

       2)存储过程可以返回多个值;函数只能有一个返回值。

       3)存储过程一般独立的来执行;而函数可以作为其他SQL语句的组成部分来出现。

       使用存储过程注意事项

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

       2)如果函数体或过程体有多个语句,需要包含在BEIGIN...AND语句块中。

       3)存储过程通过CALL关键字来调用。


感谢 丿木呈广予口贝 支持 磐实编程网 原文地址:
blog.csdn.net/erlian1992/article/details/51615550

文章信息

发布时间:2016-06-09

作者:丿木呈广予口贝

发布者:aquwcw

浏览次数: