Nota

Esplora le funzionalità SQL in Oracle Database 23ai

Introduzione

Impareremo 10 caratteristiche in questo tutorial che è necessario conoscere e come si confrontano con le loro controparti esistenti. Di seguito sono riportate le funzioni disponibili.

Obiettivi

Prerequisiti

Funzione 1: Usa clausola FROM

Una funzione interessante introdotta in Oracle Database 23ai è l'opzionalità della clausola FROM nelle istruzioni SELECT. Fino a questa versione era richiesta la clausola FROM.

Ecco alcuni potenziali vantaggi di una funzione SELECT senza FROM in Oracle Database 23ai.

Funzione 2: Usa tipo di dati BOOLEAN

Oracle Database 23ai introduce il nuovo tipo di dati BOOLEAN. Ciò sfrutta l'uso di colonne/variabili booleane vere, invece di simularle con un valore numerico o Varchar. La capacità di scrivere predicati booleani semplifica la sintassi delle istruzioni SQL.

  1. Creare una tabella denominata 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. Immettere i dati nella nuova tabella. Il valore IS_SLEEPING sarà NOT NULL impostato su FALSE come valore predefinito.

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING boolean NOT NULL);

    Immagine che mostra come utilizzare il valore booleano per SQL 

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING default FALSE);

Table TEST_BOOLEAN altered.

Elapsed: 00:00:00.014

    Qui puoi vedere i diversi tipi di input booleano per Mick, Keith e Ron. Tutti sono validi. Per Mick viene utilizzato il valore predefinito FALSE: Mick non dorme.

    1 row inserted.

Elapsed: 00:00:00.006

    Per Keith viene utilizzato un valore NO, ovvero Keith non sta dormendo.

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

1 row inserted.

Elapsed: 00:00:00.002

    E per Ron usiamo un valore di 1: Ron dorme.

  3. Vedi alcuni risultati in base ai nostri valori booleani.

    SELECT * FROM test_boolean;

    Non è più necessario ricordare che tipo di sistema booleano hai messo in atto. Come abbiamo dimostrato, l'utilizzo di 0/1, True/False, Yes/No o qualsiasi altro input comune restituirà un valore di tabella accurato.

Funzione 3: Usa clausola DDL IF NOT EXISTS

A partire da Oracle Database 23ai, la nuova clausola DDL IF NOT EXISTS consente di decidere come verranno gestiti gli errori DDL. Ciò semplifica lo script DDL, poiché i potenziali errori dovuti all'esistenza o all'inesistenza di oggetti possono essere nascosti allo scripting.

  1. In primo luogo, eseguire il test senza utilizzare questa nuova funzionalità. Eseguire la seguente istruzione.

    DROP TABLE DEPT;

    Poiché non esiste alcuna tabella DEPT da eliminare, verrà visualizzato un errore: ORA-00942: table or view does not exist.

  2. Tuttavia, in Oracle Database 23ai, possiamo utilizzare DROP IF EXISTS senza errori. Questo ci dà tranquillità evitando gli errori. Ora, eseguire la stessa istruzione, ma includere questa nuova funzione IF EXISTS.

    DROP TABLE IF EXISTS DEPT;

  3. Allo stesso modo, possiamo utilizzare questa funzione per creare tabelle, se non esistono già. Creare la tabella DEPT.

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

    Immagine che mostra come utilizzare la clausola DDL IF [NOT] EXISTS

  4. Utilizzare questa funzione per creare più tabelle di esempio in questa esercitazione. Qui, faremo una tabella dei dipendenti chiamata 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

Funzione 4: utilizzare INSERT per più righe

Un'altra caratteristica interessante che garantisce una migliore coesistenza e compatibilità con altri sistemi di gestione di database utilizzati di frequente è l'istruzione INSERT multivalore.

  1. Nelle versioni precedenti del database Oracle, ad esempio, l'inserimento di più righe richiedeva un'istruzione di inserimento separata per ogni riga.

    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 ha introdotto la nuova sintassi che consente di inserire tutte queste righe in un'unica istruzione INSERT, in modo da poter inserire diverse tuple in un unico DML. Eseguire la seguente istruzione.

    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');

    Immagine che mostra come utilizzare Multivalue INSERTs

    Oltre a una migliore compatibilità con altri database, questa istruzione può essere utilizzata per garantire la coerenza di alcune operazioni di inserimento in modalità di commit automatico. Ciò potrebbe essere importante, ad esempio, per le applicazioni Oracle APEX che utilizzano questa modalità per lavorare sui dati.

  2. Eseguire l'istruzione seguente per utilizzare questa funzione per inserire i valori per la tabella 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);

Funzione 5: Usa nuovo costruttore VALUE

A partire dal database Oracle 23ai, il costruttore dei valori di tabella è stato esteso. Ora può essere utilizzato nelle istruzioni INSERT, per creare più righe in un singolo comando. Può essere utilizzato anche nelle istruzioni SELECT e nella sintassi di fattorizzazione della vista. In quest'ultimo caso, semplifica la sintassi delle istruzioni ed evita l'uso della tabella DUAL.

L'istruzione seguente sembra una sorta di funzione tabella al volo.

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

Immagine che mostra come utilizzare il nuovo costruttore di valori della tabella

Funzione 6: Usa alias nella clausola GROUP BY

Oracle Database 23ai introduce la possibilità di utilizzare gli alias nella clausola GROUP BY di un'istruzione SELECT. Questa funzione semplifica la scrittura di query con espressioni complesse e garantisce una migliore compatibilità con alcuni altri database relazionali, come Teradata, MySQL e PostgreSQL.

Ad esempio:

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

In Oracle Database 23ai questo può essere scritto in modo più semplice, come mostrato nell'immagine seguente:

