存储过程的创建

#存储过程和函数
存储过程和函数:类似于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 $
#PROCEDURE 过程

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

#1.空参列表
#案例:插入admin表中五条记录.整个操作在CMD或者LINUX下操作
USE girls;
SELECT * FROM admin;

#创建存储过程
DELIMITER $
CREATE PROCEDURE myp2()
BEGIN
INSERT INTO admin(username,`password`)
VALUES(‘xiyangyang‘,‘0000‘),
(‘xiongda‘,‘0000‘),
(‘xionger‘,‘0000‘),
(‘jiji‘,‘0000‘),
(‘feimao‘,‘0000‘);
END $

#调用
CALL myp2() $

#查看
SELECT * FROM admin$

#2.创建带in模式参数的存储过程
#案例1.创建存储过程实现:根据女神名,查询对应的男神
CREATE PROCEDURE cunchu2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.`name`=beautyName;
END $

#调用
CALL cunchu2(‘妙法‘)$

提示:遇到ERROR 1366 Incorrect STRING VALUE ‘\xC1\XF8\XD2‘表时字符集错误
解决方法:设置字符集,用了存储过程结尾处，用$结尾
SET NAMES GBK$
SET NAMES UTF8$

#案例:创建存储过程实现,用户是否登陆成功
CREATE PROCEDURE cuchu3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result VARCHAR(20) DEFAULT ‘‘; #声明并初始化
SELECT COUNT(*) INTO result  #赋值
FROM admin
WHERE admin.username=username
AND admin.password=PASSWORD;
SELECT result;   #使用
END $

#调用
CALL cuchu3(‘石原‘,‘1918‘)$

#调用
CALL cuchu3(‘banyuan‘,‘0002‘)$

#添加逻辑//storage存储
CREATE PROCEDURE storage4(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,‘成功‘,‘失败‘);
END $
#调用
CALL storage4(‘石原‘,‘1918‘)$

#调用
CALL storage4(‘banyuan‘,‘0002‘)$

#3.创建带out模式的存储过程
#案例1：根据女生名，返回对应的男生名(单参)单个
添加测数据
INSERT INTO boys VALUES(4,‘黑猫警长‘,200);
UPDATE boys SET boyName=‘探长维乐‘ WHERE id=4;
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,‘侦探梅拉德‘,‘女‘,‘1990-2-2‘,‘18699888880‘,NULL,4);
定义结束符，因为连接断开了，所以要重新定义结束符。
DELIMITER $

