mysql存储过程

mysql存储过程

#存储过程

#查看已有存储过程
SHOW PROCEDURE STATUS;

#以下2条sql语句，效果一样
SELECT 666 FROM DUAL;
SELECT 666;
/*
在MySQL中,DUAL纯粹是为了满足select ... from...这一习惯
在mysql中，SELECT 666;和SELECT 666 FROM DUAL;效果一样，但
是在oracle中，SELECT 666;这样写会报错，所以必须加上FROM DUAL，必
须写成SELECT 666 FROM DUAL;
*/
#
SELECT '江西省赣州市于都县' FROM DUAL;
SELECT '江西省赣州市于都县';
#
SELECT 10 + 15 FROM DUAL;
SELECT 10 + 15;

SELECT 5 + 3 FROM DUAL WHERE 666 = 888;

/*
过程：封装了若干条语句，调用时，这些封装体执行
函数：是一个有返回值的过程
过程：没有返回值的函数

我们把若干条sql封装起来，然后起个名字，这就是过程
把此过程存储在数据库中，我们称之为存储过程

存储过程创建语法
CREATE PROCEDURE 存储过程名()
BEGIN
sql语句;
END$

调用存储过程 CALL 存储过程名();

*/

#定义一个存储过程
DELIMITER $ #把结束符设置为$(即遇到$符号才算是结束，遇到;分号不会结束)
CREATE PROCEDURE p1()
BEGIN
SELECT '我老家在江西省赣州市于都县' FROM DUAL; #遇到;分号不会结束
SELECT 5 + 3; #遇到;分号不会结束
END$ #遇到$符号才算是结束

#调用存储过程
CALL p1();

#定义一个存储过程(存储过程中使用变量)
DELIMITER $
CREATE PROCEDURE p2()
BEGIN
DECLARE age int DEFAULT 16; #定义变量
DECLARE height int DEFAULT 174; #定义变量
SELECT CONCAT('我今年', age, '岁,我身高', height);
SELECT CONCAT('我今年', age, '岁,我身高', height) FROM DUAL;
END$

#调用存储过程
CALL p2();

/*
存储过程中是可以编程的，这就意
味着存储过程中可以使用变量、表达式、
控制结构等来完成复杂的功能

在存储过程中，用DECLARE关键字来声
明变量，语法如下：
DECLARE 变量名 数据类型 [DEFAULT 16];

*/

#赋值给变量，语法set 变量名:= 表达式/直接一个值
#在存储过程中，使用变量(拿变量做运算)
DELIMITER $
CREATE PROCEDURE p3()
BEGIN
DECLARE age int DEFAULT 16; #定义变量
DECLARE height int DEFAULT 174; #定义变量
SET age := age + 10;
SELECT CONCAT('10年后的年龄为', age, '岁,身高为', height);
END$

#调用存储过程
CALL p3();

/*
if结构
/*
功能：实现多重分支
语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
【else 语句n;】
end if;
*/
#在存储过程中，使用if else 控制结构
DELIMITER $
CREATE PROCEDURE p4()
BEGIN
DECLARE age int DEFAULT 36; #定义变量
IF age<=20 THEN SELECT '二十弱冠';
ELSEIF age<=30 THEN SELECT '三十而立';
ELSEIF age<=40 THEN SELECT '四十不惑';
ELSEIF age<=50 THEN SELECT '五十知天命';
ELSEIF age<=60 THEN SELECT '六十花甲';
ELSEIF age<=70 THEN SELECT '七十古稀';
ELSEIF age<=80 THEN SELECT '八十耄耋';
ELSE SELECT '长命百岁';
END if;
END$

#调用存储过程
CALL p4();

#
DELIMITER $
CREATE PROCEDURE p5()
BEGIN
DECLARE score int DEFAULT 79; #定义变量
IF score<60 THEN SELECT '不及格';
ELSE SELECT '及格';
END if;
END$
#调用存储过程
CALL p5();

#带参数的存储过程,语法是 CREATE PROCEDURE 存储过程名([in/out/inout] 参数名 数据类型)
#in/out/inout后面会介绍
#计算一个矩形的面积
DELIMITER $
CREATE PROCEDURE area(width INT, height INT)
BEGIN
SELECT CONCAT('矩形的面积是', width * height);
IF width > height THEN SELECT '你比较胖';
ELSEIF width < height THEN SELECT '你比较瘦';
ELSE SELECT '你比较方';
END IF;
END$

#调用带参数的存储过程
CALL area(7, 10);
CALL area(5, 2);
CALL area(20, 20);

#存储过程中使用循环
#求1到100的和
DELIMITER $
CREATE PROCEDURE p6(endNum INT)
BEGIN
DECLARE total int DEFAULT 0;
DECLARE startNumber int DEFAULT 1;
WHILE startNumber <= endNum DO
set total := total + startNumber;
set startNumber := startNumber + 1;
END WHILE;
SELECT CONCAT('1到',endNum,'的和是', total);
END$

#
CALL p6(100);
CALL p6(10);

#求1到100的和
#存储过程中带输入参数、输出参数
/*
in 表示输入参数，in关键字可以不写，因为默认就是输入参数，(注
意：in关键字可以省略不写，因为默认就是in，但是不建议省略in，最好是写上in,可读性比较好)
out 表示输出参数
inout 表示输入输出参数
*/
DELIMITER $
CREATE PROCEDURE p7(IN endNum INT, OUT total INT)
BEGIN
DECLARE startNumber int DEFAULT 1;
SET total := 0; #给total这个参数赋一个默认值，如果不赋默认值，那默认值就是NULL
WHILE startNumber <= endNum DO
set total := total + startNumber;
set startNumber := startNumber + 1;
END WHILE;
SELECT CONCAT('1到',endNum,'的和是', total);
END$

#调用存储过程
CALL p7(1000, @sum);
SELECT @sum;

#
DELIMITER $
#存储过程的输入参数，是可以省略in关键字的，如下：
CREATE PROCEDURE p8(endNum INT, OUT total INT)
BEGIN
DECLARE startNumber int DEFAULT 1;
SET total := 0;
WHILE startNumber <= endNum DO
set total := total + startNumber;
set startNumber := startNumber + 1;
END WHILE;
SELECT CONCAT('1到',endNum,'的和是：', total);
END$

#调用存储过程
CALL p8(10, @mySum);
SELECT @mySum;

#
DELIMITER $
CREATE PROCEDURE p9(endNum INT, OUT total INT)
BEGIN
DECLARE startNumber int DEFAULT 1;
# SET total := 0; #不给total这个参数赋默认值，看看会是什么结果？
WHILE startNumber <= endNum DO
set total := total + startNumber;
set startNumber := startNumber + 1;
END WHILE;
SELECT CONCAT('1到',endNum,'的和是：', total);
END$

#
CALL p9(10, @sum);
SELECT @sum;

#
SELECT NULL;
SELECT NULL = NULL;
SELECT NULL != NULL;
SELECT NULL + 2;
SELECT NULL * 6;

#存储过程中带inout输入输出参数
DELIMITER $
CREATE PROCEDURE p10(INOUT score INT)
BEGIN
SET score := score + 5;
END$

#调用存储过程
SET @myScore=89;
CALL p10(@myScore);
SELECT @myScore;

#
SELECT RAND(), RAND() * 5, FLOOR(RAND() * 5);