---
title: "PLSQL"
...

\newpage{}

# Procédure, Fonctions, Triggers

## Bloc PLSQL

```sql
DECLARE
	...
BEGIN
	...
EXCEPTION
	...
END;

```

## Déclarer une variable 

```sql
DECLARE
    identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initvalueexpr]
```

Exemples : 

```sql
DECLARE 
    hiredate DATE;
    deptno NUMBER(2) NOT NULL := 10;
    location VARCHAR(13) := 'Paris';
    comm CONSTANT NUMBER := 1400
```

## Assigner des valeurs

```sql
identifier := expr;

hiredate := '2020-01-01'
```

## Types de données

- VARCHAR2(maxlength)
- NUMBER[(presicion,scale)]
- DATE
- CHAR[(maxlength)]
- LONG
- LONG RAW
- BOOLEAN
- BINARY_INTEGER

On peut également affecter dynamiquement le type d'une autre variable ou d'une colonne de la base avec l'attributs `%TYPE` :

```sql
DECLARE
name EMP.NAME%Type;
```

On peut également récupérer un type Tuple depuis une table : 
```sql
DECLARE
dept_record DEPT.%ROWTYPE;
```


> Le fonctions SQL (sauf fonctions d'agrégation sont valides en PLSQL)

## Interagir avec la base de donnée

### Récupérer des données

```sql
DECLARE
	v_deptno NUMBER(2);
	v_loc 	VARCHAR(15);
BEGIN
	SELECT deptno, loc INTO v_deptno,v_loc
    FROM dept
    WHERE dname = 'SALES':
```


```sql
DECLARE
	v_sum_salary EMP.SAL%TYPE;
    v_deptno NUMBER NOT NULL := 10;
BEGIN
	SELECT SUM(sal) INTO v_sum_salary
    FROM EMP
    WHERE deptno = v_deptno
```

### Manipuler des données

On peut `INSERT` / `UPDATE` / `DELETE` des données : 

```sql
DECLARE 
	v_empno EMP.EMPBO%TYPE
BEGIN
	SELECT empno_sequence.NEXTVAL
    INTO v_empno
    FROM DUAL;
    
    INSERT INTO EMP(EMPNO,ENAME,JOB,DEPTNO)
    VALUES (v_empno),'HARDING','CLERK',10);
END;
```

```sql
DECLARE
	v_sal_increase EMP.SAL%TYPE := 2000;
BEGIN
	UPDATE EMP
    SET SAK = SAL + v_sal_increase
	WHERE JOB = 'ANALYST';
```

```sql
DECLARE
	v_deptno EMP.DEPTNO%TYPE := 10
BEGIN
	DELETE FROM EMP
    WHERE DEPTNO = v_deptno
```

### Gestion des Transactions

On peut créer un point de sauvegarde : 

```sql
SAVEPOINT a;
```

Pour y revenir plus tard : 

```sql
ROLLBACK TO SAVEPOINT a;
```

Le Rollback n'est pas un "GOTO", il ne contrôle pas le flux d'exécution du code PLSQL, il va simplement annuler les opérations DML depuis le point de sauvegarde.

On peut  également valider la transaction : 

```sql
COMMIT;
```

## Structures de contrôle

On peut faire un If : 

```sql
IF ...condition... THEN
	...statements...
[ELSIF ...condition... THEN
	...statements...]
[ELSE ...statements...]
END IF;
```

## Boucles

### Boucle simple (équivalent do while)

```sql
LOOP 
	...statement...
    EXIT [WHEN condition]
END LOOP;
```

### Boucle FOR

```sql
FOR ...index variable... IN [REVERSE] ...start... .. ...end... LOOP
	...statements...
END LOOP;
```

### Boucle WHILE

```sql
WHILE ...condition... LOOP
	...statements...
END LOOP;
```

## Curseur

Structure de donnée permettant d'itérer sur les résultats d'une requête SQL.

### Déclarer un curseur

```sql
CURSOR employees IS
SELECT * FROM EMP;
```

### Opérations sur un curseur

- Open : ouvre le curseur

```sql
OPEN employees;
```

- Fetch : récupère le prochain élément du curseur dans une variable

```sql
FETCH employees INTO employee
```

- Close : ferme le curseur : 
```sql
CLOSE employees;
```

### Attributs d'un curseur

- `%ISOPEN` : `TRUE` si le curseur est ouvert
- `%NOTFOUND` : `TRUE` si le dernier `FETCH` n'a pas retourné d'élément
- `%FOUND` : `TRUE` si le dernier `FETCH` a retourné d'élément
- `%ROWCOUNT` : Nombre d'éléments récupérer jusque là 

### Curseur avec boucle FOR

On peut itérer sur un curseur directement avec une boucle FOR, à la manière d'un FOR EACH.

```sql
FOR employee IN employees LOOP
	...statments...
END LOOP;
```

### Curseur particulier : SQL

Le curseur SQL permet de récupérer des informations sur la dernière instruction DML exécutée : 

- `SQL%ROWCOUNT` : Nombre de ligne affectées
- `SQL%FOUND` : `TRUE` si au moins une ligne affectée
- `SQL%NOTFOUND` : `TRUE` si aucune ligne affectée
- `SQL%ISOPEN` : toujours `FALSE`


## Exceptions

On peut utiliser une clause `EXCEPTION` dans un bloc PLSQL pour gérer les exceptions levées.

```sql
DECLARE
	...
BEGIN
	...
EXCEPTION
	...
END;
```

### Syntaxe

```sql
EXCEPTION
	WHEN ...exception1... [OR ...exception2...] THEN
    	...statements...
    [WHEN ...exception1... [OR ...exception2...] THEN
    	...statements...]
```

Exemple : 

```sql
DECLARE 
	v_name EMP.NAME%TYPE;
BEGIN
	SELECT NAME INTO v_empno 
    FROM EMP 
    WHERE EMPNO = 10 ;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
    	DBMS_OUTPUT.PUT_LINE('No employee with this Id');
END;
```

Pour récupérer respectivement le code de l'erreur et son message, utiliser les macros `SQLCODE` et `SQLREM`.

### Exception définies par l'utilisateur

On peut aussi déclarer nos propres exception, et leur assigner un code d'erreur (entre -20 000 et -20 999) :

```sql
DECLARE
	e_no_such_emp EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_no_such_emp, -22292)
```

Pour lever une exception : 

```sql
RAISE e_no_such_emp;
```

## Procédures

Une procédure est une bloc PLSQL réutilisable qui effectue une action.

```sql
CREATE [OR REPLACE] PROCEDURE ...nom... (...arg1... [...mode...] ...type, ...)
IS
... bloc plsql...
END ...nom...;
```

Supprimer une procédure : `DROP PROCEDURE ...nom...`

### Mode de passage de paramètres

- `IN` : Données passée au sous programme à laquelle il accèdera en lecture uniquement
- `OUT` : Utilisé pour retourner une valeur au programme appelant par effet de bord
- `IN OUT` : Données passée au sous programme auquel il accède en lecture et écriture qui est retournée au programme parent.

## Fonctions

Une fonction est une procédure qui retourne une valeur.

```sql
CREATE [OR REPLACE] FUNCTION ...nom... (...arg1... [...mode...] ...type..., ...)
RETURN ...type...
IS
... bloc plsql...
END ...nom...;
```

## Packages

Permet de lier les éléments qui ont un rapport logique ou sémantique entre eux. Un package est constitué d'un entête et d'un corp : 

### Entête

Pour les prototype des éléments: 

```sql
CREATE [OR REPLACE] PACKAGE ...nom...
IS 
	...déclaration des prototypes...
END ...name...;
```

### Corps

Pour les implémentations : 

```sql
CREATE [OR REPLACE] PACKAGE BODY ...nom...
IS 
	...déclaration des implémentations...
END ...name...;
```

### Exemple

```sql
CREATE OR REAPLACE PACKAGE time IS
	FUNCTION GetTimestamp RETURN DATE;
    PROCEDURE resetTimeStamp;
END time;

CREATE OR REPLACE PACKAGE BODY time IS
		StartTimesStamp DATE := SYSDATE; -- donnée privée du package
        
        FUNCTION GetTimeStamp RETURN DATE IS
        BEGIN
        	RETURN StartTimesStamp;
        END GetTimeStamp;
        
        PROCEDURE ResetTimeStamp IS
        BEGIN
        	StartTimestamp := SYSDATE;
        END ResetTimeStamp;
END time;                  
```

## Triggers

Un trigger est un bloc PLSQL qui s'exécute automatiquement sur un événement particulier, les interactions avec la base de donnée.

### Syntaxe

```sql
CREATE [OR REPLACE] TRIGGER ...nom...
...timing... ...event... [OR ...event2..., ]
ON ...table...
[FOR EACH ROW]
[WHEN ...condition...]
...bloc PLSQL...
```

### Timing

- `BEFORE` : le trigger s'exécute avant le déclanchement de l'événement.
- `AFTER` : le trigger s'exécute après le déclanchement de l'événement.

### Evènement

L'événement en question peut être une des trois opérations DML : `INSERT`, `UPDATE` ou `DELETE`.

### Type

Il existe deux types de trigger : 

- Statement : s'exécute une seule fois pour un événement (défaut) pas d'accès au contenu de chaque ligne
- Row : s'exécute une fois pour chaque ligne affectée par l'événement. Possibilité d'accéder aux valeurs avec `:OLD` et `:NEW`.

### Prédicats conditionnel

Pour les triggers qui se déclenchent sur plusieurs événements, on peut vérifier de quel événement il s'agit dans une condition : 

```sql
IF INSERTING THEN
	... statements...
END IF;
```

Le prédicat peut également porter sur une colonne précise de la table : 
```sql
IF UPDATING('SAL') THEN
	... statements...
END IF;
```

### Activation

```sql
ALTER TRIGGER ...nom trigger... DISABLE | ENABLE
```

Pour tous les trigger d'une table : 

```sql
ALTER TRIGGER ...nom table... DISABLE | ENABLE ALL TRIGGERS
```


# Objets Oracle

## Outils d'administration Oracle

- IOR : démarrage et l'arrêt d'un système Oracle
- SGI : suivi d'un système Oracle
- DOS : suivi de l'utilisation d'une système Oracle
- AIJ : journalisation
- CRT : définition des caractéristiques des terminaux
- OEM (Oracle Enterprise Manager) : Outil graphique d'Oracle

Deux utilisateurs particuliers existent : SYS et SYSTEM. Ils ont les privilèges du DBA (Database Administrator).

- SYS possède toutes les tables du dictionnaire de données
- SYSTEM possède toutes les vues du dictionnaire de données

## Dictionnaire de données

Le dictionnaire de données est une base de donnée qui contient toutes les structures et objets de la base Oracle : 

- Les utilisateurs et leurs privilèges
- Les tables, colonnes et leurs types
- Statistiques sur les tables et les index
- Structures de stockage de la base

Il est divisé en deux niveaux : 

- Niveau interne : contient toutes les tables
- Niveau externe : fournit de multiple vues sur ces tables pour offrir de l'info sur les objets et structures

La vue `DICT` ou `DICTIONNARY` contient toutes les vues du dictionnaire de données, qui se divisent en quatre catégories : 

- `USER_` : vues relatives aux objets appartenant à l'utilisateur
- `ALL_` : vues relatives aux objets accessibles par l'utilisateur
- `DBA_` : vues relatives à l'administration. Accessible uniquement par SYSTEM
- `V$` : vues dynamiques relatives au suivi des performances

Les vues du dictionnaire les plus notables sont : 

- `ALL_CATALOG` : tous les objets accessibles à l'utilisateur
- `USER_CATALOG` : tous les objets dont l'utilisateur à la propriété
- `ALL_COL_COMMENTS` : commentaires sur les attributs des objets
- `ALL_CONSTRAINTS`: Toutes les contraintes d'intégrité, y compris référentielles
- `ALL_TAB_PRIV`: droits sur les objets accessibles
- `ALL_USERS` : informations sur les utilisateurs de la base
- `USER_USERS` : informations sur l'utilisateur

## Les index

Un objet qui contient un entrée pour chaque valeur apparaissant dans une colonne indexée, pour un accès plus rapide.

Permet : 

- D'améliorer les performances de lecture
- Ne modifie pas les tables
- Ne modifie pas les requêtes

Pour créer un index explicitement : 

```sql
CREATE INDEX ...nom...
ON ...table...(...colonne...[,...colonne2...,];
```

> Les contraintes `PRIMARY KEY` et `UNIQUE` vont créer des index implicites

Créer un index ralenti la mise à jours mais accélère la lecture.

Les index sont utiles sur : 

- Les attributs utilisés comme critère de jointure
- Les attributs utilisés comme critère de sélection
- Les tables avec beaucoup d'enregistrements, quand la majorité des requêtes sélectionnent moins de 5% des lignes

Les index sont inutiles sur :

- Les attributs avec peu de valeurs différentes
- Les attributs souvent modifiés
- Les attributs souvent utilisés dans des expression
- Les petites tables

Index Bitmap : un type spécial d'index pour les colonnes qui n'ont pas beaucoup de valeurs différentes.

```sql
CREATE BITMAP ...nom...
ON ...table...(...colonne...[,...colonne2...,];
```

Les vues Oracle qui concernent les index sont : 

- `USER_INDEXES`
- `USER_IND_COLUMNS`
- `ALL_INDEXES`

## Les synonymes

Permet de créer un alias public d'un objet privé : 

```plsql
CREATE [PUBLIC] SYNONYM ...nom... FOR ...objet...;
```

## Les séquences

Objet qui permet de générer des entiers sans conflit / deadlocks. Utile pour faire des identifiant à auto incrément.

```plsql
CREATE SEQUENCE ...nom... [INCREMENT BY ...incrément...] [MAX VALUE ...max...] [MIN VALUE ...min...] CYCLE | NOCYCLE;
```

On peut autoriser une séquence à se répéter si elle atteint sa valeur max avec `CYCLE`;

On peut ensuite accèder dans un code PLSQL aux valeurs : 

- `NEXTVAL` : première ou suivante valeur
- `CURVAL` : valeur courante de la séquence 

## Les clusters

Les clusters sont des regroupements physiques de plusieurs tables autours d'attributs de jointures pour accélérer les performance. A utiliser seulement pour les jointures très fréquentes.

On utilise ces attributs comme clé de cluster.

Types : 

- Cluster à Index : indexation sur la clé du cluster
- Cluster à hachage : organisation suivante une fonction de hachage. Les enregistrement avec la même clé sont regroupés.

```plsql
CREATE CLUSTER ...nom... (...attribut... [,...attribut2...,])
INDEX | HASH IS ...nom cle hash...
```

## Comptes utilisateur

Créer un utilisateur : 

```plsql
CREATE USER JohnShepard
IDENTIFIED BY tali<3
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA 2M ON USERS
```

Modifier le mot de passe : 

```plsql
ALTER USER JohnShepard
IDENTIFIED BY normandy
```


# Structure Oracle

## Architecture d'une base de donnée

Le standard ANSI définit la structure suivante pour une base de donnée relationnelle : 

- Niveau conceptuel (logique)
- Niveau physique
- Niveau externe

Permet le séparation de la sémantique et de leur implémentation et montrer à chaque utilisateur que ce qu'il a le droit/besoin de voir.

La description de ces niveaux est faite à travers le dictionnaire de données.

## Structure logique 

### Tablespaces

Les *tablespaces*, *segments* et *extensions* permettent de définir comment la base est organisée physiquement. Ils sont le lien entre le niveau logique et le niveau physique de la base.

**Tablespace** : Unité logique qui compose la base. Regroupe un ensemble d'objets logique. Un objet logique est associé à un seul tablespace.

La base de donnée Oracle contient au moins un tablespace nommé `SYSTEM` qui contient le dictionnaire de données.

Créer un tablespace : 

```sql
CREATE TABLESPACE ...nom...
DATAFILE ...fichier..., [ ...fichier..., ]
[DEFAULT STORAGE (...spec storage...)]
[ONLINE | OFFLINE]
```

- `fichier` : emplacement du fichier sur le disque et taille
- `spec storage` : paramètre par défaut du storage pour les objets de ce tablespace
- `ONLINE` : actif dès sa création
- `OFFLINE` : inactif après la création

Exemple :

```sql
CREATE TABLESPACE ts_logistique
DATAFILE '/usr/base/fichier1.dbf' SIZE 1M, '/usr/base/fichier2' SIZE 1M
DEFAULT STORAGE (INITIAL 100K NEXT 100K MINEXTENT 1 MAXEXTENT 5 PCTINCREASE 50);
```

### Segment, Extension et Bloc

Quand un fichier est créé, Oracle prend tout l'espace mais le gère dynamiquement en fonction de l'utilisation de la base.

Trois niveaux de granularité.

#### Bloc

Niveau le plus fin, correspond à un nombre d'octets définit à la création de la base. Notion différents des blocs physiques utilisés par l'OS.

#### Extension

Suite de blocs continus alloués en même temps et stockant un type spécifique de donnée. Le comportement des extensions est défini par la clause `STORAGE` : 

- `INTIAL` : Taille de la première extension allouées quand un segment est créé
- `EXTENT` : Taille de la deuxième extension allouée au segment
- `MINEXTENT` : Nombre d'extensions allouées à la création du segment
- `MAXEXTENT` : Nombre total d'extensions pouvant être allouées à un segment
- `PCTINCREASE` : Pourcentage d'accroissement de la taille de l'extension suivante après la 2e.

La clause `STORAGE` pour être utilisée quand on crée des objets (tables, index, etc ...). Les paramètre de storage sur les objets remplacent ceux du tablespace.

En cas de changement des paramètres de storage, les changements sont appliqués aux extensions allouées dans le futur.

#### Segment

Ensemble d'extensions allouées à une structure logique. Il en existe cinq types : 

- DATA SEGMENT : données des tables / clusters. Chaque table à automatiquement un segment alloué à sa création.
- INDEX SEGMENT : index sur les colonnes. Chaque index à automatiquement un segment alloué à se création.
- ROLLBACK SEGMENT : Enregistre les actions effectuées par les transaction pour pouvoir les annuler.
- TEMPORARY SEGMENT : Utilisé pour les commandes qui ont besoin de stockage temporaire (ORDER BY, GROUP BY, SELECT DISTINCT, UNION, MINUS...)
- BOOTSTRAP SEGMENT : créé à la création de la base, définitions initiales du dictionnaire de données
	
## Structure Physique

Ensemble de fichiers gérés dynamiquement par Oracle : 

- Fichiers de données
- Fichiers de reprise
- Fichier de contrôle