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. You can enter conditional processing and programming syntax in a database environment. Procedures can be created using the following instructions:

CREATE PROCEDURE procedure_name(parameters)
BEGIN
    procedure_body
END    

procedure_name is the name of the procedure being created and must be on the right side of the expression CREATE PROCEDURE. As part of the created procedure, you can define parameters (input IN and outputOUT) passed to it using parameters. The code for the specific procedure must be between the keywords BEGIN andEND. An example of a working procedure could be:

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

ATTENTION: For the mysql client, before declaring the procedure, it is worth changing the separator from;to e.g.//, so that we can still use semicolons inside the procedure code. The separator can be defined using the delimiter command, i.e. before creating a procedure, we perform delimiter //, and after defining it delimiter;.

The procedure can be called using the command:

CALL procedure_name(parameters);

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

CALL GetProductsDetails('Notebook');

Functions¶

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

Creating a function is possible using the following instructions:

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

function_name is the name of the function being created, which we will then refer to. Must be on the right side of the expression CREATE FUNCTION. As part of the created function, you can define 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 last body instruction should be `RETURN '. An example of a working function may be:

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

ATTENTION: As in the case of the declaration of procedures, using the client mysql it is worth to execute the command delimiter / before creating the function, and after defining it delimiter;. PIn addition, it is possible that before creating such a function as 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 instruction 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 types of procedures attached to the tables created. Triggers are only triggered when a specific operation occurs DML: INSERT, UPDATE, DELETE. When you declare a trigger, a specific excitation operation is selected. The trigger structure can look like this:

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

The CREATE TRIGGER command with the given name trigger_name. The keyword BEFORE and one of the instructionsINSERT, UPDATE,DELETE indicates when the trigger is to be called. The expression FOR EACH ROW defines the type of operation performed: operation on each of the existing table records. An example trigger may look like this:

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

PThe above trigger backs up the record before attempting any update. The keyword OLD allows access to the value of the updated record.