-- 10/21
tee 2024_10_21_sql_log.txt
use toyu


-- A very simple stored procedure.
-- Redefine the delimiter to end the procedure.
DELIMITER //

-- IN: pass by value; OUT: copy out hen exiting
CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), OUT numFaculty INT)
BEGIN
   --  Display some information.
   -- e.g. dCode: ‘CSCI’; numFaculty: null
   SELECT d.deptName, d.SchoolCode, t1.n_majors, t2.n_minors
   FROM department AS d INNER JOIN
      (SELECT COUNT(stuId) AS n_majors
      FROM student
      WHERE major = dCode) AS t1 INNER JOIN
      (SELECT COUNT(stuId) AS n_minors
      FROM student
      WHERE minor = dCode) AS t2
   WHERE d.deptCode = dCode;

   -- MySQL does not direct sending output to console.
   -- It is necessary to use a SQL statement.
   SELECT 'Debuggin comment can be put here.';
   SELECT CONCAT('Faculty in the department: ', dCode) AS faculty;

   SELECT *
   FROM faculty AS f
   WHERE f.deptCode = dCode;

   SELECT COUNT(f.facId) INTO numFaculty
   FROM faculty AS f
   WHERE f.deptCode = dCode;
END //

DELIMITER ;

SHOW CREATE PROCEDURE deptInfo;

SET @numFaculty = 0;
SET @dCode = 'CSCI';
CALL deptInfo(@dCode, @numFaculty);
SELECT @dCode, @numFaculty;

SET @dCode = 'ITEC';
CALL deptInfo(@dCode, @numFaculty);
SELECT @dCode, @numFaculty;


CALL deptInfo('CSCI', @numFaculty);
SELECT 'CSCI', @numFaculty;


CALL deptInfo('CSCI', 3);
ERROR 1414 (42000): OUT or INOUT argument 2 for routine toyu.deptInfo is not a variable or NEW pseudo-variable in BEFORE trigger
SELECT 'CSCI', @numFaculty;


DELIMITER //

-- IN: pass by value; OUT: copy out hen exiting
CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), INOUT numFaculty INT)
BEGIN
    SELECT dCode, numFaculty;
   --  Display some information.
   -- e.g. dCode: ‘CSCI’; numFaculty: null
   SELECT d.deptName, d.SchoolCode, t1.n_majors, t2.n_minors
   FROM department AS d INNER JOIN
      (SELECT COUNT(stuId) AS n_majors
      FROM student
      WHERE major = dCode) AS t1 INNER JOIN
      (SELECT COUNT(stuId) AS n_minors
      FROM student
      WHERE minor = dCode) AS t2
   WHERE d.deptCode = dCode;

   -- MySQL does not direct sending output to console.
   -- It is necessary to use a SQL statement.
   SELECT 'Debuggin comment can be put here.';
   SELECT CONCAT('Faculty in the department: ', dCode) AS faculty;

   SELECT *
   FROM faculty AS f
   WHERE f.deptCode = dCode;

   SELECT COUNT(f.facId) INTO numFaculty
   FROM faculty AS f
   WHERE f.deptCode = dCode;
END //

DELIMITER ;

SET @dCode = 'ITEC';
SET @numFaculty = 1;
CALL deptInfo(@dCode, @numFaculty);

SET @dCode = 'CSCI';
CALL deptInfo(@dCode, @numFaculty);
SELECT @dCode, @numFaculty;



-- pragma


-- A simple function
DELIMITER //

-- function: no OUT or INOUT. Only IN
CREATE OR REPLACE FUNCTION n_major(dCode varchar(4)) RETURNS INT
READS SQL DATA
BEGIN
   DECLARE count INT DEFAULT 0;

   SELECT COUNT(*) INTO count
   FROM student
   WHERE major = dCode;

   RETURN count;
END //

DELIMITER ;

SHOW CREATE FUNCTION n_major;

SELECT n_major('CSCI');
SELECT n_major('ITEC');





DROP PROCEDURE deptInfo;



-- A simple function
DELIMITER //

-- function: no OUT or INOUT. Only IN
CREATE OR REPLACE FUNCTION n_major(dCode varchar(4)) RETURNS INT
READS SQL DATA
BEGIN
   DECLARE count INT DEFAULT 0;

   SELECT COUNT(*) -- side effect.
   FROM student
   WHERE major = dCode;

   RETURN count;
END //

ERROR 1415 (0A000): Not allowed to return a result set from a function

DELIMITER ;

SHOW CREATE FUNCTION n_major;

SELECT n_major('CSCI');



-- using cursor.
DELIMITER //

CREATE FUNCTION major_students(dept VARCHAR(4))
RETURNS VARCHAR(1000)
READS SQL DATA
BEGIN
DECLARE result VARCHAR(1000) DEFAULT '';
DECLARE name VARCHAR(41) DEFAULT '';
DECLARE done INT DEFAULT FALSE;

DECLARE cursor_1 CURSOR FOR
SELECT DISTINCT CONCAT(fName, ' ', lName) AS name
FROM student
WHERE major = dept;

DECLARE continue handler FOR NOT FOUND SET done = TRUE;

OPEN cursor_1;
SET result = ''; 

compute_loop: LOOP
-- This is needed as there are other SQL statements that may set done to true.
SET done = false;
FETCH cursor_1 INTO name;
IF done THEN
LEAVE compute_loop;
END IF;

IF (result <> '') THEN
SET result = CONCAT(result, ', ');
END IF;

SET result = CONCAT(result, name);
END LOOP;

CLOSE cursor_1;

RETURN result;
END //

DELIMITER ;

SELECT major_students('CSCI');
SELECT major_students('CINF');


SELECT n_major('ITEC');