OC3361 - Oracle Database 11g - SQL e PL/SQL

Instruções SQL e programas PL/SQL de alto desempenho e com recursos avançados. Sera abordado os últimos recursos e ferramentas SQL, técnicas de otimização de desempenho, consultas avançadas, suporte à Java e XML.

Carga Horária

40 h

Conteúdo

1 - Introdução
O que é um banco de dados relacional? 
Apresentando a linguagem SQL (Structured Query Language) 
Usando o SQL*Plus
Iniciando o SQL*Plus
Iniciando o SQL*Plus a partir da linha de comando 
Executando uma instrução SELECT usando o SQL*Plus 
SQL Developer 
Criando o esquema da loja

Executando o script SQL*Plus para criar o esquema da loja 
Instruções DDL (Data Definition Language) usadas para criar o esquema da loja 
Adicionando, modificando e removendo linhas
Adicionando uma linha em uma tabela 
Modificando uma linha existente em uma tabela 
Removendo uma linha de uma tabela
Os tipos BINARY_FLOAT e BINARY_DOUBLE

Vantagens de BINARY_FLOAT e BINARY_DOUBLE
Usando BINARY_FLOAT e BINARY_DOUBLE em uma tabela 
Valores especiais 
Saindo do SQL*Plus
Introdução ao PL/SQL da Oracle


2 - Recuperando informações de tabelas de banco de dados
Executando instruções SELECT em uma única tabela
Recuperando todas as colunas de uma tabela 
Especificando as linhas a serem recuperadas usando a cláusula WHERE 
Identificadores de linha
Números de linha 
Efetuando cálculos aritméticos
Efetuando aritmética de data 
Usando colunas na aritmética 
Usando apelidos de coluna
Combinando saída de coluna usando concatenação 
Valores nulos
Exibindo linhas distintas 
Comparando valores 
Usando os operadores SQL 
Usando o operador LIKE
Usando o operador IN 
Usando o operador BETWEEN 
Usando os operadores lógicos
Precedência de operadores 
Classificando linhas usando a cláusula ORDER BY 
Executando instruções SELECT que usam duas tabelas
Usando apelidos de tabela
Produtos cartesianos 
Executando instruções SELECT que usam mais de duas tabelas 
Condições de join e tipos de join
Não-equijoins 
Joins externas
Autojoins 
Realizando joins usando a sintaxe SQL/92
Realizando joins internas em duas tabelas usando SQL/92
Simplificando joins com a palavra-chave USING 
Realizando joins internas em mais de duas tabelas usando SQL/92 
Realizando joins internas em várias colunas usando SQL/92 
Realizando joins externas usando SQL/92 
Realizando autojoin usando SQL/92
Realizando join cruzada usando SQL/92 

3 - Usando o SQL*Plus
Exibindo a estrutura de uma tabela 
Editando instruções SQL 
Salvando, recuperando e executando arquivos 
Formatando colunas 
Definindo o tamanho da página
Definindo o tamanho da linha
Limpando formatação de coluna 
Usando variáveis
Variáveis temporárias
Variáveis definidas 
Criando relatórios simples
Usando variáveis temporárias em um script
Usando variáveis definidas em um script
Passando um valor para uma variável em um script
Adicionando um cabeçalho e um rodapé
Calculando subtotais
Obtendo ajuda do SQL*Plus
Gerando instruções SQL automaticamente
Desconectando-se do banco de dados e saindo do SQL*Plus

4 - Usando funções simples
Usando funções de uma única linha
Funções de caractere
Funções numéricas
Funções de conversão
Funções de expressão regular
Usando funções agregadas
Agrupando linhas
Usando a cláusula GROUP BY para agrupar linhas
Utilização incorreta de chamadas de funções agregadas
Usando a cláusula HAVING para filtrar grupos de linhas
Usando as cláusulas WHERE e GROUP BY juntas
Usando as cláusulas WHERE, GROUP BY e HAVING juntas