Immagine che mostra come utilizzare gli alias nella clausola GROUP BY

Funzione 7: utilizzare la clausola RETURNING della dichiarazione UPDATE e MERGE

Questa clausola era stata implementata in precedenza come parte dell'istruzione EXECUTE IMMEDIATE. Tuttavia, in Oracle Database 23ai possiamo trovarlo come parte delle istruzioni DML tradizionali e statiche.

  1. In questo caso, consente di ottenere valori vecchi e nuovi di colonne da una riga elaborata. In primo luogo, vediamo lo stipendio attuale di King.

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

  2. Per utilizzare le variabili in LiveSQL, le istruzioni verranno completate in PL/SQL. Eseguire questo script. In primo luogo creerà le variabili per il vecchio e il nuovo stipendio, quindi aggiornerà lo stipendio di King utilizzando la clausola RETURNING per impostare le nostre variabili. Esamineremo quindi i risultati.

    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;

    Immagine che mostra come utilizzare la clausola RETURNING dell'istruzione UPDATE e MERGE 

    Old Salary: 6000
New Salary: 7000

    In questo esempio è stata utilizzata un'istruzione UPDATE, ma la clausola RETURNING può essere utilizzata in modo simile con le istruzioni MERGE.

Funzione 8: Usa join in UPDATE e DELETE

È possibile aggiornare i dati della tabella utilizzando i join in base alle condizioni della tabella esterna. Non sono necessarie selezioni secondarie o clausole IN.

  1. Eseguire il seguente rendiconto per visualizzare le informazioni sullo stipendio del dipendente dal reparto di ricerca.

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

  2. Per aggiornare le informazioni sullo stipendio, prima di Oracle Database 23ai è necessario utilizzare un'istruzione nidificata.

    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');

    Con Oracle Database 23ai, puoi usarlo in questo modo:

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

    Immagine che mostra come utilizzare i join in UPDATE e DELETE

  3. Lo stipendio è stato aggiornato correttamente.

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

    Immagine che mostra come utilizzare i join in UPDATE e DELETE

Funzione 9: Usa annotazioni

Le annotazioni sono metadati facoltativi per gli oggetti di database. Un'annotazione è una coppia nome-valore o un nome da sola. Il nome e il valore facoltativo sono campi di testo in formato libero. Un'annotazione è rappresentata come elemento subordinato all'oggetto di database a cui è stata aggiunta l'annotazione. Gli oggetti schema supportati includono tabelle, viste, viste materializzate e indici. Con le annotazioni è possibile memorizzare e recuperare i metadati relativi a un oggetto di database. È possibile utilizzarlo per personalizzare la business logic, le interfacce utente o fornire metadati ai repository di metadati. Può essere aggiunto con l'istruzione CREATE o ALTER a livello di tabella o colonna.

Con le annotazioni è possibile memorizzare e recuperare i metadati sugli oggetti di database. È possibile utilizzarlo per personalizzare la business logic, le interfacce utente o fornire metadati ai repository di metadati.

  1. Creare una tabella annotata EMP_ANNOTATED_NEW con annotazioni di colonna e tabella.

    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');

    Immagine che mostra come utilizzare le annotazioni, nuovi metadati per gli oggetti di database

  2. Le viste del dizionario dati, ad esempio USER_ANNOTATIONS e USER_ANNOTATIONS_USAGE, consentono di monitorare l'uso.

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

    Immagine che mostra come utilizzare le annotazioni, nuovi metadati per gli oggetti di database

Funzione 10: Usa domini SQL

Un dominio SQL è un oggetto dizionario che appartiene a uno schema e incapsula un set di proprietà e vincoli facoltativi e viene creato con un'istruzione CREATE DOMAIN. I domini forniscono attributi di vincoli, visualizzazione, ordinamento e annotazioni. Dopo aver definito un dominio SQL, è possibile definire le colonne di tabella da associare a tale dominio, applicando in modo esplicito le proprietà e i vincoli facoltativi del dominio a tali colonne.

I domini SQL consentono agli utenti di dichiarare l'uso previsto per le colonne. Si tratta di oggetti del dizionario dati in modo che le conoscenze specifiche del dominio astratto possano essere facilmente riutilizzate.

  1. Creare un dominio denominato yearbirth e una tabella denominata 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');

    Immagine che mostra come utilizzare tipi di oggetti leggeri con domini SQL

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

    Immagine che mostra come utilizzare tipi di oggetti leggeri" con i domini SQL

    ``desc persona;

    Nome Null? Type

ID NUMBER(5) NOME VARCHAR2(50) STIPENDIO NUMBER PERSON_BIRTH NUMBER(4) DOMINIO YEARBIRTH

INSERIRE I valori persona (1,'MARTIN',3000, 1988);

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

SELEZIONARE DOMAIN_DISPLAY(person_birth) DA persona;


   ![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`.

SELEZIONARE * DA user_annotations_usage;


   Define reusable domain types (lightweight objects).

CREA DOMINIO EmailAddress COME CONTROLLO VARCHAR2(100) (REGEXP_LIKE(VALORE, '^[^@]+@[^@]+.[^@]+$'));

CREATE TABLE utenti ( user_id NUMBER, e-mail EmailAddress ); ```

Immagine che mostra come utilizzare tipi di oggetti leggeri" con i domini SQL

Limitazioni e restrizioni nelle funzioni di Oracle Database 23ai

Restrizioni generali

Conferme

Altre risorse di apprendimento

Esplora altri laboratori su docs.oracle.com/learn o accedi a più contenuti di formazione gratuiti sul canale YouTube di Oracle Learning. Inoltre, visitare education.oracle.com/learning-explorer per diventare Oracle Learning Explorer.

Per la documentazione del prodotto, visitare Oracle Help Center.