PL / SQL - Guide rapide

Le langage de programmation PL / SQL a été développé par Oracle Corporation à la fin des années 1980 en tant que langage d'extension de procédure pour SQL et la base de données relationnelle Oracle. Voici certains faits notables sur PL / SQL -

PL / SQL est un langage de traitement transactionnel hautement portable et performant.
PL / SQL fournit un environnement de programmation intégré, interprété et indépendant du système d'exploitation.
PL / SQL peut également être appelé directement depuis la ligne de commande SQL*Plus interface.
Un appel direct peut également être effectué à partir d'appels de langage de programmation externe vers la base de données.
La syntaxe générale de PL / SQL est basée sur celle du langage de programmation ADA et Pascal.
Outre Oracle, PL / SQL est disponible en TimesTen in-memory database et IBM DB2.

Caractéristiques de PL / SQL

PL / SQL a les fonctionnalités suivantes -

PL / SQL est étroitement intégré à SQL.
Il offre une vérification complète des erreurs.
Il propose de nombreux types de données.
Il offre une variété de structures de programmation.
Il prend en charge la programmation structurée à travers des fonctions et des procédures.
Il prend en charge la programmation orientée objet.
Il prend en charge le développement d'applications Web et de pages serveur.

Avantages de PL / SQL

PL / SQL présente les avantages suivants -

SQL est le langage de base de données standard et PL / SQL est fortement intégré à SQL. PL / SQL prend en charge le SQL statique et dynamique. Static SQL prend en charge les opérations DML et le contrôle des transactions à partir du bloc PL / SQL. Dans Dynamic SQL, SQL permet d'incorporer des instructions DDL dans des blocs PL / SQL.
PL / SQL permet d'envoyer un bloc entier d'instructions à la base de données en une seule fois. Cela réduit le trafic réseau et offre des performances élevées pour les applications.
PL / SQL offre une productivité élevée aux programmeurs car il peut interroger, transformer et mettre à jour les données d'une base de données.
PL / SQL permet de gagner du temps sur la conception et le débogage grâce à des fonctionnalités puissantes, telles que la gestion des exceptions, l'encapsulation, le masquage des données et les types de données orientés objet.
Les applications écrites en PL / SQL sont entièrement portables.
PL / SQL offre un niveau de sécurité élevé.
PL / SQL permet d'accéder à des packages SQL prédéfinis.
PL / SQL prend en charge la programmation orientée objet.
PL / SQL prend en charge le développement d'applications Web et de pages serveur.

Dans ce chapitre, nous aborderons la configuration de l'environnement de PL / SQL. PL / SQL n'est pas un langage de programmation autonome; c'est un outil dans l'environnement de programmation Oracle.SQL* Plusest un outil interactif qui vous permet de taper des instructions SQL et PL / SQL à l'invite de commande. Ces commandes sont ensuite envoyées à la base de données pour traitement. Une fois les relevés traités, les résultats sont renvoyés et affichés à l'écran.

Pour exécuter des programmes PL / SQL, le serveur Oracle RDBMS doit être installé sur votre ordinateur. Cela s'occupera de l'exécution des commandes SQL. La version la plus récente d'Oracle RDBMS est 11g. Vous pouvez télécharger une version d'essai d'Oracle 11g à partir du lien suivant -

Télécharger Oracle 11g Express Edition

Vous devrez télécharger la version 32 bits ou 64 bits de l'installation selon votre système d'exploitation. Il y a généralement deux fichiers. Nous avons téléchargé la version 64 bits. Vous utiliserez également des étapes similaires sur votre système d'exploitation, peu importe qu'il s'agisse de Linux ou de Solaris.

win64_11gR2_database_1of2.zip
win64_11gR2_database_2of2.zip

Après avoir téléchargé les deux fichiers ci-dessus, vous devrez les décompresser dans un seul répertoire database et en dessous vous trouverez les sous-répertoires suivants -

Étape 1

Lançons maintenant Oracle Database Installer à l'aide du fichier d'installation. Voici le premier écran. Vous pouvez fournir votre identifiant de messagerie et cocher la case comme indiqué dans la capture d'écran suivante. Clique leNext bouton.

Étape 2

Vous serez dirigé vers l'écran suivant; décochez la case et cliquez sur leContinue bouton pour continuer.

Étape 3

Sélectionnez simplement la première option Create and Configure Database en utilisant le bouton radio et cliquez sur le Next bouton pour continuer.

Étape 4

Nous supposons que vous installez Oracle dans le but de base de l'apprentissage et que vous l'installez sur votre PC ou ordinateur portable. Ainsi, sélectionnez leDesktop Class et cliquez sur le Next bouton pour continuer.

Étape 5

Indiquez un emplacement dans lequel vous installerez le serveur Oracle. Modifiez simplement leOracle Baseet les autres emplacements seront définis automatiquement. Vous devrez également fournir un mot de passe; il sera utilisé par le DBA système. Une fois que vous avez fourni les informations requises, cliquez sur le boutonNext bouton pour continuer.

Étape 6

Encore une fois, cliquez sur le Next bouton pour continuer.

Étape 7

Clique le Finishbouton pour continuer; cela lancera l'installation réelle du serveur.

Étape 8

Cela prendra quelques instants, jusqu'à ce qu'Oracle commence à effectuer la configuration requise.

Étape 9

Ici, l'installation d'Oracle copiera les fichiers de configuration requis. Cela devrait prendre un moment -

Étape 10

Une fois les fichiers de la base de données copiés, vous aurez la boîte de dialogue suivante. Cliquez simplement surOK bouton et sortez.

Étape 11

Lors de l'installation, vous aurez la dernière fenêtre suivante.

Dernière étape

Il est maintenant temps de vérifier votre installation. À l'invite de commande, utilisez la commande suivante si vous utilisez Windows -

sqlplus "/ as sysdba"

Vous devriez avoir l'invite SQL où vous allez écrire vos commandes et scripts PL / SQL -

Éditeur de texte

L'exécution de programmes volumineux à partir de l'invite de commande peut vous amener à perdre par inadvertance une partie du travail. Il est toujours recommandé d'utiliser les fichiers de commandes. Pour utiliser les fichiers de commandes -

Tapez votre code dans un éditeur de texte, comme Notepad, Notepad+, ou EditPlus, etc.
Enregistrez le fichier avec le .sql extension dans le répertoire personnel.
Lancez le SQL*Plus command prompt à partir du répertoire dans lequel vous avez créé votre fichier PL / SQL.
Type @file_name à l'invite de commande SQL * Plus pour exécuter votre programme.

Si vous n'utilisez pas de fichier pour exécuter les scripts PL / SQL, copiez simplement votre code PL / SQL et faites un clic droit sur la fenêtre noire qui affiche l'invite SQL; Utilisez lepasteoption pour coller le code complet à l'invite de commande. Enfin, appuyez simplement surEnter pour exécuter le code, s'il n'est pas déjà exécuté.

Dans ce chapitre, nous discuterons de la syntaxe de base de PL / SQL qui est un block-structuredLangue; cela signifie que les programmes PL / SQL sont divisés et écrits en blocs logiques de code. Chaque bloc se compose de trois sous-parties -

S. Non	Sections et description
1	Declarations Cette section commence par le mot-clé DECLARE. C'est une section facultative et définit toutes les variables, curseurs, sous-programmes et autres éléments à utiliser dans le programme.
2	Executable Commands Cette section est incluse entre les mots-clés BEGIN et ENDet c'est une section obligatoire. Il se compose des instructions PL / SQL exécutables du programme. Il doit avoir au moins une ligne de code exécutable, qui peut être juste unNULL command pour indiquer que rien ne doit être exécuté.
3	Exception Handling Cette section commence par le mot-clé EXCEPTION. Cette section facultative contientexception(s) qui gèrent les erreurs dans le programme.

Chaque instruction PL / SQL se termine par un point-virgule (;). Les blocs PL / SQL peuvent être imbriqués dans d'autres blocs PL / SQL en utilisantBEGIN et END. Voici la structure de base d'un bloc PL / SQL -

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)>
EXCEPTION 
   <exception handling> 
END;

L'exemple 'Hello World'

DECLARE 
   message  varchar2(20):= 'Hello, World!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/

le end;La ligne signale la fin du bloc PL / SQL. Pour exécuter le code à partir de la ligne de commande SQL, vous devrez peut-être taper / au début de la première ligne vide après la dernière ligne du code. Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Hello World  

PL/SQL procedure successfully completed.

Les identifiants PL / SQL

Les identificateurs PL / SQL sont des constantes, des variables, des exceptions, des procédures, des curseurs et des mots réservés. Les identificateurs se composent d'une lettre éventuellement suivie de plusieurs lettres, chiffres, signes dollar, traits de soulignement et signes numériques et ne doivent pas dépasser 30 caractères.

Par défaut, identifiers are not case-sensitive. Ainsi, vous pouvez utiliserinteger ou INTEGERpour représenter une valeur numérique. Vous ne pouvez pas utiliser un mot-clé réservé comme identifiant.

Les délimiteurs PL / SQL

Un délimiteur est un symbole avec une signification particulière. Voici la liste des délimiteurs en PL / SQL -

Délimiteur	La description
*+, -, , /**	Addition, soustraction / négation, multiplication, division
%	Indicateur d'attribut
'	Délimiteur de chaîne de caractères
.	Sélecteur de composants
(,)	Expression ou délimiteur de liste
:	Indicateur de variable hôte
,	Séparateur d'articles
"	Délimiteur d'identifiant cité
=	Opérateur relationnel
@	Indicateur d'accès à distance
;	Terminateur de déclaration
:=	Opérateur d'assignation
=>	Opérateur d'association
\|\|	Opérateur de concaténation
**	Opérateur d'exponentiation
<<, >>	Délimiteur d'étiquette (début et fin)
*/, /*	Délimiteur de commentaire sur plusieurs lignes (début et fin)
--	Indicateur de commentaire sur une seule ligne
..	Opérateur de gamme
<, >, <=, >=	Opérateurs relationnels
<>, '=, ~=, ^=	Différentes versions de NOT EQUAL

Les commentaires PL / SQL

Les commentaires de programme sont des instructions explicatives qui peuvent être incluses dans le code PL / SQL que vous écrivez et aident quiconque à lire son code source. Tous les langages de programmation permettent une certaine forme de commentaires.

Le PL / SQL prend en charge les commentaires sur une seule ligne et sur plusieurs lignes. Tous les caractères disponibles dans n'importe quel commentaire sont ignorés par le compilateur PL / SQL. Les commentaires sur une seule ligne PL / SQL commencent par le délimiteur - (double tiret) et les commentaires sur plusieurs lignes sont encadrés par / * et * /.

DECLARE 
   -- variable declaration 
   message  varchar2(20):= 'Hello, World!'; 
BEGIN 
   /* 
   *  PL/SQL executable statement(s) 
   */ 
   dbms_output.put_line(message); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Hello World

PL/SQL procedure successfully completed.

Unités de programme PL / SQL

Une unité PL / SQL est l'une des suivantes -

Bloc PL / SQL
Function
Package
Corps du paquet
Procedure
Trigger
Type
Corps de type

Chacune de ces unités sera abordée dans les chapitres suivants.

Dans ce chapitre, nous aborderons les types de données en PL / SQL. Les variables, constantes et paramètres PL / SQL doivent avoir un type de données valide, qui spécifie un format de stockage, des contraintes et une plage de valeurs valide. Nous nous concentrerons sur leSCALAR et le LOBtypes de données dans ce chapitre. Les deux autres types de données seront traités dans d'autres chapitres.

S. Non	Description de la catégorie
1	Scalar Valeurs uniques sans composants internes, comme un NUMBER, DATE, ou BOOLEAN.
2	Large Object (LOB) Pointeurs vers des objets volumineux stockés séparément des autres éléments de données, tels que du texte, des images graphiques, des clips vidéo et des formes d'ondes sonores.
3	Composite Éléments de données dont les composants internes sont accessibles individuellement. Par exemple, les collections et les enregistrements.
4	Reference Pointeurs vers d'autres éléments de données.

Types et sous-types de données scalaires PL / SQL

Les types et sous-types de données scalaires PL / SQL appartiennent aux catégories suivantes -

S. Non	Type de date et description
1	Numeric Valeurs numériques sur lesquelles les opérations arithmétiques sont effectuées.
2	Character Valeurs alphanumériques qui représentent des caractères uniques ou des chaînes de caractères.
3	Boolean Valeurs logiques sur lesquelles les opérations logiques sont effectuées.
4	Datetime Dates et heures.

PL / SQL fournit des sous-types de types de données. Par exemple, le type de données NUMBER a un sous-type appelé INTEGER. Vous pouvez utiliser les sous-types de votre programme PL / SQL pour rendre les types de données compatibles avec les types de données d'autres programmes tout en incorporant le code PL / SQL dans un autre programme, tel qu'un programme Java.

Types et sous-types de données numériques PL / SQL

Le tableau suivant répertorie les types de données numériques prédéfinis PL / SQL et leurs sous-types -

S. Non	Type de données et description
1	PLS_INTEGER Entier signé compris entre -2 147 483 648 et 2 147 483 647, représenté en 32 bits
2	BINARY_INTEGER Entier signé compris entre -2 147 483 648 et 2 147 483 647, représenté en 32 bits
3	BINARY_FLOAT Nombre à virgule flottante au format IEEE 754 simple précision
4	BINARY_DOUBLE Nombre à virgule flottante au format IEEE 754 double précision
5	NUMBER(prec, scale) Nombre à virgule fixe ou à virgule flottante avec une valeur absolue comprise entre 1E-130 et (mais non compris) 1.0E126. Une variable NUMBER peut également représenter 0
6	DEC(prec, scale) Type à virgule fixe spécifique ANSI avec une précision maximale de 38 chiffres décimaux
sept	DECIMAL(prec, scale) Type à virgule fixe spécifique à IBM avec une précision maximale de 38 chiffres décimaux
8	NUMERIC(pre, secale) Type flottant avec une précision maximale de 38 chiffres décimaux
9	DOUBLE PRECISION Type à virgule flottante spécifique ANSI avec une précision maximale de 126 chiffres binaires (environ 38 chiffres décimaux)
dix	FLOAT Type à virgule flottante spécifique ANSI et IBM avec une précision maximale de 126 chiffres binaires (environ 38 chiffres décimaux)
11	INT Type d'entier spécifique ANSI avec une précision maximale de 38 chiffres décimaux
12	INTEGER Type d'entier spécifique ANSI et IBM avec une précision maximale de 38 chiffres décimaux
13	SMALLINT Type d'entier spécifique ANSI et IBM avec une précision maximale de 38 chiffres décimaux
14	REAL Type à virgule flottante avec une précision maximale de 63 chiffres binaires (environ 18 chiffres décimaux)

Voici une déclaration valide -

DECLARE 
   num1 INTEGER; 
   num2 REAL; 
   num3 DOUBLE PRECISION; 
BEGIN 
   null; 
END; 
/

Lorsque le code ci-dessus est compilé et exécuté, il produit le résultat suivant -

PL/SQL procedure successfully completed

Types de données et sous-types de caractères PL / SQL

Voici le détail des types de données de caractères prédéfinis PL / SQL et de leurs sous-types -

S. Non	Type de données et description
1	CHAR Chaîne de caractères de longueur fixe d'une taille maximale de 32 767 octets
2	VARCHAR2 Chaîne de caractères de longueur variable d'une taille maximale de 32 767 octets
3	RAW Chaîne binaire ou d'octets de longueur variable d'une taille maximale de 32 767 octets, non interprétée par PL / SQL
4	NCHAR Chaîne de caractères nationale de longueur fixe d'une taille maximale de 32767 octets
5	NVARCHAR2 Chaîne de caractères nationaux de longueur variable d'une taille maximale de 32 767 octets
6	LONG Chaîne de caractères de longueur variable d'une taille maximale de 32 760 octets
sept	LONG RAW Chaîne binaire ou d'octets de longueur variable d'une taille maximale de 32 760 octets, non interprétée par PL / SQL
8	ROWID Identificateur de ligne physique, l'adresse d'une ligne dans une table ordinaire
9	UROWID Identificateur de ligne universel (identifiant de ligne physique, logique ou étranger)

