GDCA学习实践--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;

变量赋值

let var = value;
let var1, var2,... = val1, val2, ...;
let var1, var2, ... = function(args1, args2, ...);
let var1, var2, ... = (select col1, col2, ... from table_name);

条件

• case … when

case expr 
when val_1 then 
    code_1 
[when val_2 then 
    code_2 ...] 
else 
    code_n 
end case;

• if … then

if condition_1 then 
    code_1
elif condition_2 then 
    code_2
elif condition_3 then 
    code_3
...
else 
    code_n
end if;

循环

• GOTO

<<lbl_goto>>
    spl_code;
if expr then
    goto lbl_goto;
end if;

• LOOP

loop
    if expr then
        exit;
    end if;
end loop;

loop
    exit when expr;
end loop;

• 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;

• WHILE

while expr loop
    spl_code;
end loop;

while expr
    spl_code;
end while;

异常处理

on exception [in (...)] set sql_err_num[,isam_err_num]
    spl_code;
end exception [with resume];