国产数据库---Oracle迁移至GBase8a---第五章---存储过程

异构数据引擎的迁移难点在于存储过程的迁移，本篇材料整理了Oracle存储过程向GBase 8a 的迁移的一些说明，请大家参考。

1. 1. 创建语法

下表介绍了 GBase 8a 和Oracle 存储过程的创建语法。

数据库	语法
GBase 8a	CREATE PROCEDURE `<databaseName>`.`<procedureName>` (  [ IN | OUT | INOUT ] [param_name] [type] ) BEGIN   /* 语句 */ END
Oracle

下面给出一个创建存储过程的示例：

数据库	示例
GBase 8a	CREATE PROCEDURE temp_proc ( p_id int ) BEGIN declare User_id NUMBER; select name from user where id = p_id; END;
Oracle	CREATE PROCEDURE temp_proc (p_id NUMBER) AS User_id NUMBER; BEGIN select name from user where id = p_id;  END; /

1. 1. 参数的默认值

GBase 8a ,参数没有默认值,且调用时不可以省略参数,如果参数无值可是输入 null ， Oracle 可以为参数设默认值,且可以省略有默认值的参数

示例：

数据库	示例
GBase 8a	没有此功能
Oracle	CREATE PROCEDURE temp_proc ( p_id number  := 1)

1. 1. 定义临时变量

GBase 8a：

DECLARE语句用来定义各种程序的局部项：局部变量, 条件和处理器以及游标，DECLARE只能被用在BEGIN ... END复合语句之间，且必须位于其它语句之前的开始处，游标必须在声明处理器和变量之前被声明，并且条件必须在声明游标或处理器前声明，命名可以任意(不能使用GBase 8a关键字),每个DECLARE必须以;来结束。

Oracle：

直接定义变量。

例：

GBase 8a： DECLARE p_name varchar(32) defualt 'abc';

Oracle： User_id varchar(32) :='abc';

1. 1. if 语句

语法格式不同：

GBase 8a： IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list] ...

[ELSE statement_list]

END IF

Oracle：IF search_condition THEN statement_list

[ELSIF search_condition THEN statement_list] ...

[ELSE statement_list]

END IF

示例：  

数据库	示例
GBase 8a	if( flag = 0) then set name = 'aa'; elseif( flag = 1 ) then set name = 'bb'; else set name = 'cc'; end if;
Oracle	IF flag = 0 THEN name := 'aa'; ELSIF flag = 1 then name := 'bb'; ELSE name := ‘cc’ END IF;

1. 1. while 语句

所有循环应该放在一起写

语法：

数据库	语法
GBase 8a	[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]
Oracle	<<label_name>> WHILE condition LOOP    sequence_of_statements END LOOP;

解释：

只要search_condition为真，WHILE语句中的一个或多个语句就被重复执行。 WHILE可以加标号。只有当begin_label 出现时才能有end_label ，并且如果两个都存在，必须相同。

对于Oracle使用 EXIT ，GBase 8a使用LEAVE语句，

语法： LEAVE label

这条语句用来退出任何有标号的流控制构造。它可以用在 BEGIN ... END 或循环中。

对于跳过 （CONTINUE） ，GBase 8a 使用 ITERATE 语句，Oracle 可以使用 goto 也可以用 if  结构加以限制。

语法： ITERATE label

ITERATE只能出现在LOOP, REPEAT, 和WHILE语句中。ITERATE 意味着“再一次循环。”

示例：

数据库	示例
GBase 8a	while_label:while( i<10 ) do i++; if( i%2 >0 ) then ITERATE while_label; end if; set temp_str = concat( temp_str, ',' ,i ); if( i = 8 ) then    LEAVE while_label; end if; end while while_label;
Oracle	<<loop_statr>> WHILE i <= 10 LOOP i:=i+1; if mod(i,2) >0  then goto loop_statr; end if; temp_str := temp_str || ',' || to_char(i) ; if( i = 8 ) then exit; end if; END LOOP;

1. 1. 游标

GBase 8a： 存储过程和函数中支持游标。语法就像是嵌入式SQL。游标是未知的，只读的和不能滚动的。未知意味着服务器可能对它的结果表做一个拷贝，也可能不做。

游标必须在声明处理器之前被声明，变量和条件必须在声明游标或处理器之前被声明。

示例：

Oracle

GBase 8a

CREATE OR REPLACE PROCEDURE hr.testProc IS my_sal hr.employees.salary%TYPE; my_job hr.employees.job_id%TYPE; factor INTEGER := 2; CURSOR c1 IS SELECT factor*salary FROM hr.employees WHERE job_id = my_job; BEGIN my_job := 'IT_PROG'; OPEN c1;  --打开游标 LOOP FETCH c1 INTO my_sal; EXIT WHEN c1%NOTFOUND; factor := factor + 1;  -- does not affect FETCH END LOOP; DBMS_OUTPUT.PUT_LINE(factor); END; /

CREATE PROCEDURE testProc() BEGIN DECLARE my_sal DOUBLE; DECLARE my_job varchar(10); DECLARE factor int DEFAULT 2; DECLARE done int DEFAULT 0; DECLARE c1 CURSOR FOR SELECT factor*salary FROM employees WHERE job_id = my_job; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; set my_job = 'IT_PROG'; OPEN c1;  -- 打开游标 ll:LOOP FETCH c1 INTO my_sal; if( done = 1 )then LEAVE ll; --退出循环 end if; set factor = factor + 1;  -- does not affect FETCH END LOOP ll; select factor; END |

需要注意的是GBase 8a中判断游标执行到最后，是使用定义一个异常处理来判断的。DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;当游标指针指导最后一个未知，就会触发一个异常，通过捕获这个异常，将变量 done 值变为 1 。

1. 1. 管道表迁移

这个需要根据不同的场景进行合理的改造。或借助临时表，或得到结果后直接返回字符串，或者借助其他方法。

关于管道表的迁移，没有肯定的改造方法。

1. 1. for循环

一般的for循环，可以借助while或repeat迁移。但如果for使用的是类似shell脚本中for，循环条件为类似枚举类型或结果集，那么就需要根据需要是否变成一个游标代替，或寻找其他方案。

1. 1. 异常捕捉

GBase 8a支持对存储过程中的异常捕捉，形式如下：

Declare {exit|continue } handler for {sqlexception | not found | sqlstate ‘02000’}

sqlstate后面为GBase 8a的错误代码，也就是说可以针对性的捕捉任一异常。