Types de données booléens PL / SQL

le BOOLEANle type de données stocke les valeurs logiques utilisées dans les opérations logiques. Les valeurs logiques sont les valeurs booléennesTRUE et FALSE et la valeur NULL.

Cependant, SQL n'a pas de type de données équivalent à BOOLEAN. Par conséquent, les valeurs booléennes ne peuvent pas être utilisées dans -

Instructions SQL
Fonctions SQL intégrées (telles que TO_CHAR)
Fonctions PL / SQL appelées à partir d'instructions SQL

Datetime PL / SQL et types d'intervalle

le DATELe type de données est utilisé pour stocker les heures de données de longueur fixe, qui incluent l'heure du jour en secondes depuis minuit. Les dates valides vont du 1er janvier 4712 avant JC au 31 décembre 9999 après JC.

Le format de date par défaut est défini par le paramètre d'initialisation Oracle NLS_DATE_FORMAT. Par exemple, la valeur par défaut peut être «JJ-MON-AA», qui comprend un numéro à deux chiffres pour le jour du mois, une abréviation du nom du mois et les deux derniers chiffres de l'année. Par exemple, 01-OCT-12.

Chaque DATE comprend le siècle, l'année, le mois, le jour, l'heure, la minute et la seconde. Le tableau suivant montre les valeurs valides pour chaque champ -

Nom de domaine	Valeurs Datetime valides	Valeurs d'intervalle valides
AN	-4712 à 9999 (hors année 0)	Tout entier différent de zéro
MOIS	01 à 12	0 à 11
JOURNÉE	01 à 31 (limité par les valeurs de MONTH et YEAR, selon les règles du calendrier pour les paramètres régionaux)	Tout entier différent de zéro
HEURE	00 à 23	0 à 23
MINUTE	00 à 59	0 à 59
SECONDE	00 à 59,9 (n), où 9 (n) est la précision des fractions de seconde	0 à 59,9 (n), où 9 (n) est la précision des fractions de seconde d'intervalle
TIMEZONE_HOUR	-12 à 14 (la plage s'adapte aux changements d'heure d'été)	N'est pas applicable
TIMEZONE_MINUTE	00 à 59	N'est pas applicable
TIMEZONE_REGION	Trouvé dans la vue dynamique des performances V $ TIMEZONE_NAMES	N'est pas applicable
TIMEZONE_ABBR	Trouvé dans la vue dynamique des performances V $ TIMEZONE_NAMES	N'est pas applicable

Types de données PL / SQL Large Object (LOB)

Les types de données LOB (Large Object) font référence à des éléments de données volumineux tels que du texte, des images graphiques, des clips vidéo et des formes d'ondes sonores. Les types de données LOB permettent un accès efficace, aléatoire et par morceaux à ces données. Voici les types de données PL / SQL LOB prédéfinis -

Type de données	La description	Taille
BFILE	Utilisé pour stocker des objets binaires volumineux dans des fichiers du système d'exploitation en dehors de la base de données.	Dépend du système. Ne peut pas dépasser 4 gigaoctets (Go).
GOUTTE	Utilisé pour stocker de gros objets binaires dans la base de données.	8 à 128 téraoctets (To)
CLOB	Utilisé pour stocker de gros blocs de données de caractères dans la base de données.	8 à 128 To
NCLOB	Utilisé pour stocker de gros blocs de données NCHAR dans la base de données.	8 à 128 To

Sous-types PL / SQL définis par l'utilisateur

Un sous-type est un sous-ensemble d'un autre type de données, appelé son type de base. Un sous-type a les mêmes opérations valides que son type de base, mais seulement un sous-ensemble de ses valeurs valides.

PL / SQL prédéfinit plusieurs sous-types dans le package STANDARD. Par exemple, PL / SQL prédéfinit les sous-typesCHARACTER et INTEGER comme suit -

SUBTYPE CHARACTER IS CHAR; 
SUBTYPE INTEGER IS NUMBER(38,0);

Vous pouvez définir et utiliser vos propres sous-types. Le programme suivant illustre la définition et l'utilisation d'un sous-type défini par l'utilisateur -

DECLARE 
   SUBTYPE name IS char(20); 
   SUBTYPE message IS varchar2(100); 
   salutation name; 
   greetings message; 
BEGIN 
   salutation := 'Reader '; 
   greetings := 'Welcome to the World of PL/SQL'; 
   dbms_output.put_line('Hello ' || salutation || greetings); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Hello Reader Welcome to the World of PL/SQL 
 
PL/SQL procedure successfully completed.

NULL en PL / SQL

Les valeurs PL / SQL NULL représentent missing ou unknown dataet ils ne sont pas un entier, un caractère ou tout autre type de données spécifique. Notez queNULL n'est pas la même chose qu'une chaîne de données vide ou la valeur de caractère nul '\0'. Un nul peut être attribué mais il ne peut être assimilé à rien, y compris lui-même.

Dans ce chapitre, nous aborderons les variables dans Pl / SQL. Une variable n'est rien d'autre qu'un nom donné à une zone de stockage que nos programmes peuvent manipuler. Chaque variable dans PL / SQL a un type de données spécifique, qui détermine la taille et la disposition de la mémoire de la variable; la plage de valeurs pouvant être stockées dans cette mémoire et l'ensemble des opérations pouvant être appliquées à la variable.

Le nom d'une variable PL / SQL se compose d'une lettre éventuellement suivie de plusieurs lettres, chiffres, signes dollar, traits de soulignement et signes numériques et ne doit pas dépasser 30 caractères. Par défaut, les noms de variables ne sont pas sensibles à la casse. Vous ne pouvez pas utiliser un mot clé PL / SQL réservé comme nom de variable.

Le langage de programmation PL / SQL permet de définir différents types de variables, tels que les types de données date-heure, les enregistrements, les collections, etc. que nous aborderons dans les chapitres suivants. Pour ce chapitre, étudions uniquement les types de variables de base.

Déclaration de variable en PL / SQL

Les variables PL / SQL doivent être déclarées dans la section déclaration ou dans un package en tant que variable globale. Lorsque vous déclarez une variable, PL / SQL alloue de la mémoire pour la valeur de la variable et l'emplacement de stockage est identifié par le nom de la variable.

La syntaxe pour déclarer une variable est -

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

Où, nom_variable est un identifiant valide en PL / SQL, le type de données doit être un type de données PL / SQL valide ou tout type de données défini par l'utilisateur dont nous avons déjà parlé dans le dernier chapitre. Certaines déclarations de variables valides ainsi que leur définition sont présentées ci-dessous -

sales number(10, 2); 
pi CONSTANT double precision := 3.1415; 
name varchar2(25); 
address varchar2(100);

Lorsque vous indiquez une taille, une échelle ou une limite de précision avec le type de données, cela s'appelle un constrained declaration. Les déclarations contraintes nécessitent moins de mémoire que les déclarations non contraintes. Par exemple -

sales number(10, 2); 
name varchar2(25); 
address varchar2(100);

Initialisation des variables dans PL / SQL

Chaque fois que vous déclarez une variable, PL / SQL lui attribue une valeur par défaut NULL. Si vous souhaitez initialiser une variable avec une valeur autre que la valeur NULL, vous pouvez le faire lors de la déclaration, en utilisant l'une des méthodes suivantes:

le DEFAULT mot-clé
le assignment opérateur

Par exemple -

counter binary_integer := 0; 
greetings varchar2(20) DEFAULT 'Have a Good Day';

Vous pouvez également spécifier qu'une variable ne doit pas avoir NULL valeur en utilisant le NOT NULLcontrainte. Si vous utilisez la contrainte NOT NULL, vous devez explicitement affecter une valeur initiale à cette variable.

C'est une bonne pratique de programmation d'initialiser correctement les variables, sinon les programmes produiraient parfois des résultats inattendus. Essayez l'exemple suivant qui utilise différents types de variables -

DECLARE 
   a integer := 10; 
   b integer := 20; 
   c integer; 
   f real; 
BEGIN 
   c := a + b; 
   dbms_output.put_line('Value of c: ' || c); 
   f := 70.0/3.0; 
   dbms_output.put_line('Value of f: ' || f); 
END; 
/

Lorsque le code ci-dessus est exécuté, il produit le résultat suivant -

Value of c: 30 
Value of f: 23.333333333333333333  

PL/SQL procedure successfully completed.

Portée variable en PL / SQL

PL / SQL permet l'imbrication de blocs, c'est-à-dire que chaque bloc de programme peut contenir un autre bloc interne. Si une variable est déclarée dans un bloc interne, elle n'est pas accessible au bloc externe. Cependant, si une variable est déclarée et accessible à un bloc externe, elle est également accessible à tous les blocs internes imbriqués. Il existe deux types de portée variable -

Local variables - Variables déclarées dans un bloc interne et non accessibles aux blocs externes.
Global variables - Variables déclarées dans le bloc le plus à l'extérieur ou dans un package.

L'exemple suivant montre l'utilisation de Local et Global variables sous sa forme simple -

DECLARE 
   -- Global variables  
   num1 number := 95;  
   num2 number := 85;  
BEGIN  
   dbms_output.put_line('Outer Variable num1: ' || num1); 
   dbms_output.put_line('Outer Variable num2: ' || num2); 
   DECLARE  
      -- Local variables 
      num1 number := 195;  
      num2 number := 185;  
   BEGIN  
      dbms_output.put_line('Inner Variable num1: ' || num1); 
      dbms_output.put_line('Inner Variable num2: ' || num2); 
   END;  
END; 
/

Lorsque le code ci-dessus est exécuté, il produit le résultat suivant -

Outer Variable num1: 95 
Outer Variable num2: 85 
Inner Variable num1: 195 
Inner Variable num2: 185  

PL/SQL procedure successfully completed.

Attribution de résultats de requête SQL à des variables PL / SQL

Vous pouvez utiliser le SELECT INTOinstruction SQL pour affecter des valeurs aux variables PL / SQL. Pour chaque élément duSELECT list, il doit y avoir une variable correspondante compatible avec le type dans le INTO list. L'exemple suivant illustre le concept. Créons une table nommée CUSTOMERS -

(For SQL statements, please refer to the SQL tutorial)

CREATE TABLE CUSTOMERS( 
   ID   INT NOT NULL, 
   NAME VARCHAR (20) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR (25), 
   SALARY   DECIMAL (18, 2),        
   PRIMARY KEY (ID) 
);  

Table Created

Insérons maintenant quelques valeurs dans le tableau -

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
  
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); 
 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

Le programme suivant affecte les valeurs du tableau ci-dessus aux variables PL / SQL à l'aide du SELECT INTO clause de SQL -

DECLARE 
   c_id customers.id%type := 1; 
   c_name  customers.name%type; 
   c_addr customers.address%type; 
   c_sal  customers.salary%type; 
