Arquivos de Tag: Oracle

Banco de Dados II – Aula 13

PERMISSÃO PARA MODO DEBUG

ScreenHunter_114 May. 06 23.47

EXECUÇÃO DA PROCEDURE

ScreenHunter_115 May. 06 23.48

PASSOS PARA FAZER O DEBUG

ScreenHunter_116 May. 07 00.23

EXEMPLO DE CÓDIGO

CREATE OR REPLACE PROCEDURE SP_TESTE_DEBUG
  (p_employee_id_inicial IN EMPLOYEES.EMPLOYEE_ID%TYPE,
   p_employee_id_final   IN EMPLOYEES.EMPLOYEE_ID%TYPE)
IS
  v_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
  v_last_name   EMPLOYEES.LAST_NAME%TYPE;
  v_hire_date   EMPLOYEES.HIRE_DATE%TYPE;
  v_contador NUMBER;
  
  CURSOR cur_ret_employees is
    SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID BETWEEN p_employee_id_inicial AND p_employee_id_final;
BEGIN
  v_contador := 0;
  
  FOR r_employees IN cur_ret_employees LOOP
    FETCH cur_ret_employees
    INTO v_employee_id, v_last_name, v_hire_date;
    EXIT WHEN cur_ret_employees%NOTFOUND;
    
    v_contador := v_contador +1;
    
    DBMS_OUTPUT.PUT_LINE('Record    : '|| v_contador);
    DBMS_OUTPUT.PUT_LINE('EmployeeId: '||   v_employee_id);
    DBMS_OUTPUT.PUT_LINE('Last Name : ' ||   v_last_name);
    DBMS_OUTPUT.PUT_LINE('Hire Date : ' ||   v_hire_date);
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
  EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error was detected');
END;
/

TESTE DE DEBUG

  • Não crie bloco anônimo para fazer o teste do debug no código;
  • Nos parâmetros, coloque intervalos pequenos somente para ver o resultado;

TRIGGERS (GATILHOS)

  • Trigger é um bloco PL/SQL que é armazenado no banco de dados e executado em resposta de um específico evento;
  • O banco de dados Oracle executa um trigger automaticamente quando é especificada uma ocorrência de uma condição;
  • Uma trigger pode ser definida em uma tabela, view, schema (dono do schema), ou banco de dados (para todos os usuários)Untitled

TIPOS DE EVENTOS DAS TRIGGERS

  • Podemos escrever triggers que disparam em uma das operações que ocorre no banco de dados:
    • DML (insert, update, delete)
    • DDL (create, alter, drop)
    • Operações no banco como SERVERERROR, LOGON, LOGOFF, STARTUP ou SHUTDOWN;

CENÁRIOS DE APLICAÇÕES PARA IMPLEMENTAÇÃO

  • Podemos utilizar triggers em:
    • Segurança
    • Auditoria
    • Integridade de Dados
    • Integridade Referencial
    • Replicação de dados
    • Dados derivados de cálculos
    • Eventos de Logging

DISPONIBILIDADE DOS TIPOS DE TRIGGERS

  • Em DML simples
    • BEFORE
    • AFTER
    • INSTEAD OF

TIPOS DE EVENTOS DA TRIGGER E CORPO

  • Um tipo de evento da trigger determina como as cláusulas da declaração DML irá executar a trigger. As possibilidade são:
    • INSERT
    • UPDATE [OF COLUMN]
    • DELETE
  • O corpo determina a ação é chamada e é um bloco PL/SQL ou a chamada de uma procedure;

CRIANDO TRIGGER DML

ScreenHunter_117 May. 07 01.00

ESPECIFICANDO O TEMPO DE EXECUÇÃO

  • Voce pode especificar o tempo que a trigger irá ser executada em uma das declarações:
    • BEFORE: Executa o corpo da trigger antes do acionamento do evento DML na tabela;
    • AFTER: Executa o corpo da trigger depois do acionamento do evento DML na tabela;
    • INSTEAD OF: Executa o corpo da trigger ao invés do evento disparado (geralmente um comando DML);

SEQUENCIA DE DISPARO DAS TRIGGERS: MANIPULAÇÃO SIMPLES DE LINHA

  • A sequencia de disparo de uma trigger em uma tabela quando um linha é manipulada:

Untitled2

SEQUENCIA DE DISPARO DAS TRIGGERS: MANIPULAÇÃO DE VÁRIAS LINHAS

  • A sequencia de disparo de uma trigger em uma tabela quando são manipuladas várias linhas:

Untitled3

EXEMPLO DA TRIGGER SECURE_EMP

Untitled4

TESTANDO A TRIGGER SECURE_EMP

Untitled5

USANDO PREDICADOS CONDICIONAIS

CREATE OR REPLACE TRIGGER secure_emp BEFORE
  INSERT OR UPDATE OR DELETE ON employees
BEGIN
  IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
     (TO_CHAR(SYSDATE,'HH24') NOT BETWEEN '08' AND '18') THEN
    IF DELETING THEN RAISE_APPLICATION_ERROR(
       -20502,'Voce pode deletar empregados durante o horario comercial.');
    ELSIF INSERTING THEN RAISE_APPLICATION_ERROR (
       -20500,'Voce pode inserir empregados durante o horario comercial.');
    ELSIF UPDATING ('SALARY') THEN RAISE_APPLICATION_ERROR (
       -20503,'Voce pode atualizar o salary em horario comercial');
    ELSE RAISE_APPLICATION_ERROR(
       -20504,'Voce pode atualizar a tabela empregados durante o horario comercial');
    END IF;
  END IF;
END;

desc employees

insert into employees (EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID)
VALUES (207,'TESTE','TESTE',TO_DATE('05/05/2017','MM/DD/YYYY'),'HR_REP')

CRIANDO TRIGGER DE DML DE LINHA

–EXEMPLO 2

CREATE OR REPLACE TRIGGER SALARIO_RESTRITO
  BEFORE INSERT OR UPDATE OF SALARY ON EMPLOYEES
  FOR EACH ROW
BEGIN
  IF NOT (:NEW.job_id IN ('AD_PRES','AD_VP'))
    AND :NEW.salary > 15000 THEN
      RAISE_APPLICATION_ERROR (-20202,
        'Empregado não pode ganhar mais que $15000.');
  END IF;
END;

--teste da chamada
UPDATE employees
SET salary = 15500
WHERE last_name = 'Russell';

USANDO QUALIFICADORES OLD E NEW

  • Quando uma linha é atingida, o motor do run-time PL/SQL cria e popula duas estruturas de dados:
  • OLD: armazena os valores originais do registro processado pela trigger;
  • NEW: contém os novos valores;
  • NEW e OLD tem a mesma estrutura do registro declarado usando %ROWTYPE na tabela a qual a trigger é atachada (criada);

Untitled6

CREATE TABLE AUDIT_EMP (
  USER_NAME        VARCHAR2(30),
  TIME_STAMP       DATE,
  ID               NUMBER(6),
  OLD_LAST_NAME    VARCHAR2(25),
  NEW_LAST_NAME    VARCHAR2(25),
  OLD_TITLE        VARCHAR2(10),
  NEW_TITLE        VARCHAR2(10),
  OLD_SALARY       NUMBER(8,2),
  NEW_SALARY       NUMBER(8,2)
)
/

CREATE OR REPLACE TRIGGER AUDIT_EMP_VALUES
  AFTER DELETE OR INSERT OR UPDATE ON EMPLOYEES
  FOR EACH ROW
BEGIN
  INSERT INTO AUDIT_EMP(USER_NAME, TIME_STAMP, ID,
                        OLD_LAST_NAME, NEW_LAST_NAME,
                        OLD_TITLE,    NEW_TITLE,
                        OLD_SALARY,   NEW_SALARY)
  VALUES (USER, SYSDATE, :OLD.EMPLOYEE_ID,
          :OLD.LAST_NAME, :NEW.LAST_NAME,
          :OLD.JOB_ID, :NEW.JOB_ID,
          :OLD.SALARY, :NEW.SALARY);
END;

--EXMEPLO DE CHAMADA DA FUNÇÃO

INSERT INTO EMPLOYEES (EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY,
EMAIL, HIRE_DATE)
VALUES (999,'TEMP EMP','SA_REP',6000, 'TEMP EMP', TRUNC(SYSDATE));
/

UPDATE EMPLOYEES
SET SALARY = 7000,
LAST_NAME = 'Smith'
WHERE EMPLOYEE_ID = 999;

SELECT *
FROM AUDIT_EMP;

Exercícios

1. Crie uma tabela chamada empregados2 com a mesma estrutura da tabela employees. Faça uma trigger que a medida que a tabela employees for sido preenchida, essa tabela empregados2 deve ter os mesmos dados;

2. Faça uma trigger que verifique se os dados da tabela empregados2 está sendo alterada. Caso o salário esteja sendo alterado, a trigger não deve permitir gravar o registro;

3. Crie uma tabela chamada empregados3. Copie todos os dados que tenham o job_id = ‘IT_PROG’ da tabela employees para essa tabela. Crie uma outra tabela chamada log_empregados3, contendo as estrutura da tabela empregados3, com dados antes e depois de alterações. Faça uma trigger na tabela empregados3 para fazer essa auditoria;

PACKAGES (Pacotes)

  • Package é um objeto de um schema que agrupa logicamente tipos PL/SQL, variáveis e subprogramas;
  • Packages geralmente são divididos em 2 partes:
  • A especificação (spec)
  • O corpo
  • A especificação é uma interface para a Package. Ela declara os tipos, variáveis, constantes, exceções, cursores, e subprogramas que pode ser referenciados do lado de fora da Package;
  • O corpo define as queries de cursores e o código para os subprogramas;
  • Habilita o servidor Oracle a ler múltiplos objetos na memória de uma vez;

VANTAGENS DO USO DAS PACKAGES

  • Modularidade: Encapsulando construtores relacionados;
  • Fácil manutenção: mantendo logicamente funcionalidades relacionadas juntas;
  • Fácil projeto de aplicação: Codificando e compilando a especificação e o corpo separadamente;
  • Escondendo informação:
    • Somente as declarações na Especificação da Package são visíveis e acessíveis para aplicações;
    • Construtores privados no corpo da Package são escondidos e inacessíveis;
    • Toda codificação é escondida no corpo da Package;
  • Adição de funcionalidades: Persistência de variáveis públicas e cursores;
  • Melhor desempenho:
    • Toda a Package é carregada para a memória quando uma Package é referenciada na primeira vez;
    • Há uma única cópia na memória para todos os usuários;
    • A hierárquia de dependência é simplificada;
    • Overloading: Múltiplos subprogramas com o mesmo nome;

COMPONENTES DE UMA PACKAGE PL/SQL

Untitled

VISIBILIDADE INTERNA E EXTERNA DOS COMPONENTES DA PACKAGE

Untitled2

CRIANDO A ESPECIFICAÇÃO DA PACKAGE

Untitled3

  • Variáveis declaradas na especificação da Package são inicializados NULL por padrão;
  • Todos os construtores declarados na especificação da Package são visíveis a usuários que tem privilégios na Package;

CRIANDO A ESPECIFICAÇÃO DA PACKAGE NO SQL DEVELOPER

Untitled4

CRIANDO O CORPO DA PACKAGE NO SQL DEVELOPER

Untitled5

EXEMPLO DE ESPECIFICAÇÃO DA PACKAGE

CREATE OR REPLACE PACKAGE COMM_PKG IS
V_STD_COMM NUMBER := 0.10; --INICIALIZADO COM 0.10
PROCEDURE RESET_COMM(P_NEW_COMM NUMBER);
END COMM_PKG;
/
  • V_STD_COMM é variável pública global inicializada com 0.10;
  • RESET_COMM é procedure publica usada para resetar a comissão padrão baseada em algumas regras de negócio; Será implementadado no corpo da Package;

CRIANDO O CORPO DA PACKAGE

Untitled6

  • Identificadores definidos como privado o corpo da Package e não visível do lado de fora do corpo da package;
  • Todos construtores privados precisam ser declarados antes de eles serem referenciados;
  • Construtores públicos são visíveis para todo o corpo da Package;

EXEMPLO DE CORPO DA PACKAGE

CREATE OR REPLACE PACKAGE BODY COMM_PKG IS
  FUNCTION VALIDATE(P_COMM NUMBER) RETURN BOOLEAN IS
    V_MAX_COMM  EMPLOYEES.COMMISSION_PCT%TYPE;
  BEGIN
    SELECT  MAX(COMMISSION_PCT)
    INTO    V_MAX_COMM
    FROM    EMPLOYEES;
    RETURN (P_COMM BETWEEN 0.0 AND V_MAX_COMM);
  END VALIDATE;

  PROCEDURE RESET_COMM(P_NEW_COMM NUMBER) IS
  BEGIN
    IF VALIDATE(P_NEW_COMM) THEN
      V_STD_COMM := P_NEW_COMM; -- RESETA A VARIAVEL PUBLICA
    ELSE
      RAISE_APPLICATION_ERROR (-20210,'COMISSAO RUIM');
    END IF;
  END RESET_COMM;
END COMM_PKG;
/

CHAMANDO SUBPROGRAMAS DA PACKAGE

EXECUTE COMM_PKG.RESET_COMM(0.15);

Exercícios

  1. Faça uma Package que faça a Insert de funcionários (employees) e Select por ID. Deve conter também uma função que retorne o nome completo do funcionário, passando por parâmetro o ID. Faça os devidos testes de chamadas;
  1. Crie uma Package que faça o Insert, Update, Delete, e Select por ID do Departamento e outra Select pelo Nome do Departamento. Para fazer Inserção e atualização, é necessário verificar se o nome do departamento não exista na tabela. Em caso de um departamento já existente, exiba uma mensagem para ser exibido pelo ambiente chamador. Faça os devidos testes de chamadas;

 

Video 01

Anúncios

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 04

FUNÇÕES DE CONVERSÃO

  • Existem 2 tipos de funções de conversão: implícitas e explícitas;
  • Implícitas o ORACLE converte para o tipo apropriado;
  • Explicitas o usuário faz a utilização manualmente;
  • Sempre que possível, utilizar conversões explícitas;

CONVERSÃO IMPLÍCITAS:

  • O ORACLE automaticamente faz:
    • VARCHAR2 ou CHAR para NUMBER;
    • VARCHAR2 ou CHAR para DATE;
    • NUMBER para VARCHAR2;
    • DATE para VARCHAR2;
  • EXEMPLO:… WHERE NUMERO = ‘25’;… WHERE DATANASC = ’10/23/2000’;

conversoesoracle

http://oracledbagirl.blogspot.com.br/2014/11/funcoes-de-conversao.html

TO_CHAR PARA DATE

  • Tanto TO_CHAR quanto TO_DATE, tem praticamente a mesma formatação;
  • Necessário estar limitado entre aspas simples;
  • É CASE SENSITIVE;

FORMATOS DE DADOS MAIS UTILIZADOS

Formato Significado
YYYY Ano completo com 4 dígitos
YEAR Ano por extenso (em inglês)
MM Mês com 2 dígitos em formato numérico
MONTH Nome do mês por extenso (em inglês)
MON Mês abreviado com 2 dígitos (em inglês)
DY Dia da semana abreviado por extenso (em inglês)
DAY Dia da semana por extenso (em inglês)
DD Dia com 2 dígitos por extenso (em inglês)

OUTROS FORMATOS DE DATAS

Formato Significado
SCC ou CC Acrescenta o A.C. no século
Anos em datas YYYY ou SYYYY Ano; Acrescenta o A.C.
YYY, YY ou Y Últimos 3, 2 ou 1 dígito do ano
Y.YYY Ano com formatação de ponto
IYYY, IYY ou IY Últimos 3, 2 ou 1 dígito do ano no padrão ISSO
SYEAR ou YEAR Ano por extenso, quando tem o S inclui prefixo A.C.
BC ou AD Indica o ano AC ou DC
B.C. ou A.D. Indica o ano A.C. ou D.C. (com pontos)
Q Trimestre do ano
MM Mês com 2 dígitos

OUTROS FORMATOS DE DATAS

 Formato Significado
MONTH Mês por extenso, com 9 dígitos, preenchidos com espaços em branco
MON Mês com abreviação com 3 dígitos
RM Mês em numeral romano
WW ou W Semana do ano ou mês
DDD, DD ou D Dia do ano, do mês ou da semana
DAY Dia por extenso, com 9 dígitos, preenchidos com espaços em branco
DY Dia com abreviação com 3 dígitos
J Dia juliano, desde 31/12/4713 A.C. (número de dias)

FORMATOS DE HORÁRIO

 Formato Significado
AM ou PM Indica o meridiano
A.M. ou P.M. Indica o meridiano com pontos
HH ou HH12 ou HH24 Hora do dia, hora de 1-12 ou hora de 0 a 24
MI Minutos de 0-59
SS Segundos de 0-59
SSSSS Segundos após meia-noite de 0-86399

OUTROS FORMATOS DE DATAS

 Formato Significado
/ , . Pontuação será produzida no resultado
“of the” String será produzida no resultado
TH Numero ordinal (3TH, 4TH)
SP Número por extenso (FOUR, FIVE)
SPTH ou THSP Número ordinal por extenso (FORURTH, FIVETH)

TO_CHAR PARA DATE

  • O formato de data RR é semelhante ao formato YY, porém é específico para distinguir séculos diferentes;
  • Para o século atual, o ORACLE considera os anos de 00 a 49;
  • Para o século anterior, o ORACLE considera os anos de 50 a 99;
  • Exemplos
SELECT SYSDATE FROM DUAL;

SELECT TO_CHAR(SYSDATE,'mm/dd/yyyy') FROM DUAL;

SELECT TO_CHAR(SYSDATE,'dd/mm/yyyy') FROM DUAL;

SELECT TO_CHAR(SYSDATE,'DAY-MONTH-YEAR') FROM DUAL;

SELECT TO_CHAR(SYSDATE,'mm/dd/yyyy HH24:MI:SS') FROM DUAL;

 

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

SELECT TO_DATE('01/01/97','DD/MM/YY') FROM DUAL;

SELECT TO_DATE('01/01/97','DD/MM/RR') FROM DUAL;

SELECT TO_DATE('01/01/49','DD/MM/RR') FROM DUAL;

SELECT TO_DATE('01/01/50','DD/MM/RR') FROM DUAL;

select trunc(to_date('27-Jul-1987'),'YYYY') FROM dual;

select trunc(to_date('27-Jul-1987'),'RRRR') FROM dual;

TO_CHAR PARA NÚMEROS

  • O tipo de dados é de NUMBER para VARCHAR2;
  • Muito utilizado para concatenação de dados;
  • O ORACLE também faz conversão implícita de VARCHAR2 para NUMBER, quando possível;
  • Exemplos
SELECT FIRST_NAME, SALARY FROM   EMPLOYEES;

SELECT FIRST_NAME, TO_CHAR(SALARY,'L99999D99') AS SALARY FROM   EMPLOYEES;

SELECT TO_CHAR(1234,'099999') FROM DUAL;

SELECT TO_CHAR(-1234,'9999MI') FROM DUAL;

SELECT TO_CHAR(1234,'S9999') FROM DUAL;

SELECT TO_CHAR(-1234,'S9999') FROM DUAL;

TO_CHAR PARA DATE

  • PRINCIPAIS ELEMENTOS
Caracter Significado
9 Representa um número
0 Impõe a exibição de um zero
$ Insere um sinal de dólar
L Usa o símbolo da moeda local
. Imprime uma casa decimal
, Imprime uma virgula como indicador de milhar
D Retorna caracter especial na posição especificada (por padrão é ,)
MI Sinal de – à direita (valores negativos)

ORDEM DE EXECUÇÃO DAS FUNÇÕES

  • A ordem de execução é feita de dentro para fora, quando existe mais de uma função na mesma expressão;
  • Exemplo
