SQL Tutorial - Cursors
1. Cursors
DELIMITER //
CREATE PROCEDURE cursorExample (
)
BEGIN
DECLARE done INTEGER DEFAULT 0;
DECLARE theName varchar(100) DEFAULT "";
-- declare cursor
DEClARE curName
CURSOR FOR
SELECT username FROM app_user;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET done = 1;
OPEN curName;
myLoop: LOOP
FETCH curName INTO theName;
IF finished = 1 THEN
LEAVE myLoop;
END IF;
-- provide output
select theName;
END LOOP myLoop;
-- Clean up when we are done
CLOSE curName;
END//
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE cursorExample (
)
BEGIN
DECLARE done INTEGER DEFAULT 0;
DECLARE theName varchar(100) DEFAULT "";
DECLARE output varchar(1000) DEFAULT "";
-- declare cursor
DECLARE myCursor
CURSOR FOR
SELECT username FROM app_user;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET done = 1;
OPEN myCursor;
myLoop: LOOP
FETCH myCursor INTO theName;
IF done = 1 THEN
LEAVE myLoop;
END IF;
-- provide output
set theName = concat(theName,", ");
set output = concat(output,theName);
END LOOP myLoop;
-- Clean up when we are done
CLOSE myCursor;
select output;
END//
DELIMITER ;
DELIMITER //
CREATE OR REPLACE PROCEDURE cursorExample (
)
BEGIN
DECLARE done INTEGER DEFAULT 0;
DECLARE theName varchar(100) DEFAULT "";
DECLARE theLogin varchar(100) DEFAULT "";
DECLARE output varchar(1000) DEFAULT "";
-- declare cursor
DECLARE myCursor
CURSOR FOR
SELECT userName,userLogin FROM app_user;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET done = 1;
OPEN myCursor;
myLoop: LOOP
FETCH myCursor INTO theName,theLogin;
IF done = 1 THEN
LEAVE myLoop;
END IF;
-- provide output
set theLogin = concat(" [",theName,"]");
set theName = concat(theName,theLogin," | ");
set output = concat(output,theName);
END LOOP myLoop;
-- Clean up when we are done
CLOSE myCursor;
select output;
END//
DELIMITER ;
On this page of the site you can watch the video online SQL Tutorial - Cursors with a duration of hours minute second in good quality, which was uploaded by the user Develpreneur 02 June 2022, share the link with friends and acquaintances, this video has already been watched 21 times on youtube and it was liked by 0 viewers. Enjoy your viewing!
