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();

结果