Observação:

Explore os recursos de SQL no Oracle Database 23ai

Introdução

Vamos aprender 10 recursos neste tutorial que você precisa saber e como eles se comparam com os seus homólogos existentes. Estes recursos são:

Objetivos

Pré-requisitos

Recurso 1: Usar a Cláusula FROM

Um recurso interessante introduzido no Oracle Database 23ai é a opcionalidade da cláusula FROM em instruções SELECT. Até essa versão, a cláusula FROM era obrigatória.

Veja alguns benefícios potenciais de um recurso SELECT sem FROM no Oracle Database 23ai.

Recurso 2: Usar o Tipo de Dados BOOLEAN

O Oracle Database 23ai apresenta o novo tipo de dados BOOLEAN. Isso aproveita o uso de colunas/variáveis boolianas verdadeiras, em vez de simulá-las com um valor numérico ou Varchar. A capacidade de escrever predicados boolianos simplifica a sintaxe de instruções SQL.

  1. Crie uma tabela chamada 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. Insira dados na nova tabela. O valor IS_SLEEPING será NOT NULL definido como FALSE como padrão.

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING boolean NOT NULL);

    Imagem mostrando como usar Booliano para SQL 

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING default FALSE);

Table TEST_BOOLEAN altered.

Elapsed: 00:00:00.014

    Aqui, você pode ver os diferentes tipos de entrada booliana para Mick, Keith e Ron. Todos são válidos. Para Mick, o valor FALSO padrão é usado - Mick não está em suspensão.

    1 row inserted.

Elapsed: 00:00:00.006

    Para Keith, usamos um valor NO - Keith não está em suspensão.

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

1 row inserted.

Elapsed: 00:00:00.002

    E para Ron, usamos um valor 1 - Ron está dormindo.

  3. Veja alguns resultados com base em nossos valores boolianos.

    SELECT * FROM test_boolean;

    Você não precisa mais se lembrar de que tipo de sistema booliano você implementou. Como mostramos, usar 0/1, Verdadeiro/Falso, Sim/Não ou qualquer outra entrada comum retornará um valor de tabela preciso.

Recurso 3: Usar a Cláusula DDL IF NOT EXISTS

A partir do Oracle Database 23ai, a nova cláusula DDL IF NOT EXISTS permite decidir como os erros DDL serão tratados. Isso simplifica o script DDL, pois possíveis erros devido à existência ou inexistência de objetos podem ser ocultados para o script.

  1. Primeiro, teste sem usar esse novo recurso. Execute a instrução a seguir.

    DROP TABLE DEPT;

    Como não há tabela DEPT existente para eliminar, veremos um erro: ORA-00942: table or view does not exist.

  2. No entanto, no Oracle Database 23ai, podemos usar o DROP IF EXISTS sem erro. Isso nos dá paz de espírito, evitando erros. Agora, execute a mesma instrução, mas inclua esse novo recurso IF EXISTS.

    DROP TABLE IF EXISTS DEPT;

  3. Da mesma forma, podemos usar esse recurso para criar tabelas, caso elas ainda não existam. Crie essa tabela DEPT.

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

    Imagem mostrando como usar a cláusula DDL IF [NOT] EXISTS

  4. Use este recurso para criar mais tabelas de amostra neste tutorial. Aqui, vamos criar uma tabela de funcionários chamada 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

Recurso 4: Usar INSERT para Várias Linhas

Outro recurso interessante que garante uma melhor coexistência e compatibilidade com outros sistemas de gerenciamento de banco de dados usados com frequência é a instrução INSERT de vários valores.

  1. Em versões anteriores do banco de dados Oracle, como exemplo, a inserção de várias linhas exigia uma instrução de inserção separada para cada linha.

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

    O Oracle Database 23ai introduziu a nova sintaxe permitindo a inserção de todas essas linhas em uma única instrução INSERT, para que você possa inserir várias tuplas em uma DML. Execute a instrução a seguir.

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

    Imagem mostrando como usar INSERTs de Vários Valores

    Além de uma melhor compatibilidade com outros bancos de dados, esta instrução pode ser usada para garantir a consistência de algumas operações de inserção no modo de commit automático. Isso pode ser importante, por exemplo, para aplicativos Oracle APEX que usam esse modo para trabalhar em dados.

  2. Execute a instrução a seguir para usar esse recurso a fim de preencher valores para a tabela 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);

Recurso 5: Usar Novo Construtor VALUE

A partir do Oracle database 23ai, o construtor de valores de tabela foi estendido. Ela agora pode ser usada em instruções INSERT para criar várias linhas em um único comando. Ele também pode ser usado em instruções SELECT e na sintaxe de fatoração de view. Neste último caso, ele simplifica a sintaxe das instruções e evita o uso da tabela DUAL.

A instrução a seguir se parece com um tipo de função de tabela instantânea.

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

Imagem mostrando como usar o construtor Novo valor de tabela

Recurso 6: Usar Aliases na Cláusula GROUP BY

O Oracle Database 23ai introduz a capacidade de usar aliases na cláusula GROUP BY de uma instrução SELECT. Esse recurso simplifica a gravação de consultas com expressões complexas, bem como garante melhor compatibilidade com alguns outros bancos de dados relacionais, como Teradata, MySQL e PostgreSQL.

Por exemplo:

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

No Oracle Database 23ai, isso pode ser escrito de maneira mais simples, conforme mostrado na seguinte imagem:

