Note :

Explorer les fonctions SQL dans Oracle Database 23ai

Présentation

Nous allons apprendre 10 fonctionnalités dans ce tutoriel que vous devez savoir et comment ils se comparent à leurs homologues existants. Ces fonctions sont les suivantes :

Objectifs

Préalables

Fonction 1 : Utiliser la clause FROM

Une fonctionnalité intéressante introduite dans Oracle Database 23ai est l'optionalité de la clause FROM dans les énoncés SELECT. Jusqu'à cette version, la clause FROM était requise.

Voici quelques avantages potentiels d'une fonction SELECT sans FROM dans Oracle Database 23ai.

Fonction 2 : Utiliser le type de données BOOLEAN

Oracle Database 23ai présente le nouveau type de données BOOLEAN. Cela tire parti de l'utilisation de vraies colonnes/variables booléennes, au lieu de les simuler avec une valeur numérique ou Varchar. La possibilité d'écrire des prédicats booléens simplifie la syntaxe des instructions SQL.

  1. Créez une table nommée TEST_BOOLEAN.

    CREATE TABLE IF NOT EXISTS TEST_BOOLEAN (name VARCHAR2(100), IS_SLEEPING BOOLEAN);

Table TEST_BOOLEAN created.

Elapsed: 00:00:00.004

  2. Entrez des données dans la nouvelle table. La valeur IS_SLEEPING sera NOT NULL réglée à FALSE comme valeur par défaut.

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING boolean NOT NULL);

    Image montrant comment utiliser la valeur booléenne pour SQL 

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING default FALSE);

Table TEST_BOOLEAN altered.

Elapsed: 00:00:00.014

    Ici, vous pouvez voir les différents types d'entrée booléenne pour Mick, Keith et Ron. Tous sont valides. Pour Mick, la valeur par défaut FALSE est utilisée - Mick ne dort pas.

    1 row inserted.

Elapsed: 00:00:00.006

    Pour Keith, nous utilisons une valeur NO - Keith ne dort pas.

    INSERT INTO TEST_BOOLEAN (name, is_sleeping) values ('Keith','NO');

1 row inserted.

Elapsed: 00:00:00.002

    Et pour Ron, nous utilisons une valeur de 1 - Ron dort.

  3. Voir certains résultats basés sur nos valeurs booléennes.

    SELECT * FROM test_boolean;

    Vous n'avez plus besoin de vous rappeler quel type de système booléen vous avez mis en place. Comme nous l'avons montré, l'utilisation de 0/1, Vrai/Faux, Oui/Non, ou toute autre entrée commune retournera une valeur de table exacte.

Fonction 3 : Utiliser la clause LDD IF NOT EXISTS

À partir d'Oracle Database 23ai, la nouvelle clause LDD IF NOT EXISTS permet de décider comment les erreurs LDD seront traitées. Cela simplifie le script LDD, car les erreurs potentielles dues à l'existence ou à l'inexistence d'objets peuvent être masquées dans le script.

  1. Tout d'abord, testez sans utiliser cette nouvelle fonctionnalité. Exécutez l'instruction suivante.

    DROP TABLE DEPT;

    Comme il n'existe aucune table DEPT à supprimer, une erreur s'affiche : ORA-00942: table or view does not exist.

  2. Toutefois, dans Oracle Database 23ai, nous pouvons utiliser DROP IF EXISTS sans erreur. Cela nous donne la tranquillité d'esprit tout en évitant les erreurs. Exécutez maintenant le même énoncé, mais incluez cette nouvelle fonction IF EXISTS.

    DROP TABLE IF EXISTS DEPT;

  3. De même, nous pouvons utiliser cette fonctionnalité pour créer des tables, si elles n'existent pas déjà. Créez cette table DEPT.

    CREATE TABLE IF NOT EXISTS DEPT
	(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;

    Image montrant comment utiliser la clause IF [NOT] EXISTS DDL

  4. Utilisez cette fonction pour créer d'autres tables-échantillons tout au long de ce tutoriel. Ici, nous allons créer une table d'employés appelée EMP.

    CREATE TABLE IF NOT EXISTS EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

Table EMP created.

Elapsed: 00:00:00.006

Fonction 4 : Utiliser INSERT pour plusieurs rangées

Une autre fonction intéressante assurant une meilleure coexistence et compatibilité avec d'autres systèmes de gestion de base de données fréquemment utilisés est l'énoncé INSERT multivalue.

  1. Dans les versions précédentes de la base de données Oracle, par exemple, l'insertion de plusieurs lignes nécessite une instruction d'insertion distincte pour chaque ligne.

    INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO DEPT VALUES (50,'HR','LOS ANGELES');
INSERT INTO DEPT VALUES (60,'IT','SAN FRANCISCO');
INSERT INTO DEPT VALUES (70,'MANUFACTURING','DETROIT');

    Oracle Database 23ai a introduit la nouvelle syntaxe permettant d'insérer toutes ces rangées dans un seul énoncé INSERT. Vous pouvez donc insérer plusieurs tuples dans un LMD. Exécutez l'instruction suivante.

    INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON'),
(50,'HR','LOS ANGELES'),
(60,'IT','SAN FRANCISCO'),
(70,'MANUFACTURING','DETROIT');

    Image montrant comment utiliser des insertions multivalues

    En plus d'une meilleure compatibilité avec d'autres bases de données, cette instruction peut être utilisée pour assurer la cohérence de certaines opérations d'insertion en mode de validation automatique. Cela peut être important, par exemple, pour les applications Oracle APEX qui utilisent ce mode pour travailler sur des données.

  2. Exécutez l'énoncé suivant pour utiliser cette fonction afin de remplir les valeurs de la table EMP.

    INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20),
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30),
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30),
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30),
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30),
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30),
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20),
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30),
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20),
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

