Apache Hive : HPL/SQL - DECLARE CURSOR Statement

You can use DECLARE CURSOR statement to declare a cursor using a dynamic SQL.

Syntax:

DECLARE name CURSOR FOR | AS | IS dynamic_sql_string | select_statement;

Parameters:

Notes:

• dynamic_sql_string expression is evaluated at cursor open time, not declare time.

Example 1:

Using a dynamic SQL string:

DECLARE tabname VARCHAR DEFAULT 'db.orders';
DECLARE id INT;
DECLARE cur CURSOR FOR 'SELECT id FROM ' || tabname;
OPEN cur;
FETCH cur INTO id;
WHILE SQLCODE=0 THEN
  PRINT id;
  FETCH cur INTO id;
END WHILE;
CLOSE cur;

Example 2:

Using a SQL SELECT statement:

DECLARE id INT;
DECLARE cur CURSOR FOR SELECT id FROM db.orders;
OPEN cur;
FETCH cur INTO id;
WHILE SQLCODE=0 THEN
  PRINT id;
  FETCH cur INTO id;
END WHILE;
CLOSE cur;

Compatibility: IBM DB2, MySQL, Teradata.

See also:

• OPEN
• FETCH
• CLOSE
• SQLCODE