INTRODUÇÃO AO ORACLE 11g
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 2
INDICE
CONCEITO DE BANCO DE DADOS RELACIONAL ......................................................................................................................6
Conceitos do Modelo Relacional ...................................................................................................................................... 6
Funções do Banco de Dados Relacional ........................................................................................................................... 6
Regras Gerais.................................................................................................................................................................... 6
Propriedades do Banco de Dados Relacional .........................................................................................................................6
Lista Resumida dos Objetos do Banco de Dados .............................................................................................................. 7
Objeto .............................................................................................................................................................................. 7
Table ................................................................................................................................................................................. 7
Constraints de Integridade dos dados .............................................................................................................................. 7
Tipo de Constraint ............................................................................................................................................................ 7
Descrição .......................................................................................................................................................................... 7
Recursos do SQL ............................................................................................................................................................... 8
Recursos do SQL*Plus ...................................................................................................................................................... 8
Categorias de Comandos SQL ........................................................................................................................................... 9
Categoria de SQL ....................................................................................................................................................................9
Descrição ................................................................................................................................................................................9
Categoria de SQL ....................................................................................................................................................................9
Descrição ................................................................................................................................................................................9
Categorias de Comandos SQL*Plus ........................................................................................................................................9
Categoria ................................................................................................................................................................................9
Comando ................................................................................................................................................................................9
Comandos de execução .........................................................................................................................................................9
, EXECUTE, RUN ......................................................................................................................................................................9
Comandos de edição ..............................................................................................................................................................9
LIST, APPEND, CHANGE, DEL, INPUT, EDIT .............................................................................................................................9
Comandos de Sistema ............................................................................................................................................................9
SET, SHOW, PAUSE .................................................................................................................................................................9
Comandos de formatação ......................................................................................................................................................9
COLUMN, CLEAR, BREAK, COMPUTE, TTITLE, BTITLE .............................................................................................................9
Comandos para manipulação de arquivos .............................................................................................................................9
SAVE, GET, START, @ , SPOOL ................................................................................................................................................9
Comandos interativos ............................................................................................................................................................9
PROMPT, ACCEPT, DEFINE, VARIABLE, PRINT ........................................................................................................................9
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 3
Comandos para acesso ao banco de dados ...........................................................................................................................9
CONNECT, DISCONNECT ........................................................................................................................................................9
Comandos em geral ...............................................................................................................................................................9
EXIT, HELP, DESCRIBE (DESC), HOST, QUIT .............................................................................................................................9
FUNDAMENTOS DE SQL .......................................................................................................................................................12
Operadores aritméticos ................................................................................................................................................. 12
Operador ........................................................................................................................................................................ 12
Operador de concatenação ............................................................................................................................................ 12
Operadores de comparação ........................................................................................................................................... 12
Operadores lógicos ........................................................................................................................................................ 13
Operadores de conjunto ................................................................................................................................................ 13
UNION ....................................................................................................................................................................... 13
UNION ALL ................................................................................................................................................................ 13
INTERSECT ................................................................................................................................................................. 14
MINUS ....................................................................................................................................................................... 14
Escrevendo pesquisas simples com o comando SELECT ......................................................................................................14
Selecionando apenas as colunas desejadas .........................................................................................................................15
Mostrando nomes específicos para colunas ........................................................................................................................15
Removendo linhas duplicadas..............................................................................................................................................15
Exibindo linhas específicas de dados ...................................................................................................................................16
Ordenando resultados .........................................................................................................................................................16
Tabela DUAL .........................................................................................................................................................................17
Comando ........................................................................................................................................................................ 19
Salvando comandos SQL em arquivos de Script ............................................................................................................. 19
Retornando o arquivo salvo para o SQL*Buffer ............................................................................................................. 19
Executando o arquivo de script ...................................................................................................................................... 19
Entrando com valores em tempo de execução .............................................................................................................. 20
PROMPT: Permite apresentar um texto para o usuário. ...................................................................................... 21
Variáveis de configuração do ambiente SQL*Plus ......................................................................................................... 22
REALIZANDO CALCULO COM DADOS ...................................................................................................................................24
Funções numéricas simples .................................................................................................................................................25
RESULTADO ..........................................................................................................................................................................25
ARREDONDA M PARA N CASAS DECIMAIS ...........................................................................................................................25
TRUNCA N PARA M CASAS DECIMAIS ..................................................................................................................................25
RESTO DA DIVISAO M POR N ...............................................................................................................................................25
RETORNA VALOR ABSOLUTO DE N.......................................................................................................................................25
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 4
RETORNA O CO-SENO DE ARCO ...........................................................................................................................................25
RETORNA O CODIGO ASCII DO CARACTER INFORMADO .....................................................................................................25
RETORNA A RAIZ QUADRADA DE N ......................................................................................................................................25
Realizando cálculos com NULL:NVL .....................................................................................................................................26
EXPRESSÃO ..................................................................................................................................................................... 27
Exemplo: ..............................................................................................................................................................................27
Funções simples de data ......................................................................................................................................................28
FUNÇÃO ......................................................................................................................................................................... 28
SELECT ROUND(TO_DATE(’27-OCT-1992’),’YEAR’) “EXEMPLO DE ROUND” ........................................................................28
REALIZAR ADIÇÃO DE MESES ATRAVÉS DA FUNÇÃO ADD_MONTHS...................................................................................29
ELEMENTO ..................................................................................................................................................................... 31
MANUPULAÇÃO DE CARACTERES ........................................................................................................................................33
FUNÇÃO ......................................................................................................................................................................... 33
MANIPULAR PARTES DE UM VALOR CARATER COM A FUNÇÃO SUBSTR. ...........................................................................37
MOSTRAR DADOS COM A FUNÇÃO RPAD ............................................................................................................................39
MANIPULAR DADOS COM A FUNÇÃO TRIM.........................................................................................................................39
MOSTRAR E MANIPULAR CARACTERES COM A FUNÇÃO LTRIM ..........................................................................................39
MOSTRAR E MANIPULAR CARACTERES COM A FUNÇÃO CHR .............................................................................................40
MANIPULAR DADOS COM A FUNÇÃO CONCAT ...................................................................................................................40
FUNÇÕES DE GRUPO SIMPLES .............................................................................................................................................41
FUNÇÃO ......................................................................................................................................................................... 41
CRIAR TABELAS A PARTIR DE SUBCONSULTAS .....................................................................................................................75
DELETAR LINHAS A PARTIR DE SUBCONSULTAS ..................................................................................................................76
INSERIR LINHAS DE DADOS A PARTIR DE SUBCONSULTAS ...................................................................................................77
OPERADORES DE CONJUNTO ...............................................................................................................................................78
OPERADOR ...........................................................................................................................................................................78
RETORNA ........................................................................................................................................................................ 78
MINUS ..................................................................................................................................................................................79
ORACLE DATATYPES .............................................................................................................................................................80
Character Datatype ........................................................................................................................................................ 80
Numeric Datatype .......................................................................................................................................................... 80
Date Datatype ................................................................................................................................................................ 80
Datatype ......................................................................................................................................................................... 81
Descrição ........................................................................................................................................................................ 81
Outros Datatypes ........................................................................................................................................................... 81
ANSI/DB2/SQL-DS Datatype ........................................................................................................................................... 82
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 5
CRIANDO TABELAS NO BANCO DE DADOS ...........................................................................................................................82
CONSTRAINTS ......................................................................................................................................................................85
CRIANDO SEQUENCES ..........................................................................................................................................................87
SIMPLIFICANDO O ACESSO AOS DADOS ATRAVÉS DE VISÕES .............................................................................................89
MELHORAR A PERFORMANCE DAS CONSULTAS ATRAVÉS DE ÍNDICES ...............................................................................90
HISTÓRICO DE NOVIDADES POR VERSÕES DO ORACLE .......................................................................................................91
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 6
CONCEITO DE BANCO DE DADOS RELACIONAL
Os conceitos do modelo relacional foram descritos pela primeira vez pelo Dr. E. F. Codd
(do laboratório de pesquisas da IBM) em um artigo de julho de 1970 chamado “A Relational Model
of Data for Large Shared Data Banks”. (“Um Modelo Relacional de Dados para Grandes Bancos de
Dados Compartilhados”). Nesse artigo, o Dr. Codd propôs o modelo relacional para sistemas de
banco de dados.
Os modelos mais populares, naquela época, eram os hierárquicos e de rede, ou mesmo
simples estruturas de dados de arquivos planos. Os sistemas de gerenciamento de bancos de dados
relacionais, ou RDBMS, logo se tornaram bem populares em pouco tempo, devido, especialmente a
sua facilidade de uso e flexibilidade de estrutura. Além disso, houve vários distribuidores
inovadores, como a Oracle, que acrescentaram ao RDBMS um pacote de poderosos produtos de
desenvolvimento de aplicativos e produtos para o usuário, proporcionando uma solução total.
Conceitos do Modelo Relacional
Coleções de objetos ou relações armazenam os dados.
Um conjunto de operadores pode atuar nas relações para produzir outras relações.
Um banco de dados relacional deve possuir integridade de dados, de modo que esses devem
ser precisos e consistentes.
Um exemplo de uma relação é uma tabela. Para recuperar os dados de tabelas, use os comandos
SQL de operações relacionais.
Funções do Banco de Dados Relacional
Gerenciar o armazenamento de dados
Controlar o acesso as dados
Fornecer um meio para recuperar e modificar os dados
Regras Gerais
Valores duplicados não são permitidos em uma chave primária.
Em geral, as chaves primárias não podem ser alteradas.
Chaves estrangeiras são baseadas em valores de dados e são ponteiros puramente lógicos,
não físicos.
O valor de uma chave estrangeira deve coincidir com um valor de chave primária existente
ou, então, deve ser NULL.
Propriedades do Banco de Dados Relacional
Um banco de dados relacional é baseado em uma percepção do mundo real, que consistem em
uma coleção de objetos básicos, chamados de tabelas, e em relacionamentos entre esses objetos.
Uma tabela é um conjunto único de atributos ou características, chamadas de colunas. Por exemplo,
os atributos conta e saldo descrevem uma conta particular em um banco.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 7
Um relacionamento é uma associação entre duas tabelas. Por exemplo, um relacionamento
cliente-conta relaciona um cliente a cada conta que ele possui. Os bancos de dados relacionais
permitem o relacionamento entre as tabelas por meio da definição de chaves de acesso, visando
principalmente evitar inconsistências e duplicação de informações. Alguns tipos de chaves de
acesso são:
Chave primária: é a principal chave de acesso a uma tabela. A criação desta chave faz
com que, automaticamente, a tabela seja ordenada por esta chave e que não seja permitida
duplicidade em seu valor.
Chave única: além da chave primária, uma tabela pode possuir tantas chaves únicas
quantas forem necessárias. Elas chave possibilita que não sejam inseridos dados duplicados
em campos que não fazem parte da chave primária. Por exemplo, se na tabela Pessoa a
chave primária for o código interno da pessoa, podem ser criadas chaves únicas para o
número do CPF/CGC e para o número da previdência social.
Chave secundária: é a chave auxiliar de acesso a uma tabela. A chave secundária também
possui índices relacionados que são utilizados em campos nos quais se efetua constante
pesquisa ou acesso.
Chave estrangeira: permite o acesso e a validação de outras tabelas. Esta chave possibilita
que se estabeleçam os relacionamentos em um banco de dados. A chave estrangeira deve
ser compatível com sua correspondente chave (primária ou única) na outra tabela.
Para acessar o banco de dados, execute um comando SQL (Structured Query Language),
que é a linguagem padrão do American National Standards Institute (ANSI) para operação em
bancos de dados relacionais. A linguagem contém um vasto conjunto de operadores para
combinação e particionamento de relações. O banco de dados pode ser facilmente modificado,
com o uso de comandos SQL. O modelo relacional permite total independência de dados.
Lista Resumida dos Objetos do Banco de Dados
Objeto Descrição
Table Unidade básica de armazenamento composta de linhas e colunas
View Representa, logicamente, subconjuntos de dados de uma ou mais tabelas
Sequence Gera valores de chave primária
Index Melhora o desempenho de algumas consultas
Synonym Nome alternative para um objeto
Program unit Procedimento, função, ou pacote de commandos SQL e PL/SQL
Constraints de Integridade dos dados
Assegura que os usuários somente realizem operações que mantenham um estado correto e
consistente do banco de dados, com constraints de integridade de dados. Todas as constraints de
integridade de dados devem ser reforçadas pelo servidor de banco de dados ou pelo software de
aplicação.
Tipo de Constraint Descrição
Entidade Nenhuma parte de uma chave primária pode ser NULL e o valor
deve ser único
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 8
Referencial Os valores das chaves estrangeiras devem corresponder a uma chave
primária ou devem ser NULL
Coluna Os valores na coluna devem corresponder ao tipo de dados definido
(datatype)
Definido pelo
usuário
Os valores devem estar de acordo com as regras de negócio da
empresa
SQL, SQL*PLUS, PL/SQL E ORACLE SQL DEVELOPER
Os comandos SQL, SQL*Plus, e PL/SQL são usados para acessar e tratar dados
armazenados em um banco de dados Oracle.
SQL é uma linguagem de comandos para comunicações como o Oracle Server a partir de qualquer
ferramenta ou aplicação. O Oracle SQL contém várias extensões.
SQL*PLUS consiste na ferramenta Oracle que reconhece e submete comandos SQL e PL/SQL
para execução no servidor Oracle, possuindo também sua própria linguagem de comandos.
PL/SQL representa a linguagem procedural Oracle que amplia o SQL ao incluir lógica na aplicação
ORACLE SQL DEVELOPER ferramenta gráfica que aumenta a produtividade e simplifica as
atividades de desenvolvimento do banco de dados Oracle.
Recursos do SQL
Pode ser usado por vários usuários, incluindo os que têm pouca ou nenhuma experiência em
programação.
É uma linguagem não-procedural
Reduz o tempo exigido para a criação e manutenção de sistemas
É uma linguagem parecida com o inglês
Recursos do SQL*Plus
Aceita a entrada de comandos “ad hoc” (como e quando necessária)
Aceita entradas SQL e PL/SQL dos arquivos
Edita os comandos SQL com um editor de linha
Controla as definições de ambiente
Formata os resultados das consultas em relatórios básicos
Interage com os usuários finais
Acessa bancos de dados remotos
Especificações do Oracle SQL Developer
Desenvolvido em Java
Suporta Windows, Linux e Mac OS X
Não requer instalador
Conecta a qualquer banco de dados Oracle com versão acima de 9.2
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 9
Ferramenta gratuita disponível para download no site http://www.oracle.com
Necessita do JDK 1.5 que pode ser obtido no site http://java.sun.com
Categorias de Comandos SQL
Categoria de SQL Descrição
DDL (Data Definition Language) Usado para definir, alterar ou eliminar objetos do
banco de dados. Ex: CREATE TABLE, ALTER
INDEX, GRANT, REVOKE, TRUNCATE, etc.
DML (Data Manipulation Language) Comandos usados para acessar, criar ou manipular
dados em estruturas existentes. Ex: SELECT,
INSERT, UPDATE, DELETE, etc.
Transaction Control Controla as alterações realizadas através dos
comandos DML, permitindo salvá-las ou discartá-
las. Ex: COMMIT, ROLLBACK, SAVEPOINT,
SET TRANSACTION
Categoria de SQL Descrição
Session Control Permitem gerenciar as propriedades da sessão do
usuário. Ex: SET ROLE, ALTER SESSION
System Control Usados no gerenciamento das propriedades do
banco de dados através do comando ALTER
SYSTEM.
Categorias de Comandos SQL*Plus
Categoria Comando
Comandos de execução / , EXECUTE, RUN
Comandos de edição LIST, APPEND, CHANGE, DEL, INPUT, EDIT
Comandos de Sistema SET, SHOW, PAUSE
Comandos de formatação COLUMN, CLEAR, BREAK, COMPUTE, TTITLE,
BTITLE
Comandos para manipulação de
arquivos
SAVE, GET, START, @ , SPOOL
Comandos interativos PROMPT, ACCEPT, DEFINE, VARIABLE, PRINT
Comandos para acesso ao banco
de dados
CONNECT, DISCONNECT
Comandos em geral EXIT, HELP, DESCRIBE (DESC), HOST, QUIT
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 10
TABELAS EXEMPLOS DO CURSO
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 11
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 12
FUNDAMENTOS DE SQL
Operadores aritméticos
Operador Propósito Exemplo
+ Adição 2 + 4
- Subtração 30.4 – 8
* Multiplicação 5 * 10
/ Divisão 7.9 / 3
Operador de concatenação
Utilizado para concatenar strings, representado por duas barras verticais (||), exemplo:
„Oracle9i‟ || „Database‟ resulta em „Oracle9iDatabase‟.
Operadores de comparação
Operador Propósito Exemplo
= Testes de igualdade SELECT * FROM emp
WHERE last_name =
„SCOTT‟;
!=
<>
^=
As três formas são usadas para testes
de diferença
SELECT * FROM emp
WHERE first_name <>
„TIGER‟;
<
<=
Testar se valor é menor ou menor-
igual a outro
SELECT last_name FROM
emp WHERE salary < 2000;
>
>=
Testar se valor é maior ou maior-
igual a outro
SELECT first_name, salary
FROM emp WHERE salary >
10000;
[NOT] IN Testar se determinado valor é igual a
qualquer elemento da lista. Caso o
operador NOT seja usado, retornará
TRUE caso o valor não esteja na lista
SELECT first_name, salary
FROM emp WHERE
first_name IN („JOHN‟,
„SAM‟, „MARY‟);
ANY
SOME
Compara o valor com cada elemento
da lista ou da sub-consulta. Deve ser
usado após algum dos operadores
=,!=, >,<,>=,<=
SELECT last_name FROM
emp WHERE salary <= ANY
( SELECT salary FROM emp
WHERE dept = 10);
ALL Compara o valor com TODOS os
elementos da Lista. Deve ser
acompanhado por algum dos
operadores =,!=, >,<,>=,<=
SELECT last_name FROM
emp WHERE salary <=
ALL(500,1200,800)
[NOT] BETWEEN
a AND b
Compara se valor é maior-igual “a” e
menor-igual “b”
SELECT last_name FROM
emp WHERE salary
BETWEEN 5000 and 10000;
[NOT] EXISTS Retorna TRUE se a sub-consulta SELECT last_name FROM
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 13
retorna alguma linha emp A WHERE EXISTS(
Operador Propósito Exemplo
[NOT] LIKE Comparações parciais. O caracter
curinga “%” é usado para comparar
qualquer valor e o caracter “_”
compara um caracter na posição
desejada. A palavra-chave ESCAPE
é usada para tratar o caracter “%” ou
“_” como uma literal e não como um
caracter especial que é seu padrão.
SELECT last_name FROM
emp WHERE last_name LIKE
„_HOR%‟
IS [NOT] NULL O único operador usado para testar
valores NULOS (NULL)
SELECT last_name FROM
emp WHERE salary IS NULL;
Operadores lógicos
São usados para combinar o resultado de duas ou mais condição de comparação produzindo
um resultado único ou invertendo o resultado da comparação.
Operador Propósito Exemplo
NOT Inverte o resultado. SELECT * FROM emp
WHERE NOT (salary <
1000);
AND Retorna TRUE se todas as comparações forem
verdadeiras
SELECT * FROM emp
WHERE last_name =
„JACOB‟ AND sal > 5000;
OR Retorna TRUE se uma das comparações forem
verdadeiras
SELECT * FROM emp
WHERE last_name =
„JACOB‟ OR last_name =
„THOMAS‟;
Operadores de conjunto
Usados para combinar os resultados de duas ou mais pesquisas.
Operador Propósito Exemplo
UNION Retorna todas as linhas das pesquisas. Não
retorna linhas duplicadas
SELECT last_name FROM
emp
UNION
SELECT first_name FROM
emp;
UNION ALL Retorna todas as linhas das pesquisas
incluindo as duplicadas
SELECT last_name FROM
emp
UNION ALL
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 14
SELECT first_name FROM
emp;
Operador Propósito Exemplo
INTERSECT Retorna apenas as linhas existentes em
ambas as pesquisas
SELECT last_name FROM
emp
INTERSECT
SELECT first_name FROM
emp;
MINUS Retorna as linhas da primeira pesquisa que
não existam na segunda
SELECT last_name FROM
emp
MINUS
SELECT first_name FROM
emp;
Escrevendo pesquisas simples com o comando SELECT
Sintaxe:
SELECT <COLUNAS>
FROM <TABELAS>
WHERE <CONDIÇÃO>
ORDER BY <EXPRESSÃO OU CHAVE>
Ex:
SQL9i>select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Listando a estrutura da tabela
SQL9i>DESC EMP
Name Null? Type
----------------------------------------- -------- ---------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 15
Selecionando apenas as colunas desejadas
SQL9i>select empno, ename from emp;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
Mostrando nomes específicos para colunas
SQL9i>select empno Cod, ename As "Nome Func." from emp;
COD Nome Func.
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
Removendo linhas duplicadas
SQL9i>select DISTINCT job from emp;
JOB
---------
ANALYST
CLERK
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 16
MANAGER
PRESIDENT
SALESMAN
Exibindo linhas específicas de dados
SQL9i>select ename, job from emp where deptno=10;
ENAME JOB
---------- ---------
CLARK MANAGER
KING PRESIDENT
MILLER CLERK
SQL9i>select ename, sal, job from emp where sal > 3000;
ENAME SAL JOB
---------- ---------- ---------
KING 5000 PRESIDENT
SQL9i>select ename, sal, job from emp where ename like 'A%';
ENAME SAL JOB
---------- ---------- ---------
ALLEN 1600 SALESMAN
ADAMS 1100 CLERK
SQL9i>select ename, sal from emp where deptno=30 and comm is not null;
ENAME SAL
---------- ----------
ALLEN 1600
WARD 1250
MARTIN 1250
TURNER 1500
Ordenando resultados
SQL9i> select ename, sal
2 from emp
3 where job = 'SALESMAN'
4 order by sal DESC;
ENAME SAL
---------- ----------
ALLEN 1600
TURNER 1500
WARD 1250
MARTIN 1250
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 17
SQL9i>select empno, ename, hiredate
2 from emp
3 where deptno = 20
4 order by 3;
EMPNO ENAME HIREDATE
---------- ---------- ----------
7369 SMITH 17/12/1980
7566 JONES 02/04/1981
7902 FORD 03/12/1981
7788 SCOTT 19/04/1987
7876 ADAMS 23/05/1987
SQL9i>select empno cod, ename nome
2 from emp
3 where job='MANAGER'
4 order by cod;
COD NOME
---------- ----------
7566 JONES
7698 BLAKE
7782 CLARK
SQL9i>select mgr, empno, ename
2 from emp
3 WHERE mgr is not null
4 order by mgr DESC, empno ASC;
MGR EMPNO ENAME
---------- ---------- ----------
7902 7369 SMITH
7839 7566 JONES
7839 7698 BLAKE
7839 7782 CLARK
7788 7876 ADAMS
7782 7934 MILLER
7698 7499 ALLEN
7698 7521 WARD
7698 7654 MARTIN
7698 7844 TURNER
7698 7900 JAMES
7566 7788 SCOTT
7566 7902 FORD
Tabela DUAL
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 18
DUAL é uma tabela criada automaticamente pelo Oracle através do dicionário de dados.
Ebora pertença ao usuário SYS, pode ser acessada por todos os usuários.
Ela possui apenas uma coluna chamada DUMMY, definida como VARCHAR2(1) e contém
uma linha com o valor “X”.
A aplicação mais comum para a tabela DUAL é selecionar variáveis de sistema e computar
expressões com o comando SELECT.
Alternativamente, você pode selecionar uma constante, função ou uma expressão a partir de
qualquer tabela, mas o valor será retornado tantas vezes quanto o número de linhas da tabela.
Ex:
SQL9i>select 'Estou no curso de Introdução ao Oracle' as info from dual;
INFO
--------------------------------------
Estou no curso de Introdução ao Oracle
SQL9i>select SYSDATE, USER from dual;
SYSDATE USER
---------- ------------------------------
13/04/2004 SCOTT
SQL9i>select SYSDATE, USER from dept;
SYSDATE USER
---------- ------------------------------
13/04/2004 SCOTT
13/04/2004 SCOTT
13/04/2004 SCOTT
13/04/2004 SCOTT
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 19
Editando declarações SQL
O SQL*Plus oferece alguns comandos para alterar seus comandos SQL contidos no
SQL*Buffer.
Comando Descrição
L(ist) Lista por completo a declaração SQL contida no SQL*Buffer
A(ppend) Adiciona um texto ao final de uma linha corrente
DEL Elimina a linha corrente
I(nput) Adiciona uma nova linha em uma declaração SQL
C(hange) Alterar textos da linha corrente
CLEAR BUFFER Limpa o buffer
Após o término da edição do comando SQL no SQL*Buffer, utilize um dos comandos
abaixo para executar sua declaração:
/ : para executar a declaração mostrando somente o resultado;
RUN : exibe o comando e executa a declaração.
Salvando comandos SQL em arquivos de Script
Para salvar os comandos SQL a partir do buffer para um arquivo, podemos utilizar o
comando SAVE:
SAVE nome_do_arquivo [REPLACE] [APPEND]
Retornando o arquivo salvo para o SQL*Buffer
GET nome_do_arquivo
Executando o arquivo de script
@ nome_do_arquivo
ou
START nome_do_arquivo
Você também pode executar arquivos de script dentro de outros arquivos script utilizando o
comando @@<nome_do_arquivo>. Assim, o SQL*Plus executará o arquivo procurando no mesmo
diretório do script que o chamou.
Caso desejar comentar seus scripts, utilize o comando REM <texto_comentário> ou entre
com o comentário de várias linhas /* <texto_comentário> */
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 20
Entrando com valores em tempo de execução
È possível criar variáveis para tornar o comando SQL mais interativo. O símbolo “&” é
usado para definir variáveis no SQL*Plus que são solicitadas no momento da execução do
comando. Para suprimir as mensagens de substituição da variável pelo valor informado, utilize o
comando SET VERIFY OFF.
Você também poderá utilizar o comando DEFINE para definir e atribuir automaticamente
um valor para uma variável. O comando UNDEFINE desfaz a definição.
Exemplos:
SQL9i>select empno, ename
2 from emp
3 where deptno = &nr_depto;
Entre o valor para nr_depto: 10
antigo 3: where deptno = &nr_depto
novo 3: where deptno = 10
EMPNO ENAME
---------- ----------
7782 CLARK
7839 KING
7934 MILLER
SQL9i>set verify off
SQL9i>select empno, ename
2 from emp
3 where deptno = &nr_depto;
Entre o valor para nr_depto: 20
EMPNO ENAME
---------- ----------
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD
SQL9i>define nr_depto = 30
SSQL9i>select empno, ename
2 from emp
3 where deptno = &nr_depto;
EMPNO ENAME
---------- ----------
7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7844 TURNER
7900 JAMES
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 21
Utilizando variáveis em arquivos de script
È possível substituir as variáveis no momento da chamada do arquivo de script passando os
valores como parâmetros em suas respectivas posições. Para isso, substitua o nome da variável por
um número de parâmetro.
Exemplo:
SQL9i>select empno, ename
2 from emp
3 where deptno = &1
4 and sal > &2
5
SQL9i>save c:\query1
Criado arquivo c:\query1.SQL
SQL9i>@C:\query1 10 2000
EMPNO ENAME
---------- ----------
7782 CLARK
7839 KING
O SQL*Plus possui também outros dois comandos usados para interagir com o usuário
através de arquivos de script:
PROMPT: Permite apresentar um texto para o usuário.
ACCEPT: Permite receber valores para variáveis através de uma mensagem
desejada.
Exemplo:
Conteúdo do arquivo QUERY2.SQL:
PROMPT ******************
PROMPT Teste de Script !!
PROMPT ==================
ACCEPT nr_depto NUMBER PROMPT "Informe o nr. do depto: "
SET VERIFY OFF
select empno, ename
from emp
where deptno = &nr_depto;
SET VERIFY ON
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 22
Executando o arquivo QUERY2.SQL:
SQL9i>@c:\QUERY2
******************
Teste de Script !!
==================
Informe o nr. do depto: 20
EMPNO ENAME
---------- ----------
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD
Variáveis de configuração do ambiente SQL*Plus
O SQL*Plus possui uma série de variáveis para configuração de seu ambiente. Essas
variáveis são definidas através do comando SET. O comando SHOW <variável> pode ser usado
para conhecer a configuração atual da variável, SHOW ALL lista o ambiente corrente. STORE
SET <nome_do_arquivo> cria um arquivo com a definição das variáveis do ambiente corrente.
Dentre as variáveis de ambiente mais usadas, podemos citar:
Nome da variável Descrição
AUTO[COMMIT] Controla quando o Oracle confirma as alterações. Commit
automático
AUTOT[RACE] Exibe um relatório de execução dos comandos SQL DML
ECHO Lista ou não os comandos de um arquivo de script executado
com START ou @
EDITF[ILE] Nome padrão do arquivo para o comando EDIT
FEED[BACK] Controla quando mostrar o número de registros retornados por
uma pesquisa
HEA[DING] Exibe ou não os cabeçalhos de coluna nas pesquisas
LIN[ESIZE] Número de caracteres exibidos por linha
LONG Máximo em bytes para visualização de campos LONG, CLOB
NEWP[AGE] Número de linhas em branco exibidas a cada página
NULL Texto a ser apresentado para colunas de valor nulo
NUMF[ORMAT Formatação padrão para campos numéricos
PAGES[IZE] Número de linhas por página
PAU[SE] Pausa entre páginas
SERVEROUTPUT Exibe as mensages através da procedure
DBMS_OUTPUT.PUT_LINE em blocos PL/SQL pelo
SQL*Plus
SQLP[ROMPT] Define o prompt de comandos do SQL*Plus
TERM[OUT] Exibe ou não os resultados gerados por arquivos de script
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 23
TI[ME] Apresenta a hora corrente a cada comando
Nome da variável Descrição
TIMI[NG] Exibe o tempo de execução de cada comando SQL ou bloco
PL/SQL
VER[IFY] Define quando exibir o comando SQL antes e após a
substituição dos valores das variáveis
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 24
REALIZANDO CALCULO COM DADOS
Modificar e apresentar dados em variedade de formas usando padrões e funções aritméticas do
SQL.
Realizar cálculos com números usando operadores aritméticos:
Operadores Aritméticos.
+ SOMA
- SUBTRAÇÃO
* MULTIPLICAÇÃO
/ DIVISÃO
Use os operadores aritméticos em qualquer cláusula SQL, exceto na cláusula FROM.
Exemplo:
SELECT SOBRENOME, SALARIO, PCT_COMISSAO,
SALARIO * PCT_COMISSAO / 100 COMISSAO
FROM EMPREGADO
WHERE CARGO = „Sales Representative‟
ORDER BY SALARIO * PCT_COMISSAO / 100 ;
Especificando um critério complexo coma cláusula WHERE usando cálculos.
Exemplo:
SELECT SOBRENOME, SALARIO, PCT_COMISSAO,
SALARIO * PCT_COMISSAO / 100 COMISSAO
FROM EMPREGADO
WHERE CARGO = „Sales Representative‟
AND SALARIO * PCT_COMISSAO / 100 > 200
ORDER BY SALARIO * PCT_COMISSAO / 100 ;
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 25
Siga a precedência dos operadores aritméticos quando formular expressões
complexas.
Regras de precedência:
* Operadores em uma expressão aritmética são avaliados na seguinte ordem:
1 – Multiplicação e Divisão ( * , / ).
2 – Adição e Subtração ( + , - ).
Exemplo:
SELECT SOBRENOME, SALARIO, SALARIO * 12 + 100
FROM EMPREGADO
WHERE COD_DEPTO = 41;
SELECT SOBRENOME, SALARIO, (SALARIO + 100) * 12
FROM EMPREGADO
WHERE COD_DEPTO = 41;
Funções numéricas simples
FUNÇÃO EXEMPLO RESULTADO
ROUND ROUND(M,N) ARREDONDA M PARA N
CASAS DECIMAIS
TRUNC TRUNC(N,M) TRUNCA N PARA M
CASAS DECIMAIS
MOD MOD(M,N) RESTO DA DIVISAO M
POR N
ABS ABS(N) RETORNA VALOR
ABSOLUTO DE N
ACOS ACOS(N) RETORNA O CO-SENO DE
ARCO
ASCII ASCII(„CHARACTER‟) RETORNA O CODIGO
ASCII DO CARACTER
INFORMADO
SQRT SQRT(N) RETORNA A RAIZ
QUADRADA DE N
Esta é uma parte da lista de funções numéricas.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 26
Exemplo:
SELECT SOBRENOME, SALARIO, SALARIO / 22, ROUND(SALARIO/22,0)
FROM EMPREGADO
WHERE COD_DEPTO = 32;
SELECT SOBRENOME, SALARIO, SALARIO / 22, TRUNC(SALARIO/22,0)
FROM EMPREGADO
WHERE COD_DEPTO = 32;
SELECT SOBRENOME, DT_ADMISSAO, SYSDATE, SYSDATE-DT_ADMISSAO
“TOTAL DIAS”, TRUNC( (SYSDATE-DT_ADMISSAO) / 7,0) SEMANAS,
ROUND(MOD( (SYSDATE-DT_ADMISSAO) / 7),0) SEMANAS,DIAS
FROM EMPREGADO
WHERE DT_ADMISSAO > ‟01-JAN-92‟;
Realizando cálculos com NULL:NVL
Representando perda, desconhecimento ou valor inapropriado com nulo, uma expressão ou
função contendo valor nulo avalia nulo.
Exemplo:
SELECT SOBRENOME, SALARIO * PCT_COMISSAO / 100
FROM EMPREGADO
WHERE SALARIO > = 1500;
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 27
Este exemplo mostra que todo valor numérico que calculado ou manipulado com nulos,
sempre retornara nulos.
Converter nulo para valor atual em um calculo com a função NVL
Sintaxe:
NVL ( EXPR1 , EXPR2 )
Onde:
EXPR1: é o valor de original que deve conter null.
EXPR2: é o valor alvo que substituirá os nulls.
Exemplo:
SELECT SOBRENOME, NVL(PCT_COMISSAO,0), SALARIO *
NVL(PCT_COMISSAO,0) / 100 COMISSAO
FROM EMPREGADO
WHERE SALARIO >= 1500;
Realizar cálculos com datas:
Realizando cálculos com datas usando operadores aritméticos.
EXPRESSÃO DESCRIÇÃO
DATA+NÚMERO SOMA À DATA NÚMEROS DE DIAS.
DATA - NÚMERO SUBTRAI À DATA NÚMERO DE DIAS.
DATA-DATA RETORNA O NÚMERO DE DIAS ENTRE AS DATAS.
Exemplo:
SELECT SOBRENOME, DT_ADMISSAO, DT_ADMISSAO + 90
FROM EMPREGADO
WHERE COD_DEPTO = 42;
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 28
CALCULAR O NÚMERO DE DIAS DE UMA DATA OU DA DATA CORRENTE
USANDO A FUNÇÃO SYSDATE.
Funções simples de data
FUNÇÃO EXEMPLO RESULTADO
SYSDATE SYSDATE A DATA E A HORA CORRENTES
ADD_MONTHS ADD_MONTHS(D,N) ADICIONA N MESES A DATA D
LAST_DAY LAST_DAY(D) DATA DO ULTIMO DIA DO MÊS
DA DATA D
NEXT_DAY NEXT_DAY(D,CHAR) DEVOLVE O PRIMEIRO DIA DA
SEMANA DETERMINADO POR
CHAR (MONDAY, FRIDAY ...).
MONTHS_BETWEEN MONTHS_BETWEEN. O NUMERO DE MESES
EXISTENTES (D,E) ENTRE AS
DATAS D e E
ROUND ROUND(TO_DATE
(„DATA‟))
ARREDONDA UMA DATA EM DIA,
MÊS OU ANO.
TRUNC TRUNCT(TO_DATE
(„DATA‟))
TRUNCA UMA DATA EM DIA,MÊS
OU ANO.
Exemplo de ROUND para Data:
SELECT ROUND(TO_DATE(‟27-OCT-1992‟),‟YEAR‟) “EXEMPLO DE ROUND”
FROM DUAL;
Exemplo de TRUNC para Data:
SELECT TRUNC(TO_DATE(‟27-OCT-1992‟), „YEAR‟) “EXEMPLO DE TRUNC”
FROM DUAL;
CALCULAR O NÚMERO DE DIAS A PARTIR DA DATACORRENTE USANDO A
FUNÇÃO SYSDATE.
Exemplo:
SELECT SOBRENOME, (SYSDATE – DT_ADMISSAO) / 7 SEMANAS
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 29
FROM EMPREGADO
WHERE COD_DEPTO = 43;
REALIZAR ADIÇÃO DE MESES ATRAVÉS DA FUNÇÃO ADD_MONTHS
Exemplo:
SELECT SOBRENOME, DT_ADMISSAO,
ADD_MONTHS( DT_ADMISSAO, 6 ) DT_FUTURA
FROM EMPREGADO
WHERE COD_DEPTO = 45;
DETERMINAR O ÚLTIMO DIA DO MÊS À PARTIR DE UMA DATA USANDO A
FUNÇÃO LAST_DAY.
Sintaxe:
LAST_DAY ( D )
Onde:
D, corresponde a data no qual devera ser extraído o valor desejado.
Exemplo:
SELECT CODIGO, DT_ADMISSAO, LAST_DAY(DT_ADMISSAO)
FROM EMPREGADO;
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 30
DETERMINAR O PRÓXIMO DIA DA SEMANA À PARTIR DE UMA DATA
USANDO A FUNÇÃO NEXT_DAY.
Sintaxe:
NEXT_DAY( D,CHAR )
Onde:
D- Será a data determinada para retorno do valor desejado.
CHAR – Será o dia da semana desejada.
Exemplo:
SELECT CODIGO, DT_ADMISSAO,
NEXT_DAY( DT_ADMISSAO, „MONDAY‟ ) PROXIMA_DATA
FROM EMPREGADO;
Determinar o número de meses entre duas datas pela utilização da função
MONTHS_BETWEEN
Exemplo:
SELECT CODIGO, DT_ADMISSAO,
MONTHS_BETWEEN( SYSDATE, DT_ADMISSAO”) “MESES PASSADOS”
FROM EMPREGADO;
REFORMATAÇÃO DE DATAS
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 31
Formato de datas.
ELEMENTO DESCRIÇÃO
DD DIA DO MÊS
DY NOME DO DIA ABREVIADO COM 3 LETRAS
DAY NOME DO DIA COM 9 LETRAS
DDSP NÚMERO DO DIA DO MÊS POR EXTENSO
MM NÚMERO DO MÊS
MON NOME DO MÊS ABREVIADO, COM 3 LETRAS
MONTH NOME DO MÊS ABREVIADO, COM 9 LETRAS
YY DOIS DÍGITOS ANO
YYYY QUATRO DIGITOS ANO
HH:MI:SS HORA, MINUTOS E SEGUNDOS
FM RETIRA ESPAÇO EXTRA EM CADA ELEMENTO DA
MÁSCARA
HH24 HORA DO DIA (0-23)
TH NÚMERO ORDINAL
AM OU PM INDICADOR MERIDIANO
MUDAR O FORMATO PARA MOSTRAR DATAS USANDO O COMANDO
TO_CHAR
Sintaxe:
TO_CHAR ( DATE_VALUE , FORMAT_MASK )
Onde:
DATA_VALUE - é a coluna ou constante que possue um valor data ou numérico.
FORMAT_MASKL, é o formato para data.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 32
Exemplo:
SELECT CODIGO, TO_CHAR(DT_PEDIDO, „MM/YY‟ ) PEDIDOS
FROM PEDIDO
WHERE COD_REPR = 11;
FORMATAR O DISPLAY DE VALORES DO TIPO DATA COM A FUNÇÃO DE
CONVERSÃO TO_CHAR.
Exemplo:
SELECT SOBRENOME,
TO_CHAR( DT_ADMISSAO, „FMDD “OF” MONTH YYYY‟ ) ADMISSAO
FROM EMPREGADO
WHERE COD_DEPTO = 45;
MUDAR O FORMATO PARA ENTREDA DE DATA COM A FUNÇÃO DE
CONVERSÃO TO_DATE.
Sintaxe:
TO_DATE ( CHARACTER_VALUE, FORMAT_MASK )
Onde:
CHACARACTER_VALUE: éa coluna ou constante que está com o tipo caracter
FORMAT_MASK: éo formato para o valor caracter.
Exemplo:
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 33
SELECT TO_DATE(„070393‟,‟MMDDYY‟)
FROM DUAL;
MUDAR O FORMATO DE ENTRADA E SAIDA PARA NUMEROS QUE
ESTEJAM NO FORMATO CARACTER COM A FUNÇÃO DE CONVERSÃO
TO_NUMBER.
Sintaxe:
TO_NUMBER ( CHARACTER_VALUE)
Onde:
CHARACTER_VALUE: é a coluna ou constante que está com o tipo de caracter.
MANUPULAÇÃO DE CARACTERES
Combinar strings de caracteres junto com o operador de concatenação ( || ).
Exemplo:
SELECT NOME| |‟ „| |SOBRENOME NOME
FROM EMPREGADO
WHERE COD_DEPTO = 41;
FUNÇÕES SIMPLES CARACTER
FUNÇÃO EXEMPLO RESULTADO
INITCAP INITCAP(NAME) RETORNA NAME COM O
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 34
PRIMEIRO CARACTER DE CADA
PALAVRA EM MAIÚSCULA
UPPER UPPER(NAME) CONVERTE TODOS OS
CARACTERES DE NAME PARA
MAIÚSCULA
LOWER LOWER(NAME) CONVERTE TODOS OS
CARACTERES DE NAME PARA
MINUSCULA
SUBSTR SUBSTR(C,M,[N]) A SUBSEQUENCIA DE C COM
INICIO NA POSIÇÃO M COM N
CARACTERES. CASO N SEJA
OMITIDO, RETORNA DA
POSIÇÃO M ATÉ O FIM.
LENGTH LENGTH(NAME) NÚMERO DE CARACTERES EM
NAME
LPAD LPAD(CHAR1, N, CHAR2) PREENCHE A ESQUERDA DE
CHAR1 ATÉ O TAMANHO DE N
COM O CHAR2
RPAD RPAD(CHAR1,N,CHAR2) PREENCHE A DIREITA DE
CHAR1 ATÉ O TAMANHO DE N
COM O CHAR 2.
TRIM TRIM(CHAR1 FROM CHAR2) A FUNÇÃO IRÁ REMOVER
QUALQUER CARACTER IGUAL
A CHAR1 A PARTIR DA STRING
DE CHAR2
LTRIM LTRIM(CHAR1, CHAR2) A FUNÇÃO IRÁ REMOVER A
ESQUERDA DE CHAR1 O
CARACTER IGUAL A CHAR2
RTRIM RTRIM(CHAR1, CHAR2) A FUNÇÃO IRÁ REMOVER A
DIREITA DE CHAR1 O
CARACTER IGUAL A CHAR2
CONCAT CONCAT(CHAR1,CHAR2) RETORNA A CONCATENAÇÃO
DE CHAR1 C/ CHAR2 É
EQUIVALENTE AO OPERADOR
“||”
CHR CHR(N) RETORNA PARA O CARACTER
TENDO O EQUIVALENTE
BINÁRIO DE N
REPLACE REPLACE(CHAR1,CHAR2,CHAR3) RETORNA A SUBSTITUIÇÃO DE
CHAR 2 POR CHAR3 A PARTIR
DA STRING CHAR1
TRABALHANDO COM A EXPRESSAO DECODE;
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 35
DECODE (expr, search, result, default )
Onde:
Para avaliar a expressão, o Oracle compara expr para cada valor de search, um a um.
Se expr é igual a search, o Oracle retorna o result correspondente. Se não achou, o
Oracle retorna o default, ou se o default é omitido, retorna null.
Se expr e search contém dados caracter, o Oracle vai compará-los semanticamente.
Exemplo:
SELECT cod_depto, decode(cod_depto,10,‟Finance‟,
31,‟SALES‟,
32,‟SALES‟,
33,‟SALES‟,
34,‟SALES‟,
35,‟SALES‟,
41,‟OPERATION‟,
42,‟OPERATION‟,
43,‟OPERATION‟,
44,‟OPERATION‟,
45,‟OPERATION‟,
„Administration‟) “Nome Depto”
FROM empregado;
MOSTRAR VALORES CARACTER COM INICIAIS MAIÚSCULAS COM A
FUNÇÃO INITCAP
Exemplo:
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 36
SELECT INITCAP(COD_USUARIO) USUARIO, CARGO
FROM EMPREGADO
WHERE CARGO LIKE „VP%‟;
Mostar o sobrenome e salário do presidente:
SELECT SOBRENOME, SALARIO
FROM EMPREGADO
WHERE INITCAP(CARGO) = „Presidente‟;
MOSTRAR E MANIPULAR CARACTERES EM MAIÚSCULAS COM A FUNÇÃO
UPPER.
Exemplos:
SELECT NOME, SOBRENOME
FROM EMPREGADO
WHERE SOBRENOME = „PATEL‟;
NO ROWS SELECTED
SELECT NOME, SOBRENOME
FROM EMPREGADO
WHERE UPPER(SOBRENOME) = „PATEL‟;
NOME SOBRENOME
_____ ___________
Vikram Patel
Radha Patel
MOSTRAR VALORES CARACTER EM MINÚSCULAS COM A FUNÇÃO
LOWER.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 37
Exemplo:
SELECT LOWER(NOME) , LOWER( SOBRENOME) , LOWER(CARGO)
FROM EMPREGADO
WHERE COD_DEPTO = 50;
SELECT NOME
FROM PRODUTO
WHERE LOWER(NOME) LIKE „%ski%‟;
MANIPULAR PARTES DE UM VALOR CARATER COM A FUNÇÃO SUBSTR.
Sintaxe:
SUBSTR( CHAR, M [,N] )
Onde:
CHAR- especifica o valor caracter.
M- especifica a partir de que posição ele começa a pegar.
N- especifica quantas posições irá pegar a partir do elemento M.
Exemplo:
SELECT NOME
FROM PRODUTO
WHERE SUBSTR (NOME, 1,3) = „ACE‟;
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 38
SELECT NOME | | „ „ | | SOBRENOME | |SUBSTR(CARGO,3) “VICE
PRESIDENTES”
FROM EMPREGADO
WHERE CARGO LIKE „VP%‟;
DETERMINAR O NÚMERO DE CARACTERES EM UM VALOR CARACTER
PELO USO DA FUNÇÃO LENGHT
Exemplo:
SELECT SOBRENOME, LENGHT(SOBRENOME)
FROM EMPREGADO
WHERE COD_DEPTO = 50;
SOBRENOME LENGTH (SOBRENOME)
------------------ ---------------------------------
VELASQUEZ 9
SMITH 5
MOSTRAR E MANIPULAR CARACTERES COM A FUNÇÃO LPAD.
Exemplo:
SELECT LPAD( „Page 1‟, 15, „*. „ ) “exemplo LPAD”
FROM DUAL;
LPAD
--------
*.*.*.*.*Page 1
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 39
MOSTRAR DADOS COM A FUNÇÃO RPAD
Exemplo:
SELECT RPAD („MORRISON‟, 12 , „ab‟) “exemplo o RPAD”
FROM DUAL;
RPAD
----------
MORRISONabab
MANIPULAR DADOS COM A FUNÇÃO TRIM
Exemplo:
SELECT TRIM ( 0 FROM 009872348900) “Exemplo TRIM”
FROM DUAL;
TRIM
----------
98723489
MOSTRAR E MANIPULAR CARACTERES COM A FUNÇÃO LTRIM
Exemplo:
SELECT LTRIM(„xyxXxyLASTWORD‟,‟xy‟) “LTRIM example”
FROM DUAL;
LTRIM
-----------
XxyLASTWORD
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 40
MOSTRAR E MANIPULAR CARACTERES COM A FUNÇÃO RTRIM
Exemplo:
SELECT RTRIM(„BROWNINGyxXxy‟,‟xy‟) “RTRIM ”
FROM DUAL;
RTRIM
_______
BROWNINGyxX
MOSTRAR E MANIPULAR CARACTERES COM A FUNÇÃO CHR
Exemplo:
SELECT CHR(67) | |CHR(65)| |CHR(84) “GATO”
FROM DUAL;
GATO
--------
CAT
MANIPULAR DADOS COM A FUNÇÃO CONCAT
Exemplo:
SELECT CONCAT(CONCAT(nome, „ é um „ ) cargo) “cargo”
FROM empregado
WHERE código = 7900;
Cargo
----------
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 41
JAMES é um balconista
FUNÇÕES DE GRUPO SIMPLES
FUNÇÃO EXEMPLO RESULTADO
AVG AVG(SALARIO) Média de Salário; Ignora Valores
Nulos
MAX MAX(SALARIO) Maior Valor de Salário
MIN MIN(SALARIO) Menor Valor de Salário
SUM SUM(SALARIO) Soma dos Valores de Salário
COUNT COUNT(SALARIO)
COUNT(*)
Número de linhas para o salário não
nulo
O * faz contar todas as linhas
Obs: È apenas uma parte da lista de funções de grupo.
EXIBIR TOTALIZAÇÃO DE RESULTADOS PARA GRUPOS DE LINHAS COM
AS CLÁUSULAS GROUP BY E HAVING.
Declarações select com novas cláusulas.
Sintaxe:
SELECT NOME_COLUNA [ NOME_COLUNA ]
FROM NOME_TABELA
WHERE CONDICAO
GROUP BY EXPRESSÃO_GROUP_BY
Onde:
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 42
EXPRESSÃO_GROUP_BY = especifica a(s) coluna(s) pelos quais serão agrupados pela
função de grupo.
Exemplo:
SELECT CREDITO, COUNT(*) “NUMERO DE CLIENTES”
FROM CLIENTE
GROUP BY CREDITO;
CREDITO NUMERO DE CLIENTES
------------ ---------------------------------
EXCELLENT 8
GOOD 3
POOR 3
SELECT COD_DEPTO, COUNT(*) “NUMERO DE FUNCIONARIOS”
FROM EMPREGADO
GROUP BY COD_DEPTO;
COD_DEPTO NUMERO DE FUNCIONARIOS
----------------- --------------------------------------------
10 1
31 2
32 1
Obs: Sempre que for utilizado uma função de grupo deverá ser utilizado a cláusula GROUP
BY.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 43
NUNCA SELECIONE UMA COLUNA SIMPLES COM UMA FUNÇÃO DE GRUPO
SEM A CLÁUSULA GROUP BY:
Exemplo:
SELECT COD_DEPTO, CARGO, COUNT(*)
FROM EMPREGADO;
SELECT COD_DEPTO, CARGO, COUNT(*)
ERROR AT LINE 1:
ORA-00937: NOT A SINGLE-GROUP GROUP FUNCTION
Obs: Devemos então obrigatoriamente incluir todas as colunas na cláusula GROUP BY;
Exemplo:
SELECT COD_DEPTO, CARGO, COUNT(*)
FROM EMPREGADO
GROUP BY COD_DEPTO, CARGO;
COD_DEPTO CARGO COUNT(*)
------------------ ---------- ------------
10 VP, FINANCE 1
31 SALES REPRESENTATIVE 1
31 STOCK CLERK 2
MOSTRAR LINHAS ESPECÍFICAS OU GRUPOS ESPECÍFICOS UTILIZANDO A
CLÁUSULA HAVING.
Sintaxe:
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 44
SELECT NOME_COLUNA [NOME_COLUNA]
FROM NOME_TABELA
WHERE CONDIÇÃO
GROUP BY EXPRESSÃO_GROUP_BY
HAVING CONDIÇÃO
Onde:
HAVING CONDIÇÃO = > restringe o grupo de linhas retomadas daqueles grupos para
especificar uma condição TRUE.
Exemplo:
SELECT CARGO, 12 * AVG(SALARIO)
FROM EMPREGADO
GROUP BY CARGO
HAVING 12* AVG(SALARIO) > 10000;
CARGO 12* AVG(SALARIO)
----------------- --------------------
PRESIDENT 34115.28
STOCK CLERK 11388
Exemplo:
SELECT CARGO, SUM(SALARIO) “SOMA SALARIO”
FROM EMPREGADO
WHERE CARGO NOT LIKE „VP%‟
GROUP BY CARGO
HAVING SUM(SALARIO) > 5000
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 45
ORDER BY SUM(SALARIO);
CARGO SOMA SALARIO
------------ -----------------------
WAREHOUSE MANAGER 6157
SALES REPRESENTATIVE 7380
STOCK CLERK 9490
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 46
MOSTRAR DADOS A PARTIR DE MÚLTIPLAS TABELAS: OVERVIEW
JOINS
Um join é uma query que combina linhas a partir de duas ou mais tabelas ou visões.
O Oracle realiza um join sempre que múltiplas tabelas são consultadas nas query´s a partir da
clausula FROM.
A lista no select das consultas podem selecionar quaisquer colunas a partir destas tabelas. Se
qualquer uma das tabelas possuem uma coluna com nome em comum, você deve qualificar todas as
referencias destas colunas na query, com nome da tabela antes das colunas para eliminar
ambigüidade.
CONDIÇÕES DE JOINS
Consultas com joins devem conter condições com a clausula WHERE que comparam duas colunas,
cada uma de tabela diferente. Tal condição é chamada condição Join. Para executar um join, Oracle
combina pares de linhas, cada uma contendo linha de cada tabela, para que possa avaliar para
VERDADE a condição de join. As colunas na condição de join não precisam aparecer ta,bem na
lista do select.
Para executar um join de três ou mais tabelas, Oracle primeira une duas das tabelas baseadas nas
condições de join comparando suas colunas e então une o resultado para outra tabela em condições
de join contendo colunas das tabelas unidas e a nova tabela. Oracle continua este processo ate que
todas as tabelas sejam unidas no resultado. O otimizador determina a ordem em que o Oracle une
tabelas baseadas nas condições do join, índices nas tabelas, etc.
Além condições de joins, a clausula WHERE de um join pode conter também outras condições que
se referem as colunas de só uma tabela. Estas condições podem adicionar restrições as linhas
retornadas pelo join da query.
EXISTEM QUATRO TIPOS DE JOINS:
NOEQUIJOINS
Um noequijoins é uma condição contendo um operador igualdade. Um noequijoins conbina linhas
que tem valores equivalentes para as colunas especificadas só que comparando a partir de uma
região de dados, como se estivesse fazendo um IN ou um BETWEEN. Dependendo do algoritmo
interno o otimizador escolhe o join a executar.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 47
EQUIJOINS
Um equijoin é um join com uma condição contendo um operador igualdade.Um equijoin combina
linhas que tem valores equivalentes para as colunas especificadas. Dependendo do algoritmo
interno o otimizador escolhe o join a executar.
SELF JOINS
Um selfjoin é um join de uma tabela com ela mesma. Esta tabela aplica duas vezes na clausula
FROM seguindo por um alias na tabela que qualifica nomes de colunas na condição join. Para
realizar um selfjoin, o Oracle combina e retorna linhas da tabela que satisfaçam a condição join.
OUTER JOINS
Um outer join extende o resultado de um join simples. Um outer join retorna todas as linhas que
satisfaçam a condição joine aquelas linhas a partir de uma tabela que não satisfaçam a condição de
join da outra tabela. Tais linhas não são retornadas pelo join simples. Para escrever uma consulta
que realiza um outer join de tabelas A e B e retorna todas as linhas a partir da A, deve-se aplicar o
operador (+) para todas as colunas de B na condição join. Para todas as linhas em A que não tiver
linhas em B, o Oracle retorna null para qualquer lista selecionada contendo colunas B.
PRODUTO CARTESIANO
Se duas tabelas em um join não teve sua condição join escrita, o Oracle retorna um produto
Cartesiano, ou seja, Oracle combina cada linha de uma tabela com cada linha da outra tabela, Um
produto cartesiano permite gerar muitas linhas e raramente é usado, Por exemplo, o produto
cartesiano de duas tabelas, cada com 100 linhas, tem 10000 linhas.
MOSTRAR LINHAS DE DUAS OU MAIS TABELAS ESCREVENDO UMA
CONDIÇÃO SIMPLES DE LIGAÇÃO NA CLAUSULA WHERE.
Especificação de ligação em consulta simples. EQUIJOIN (SIMPLE JOIN)
Sintaxe:
SELECT TABELA.COLUNA, TABELA.COLUNA ………..
FROM TABELA1 , TABELA2 ..............
WHERE TABELA1.COLUNA = TABELA2.COLUNA
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 48
Onde:
TABELA.COLUNA: é a tabela e a coluna do dado que será retornado.
TABELA1.COLUNA: é a condição dos joins ou relações.
TABELA2.COLUNA: das tabelas juntas, o nome da tabela deverá aparecer antes que o
nome da coluna.
Quando escrever uma declaração select que ligue tabelas, preceda o nome da coluna com o
nome da tabela para maior clareza.
SELECT EMPREGADO.SOBRENOME,EMPREGADO.COD_DEPTO,
DEPARTAMENTO.NOME
FROM EMPREGADO, DEPARTAMENTO
WHERE EMPREGADO.COD_DEPTO = DEPARTAMENTO.CODIGO;
SELECT DISTINCT DEPTO.CODIGO “COD. DEPTO”, REG.CODIGO “CODIGO
REGIAO”, REG.NOME “NOME REGIAO”
FROM DEPARTAMENTO DEPTO, REGIAO REG
WHERE DEPTO.COD_REGIAO = REG.CODIGO
NOSTRAR LINHAS ESPECIFICAS A PARTIR DE TABELAS RELACIONADAS.
Exibir linhas especificas de tabelas especificando condições de consulta em adição à
condição na cláusula WHERE.
Exemplo:
SELECT EMPREGADO.SOBRENOME, EMPREGADO.COD_DEPTO,
DEPARTAMENTO.NOME
FROM EMPREGADO, DEPARTAMENTO
WHERE EMPREGADO.COD_DEPTO = DEPARTAMENTO.CODIGO
AND EMPREGADO.SOBRENOME = „MENCHU‟ ;
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 49
SOBRENOME COD_DEPTO NOME
------------------ ----------------- ----------------
MENCHU 420 PERATIONS
MOSTRAR DADOS ATRAVÉS DE UM NOEQUIJOIN
Retornar linhas de uma tabela que tenha ligação direta com as linhas de uma outra tabela com um
“NOEQUIJOIN”.
Sintaxe:
SELECT TABELA.COLUNA, TABELA.COLUNA...
FROM TABELA1, TABELA2
WHERE TABELA1.COLUNA BETWEEN TABELA2.COLUNA
AND TABELA2.COLUNA
MOSTRAR DADOS SEM UMA RELAÇÃO DIRETA.
Retornar linhas de uma tabela que não tenha ligação direta com as linhas de uma outra
tabela com um “OUTER JOIN”.
Sintaxe:
SELECT TABELA.COLUNA, TABELA.COLUNA . . .
FROM TABELA1, TABELA2
WHERE TABELA1.COLUNA = TABELA2.COLUNA(+)
OU
SELECT TABELA.COLUNA, TABELA.COLUNA . . .
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 50
FROM TABELA1, TABELA2
WHERE TABELA1.COLUNA(+) = TABELA2.COLUNA
Onde:
(+) é o operador especial para junção externa, ou seja, se um dado existir somente em uma
tabela, com este símbolo será possível retornar esta linha faltando o outro valor.
Acrescentar o símbolo OUTER JOIN seguido ao nome da tabela que não tem linhas
correspondentes.
Exemplo:
SELECT EMPREGADO.SOBRENOME, EMPREGADO.CODIGO, CLIENTE.NOME
FROM EMPREGADO, CLIENTE
WHERE EMPREGADO.CODIGO(+) = CLIENTE.COD_REPR
ORDER BY EMPREGADO.CODIGO
MOSTRAR DADOS DE LINHAS DIFERENTES NA MESMA TABELA
Ligando uma tabela consigo mesma como se fosse duas tabelas separadas com um SELF-
JOIN.
Exemplo:
SELECT TRABALHADOR.SOBRENOME| | „TRABALHA PARA‟| |
GERENTE.SOBRENOME “EMPREGADOS”
FROM EMPREGADO TRABALHADOR, EMPREGADO GERENTE
WHERE TRABALHADOR.COD_GERENTE = GERENTE.CODIGO;
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 51
MOSTRAR DADOS A PARTIR DE MÚLTIPLAS TABELAS
Mostrar dados a partir de tabelas relacionadas
Equijoins Um equijoin é um join com uma condição contendo um operador
igualdade. Um eguijoin combina linhas que tem valores equivalentes
para as colunas especificadas. Dependendo do algoritimo interno o
otimizador ecolhe o join executar.
Self Joins Um self join é um join de uma tabela com ela mesma.
Esta tabela aplica duas vezes na clausula FROM seguido por um alias
na tabela que qualifica nomes de colunas na condição join. Para realizar
um self join, o Oracle combina e retorna linhas da tabela que satisfaçam
a condição join.
Outer Joins Um outer join extende o resultado de um join simples.
Um outer join retorna todas a linhas que satisfaçam a condição join e
aquelas linhas a partir de uma tabela que não satisfaçam a condição de
join de outra tabela. Tais linhas não são retornadas pelo join simples.
Para escrever uma consulta que realiza um outer join de tabelas A e B e
retorna todas as linhas a partir A, deve-se aplicar o operador(+) para
todas as colunas de B na condição join. Para todas as linhas em A que
não tiver linhas em B, o Oracle retorna null para qualquer lista
selecionada contendo colunas B
NoEquijoins Um noequijoin é um join com uma condição contendo um operador
igualdade. Um noequijoin combina linhas que tem valores equivalentes
para as colunas especificadas, usando uma região de valores com
operador BETWEEN. Dependendo do algoritimo interno o otimizador
escolhe o join a executar.
Especificação de ligação em consulta simples. EQUIJOIN (SIMPLEJOIN)
Sintaxe:
SELECT TABELA.COLUNA, TABELA.COLUNA . . .
FROM TABELA1, TABELA2 . . .
WHERE TABELA1.COLUNA = TABELA2.COLUNA
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 52
Retornar linhas de uma tabela que não tenha ligação direta com as linhas de uma outra
tabela com um “OUTER JOIN”.
Sintaxe:
SELECT TABELA.COLUNA, TABELA.CONULA . . .
FROM TABELA1, TABELA2
WHERE TABELA1.COLUNA = TABELA2.COLUNA(+)
OU
SELECT TABELA.COLUNA, TABELA.CONULA . . .
FROM TABELA1, TABELA2
WHERE TABELA1.COLUNA(+) = TABELA2.COLUNA
Onde:
(+) é o operador especial para junção externa, ou seja, se um dado existir somente em uma
tabela, com este símbolo será possível retornar esta linhas faltando o outro valor.
Ligando uma tabela consigo mesma como se fosse duas tabelas separadas com um SELF-
JOIN.
Sintaxe:
SELECT ALIAS1.COLUNA, ALIAS2.COLUNA. . .
FROM TABELA1 ALIAS1, TABELA2 ALIAS2
WHERE ALIAS1.COLUNA = ALIAS2.COLUNA
PASSANDO VALORES ENTRE CONSULTAS
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 53
1. PASSAR UMA LINHA DE DADO DE UMA CONSULTA PRINCIPAL PARA
SUBCONSULTA.
2. PASSAR MÚLTIPLAS LINHAS DE DADOS DE UMA CONSULTA
PRINCIPAL PARA SUBCONSULTA.
3. ANINHAR MÚLTIPLAS SUBCONSULTAS COM UMA CONSULTA
PRINCIPAL.
PASSANDO VALORES ENTRE CONSULTAS OVERVIEW
Mostrar dados com uma query que dependa dos resultados de outras QUERYS escrevendo
subconsultas.
Justificativas Para Se Escrever Subconsultas
Mostrar dados quando o valor constante para uma condição de cláusula WHERE
não é informada.
Mostrar dados quando uma função de grupo deve ser usado para restringir linhas na
cláusula WHERE.
Mostrar dados baseados sobre condições complexas que requer múltiplas subconsultas.
ANINHAR SUBCONSULTAS QUE RETORNEM UMA ÚNICA LINHA.
Restringir linhas a serem exibidas pela QUERY principal dependendo de uma linha
retornada pela subconsulta.
Sintaxe:
SELECT COLUMN_NAME, [ COLUMN_NAME ]
FROM TABLE_NAME
WHERE EXPRESSION OPERATOR
( SELECT COLUMN_NAME, [,COLUMN_NAME]
FROM TABLENAME)
Onde:
OPERATOR = consiste em um operador de comparação como por exemplo: = , < , > ,
IN.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 54
Exemplo:
SELECT SOBRENOME, CARGO
FROM EMPREGADO
WHERE CARGO = (SELECT CARGO
FROM EMPREGADO
WHERE SOBRENOME = „Smith‟ ) ;
Uma subconsulta dever ser incluída em parênteses.
Linhas simples de subconsultas usa os seguintes operadores: = , <>, >=, > , <, OR <=
Se uma subconsulta for retornar somente uma linha pode-se utilizar o operador =, no
entanto, se a mesma for retornar mais de uma linha deverá ser utilizado o operador IN.
Poderá se aplicar na cláusula WHERE várias condições, cada uma utilizando uma ou mais
subconsultas.
Exemplo:
SELECT SOBRENOME, CARGO, COD_DEPTO
FROM EMPREGADO
WHERE COD_DEPTO = (SELECT COD_DEPTO
FROM EMPREGADO
WHERE UPPER(SOBRENOME) = „BIRI‟ );
Mostrar dados com uma consulta principal a partir de uma outra tabela com subconsulta.
Exemplo:
SELECT CODIGO, DT_PEDIDO
FROM PEDIDO
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 55
WHERE COD_CLIENTE = (SELECT CODIGO
FROM CLIENTE
WHERE UPPER(NOME) = „MUNCH SPORTS‟ );
Mostrar dados a partir de uma QUERY Principal usando uma função de grupo em uma
subconsulta que retorne uma única linha.
Exemplo:
SELECT SOBRENOME, CARGO, SALARIO
FROM EMPREGADO
WHERE SALARIO > (SELECT AVG(SALARIO)
FROM EMPREGADO);
ANINHAR SUBCONSULTAS QUE RETORNEM MULTIPLAS LINHAS
Restringir linhas a serem mostradas pela consulta principal dependendo do retorno de várias linhas
da subconsulta. Por exemplo: Mostrar os empregados que trabalhem em departamentos que
pertençam a região 2
Exemplo:
SELECT CODIGO, SOBRENOME, SALARIO, COD_DEPTO
FROM EMPREGADO
WHERE COD_DEPTO IN ( SELECT CODIGO
FROM DEPARTAMENTO
WHERE COD_REGIAO = 2 );
Uma subconsulta que retorne várias linhas deve usar um operador compatível para comparação de
vários valores.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 56
Exemplos:
SELECT CODIGO, SOBRENOME, COD_DEPTO
FROM EMPREGADO
WHERE COD_DEPTO = (SELECT COD_DEPTO
FROM EMPREGADO
WHERE UPPER(SOBRENOME) = „PATEL‟);
ERROR:
ORA-01427: SINGLE-ROW SUBQUERY RETURNS MORE THAN ONE ROW.
---------------------------------------------------------------
SELECT CODIGO, SOBRENOME, COD_DEPTO
FROM EMPREGADO
WHERE COD_DEPTO IN (SELECT COD_DEPTO
FROM EMPREGADO
WHERE UPPER(SOBRENOME) = „PATEL‟ );
Mostrar dados baseados em condições complexas que requerem múltiplas subconsultas em uma
única declaração SELECT.
Exemplo:
SELECT CODIGO, SOBRENOME, DT_ADMISSAO
FROM EMPREGADO
WHERE SALARIO < (SELECT AVG(SALARIO)
FROM EMPREGADO)
AND COD_DEPTO IN (SELECT COD_DEPTO
FROM EMPREGADO
WHERE UPPER(SOBRENOME) = „PATEL‟);
Exemplo:
SELECT CODIGO, NOME, CREDITO
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 57
FROM CLIENTE
WHERE COD_REGIAO IN (SELECT CODIGO
FROM REGIAO
WHERE NOME = „NORTH AMERICA‟)
OR COD_REPR = (SELECT CODIGO
FROM EMPREGADO
WHERE UPPER(SOBRENOME) = „NAGAYAMA‟);
UTILIZANDO SUBQUERY´S EM SUBSTITUIÇÃO A TABELAS CLAUSULA
FROM:
Podemos ao invés de buscar dados em uma tabela indicada na clausula FROM, podemos escrever
uma subquery no qual irá retornar linhas e colunas, sendo utilizado como se fosse uma tabela física.
Exemplo:
Select b.cod_depto “depto”, b.sal_depto, a.sal_empresa,
Round((b.sal_depto / a.sal_empresa) * 100,2) “ %sal depto em rel.empresa”
From (select sum(salário) sal_empresa from empregado) a,
(select cod_depto, sum(salário) sal_depto
from empregado
group by cod_depto) b
depto SAL_DEPTO SAL_EMPRESA %sal depto em rel.empresa
Exemplo:
select a.código “depto”, b.nome “emp”
from (select código from departamento) a,
(select cod_depto, nome
from empregado ) b
where a.código = b.cod_depto;
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 58
MANIPULANDO DADOS
1. Adicionar novas linhas ao Banco de Dados.
2. Gerar automaticamente valores de chaves para novas linhas.
3. Modificar linhas existentes dentro da tabela.
4. Remover linhas existentes na tabela.
5. Controlar mudanças de dados nas tabelas.
6. Executar a mesma declaração SQL repetidamente com diferentes valores.
MANIPULAR DADOS DENTRO DO BANCO DE DADOS COM DECLARAÇÕES
SQL.
Declarações de manipulação de dados.
DECLARAÇÃO DESCRIÇÃO
INSERT ADICIONA UMA NOVA LINHA À TABELA
UPDATE MODIFICA LINHAS EXISTENTES
DELETE REMOVE LINHAS EXISTENTES
COMMIT TORNA TODAS MUDANÇAS DE DADOS PENDENTES EM
PERMANENTES
ROLLBACK DESFAZ TODAS MUDANÇAS DE DADOS PENDENTES
INSERIR DADOS
Adicionar uma nova linha à tabela com a declaração “INSERT”.
Sintaxe:
INSERT INTO TABELA (COLUNA)
VALUES (VALORES)
Onde:
Tabela: é o nome da tabela.
Coluna: é o nome da coluna.
Valores: é o valor correspondente para a coluna.
Obs:
Uma tabela deve ser criada antes de inserir dados;
Separe os valores com vírgulas;
Use o comando DESCRIBE para mostrar ordem e tipo de colunas;
Cada valor deve ser comparado ao tipo de dado da coluna alvo.
Feche os valores VARCHAR2, CHAR, DATE em aspas simples.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 59
Exemplo:
Gravar informações sobre um novo depto de finanças.
DESC DEPARTAMENTO
NOME NULL? TYPE
CODIGO NOT NULL NUMBER(7)
NOME NOT NULL VARCHAR2(25)
REGIAO NUMBER(7)
INSERT INTO DEPARTAMENTO
VALUES (11,‟FINANCE‟,2);
OBS: Digite os valores de acordo com a ordem “DEFAULT” de colunas na tabela. Inclua caracteres
e valores de data dentro de aspas simples, não inclua aspas entre valores numéricos.
Liste os nomes de colunas na cláusula INSERT para:
Entre com os dados em uma seqüência especifica;
Os valores devem ser listados na ordem especifica pela cláusula INSERT.
Exemplo:
Inserir um novo empregado, mas somente assinar o código do empregado, o último nome, o
primeiro nome, o código de usuário, a data corrente, o código do gerente e o código do depto.
INSERT INTO EMPREGADO (CODIGO, SOBRENOME , NOME , COD_USUARIO,
DT_ADMISSAO, COD_CLIENTE, COD_DEPTO)
VALUES (26, „HERING‟,‟ELIZABETH‟,‟EHERING‟,‟12-DEC-85‟,2,32);
Obs:
Digite valores de acordo com a ordem em que as colunas estão digitadas.
Insira a data e hora corrente na nova linha com a função “SYSDATE”.
INSERIR VALORES ESPECIAIS
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 60
Inserindo “NULLS”
MÉTODO DESCRIÇÃO
EXPLICITO ESPECIFIQUE O “NULL” NA LISTA DE
INSERÇÃO
EXPLICITO PARA CARACTERES “STRINGS” E DO
TIPO DATA SOMENTE, ESPECIFIQUE
UMA “STRING” VAZIA DENTRO DE
ASPAS SIMPLES NA LISTA DE
INSERÇÃO
IMPLICITO OMITA A COLUNA LISTA DE
INSERÇÃO
Exemplo:
DESC CLIENTES
MOME NULL? TYPE
CODIGO NUMBER(5)
NOME VARCHAR2(30)
EMPRESA VARCHAR2(30)
ENDERECO VARCHAR2(30)
FONE VARCHAR2(15)
DEBITO NUMBER(10)
INSERT INTO CLIENTES
VALUES (220,‟ESPORTES EM AÇÃO‟,NULL, NULL, NULL, NULL);
Obs:
A coluna selecionada DEVE permitir valores “NULL” como visualizado com o comando
“DESCRIBE”.
ATUALIZAR DADOS
Modificar linha(s) existente(s) com a declaração “UPDATE”.
Sintaxe:
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 61
UPDATE TABELA
SET COLUNA = VALOR
WHERE CONDIÇÃO
Onde:
TABELA: É O NOME DA TABELA A SER ATUALIZADA
COLUNA: É O NOME DA COLUNA A SER ATUALIZADA
VALOR: È O NOVO VALOR PARA A COLUNA.
CONDIÇÃO: IDENTIFICA A LINHA OU LINHAS A SEREM ATUALIZADAS.
Obs:
Verifique os nomes da colunas com o comando “DESCRIBE”
Confirme a operação de atualização pela visualização das linhas a serem atualizadas
com a declaração “SELECT”]
Atualizar uma única linha identificando-a na cláusula “WHERE”
Exemplos:
UPDATE EMPREGADO
SET COD_DEPTO = 20
WHERE CODIGO = 2;
UPDATE EMPREGADO
SET COD_DEPTO = 32, SALARIO = 3200,
WHERE CODIGO = 5;
DELETANDO DADOS
Remova linha(s) existentes(s) com a declaração “DELETE”.
Sintaxe:
DELETE FROM TABELA
WHERE CONDIÇÃO]
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 62
Onde:
TABELA: É o nome da tabela.
CONDIÇÃO: Identifica a linha ou linhas a serem EXCLUÍDAS.
Obs:
Verifique os nomes das colunas com o comando “DESCRIBE”.
Confirme a operação de exclusão pela visualização da linhas a serem excluídas com a
declaração “SELECT”.
DELETAR UMA LINHA SIMPLES PELA IDENTIFICAÇÃO NA CLÁUSULA
“WHERE”
Exemplo:
DELETE FROM DEPARTAMENTO
WHERE CODIGO = 52;
Obs:
Remova várias linhas pela identificação de um grupo de linhas na cláusula
WHERE.
Nunca omita a cláusula WHERE, pois será deletada toda a tabela.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 63
TRANSAÇÕES COMMIT E ROLLBACK
Controlar as transações lógicas com declarações COMMIT e ROLLBACK, uma
transação é uma série de operações de manipulações de dados relacionadas entre si.
Controle Lógico de Transações
DECLARAÇÃO DESCRIÇÃO
COMMIT FINALIZA A TRANSAÇÃO CORRENTE TORNANDO
PERMANENTE TODAS AS MUDANÇAS PENDENTES NO
BANCO DE DADOS.
ROLLBACK FINALIZA A TRANSAÇÃO CORRENTE DESCARTANDO
TODAS AS MUDANÇAS PENDENTES.
Todas mudanças de dados efetuadas durante a transação é temporária até que a transação
seja efetivada, ou seja, dado um “COMMIT”.
ESTADO DOS DADOS ANTES DO “COMMIT” OU „ROLLBACK”
As operações de manipulação de dados primeiramente afetam o “BUFFER” do banco de
dados. O estado anterior dos dados pode ser recuperado.
O usuário corrente pode rever os resultados das operações de manipulação de dados com a
declaração “SELECT”.
Outros usuários não podem ver os resultados das operações de manipulação de dados do
usuário corrente.
As linhas afetadas estão “TRAVADAS” (LOCKED); outros usuários não podem mudar os
dados dentro das linhas afetadas.
FAÇA TODAS AS MUDANÇAS PENDENTES TORNAREM-SE PERMANENTE COM A
DECLARAÇÃO “COMMIT”.
Estado dos dados Após
Os dados alterados foram escritos nos arquivos do banco de dados. O estado anterior Ao
“COMMIT” dos dados é permanentemente perdido.
Todos usuários podem ver os resultados da transação.
As “TRAVAS” (LOCKS) em efeito são liberadas; as linhas agora estão disponíveis para
outros usuários para realizarem novas mudanças de dados.
DESCARTANDO TODAS MUDANÇAS PENDENTES COM A DECLARAÇÃO
“ROLLBACK”.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 64
Estado dos Dados Após o “ROLLBACK”.
As mudanças de dados são desfeitas.
O estado anterior dos dados é rearmazenado.
As “travas” em efeito são liberadas; as linhas agora estão disponíveis para outros usuários
para realizar novas mudanças de dados.
ROLLBACK TO SAVEPOINT A COMMIT
Altere a lógica de transações com os “SAVEPOINTS”. Um “SAVEPOINT” (PONTO-
SALVO) marca um ponto intermediário no processamento de uma transação.
Altere a lógica de transação.
COMANDO DESCRIÇÃO
SAVEPOINT MARCA UM “SAVEPOINT” DENTRO DA TRANSAÇÃO
CORRENTE.
ROLLBACK TO
SAVEPONT
DESCARTA AS MUDANÇAS PENDENTES EFETUADAS
LOGO APÓS ONDE "SAVEPONT” FOI MARCADO.
Exemplo:
Este exemplo mostra a criação de SAVEPOINT antes de se realizar a operação de
“COMMIT”.
INSERT INTO DEPARTAMENTO (CODIGO, NOME, COD_RELIGIAO)
VALUES (55, „EDUCAÇÃO‟, 3);
SAVEPOINT A;
SAVEPOINT CREATED.
INSERT INTO DEPARTAMENTO (CODIGO, NOME, COD_REGIAO)
VALUES (56,‟EDUCAÇÃO‟, 4);
SAVEPOINT B ;
SAVEPOINT CREATED.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 65
INSERT INTO DEPARTAMENTO (CODIGO, NOME, COD_REGIAO)
VALUES (S_DEPT_ID.NEXT,‟EDUCAÇÃO‟,5);
Esta parte desfaz os últimos dois relacionamentos, mas deixa a primeira inserção
intacta; após isso efetua o “COMMIT‟.
ROLLBACK TO SAVEPOINT A;
ROLLBACK COMPLETE.
COMMIT;
COMMIT COMPLETE.
VERIFICAR O ROLLBACK:
SELECT *
FROM DEPARTAMENTO
WHERE NOME = „EDUCAÇÃO‟
NOTE AS CIRCUNSTÂNCIAS NO QUAL O ORACLE REALIZA UM “COMMIT” OU
“ROLLBACK” IMPLICITAMENTE.
Processamento de Transação Implícito
CIRCUNSTÂNCIA RESULTADO
COMANDO DDL EDITADO TAL COMO
O “CREATE TABELA”
“COMMIT”AUTOMÁTICO
SAÍDA NORMAL DO SQL*PLUS SEM
EDITAR EXPLICITAMENTE O
“COMMI” OU “ROLLBACK”
“COMMIT” AUTOMÁTICO
SAÍDA ANORMAL DO SQL*PLUS, OU
FALHA NO SISTEMA
“ROLLBACK” AUTOMÁTICO
Obs:
Explicitamente edite “COMMIT” ou “ROLLBACK”, para evitar processamento de
transação implícita.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 66
CRIAR “SCRIPTS” PARA MANIPULAR DADOS
Criar um arquivo “BATCH” para carregar uma grande quantidade de dados de uma única
vez incluindo o número de declarações “INSERT” dentro dele.
Exemplo:
Este exemplo mostra a utilização de um arquivo “BATCH” com alguns comandos
“INSERT”, para inserir dados na tabela REGIAO.
SET ECHO OFF
INSERT INTO REGIAO (CODIGO, NOME)
VALUES (1, „NORTH AMARICA‟)
/
INSERT INTO REGIAO (CODIGO, NOME)
VALUES (2, „SOUTH AMARICA‟)
/
INSERT INTO REGIAO (CODIGO, NOME)
VALUES (3, „ASIA‟)
/
COMMIT
/
Obs:
„SET ECHO OFF” ocultará a visualização de commandos durante a execução.
Oculte a reaparição de mensagens (POR EX: 1 ROW CREATED.) Incluindo “SET
FEEDBACK OFF” no início do arquivo “BATCH”.
@LOAD_REG
1 ROW CREATED.
1 ROW CREATED.
1 ROW CREATED.
COMMIT COMPLETE.
CRIE UM ARQUIVO TEXTO PARA CARREGAR DADOS INTERATIVAMENTE PELA
INCLUSÃO DE PARÂMETROS DE SUBSTITUIÇÃO SQL*PLUS DENTRO DA
DECLARAÇÃO “INSERT”.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 67
Exemplo:
Este exemplo mostra a passagem de parâmetros para a cláusula VALUES do comando
INSERT, fornecidos pelo usuário.
SET ECHO OFF
INSERT INTO REGIAO (CODIGO,NOME)
VALUES (&COD_REGIAO,‟&NOME_REGIAO‟)
/
@LOAD2
ENTER VALUE FOR COD_REGIAO: 6
ENTER VALUE FOR NOME_REGIAO: CALIFORNIA
OLD 2: VALUES (&COD_REGIAO,‟&NOME_REGIAO‟)
NEW 2: VALUES(6, „CALIFORNIA‟)
Obs:
Prefixe o parâmetro de substituição SQL*PLUS com o “&” quando referenciá-lo
na declaração SQL.
PERSONALIZAR A MENSAGEM “PROMPT” PARA O PARÂMETRO DE
SUBSTITUIÇÃO COM O COMANDO SQL*PLUS “ACCEPT”.
Sintaxe:
ACCEPT PARAMETRO_SUBSTITUIÇÃO PROMPT MENSAGEM
Onde:
PARAMETRO_SUBSTITUIÇÃO- É o nome do parâmetro de substituição SQL*PLUS
MENSAGEM- É a mensagem que aparece na linha de entrada de dados do usuário.
Exemplo:
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 68
Este exemplo mostra que o comando ACCEPT, personaliza a solicitação de valor no
“PROMPT” para as variáveis de passagem de parâmetro.
SET ECHO OFF
ACCEPT COD_REGIAO PROMPT „ENTRE COM O CODIGO DA REGIAO :‟
ACCEPT NOME_REGIAO PROMPT „ENTRE COM O NOME DA REGIAO :‟
INSERT INTO REGIAO (CODIGO,NOME)
VALUES (&COD_REGIAO,‟&NOME_REGIAO‟)
/
&LOAD3
ENTRE COM O CODIGO DA REGIAO: 7
ENTRE COM O NOME DA REGIAO: TEXAS
OLD 2: VALUES (&COD_REGIAO,‟&NOME_REGIAO‟)
NEW 2: VALUES (7,‟TEXAS‟)
1 ROW CREATED.
Obs:
Não prefixe o parâmetro de substituição SQL*PLUS com o “&” quando referenciá-
lo no comando “ACCEPT”.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 69
CONSTRUINDO RELATÓRIOS USANDO SQL*PLUS
1. Controlar o ambiente de relatório para uma sessão SQL*Plus.
2. Formatar um relatório.
3. Salvar os comandos para produzir um relatório, bem como sua saída, para um arquivo
texto.
CONSTRUIR RELATÓRIOS COM SQL*PLUS: OVERVIEW.
Construir um relatório simples com comandos SQL*PLUS.
CATEGORIA DESCRIÇÃO
COMANDOS DE SISTEMA COMANDOS QUE AFETAM O
PROCEDIMENTO DO SQL. AMBIENTE
SQL*PLUS
COMANDOS DE FORMATAÇÃO COMANDOS QUE CONTROLAM OS
RELATÓRIOS.
COMANDOS DE ARQUIVOS COMANDOS QUE ARMAZENAM
INFORMAÇÕES EM ARQUIVO TEXTO.
CONTROLAR O AMBIENTE DENTRO DA SESSÃO.
Controlar variáveis de sistema afetando o procedimento geral da declaração
SQL pelo comando SET da sessão SQL*PLUS.
Sintaxe:
SET SYSTEM_VARIABLE VALUE
Onde:
VALUE = valor para a variável de sistema.
SYSTEM_VARIABLE = variáveis de controle do sistema.
Obs:
Não é necessário terminar o comando SQL*PLUS usando(;).
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 70
VARIÁVEIS DE SISTEMA SQL*PLUS
VARIÁVEIS DE SISTEMA DESCRIÇÃO
FEEDBACK N CONTROLA QUANDO EXIBIR O
NÚMERO DE REGISTROS
RETORNADOS POR UMA QUERY. O
VALOR DEFAULT É 6.
FEEDBACK ON/OFF DETERMINA SE O FEEDBACK ESTARÁ
ON OU OFF; O VALOR DEFAULT É ON.
PAUSE MESSAGE EXIBE MENSAGEM.
PAUSE ON/OFF DETERMINA SE A PAUSA ESTARÁ ON
OU OFF; O VALOR DEFAULT É OFF,
OU SEJA ESTÁ DESABILITADA A
PAUSA.
Obs:
Mostrar os sets correntes das variáveis de sistema usando o comando do
SQL*PLUS chamado SHOW ALL
VARIÁVEIS DE SISTEMA DESCRIÇÃO
HEADNG ON/OFF DETERMINA SE O CABEÇALHO DA
COLUNA SERÁ EXIBIDA NO
RELATÓRIO.
PAGESIZE N SETA O NÚMERO DE LINHAS POR
PAGINA DO RELATÓRIO; VALOR
DEFAULT É 14.
LINESIZE N SETA O NÚMERO DE CARACTERES
POR LINHA DO RELATORIO; O VALOR
DEFAULT É 80.
SPACE N SETA O NÚMERO DE ESPAÇOS ENTRE
COLUNAS DE SAÍDA, VALOR MÁXIMO
É 10; VALOR DEFAULT É 1.
ECHO ON/OFF DETERMINA SE UM COMANDO
CONTIDO NUM ARQUIVO SERÁ
LISTADO OU NÃO QUANDO O
ARQUIVO FOR SER EXECUTADO;
VALOR DEFAULT É ON.
VERIFY ON/OFF DETERMINA SE SERÁ MOSTRADO
VALORES ANTERIORES E ATUAIS DOS
PARÂMETROS DE SUBSTITUIÇÃO
APÓS A EXECUÇÃO DE UM COMANDO
SQL QUE TENHA CONTIDO NELE
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 71
ESSES PARÂMETROS.
CENÁRIO PARA CONTROLE DE CONFIGURAÇÕES NO SQL*PLUS.
SET FEEDBACK 10
Quando mudar o valor da variável de sistema, o mesmo será mantido em memória.
Cada ambiente permanece ativo até o final do sessão SQL*PLUS, ou até o
ambiente ser reescrito ou limpo
CONTROLE DO AMBIENTE DA SESSÃO
Seguindo a convenção para tratar as regras do SQL-PLUS, recomponha as variáveis de
sistema para o valor DEFAULT após rodar um relatório.
SQL> SET PAGESIZE 10
SQL> SET PAUSE „PRESSIONE QUALQUER TECLA PARA CONTINUAR …..‟
SQL> SET PAUSE ON
SQL> SELECT SOBRENOME
FROM EMPREGADO;
PRESSIONE QUALQUER TECLA PARA CONTINUAR ….
SOBRENOME
VELASQUEZ
NGAO
NAGAYAMA
MENCHU
PRESSIONE QUALQUER TECLA PARA CONTINUAR ….
BIRI
HAVEL
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 72
MAGEE
Voltar para os valores padrão (DEFAULT):
SQL> SET PAUSE OFF
SQL> SET PAGESIZE 24
FORMATANDO RELARÓRIOS
Controlar o formato de relatórios com os comandos SQL*PLUS.
Comandos de formatação do SQL*PLUS:
COMANDOS DESCRIÇÃO
TTITLE Define o título do relatório, ocupa espaço no
topo de cada página.
BTITLE Define no rodapé do relatório, ocupa espaço
no rodapé de cada página.
COLUMN Controla como será dado o display de cada
coluna
BREAK Define quebras no relatório.
COMPUTE Utilizada para calcular, gerando subtotais e
totais gerais ao nível de relatório, etc....
O resultado de cada comando de formatação valerá para toda a sessão enquanto estiver
aberta, ou for replicado um novo valor para a sessão.
EXIBINDO CABEÇALHOS E RODAPÉ
Usando TTITLE e BTITLE para relatórios.
Sintaxes: TTITLE(MESSAGE)
SQL> TTITLE „RELATÓRIOS|EMPREGADOS‟
A sintaxe para TTITLE e BTITLE são idênticos.
Por DEFAULT, o comando TTITLE centraliza o cabeçalho, mostrando a data no
lado superior esquerdo e o número da página no lado superior direito.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 73
Controlar o cabeçalho e o rodapé, exibindo as regras correntes e limpando-as se for
necessário.
Tratando cabeçalhos e montando rodapé
COMANDOS DESCRIÇÃO
TTITLE Exibe a configuração corrente para o
cabeçalho.
TTITLE OFF Desabilita o cabeçalho.
BTITTLE Exibe a configuração corrente para o rodapé
BTITLE OFF Desabilita o rodapé
Exemplo:
SQL> SET PAGESIZE 14
SQL> SET LINESIZE 60
SQL> SET FEEDBACK OFF
SQL > TTITLE „ RELATORIO | EMPREGADO „
SQL> BTITLE „ CONFIDENCIAL „
CONTROLAR E EXIBIR ATRIBUTOS DE COLUNAS PARA RELATÓRIO DO
SQL*PLUS
Sintaxes:
COLUMN NOME_COLUNA HEADING CABEÇALHO
Onde:
NOME_COLUNA: é o nome da coluna
CABEÇALHO: é o texto do cabeçalho do relatório
Exemplo:
COLUMN COD_REPR HEADING „VENDEDOR‟
A coluna com nome „COD_REPR‟ será substituída por „VENDEDOR‟ na hora da
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 74
formatação e execução do relatório.
CONTROLE DE QUEBRA USANDO A CLÁUSULA ORDER BY E CLÁUSULA
SELECT.
Sintaxe:
SELECT ...............
FROM .............
ORDER BY BREAK_COLUMN
Onde:
BREAK_COLUN nome da coluna usada no comando BREAK ON.
Controla e quebra para exibição do BREAK corrente e limpa se necessário.
COMANDOS DESCRIÇÃO
BREAK Exibe o BREAK corrente(comando)
CLEAR BREAKS Desabilita o BREAK corrente
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 75
EXECUTANDO ATUALIZAÇÕES A PARTIR DE SUBCONSULTAS:
Você pode usar uma subconsulta em comandos UPDATE para alterar valores em uma
tabela baseados em valores de outra tabela.
Sintaxe:
UPDATE NOME_TABELA
SET NOME_COLUNA = ( SELECT NOME_COLUNA
FROM NOME_TABELA
WHERE CONDIÇÃO )
WHERE NOME_COLUNA = ( SELECT NOME_COLUNA
FROM NOME_TABELA
WHERE CONDIÇÃO )
Exemplo:
UPDATE EMPREGADO
SET COD_DEPTO = ( SELECT COD_DEPTO
FROM EMPREGADO
WHERE CODIGO = 1 )
WHERE CARGO = ( SELECT CARGO
FROM EMPREGADO
WHERE CODIGO = 1 ) ;
CRIAR TABELAS A PARTIR DE SUBCONSULTAS
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 76
Você pode usar uma subconsulta em um comando CREATE TABLE para inserir linhas
nesta nova tabela baseados em valores de uma tabela.
Sintaxe:
CREATE TABLE NOME_TABELA AS
SELECT NOME_COLUNA, NOME_COLUNA, COME_COLUNA .......
FROM NOME_TABELA
WHERE NOME_COLUNA = ( SELECT NOME_COLUNA
FROM NOME TABELA
WHERE CONSIÇÃO )
Exemplo:
CREATE TABLE SALARIO AS
SELECT NOME, CARGO, COD_DEPTO
FROM EMPREGADO
WHERE SALARIO < ( SELECT SALARIO
FROM EMPREGADO
WHERE NOME = „EDDIE‟ );
A partir das versões 8i já é possível criar tabelas a partir de consultas com linhas ordenadas,
basta colocar a cláusula ORDER BY
DELETAR LINHAS A PARTIR DE SUBCONSULTAS
Você pode usar subconsultas em uma comando DELETE para remover linhas a partir de
valores baseados em outras tablas.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 77
Sintaxe:
DELETE FROM NOME_TABELA
WHERE NOME_COLUNA = ( SELECT NOME_COLUNA
FROM NOME_TABELA
WHERE CONDIÇÃO )
Exemplo:
DELETE FROM EMPREGADO
WHERE PCT_COMISSAO = 10
AND COD_DEPTO = ( SELECT CODIGO
FROM DEPARTAMENTO
WHERE NOME = „SALES‟ );
INSERIR LINHAS DE DADOS A PARTIR DE SUBCONSULTAS
Você pode usar subconsultas em um comando INSERT para inserir dados a partir de
valores baseados em outras tabelas.
Sintaxe:
INSERT INTO NOME_TABELA ( NOME_COLUNA, NOME_COLUNA ...... )
SELECT NOME COLUNA, NOME_COLUNA .........
FROM NOME_TABELA
WHERE NOME_COLUNA = ( SELECT FROM NOME_COLUNA
FROM NOME_TABELA
WHERE CONDIÇÃO )
Exemplo:
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 78
CREATE TABLE STOCK AS
SELECT CODIGO, NOME, CARGO, SALARIO
FROM EMPREGADO
WHERE 1=2;
INSERT INTO STOCK ( CODIGO, NOME, CARGO, SALARIO)
SELECT CODIGO, NOME, CARGO, SALARIO
FROM EMPREGADO;
OPERADORES DE CONJUNTO
Operador de conjunto combina o resultado componente de duas consultas a partir de um
simples resultado. Consultas contendo operadores de conjunto são chamados de Consultas
Compostas. A tabela abaixo lista os operadores de Conjunto.
OPERADOR RETORNA
UNION Todas as linhas selecionadas por qualquer
uma das consultas
UNION ALL Todas as linhas selecionadas por qualquer
uma das consultas, incluindo todas as
duplicadas
INTERSECT Todas as linhas distintas selecionadas por
ambas as consultas
MINUS Todas as linhas distintas selecionadas pela
primeira consulta e não pela segunda.
Todos os operadores de Conjunto possuem procedência igual.
Exemplo:
UNION
SELECT PART, PARTNUM, TO_DATE(NULL) DATE_IN
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 79
FROM ORDERS_LIST1
UNION
SELECT PART, TO_DATE(NULL) DATE_IN
FROM ORDERS_LIST2
UNION ALL
SELECT PART
FROM ORDERS_LIST1
UNION ALL
SELECT PART
FROM ORDERS_LIST2
Note que o operador UNION retorna somente linhas distintas que aparece em um resultado,
enquanto o operador UNION ALL retorna todas as linhas;
Exemplo:
INTERSECT
SELECT PART
FROM ORDERS_LIST1
INTERSECT
SELECT PART
FROM ORDERS_LIST2
MINUS
SELECT PART
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 80
FROM ORDERS_LIST1
MINUS
SELECT PART
FROM ORDERS_LIST2
ORACLE DATATYPES
Quando você cria uma tabela para armazenar informações em seu banco de dados você
necessita especificar o tipo de dados para cada coluna de sua tabela. O Oracle possui diferentes
tipos de dados (datatypes) para atender suas necessidades, que são divididos nas seguintes
categorias: CHARACTER, NUMBER, DATE, LOB.
Character Datatype
Usados para armazenar dados alfanuméricos.
CHAR(<n>) armazena string de tamanho fixo. Tamanho default 1, máximo 2000 .
VARCHAR2(<n>) armazena string de tamanho variável. É possível armazenar
string de até 4000 bytes.
VARCHAR(<n>) sinônimo para o tipo VARCHAR2.
NCHAR(<n>) e NVARCHAR2(<n>) possuem as mesmas características dos
tipos CHAR e VARCHAR2 e são usados para armazenar dados NLS (National
Language Support). A arquitetura Oracle NLS permite armazenar, processar e
recuperar informações em linguagens nativas.
LONG é um tipo de dados que se tornou “obsoleto” com a chegada dos tipos LOB
(Large Object). O tipo LONG armazena strings de tamanho variável e pode
armazenar até 2GB de dados. Dentre as restrições do tipo LONG, podemos citar:
Não pode ser usada em cláusulas WHERE, GROUP BY, ORDER BY ou
CONNECT BY e nem ser usado com operador DISTINCT. Uma tabela não pode
possuir mais de uma coluna com o tipo LONG.
Numeric Datatype
Usado para armazenar dados numéricos com precisão de até 38 digitos.
NUMBER(<x>, <y>) onde <X> corresponde ao número de dígitos e <Y> o
número de casas decimais. Valores inseridos em colunas numéricas com número de
casas decimais menor que o dado inserido serão arredondados.
Date Datatype
O tipo DATE permite valores de data e hora. O formato padrão é definido pelo parâmetro
NLS_DATE_FORMAT. O Oracle armazena internamente a data em formato de número juliano
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 81
com a parte fracionária usada para controlar a hora. Uma data Juliana corresponde ao número de
dias desde 1 de Janeiro de 4712 A.C.
Para operações aritméticas com datas no Oracle, basta adicionar ou subtrair números
inteiros ou fracionários. Por exemplo, SYSDATE + 1 para somar uma dia, 1/24 para acrescentar
uma hora, 1/(24x60) ou 1/1440 para acrescentar 1 minuto e 1/(24x60x60) ou 1/86400 para um
segundo.
Além do tipo DATE, outros o Oracle também possui 5 outros tipos de dados para
armazenar tipos de data:
TIMESTAMP semelhante ao tipo DATE, com a diferença de armazenar fração de
segundos com precisão de até 9 digitos.
TIMESTAMP WITH TIME ZONE armazena data/hora com informações de fuso
horário.
TIMESTAMP WITH LOCAL TIME ZONE armazena data/hora no fuso horário
do servidor. Quando o usuário seleciona os dados, o valor é ajustado para as
configurações da sua sessão.
INTERVAL YEAR TO MONTH usado para armazenar espaço de tempo em anos
e meses.
INTERVAL DAY TO SECOND permite especificar intervalos em dias, horas,
minutos e segundos.
LOB Datatypes
Large Object (LOB) datatypes são usado para armazenar dados não estruturados como
imagens, arquivos binários. Os tipos LOBs podem armazenar até 4GB de informação. A
manipulação dos tipos LOB é feita através da package DBMS_LOB.
Datatype Descrição
BLOB Binary Large Object Armazena até 8~128TB de dados binários no banco
CLOB Character Large Object Armazena até 8~128TB de dados caráter
BFILE Binary File Armazena até 8~128TB de dados em arquivos
binários externos. Uma coluna BFILE armazena um
ponteiro para o arquivo armazenado no sistema
operacional.
Outros Datatypes
RAW é um tipo para dados binários, não interpretados pelo banco. Podem
armazenar até 2000 bytes de informação e seus dados não passam por conversão de
conjunto de caracteres entre cliente e servidor.
LONGRAW semelhante ao tipo LONG, é um tipo de dados “obsoleto” que pode
armazenar até 2GB de dados. Seu uso foi depreciado pelos tipos BLOB e BFILE.
ROWID Oracle utiliza o datatype ROWID para armazenar o endereço de cada
linha no banco de dados. Toda tabela contém uma coluna oculta chamada ROWID
que retorna um identificador único do endereço da linha no banco de dados no
formato OOOOOOFFFBBBBBBRRR onde “O” representa o número do objeto,
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 82
“F” o número do datafile, “B” a identificação do bloco Oracle e “R” a identificação
da linha no bloco.
UROWID Universal ROWID, suporta todos os tipos de ROWID (físicas ou
lógicas) bem como de tabelas não Oracle acessadas através de gateway.
ANSI, DB2 e SQL/DS Datatypes
Comandos SQL para criação de tabelas que usam a notação de tipos ANSI ou de
produtos IBM DB2 e SQL/DS. O Oracle reconhece alguns nomes de datatypes dos padrões
citados e converte para seus tipos internos conforme tabela a seguir:
ANSI/DB2/SQL-DS Datatype Oracle Datatype
CHARACTER(n) CHAR(n)
CHARACTER VARYING(n)
CHAR VARYING(n)
VARCHAR(n)
NATIONAL CHARACTER(n)
NATIONAL CHAR(n)
NCHAR(n)
NATIONAL CHARACTER VARYING(n)
NATIONAL CHAR VARYING(n)
NCHAR VARYING(n)
NVARCHAR2(n)
NUMERIC(x,y)
DECIMAL(x,y)
NUMBER(x,y)
INTEGER
INT
SMALLINT
NUMBER(38)
FLOAT
DOUBLE PRECISION
REAL
NUMBER
LONG VARCHAR(n) LONG
CRIANDO TABELAS NO BANCO DE DADOS
CRIAR TABELAS PARA ARMAZENAR DADOS COM O COMANDO SQL CREATE
TABLE
Sintaxe:
CREATE (GLOBAL TEMPORARY) TABLE NOME_TABELA
({ NOME_COLUNA DATATYPE | DEFAULT | CONTRAINT TABLE ]
[,{ NOME_COLUNA DATATYPE | DEFAULT | CONTRAINT TABLE }] ……)
ON COMMIT (DELETE |PRESERVE) ROWS
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 83
Onde:
NOME_TABELA é o nome da tabela.
NOME_COLUNA é o nome da coluna.
GLOBAL TEMPORARY especifica que a tabela temporária e que sua definição é visível por todas
as sessões. O dado na tabela temporária é visível somente a sessão que inseriu os dados na tabela.
Uma tabela temporária tem definição da mesma forma que uma tabela normal., mas contém as
opções session-specific e transaction-specific. Você especifica se os dados são session-or-
transaction-specific com a palavra chave ON COMMIT(abaixo).
DATATYPE é o tipo de dado da coluna
DEFAULT especifica um valor a ser atribuído para a coluna se a declaração INSERT omitir um
valor para a coluna. O datatype da expressão deve ser igual ao datatype da coluna. Uma expressão
DEFAULT não pode conter referencias de outras colunas, a pseudocolunas CURRVAL,
NEXTVAL, LEVEL, e ROWNUM, ou constantes data que não completamente especificadas.
CONSTRAINT_TABELA é a “CONSTRAINT” de dado para a coluna
ON COMMIT pode ser especificado somente se estiver criando uma tabela temporária. Esta
clausula determina a permanência dos dados na tabela temporária. Se terá duração de uma transação
ou uma sessão.
DELETE ROWS Especifica que a tabela temporária é para uma transação especifica ( esta é
default). Oracle irá truncar a tabela, ou seja, remover todas as linhas após cada linha comitada.
PRESERVE ROWS Especifica que a tabela temporária é para uma sessão especifica. Oracle irá
truncar a tabela quando terminar a sessão.
REGRAS DE NOMEAÇÃO
Os nomes das tabelas e colunas deve ser de 1-30 caracteres; o primeiro caracter deve ser alfabético.
Os nomes devem contem somente caracteres de a-z, A-Z, 0-9 , _(underscore), $ e # (mas seu uso
não e recomendado).
Os nomes não podem ser iguais às palavras reservadas do Oracle.
O nome da tabela não pode ser igual ao nome de um outro objeto criado pelo mesmo usuário
Oracle.
DEFINIR CONSTRAINTS DE INTEGRIDADE DE DADOS
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 84
CONSTRAINT DESFRIÇÃO
NOT NULL Especifica que esta coluna não pode conter
valores nulos
UNIQUE Especifica que a coluna ou combinação de
colunas que terão seus valores únicos na
tabela.
PRIMARY KEY Identifica a unicidade de cada linha na
tabela.
FOREIGN KEY REFERENCES Estabelece e força um relacionamento de
chave tabela(nome da coluna) estrangeira
entre esta coluna e a chave primaria da
tabela referenciada.
CHECK Especifica uma condição que deve ser
verdadeira obedecendo uma regra de
negócio.
Exemplo:
SQL> CREATE TABLE REGIAO
( CODIGO NUMBER(7),
NOME BARCHAR2(30)
CONSTRAINT REGIAO_NOME_NN NOT NULL,
CONSTRAINT REGIAO_CODIGO_PK PRIMARY LEY(CODIGO),
COSNTRAINT REGIAO_NOME_UK UNIQUE (NOME) );
ADICIONAR UMA COLUNA À TABELA
Adicionar uma coluna a tabela com o comando ALTER TABLE.
ALTER TABELA REGIAO
ADD ( COMENTARIOS CARVAR2(255)
CONSTRAINT REGIA_COMENTARIOS_NN NOT NULL ) ;
MODIFICAR UMA COLUNA
Modificar a definição de uma coluna com o comando ALTER TABLE.
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 85
ALTER TABLE EMPREGADO
MODIFY ( SOBRENOME VARCHAR2(30));
REMOVER UMA COLUNA
Modificar a definição de uma coluna com o comando ALTER TABLE.
ALTER TABLE EMPREGADO
DROP COLUMN ( SOBRENOME );
REMOVER UMA TABELA
DROP TABLE EMPREGADO
MARCANDO UMA COLUNA COMO “UNUSED”
ALTER TABLE EMPREGADO
SET UNUSED COLUMN SOBRENOME;
REMOVENDO UMA COLUNA “UNUSED”
ALTER TABLE EMPREGADO
DROP UNUSED COLUMNS;
CONSTRAINTS
ADIAR CHECAGEM DE CONSTRAINT
Pode uma constraint ser adiada?
NOT DEFERRABLE (DEFAULT)
DEFERRABLE
Comportamento Default de uma constraint:
INITIALLY IMMEDIATE (DEFAULT)
INITIALLY DEFERRED
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 86
Use SET CONSTRAINTS ou ALTER SESSION SET CONSTRAINT para alterar o
comportamento default de uma constraint.
SET CONSTRAINT(S)
Propósito
Para especificar, em uma transação particular, se a checagem da constraint é adiada ou a cada
declaração UML ou quando a transação é comitada.
Pré-Requisitos
Para especificar quando a constraint irá checar, você deve ter privilégios de SELECT sobre a tabela
no qual a constraint é aplicada, a menos que a tabela esteja em seu schema.
PALAVRAS CHAVES E PARAMETROS
Constraint Nome de uma ou mais constraints de inetgridade
ALL Seta toas as constraints desta transação
IMMEDIATE Esta condição indica que uma constraint ou todas
será checadas imediatamente apos cada declaração
SQL
DEFERRED Esta condição indica que a constraint será checada
quando a transação for comitada.
Você pode verificar o sucesso das ações das constraints,antes mesmo do commit pelo
comando SET CONSTRAINTS ALL IMMEDIATE
Validando Constraints
ENABLE / DISABLE afeta futuras alterações:
ENABLE para checar futuras alterações
DISABLE para não checar futuras alterações
VALIDADE / NOVALIDADE afeta dados da tabela corrente:
VALIDADE para checar o dado da tabela corrente
NOVALIDADE para evitar a checagem do dado na tabela corrente
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 87
Exemplos:
ALTER TABLE ESTADO
ADD PRIMARY KEY (COD_ESTADO)
DISABLE NOVALIDADE;
--
ALTER TABLE CIDADE
ADD FOREIGN KEY (COD_ESTADO) REFERENCES ESTADO(COD_ESTADO)
DISABLE NOVALIDADE;
--
CREATE TABLE CONTA
( NR_CONTA NUMBER(10),
COD_CLIENTE NUMBER(10),
COMENTARIO_CONTA VARCHAR2(200),
CONSTAINT PK_CID_AID PRIMARY KEY ( COD_CLIENTE, NR_CONTA)
DISABLE) ;
CRIANDO SEQUENCES
Sequences são usadas para gerar valores únicos, normalmente armazenados em
colunas de chave primária.
Sintaxe:
CREATE SEQUENCE sequence_name
[INCREMENT BY n]
[START WITH n]
[ {MINVALUE n | NOMINVALUE} ]
[ {MAXVALUE n | NOMAXVALUE} ]
[ {CYCLE | NOCYCLE} ]
[ {CACHE n | NOCACHE} ]
Onde:
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 88
SEQUENCE_NAME - é o nome do gerador de seqüência a ser criado.
INCREMENT BY – especifica o intervalo entre o número da seqüência. O default é 1.
START WITH – especifica o primeiro número da seqüência a ser gerado. O default é 1.
MINVALUE – menor valor que será gerado pela seqüência, ou valor limite para seqüências
decrescentes. O default é NOMINVALUE.
NOMINVALUE – O Oracle converte para 1 para seqüências positivas e -1026
para seqüências
negativas.
MAXVALUE – especifica o valor máximo gerado pela seqüência.
NOMAXVALUE – especifica o valor máximo de 1027
CYCLE – especifica se a seqüência continuará a gerar números após atingir o valor máximo ou
mínimo.
CACHE – especifica a quantidade de números o oracle irá alocar e manter em memória no servidor.
Por default, o Oracle alocará o valor de 20.
NOCACHE – força os dados de dicionário Oracle serem atualizados a cada seqüência gerada.
Exemplo:
CREATE SEQUENCE DEPTO_CODIGO
MAXVALUE 999999
INCREMENT BY 1
START WITH 51
NOCACHE;
Utilize a view USER_SEQUENCES para obter informações sobre as sequences.
Referenciando valores de sequences
Você pode referenciar seqüências em comandos DMLs para gerar valores únicos
automaticamente através de suas pseudo-colunas:
Expressão Descrição
sequence_name.NEXTVAL Retorna automaticamente o próximo valor
disponível da seqüência. Irá retornar um
valor único a cada vez que for referenciado,
mesmo para diferentes usuários utilizando a
mesma seqüência.
sequence_name.CURRVAL Retorna o último valor da seqüência obtido
pela sessão corrente através do NEXTVAL.
Enquanto NEXTVAL não for referenciado
pelo menos uma vez, CURRVAL não estará
disponível.
Exemplo:
INSERT INTO REGIAO(cod_regiao, nome) VALUES(seq_regiao.NEXTVAL, „Centro
Sul‟);
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 89
INSERT INTO DEPARTAMENTO(cod_depto, nome, cod_regiao)
VALUES(seq_depto.NEXTVAL, „Crédito e cobrança‟, seq_regiao.CURRVAL);
Removendo uma seqüência
DROP SEQUENCE sequence_name;
SIMPLIFICANDO O ACESSO AOS DADOS ATRAVÉS DE VISÕES
Uma view é uma representação customizada dos dados de uma ou mais tabela, podendo ser
considerada como uma query armazenada ou uma tabela virtual. Apenas a pesquisa (SELECT ...
FROM ...) é armazenada no dicionário de dados Oracle.
Sintaxe:
CREATE [OR REPLACE] [FORCE/NOFORCE] VIEW view_name [(alias, [alias] ...)]
AS consulta
[WITH CHECK OPTION [CONSTRAINT constraint]
[WITH READ ONLY]
Onde:
VIEW_NAME - o nome da visão
ALIAS – especifica nomes para as colunas selecionadas pelas querys da visão. O número de aliases
deve ser igual ao número de expressões selecionadas pela visão.
CONSULTA – é uma cláusula SELECT completa.
WITH CHECK OPTION – verifica se INSERTs E UPDATEs realizados através da visão respeitam
a cláusula WHERE da query.
WITH READ ONLY – não permite DMLs na view
CONSTRAINT – é o nome da constraint assinalada ao CHECK OPTION
OR REPLACE – permite recriar a view
FORCE – cria a visão não importando se as tabelas relacionadas no SELECT existam ou se o
proprietário possui privilégio sobre tais tabelas.
NO FORCE – é o default, cria a visão somente se as tabelas do SELECT existir e o proprietário
possuir privilégios sobre elas.
Exemplo:
CREATE VIEW visao10
AS
SELECT EMP.CODIGO “ID_NUMBER”, EMP.SOBRENOME “EMPLOYEE”,
EMP.CARGO “JOB”, EMP.COD_DEPTO “DEPTO”, DEP.NOME “NOME_DEPT”
FROM EMPREGADO EMP, DEPARTAMENTO DEP
WHERE EMP.COD_DEPTO IN (20, 30,40)
AND EMP.COD_DEPTO = DEP.CODIGO;
Utilize a view USER_VIEWS para obter informações sobre views
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 90
Removendo uma view
DROP VIEW view_name;
MELHORAR A PERFORMANCE DAS CONSULTAS ATRAVÉS DE ÍNDICES
Criar índice para uma ou mais colunas se:
A coluna é frequentemente usada na cláusula WHERE ou em algum JOIN
A tabela é grande e muitas consultas retornam menos de 10-15% das linhas
A coluna não sobre muitas alterações
O Oracle mantém os índices automaticamente. Não há impacto na sintaxe SQL, contudo,
pode haver impacto na performance se os dados são frequentemente alterados.
Sintaxe de criação de índices:
CREATE [UNIQUE] INDEX nome_indice
ON nome_tabela (nome_coluna[, nome_coluna] ...)
A palavra-chave UNIQUE especifica que o índice não conterá valores duplicados,
garantindo que duas linhas da tabela não contenham mesmo valor para a coluna ou colunas
indexadas.
Exemplo:
CREATE INDEX INDX_EMP_SOBRENOME
ON EMPREGADO(SOBRENOME);
CREATE UNIQUE INDEX INDX_CLIENTE_CPF
ON CLIENTE(NR_CPF);
No Oracle, índices únicos são criados automaticamente pelo sistema para forçar a chave
primária (primary key) e unique constraints.
Removendo um índice
DROP INDEX nome_indice;
Utilize as views USER_INDEXES e USER_IND_COLUMNS para obter informações sobre
os índices
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 91
HISTÓRICO DE NOVIDADES POR VERSÕES DO ORACLE
11g
DDL TIMEOUT
ALTER SESSION SET ddl_lock_timeout=30;
ALTER TABLE lock_tab ADD (
description VARCHAR2(50)
);
INVISIBLE INDEXES
CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;
READ ONLY TABLES
ALTER TABLE table_name READ ONLY;
ALTER TABLE table_name READ WRITE;
VIRTUAL COLUMNS
CREATE TABLE employees (
id NUMBER,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
salary NUMBER(9,2),
comm1 NUMBER(3),
comm2 NUMBER(3),
salary1 AS (ROUND(salary*(1+comm1/100),2)),
salary2 NUMBER GENERATED ALWAYS AS
(ROUND(salary*(1+comm2/100),2)) VIRTUAL,
CONSTRAINT employees_pk PRIMARY KEY (id)
);
SELECT PIVOT AND UNPIVOT
CREATE TABLE pivot_test (
id NUMBER,
customer_id NUMBER,
product_code VARCHAR2(5),
quantity NUMBER
);
INSERT INTO pivot_test VALUES (1, 1, 'A', 10);
INSERT INTO pivot_test VALUES (2, 1, 'B', 20);
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 92
INSERT INTO pivot_test VALUES (3, 1, 'C', 30);
INSERT INTO pivot_test VALUES (4, 2, 'A', 40);
INSERT INTO pivot_test VALUES (5, 2, 'C', 50);
INSERT INTO pivot_test VALUES (6, 3, 'A', 60);
INSERT INTO pivot_test VALUES (7, 3, 'B', 70);
INSERT INTO pivot_test VALUES (8, 3, 'C', 80);
INSERT INTO pivot_test VALUES (9, 3, 'D', 90);
INSERT INTO pivot_test VALUES (10, 4, 'A', 100);
COMMIT;
SELECT * FROM pivot_test;
ID CUSTOMER_ID PRODU QUANTITY
---------- ----------- ----- ----------
1 1 A 10
2 1 B 20
3 1 C 30
4 2 A 40
5 2 C 50
6 3 A 60
7 3 B 70
8 3 C 80
9 3 D 90
10 4 A 100
10 rows selected.
SELECT *
FROM (SELECT customer_id, product_code, quantity
FROM pivot_test)
PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS
a, 'B' AS b, 'C' AS c))
ORDER BY customer_id;
CUSTOMER_ID A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
----------- -------------- -------------- --------------
1 10 20 30
2 40 50
3 60 70 80
4 100
4 rows selected.
SQL*PLUS ERROR LOGGING
SET ERRORLOGGING ON
SELECT TIMESTAMP, USERNAME, SCRIPT, IDENTIFIER, STATEMENT, MESSAGE
FROM SPERRORLOG;
-------
CREATE TABLE MY_sperrorlog(username VARCHAR(256),
timestamp TIMESTAMP,
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 93
script VARCHAR(1024),
identifier VARCHAR(256),
message CLOB,
statement CLOB);
commit;
SET ERRORLOGGING ON TABLE MY_sperrorlog
10g
RECYCLEBIN
Drop table clientes purge;
Drop table;
Purge recyclebin;
Show recyclebin (sql*plus)
FLASHBACK TABLE
Flashback table clientes to before drop;
Flashback table clientes to before drop rename to cli001;
Flashback table caixa to timestamp systimestamp – 1;
FLASHBACK VERSIONS QUERY
select cod, nome, versions_starttime, versions_endtime,
versions_xid, versions_operation
from cliente
versions between timestamp minvalue and maxvalue
FLASHBACK TRANSACTION QUERY
select logon_user, operation, undo_sql
from flashback_transaction_query
where table_name = 'CLIENTE'
MODEL SQL
SELECT country,prod,year,s
FROM sales_view_ref
MODEL
PARTITION BY (country)
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 94
DIMENSION BY (prod, year)
MEASURES (sale s)
RULES (
s[prod='Mouse Pad', year=2001] =
s['Mouse Pad', 1999] + s['Mouse Pad', 2000],
s['Standard Mouse', 2002] = s['Standard Mouse', 2001]
)
ORDER BY country, prod, year;
MERGE
MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM
employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus +
S.salary*.01
DELETE WHERE (S.salary > 8000)
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1)
WHERE (S.salary <= 8000);
PARTITIONED OUTER JOINS
select v.periodo, nvl(v.valor,0) vlrR$, c.cod, c.nome
from v_movimento_cliente v
partition by (periodo)
right outer join cliente c
on c.cod = v.cod_cliente
EXPRESSÕES REGULARES
REGEXP_LIKE()
SELECT zip
FROM zipcode
WHERE REGEXP_LIKE(zip, '[^[:digit:]]'));
SELECT last_name
FROM employees
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 95
WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i');
alter table cliente
add constraint chk_formato_cpf check (REGEXP_LIKE(cpf,
'^([[:digit:]]{3}.[[:digit:]]{3}.[[:digit:]]{3}-
[[:digit:]]{2})$'));
Oracle 9iR2
FLASHBACK QUERY
select * from clientes AS OF TIMESTAMP SYSDATE –
INTERVAL ‘20’ MINUTE;
ALTER TABLE
alter table clientes rename column NRFONE to
NR_TELEFONE;
alter table clientes rename constraint sys_c00344 to
fk_cliente_cidade;
ANSI JOINS
select * from cliente NATURAL JOIN departamento;
select * from cliente JOIN departamento USING (cod_dep);
select a.codigo, b.nome from cliente a join cidade b
ON (a.cod_cidade=b.codigo);
select * from cliente LEFT JOIN departamento
ON (a.cod_dept = b.codigo);
select * from cliente RIGHT JOIN departamento
ON (a.cod_dept = b.codigo);
select * from cliente FULL JOIN departamento
ON (a.cod_dept = b.codigo);
Select * from empregado a INNER JOIN empregado b
ON (a.cod_gerente = b.cod_empregado);
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 96
Oracle 9iR1
SELECT FOR UPDATE
SELECT *
FROM employees
WHERE empno = 20
FOR UPDATE WAIT 30;
SELECT *
FROM employees
WHERE empno = 20
FOR UPDATE SKIP LOCKED;
EXTERNAL TABLES
CREATE TABLE emp_ext
(
empcode NUMBER(4),
empname VARCHAR2(25),
deptname VARCHAR2(25),
hiredate date
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tables
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
)
LOCATION ('emp_ext1.dat','emp_ext2.dat')
)
REJECT LIMIT UNLIMITED;
DATATYPES
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
TIMESTAMP
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 97
EXPRESSÕES DATA
Select systimestamp AT TIME ZONE ‘-04:00’ from dual;
EXPRESSÕES DE INTERVALO
Select sysdate + interval ‘3’ minute from dual;
Select sysdate – interval ‘3 1:30:20’ day to second from
dual;
Select systimestamp + interval ‘3’ month from dual;
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 98
EXPRESSÃO CASE
Searched:
select CASE
when sysdate between '01-jan-05' and ’31-Dec-05’ then
'Ano 2005'
when 1=2 then
'Inválido'
else
'...'
END from dual
MERGE
merge into clientes destino
using txt_cli origem
on (destino.cod = origem.codcli)
when matched then
update
set destino.nome = origem.nome,
destino.cidade=origem.cidade
when not matched then
insert (destino.cod, destino.nome)
values(origem.codcli, origem.nome);
DEFAULT VALUES
create table t2(col1 number(3), col2 char default ‘S’);
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 99
Insert into t2 values(1, DEFAULT);
Update t2
Set col2 = DEFAULT
where col1=1;
TRANSACTION NAME
Set transaction name ‘Atualizaçao Noturna’;
MULTITABLE INSERT
INSERT ALL
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date, sales_sun)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
SELECT product_id, customer_id, weekly_start_date, sales_sun,
sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
FROM sales_input_table;
Condicional, Insert all:
INSERT ALL
WHEN order_total < 1000000 THEN
INTO small_orders
WHEN order_total > 1000000 AND order_total < 2000000 THEN
INTO medium_orders
WHEN order_total > 2000000 THEN
INTO large_orders
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 100
SELECT order_id, order_total, sales_rep_id, customer_id
FROM orders;
Condicional, Insert First: INSERT FIRST
WHEN ottl < 100000 THEN
INTO small_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl > 100000 and ottl < 200000 THEN
INTO medium_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl > 290000 THEN
INTO special_orders
WHEN ottl > 200000 THEN
INTO large_orders
VALUES(oid, ottl, sid, cid)
SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;
GROUP BY
Além do CUBE() e ROLLUP() da versão 8i no Oracle 9i temos também a
GROUPING SETS()
CUBE(a, b, c)
Equivale a:
GROUPING SETS ((a, b, c), (a, b), (a, c), (b, c), (a), (b), (c),
())
ROLLUP(a, b, c)
Equivale a:
GROUPING SETS ((a, b, c), (a, b), ())
Cláusula WITH
WITH channel_summary AS (
SELECT channels.channel_desc, SUM(amount_sold) AS
channel_total
FROM sales, channels
WHERE sales.channel_id = channels.channel_id
GROUP BY channels.channel_desc
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 101
)
SELECT channel_desc, channel_total
FROM channel_summary
WHERE channel_total > (
SELECT SUM(channel_total) * 1/3
FROM channel_summary);
Oracle 8i
Relatórios em HTML no SQL*Plus
SET ECHO OFF
SET MARKUP HTML ON SPOOL ON
SPOOL c:\test.html
SELECT * FROM test;
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO OFF
sqlplus -s -m "HTML ON" us/pw @c:\test.sql>test.html
CASE Simples :
select CASE extract(day from sysdate)
when 1 then
'Primeiro'
when 1 then
'Segundo'
else
'...'
END As Dia from dual
ANALYTIC FUNCTIONS
{SUM|AVG|MAX|MIN|COUNT|STDDEV|VARIANCE|FIRST_VALUE|LAST_VALUE}
({<value expression1> | *}) OVER
([PARTITION BY <value expression2>[,...]]
ORDER BY <value expression3> [collate clause>]
[ASC| DESC] [NULLS FIRST | NULLS LAST] [,...]
ROWS | RANGE
{{UNBOUNDED PRECEDING | <value expression4> PRECEDING}
| BETWEEN
{UNBOUNDED PRECEDING | <value expression4> PRECEDING}
AND{CURRENT ROW | <value expression4> FOLLOWING}}
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 102
RANK() OVER (
[PARTITION BY <value expression1> [, ...]]
ORDER BY <value expression2> [collate clause] [ASC|DESC]
[NULLS FIRST|NULLS LAST] [, ...]
)
ROW_NUMBER() OVER
([PARTITION BY <value expression1> [, ...]]
ORDER BY <value expression2> [collate clause] [ASC|DESC]
[NULLS FIRST | NULLS LAST] [, ...])
{SUM | AVG | MAX | MIN | COUNT | STDDEV | VARIANCE}
([ALL | DISTINCT] {<value expression1> | *})
OVER ([PARTITION BY <value expression2>[,...]])
{LAG | LEAD}
(<value expression1>, [<offset> [, <default>]]) OVER
([PARTITION BY <value expression2>[,...]]
ORDER BY <value expression3> [collate clause>]
[ASC | DESC] [NULLS FIRST | NULLS LAST] [,...])
SELECT s_productkey, s_amount,
RANK() OVER (ORDER BY s_amount) AS default_rank,
RANK() OVER (ORDER BY s_amount DESC NULLS LAST) AS
custom_rank
FROM sales;
SELECT Acct_number, Trans_date, Trans_amount,
SUM(Trans_amount) OVER (PARTITION BY Acct_number
ORDER BY Trans_date ROWS UNBOUNDED PRECEDING) AS
Balance
FROM Ledger
ORDER BY Acct_number, Trans_date;
SELECT Account_number, Trans_date, Trans_amount,
AVG (Trans_amount) OVER
(PARTITION BY Account_number ORDER BY Trans_date
RANGE INTERVAL '7' DAY PRECEDING) AS mavg_7day;
SELECT Account_number, Trans_date, Trans_amount,
AVG (Trans_amount) OVER
(PARTITION BY Account_number ORDER BY Trans_date
RANGE BETWEEN INTERVAL '1' MONTH PRECEDING
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 103
AND INTERVAL '1' MONTH FOLLOWING) as
c_avg
FROM Ledger;
SELECT NRGUIA, DTREFERENCIA, VLPAGO, SUM(VLPAGO) OVER(ORDER
BY NRGUIA ROWS BETWEEN 1 PRECEDING AND CURRENT ROW )
FROM MOVIMENTO_PRESTADOR
WHERE NRREGISTRO_PREST= 19994
AND NRPERIODO_COMPET = 200412;
SELECT t_timekey, s_amount,
FIRST_VALUE(s_amount) OVER
(ORDER BY t_timekey ROWS 1 PRECEDING) AS
LAG_physical,
SUM(s_amount) OVER
(ORDER BY t_timekey ROWS 1 PRECEDING) AS MOVINGSUM,
FROM sales, time
WHERE sales.s_timekey = time.t_timekey
ORDER BY t_timekey;
SELECT t_timekey, s_amount,
LAG(s_amount,1) OVER (ORDER BY t_timekey) AS
LAG_amount,
LEAD(s_amount,1) OVER (ORDER BY t_timekey) AS
LEAD_amount
FROM sales, time
WHERE sales.s_timekey = time.t_timekey
ORDER BY t_timekey;
SELECT p_productkey, s_amount,
ROW_NUMBER() (ORDER BY s_amount DESC NULLS LAST) AS
srnum
FROM product, sales
WHERE p_productkey = s_productkey;
CUBE(), ROLLUP() e GROUPPING()
SELECT Time, Region, Department, SUM(Profit) AS Profit,
GROUPING (Time) as T,
GROUPING (Region) as R,
GROUPING (Department) as D
FROM Sales
GROUP BY ROLLUP (Time, Region, Department);
Introdução ao Oracle SQL 11g
Rua Campo Grande, 533, Centro, Cuiabá/MT | http://www.tdstecnologia.com.br 104
SELECT
decode(grouping(Time), 1, 'All Times', Time) AS Time,
decode(grouping(region), 1, 'All Regions', 0, null)) AS
Region, sum(Profit) AS Profit FROM Sales
group by CUBE(Time, Region);
SELECT Time, Region, Department, SUM(Profit) AS Profit,
GROUPING (Time) AS T,
GROUPING (Region) AS R,
GROUPING (Department) AS D
FROM Sales
GROUP BY CUBE (Time, Region, Department)
HAVING (GROUPING(Department)=1 AND GROUPING(Region)=1 AND
GROUPING(Time)=1)
OR (GROUPING(Region)=1 AND (GROUPING(Department)=1)
OR (GROUPING(Time)=1 AND GROUPING(department)=1);