Monday, 24 December 2018

Mysql: Stored Objects and Programs

4.  Stored Objects and Programs

A stored database object is a set of SQL statements that are stored in the server for subsequent invocation. MySQL stored objects include:
  1. User-Defined Functions & Stored Procedures: Collectively known as stored routines, that contain a set of SQL statements stored in the server, for subsequent invocation.
    A function takes a set of parameters, performs the programmed operations, and returns a result to the caller. In addition to the MySQL built-in functions, you can write your own functions using SQL statements. To invoke a function, use functionName(parameters). You can store the return value in a variable, e.g., SET @variableName := functionName(parameters); or SELECT functionName(parameters).
    A procedure is similar to a function, except that it does not return a value. However, you could use the parameters to pass values between the caller and the procedure by declaring the direction of the parameters as IN, OUT, or INOUT (whereas all parameters for function are restricted to IN). Use CALL statement to invoke a procedure, in the form of CALL procedureName(parameters).
  2. Triggers: A trigger is an event handler that is activated upon the occurrence of a particular event, e.g., BEFORE|AFTER INSERT, DELETE, UPDATE. A trigger is associated with a table. For example, you can use a BEFORE INSERT trigger to set the creation TIMESTAMP of the record; or BEFORE DELETE trigger to perform a backup.
  3. Events: An event is a scheduled task, which runs at the scheduled date/time, and could be recurring.
  4. Views: A view is a stored query. It is a virtual table with no real data, which is often used to limit the information available to a less-privilege user or produce additional derived columns (such as total price).
A stored object is associated with a database. You can invoke a routine not belonging to the current database by qualifying the database name, e.g. test.myFunction(), or CALL test.myProcedure(). All stored objects for a database are removed, when the database is dropped.
You can use CREATE|ALTER|DROP FUNCTION|PROCEDURE|TRIGGER|EVENT|VIEW, to create, alter, or delete the stored objects.
Before discussing the stored objects, we need to look at the MySQL syntax for programming stored objects.

4.1  Compound Statement

A compound statement comprises multiple statements, treated as a unit. A compound statement is enclosed within BEGIN ... END. Each of the statements is terminated with ';' (called statement delimiter). As the "statement delimiter" crashes with the "end-of-statement" delimiter of the mysqlclient (which signals the client to send the statement to the server for processing), we need to use DELIMITER command to temporarily change the "end-of-statement" delimiter for the mysql client. For example,
-- Change the "end-of-statement" delimiter from ';' to '//'
DELIMITER //
   
CREATE PROCEDURE procedureName (parameters)
BEGIN
   -- Declaring local variables having scope within BEGIN ... END.
   DECLARE variableName1 [, variablesName2 ...] type [DEFAULT value];
   statement1;
   ....
   statementN;
END//   -- end-of-statement for CREATE PROCEDURE

-- Restore the "end-of-statement" delimiter to default of ';'
DELIMITER ;

4.2  Local Variables

Within a compound statement enclosed by BEGIN ... END, we can declare local variables using DECLARE statement, specifying its name, type and optional default value. The scope of the local variables is within the BEGIN ... END. We can use the SET command to assign value to the local variables.
We can also use "SELECT ... INTO variableName1 [, variableName2 ...]" to assign a value to local variable(s) from a query. The "SELECT ... INTO" should return a single row. You may use "LIMIT 1" to limit the output of SELECT to a single row with proper selection criteria and ordering.

4.3  Flow Control

MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT flow control constructs for stored programs.
-- IF-THEN-ELSE
IF condition THEN trueStatements;
[ELSEIF condition THEN trueStatements;]
[ELSE elseStatements;]
END IF;
Example
 
 
 
 
-- CASE-WHEN with value
CASE caseValue
  WHEN value1 THEN value1Statements;
  WHEN value2 THEN value2Statements;
  ......
  [ELSE elseStatements;]
END CASE;
Example
 
 
 
 
 
 
-- CASE-WHEN with conditions
CASE
  WHEN condition1 THEN condition1Statements;
  WHEN condition2 THEN condition2Statements;
  .......
  [ELSE elseStatements;]
END CASE;
Example
 
  
 
 
 

-- REPEAT statements UNTIL the condition is true
REPEAT falseStatements;
  UNTIL condition
END REPEAT;
Example
 
 

-- WHILE condition is true DO statements
WHILE condition 
  DO trueStatements;
END WHILE;
Example
 
 
 
-- Repeat statements
[begin_label:] LOOP 
   statements;
END LOOP [end_label];
Example
 
 
 
-- "break" the flow control construct identified by label
label: LOOP
   ......
   LEAVE label;   -- "Break" the block identified by label
   ......
END LOOP
Example
 
 
 
 
 
-- "continue" to the flow control construct identified by label
label: LOOP
   ......
   ITERATE label;
   ......