5 - Armazenando e processando datas e horas
Exemplos simples de armazenamento e recuperação de datas
Convertendo data/horários com TO_CHAR() e TO_DATE()
Usando TO_CHAR() para converter uma data/horário em uma string
Usando TO_DATE() para converter uma string em uma data/horário
Configurando o formato de data padrão
Como o Oracle interpreta anos de dois dígitos
Usando o formato YY 
Usando o formato RR
Usando funções de data/horário
ADD_MONTHS()
LAST_DAY()
MONTHS_BETWEEN()
NEXT_DAY()
ROUND()
SYSDATE
TRUNC()
Usando fusos horários
Funções de fuso horário
O fuso horário do banco de dados e o fuso horário da sessão
Obtendo diferenças de fuso horário
Obtendo nomes de fuso horário
Convertendo uma data/horário de um fuso horário para outro
Usando timestamp
Usando os tipos de timestamp
Funções de timestamp
Usando intervalos de tempo
Usando o tipo INTERVAL YEAR TO MONTH
Usando o tipo INTERVAL DAY TO SECOND. 
Funções de intervalo de tempo

6 - Subconsultas
Tipos de subconsultas
Escrevendo subconsultas de uma única linha
Subconsultas em uma cláusula WHERE
Usando outros operadores de uma única linha
Subconsultas em uma cláusula HAVING
Subconsultas em uma cláusula FROM (visões inline)
Erros que você pode encontrar
Escrevendo subconsultas de várias linhas
Usando IN em uma subconsulta de várias linhas
Usando ANY em uma subconsulta de várias linhas. 
Usando ALL em uma subconsulta de várias linhas
Escrevendo subconsultas de várias colunas
Escrevendo subconsultas correlacionadas
Exemplo de subconsulta correlacionada
Usando EXISTS e NOT EXISTS em uma subconsulta correlacionada
Escrevendo subconsultas aninhadas
Escrevendo instruções UPDATE e DELETE contendo subconsultas
Escrevendo uma instrução UPDATE contendo uma subconsulta
Escrevendo uma instrução DELETE contendo uma subconsulta

7 - Consultas avançadas
Usando os operadores de conjunto
As tabelas de exemplo
Usando o operador UNION ALL
Usando o operador UNION
Usando o operador INTERSECT
Usando o operador MINUS
Combinando operadores de conjunto
Usando a função TRANSLATE()
Usando a função DECODE()
Usando a expressão CASE
Usando expressões CASE simples
Usando expressões CASE pesquisadas
Consultas hierárquicas
Os dados de exemplo
Usando as cláusulas CONNECT BY e START WITH
Usando a pseudocoluna LEVEL
Formatando os resultados de uma consulta hierárquica
Começando em um nó que não é o raiz
Usando uma subconsulta em uma cláusula START WITH
Percorrendo a árvore para cima
Eliminando nós e ramos de uma consulta hierárquica
Incluindo outras condições em uma consulta hierárquica
Usando as cláusulas GROUP BY estendidas
As tabelas de exemplo
Usando a cláusula ROLLUP
Usando a cláusula CUBE
Usando a função GROUPING()
Usando a cláusula GROUPING SETS
Usando a função GROUPING_ID()
Usando uma coluna várias vezes em uma cláusula GROUP BY
Usando a função GROUP_ID()
Usando as funções analíticas
A tabela de exemplo
Usando as funções de classificação
Usando as funções de percentil inversas
Usando as funções de janela
Usando as funções de relatório
Usando as funções LAG() e LEAD()
Usando as funções FIRST e LAST
Usando as funções de regressão linear
Usando as funções de classificação hipotética e distribuição
Usando a cláusula MODEL
Um exemplo da cláusula MODEL
Usando notação posicional e simbólica para acessar células
Acessando um intervalo de células com BETWEEN e AND
Acessando todas as células com ANY e IS ANY
Obtendo o valor atual de uma dimensão com CURRENTV()
Acessando células com um loop FOR
Tratando de valores nulos e ausentes
Atualizando células existentes
Usando as cláusulas PIVOT e UNPIVOT
Um exemplo simples da cláusula PIVOT
Usando pivô em várias colunas
Usando várias funções agregadas em um pivô
Usando a cláusula UNPIVOT

