MariaDB [toyu]> -- A very simple stored procedure.
MariaDB [toyu]> -- Redefine the delimiter to end the procedure.
MariaDB [toyu]> DELIMITER //
MariaDB [toyu]> 
MariaDB [toyu]> CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), OUT numFaculty INT)
    -> 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 //
Query OK, 0 rows affected (0.011 sec)

MariaDB [toyu]> 
MariaDB [toyu]> DELIMITER ;
MariaDB [toyu]> 
MariaDB [toyu]> SHOW CREATE PROCEDURE deptInfo;
+-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                            | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| deptInfo  | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`yue`@`localhost` PROCEDURE `deptInfo`(IN dCode VARCHAR(4), OUT numFaculty INT)
BEGIN
   
   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;

   
   
   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 | cp850                | cp850_general_ci     | utf8mb4_general_ci |
+-----------+-----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.002 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SET @numFaculty = 0;
Query OK, 0 rows affected (0.001 sec)

MariaDB [toyu]> SET @dCode = 'CSCI';
Query OK, 0 rows affected (0.000 sec)

MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty);
+------------------+------------+----------+----------+
| deptName         | SchoolCode | n_majors | n_minors |
+------------------+------------+----------+----------+
| Computer Science | CSE        |        3 |        1 |
+------------------+------------+----------+----------+
1 row in set (0.007 sec)

+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
1 row in set (0.009 sec)

+---------------------------------+
| faculty                         |
+---------------------------------+
| Faculty in the department: CSCI |
+---------------------------------+
1 row in set (0.010 sec)

+-------+--------+--------+----------+---------------------+
| facId | fname  | lname  | deptCode | rank                |
+-------+--------+--------+----------+---------------------+
|  1011 | Paul   | Smith  | CSCI     | Professor           |
|  1012 | Mary   | Tran   | CSCI     | Associate Professor |
|  1013 | David  | Love   | CSCI     | NULL                |
|  1014 | Sharon | Mannes | CSCI     | Assistant Professor |
+-------+--------+--------+----------+---------------------+
4 rows in set (0.012 sec)

Query OK, 1 row affected (0.018 sec)

MariaDB [toyu]> SELECT @dCode, @numFaculty;
+--------+-------------+
| @dCode | @numFaculty |
+--------+-------------+
| CSCI   |           4 |
+--------+-------------+
1 row in set (0.000 sec)

MariaDB [toyu]> 
MariaDB [toyu]> 
MariaDB [toyu]> SET @dCode = 'ITEC';
Query OK, 0 rows affected (0.000 sec)

MariaDB [toyu]> CALL deptInfo(@dCode, @numFaculty);
+------------------------+------------+----------+----------+
| deptName               | SchoolCode | n_majors | n_minors |
+------------------------+------------+----------+----------+
| Information Technology | CSE        |        2 |        2 |
+------------------------+------------+----------+----------+
1 row in set (0.001 sec)

+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
1 row in set (0.002 sec)

+---------------------------------+
| faculty                         |
+---------------------------------+
| Faculty in the department: ITEC |
+---------------------------------+
1 row in set (0.004 sec)

+-------+----------+-------+----------+-----------+
| facId | fname    | lname | deptCode | rank      |
+-------+----------+-------+----------+-----------+
|  1017 | Deborah  | Gump  | ITEC     | Professor |
|  1019 | Benjamin | Yu    | ITEC     | Lecturer  |
+-------+----------+-------+----------+-----------+
2 rows in set (0.005 sec)

Query OK, 1 row affected (0.007 sec)

MariaDB [toyu]> SELECT @dCode, @numFaculty;
+--------+-------------+
| @dCode | @numFaculty |
+--------+-------------+
| ITEC   |           2 |
+--------+-------------+
1 row in set (0.001 sec)

MariaDB [toyu]> CALL deptInfo('CINF', @numFaculty);
+------------------------------+------------+----------+----------+
| deptName                     | SchoolCode | n_majors | n_minors |
+------------------------------+------------+----------+----------+
| Computer Information Systems | CSE        |        2 |        3 |
+------------------------------+------------+----------+----------+
1 row in set (0.001 sec)

+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
1 row in set (0.004 sec)

+---------------------------------+
| faculty                         |
+---------------------------------+
| Faculty in the department: CINF |
+---------------------------------+
1 row in set (0.006 sec)

+-------+--------+-------+----------+---------------------+
| facId | fname  | lname | deptCode | rank                |
+-------+--------+-------+----------+---------------------+
|  1015 | Daniel | Kim   | CINF     | Professor           |
|  1016 | Andrew | Byre  | CINF     | Associate Professor |
+-------+--------+-------+----------+---------------------+
2 rows in set (0.007 sec)

Query OK, 1 row affected (0.011 sec)

MariaDB [toyu]> SELECT @dCode, @numFaculty;
+--------+-------------+
| @dCode | @numFaculty |
+--------+-------------+
| ITEC   |           2 |
+--------+-------------+
1 row in set (0.000 sec)

MariaDB [toyu]> SELECT 'CINF', @numFaculty;
+------+-------------+
| CINF | @numFaculty |
+------+-------------+
| CINF |           2 |
+------+-------------+
1 row in set (0.001 sec)

MariaDB [toyu]> CALL deptInfo(CONCAT('CS', 'CI'), @numFaculty);
+------------------+------------+----------+----------+
| deptName         | SchoolCode | n_majors | n_minors |
+------------------+------------+----------+----------+
| Computer Science | CSE        |        3 |        1 |
+------------------+------------+----------+----------+
1 row in set (0.001 sec)