BEGIN 
   SELECT name, address, salary INTO c_name, c_addr, c_sal 
   FROM customers 
   WHERE id = c_id;  
   dbms_output.put_line 
   ('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal); 
END; 
/

Lorsque le code ci-dessus est exécuté, il produit le résultat suivant -

Customer Ramesh from Ahmedabad earns 2000  

PL/SQL procedure completed successfully

Dans ce chapitre, nous discuterons constants et literalsen PL / SQL. Une constante contient une valeur qui, une fois déclarée, ne change pas dans le programme. Une déclaration de constante spécifie son nom, son type de données et sa valeur, et lui alloue du stockage. La déclaration peut également imposer leNOT NULL constraint.

Déclarer une constante

Une constante est déclarée en utilisant le CONSTANTmot-clé. Il nécessite une valeur initiale et ne permet pas de modifier cette valeur. Par exemple -

PI CONSTANT NUMBER := 3.141592654; 
DECLARE 
   -- constant declaration 
   pi constant number := 3.141592654; 
   -- other declarations 
   radius number(5,2);  
   dia number(5,2);  
   circumference number(7, 2); 
   area number (10, 2); 
BEGIN  
   -- processing 
   radius := 9.5;  
   dia := radius * 2;  
   circumference := 2.0 * pi * radius; 
   area := pi * radius * radius; 
   -- output 
   dbms_output.put_line('Radius: ' || radius); 
   dbms_output.put_line('Diameter: ' || dia); 
   dbms_output.put_line('Circumference: ' || circumference); 
   dbms_output.put_line('Area: ' || area); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Radius: 9.5 
Diameter: 19 
Circumference: 59.69 
Area: 283.53  

Pl/SQL procedure successfully completed.

Les littéraux PL / SQL

Un littéral est une valeur numérique, caractère, chaîne ou booléenne explicite non représentée par un identificateur. Par exemple, TRUE, 786, NULL, 'tutorialspoint' sont tous des littéraux de type Boolean, number ou string. PL / SQL, les littéraux sont sensibles à la casse. PL / SQL prend en charge les types de littéraux suivants -

Littéraux numériques
Littéraux de caractères
Littéraux de chaîne
Littéraux BOOLÉEN
Littéraux de date et d'heure

Le tableau suivant fournit des exemples de toutes ces catégories de valeurs littérales.

S. Non	Type littéral et exemple
1	Numeric Literals 050 78-14 0 +32767 6,6667 0,0 -12,0 3,14159 + 7800,00 6E5 1.0E-8 3.14159e0 -1E38 -9.5e-3
2	Character Literals 'A' '%' '9' '' 'z' '('
3	String Literals 'Bonjour le monde!' 'Tutoriels Point' '19 -NOV-12 '
4	BOOLEAN Literals TRUE, FALSE et NULL.
5	Date and Time Literals DATE «1978-12-25»; TIMESTAMP '2012-10-29 12:01:01';

Pour incorporer des guillemets simples dans une chaîne littérale, placez deux guillemets simples l'un à côté de l'autre comme indiqué dans le programme suivant -

DECLARE 
   message  varchar2(30):= 'That''s tutorialspoint.com!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

That's tutorialspoint.com!  

PL/SQL procedure successfully completed.

Dans ce chapitre, nous aborderons les opérateurs en PL / SQL. Un opérateur est un symbole qui indique au compilateur d'effectuer une manipulation mathématique ou logique spécifique. Le langage PL / SQL est riche en opérateurs intégrés et fournit les types d'opérateurs suivants -

Opérateurs arithmétiques
Opérateurs relationnels
Opérateurs de comparaison
Opérateurs logiques
Opérateurs de chaîne

Ici, nous allons comprendre les opérateurs arithmétique, relationnel, de comparaison et logique un par un. Les opérateurs String seront discutés dans un chapitre ultérieur -PL/SQL - Strings.

Opérateurs arithmétiques

Le tableau suivant montre tous les opérateurs arithmétiques pris en charge par PL / SQL. Supposonsvariable A détient 10 et variable B détient 5, alors -

Afficher des exemples

Opérateur	La description	Exemple
+	Ajoute deux opérandes	A + B donnera 15
-	Soustrait le deuxième opérande du premier	A - B donnera 5
*	Multiplie les deux opérandes	A * B donnera 50
/	Divise le numérateur par le dénumérateur	A / B donnera 2
**	Opérateur d'exponentiation, élève un opérande à la puissance d'un autre	A ** B donnera 100000

Opérateurs relationnels

Les opérateurs relationnels comparent deux expressions ou valeurs et renvoient un résultat booléen. Le tableau suivant montre tous les opérateurs relationnels pris en charge par PL / SQL. Supposonsvariable A détient 10 et variable B détient 20, alors -

Afficher des exemples

Opérateur	La description	Exemple
=	Vérifie si les valeurs de deux opérandes sont égales ou non, si oui, la condition devient vraie.	(A = B) n'est pas vrai.
! = <> ~ =	Vérifie si les valeurs de deux opérandes sont égales ou non, si les valeurs ne sont pas égales, la condition devient vraie.	(A! = B) est vrai.
>	Vérifie si la valeur de l'opérande gauche est supérieure à la valeur de l'opérande droit, si oui, la condition devient vraie.	(A> B) n'est pas vrai.
<	Vérifie si la valeur de l'opérande gauche est inférieure à la valeur de l'opérande droit, si oui, la condition devient vraie.	(A <B) est vrai.
> =	Vérifie si la valeur de l'opérande gauche est supérieure ou égale à la valeur de l'opérande droit, si oui, la condition devient vraie.	(A> = B) n'est pas vrai.
<=	Vérifie si la valeur de l'opérande gauche est inférieure ou égale à la valeur de l'opérande droit, si oui, la condition devient vraie.	(A <= B) est vrai

Opérateurs de comparaison

Les opérateurs de comparaison sont utilisés pour comparer une expression à une autre. Le résultat est toujours soitTRUE, FALSE ou NULL.

Afficher des exemples

Opérateur	La description	Exemple
COMME	L'opérateur LIKE compare un caractère, une chaîne ou une valeur CLOB à un modèle et renvoie TRUE si la valeur correspond au modèle et FALSE si ce n'est pas le cas.	Si 'Zara Ali' comme 'Z% A_i' renvoie un booléen vrai, alors que 'Nuha Ali' comme 'Z% A_i' renvoie un booléen faux.
ENTRE	L'opérateur BETWEEN teste si une valeur se trouve dans une plage spécifiée. x ENTRE a ET b signifie que x> = a et x <= b.	Si x = 10 alors, x entre 5 et 20 renvoie vrai, x entre 5 et 10 renvoie vrai, mais x entre 11 et 20 renvoie faux.
DANS	L'opérateur IN teste l'appartenance à l'ensemble. x IN (ensemble) signifie que x est égal à n'importe quel membre de l'ensemble.	Si x = 'm' alors, x in ('a', 'b', 'c') renvoie Boolean false mais x in ('m', 'n', 'o') renvoie Boolean true.
EST NULL	L'opérateur IS NULL renvoie la valeur BOOLEAN TRUE si son opérande est NULL ou FALSE s'il n'est pas NULL. Les comparaisons impliquant des valeurs NULL donnent toujours NULL.	Si x = 'm', alors 'x is null' renvoie Boolean false.

Opérateurs logiques

Le tableau suivant montre les opérateurs logiques pris en charge par PL / SQL. Tous ces opérateurs travaillent sur des opérandes booléens et produisent des résultats booléens. Supposonsvariable A est vrai et variable B est faux, alors -

Afficher des exemples

Opérateur	La description	Exemples
et	Appelé l'opérateur logique AND. Si les deux opérandes sont vrais, la condition devient vraie.	(A et B) est faux.
ou	Appelé l'opérateur logique OR. Si l'un des deux opérandes est vrai, la condition devient vraie.	(A ou B) est vrai.
ne pas	Appelé l'opérateur logique NOT. Utilisé pour inverser l'état logique de son opérande. Si une condition est vraie, l'opérateur NOT logique la rendra fausse.	not (A et B) est vrai.

Priorité des opérateurs PL / SQL

La priorité des opérateurs détermine le regroupement des termes dans une expression. Cela affecte la manière dont une expression est évaluée. Certains opérateurs ont une priorité plus élevée que d'autres; par exemple, l'opérateur de multiplication a une priorité plus élevée que l'opérateur d'addition.

Par exemple, x = 7 + 3 * 2; ici,x est assigné 13, pas 20 car l'opérateur * a une priorité plus élevée que +, il est donc d'abord multiplié par 3*2 puis ajoute dans 7.

Ici, les opérateurs avec la priorité la plus élevée apparaissent en haut du tableau, ceux avec la priorité la plus basse apparaissent en bas. Dans une expression, les opérateurs de priorité supérieure seront évalués en premier.

La priorité des opérateurs est la suivante: =, <,>, <=,> =, <>,! =, ~ =, ^ =, IS NULL, LIKE, BETWEEN, IN.

Afficher des exemples

Opérateur	Opération
**	exponentiation
+, -	identité, négation
*, /	multiplication, division
+, -, \|\|	addition, soustraction, concaténation
Comparaison
NE PAS	négation logique
ET	conjonction
OU	inclusion

Dans ce chapitre, nous discuterons des conditions en PL / SQL. Les structures de prise de décision exigent que le programmeur spécifie une ou plusieurs conditions à évaluer ou tester par le programme, ainsi qu'une ou plusieurs instructions à exécuter si la condition est jugée vraie, et éventuellement d'autres instructions à exécuter si le condition est considérée comme fausse.

Voici la forme générale d'une structure conditionnelle typique (c'est-à-dire prise de décision) trouvée dans la plupart des langages de programmation -

Le langage de programmation PL / SQL fournit les types suivants d'énoncés de prise de décision. Cliquez sur les liens suivants pour vérifier leurs détails.

S. Non	Déclaration et description
1	Instruction IF - THEN le IF statement associe une condition à une séquence d'instructions entourées par les mots-clés THEN et END IF. Si la condition est vraie, les instructions sont exécutées et si la condition est fausse ou NULL, l'instruction IF ne fait rien.
2	Instruction IF-THEN-ELSE IF statement ajoute le mot-clé ELSEsuivi d'une séquence alternative d'énoncés. Si la condition est fausse ou NULL, alors seule la séquence alternative d'instructions est exécutée. Il garantit que l'une ou l'autre des séquences d'instructions est exécutée.
3	Instruction IF-THEN-ELSIF Il vous permet de choisir entre plusieurs alternatives.
4	Déclaration de cas Comme l'instruction IF, le CASE statement sélectionne une séquence d'instructions à exécuter. Cependant, pour sélectionner la séquence, l'instruction CASE utilise un sélecteur plutôt que plusieurs expressions booléennes. Un sélecteur est une expression dont la valeur est utilisée pour sélectionner l'une des nombreuses alternatives.
5	Instruction CASE recherchée L'instruction CASE recherchée has no selector, et ses clauses WHEN contiennent des conditions de recherche qui donnent des valeurs booléennes.
6	imbriqué IF-THEN-ELSE Vous pouvez en utiliser un IF-THEN ou IF-THEN-ELSIF déclaration dans un autre IF-THEN ou IF-THEN-ELSIF déclaration (s).

Dans ce chapitre, nous aborderons les boucles en PL / SQL. Il peut arriver que vous deviez exécuter un bloc de code plusieurs fois. En général, les instructions sont exécutées de manière séquentielle: la première instruction d'une fonction est exécutée en premier, suivie de la seconde, et ainsi de suite.

Les langages de programmation fournissent diverses structures de contrôle qui permettent des chemins d'exécution plus compliqués.

Une instruction de boucle nous permet d'exécuter une instruction ou un groupe d'instructions plusieurs fois et voici la forme générale d'une instruction de boucle dans la plupart des langages de programmation -

PL / SQL fournit les types de boucle suivants pour gérer les exigences de bouclage. Cliquez sur les liens suivants pour vérifier leurs détails.

S. Non	Type de boucle et description
1	BOUCLE de base PL / SQL Dans cette structure de boucle, la séquence d'instructions est incluse entre les instructions LOOP et END LOOP. A chaque itération, la séquence d'instructions est exécutée puis le contrôle reprend en haut de la boucle.
2	PL / SQL EN BOUCLE Répète une instruction ou un groupe d'instructions tant qu'une condition donnée est vraie. Il teste la condition avant d'exécuter le corps de la boucle.
3	PL / SQL POUR BOUCLE Exécute une séquence d'instructions plusieurs fois et abrége le code qui gère la variable de boucle.
4	Boucles imbriquées dans PL / SQL Vous pouvez utiliser une ou plusieurs boucles à l'intérieur de n'importe quelle autre boucle de base, while ou for.

Étiquetage d'une boucle PL / SQL

Les boucles PL / SQL peuvent être étiquetées. L'étiquette doit être entourée de doubles crochets (<< et >>) et apparaître au début de l'instruction LOOP. Le nom de l'étiquette peut également apparaître à la fin de l'instruction LOOP. Vous pouvez utiliser l'étiquette dans l'instruction EXIT pour quitter la boucle.

Le programme suivant illustre le concept -

DECLARE 
   i number(1); 
   j number(1); 
BEGIN 
   << outer_loop >> 
   FOR i IN 1..3 LOOP 
      << inner_loop >> 
      FOR j IN 1..3 LOOP 
         dbms_output.put_line('i is: '|| i || ' and j is: ' || j); 
      END loop inner_loop; 
   END loop outer_loop; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

i is: 1 and j is: 1 
i is: 1 and j is: 2 
i is: 1 and j is: 3 
i is: 2 and j is: 1 
i is: 2 and j is: 2 
i is: 2 and j is: 3 
i is: 3 and j is: 1 
i is: 3 and j is: 2 
i is: 3 and j is: 3  

PL/SQL procedure successfully completed.

Les instructions de contrôle de boucle

Les instructions de contrôle de boucle modifient l'exécution de sa séquence normale. Lorsque l'exécution quitte une étendue, tous les objets automatiques qui ont été créés dans cette étendue sont détruits.

PL / SQL prend en charge les instructions de contrôle suivantes. Les boucles d'étiquetage aident également à prendre le contrôle en dehors d'une boucle. Cliquez sur les liens suivants pour vérifier leurs détails.

S. Non	Déclaration de contrôle et description
1	Instruction EXIT L'instruction Exit termine la boucle et le contrôle passe à l'instruction immédiatement après END LOOP.
2	Instruction CONTINUE Force la boucle à ignorer le reste de son corps et à retester immédiatement son état avant de réitérer.
3	Déclaration GOTO Transfère le contrôle vers l'instruction étiquetée. Bien qu'il ne soit pas conseillé d'utiliser l'instruction GOTO dans votre programme.

La chaîne en PL / SQL est en fait une séquence de caractères avec une spécification de taille facultative. Les caractères peuvent être numériques, lettres, blancs, caractères spéciaux ou une combinaison de tous. PL / SQL propose trois types de chaînes -

Fixed-length strings- Dans de telles chaînes, les programmeurs spécifient la longueur lors de la déclaration de la chaîne. La chaîne est complétée à droite avec des espaces à la longueur ainsi spécifiée.
Variable-length strings - Dans de telles chaînes, une longueur maximale jusqu'à 32 767, pour la chaîne est spécifiée et aucun remplissage n'a lieu.
Character large objects (CLOBs) - Ce sont des chaînes de longueur variable pouvant aller jusqu'à 128 téraoctets.

Les chaînes PL / SQL peuvent être des variables ou des littéraux. Un littéral de chaîne est placé entre guillemets. Par exemple,

'This is a string literal.' Or 'hello world'

Pour inclure un guillemet simple dans une chaîne littérale, vous devez taper deux guillemets simples l'un à côté de l'autre. Par exemple,

'this isn''t what it looks like'

Déclaration de variables de chaîne

La base de données Oracle fournit de nombreux types de données de type chaîne, tels que CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB et NCLOB. Les types de données précédés d'un'N' sont 'national character set' les types de données, qui stockent les données de caractères Unicode.

Si vous devez déclarer une chaîne de longueur variable, vous devez fournir la longueur maximale de cette chaîne. Par exemple, le type de données VARCHAR2. L'exemple suivant illustre la déclaration et l'utilisation de certaines variables de chaîne -

DECLARE 
   name varchar2(20); 
   company varchar2(30); 
   introduction clob; 
   choice char(1); 
BEGIN 
   name := 'John Smith'; 
   company := 'Infotech'; 
   introduction := ' Hello! I''m John Smith from Infotech.'; 
   choice := 'y'; 
   IF choice = 'y' THEN 
      dbms_output.put_line(name); 
      dbms_output.put_line(company); 
      dbms_output.put_line(introduction); 
   END IF; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

John Smith 
Infotech
Hello! I'm John Smith from Infotech.  

PL/SQL procedure successfully completed

Pour déclarer une chaîne de longueur fixe, utilisez le type de données CHAR. Ici, il n'est pas nécessaire de spécifier une longueur maximale pour une variable de longueur fixe. Si vous omettez la contrainte de longueur, Oracle Database utilise automatiquement une longueur maximale requise. Les deux déclarations suivantes sont identiques -

red_flag CHAR(1) := 'Y'; 
 red_flag CHAR   := 'Y';

Fonctions et opérateurs de chaînes PL / SQL

PL / SQL propose l'opérateur de concaténation (||)pour joindre deux chaînes. Le tableau suivant fournit les fonctions de chaîne fournies par PL / SQL -

S. Non	Fonction et objectif
1	ASCII(x); Renvoie la valeur ASCII du caractère x.
2	CHR(x); Renvoie le caractère avec la valeur ASCII de x.
3	CONCAT(x, y); Concatène les chaînes x et y et renvoie la chaîne ajoutée.
4	INITCAP(x); Convertit la lettre initiale de chaque mot en x en majuscule et renvoie cette chaîne.
5	INSTR(x, find_string [, start] [, occurrence]); Recherches pour find_string en x et renvoie la position à laquelle il se produit.
6	INSTRB(x); Renvoie l'emplacement d'une chaîne dans une autre chaîne, mais renvoie la valeur en octets.
sept	LENGTH(x); Renvoie le nombre de caractères de x.
8	LENGTHB(x); Renvoie la longueur d'une chaîne de caractères en octets pour un jeu de caractères à un octet.
9	LOWER(x); Convertit les lettres en x en minuscules et renvoie cette chaîne.
dix	LPAD(x, width [, pad_string]) ; Tampons x avec des espaces à gauche, pour amener la longueur totale de la chaîne à la largeur des caractères.
11	LTRIM(x [, trim_string]); Coupe les caractères à gauche de x.
12	NANVL(x, value); Renvoie la valeur si x correspond à la valeur spéciale NaN (pas un nombre), sinon x est retourné.
13	NLS_INITCAP(x); Identique à la fonction INITCAP, sauf qu'elle peut utiliser une méthode de tri différente de celle spécifiée par NLSSORT.
14	NLS_LOWER(x) ; Identique à la fonction LOWER, sauf qu'elle peut utiliser une méthode de tri différente, comme spécifié par NLSSORT.
15	NLS_UPPER(x); Identique à la fonction UPPER, sauf qu'elle peut utiliser une méthode de tri différente, comme spécifié par NLSSORT.
16	NLSSORT(x); Modifie la méthode de tri des caractères. Doit être spécifié avant toute fonction NLS; sinon, le tri par défaut sera utilisé.
17	NVL(x, value); Renvoie la valeur si xest nul; sinon, x est renvoyé.
18	NVL2(x, value1, value2); Renvoie valeur1 si x n'est pas nul; si x est nul, valeur2 est renvoyée.
19	REPLACE(x, search_string, replace_string); Recherches x pour search_string et le remplace par replace_string.
20	RPAD(x, width [, pad_string]); Tampons x à droite.
21	RTRIM(x [, trim_string]); Trims x de la droite.
22	SOUNDEX(x) ; Renvoie une chaîne contenant la représentation phonétique de x.
23	SUBSTR(x, start [, length]); Renvoie une sous-chaîne de xqui commence à la position spécifiée par start. Une longueur facultative pour la sous-chaîne peut être fournie.
24	SUBSTRB(x); Identique à SUBSTR sauf que les paramètres sont exprimés en octets au lieu de caractères pour les systèmes de caractères à un octet.
25	TRIM([trim_char FROM) x); Coupe les caractères à gauche et à droite de x.
26	UPPER(x); Convertit les lettres en x en majuscules et renvoie cette chaîne.