END LOOP
Example
The following flow-control functions are available:
-- If the condition is true, return trueResult;
-- otherwise, return falseResult.
IF(condition, trueResult, falseResult)

Example
 
 
-- If value is NULL, return nullResult;
-- otherwise, return value.
IFNULL(value, nullResult)

Example
 
 
-- If value is NULL, return 1 (true);
-- otherwise return 0 (false).
ISNULL(value)

Example
 
 
-- If value1 equals to value2, return NULL;
-- otherwise, return value1.
NULLIF(value1, value2)
Example

4.4  Stored Procedures

Syntax:
CREATE PROCEDURE procedureName (parameter[, ...])
  statement

parameter:
  [IN|OUT|INOUT] parameterName parameterType
You can use parameters to pass data into and receive data from a stored procedure by declaring the direction of the parameters as IN, OUT, or INOUT. The received data are often placed in user-defined variables.
You can use a compound statement, consisting of multiple statements, as the body of the CREATE PROCEDURE. A compound statement is enclosed between BEGIN and END. As each of the statements is terminated by semi-colon, which crashes with MySQL statement terminator, we have to use DELIMITER to change the MySQL's delimiter.
Example:
-- Use this table for testing stored procedure
CREATE TABLE IF NOT EXISTS products (
   productID    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
   productCode  CHAR(3)       NOT NULL DEFAULT '',
   name         VARCHAR(30)   NOT NULL DEFAULT '',
   quantity     INT UNSIGNED  NOT NULL DEFAULT 0,
   price        DECIMAL(7,2)  NOT NULL DEFAULT 99999.99,
   PRIMARY KEY  (productID)
);
 
INSERT INTO products VALUES 
   (1001, 'PEN', 'Pen Red',    5000, 1.23),
   (1002, 'PEN', 'Pen Blue',   8000, 1.25),
   (1003, 'PEN', 'Pen Black',  2000, 1.25),
   (1004, 'PEC', 'Pencil 2B', 10000, 0.48),
   (1005, 'PEC', 'Pencil 2H',  8000, 0.49); 
SELECT * FROM `products`;

-- Define a simple procedure without parameter
-- The procedure has only one statement
CREATE PROCEDURE simpleProc()
SELECT * FROM products;

-- Use CALL to invoke the procedure
CALL simpleProc;

-- Define a procedure with a compound statement
-- The compound statement uses ; which crash with MySQL delimiter.
-- Hence, we change the MySQL delimiter temporary
DELIMITER //
CREATE PROCEDURE compoundProc()
BEGIN  -- A compound statement is enclosed within BEGIN and END
  SELECT * FROM products;
  SELECT NOW();
END//  -- End of MySQL CREATE PROCEDURE statement
DELIMITER ;  -- Restore MySQL delimiter
 
-- Invoke the procedure
CALL compoundProc;

-- Define stored procedure with parameters
DELIMITER //

-- Find the products with `quantity` less than the given `reorderLevel`,
-- and return the total cost to replenish in `cost`
-- The parameters could be declared as IN, OUT or INOUT
CREATE PROCEDURE reorderCost (IN reorderLevel INT, OUT cost DECIMAL(8,2))
BEGIN
  SELECT IFNULL(sum(price*(reorderLevel-quantity)), 0) INTO cost
  FROM products
  WHERE quantity < reorderLevel;
END//

DELIMITER ;     -- restore the default delimiter ';'
 
-- Call the stored procedure. Pass in input parameter, and receive output in a variable
CALL reorderCost(5000, @cost);
   
-- Show the return value
SELECT @cost;
+--------+
| @cost  |
+--------+
| 353.00 |
+--------+
 
-- Test IFNULL() in the procedure
CALL reorderCost(1000, @cost);

SELECT @cost;
+-------+
| @cost |
+-------+
|  0.00 |
+-------+
You can optionally use "DEFINER" and "SQL SECURITY" to control user access to the stored procedure:
CREATE
  DEFINER=CURRENT_USER|'user'@'host'
  SQL SECURITY DEFINER|INVOKER
  PROCEDURE procedureName (parameter[, ...])
    statements
If SQL SECURITY DEFINER is used, the procedure executes with the privileges of DEFINER user no matter which user invokes it. If SQL SECURITY INVOKER is used, the procedure executes with the privileges of the user who invoke it. The default DEFINER is the current user.

4.5  User-Defined Functions

Syntax:
CREATE FUNCTION functionName (parameter[, ...]) RETURNS returnType
  statements;
  RETURN value;
A function returns a scalar value, via the statement RETURN. All parameters are IN parameters.
Example:
DELIMITER //
CREATE FUNCTION sayHello (name VARCHAR(20)) RETURNS VARCHAR(30)
BEGIN
   RETURN CONCAT('Hello, ', name, '!');
