# Curso SQL - DÍA 4
## Variables en SQL

### Declaracion de variables
Las variables en postgresql se declara de la siguiente manera

DECLARE vAlt varchar;

En donde `vAlt` corresponde al nombre de la variable y `varchar` al tipo de la misma.

Para setear el valor de la variable, se realiza de la siguiente manera:

vAlt := 'valor a setear';

Tambien se podría declarar la variable con el valor en 1 sola línea, que sería de la siguiente manera:

DECLARE vAlt varchar := 'valor a setear';

Una variable tambien se puede setear con el resultado de una query:

DECLARE vAlt varchar := 'valor a setear';
SELECT name INTO vAlt FROM table WHERE id = 1;

### Uso de variables en Queries

#### Clausula WITH
Para usar las variables debemos hacer uso de la clausula `WITH` en postgresql, de la cual se realiza de la siguiente manera:

WITH myconstants (var1, var2) as (
   values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
   OR something_else = var2;

#### Clausula DO

DO $$
DECLARE foo TEXT;
BEGIN
  foo := 'bar';
  SELECT foo;
END $$;

DO $$
DECLARE
   film_count INTEGER;
BEGIN
   SELECT COUNT(*) INTO film_count FROM film;
   RAISE NOTICE 'The number of films: %', film_count;
END; $$;

## Stored Procedure, Functions, Triggers y Views
### Stored Procedure
Un procedimiento almacenado (Stored Procedure) es un bloque de codigo SQL compilado en el cual puede (o no) devolver un resultado (o varios).

La forma de declarar un SP es de la siguiente manera

create [or replace] procedure sp_name(parameter_list)
language plpgsql
as $$
declare
    -- declaración de variables
begin
    -- Código a ejecutar
end; $$

En donde:
- **sp_name**: Es el nombre del procedimiento almacenado, se suele comenzar con las siglas **SP_**.
- **parameter_list**: Son los parametros que tiene el procedimiento, puede o no tener los mismos.
- **LANGUAGE**: Es el lenguaje en el cual esta declarado el SP.
- **DECLARE**: Es donde se declaran las variables internas que se van a utilizar en el SP.
- **BEGIN/END**: Es el bloque de codigo que se va a ejecutar.

Veamos un ejemplo mas claro de esto

CREATE OR REPLACE PROCEDURE transfer(
   sender int,
   receiver int, 
   amount dec
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts 
    SET balance = balance - amount 
    WHERE id = sender;

    UPDATE accounts 
    SET balance = balance + amount 
    WHERE id = receiver;

    COMMIT;
END;$$

Y la forma de llamar a un SP es de la siguiente manera:

CALL sp_name(parameter_list);

En donde:
- **sp_name**: Es el nombre del procedimiento almacenado
- **parameter_list**: Son los parametros que se envían a los mismos.

Y con el ejemplo anterior, a dicho SP se lo podría llamar de la siguiente manera:

CALL transfer(1, 2, 1000);

[Mas información acerca de Stored Procedure](https://www.postgresql.org/docs/current/sql-createprocedure.html)

### Functions
Una funcion en postgresql, es parecido al SP, con las principales diferencias en que:
- Una funcion esta obligada a devolver solo 1 resultado.
- Una funcion se puede utilizar en una consulta, mientras que en el SP esto no es posible, se debe almacenar el resultado del SP en una variable y luego poder utilizarla.

La forma de declarar una funcion es de la siguiente manera:

CREATE [OR REPLACE] FUNCTION function_name (parameter_list)
   RETURNS return_type 
   LANGUAGE plpgsql
AS
$$
DECLARE
    -- declaración de variables
BEGIN
    --Código a ejecutar
    RETURN { variable_name | value }
END;
$$

En donde:
- **function_name**: Es el nombre de la funcion.
- **parameter_list**: Son los parametros que tiene el procedimiento, puede o no tener los mismos.
- **RETURNS**: Especifica el tipo de valor que se va a devolver el mismo.
- **LANGUAGE**: Es el lenguaje en el cual esta declarado el SP.
- **DECLARE**: Se declaran las variables internas que se van a utilizar (si es que se utilizan, sino se omite)
- **BEGIN/END**: Es el bloque de codigo que se va a ejecutar.
- **RETURN**: Especifíca el valor a devolver.

Vamos a ver un ejemplo de una función:

CREATE FUNCTION get_film_count(len_from int, len_to int)
RETURNS int
LANGUAGE plpgsql
AS
$$
DECLARE
   film_count integer;
BEGIN
   SELECT COUNT(*) 
   INTO film_count
   FROM film
   WHERE length BETWEEN len_from AND len_to;
   
   RETURN film_count;
END;
$$;

La forma de llamarlo, es a traves del ``SELECT``.
Para el anterior ejemplo, si lo deberíamos llamar, sería de la siguiente manera:

SELECT get_film_count(40,90);

[Mas información acerca de Functions](https://www.postgresql.org/docs/current/sql-createfunction.html)

### Triggers
Los triggers, son disparadores que se disparan en acciones definidas en una tabla.
Esto se puede disparar en los siguientes casos:
- Antes de intentar realizar un **INSERT**/**UPDATE**/**DELETE** en una tabla.
- Despues de realizar un **INSERT**/**UPDATE**/**DELETE** en una tabla.
- Reemplazar la accion de un **INSERT**/**UPDATE**/**DELETE**.

En el caso de postgresql, la creacion del trigger, se separa en 2 partes.

La primera, es crear una funcion el cual debe retornar el tipo **TRIGGER**.
La forma de hacerlo es la siguiente:

CREATE FUNCTION trigger_function() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
BEGIN
   -- Código a ejecutar
END;
$$

La segunda parte, consiste en crear el Trigger propiamente dicho, que se realiza de la siguiente manera:

CREATE TRIGGER TR_name 
   {BEFORE | AFTER |INSTEAD OF} { event_name }
   ON table_name
   [FOR [EACH] { ROW | STATEMENT }]
       EXECUTE PROCEDURE trigger_function

En donde
- **tr_name**: Hace referencia al nombre del trigger, generalmente con el prefijo **TR_**.
- **BEFORE | AFTER | INSTEAD OF**: En que momento queres que se dispare el trigger, **BEFORE** (Antes), **AFTER** (Despues), **INSTEAD OF** Reemplazando.
- **event_name**: En que evento (**INSERT**, **UPDATE**, **DELETE**, **TRUNCATE**) se dispara el trigger.
- Para el caso de UPDATE, se puede especificar que se dispare cuando se actualiza una columna en particular poniendo **UPDATE OF column_name**.
- **table_name**: Nombre de la tabla en donde se dispara.
- **FOR EACH**: Esto tiene sentido cuando se hace un UPDATE y el mismo afecta a varias columnas.

Esto se puede ver mas claro de la siguiente manera:

CREATE OR REPLACE FUNCTION log_last_name_changes()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
	IF NEW.last_name <> OLD.last_name THEN
		 INSERT INTO employee_audits(employee_id,last_name,changed_on)
		 VALUES(OLD.id,OLD.last_name,now());
	END IF;

	RETURN NEW;
END;
$$

*En el caso de la funcion, se debe devolver **NEW** o **OLD**, para saber si se debe mantener el viejo registro o el nuevo.*

CREATE TRIGGER last_name_changes
  BEFORE UPDATE
  ON employees
  FOR EACH ROW
  EXECUTE PROCEDURE log_last_name_changes();

[Mas información acerca de Triggers](https://www.postgresql.org/docs/current/plpgsql-trigger.html)

### Views
Una vista lo que hace es representar el resulta de una consulta, esto tiene sentido cuando la consulta que se debe realizar se utiliza en varias partes del sistema de diferentes sistemas, y es una consulta compleja, la forma de declarar una vista es de la siguiente manera:

CREATE VIEW V_name AS
SELECT column1, column2, columnN
FROM table_name
WHERE column5 = 'ADMIN'

En donde:
- **V_name**: Nombre de la vista.

La forma de consumir una vista es como si fuese una tabla, de la misma manera, sería de la siguiente manera:

SELECT * FROM V_name;

[Mas información acerca de las Views](https://www.postgresql.org/docs/current/sql-createview.html)

## Laboratorios
### Crear una tabla de bitacora sobre la tabla Usuarios
Se debe registrar los cambios de la tabla de Usuarios en una nueva tabla llamada Usuarios_Auditoria.

### Crear dos SP: 1 para la creacion y otro para la actualizacion de un usuario
Se debe crear un SP en el cual le paso los parametros de como crear/modificar un usuario y el mismo debe crear el INSERT del usuario.

### Hacer un Function para que encripte la password del usuario
Se debe crear una funcion que encripte el password del usuario, esta funcion se debe llamar desde el SP de creacion de usuario

### Hacer una Vista de la tabla Usuarios
Se debe crear una vista de la tabla usuarios en el cual se pueda listar los usuarios con su password desencriptado, para esto deben crear otra funcion que desencripte el password.