4tyXNiSF

1DROP DATABASE IF EXISTS test;
2CREATE DATABASE test;
3USE test;
4
5CREATE TABLE users(
6id INT AUTO_INCREMENT PRIMARY KEY,
7username VARCHAR(255) NOT NULL
8);
9
10INSERT INTO users VALUES(1, 'miro'),
11						(2, 'valyo'),
12                        (3, 'marinela');
13                
14                
15DROP PROCEDURE IF EXISTS showAllUsers;
16DELIMITER $$
17CREATE PROCEDURE showAllUsers()
18BEGIN
19	DECLARE finished INT DEFAULT 0;
20    DECLARE v_username VARCHAR(255);
21    DECLARE userCursor CURSOR FOR SELECT username FROM users;
22    
23    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished =1;
24    
25    CREATE TEMPORARY TABLE tempUsers(
26		tempUsername VARCHAR(255) NOT NULL
27	);
28    
29    OPEN userCursor;
30    
31    loop_users : LOOP
32    
33		FETCH userCursor INTO v_username;
34    
35		IF finished = 1
36		THEN LEAVE loop_users;
37		END IF;
38    
39		INSERT INTO tempUsers VALUES(v_username);
40    
41    END LOOP;
42    
43    SELECT * FROM tempUsers;
44    CLOSE userCursor;
45	DROP TEMPORARY TABLE tempUsers;
46END $$
47DELIMITER ;
48
49CALL showAllUsers();