END//
DELIMITER ;
To invoke the function, use functionName(parameters):
mysql> SELECT sayHello('peter');
+-------------------+
| sayHello('peter') |
+-------------------+
| Hello, peter!     |
+-------------------+
Example:
// Define FUNCTION
DROP FUNCTION IF EXISTS reorderCostFn;
DELIMITER //

-- Find the products with `quantity` less than the given `reorderLevel`,
-- and return the total cost to replenish
CREATE FUNCTION reorderCostFn (reorderLevel INT) RETURNS DECIMAL(8,2)
BEGIN
  SELECT IFNULL(sum(price*(reorderLevel-quantity)), 0) INTO @cost
  FROM products 
  WHERE quantity < reorderLevel;
 
  RETURN @cost;
END//

DELIMITER ;     -- restore the default delimiter ';'
   
-- Invoke the function
SELECT reorderCostFn(5000) AS Cost;
+---------+
| Cost    |
+---------+
| 3750.00 |
+---------+

4.6  Views

A view is a preset query stored in a database. A view could be useful for improved security by restricting the data available to less-privilege users; or producing derived column (such as total prices from unit prices).
Syntax:
CREATE VIEW viewName (columnList) AS selectStatement
Example: Our products table has 5 columns. We can create a view to restrict certain users to two columns, name and price, as follows:
-- Create a VIEW which shows only selected columns
DROP VIEW IF EXISTS products_view;

CREATE VIEW products_view (Name, Price)
AS
SELECT CONCAT(productCode, ' - ', name), price FROM products;

DESCRIBE products_view;
   
SELECT * FROM products_view;
+-----------------+-------+
| Name            | Price |
+-----------------+-------+
| PEN - Pen Red   |  1.23 |
  ......
 
-- Create another VIEW with a derived column
DROP VIEW IF EXISTS products_total_view;
 
CREATE VIEW products_total_view
AS
SELECT
   CONCAT(productCode, ' - ', name) AS Name,
   quantity AS Quantity,
   price AS Price,
   quantity*price AS Total
FROM products;
 
SELECT * FROM products_total_view;
+-----------------+----------+-------+----------+
| Name            | Quantity | Price | Total    |
+-----------------+----------+-------+----------+
| PEN - Pen Red   |     5000 |  1.23 |  6150.00 |
| PEN - Pen Blue  |     8000 |  1.25 | 10000.00 |
 ......

4.7  Triggers

A trigger is a event handler that executes in response to an event. A trigger is associated with a table. Three types of table events can activate a trigger: DELETE (include REPLACE), INSERT (include LOAD DATA and REPLACE), and UPDATE. You can set the trigger to execute BEFORE or AFTER the table event.
Syntax:
CREATE TRIGGER triggerName 
  {AFTER|BEFORE} {DELETE|INSERT|UPDATE}
ON tableName FOR EACH ROW 
  statements
You can refer to columns in the table associated with the trigger by using the aliases OLD and NEW:
  • For DELETE and UPDATE: OLD.columnName refers to a column of an existing row before it is updated or deleted.
  • For INSERT and UPDATE: NEW.colunmName refers to the column of a new row to be inserted or an existing row after it is updated. To set a column value for INSERT, you need to use a BEFORE INSERT trigger and set the via via NEW.columnName. See example below.
Example: Save the row in a backup table before the row is deleted.
-- Create the backup table for persons (See earlier example)
CREATE TABLE backup_persons (
   `personID`         INT UNSIGNED  NOT NULL,
   `firstName`        VARCHAR(20)   NOT NULL,
   `lastName`         VARCHAR(20)   NOT NULL,
   `datetimeDeleted`  DATETIME      NOT NULL,
    PRIMARY KEY(`personID`)
);
 
-- Define a trigger "before" a row is "deleted" from table persons
DELIMITER //
CREATE TRIGGER archiveEmployees
BEFORE DELETE ON persons FOR EACH ROW
BEGIN
  INSERT INTO backup_persons
  -- OLD is a system-provided alias referring to the existing columns
  --  before update or delete.
  VALUES (OLD.personID, OLD.firstName, OLD.lastName, NOW());
END//

DELIMITER ;
To test the trigger:
INSERT INTO persons VALUE (NULL, 'Peter', 'Johonson');
   
DELETE FROM persons WHERE firstName='Peter' AND lastName = 'Johonson';
   
SELECT * FROM persons WHERE firstName='Peter' AND lastName = 'Johonson';
Empty set (0.00 sec)
   
