Functions procedures triggers

Functions, procedures, triggers¶

Stored Procedures¶

Procedures are SQL code that can be executed multiple times. The procedures are stored on the database server and are compiled on the first execution. This allows conditional processing and programming syntax to be introduced in the database environment. You can create procedures using the following instruction:

CREATE PROCEDURE procedure_name(parameters)
BEGIN
    procedure_body
END    

procedure_name is the name of the routine being created and must appear to the right of the CREATE PROCEDURE expression. As part of the created procedure, you can define parameters (input IN and output OUT) that are passed to it using parameters. The code of a specific procedure must be between the keywords BEGIN and END. An example of a working procedure might be:

CREATE PROCEDURE GetProductsDetails(IN name VARCHAR(20))
BEGIN
    SELECT * FROM Product INNER JOIN Producer ON Product.id=Producer.id WHERE Producer.Name= name;
END;

NOTE: For the mysql client, before declaring the procedure, you should change the separator from;to e.g.//, so that we can still use semicolons inside the procedure code. The separator can be defined with the command delimiter, i.e. before creating the procedure, we executedelimiter //, and after defining it, delimiter;.

The procedure can be called with the command:

CALL procedure_name(parameters);

Referring to the example above, the call to the GetProductsDetails procedure will look like this:

CALL GetProductsDetails('Notebook');

Functions¶

User functions, like procedures, allow the user to execute SQL code and, like procedures, are stored on the database side. The difference between a procedure and a function is that a function returns the result of queries implemented inside its body. Moreover, the function can only have input parameters and can be called inside the function. The opposite situation is not possible.

You can create a function using the following instruction:

CREATE FUNCTION function_name(parameters)
RETURNS result
BEGIN
    function_body
RETURN result;
END

function_name is the name of the function being created that we will then refer to. Must be to the right of CREATE FUNCTION. As part of the created function, it is possible to define the input parameters passed to it using parameters. The RETURNS clause defines the type returned from the function. The code for a specific function must be between the keywords BEGIN and END. The final instruction of the body should be RETURN. An example of a working function could be:

CREATE FUNCTION getProducentRanking(price DECIMAL(10,2))
RETURNS VARCHAR(30)
BEGIN
    DECLARE ranking VARCHAR(30)
    IF price > 10000 THEN
        SET ranking = 'PREMIUM';
    ELSEIF price > 5000 THEN
        SET ranking = 'BUSINESS';
    ELSE
        SET ranking = 'BUDGET';
    END IF;            
    RETURN (ranking);
END;

NOTE: As in the case of the procedure declaration, when using the mysql client, it is advisable to execute thedelimiter /command before creating the function, and after defining it,delimiter;. Moreover, it is possible that before creating a function like above, you need to change the default configuration of the mysql database, additionally executing the command:set global log_bin_trust_function_creators = 1;.

The SET statement allows you to set a specific value on a declared variable (declared withDECLARE). An example of a function call might look like this:

SET ranking=getProducerRanking(3000);

SELECT getProducerRanking(12000);

Triggers¶

The so-called Triggers are special kinds of procedures attached to tables that are created. Triggers are only fired when the specified DML operation occurs: INSERT, UPDATE, DELETE. When declaring a trigger, a specific excitation operation is selected. The structure of the trigger might look like below:

CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT, UPDATE, DELETE} ON table_name FOR EACH ROW SET operation;

The CREATE TRIGGER command with the specified name trigger_name, creates a trigger with the specified name. The keyword BEFORE and one of the statements INSERT, UPDATE, DELETE indicate when the trigger is to be called. The expression FOR EACH ROW defines the type of executed operation: operation on each of the previous table records. A sample trigger can look like this:

CREATE TRIGGER before_product_update BEFORE UPDATE ON Product FOR EACH ROW 
INSERT INTO Product SET 
    Name=OLD.Name, 
    IDOFTHEPRODUCER=OLD.ProducerID, 
    timestamp = NOW();

The above trigger creates a backup of the record before attempting any update. The OLD keyword allows access to the value of the record being updated.