Fonction 5 : Utiliser le nouveau constructeur VALUE

Depuis la base de données Oracle 23ai, le constructeur de valeurs de table a été étendu. Il peut maintenant être utilisé dans les énoncés INSERT pour créer plusieurs rangées dans une seule commande. Il peut également être utilisé dans les énoncés SELECT et dans la syntaxe de factorisation de vue. Dans ce dernier cas, il simplifie la syntaxe des énoncés et évite d'utiliser la table DUAL.

L'instruction suivante ressemble à une sorte de fonction de table à la volée.

SELECT * FROM (VALUES (50,'HR'), (60,'DEV'), (70,'AI')) virt_dept (deptno, dname);

Image montrant comment utiliser le constructeur de nouvelle valeur de table

Fonction 6 : Utiliser des alias dans la clause GROUP BY

Oracle Database 23ai introduit la possibilité d'utiliser des alias dans la clause GROUP BY d'un énoncé SELECT. Cette fonction simplifie l'écriture des interrogations avec des expressions complexes et assure une meilleure compatibilité avec d'autres bases de données relationnelles, telles que Teradata, MySQL et PostgreSQL.

Par exemple :

SELECT to_char(hiredate,'YYYY') "Year", count(*)
FROM emp
GROUP BY to_char(hiredate,'YYYY');

Dans Oracle Database 23ai, cela peut être écrit de manière plus simple, comme le montre l'image suivante :

Image montrant comment utiliser des alias dans la clause GROUP BY

Fonction 7 : Utiliser la clause RETURNING de l'énoncé UPDATE et MERGE

Cette clause avait été mise en oeuvre précédemment dans le cadre de l'énoncé EXECUTE IMMEDIATE. Toutefois, dans Oracle Database 23ai, nous pouvons le trouver dans le cadre d'instructions LMD statiques traditionnelles.

  1. Dans ce cas, il permet d'obtenir les anciennes et nouvelles valeurs de colonnes à partir d'une ligne traitée. Tout d'abord, voyons le salaire actuel de King.

    SELECT ename, sal FROM emp WHERE ename = 'KING';

  2. Pour utiliser des variables dans LiveSQL, nous allons fermer les énoncés dans PL/SQL. Exécutez ce script. Il créera d'abord les variables pour l'ancien et le nouveau salaire, puis mettra à jour le salaire de King à l'aide de la clause RETURNING pour définir nos variables. Nous examinerons ensuite les résultats.

    BEGIN
	DECLARE
		old_salary NUMBER;
		new_salary NUMBER;
	BEGIN
		UPDATE emp
		SET sal = sal + 1000
		WHERE ename = 'KING'
		RETURNING OLD sal, NEW sal INTO old_salary, new_salary;
		DBMS_OUTPUT.PUT_LINE('Old Salary: ' || old_salary);
		DBMS_OUTPUT.PUT_LINE('New Salary: ' || new_salary);
	END;
END;

    Image montrant comment utiliser la clause RETURNING de l'énoncé UPDATE et MERGE 

    Old Salary: 6000
New Salary: 7000

    Cet exemple a utilisé un énoncé UPDATE, mais la clause RETURNING peut être utilisée de la même manière avec les énoncés MERGE.

Fonction 8 : Utiliser des jointures dans UPDATE et DELETE

Vous pouvez mettre à jour les données de table à l'aide de jointures basées sur des conditions de table étrangère. Il n'est pas nécessaire d'effectuer des sous-sélections ou une clause IN.

  1. Exécutez l'instruction suivante pour voir les informations sur le salaire de l'employé provenant du service de recherche.

    select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH';

  2. Pour mettre à jour les informations sur le salaire, avant Oracle Database 23ai, nous devons utiliser une instruction imbriquée.

    UPDATE emp e set e.sal=e.sal*2
WHERE e.deptno in
(SELECT d.deptno FROM dept d WHERE e.deptno=d.deptno
and d.dname='RESEARCH');

    Avec Oracle Database 23ai, vous pouvez l'utiliser comme suit :

    UPDATE emp e set e.sal=e.sal*2  FROM dept d  WHERE e.deptno=d.deptno  and d.dname='RESEARCH';

    Image montrant comment utiliser les jointures dans UPDATE et DELETE

  3. Vous pouvez voir que le salaire a été mis à jour.

    select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH';

    Image montrant comment utiliser les jointures dans UPDATE et DELETE

