示例1:查询语句是静态SQL 语句。 gbase> DELIMITER // gbase> DROP PROCEDURE IF EXISTS docursor // Query OK, 0 rows affected gbase> CREATE PROCEDURE docursor() BEGIN DECLARE s_region VARCHAR(40); DECLARE DONE INT DEFAULT(0); DECLARE cur REF CURSOR; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur FOR SELECT DISTINCT c_region FROM ssbm.customer ORDER BY c_region LIMIT 6; REPEAT FETCH cur INTO s_region; IF NOT done THEN SELECT s_region; END IF; UNTIL DONE END REPEAT; CLOSE cur; END //
GBase 8a MPP Cluster 产品手册 5 数据库管理指南 文档版本953(2022-09-15) 南大通用数据技术股份有限公司 1372 Query OK, 0 rows affected gbase> DELIMITER ; gbase> CALL docursor(); +----------+ | s_region | +----------+ | AFRICA | +----------+ 1 row in set +----------+ | s_region | +----------+ | AMERICA | +----------+ 1 row in set +----------+ | s_region | +----------+ | ASIA | +----------+ 1 row in set +----------+ | s_region | +----------+ | EUROPE | +----------+ 1 row in set +-------------+ | s_region | +-------------+ | MIDDLE EAST | +-------------+ 1 row in set Query OK, 0 rows affected 示例2:OPEN 语句中的SELECT 语句包含由文本字符串或者内容为文本字符串 的用户变量。
GBase 8a MPP Cluster 产品手册 5 数据库管理指南 文档版本953(2022-09-15) 南大通用数据技术股份有限公司 1373 示例中所用的表及数据: DROP TABLE t1; CREATE TABLE t1 (i INT, j INT); INSERT INTO t1 VALUES(1, 1); INSERT INTO t1 VALUES (1, 1); INSERT INTO t1 VALUES (2, 2); INSERT INTO t1 VALUES (3, 3); INSERT INTO t1 VALUES (4, 4); SELECT * FROM t1; 创建存储过程: gbase> DELIMITER // gbase> CREATE PROCEDURE hunter.test_1() BEGIN DECLARE v VARCHAR(200); DECLARE i INT DEFAULT (0); DECLARE j INT DEFAULT (0); DECLARE cur REF CURSOR; SET v = 'SELECT * FROM hunter.t1'; SET @sql_str = v; OPEN cur FOR @sql_str; FETCH cur INTO i, j; SELECT i, j; CLOSE cur; END // Query OK, 0 rows affected 执行结果: gbase> DELIMITER ; gbase> CALL hunter.test_1(); +------+------+ | i | j | +------+------+ | 1 | 1 | +------+------+ 1 row in set Query OK, 0 rows affected 示例3:动态游标中的预处理语句包含动态SQL 语句。 gbase> DELIMITER // gbase> CREATE PROCEDURE hunter.test_1() BEGIN DECLARE v VARCHAR(200);
GBase 8a MPP Cluster 产品手册 5 数据库管理指南 文档版本953(2022-09-15) 南大通用数据技术股份有限公司 1374 SET v = 'SELECT * FROM hunter.t1 WHERE i = ? AND j = ?'; SET @sql_str = v; SET @a = 1; SET @b = 2; PREPARE stmt FROM @sql_str; EXECUTE stmt USING @a, @b; END // Query OK, 0 rows affected gbase> DELIMITER ; gbase> CALL hunter.test_1(); Empty set Query OK, 0 rows affected