SPL例程由开始语句，语句块，结束语句组成。
SPL例程根据是否需要返回值，分为存储过程和函数。
语句块由SQL和SPL语句组成。
语法
create procedure proc_name(param1 data_type1, param2 data_type2, ...)
spl_code;
end procedure;
语法
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;
语法
define var data_type;
define global var data_type default def_value;
示例
create procedure pro_define1()
define m, n int;
define global i int default 1;
define global loginname varchar(20) default 'admin';
end procedure;
create procedure pro_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;
语法
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 pro_let1()
define userid int;
define username varchar(20);
let userid, username = 99, 'sinodb';
insert into t_user values(userid, username);
end procedure;
示例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 pro_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;
示例3
create procedure pro_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;
语法
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;
语法
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_condition(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;
语法
<<lbl_goto>>
spl_code;
if expr then
goto lbl_goto;
end if;
示例
create procedure pro_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;
语法
loop
if expr then
exit;
end if;
end loop;
loop
exit when expr;
end loop;
示例1
create procedure Pro_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;
示例2
create procedure pro_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;
语法
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 pro_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;
示例2
create procedure pro_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;
示例3
create procedure pro_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;
语法
while expr loop
spl_code;
end loop;
while expr
spl_code;
end while;
示例1
create procedure pro_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;
示例2
create procedure pro_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;
语法
on exception [in (...)] set sql_err_num[,isam_err_num]
spl_code;
end exception [with resume];
示例1
create table if not exists t_error_log(f_logid serial, f_sql_error_num int, f_isam_error_num int);
create procedure Pro_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,'sinodb');
end procedure;
目标
生成两类表的数据。一张为部门表，需要生成100条记录。一张为员工表，需要生成10000条记录
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 pro_dept_add(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 pro_employee_add(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 pro_dept_add(100);
call pro_employee_add(10000);
生成结果
> call pro_dept_add(100);
Routine executed.
> call pro_employee_add(10000);
Routine executed.
>
> 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.
>
由于生成程序失误，导致身份证和年龄不匹配，需要修正数据。
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, '%iY%m%d');
let dtnow = today;
let y1 = year(dtstart);
let y2 = year(dtnow);
let age = y2 - y1 + 1;
return age;
end function;
create procedure pro_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 pro_age_update();
结果
Routine executed.
> select first 10 * from t_employee;
f_employeeid 1
f_dept 2
f_idcard 120102197102027884
f_age 52
f_username employee_1
f_employeeid 2
f_dept 3
f_idcard 120103197203031774
f_age 51
f_username employee_2
f_employeeid 3
f_dept 4
f_idcard 120104197304043156
f_age 50
f_username employee_3
f_employeeid 4
f_dept 5
f_idcard 120105197405054935
f_age 49
f_username employee_4
f_employeeid 5
f_dept 6
f_idcard 120106197506067110
f_age 48
f_username employee_5
f_employeeid 6
f_dept 7
f_idcard 120221197607079682
f_age 47
f_username employee_6
f_employeeid 7
f_dept 8
f_idcard 120222197708081265
f_age 46
f_username employee_7
f_employeeid 8
f_dept 9
f_idcard 120223197809091602
f_age 45
f_username employee_8
f_employeeid 9
f_dept 10
f_idcard 120224197910101979
f_age 44
f_username employee_9
f_employeeid 10
f_dept 11
f_idcard 120225198011112395
f_age 43
f_username employee_10
10 row(s) retrieved.
>
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));
FOR/FOREACH