MySQL学习笔记
文章目录
1 概述
SPL例程由开始语句，语句块，结束语句组成。
SPL例程根据是否需要返回值，分为存储过程和函数。
语句块由SQL和SPL语句组成。
—1.1 存储过程
语法
create procedure proc_name(param1 data_type1, param2 data_type2, ...)
spl_code;
end procedure;
—1.2 函数
语法
create function func_name(param1 data_type1, param2 data_type2, ...)
returning data_type1 [as id1], data_type2 [as id2], ...
spl_code;
return val1, val2, ... [with resume];
end function;
2 语法
—2.1 变量声明
语法
define var data_type; 本地变量 define global var data_type default def_value; 全局变量
示例
create procedure up_test_define1() define m, n int; define global i int default 1; define global loginname varchar(20) default 'admin'; end procedure; 需要有default默认值。
create procedure up_test_define2()
define global i int default 0;
define global loginname varchar(20) default '';
insert into t_user values(i, loginname);
let i = i + 1;
let loginname = concat('user_', to_char(i));
end procedure;
> create table t_user(f_userid int,f_username varchar(20));
Table created.
> call up_test_define2();
Routine executed.
> select * from t_user;
f_userid f_username
1 admin
1 row(s) retrieved.
> call up_test_define2();
Routine executed.
> select * from t_user;
f_userid f_username
1 admin
2 user_2
—2.2 变量赋值
语法
let var = value; let var1, var2,... = val1, val2, ...; let var1, var2, ... = function(args1, args2, ...); let var1, var2, ... = (select col1, col2, ... from table_name);
示例1
create procedure up_test_let1() define userid int; define username varchar(20); let userid, username = 99, 'gbasedbt'; insert into t_user values(userid, username); end procedure;
> call up_test_let1();
Routine executed.
> select * from t_user;
f_userid f_username
1 admin
2 user_2
99 gbasedbt
示例2
create function fn_get_user_info(userid int)
returning int as userid, varchar(20) as username
return userid, concat('user_func_', to_char(userid));
end function;
create procedure up_test_let2()
define userid int;
define username varchar(20);
let userid, username = fn_get_user_info(888);
insert into t_user values(userid, username);
end procedure;
> call up_test_let2();
Routine executed.
> select * from t_user;
f_userid f_username
1 admin
2 user_2
99 gbasedbt
888 user_func_888
示例3
create procedure up_test_let3() define userid int; define username varchar(20); let userid, username = (select first 1 f_userid * 10 as f_userid, concat(f_username, '_select') from t_user); insert into t_user values(userid, username); end procedure;
> call up_test_let3();
Routine executed.
> select * from t_user;
f_userid f_username
1 admin
2 user_2
99 gbasedbt
888 user_func_888
10 admin_select
—2.3 条件
语法
case expr
when val_1 then
code_1
[when val_2 then
code_2 ...]
else
code_n
end case;
示例1
create function fn_test_case(month int)
returning varchar(20) as f_quarter
define quarter varchar(20);
case month
when 1 then
let quarter = '第一季度';
when 2 then
let quarter = '第一季度';
when 3 then
let quarter = '第一季度';
when 4 then
let quarter = '第二季度';
when 5 then
let quarter = '第二季度';
when 6 then
let quarter = '第二季度';
when 7 then
let quarter = '第三季度';
when 8 then
let quarter = '第三季度';
when 9 then
let quarter = '第三季度';
when 10 then
let quarter = '第四季度';
when 11 then
let quarter = '第四季度';
when 12 then
let quarter = '第四季度';
else
let quarter = 'UNK';
end case;
return quarter;
end function;
> call fn_test_case(3); f_quarter 第一季度 1 row(s) retrieved. > call fn_test_case(8); f_quarter 第三季度 1 row(s) retrieved. > call fn_test_case(108); f_quarter UNK 1 row(s) retrieved.
语法
if condition_1 then
code_1
elif condition_2 then
code_2
elif condition_3 then
code_3
...
else
code_n
end if;
示例2
create function fn_test_if(month int)
returning varchar(20) as f_quarter
define quarter varchar(20);
if month >= 1 and month <=3 then
let quarter = '第一季度';
elif month >= 4 and month <=6 then
let quarter = '第二季度';
elif month >= 7 and month <=9 then
let quarter = '第三季度';
elif month >= 10 and month <= 12 then
let quarter = '第四季度';
else
let quarter = 'UNK';
end if;
return quarter;
end function;
> call fn_test_if(5); f_quarter 第二季度 1 row(s) retrieved. > call fn_test_if(11); f_quarter 第四季度 1 row(s) retrieved. > call fn_test_if(111); f_quarter UNK
—2.4 循环
——2.4.1 GOTO
语法
<<lbl_goto>>
spl_code;
if expr then
goto lbl_goto;
end if;
示例
create procedure up_test_goto()
define i int;
let i = 0;
begin
<<loop_label>>
begin
let i = i + 1;
end;
if i < 10 then
insert into t_user values(i, concat('user_loop', to_char(i)));
goto loop_label;
end if;
end;
end procedure;
> delete from t_user;
5 row(s) deleted.
> call up_test_goto();
Routine executed.
> select * from t_user;
f_userid f_username
1 user_loop1
2 user_loop2
3 user_loop3
4 user_loop4
5 user_loop5
6 user_loop6
7 user_loop7
8 user_loop8
9 user_loop9
——2.4.2 LOOP
语法
loop
if expr then
exit;
end if;
end loop;
loop
exit when expr;
end loop;
示例1
create procedure up_test_loop1()
define i int;
let i = 1;
loop
if i > 10 then
exit;
else
insert into t_user values(i, concat('user_loop1_', to_char(i)));
end if;
let i = i + 1;
end loop;
end procedure;
> call up_test_loop1();
Routine executed.
> select * from t_user;
f_userid f_username
1 user_loop1_1
2 user_loop1_2
3 user_loop1_3
4 user_loop1_4
5 user_loop1_5
6 user_loop1_6
7 user_loop1_7
8 user_loop1_8
9 user_loop1_9
10 user_loop1_10
示例2
create procedure up_test_loop2()
define i int;
let i = 1;
loop
exit when i > 10;
insert into t_user values(i, concat('user_loop2_', to_char(i)));
let i = i + 1;
end loop;
end procedure;
> delete from t_user;
19 row(s) deleted.
> call up_test_loop2();
Routine executed.
> select * from t_user;
f_userid f_username
1 user_loop2_1
2 user_loop2_2
3 user_loop2_3
4 user_loop2_4
5 user_loop2_5
6 user_loop2_6
7 user_loop2_7
8 user_loop2_8
9 user_loop2_9
10 user_loop2_10
——2.4.3 FOR/FOREACH
语法
for i in (start_val to end_val) loop
spl_code;
end loop;
for i in (start_val to end_val)
spl_code;
end for;
foreach select col1, col2,... into var1, var2,... from table_name
spl_code;
end foreach;
示例1
create procedure up_test_for1()
define i int;
for i in (1 to 10) loop
insert into t_user values(i, concat('user_for1_', to_char(i)));
end loop;
end procedure;
> delete from t_user;
10 row(s) deleted.
> call up_test_for1();
Routine executed.
> select * from t_user;
f_userid f_username
1 user_for1_1
2 user_for1_2
3 user_for1_3
4 user_for1_4
5 user_for1_5
6 user_for1_6
7 user_for1_7
8 user_for1_8
9 user_for1_9
10 user_for1_10
示例2
create procedure up_test_for2()
define i int;
for i in (1 to 10)
insert into t_user values(i, concat('user_for2_', to_char(i)));
end for;
end procedure;
> delete from t_user;
10 row(s) deleted.
> call up_test_for2();
Routine executed.
> select * from t_user;
f_userid f_username
1 user_for2_1
2 user_for2_2
3 user_for2_3
4 user_for2_4
5 user_for2_5
6 user_for2_6
7 user_for2_7
8 user_for2_8
9 user_for2_9
10 user_for2_10
示例3
create procedure up_test_foreach()
define userid int;
define username varchar(20);
foreach select f_userid, f_username into userid, username from t_user
if mod(userid, 2) == 0 then
insert into t_user2 values(userid, username);
end if;
end foreach;
end procedure;
> info tables;
Table name
t_city t_date t_datt t_dept
t_employee t_float t_log t_money
t_sale t_user t_user1 t_user3
> create table t_user2(f_userid int,f_username varchar(20));
Table created.
> call up_test_foreach();
Routine executed.
> select * from t_user2;
f_userid f_username
2 user_for2_2
4 user_for2_4
6 user_for2_6
8 user_for2_8
10 user_for2_10
——2.4.4 WHILE
语法
while expr loop
spl_code;
end loop;
while expr
spl_code;
end while;
示例1
create procedure up_test_while1()
define i int;
let i = 1;
while i < 10 loop
insert into t_user values(i, concat('user_while1_', to_char(i)));
let i = i + 1;
end loop;
end procedure;
> call up_test_while1();
Routine executed.
> select * from t_user;
f_userid f_username
1 user_while1_1
2 user_while1_2
3 user_while1_3
4 user_while1_4
5 user_while1_5
6 user_while1_6
7 user_while1_7
8 user_while1_8
9 user_while1_9
示例2
create procedure up_test_while2()
define i int;
let i = 1;
while i < 10
insert into t_user values(i, concat('user_while2_', to_char(i)));
let i = i + 1;
end while;
end procedure;
> call up_test_while2();
Routine executed.
> select * from t_user;
f_userid f_username
1 user_while2_1
2 user_while2_2
3 user_while2_3
4 user_while2_4
5 user_while2_5
6 user_while2_6
7 user_while2_7
8 user_while2_8
9 user_while2_9
—2.5 异常处理
语法
on exception [in (...)] set sql_err_num[,isam_err_num]
spl_code;
end exception [with resume];
示例
create table if not exists t_error_log(f_logid serial, f_sql_error_num int, f_isam_error_num int); create procedure up_test_exception1() define sql_err_num int; define isam_err_num int; on exception set sql_err_num, isam_err_num insert into t_error_log(f_sql_error_num, f_isam_error_num) values(sql_err_num, isam_err_num); end exception; insert into t_user100 values(1,'gbasedbt'); end procedure;
> create table t_user100(f_userid int,f_username varchar(20));
Table created.
> select * from t_error_log;
f_logid f_sql_error_num f_isam_error_num
> drop table t_user100;
Table dropped.
> call up_test_exception1();
Routine executed.
> select * from t_error_log;
f_logid f_sql_error_num f_isam_error_num
1 -206 -111
3 演示
—3.1 生成测试数据
目标
生成两类表的数据。一张为部门表，需要生成100条记录。一张为员工表，需要生成10000条记录。
员工表需要生成模拟身份证和年龄，年龄不超过120岁。
create table t_dept(f_deptid int, f_deptname varchar(20));
create table t_employee(f_employeeid int, f_dept int, f_idcard varchar(20), f_age int, f_username varchar(20));
create procedure up_dept_make(num int)
define deptname varchar(20);
define i int;
let i = 1;
for i in (1 to num)
let deptname = concat('deptname_', to_char(i));
insert into t_dept values(i, deptname);
end for;
end procedure;
create function fn_idcard_get(seed int)
returning varchar(20) as f_idcard
define year, month, day int;
define prefix varchar(6);
define tail varchar(4);
define idcard varchar(20);
define pos int;
let pos = mod(seed, 11) + 1;
case pos
when 1 then
let prefix = '120101';
when 2 then
let prefix = '120102';
when 3 then
let prefix = '120103';
when 4 then
let prefix = '120104';
when 5 then
let prefix = '120105';
when 6 then
let prefix = '120106';
when 7 then
let prefix = '120221';
when 8 then
let prefix = '120222';
when 9 then
let prefix = '120223';
when 10 then
let prefix = '120224';
when 11 then
let prefix = '120225';
else
let prefix = '120101';
end case;
let year = mod(seed, 41) + 1970;
let month = mod(seed, 12) + 1;
let day = mod(seed, 28) + 1;
let tail = substr(to_char(year * month * day), 1, 4);
let idcard = concat(prefix, to_char(year));
let idcard = concat(idcard, lpad(to_char(month), 2, '0'));
let idcard = concat(idcard, lpad(to_char(day), 2, '0'));
let idcard = concat(idcard, tail);
return idcard;
end function;
create function fn_age_get(seed int)
returning int as f_age
define age int;
let age = mod(pow(seed, 3), 119) + 1;
return age;
end function;
create procedure up_employee_make(num int)
define employeename varchar(20);
define idcard varchar(20);
define deptid int;
define age int;
define employeeid int;
define i int;
for i in (1 to num)
let employeeid = i;
let employeename = concat('employee_', to_char(i));
let deptid, idcard, age = mod(i, 100) + 1, fn_idcard_get(i), fn_age_get(i);
insert into t_employee values(employeeid, deptid, idcard, age, employeename);
end for;
end procedure;
演示
call up_dept_make(100); call up_employee_make(10000);
生成结果
> select first 10 * from t_dept;
f_deptid f_deptname
1 deptname_1
2 deptname_2
3 deptname_3
4 deptname_4
5 deptname_5
6 deptname_6
7 deptname_7
8 deptname_8
9 deptname_9
10 deptname_10
10 row(s) retrieved.
> select first 10 * from t_employee;
f_employeeid 1
f_dept 2
f_idcard 120102197102027884
f_age 2
f_username employee_1
f_employeeid 2
f_dept 3
f_idcard 120103197203031774
f_age 9
f_username employee_2
f_employeeid 3
f_dept 4
f_idcard 120104197304043156
f_age 28
f_username employee_3
f_employeeid 4
f_dept 5
f_idcard 120105197405054935
f_age 65
f_username employee_4
f_employeeid 5
f_dept 6
f_idcard 120106197506067110
f_age 7
f_username employee_5
f_employeeid 6
f_dept 7
f_idcard 120221197607079682
f_age 98
f_username employee_6
f_employeeid 7
f_dept 8
f_idcard 120222197708081265
f_age 106
f_username employee_7
f_employeeid 8
f_dept 9
f_idcard 120223197809091602
f_age 37
f_username employee_8
f_employeeid 9
f_dept 10
f_idcard 120224197910101979
f_age 16
f_username employee_9
f_employeeid 10
f_dept 11
f_idcard 120225198011112395
f_age 49
f_username employee_10
10 row(s) retrieved.
>
—3.2 数据清洗
由于生成程序失误，导致身份证和年龄不匹配，需要修正数据。
关于年龄修正，可以用最简单高效的UPDATE语句完成，以下示例为了演示，采用逐行处理的方式进行数据修正。逐行处理的方式，可以应用在复杂的数据清洗场景。
create function fn_get_age_from_idcard(idcard varchar(20))
returning int as f_age
define age int;
define len int;
define dt varchar(20);
define dtnow, dtstart date;
define y1, y2 int;
let len = length(idcard);
if len <> 18 then
let age = -1;
return age;
end if;
let dt = substr(idcard, 7, 8);
let dtstart = to_date(dt, 'yyyymmdd');
let dtnow = today;
let y1 = year(dtstart);
let y2 = year(dtnow);
let age = y2 - y1 + 1;
return age;
end function;
create procedure up_age_update()
define age int;
define employeeid int;
define idcard varchar(20);
foreach select f_employeeid, f_idcard into employeeid, idcard from t_employee
let age = fn_get_age_from_idcard(idcard);
update t_employee set f_age = age where f_employeeid = employeeid;
end foreach;
end procedure;
演示
call up_age_update();
结果
> select first 10 * from t_employee; f_employeeid 1 f_dept 2 f_idcard 120102197102027884 f_age 51 f_username employee_1 f_employeeid 2 f_dept 3 f_idcard 120103197203031774 f_age 50 f_username employee_2 f_employeeid 3 f_dept 4 f_idcard 120104197304043156 f_age 49 f_username employee_3 f_employeeid 4 f_dept 5 f_idcard 120105197405054935 f_age 48 f_username employee_4 f_employeeid 5 f_dept 6 f_idcard 120106197506067110 f_age 47 f_username employee_5 f_employeeid 6 f_dept 7 f_idcard 120221197607079682 f_age 46 f_username employee_6 f_employeeid 7 f_dept 8 f_idcard 120222197708081265 f_age 45 f_username employee_7 f_employeeid 8 f_dept 9 f_idcard 120223197809091602 f_age 44 f_username employee_8 f_employeeid 9 f_dept 10 f_idcard 120224197910101979 f_age 43 f_username employee_9 f_employeeid 10 f_dept 11 f_idcard 120225198011112395 f_age 42 f_username employee_10 10 row(s) retrieved. >
4 附录
create table t_user(f_userid int, f_username varchar(20));
create table t_user2(f_userid int, f_username varchar(20));
create table if not exists t_error_log(f_logid serial, f_sql_error_num int, f_isam_error_num int);
create table t_dept(f_deptid int, f_deptname varchar(20));
create table t_employee(f_employeeid int, f_dept int, f_idcard varchar(20), f_age int, f_username varchar(20));