DAM - DAW - MP 0484 Bases de Dades - NF5

Procediments

Un Procediment és un bloc de codi que emmagatzemem dins el SGBDR per a poder executar-lo en qualsevol moment.

Els procediments es creen amb la comanda CREATE PROCEDURE.

CREATE PROCEDURE permet donar un nom a un bloc de codi que admetrà una sèrie de paràmetres que podrem utilitzar dins el codi com si fossin variables locals.

Els procediments admeten paràmetres que poden ser d’entrada (IN), de sortida (OUT) o de entrada i sortida (INOUT).

Una procediment es crida amb la sentència CALL.

CREATE PROCEDURE

Sintaxis de CREATE PROCEDURE.

CREATE [ OR REPLACE ]
  [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
  PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
  [characteristic ...] routine_body

proc_parameter:
  [ IN | OUT | INOUT ] param_name type

Characteristic:
  LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

CREATE PROCEDURE crea un procediment emmagatzemat. Quan s’invoca des d’una altra base, diferent a la que pertany, s’executa un USE baseDeDades implícit

Per executar un procediment emmagatzemat cal utilitzar la sentència CALL.

Els procediments admeten paràmetres que poden ser d’entrada, de sortida o de entrada i sortida.

Podem utilitzar variables d’usuari que definirem amb la comanda SET i anomenarem amb un nom que comenci per arrova @.

IN / OUT / INOUT en Exemple

DELIMITER //
CREATE OR REPLACE
PROCEDURE pProva1(IN pUn INT, INOUT pDos INT, OUT pTres INT)
BEGIN
    SET pTres = pUn - pDos;
    SET pDos = pDos + pUn;
    SET pUn = 0;
    SELECT concat_ws(' ', 'Valors dins de la funció: (', pUn, ',', pDos, ', ', pTres, ')');
END//
DELIMITER ;
SET @a = 15;
SET @b = 4;
SELECT concat_ws(' ', 'Valors abans de la funció: (',@a, @b,@c, ')');
CALL pProva1(@a, @b, @c);
SELECT concat_ws(' ', 'Valors després de la funció: (', @a, @b,@c, ')');

EXEMPLE

CREATE OR REPLACE TABLE empleats AS
          SELECT * FROM empresa.employees;
DELIMITER //
CREATE OR REPLACE PROCEDURE
    PujaSou(p_employee_id TYPE OF empleats.employee_id,
            p_increment TYPE OF empleats.salary) MODIFIES SQL DATA
BEGIN
    UPDATE empleats SET salary = salary + p_increment
           WHERE employee_id = p_employee_id;
    IF ROW_COUNT() > 0 THEN
        SELECT CONCAT('Sou modificat per l''empleat ', p_employee_id);
    ELSE
        SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT='Empleat inexistent';
    END IF;
END//
DELIMITER ;
CALL PujaSou(100, 10);
CALL PujaSou(10, 10);

Modifica el procediment de forma que miri, abans de fer l'UPDATE, si el nou sou està comprès entre els valors MIN_SALARY i MAX_SALARY de la taula JOBS.

En cas contrari genera un error amb SIGNAL

Possible primera versió. Consules senzilles

Possible solució

DELIMITER //
CREATE OR REPLACE PROCEDURE
    PujaSou(p_employee_id TYPE OF empleats.employee_id,
            p_increment TYPE OF empleats.salary) MODIFIES SQL DATA
BEGIN
  -- Comprovem que el SOU NOU estigui entre el MIN_SALARY i el MAX_SALARY pel JOB que té
  DECLARE v_salary TYPE OF empleats.salary;
  DECLARE v_job_id TYPE OF jobs.job_id;
  DECLARE v_min_salary TYPE OF jobs.min_salary;
  DECLARE v_max_salary TYPE OF jobs.max_salary;
  SELECT SALARY, JOB_ID
    INTO v_salary, v_job_id
    FROM empleats e 
    WHERE employee_id = p_employee_id;
  IF v_salary IS NOT NULL THEN
    -- Comprovem si l'UPDATE serà valid anant a buscar el MIN_SALARY i el MAX_SALARY
    SELECT min_salary, max_salary INTO v_min_salary, v_max_salary 
      FROM JOBS WHERE job_id = v_job_id;
    IF v_salary + p_increment BETWEEN v_min_salary AND v_max_salary THEN 
      UPDATE empleats SET salary = salary + p_increment
              WHERE employee_id = p_employee_id;
        IF ROW_COUNT() > 0 THEN
            SELECT CONCAT('Sou modificat per l''empleat ', p_employee_id);
        END IF;
    ELSE
      SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT='Sou fora de rang';
    END IF;
  ELSEIF v_job_id IS NULL THEN
    SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT='Empleat inexistent';
  END IF;
END//
DELIMITER ;

Possible segona versió. Amb JOIN

Possible solució amb JOIN

DELIMITER //
CREATE OR REPLACE PROCEDURE
    PujaSou(p_employee_id TYPE OF empleats.employee_id,
            p_increment TYPE OF empleats.salary) MODIFIES SQL DATA
BEGIN
    -- Comprovem que el SOU NOU estigui entre el MIN_SALARY i el MAX_SALARY pel JOB que té
    DECLARE v_salary TYPE OF empleats.salary;
    DECLARE v_job_id TYPE OF jobs.job_id;
    DECLARE v_min_salary TYPE OF jobs.min_salary;
    DECLARE v_max_salary TYPE OF jobs.max_salary;
    SELECT SALARY, JOB_ID, MIN_SALARY, MAX_SALARY
        INTO v_salary, v_job_id, v_min_salary, v_max_salary
        FROM empleats e JOIN jobs USING (job_id)
        WHERE employee_id = p_employee_id;
    IF v_job_id IS NULL THEN
        SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT='Empleat inexistent';
    ELSE
        IF v_salary + p_increment BETWEEN v_min_salary AND v_max_salary THEN 
            UPDATE empleats SET salary = salary + p_increment
                  WHERE employee_id = p_employee_id;
            IF ROW_COUNT() > 0 THEN
                SELECT CONCAT('Sou modificat per l''empleat ', p_employee_id);
            END IF;
        ELSE
            SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT='Sou fora de rang';
        END IF;
    END IF;
END//
DELIMITER ;

ALTER PROCEDURE

ALTER PROCEDURE s'utilitza per a modificar alguna de les característiques d’un procediment. No permet modificar ni els paràmetres ni el codi. Per a fer aquests canvis tenim la comanda CREATE OR REPLACE PROCEDURE.

ALTER PROCEDURE pProva1
    CONTAINS SQL
    SQL SECURITY INVOKER
    COMMENT 'Prova de IN, OUT i INOUT';

Diccionari de dades

select * from information_schema.routines
where routine_name = 'PujaSou';

Name	Value
SPECIFIC_NAME	PujaSou
ROUTINE_CATALOG	def
ROUTINE_SCHEMA	pft
ROUTINE_NAME	PujaSou
ROUTINE_TYPE	PROCEDURE
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	BEGIN¶ UPDATE empleats ...
***	***
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	CONTAINS SQL
***	***
SECURITY_TYPE	DEFINER
CREATED	AAAA-MM-DD HH:mm :SS
LAST_ALTERED	AAAA-MM-DD HH:mm :SS
***	***
DEFINER	usuari@localhost
***	***