+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
| Debuggin comment can be put here. |
+-----------------------------------+
1 row in set (0.004 sec)

+---------------------------------+
| faculty                         |
+---------------------------------+
| Faculty in the department: CSCI |
+---------------------------------+
1 row in set (0.006 sec)

+-------+--------+--------+----------+---------------------+
| facId | fname  | lname  | deptCode | rank                |
+-------+--------+--------+----------+---------------------+
|  1011 | Paul   | Smith  | CSCI     | Professor           |
|  1012 | Mary   | Tran   | CSCI     | Associate Professor |
|  1013 | David  | Love   | CSCI     | NULL                |
|  1014 | Sharon | Mannes | CSCI     | Assistant Professor |
+-------+--------+--------+----------+---------------------+
4 rows in set (0.007 sec)

Query OK, 1 row affected (0.012 sec)

MariaDB [toyu]> CALL deptInfo('CINF', 3);
ERROR 1414 (42000): OUT or INOUT argument 2 for routine toyu.deptInfo is not a variable or NEW pseudo-variable in BEFORE trigger
MariaDB [toyu]> -- A simple function
MariaDB [toyu]> DELIMITER //
MariaDB [toyu]> 
MariaDB [toyu]> 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 //
Query OK, 0 rows affected (0.011 sec)

MariaDB [toyu]> 
MariaDB [toyu]> DELIMITER ;
MariaDB [toyu]> 
MariaDB [toyu]> SHOW CREATE FUNCTION n_major;
+----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Function | sql_mode                                            | Create Function                                                                                                                                                                                                                             | character_set_client | collation_connection | Database Collation |
+----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| n_major  | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`yue`@`localhost` FUNCTION `n_major`(dCode varchar(4)) RETURNS int(11)
    READS SQL DATA
BEGIN
   DECLARE count INT DEFAULT 0;

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

   RETURN count;
END | cp850                | cp850_general_ci     | utf8mb4_general_ci |
+----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.000 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT n_major('CSCI');
+-----------------+
| n_major('CSCI') |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.001 sec)

MariaDB [toyu]> SELECT n_major('ITEC');
+-----------------+
| n_major('ITEC') |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.000 sec)

MariaDB [toyu]> 
MariaDB [toyu]> 
MariaDB [toyu]> -- A simple function
MariaDB [toyu]> DELIMITER //
MariaDB [toyu]> 
MariaDB [toyu]> CREATE OR REPLACE FUNCTION n_major(dCode varchar(4)) RETURNS INT
    -> READS SQL DATA
    -> BEGIN
    ->    DECLARE count INT DEFAULT 0;
    -> 
    ->    SELECT COUNT(*) 
    ->    FROM student
    ->    WHERE major = dCode;
    -> 
    ->    RETURN count;
    -> END //
ERROR 1415 (0A000): Not allowed to return a result set from a function
MariaDB [toyu]> 
MariaDB [toyu]> DELIMITER ;
MariaDB [toyu]> 
MariaDB [toyu]> SHOW CREATE FUNCTION n_major;
+----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Function | sql_mode                                            | Create Function                                                                                                                                                                                                                             | character_set_client | collation_connection | Database Collation |
+----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| n_major  | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`yue`@`localhost` FUNCTION `n_major`(dCode varchar(4)) RETURNS int(11)
    READS SQL DATA
BEGIN
   DECLARE count INT DEFAULT 0;

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

   RETURN count;
END | cp850                | cp850_general_ci     | utf8mb4_general_ci |
+----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.000 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT n_major('CSCI');
+-----------------+
| n_major('CSCI') |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.001 sec)

MariaDB [toyu]> SELECT n_major('ITEC');
+-----------------+
| n_major('ITEC') |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.001 sec)

MariaDB [toyu]> 
MariaDB [toyu]> 
MariaDB [toyu]> -- A simple function
MariaDB [toyu]> DELIMITER //
MariaDB [toyu]> 
MariaDB [toyu]> CREATE OR REPLACE FUNCTION n_major(dCode varchar(4)) RETURNS INT
    -> READS SQL DATA
    -> BEGIN
    ->    DECLARE count INT DEFAULT 0;
    -> 
    ->    SELECT COUNT(*) 
    ->    FROM student
    ->    WHERE major = dCode;
    -> 
    ->    RETURN count;
    -> END //
ERROR 1415 (0A000): Not allowed to return a result set from a function
MariaDB [toyu]> 
MariaDB [toyu]> DELIMITER ;
MariaDB [toyu]> 
MariaDB [toyu]> SHOW CREATE FUNCTION n_major;
+----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Function | sql_mode                                            | Create Function                                                                                                                                                                                                                             | character_set_client | collation_connection | Database Collation |
+----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| n_major  | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`yue`@`localhost` FUNCTION `n_major`(dCode varchar(4)) RETURNS int(11)
    READS SQL DATA
BEGIN
   DECLARE count INT DEFAULT 0;

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

   RETURN count;
END | cp850                | cp850_general_ci     | utf8mb4_general_ci |
+----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.000 sec)

MariaDB [toyu]> 
MariaDB [toyu]> SELECT n_major('CSCI');
+-----------------+
| n_major('CSCI') |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.000 sec)

MariaDB [toyu]> SELECT n_major('ITEC');
+-----------------+
| n_major('ITEC') |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.000 sec)

MariaDB [toyu]> 
MariaDB [toyu]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
| swim               |
| test               |
| tinker             |
| toyu               |
+--------------------+
8 rows in set (0.001 sec)

MariaDB [toyu]> notee