Arquivos de Categoria: Provas

Bando de Dados II – Aula 15A

Simulado para a prova de Banco de dados II, podendo ser baixado no Github, através do seguinte endereço:

https://github.com/rodrigoksaito/anchieta

 

Anúncios

Banco de Dados II – Aula 15

BACKUP

  • Um membro do role de servidor sysadmin pode fazer backup de qualquer banco de dados da instancia e os membros do role de banco de dados db_owner podem fazer backup de seus banco de dados.
  • Voce também pode adicionar um usuário no role de banco de dados fixo db_backupoperator para permitir ao usuário fazer backup de um banco de dados, enquanto impede qualquer outro acesso ao banco de dados.

TIPOS DE BACKUP

  • O SQL Server apresenta quatro diferentes tipos de backup :
    • Completo;
    • Diferencial;
    • Log de Transação;
    • Grupo de arquivos (filegroups)

BACKUP COMPLETO

  • Um backup completo captura todas as páginas que contêm dados dentro de um banco de dados.
  • As páginas que não contém dados não são incluídas no backup.
  • Portanto, um backup nunca é maior (e, a maioria dos casos, é menor) do que o banco de dados para o qual é criado.
  • Um backup completo é a base para a recuperação de um banco de dados e deve existir antes que você possa usar um backup diferencial ou de log de transação;
  • Como é mais comum fazer backup do que restaurar um banco de dados, o mecanismo de backup é otimizado para o processo de backup.

BACKUP

  • Quando um backup é iniciado, o mecanismo de backup captura as páginas dos arquivos de dados o mais rapidamente possível, sem considerar a ordem das páginas, vários threads podem ser usados para gravar páginas em seu dispositivo de backup.
  • Um backup pode ser executado concomitantemente com outras operações do banco de dados.
  • Como pode ser feita alterações no banco de dados enquanto um backup está em execução, o SQL Server precisa acomodá-las ao passo que também garante que os backups sejam consistentes para propósitos de restauração.
  • O fator limitante para a velocidade de um backup é o desempenho do dispositivo onde o backup está sendo gravado.
  • As únicas operações que não são permitidas durante um backup completo são: adicionar ou remover um arquivo do banco de dados e reduzir um banco de dados;
  • Os dois únicos parâmetros exigidos para um backup são o nome do banco de dados e o dispositivo de backup.
  • Quando você especifica um dispositivo de disco para backup, um diretório e um nome de arquivo podem ser especificados;
  • Se não for especificado um diretório, o SQL Server fará o backup no disco e gravará o arquivo no diretório padrão configurado na instancia;
  • Embora a maioria dos backups seja gravada em um único arquivo em disco ou em um único dispositivo em fita, você pode especificar até 64 dispositivos de backup.
  • Quando você especifica mais de um dispositivo de backup, o SQL Server divide o striped backup (backup em faixas) por todos os dispositivos especificados.
  • Quando o SQL Server coloca um striped backup em vários dispositivos, é obrigatório que todos os dispositivos restaurem com sucesso.
  • O SQL Server não fornece failover ou tolerância à falhas dentro do conjunto de faixas. O conjunto de faixas é utilizado estritamente para propósitos de desempenho.

EXEMPLO DE STRIPED BACKUP

--striped backup

use master
go 

--Em Windows
BACKUP DATABASE AdventureWorks2012
  TO DISK = 'C:\temp\AWorks2012_1.bak',
  DISK = 'C:\emp\AWoks2012_2.bak'
GO

--Em Linux
BACKUP DATABASE [AdventureWorks2016CTP3]
  TO DISK = '/var/opt/mssql/AdvWorks2016_1.bak',
  DISK = '/var/opt/mssql/AdvWorks2016_2.bak'
GO

MIRROR TO

  • A cláusula MIRROR TO oferece um recurso interno para criar até quatro cópias de um backup em uma única operação.
  • Quando você inclui a cláusula MIRROR TO, o SQL Server recupera a página uma vez do banco de dados e grava uma cópia da página em cada espelho de backup.
  • Durante a restauração, os espelhos tem um pequeno número de requisitos:
    • Todos os dispositivos de backup devem ser o mesmo tipo de mídia;
    • Cada espelho deve ter o mesmo número de dispositivos de backup;
    • WITH FORMAT deve ser especificado no comando de backup;

DIMINUINDO TEMPOS DE BACKUP

  • A sobrecarga da compactação sempre vale a pena.
  • O tempo economizado por um backup compactado supera em muito a sobrecarga associada à operação de compactação.
  • O SQL Server tem uma opção de configuração chamada padrão de compactação de backup (backup compression default)
  • Você pode configurar de modo a sempre ter backups compactados, independente de especificar a compactação explicitamente, porém está disponível somente no SQL Server Enterprise.
  • Um único dispositivo de backup pode conter vários backups
  • As opções INIT/NOINIT de um comando BACKUP controlam se um arquivo de backup existente é sobrescrito ou recebe anexação.
  • Quando você especifica NOINIT e está fazendo backup em um arquivo já existente, o SQL Server anexa o novo backup no final do arquivo.
  • Se você especifica INIT e o arquivo já existe, o SQL Server sobrescreve o arquivo com o conteúdo do novo backup.
  • Quando CHECKSUM é especificado, o SQL Server confere a soma de verificação da página, se ela existir, antes de gravar a página do backup.
  • Além disso, é calculada uma soma de verificação para o backup inteiro, a qual pode ser usada para determinar se o backup foi corrompido.

BACKUP DE LOG DE TRANSAÇÃO

  • Cada alteração física feita em um banco de dados tem uma entrada inserida no log de transação.
  • Cada linha recebe um número exclusivo internamente, chamado LSN (Log Sequence Number).
  • O LSN é um numero inteiro que começa do 0 quando o banco de dados é criado e é incrementado até o infinito
  • Um LSN nunca é reutilizado para um banco de dados e é sempre incrementado.
  • Basicamente, um LSN fornece um número em sequencia para cada alteração feita em um banco de dados.
  • O conteúdo de um log de transação é dividido em 2 partes básicas – uma ativa e uma inativa.
  • A parte inativa contém todas as alterações efetivadas no banco de dados.
  • A parte ativa contém todas as transações ainda não efetivadas.Quando um backup de log de transação é executado, o SQL Server inicia no menor LSN do log de transação e começa a gravar cada registro de log de transação sucessivo no backup.
  • Assim que o SQL Server atinge o primeiro LSN que ainda não foi efetivado (isto é, a transação aberta mais antiga), o backup de log de transação termina.
  • Então, a parte do log de transação que teve o backup feito é removida, permitindo que o espaço seja reutilizado.
  • Com base no número de sequencia é possível restaurar um backup de log de transação após o outro para recuperar um banco de dados em qualquer ponto no tempo, simplesmente seguindo o encadeamento de transações, conforme identificado pelo LSN.
  • Como os backups de log de transação se destinam a ser restaurados um após o outro, as constraints sobre eles dependem de se ter a sequencia de LSNs inteira intacta.
  • Um backup de log de transação reúne todas as transações efetivadas no log desde o último backup de log de transação.
  • Conteúdo, como um backup de log de transações contém apenas as transações que foram executadas no banco de dados, você precisa de um ponto de partida para o encadeamento de logs de transação.
  • Antes de executar um backup de log de transação, você deve fazer um backup completo.
  • Após o primeiro backup você pode restaurar o banco de dados em qualquer ponto no tempo, desde o encadeamento de logs de transação não seja interrompido.
  • Backups completos adicionais podem ser criados para se ter um ponto de partida mais recente para uma operação de restauração.
  • Independente do número de backups completos criados, desde que você não tenha introduzido uma lacuna no encadeamento de LSNs, pode começar com qualquer backup completo e restaurar qualquer log de transação desse ponto em diante para recuperar um banco de dados.

SINTAXE DO BACKUP DE LOG DE TRANSAÇÃO

Sem título6

BACKUP DIFERENCIAIS

  • Um backup diferencial captura todas as extensões que mudaram desde o último backup completo.
  • O principal objetivo de um backup diferencial é reduzir o número de backups de log de transação que precisam ser restaurados.
  • Um backup diferencial precisa ser aplicado em um backup completo e não pode existir até que um backup completo seja criado.
  • O SQL Server controla cada extensão que foi alterada após um backup completo usando uma página especial no cabeçalho de um banco de dados, chamado DCM (Differential Change Map – mapa de alteração diferencial)
  • Um backup completo zera o conteúdo do DCM.
  • Quando são feitas alterações em extensões dentro do banco de dados, o SQL Server ajusta para 1 o bit correspondente à extensão.
  • Quando um backup diferencial é executado, o SQL Server lê o conteúdo do DCM para localizar todas as extensões que foram alteradas desde o último backup completo.
  • Um backup diferencial não é o mesmo que um backup de log de transação. Um backup diferencial contém todas as páginas alteradas desde o último backup completo.

OPÇÃO COPY ONLY

  • Uma das opções que podem ser especificadas para qualquer tipo de backup é a COPY_ONLY.
  • Cada backup executado em um banco de dados tem um efeito no ponto de partida de uma recuperação e em quais backups podem ser usados.
  • Os backups diferenciais contém todas as extensões que mudaram desde o ultimo backup completo; portanto, todo backup completo executado altera o ponto de partida em que um backup diferencial é baseado.
  • Quando um backup de log de transação é executado, as transações que tiveram o backup feito são removidas do log de transação.
  • Ocasionalmente você precisa gerar um backup para criar um banco de dados para um ambiente de desenvolvimento ou teste.
  • Voce quer ter o conjunto de dados mais recente, mas não quer afetar o conjunto de backup do ambiente de produção.
  • A opção COPY_ONLY permite gerar um backup que pode ser usado para criar o ambiente de desenvolvimento ou teste, mas não afeta o estado do banco de dados nem o conjunto de backups que está em produção.
  • Um backup completo com a opção COPY_ONLY não reinicia a página do mapa de alterações de diferencial e, portanto, não tem nenhum impacto nos backups diferenciais.
  • Um backup de log de transação com a opção COPY_ONLY não remove transações de log de transação.

BACKUP DE GRUPOS DE ARQUIVOS (FILE GROUPS)

  • Embora os backups completos capturem todas as páginas usadas no banco de dados inteiro, o backup completo de um banco de dados grande pode consumir uma quantidade significativa de espaço e tempo.
  • Se você precisa reduzir o tempo de backup, pode contar com o recurso de backups de grupo de arquivos, que permite ter como alvo parte de um banco de dados.
  • Backups de grupo de arquivos podem ser usados em conjunto com os backups diferenciais e de log de transação para recuperar uma parte do banco de dados no caso de uma falha.
  • Além disso, desde que você não precise restaurar o grupo de arquivos principal e esteja usando o SQL Server Enterprise, o banco de dados poderá permanecer online e acessível para os aplicativos durante a operação de restauração.
  • Somente a parte do banco de dados que está sendo restaurada fica offiline.