Travaillons maintenant sur quelques exemples pour comprendre le concept -

Exemple 1

DECLARE 
   greetings varchar2(11) := 'hello world'; 
BEGIN 
   dbms_output.put_line(UPPER(greetings)); 
    
   dbms_output.put_line(LOWER(greetings)); 
    
   dbms_output.put_line(INITCAP(greetings)); 
    
   /* retrieve the first character in the string */ 
   dbms_output.put_line ( SUBSTR (greetings, 1, 1)); 
    
   /* retrieve the last character in the string */ 
   dbms_output.put_line ( SUBSTR (greetings, -1, 1)); 
    
   /* retrieve five characters,  
      starting from the seventh position. */ 
   dbms_output.put_line ( SUBSTR (greetings, 7, 5)); 
    
   /* retrieve the remainder of the string, 
      starting from the second position. */ 
   dbms_output.put_line ( SUBSTR (greetings, 2)); 
     
   /* find the location of the first "e" */ 
   dbms_output.put_line ( INSTR (greetings, 'e')); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

HELLO WORLD 
hello world 
Hello World 
h 
d 
World 
ello World 
2  

PL/SQL procedure successfully completed.

Exemple 2

DECLARE 
   greetings varchar2(30) := '......Hello World.....'; 
BEGIN 
   dbms_output.put_line(RTRIM(greetings,'.')); 
   dbms_output.put_line(LTRIM(greetings, '.')); 
   dbms_output.put_line(TRIM( '.' from greetings)); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

......Hello World  
Hello World..... 
Hello World  

PL/SQL procedure successfully completed.

Dans ce chapitre, nous aborderons les tableaux en PL / SQL. Le langage de programmation PL / SQL fournit une structure de données appeléeVARRAY, qui peut stocker une collection séquentielle de taille fixe d'éléments du même type. Un varray est utilisé pour stocker une collection ordonnée de données, mais il est souvent préférable de considérer un tableau comme une collection de variables du même type.

Toutes les varrays sont constituées d'emplacements de mémoire contigus. L'adresse la plus basse correspond au premier élément et l'adresse la plus élevée au dernier élément.

Un tableau fait partie des données de type collection et représente des tableaux de taille variable. Nous étudierons d'autres types de collections dans un chapitre ultérieur'PL/SQL Collections'.

Chaque élément d'un varraya un index qui lui est associé. Il a également une taille maximale qui peut être modifiée dynamiquement.

Création d'un type de varray

Un type varray est créé avec le CREATE TYPEdéclaration. Vous devez spécifier la taille maximale et le type d'éléments stockés dans le varray.

La syntaxe de base pour créer un type VARRAY au niveau du schéma est -

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>

Où,

varray_type_name est un nom d'attribut valide,
n est le nombre d'éléments (maximum) dans le varray,
element_type est le type de données des éléments du tableau.

La taille maximale d'un varray peut être modifiée à l'aide du ALTER TYPE déclaration.

Par exemple,

CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10); 
/ 

Type created.

La syntaxe de base pour créer un type VARRAY dans un bloc PL / SQL est -

TYPE varray_type_name IS VARRAY(n) of <element_type>

Par exemple -

TYPE namearray IS VARRAY(5) OF VARCHAR2(10); 
Type grades IS VARRAY(5) OF INTEGER;

Travaillons maintenant sur quelques exemples pour comprendre le concept -

Exemple 1

Le programme suivant illustre l'utilisation de varrays -

DECLARE 
   type namesarray IS VARRAY(5) OF VARCHAR2(10); 
   type grades IS VARRAY(5) OF INTEGER; 
   names namesarray; 
   marks grades; 
   total integer; 
