---

存储过程

• 存储过程，带有逻辑的sql语句
• 之前的sql没有条件判断，没有循环
• 存储过程带上流程控制语句(if while)

---

特点

• 存储过程是在数据库的服务器端执行的，效率很高
• 移植性很差，不同数据库的存储过程是不能移植的，消耗服务器的资源

---

基本语法格式

DELIMITER $ #声明结束标记
CREATE PROCEDURE 存储过程名(
[IN 参数名 数据类型,参数名 数据类型...],[OUT 参数名 数据类型,参数名 数据类型...],
[INOUT 参数名 数据类型,参数名 数据类型...]
)
BEGIN [DECLARE 变量名 数据类型 DEFAULT 初始值;]执行的逻辑,包括SQL语句END $

---

关于存储过程的参数

---

1.输入参数

CREATE PROCEDURE 存储过程名(IN 参数名 数据类型,参数名 数据类型...)

---

2.输出参数

CREATE PROCEDURE 存储过程名(OUT 参数名 数据类型,参数名 数据类型...)

---

3.输入参数输出参数可以一起使用

CREATE PROCEDURE 存储过程名(IN 参数名 数据类型,参数名 数据类型...,OUT 参数名 数据类型,参数名 数据类型...)

---

4.INOUT输入输出参数，具有in和out的双重功能

CREATE PROCEDURE 存储过程名(INOUT 参数名 数据类型,参数名 数据类型...)

---

示例

DELIMITER $
CREATE PROCEDURE myp1()
BEGINDECLARE boyName VARCHAR(20)  DEFAULT '大忽悠';SELECT boyName;
END $
#调用
CALL myp1();

---

1.使用in参数

DELIMITER $
CREATE PROCEDURE getname(IN id INT(100))
BEGINSELECT p.serial FROM payment p WHERE p.id=id; 
END $
#调用
CALL getname(1);

---

2.创建带输入和输出参数的存储过程

DELIMITER $
#两个输入参数，一个输出参数
CREATE PROCEDURE concatStr(IN s1 VARCHAR(50),IN s2 VARCHAR(50),OUT ret VARCHAR(50))
BEGIN
#拼接参数,设计局部变量的值SET ret=CONCAT(s1,"--->",s2);
END $#声明一个局部变量，用来接收输出参数的结果
SET @res :="";#调用
CALL concatStr("小朋友","大忽悠",@res);#打印输出参数的结果
SELECT @res;

---

注意

使用有输出参数(有返回值)的存储过程

• 我们要现在外面定义变量来接收存储过程输出参数 set @变量名 := 变量的初始化值
• 在调用存储过程时，把定义的变量当做实参放在call调用存储过程函数的对应输出参数位置----（[输入值…],[@变量名])
• 变量在接受到存储过程的返回值(输出值)就可以直接使用了，例如查询输出变量（select @变量名）

---

3.使用INOUT参数，具有in和out的双重功能

DELIMITER $
CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)
BEGINSET a=a*2;SET b=b*2;
END $#调用
SET @m=10;
SET @n=20;
CALL myp8(@m,@n);
SELECT @m,@n;

---

4.存储过程使用局部变量

DELIMITER $
CREATE PROCEDURE myp1(IN NAME VARCHAR(20))
BEGIN
#声明一个局部变量DECLARE uoionname VARCHAR(20)  DEFAULT '大忽悠';SET uoionname=CONCAT(NAME,"和",uoionname);   SELECT  uoionname;
END $
#调用
CALL myp1("小朋友");

---

5.使用 [ select 字段 into变量 ] 把查询的结果赋值给变量

DELIMITER $
CREATE PROCEDURE myp3(OUT NAME VARCHAR(20))
BEGIN
#声明一个局部变量
SELECT SERIAL INTO NAME FROM payment WHERE id =1;
END $#调用
SET @NAME="";
CALL myp3(@NAME);
SELECT @NAME;

---

存储过程中的流程控制

IF语句

如果,否则

IF...THEN...ELSE...END IF

如果，如果,…否则

IF...THEN...ELSEIF...THEN...ELSEIF...THEN....ELSE...END IF

例子:

DELIMITER $
CREATE PROCEDURE pass(IN i INT(10))
BEGIN#if语句
IF i<=0 THEN 
SELECT "不通过";
ELSE
SELECT "通过";
END IF;END $#调用存储过程
CALL pass(1);

DELIMITER $
CREATE PROCEDURE pass1(IN i INT(10))
BEGIN#if语句
IF i=0 THEN 
SELECT "不通过";
ELSEIF i=1 THEN
SELECT "通过";
ELSE
SELECT "你小子想干啥";
END IF;END $#调用存储过程
CALL pass1(2);

---

循环遍历

WHILE....DO...END WHILE

举例:

DELIMITER $CREATE PROCEDURE pass8(IN num INT(10))
BEGINDECLARE i INT(100) DEFAULT 0;
WHILE i<=num DO
SET i=i+1;
END WHILE;SELECT i;END$CALL pass8(5);

---

查看存储过程列表

SHOW PROCEDURE STATUS;

---

查看某个存储过程的结构和信息

show create procedure 存储过程名;

---

存储过程的删除

drop procedure  存储过程名;

---

mysql 存储过程中的 prepare语句(存储过程中动态增减表字段)

PREPARE stmt from '你的sql语句';
EXECUTE stmt (如果sql有参数的话, USING xxx,xxx); // 这里USING的只能是会话变量
DEALLOCATE PREPARE stmt; 

这三句话分别就是预定义好sql.

执行预定义的sql

释放掉数据库连接

使用这个语法便可以在存储过程中写一些ddl语句，但是在网上看到的是在存储过程中最好是不要写ddl,因为ddl操作会锁表，总之就是不建议在存储过程中去更改表结构。不过我们这里是对临时表的改变，是不影响的啦。

该功能可以在存储过程中动态的拼接表名，字段名，来达到动态查询的效果

sql语句中还可以用?来代表参数，这样可以有效的防止sql注入

---

实例用法:

delimiter //
create procedure myTest()
begin
set @_sql = 'select ? + ?';
set @a = 5;
set @b = 6;
PREPARE stmt from @_sql; // 预定义sql
EXECUTE stmt USING @a,@b;// 传入两个会话变量来填充sql中的 ?
DEALLOCATE PREPARE stmt; // 释放连接
end //

delimiter //
create procedure myTest(in columnName varchar(32)) // 传入一个字符串
BEGIN
drop table if exists tmpTable; // 如果临时表存在先删除掉
set @_sql = concat('create temporary table if not exists tmpTable( ', columnName, ' varchar(32), id int(11), _name varchar(32));'); // 创建临时表的语法，我们把传入的参数拼接进来
PREPARE stmt from @_sql;    
EXECUTE stmt;
DEALLOCATE PREPARE stmt;  // 执行
desc tmpTable;
end //