oracle Oprateur de concatnation Un oprateur de concatnation

oracle

Opérateur de concaténation �Un opérateur de concaténation : �Lie des colonnes ou des chaînes de caractères à d'autres colonnes �Est représenté par deux barres verticales (||) �Crée une colonne résultante qui est une expression de type caractère SELECT last_name||job_id AS "Employees" FROM employees;

Utiliser des chaînes de caractères littérales SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;

Opérateur de délimitation (q) alternatif SELECT department_name || q'[, it's assigned Manager Id: ]' || manager_id AS "Department and Manager" FROM departments;

Utiliser les fonctions de manipulation de caractères SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'? " FROM employees WHERE SUBSTR(job_id, 4) = 'REP';

Fonctions numériques �ROUND : arrondit la valeur à une décimale donnée �TRUNC : tronque la valeur à une décimale donnée �MOD : renvoie le reste d'une division Fonction Résultat ROUND(45. 926, 2) 45. 93 TRUNC(45. 926, 2) 45. 92 MOD(1600, 300) 100

Manipuler les dates �Dans la base de données Oracle, les dates sont stockées dans un format numérique interne : siècle, année, mois, jour, heures, minutes et secondes. �Le format de date par défaut est DD-MON-RR. �Ce format permet le stockage des dates du 21ème siècle dans le 20ème siècle via l'indication les deux derniers chiffres de l'année uniquement. �De la même façon, il permet le stockage des dates du 20ème siècle dans le 21ème siècle. SELECT last_name, hire_date FROM employees WHERE hire_date < '01 -FEB-88';

Utiliser les dates SYSDATE est une fonction qui renvoie : �La date �L'heure

Utiliser des opérateurs arithmétiques avec des dates SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90;

Fonctions de date Fonction MONTHS_BETWEEN Résultat ADD_MONTHS Ajout de mois calendaires à une date NEXT_DAY LAST_DAY Jour qui suit la date indiquée ROUND Date arrondie TRUNC Date tronquée Nombre de mois entre deux dates Dernier jour du mois

Utiliser des fonctions de date Fonction Résultat MONTHS_BETWEEN ('01 -SEP-95', '11 -JAN-94') 19. 6774194 ADD_MONTHS ('11 -JAN-94', 6) '11 -JUL-94' NEXT_DAY ('01 -SEP-95', 'FRIDAY') '08 -SEP-95' LAST_DAY ('01 -FEB-95') '28 -FEB-95'

Utiliser l'expression CASE Elle facilite les interrogations conditionnelles grâce un fonctionnement semblable à celui de l'instruction IF-THEN-ELSE : SELECT last_name, job_id, salary, CASE job_id WHEN 'IT_PROG' THEN 1. 10*salary WHEN 'ST_CLERK' THEN 1. 15*salary WHEN 'SA_REP' THEN 1. 20*salary ELSE salary END "REVISED_SALARY" FROM employees; … …

Définition du langage PL/SQL �PL/SQL : �Signifie "Procedural Language extension to SQL" (extension procédurale du langage SQL) �Est le langage standard d'accès aux données d'Oracle Corporation pour les bases de données relationnelles �Intègre de manière transparente les structures procédurales au langage SQL �Fournit des structures procédurales telles que : �Variables, constantes et types �Structures de contrôle, telles que les instructions conditionnelles et les boucles �Programmes réutilisables écrits une fois et exécutés plusieurs fois

Types de bloc � Anonyme Procédure Fonction [DECLARE] PROCEDURE name IS BEGIN --statements [EXCEPTION] FUNCTION name RETURN datatype IS BEGIN --statements RETURN value; [EXCEPTION] END;

Tester la sortie d'un bloc PL/SQL �Activez la sortie dans i. SQL*Plus avec la commande SET SERVEROUTPUT ON. �Utilisez un package Oracle prédéfini et sa procédure : �DBMS_OUTPUT. PUT_LINE SET SERVEROUTPUT ON … DBMS_OUTPUT. PUT_LINE(' The First Name of the Employee is ' || f_name); …

Déclarer et initialiser des variables PL/SQL Syntaxe : identifier [CONSTANT] datatype [NOT NULL] [: = | DEFAULT expr]; Exemples : DECLARE emp_hiredate emp_deptno location c_comm DATE; NUMBER(2) NOT NULL : = 10; VARCHAR 2(13) : = 'Atlanta'; CONSTANT NUMBER : = 1400;

Contrôle d'itération : instructions LOOP �Les boucles permettent d'exécuter plusieurs fois une instruction ou une séquence d'instructions. �Il existe trois types de boucle : �Boucle de base �Boucle FOR �Boucle WHILE

Boucles de base �Syntaxe : LOOP statement 1; . . . EXIT [WHEN condition]; END LOOP;