BEGIN 
   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   total := names.count; 
   dbms_output.put_line('Total '|| total || ' Students'); 
   FOR i in 1 .. total LOOP 
      dbms_output.put_line('Student: ' || names(i) || ' 
      Marks: ' || marks(i)); 
   END LOOP; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Total 5 Students 
Student: Kavita  Marks: 98 
Student: Pritam  Marks: 97 
Student: Ayan  Marks: 78 
Student: Rishav  Marks: 87 
Student: Aziz  Marks: 92 

PL/SQL procedure successfully completed.

Please note -

Dans l'environnement Oracle, l'index de départ des varrays est toujours 1.
Vous pouvez initialiser les éléments varray à l'aide de la méthode constructeur du type varray, qui porte le même nom que varray.
Les varrays sont des tableaux unidimensionnels.
Un varray est automatiquement NULL lorsqu'il est déclaré et doit être initialisé avant que ses éléments puissent être référencés.

Exemple 2

Les éléments d'un varray peuvent également être un% ROWTYPE de n'importe quelle table de base de données ou% TYPE de n'importe quel champ de table de base de données. L'exemple suivant illustre le concept.

Nous utiliserons la table CUSTOMERS stockée dans notre base de données comme -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

L'exemple suivant utilise cursor, que vous étudierez en détail dans un chapitre séparé.

DECLARE 
   CURSOR c_customers is 
   SELECT  name FROM customers; 
   type c_list is varray (6) of customers.name%type; 
   name_list c_list := c_list(); 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter + 1; 
      name_list.extend; 
      name_list(counter)  := n.name; 
      dbms_output.put_line('Customer('||counter ||'):'||name_list(counter)); 
   END LOOP; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed.

Dans ce chapitre, nous aborderons les procédures en PL / SQL. UNEsubprogramest une unité / module de programme qui exécute une tâche particulière. Ces sous-programmes sont combinés pour former des programmes plus vastes. C'est ce qu'on appelle fondamentalement la «conception modulaire». Un sous-programme peut être appelé par un autre sous-programme ou programme appelécalling program.

Un sous-programme peut être créé -

Au niveau du schéma
À l'intérieur d'un colis
À l'intérieur d'un bloc PL / SQL

Au niveau du schéma, le sous-programme est un standalone subprogram. Il est créé avec l'instruction CREATE PROCEDURE ou CREATE FUNCTION. Il est stocké dans la base de données et peut être supprimé avec l'instruction DROP PROCEDURE ou DROP FUNCTION.

Un sous-programme créé à l'intérieur d'un package est un packaged subprogram. Il est stocké dans la base de données et ne peut être supprimé que lorsque le package est supprimé avec l'instruction DROP PACKAGE. Nous discuterons des packages dans le chapitre'PL/SQL - Packages'.

Les sous-programmes PL / SQL sont nommés des blocs PL / SQL qui peuvent être appelés avec un ensemble de paramètres. PL / SQL fournit deux types de sous-programmes -

Functions- Ces sous-programmes renvoient une valeur unique; principalement utilisé pour calculer et renvoyer une valeur.
Procedures- Ces sous-programmes ne renvoient pas directement de valeur; principalement utilisé pour effectuer une action.

Ce chapitre couvrira des aspects importants d'une PL/SQL procedure. Nous discuteronsPL/SQL function dans le chapitre suivant.

Parties d'un sous-programme PL / SQL

Chaque sous-programme PL / SQL a un nom et peut également avoir une liste de paramètres. Comme les blocs PL / SQL anonymes, les blocs nommés auront également les trois parties suivantes -

S. Non	Pièces et description
1	Declarative Part C'est une partie optionnelle. Cependant, la partie déclarative d'un sous-programme ne commence pas par le mot clé DECLARE. Il contient des déclarations de types, curseurs, constantes, variables, exceptions et sous-programmes imbriqués. Ces éléments sont locaux au sous-programme et cessent d'exister lorsque le sous-programme termine l'exécution.
2	Executable Part Il s'agit d'une partie obligatoire et contient des instructions qui exécutent l'action désignée.
3	Exception-handling C'est encore une partie optionnelle. Il contient le code qui gère les erreurs d'exécution.

Créer une procédure

Une procédure est créée avec le CREATE OR REPLACE PROCEDUREdéclaration. La syntaxe simplifiée de l'instruction CREATE OR REPLACE PROCEDURE est la suivante:

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name;

Où,

nom-procédure spécifie le nom de la procédure.
L'option [OR REPLACE] permet de modifier une procédure existante.
La liste des paramètres facultatifs contient le nom, le mode et les types des paramètres. IN représente la valeur qui sera transmise de l'extérieur et OUT représente le paramètre qui sera utilisé pour renvoyer une valeur en dehors de la procédure.
procedure-body contient la partie exécutable.
Le mot clé AS est utilisé à la place du mot clé IS pour créer une procédure autonome.

Exemple

L'exemple suivant crée une procédure simple qui affiche la chaîne "Hello World!" sur l'écran lors de l'exécution.

CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
   dbms_output.put_line('Hello World!'); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'aide de l'invite SQL, il produira le résultat suivant -

Procedure created.

Exécution d'une procédure autonome

Une procédure autonome peut être appelée de deux manières:

En utilisant le EXECUTE mot-clé
Appel du nom de la procédure à partir d'un bloc PL / SQL

La procédure ci-dessus nommée 'greetings' peut être appelé avec le mot-clé EXECUTE comme -

EXECUTE greetings;

L'appel ci-dessus affichera -

Hello World

PL/SQL procedure successfully completed.

La procédure peut également être appelée depuis un autre bloc PL / SQL -

BEGIN 
   greetings; 
END; 
/

L'appel ci-dessus affichera -

Hello World  

PL/SQL procedure successfully completed.

Suppression d'une procédure autonome

Une procédure autonome est supprimée avec le DROP PROCEDUREdéclaration. La syntaxe pour supprimer une procédure est -

DROP PROCEDURE procedure-name;

Vous pouvez supprimer la procédure de message d'accueil en utilisant l'instruction suivante -

DROP PROCEDURE greetings;

Modes de paramètres dans les sous-programmes PL / SQL

Le tableau suivant répertorie les modes de paramètres dans les sous-programmes PL / SQL -

S. Non	Mode de paramètre et description
1	IN Un paramètre IN vous permet de transmettre une valeur au sous-programme. It is a read-only parameter. À l'intérieur du sous-programme, un paramètre IN agit comme une constante. Il ne peut pas recevoir de valeur. Vous pouvez transmettre une constante, un littéral, une variable initialisée ou une expression en tant que paramètre IN. Vous pouvez également l'initialiser à une valeur par défaut; cependant, dans ce cas, il est omis de l'appel de sous-programme.It is the default mode of parameter passing. Parameters are passed by reference.
2	OUT Un paramètre OUT renvoie une valeur au programme appelant. À l'intérieur du sous-programme, un paramètre OUT agit comme une variable. Vous pouvez modifier sa valeur et référencer la valeur après l'avoir affectée.The actual parameter must be variable and it is passed by value.
3	IN OUT Un IN OUTLe paramètre transmet une valeur initiale à un sous-programme et renvoie une valeur mise à jour à l'appelant. Une valeur peut lui être attribuée et la valeur peut être lue. Le paramètre réel correspondant à un paramètre formel IN OUT doit être une variable et non une constante ou une expression. Une valeur doit être affectée au paramètre formel.Actual parameter is passed by value.

Exemple de mode IN & OUT 1

Ce programme trouve le minimum de deux valeurs. Ici, la procédure prend deux nombres en utilisant le mode IN et renvoie leur minimum en utilisant les paramètres OUT.

DECLARE 
   a number; 
   b number; 
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS 
BEGIN 
   IF x < y THEN 
      z:= x; 
   ELSE 
      z:= y; 
   END IF; 
END;   
BEGIN 
   a:= 23; 
   b:= 45; 
   findMin(a, b, c); 
   dbms_output.put_line(' Minimum of (23, 45) : ' || c); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Minimum of (23, 45) : 23  

PL/SQL procedure successfully completed.

Exemple de mode IN & OUT 2

Cette procédure calcule le carré de la valeur d'une valeur passée. Cet exemple montre comment utiliser le même paramètre pour accepter une valeur, puis renvoyer un autre résultat.

DECLARE 
   a number; 
PROCEDURE squareNum(x IN OUT number) IS 
BEGIN 
  x := x * x; 
END;  
BEGIN 
   a:= 23; 
   squareNum(a); 
   dbms_output.put_line(' Square of (23): ' || a); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Square of (23): 529 

PL/SQL procedure successfully completed.

Méthodes de transmission des paramètres

Les paramètres réels peuvent être transmis de trois manières -

Notation positionnelle
Notation nommée
Notation mixte

Notation positionnelle

En notation positionnelle, vous pouvez appeler la procédure comme -

findMin(a, b, c, d);

En notation positionnelle, le premier paramètre réel est remplacé par le premier paramètre formel; le deuxième paramètre réel est remplacé par le deuxième paramètre formel, et ainsi de suite. Alors,a se substitue à x, b se substitue à y, c se substitue à z et d se substitue à m.

Notation nommée

Dans la notation nommée, le paramètre réel est associé au paramètre formel à l'aide de la arrow symbol ( => ). L'appel de procédure sera comme suit -

findMin(x => a, y => b, z => c, m => d);

Notation mixte

En notation mixte, vous pouvez mélanger les deux notations dans l'appel de procédure; cependant, la notation positionnelle doit précéder la notation nommée.

L'appel suivant est légal -

findMin(a, b, c, m => d);

Cependant, ce n'est pas légal:

findMin(x => a, b, c, d);

Dans ce chapitre, nous aborderons les fonctions en PL / SQL. Une fonction est identique à une procédure, sauf qu'elle renvoie une valeur. Par conséquent, toutes les discussions du chapitre précédent sont également valables pour les fonctions.

Créer une fonction

Une fonction autonome est créée à l'aide du CREATE FUNCTIONdéclaration. La syntaxe simplifiée pour leCREATE OR REPLACE PROCEDURE la déclaration est la suivante -

CREATE [OR REPLACE] FUNCTION function_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
RETURN return_datatype 
{IS | AS} 
BEGIN 
   < function_body > 
END [function_name];

Où,

nom-fonction spécifie le nom de la fonction.
L'option [OR REPLACE] permet la modification d'une fonction existante.
La liste des paramètres facultatifs contient le nom, le mode et les types des paramètres. IN représente la valeur qui sera transmise de l'extérieur et OUT représente le paramètre qui sera utilisé pour renvoyer une valeur en dehors de la procédure.
La fonction doit contenir un return déclaration.
La clause RETURN spécifie le type de données que vous allez renvoyer à partir de la fonction.
function-body contient la partie exécutable.
Le mot clé AS est utilisé à la place du mot clé IS pour créer une fonction autonome.

Exemple

L'exemple suivant illustre comment créer et appeler une fonction autonome. Cette fonction renvoie le nombre total de CLIENTS dans la table des clients.

Nous utiliserons la table CUSTOMERS, que nous avions créée dans le chapitre Variables PL / SQL -

Select * from customers; 
 
+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

CREATE OR REPLACE FUNCTION totalCustomers 
RETURN number IS 
   total number(2) := 0; 
BEGIN 
   SELECT count(*) into total 
   FROM customers; 
    
   RETURN total; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'aide de l'invite SQL, il produira le résultat suivant -

Function created.

Appeler une fonction

Lors de la création d'une fonction, vous donnez une définition de ce que la fonction doit faire. Pour utiliser une fonction, vous devrez appeler cette fonction pour exécuter la tâche définie. Lorsqu'un programme appelle une fonction, le contrôle du programme est transféré à la fonction appelée.

Une fonction appelée exécute la tâche définie et lorsque son instruction return est exécutée ou lorsque le last end statement est atteint, il ramène le contrôle du programme au programme principal.

Pour appeler une fonction, il vous suffit de transmettre les paramètres requis avec le nom de la fonction et si la fonction renvoie une valeur, vous pouvez stocker la valeur renvoyée. Le programme suivant appelle la fonctiontotalCustomers à partir d'un bloc anonyme -

DECLARE 
   c number(2); 
BEGIN 
   c := totalCustomers(); 
   dbms_output.put_line('Total no. of Customers: ' || c); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Total no. of Customers: 6  

PL/SQL procedure successfully completed.

Exemple

L'exemple suivant illustre la déclaration, la définition et l'appel d'une fonction PL / SQL simple qui calcule et renvoie le maximum de deux valeurs.

DECLARE 
   a number; 
   b number; 
   c number; 
FUNCTION findMax(x IN number, y IN number)  
RETURN number 
IS 
    z number; 
BEGIN 
   IF x > y THEN 
      z:= x; 
   ELSE 
      Z:= y; 
   END IF;  
   RETURN z; 
END; 
BEGIN 
   a:= 23; 
   b:= 45;  
   c := findMax(a, b); 
   dbms_output.put_line(' Maximum of (23,45): ' || c); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Maximum of (23,45): 45   

PL/SQL procedure successfully completed.

Fonctions récursives PL / SQL

Nous avons vu qu'un programme ou sous-programme peut appeler un autre sous-programme. Lorsqu'un sous-programme s'appelle lui-même, il est appelé appel récursif et le processus est appelérecursion.

Pour illustrer le concept, calculons la factorielle d'un nombre. La factorielle d'un nombre n est définie comme -

n! = n*(n-1)! 
   = n*(n-1)*(n-2)! 
      ... 
   = n*(n-1)*(n-2)*(n-3)... 1

Le programme suivant calcule la factorielle d'un nombre donné en s'appelant récursivement -

DECLARE 
   num number; 
   factorial number;  
   
FUNCTION fact(x number) 
RETURN number  
IS 
   f number; 
BEGIN 
   IF x=0 THEN 
      f := 1; 
   ELSE 
      f := x * fact(x-1); 
   END IF; 
RETURN f; 
END;  

BEGIN 
   num:= 6; 
   factorial := fact(num); 
   dbms_output.put_line(' Factorial '|| num || ' is ' || factorial); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Factorial 6 is 720 
  
PL/SQL procedure successfully completed.

Dans ce chapitre, nous aborderons les curseurs en PL / SQL. Oracle crée une zone de mémoire, appelée zone de contexte, pour le traitement d'une instruction SQL, qui contient toutes les informations nécessaires au traitement de l'instruction; par exemple, le nombre de lignes traitées, etc.

UNE cursorest un pointeur vers cette zone de contexte. PL / SQL contrôle la zone de contexte via un curseur. Un curseur contient les lignes (une ou plusieurs) renvoyées par une instruction SQL. L'ensemble de lignes que contient le curseur est appeléactive set.

Vous pouvez nommer un curseur afin qu'il puisse être référencé dans un programme pour récupérer et traiter les lignes renvoyées par l'instruction SQL, une à la fois. Il existe deux types de curseurs -

Curseurs implicites
Curseurs explicites

Curseurs implicites

Les curseurs implicites sont automatiquement créés par Oracle chaque fois qu'une instruction SQL est exécutée, lorsqu'il n'y a pas de curseur explicite pour l'instruction. Les programmeurs ne peuvent pas contrôler les curseurs implicites et les informations qu'ils contiennent.

Chaque fois qu'une instruction DML (INSERT, UPDATE et DELETE) est émise, un curseur implicite est associé à cette instruction. Pour les opérations INSERT, le curseur contient les données qui doivent être insérées. Pour les opérations UPDATE et DELETE, le curseur identifie les lignes qui seraient affectées.

En PL / SQL, vous pouvez faire référence au curseur implicite le plus récent comme SQL cursor, qui a toujours des attributs tels que %FOUND, %ISOPEN, %NOTFOUND, et %ROWCOUNT. Le curseur SQL a des attributs supplémentaires,%BULK_ROWCOUNT et %BULK_EXCEPTIONS, conçu pour être utilisé avec le FORALLdéclaration. Le tableau suivant fournit la description des attributs les plus utilisés -

S. Non	Attribut et description
1	%FOUND Renvoie TRUE si une instruction INSERT, UPDATE ou DELETE affectait une ou plusieurs lignes ou si une instruction SELECT INTO renvoyait une ou plusieurs lignes. Sinon, il renvoie FALSE.
2	%NOTFOUND Le contraire logique de% FOUND. Elle renvoie TRUE si une instruction INSERT, UPDATE ou DELETE n'a affecté aucune ligne ou si une instruction SELECT INTO n'a renvoyé aucune ligne. Sinon, il renvoie FALSE.
3	%ISOPEN Renvoie toujours FALSE pour les curseurs implicites, car Oracle ferme automatiquement le curseur SQL après avoir exécuté son instruction SQL associée.
4	%ROWCOUNT Renvoie le nombre de lignes affectées par une instruction INSERT, UPDATE ou DELETE, ou renvoyées par une instruction SELECT INTO.

Tout attribut de curseur SQL sera accessible comme sql%attribute_name comme indiqué ci-dessous dans l'exemple.

Exemple

Nous utiliserons la table CUSTOMERS que nous avons créée et utilisée dans les chapitres précédents.

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

Le programme suivant mettra à jour le tableau et augmentera le salaire de chaque client de 500 et utilisera le SQL%ROWCOUNT attribut pour déterminer le nombre de lignes affectées -

DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE customers 
   SET salary = salary + 500; 
   IF sql%notfound THEN 
      dbms_output.put_line('no customers selected'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected '); 
   END IF;  
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

6 customers selected  

PL/SQL procedure successfully completed.

Si vous vérifiez les enregistrements dans la table des clients, vous constaterez que les lignes ont été mises à jour -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2500.00 | 
|  2 | Khilan   |  25 | Delhi     |  2000.00 | 
|  3 | kaushik  |  23 | Kota      |  2500.00 | 
|  4 | Chaitali |  25 | Mumbai    |  7000.00 | 
|  5 | Hardik   |  27 | Bhopal    |  9000.00 | 
|  6 | Komal    |  22 | MP        |  5000.00 | 
+----+----------+-----+-----------+----------+

Curseurs explicites

Les curseurs explicites sont des curseurs définis par le programmeur pour obtenir plus de contrôle sur le context area. Un curseur explicite doit être défini dans la section déclaration du bloc PL / SQL. Il est créé sur une instruction SELECT qui renvoie plus d'une ligne.

La syntaxe pour créer un curseur explicite est -

CURSOR cursor_name IS select_statement;

Travailler avec un curseur explicite comprend les étapes suivantes -

Déclaration du curseur d'initialisation de la mémoire
Ouverture du curseur pour allouer la mémoire
Récupération du curseur pour récupérer les données
Fermer le curseur pour libérer la mémoire allouée

Déclaration du curseur

La déclaration du curseur définit le curseur avec un nom et l'instruction SELECT associée. Par exemple -

CURSOR c_customers IS 
   SELECT id, name, address FROM customers;

Ouverture du curseur

L'ouverture du curseur alloue la mémoire pour le curseur et le prépare à récupérer les lignes renvoyées par l'instruction SQL. Par exemple, nous allons ouvrir le curseur défini ci-dessus comme suit -

OPEN c_customers;

Récupération du curseur

La récupération du curseur implique d'accéder à une ligne à la fois. Par exemple, nous allons récupérer les lignes du curseur ouvert ci-dessus comme suit -

FETCH c_customers INTO c_id, c_name, c_addr;

Fermer le curseur

Fermer le curseur signifie libérer la mémoire allouée. Par exemple, nous fermerons le curseur ouvert ci-dessus comme suit -

CLOSE c_customers;

Exemple

Voici un exemple complet pour illustrer les concepts de curseurs explicites & minua;

DECLARE 
   c_id customers.id%type; 
   c_name customer.name%type; 
   c_addr customers.address%type; 
   CURSOR c_customers is 
      SELECT id, name, address FROM customers; 
BEGIN 
   OPEN c_customers; 
   LOOP 
   FETCH c_customers into c_id, c_name, c_addr; 
      EXIT WHEN c_customers%notfound; 
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 
   END LOOP; 
   CLOSE c_customers; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

1 Ramesh Ahmedabad  
2 Khilan Delhi  
3 kaushik Kota     
4 Chaitali Mumbai  
5 Hardik Bhopal   
6 Komal MP  
  
PL/SQL procedure successfully completed.

Dans ce chapitre, nous aborderons les enregistrements en PL / SQL. UNErecordest une structure de données qui peut contenir des éléments de données de différents types. Les enregistrements se composent de différents champs, similaires à une ligne d'une table de base de données.

Par exemple, vous souhaitez garder une trace de vos livres dans une bibliothèque. Vous souhaiterez peut-être suivre les attributs suivants pour chaque livre, tels que le titre, l'auteur, le sujet, l'ID du livre. Un enregistrement contenant un champ pour chacun de ces éléments permet de traiter un LIVRE comme une unité logique et vous permet d'organiser et de mieux représenter ses informations.

PL / SQL peut gérer les types d'enregistrements suivants -

Table-based
Enregistrements basés sur le curseur
Enregistrements définis par l'utilisateur

Enregistrements basés sur des tables

L'attribut% ROWTYPE permet à un programmeur de créer table-based et cursorbased records.

L'exemple suivant illustre le concept de table-basedrecords. Nous utiliserons la table CUSTOMERS que nous avons créée et utilisée dans les chapitres précédents -

DECLARE 
   customer_rec customers%rowtype; 
BEGIN 
   SELECT * into customer_rec 
   FROM customers 
   WHERE id = 5;  
   dbms_output.put_line('Customer ID: ' || customer_rec.id); 
   dbms_output.put_line('Customer Name: ' || customer_rec.name); 
   dbms_output.put_line('Customer Address: ' || customer_rec.address); 
   dbms_output.put_line('Customer Salary: ' || customer_rec.salary); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Customer ID: 5 
Customer Name: Hardik 
Customer Address: Bhopal 
Customer Salary: 9000 
 
PL/SQL procedure successfully completed.

Enregistrements basés sur le curseur

L'exemple suivant illustre le concept de cursor-basedrecords. Nous utiliserons la table CUSTOMERS que nous avons créée et utilisée dans les chapitres précédents -

DECLARE 
   CURSOR customer_cur is 
      SELECT id, name, address  
      FROM customers; 
   customer_rec customer_cur%rowtype; 
BEGIN 
   OPEN customer_cur; 
   LOOP 
      FETCH customer_cur into customer_rec; 
      EXIT WHEN customer_cur%notfound; 
      DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name); 
   END LOOP; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

1 Ramesh 
2 Khilan 
3 kaushik 
4 Chaitali 
5 Hardik 
6 Komal  

PL/SQL procedure successfully completed.

Enregistrements définis par l'utilisateur

PL / SQL fournit un type d'enregistrement défini par l'utilisateur qui vous permet de définir les différentes structures d'enregistrement. Ces enregistrements se composent de différents champs. Supposons que vous souhaitiez garder une trace de vos livres dans une bibliothèque. Vous souhaiterez peut-être suivre les attributs suivants pour chaque livre -

Title
Author
Subject
ID du livre

Définition d'un enregistrement

Le type d'enregistrement est défini comme -

TYPE 
type_name IS RECORD 
  ( field_name1  datatype1  [NOT NULL]  [:= DEFAULT EXPRESSION], 
   field_name2   datatype2   [NOT NULL]  [:= DEFAULT EXPRESSION], 
   ... 
   field_nameN  datatypeN  [NOT NULL]  [:= DEFAULT EXPRESSION); 
record-name  type_name;

L'enregistrement du livre est déclaré de la manière suivante -

DECLARE 
TYPE books IS RECORD 
(title  varchar(50), 
   author  varchar(50), 
   subject varchar(100), 
   book_id   number); 
book1 books; 
book2 books;

Accès aux champs

Pour accéder à n'importe quel champ d'un enregistrement, nous utilisons le point (.)opérateur. L'opérateur d'accès aux membres est codé comme un point entre le nom de la variable d'enregistrement et le champ auquel nous souhaitons accéder. Voici un exemple pour expliquer l'utilisation de record -

DECLARE 
   type books is record 
      (title varchar(50), 
      author varchar(50), 
      subject varchar(100), 
      book_id number); 
   book1 books; 
   book2 books; 
BEGIN 
   -- Book 1 specification 
   book1.title  := 'C Programming'; 
   book1.author := 'Nuha Ali ';  
   book1.subject := 'C Programming Tutorial'; 
   book1.book_id := 6495407;  
   -- Book 2 specification 
   book2.title := 'Telecom Billing'; 
   book2.author := 'Zara Ali'; 
   book2.subject := 'Telecom Billing Tutorial'; 
   book2.book_id := 6495700;  
  
  -- Print book 1 record 
   dbms_output.put_line('Book 1 title : '|| book1.title); 
   dbms_output.put_line('Book 1 author : '|| book1.author); 
   dbms_output.put_line('Book 1 subject : '|| book1.subject); 
   dbms_output.put_line('Book 1 book_id : ' || book1.book_id); 
   
   -- Print book 2 record 
   dbms_output.put_line('Book 2 title : '|| book2.title); 
   dbms_output.put_line('Book 2 author : '|| book2.author); 
   dbms_output.put_line('Book 2 subject : '|| book2.subject); 
   dbms_output.put_line('Book 2 book_id : '|| book2.book_id); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Book 1 title : C Programming 
Book 1 author : Nuha Ali 
Book 1 subject : C Programming Tutorial 
Book 1 book_id : 6495407 
Book 2 title : Telecom Billing 
Book 2 author : Zara Ali 
Book 2 subject : Telecom Billing Tutorial 
Book 2 book_id : 6495700  

PL/SQL procedure successfully completed.

Enregistrements en tant que paramètres de sous-programme

Vous pouvez transmettre un enregistrement en tant que paramètre de sous-programme tout comme vous passez n'importe quelle autre variable. Vous pouvez également accéder aux champs d'enregistrement de la même manière que vous avez accédé dans l'exemple ci-dessus -

DECLARE 
   type books is record 
      (title  varchar(50), 
      author  varchar(50), 
      subject varchar(100), 
      book_id   number); 
   book1 books; 
   book2 books;  
PROCEDURE printbook (book books) IS 
BEGIN 
   dbms_output.put_line ('Book  title :  ' || book.title); 
   dbms_output.put_line('Book  author : ' || book.author); 
   dbms_output.put_line( 'Book  subject : ' || book.subject); 
   dbms_output.put_line( 'Book book_id : ' || book.book_id); 
END; 
   
BEGIN 
   -- Book 1 specification 
   book1.title  := 'C Programming'; 
   book1.author := 'Nuha Ali ';  
   book1.subject := 'C Programming Tutorial'; 
   book1.book_id := 6495407;
   
   -- Book 2 specification 
   book2.title := 'Telecom Billing'; 
   book2.author := 'Zara Ali'; 
   book2.subject := 'Telecom Billing Tutorial'; 
   book2.book_id := 6495700;  
   
   -- Use procedure to print book info 
   printbook(book1); 
   printbook(book2); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Book  title : C Programming 
Book  author : Nuha Ali 
Book subject : C Programming Tutorial 
Book  book_id : 6495407 
Book title : Telecom Billing 
Book author : Zara Ali 
Book subject : Telecom Billing Tutorial 
Book book_id : 6495700  

PL/SQL procedure successfully completed.

Dans ce chapitre, nous aborderons les exceptions en PL / SQL. Une exception est une condition d'erreur lors de l'exécution d'un programme. PL / SQL aide les programmeurs à détecter ces conditions en utilisantEXCEPTIONbloc dans le programme et une action appropriée est prise contre la condition d'erreur. Il existe deux types d'exceptions -

Exceptions définies par le système
Exceptions définies par l'utilisateur

Syntaxe pour la gestion des exceptions

La syntaxe générale de la gestion des exceptions est la suivante. Ici, vous pouvez lister autant d'exceptions que vous pouvez gérer. L'exception par défaut sera gérée en utilisantWHEN others THEN -

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)> 
EXCEPTION 
   <exception handling goes here > 
   WHEN exception1 THEN  
      exception1-handling-statements  
   WHEN exception2  THEN  
      exception2-handling-statements  
   WHEN exception3 THEN  
      exception3-handling-statements 
   ........ 
   WHEN others THEN 
      exception3-handling-statements 
END;

Exemple

Écrivons un code pour illustrer le concept. Nous utiliserons la table CUSTOMERS que nous avons créée et utilisée dans les chapitres précédents -

DECLARE 
   c_id customers.id%type := 8; 
   c_name customerS.Name%type; 
   c_addr customers.address%type; 
BEGIN 
   SELECT  name, address INTO  c_name, c_addr 
   FROM customers 
   WHERE id = c_id;  
   DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name); 
   DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 