BACKUPS PARCIAIS

  • Os grupos de arquivo podem ser marcados como somente leitura.
  • Um grupo de arquivos somente leitura não pode ter alterações feitas nos objetos nele armazenados.
  • Como os objetivo dos backups é capturar as alterações para que você possa reconstruir um banco de dados no estado mais atual durante uma operação de recuperação, o backup de grupos de arquivo que não podem mudar consome espaço de forma desnecessária dentro do backup.
  • Para reduzir o tamanho de um backup para apenas os grupos de arquivo que podem mudar, você pode fazer um backup parcial. Os backups parciais são feitos especificando se a opção READ_WRITE_FILEGROUPS

Sem título7

  • Quando um backup parcial é executado, o SQL Server faz o backup do grupo de arquivos principal, de todos os grupos de arquivo de leitura/gravação e de todos os grupos de arquivos somente leitura especificados explicitamente.

CORRUPÇÃO DE PÁGINA

  • A partir do SQL Server 2005, houve a introdução da capacidade de colocar páginas corrompidas em quarentena, enquanto permitia ao banco de dados permanecer online.
  • Executando-se o comando abaixo, o SQL Server detecta páginas corrompidas e as coloca em quarentena.

Sem título8

  • Geralmente a corrupção de páginas são causadas por componentes de hardware que falham, especialmente controladoras e unidades de disco.
  • Antes de uma falha, as controladoras ou unidades de disco podem corromper páginas de dados realizando gravações incompletas;
  • Quando  o SQL Server grava uma página no disco, é calculada uma soma de verificação para a página.
  • Quando você ativa a verificação de página, sempre que uma página é lida do disco o SQL Server calcula uma nova soma de verificação e a compara com a soma de verificação armazenada na página.
  • Se as somas de verificação não corresponderem, o SQL Server retornará um erro e registrará a página em uma tabela no banco de dados msdb;
  • Embora páginas corrompidas possam ser colocadas em quarentena, o SQL Server tem um mecanismo de proteção em vigor para proteger seu banco de dados de uma corrupção generalizada.
  • Há um limite de 1.000 páginas corrompidas no total em um banco de dados.
  • Quando você atinge o limite de páginas corrompidas, o SQL Server tira o banco de dados do ar e coloca em um estado suspeito para protege-lo de danos maiores;

VALIDANDO BACKUP

  • Como os backups são sua apólice de seguro para um banco de dados, você precisa garantir que os backups criados sejam válidos e utilizáveis.
  • Para validar um backup, execute o seguinte comando:

Sem título9

  • Quando um backup é validado, o SQL Server realiza as seguintes verificações:
    • Calcula uma soma de verificação para o backup e compara com a soma de verificação armazenada no arquivo de backup;
    • Verifica se o cabeçalho do backup está gravado corretamente e é válido;
    • Percorre o encadeamento de páginas para certificar-se de que todas as páginas estejam no banco de dados e passam ser localizadas;

EXEMPLOS (MIRROR E COMPACTAÇÃO)

--backup com mirror e compactado

USE master
GO

--Em Windows
BACKUP DATABASE AdventureWorks2012
  TO DISK = 'c:\temp\AWorks_B1.bak'
  MIRROR TO DISK = 'c:\temp\AWorks_B2.bak'
  WITH COMPRESSION, INIT, FORMAT, CHECKSUM, STOP_ON_ERROR


--Em Linux
BACKUP DATABASE [AdventureWorks2016CTP3]
  TO DISK = '/var/opt/mssql/AdvWorks2016_compress.bak'
  MIRROR TO DISK = '/var/opt/mssql/AdvWorks2016_compress2.bak'
  WITH COMPRESSION, INIT, FORMAT, CHECKSUM, STOP_ON_ERROR

EXEMPLOS (BACKUP DE LOG DE TRANSAÇÃO)

--backups de transacao

USE AdventureWorks2012
GO

INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('TESTE1','Research and Development')
GO

BACKUP LOG AdventureWorks2012
TO DISK = 'c:\temp\AdventureWorks2012_1.trn'
WITH COMPRESSION, INIT, CHECKSUM, STOP_ON_ERROR
GO

--alteracao e backup de log novamente
INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('TESTE2','Research and Development')
GO

BACKUP LOG AdventureWorks2012
TO DISK = 'c:\temp\AdventureWorks2012_2.trn'
WITH COMPRESSION, INIT, CHECKSUM, STOP_ON_ERROR
GO

EXEMPLOS (BACKUP DIFERENCIAL)

--backup diferencial

USE AdventureWorks2012
GO

INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('TESTE3','Research and Development')
GO

BACKUP DATABASE AdventureWorks2012
TO DISK = 'c:\temp\AdventureWorks2012_1.dif'
MIRROR TO DISK = 'c:\temp\AdventureWorks2012_2.dif'
WITH DIFFERENTIAL, COMPRESSION, INIT, FORMAT, CHECKSUM, STOP_ON_ERROR
GO

EXEMPLOS (UTILIZANDO DISPOSITIVOS)

–criando um dispositivo de backup. mesmo que o caminho nao exista, ele vai
–criar,pois esta referenciando um dispositivo e nao um arquivo fisico

--teste de caminho inválido

exec sp_addumpdevice 'disk','meudispositivo','c:\a\backup.bak'
go


--caminho válido
exec sp_addumpdevice 'disk','meudispositivo','c:\temp\backup.bak'
go

-- excluindo um dispositivo
exec sp_dropdevice 'meudispositivo'
go

--até agora, foi apenas criado o dispositivo, mas nao o backup em si
backup database AdventureWorks2012 to meudispositivo
go

--para restaurar o banco de dados através do dispositivo
restore database AdventureWorks2012 from meudispositivo
go

--imaginando que seu banco corrompeu e voce nao consegue dar
-- drop no banco de dados
restore database AdventureWorks from meudispositivo with replace

--verificar sempre o modo de compatibilidade de restaurancao do bd
--FULL
--Simple - nao deixa fazer restore de um log
--Bulk log - somente guarda as transacoes do tipo bulk
-- o restore precisa ser compatível com o backup

--criando 2 dispositivos, um para backups full e outro para backup de log
exec sp_addumpdevice 'disk', 'meufull', 'c:\temp\MeuFull.bak'
go

exec sp_addumpdevice 'disk', 'meulog', 'c:\temp\MeuLog.bak'
go

--backup com with init, para nao deixar 2 arquivos no mesmo dispositivo
backup database AdventureWorks2012 to meufull with init

--backup do log
backup log AdventureWorks2012 to meulog
go

 
--simulando mais movimentacao no bd
use AdventureWorks
go

select * from Person.AddressType
go
insert into Person.AddressType (name, rowguid) 
values ('teste1',NewID())
go

 
--voltando ao banco master
use master
go

--backup do log, somente do log
backup log AdventureWorks2012 to meulog
go

 
--demonstrar em ambliente gráfico os arquivos dentro de um dispositivo

--restaurando o backup full e seus logs
--fez o restore somente do full, nao utilizando os logs

restore database AdventureWorks2012 from meufull
go

--fazendo backups novamente com init

backup database AdventureWorks2012 to meufull with init
go

backup log AdventureWorks2012 to meulog with init
go

--esperando pelos logs, em modo restoring
restore database AdventureWorks2012 from meufull with norecovery
Go

--restaurando o primeiro arquivo do log
restore log AdventureWorks2012 from meulog with file=1, recovery
go

-- exemplo de backup diferencial
exec sp_addumpdevice 'disk', 'meudiff', 'c:\temp\meudiff.bak'
go

backup database AdventureWorks2012 to meudiff with differential
go

Video 01

Banco de Dados II – Aula 12

MANIPULANDO EXCEÇÕES

  • Quando desenvolvemos procedures que são chamadas de outras procedures, devemos ter ciência dos processamentos e exceções de não processamento nas transações e chamadas de procedures;
  • Quando uma exceção é levantada na chamada de uma procedure, o controle vai imediatamente para a seção de exceção daquele bloco.
  • Uma exceção é considera processada se a exceção fornece um manipulador para a exceção levantada;
  • Quando uma exceção ocorre e é manipulada, o seguinte fluxo de código ocorre:
    • A exceção é erguida/levantada;
    • O controle é transferido para o manipulador de exceção;
    • O bloco é terminado;
    • O bloco/programa chamador continua a executar como se nada tivesse acontecido;

Sem título

EXEMPLO

CREATE OR REPLACE PROCEDURE add_department (p_name   VARCHAR2,
                                            p_mgr    NUMBER,
                                            p_loc    NUMBER) IS
BEGIN
  INSERT INTO departments(department_id,
                          department_name,
                          manager_id,
                          location_id)
  VALUES (departments_seq.NEXTVAL,
          p_name,
          p_mgr,
          p_loc);

  DBMS_OUTPUT.PUT_LINE('Departamento adicionado: ' || p_name);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Erro: adicionando departamento:' || p_name);
END add_department;
/

–CRIANDO UMA PROCEDURE PARA CHAMADA

CREATE OR REPLACE PROCEDURE CREATE_DEPARTMENTS IS
BEGIN
  add_department('Midia',100,1800);
  add_department('Edicao',99,1800);
  add_department('Propaganda',101,1800);
END;
/

–VERIFICANDO DADOS

SELECT * FROM DEPARTMENTS;

SET SERVEROUTPUT ON;

EXECUTE CREATE_DEPARTMENTS;

SELECT EMPLOYEE_ID
FROM   EMPLOYEES
WHERE  EMPLOYEE_ID IN (100,99,101);

ERRO GERADO DA CHAMADA DA PROCEDURE

Sem título2

MANIPULANDO EXCEÇÕES

  • Se uma exceção não é manipulada, então todos as declarações DML na chamada da procedure são feitas ROLLBACK, não efetivando nenhuma alteração em dados;
  • Declarações DML que não são afetadas são declarações que foram executadas antes da chamada do código PL/SQL a qual exceções não são manipuladas;

EXEMPLO

–EXEMPLO DE EXCEÇÕES NAO MANIPULADAS

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE add_department_noex (p_name VARCHAR2,
                                                 p_mgr  NUMBER,
                                                 p_loc  NUMBER ) IS
BEGIN
  INSERT INTO DEPARTMENTS (department_id,      
                           department_name,
                           manager_id,
                           location_id)
  VALUES(DEPARTMENTS_SEQ.NEXTVAL, p_name,
         p_mgr, p_loc);
  DBMS_OUTPUT.PUT_LINE('Departamento adicionado: ' || p_name);
END add_department_noex;
/

–CRIACAO DA PROCEDURE CHAMADORA

CREATE OR REPLACE PROCEDURE create_departments_noex IS
BEGIN
  add_department_noex('Midia 2',100,1800);
  add_department_noex('Edicao 2',99,1800);
  add_department_noex('Propaganda 2',101,1800);
END;

–CHAMADA DA PROCEDURE NOEX

EXECUTE create_departments_noex;

SELECT * FROM DEPARTMENTS;
  • Perceba que neste último exemplo, sem o tratamento de exceção, uma única linha não consistente não deixou que as demais linhas fossem incluídas na tabela;

Sem título3

EXCLUSÃO DE PROCEDURES

  • Para se fazer a exclusão de uma procedure, utilizamos o comando:
    • DROP PROCEDURE
  • Verifique antes na documentação se essa procedure não está sendo utilizada por nenhuma aplicação;
  • Caso você apague uma procedure que é chamada por outros objetos, o ORACLE não irá alertá-lo sobre isso;
  • As procedures ou objetos que são dependentes da procedure apagada ficam em estado “pendentes”;

Sem título4

VISUALIZANDO INFORMAÇÕES DE PROCEDURES UTILIZANDO DICIONARIO DE DADOS DAS VIEWS

  • O código fonte de subprogramas PL/SQL é armazenado no dicionário de dados;
  • Para ver os códigos armazenados, execute select nas declarações seguidas das tabelas:
    • Tabela USER_SOURCE mostra o código PL/SQL do seu dono;
    • Tabela ALL_SOURCE mostra o código a qual você tem direito concedido de EXECUTE pelo dono do subprograma;
DESCRIBE USER_SOURCE;

select * from USER_SOURCE;

DESCRIBE ALL_SOURCE;

select * from ALL_SOURCE;

LOOPS (LAÇOS)

  • Loops são repetições de um determinado trecho de código, ou seja, as instruções são executadas por várias vezes;
  • Em PL/SQL existem três tipos de loops;
    • LOOP BÁSICO (LAÇO);
    • LOOP FOR (PARA);
    • LOOP WHILE (ENQUANTO)

EXEMPLO DE LOOP BÁSICO

–LOOPS BASICO

DESCRIBE DEPARTMENTS;

DECLARE
  v_max_department_id  number;
  v_novo_departamento  departments.department_name%TYPE;
  v_contador           number := 1;
BEGIN
  SELECT MAX(DEPARTMENT_ID)
  INTO       v_max_department_id
  FROM      DEPARTMENTS;
  
  v_novo_departamento := '&NovoDepto';

  LOOP
    v_novo_departamento := v_novo_departamento || v_contador;
    
    INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME)
    VALUES (v_max_department_id + v_contador, v_novo_departamento);
    
    v_contador := v_contador + 1;
    
    EXIT WHEN v_contador > 4;
  END LOOP;
END;
/

–VERIFIQUE OS RESULTADOS

select * from departments;

EXEMPLO DE LOOP WHILE

DECLARE
  v_max_department_id  number;
  v_novo_departamento  departments.department_name%TYPE;
  v_contador           number := 1;
BEGIN
  SELECT MAX(DEPARTMENT_ID)
  INTO       v_max_department_id
  FROM      DEPARTMENTS;

  v_novo_departamento := '&NovoDepto';

  WHILE v_contador < 4 LOOP
    v_novo_departamento := v_novo_departamento || v_contador;
    INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME)
    VALUES (v_max_department_id + v_contador, v_novo_departamento);
    v_contador := v_contador + 1;
END LOOP;
END;
/

–VERIFIQUE OS RESULTADOS

select * from departments;

EXEMPLO DE LOOP FOR

DECLARE
  v_max_department_id   number;
  v_novo_departamento  departments.department_name%TYPE;
  v_contador            number := 1;
BEGIN
  SELECT MAX(DEPARTMENT_ID)
  INTO       v_max_department_id
  FROM      DEPARTMENTS;

  v_novo_departamento := '&NovoDepto';

  FOR I IN 1..3 LOOP
    v_novo_departamento := v_novo_departamento || v_contador;
    
    INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME)
    VALUES (v_max_department_id + v_contador, v_novo_departamento);

    v_contador := v_contador + 1;
  END LOOP;
END;
/

–VERIFIQUE OS RESULTADOS

select * from departments;

INSTRUÇÃO CONTINUE

  • É utilizado dentro de um dos tipos de loops;
  • Faz com que a condição do laço seja avaliada antes de terminar o bloco do loop;
  • Oferece aos programadores a habilidade de transferir o controle para a próxima interação;

EXEMPLO DE CONTINUE

SET SERVEROUTPUT ON;
DECLARE
  v_soma SIMPLE_INTEGER :=0;
BEGIN
  FOR i IN 1..10 LOOP
    v_soma := v_soma + 1;
    DBMS_OUTPUT.PUT_LINE('A soma é: ' || v_soma);
    CONTINUE WHEN i > 6;
    v_soma := v_soma +1;
    DBMS_OUTPUT.PUT_LINE('Sem entrar continue, soma é: ' || v_soma);
  END LOOP;
END;
/

DATATYPE

  • Datatypes são tipo de dados que possuem múltiplos valores, ou seja, pode-se armazenar vários dados em uma única variável, que ao contrário do tipos char, number, date, podem armazenar somente 1 por vez;
  • Podem ser do tipos RECORD e COLLECTION;

RECORDS e COLLECTIONS

  • RECORD é quando queremos armazenar um valor com diferentes tipos de dados em uma única ocorrência naquele momento;
  • COLLECTION é quando queremos armazenar alguns valores do mesmo tipo de dado (record);

Sem título5

  • RECORD contém um ou mais componentes, também conhecidos como campos de qualquer tipo escalar (number, char, date, etc);
  • São similares a estrutura da linguagem C;
  • Trata uma coleção de campos como uma unidade lógica;

ESTRUTURA DE UM RECORD

Sem título6

MANUPILAÇÃO DOS RECORDS

  • Notação de um campo individual:
    • CLI_RECORD.NOME;
  • Atribuindo um valor ao record:
    • CLI_RECORD.NOME := ‘JOAO DA SILVA’;

ATRIBUTO %ROWTYPE

  • Declara uma variável de acordo com uma coleção de colunas de uma tabela uma view;
  • Campos de um RECORD tem que ter mesmos nomes e tipos de colunas de uma tabela ou view;

EXEMPLO DE  %ROWTYPE

SET SERVER_OUTPUT ON;
DECLARE
  TYPE EMP_REC IS RECORD (
  v_sal       number(8),
  v_minsal    number(8) default 1000,
  v_hire_date employees.hire_date%TYPE,
  v_emp1      employees%ROWTYPE);
  v_meurec EMP_REC;
BEGIN
  v_meurec.v_sal := v_meurec.v_minsal + 500;
  v_meurec.v_hire_date := sysdate;

  SELECT  *
  INTO    v_meurec.v_emp1
  FROM    employees
  WHERE   EMPLOYEE_ID = 100;

  DBMS_OUTPUT.PUT_LINE(v_meurec.v_emp1.last_name || ' ' ||
  to_char(v_meurec.v_hire_date) || ' ' ||
  to_char(v_meurec.v_sal));
END;
/

CURSORES

  • Toda instrução SQL que é executada no Servidor Oracle é associado um cursor individual;
  • Cursor implícito é declarado e gerenciado pelo PL/SQL;
  • Cursor explícito é declarado e gerenciado pelo programador;

CURSORES EXPLICITOS

Sem título7

CONTROLANDO CURSORES EXPLÍCITOS

Sem título8

DECLARANDO CURSORES

  • Não incluir INTO na declaração do cursor, pois o carregamento dos valores será feita após o FETCH;
  • Caso queira que seja processado em uma determinada sequencia, utilize ORDER BY;
  • Um cursor pode suportar SELECTs com JOINS;
SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp_cursor is
    SELECT  employee_id, last_name
    FROM    employees
    WHERE   department_id = 30;
  v_empno employees.employee_id%TYPE;
  v_lname employees.last_name%TYPE;
BEGIN
  OPEN c_emp_cursor;
  FETCH c_emp_cursor INTO v_empno, v_lname;
  DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);
END;
/

CURSORES COM LOOP

SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp_cursor is
    SELECT  employee_id, last_name    
    FROM    employees WHERE   department_id = 30;
    v_empno employees.employee_id%TYPE;
    v_lname employees.last_name%TYPE;
BEGIN
  OPEN c_emp_cursor;
  
  LOOP
    FETCH c_emp_cursor INTO v_empno, v_lname;

    EXIT WHEN c_emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);
  END LOOP;

  CLOSE c_emp_cursor ;
END;
/

LOOP FOR COM CURSORES

SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp_cursor IS
    SELECT employee_id, last_name FROM employees
    WHERE department_id = 30;
BEGIN
  FOR emp_record IN c_emp_cursor
  LOOP
    DBMS_OUTPUT.PUT_LINE(emp_record.employee_id ||' '||   emp_record.last_name);
  END LOOP;
END;
/

STATUS DE CURSORES

Sem título9

UTILIZAÇÃO DO %ISOPEN

SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp_cursor is
    SELECT  employee_id, last_name
    FROM    employees
    WHERE   department_id = 30;
  v_empno employees.employee_id%TYPE;
  v_lname employees.last_name%TYPE;
BEGIN
  IF NOT c_emp_cursor%ISOPEN THEN
    OPEN c_emp_cursor;
  END IF;
  
  LOOP
    FETCH c_emp_cursor INTO v_empno, v_lname;
    EXIT WHEN c_emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);
  END LOOP;
END;
/

UTILIZAÇÃO DO %ROWCOUNT e %NOTFOUND

SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp_cursor is
    SELECT  employee_id, last_name FROM    employees
    WHERE   department_id = &cod_departamento;
  v_emp_record c_emp_cursor%ROWTYPE;
BEGIN
  OPEN c_emp_cursor;
  LOOP
    FETCH c_emp_cursor INTO v_emp_record;
    EXIT WHEN c_emp_cursor%ROWCOUNT > 10 OR
    
    c_emp_cursor%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id
    || ' ' || v_emp_record.last_name);
  END LOOP;
  CLOSE c_emp_cursor;