Fonction 9 : Utiliser des annotations

Les annotations sont des métadonnées facultatives pour les objets de base de données. Une annotation est soit une paire nom-valeur, soit un nom par lui-même. Le nom et la valeur facultative sont des champs de texte à structure libre. Une annotation est représentée en tant qu'élément subordonné à l'objet de base de données auquel l'annotation a été ajoutée. Les objets de schéma pris en charge incluent les tables, les vues, les vues matérialisées et les index. Avec les annotations, vous pouvez stocker et extraire des métadonnées sur des objets de base de données. Vous pouvez l'utiliser pour personnaliser la logique métier, les interfaces utilisateur ou fournir des métadonnées aux référentiels de métadonnées. Il peut être ajouté avec l'énoncé CREATE ou ALTER au niveau de la table ou de la colonne.

Avec les annotations, vous pouvez stocker et extraire les métadonnées relatives aux objets de base de données. Vous pouvez l'utiliser pour personnaliser la logique métier, les interfaces utilisateur ou fournir des métadonnées aux référentiels de métadonnées.

  1. Créez une table annotée EMP_ANNOTATED_NEW avec des annotations de colonne et de table.

    CREATE TABLE emp_annotated_new
	(empno number annotations(identity, display 'person_identity', details 'person_info'),
	ename varchar2(50),
	salary number annotations (display 'person_salary', col_hidden))
	annotations (display 'employee_table');

    Image montrant comment utiliser les annotations, nouvelles métadonnées pour les objets de base de données

  2. Les vues du dictionnaire de données telles que USER_ANNOTATIONS et USER_ANNOTATIONS_USAGE peuvent aider à surveiller l'utilisation.

    SELECT object_name, object_type, column_name, annotation_name, annotation_value FROM user_annotations_usage;

    Image montrant comment utiliser les annotations, nouvelles métadonnées pour les objets de base de données

Fonction 10 : Utiliser des domaines SQL

Un domaine SQL est un objet de dictionnaire qui appartient à un schéma et qui encapsule un jeu de propriétés et de contraintes facultatives et qui est créé avec un énoncé CREATE DOMAIN. Les domaines fournissent des attributs de contraintes, d'affichage, de classement et d'annotations. Après avoir défini un domaine SQL, vous pouvez définir des colonnes de table à associer à ce domaine, en appliquant explicitement les propriétés et contraintes facultatives du domaine à ces colonnes.

Les domaines SQL permettent aux utilisateurs de déclarer l'utilisation prévue pour les colonnes. Il s'agit d'objets de dictionnaire de données qui permettent de réutiliser facilement des connaissances propres à un domaine abstrait.

  1. Créez un domaine nommé yearbirth et une table nommée person.

    CREATE DOMAIN yearbirth as number(4)
constraint check ((trunc(yearbirth) = yearbirth) and (yearbirth >= 1900))
display (case when yearbirth < 2000 then '19-' ELSE '20-' end)||mod(yearbirth, 100)
order (yearbirth -1900)
annotations (title 'yearformat');

    Image montrant comment utiliser des types d'objet de poids léger avec des domaines SQL

    CREATE TABLE person
(id number(5),
name varchar2(50),
salary number,
person_birth number(4) DOMAIN yearbirth
)
annotations (display 'person_table');

    Image montrant comment utiliser des types d'objet légers avec des domaines SQL

    " personne desc;

    Nom nul? Type

ID NUMBER(5) NOM VARCHAR2(50) NUMÉRO DE SALAIRE PERSON_BIRTH NUMÉRO(4) DOMAINE ANNÉE DE NAISSANCE

INSÉRER LES valeurs de personne (1,'MARTIN',3000, 1988);

2. With the new function `DOMAIN_DISPLAY` you can display the property.

SÉLECTIONNER DOMAIN_DISPLAY(person_birth) DE LA personne;


   ![Image showing how to use Light weight object types" with SQL Domains ](./images/Picture14.png "Light weight object types with SQL Domains")

3. Domain usage and annotations can be monitored with data dictionary views. Let us view `user_annotations_usage`.

SÉLECTIONNEZ * À PARTIR DE user_annotations_usage;


   Define reusable domain types (lightweight objects).

CRÉER LE DOMAINE EmailAddress EN TANT QUE VÉRIFICATION VARCHAR2(100) (REGEXP_LIKE(VALEUR, '^[^@]+@[^@]+.[^@]+$'));

CREATE TABLE users ( user_id NUMBER, courriel EmailAddress ); ```

Image montrant comment utiliser des types d'objet légers avec des domaines SQL

Limites et restrictions dans les fonctions d'Oracle Database 23ai

Restrictions générales

Remerciements

Autres ressources d'apprentissage

Explorez d'autres laboratoires sur le site docs.oracle.com/learn ou accédez à plus de contenu d'apprentissage gratuit sur le canal Oracle Learning YouTube. De plus, visitez education.oracle.com/learning-explorer pour devenir un explorateur Oracle Learning.

Pour obtenir la documentation sur le produit, visitez Oracle Help Center.