创建存储过程
CREATE PROCEDURE storage5(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 $
定义SET可以省略，调用存储过程
执行
CALL storage5(‘侦探梅拉德‘,@bName)$
SELECT @bName$

#案例:根据女生名，返回对应的男神名和男生魅力值。(多参)多个返回值
CREATE PROCEDURE storage6(IN beautyName 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=beautyName;
END $

执行
CALL storage6(‘侦探梅拉德‘,@bName,@userCP)$

调用
SELECT @bName,@userCP$

#4.创建带inout模式参数的存储过程。
#案例1:传入a和b两个值，最终a和b都翻倍(重新赋值)并返回
#局部变量的赋值SET
#局部变量不用加@
CREATE PROCEDURE storage7(INOUT a INT,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $

不能直接调用，a与b现在没值，所以先要定义两个变量。
调用:名字可以不一样。
SET @m=10$
SET @n=20$

传过去是两个有值的变量
CALL storage7(@m,@n)$

查看值
SELECT @m,@n$

#练习
#一.创建存储过程实现传入用户名和密码，插入到admin表中。
CREATE PROCEDURE login(IN username VARCHAR(20),IN loginpwd VARCHAR(20))
BEGIN
INSERT INTO admin(admin.username,PASSWORD)
VALUES(username,loginpwd);
END $

调用
CALL login(‘admin‘,‘0000‘)$

#查看
SELECT * FROM admin$

#二.创建存储过程实现传入女生编号，返回女生名称和女生电话。
CREATE PROCEDURE test_pro2(IN id INT,OUT mingzi VARCHAR(20),OUT dianhua VARCHAR(20))
BEGIN
SELECT b.name,b.phone INTO mingzi,dianhua
FROM beauty b
WHERE b.id=id;
END $

#调用
CALL test_pro2(13,@n,@p)$

#查看
SELECT @n,@p$

#三.创建存储过程或函数实现传入两个女生生日，返回大小
#返回正数值1比值2大，负数值1比值2小，相等返回0
#运用函数直接比较DATEDIFF(值1,值2)
CREATE PROCEDURE test_pro3(IN birth1 DATETIME,IN birth2 DATETIME,OUT result INT)
BEGIN
SELECT DATEDIFF(birth1,birth2) INTO result;
END $

#调用
CALL test_pro3(‘1998-8-1‘,now(),@result)$

#查看
SELECT @result$

存储过程的删除
#删除存储过程
语法:
DROP PROCEDURE 存储过程名;
#定义了结束符所以$结尾。不定义结束符就用“;”结尾。
DROP PROCEDURE cuchu;
DROP PROCEDURE cuchu2$

#查看存储过程的结构。
SHOW CREATE PROCEDURE login;
SHOW CREATE PROCEDURE login$

函数
含义
含义:一组预先编译好的SQL语句的集合，理解成批处理语句
1.提高代码的重用性。
2.简化操作
3.减少编译次数并且减少了和数据服务器的连接次数，提高效率

区别:
存储过程:可以有0个返回值，也可以多个返回，适合做批量插入，批量更新
函数:有且仅有一个返回，适合做处理数据后返回一个结果。

#一.创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS返回类型
BEGIN
函数体
END

注意:
1.参数列表 包含两部分:
参数名 参数类型

2.函数体:肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议。

RETURN值；
3.函数体中仅有一句话，则可以省略begin END
4.使用delimiter语句设置结束标记。

#调用语法
SELECT 函数名(参数列表)
#执行函数中所有的语句，并显示返回值。

#1.无参有返回
#案例1.返回公司的员工个数
#设置结束符
DELIMITER $
#创建函数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0; #定义局部变量
SELECT COUNT(*) INTO c #赋值
FROM employees;
RETURN c;
END $

#调用
SELECT myf1()$

#2.有参有返回
#案例1:根据员工名，返回它的工资。
#创建函数
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0;  #定义用户变量
SELECT salary INTO @sal #赋值
FROM employees
WHERE last_name=empName;
RETURN @sal;
END $

#调用
SELECT myf2(‘Fay‘)$

#案例2:根据部门名，返回该部门的平均工资
#创建
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE;
SELECT AVG(salary) INTO sal
FROM employees e
INNER JOIN departments d ON e.department_id=d.department_id
WHERE d.department_name=deptName;
RETURN sal;
END $

#调用
SELECT myf3(‘IT‘)$

#查看函数
使用结束符
SHOW CREATE FUNCTION myf3$

不使用结束符
SHOW CREATE FUNCTION myf3;

#删除函数
DROP FUNCTION 函数名
DROP FUNCTION myf3$

#案例，创建函数，实现传入两个float，返回二者之和。
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END $

#调用
SELECT test_fun1(1,2)$

程控制结构加强
执行顺序
顺序结构:程序从上往下依次执行
分支结构:程序从两条或者多条路径中选择一条去执行
循环结构:程序满足一定条件的基础上，重复执行一段代码

#分支结构
#1.if函数
功能:实现简单的双分支
语法:
SELECT IF(表达式1,表达式2,表达式3)
执行顺序:
如果表达式1成立,则IF函数返回表达式2的值,否则返回表达式3的值
应用在任何地方。

#CASE结构
情况1:类似于java中的switch语句,一般用于实现的等值判断
语法:
CASE 变量:表达式:字段
WHEN要判断的值THEN 返回的值1【在存储过程这里放语句1;】
WHEN要判断的值THEN 返回的值2【在存储过程这里放语句2;】
...
ELSE 要返回的值n【在存储过程这里放语句n;】
END【在存储过程这里放END CAES;】

情况2:类似于java中的多重IF语句,一般用于实现区间判断
语法:
CASE
WHEN要判断的条件1 THEN返回的值1【在存储过程这里放语句1;】
WHEN要判断的条件2 THEN返回的值2【在存储过程这里放语句2;】
...
ELSE要返回的值n【在存储过程这里放语句n】
END【在存储过程这里放END CASE;】


特点:
①
1.可以作为表达式,嵌套在其他语句中使用,可以放在任何地方，BEGIN END中或BEGIN END外面。
2.可以作为独立的语句去使用，只能放在BEGIN END中。
②
如果WHEN中的值满足或条件成立,则执行对应的THEN后面的语句,并且结束CASE。
如果都不满足,则执行ELSE中的语句或值。
③
ELSE可以省略,如果ELSE省略了，并且所有WHEN条件都不满足，则返回NULL

#案例：
#创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩:90-100,显示A，80-90显示B，60-80显示C，否则显示D。
CREATE PROCEDURE test_case(IN score INT)
BEGIN
CASE
WHEN score>=90 AND score<=100 THEN SELECT ‘A‘;
WHEN score>=80 THEN SELECT ‘B‘;
WHEN score>=60 THEN SELECT ‘C‘;
ELSE SELECT ‘D‘;
END CASE;
END $

CALL test_case(95)$    

#3.if结构

功能:实现多重分支。

语法:
IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
...
【ELSE 语句n;】
END IF;
应用场合:应用在BEGIN END中。

#案例:根据传入的的成绩，返回级别。
/*
90-100 返回A
80-90 返回B
60-80 返回C
否则返回D
*/

CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
IF score>=90 AND score<=100 THEN RETURN ‘A‘;
ELSEIF score>=80 THEN RETURN ‘B‘;
ELSEIF score>=60 THEN RETURN ‘C‘;
ELSE RETURN ‘D‘;
END IF;
END $

#执行函数
SELECT test_if(85)$

#二.循环结构
分类:
while, loop, repeat
循环控制
iterate类似于continue，继续，结束本次循环，继续下一次。
leave类似于break，跳出，结束当前所在的循环。

#1.while
语法:
【标签:】while 循环条件 do
循环体;
end while【标签】;

联想java中:
while(循环条件){
循环体;
}

#2.loop

【标签:】loop
循环体;
end loop 【标签】;
可以用来模拟简单的死循环。

#3.repeat
语法:
【标签:】repeat
循环体;
until 结束循环的的条件
end repeat【标签】;

#没有添加循环控制语句
#案例:批量插入，根据插入，根据次数插入admin表中多条记录。
#没有返回值用存储过程。

建立
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
INSERT INTO admin(username,password) VALUES(CONCAT(‘Luxi‘,i),‘123‘);
SET i=i+1;
END WHILE;
END $

#执行//这里可以尝试插入100W行数据
CALL pro_while1(100)$

#查看//试着插入100W行数据，看看！
SELECT COUNT(*) FROM admin;$

#添加leave语句
#案例:批量插入,根据次数插入到admin表中多条记录，如果次数>=20则停止。
#设置结束符
DELIMITER $

#清除表内数据
TRUNCATE TABLE admin$

CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username,password) VALUES(CONCAT(‘xiaomin‘,i),‘0000‘);
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $

#调用执行
CALL test_while1(100)$
#查看
SELECT COUNT(*) FROM admin$

#添加iterate语句
#案例:批量插入,根据次数插入到admin表中多条记录，只插入偶数次2468。

Java写法
int i=1;
while(i<=insertCount){
if(i%2==0){
countinue;
}
插入
}

#设置结束符
DELIMITER $
#清除表内数据
TRUNCATE TABLE admin$
#编写存储过程
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<=insertCount DO
SET i=i+1;
IF MOD(i,2)!=0 THEN ITERATE a;
END IF;
INSERT INTO admin(username,password) VALUES(CONCAT(‘xiaoyu‘,i),‘2333‘);
END WHILE a;
END $

#调用
#查看
CALL test_while1(30)$
SELECT * FROM admin$