EXCEPTION 
   WHEN no_data_found THEN 
      dbms_output.put_line('No such customer!'); 
   WHEN others THEN 
      dbms_output.put_line('Error!'); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

No such customer!  

PL/SQL procedure successfully completed.

Le programme ci-dessus affiche le nom et l'adresse d'un client dont l'ID est donné. Puisqu'il n'y a pas de client avec la valeur d'ID 8 dans notre base de données, le programme lève l'exception d'exécutionNO_DATA_FOUND, qui est capturé dans le EXCEPTION block.

Lever des exceptions

Les exceptions sont déclenchées automatiquement par le serveur de base de données chaque fois qu'il y a une erreur de base de données interne, mais les exceptions peuvent être déclenchées explicitement par le programmeur à l'aide de la commande RAISE. Voici la syntaxe simple pour lever une exception -

DECLARE 
   exception_name EXCEPTION; 
BEGIN 
   IF condition THEN 
      RAISE exception_name; 
   END IF; 
EXCEPTION 
   WHEN exception_name THEN 
   statement; 
END;

Vous pouvez utiliser la syntaxe ci-dessus pour déclencher l'exception standard Oracle ou toute exception définie par l'utilisateur. Dans la section suivante, nous vous donnerons un exemple sur la levée d'une exception définie par l'utilisateur. Vous pouvez lever les exceptions standard Oracle de la même manière.

Exceptions définies par l'utilisateur

PL / SQL vous permet de définir vos propres exceptions en fonction des besoins de votre programme. Une exception définie par l'utilisateur doit être déclarée puis déclenchée explicitement, à l'aide d'une instruction RAISE ou de la procédureDBMS_STANDARD.RAISE_APPLICATION_ERROR.

La syntaxe pour déclarer une exception est -

DECLARE 
   my-exception EXCEPTION;

Exemple

L'exemple suivant illustre le concept. Ce programme demande un identifiant client, lorsque l'utilisateur entre un identifiant invalide, l'exceptioninvalid_id est soulevé.

DECLARE 
   c_id customers.id%type := &cc_id; 
   c_name customerS.Name%type; 
   c_addr customers.address%type;  
   -- user defined exception 
   ex_invalid_id  EXCEPTION; 
BEGIN 
   IF c_id <= 0 THEN 
      RAISE ex_invalid_id; 
   ELSE 
      SELECT  name, address INTO  c_name, c_addr 
      FROM customers 
      WHERE id = c_id;
      DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);  
      DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 
   END IF; 

EXCEPTION 
   WHEN ex_invalid_id THEN 
      dbms_output.put_line('ID must be greater than zero!'); 
   WHEN no_data_found THEN 
      dbms_output.put_line('No such customer!'); 
   WHEN others THEN 
      dbms_output.put_line('Error!');  
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Enter value for cc_id: -6 (let's enter a value -6) 
old  2: c_id customers.id%type := &cc_id; 
new  2: c_id customers.id%type := -6; 
ID must be greater than zero! 
 
PL/SQL procedure successfully completed.

Exceptions prédéfinies

PL / SQL fournit de nombreuses exceptions prédéfinies, qui sont exécutées lorsqu'une règle de base de données est violée par un programme. Par exemple, l'exception prédéfinie NO_DATA_FOUND est déclenchée lorsqu'une instruction SELECT INTO ne renvoie aucune ligne. Le tableau suivant répertorie quelques-unes des exceptions prédéfinies importantes -

Exception	Erreur Oracle	SQLCODE	La description
ACCESS_INTO_NULL	06530	-6530	Il est déclenché lorsqu'un objet nul reçoit automatiquement une valeur.
CASE_NOT_FOUND	06592	-6592	Il est déclenché lorsqu'aucun des choix de la clause WHEN d'une instruction CASE n'est sélectionné et qu'il n'y a pas de clause ELSE.
COLLECTION_IS_NULL	06531	-6531	Il est déclenché lorsqu'un programme tente d'appliquer des méthodes de collecte autres que EXISTS à une table imbriquée ou varray non initialisée, ou lorsque le programme tente d'attribuer des valeurs aux éléments d'une table imbriquée ou varray non initialisée.
DUP_VAL_ON_INDEX	00001	-1	Il est déclenché lorsque des valeurs en double sont tentées d'être stockées dans une colonne avec un index unique.
INVALID_CURSOR	01001	-1001	Il est déclenché lorsque des tentatives sont effectuées pour effectuer une opération de curseur non autorisée, comme la fermeture d'un curseur non ouvert.
NUMÉRO INVALIDE	01722	-1722	Il est déclenché lorsque la conversion d'une chaîne de caractères en un nombre échoue car la chaîne ne représente pas un nombre valide.
LOGIN_DENIED	01017	-1017	Il est déclenché lorsqu'un programme tente de se connecter à la base de données avec un nom d'utilisateur ou un mot de passe non valide.
AUCUNE DONNÉE DISPONIBLE	01403	+100	Il est déclenché lorsqu'une instruction SELECT INTO ne renvoie aucune ligne.
NOT_LOGGED_ON	01012	-1012	Il est déclenché lorsqu'un appel de base de données est émis sans être connecté à la base de données.
PROGRAM_ERROR	06501	-6501	Il est déclenché lorsque PL / SQL a un problème interne.
ROWTYPE_MISMATCH	06504	-6504	Il est déclenché lorsqu'un curseur récupère une valeur dans une variable de type de données incompatible.
SELF_IS_NULL	30625	-30625	Il est déclenché lorsqu'une méthode membre est appelée, mais que l'instance du type d'objet n'a pas été initialisée.
STORAGE_ERROR	06500	-6500	Il est déclenché lorsque PL / SQL a manqué de mémoire ou que la mémoire a été corrompue.
TOO_MANY_ROWS	01422	-1422	Il est déclenché lorsqu'une instruction SELECT INTO renvoie plus d'une ligne.
VALUE_ERROR	06502	-6502	Il est déclenché lorsqu'une erreur d'arithmétique, de conversion, de troncature ou de contrainte de taille se produit.
ZERO_DIVIDE	01476	1476	Il est déclenché lorsqu'une tentative est faite pour diviser un nombre par zéro.

Dans ce chapitre, nous aborderons les déclencheurs en PL / SQL. Les déclencheurs sont des programmes stockés, qui sont automatiquement exécutés ou déclenchés lorsque certains événements se produisent. Les déclencheurs sont, en fait, écrits pour être exécutés en réponse à l'un des événements suivants -

UNE database manipulation (DML) instruction (DELETE, INSERT ou UPDATE)
UNE database definition (DDL) instruction (CREATE, ALTER ou DROP).
UNE database operation (SERVERERROR, LOGON, LOGOFF, STARTUP ou SHUTDOWN).

Les déclencheurs peuvent être définis sur la table, la vue, le schéma ou la base de données à laquelle l'événement est associé.

Avantages des déclencheurs

Les déclencheurs peuvent être écrits aux fins suivantes -

Générer automatiquement des valeurs de colonne dérivées
Faire respecter l'intégrité référentielle
Journalisation des événements et stockage des informations sur l'accès aux tables
Auditing
Réplication synchrone des tables
Imposer des autorisations de sécurité
Empêcher les transactions invalides

Créer des déclencheurs

La syntaxe pour créer un déclencheur est -

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END;

Où,

CREATE [OR REPLACE] TRIGGER trigger_name - Crée ou remplace un déclencheur existant par le trigger_name .
{AVANT | APRÈS | INSTEAD OF} - Ceci spécifie quand le déclencheur sera exécuté. La clause INSTEAD OF est utilisée pour créer un déclencheur sur une vue.
{INSÉRER [OU] | MISE À JOUR [OU] | DELETE} - Ceci spécifie l'opération DML.
[OF col_name] - Ceci spécifie le nom de la colonne qui sera mis à jour.
[ON nom_table] - Ceci spécifie le nom de la table associée au déclencheur.
[REFERENCING OLD AS o NEW AS n] - Cela vous permet de faire référence à des valeurs nouvelles et anciennes pour diverses instructions DML, telles que INSERT, UPDATE et DELETE.
[POUR CHAQUE RANGÉE] - Ceci spécifie un déclencheur au niveau de la ligne, c'est-à-dire que le déclencheur sera exécuté pour chaque ligne affectée. Sinon, le déclencheur ne s'exécutera qu'une seule fois lorsque l'instruction SQL est exécutée, ce qui est appelé un déclencheur de niveau table.
WHEN (condition) - Ceci fournit une condition pour les lignes pour lesquelles le déclencheur serait déclenché. Cette clause n'est valide que pour les déclencheurs de niveau ligne.

Exemple

Pour commencer, nous utiliserons la table CUSTOMERS que nous avons créée et utilisée dans les chapitres précédents -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

Le programme suivant crée un row-leveldéclencheur pour la table clients qui se déclencherait pour les opérations INSERT ou UPDATE ou DELETE effectuées sur la table CUSTOMERS. Ce déclencheur affichera la différence de salaire entre les anciennes valeurs et les nouvelles valeurs -

CREATE OR REPLACE TRIGGER display_salary_changes 
BEFORE DELETE OR INSERT OR UPDATE ON customers 
FOR EACH ROW 
WHEN (NEW.ID > 0) 
DECLARE 
   sal_diff number; 
BEGIN 
   sal_diff := :NEW.salary  - :OLD.salary; 
   dbms_output.put_line('Old salary: ' || :OLD.salary); 
   dbms_output.put_line('New salary: ' || :NEW.salary); 
   dbms_output.put_line('Salary difference: ' || sal_diff); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Trigger created.

Les points suivants doivent être considérés ici -

Les références ANCIENNES et NOUVELLES ne sont pas disponibles pour les déclencheurs de niveau table, mais vous pouvez les utiliser pour les déclencheurs de niveau enregistrement.
Si vous souhaitez interroger la table dans le même déclencheur, vous devez utiliser le mot-clé AFTER, car les déclencheurs peuvent interroger la table ou la modifier à nouveau uniquement après que les modifications initiales ont été appliquées et que la table est de retour dans un état cohérent.
Le déclencheur ci-dessus a été écrit de telle manière qu'il se déclenchera avant toute opération DELETE, INSERT ou UPDATE sur la table, mais vous pouvez écrire votre déclencheur sur une ou plusieurs opérations, par exemple BEFORE DELETE, qui se déclenchera chaque fois qu'un enregistrement sera supprimé à l'aide de l'opération DELETE sur la table.

Déclencher un déclencheur

Faisons quelques opérations DML sur la table CUSTOMERS. Voici une instruction INSERT, qui créera un nouvel enregistrement dans la table -

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

Lorsqu'un enregistrement est créé dans la table CUSTOMERS, le déclencheur de création ci-dessus, display_salary_changes sera déclenché et affichera le résultat suivant -

Old salary: 
New salary: 7500 
Salary difference:

Comme il s'agit d'un nouvel enregistrement, l'ancien salaire n'est pas disponible et le résultat ci-dessus est nul. Exécutons maintenant une autre opération DML sur la table CUSTOMERS. L'instruction UPDATE mettra à jour un enregistrement existant dans la table -

UPDATE customers 
SET salary = salary + 500 
WHERE id = 2;

Lorsqu'un enregistrement est mis à jour dans la table CUSTOMERS, le déclencheur de création ci-dessus, display_salary_changes sera déclenché et affichera le résultat suivant -

Old salary: 1500 
New salary: 2000 
Salary difference: 500

Dans ce chapitre, nous aborderons les packages en PL / SQL. Les packages sont des objets de schéma qui regroupent les types, variables et sous-programmes PL / SQL liés de manière logique.

Un colis aura deux parties obligatoires -

Spécification du paquet
Corps ou définition du package

Spécification du paquet

La spécification est l'interface avec le package. C'est justeDECLARESles types, variables, constantes, exceptions, curseurs et sous-programmes qui peuvent être référencés de l'extérieur du package. En d'autres termes, il contient toutes les informations sur le contenu du package, mais exclut le code des sous-programmes.

Tous les objets placés dans la spécification sont appelés publicobjets. Tout sous-programme ne figurant pas dans la spécification du package mais codé dans le corps du package est appeléprivate objet.

L'extrait de code suivant montre une spécification de package ayant une seule procédure. Vous pouvez définir de nombreuses variables globales et plusieurs procédures ou fonctions dans un package.

CREATE PACKAGE cust_sal AS 
   PROCEDURE find_sal(c_id customers.id%type); 
END cust_sal; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Package created.

Corps du paquet

Le corps du package contient les codes des différentes méthodes déclarées dans la spécification du package et d'autres déclarations privées, qui sont masquées dans le code en dehors du package.

le CREATE PACKAGE BODYL'instruction est utilisée pour créer le corps du package. L'extrait de code suivant montre la déclaration du corps du package pour lecust_salpackage créé ci-dessus. J'ai supposé que nous avions déjà créé la table CUSTOMERS dans notre base de données comme mentionné dans le chapitre PL / SQL - Variables .

CREATE OR REPLACE PACKAGE BODY cust_sal AS  
   
   PROCEDURE find_sal(c_id customers.id%TYPE) IS 
   c_sal customers.salary%TYPE; 
   BEGIN 
      SELECT salary INTO c_sal 
      FROM customers 
      WHERE id = c_id; 
      dbms_output.put_line('Salary: '|| c_sal); 
   END find_sal; 
END cust_sal; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Package body created.

Utilisation des éléments du package

Les éléments du package (variables, procédures ou fonctions) sont accessibles avec la syntaxe suivante -

package_name.element_name;

Considérez que nous avons déjà créé le package ci-dessus dans notre schéma de base de données, le programme suivant utilise le find_sal méthode de la cust_sal paquet -

DECLARE 
   code customers.id%type := &cc_id; 
BEGIN 
   cust_sal.find_sal(code); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il vous invite à entrer l'ID client et lorsque vous entrez un ID, il affiche le salaire correspondant comme suit -

Enter value for cc_id: 1 
Salary: 3000 

PL/SQL procedure successfully completed.

Exemple

Le programme suivant fournit un package plus complet. Nous utiliserons la table CUSTOMERS stockée dans notre base de données avec les enregistrements suivants -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  3000.00 | 
|  2 | Khilan   |  25 | Delhi     |  3000.00 | 
|  3 | kaushik  |  23 | Kota      |  3000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  7500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  9500.00 | 
|  6 | Komal    |  22 | MP        |  5500.00 | 
+----+----------+-----+-----------+----------+

La spécification du paquet

CREATE OR REPLACE PACKAGE c_package AS 
   -- Adds a customer 
   PROCEDURE addCustomer(c_id   customers.id%type, 
   c_name  customerS.No.ame%type, 
   c_age  customers.age%type, 
   c_addr customers.address%type,  
   c_sal  customers.salary%type); 
   
   -- Removes a customer 
   PROCEDURE delCustomer(c_id  customers.id%TYPE); 
   --Lists all customers 
   PROCEDURE listCustomer; 
  
END c_package; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il crée le package ci-dessus et affiche le résultat suivant -

Package created.

Création du corps du package

CREATE OR REPLACE PACKAGE BODY c_package AS 
   PROCEDURE addCustomer(c_id  customers.id%type, 
      c_name customerS.No.ame%type, 
      c_age  customers.age%type, 
      c_addr  customers.address%type,  
      c_sal   customers.salary%type) 
   IS 
   BEGIN 
      INSERT INTO customers (id,name,age,address,salary) 
         VALUES(c_id, c_name, c_age, c_addr, c_sal); 
   END addCustomer; 
   
   PROCEDURE delCustomer(c_id   customers.id%type) IS 
   BEGIN 
      DELETE FROM customers 
      WHERE id = c_id; 
   END delCustomer;  
   
   PROCEDURE listCustomer IS 
   CURSOR c_customers is 
      SELECT  name FROM customers; 
   TYPE c_list is TABLE OF customers.Name%type;  
   name_list c_list := c_list(); 
   counter integer :=0; 
   BEGIN 
      FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list.extend; 
      name_list(counter) := n.name; 
      dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter)); 
      END LOOP; 
   END listCustomer;
   