8 - Alterando o conteúdo de tabelas
Adicionando linhas com a instrução INSERT
Omitindo a lista de colunas
Especificando um valor nulo para uma coluna
Incluindo apóstrofos e aspas em um valor de coluna
Copiando linhas de uma tabela para outra
Modificando linhas com a instrução UPDATE
A cláusula RETURNING
Removendo linhas com a instrução DELETE. 
Integridade do banco de dados
Aplicação das restrições de chave primária. 
Aplicação das restrições de chave estrangeira
Usando valores padrão
Mesclando linhas com MERGE
Transações de banco de dados
Confirmando e revertendo uma transação
Iniciando e terminando uma transação
Savepoints (pontos de salvamento)
Propriedades de transação ACID
Transações concorrentes
Bloqueio de transação
Níveis de isolamento de transação 
Exemplo de transação SERIALIZABLE
Consultas Flashback
Concedendo o privilégio de usar flashbacks
Consultas flashback de tempo
Consultas flashback com número de alteração de sistema

9 - Usuários, privilégios e atribuições
Usuários
Criando um usuário
Alterando a senha de um usuário 
Excluindo um usuário
Privilégios de sistema
Concedendo privilégio de sistema a um usuário
Verificando os privilégios de sistema concedidos a um usuário. 
Utilizando privilégios de sistema
Revogando privilégios de sistema de um usuário
Privilégios de objeto
Concedendo privilégios de objeto a um usuário
Verificando os privilégios de objeto concedidos
Verificando os privilégios de objeto recebidos
Utilizando privilégios de objeto
Sinônimos
Sinônimos públicos
Revogando privilégios de objeto
Atribuições (Roles)
Criando atribuições
Concedendo privilégios a atribuições
Concedendo atribuições a um usuário
Verificando as atribuições concedidas a um usuário
Verificando os privilégios de sistema concedidos a uma atribuição. 
Verificando os privilégios de objeto concedidos a uma atribuição
Utilizando os privilégios concedidos a uma atribuição
Atribuições padrão
Revogando uma atribuição
Revogando privilégios de uma atribuição
Excluindo uma atribuição
Auditoria
Privilégios necessários para fazer auditoria
Exemplos de auditoria
Visões de trilha de auditoria

10 - Criando tabelas, seqüências, índices e visões
Tabelas
Criando uma tabela
Obtendo informações sobre tabelas
Obtendo informações sobre colunas nas tabelas
Alterando uma tabela
Mudando o nome de uma tabela
Adicionando um comentário em uma tabela
Truncando uma tabela
Excluindo uma tabela
Seqüências
Criando uma seqüência
Recuperando informações sobre seqüências
Usando uma seqüência
Preenchendo uma chave primária usando uma seqüência
Modificando uma seqüência
Excluindo uma seqüência
Índices
Criando um índice de árvore B
Criando um índice baseado em função
Recuperando informações sobre índices
Recuperando informações sobre índices em uma coluna
Modificando um índice
Excluindo um índice. 
Criando um índice de bitmap
Visões
Criando e usando uma visão
Modificando uma visão
Excluindo uma visão
Arquivos de Dados de Flashback

11 - Introdução à programação PL/SQL
Estrutura de bloco
Variáveis e tipos
Lógica condicional
Loops
Loops simples
Loops WHILE
Loops FOR
Cursores
Passo 1: Declarar as variáveis para armazenar os valores de coluna
Passo 2: Declarar o cursor
Passo 3: Abrir o cursor
Passo 4: Buscar as linhas do cursor
Passo 5: Fechar o cursor
Exemplo completo: product_cursor.sql
Cursores e loops FOR
Instrução OPEN-FOR
Cursores irrestritos
Exceções
Exceção ZERO_DIVIDE
Exceção DUP_VAL_ON_INDEX
Exceção INVALID_NUMBER
Exceção OTHERS
Procedures
Criando uma procedure 
Chamando uma procedure
Obtendo informações sobre procedures
Excluindo uma procedure. 
Vendo erros em uma procedure
Funções
Criando uma função
Chamando uma função
Obtendo informações sobre funções
Excluindo uma função
Pacotes (Packages) 
Criando uma especificação de pacote
Criando o corpo de um pacote
Chamando funções e procedures em um pacote
Obtendo informações sobre funções e procedures em um pacote
Excluindo um pacote
Triggers
Quando um trigger é disparado
Configuração do trigger de exemplo
Criando um trigger
Disparando um trigger
Obtendo informações sobre triggers
Desativando e ativando um trigger 
Excluindo um trigger
Novos recursos PL/SQL no Oracle Databaseg
Tipo SIMPLE_INTEGER
Seqüências em PL/SQL
Geração de código de máquina nativo PL/SQL

