mysql中存储过程

mysql中存储过程

直接po代码

#存储过程和函数
/*
存储过程和函数：类似于java中的方法
好处：
1、提高代码的重用性
2、简化操作



*/
#存储过程
/*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率



*/

#一、创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN

	存储过程体(一组合法,有效的SQL语句)
END

#注意：
/*
1、参数列表包含三部分
参数模式  参数名  参数类型
举例：
in stuname varchar(20)

参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
注意：delimiter后面跟的结束标记可以自定义
比如：
delimiter $
比如：
delimiter //
*/


#二、调用语法

CALL 存储过程名(实参列表);


#--------------------------------案例演示-----------------------------------
#
USE girls;
SELECT * FROM admin;
CREATE TABLE admin3 SELECT * FROM admin WHERE 666 = 888;
SELECT * FROM admin3;

#1.空参列表
#案例：插入到admin3表中五条记录
#创建存储过程
DELIMITER $
CREATE PROCEDURE myP1()
BEGIN
INSERT INTO admin3(username, `password`) VALUES 
('令狐冲', '123'),
('韦小宝', '456'),
('张无忌', '789'),
('杨过', '123'),
('段誉', '123');
END $

#调用存储过程
CALL myP1()$


#创建存储过程
DELIMITER $
CREATE PROCEDURE myP2()
BEGIN
CREATE TABLE IF NOT EXISTS test(
	id INT PRIMARY KEY AUTO_INCREMENT,
	tName VARCHAR(30) UNIQUE
);
END $

#调用存储过程
CALL myP2()$

#删除存储过程
DROP PROCEDURE myP2;

#2.创建带in模式参数的存储过程
#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
#创建存储过程(注意：in关键字可以省略不写，因为默认就是in，但是不建议省略in，最好是写上in,可读性比较好)
DELIMITER $
CREATE PROCEDURE myP3(IN beautyName VARCHAR(30))
BEGIN
	SELECT * FROM boys AS bo
	LEFT OUTER JOIN beauty AS b
	ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $

#调用存储过程
CALL myP3('周芷若')$


#案例2 ：创建存储过程实现，用户是否登录成功(判断用户名和密码是否存在)
DELIMITER $
CREATE PROCEDURE myP4(IN username VARCHAR(30), IN PASSWORD VARCHAR(30))
BEGIN
	#注意：使用DECLARE关键字定义的变量是局部变量，局部变量的作用域仅仅在定义它的begin end块中有效
	#declare声明的变量必须放在begin end中的第一句话
	##声明并初始化变量
	#declare result varchar(20) default '';
	DECLARE result INT DEFAULT 0;
	#将查询结果赋值给变量
	SELECT COUNT(*) INTO result 
	FROM admin WHERE admin.username = username
	AND admin.password = PASSWORD;
	SELECT result;
	SELECT IF(result > 0, '登录成功', '登录失败') AS "登录状态";
END $

#调用存储过程
CALL myP4('杨过', '789')$
CALL myP4('杨过', '123')$


#3.创建out 模式参数的存储过程
#案例1：根据输入的女神名，返回对应的男神名
DELIMITER $
CREATE PROCEDURE myP5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName FROM boys bo
	LEFT OUTER JOIN beauty b
	ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $

#调用
SET @boyName=''$
CALL myP5('高圆圆', @boyName)$
SELECT @boyName$
#调用(mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了)
CALL myP5('古力娜扎', @bName)$
SELECT @bName$


#案例2：根据输入的女神名，返回对应的男神名和魅力值
DELIMITER $
CREATE PROCEDURE myP6(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
	SELECT bo.boyName, bo.userCP INTO boyName, userCP FROM boys bo
	LEFT OUTER JOIN beauty b
	ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $

#调用
CALL myP6('古力娜扎', @boyName, @userCP)$
SELECT @boyName AS "男神", @userCP AS '魅力值'$

#调用
SET @boyName=''$
SET @userCP=0$
CALL myP6('高圆圆', @boyName, @userCP)$
SELECT @boyName AS 男神, @userCP 魅力值$


#4.创建带inout模式参数的存储过程
#案例1：传入number1、number2、number3这3个值，最终这3个数都翻倍并返回
DELIMITER $
CREATE PROCEDURE myP7(INOUT number1 INT, INOUT number2 INT, INOUT number3 INT)
BEGIN
	#赋值(SET 局部变量名=值; 或者 SET 局部变量名:=值;)
	SET number1 = number1 * 2;#赋值
	SET number2 := number2 * 2;#赋值
	#使用select方式赋值(SELECT @局部变量名:=值;)
	SELECT @number3:= number3 * 2; #使用SELECT给变量赋值的方式，貌似不支持返回
	#SET number3 = @number3;
	#SET number3 := @number3;
END $

#调用
SET @num1=10$
SET @num2=20$
SET @num3=30$
CALL myP7(@num1, @num2, @num3)$ #@num3没办法正常返回
SELECT @num1, @num2, @num3$ 
#


#三、删除存储过程
#语法：drop procedure 存储过程名
DROP PROCEDURE myP7;
DROP PROCEDURE IF EXISTS myP7;
#不支持一次性删除多个存储过程
#DROP PROCEDURE myP1, myP2; #错误的写法

#四、查看存储过程的信息
#不支持如下这种写法
DESC myP7;

#正确写法
SHOW CREATE PROCEDURE myP7;