Mysql数据库存储过程基本语法小结

【注意：执行语句在命令行窗口中执行,select的作用相当于print打印】
/*设置字符集编码：set names gbk;
mysql> CALL myp2(‘柳岩’)$
ERROR 1366 (HY000): Incorrect string value: ‘\xC1\xF8\xD1\xD2’ for column ‘beautyName’ at row 1
mysql> set names gbk$
Query OK, 0 rows affected (0.00 sec)
*/

设置结束符为$： DELIMITER $

1.空参列表

案例：插入到admin表中五条记录

SELECT * FROM admin;

CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username, password)
VALUES(‘John1’,‘0000’),(‘Rose’,‘0000’),(‘Jack’,‘0000’),(‘Tom’,‘0000’),(‘Lucy’,‘0000’);
END $

调用

CALL myp1();

2. 创建带in模式参数的存储过程

案例1：创建存储过程实现 根据女神名，查询对应的男神信息

CREATE PROCEDURE myp2(IN name VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name = beautyName;
END$

调用

CALL myp2(‘小昭’)$

案例2：创建存储过程实现，用户是否登录成功

CREATE PROCEDURE myp4(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0; # 声明并初始化

SELECT COUNT(*) INTO result   # 赋值
FROM admin
WHERE admin.username = username
AND admin.password = PASSWORD;

SELECT IF(result>0,'成功','失败') AS '登录结果';

END $

调用

CALL myp4(‘张飞’,‘8888’)$

3. 创建带out模式的存储过程

案例1：根据女神名，返回对应的男生名

CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName
FROM boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name = beautyName;
END $

调用

CALL myp5(‘小昭’, @bName)$
SELECT @bName$

案例2：根据女神名，返回对应的男神名和魅力值

CREATE PROCEDURE myp7(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT usercp INT)
BEGIN
SELECT boys.boyname, boys.usercp INTO boyName,usercp
FROM boys
RIGHT JOIN
beauty b ON b.boyfriend_id = boys.id
WHERE b.name = beautyName;
END $

调用

CALL myp5(‘小昭’, @boyName, @usercp)$
SELECT @bName, @usercp$

创建带inout模式参数的存储过程

案例1：传入a和b两个值，最终a和b都翻倍并返回

CREATE PROCEDURE myp8(INOUT a INT, INOUT b INT)
BEGIN
SET a = a2; # a,b此时是局部变量
SET b = b2;
END $

调用

执行mysql存储过程，存储过程为mysql用户变量：@variable
SET @m=10; # m,n此时是定义的用户变量
SET @n=20;
CALL myp8(@m,@n)
SELECT @m,@n;

补充

DELIMITER $
DROP PROCEDURE IF EXISTS pr_add $
CREATE PROCEDURE pr_add(INOUT a INT, INOUT b INT)
BEGIN
DECLARE c INT; # 可以写成DECLARE c INT DEFAULT 0; 声明并设置默认值
IF a IS NULL
THEN SET a=0;
END IF;
IF b IS NULL
THEN SET b=0;
END IF;
SET c=a+b;
SELECT c AS ‘返回结果’;
END $

调用

SET @m=10; # m,n此时是定义的临时变量
SET @n=20;
CALL pr_add(@m,@n)
SELECT @m,@n;

创建存储过程实现传入用户名和密码，插入到admin表中

CREATE PROCEDURE myp9(IN username VARCHAR(10), IN pwd VARCHAR(10))
BEGIN
INSERT INTO admin(admin.username,PASSWORD) VALUE(username,pwd); # 注意避免重名，用表名限制一下
END $

调用

CALL myp9(‘Alice’, ‘7896’)$

创建存储过程实现传入女神编号，返回女神名称和女神电话

CREATE PROCEDURE myp10(IN id INT, OUT NAME VARCHAR(20), OUT phone VARCHAR(20))
BEGIN
SELECT b.name, b.phone INTO NAME,phone
FROM beauty b
WHERE b.id=id;
END $

调用

CALL myp10(5, @name1, @phone1)$
SELECT @name1 AS ‘姓名’, @phone1 AS ‘电话’$

创建存储过程实现传入两个女神生日，返回大小

CREATE PROCEDURE myp11(IN birth1 DATETIME, IN birth2 DATETIME, OUT ret INT)
BEGIN
SELECT DATEDIFF(birth1, birth2) INTO ret;
END $

调用

CALL myp11(‘1998-1-1’, NOW(), @ret)$ # 因为是返回值，所以要传入一个变量(用户变量)
SELECT @ret$

创建存储过程或函数实现传入女神名称，返回：女神 AND 男神 格式的字符串

如 传入：小昭

返回： 小昭 and 张无忌

DROP PROCEDURE myp12 $
CREATE PROCEDURE myp12(IN beautyName VARCHAR(20), OUT ret VARCHAR(20))
BEGIN
SELECT CONCAT(b.name,’ AND ', IFNULL(boys.boyName, ‘NULL’)) INTO ret
FROM beauty b
LEFT JOIN boys
ON b.boyfriend_id=boys.id
WHERE b.name=beautyName;
END $

调用

CALL myp12(‘小昭’, @ret)$
SELECT @ret$

二、删除存储过程

语法：drop procedure 存储过程名

三、查看存储过程的信息

SHOW CREATE PROCEDURE 存储过程名;