More SQL

by K. Yue

1. Views

Views are virtual table derived from other tables.
Some advantages of using views:
1. Better data abstraction
2. Logical (logical) data independence
3. Better consistency
4. Possible centralized security control
5. Can be more efficient
Some disadvantages:
- More work.
- Complicated especially when views are updated.
MySQL Create View Maunal: search for "mysql view manual"

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

There are some limitations on the select clause in the create view statement.
For example, in earlier versions of MySQL, subqueries are not allowed in the select clause of the create view statement.
You may need to define intermediate views accordingly.

Example:

CREATE OR REPLACE VIEW school_summary(
    schoolCode, schoolName, n_departments) AS
SELECT s.schoolCode, s.schoolName,
    COUNT(d.deptCode) AS n_departments
FROM school s LEFT JOIN department d
    ON (s.schoolCode = d.schoolCode)
GROUP BY s.schoolCode, s.schoolName;

SELECT *
FROM school_summary
WHERE n_departments > 0;

Notes:

"create or replace" can replace existing view definition.
There are no data types for the columns.
The column list is optional.

2. Stored Procedures

Routines stored by the DBMS can be called.
Routines can be
1. functions: return a value, or
2. procedures
Known as SQL/PSM (SQL/Persistent Stored Modules) in MySQL.
- Can execute SQL statements
- Include general programming constructs
Some advantages of stored subroutines:
1. Enforcing application constraints and requirements.
2. Providing consistency and security control.
3. Possible performance optimization by both the developers and the DBMS.
4. Sharing among DB applications.
Some disadvantages of stored procedures:
1. Use up DB server's resources.
2. Potentially inefficient because of limitations in language constructs.
3. Potentially harder to develop because of relative lack of libraries and difficulty in debugging.
4. Can be vendor specific.
In general, stored procedures should be used more often.
MySQL stored procedures:
- http://dev.mysql.com/doc/refman/5.6/en/stored-routines.html
- http://dev.mysql.com/doc/refman/5.6/en/faqs-stored-procs.html#qandaitem-B-4-1-2

Example:

-- A very simple stored procedure.
DROP PROCEDURE dept_info;

-- Redefine the delimiter to end the procedure.
DELIMITER //

CREATE PROCEDURE dept_info(IN dept VARCHAR(4))
BEGIN
    SELECT d.deptName, d.SchoolCode, t1.n_majors, t2.n_minors
    FROM department d INNER JOIN
        (SELECT COUNT(stuId) AS n_majors
         FROM student
         WHERE major = dept) AS t1 INNER JOIN
        (SELECT COUNT(stuId) AS n_minors
         FROM student
         WHERE minor = dept) AS t2
    WHERE d.deptCode = dept;

    -- MySQL does not directly support output to console.
    SELECT 'Faculty in the department';

    SELECT *
    FROM faculty
    WHERE deptCode = dept;

end //

DELIMITER ;

CALL dept_info('CSCI');

Note:

Use of the DELIMITER command to redefine '//' as the delimiter indicating the end of the stored procedure. Otherwise, the default ';' is the delimiter indicating the end of the stored procedure.
No parameters for this procedure.
A procedure does not return any value and accomplish its goal through side effects.
The result is displayed when called.

Example:

DROP FUNCTION n_major;

DELIMITER //

CREATE FUNCTION n_major(dept varchar(4)) RETURNS INT
READS SQL DATA
BEGIN
    DECLARE count INT DEFAULT 0;

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

RETURN count;
END //

DELIMITER ;

SELECT n_major('CSCI');

Note:

A function returns a value and does not accomplish its goal through side effect.
Note the argument passed into the function.
Formal arguments of a function use the IN parameter passing mode.
Note the variable declaration and assignment.
The scope of the variable is within the function.
'READS SQL DATA' describes the characteristic of the subroutine.
On the other hand, a session variable (starts with a @) has the session as its scope. Use them very carefully.

Stored Program Syntax

Syntax for MySQL's stored programs can be found in compound statement section: https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-compound-statements.html.
Various high level language constructs are supported, such as variable declaration, conditional statements, control statements, etc.
In particular, cursor is supported for allow iteration through relations: https://dev.mysql.com/doc/refman/5.7/en/cursoriness.

Example:

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');

Note that the example is only used to demonstrate cursors in a stored function. A much better solution is to simply use GROUP_CONCAT, such as:

SELECT GROUP_CONCAT(CONCAT(fName, ' ', lName) SEPARATOR ', ') AS majors
FROM student
WHERE major = 'CSCI';

3. Triggers

Triggers allow event-driven programming.
A trigger is activated when certain events occur. Unlike stored routines, trigger is not called directly.
Events:
- Insert a row
- Update a row
- Delete a row
Actions can be executed:

Before the event
After the event

Information can be obtained through:
- old:
  - the old value of the row before the event.
  - for update and delete events
- new:
  - the new value of the row after the event.
  - for update and insert events
Advantages of triggers:
- event driven model suit certain kind of tasks better.
- implementation of consistency check.
- implementation of business logic and integrity check.
Disadvantages:
- Relatively invisible and possibly overlook by developers.
- Relatively difficult to debug.
- Potential performance issues.
- Potential complicated interactions.

Example:

An example of using trigger is for auditing. Please see: http://www.databasejournal.com/features/mysql/the-wonderful-and-not-so-wonderful-things-about-mysql-triggers.html.