tee 2026_3_18_sql_log.txt

-- A very simple stored procedure.
-- Redefine the delimiter to end the procedure.
-- default delimiter: ;
DELIMITER $$

CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), OUT numFaculty INT)
-- SQL parameter passing mode: IN (pass by value), OUT, INOUT
BEGIN
   --  Display some information.
   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('CINF', @numFaculty);
SELECT 'CINF', @numFaculty;

CALL deptInfo(CONCAT('CS', 'CI'), @numFaculty);
SELECT CONCAT('CS', 'CI'), @numFaculty;

CALL deptInfo(@dCode, 3);

DROP PROCEDURE deptInfo;


-- A simple function
DELIMITER //

CREATE OR REPLACE FUNCTION n_major(dCode varchar(4)) RETURNS INT
-- no mode: by default: IN (OUT and INOUT not allowed)
-- have a return value (INT)
READS SQL DATA  -- pragma
BEGIN
   DECLARE count INT DEFAULT 0;

   SELECT COUNT(*) INTO count -- must use INTO option
   FROM student
   WHERE major = dCode;

   RETURN count;
END //

DELIMITER ;

SHOW CREATE FUNCTION n_major;

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


-- A simple function
DELIMITER //

CREATE OR REPLACE FUNCTION n_major(dCode varchar(4)) RETURNS INT
-- no mode: by default: IN (OUT and INOUT not allowed)
-- have a return value (INT)
READS SQL DATA  -- pragma
BEGIN
   DECLARE count INT DEFAULT 0;

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

   RETURN count;
END //

DELIMITER ;

-- System Catalog
-- Getting selected columns from information_schema
SELECT table_name, table_type, row_format, table_rows, avg_row_length
FROM information_schema.tables
WHERE table_schema = 'information_schema'
ORDER BY table_name DESC;