END c_package; 
/

L'exemple ci-dessus utilise le nested table. Nous discuterons du concept de table imbriquée dans le prochain chapitre.

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Package body created.

Utilisation du package

Le programme suivant utilise les méthodes déclarées et définies dans le package c_package .

DECLARE 
   code customers.id%type:= 8; 
BEGIN 
   c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500); 
   c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500); 
   c_package.listcustomer; 
   c_package.delcustomer(code); 
   c_package.listcustomer; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal 
Customer(7): Rajnish 
Customer(8): Subham 
Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal
Customer(7): Rajnish 

PL/SQL procedure successfully completed

Dans ce chapitre, nous aborderons les collections en PL / SQL. Une collection est un groupe ordonné d'éléments ayant le même type de données. Chaque élément est identifié par un indice unique qui représente sa position dans la collection.

PL / SQL fournit trois types de collection -

Tables indexées ou tableau associatif
Table imbriquée
Tableau de taille variable ou Varray

La documentation Oracle fournit les caractéristiques suivantes pour chaque type de collections -

Type de collecte	Nombre d'éléments	Type d'indice	Dense ou clairsemé	Où créé	Peut être un attribut de type d'objet
Tableau associatif (ou table indexée)	Sans bornes	Chaîne ou entier	Soit	Uniquement dans le bloc PL / SQL	Non
Table imbriquée	Sans bornes	Entier	Commence dense, peut devenir clairsemé	Soit en bloc PL / SQL, soit au niveau du schéma	Oui
Tableau de taille de variable (Varray)	Délimité	Entier	Toujours dense	Soit en bloc PL / SQL, soit au niveau du schéma	Oui

Nous avons déjà discuté de varray dans le chapitre 'PL/SQL arrays'. Dans ce chapitre, nous aborderons les tables PL / SQL.

Les deux types de tables PL / SQL, c'est-à-dire les tables indexées et les tables imbriquées ont la même structure et leurs lignes sont accessibles en utilisant la notation en indice. Cependant, ces deux types de tableaux diffèrent sous un aspect; les tables imbriquées peuvent être stockées dans une colonne de base de données et les tables indexées ne le peuvent pas.

Table indexée

Un index-by table (également appelée associative array) est un ensemble de key-valuepaires. Chaque clé est unique et est utilisée pour localiser la valeur correspondante. La clé peut être un entier ou une chaîne.

Une table indexée est créée à l'aide de la syntaxe suivante. Ici, nous créons unindex-by table nommée table_name, dont les clés seront de type subscript_type et les valeurs associées seront de type element_type

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; 
 
table_name type_name;

Exemple

L'exemple suivant montre comment créer une table pour stocker des valeurs entières avec des noms et plus tard, il imprime la même liste de noms.

DECLARE 
   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20); 
   salary_list salary; 
   name   VARCHAR2(20); 
BEGIN 
   -- adding elements to the table 
   salary_list('Rajnish') := 62000; 
   salary_list('Minakshi') := 75000; 
   salary_list('Martin') := 100000; 
   salary_list('James') := 78000;  
   
   -- printing the table 
   name := salary_list.FIRST; 
   WHILE name IS NOT null LOOP 
      dbms_output.put_line 
      ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name))); 
      name := salary_list.NEXT(name); 
   END LOOP; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Salary of James is 78000 
Salary of Martin is 100000 
Salary of Minakshi is 75000 
Salary of Rajnish is 62000  

PL/SQL procedure successfully completed.

Exemple

Les éléments d'une table indexée peuvent également être %ROWTYPE de n'importe quelle table de base de données ou %TYPEde n'importe quel champ de table de base de données. L'exemple suivant illustre le concept. Nous utiliserons leCUSTOMERS table stockée dans notre base de données comme -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

DECLARE 
   CURSOR c_customers is 
      select name from customers; 

   TYPE c_list IS TABLE of customers.Name%type INDEX BY binary_integer; 
   name_list c_list; 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list(counter) := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_lis t(counter)); 
   END LOOP; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed

Tables imbriquées

UNE nested tableest comme un tableau unidimensionnel avec un nombre arbitraire d'éléments. Cependant, une table imbriquée diffère d'un tableau dans les aspects suivants -

Un tableau a un nombre déclaré d'éléments, mais pas une table imbriquée. La taille d'une table imbriquée peut augmenter de manière dynamique.
Un tableau est toujours dense, c'est-à-dire qu'il a toujours des indices consécutifs. Un tableau imbriqué est dense au départ, mais il peut devenir clairsemé lorsque des éléments en sont supprimés.

Une table imbriquée est créée à l'aide de la syntaxe suivante -

TYPE type_name IS TABLE OF element_type [NOT NULL]; 
 
table_name type_name;

Cette déclaration est similaire à la déclaration d'un index-by table, mais il n'y a pas INDEX BY clause.

Une table imbriquée peut être stockée dans une colonne de base de données. Il peut en outre être utilisé pour simplifier les opérations SQL où vous joignez une table à une seule colonne avec une table plus grande. Un tableau associatif ne peut pas être stocké dans la base de données.

Exemple

Les exemples suivants illustrent l'utilisation de la table imbriquée -

DECLARE 
   TYPE names_table IS TABLE OF VARCHAR2(10); 
   TYPE grades IS TABLE OF INTEGER;  
   names names_table; 
   marks grades; 
   total integer; 
BEGIN 
   names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   total := names.count; 
   dbms_output.put_line('Total '|| total || ' Students'); 
   FOR i IN 1 .. total LOOP 
      dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i)); 
   end loop; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Total 5 Students 
Student:Kavita, Marks:98 
Student:Pritam, Marks:97 
Student:Ayan, Marks:78 
Student:Rishav, Marks:87 
Student:Aziz, Marks:92  

PL/SQL procedure successfully completed.

Exemple

Éléments d'un nested table peut aussi être un %ROWTYPEde n'importe quelle table de base de données ou% TYPE de n'importe quel champ de table de base de données. L'exemple suivant illustre le concept. Nous utiliserons la table CUSTOMERS stockée dans notre base de données comme -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

DECLARE 
   CURSOR c_customers is  
      SELECT  name FROM customers;  
   TYPE c_list IS TABLE of customerS.No.ame%type; 
   name_list c_list := c_list(); 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list.extend; 
      name_list(counter)  := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_list(counter)); 
   END LOOP; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed.

Méthodes de collecte

PL / SQL fournit les méthodes de collecte intégrées qui facilitent l'utilisation des collections. Le tableau suivant répertorie les méthodes et leur objectif -

S. Non	Nom et objectif de la méthode
1	EXISTS(n) Renvoie TRUE si le nième élément d'une collection existe; sinon renvoie FALSE.
2	COUNT Renvoie le nombre d'éléments qu'une collection contient actuellement.
3	LIMIT Vérifie la taille maximale d'une collection.
4	FIRST Renvoie les premiers (plus petits) numéros d'index d'une collection qui utilise les indices entiers.
5	LAST Renvoie les derniers numéros d'index (les plus grands) d'une collection qui utilise les indices entiers.
6	PRIOR(n) Renvoie le numéro d'index qui précède l'index n dans une collection.
sept	NEXT(n) Renvoie le numéro d'index qui succède à l'index n.
8	EXTEND Ajoute un élément nul à une collection.
9	EXTEND(n) Ajoute n éléments nuls à une collection.
dix	EXTEND(n,i) Ajoute ncopies du i ^ème élément dans une collection.
11	TRIM Supprime un élément de la fin d'une collection.
12	TRIM(n) Supprime n éléments de la fin d'une collection.
13	DELETE Supprime tous les éléments d'une collection, en définissant COUNT sur 0.
14	DELETE(n) Supprime le n^thélément d'un tableau associatif avec une clé numérique ou une table imbriquée. Si le tableau associatif a une clé de chaîne, l'élément correspondant à la valeur de clé est supprimé. Sin est nul, DELETE(n) ne fait rien.
15	DELETE(m,n) Supprime tous les éléments de la plage m..nà partir d'un tableau associatif ou d'une table imbriquée. Sim est plus grand que n ou si m ou n est nul, DELETE(m,n) ne fait rien.

Exceptions de collection

Le tableau suivant fournit les exceptions de collection et quand elles sont déclenchées -

Exception de collection	Élevé dans des situations
COLLECTION_IS_NULL	Vous essayez d'opérer sur une collection atomiquement nulle.
AUCUNE DONNÉE DISPONIBLE	Un indice désigne un élément qui a été supprimé, ou un élément inexistant d'un tableau associatif.
SUBSCRIPT_BEYOND_COUNT	Un indice dépasse le nombre d'éléments dans une collection.
SUBSCRIPT_OUTSIDE_LIMIT	Un indice est en dehors de la plage autorisée.
VALUE_ERROR	Un indice est nul ou non convertible en type de clé. Cette exception peut se produire si la clé est définie comme unPLS_INTEGER plage, et l'indice est en dehors de cette plage.

Dans ce chapitre, nous aborderons les transactions en PL / SQL. Une base de donnéestransactionest une unité de travail atomique qui peut consister en une ou plusieurs instructions SQL associées. On l'appelle atomique car les modifications de base de données provoquées par les instructions SQL qui constituent une transaction peuvent être collectivement soit validées, c'est-à-dire rendues permanentes dans la base de données, soit annulées (annulées) de la base de données.

Une instruction SQL exécutée avec succès et une transaction validée ne sont pas identiques. Même si une instruction SQL est exécutée avec succès, à moins que la transaction contenant l'instruction ne soit validée, elle peut être annulée et toutes les modifications apportées par la ou les instructions peuvent être annulées.

Début et fin d'une transaction

Une transaction a un beginning Et un end. Une transaction démarre lorsque l'un des événements suivants se produit -

La première instruction SQL est exécutée après la connexion à la base de données.
À chaque nouvelle instruction SQL émise après qu'une transaction est terminée.

Une transaction se termine lorsque l'un des événements suivants se produit -

UNE COMMIT ou un ROLLBACK une déclaration est émise.
UNE DDL déclaration, telle que CREATE TABLEdéclaration, est publiée; car dans ce cas, un COMMIT est automatiquement exécuté.
UNE DCL déclaration, telle qu'une GRANTdéclaration, est publiée; car dans ce cas, un COMMIT est automatiquement exécuté.
L'utilisateur se déconnecte de la base de données.
L'utilisateur quitte SQL*PLUS en émettant le EXIT commande, un COMMIT est automatiquement exécuté.
SQL * Plus se termine anormalement, un ROLLBACK est automatiquement effectuée.
UNE DMLla déclaration échoue; dans ce cas, un ROLLBACK est automatiquement exécuté pour annuler cette instruction DML.

Valider une transaction

Une transaction est rendue permanente en émettant la commande SQL COMMIT. La syntaxe générale de la commande COMMIT est -

COMMIT;

Par exemple,

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (6, 'Komal', 22, 'MP', 4500.00 ); 

COMMIT;

Annulation des transactions

Les modifications apportées à la base de données sans COMMIT peuvent être annulées à l'aide de la commande ROLLBACK.

La syntaxe générale de la commande ROLLBACK est -

ROLLBACK [TO SAVEPOINT < savepoint_name>];

Lorsqu'une transaction est annulée en raison d'une situation sans précédent, comme une défaillance du système, l'ensemble de la transaction depuis une validation est automatiquement annulée. Si vous n'utilisez passavepoint, puis utilisez simplement l'instruction suivante pour annuler toutes les modifications -

ROLLBACK;

Points de sauvegarde

Les points de sauvegarde sont des sortes de marqueurs qui aident à diviser une longue transaction en unités plus petites en définissant des points de contrôle. En définissant des points de sauvegarde dans une longue transaction, vous pouvez revenir à un point de contrôle si nécessaire. Cela se fait en émettant leSAVEPOINT commander.

La syntaxe générale de la commande SAVEPOINT est -

SAVEPOINT < savepoint_name >;

