mysql存储过程

1.变量

局部变量

用户自定义，在begin/end块中有效

语法：

declare name type [default var_value];
e.g.: declare name varchar(32);

举例：

delimiter $$
CREATE PROCEDURE test1()
BEGIN
	declare nickname varchar(32) default "James";
	set nickname = "ming";
	select nickname;
END$$

call test1()

全局变量

用户自定义，当前会话（连接）有效

语法：

@name
-- 不需要提前声明，使用即声明

示例：

delimiter $$
CREATE PROCEDURE test2()
BEGIN
	set @nickname = "Lucy";
END$$
call test2 $$
select @nickname $$

给全局变量赋值

delimiter $$
CREATE PROCEDURE test3()
BEGIN
	select account.`name` into @username from account where account.id=1;
END$$

call test3 $$
select @username $$

会话变量

2.入参与出参

语法：

in | out | inout | param_name type

入参示例：

delimiter $$
CREATE PROCEDURE test4(in nickname varchar(32))
BEGIN
	set @user_name = nickname;
END$$

call test4("ming") $$

select @user_name $$

出参示例：

delimiter $$
CREATE PROCEDURE test5(in address varchar(32), out account_name varchar(32))
BEGIN
	select account.`name` into account_name from account where account.`address`=address;
END$$

call test5("beijing",@account_name) $$

select @account_name $$

出入参示例：

delimiter $$
CREATE PROCEDURE test_inout(in address varchar(32), inout account_name varchar(32))
BEGIN
	declare a_name varchar(32);
	set a_name = account_name;
	select account.`name` into account_name from account where account.`address`=address;
	select concat(a_name, " ",account_name);
END$$

-- 执行
set @account_name = "hello";
call test_inout("beijing",@account_name);

3.流程控制

IF语法

IF search_condition THEN statement_list
	[ELSEIF search_condition THEN statement_list]...
	[ELSE statement_list]
END IF

示例

delimiter $$
CREATE PROCEDURE test_years(in account_id int)
BEGIN
	declare result varchar(32);
	declare years int;
	select timestampdiff(year,account.info_date,now()) into years from account where account.id=account_id;
	if years > 30
		then set result = "老员工";
	elseif years > 20
		then set result = "骨干";
	else
		set result = "新人";
	end if;
	select result;
END$$

-- 执行
call test_years(1);

CASE语法

-- 语法1
CASE case_value
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list]
	[ELSE statement_list]
END CASE
-- 语法2
CASE
	WHEN search_condition THEN statement_list
	[WHEN search_condition THEN statement_list]...
	[ELSE statement_lsit]
END CASE

示例

delimiter $$
CREATE PROCEDURE test_years_case(in account_id int)
BEGIN
	declare result varchar(32);
	declare years int;
	select timestampdiff(year,account.info_date,now()) into years from account where account.id=account_id;
	case
	when years > 30
		then
			set result = "老员工";
	when years > 20
		then
			set result = "骨干";
	else
		set result = "新人";
	end case;
	select result;
END$$
-- 执行
call test_years_case(1);

4.循环

4.1 loop

语法

[begin_label:]LOOP
	statement_list
END LOOP [end_label]

loop是死循环，需要手动退出循环，我们可以使用leave退出，可以把leave看成break语句，与之对应的，就有iterate(继续循环)，类比continue。

leave示例

delimiter $$
CREATE PROCEDURE test_loop()
BEGIN
	declare c_idx int default 1;
	tag:loop -- 声明一个loop标签
		select c_idx;
		if c_idx >= 10
			then leave tag; -- 条件成立结束死循环
		end if;
		set c_idx = c_idx + 1;
	end loop tag;
END$$

call test_loop()

iterate + leave 控制循环

delimiter $$
CREATE PROCEDURE test_loop2()
BEGIN
	declare c_idx int default 1;
	declare result varchar(256) default "1";
	tag:loop
		set c_idx = c_idx +1;
		set result = concat(result, ',', c_idx);
		if c_idx < 10 then
			iterate tag; -- iterate 相当于 python中continue,下面代码不执行
		end if;
		leave tag; -- 当不满足条件时，离开循环
	end loop tag;
	select result;
END$$

test_loop2()  -- 1,2,3,4,5,6,7,8,9,10

4.2 repeat

语法：

[begin_label:] REPEAT
	statement_list
UNTIL search_condition -- 直到...为止 退出循环
END REPEAT [end_label]

示例

delimiter $$
CREATE PROCEDURE test_repeat()
BEGIN
	declare c_idx int default 1;
	declare result varchar(256) default "1";
	cnt:repeat
		set c_idx = c_idx +1;
		set result = concat(result, ',', c_idx);
		until c_idx >= 10
	end repeat cnt;
	select result;
END$$
-- 执行
call test_repeat()

4.3 while

语法

[begin_label:] WHILE search_condition DO
	statement_list
END WHILE [end_label]

示例


delimiter $$
CREATE PROCEDURE test_while()
BEGIN
	declare c_idx int default 1;
	declare result varchar(256) default "1";
	while c_idx < 10 Do
		set c_idx = c_idx +1;
		set result = concat(result, ",", c_idx);
	end while;
	select result;
END$$
-- 执行
call test_while() -- 1,2,3,4,5,6,7,8,9,10

