存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
# 存储过程和函数

/*
存储过程和函数：类似Java中的方法

好处：
1. 提高代码重用性
2. 简化操作
*/


# 存储过程
/*
含义：一组预先编译好的SQL语句集合

好处：
1. 提高代码重用性
2. 简化操作
3. 减少编译次数
4. 减少数据库服务器的连接次数，提高效率
*/

# 存储过程创建
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
		存储过程体(一组合法的SQL语句)
END

# 注意
1. 参数列表包含三部分：参数模式 参数名 参数类型
2. 参数模式有 IN,OUT,INOUT三种
IN:该参数可以作为输入，也就是说该参数需要调用方传入值
OUT:该参数可以作为输出，也就是该参数可以作为返回值
INOUT:该参数既可以作为输入，也可以作为输出，急需要传入值也可以返回值
3. 如果存储过程体中仅有一句话，BEGIN END 可以省略
4. 存储过程体中每条SQL语句结尾要求必须加分号
5. 存储过程的结尾可以用DELIMITER重新配置
语法：
DELIMITER 结束标记


# 调用语法
CALL 存储过程名(实参列表);



# 空参的存储过程
# 案例：插入到admin表中三条记录
DROP TABLE IF EXISTS admin;
CREATE TABLE admin(
		id INT PRIMARY KEY auto_increment,
		username VARCHAR(20),
		password VARCHAR(15)
);
SELECT * FROM admin;
# 题目完成
DELIMITER $  # 修改终止符号，只能在DOS窗口里应用，图形化界面无效，修改后，后续都需要用这个符号作为终止符
CREATE PROCEDURE p1()
BEGIN
	INSERT INTO admin 
	VALUES(NULL,'Leslie','0000'),(NULL,'JJ','0111'),(NULL,'Lily','0011');
END $
# 调用函数(在DOS窗口中用CALL p1()$调用)
CALL p1();



# 创建带IN类型参数的存储过程
# 案例：根据女友姓名，查询男友信息
USE girls;
CREATE PROCEDURE p2(IN girlName VARCHAR(20))
BEGIN 
		SELECT bo.*
		FROM boys bo
		RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`
		WHERE b.`name`=girlName;
END 
# 调用
CALL p2('小昭');

# 案例2：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE p3(IN username VARCHAR(20),IN password VARCHAR(20))
BEGIN
		# 定义变量用于输出结果
		DECLARE result INT DEFAULT 0;
		# 检查登录
		SELECT COUNT(*) INTO result
		FROM admin
		WHERE username=admin.username
		AND password=admin.`password`;
		# 输出结果
		SELECT IF(result>0,'成功','失败') 结果;
END
# 调用
CALL p3('john','8888');




# 创建待out模式的存储
# 实际就是其他语言的返回值，Mysql支持多个out模式即多个返回值

# 根据女生名返回男生姓名
USE girls;
CREATE PROCEDURE p4(IN girlName 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`=girlName;
END
# 调用
CALL p4('小昭',@boyName);
SELECT @boyName;


# 案例2：根据女士姓名，返回男士姓名和数值（一次返回多个值）
CREATE PROCEDURE p5(IN girlName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
		SELECT bo.boyName ,bo.userCP INTO boyName,userCP
		FROM boys bo
		INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
		WHERE b.`name`=girlName;
END

# 调用
CALL p5('小昭',@boyName,@boyCP);
SELECT @boyName,@boyCP;



# 带inout模式的存储模式
# 传入a和b两个值，最终a和b都被双倍返回
CREATE PROCEDURE p6(INOUT a INT ,INOUT b INT)
BEGIN 
		SET a=a*2;
		SET b=b*2;
END

SET @aNum=10;
SET @bNum=6;
CALL p6(@aNum,@bNum);
SELECT @aNum,@bNum;



# 存储过程的删除
DROP PROCEDURE p2;

# 查看存储过程结构信息
SHOW CREATE PROCEDURE p3;

# 存储过程一般不修改

函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
# 函数

/*

存储过程与函数区别：
存储过程可以有任意个返回值（零或多个），适合于批量插入或修改
函数有且仅有一个返回值，适合处理数据后返回一个结果

*/

# 创建语法

CREATE FUNCTION 方法名(参数列表) RETURNS 返回类型
BEGIN 
		函数体
END
/*
参数列表包含两部分：参数名  参数类型

函数体：肯定有return语句
如果return语句不会报错，但没有实际效果

函数体中只有一句话时，BEGIN END可以省略

在DOS窗口下仍然使用DELIMITER设置终止符
*/

# 调用语法,执行函数体内所有语句，并最终显示返回值
SELECT 函数名(参数列表);

# 案例：无参有返回值，返回公司员工格式
use myemployees;
CREATE FUNCTION f1() RETURNS INT
BEGIN
		DECLARE c INT ; # 声明返回值变量
		SELECT count(*) INTO c  # 赋值
		FROM employees;
		RETURN c; # 返回值
END


SELECT f1() result;

# 案例二：含参有返回，根据员工名，返回对应工资
CREATE FUNCTION f2(username VARCHAR(20)) RETURNS INT
BEGIN 
		DECLARE s INT;
		SELECT salary INTO s
		FROM employees
		WHERE last_name=username;
		RETURN s;
END

SELECT f2('Chen') salary;


# 查看函数
SHOW CREATE FUNCTION f1;
# 删除函数
DROP FUNCTION f1;