mysql复杂存储过程写法简书_MySQL学习笔记（五）存储过程

存储引擎

输入SQL命令，MySQL引擎对命令进行分析，查看输入的语法是否正确，如果正确，再进行编译，编译成MySQL引擎可识别的命令，最后再进行执行，并将执行结果返回给客户端。

如果省略了语法分析和编译的阶段，则效率可提高。

存储过程：

是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理(类似函数)。存储过程存储在数据库内，可以由应用程序调用执行，而且允许用户申明变量以及进行流程控制。存储过程可以接受输入类型的参数和输出类型的参数，并且可以存在多个返回值。只在第一次调用时进行语法分析和编译，以后的调用直接调用编译的结果，效率大大提高。

优点：

1、增强SQL语句的功能和灵活性：可以通过控制语句对流程进行控制和判断

2、实现较快的执行速度：客户端第一次调用存储过程时，MySQL引擎会对其进行语法分析、编译等操作，然后将编译结果存储到内存中，所以第一次和之前的效率一样，然而以后会直接调用内存中的编译结果，效率提高

3、减少网络流量：例如删除一个记录，我们原本要输入DELETE FROM xx WHERE ...; 要传输的字符较多，如果写成存储过程，就只要调用存储过程的名字和相应参数就行，传输的字符数量较少，所以减少了网络流量。

1、存储过程语法结构分析

CREATE

[DEFINER = {userCURRENT_USER}]//定义时的用户，若是不写就默认为当前用户

PROCEDURE sp_name ([proc_parameter[,...]]) //可以带0到多个参数

[characteristic ...] routine_body

其中参数

proc_parameter:

[IN OUT INOUT] param_name type

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

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

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

2.特性

COMMENT 'string'

{CONTAINS SQLNO SQLREADS SQL DATAMODIFIES SQL DATASQL SECURITY{DEFINERINVOKER}

COMMENT:注释

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

NO SQL:不包含SQL语句

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

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

SQL SECURITY {DEFINERINVOKER}指明谁有权限来执行

3.过程体

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

(2)过程体可以是任意SQL语句;对表格进行增删，连接，但是不能创建数据表

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

(4)复合结构可以使用条件、循环等控制语句

创建不带参数的存储过程：

1.创建没有参数的存储过程

CREATE PROCEDURE sp1() SELECT VERSION();

2.调用存储过程

CALL sp_name([parameter[,...]]) - 带参数的存储过程的调用

CALL sp_name[()] - 不带参数的存储过程调用

CALL sp1;

CALL sp1();

1.创建带有INT类型参数的存储过程

DESC users;

DELIMITER //

CREATE PROCEDURE removeUserByID(IN id INT UNSIGNED)

BEGIN

DELETE FROM users WHERE id = id;

END

//

DELIMITER ;

CALL removeUserById(3); //参数名称最好不要和表中的字段相同

SELECT * FROM users; //全删除了

DELIMITER //

CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)

BEGIN DELETE FROM users WHERE id = p_id;

END

//

DELIMITER ;

SELECT * FROM users;

CALL removeUserById(22);

SELECT * FROM users WHERE id = 22;

2.修改存储过程

ALTER PROCEDURE sp_name [characteristic]

COMMENT 'string'

|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}

|SQL SECURITY{DEFINER|INVOKER}

3.删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name;

DROP removeUserById;

1.用户变量：以"@"开始，形式为"@变量名"

用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效

2.全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名 或者 set @@global.变量名

对所有客户端生效。只有具有super权限才可以设置全局变量

3.会话变量：只对连接的客户端有效。

4.局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量

declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量

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

1.明确什么是OUT类型的参数，有什么作用？

答：OUT类型参数，表示在调用存储过程时，该参数的值可以被存储过程改变，并且返回一个值。通常是这种类型时，调用时写入的参数都是变量用“@”符号开头的变量，在BEGIN和END之中的变量是局部变量，在调用存储过程时写入的变量是“用户变量”，比如有一个存储过程的名称叫做“addUser()”，调用时addUser(@sum),sum就是用户变量。

2.ROW_COUNT()函数就相当于PHP的MySQL函数库中的mysql_affected_rows()这个函数，两者的作用都是相同的，都是计算当插入记录和修改记录、以及添加和删除记录时的个数总和的。

3.例子：

SELECT ROW_COUNT() INTO delete_User ；是指调用ROW_COUNT()这个函数，并且把返回值存储在delete_User这个局部变量中。

SELECT COUNT(id) FROM users INTO remain_User ;是指在删除用户后，调用COUNT()函数对剩下的用户数做一个统计，在哪张表中要说明清楚，然后把返回值存储在remain_User这个局部变量中。

【因为delete_User和remain_User是OUT类型的参数，所以会返回一个值给调用时的用户变量，用"SELECT @a,@b"可以知道返回的值是多少了】

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

DESC users;

SELECT * FROM users;

SELECT ROW_COUNT(); //

DESC test;

INSERT test(username) VALUES('A'),('B,'),('C');

SELECT ROW_COUNT(); //返回被插入的记录总数

SELECT * FROM test;

UPDATE test SET username = CONTCAT(username, '--immoc') WHERE id <= 2;

SELECT row_COUNT(); //得到被更新的记录总数

DELIMITER //

CREATE PROCEDURE rmUserByAgeAndRtInfos(IN p_age SMALLINT UNSIGNED, OUT delNums SMALLINT UNSIGNED, OUT leftNums SMALLINT UNSIGNED)

BEGIN

DELETE FROM users WHERE WHERE age = p_age; //注意变量不同

SELECT ROW_COUNT() INTO delNums;

SELECT COUNT(id) FROM users INTO leftNums;

END

//

DELIMITER ;

SELECT * FROM users;

SELECT COUNT(id) FROM users WHERE age = 20;

CALL rmUserByAgeAndRtInfos(20, @a, @b);

SELECT * FROM users;

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

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

SELECT @a, @b;

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

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

A、存储过程实现的功能相对复杂，函数针对性较强

B、存储过程可以返回多个值，函数只能有一个返回值

C、存储过程一般独立执行，函数可以作为 sql 语句的组成部分来出现

//修改存储过程

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;

1、存储过程：是SQL语句和控制语句的预编译集合，以一个名字存储并作为一个单元来处理。

2、参数：输入类型 输出类型 输入&&是输出

3、创建：CREATE...PROCEDURE...

4、注意事项：

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

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

(3)存储过后才能通过call来调用。