---

DELIMITER //

CREATE FUNCTION Nazv_Pr(sh INT)
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE res VARCHAR(100);

    SELECT project INTO res
    FROM PROJECT
    WHERE shifr = sh;

    RETURN res;
END //

DELIMITER ;

DELIMITER //

CREATE FUNCTION KolRabot(teamName VARCHAR(50))
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE res INT;

    SELECT COUNT(*) INTO res
    FROM WORK w
    JOIN TEAM t ON w.team = t.id
    WHERE t.team = teamName;

    RETURN res;
END //

DELIMITER ;

DELIMITER //

CREATE FUNCTION Pr_mes(m INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE res INT;

    SELECT COUNT(*) INTO res
    FROM PROJECT
    WHERE MONTH(dateStart) = m;

    RETURN res;
END //

DELIMITER ;

DELIMITER //

CREATE FUNCTION Nazv_MaxPr(teamName VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE res VARCHAR(100);

    SELECT p.project INTO res
    FROM PROJECT p
    JOIN WORK w ON p.shifr = w.project
    JOIN TEAM t ON w.team = t.id
    WHERE t.team = teamName
    ORDER BY p.cost DESC
    LIMIT 1;

    RETURN res;
END //

DELIMITER ;

DELIMITER //

CREATE FUNCTION Dni_pr(sh INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE d DATE;

    SELECT dateStart INTO d
    FROM PROJECT
    WHERE shifr = sh;

    RETURN Kol_Dney(d, CURDATE());
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE PrF()
BEGIN
    SHOW FUNCTION STATUS WHERE Db = DATABASE();
END //

DELIMITER ;

CALL PrF();

DELIMITER //

CREATE FUNCTION ProjectsRange(a INT, b INT)
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE res TEXT DEFAULT '';
    DECLARE pr INT;

    DECLARE cur CURSOR FOR
        SELECT shifr FROM PROJECT
        WHERE cost BETWEEN a AND b;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO pr;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET res = CONCAT(res, pr, ', ');
    END LOOP;

    CLOSE cur;

    RETURN res;
END //

DELIMITER ;