Par exemple

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (7, 'Rajnish', 27, 'HP', 9500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (8, 'Riddhi', 21, 'WB', 4500.00 ); 
SAVEPOINT sav1;
  
UPDATE CUSTOMERS 
SET SALARY = SALARY + 1000; 
ROLLBACK TO sav1;
  
UPDATE CUSTOMERS 
SET SALARY = SALARY + 1000 
WHERE ID = 7; 
UPDATE CUSTOMERS 
SET SALARY = SALARY + 1000 
WHERE ID = 8; 

COMMIT;

ROLLBACK TO sav1 - Cette instruction annule toutes les modifications jusqu'au point où vous aviez marqué savepoint sav1.

Après cela, les nouvelles modifications que vous apportez commenceront.

Contrôle automatique des transactions

Pour exécuter un COMMIT automatiquement chaque fois qu'un INSERT, UPDATE ou DELETE est exécutée, vous pouvez définir le AUTOCOMMIT variable d'environnement comme -

SET AUTOCOMMIT ON;

Vous pouvez désactiver le mode de validation automatique à l'aide de la commande suivante -

SET AUTOCOMMIT OFF;

Dans ce chapitre, nous discuterons de la date et de l'heure en PL / SQL. Il existe deux classes de types de données liés à la date et à l'heure dans PL / SQL -

Types de données Datetime
Types de données d'intervalle

Les types de données Datetime sont -

DATE
TIMESTAMP
HORAIRE AVEC FUSEAU HORAIRE
HORAIRE AVEC FUSEAU HORAIRE LOCAL

Les types de données Intervalle sont -

INTERVALLE ANNÉE AU MOIS
INTERVALLE JOUR À DEUXIÈME

Valeurs de champ pour les types de données Datetime et Interval

Tous les deux datetime et interval les types de données se composent de fields. Les valeurs de ces champs déterminent la valeur du type de données. Le tableau suivant répertorie les champs et leurs valeurs possibles pour les dates et les intervalles.

Nom de domaine	Valeurs Datetime valides	Valeurs d'intervalle valides
AN	-4712 à 9999 (hors année 0)	Tout entier différent de zéro
MOIS	01 à 12	0 à 11
JOURNÉE	01 à 31 (limité par les valeurs de MONTH et YEAR, selon les règles du calendrier pour les paramètres régionaux)	Tout entier différent de zéro
HEURE	00 à 23	0 à 23
MINUTE	00 à 59	0 à 59
SECONDE	00 à 59,9 (n), où 9 (n) est la précision des fractions de seconde La partie 9 (n) n'est pas applicable pour DATE.	0 à 59,9 (n), où 9 (n) est la précision des fractions de seconde d'intervalle
TIMEZONE_HOUR	-12 à 14 (la plage s'adapte aux changements d'heure d'été) Non applicable pour DATE ou TIMESTAMP.	N'est pas applicable
TIMEZONE_MINUTE	00 à 59 Non applicable pour DATE ou TIMESTAMP.	N'est pas applicable
TIMEZONE_REGION	Non applicable pour DATE ou TIMESTAMP.	N'est pas applicable
TIMEZONE_ABBR	Non applicable pour DATE ou TIMESTAMP.	N'est pas applicable

Les types et fonctions de données Datetime

Voici les types de données Datetime -

DATE

Il stocke les informations de date et d'heure dans les types de données caractère et numérique. Il est composé d'informations sur le siècle, l'année, le mois, la date, l'heure, les minutes et les secondes. Il est spécifié comme -

HORAIRE

C'est une extension du type de données DATE. Il stocke l'année, le mois et le jour du type de données DATE, ainsi que les valeurs d'heure, de minute et de seconde. Il est utile pour stocker des valeurs de temps précises.

HORAIRE AVEC FUSEAU HORAIRE

Il s'agit d'une variante de TIMESTAMP qui inclut un nom de région de fuseau horaire ou un décalage de fuseau horaire dans sa valeur. Le décalage du fuseau horaire est la différence (en heures et minutes) entre l'heure locale et UTC. Ce type de données est utile pour collecter et évaluer les informations de date dans les régions géographiques.

HORAIRE AVEC FUSEAU HORAIRE LOCAL

C'est une autre variante de TIMESTAMP qui inclut un décalage de fuseau horaire dans sa valeur.

Le tableau suivant fournit les fonctions Datetime (où, x a la valeur datetime) -

S. Non	Nom et description de la fonction
1	ADD_MONTHS(x, y); Ajoute y mois à x.
2	LAST_DAY(x); Renvoie le dernier jour du mois.
3	MONTHS_BETWEEN(x, y); Renvoie le nombre de mois entre x et y.
4	NEXT_DAY(x, day); Renvoie la date et l' heure du jour suivant aprèsx.
5	NEW_TIME; Renvoie la valeur heure / jour d'un fuseau horaire spécifié par l'utilisateur.
6	ROUND(x [, unit]); Les manches x.
sept	SYSDATE(); Renvoie la date / heure actuelle.
8	TRUNC(x [, unit]); Tronque x.

Fonctions d'horodatage (où, x a une valeur d'horodatage) -

S. Non	Nom et description de la fonction
1	CURRENT_TIMESTAMP(); Renvoie un TIMESTAMP WITH TIME ZONE contenant l'heure actuelle de la session ainsi que le fuseau horaire de la session.
2	EXTRACT({ YEAR \| MONTH \| DAY \| HOUR \| MINUTE \| SECOND } \| { TIMEZONE_HOUR \| TIMEZONE_MINUTE } \| { TIMEZONE_REGION \| } TIMEZONE_ABBR ) FROM x) Extrait et renvoie une année, un mois, un jour, une heure, une minute, une seconde ou un fuseau horaire à partir de x.
3	FROM_TZ(x, time_zone); Convertit TIMESTAMP x et le fuseau horaire spécifié par time_zone en TIMESTAMP WITH TIMEZONE.
4	LOCALTIMESTAMP(); Renvoie un TIMESTAMP contenant l'heure locale dans le fuseau horaire de la session.
5	SYSTIMESTAMP(); Renvoie un TIMESTAMP WITH TIME ZONE contenant l'heure actuelle de la base de données ainsi que le fuseau horaire de la base de données.
6	SYS_EXTRACT_UTC(x); Convertit TIMESTAMP WITH TIMEZONE x en un TIMESTAMP contenant la date et l'heure UTC.
sept	TO_TIMESTAMP(x, [format]); Convertit la chaîne x en TIMESTAMP.
8	TO_TIMESTAMP_TZ(x, [format]); Convertit la chaîne x en TIMESTAMP WITH TIMEZONE.

Exemples

Les extraits de code suivants illustrent l'utilisation des fonctions ci-dessus -

Example 1

SELECT SYSDATE FROM DUAL;

Output -

08/31/2012 5:25:34 PM

Example 2

SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH:MI:SS') FROM DUAL;

Output -

31-08-2012 05:26:14

Example 3

SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;

Output -

01/31/2013 5:26:31 PM

Example 4

SELECT LOCALTIMESTAMP FROM DUAL;

Output -

8/31/2012 5:26:55.347000 PM

Les types et fonctions de données d'intervalle

Voici les types de données d'intervalle -

IINTERVAL YEAR TO MONTH - Il stocke une période de temps en utilisant les champs datetime YEAR et MONTH.
INTERVALLE JOUR À SECOND - Il stocke une période de temps en termes de jours, heures, minutes et secondes.

Fonctions d'intervalle

S. Non	Nom et description de la fonction
1	NUMTODSINTERVAL(x, interval_unit); Convertit le nombre x en un INTERVALLE DU JOUR À LA SECONDE.
2	NUMTOYMINTERVAL(x, interval_unit); Convertit le nombre x en INTERVALLE ANNÉE EN MOIS.
3	TO_DSINTERVAL(x); Convertit la chaîne x en INTERVAL DAY TO SECOND.
4	TO_YMINTERVAL(x); Convertit la chaîne x en INTERVAL YEAR TO MONTH.

Dans ce chapitre, nous aborderons la sortie du SGBD en PL / SQL. leDBMS_OUTPUTest un package intégré qui vous permet d'afficher la sortie, les informations de débogage et d'envoyer des messages à partir de blocs, de sous-programmes, de packages et de déclencheurs PL / SQL. Nous avons déjà utilisé ce package tout au long de notre tutoriel.

Regardons un petit extrait de code qui affichera toutes les tables utilisateur de la base de données. Essayez-le dans votre base de données pour lister tous les noms de table -

BEGIN 
   dbms_output.put_line  (user || ' Tables in the database:'); 
   FOR t IN (SELECT table_name FROM user_tables) 
   LOOP 
      dbms_output.put_line(t.table_name); 
   END LOOP; 
END; 
/

Sous-programmes DBMS_OUTPUT

Le package DBMS_OUTPUT comprend les sous-programmes suivants -

S. Non	Sous-programme et objectif
1	DBMS_OUTPUT.DISABLE; Désactive la sortie de message.
2	DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000); Active la sortie de message. Une valeur NULL debuffer_size représente une taille de tampon illimitée.
3	DBMS_OUTPUT.GET_LINE (line OUT VARCHAR2, status OUT INTEGER); Récupère une seule ligne d'informations mises en mémoire tampon.
4	DBMS_OUTPUT.GET_LINES (lines OUT CHARARR, numlines IN OUT INTEGER); Récupère un tableau de lignes du tampon.
5	DBMS_OUTPUT.NEW_LINE; Place un marqueur de fin de ligne.
6	DBMS_OUTPUT.PUT(item IN VARCHAR2); Place une ligne partielle dans le tampon.
sept	DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2); Place une ligne dans le tampon.

Exemple

DECLARE 
   lines dbms_output.chararr; 
   num_lines number; 
BEGIN 
   -- enable the buffer with default size 20000 
   dbms_output.enable; 
   
   dbms_output.put_line('Hello Reader!'); 
   dbms_output.put_line('Hope you have enjoyed the tutorials!'); 
   dbms_output.put_line('Have a great time exploring pl/sql!'); 
  
   num_lines := 3; 
  
   dbms_output.get_lines(lines, num_lines); 
  
   FOR i IN 1..num_lines LOOP 
      dbms_output.put_line(lines(i)); 
   END LOOP; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Hello Reader! 
Hope you have enjoyed the tutorials! 
Have a great time exploring pl/sql!  

PL/SQL procedure successfully completed.

Dans ce chapitre, nous aborderons le PL / SQL orienté objet. PL / SQL permet de définir un type d'objet, ce qui aide à concevoir une base de données orientée objet dans Oracle. Un type d'objet vous permet de créer des types composites. L'utilisation d'objets vous permet d'implémenter des objets du monde réel avec une structure spécifique de données et des méthodes pour les exploiter. Les objets ont des attributs et des méthodes. Les attributs sont les propriétés d'un objet et sont utilisés pour stocker l'état d'un objet; et des méthodes sont utilisées pour modéliser son comportement.

Les objets sont créés à l'aide de l'instruction CREATE [OR REPLACE] TYPE. Voici un exemple pour créer un simpleaddress objet composé de quelques attributs -

CREATE OR REPLACE TYPE address AS OBJECT 
(house_no varchar2(10), 
 street varchar2(30), 
 city varchar2(20), 
 state varchar2(10), 
 pincode varchar2(10) 
); 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Type created.

Créons un autre objet customer où nous allons envelopper attributes et methods ensemble pour avoir une sensation orientée objet -

CREATE OR REPLACE TYPE customer AS OBJECT 
(code number(5), 
 name varchar2(30), 
 contact_no varchar2(12), 
 addr address, 
 member procedure display 
); 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Type created.

Instancier un objet

La définition d'un type d'objet fournit un plan pour l'objet. Pour utiliser cet objet, vous devez créer des instances de cet objet. Vous pouvez accéder aux attributs et méthodes de l'objet en utilisant le nom de l'instance etthe access operator (.) comme suit -

DECLARE 
   residence address; 
BEGIN 
   residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301'); 
   dbms_output.put_line('House No: '|| residence.house_no); 
   dbms_output.put_line('Street: '|| residence.street); 
   dbms_output.put_line('City: '|| residence.city); 
   dbms_output.put_line('State: '|| residence.state); 
   dbms_output.put_line('Pincode: '|| residence.pincode); 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

House No: 103A 
Street: M.G.Road 
City: Jaipur 
State: Rajasthan 
Pincode: 201301  

PL/SQL procedure successfully completed.

Méthodes des membres

Member methods sont utilisés pour manipuler le attributesde l'objet. Vous fournissez la déclaration d'une méthode membre lors de la déclaration du type d'objet. Le corps de l'objet définit le code des méthodes membres. Le corps de l'objet est créé à l'aide de l'instruction CREATE TYPE BODY.

Constructorssont des fonctions qui renvoient un nouvel objet comme valeur. Chaque objet a une méthode de constructeur définie par le système. Le nom du constructeur est le même que le type d'objet. Par exemple -

residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301');

le comparison methodssont utilisés pour comparer des objets. Il existe deux façons de comparer des objets -

Méthode de la carte

le Map methodest une fonction implémentée de telle manière que sa valeur dépend de la valeur des attributs. Par exemple, pour un objet client, si le code client est le même pour deux clients, les deux clients peuvent être identiques. La relation entre ces deux objets dépendrait donc de la valeur du code.

Méthode de commande

le Order methodimplémente une logique interne pour comparer deux objets. Par exemple, pour un objet rectangle, un rectangle est plus grand qu'un autre rectangle si ses deux côtés sont plus grands.

Utilisation de la méthode Map

Essayons de comprendre les concepts ci-dessus en utilisant l'objet rectangle suivant -

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 member function enlarge( inc number) return rectangle, 
 member procedure display, 
 map member function measure return number 
); 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Type created.

Création du corps du type -

CREATE OR REPLACE TYPE BODY rectangle AS 
   MEMBER FUNCTION enlarge(inc number) return rectangle IS 
   BEGIN 
      return rectangle(self.length + inc, self.width + inc); 
   END enlarge;  
   MEMBER PROCEDURE display IS 
   BEGIN  
      dbms_output.put_line('Length: '|| length); 
      dbms_output.put_line('Width: '|| width); 
   END display;  
   MAP MEMBER FUNCTION measure return number IS 
   BEGIN 
      return (sqrt(length*length + width*width)); 
   END measure; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Type body created.

Maintenant en utilisant l'objet rectangle et ses fonctions membres -

DECLARE 
   r1 rectangle; 
   r2 rectangle; 
   r3 rectangle; 
   inc_factor number := 5; 
BEGIN 
   r1 := rectangle(3, 4); 
   r2 := rectangle(5, 7); 
   r3 := r1.enlarge(inc_factor); 
   r3.display;  
   IF (r1 > r2) THEN -- calling measure function 
      r1.display; 
   ELSE 
      r2.display; 
   END IF; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Length: 8 
Width: 9 
Length: 5 
Width: 7  

PL/SQL procedure successfully completed.

Utilisation de la méthode de commande

Maintenant le same effect could be achieved using an order method. Recréons l'objet rectangle en utilisant une méthode de commande -

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 member procedure display, 
 order member function measure(r rectangle) return number 
); 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Type created.

Création du corps du type -

CREATE OR REPLACE TYPE BODY rectangle AS 
   MEMBER PROCEDURE display IS 
   BEGIN 
      dbms_output.put_line('Length: '|| length); 
      dbms_output.put_line('Width: '|| width); 
   END display;  
   ORDER MEMBER FUNCTION measure(r rectangle) return number IS 
   BEGIN 
      IF(sqrt(self.length*self.length + self.width*self.width)> 
         sqrt(r.length*r.length + r.width*r.width)) then 
         return(1); 
      ELSE 
         return(-1); 
      END IF; 
   END measure; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Type body created.

Utilisation de l'objet rectangle et de ses fonctions membres -

DECLARE 
   r1 rectangle; 
   r2 rectangle; 
BEGIN 
   r1 := rectangle(23, 44); 
   r2 := rectangle(15, 17); 
   r1.display; 
   r2.display; 
   IF (r1 > r2) THEN -- calling measure function 
      r1.display; 
   ELSE 
      r2.display; 
   END IF; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Length: 23 
Width: 44 
Length: 15 
Width: 17 
Length: 23 
Width: 44 

PL/SQL procedure successfully completed.

Héritage pour les objets PL / SQL

PL / SQL permet de créer des objets à partir des objets de base existants. Pour implémenter l'héritage, les objets de base doivent être déclarés commeNOT FINAL. La valeur par défaut estFINAL.

Les programmes suivants illustrent l'héritage dans les objets PL / SQL. Créons un autre objet nomméTableTop, ceci est hérité de l'objet Rectangle. Pour cela, nous devons créer l' objet rectangle de base -

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 member function enlarge( inc number) return rectangle, 
 NOT FINAL member procedure display) NOT FINAL 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Type created.

Création du corps du type de base -

CREATE OR REPLACE TYPE BODY rectangle AS 
   MEMBER FUNCTION enlarge(inc number) return rectangle IS 
   BEGIN 
      return rectangle(self.length + inc, self.width + inc); 
   END enlarge;  
   MEMBER PROCEDURE display IS 
   BEGIN 
      dbms_output.put_line('Length: '|| length); 
      dbms_output.put_line('Width: '|| width); 
   END display; 
END; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Type body created.

Création du plateau de l' objet enfant -

CREATE OR REPLACE TYPE tabletop UNDER rectangle 
(   
   material varchar2(20), 
   OVERRIDING member procedure display 
) 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Type created.

Création du corps de type pour la table d' objet enfant

CREATE OR REPLACE TYPE BODY tabletop AS 
OVERRIDING MEMBER PROCEDURE display IS 
BEGIN 
   dbms_output.put_line('Length: '|| length); 
   dbms_output.put_line('Width: '|| width); 
   dbms_output.put_line('Material: '|| material); 
END display; 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Type body created.

Utilisation de l' objet de table et de ses fonctions membres -

DECLARE 
   t1 tabletop; 
   t2 tabletop; 
BEGIN 
   t1:= tabletop(20, 10, 'Wood'); 
   t2 := tabletop(50, 30, 'Steel'); 
   t1.display; 
   t2.display; 
END;
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Length: 20 
Width: 10 
Material: Wood 
Length: 50 
Width: 30 
Material: Steel  

PL/SQL procedure successfully completed.

Objets abstraits en PL / SQL

le NOT INSTANTIABLEclause vous permet de déclarer un objet abstrait. Vous ne pouvez pas utiliser un objet abstrait tel quel; vous devrez créer un sous-type ou un type enfant de tels objets pour utiliser ses fonctionnalités.

Par exemple,

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE display)  
 NOT INSTANTIABLE NOT FINAL 
/

Lorsque le code ci-dessus est exécuté à l'invite SQL, il produit le résultat suivant -

Type created.