#存储过程和函数
/*
存储过程和函数:类似于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 $
*/
#二、调用语法
CALL 存储过程名 (实参列表);
#--------------------------------案例演示-----------------------------------
#1.空参列表
#案例:插入到admin表中五条记录
SELECT
*
FROM
admin;
DELIMITER $
CREATE PROCEDURE myp1 ()
BEGIN
INSERT INTO admin (username, `password`)
VALUES
('john1', '0000'),
('lily', '0000'),
('rose', '0000'),
('jack', '0000'),
('tom', '0000');
END $
#调用
CALL myp1 () $
#2.创建带in模式参数的存储过程
#案例1:创建存储过程实现 根据女神名,查询对应的男神信息
DELIMITER $
CREATE PROCEDURE myp2 (IN beautyName VARCHAR (20))
BEGIN
SELECT
*
FROM
boys
WHERE id =
(SELECT
boyfriend_id
FROM
beauty
WHERE NAME = beautyName);
END $
#调用
CALL myp2 ('刘亦菲') $
#案例2 :创建存储过程实现,用户是否登录成功
CREATE PROCEDURE myp3 (
IN username VARCHAR (20),
`password` VARCHAR (20)
)
BEGIN
DECLARE result INT DEFAULT 0;
#声明并初始化
SELECT
COUNT (*) INTO result
FROM
admin a
WHERE a.username = username
AND a.password = `password`;
SELECT
IF (result > 0, '成功', '失败');
END $
#调用
CALL myp3 ('张飞', '8888') $
#3.创建out 模式参数的存储过程
#案例1:根据输入的女神名,返回对应的男神名
CREATE PROCEDURE myp4 (
IN beautyname VARCHAR (20),
OUT boyname VARCHAR (20)
)
BEGIN
SELECT
b.`boyName` INTO boyname
FROM
boys b
WHERE b.id =
(SELECT
boyfriend_id
FROM
beauty
WHERE `name` = beautyname);
END $
CALL myp4 ('刘亦菲', @bname) $
SELECT
@banme $
#案例2:根据输入的女神名,返回对应的男神名和魅力值
CREATE PROCEDURE myp5 (
IN beautyName VARCHAR (20),
OUT boyname VARCHAR (20),
OUT usercp INT
)
BEGIN
SELECT
b.boyName,
b.usercp INTO boyname,
usercp
FROM
boys b
WHERE b.id =
(SELECT
boyfriend_id
FROM
beauty
WHERE `name` = beautyname);
END $
CALL myp5 ('刘亦菲', @name, @cp) $
SELECT
@name,
@cp $
#4.创建带inout模式参数的存储过程
#案例1:传入a和b两个值,最终a和b都翻倍并返回
CREATE PROCEDURE myp6 (INOUT a INT, INOUT b INT)
BEGIN
SET a = a * 2;
SET b = b * 2;
END $
SET @a = 2 $
SET @b = 3 $
CALL myp6 (@a, @b) $
SELECT
@a,
@b $
#三、删除存储过程
#语法:drop procedure 存储过程名
DROP PROCEDURE myp1;
DROP PROCEDURE p2,
p3;
# 错误的
#四、查看存储过程的信息
DESC myp2;
# 错误的
SHOW CREATE PROCEDURE myp2;
#案例讲解
#一、创建存储过程实现传入用户名和密码,插入到admin表中
CREATE PROCEDURE pr1 (
IN username VARCHAR (20),
IN pasword VARCHAR (20)
)
BEGIN
INSERT INTO admin (username, `password`)
VALUES
(username, pasword);
END $
CALL pr1 ('lulala', '1234') $
SELECT
*
FROM
admin $
#二、创建存储过程实现传入女神编号,返回女神名称和女神电话
CREATE PROCEDURE pr2 (
IN bId INT,
OUT bname VARCHAR (20),
OUT phone VARCHAR (20)
)
BEGIN
SELECT
b.name,
b.phone INTO bname,
phone
FROM
beauty b
WHERE b.id = bid;
END $
CALL pr2 (5, @n, @p) $
SELECT
@n,
@p $
#三、创建存储存储过程或函数实现传入两个女神生日,返回大小
CREATE PROCEDURE pr3 (
IN bd1 DATETIME,
IN bd2 DATETIME,
OUT result INT
)
BEGIN
SELECT
DATEDIFF (bd1, bd2) INTO result;
END $
CALL pr3 (
'1992-01-01 00:00:00',
'1993-01-01 00:00:00',
@r
) $
#四、创建存储过程或函数实现传入一个日期,格式化成xx年xx月xx日并返回
CREATE PROCEDURE pr4 (
IN date1 DATETIME,
OUT date2 VARCHAR (50)
)
BEGIN
SELECT
DATE_FORMAT (date1, '%y年%m月%d日') INTO date2;
END $
CALL pr4 (1992-01-01, @a) $
SELECT
@a $
#五、创建存储过程或函数实现传入女神名称,返回:女神 and 男神 格式的字符串
CREATE PROCEDURE pr5 (
IN bname VARCHAR (20),
OUT bandbo VARCHAR (30)
)
BEGIN
SELECT
CONCAT (
b.name,
' and ',
IFNULL (boyname, 'null')
) INTO bandbo
FROM
beauty b
LEFT JOIN boys bo
ON bo.id = b.boyfriend_id
WHERE b.name = bname;
END $
CALL pr5 ('刘亦菲', @a) $
SELECT
@a $
#六、创建存储过程或函数,根据传入的条目数和起始索引,查询beauty表的记录
CREATE PROCEDURE pr6 (IN startindex INT, IN size INT)
BEGIN
SELECT
*
FROM
beauty
LIMIT startindex, size;
END $
CALL pr6 (2, 4) $
函数
#函数
/*
含义:一组预先编译好的SQL语句的集合,理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
区别:
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有1 个返回,适合做处理数据后返回一个结果
*/
#一、创建语法
CREATE FUNCTION 函数名 (参数列表) RETURNS 返回类型
BEGIN
函数体
END
/*
注意:
1.参数列表 包含两部分:
参数名 参数类型
2.函数体:肯定会有return语句,如果没有会报错
如果return语句没有放在函数体的最后也不报错,但不建议
return 值;
3.函数体中仅有一句话,则可以省略begin end
4.使用 delimiter语句设置结束标记
*/
#二、调用语法
SELECT
函数名 (参数列表) #------------------------------案例演示----------------------------
#1.无参有返回
#案例:返回公司的员工个数
CREATE FUNCTION f1 () RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;
SELECT
COUNT (*) INTO c
FROM
employees;
RETURN c;
END $
SELECT
f1 () $ #2.有参有返回
#案例1:根据员工名,返回它的工资
CREATE FUNCTION f2 (ename VARCHAR (20)) RETURNS DOUBLE
BEGIN
DECLARE sal INT DEFAULT 0;
SELECT
salary INTO sal
FROM
employees
WHERE last_name = ename;
RETURN sal;
END $
SELECT
f2 ('Chen') $ #案例2:根据部门名,返回该部门的平均工资
CREATE FUNCTION f3 (dname VARCHAR (20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE DEFAULT 0;
SELECT
AVG (salary) INTO sal
FROM
employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = dname;
RETURN sal;
END $
SELECT
f3 ('IT') $ #三、查看函数
SHOW CREATE FUNCTION f3;
#四、删除函数
DROP FUNCTION f3;
#案例
#一、创建函数,实现传入两个float,返回二者之和
CREATE FUNCTION f4 (a FLOAT, b FLOAT) RETURNS FLOAT
BEGIN
DECLARE c FLOAT DEFAULT 0;
SET c = a + b;
RETURN c;
END $
流程控制
#流程控制结构
/*
顺序、分支、循环
*/
#一、分支结构
#1.if函数
/*
语法:if(条件,值1,值2)
功能:实现双分支
应用在begin end中或外面
*/
#2.case结构
/*
语法:
情况1:类似于switch
case 变量或表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end
情况2:
case
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end
应用在begin end 中或外面
*/
#3.if结构
/*
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
else 语句n;
end if;
功能:类似于多重if
只能应用在begin end 中
*/
#案例1:创建函数,实现传入成绩,如果成绩>90,返回A,如果成绩>80,返回B,
# 如果成绩>60,返回C,否则返回D
CREATE FUNCTION f5 (score INT) RETURNS VARCHAR (10)
BEGIN
DECLARE grade VARCHAR (10);
IF score > 90
THEN SET grade = 'A';
ELSEIF score > 80
THEN SET grade = 'B';
ELSEIF score > 60
THEN SET grade = 'C';
ELSE SET grade = 'D';
END IF;
RETURN grade;
END $
SELECT
f5 (88) $ #案例2:创建存储过程,如果工资<2000,则删除,如果5000>工资>2000,则涨工资1000,否则涨工资500
CREATE PROCEDURE prtest1 (IN sal DOUBLE)
BEGIN
IF sal < 2000
THEN
DELETE
FROM
employees
WHERE employees.`salary` = sal;
ELSEIF sal > 2000
AND sal < 5000
THEN
UPDATE
employees
SET
salary = salary + 1000
WHERE employees.`salary` = sal;
ELSE
UPDATE
employees
SET
salary = salary + 500
WHERE employees.`salary` = sal;
END IF;
END $ #案例1:创建函数,实现传入成绩,如果成绩>90,返回A,
# 如果成绩>80,返回B,如果成绩>60,返回C,否则返回D
CREATE FUNCTION f6 (score INT) RETURNS CHAR
BEGIN
DECLARE g CHAR;
CASE
WHEN score > 90
THEN SET g = 'A';
WHEN score > 80
THEN SET g = 'B';
WHEN score > 60
THEN SET g = 'C';
ELSE SET g = 'D';
END CASE;
RETURN g;
END $
SELECT
f6 (77) $ #二、循环结构
/*
分类:
while、loop、repeat
循环控制:
iterate类似于 continue,继续,结束本次循环,继续下一次
leave 类似于 break,跳出,结束当前所在的循环
*/
#1.while
/*
语法:
【标签:】while 循环条件 do
循环体;
end while【 标签】;
联想:
while(循环条件){
循环体;
}
*/
#2.loop
/*
语法:
【标签:】loop
循环体;
end loop 【标签】;
可以用来模拟简单的死循环
*/
#3.repeat
/*
语法:
【标签:】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;
*/
#1.没有添加循环控制语句
#案例:批量插入,根据次数插入到admin表中多条记录
CREATE PROCEDURE pro1 (IN insertTimes INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE
i <= insertTimes DO
INSERT INTO admin (username, `password`)
VALUES
(CONCAT ('Rose', i), '666');
SET i = i + 1;
END WHILE;
END $ CALL pro1 (3) $ #2.添加leave语句
#案例:批量插入,根据次数插入到admin表中多条记录,如果次数>20则停止
CREATE PROCEDURE pro2 (insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a :
WHILE
i <= insertCount DO
INSERT INTO admin (username, `password`)
VALUES
(CONCAT ('Jack', i), i);
IF i >= 20
THEN LEAVE a;
END IF;
SET i = i + 1;
END WHILE a;
END $ CALL pro2 (21) $
SELECT
*
FROM
admin;
#3.添加iterate语句
#案例:批量插入,根据次数插入到admin表中多条记录,只插入偶数次
CREATE PROCEDURE pro3 (insertCounts INT)
BEGIN
DECLARE i INT DEFAULT 0;
a :
WHILE
i <= insertCounts DO SET i = i + 1;
IF i % 2 != 0
THEN ITERATE a;
END IF;
INSERT INTO admin (username, `password`)
VALUES
(CONCAT ('Tom', i), i);
END WHILE a;
END $ CALL pro3 (24) $ #案例讲解:
/*一、已知表stringcontent
其中字段:
id 自增长
content varchar(20)
向该表插入指定个数的,随机的字符串
*/
CREATE TABLE stringcontent (
id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR (20)
);
DELIMITER $
CREATE PROCEDURE protest (times INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE str VARCHAR (26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
DECLARE startIndex INT;
#代表初始索引
DECLARE len INT;
#代表截取的字符长度
WHILE
i <= times DO SET startIndex = FLOOR (RAND () * 26+1);
#代表初始索引,随机范围1-26
SET len = FLOOR (RAND () * (20- startIndex + 1) + 1);
#代表截取长度,随机范围1-(20-startIndex+1)
INSERT INTO stringcontent (content)
VALUES
(SUBSTR (str, startIndex, len));
SET i = i + 1;
END WHILE;
END $
CALL test_randstr_insert (10) $
SELECT
*
FROM
stringcontent;