MySQL Forums
Forum List  »  Oracle

Re: Cursor and Prepared statement
Posted by: Carol Nickel
Date: September 10, 2008 05:27PM

We've found a couple of possible work-arounds for the dynamic cursor problem, but both have limitations.

The first is for cases where absolutely only one user at a time will be running the procedure. A prepare statement can be used to create a view with the dynamic SQL and the cursor can select from this statically-named view. There's almost no performance impact. Unfortunately, these views are also visible to other users (there's no such thing as a temporary view), so this won't work for multiple users.

Analogously, a temporary table can be created in the prepare statement and the cursor can select from the temporary table. Only the current session can see a temporary table, so the multiple user issue is resolved. But this solution can have significant performance impact since a temp table has to be created each time the proc runs.

Bottom line: We still need cursors to be able to be created dynamically!

Here's an example of using a view to pass the table name and column name into a cursor.
-------------

DELIMITER //
DROP PROCEDURE IF EXISTS test_prepare//

CREATE PROCEDURE test_prepare(IN tablename varchar(255), columnname varchar(50))
BEGIN
DECLARE cursor_end CONDITION FOR SQLSTATE '02000';
DECLARE v_column_val VARCHAR(50);
DECLARE done INT DEFAULT 0;
DECLARE cur_table CURSOR FOR SELECT * FROM test_prepare_vw;
DECLARE CONTINUE HANDLER FOR cursor_end SET done = 1;

SET @query = CONCAT('CREATE VIEW test_prepare_vw as select ', columnname, ' from ', tablename);
select @query;
PREPARE stmt from @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

OPEN cur_table;
FETCH cur_table INTO v_column_val;
WHILE done = 0 DO
SELECT v_column_val;
FETCH cur_table INTO v_column_val;
END WHILE;
CLOSE cur_table;

DROP VIEW test_prepare_vw;

END;
//

DELIMITER ;

Options: ReplyQuote


Subject
Views
Written By
Posted
25694
September 19, 2006 02:32AM
10466
June 02, 2008 03:56AM
11673
July 21, 2008 06:21PM
Re: Cursor and Prepared statement
24448
September 10, 2008 05:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.