一、存储过程
1、存储过程含义：一组预先编译好的sql语句的集合，理解成批处理语句
语法：
#1、创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体（一组合法有效的方法体）
END注意：
1、参数列表包含三部分
参数模式，参数名，参数类型
举例：
IN stuname VARCHAR(20)
参数模式：
IN 该参数可以作为输入，也就是说该参数需要调用方传入值
OUT 该参数可以作为删除，也就是该参数可以作为返回值
INOUT 该参数既可以作为输入又可以作为输出，也就是该参数即需要传入值，又可以返回值
2、如果存储过程体仅仅只有一句话，BEGIN END 可以省略
存储过程体中每条SQL语句的结尾要求必须加分号。
存储过程的结尾可以使用DELIMITER重新设置
语法：
DELIMITER 结束标记
案例：
DELIMITER $#调用语法
CALL 存储过程名(实参列表)案例：
#1、空参列表
#案例：插入到admin表中的五条记录
准备工作：查看admin表中的数据
USE girls;
SELECT * FROM admin;具体步骤
#已$作为结尾标记
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,PASSWORD) VALUES
('rose','00000'),
('jock','00000'),
('tom','00000'),
('cat','00000'),
('back','00000');
END $ -- 需要在end之后加标记
CALL mypl() $编写以后再mysql终端内执行
注意要添加结尾标记
#2、创建带in模式参数的存储过程
#案例1：创建存储过程实现，根据女神名，查询对应的男神的信息
DELIMITER $
CREATE PROCEDURE myp2(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 myp2('倪妮')$编写以后再mysql终端内执行
#案例2：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN passwd VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0; #result变量声明
SELECT COUNT(*) INTO result #把count(*)赋值给result
FROM admin
WHERE admin.`username`=username AND PASSWORD = passwd;
SELECT IF(result>0,'成功','失败');
END $
CALL myp3('join','8888')$编写以后再mysql终端内执行
#3、创建带out模式的存储过程
#案例1：根据女神名，返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.`boyName` INTO boyName
FROM boys bo RIGHT JOIN beauty b
ON bo.id = b.boyfriend_id
WHERE b.name = beautyName;
END $
#定义用户变量bname用于接受返回的男神名
#set @bName$ 这一步可以忽略不写
CALL myp4('倪妮',@bName) $
SELECT @bName$编写以后再mysql终端内执行
#案例2：根据女神名，返回对应的男神名和魅力值
DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
SELECT bo.boyName ,bo.userCP INTO boyName,userCP 注意：给out变量赋值
FROM boys bo RIGHT JOIN beauty b
ON bo.id = b.`boyfriend_id`
WHERE b.name = beautyName;
END $
CALL myp5('倪妮',@bName,@userCp)$
SELECT @bName,@userCp $编写以后再mysql终端内执行
#4、创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回
DELIMITER $
CREATE PROCEDURE myp6(INOUT a INT,INOUT b INT )
BEGIN
SET a=a*2;
SET b=b*2;
END $
SET @a=10$
SET @b=20$
CALL myp6(@a,@b) $
SELECT @a,@b $2、删除存储过程
语法：drop procedure 存储过程名
DROP PROCEDURE myp1;注意：一次只能删除一个存储过程
3、查看存储过程的信息
SHOW CREATE PROCEDURE myp2;存储过程不能直接修改，可以先删除然后在用sql语句修改
案例：#创建存储过程实现传入一个日期，格式化成xx年xx月xx日并返回
CREATE PROCEDURE myp7(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;
END $
CALL myp7(NOW(),@strDate)$
SELECT @strDate$#创建存储过程实现传入女神名，返回：女神and男神 格式的字符串
CREATE PROCEDURE myp8(IN gName VARCHAR(50),OUT str VARCHAR(50))
BEGIN
SELECT CONCAT(gName,'and',IFNULL(boyName,'null')) INTO str
FROM beauty b LEFT JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE b.`name` = gName;
END $
CALL myp8('赵丽颖',@str)$
SELECT @str$二、函数
含义：一组预先编译好的sql语句的集合，理解成批处理语句
区别：
存储过程：可以有0个返回，也可以有多个返回适合做批量插入，批量更新
函数：有且仅有1个返回，适合做处理数据后返回的一个结果
1、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END注意：
1）参数列表包含两部分：参数名和参数类型
2)函数体：肯定有return语句，如果没有回报错
如果return语句没有放在函数体的最后也不会报错，但不建议
3）函数体中仅有一句话，则可以省略begin end
4）使用delimiter 语句设置结束标记。例如：delimiter $ -->结尾还的加上；如：select * from be $;
2、调用语法
SELECT 函数名(参数列表) #案例演示
#1、无参又反悔
#案例1：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0; #定义变量用于接收返回值
SELECT COUNT(*) INTO c
FROM employees;
RETURN c;
END $
SELECT myf1()$#案例2：根据员工名，返回他的工资
CREATE FUNCTION myf2(eName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0; #定义用户变量
SELECT Salary INTO @sal
FROM employees
WHERE Last_name = eName;
RETURN @sal;
END $
SELECT myf2('Ernst')$#案例3：根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(dpartName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE avgSal DOUBLE DEFAULT 0;
SELECT AVG(Salary) INTO avgSal
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = dpartName;
RETURN avgSal;
END $
SELECT myf3('Adm')$
2、查看函数
SHOW CREATE FUNCTION myf3;3、删除函数
DROP FUNCTION myf3;#案例：创建函数，实现传入两个float,返回二者之和
CREATE FUNCTION myf4(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
SET @res=num1+num2;
RETURN @res;
END $
SELECT myf4(1.1,1.2)$
DROP FUNCTION myf4;三、流程控制语句
顺序结构：程序从上往下一次执行
分支结构：程序从两条或多条路径中选择一条去执行
循环结构：程序在满足一定条件的基础上，重复执行一段代码
1、分支结构
#1、if函数 if(表达式1，表达式2，表达式3)
执行顺序：如果表达式1成立，执行表达式2，否则执行表达式3
#2、case结构
情况1：和Java中的switch相似，一般实现等值判断
语法：
case 变量|表达式|字段
when 要判断的值1 then 返回的值1或语句1；
when 要判断的值2 then 返回的值2或语句2；
...
else 要返回的值n或语句n;
end case；
情况2：类似于Java中的if...elseif...else 这样的多重语句
语法：
case
when 要判断的条件1 then 返回的值1或语句1；
when 要判断的条件2 then 返回的值2或语句2；
...
else 返回的值n或语句n;
end case;
# 案例：
#案例1：创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩90-100，显示A，80-90，显示B，60-80 显示C，否则显示D
DELIMITER $
CREATE PROCEDURE mypp1(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 mypp1(96)$#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 myff1(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 myff1(96)$#4、循环结构
必须放到begin end 里面
分类：while ， loop，repeat
循环控制：
iterate类似于continue继续 ，结束本次循环，继续下一次
leave 类似于break 跳出，结束当前所在的循环
1）while
语法：
【标签：】while 循环条件 do
循环体；
end 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('Rose',i),'666');
SET i=i+1;
END WHILE;
END $
CALL pro_while1(50) $# 添加leave语句 批量插入，根据次数插入到admin表中多条记录，如果次数>20 则停止
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('liyin',i),'666');
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $
CALL pro_while1(50) $#添加iterate语句，批量插入，根据次数插入到admin表中多条记录，只插入偶数次
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
SET i=i+1;
IF MOD(i,2) THEN ITERATE a;
END IF;
INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('liyin',i),'666');
END WHILE a;
END $
CALL pro_while1(50) $
======Java实现本案例的方式=====
int i=1;
FOR(i<insertCount){
i++;
if(i%2==0){
continue;
}
插入语句
}
#金典案例
已知表stringcontent
其中字段：id自增长 connect varchar(20)
向该表插入指定个数的随机的字符串
USE test;
CREATE TABLE stringcontent(
id INT PRIMARY KEY AUTO_INCREMENT,
connect VARCHAR(50)
);
DESC stringcontent;
DROP TABLE IF EXISTS test_randstr_insert;
DELIMITER $
CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
BEGIN
#定义一个循环变量i,表示插入的次数
DECLARE i INT DEFAULT 1;
#定义一个字符串，用于取数
DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwsyz';
#插入指定个数的字符串，用到了substr(str,index,len)函数
DECLARE startIndex INT DEFAULT 1; -- 定义一个索引用于实现substr函数内的index,并设置成随机数
DECLARE len INT DEFAULT 1; -- 定义长度实现substr函数内的len,并设置成随机数
WHILE i<= insertCount DO
SET startIndex = FLOOR(RAND()*26+1);-- 设置起始索引为随机数，rand()是0-1，*26变成了0-25.788这样，所以要加1，并向下取整
SET len = FLOOR(RAND()*(26-startIndex+1)+1);
INSERT INTO stringcontent (connect) VALUES (SUBSTR(str,startIndex,len));
SET i=i+1;
END WHILE;
END $
CALL test_randstr_insert(10) $
