DELIMITER $
CREATE PROCEDURE pro_test1()
BEGIN
SELECT 'hell word';
END$
简单调用
CALL pro_test1();
将查询出来的某个值赋值给某个变量
CREATE PROCEDURE pro_test2()
BEGIN
DECLARE num int DEFAULT 0 ;
SELECT COUNT(*) INTO num FROM Course ;
END;
CALL pro_test2();
删除存储过程
DROP PROCEDURE pro_test2;
CREATE PROCEDURE pro_test3()
BEGIN
DECLARE num int DEFAULT 0;
SELECT COUNT(*) INTO num FROM Course ;
SELECT num;
END;
-- 查询所有的存储过程
SHOW PROCEDURE status;
-- 显示 pro_test1 创建时的详细信息
SHOW CREATE PROCEDURE pro_test1;
-- 调用存储过程
CALL pro_test3();
-- if 条件
-- 根据定义的身高变量, 判定当前身高的所属的身材类型
-- 180 及以上 -------------------------》身材高挑
-- 170 - 180 --------------------------》标准身材
-- 170 一下 --------------------------》 一般身材
CREATE PROCEDURE pro_test4()
BEGIN
DECLARE hight int DEFAULT 175;
DECLARE descprition VARCHAR(50) DEFAULT '';
if hight >= 180 THEN
SET descprition = '身材高挑';
ELSEIF hight >=170 AND hight < 180 THEN
SET descprition = '标准身材';
else
SET descprition = '一般身材';
END IF;
SELECT CONCAT('shen gao ', hight, 'shen cai ', descprition);
END;
CALL pro_test4();
-- 存储过程带参数 注意 in 标识入参, 默认就是入参,out 标识返回结果参数, inout 表示即是入参,又表示出参
CREATE PROCEDURE pro_test5(in hight int)
BEGIN
DECLARE descprition VARCHAR(50) DEFAULT '';
if hight >= 180 THEN
SET descprition = '身材高挑';
ELSEIF hight >=170 AND hight < 180 THEN
SET descprition = '标准身材';
else
SET descprition = '一般身材';
END IF;
SELECT CONCAT('shen gao ', hight, 'shen cai ', descprition);
END;
CALL pro_test5(65);
CALL pro_test5(190);
-- 应该使用变量 来接收输出参数 @descprition 标识用户会话变量
-- 用户会话变量标识, 当前连接,即当前会话结束时候,会话变量就会释放。
-- @@ 如果变量前面有两个@@ 标识系统变量。
-- 输出参数,相当于返回值。
CREATE PROCEDURE pro_test6(in hight int, out descprition VARCHAR(50))
BEGIN
if hight >= 180 THEN
SET descprition = 'shen cai gao tiao';
ELSEIF hight >=170 AND hight < 180 THEN
SET descprition = 'biao zhun shen cai';
else
SET descprition = 'yi ban shen cai';
END IF;
END;
CALL pro_test6(190, @descprition);
-- 输出变量
SELECT @descprition;
CALL pro_test6(158, @descprition);
-- 输出变量
SELECT @descprition;
-- 定义一个会话变量
-- 输出会话变量
SET @name ='itcast';
SELECT @name;
-- case 结构
-- 一种case 后面是个变量
-- 一种后面是表达式
-- 给定一个月份, 然后计算该月份的季度
-- 输入参数,可以省略in
CREATE PROCEDURE pro_test7(mounth int)
BEGIN
declare result varchar(19);
CASE
WHEN mounth >= 1 and mounth <= 3 THEN
set result = 'yi ji du';
WHEN mounth >= 4 and mounth <= 6 THEN
set result = 'er ji du';
WHEN mounth >= 7 and mounth <= 9 THEN
set result = 'san ji du';
WHEN mounth >= 10 and mounth <= 12 THEN
set result = 'si ji du';
ELSE
set result = 'input incorrect';
END CASE;
SELECT CONCAT('current mounth ', mounth, 'current ji du', result) as content;
END;
-- 删除存储过程
DROP PROCEDURE pro_test7;
-- 调用存储过程
CALL pro_test7(4);
-- 循环 while 是满足条件,就循环
-- 从1 加到n
CREATE PROCEDURE pro_test8(n int)
BEGIN
DECLARE total int DEFAULT 0;
DECLARE num int DEFAULT 1;
WHILE num <= n DO
SET total = total + num;
SET num = num + 1;
END WHILE;
SELECT total;
END;
CALL pro_test8(4);
-- repeat 满足某个条件,就退出循环
-- repeat 注意 until 后面不能用分号
-- 使用repeat 完成 1 加到n
CREATE PROCEDURE pro_test9(n int)
BEGIN
declare total int default 0;
REPEAT
SET total = total + n;
SET n = n - 1;
UNTIL n = 0
END REPEAT;
SELECT total;
END;
CALL pro_test9(50);
-- 使用loop 循环
-- 注意要给循环起个名字, 退出时候,使用leave 循环名;
-- 使用loop 完成 1 加到n
-- 注意结束循环条件 leave
CREATE PROCEDURE pro_test10(n int)
BEGIN
declare total int default 0 ;
c: LOOP
set total = total + n;
set n = n - 1;
IF n <= 0 THEN
LEAVE c;
END IF;
END LOOP c;
SELECT total;
END;
CALL pro_test10(3);
-- 游标 使用来存储查询结果集的数据类型
-- 查询表中的数据, 展示
CREATE PROCEDURE pro_test11()
BEGIN
DECLARE s_sex varchar(20);
DECLARE s_birth varchar(20);
DECLARE s_name varchar(20);
DECLARE s_id int(11);
DECLARE stu_result CURSOR FOR SELECT * FROM Student;
OPEN stu_result;
FETCH stu_result INTO s_id, s_name, s_birth, s_sex;
SELECT CONCAT('s_id: ', s_id, ' ,s_name: ', s_name, ' , s_birth: ', s_birth, ' ,s_sex: ', s_sex);
FETCH stu_result INTO s_id, s_name, s_birth, s_sex;
SELECT CONCAT('s_id: ', s_id, ' ,s_name: ', s_name, ' , s_birth: ', s_birth, ' ,s_sex: ', s_sex);
CLOSE stu_result;
END;
DROP PROCEDURE pro_test11;
CALL pro_test11();
-- 使用循环 使用游标输出
-- mysql 提供的一个光标退出机制
CREATE PROCEDURE pro_test12()
BEGIN
DECLARE s_sex varchar(20);
DECLARE s_birth varchar(20);
DECLARE s_name varchar(20);
DECLARE s_id int(11);
DECLARE has_data int default 1;
DECLARE stu_result CURSOR FOR SELECT * FROM Student;
DECLARE EXIT HANDLER FOR NOT FOUND SET has_data = 0;
OPEN stu_result;
REPEAT
FETCH stu_result INTO s_id, s_name, s_birth, s_sex;
SELECT CONCAT('s_id: ', s_id, ' ,s_name: ', s_name, ' , s_birth: ', s_birth, ' ,s_sex: ', s_sex);
UNTIL has_data= 0
END REPEAT;
CLOSE stu_result;
END;
CALL pro_test12();
-- 存储函数
-- 存储函数有返回值
-- 创建存储函数,返回一个表中的id= 01的 出生年月
CREATE FUNCTION returnCount (sid VARCHAR(20))
RETURNS VARCHAR(20)
BEGIN
DECLARE birth VARCHAR(20) DEFAULT '' ;
SELECT s_birth INTO birth FROM Student WHERE s_id = sid;
return birth;
END;
-- 删除函数
DROP FUNCTION returnCount;
-- 调用存储函数
-- 是跟调用其他函数一样的。 SELECT 函数名
SELECT returnCount('01');