SELECT * FROM backup_persons WHERE firstName='Peter' AND lastName = 'Johonson';
+----------+-----------+----------+---------------------+
| personID | firstName | lastName | datetimeDeleted     |
+----------+-----------+----------+---------------------+
|        2 | Peter     | Johonson | 2012-10-26 18:06:09 |
+----------+-----------+----------+---------------------+
Example (Created and Last-Updated)
DROP TABLE timestamp_trigger_arena;
CREATE TABLE timestamp_trigger_arena (
   `desc`        VARCHAR(20),
   created       TIMESTAMP DEFAULT 0,
   last_updated  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
 
CREATE TRIGGER created_trigger
BEFORE INSERT ON timestamp_trigger_arena FOR EACH ROW
   SET NEW.created = NOW();
   -- NEW is an alias referring to the columns to be inserted or updated.
   -- Need to set NEW.columnName BEFORE insert.
 
INSERT INTO timestamp_trigger_arena (`desc`) VALUE ('Created');
SELECT * FROM timestamp_trigger_arena;
UPDATE timestamp_trigger_arena SET `desc`='Updated' WHERE `desc`='Created';
SELECT * FROM timestamp_trigger_arena;

4.8  Events

An event is a set of stored SQL statements that get executed at the scheduled date and time. Once an event is completed, it will be dropped automatically.
Syntax:
CREATE EVENT eventName ON SCHEDULE 
AT timestamp [+ INTERVAL intervalNumber intervalUnit]
DO 
statements
The schedule is defined in "AT timestamp" (e.g., AT now(), AT '2011-01-01 00:00:00'). You could specify a relative time with the optional "+ INTERVAL", (e.g., AT now() + 1 HOUR).
You need to enable the event scheduler, which is a special thread for maintaining the event queue and running scheduled events:
-- Enable Event Scheduler thread
mysql> SET @@global.event_scheduler = ON;
   
-- Show the current processing threads
mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
     Id: 1
   ......
   Info: SHOW PROCESSLIST
*************************** 2. row ***************************
     Id: 2
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 329
  State: Waiting on empty queue
   Info: NULL
You can also start the server mysqld with option --event-scheduler=DISABLED|ENABLED.
Example:
mysql> SELECT * FROM products WHERE productCode LIKE 'PIL%';
+-------------+-----------+-------------+-----------+--------------+
| productCode | name      | description | unitPrice | unitsInStock |
+-------------+-----------+-------------+-----------+--------------+
| PIL-0001    | Pencil 2B | 2B Pencil   |      0.54 |         2000 |
+-------------+-----------+-------------+-----------+--------------+
   
-- Check whether event scheduler is enabled
mysql> SELECT @@global.event_scheduler;
+--------------------------+
| @@global.event_scheduler |
+--------------------------+
| OFF                      |
+--------------------------+
   
-- Enable event scheduler
mysql> SET @@global.event_scheduler = ON;
-- In mysqld console --
100426 21:58:20 [Note] Event Scheduler: scheduler thread started with id 2
   
-- Schedule an event to increase the price by 10% for some products
mysql> CREATE EVENT testEvent
   ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 30 SECOND
   DO
      UPDATE products SET unitPrice = unitPrice * 1.1 WHERE productCode LIKE 'PIL%';

mysql> SHOW EVENTS \G
*************************** 1. row ***************************
                  Db: southwind_mini
                Name: testEvent
             Definer: myuser@localhost
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2010-04-26 21:49:21
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 0
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: utf8_unicode_ci
1 row in set (0.03 sec)
   
-- 30 seconds later, in mysqld console --
100426 21:59:05 [Note] Event Scheduler: Last execution of southwind_mini.testEvent. Dropping.
100426 21:59:05 [Note] Event Scheduler: Dropping southwind_mini.testEvent
100426 21:59:05 [Note] Event Scheduler: [myuser@localhost][southwind_mini.testEvent] 
   Data truncated for column 'unitPrice' at row 0
   
mysql> SHOW EVENTS \G
Empty set (0.04 sec)
   
mysql> SELECT * FROM products WHERE productCode LIKE 'PIL%';
+-------------+-----------+-------------+-----------+--------------+
| productCode | name      | description | unitPrice | unitsInStock |
+-------------+-----------+-------------+-----------+--------------+
| PIL-0001    | Pencil 2B | 2B Pencil   |      0.59 |         2000 |
+-------------+-----------+-------------+-----------+--------------+
An event can also be recurring:
CREATE EVENT eventName ON SCHEDULE 
EVERY intervalNumber intervalUnit
   [STARTS startTimestamp [+ INTERVAL count interval]]
   [ENDS endTimestamp [+ INTERVAL count interval]]
DO SQLstatements
Example:
-- Write an event to backup the Customers table daily
CREATE EVENT backupCustomers
ON SCHEDULE 
EVERY 1 DAY
STARTS NOW()
ON COMPLETION PRESERVE
COMMENT 'Daily copy of customers table to backup_customers'
DO 
......

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)