Imagem mostrando como usar Aliases na cláusula GROUP BY

Recurso 7: Usar a Cláusula RETURNING da Instrução UPDATE e MERGE

Essa cláusula foi implementada anteriormente como parte da instrução EXECUTE IMMEDIATE. No entanto, no Oracle Database 23ai, podemos encontrá-lo como parte de instruções DML tradicionais e estáticas.

  1. Nesse caso, ele permite obter valores antigos e novos de colunas de uma linha processada. Primeiro, vamos ver o salário atual de King.

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

  2. Para usar variáveis em LiveSQL, encapsularemos instruções no PL/SQL. Execute esse script. Primeiro, ele criará as variáveis dos salários antigo e novo e, em seguida, atualizará o salário de King usando a cláusula RETURNING para definir nossas variáveis. Vamos então dar uma olhada nos resultados.

    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;

    Imagem mostrando como usar a cláusula RETURNING da instrução UPDATE e MERGE 

    Old Salary: 6000
New Salary: 7000

    Este exemplo usou uma instrução UPDATE, mas a cláusula RETURNING pode ser usada de maneira semelhante com instruções MERGE.

Recurso 8: Usar Junções em UPDATE e DELETE

Você pode atualizar dados da tabela usando junções com base em condições de tabela estrangeiras. Não há necessidade de subseleções ou cláusula IN.

  1. Execute a instrução a seguir para ver as informações de salário do funcionário do departamento de pesquisa.

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

  2. Para atualizar as informações de salário, antes do Oracle Database 23ai, precisamos usar uma instrução aninhada.

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

    Com o Oracle Database 23ai, você pode usá-lo desta forma:

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

    Imagem mostrando como usar Junções em UPDATE e DELETE

  3. Você pode ver que o salário foi atualizado com sucesso.

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

    Imagem mostrando como usar Junções em UPDATE e DELETE

Recurso 9: Usar Anotações

As anotações são metadados opcionais para objetos de banco de dados. Uma anotação é um par nome-valor ou um nome por si só. O nome e o valor opcional são campos de texto de formato livre. Uma anotação é representada como um elemento subordinado ao objeto de banco de dados ao qual a anotação foi adicionada. Os objetos de esquema suportados incluem tabelas, views, views materializadas e índices. Com anotações, você pode armazenar e recuperar metadados sobre objetos de banco de dados. Você pode usá-lo para personalizar a lógica de negócios, as interfaces do usuário ou fornecer metadados aos repositórios de metadados. Ele pode ser adicionado com a instrução CREATE ou ALTER no nível de tabela ou coluna.

Com anotações, você pode armazenar e recuperar metadados sobre objetos de banco de dados. Você pode usá-lo para personalizar a lógica de negócios, as interfaces do usuário ou fornecer metadados aos repositórios de metadados.

  1. Crie uma tabela anotada EMP_ANNOTATED_NEW com anotações de coluna e tabela.

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

    Imagem mostrando como usar Anotações, novos metadados para objetos de banco de dados

  2. As views do dicionário de dados, como USER_ANNOTATIONS e USER_ANNOTATIONS_USAGE, podem ajudar a monitorar o uso.

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

    Imagem mostrando como usar Anotações, novos metadados para objetos de banco de dados

Recurso 10: Usar Domínios SQL

Um domínio SQL é um objeto de dicionário que pertence a um esquema e encapsula um conjunto de propriedades e restrições opcionais e é criado com uma instrução CREATE DOMAIN. Os domínios fornecem restrições, exibição, ordenação e atributos de anotações. Depois de definir um domínio SQL, você pode definir colunas de tabela a serem associadas a esse domínio, aplicando assim explicitamente as propriedades e restrições opcionais do domínio a essas colunas.

Os domínios SQL permitem que os usuários declarem o uso pretendido para colunas. São objetos de dicionário de dados para que o conhecimento específico de domínio abstrato possa ser facilmente reutilizado.

  1. Crie um domínio chamado yearbirth e uma tabela chamada 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');

    Imagem mostrando como usar tipos de objeto Lightweight com Domínios SQL

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

    Imagem mostrando como usar tipos de objeto Lightweight" com Domínios SQL

    "pessoa descendente";

    Name Null? Tipo

ID NUMBER(5) NAME VARCHAR2(50) SALARY NUMBER PERSON_BIRTH NUMBER(4) DOMAIN YEARBIRTH

INSERIR EM valores de pessoa (1,'MARTIN',3000, 1988);

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

SELECT DOMAIN_DISPLAY(person_birth) FROM pessoa;


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

SELECIONE * A PARTIR DE user_annotations_usage;


   Define reusable domain types (lightweight objects).

CRIAR DOMÍNIO EmailAddress COMO A VERIFICAÇÃO VARCHAR2(100) (REGEXP_LIKE(VALOR, '^[^@]+@[^@]+.[^@]+$'));

CREATE TABLE usuários ( user_id NUMBER, email EmailAddress ); ```

Imagem mostrando como usar tipos de objeto Lightweight" com Domínios SQL

Limitações e Restrições nos Recursos do Oracle Database 23ai

Restrições Gerais

Confirmações

Mais Recursos de Aprendizagem

Explore outros laboratórios em docs.oracle.com/learn ou acesse mais conteúdo de aprendizado gratuito no canal do Oracle Learning YouTube. Além disso, acesse education.oracle.com/learning-explorer para se tornar um Oracle Learning Explorer.

Para obter a documentação do produto, visite o Oracle Help Center.