![]() ![]() Hotels have rooms and rooms have capacities. The cursor is made active with OPEN, finished with CLOSE and read from using FETCH INTO x, y.įor a working example I’m going to write a contrived procedure that updates days of availability for a hotel. It sets the done variable to true and then the done variable is used to break out of the loop. The CONTINUE HANDLER is fired when the cursor comes to the end of the result set. ![]() First is the order of how everything is declared. A basic CURSOR would look something like this: BEGIN DECLARE done INT DEFAULT FALSE DECLARE p_id INT DECLARE p_name CHAR(24) DECLARE cursorForProfile CURSOR FOR SELECT id, name FROM Profile DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE OPEN cursorForProfile read_loop: LOOP FETCH cursorForProfile INTO p_id, p_name IF done THEN LEAVE read_loop END IF #do something with p_id and p_name END LOOP CLOSE cursorForProfile END They are read only and are traversable in one direction. They also need some handlers to deal with the end of the cursor. They follow a similar format to SELECT INTO but need to be declared in advance, opened, fetched and closed. Cursors are a little bit fiddly and take a bit of care and patience. In order to iterate over a result a CURSOR can be used. Although I prefer to use the EXISTS to check for the presence of a row and the SELECT INTO when I know something already exists and fail if it doesn’t exist. ![]() You might want to declare a continue handler for SELECT INTO when nothing is found. SELECT id, name FROM Profile WHERE id = 10 INTO v_id, v_name The select will need to return one row and the variables being selected “in to” need to be already declared. The SELECT INTO allows for columns to be selected from a table in to variables. IF NOT EXISTS(SELECT * FROM People) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'OMG PANIC' END IF Use it to check if data is present in other tables. The EXISTS subqueries simply check if a query returns any rows and evaluates to true if it does. For this article I’ll go over EXISTS, SELECT INTO and CURSORS. Previously I’ve covered basic usage and using loops. This time around I’m going to cover reading information from tables and manipulating it. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |