GBase 8a MPP Cluster 产品手册 5 数据库管理指南 文档版本953(2022-09-15) 南大通用数据技术股份有限公司 1349 参数名称 描 述 var_name 变量名 type 变量数据类型,值为GBase 8a MPP Cluster 支持的数据类型 DEFAULT value 设置变量的默认值。 这个值可以指定为一个表达式,或一 个常量。如果DEFAULT 缺少子句,初始值为NULL。 示例 示例1:DECLARE intX INT gbase> DELIMITER // gbase> DROP PROCEDURE IF EXISTS dodeclare // Query OK, 0 rows affected gbase> CREATE PROCEDURE dodeclare (p1 INT) BEGIN DECLARE intX INT; SET intX = 0; REPEAT SET intX = intX + 1; UNTIL intX > p1 END REPEAT; SELECT intX; END // Query OK, 0 rows affected gbase> DELIMITER ; gbase> CALL dodeclare(1000); +------+ | intX | +------+ | 1001 | +------+ 1 row in set Query OK, 0 rows affected 示例2:DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 gbase> DELIMITER // gbase> DROP PROCEDURE IF EXISTS curdemo // Query OK, 0 rows affected gbase> CREATE PROCEDURE curdemo() BEGIN DECLARE done INT DEFAULT 0; DECLARE cnt INT DEFAULT 0; DECLARE s_region CHAR(255); DECLARE stmp CHAR(255) DEFAULT '';
GBase 8a MPP Cluster 产品手册 5 数据库管理指南 文档版本953(2022-09-15) 南大通用数据技术股份有限公司 1350 DECLARE cur_region CURSOR FOR SELECT DISTINCT c_region FROM ssbm.customer ORDER BY c_region LIMIT 1000; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; DROP TABLE IF EXISTS products; CREATE TABLE products(region CHAR(255),count INT); OPEN cur_region; REPEAT FETCH cur_region INTO s_region; IF NOT done THEN IF stmp='' THEN SET stmp=s_region; SET cnt=1; END IF; IF stmp!=s_region THEN INSERT INTO products(region,count) VALUES(stmp,cnt); SET cnt=1; SET stmp=s_region; END IF; SET cnt=cnt+1; END IF; UNTIL done END REPEAT; CLOSE cur_region; INSERT INTO products(region,count) VALUES(stmp,cnt); END // Query OK, 0 rows affected gbase> DELIMITER ; gbase> CALL curdemo; Query OK, 1 row affected gbase> SELECT region,count FROM products; +-----------------------------------------------------------+-------+ |region |count| +-----------------------------------------------------------+-------+ |AFRICA | 2 | |AMERICA | 2 | |ASIA | 2 | |EUROPE | 2 | |MIDDLEEAST | 2 | +------------------------------------------------------------+-------+ 5 rows in set