Remarques :

Découvrir les fonctionnalités SQL dans Oracle Database 23ai

Introduction

Nous allons découvrir 10 fonctionnalités de ce tutoriel que vous devez connaître et comment elles se comparent à leurs homologues existantes. Ces fonctionnalités sont les suivantes :

Objectifs

Prérequis

Fonctionnalité 1 : utiliser la clause FROM

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

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

Fonctionnalité 2 : utiliser le type de données BOOLEAN

Oracle Database 23ai introduit 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 les données dans la nouvelle table. La valeur IS_SLEEPING sera NOT NULL définie sur FALSE par défaut.

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING boolean NOT NULL);

    Image illustrant l'utilisation de 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 n'est pas en sommeil.

    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. Consultez des 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, True/False, Yes/No ou toute autre entrée commune renverra une valeur de table précise.

Fonctionnalité 3 : Utiliser la clause LDD IF NOT EXISTS

A partir d'Oracle Database 23ai, la nouvelle clause LDD IF NOT EXISTS permet de décider de la façon dont les erreurs LDD seront géré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;

    Etant donné qu'aucune table DEPT existante ne doit être supprimée, une erreur est survenue : ORA-00942: table or view does not exist.

  2. Cependant, 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 à présent la même instruction, mais incluez cette nouvelle fonctionnalité 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 illustrant l'utilisation de la clause IF [NOT] EXISTS DDL

  4. Utilisez cette fonctionnalité pour créer d'autres exemples de table tout au long de ce tutoriel. Ici, nous allons créer une table d'employés nommé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

Fonctionnalité 4 : Utiliser INSERT pour plusieurs lignes

Une autre fonctionnalité intéressante garantissant une meilleure coexistence et compatibilité avec d'autres systèmes de gestion de base de données fréquemment utilisés est l'instruction INSERT à choix multiples.

  1. Dans les versions précédentes de la base de données Oracle, par exemple, l'insertion de plusieurs lignes nécessitait une instruction insert 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 lignes dans une instruction INSERT unique. Vous pouvez donc insérer plusieurs tuples dans une instruction 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 illustrant l'utilisation des instructions INSERT multivalue

    Outre une meilleure compatibilité avec d'autres bases de données, cette instruction peut être utilisée pour garantir 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 les données.

  2. Exécutez l'instruction suivante pour utiliser cette fonctionnalité 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);

Fonctionnalité 5 : utilisation du nouveau constructeur VALUE

A partir de la base de données Oracle 23ai, le constructeur de valeurs de table a été étendu. Elle peut désormais être utilisée dans les instructions INSERT pour créer plusieurs lignes en une seule commande. Il peut également être utilisé dans les instructions SELECT et dans la syntaxe de factorisation de vue. Dans ce dernier cas, il simplifie la syntaxe des instructions 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);

Illustration présentant l'utilisation du nouveau constructeur de valeur de table

Fonctionnalité 6 : utilisation d'alias dans la clause GROUP BY

Oracle Database 23ai introduit la possibilité d'utiliser des alias dans la clause GROUP BY d'une instruction SELECT. Cette fonctionnalité simplifie l'écriture de requêtes avec des expressions complexes et garantit une meilleure compatibilité avec d'autres bases de données relationnelles, telles que Teradata, MySQL et PostgreSQL.

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 illustré dans l'image suivante :

Image montrant comment utiliser des alias dans la clause GROUP BY

Fonctionnalité 7 : utiliser la clause RETURNING de la clause UPDATE et de la déclaration MERGE

Cette clause avait été implémentée précédemment dans le cadre de l'instruction EXECUTE IMMEDIATE. Cependant, dans Oracle Database 23ai, nous pouvons le trouver dans le cadre d'instructions LMD statiques traditionnelles.

  1. Dans ce cas, il permet d'obtenir de nouvelles et anciennes 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 terminer les instructions en PL/SQL. Exécutez ce script. Elle crée d'abord les variables pour l'ancien et le nouveau salaire, puis met à jour le salaire de King à l'aide de la clause RETURNING pour définir nos variables. Nous allons ensuite examiner 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'instruction UPDATE et MERGE 

    Old Salary: 6000
New Salary: 7000

    Cet exemple a utilisé une instruction UPDATE, mais la clause RETURNING peut être utilisée de la même manière que les instructions MERGE.

Fonctionnalité 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. Vous n'avez pas besoin de sous-sélection ou de clause IN.

  1. Exécutez l'instruction suivante pour afficher les informations sur le salaire des employés 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 de 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 illustrant l'utilisation des jointures dans UPDATE et DELETE

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

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

    Image illustrant l'utilisation des jointures dans UPDATE et DELETE

Fonctionnalité 9 : Utiliser des annotations

Les annotations sont des métadonnées facultatives pour les objets de base de données. Une annotation est une paire nom-valeur ou un nom lui-même. Le nom et la valeur facultative sont des champs de texte au format libre. Une annotation est représentée comme un é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 concernant 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'instruction CREATE ou ALTER au niveau de la table ou de la colonne.

Avec les annotations, vous pouvez stocker et extraire des métadonnées sur les 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 illustrant l'utilisation des 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 illustrant l'utilisation des annotations, nouvelles métadonnées pour les objets de base de données

Fonctionnalité 10 : Utiliser des domaines SQL

Un domaine SQL est un objet de dictionnaire qui appartient à un schéma et encapsule un ensemble de propriétés et de contraintes facultatives. Il est créé à l'aide d'une instruction CREATE DOMAIN. Les domaines fournissent des attributs de contraintes, d'affichage, de tri et d'annotations. Une fois que vous avez défini un domaine SQL, vous pouvez définir les colonnes de table à associer à ce domaine, ce qui permet d'appliquer 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 légers 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 illustrant l'utilisation de types d'objet légers avec des domaines SQL

    ``` desc person ;

    Name Null? Type

NUMÉRO D'IDENTIFICATION(5) NOM VARCHAR2(50) NUMÉRO DE SALAIRE PERSON_BIRTH NUMÉRO(4) DOMAINE ANNÉE DE NAISSANCE

INSERER DANS les valeurs de personne (1,'MARTIN',3000, 1988) ;

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

SELECT DOMAIN_DISPLAY(person_birth) FROM person ;


   ![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 utilisateurs ( user_id NUMBER, email EmailAddress ) ; ```

Image illustrant l'utilisation de types d'objet légers avec des domaines SQL

Limites et restrictions dans les fonctionnalités d'Oracle Database 23ai

Restrictions générales

Accusés de réception

Ressources de formation supplémentaires

Explorez d'autres ateliers sur le site docs.oracle.com/learn ou accédez à d'autres contenus d'apprentissage gratuits sur le canal Oracle Learning YouTube. En outre, visitez le site education.oracle.com/learning-explorer pour devenir un explorateur Oracle Learning.

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