MariaDB [toyu]> use toyu
Database changed
MariaDB [toyu]> 
MariaDB [toyu]> -- Redefine the delimiter to end the procedure.
MariaDB [toyu]> -- default delimiter: ;
MariaDB [toyu]> DELIMITER //
MariaDB [toyu]> 
MariaDB [toyu]> -- IN: pass by value; 
MariaDB [toyu]> --  OUT: copy out when exiting.
MariaDB [toyu]> CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), OUT numFaculty INT)
    -> BEGIN   -- dcode: `CSCI'; numFaculty: Null
    ->    --  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.017 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.001 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.001 sec)

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

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

+---------------------------------+
| faculty                         |
+---------------------------------+
| Faculty in the department: CSCI |
+---------------------------------+
1 row in set (0.258 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.359 sec)

Query OK, 1 row affected (0.572 sec)

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

MariaDB [toyu]> 
MariaDB [toyu]> SET @dCode = 'ITEC';
Query OK, 0 rows affected (0.001 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.070 sec)

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

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

Query OK, 1 row affected (0.434 sec)

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

MariaDB [toyu]> 
MariaDB [toyu]> DROP PROCEDURE deptInfo;
Query OK, 0 rows affected (0.016 sec)

MariaDB [toyu]> 
MariaDB [toyu]> -- Redefine the delimiter to end the procedure.
MariaDB [toyu]> -- default delimiter: ;
MariaDB [toyu]> DELIMITER //
MariaDB [toyu]> 
MariaDB [toyu]> -- IN: pass by value; 
MariaDB [toyu]> --  OUT: copy out when exiting.
MariaDB [toyu]> CREATE OR REPLACE PROCEDURE deptInfo(IN dCode VARCHAR(4), OUT numFaculty INT)
    -> BEGIN   -- dcode: `CSCI'; numFaculty: Null
    ->    --  Display some information.
    ->    SELECT d.deptName, d.SchoolCode, t1.n_majors, t2.n_minors
    ->    FROM toyu.department AS d INNER JOIN
    ->       (SELECT COUNT(stuId) AS n_majors
    ->       FROM toyu.student
    ->       WHERE major = dCode) AS t1 INNER JOIN
    ->       (SELECT COUNT(stuId) AS n_minors
    ->       FROM toyu.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 toyu.faculty AS f
    ->    WHERE f.deptCode = dCode;
    -> END //
Query OK, 0 rows affected (0.016 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 toyu.department AS d INNER JOIN
      (SELECT COUNT(stuId) AS n_majors
      FROM toyu.student
      WHERE major = dCode) AS t1 INNER JOIN
      (SELECT COUNT(stuId) AS n_minors
      FROM toyu.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 toyu.faculty AS f
   WHERE f.deptCode = dCode;
END | cp850                | cp850_general_ci     | utf8mb4_general_ci |
+-----------+-----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.001 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.001 sec)

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

+---------------------------------+
| faculty                         |
+---------------------------------+
| Faculty in the department: CSCI |
+---------------------------------+
1 row in set (0.305 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.417 sec)

Query OK, 1 row affected (0.607 sec)

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

MariaDB [toyu]> 
MariaDB [toyu]> SET @dCode = 'ITEC';
Query OK, 0 rows affected (0.001 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.111 sec)

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

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

Query OK, 1 row affected (0.490 sec)

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

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

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

+---------------------------------+
| faculty                         |
+---------------------------------+
| Faculty in the department: CSCI |
+---------------------------------+
1 row in set (0.209 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.310 sec)

Query OK, 1 row affected (0.525 sec)

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

MariaDB [toyu]> CALL deptInfo('CSCI', @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.164 sec)

+---------------------------------+
| faculty                         |
+---------------------------------+
| Faculty in the department: CSCI |
+---------------------------------+
1 row in set (0.205 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.284 sec)

Query OK, 1 row affected (0.505 sec)

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

MariaDB [toyu]> CALL deptInfo('CSCI', 4);
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]> SELECT 'CSCI', @numFaculty;
+------+-------------+
| CSCI | @numFaculty |
+------+-------------+
| CSCI |           4 |
+------+-------------+
1 row in set (0.000 sec)

MariaDB [toyu]> DROP PROCEDURE deptInfo;
Query OK, 0 rows affected (0.012 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(*) INTO count
    ->    FROM student
    ->    WHERE major = dCode;
    -> 
    ->    RETURN count;
    -> END //
Query OK, 0 rows affected (0.013 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.001 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]> 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]> -- mode: IN only; OUT and IN OUT not acceptable in a function
MariaDB [toyu]> CREATE OR REPLACE FUNCTION n_major(dCode varchar(4)) RETURNS INT
    -> READS SQL DATA
    -> BEGIN
    ->    DECLARE count INT DEFAULT 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 4
MariaDB [toyu]> 
MariaDB [toyu]>    SELECT COUNT(*)
    ->    FROM toyu.student
    ->    WHERE major = dCode;
ERROR 1054 (42S22): Unknown column 'dCode' in 'where clause'
MariaDB [toyu]> 
MariaDB [toyu]>    RETURN count;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RETURN count' at line 1
MariaDB [toyu]> END //
    -> 
    -> DELIMITER ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END //

DELIMITER' at line 1
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 toyu.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]> CREATE OR REPLACE FUNCTION n_major(dCode varchar(4)) RETURNS INT
    -> READS SQL DATA
    -> BEGIN
    ->    DECLARE count INT DEFAULT 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 4