5.游标

用游标得到某一个结果集，逐行处理数据

-- 声明语法
DECLARE cursor_name CURSOR FOR select_statement;
-- 打开语法
OPEN cursor_name
-- 取值语法 每次取一行
FETCH cursor_name INTO var_name [, var_name] ...
-- 关闭语法
CLOSE cursor_name

示例：根据输入地址，打印消息

delimiter $$
CREATE PROCEDURE test_cursor(in address varchar(32))
BEGIN
	declare uid int;
	declare uname varchar(32);
	declare age int;
	declare lp_flag boolean default true;-- 标记
	declare address_cursor cursor for 
		select account.`id`,account.`name`,account.`age` from account where account.`address` = address;
	-- handler 句柄 当出现 1329异常 将lp_flag改为false 并继续执行， 当把continue换成exit则会
	-- 也可以使用NOT FOUND
	declare continue handler for 1329 set lp_flag = false;
	open address_cursor;-- 打开游标
	-- 遍历游标
	cnt:loop
		fetch address_cursor into uid,uname,age;-- 获取每行数据,当数据没了会报错，这样会触发 handler句柄，
		if lp_flag then
			select uid,uname,age;
		else
			leave cnt;
		end if;
	end loop cnt;

	close address_cursor;-- 关闭游标
END$$
call test_cursor('beijing');

示例：查询beijing，用户名不为ming的岁数加1

delimiter $$
CREATE PROCEDURE test_cursor2(in address varchar(32))
BEGIN
	declare uid int;
	declare uname varchar(32);
	declare age int;
	declare lp_flag boolean default true;-- 标记
	declare address_cursor cursor for 
		select account.`id`,account.`name`,account.`age` from account where account.`address` = address;
	-- handler 句柄 当出现 1329异常 将lp_flag改为false 并继续执行， 当把continue换成exit则会
	-- 也可以使用NOT FOUND
	declare continue handler for NOT FOUND set lp_flag = false;
	open address_cursor;-- 打开游标
	-- 遍历游标
	cnt:loop
		fetch address_cursor into uid,uname,age;-- 获取每行数据,当数据没了会报错，这样会触发 handler句柄，
		if lp_flag then
			if uname = "ming" then
				iterate cnt;
			else
				update account set account.age=account.age + 1 where account.id=uid;
			end if;
		else
			leave cnt;
		end if;
	end loop cnt;

	close address_cursor;-- 关闭游标
END$$
-- 执行
call test_cursor2('beijing');

6.循环创建表

我们需要用某个表记录很多数据，比如记录用户行为，当每天记录较多时，如果把所有数据都记录到一张表中，过于庞大，需要分表，我们的要求是每天一张表，存当前统计数据，就要提前创建这些表，每月月底创建下一个月每天的表。

知识点：预编译

PREPARE stmt_name FROM preparable_stmt
EXECUTE stmt_name [USING @var_name [, @var_name] ...]
{DEALLOCATE | DROP} PREPARE stmt_name

时间的处理

EXTRACT(unit FROM date) 截取时间的指定位置值
DATE_ADD(date,INTERVAL expr unit) 日期运算
LAST_DAY(date)  获取日期的最后一天
YEAR(date)	返回日期中的年
MONTH(date)	返回日期中的月
DAYOFMONTH(date) 返回日

示例代码：

delimiter $$
create procedure sp_create_table()
BEGIN
	declare next_year int;
	declare next_month int;
	declare next_month_str char(2);
	declare next_month_day int;
	declare next_month_day_str char(2);
	declare table_name_str char(10);
	declare t_index int default 1;-- 控制循环
	-- 获取下个月年份
	set next_year = year(date_add(now(),INTERVAL 1 month));
	-- 获取下个月是几月
	set next_month = month(date_add(now(), INTERVAL 1 month));
	-- 下个月最后一天是几号
	set next_month_day = dayofmonth(LAST_DAY(date_add(now(), INTERVAL 1 month)));
	if next_month < 10
		then set next_month_str = concat('0', next_month);
	else
		set next_month_str = concat('', next_month);
	end if;
	
	while t_index <= next_month_day do
		if (t_index < 10)
			then set next_month_day_str = concat('0', t_index);
		else
			set next_month_day_str = concat('',t_index);
		end if;
		-- 表名
		set table_name_str = concat(next_year, "_", next_month_str, "_", next_month_day_str);
		-- sql语句拼接
		set @create_table_sql = concat(
		"create table comp_", 
		table_name_str,
		"(`grade` INT(11) NULL, `losal` INT(11) NULL, `hisal` INT(11) NULL) COLLATE=\'utf8_general_ci\'");
		
		-- 预编译执行语句 FROM后面不能使用局部变量
		prepare create_table_stmt FROM @create_table_sql;
		execute create_table_stmt;
		DEALLOCATE prepare create_table_stmt;
		
		set t_index = t_index + 1;
	end while;
END$$

posted @ 2021-02-01 14:54 是阿凯啊阅读(69) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

阿凯

mysql存储过程

mysql存储过程

1.变量

2.入参与出参

3.流程控制

4.循环

4.1 loop

4.2 repeat

4.3 while

5.游标

6.循环创建表

公告