DROP DATABASE IF EXISTS university;
CREATE DATABASE IF NOT EXISTS university;
USE university;
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(45) NOT NULL,
d_id INT
);
INSERT INTO users (name, d_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3),
('Eve', 2);
CREATE TABLE IF NOT EXISTS hobbies (
id INT AUTO_INCREMENT PRIMARY KEY,
hobby VARCHAR(45) NOT NULL,
u_id INT
);
INSERT INTO hobbies (hobby, u_id) VALUES
('Reading', 1),
('Swimming', 1),
('Cycling', 2),
('Painting', 3),
('Gaming', 4),
('Cooking', 2),
('Hiking', 5);
DELIMITER //
CREATE PROCEDURE showHobbies (OUT printstr VARCHAR(500))
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE cur_id INT;
DECLARE cur_name VARCHAR(45);
DECLARE outstr VARCHAR(500) DEFAULT '\n';
DECLARE curusers CURSOR FOR SELECT id, name FROM users;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
OPEN curusers;
USERSLOOP: LOOP
FETCH curusers INTO cur_id, cur_name;
IF done THEN
LEAVE USERSLOOP;
END IF;
SET @hobbies_list = ''; -- Сбрасываем список хобби для каждого пользователя
SELECT GROUP_CONCAT(hobby SEPARATOR ', ')
INTO @hobbies_list
FROM hobbies
WHERE u_id = cur_id;
IF @hobbies_list IS NULL THEN
SET @hobbies_list = 'No hobbies';
END IF;
SET outstr = CONCAT(outstr, 'The hobby list for ', cur_name, ' is: ', @hobbies_list, '\n');
END LOOP USERSLOOP;
CLOSE curusers;
SET printstr := outstr;
END//
DELIMITER ;
CALL showHobbies(@list);
SELECT @list;
Telegram: DB developers channel