Procedures SQL

name: portada
layout: true
class: portada-slide, middle, right
---

# SQL Stored Procedures
## Administració de Sistemes Gestors de Bases de Dades

.footnote[Joan Puigcerver Ibáñez ([j.puigcerveribanez@edu.gva.es](mailto:j.puigcerveribanez@edu.gva.es))]

---
layout: true
class: regular-slide
.right[.logo[![logoMislata](/itb/images/logo_mislata.png)]]

---
# Stored Procedures

__Stored Procedures__ are a set of SQL statements that are stored in the
database that can be reused later on.

Procedures have __input__ parameters, used to introduce data to the procedure, and can produce an __output__.

.center[[MariaDB Procedures](https://mariadb.com/kb/en/create-procedure/)]

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

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

routine_body:
    Valid SQL procedure statement
```

---
# Stored Procedures

```
DELIMITER //

CREATE PROCEDURE example_procedure (
  IN input_parameter1 INT,
  IN input_parameter2 VARCHAR(10),
)
NOT DETERMINISTIC

BEGIN
  DECLARE variable INT;
  SELECT COUNT(*) INTO variable FROM table1 WHERE id = input_parameter1;
  INSERT INTO table2(id, num) VALUES (variable, input_parameter2);
END; //

DELIMITER ;
```

---
# Parameters
There are three different types of parameters:
- __IN__: Default value. A value is introduced to the procedure. The procedure can modify the value,
but this change will not be visible when the procedure ends.
- __OUT__: The procedure returns a value to the caller of the procedure. It is inicialized to NULL.
- __INOUT__: Both `IN` and `OUT`. It introduces a value into the procedure than can be modified
inside the procedure and will be returned back.

---
# Deterministic
Only applies to __FUNCTIONS__.

- __DETERMINISTIC__: They always return the same value for the same input. (Example: SUM function)
- __NOT DETERMINISTIC__: The result can be different for different executions with the same input parameters. This will be the case
for any function that relies with data in the database.

.center[[MariaDB Deterministic](https://mariadb.com/kb/en/create-procedure/#deterministicnot-deterministic)]

---
# Variables
__Variables__ can be defined inside functions and procedures, using the statement __DECLARE__.

.center[[MariaDB Declare](https://mariadb.com/kb/en/declare-variable/)]

Variables must be defined at the beggining of the procedure, just after the `BEGIN` statement.

```
BEGIN
  DECLARE tmp1 INT;
  DECLARE tmp2 TYPE OF table.column;

-- Statements
END
```

---
# Example
```
USE highschool;

DROP PROCEDURE IF EXISTS convalidate_subject;
DELIMITER //

CREATE PROCEDURE convalidate_subject (
  IN studentDNI VARCHAR(9),
  IN subject_acronym VARCHAR(6),
  IN start_year INT
)
NOT DETERMINISTIC

BEGIN
    DECLARE var_subject_id type of subject.subject_id;

SELECT subject_id INTO var_subject_id FROM subject WHERE acronym = subject_acronym;

INSERT INTO enrolled(studentDNI, subject_id, year, grade)
    VALUES (studentDNI, var_subject_id, start_year, 5);
END; //

DELIMITER ;
```
---
# CALL
A procedure can be called or executed with the statement __`CALL`__, specifiend the value of the input parameters.

```
CALL procedure_name([param1 [, param2 ] ...])
```

Example:

```
CALL convalidate_subject("24446666W", "PRG", 2018);
```

---
# IF - ELSEIF - ELSE
.center[[MariaDB IF](https://mariadb.com/kb/en/if/)]

The __`IF`__ statements implements a simple conditional structure. If the `condition` is evaluated to `TRUE`,
the statements defined under the `IF` statement are executed. If none of the conditions is met, the statements
under the `ELSE` are executed.

`ELSEIF` and `ELSE` blocks are optional.

```
IF condition THEN 
    statement_list
[ELSEIF condition THEN 
    statement_list] ...
[ELSE 
    statement_list]
END IF;
```

---
# IF Example

```
DELIMITER //

CREATE PROCEDURE test_if(
  IN number INT
)
DETERMINISTIC

BEGIN
    if number % 2 = 0
    then
        select concat(number, " is even") as result;
    else
        select concat(number, " is odd") as result;
    end if;
END; //

DELIMITER ;
```

---
# Funciones
.center[[MariaDB Functions](https://mariadb.com/kb/en/create-function/)]

```
CREATE [OR REPLACE]
    [DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
    [AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...]
    RETURN func_body

func_parameter:
    [ IN | OUT | INOUT | IN OUT ]  param_name type

type:
    Any valid MariaDB data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

func_body:
    Valid SQL procedure statement
```