12 - Objetos de banco de dados
Introdução aos objetos
Criando tipos de objeto 
Usando DESCRIBE para obter informações sobre tipos de objeto
Usando tipos de objeto em tabelas de banco de dados
Objetos de coluna
Tabelas de objeto
Identificadores de objeto e referências de objeto
Comparando valores de objeto
Usando objetos em PL/SQL 
A função get_products()
A procedure display_product()
A procedure insert_product()
A procedure update_product_price()
A função get_product()
A procedure update_product()
A função get_product_ref()
A procedure delete_product()
A procedure product_lifecycle()
A procedure product_lifecycle2()
Herança de tipo
Usando um objeto de subtipo no lugar de um objeto de supertipo
Exemplos em SQL
Exemplos em PL/SQL 
Objetos NOT SUBSTITUTABLE
Outras funções de objeto úteis
IS OF()
TREAT()
SYS_TYPEID()
Tipos de objeto NOT INSTANTIABLE
Construtores definidos pelo usuário
Sobrescrevendo métodos
Invocação generalizada

13 - Coleções
Introdução às coleções
Criando tipos de coleção
Criando um tipo de varray
Criando um tipo de tabela aninhada
Usando um tipo de coleção para definir uma coluna em uma tabela
Usando um tipo de varray para definir uma coluna em uma tabela
Usando um tipo de tabela aninhada para definir uma coluna em uma tabela
Obtendo informações sobre coleções
Obtendo informações sobre um varray
Obtendo informações sobre uma tabela aninhada
Preenchendo uma coleção com elementos
Preenchendo um varray com elementos
Preenchendo uma tabela aninhada com elementos
Recuperando elementos de coleções
Recuperando elementos de um varray
Recuperando elementos de uma tabela aninhada
Usando TABLE() para tratar uma coleção como uma série de linhas
Usando TABLE() com um varray
Usando TABLE() com uma tabela aninhada
Modificando elementos de coleções
Modificando elementos de um varray
Modificando elementos de uma tabela aninhada
Usando um método de mapeamento para comparar o conteúdo de tabelas aninhadas
Usando CAST() para converter coleções de um tipo para outro
Usando CAST() para converter um varray em uma tabela aninhada
Usando CAST() para converter uma tabela aninhada em um varray
Usando coleções em PL/SQL
Manipulando um varray
Manipulando uma tabela aninhada
Métodos de coleção PL/SQL
Coleções de múltiplos níveis
Aprimoramentos feitos nas coleções pelo Oracle Databaseg
Arrays associativos
Alterando o tamanho de um tipo de elemento
Aumentando o número de elementos em um varray
Usando varrays em tabelas temporárias
Usando um tablespace diferente para a tabela de armazenamento de uma tabela
aninhada
Suporte ANSI para tabelas aninhadas

14 - Large objects (objetos grandes)
Introdução aos large objects (LOBs)
Os arquivos de exemplo
Tipos de large object
Criando tabelas contendo large objects
Usando large objects em SQL
Usando CLOBs e BLOBs
Usando BFILEs
Usando large objects em PL/SQL
APPEND()
CLOSE()
COMPARE()
COPY()
CREATETEMPORARY()
ERASE()
FILECLOSE()
FILECLOSEALL()
FILEEXISTS()
FILEGETNAME()
FILEISOPEN()
FILEOPEN()
FREETEMPORARY()
GETCHUNKSIZE()
GET_STORAGE_LIMIT() 
GETLENGTH()
INSTR()
ISOPEN()
ISTEMPORARY()
LOADFROMFILE()
LOADBLOBFROMFILE()
LOADCLOBFROMFILE()
OPEN()
READ()
SUBSTR()
TRIM()
WRITE()
WRITEAPPEND()
Exemplos de procedures em PL/SQL
Tipos LONG e LONG RAW
As tabelas de exemplo
Adicionando dados em colunas LONG e LONG RAW
Convertendo colunas LONG e LONG RAW em LOBs
Aprimoramentos feitos pelo Oracle Databaseg nos large objects
Conversão implícita entre objetos CLOB e NCLOB
Uso do atributo :new ao utilizar LOBs em um trigger 
Aprimoramentos feitos pelo Oracle Databaseg nos large objects
Criptografia de dados de LOB
Compactando dados de LOB
Removendo dados de LOB duplicados