END;
/

FUNÇÕES

  • É um bloco PL/SQL nomeado que retorna um valor;
  • Pode ser armazenado no banco de dados como um objeto de schema para execuções repetidas;
  • É chamada como parte de uma expressão ou é usada para fornecer um valor de parâmetro para outro subprograma;
  • Pode ser agrupadas em pacotes do PL/SQL;
  • O bloco PL/SQL precisa ter ao menos uma declaração de retorno (RETURN);
  • É criado com o comando CREATE FUNCTION;
  • Apesar de parâmetros OUT e IN OUT serem modos que podem utilizar as funções, não é uma boa prática utilizá-las em funções;
  • No entanto, se você precisa retornar mais de um valor de uma função, considere retornar valores compostos de estrutura de dados como um registro ou tabela PL/SQL;

DIFERENÇAS ENTRE PROCEDURES E FUNÇÕES

PROCEDURES FUNCTIONS
Executa como declaração PL/SQL Chamado com uma parte de uma expressão
Não contém cláusula RETURN no cabeçalho Precisa conter um RETURN no cabeçalho
Pode passar valores (se precisar) usando parâmetros output Precisa retornar um valor simples
Pode conter a declaração RETURN sem um valor Precisa conter ao menos uma declaração RETURN

VISÃO GERAL DA CRIAÇÃO E EXECUÇÃO DE FUNÇÕES

Sem título10

CRIANDO E CHAMANDO UMA FUNÇÃO

–CRIANDO A FUNÇÃO

CREATE OR REPLACE FUNCTION get_sal
  (p_id IN employees.employee_id%TYPE)
RETURN NUMBER IS
  v_sal employees.salary%TYPE := 0;
BEGIN
  SELECT salary
  INTO   v_sal
  FROM   employees
  WHERE  employee_id = p_id;
  
  RETURN v_sal;
END get_sal;
/

–CHAMANDO A FUNÇÃO EM UMA EXPRESSAO
–COM UM VALOR DE PARAMETRO

SET SERVEROUTPUT ON;

EXECUTE dbms_output.put_line(get_sal(100));

DIFERENTES MÉTODOS PARA SE EXECUTAR UMA FUNÇÃO

–EXEMPLO 1

VARIABLE b_salary NUMBER

EXECUTE :b_salary := get_sal(100);

PRINT b_salary;

–EXEMPLO 2

SET SERVEROUTPUT ON

DECLARE
  sal employees.salary%TYPE;
BEGIN
  sal := get_sal(100);
  DBMS_OUTPUT.PUT_LINE('o SALÁRIO É: ' || sal);
END;
/

–EXEMPLO 3

EXECUTE dbms_output.put_line   (get_sal(100));

–EXEMPLO 4

SELECT  JOB_ID, GET_SAL(EMPLOYEE_ID)
FROM    EMPLOYEES;

TABELA DO DICIONÁRIO DE DADOS

  • Podemos utilizar a tabela USER_OBJECTS para identificar os tipos de objetos que o usuário corrente tem acesso;
SELECT   object_name,
         object_type
FROM     user_objects;

EXEMPLO DE FUNÇÃO COM COMPARAÇÕES

CREATE OR REPLACE FUNCTION check_sal
  RETURN Boolean IS
  v_dept_id   employees.department_id%TYPE;
  v_empno     employees.employee_id%TYPE;
  v_sal       employees.salary%TYPE;
  v_avg_sal   employees.salary%TYPE;
BEGIN
  v_empno := 205;
  SELECT  salary, department_id
  INTO    v_sal, v_dept_id,
  FROM    employees
  WHERE   employee_id = v_empno;

  SELECT  avg(salary)
  INTO    v_avg_sal
  FROM    employees
  WHERE   department_id = v_dept_id;

  IF v_sal > v_avg_sal THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;

MOSTRANDO POSSÍVEIS ERROS

–mostrando onde estão os possíveis erros

SHOW ERRORS FUNCTION check_sal

--SERÁ MOSTRADO POSSIVELMENTE

LINE/COL ERROR
-------- ----------------------------------
9/3      PL/SQL: SQL Statement ignored
11/3     PL/SQL: ORA-00936: missing expression

AJUSTE DA FUNÇÃO

–ajuste a seguinte parte da função

SELECT  salary,
        department_id
INTO    v_sal,
        v_dept_id

CHAMADA DA FUNÇÃO

–chamada da função

SET SERVEROUTPUT ON;
BEGIN
  IF (check_sal IS NULL) THEN
  DBMS_OUTPUT.PUT_LINE('A função retornou NULL devido a exceção');
  ELSIF (check_sal) THEN
    DBMS_OUTPUT.PUT_LINE('Salario > média');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Salario < média');
  END IF;
END;
/

PASSANDO PARAMETROS PARA UMA FUNÇÃO

CREATE OR REPLACE FUNCTION check_sal
  (p_empno employees.employee_id% TYPE)
RETURN Boolean IS
  v_dept_id   employees.department_id%TYPE;
  v_sal       employees.salary%TYPE;
  v_avg_sal   employees.salary%TYPE;
BEGIN
  SELECT  salary, department_id
  INTO    v_sal, v_dept_id
  FROM    employees
  WHERE   employee_id = p_empno;
  
  SELECT  avg(salary)
  INTO    v_avg_sal
  FROM    employees
  WHERE   department_id = v_dept_id;

  IF v_sal > v_avg_sal THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

CHAMADA DA FUNÇÃO COM PARAMETROS

SET SERVEROUTPUT ON;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Checando o empregado 205');
  
  IF (check_sal(205) IS NULL) THEN
    DBMS_OUTPUT.PUT_LINE('A função retornou NULL devido   a exceção');
  ELSIF (check_sal(205)) THEN
    DBMS_OUTPUT.PUT_LINE('Salario > média');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Salario < média');   END IF;   DBMS_OUTPUT.PUT_LINE('Checando o empregado 70');      IF (check_sal(70) IS NULL) THEN     DBMS_OUTPUT.PUT_LINE('A função retornou NULL devido     a exceção');   ELSIF (check_sal(70)) THEN     DBMS_OUTPUT.PUT_LINE('Salario > média');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Salario < média');
  END IF;
END;
/

Exercícios

  1. Crie uma função, que passado 2 parâmetros do tipo number, (o valor do primeiro parâmetro deve ser menor que o segundo) retorne o valor do maior salário encontrado. Os parâmetros passados são correspondentes aos códigos dos empregados. Essa função deve tratar erro, caso o primeiro parâmetro seja maior que o segundo parâmetro. No ambiente chamador, mostre o valor encontrado;
  1. Crie uma função, que passado um parâmetro do tipo number e outro do tipo data, que retorne a quantidade de tempo que o empregado está na empresa em anos que o valor deve ser arredondado. Os parâmetros passados são correspondentes ao códigos do empregados e a data de admissão. No ambiente chamador, deve-se chamar a função através de um comando select;
  1. Crie uma função, que passado um parâmetro do tipo number, retorne se o funcionário tem o job_id igual a “IT_PROG” ou “PU_CLERCK”. O parâmetro passado é correspondente ao código do funcionário. No ambiente chamador, deve ser impresso se o funcionário tem ou não tem o job_id igual a “IT_PROG” ou “PU_CLERCK”;
  1. Crie uma função, que passado um parâmetro do tipo number, retorne a quantidade de funcionário que existem em um determinado departamento. O parâmetro passado é correspondente ao departamento. No ambiente chamador, é necessário estar impresso o código do departamento e a quantidade de funcionários;

Vídeo – Parte 1

Vídeo – Parte 2

Vídeo – Parte 3

 

 

 

 

Banco de Dados II – Aula 11

INTRODUÇÃO AO PL/SQL

  • PL/SQL é uma linguagem de programação em blocos;
  • Uma unidade de programa pode ser Nomeada ou Não Nomeada;
  • Blocos Não Nomeados também são conhecidos como blocos anônimos;

Sintaxe geral:

[DECLARE]
declaration_statements
BEGIN
execution_statements
[EXCEPTION]
exception_handling_statements
END;

  • Em uma declaração de blocos, podem ser definidos tipos de dados, estruturas, variáveis;
  • Estruturas também podem ser localmente chamadas de funções, procedimentos e cursores;
  • BEGIN e END são palavras reservadas, com o uso opcional da palavra EXCEPTION
  • O mínimo código de um bloco anônimo é:
BEGIN
NULL;
END;
/
  • A exceção do bloco de manipulação permite você gerenciar exceções; 
  • Blocos nomeados têm uma ligeira diferença dos blocos não nomeados, porque os blocos nomeados ficam armazenados no banco de dados;
  • Blocos nomeados tem também uma sessão de declaração, chamado como header (cabeçalho);
  • O nome e a lista de parâmetros são chamados de uma assinatura de uma sub-rotina;
  • As mesmas regras são aplicadas para os corpos dos objetos (bodies); 

Exemplo de bloco nomeado:

PROCEDURE procedure_name
( parameter1 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type
, parameter2 [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type
, parameter(n+1) [IN][OUT] [NOCOPY] sql_data_type | plsql_data_type )
[ AUTHID {DEFINER | CURRENT_USER}]
declaration_statements
BEGIN
  execution_statements
[EXCEPTION]
  exception_handling_statements
END;
/

Habilitando OUTPUT em um bloco PL/SQL:

  • Escreva antes do bloco PL/SQL:
  SET SERVEROUTPUT ON;

Use o pacote (package) pré-definido DBMS_OUTPUT   
Exemplo do DBMS_OUTPUT  :

DBMS_OUTPUT.PUT_LINE ('O primeiro nome de funcionário é ' || v_fname);
... 
  • Escreva antes do bloco PL/SQL:
  SET SERVEROUTPUT ON;

Use o pacote (package) pré-definido DBMS_OUTPUT.PUT_LINE(‘string’) 

Exemplo:

SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE  ('IMPRESSAO DE LINHA  USANDO PUT_LINE');
END;
/  

CONFIGURAÇÃO DO SERVEROUTPUT

–CONFIGURANDO O AMBIENTE, QTD DE BUFFER SET SERVEROUTPUT ON SIZE 1000000;

DBMS_OUTPUT.ENABLE(1000000); 
DBMS_OUTPUT.DISABLE; 

–EXEMPLO

BEGIN DBMS_OUTPUT.PUT('LINHA '); 
  DBMS_OUTPUT.PUT('UM.'); 
  DBMS_OUTPUT.NEW_LINE; 
  DBMS_OUTPUT.PUT_LINE('LINHA DOIS.'); 
END; 
/

TIPO BOLEANO

  • O tipo boleado de dados por ter três possibilidade de valores: TRUE, FALSE e NULL;
  • Pode ser declarado como e exemplo a seguir;
SET SERVEROUTPUT ON;
DECLARE
  var1 BOOLEAN;
  var2 BOOLEAN NOT NULL := TRUE;
  var3 BOOLEAN NOT NULL := FALSE;
BEGIN
  DBMS_OUTPUT.PUT_LINE(CASE WHEN var1 THEN 'TRUE'
                         WHEN var1 IS NULL THEN 'NULL'
                         ELSE 'FALSE'
                       END );

  DBMS_OUTPUT.PUT_LINE(CASE WHEN var2 THEN 'TRUE'
                       WHEN var2 IS NULL THEN 'NULL'
                       ELSE 'FALSE'
                     END);

  DBMS_OUTPUT.PUT_LINE(CASE WHEN var3 THEN 'TRUE'
                       WHEN var3 IS NULL THEN 'NULL'
                       ELSE 'FALSE'
                     END);
END;
/

CARACTERES E STRINGS

  • O tamanho da string é declarado pelo número de bytes ou caracteres;
  • Qualquer tentativa de armazenamento maior que o tamanho suportado, pode ser tratado com exceção;
SET SERVEROUTPUT ON;
DECLARE
  c CHAR(32767) := ' ';
  v VARCHAR2(32767) := ‘ ';
BEGIN
  DBMS_OUTPUT.PUT_LINE(' c é ['|| LENGTH(c)||']');
  DBMS_OUTPUT.PUT_LINE(' v é ['|| LENGTH(v)||']');
  v := v || ' ';
  DBMS_OUTPUT.PUT_LINE(' v é ['|| LENGTH(v)||']');
END;
/

O TIPO CHAR E VARCHAR

  • O tipo CHAR é baseado em um tamanho fixo de strings. O padrão é o tamanho de 1 byte, podendo chegar até a 32.767;
  • Em colunas CHAR, o máximo permitido são 4000 bytes;
  • A Oracle recomenda utilizar CLOB ou LONG para colunas;
  • O tipo VARCHAR2 tem base em tamanhos variados;
  • VARCHAR2 pode chegar até 32.767 bytes de tamanho;

EXEMPLO DO TIPO CHAR

--tipo char
SET SERVEROUTPUT ON;
DECLARE
  var1 CHAR;
  var2 CHAR(10);
  var3 CHAR(10 BYTE);
  var4 CHAR(10 CHAR);
BEGIN
  var1 := 'a'; var2 := 'b'; var3 := 'c'; var4 := 'd';
  DBMS_OUTPUT.PUT_LINE(' var1 é ['|| LENGTH(var1)||']');
  DBMS_OUTPUT.PUT_LINE(' var2 é ['|| LENGTH(var2)||']');
  DBMS_OUTPUT.PUT_LINE(' var3 é ['|| LENGTH(var3)||']');
  DBMS_OUTPUT.PUT_LINE(' var4 é ['|| LENGTH(var4)||']');
END;
/

EXEMPLO DO TIPO VARCHAR2
--tipo varchar2
SET SERVEROUTPUT ON;
DECLARE
  var1 VARCHAR2; --erro
  var2 VARCHAR2(10);
  var3 VARCHAR2(10 BYTE);
  var4 VARCHAR2(10 CHAR);
BEGIN
  var1 := 'A'; var2 := 'AB'; var3 := 'ABC'; var4 := 'ABCD';
  DBMS_OUTPUT.PUT_LINE(' var1 é ['|| LENGTH(var1)||']');
  DBMS_OUTPUT.PUT_LINE(' var2 é ['|| LENGTH(var2)||']');
  DBMS_OUTPUT.PUT_LINE(' var3 é ['|| LENGTH(var3)||']');
  DBMS_OUTPUT.PUT_LINE(' var4 é ['|| LENGTH(var4)||']');
END;
/

OS TIPOS DATE, TIME, INTERVAL

Sem título

EXEMPLO DO DATE

SET SERVEROUTPUT ON;
DECLARE
  var1 DATE;
  var2 DATE := SYSDATE;
  var3 DATE := SYSDATE +1;
  var4 DATE := '29-FEB-08';
BEGIN
  DBMS_OUTPUT.PUT_LINE(' var1 é ['|| var1 ||']');
  DBMS_OUTPUT.PUT_LINE(' var2 é ['|| var2 ||']');
  DBMS_OUTPUT.PUT_LINE(' var3 é ['|| var3 ||']');
  DBMS_OUTPUT.PUT_LINE(' var4 é ['|| var4 ||']');
END;
/

O TIPO TIMESTAMP

  • É um subtipo extendido de DATE, que oferece mais precisão no tempo;
  • SYSTIMESTAMP oferece o tempo mais preciso dependendo da plataforma;

EXEMPLO DO TIMESTAMP

SET SERVEROUTPUT ON;
DECLARE
  d DATE := SYSTIMESTAMP;
  t TIMESTAMP(3) := SYSTIMESTAMP;
BEGIN
  DBMS_OUTPUT.PUT_LINE('DATE      ['||d||']');
  DBMS_OUTPUT.PUT_LINE('TO_CHAR['||TO_CHAR(d,'DD-MON-YY HH24:MI:SS') ||']');
  DBMS_OUTPUT.PUT_LINE('TIMESTAMP ['||t||']');
END;
/

CARACTERES UNICODES

  • Caracteres unicodes servem para suportar globalização;
  • Globalização é a utilização de caracteres que suportam múltiplos conjuntos de caracteres;
  • A codificação AL16UTF16 ou UTF8 são oferecidas pelo Oracle;
  • AL16UTF16 utiliza 2 bytes por caracter;
  • UTF8 utiliza 3 bytes por caracter;
  • NCHAR é o tipo de dado UNICODE;
  • Pode armazenar até o tamanho de 16.383 (32.767 / 2);
  • NVARCHAR2 é o equivalente ao VARCHAR2;

Sem título2Ref: https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch6unicode.htm

O TIPO NUMBER

  • Há 4 principais tipos de números no Oracle: BINARY_INTEGER, IEEE 754 (BINARY_DOUBLE e BINARY_FLOAT), O NUMBER e PLS_INTEGER;
  • BINARY_INTEGER e PLS_INTEGER são idênticos, suportando a -2.147.483.648 a 2.147.483.647;

ATRIBUINDO VALORES À VARIÁVEIS DINAMICAMENTE

  • No ambiente SQL Developer, podemos fazer a atribuição de variáveis através do símbolo “&” (e comercial sem aspas);
--EXEMPLO DE ATRIBUIÇÃO DE VARIÁVEIS
SET SERVEROUTPUT ON;
DECLARE
  var1  number;   var2  number;   soma  number;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Digite valor 1:');
  var1 := &Valor1;
  
  DBMS_OUTPUT.PUT_LINE('Digite valor 2:');
  var2 := &Valor2;
  
  DBMS_OUTPUT.PUT_LINE('Valor 1:' || var1);
  
  DBMS_OUTPUT.PUT_LINE('Valor 2:' || var2);
  
  soma := var1 + var2;
  
  DBMS_OUTPUT.PUT_LINE('Soma:' || soma);
END;
/

EXEMPLO DE DECLARAÇÃO DE TIPOS DE VARIÁVEIS
SET serveroutput ON;
DECLARE
  nIdFuncionario binary_integer;
  nSalario NUMBER(10, 2);  nAcrescimo FLOAT;
BEGIN
  nidfuncionario := 25;
  nSalario       := 1500;
  nAcrescimo     := 0.25;
  dbms_output.put_line('O novo salário do funcionário: ' || nSalario * (1 + nAcrescimo));
END;

--VARIAVEIS NUMBER, FLOAT, BINARY_INTEGER

SET SERVEROUTPUT ON;
DECLARE
  v_FUNCIONARIO_ID  BINARY_INTEGER;
  v_SALARIO NUMBER(10,2); v_ACRESCIMO  FLOAT;
BEGIN
  v_FUNCIONARIO_ID := 10; v_SALARIO := 2000;
  v_ACRESCIMO := 0.10;
 
DBMS_OUTPUT.PUT_LINE('O novo salario do funcionario: '
  || v_FUNCIONARIO_ID || ' terá acrescimo de '
  || v_SALARIO * v_ACRESCIMO);
END;

--VARIAVEIS BOOLEAN
SET SERVEROUTPUT ON;
DECLARE
  v_FUNCIONARIO_ID  BINARY_INTEGER;
  v_SALARIO         NUMBER(10,2);
  v_SAL_EM_REAL     BOOLEAN;
BEGIN
  v_FUNCIONARIO_ID := 10;
  v_SALARIO := 2000;
  v_SAL_EM_REAL := TRUE;
  IF v_SAL_EM_REAL THEN
    DBMS_OUTPUT.PUT_LINE('O salario do funcionario: '
      || v_FUNCIONARIO_ID || ' é de  '
      || v_SALARIO || ' em reais');
  ELSE
    DBMS_OUTPUT.PUT_LINE('O salario do funcionario: '
      || v_FUNCIONARIO_ID || ' é de  '
      || v_SALARIO || ' em dolares');
  END IF;
END;
/

CONSTANTES

  • É similar a uma variável, porém valores não podem ser atribuídos durante o bloco de código;
  • A palavra CONSTANT reserva é colocada depois do nome da variável e antes do tipo da variável;
  • Pode ser definida por qualquer tipo de dados disponível para variáveis;
  • Para utiliza-la, tenha certeza de que o valor não será alterado;
EXEMPLO DE CONSTANTES
--irá gerar erro
SET SERVEROUTPUT ON;
DECLARE
  v_SALARIO CONSTANT number(4);
BEGIN
  v_SALARIO := 1000;
  dbms_output.put_line (v_SALARIO);
END;
/

 
--irá funcionar
SET SERVEROUTPUT ON;
DECLARE
  v_SALARIO CONSTANT number(4) := 1000;
BEGIN
  dbms_output.put_line (v_SALARIO);
END;
/

VARIÁVEIS NOT NULL

  • É possível declarar em PL/SQL variáveis NOT NULL;
  • No decorrer do código, esta variável deverá ter algum valor, não podendo ser nula;
  • É declarado na seção DECLARE, sendo que a variável precisa ter um valor associado;

EXEMPLO DE VARIÁVEIS NOT NULL

--erro
SET SERVEROUTPUT ON;
DECLARE
  v_FUNCIONARIO_ID BINARY_INTEGER NOT NULL := 10;
  v_SALARIO        NUMBER(10,2) := 1000;
BEGIN
  v_FUNCIONARIO_ID := NULL;
  DBMS_OUTPUT.PUT_LINE('Funcionario: ' || v_FUNCIONARIO_ID);
  v_SALARIO := NULL;
  DBMS_OUTPUT.PUT_LINE('Salario : ' || v_SALARIO);
END;
/ 

--correto
SET SERVEROUTPUT ON;
DECLARE
  v_FUNCIONARIO_ID BINARY_INTEGER NOT NULL := 10;
  v_SALARIO        NUMBER(10,2) := 1000;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Funcionario: ' || v_FUNCIONARIO_ID);
  v_SALARIO := NULL;
  DBMS_OUTPUT.PUT_LINE('Salario : ' || v_SALARIO);
END;
/

EXEMPLO DE IF..THEN.. ELSE

-- IF THEN ELSE
SET SERVEROUTPUT ON;
DECLARE
  v_SALARIO         NUMBER(10,2);
BEGIN
  v_SALARIO := &Salario;
  IF v_SALARIO BETWEEN 0 AND 1000 THEN
    DBMS_OUTPUT.PUT_LINE('Salario baixo:  ' || v_SALARIO);
  ELSIF v_SALARIO BETWEEN 1001 AND 2000 THEN
    DBMS_OUTPUT.PUT_LINE('Salario médio:  ' || v_SALARIO);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Salario alto:  ' || v_SALARIO);
  END IF;
END;
/

Exercícios

  1. Crie uma bloco anônimo que leia três variáveis do tipo number e aponte mostre os números digitados e o maior número dentre os três;
  1. Crie um bloco anônimo que leia 2 variáveis do tipo number, mostre os dois números digitados e mostre o resultado da soma, multiplicação, subtração e divisão desses dois números;
  1. Crie um bloco anônimo que leia 2 variáveis do tipo number, e leia qual operação o usuário deseja fazer, escolhendo entre os símbolos (+, -, /, *). O símbolo escolhido deve ser apresentado o resultado dentre os dois números;
  1. Crie um bloco anônimo que leia 3 frases de até 100 caracteres, mostre as frases digitadas, mostre quantos caracteres cada frase possui, e mostre a soma da quantidade de caracteres total das tres frases;
  1. Crie um bloco anônimo que leia 2 datas, mostrando a quantidade de dias, meses e anos dentre essas datas. (Pode fazer arredondamento caso necessário). Observação, a Data Inicial precisa ser menor que a data final;
  1. Crie um bloco nomeado que tenha 3 parametros de entrada do tipo number e que mostre o menor número dos 3 argumentos passados;
  1. Crie um bloco nomeado que tenha 2 parametros de entrada do tipo nvarchar, e mostre quantos bytes cada parâmetro está ocupando;
  1. Crie um bloco nomeado que tenha 2 parametros de entrada, uma do tipo number e outra do tipo date, e que mostre a data mais o parâmetro do tipo number e a data menos o parâmetro do tipo number;

PROCEDURES EM PL/SQL

  • São tipos de sub-programas que executam uma ação;
  • Pode ser armazenado em um banco como um objeto de schema;
  • Provê reusabilidade e fácil manutenção;
  • Pode aceitar parâmetros;
  • A procedure é compilada e armazenada no banco de dados como um schema de objeto;
  • Um bloco de comandos PL/SQL pode aceitar variáveis de substituição;
SET SERVEROUTPUT ON
DECLARE
  my_var VARCHAR2(30);
BEGIN
  my_var := '&input';
  dbms_output.put_line('Hello '|| my_var );
END;
/
  • O operador de atribuição é “:=” (sem aspas);
  • Strings são delimitadas por aspas simples;
  • Declara-se variáveis na seção DECLARE;
  • Os tipos de variáveis utilizadas são as mesmos tipos de variáveis utilizados nas tabelas do Oracle;
SET SERVEROUTPUT ON
DECLARE
  my_var VARCHAR2(10);
BEGIN
  my_var := '&input';
  dbms_output.put_line('Hello '|| my_var );
EXCEPTION
  WHEN others THEN
    dbms_output.put_line(SQLERRM);
END;
/
  • Em PL/SQL, é possível fazer tratamentos de erros com a cláusula EXCEPTION;
  • A função SQLERRM irá capturar o erro, e é possível mostrar os erros através do comando de impressão;
  • SQLERRM retorna a mensagem de erro associado ao número do erro;
  • Quando estamos codificando em linguagens de programação, é normal que tenhamos que seguir algumas regras de convenção de nomes;
  • Elas servem para que se tenha padronização em na codificação dos programas;
  • Não há uma regra clara sobre o uso de nomeações padrão. Caso não tenha, é importante adotar uma;

EXEMPLO DE CONVENSÃO DE NOMES EM PL/SQL

Sem título4

PARAMETROS E SEUS MODOS

  • São declaradas depois dos subprogramas no header da procedure;
  • Passa dados entre o ambiente de chamada e o subprograma;
  • São usados como variáveis locais, mas são dependentes do modo em que são tratados
  • IN é o modo padrão que passa os valores do ambiente de chamada para o subprograma;
  • OUT é o modo de retorno do valor para o ambiente chamador;
  • IN OUT é o modo que passa valores do ambiente de chamada, a qual pode retornar o valor modificado (o mesmo parâmetro);

Sem título5

PARAMETRO FORMAL

  • Variáveis locais declaradas na lista de parâmetros da especificação do subprograma;

PARAMETRO ATUAL

  • Ou também conhecidos como argumentos: Valores literais, variáveis e expressões são usadas na lista de parâmetros que chamam o subprograma (procedure)

MODOS DOS PARAMETROS

  • O tipo dos parâmetros pode ser especificado sem o tamanho do seu tipo;
  • Podem ser utilizados:
  • O tipo explicito do dado;
  • Usando a definição %TYPE;
  • Usando a definição %ROWTYPE;
  • Um ou mais parâmetros formais podem ser declarados, cada um separados por virgula;

COMPARAÇÃO DOS MODOS DOS PARAMETROS

  • IN é o padrão se não há especificação na declaração;
  • OUT e IN OUT precisam ser explicitamente especificados na declaração dos parâmetros;
  • No parâmetro formal IN não pode ser atribuído um valor e não pode ser modificado no corpo da procedure. Por padrão, o parâmetro IN é passado por referencia
  • Um parâmetro IN pode ser atribuído por um valor padrão na declaração do parâmetro formal, em caso do ambiente chamador não fornecer um valor para o parâmetro se o valor padrão é aplicado;
  • OUT ou IN OUT precisam ser atribuídos um valor antes do retorno do ambiente chamador.
  • OUT e IN OUT não podem ser atribuídos valores padrão;

COMPARAÇÃO DOS MODOS DOS PARAMETROS

Não pode ser atribuído um valor padrão

IN OUT IN OUT
Modo padrão Precisa ser especificado Precisa ser especificado
Valor é passado para o subprograma Valor é retornado para o ambiente chamador Valor é passado para o subprograma; valor é retornado para o ambiente chamador
Parâmetros formais e atuais como uma constante Variáveis não inicializáveis Variáveis inicializáveis
Parâmetro atual pode ser literal, expressão, constantes, ou variáveis inicializáveis Precisa ser uma variável Precisa ser uma variável
Pode ser atribuído um valor padrão Não pode ser atribuído um valor padrão

EXEMPLO DE USO DO PARÂMETRO IN

--EXEMPLO DO PARAMETRO IN
CREATE OR REPLACE PROCEDURE previsao_salario (p_id   in employees.employee_id%TYPE, p_percent in NUMBER)
IS
BEGIN
  UPDATE  EMPLOYEES
  SET     SALARY = SALARY * (1 + p_percent /100)
  WHERE  EMPLOYEE_ID = p_id;
END previsao_salario;
/
 
--CONFERINDO O VALOR ANTERIOR
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 176;

--ESTA É A CHAMADA
EXECUTE previsao_salario(176,10);

--CONVERINDO O VALOR POSTERIOR
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 176;

--ABANDOMANDO ALTERAÇÕES
ROLLBACK;

EXEMPLO DE USO DO PARÂMETRO OUT 
--EXEMPLO DO PARAMETRO OUT
CREATE OR REPLACE PROCEDURE QUERY_EMP (p_id     IN  employees.employee_id%TYPE,
                                       p_name   OUT employees.last_name%TYPE,
                                       p_salary OUT employees.salary%TYPE ) IS
BEGIN
  SELECT  last_name, salary
  INTO    p_name,    p_salary
  FROM    employees
  WHERE   employee_id = p_id;
END QUERY_EMP;
/

--CHAMADA DA PROCEDURE EXEMPLO OUT

SET SERVEROUTPUT ON
DECLARE
  v_emp_name employees.last_name%TYPE;
  v_emp_sal  employees.salary%TYPE;
BEGIN
  QUERY_EMP(171,v_emp_name, v_emp_sal);
  DBMS_OUTPUT.PUT_LINE(v_emp_name || ' ganha ' ||
    to_char(v_emp_sal, '$999,999.00'));
END;
/

PARÂMETRO IN OUT

  • O parametro IN OUT pode ser passado um valor para a procedure que pode ser atualizado, ou seja, o valor do mesmo parametro pode ser atualizado durante o processamento nas linhas da procedure;

EXEMPLO DE PARÂMETRO IN OUT

CREATE OR REPLACE PROCEDURE format_phone
(p_phone_no IN OUT VARCHAR2) IS
BEGIN
p_phone_no := '('   || SUBSTR(p_phone_no,1,3) ||
')  ' || SUBSTR(p_phone_no,4,3) ||
'-'   || SUBSTR(p_phone_no,7);
END format_phone;
/

–CHAMADA DA PROCEDURE IN OUT

VARIABLE b_phone_no VARCHAR2(15)
EXECUTE :b_phone_no := '8006330575'
PRINT b_phone_no
EXECUTE format_phone(:b_phone_no)
PRINT b_phone_no

PASSAGEM DE PARAMETROS

  • Quando chamamos um subprograma, você pode escrever os parâmetros atuais usando as seguintes notações:
    • Posicional: lista dos parâmetros atuais na mesma ordem do que os parâmetros formais;
    • Nomeado: Lista dos parâmetros atuais em ordem arbitrária, usando o operador => para associar a nomeação do parâmetro formal com o parâmetro atual;
    • Misto: Lista de alguns parâmetros atuais como posicionais e alguns como nomeados;
  • Antes do Oracle Database 11g, somente a notação posicional era suportada nas chamadas das SQL´s;
  • A partir da versa 11g, notações nomeadas e mistos podem ser usadas para especificar argumentos na chamada para sub-rotinas de declarações do PL/SQL;

–PASSAGEM DE PARAMETRO ATUAIS

CREATE OR REPLACE PROCEDURE add_dept(p_name in  departments.department_name%TYPE,
                                     p_loc  in  departments.location_id%TYPE) IS
BEGIN
  INSERT INTO departments(department_id,
                          department_name,
                          location_id)
  VALUES (departments_seq.NEXTVAL, p_name, p_loc);
END add_dept;


--PASSAGEM DE NOTAÇÃO POSICIONAL
EXECUTE add_dept('TRAINING',2500);

--PASSAGEM DE NOTAÇÃO NOMEADA
EXECUTE add_dept(p_loc=>2400, p_name=>'EDUCATION');

USANDO O PARAMETRO OPCIONAL DEFAULT

  • Define um valor padrão para o parâmetro;
  • Provê flexibilidade por combinação posicional e nomeada na sintaxe da passagem de parâmetros;

ALTERANDO A PROCEDURE add_dept

–PARAMETRO DEFAULT

CREATE OR REPLACE PROCEDURE add_dept
(p_name departments.department_name%TYPE :='Desconhecido',
p_loc  departments.location_id%TYPE DEFAULT 1700 )
IS
BEGIN
INSERT INTO departments  (department_id,
department_name,
location_id)
VALUES (departments_seq.NEXTVAL, p_name, p_loc);
END add_dept;
/

CHAMADA DA PROCEDURE add_dept

--chamando a procedure
EXECUTE add_dept;

EXECUTE add_dept('PROPAGANDA',p_loc=>1200);

EXECUTE add_dept(p_loc=>1200);

--conferindo os dados inseridos
select * from departments;
  • Você pode chamar procedures usando blocos anônimos;
  • Voce precisa ser dono da procedure ou ter privilégio de EXECUTE;

Exercícios

  1. Crie uma bloco nomeado (procedure), que passado 2 parâmetros do tipo number, (o valor do primeiro parâmetro deve ser menor que o segundo) retorne todos os funcionários código, nome concatenado com o sobrenome, salário e departamento que o salário esteja entre esses dois parametros
  2. Crie um bloco nomeado (procedure), que passado um parâmetro do tipo data, retorne o código, sobrenome, nome, data de admissão e departamento de funcionários, onde a data de admissão seja maior ou igual ao parâmetro passado. O parâmetro deve ser uma data válida;
  3. Crie um bloco nomeado (procedure), que passado um parâmetro do tipo string, retorne o código, nome concatenado com o sobrenome dos funcionários, onde a letra do parâmetro deve ser substituída pela string “STAR”; Exemplo: o usuário passa a letra A, todos as letras do nome e sobrenome QUE CONTENHAM A deve ser substituída por STAR.
  4. Crie um bloco nomeado (procedure), que passado um parâmetro do string, retorne o código e nome concatenado com o sobrenome dos funcionários, onde o nome deve ser iniciado com o parâmetro digitado. Deve-se mostrar quantos caracteres tem o nome e separar cada sílaba com o caracter “-“;
  5. Crie um bloco nomeado que não tenha passagem de parâmetros, que faça a inclusão automática de um departamento cada vez que for acionada; Observação: o nome do departamento pode ser concatenado com outro dado para não se repetir (mesmo que o nome não tenha tanto sentido);
  6. Crie um bloco nomeado que passe um parâmetro do tipo inteiro e outro do tipo string, e que esses parâmetros tenham os valores padrão 100 e ‘IP_PROG’, retornando os registros que tenham o employee_id e job_id iguais aos dos parâmetros passados. Caso o usuário passe valor aos parâmetros, é necessário respeitar o valor passado para se ter maior prioridade. Caso não sejam passados, os parâmetros padrões serão utilizados. Arrume a procedure para que pelo menos se tenha um registro retornado caso não seja passado nenhum parâmetro;

 

Referência:
ORACLE DATABASE 11g PL/SQL Programming
Develop Robust, Database-Driven
PL/SQL Application
Michael McLaughlin – Mc Graw Hill

Vídeo 1

Vídeo 2

 

Banco de Dados II – Aula 10

VIEWS

  • É um objeto lógico que se baseia em uma SELECT, armazenado no banco de dados;
  • Pode ser simples (apenas utilizando uma tabela) ou complexa (podendo ter vários relacionamentos e tipos de joins, grupos, funções de grupo, etc);
  • Podem ter colunas virtuais;
  • Pode fazer restrição de dados (não colocando a coluna desejada na SELECT)
  • Em Views simples, podemos executar comandos DML (insert, update, delete);
  • Em Views complexas, na maioria dos casos, não é possível executar comandos DML (possuem funções, grupos);
  • Recomendado fazer toda a SELECT primeiramente para depois ser encapsulada na VIEW;
  • Sintaxe: CREATE [OR REPLACE] VIEW nome_da_view AS SELECT …
  • Quando utilizado o REPLACE, caso já tenha uma VIEW com o mesmo nome, a mesma irá ser sobrescrita;
  • Com o parâmetro FORCE, pode-se criar a VIEW sem ter necessariamente as tabelas físicas no banco de dados. (na execução, irá dar erro);
  • Com o parâmetro NOFORCE (padrão), pode-se criar a VIEW com tabelas existentes no banco de dados;
  • Com o parâmetro WITH CHECK OPTION, apenas as linhas retornadas na VIEW podem ser alteradas;
  • Com o parâmetro WITH READ ONLY, somente pode ser executada a VIEW sem a possibilidade de executar comandos DML;

EXEMPLOS:

CREATE OR REPLACE VIEW VW_SALARIO_PROG AS
SELECT  EMPLOYEE_ID,
        FIRST_NAME,
        LAST_NAME,
        SALARY * 13 AS SAL_CLT_ANUAL
FROM    EMPLOYEES
WHERE   JOB_ID = 'IT_PROG';

–MOSTRANDO A ESTRUTURA

DESC VW_SALARIO_PROG;

–EXECUTANDO A VIEW

SELECT *
FROM VW_SALARIO_PROG;

EXEMPLO 2

CREATE OR REPLACE VIEW VW_SALARIO_PROG (CODIGO,NOME,SOBRENOME,SALARIO_ANUAL) AS
SELECT  EMPLOYEE_ID,
        FIRST_NAME,
        LAST_NAME,
        SALARY * 13 AS SAL_CLT_ANUAL
FROM    EMPLOYEES
WHERE   JOB_ID = 'IT_PROG';

VIEWS COMPLEXAS

  • As VIEWS complexas geralmente possuem relacionamento com duas ou mais tabelas (Compostas de queries complexas);
  • Na maioria das vezes, não é possível executar instruções DML;
  • Quando a select possui funções de grupo, GROUP BY, DISTINCT, ROWNUM, Expressões (colunas virtuais), colunas NOT NULL não retornadas na VIEW, NÃO podemos executar comandos DML;
  • O parâmetro WITH CHECK OPTION é possível executar instruções INSERT e UPDATE, somente com as linhas que a view pode retornar;
  • Também serve para evitar que os dados seja deletados da tabela, sendo somente deletados pela VIEW que possui o WITH CHECK OPTION;
  • O parâmetro WITH READ ONLY cria uma VIEW que não será possível executar nenhuma instrução DML, sendo somente executada para leitura de dados;
  • DROP VIEW faz a eliminação da view do banco de dados;

EXEMPLO:

CREATE VIEW SALARIOS_FUNC(FUNCAO, MIN_SALARIO, MAX_SALARIO, MED_SALARIO)
AS
SELECT B.JOB_TITLE,
       MIN(A.SALARY),
       MAX(A.SALARY),
       AVG(A.SALARY)
FROM   EMPLOYEES A
       NATURAL JOIN JOBS B
GROUP BY B.JOB_TITLE;

 
SELECT *
FROM SALARIOS_FUNC;

SELECT * FROM DEPARTMENTS;

CREATE OR REPLACE VIEW VW_DEPTO_60
AS
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60
WITH CHECK OPTION CONSTRAINT CK_DEPTO_60;


SELECT *
FROM VW_DEPTO_60;


--IRA ATUALIZAR
UPDATE VW_DEPTO_60
SET SALARY = SALARY + 1
WHERE DEPARTMENT_ID = 60;

SELECT * FROM VW_DEPTO_60;

 

--NAO IRÁ ATUALIZAR
UPDATE VW_DEPTO_60
SET DEPARTMENT_ID = 20
WHERE EMPLOYEE_ID = 103;

 

CREATE OR REPLACE VIEW VW_DEPTO_20 (ID, NOME, CARGO)
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       JOB_ID
FROM   EMPLOYEES
WHERE  DEPARTMENT_ID = 20
WITH READ ONLY;


SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20;


DELETE FROM VW_DEPTO_20
WHERE ID = 202;

ÍNDICES

  • Índice serve para que dados sejam organizados para melhorar o processo de procura em tabelas;
  • É um dos objetos do banco de dados, que pode ser criado pelo usuário ou pelo Oracle;
  • Para campos do tipo Primary Key ou Unique, as chaves são criadas automaticamente pelo Oracle;
  • Os índices ocupam mais espaço no banco de dados, sendo marcadores que permitem que os dados sejam acessados rapidamente;
  • Pode-se criar ou eliminar índices em uma tabela a qualquer momento;
  • Quando uma tabela é excluída, todos os índices dessa tabela também é excluída;
  • Os índices exclusivos são criados exclusivamente pelo Oracle, quando definimos as colunas PRIMARY KEY ou UNIQUE. Podem ser criados manualmente, mas é recomendado que o Oracle faça esse gerenciamento;
  • Índices NÃO EXCLUSIVOS são criados pelos usuários, a qual tem a finalidade de aumentar a performance das consultas;
  • Os índices exclusivos são criados exclusivamente pelo Oracle, quando definimos as colunas PRIMARY KEY ou UNIQUE. Podem ser criados manualmente, mas é recomendado que o Oracle faça esse gerenciamento;
  • Índices NÃO EXCLUSIVOS são criados pelos usuários, a qual tem a finalidade de aumentar a performance das consultas;
  • Um índice pode ter mais de uma coluna;
  • A ordem das colunas influenciam na performance do retorno das linhas;
  • A utilização correta dos índices influencia também na performance de retorno das linhas (duas ou mais colunas);

SINTAXE:

CREATE INDEX NOME_INDICE ON NOME_TABELA(COLUNA1, ...);

DROP INDEX NOME_INDICE;
  • Aconselhável criar um índice quando:
    • Quando NÃO existirem muitos valores nulos na coluna consultada;
    • Quando as colunas forem muito utilizadas na cláusula WHERE;
    • Quando o retorno de registros representa em torno de 2% a 4% (para tabelas com grande quantidade de registros);
    • Colunas que possuem faixas de valores, como datas (que são utilizadas no WHERE);
  • Aconselhável a NÃO criar um índice quando:
    • Quando a tabela sofre comandos DML frequentemente (por exemplo, quando registros são inseridos, todos os índices da tabela também precisarão ser atualizados);
    • Tabelas com poucos registros;
    • Quando o retorno de registros na maioria das consultas equivalerem a menos de 4% dos registros;
  • Sempre que criar índices, é necessário avaliar se haverá mais consultas ou mais inserções/atualizações na tabela;

Exemplo:

CREATE INDEX IDX_PRIMEIRONOME ON EMPLOYEES(FIRST_NAME);
SELECT *
FROM EMPLOYEES
WHERE FIRST_NAME = 'Valli';

DROP INDEX IDX_PRIMEIRONOME;

CREATE INDEX IDX_PRIMEIROSEGUNDONOME ON EMPLOYEES (FIRST_NAME,LAST_NAME);

SELECT *
FROM EMPLOYEES
WHERE FIRST_NAME = 'Bruce';


SELECT *
FROM EMPLOYEES
WHERE FIRST_NAME = 'Bruce'
AND LAST_NAME = 'Ernst';
 

DROP INDEX IDX_PRIMEIROSEGUNDONOME;

SEQUENCES

  • Sequence é um dos objetos utilizados para armazenamento de números sequenciais em uma determinada ordem definida na sua criação;
  • Geralmente, é utilizado na criação de PRIMARY KEYS, em número sequenciais;
  • A utilização desse recurso garante que as chaves primárias não se repetirão;
  • É um objeto independente de qualquer tabela;
  • Geralmente, é associado a uma tabela para seguir uma sequencia, porém nada impede de ser utilizado em mais de uma tabela;
  • A sintaxe de criação da SEQUENCE é:
CREATE SEQUENCE NOME_SEQUENCE INCREMENT BY QUANTIDADE

[MAXVALUE VALOR_MAXIMO]
[MINVALUE VALOR_MINIMO]
[CYCLE]
[CACHE QUANTIDADE];
  • O incremento padrão é de 1 em 1;
  • O parâmetro CYCLE é utilizado quando a numeração chega ao valor máximo (maxvalue) e o número é reiniciado novamente;
  • O padrão na criação da SEQUENCE é NOCYCLE;
  • O parâmetro CACHE já define uma determinada quantidade de números reservados, para que seja mais rápido a utilização das sequencias (mais performance). O padrão é quantidade de 20;
  • Quando utilizamos SEQUENCES, podemos utilizar as pseudocolunas NEXVAL, que retorna o próximo número disponível da sequencia; Mesmo que tenha várias sessões diferentes utilizando a mesma sequence (ainda em transação), o valor nunca se repete;
  • Quando utilizamos SEQUENCES, podemos utilizar as pseudocolunas CURRVAL, que retorna o valor atual da sequence daquela seção; Só irá funcionar depois de ter utilizado o NEXTVAL na mesma sessão;
  • O Oracle não sabe qual é o valor corrente da sessão antes de ter executado o NEXTVAL;
  • Para se utilizar o NEXTVAL e CURRVAL:
    • Pode ser utilizada em uma SELECT, a menos que ela seja uma subquery;
    • Pode ser utilizada no comando VALUES do INSERT;
    • Pode ser utilizada no comando SET do UPDATE;
    • No SELECT de uma subquery para realizar um INSERT;
  • NÃO se pode utilizar o NEXTVAL e CURRVAL:
    • Em um SELECT em uma VIEW;
    • Em um SELECT, com DISTINCT, ORDER BY, GROUP BY ou HAVING;
    • Em uma coluna definida com o valor DEFAULT;
    • Em uma subquery em um DELETE, UPDATE ou em um SELECT;
  • Quando utilizamos o parâmetro CACHE QUANTIDADE, o Oracle coloca em cache essa quantidade para que se tenha mais performance na utilização da sequence. O padrão é com a quantidade de 20;
  • É indicado quando se tem tabelas que sofram grande quantidade de inserções de dados;
  • Depois que a sequence é gerada, o numero não é reaproveitado.
  • As sequences são geradas independentes que a transação termine com COMMIT ou ROLLBACK;
  • Falhas no sistema do Oracle também podem perder os valores que estão no cache;
  • Quando utilizamos uma sequence para várias tabelas, os números não irão ser sequencias para as tabelas utilizadas;
  • Podemos utilizar o comando ALTER e DROP para se fazer alteração e exclusão da sequence respectivamente;
  • Quando alteramos uma sequence, apenas os próximos números são alterados;
  • Não é possível alterar o START WITH de uma sequence, porém pode-se dar drop e create novamente;
  • Apenas quem tem permissão de DROP ANY SEQUENCE e o dono podem alterar as sequences;

EXEMPLOS:

CREATE TABLE TESTE_SEQUENCIA (
  CODIGO NUMBER(10) NOT NULL PRIMARY KEY,
  NOME   VARCHAR(30)
);

DESC TESTE_SEQUENCIA;

SELECT * FROM TESTE_SEQUENCIA;

CREATE SEQUENCE SQ_TESTE_01
INCREMENT BY 10
START WITH 100
MAXVALUE 300
NOCACHE
NOCYCLE;

–UTILIZANDO O NEXVAL

INSERT INTO TESTE_SEQUENCIA (CODIGO, NOME)
VALUES (SQ_TESTE_01.NEXTVAL, 'SAITO');

--VERIFICANDO O VALOR INSERIDO NA TABELA
SELECT SQ_TESTE_01.CURRVAL FROM DUAL;
SELECT * FROM TESTE_SEQUENCIA;

INSERT INTO TESTE_SEQUENCIA (CODIGO, NOME)
VALUES (SQ_TESTE_01.NEXTVAL, 'ANCHIETA');

--VERIFICANDO O VALOR DA SEQUENCE
SELECT SQ_TESTE_01.CURRVAL FROM DUAL;

--ELIMITANDO UMA SEQUENCE
DROP SEQUENCE SQ_TESTE_01;

SINONIMOS

  • SINONIMO é um dos objetos utilizados para se fazer referencia a outro objeto. Geralmente, grande nomes de tabelas podem ser referenciadas com um nome menor ou referencia a objetos de outros usuários podem ser feitos acessos mais facilmente;
  • Sinônimo público pode ser acesso por qualquer usuário e o sinônimo privado pode ser acesso somente pelo seu criador;
  • Os sinônimos não podem acessar objetos que estão dentro de uma PACKAGE;
  • Para sinônimos privados, o nome deve ser único, diferente de outros objetos deste mesmo usuário;

SINTAXE:

CREATE SYNONYM NOME_SINONIMO
FOR NOME_OBJETO

DROP SYNONYM NOME_SINONIMO

EXEMPLOS:

--com erro, pois o usuário hr nao terá privilégio
CREATE PUBLIC SYNONYM TESTE_SIN FOR TESTE_TABELA ;

--excluindo o sinonimo publico
DROP PUBLIC SYNONYM TESTE_SIN;

--criacao do sinonimo privado
CREATE SYNONYM TESTE_SIN FOR TESTE_TABELA;

SELECT * FROM TESTE_SIN;

--excluindo do sinonimo
DROP SYNONYM TESTE_SIN;

Exercícios

  1. Crie uma View chamada VW_EMPREGADOS_DEPTOS, que mostre os dados da tabela employees, tendo os campos código, nome e sobrenome concatenado (espaço entre eles), e email, onde os JOB_IDS sejam IT_PROG e PU_CLERK;
  1. Prove se é possível fazer inserção de dados através da view criada no exercício 1; Explique o porque de conseguir ou não conseguir;
  1. Crie uma view chamada VW_EMPREGADOSPORTEMPO, CONTENDO o código do empregado, nome, data de admissão que estejam admitidos entre os damos de 2003 e 2005. Crie com o parâmetro WITH CHECK OPTION;
  1. Insira 3 linhas de dados pela view criada no exercício 3, e explique se você conseguiu inserir ou não esses dados;
  1. Crie uma view chamada EMPREGADOS_DEP, que utilize uma tabela com a seguinte estrutura: DEPENDENTES(EMPLOYEE_ID NUMBER(6), COD_DEP NUMBER(2), NOME_DEP VARCHAR(30), DATA_NASC DATE), onde EMPLOYEE_ID E COD_DEP são chaves primárias. A visão deve conter o código de dependente, o nome e a data de nascimento do dependente. Atenção: A tabela ainda não existe. NÃO CRIE A TABELA. Utilize o parâmetro correto para se fazer a view;
  1. Crie uma sequence chamada SQ_NUMERO_DEP, iniciando pelo numero 10, incremento de 5 em 5, tamanho mínimo 10, tamanho máximo de 500, com 30 números em cache;
  1. Crie a tabela dependentes do exercício 5, e faca a inserção de 5 dependentes, utilizando a sequence do exercício 6;
  1. Crie um índice chamado IDX_DEP_NOME na tabela dependentes, no campo NOME_DEP;

Video

learningdatabase.com.br

Tecnologias em Banco de Dados Relacionais, Modelagem de dados dimencionais, tecnologias SQL Servere e Oracle

Aprendendo Programação

Algorítmos, C, C++,Pascal, Python, R

WikiDBA

by Virendra Yaduvanshi - Microsoft SQL Server Database Architect | Consultant | Blogger | Specialist | DBA | Speaker

Blog - Fabiano Neves Amorim

SELECT * FROM [Coisas Da Minha Cabeça] WHERE dbo.fn_TempoParaPost() < dbo.fn_TempoLivre()

ROMANO DBA

Administração de Bancos de Dados

Tércio Costa, Oracle Developer, OCE SQL, ACE Associate

Guia de estudos para certificação ORACLE SQL(1Z0-047, 1Z0-051, 1Z0-061 e 1Z0-071) e PL/SQL(1Z0-144, 1Z0-146 e 1Z0-148)

Strate SQL

Data Adventures with an Architect