存储过程简单记录

MYSQL存储过程基础学习

-- 创建存储过程函数
create procedure pro_test()
BEGIN
select * from t_shop;
END;

-- 查询存储过程函数
call pro_test();

-- 删除存储过程函数
drop procedure pro_test;


-- 创建存储过程,申明变量操作
create procedure test()
BEGIN
DECLARE num int DEFAULT 10;
SELECT CONCAT('num的值为:',num);
END;

-- 调用函数测试
CALL test();

-- 创建存储过程,赋值操作
CREATE PROCEDURE test2()
BEGIN
DECLARE num int DEFAULT 0;
SET num = num + 10;
SELECT num;
END;

-- 调用函数测试
CALL test2();

-- 创建存储过程,使用select into 来进行赋值
CREATE PROCEDURE test3()
BEGIN
DECLARE num int;
SELECT COUNT(id) INTO num FROM t_shop;
SELECT CONCAT('shop表中总共存储的数据条数为:',num); 
END;

-- 调用函数测试
CALL test3();



-- 创建IF判断的存储函数
CREATE PROCEDURE test4()
BEGIN
 DECLARE height int DEFAULT 100;
 DECLARE description  varchar(50) DEFAULT '';
 IF height >= 105 THEN SET description = '面积大';
 ELSEIF height <= 100 AND height >= 80 THEN SET description = '标准面积';
 ELSE SET description = '小面积';
 END IF;
 SELECT CONCAT('这个房源的面积属于:' , description);
END;

-- 测试IF判断函数
CALL test4();


-- 创建存储过程传递参数
CREATE PROCEDURE test5(in height int)
BEGIN
 DECLARE description  varchar(50) DEFAULT '';
 IF height >= 105 THEN SET description = '面积大';
 ELSEIF height <= 100 AND height >= 80 THEN SET description = '标准面积';
 ELSE SET description = '小面积';
 END IF;
 SELECT CONCAT('这个房源的面积属于:' , description);
END;


-- 测试传递参数
CALL test5(180);

-- 创建存储过程传递参数并且有返回值
CREATE PROCEDURE test6(in height int, out description varchar(10))
BEGIN
 IF height >= 105 THEN SET description = '面积大';
 ELSEIF height <= 100 AND height >= 80 THEN SET description = '标准面积';
 ELSE SET description = '小面积';
 END IF;
END;

-- 测试传递参数
CALL test6(180,@description);

-- 查询变量 : '@' 申明用户会话变量 ; '@@' 申明系统变量 
SELECT @description;


-- 创建存储过程 使用CASE WHEN 语法
CREATE PROCEDURE test7(in mon int)
BEGIN
	declare result varchar(10);
	CASE 
		WHEN mon >= 1 AND mon <= 3 then 
			set result = '第一季度';
		WHEN mon >= 4 AND mon <= 6 then 
			set result = '第二季度';
		WHEN mon >= 7 AND mon <= 9 then 
			set result = '第三季度';
		ELSE set result = '第四季度';
	END CASE;
	
	SELECT CONCAT('传递的月份为:' , mon , ',计算结果为:',result) as content;
END;


-- 测试存储过程 CASE WHEN语法

CALL test7(5);

-- 创建存储过程 使用while循环
CREATE PROCEDURE 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 test8(100);


-- 创建repeat循环存储过程
CREATE PROCEDURE 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;

-- 测试repeat循环
CALL test9(100);


-- 创建loop ... leave循环存储过程
CREATE PROCEDURE 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;

-- 测试loop ... leave 循环
CALL test10(50);

-- 创建存储函数 游标的使用
-- 1. 申明游标 : DECLARE cursor_name CURSOR FOR select_statement;
-- 2. OPEN游标 : OPEN cursor_name;
-- 3. FETCH游标 : FETCH cursor_name INTO var_name[,var_name] ...;
-- 4. CLOSE游标 : CLOSE cursor_name;
CREATE PROCEDURE test11()
BEGIN
	DECLARE s_id varchar(32);
	DECLARE s_name varchar(64);
	DECLARE s_number varchar(32);
	DECLARE s_delFlag int(1); 
	DECLARE has_data int(1) DEFAULT 1;	
	-- 申明游标
	DECLARE shop_result cursor for SELECT id,name,number,del_flag FROM t_shop;
	-- 申明退出条件(必须放置在游标之后)
	DECLARE EXIT HANDLER FOR NOT FOUND SET has_data = 0;
	-- 打开游标
	OPEN shop_result;
	-- while循环
	WHILE has_data > 0 DO 
		-- 抓取游标
		FETCH shop_result INTO s_id,s_name,s_number,s_delFlag;
		SELECT CONCAT('商铺Id为: ',s_id,'商铺名称为: ',s_name,'商铺编号为: ',s_number,'商铺状态为: ',s_delFlag);
	END WHILE;	
	
	CLOSE shop_result;
END;

-- 测试游标的使用
CALL test11();