15 - Executando SQL usando Java
Começando
Configurando seu computador
Configurando a variável de ambiente ORACLE_HOME
Configurando a variável de ambiente JAVA_HOME
Configurando a variável de ambiente PATH
Configurando a variável de ambiente CLASSPATH 
Configurando a variável de ambiente LD_LIBRARY_PATH
Os drivers JDBC da Oracle. 
O driver Thin
O driver OCI
O driver interno server-side
O driver Thin server-side
Importando pacotes JDBC
Registrando os drivers JDBC da Oracle
Abrindo uma conexão de banco de dados
Conectando-se no banco de dados com getConnection()
A URL do banco de dados
Conectando-se com o banco de dados usando uma origem de dados Oracle
Criando um objeto JDBC Statement
Recuperando linhas do banco de dados
Passo 1: Criar e preencher um objeto ResultSet
Passo 2: Ler os valores de coluna do objeto ResultSet
Passo 3: Fechar o objeto ResultSet
Adicionando linhas no banco de dados
Modificando linhas no banco de dados
Excluindo linhas do banco de dados
Manipulando números
Manipulando valores nulos no banco de dados
Controlando transações de banco de dados
Executando instruções Data Definition Language
Tratamento de exceções
Fechando seus objetos JDBC
Exemplo de programa: BasicExample1.java
Compilando BasicExample1
Executando BasicExample1
SQL Prepared Statements
Exemplo de programa: BasicExample2.java
As extensões da Oracle para JDBC
O pacote oracle.sql
O pacote oracle.jdbc
Exemplo de programa: BasicExample3.java

16 - Ajuste de SQL
Introdução ao ajuste de SQL
Use uma cláusula WHERE para filtrar linhas
Use joins de tabela em vez de várias consultas
Use referências de coluna totalmente qualificadas ao fazer joins
Use expressões CASE em vez de várias consultas
Adicione índices nas tabelas
Use WHERE em vez de HAVING
Use UNION ALL em vez de UNION
Use EXISTS em vez de IN
Use EXISTS em vez de DISTINCT
Use GROUPING SETS em vez de CUBE
Use variáveis de bind
Instruções SQL não idênticas
Instruções SQL idênticas que usam variáveis de bind 
Listando e imprimindo variáveis de bind
Usando uma variável de bind para armazenar um valor
retornado por uma função PL/SQL
Usando uma variável de bind para armazenar linhas de um REFCURSOR
Comparando o custo da execução de consultas
Examinando planos de execução
Comparando planos de execução
Passando dicas para o otimizador
Ferramentas de ajuste adicionais
Oracle Enterprise Manager Diagnostics Pack
Automatic Database Diagnostic Monitor

17 - XML e o banco de dados Oracle
Introdução à XML
Gerando código XML a partir de dados relacionais
XMLELEMENT()
XMLATTRIBUTES()
XMLFOREST()
XMLAGG()
XMLCOLATTVAL()
XMLCONCAT()
XMLPARSE()
XMLPI()
XMLCOMMENT()
XMLSEQUENCE()
XMLSERIALIZE()
Um exemplo em PL/SQL que grava os dados XML em um arquivo
XMLQUERY()
Salvando XML no banco de dados
O arquivo de exemplo XML
Criando o esquema de exemplo XML
Recuperando informações do esquema XML de exemplo
Atualizando informações no esquema de exemplo XML

Pré-requisitos

Lógica de Programação ou ter realizado o curso SSWLPR

Certificação

Conhecimento compativel para a Certificação OCA Oracle Database 11g: SQL Fundamentals I (Exam 1Z0-051)