由系统提供,当前会话(连接)有效
语法:
@@session.var_name
show session variables; – 查看会话变量
select @@session.unique_checks; – 查看某会话变量
set @@session.unique_checks = 0; --修改会话变量
4)全局变量
由系统提供,整个mysql服务器有效
语法:
@@global.var_name
举例
– 查看全局变量中变量名有char的记录
show global variables like ‘%char%’;
– 查看全局变量character_set_client的值
select @@global.character_set_client;
3.2 入参出参
– 语法
in | out | inout param_name type
举例
– IN类型演示
delimiter $$
create procedure sp_param01(in age int)
begin
set @user_age = age;
end$$
call sp_param01(10) $$
select @user_age$$
– OUT类型,只负责输出!
– 需求:输出传入的地址字符串对应的部门编号。
delimiter $$
create procedure sp_param02(in loc varchar(64),out dept_no int(11))
begin
select d.deptno into dept_no from dept d where d.loc = loc;
–此处强调,要么表起别名,要么入参名不与字段名一致
end$$
delimiter ;
–测试
set @dept_no = 100;
call sp_param01(‘DALLAS’,@dept_no);
select @dept_no;
– INOUT类型
delimiter $$
create procedure sp_param03(inout name varchar)
begin
set name = concat(‘hello’ ,name);
end$$
delimiter ;
set @user_name = ‘小明’;
call sp_param03(@user_name);
select @user_name;
3.3 流程控制-判断
官网说明:https://dev.mysql.com/doc/refman/5.6/en/flow-control-statements.html
if
– 语法
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] …
[ELSE statement_list]
END IF
举例:
– 前置知识点:timestampdiff(unit,exp1,exp2) 取差值exp2-exp1差值,单位是unit
select timestampdiff(year,e.hiredate,now()) from emp e where e.empno = ‘7499’;
– 需求:入职年限<=38是新手 >38并且<=40老员工 >40元老
delimiter $$
create procedure sp_hire_if()
begin
declare result varchar(32);
if timestampdiff(year,‘2001-01-01’,now()) > 40
then set result = ‘元老’;
elseif timestampdiff(year,‘2001-01-01’,now()) > 38
then set result = ‘老员工’;
else
set result = ‘新手’;
end if;
select result;
end$$
delimiter ;
case
此语法是不仅可以用在存储过程,查询语句也可以用!
– 语法一(类比java的switch):
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] …
[ELSE statement_list]
END CASE
– 语法二:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] …
[ELSE statement_list]
END CASE
举例:
– 需求:入职年限年龄<=38是新手 >38并 <=40老员工 >40元老
delimiter $$
create procedure sp_hire_case()
begin
declare result varchar(32);
declare message varchar(64);
case
when timestampdiff(year,‘2001-01-01’,now()) > 40
then
set result = ‘元老’;
set message = ‘老爷爷’;
when timestampdiff(year,‘2001-01-01’,now()) > 38
then
set result = ‘老员工’;
set message = ‘油腻中年人’;
else
set result = ‘新手’;
set message = ‘萌新’;
end case;
select result;
end$$
delimiter ;
3.4 流程控制-循环
loop
– 语法
[begin_label:] LOOP
statement_list
END LOOP [end_label]
举例
需要说明,loop是死循环,需要手动退出循环,我们可以使用
leave
来退出。
可以把leave看成我们java中的break;与之对应的,就有
iterate
(继续循环)——类比java的continue
–需求:循环打印1到10
– leave控制循环的退出
delimiter $$
create procedure sp_flow_loop()
begin
declare c_index int default 1;
declare result_str varchar(256) default ‘1’;
cnt:loop
if c_index >= 10
then leave cnt;
end if;
set c_index = c_index + 1;
set result_str = concat(result_str,‘,’,c_index);
end loop cnt;
select result_str;
end$$
– iterate + leave控制循环
delimiter $$
create procedure sp_flow_loop02()
begin
declare c_index int default 1;
declare result_str varchar(256) default ‘1’;
cnt:loop
set c_index = c_index + 1;
set result_str = concat(result_str,‘,’,c_index);
if c_index < 10 then
iterate cnt;
end if;
– 下面这句话能否执行到?什么时候执行到? 当c_index < 10为false时执行
leave cnt;
end loop cnt;
select result_str;
end$$
repeat
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3xOCz6YT-1633920266336)(assets/1586956047152.png)]
[begin_label:] REPEAT
statement_list
UNTIL search_condition – 直到…为止,才退出循环
END REPEAT [end_label]
– 需求:循环打印1到10
delimiter $$
create procedure sp_flow_repeat()
begin
declare c_index int default 1;
– 收集结果字符串
declare result_str varchar(256) default ‘1’;
count_lab:repeat
set c_index = c_index + 1;
set result_str = concat(result_str,‘,’,c_index);
until c_index >= 10
end repeat count_lab;
select result_str;
end$$
while
类比java的while(){}
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
– 需求:循环打印1到10
delimiter $$
create procedure sp_flow_while()
begin
declare c_index int default 1;
– 收集结果字符串
declare result_str varchar(256) default ‘1’;
while c_index < 10 do
set c_index = c_index + 1;
set result_str = concat(result_str,‘,’,c_index);
end while;
select result_str;
end$$
3.5 流程控制-退出、继续循环
leave
类比java的breake
– 退出 LEAVE can be used within BEGIN … END or loop constructs (LOOP, REPEAT, WHILE).
LEAVE label
iterate
类比java的continue
– 继续循环 ITERATE can appear only within LOOP, REPEAT, and WHILE statements
ITERATE label
3.6 游标
用游标得到某一个结果集,逐行处理数据。
类比jdbc的ResultSet
– 声明语法
DECLARE cursor_name CURSOR FOR select_statement
– 打开语法
OPEN cursor_name
– 取值语法
FETCH cursor_name INTO var_name [, var_name] …
– 关闭语法
CLOSE cursor_name
– 需求:按照部门名称查询员工,通过select查看员工的编号、姓名、薪资。(注意,此处仅仅演示游标用法)
delimiter $$
create procedure sp_create_table02(in dept_name varchar(32))
begin
declare e_no int;
declare e_name varchar(32);
declare e_sal decimal(7,2);
declare lp_flag boolean default true;
declare emp_cursor cursor for
select e.empno,e.ename,e.sal
from emp e,dept d
where e.deptno = d.deptno and d.dname = dept_name;
– handler 句柄
declare continue handler for NOT FOUND set lp_flag = false;
open emp_cursor;
emp_loop:loop
fetch emp_cursor into e_no,e_name,e_sal;
if lp_flag then
select e_no,e_name,e_sal;
else
leave emp_loop;
end if;
end loop emp_loop;
set @end_falg = ‘exit_flag’;
close emp_cursor;
end$$
call sp_create_table02(‘RESEARCH’);
特别注意:
在语法中,变量声明、游标声明、handler声明是必须按照先后顺序书写的,否则创建存储过程出错。
3.7 存储过程中的handler
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] …
statement
handler_action: {
CONTINUE
| EXIT
| UNDO
}
condition_value: {
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
}
CONTINUE: Execution of the current program continues.
EXIT: Execution terminates for the BEGIN … END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block.
SQLWARNING: Shorthand for the class of SQLSTATE values that begin with ‘01’.
NOT FOUND: Shorthand for the class of SQLSTATE values that begin with ‘02’.
SQLEXCEPTION: Shorthand for the class of SQLSTATE values that do not begin with ‘00’, ‘01’, or ‘02’.
– 各种写法:
DECLARE exit HANDLER FOR SQLSTATE ‘42S01’ set @res_table = ‘EXISTS’;
DECLARE continue HANDLER FOR 1050 set @res_table = ‘EXISTS’;
DECLARE continue HANDLER FOR not found set @res_table = ‘EXISTS’;
4.练习
——大家注意,存储过程的业务过程在java代码中一般也可以实现,我们下面的需求是为了练习存储过程
4.1 利用存储过程更新数据
为某部门(需指定)的人员涨薪100;如果是公司总裁,则不涨薪。
delimiter //
create procedure high_sal(in dept_name varchar(32))
begin
declare e_no int;
declare e_name varchar(32);
declare e_sal decimal(7,2);
declare lp_flag boolean default true;
declare emp_cursor cursor for
select e.empno,e.ename,e.sal
from emp e,dept d
where e.deptno = d.deptno and d.dname = dept_name;
– handler 句柄
declare continue handler for NOT FOUND set lp_flag = false;
open emp_cursor;
emp_loop:loop
fetch emp_cursor into e_no,e_name,e_sal;
if lp_flag then
if e_name = ‘king’ then
iterate emp_loop;
else
update emp e set e.sal = e.sal + 100 where e.empno = e_no;
end if;
else
leave emp_loop;
end if;
end loop emp_loop;
set @end_falg = ‘exit_flag’;
close emp_cursor;
end //
call high_sal(‘ACCOUNTING’);
4.2 循环创建表
创建下个月的每天对应的表comp_2020_04_01、comp_2020_04_02、…
(模拟)需求描述:
我们需要用某个表记录很多数据,比如记录某某用户的搜索、购买行为(注意,此处是假设用数据库保存),当每天记录较多时,如果把所有数据都记录到一张表中太庞大,需要分表,我们的要求是,每天一张表,存当天的统计数据,就要求提前生产这些表——每月月底创建下一个月每天的表!
– 知识点 预处理 prepare语句from后使用局部变量会报错
– https://dev.mysql.com/doc/refman/5.6/en/sql-prepared-statements.html
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) 返回日
– 思路:循环构建表名 comp_2020_05_01 到 comp_2020_05_31;并执行create语句。
delimiter //
create procedure sp_create_table()
begin
declare next_year int;
declare next_month int;
declare next_month_day int;
declare next_month_str char(2);
declare next_month_day_str char(2);
– 处理每天的表名
declare table_name_str char(10);
declare t_index int default 1;
– declare create_table_sql varchar(200);
– 获取下个月的年份
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;
– 2020_05_01
set table_name_str = concat(next_year,‘‘,next_month_str,’’,next_month_day_str);
– 拼接create 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’ ENGINE=InnoDB’);
– 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//
call sp_create_table()
4.3 其他场景
“为用户添加购物积分,并更新到用户的总积分表中”等需要对多张表进行CRUD操作的业务。
而且内部可以使用事务命令。
5.其他
5.1 characteristic
characteristic:
COMMENT ‘string’
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
其中,SQL SECURITY的含义如下:
MySQL存储过程是通过指定SQL SECURITY子句指定执行存储过程的实际用户;
如果SQL SECURITY子句指定为DEFINER(定义者),存储过程将使用存储过程的DEFINER执行存储过程,验证调用存储
过程的用户是否具有存储过程的execute权限和DEFINER用户是否具有存储过程引用的相关对象(存储过程中的表等对象)的权限;
如果SQL SECURITY子句指定为INVOKER(调用者),那么MySQL将使用当前调用存储过程的用户执行此过程,并验证用户是否具有存储过程的execute权限和存储过程引用的相关对象的权限;
如果不显示的指定SQL SECURITY子句,MySQL默认将以DEFINER执行存储过程。
5.2 死循环处理
最后
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数Java工程师,想要提升技能,往往是自己摸索成长,自己不成体系的自学效果低效漫长且无助。
因此收集整理了一份《2024年Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,不论你是刚入门Android开发的新手,还是希望在技术上不断提升的资深开发者,这些资料都将为你打开新的学习之门!
如果你觉得这些内容对你有帮助,需要这份全套学习资料的朋友可以戳我获取!!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
acteristic:
COMMENT ‘string’
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
其中,SQL SECURITY的含义如下:
MySQL存储过程是通过指定SQL SECURITY子句指定执行存储过程的实际用户;
如果SQL SECURITY子句指定为DEFINER(定义者),存储过程将使用存储过程的DEFINER执行存储过程,验证调用存储
过程的用户是否具有存储过程的execute权限和DEFINER用户是否具有存储过程引用的相关对象(存储过程中的表等对象)的权限;
如果SQL SECURITY子句指定为INVOKER(调用者),那么MySQL将使用当前调用存储过程的用户执行此过程,并验证用户是否具有存储过程的execute权限和存储过程引用的相关对象的权限;
如果不显示的指定SQL SECURITY子句,MySQL默认将以DEFINER执行存储过程。
5.2 死循环处理
最后
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数Java工程师,想要提升技能,往往是自己摸索成长,自己不成体系的自学效果低效漫长且无助。
因此收集整理了一份《2024年Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
[外链图片转存中…(img-C9GWxIbr-1715543723664)]
[外链图片转存中…(img-SyEjueGE-1715543723664)]
[外链图片转存中…(img-K7lPeeu3-1715543723665)]
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,不论你是刚入门Android开发的新手,还是希望在技术上不断提升的资深开发者,这些资料都将为你打开新的学习之门!
如果你觉得这些内容对你有帮助,需要这份全套学习资料的朋友可以戳我获取!!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!