Boucles WHILE �Syntaxe : WHILE condition LOOP statement 1; statement 2; . . . END LOOP; �Utilisez la boucle WHILE pour répéter des instructions tant qu'une condition renvoie TRUE.

Boucles FOR �Utilisez une boucle FOR pour simplifier le contrôle du nombre d'itérations. �Ne déclarez pas le compteur (sa déclaration est implicite). �La syntaxe 'lower_bound. . upper_bound' est obligatoire. FOR counter IN [REVERSE] lower_bound. . upper_bound LOOP statement 1; statement 2; . . . END LOOP;

Boucles FOR �Règles �Ne référencez le compteur qu'à l'intérieur de la boucle ; il n'est pas défini en dehors. �Ne référencez pas le compteur en tant que cible d'une affectation. �Aucune limite de boucle ne doit être NULL.

Déclarer le curseur Syntaxe : CURSOR cursor_name IS select_statement; DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees WHERE department_id =30;

Ouvrir le curseur DECLARE CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees WHERE department_id =30; . . . BEGIN OPEN emp_cursor;

Extraire des données du curseur BEGIN OPEN emp_cursor; FETCH emp_cursor INTO empno, lname; DBMS_OUTPUT. PUT_LINE( empno ||' '||lname); . . . END; /

Extraire des données du curseur BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO empno, lname; EXIT WHEN emp_cursor%NOTFOUND; DBMS_OUTPUT. PUT_LINE( empno ||' '||lname); END LOOP; . . . END;

Fermer le curseur. . . LOOP FETCH emp_cursor INTO empno, lname; EXIT WHEN emp_cursor%NOTFOUND; DBMS_OUTPUT. PUT_LINE( empno ||' '||lname); END LOOP; CLOSE emp_cursor; END; /

Obtenir les informations d'état concernant un curseur Attribut Type Description %ISOPEN Booléen Prend la valeur TRUE si le curseur est ouvert %NOTFOUND Booléen Prend la valeur TRUE si la dernière extraction (fetch) ne renvoie pas de ligne %FOUND Booléen Prend la valeur TRUE si la dernière extraction renvoie une ligne ; complément de %NOTFOUND %ROWCOUNT Nombre Prend la valeur correspondant au nombre total de lignes renvoyées jusqu'à présent

Attribut %ISOPEN �Extrayez (fetch) les lignes uniquement lorsque le curseur est ouvert. �Utilisez l'attribut de curseur %ISOPEN avant de réaliser une extraction pour déterminer si le curseur est ouvert. Exemple : IF NOT emp_cursor%ISOPEN THEN OPEN emp_cursor; END IF; LOOP FETCH emp_cursor. . .

Curseurs avec paramètres SET SERVEROUTPUT ON DECLARE CURSOR emp_cursor (deptno NUMBER) IS SELECT employee_id, last_name FROM employees WHERE department_id = deptno; dept_id NUMBER; lname VARCHAR 2(15); BEGIN OPEN emp_cursor (10); . . . CLOSE emp_cursor; OPEN emp_cursor (20); . . .

Traiter les exceptions �Exception prédéfinie du serveur Oracle �Exception non prédéfinie du serveur Oracle �Exception définie par l'utilisateur

Intercepter les exceptions �Syntaxe : EXCEPTION WHEN exception 1 [OR exception 2. . . ] THEN statement 1; statement 2; . . . [WHEN exception 3 [OR exception 4. . . ] THEN statement 1; statement 2; . . . ] [WHEN OTHERS THEN statement 1; statement 2; . . . ]

Erreur non prédéfinie �Intercepter l'erreur numéro – 01400 du serveur Oracle, impossible d'insérer la valeur NULL SET SERVEROUTPUT ON DECLARE insert_excep EXCEPTION; PRAGMA EXCEPTION_INIT (insert_excep, -01400); BEGIN INSERT INTO departments (department_id, department_name) VALUES (280, NULL); EXCEPTION WHEN insert_excep THEN DBMS_OUTPUT. PUT_LINE('INSERT OPERATION FAILED'); DBMS_OUTPUT. PUT_LINE(SQLERRM); END; /

Fonctions d'interception des exceptions �SQLCODE : renvoie la valeur numérique du code d'erreur �SQLERRM : renvoie le message associé au code d'erreur

Intercepter les exceptions définies par l'utilisateur. . . ACCEPT deptno PROMPT 'Please enter the department number: ' ACCEPT name PROMPT 'Please enter the department name: ' DECLARE invalid_department EXCEPTION; name VARCHAR 2(20): ='&name'; deptno NUMBER : =&deptno; BEGIN UPDATE departments SET department_name = name WHERE department_id = deptno; IF SQL%NOTFOUND THEN RAISE invalid_department; END IF; COMMIT; EXCEPTION WHEN invalid_department THEN DBMS_OUTPUT. PUT_LINE('No such department id. '); END; /