存储过程

• 存储过程，带有逻辑的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()
BEGIN
         DECLARE boyName VARCHAR(20)  DEFAULT '大忽悠';
         SELECT boyName;
END $
#调用
CALL myp1();

1.使用in参数

DELIMITER $
CREATE PROCEDURE getname(IN id INT(100))
BEGIN
         SELECT 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)
BEGIN
        SET 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))
BEGIN

DECLARE 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  存储过程名;