Updating multiple rows without cursor
The two that relate to cursors are: NOT FOUND is shorthand for a class of SQLSTATE values that begin with '02'.It fires when a cursor reaches the end of a data set.
This type of cursor is faster than the previous types because no copying is done.Oracle is transaction oriented; that is, Oracle uses transactions to ensure data integrity.A transaction is a series of SQL data manipulation statements that does a logical unit of work.While we won't be looking at the use of multiple cursors within the same stored proc, it is entirely possible to have multiple cursors in the same code block, but each cursor must have a unique name. HANDLER statement specifies a handler that deals with one or more conditions.If one of these conditions occurs, the specified statement executes.As with all database tools, you must carefully consider how cursors meet the needs of your users and determine whether using them within a procedure or function will provide the best solution to the problem you are attempting to solve.
By extending SQL, PL/SQL offers a unique combination of power and ease of use.
Cursor declarations must appear after variable and condition declarations but before handler declarations.
Also keep in mind that the cursor SELECT statement cannot have an INTO clause; it's strictly read-only.
One is to loop through a recordset and update a single row in a table based on a single row lookup in another table.
DELIMITER // DROP PROCEDURE IF EXISTS sp_set_name // CREATE PROCEDURE sp_set_name () BEGIN DECLARE done INT DEFAULT 0; DECLARE client_id INT; DECLARE cur1 CURSOR FOR SELECT id from clients WHERE name IS NULL; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; read_loop: LOOP IF done THEN LEAVE read_loop; END IF; FETCH cur1 INTO client_id; UPDATE clients SET name = (SELECT name from orders WHERE id = client_id) WHERE id = client_id; END LOOP; close cur1; END; // DELIMITER ; DELIMITER // DROP PROCEDURE IF EXISTS build_phone_list // CREATE PROCEDURE build_phone_list (INOUT phone_list varchar(4000)) BEGIN DECLARE finished INTEGER DEFAULT 0; DECLARE phone varchar(15) DEFAULT ""; DECl ARE phone_cursor CURSOR FOR SELECT phone FROM contacts; DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1; OPEN phone_cursor; phone_loop: LOOP FETCH phone_cursor INTO phone; IF finished = 1 THEN LEAVE phone_loop; END IF; SET phone_list = CONCAT(phone,",",phone_list); END LOOP phone_loop; CLOSE phone_cursor; END; // DELIMITER ; You can test the build_phone_list stored procedure using the following code: SET @phone_list = ""; CALL build_phone_list(@phone_list); SELECT @phone_list; Changes made to the database after the cursor is opened are visible in the result table.
In addition to static SQL discussed in this chapter, PL/SQL also supports dynamic SQL, which enables you to execute SQL data definition, data control, and session control statements dynamically.