Updating table while open cursor

Rated 4.62/5 based on 515 customer reviews

In 40 years, there's been some amazing functionality in the database.

You can get information about any session cursor from its attributes (which you can reference in procedural statements, but not in SQL statements).Of course with My SQL not supporting dynamic cursors I found an alternative approach using temporary tables.delimiter $$ CREATE PROCEDURE `my_proc` ( IN _where TEXT ) BEGIN DECLARE done BOOL DEFAULT FALSE; DECLARE _order_id VARCHAR(32); DECLARE _total_items, _total_shipped, _total_received INT DEFAULT 0; DECLARE _order_date DATE; DECLARE cursor_1 CURSOR FOR SELECT order_id FROM _tmp_table_orders; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; SET @v = CONCAT(' CREATE TEMPORARY TABLE _tmp_table_orders ( INDEX( order_id(12) ), ) ENGINE=MEMORY SELECT t1.order_id, t1.customer_id, t2.customer_name, ( t3* t3) AS total_sell, 0 AS total_items, 0 AS total_shipped, 0 AS total_received FROM orders t1 JOIN customers t2 ON t2.customer_id = t1.customer_id JOIN items t3 ON t3.order_id = t1.order_id '); IF !If you close and immediately reopen an explicit cursor, PL/SQL does not reparse the associated query. To save an attribute value for later use, assign it to a local variable immediately.Otherwise, other operations, such as subprogram invocations, might change the value of the attribute before you can test it.

Leave a Reply