DAM - DAW - MP 0484 Bases de Dades

Variables

Les variables han de declarar abans que les condition, cursor i handler, que veurem més endavant.

Els noms de les variables no són case-sensitive.

L'abast d'una variable local es troba dins del bloc BEGIN ... END on es declara. Es pot fer referència a la variable en blocs niats dins del bloc declarant, excepte aquells blocs que declaren una variable amb el mateix nom.

Declaració de variables

Les variables es declaren amb la instrucció DECLARE variable.

La sintaxi és:

DECLARE var_name [, var_name] ... [[ROW] TYPE OF]] type [DEFAULT value];

En el moment de la declaració es pot assignar un valor amb la clàusula DEFAULT. A la variable li podem assignar:

un tipus dels SQL coneguts com ara CHAR, VARCHAR, INT, etc.
indicar que és del mateix tipus que un camp d'una taula, amb la clàusula TYPE OF
indicar que és del mateix tipus que un registre d'una taula, amb la clàusula ROW TYPE OF

Exemples:

DECLARE v_sou TYPE OF empresa.employees.salary DEFAULT 0;
DECLARE v_job VARCHAR(10) DEFAULT 'IT_PROG';

DELIMITER //
BEGIN NOT ATOMIC
    DECLARE v_sou TYPE OF empresa.employees.salary DEFAULT 0;
    DECLARE v_job VARCHAR(10) DEFAULT 'IT_PROG';
    DECLARE v_data_naix DATE;
    DECLARE v_tamany INT DEFAULT 10;
    DECLARE v_ciutat VARCHAR(13) DEFAULT 'Girona';
    DECLARE v_nombre1 INT DEFAULT 50000;
    DECLARE v_nombre2 INT;
    DECLARE v_nom VARCHAR(20) DEFAULT 'Robert';
    DECLARE v_import DECIMAL(10,2) DEFAULT 20.65;
END
//
DELIMITER ;

Assignació de variables

Per assignar un valor a una variable utilitzem la comanda SET variable. La sintaxi és:

SET var_name = expr [, var_name = expr] ...

Exemples:

SET v_sou = 1000;
SET v_job = 'SA_MAN';
SET v_sou = sou * 1.1;
SET v_job = 'SA_MAN';
SET v_data_naix = CURRENT_DATE;
SET v_tamany = v_tamany + 50;
SET v_ciutat = 'Perpinyà';
SET v_nombre1 = 70;
SET v_nombre2 = v_nombre1 * 3;
SET v_nom = CONCAT_WS(' - ', v_job, v_ciutat);

Assignació de variables a partir d'una consulta `SELECT INTO`

També podem assignar valors retornats per una SELECT a una variable amb la sentència SELECT INTO.

La consulta SELECT només pot retornar una fila

Si la consulta SELECT retorna més d'una fila, rebrem un error

SQL Error [1172] [42000]: (conn=12) Result consisted of more than one row

En el cas en què la consulta no retorni cap fila, la variables del INTO no es veurà modificada.

No és el mateix retornar NULL que no retornar cap fila.

La sintaxi és:

SELECT col_name [, col_name] ...
    INTO var_name [, var_name] ...
    table_expr

Exemples

En variables tipus camp

DELIMITER //
BEGIN NOT ATOMIC
    DECLARE v_import TYPE OF empresa.employees.salary;
    DECLARE v_feina TYPE OF empresa.employees.job_id;
    SELECT SALARY, JOB_ID
        INTO v_import, v_feina
        FROM empresa.employees
        WHERE EMPLOYEE_ID = 100;
END
//
DELIMITER ;

En una variable tipus registre o fila

DELIMITER //
BEGIN NOT ATOMIC
    DECLARE r_employees ROW TYPE OF empresa.employees;
    SELECT *
        INTO r_employees
        FROM empresa.employees
        WHERE EMPLOYEE_ID = 100;
END
//
DELIMITER ;

Variables d'usuari

En MariaDB i MySQL existeixen les variables d'usuari. Aquestes variables només existeixen durant la sessió de l'usuari i es poden utilitzar en diferents instruccions i fins i tot diferents blocs de codi.

No es defineixen i no se'ls assigna cap tipus.

Existeixen des del moment en què se'ls assigna un valor. Segons els tipus de valor que li assignem, aquest serà el seu tipus i podrà anar variant si li assignem valors de tipus diferents.

Les variables d'usuari s'identifiquen perquè comencen amb el signe arrova @.

Els noms de les variables d'usuari no distingeixen entre majúscules i minúscules.

Si demanem per una variable no assignada, el seu valor serà NULL.

Podem assignar-hi valor amb:

La comanda SET
L'operador := en una comanda SQL
SELECT .. INTO

Exemple d'ús de variables d'usuari:

SET @comptador = 0;
SET @comptador = @comptador + 1;
SET @usuari = CONCAT('usuari', LPAD(@comptador, 3, '0'));
SET @id = 0;
SELECT (@id:= @id + 1) numero, department_id
  FROM empresa.departments
  ORDER BY department_id;
SELECT COUNT(*) INTO @quantesTaules
  FROM information_schema.tables;
SELECT @comptador, @usuari, @quantesTaules;
SET @departaments = '';
SELECT @departaments := CONCAT_WS(':',@departaments,   department_name)
  FROM empresa.departments ORDER BY department_name;
SELECT @departaments;

A les línies 1 a 3 s'assignen valors amb la comanda SET.
A les línies 4 i 5 es crea i es consulta, actualitzant-se a cada fila, un comptador de files.
A la línia 8 s'assignen valors amb la comanda SELECT ... INTO ....
A la línia 12, fixeu-vos com per assignar valor en una SELECT podem utilitzar l'assignació amb dos punts abans del signe igual :="

D'aquesta manera, a més d'assignar-se el valor a la variable, també es visualitza en pantalla.

Només es permet aquest tipus d'assignació amb variables d'usuari.

Recordeu que a una variable sense assignar li correspon el valor NULL

Una primera funció

De la mateixa manera que tenim funcions que hem utilitzat fins ar, com ara CONCAT, CONCAT_WS, REPEAT, SUBSTR, IFNULL, etc, nosaltres també podem crear-nos les nostres pròpies funcions per després utilitzar-les.

DELIMITER //
CREATE OR REPLACE FUNCTION nomDepartament(
    p_department_id TYPE OF empresa.departments.department_id
)
RETURNS VARCHAR(100)
BEGIN
    DECLARE v_department_name TYPE OF empresa.departments.department_NAME DEFAULT 'INEXISTENT';
    SELECT department_name INTO v_department_name
       FROM empresa.departments
       WHERE department_id = p_department_id;
    RETURN v_department_name;
END;
//
DELIMITER ;

Per provar-la podem executar:

SELECT CONCAT('Departament 10 -> ', nomDepartament(10));SELECT nomDepartament(20) INTO @nom1;
SET @nom2 = nomDepartament(30);
SELECT nomDepartament(50), @nom1, @nom2;
SELECT last_name, first_name, nomDepartament(department_id)
       FROM employees;

Per executar comandes des de la consola, podem afegir els paràmetres -sN per presentar les dades "sense floritures"

mysql -u usuari -pusuari -sN empresa