SELECT INITCAP( CONCAT( SUBSTR( UPPER(FIRST_NAME),1,3),'_ORACLE') 
FROM EMPLOYEES

FUNÇÕES COM VALORES NULOS

  • Existem funções que podem fazer o tratamento de campos com valores nulos;
  • O valor NULO não é espaço em branco ou zeros, portanto, precisam ter funções especiais para que sem manipulados corretamente;

Comando NVL

  • Faz a conversão de um valor nulo em outro valor;
  • Sintaxe:
    • NVL(expr1,expr2)
  • Exemplo
SELECT first_name, 
       NVL(commission_pct,0), 
       commission_pct + 12 as "COMISSAO ANUAL" 
FROM   EMPLOYEES;

Comando NVL2

  • Compara se a expressão 1 não for nula, retorna a expressão 2; se a expressão 1 for nula, retorna a expressão 3;
  • Sintaxe:
    • NVL2(expr1,expr2, expr3)
  • Exemplo
SELECT first_name, 
       salary, 
       commission_pct, 
       NVL2(commission_pct, 'SALARIO / COMISSAO','SALARIO') 
FROM   employees;

Comando NULLIF

  • Compara se as duas expressões tem valores iguais. Caso sim retorna o valor NULL, e caso não, retorna o valor da expressão 1;
  • Sintaxe:
    • NULLIF(expr1,expr2)
  • Exemplo
SELECT first_name,
       last_name,
       LENGTH(first_name),   
       LENGTH(last_name),   
       NULLIF(LENGTH(first_name),        
       LENGTH(last_name))
FROM   employees;

Comando COALESCE

  • Retorna o primeiro valor NÃO NULO de uma lista de expressões;
  • Sintaxe:
    • COALESCE(expr1,expr2,expr3,… exprN);
  • Exemplo
SELECT last_name,
       manager_id,
       commission_pct,
       COALESCE(manager_id,
                commission_pct,
                -1)
FROM   employees
ORDER BY commission_pct DESC;

EXPRESSÕES CONDICIONAIS

  • Permitem utilizar IF.. THEN.. ELSE nas expressões SQL;
  • Podemos utilizar o CASE (padrão ANSI) e o DECODE (exclusivo do ORACLE)

Comando CASE

  • Praticamente tem a mesma função de expressões condicionais nas demais linguagens de programação (IF … THEN…ELSE)
  • Sintaxe do Comando CASECASE expr1WHEN comp_expr1 THEN retorno_expr1
    WHEN comp_expr2 THEN retorno_expr2
    WHEN comp_expr3 THEN retorno_expr3
    ELSE retorno_else
    END;
    
  • Exemplo de CASE
SELECT last_name, job_id, salary,
       CASE job_id
         WHEN 'IT_PROG' THEN salary * 1.20
         WHEN 'ST_CLERCK' THEN salary * 1.30
         WHEN 'SA_REP' THEN salary * 1.50
         ELSE salary
       END "NOVO SALARIO"
FROM   employees;

Comando DECODE

  • É uma versão simplificada do CASE (em termos de parâmetros), tendo a mesma finalidade;
  • Sintaxe: DECODE (expr1, busca1, resultado1, busca2, resultado2, …, retorno_default);
  • Exemplo
SELECT last_name, job_id, salary,
       DECODE (job_id, 'IT_PROG', salary * 1.20,
       'ST_CLERCK', salary * 1.30,
       'SA_REP', salary * 1.50,
       salary) "NOVO SALARIO"
FROM   employees;

FUNÇÕES DE GRUPO

  • Geralmente são utilizados para obter resultados sumarizados (resultados agrupados);
  • Pode ser aplicado na tabela inteira, ou em um determinado grupo;
  • Utiliza-se a função de grupo na sentença SELECT;
  • Na cláusula GROUP BY é onde será a determinação de qual dado será agrupado;

COMANDO AVG

  • Retorna a média aritmética de um determinado campo dividido pelo número de linhas de retorno da tabela;
  • Sintaxe: AVG([DISTINCT | ALL] n)
  • EXEMPLO DE COMANDO AVG
SELECT  AVG(SALARY)
FROM    EMPLOYEES;

SELECT  AVG(COMMISSION_PCT)
FROM    EMPLOYEES;

SELECT  AVG(NVL(COMMISSION_PCT,0))
FROM    EMPLOYEES;

COMANDO COUNT

  • Retorna o número de linhas ou quantidade de campos não nulos de uma tabela;
  • Sintaxe: COUNT(* | DISTINCT | ALL expr)
  • EXEMPLO DE COMANDO COUNT
SELECT  COUNT(*)
FROM    EMPLOYEES;

SELECT  COUNT(COMMISSION_PCT)
FROM    EMPLOYEES;

SELECT COUNT(DISTINCT JOB_ID)
FROM   EMPLOYEES;

COMANDO MAX

  • Retorna o valor máximo da expressão, ignorando os valores nulos;
  • Sintaxe: MAX([DISTINCT | ALL] expr)
  • EXEMPLO COMANDO MAX
SELECT    MIN(START_DATE),   MAX(START_DATE)
FROM      JOB_HISTORY;

COMANDO MIN

  • Retorna o valor mínimo da expressão, ignorando os valores nulos;
  • Sintaxe: MIN([DISTINCT | ALL] expr)
  • EXEMPLO COMANDO MIN E MAX
SELECT    MIN(FIRST_NAME),   MAX(FIRST_NAME)
FROM      EMPLOYEES;

COMANDO SUM

  • Retorna a soma de valores de uma expressão, ignorando os valores nulos;
  • Sintaxe: SUM([DISTINCT | ALL] expr)
  • EXEMPLO DE COMANDO SUM
SELECT  SUM(SALARY)
FROM      EMPLOYEES;

COMANDO STDDEV

  • Retorna o desvio padrão, ignorando os valores nulos;
  • Sintaxe: STDDEV([DISTINCT | ALL] expr)

COMANDO VARIANCE

  • Retorna a variação, ignorando os valores nulos;
  • Sintaxe: VARIANCE([DISTINCT | ALL] expr)

FUNÇÕES DE GRUPO COM AGRUPAMENTO

  • O agrupamento irá fazer com que dados sejam agrupados e geralmente terá uma função de grupo associada;
  • A cláusula GROUP BY precisa estar na sentença do comando SQL, contendo todos os campos que estão no SELECT, menos as próprias funções de grupo;
  • O agrupamento deve ser aplicado a dados que tenham repetição, ou que se consiga fazer operações matemáticas;
  • Não há muito sentido em fazer agrupamentos, por exemplo em campos que são chave primária, pois o conteúdo nunca se repete;
  • Os resultados retornarão uma ou mais linhas, com resultados sumarizados;
  • Não é possível utilizar apelidos das colunas na cláusula GROUP BY.
  • EXEMPLOS DE GROUP BY
SELECT    JOB_ID, COUNT(*)
FROM      EMPLOYEES
GROUP BY  JOB_ID
ORDER BY  COUNT(*) DESC;

SELECT    DEPARTMENT_ID, SUM(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID
ORDER BY  DEPARTMENT_ID NULLS FIRST;

SELECT    DEPARTMENT_ID, AVG(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID

SELECT    DEPARTMENT_ID, MAX(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID;

SELECT    DEPARTMENT_ID, MIN(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID;

FUNÇÕES DE GRUPO COM HAVING

  • A cláusula HAVING sempre será utilizada com GROUP BY;
  • Ela serve para fazer limitação de linhas, a qual é aplicada no GROUP BY;
  • Dê preferencia para fazer limitações de linhas sempre que possível na cláusula WHERE;
  • A sequencia que o ORACLE executa um comando SQL quando se tem um HAVING:
    • 1 – As linhas agrupadas;
    • 2 – A função de grupo é aplicada;
    • 3 – os grupos filtradas na cláusula HAVING são exibidos;
  • A ordem de função de grupo tem a mesma ordem das outras funções, ou seja, funções mais internas são executadas primeiro;
  • É possível ter no máximo 2 funções de grupos na mesma sentença;
  • EXEMPLO DE HAVING
SELECT    DEPARTMENT_ID, MAX(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID
HAVING    MAX(SALARY) > 10000;

SELECT    DEPARTMENT_ID, AVG(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID
HAVING    MAX(SALARY) > 10000;

SELECT    JOB_ID, SUM(SALARY)
FROM      EMPLOYEES
GROUP BY  JOB_ID
HAVING    SUM(SALARY) > 10000
ORDER BY  SUM(SALARY);

SELECT  MAX(AVG(SALARY))
FROM   EMPLOYEES
GROUP BY DEPARTMENT_ID;

SELECT  SUM(AVG(SALARY))
FROM   EMPLOYEES
GROUP BY JOB_ID;

 

Vídeo 01

Vídeo 02

Banco de Dados II – Aula 03

VARIÁVEIS DE SUBSTITUIÇÃO

  • Geralmente utilizadas para executar uma instrução SELECT mais de uma vez;
  • Servirá como se fosse parâmetro;
  • Somente utilizadas na cláusula WHERE, com o símbolo : (para apex);
  • O símbolo & para sqlplus, pode ser utilizado em mais cláusulas;
  • As variáveis de substituição servem para guardar temporariamente valores;
  • EXEMPLOS
Exemplo 01
SELECT 	* 
FROM 	employees
WHERE 	employee_id = :employee_id;

Exemplo 02
SELECT 	* 
FROM 	employees
WHERE	first_name = :first_name
    		AND salary >= :salary;
 

Exemplo 03
SELECT * 
FROM employees
WHERE last_name like '&last_name';

SELECT * 
FROM &tabela

(no SQLDeveloper, pressione  + , e ele pedirá o valor a ser digitado)

FUNÇÕES DE LINHAS

  • Podem ser utilizadas para tipos de dados character, data, números em uma instrução SELECT;
  • Funcionam como conversão, formatação de dados (datas e números), manipulam strings, podem ter ou não dados de entrada (ou parâmetros), mas sempre possuem dados de saída;
  • As maioria das funções apresentadas aqui são do Oracle;
  • Possuem 2 tipos:
    • por Linha: só retornam um resultado por linha (funções de character, número, data e conversão);
    • de Grupo: manipulam grupos de linhas para retornar apenas um resultado;
  • Caracteristicas das functions de linha;
    • Podem receber argumentos (podendo ser uma constant, variável, nome de coluna, expressão);
    • Podem ser usados na cláusulas SELECT, WHERE, ORDER BY;
    • Podem ser encadeadas;
    • Funções de data: entradas do tipo DATE e retornam um tipo DATE, exceto MONTHS_BETWEEN, que retorna um tipo número;
    • Funções numéricas: entradas do tipo NUMERIC e retornam um tipo NUMERIC;
    • Funções de character: entrada do tipo caracteres e retornam caracteres ou numeros;
    • Funções de conversão: convertem um tipo de dado em outro;
    • Outras funções: NVL, NVL2, NULLIF, COALESCE, CASE, DECODE;

FUNÇÕES DE LINHAS – CARACTERES

  • Manipulação de caixa alta e baixa (maiúsculo e minúsculo):
    • UPPER: retornar todos os valores em letras maiúsculas;
    • LOWER: retornar todos os valores em letras minúsculas;
    • INITCAP: a primeira letra de cada palavra em maiúscula e o restante minúsculas;
  • Manipulação de caracter:
    • CONCAT: Une dois valores;
    • SUBSTR: Extrai uma string de determinado tamanho;
    • LENGTH: Retorna o tamanho de uma string em um valor numérico;
    • INSTR: Localiza uma posição numérica de um character nomeado;
    • LPAD: Preenche com valor do character à esquerda;
    • RPAD: Preenche com valor do character à direita;
    • TRIM: Retira caracteres em branco à direita e à esquerda de uma string;
    • Exemplos
SELECT  UPPER('rodrigo saito') 
FROM    DUAL;

SELECT  LOWER('RODRIGO SAITO') 
FROM    DUAL;

SELECT  INITCAP('rodrigo saito') 
FROM    DUAL;

SELECT  CONCAT('Rodrigo','Saito') 
FROM    DUAL;

SELECT  SUBSTR('Teste de caracteres no Oracle',1,5) 
FROM    DUAL;

SELECT  SUBSTR('Teste de caracteres no Oracle',6) 
FROM    DUAL;

SELECT  LENGTH('Teste de caracteres no Oracle') 
FROM    DUAL;

SELECT  INSTR('Teste de caracteres no Oracle','s') 
FROM    DUAL;

SELECT  LPAD('Teste de caracteres no Oracle',40, '0')
FROM    DUAL;

SELECT  RPAD('Teste de caracteres no Oracle',40, '0')
FROM    DUAL;

SELECT REPLACE('rodrigo anchieta', 'anchieta', 'saito')
FROM DUAL;

SELECT TRIM('R' FROM 'RODRIGO') 
FROM DUAL;

FUNÇÕES DE NÚMEROS

  • Tem como entrada dados do tipo numérico e sempre retornam dados do tipo numérico;
  • ROUND: arredonda o valor para cima (caso o decimal seja maior que 5);
  • TRUNC: retorna o valor a ser truncado;
  • MOD: retorna o resto da divisão;

ROUND

  • SINTAXE: ROUND(VALOR,N): N é opcional, sendo o número de casas decimais a ser definido;
  • Exemplos
SELECT ROUND(10.5) 
FROM DUAL;

SELECT ROUND(10.5,1) 
FROM DUAL;

SELECT ROUND(10.51,1) 
FROM DUAL;

SELECT ROUND(10.55,1) 
FROM DUAL;

SELECT ROUND(10.60,1) 
FROM DUAL;

SELECT ROUND(10.554,2) 
FROM DUAL;

SELECT ROUND(10.555,2) 
FROM DUAL;

TRUNC

  • SINTAXE: TRUNC(VALOR,N): N é opcional, sendo o número de casas decimais a ser definido. O comando basicamente irá cortar o número definido;
  • Exemplos
SELECT TRUNC(10.1) FROM DUAL;

SELECT TRUNC(10.1,1) FROM DUAL;

SELECT TRUNC(10.15,1) FROM DUAL;

SELECT TRUNC(10.155,2) FROM DUAL;

SELECT TRUNC(10.155,-1) FROM DUAL;

SELECT TRUNC(10.155,-2) FROM DUAL;

SELECT TRUNC(100.155,-2) FROM DUAL;

MOD

  • SINTAXE: MOD(M,N): M PRIMEIRO VALOR, E N é o segundo valor. O comando retornar o resto de M por N.
  • Exemplos
SELECT MOD(10,2) FROM DUAL;

SELECT MOD(10,3) FROM DUAL;

SELECT MOD(11,3) FROM DUAL;

SELECT MOD(19,5) FROM DUAL;

FUNÇÕES DE DATA

  • Datas são armazenadas internamente em formato numérico, tendo a representação de século, ano, mês, dia, hora, minuto e segundos;
  • Padrão do Oracle é MM/DD/YYYY;
  • Data válidas são 01/01/4712 A.C. a 31/12/9999 D.C.;
  • Na inserção do dado do tipo data em um campo, internamente o Oracle armazena o século da função SYSDATE, nas para exibição esse dado é ocultado por padrão;
  • Date armazena internamente 4 dígitos para o ano (dois dígitos para século e dois dígitos para ano);
  • Apesar da exibição ser de 2 dígitos para o ano, internamente são 4 dígitos;
  • De 1951 a 2050 mostra os 2 últimos dígitos do século atual;

SYSDATE

  • Retorna a data e horário do século atual, podendo ser usado em qualquer coluna.
  • É possível utilizar na tabela de sistema DUAL;\
  • Exemplo
select SYSDATE from DUAL

(será exibido por padrão somente a data)

SELECT SYSDATE + 5 FROM DUAL;

SELECT SYSDATE - 5 FROM DUAL;

SELECT SYSDATE - (SYSDATE - 10) FROM DUAL;

SELECT SYSDATE - TO_DATE('01/01/2015','mm/dd/yyyy') FROM DUAL;

select TO_CHAR(sysdate,'mm/dd/yyyy hh:mi') from dual;

SELECT TO_CHAR(SYSDATE,'dd/mm/yyyy hh24:mi') FROM DUAL;

SELECT TO_CHAR(TO_DATE('01/01/2016 09:01','dd/mm/yyyy hh24:mi'),'dd/mm/yyyy hh24:mi') FROM DUAL;

SELECT TO_CHAR(TO_DATE('01/01/2016 09:01','dd/mm/yyyy hh24:mi') + 1/24,'dd/mm/yyyy hh24:mi') FROM DUAL;

SELECT TO_CHAR(TO_DATE('01/01/2016 09:01','dd/mm/yyyy hh24:mi') + 3/24,'dd/mm/yyyy hh24:mi') FROM DUAL;

SELECT TO_CHAR(TO_DATE('01/01/2016 09:01','dd/mm/yyyy hh24:mi') + 1/(24*60),'dd/mm/yyyy hh24:mi') FROM DUAL;

SELECT TO_CHAR(TO_DATE('01/01/2016 09:00:00','dd/mm/yyyy hh24:mi:ss') + 20/(24*60*60),'dd/mm/yyyy hh24:mi:ss') FROM DUAL;

SELECT TO_CHAR(SYSDATE + 20/(24*60*60),'dd/mm/yyyy hh24:mi:ss')  FROM DUAL;

SELECT TO_CHAR(SYSDATE,'dd/mm/yyyy hh24:mi') FROM DUAL;

CALCULOS DE DIAS COM DATA

  • É possível fazer adição e subtração de dias em datas, além de fazer a diferença de dias entre datas, considerando os dias, meses e anos;
  • Para se fazer operações com horas, é necessário dividir um dia pela quantidade de horas (ou seja, 24);
  • Para se fazer operações com minutos, é necessário saber o quanto 1 minuto representa em 1 dia (1440);

OUTRAS FUNÇÕES DE DATA

  • Tem como entrada de parâmetros do tipo data e retornam valores do tipo data ou numérico;

MONTHS_BETWEEN(data1,data2)

  • Retorna o número de meses entre as datas do parâmetro;
  • Exemplos
SELECT MONTHS_BETWEEN( TO_DATE('01/01/2016'), TO_DATE('01/01/2015')) FROM DUAL;

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('01/01/2015')) FROM DUAL;

ADD_MONTHS(data1,n)

  • Retorna uma data, adicionando a quantidade de meses especificadas no parâmetro em data1;
  • Exemplos
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;

SELECT SYSDATE + 90 FROM DUAL;

NEXT_DAY (data1,numeric)

  • Retorna o próximo dia da semana, sendo representando pelos números de 1 a 7 (1 é domingo a 7 que é sábado);
  • Exemplos
SELECT NEXT_DAY(SYSDATE,1) FROM DUAL;

SELECT NEXT_DAY(SYSDATE,4) FROM DUAL;

SELECT NEXT_DAY(SYSDATE,-3) FROM DUAL;

LAST_DAY (data1)

  • Retorna o último dia do mês especificado;
SELECT LAST_DAY(SYSDATE) FROM DUAL;

SELECT LAST_DAY(TO_DATE('05/04/2015')) FROM DUAL;

ROUND (data1[,valor])

  • Retorna uma data arredondada, sendo o parâmetro valor opcional, e o padrão equivalente ao parâmetro ‘day’;
  • Exemplos
SELECT ROUND(SYSDATE) FROM DUAL;

SELECT ROUND(SYSDATE,'DAY') FROM DUAL;

SELECT ROUND(SYSDATE,'MONTH') FROM DUAL;

SELECT ROUND(SYSDATE,'YEAR') FROM DUAL;

SELECT ROUND(TO_DATE('01/15/2016 11:59:00','mm/dd/yyyy hh24:mi:ss')) FROM DUAL;

SELECT ROUND(TO_DATE('01/15/2016 12:01:00','mm/dd/yyyy hh24:mi:ss')) FROM DUAL;

SELECT ROUND( TO_DATE('01/15/2016','mm/dd/yyyy'),'MONTH') FROM DUAL;

SELECT ROUND( TO_DATE('01/16/2016','mm/dd/yyyy'),'MONTH') FROM DUAL;

SELECT ROUND(TO_DATE('06/30/2016','mm/dd/yyyy'),'YEAR') FROM DUAL;

SELECT ROUND(TO_DATE('07/01/2016','mm/dd/yyyy'),'YEAR') FROM DUAL;

TRUNC (data1[,valor])

  • Retorna uma data truncada (cortada), sendo o parâmetro valor opcional;
  • Exemplos
SELECT TRUNC(SYSDATE) FROM DUAL;

SELECT TRUNC(SYSDATE,'DAY') FROM DUAL;

SELECT TRUNC(SYSDATE,'MONTH') FROM DUAL;

SELECT TRUNC(SYSDATE,'YEAR') FROM DUAL;

SELECT TRUNC( TO_DATE('07/15/2016', 'mm/dd/yyyy')) FROM DUAL;

SELECT TRUNC( TO_DATE('07/15/2016', 'mm/dd/yyyy'),'MONTH') FROM DUAL;

SELECT TRUNC(TO_DATE('07/15/2016', 'mm/dd/yyyy'),'YEAR') FROM DUAL;

Vídeo 1

Vídeo 2

Banco de Dados I – Aula 14C

EXERCÍCIOS: Utilize o Schema HR do Oracle para resolver os problemas abaixo:

hr

1 – Selecione todos os ids, nomes e sobrenomes de empregados, que estejam nas faixas de salários de 0 a 2000 e maiores ou iguais a 5000. Dê 3 tipos de consultas para resolver esse problema)

2 – Selecione todos os ids e nomes de departamentos, o id do gerente do departamento que estejam nos id de locais entre 1500 a 2000;

3 – Selecione todos os nomes de departamentos e nomes dos gerentes de departamentos onde a cidade de localização seja Sao Paulo (faça pela antiga e nova sintaxa. Se possível, resolva também em algebra relacional)

4 – Selecione todos os ids, nomes e sobrenome de empregados que possuem salario maior que 5000 (Dê duas soluções para este problema. Se possível, resolva também em algebra relacional)

5 – Selecione todos ids, nomes e sobrenomes (como um único campo – nome completo) de empregados, a data de admissao (hire date),data de inicio e fim (end_date) dos determinados serviços (jobs). Obs: um empregado pode ter trabalhado em mais de um servico em um período de tempo; (Dê 2 soluções para esse problema. Se possível, resolva também em algebra relacional)

6 – Selecione todos o nome da região, o nome do pais que os nomes de paises iniciem com as letras A,B,J (Dê 2 soluções para esse problema. Se possível, resolva também em algebra relacional)

7 – Selecione todos os id´s e nomes dos gerentes, ids e nomes de departamentos gerenciados por esses gerentes. (De 3 soluções para esse problema)

8 – Selecione todos os ids e nomes de departamentos, a qual não possuem nenhum empregado alocado (dê 2 soluções para esse problema);

9 – Selecione todos os ids e nomes de empregados, o nome do departamento, o nome do pais e o nome da região, onde as regiões devem ser iguais a Europe e Asia (dê 3 soluções para esse problema);

Vídeo 01

 

learningdatabase.com.br

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

Aprendendo Programação

Algorítmos, Linguagem 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