db2 stored procedure with create select and cursor

Question

I'm trying to define a DB2 Stored Proc that would (ideally), CREATE VIEW, then do a SELECT against that VIEW to build another piece of SQL, then execute that SQL using a CURSOR and return a result set. I've 2 problems:

DB2 doesn't appear to like the mix of CREATE, SELECT and DECLARE CURSOR within a single SP,
and I can't figure out what syntax to use to declare a cursor based on SQL that is stored as a string in the declared VARCHAR that is the output from the SELECT statement.

Has anyone done anything similar and/or able to give me some syntax examples?

AngocA AngocA · Accepted Answer · 2015-05-25T16:56:39

Sure you can do that in DB2. In order to execute a 'create view' you need to use a dynamic SQL. The same for the select on the view. Finally, for the cursor, you define a generic cursor, and execute it dynamically.

DECLARE SENTENCE VARCHAR(256);
DECLARE TABNAME VARCHAR(128);
DECLARE STMT STATEMENT;
DECLARE TABLES_CURSOR CURSOR 
  FOR TABLES_RS; 

SET SENTENCE = 'CREATE VIEW TABS SELECT TABNAME FROM SYSCAT.TABLES';
PREPARE STMT FROM SENTENCE; 
EXECUTE STMT; 

SET SENTENCE = 'SELECT TABNAME FROM TABS';
PREPARE TABLES_RS FROM SENTENCE;
OPEN TABLES_CURSOR;
FETCH TABLES_CURSOR INTO TABNAME;

You can see real SQL PL examples in my project db2unit https://github.com/angoca/db2unit/blob/master/src/main/sql-pl/04-Body.sql

db2 stored procedure with create select and cursor

1 Answers