MariaDB [toyu]> 
MariaDB [toyu]>    SELECT COUNT(*) INTO count
    ->    FROM toyu.student
    ->    WHERE major = dCode;
ERROR 1327 (42000): Undeclared variable: count
MariaDB [toyu]> 
MariaDB [toyu]>    RETURN count;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RETURN count' at line 1
MariaDB [toyu]> END //
    -> 
    -> DELIMITER ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END //

DELIMITER' at line 1
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.001 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]> -- A simple function
MariaDB [toyu]> DELIMITER //
MariaDB [toyu]> 
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 toyu.student
    ->    WHERE major = dCode;
    -> 
    ->    RETURN count;
    -> END //
Query OK, 0 rows affected (0.007 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 toyu.student
   WHERE major = dCode;

   RETURN count;
END | cp850                | cp850_general_ci     | utf8mb4_general_ci |
+----------+-----------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.001 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]> SELECT *
    -> FROM student;
+--------+-----------+----------+-------+-------+------+---------+
| stuId  | fname     | lname    | major | minor | ach  | advisor |
+--------+-----------+----------+-------+-------+------+---------+
| 100000 | Tony      | Hawk     | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk     | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk     | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim      | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson  | ITEC  | NULL  |   66 |    1017 |
| 100005 | Linda     | Johnson  | CINF  | ENGL  |   13 |    1015 |
| 100006 | Lillian   | Johnson  | CINF  | ITEC  |   18 |    1016 |
| 100007 | Ben       | Zico     | NULL  | NULL  |   16 |    NULL |
| 100008 | Bill      | Ching    | ARTS  | NULL  |   90 |    NULL |
| 100009 | Linda     | King     | ARTS  | CSCI  |  125 |    1018 |
| 100111 | Cathy     | Johanson | NULL  | NULL  |    0 |    1018 |
+--------+-----------+----------+-------+-------+------+---------+
11 rows in set (0.001 sec)

MariaDB [toyu]> SELECT *
    -> FROM student
    -> LIMIT 5;
+--------+-----------+---------+-------+-------+------+---------+
| stuId  | fname     | lname   | major | minor | ach  | advisor |
+--------+-----------+---------+-------+-------+------+---------+
| 100000 | Tony      | Hawk    | CSCI  | CINF  |   40 |    1011 |
| 100001 | Mary      | Hawk    | CSCI  | CINF  |   35 |    1011 |
| 100002 | David     | Hawk    | CSCI  | ITEC  |   66 |    1012 |
| 100003 | Catherine | Lim     | ITEC  | CINF  |   20 |    NULL |
| 100004 | Larry     | Johnson | ITEC  | NULL  |   66 |    1017 |
+--------+-----------+---------+-------+-------+------+---------+
5 rows in set (0.001 sec)

MariaDB [toyu]> 
MariaDB [toyu]> 
MariaDB [toyu]> -- using cursor.
MariaDB [toyu]> DELIMITER //
MariaDB [toyu]> 
MariaDB [toyu]> 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 -- resultset
    -> 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 //
Query OK, 0 rows affected (0.014 sec)

MariaDB [toyu]> 
MariaDB [toyu]> DELIMITER ;
MariaDB [toyu]> 
MariaDB [toyu]> SELECT major_students('CSCI');
+----------------------------------+
| major_students('CSCI')           |
+----------------------------------+
| Tony Hawk, Mary Hawk, David Hawk |
+----------------------------------+
1 row in set (0.003 sec)

MariaDB [toyu]> SELECT major_students('CINF');
+--------------------------------+
| major_students('CINF')         |
+--------------------------------+
| Linda Johnson, Lillian Johnson |
+--------------------------------+
1 row in set (0.001 sec)

MariaDB [toyu]> 
MariaDB [toyu]> DROP FUNCTION major_students;
Query OK, 0 rows affected (0.018 sec)

MariaDB [toyu]> 
MariaDB [toyu]> notee