GBase 8s 学习笔记 009 —— GBase 8s SPL入门

概述

• 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 up_test_define1()
define m, n int;
define global i int default 1;
define global loginname varchar(20) default 'admin';
end procedure;
> > > >
Routine created.

> 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;
> > > > > > > > >
Routine created.

>

变量赋值

语法

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;
> > > > > > >
Routine created.

>

示例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;
> > >
Routine created.

> > > > > > > > >
Routine created.

>

示例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;
> > > > > > > > >
Routine created.

>

条件

语法

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;
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Routine created.

>

语法

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;
> > > > > > > > > > > > > > > > > > >
Routine created.

>

循环

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;
> > > > > > > > > > > > > > > >
Routine created.

>

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;
> > > > > > > > > > > > > >
Routine created.

>

示例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;
> > > > > > > > > > > >
Routine created.

>

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;
> > > > > >
Routine created.

>

示例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;
> > > > > >
Routine created.

>

示例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;
> > > > > > > > > >
Routine created.

>

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;

> > > > > > > >
Routine created.

> >

示例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;
> > > > > > > >
Routine created.

>

异常处理

语法

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;

Table created.

> > > > > > > > > > > >
Routine created.

>

演示

生成测试数据

目标

• 生成两类表的数据。一张为部门表，需要生成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 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;


Table created.

> >
Table created.

> > > > > > > > > > > >
Routine created.

> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Routine created.

> > > > > > > > > > >
Routine created.

> > > > > > > > > > > > > > > > > > >
Routine created.

> >

演示

> call up_dept_make(100);
call up_employee_make(10000);

Routine executed.

>
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.

>

数据清洗

由于生成程序失误，导致身份证和年龄不匹配，需要修正数据。

关于年龄修正，可以用最简单高效的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;
> > > > > > > > > > > > > > > > > > > > > > > > > >
Routine created.

> > > > > > > > > > > > >
Routine created.

>

演示

> call up_age_update();

Routine executed.

>

结果

> select first 10 * from t_employee;



f_employeeid  1
f_dept        2
f_idcard      120102197102027884
f_age         53
f_username    employee_1

f_employeeid  2
f_dept        3
f_idcard      120103197203031774
f_age         52
f_username    employee_2

f_employeeid  3
f_dept        4
f_idcard      120104197304043156
f_age         51
f_username    employee_3

f_employeeid  4
f_dept        5
f_idcard      120105197405054935
f_age         50
f_username    employee_4

f_employeeid  5
f_dept        6
f_idcard      120106197506067110
f_age         49
f_username    employee_5

f_employeeid  6
f_dept        7
f_idcard      120221197607079682
f_age         48
f_username    employee_6

f_employeeid  7
f_dept        8
f_idcard      120222197708081265
f_age         47
f_username    employee_7

f_employeeid  8
f_dept        9
f_idcard      120223197809091602
f_age         46
f_username    employee_8

f_employeeid  9
f_dept        10
f_idcard      120224197910101979
f_age         45
f_username    employee_9

f_employeeid  10
f_dept        11
f_idcard      120225198011112395
f_age         44
f_username    employee_10

10 row(s) retrieved.

>