PL/SQL

Oracle 11G - PLSQL

Edgar Rodrigues Sandi
Edgar Sandi

tw/@EdgarSandi
fb/EdgarSandi
G+/EdgarSandi
Ln/EdgarSandi

Oracle PL/SQL - 24h
Aula 1 - 17/01
Aula 2 - 24/01
Aula 3 - 31/01
Aula 4 - 07/02
Aula 5 - 21/02
Aula 6 - 28/02

Alunos:
Leonardo Lima - ALUNO ORACLE ESSENCIAL e PLSQL NOV/2014 <leo7limma@outlook.com>, 
Mario Celso Gonçalves de Souza - ALUNO ORACLE ESSENCIAL e PLSQL NOV/2014 <mariocelso@customdata.com.br>, 
Amanda Cortez Machado - ALUNO ORACLE ESSENCIAL e PLSQL NOV/2014 <amanda_cortez_2012@hotmail.com>, 
Thiago Arantes Magalhães - ALUNO ORACLE ESSENCIAL e PLSQL NOV/2014 <thiagoara2012@gmail.com>, 
Bruno Gabriel dos Santos - ALUNO ORACLE PLSQL JAN/2014 <bruno.gsantos89@gmail.com>, 
Cesar Figueiredo Augusto - ALUNO ORACLE PLSQL JAN/2015 <cesarcfa@superig.com.br>



-- PL/SQL
  -- Program Language SQL
  -- Programação no Banco de dados
  -- Transaction SQL - SQL Server
  -- PG/PLSQL - PostgreSQL
  -- PL/SQL - MySQL
  
-- regras de negócio
-- procedures - Stored Procedures
-- packages
-- triggers
-- functions
-- blocos anônimos

-- ESCOPO básico de um bloco anônimo

[DECLARE]  -- opcional
   -- declaração das variáveis
BEGIN
   -- progamação 
   -- regras de negócios
[EXCEPT]
   -- Tratamentos de erros
END;

-- enviar texto ao console
BEGIN
  dbms_output.put_line('Hello world');
END;

-- comentário de linha
/*
  comentário de bloco
  
  // comentário de linha
  # comentário de linha
*/

-- Variáveis no PL/SQL ou em qualquer lugar
  -- Nomes de variáveis com no máximo 30 caracteres
  -- Use no nome da variávle algo que descreva muito bem do que
     -- se trata
  -- Inicie com letra ou _
  -- Não usar palavras reservadas do Oracle
  -- Não usar caracteres especiais (ç , ^, ~, ...) 
  -- _ e o # podem ser utilizados
  -- Quando o nome for composto utilize o camelCase
     -- camelCase -> variavelComPadraoCamelCase
        -- variaveis
        -- funções
        -- metodos
     -- StudlyCaps -> VariavelComPadraoStudlyCaps
        -- classes, intefaces, enum
     -- constantes -> IDADE
     -- constante composta -> IDADE_DO_ALUNO
     
-- Utilização das aspas simples e aspas duplas
   -- Utilização da apóstrofe ou das aspas
   -- "" -> identificação de objetos (tabelas, procedures...)
   -- '' -> identificação de textos



DECLARE
   -- <nome> <tipo> := <valor>;
   x number; -- 38 digitos
   contadorDeAlunos number(2);
   preco number(6, 2); -- 9999,99
   vNome varchar(50);
   vUF char(2);
   vSexo1 char(1) := 'M';
   ano date;
   nivel constant varchar(50) := 'admin';
   vSexo2 char(1) := 'M';
   vIdade number(3);  -- -999 a 999
   vIdade positiveN := 10;
   -- binary_integer -> -2^31 a +2^31
   -- positiveN -> binary_integer positivos e não nulos
BEGIN
   dbms_output.put_line(vSexo2);
   vSexo2 := 'F';
   dbms_output.put_line(vSexo2);
END;

-- Olá <meu nome>, tudo bem?
DECLARE
  vMeuNome varchar(50);
BEGIN
  vMeuNome := 'Edgar Rodrigues Sandi';
  dbms_output.put_line('Olá ' || vMeuNome || ', tudo bem?');
END;

-- Transações - transaction
 -- Iniciar   -> START TRANSACTION - BEGIN TRANSACTION - BEGIN
 -- Confirmar -> COMMIT
 -- Desfazer  -> ROLLBACK

SELECT * FROM tb_alunos;

START TRANSACTION
  DELETE FROM teste;
COMMIT;

ROLLBACK;
SELECT * FROM teste;

-- ACID
  A -> Atomicidade
  C -> consistencia
  I -> isolamento
  D -> durabilidade

-- Operadores de condição
/*
se  <condiçao>  então
   <comandos>
senão
   <comandos>
*/

if <condicao> then / elsif
true -> 'a' 1, 6, 9
false -> '', 0, null, 

DECLARE
  a boolean := true;
  b boolean := false;
  c boolean := null;
  vTeste boolean;
BEGIN
  vTeste := (b AND NOT c) OR NOT(a = b) AND (c IS NULL);
              -- true     ou   true      e    true
              
  if (vTeste = true) then
     dbms_output.put_line('vTeste = true');  
  elsif (vTeste = false) then
     dbms_output.put_line('vTeste = false');  
  elsif (vTeste IS NULL) then
     dbms_output.put_line('vTeste is null');
  else 
     dbms_output.put_line('NDA');
  end if;
END;

DECLARE
  a boolean := true;
BEGIN
 dbms_output.put_line('a = ');
  if not a then
    dbms_output.put_line('false');  
  else
    dbms_output.put_line('true');  
  end if;
END;

-- refatoramos
-- DRY  -> Don't repeat yourself
-- KISS -> Keep It Simple Stupid

-- lab: 01
Dado um código, nome e sexo
Responda:
   -- Olá aluna <nome>, seu código é <código>
   -- Olá aluno <nome>, seu código é <código>

DECLARE
  vCodigo number := 1;
  vNome varchar(50) := 'Joana';
  vSexo char(1) := 'M';
  vResult varchar(10);
BEGIN
  vResult := 'aluno';
  IF vSexo = 'F' THEN
    vResult := 'aluna';
  END IF;
  dbms_output.put_line('Ola '|| vResult || ' ' ||vNome||', seu código e: '|| vCodigo);
END;

-- Lab02:
-- Dado 4 notas, calcule a média e:
   -- se a média for maior que 7 mostre: Aprovado
   -- se a média for igual a 7 mostre: Em recuperação
   -- se a média for menor que 7 mostre: Reprovado

DECLARE
  vNota1 number(2);
  vNota2 number(2);
  vNota3 number(2);
  vNota4 number(2);
  vMedia number(4, 2);
BEGIN
  vMedia := (&vNota1 + &vNota2 + &vNota3 + &vNota4) /4;

  IF vMedia = 7 THEN
    dbms_output.put_line('Recuperação');
  ELSIF vMedia > 7 THEN
    dbms_output.put_line('Aprovado');
  ELSE
    dbms_output.put_line('Reprovado');
  END IF;
END;

-- Homework PL/SQL
1. Calculadora
   -- dados dois valores e a operação matemática
   -- execute a operação e mostre o valor na saída dbms
2. Conversor de temperatura
   -- Dada a temperatura em fahrenheit converta para celsius
      -- fórmula 'ºC = (ºF - 32) / 1,8'

Respostas:
1. Calculadora
DECLARE
  a number(2);
  b number(2);
  vOperacao char(1);
  vResultado number(3);
BEGIN
  a := &a;
  vOperacao := &vOperacao; ---   +
  b := &b;
  
--  vOperacao := '' || vOperacao || '';
--  vOperacao := CONCAT(CONCAT(q'[']', vOperacao), q'[']');
  dbms_output.put_line(vOperacao);  
  if vOperacao = '+' then
    vResultado := a + b;
  elsif vOperacao = '-' then
    vResultado := a - b;
  elsif vOperacao = '*' then
    vResultado := a * b;
  elsif vOperacao = '/' then
    vResultado := a / b;
  end if;
  
  dbms_output.put_line('Resultado da operacao: ' || vResultado);
END;

2. Conversor de temperatura
DECLARE
  vFahrenheit number(2) := 40;
  vCelsius number(2);
BEGIN
  vCelsius := (vFahrenheit - 32) / 1.8;
  dbms_output.put_line('Resultado: ' || vCelsius || 'º Celsius');
END;

-- Tabela cidades
CREATE TABLE tb_cidades(
  cd_cidade number(5) primary key,
  nm_cidade varchar(50) not null,
  tx_uf char(2) not null
);

-- INSERINDO VALORES
INSERT INTO tb_cidades (cd_cidade, nm_cidade, tx_uf) 
  VALUES (seq_cidade.nextval, 'Sao Paulo', 'SP');
INSERT INTO tb_cidades (cd_cidade, nm_cidade, tx_uf) 
  VALUES (seq_cidade.nextval, 'Sao Jose dos Campos', 'SP');

-- SEQUENCE
   -- MySQL - autoincrement
   -- PostgreSQL - serial
   -- Oracle - SEQUENCE
      -- current value?  currval
      -- next value?  nextval
CREATE SEQUENCE <sequencia>;
CREATE SEQUENCE seq_cidade;

-- Para descobrir o valor atual?
SELECT seq_cidade.currval FROM dual;

-- Inicializar ou pegar o próximo valor
SELECT seq_cidade.nextval FROM dual;

-- TYPES
-- Usando o Type para capturar o tipo de dado de uma coluna

-- exemplo sem usar o type
DECLARE
  vCidade varchar(50) := 'Taubaté';
  vUf char(2) := 'SP';
  vCod number(5);
BEGIN
  INSERT INTO tb_cidades (cd_cidade, nm_cidade, tx_uf)
    VALUES (seq_cidade.nextval, vCidade, vUf)
    RETURNING cd_cidade INTO vCod;
    
  dbms_output.put_line('A cidade ' || vCidade || ' foi cadastrada com o id ' || vCod);
END;

-- exemplo usando o type
DECLARE
  vCidade tb_cidades.nm_cidade%type := 'Guaratingueta';
  vUf tb_cidades.tx_uf%type := 'SP';
  vCod tb_cidades.cd_cidade%type;
BEGIN
  INSERT INTO tb_cidades (cd_cidade, nm_cidade, tx_uf)
    VALUES (seq_cidade.nextval, vCidade, vUf)
    RETURNING cd_cidade INTO vCod;
    
  dbms_output.put_line('A cidade ' || vCidade || ' foi cadastrada com o id ' || vCod);
END;

-- com o ROWTYPE
DECLARE
  vReg tb_cidades%rowtype;
  vCod vReg.cd_cidade%type;
BEGIN
  vReg.nm_cidade := 'Pindamonhangaba';
  vReg.tx_uf := 'SP';

  INSERT INTO tb_cidades (cd_cidade, nm_cidade, tx_uf)
    VALUES (seq_cidade.nextval, vReg.nm_cidade, vReg.tx_uf)
    RETURNING cd_cidade INTO vCod;
    
  dbms_output.put_line('A cidade ' || vReg.nm_cidade || ' foi cadastrada com o id ' || vCod);
END;

-- SUBTYPE
DECLARE
  SUBTYPE idade IS NUMBER;
  vIdadeHumana idade(3);
  vIdadeRochas idade(20);
  SUBTYPE Word is CHAR(20);
  verb Word;
  other Word;
  more Word;
BEGIN

END;

-- OFFTOPIC - Criando tipos de dados no Oracle
TYPE
CREATE OR REPLACE TYPE <nome>
  AS OBJECT (
  
);

-- esquentando o cerebro
CREATE OR REPLACE TYPE full_name_type
  AS OBJECT(
    First_name   varchar(30),
    Last_name    varchar(30)
);

CREATE OR REPLACE TYPE full_mailing_address_type 
  AS OBJECT(
  Street   varchar(80),
  City     varchar(80),
  State    char(2),
  Zip      char(9)
);

CREATE TABLE customer(
  full_name full_name_type,
  full_address full_mailing_address_type
);

-- Inserindo registros
INSERT INTO customer(full_name, full_address)
  VALUES(
    full_name_type('Edgar', 'Sandi'),
    full_mailing_address_type('Av. Nelson DAvila', 'SJC', 'SP', '12200-000')
  );

SELECT c.full_name.first_name FROM customer c;

-- ESCOPO de variáveis
DECLARE
  x1 number := 5;
BEGIN
  DECLARE
    x2 number := 7;
  BEGIN
    DECLARE
      x3 number := 9;
    BEGIN
      dbms_output.put_line(x3);
    END;
    dbms_output.put_line(x2);
  END;
  dbms_output.put_line(x1);
END;

-- quiz
DECLARE
  x1 number := 5;
BEGIN
  x1 := x1 + 3;
  dbms_output.put_line('ETAPA 1 = ' || x1);  -- 8
  DECLARE
    x2 number := 7;
    BEGIN
      x1 := x1 + x2;
      dbms_output.put_line('ETAPA 2 = ' || x1);  -- 15
      DECLARE
        x1 number := 9;
      BEGIN
        x1 := x1 + x2 - 3;
        dbms_output.put_line('ETAPA 3 = ' || x1); -- 13
      END;
      x1 := x1 - 1;
      dbms_output.put_line('ETAPA 4 = ' || x1); -- 14
    END;
    dbms_output.put_line('ETAPA 5 = ' || x1); -- 14
END;

-- CASE
DECLARE
  vPartido varchar(10) := 'PSOL';
BEGIN
  CASE vPartido
      WHEN 'PT' THEN
        dbms_output.put_line('PT');
      WHEN 'PSDB' THEN
        dbms_output.put_line('PSDB');
      WHEN 'PMDB' THEN
        dbms_output.put_line('PMDB');
      WHEN 'PSOL' THEN
        dbms_output.put_line('PSOL');
      ELSE
        dbms_output.put_line('NA');
  END CASE;
END;


--- Estruturas de repetição
ESCOPO
LOOP

EXIT WHEN <condicao>

END LOOP;

-- exemplo contador
DECLARE
  x number := 1;
BEGIN
  dbms_output.put_line('Inicio do LOOP');
  LOOP
    dbms_output.put_line('x = ' || x);
    EXIT WHEN x = 5;
    x := x + 1;
  END LOOP;
  dbms_output.put_line('Final do LOOP');
END;

-- WHILE
ESCOPO
WHILE <condicao> LOOP
  <comandos>
END LOOP;

DECLARE
  x number := 1;
BEGIN
  dbms_output.put_line('Inicio do LOOP');
  WHILE x <= 10 LOOP
    dbms_output.put_line('x = ' || x);
    x := x + 1;
  END LOOP;
  dbms_output.put_line('Final do LOOP');
END;

-- Lab03: - Pl/SQL
-- Se você guardar R$ 120,00 por mês na poupança, 
    -- quando você terá 1 milhão de reais?
   -- rendimento:  0.5% => 0.005 => 1.005
   -- fórmula: total = (total + deposito) * rendimento

DECLARE
  vMensal number(8,2) := 120.00;
  vRendimento number(6,3) := 1.005;
  vSaldo number(10,2) := 0;
  vMeses number := 0;
BEGIN
  WHILE vSaldo <= 1000000 LOOP
    vMeses := vMeses + 1;
    vSaldo := (vSaldo + vMensal) * vRendimento;
  END LOOP;
  dbms_output.put_line('Meses: ' || vMeses);
  dbms_output.put_line('Anos: ' || vMeses/12);
  dbms_output.put_line('Anos: ' || ROUND(vMeses/12,0));
  dbms_output.put_line('Anos: ' || FLOOR(vMeses/12));
  dbms_output.put_line('Anos: ' || CEIL(vMeses/12));
  dbms_output.put_line('Anos: ' || TRUNC(vMeses/12));
  dbms_output.put_line('Saldo final: ' || vSaldo);
END;

-- FOR
FOR <var> IN <inicio> .. <fim> LOOP

END LOOP;

-- exemplo crescente
BEGIN
  dbms_output.put_line('Inicio do LOOP');
  FOR x IN 1 .. 10 LOOP
    dbms_output.put_line('x = ' || x);
  END LOOP;
  dbms_output.put_line('Final do LOOP');
END;

-- exemplo decrescente
BEGIN
  dbms_output.put_line('Inicio do LOOP');
  FOR x IN REVERSE 1 .. 10 LOOP
    dbms_output.put_line('x = ' || x);
  END LOOP;
  dbms_output.put_line('Final do LOOP');
END;

-- Lab 04: - FOR
-- Quanto você terá na poupança aplicando R$ 50.00 por mês durante 3 anos?
   -- rendimento 0.5%

-- Resposta: - FOR
DECLARE
  vMensal number(8,2) := 50.00;
  vRendimento number(6,3) := 1.005;
  vSaldo number(10,2) := 0;
  vMeses number := 36;
BEGIN
  FOR vMes IN 1 .. vMeses LOOP
    vSaldo := (vSaldo + vMensal) * vRendimento;
    dbms_output.put_line(vMes || ' --> ' ||vSaldo);
  END LOOP;
  dbms_output.put_line('Meses: ' || vMeses);
  dbms_output.put_line('Saldo R$ ' ||vSaldo);
END;

-- SQL dentro do PL/SQL
  --- Dentro do Pl/SQL você usa do DML
  --- o SELECT dentro do PL/SQL tem limitações
     -- Ele não pode retornar mais do que 1 registro
     -- Ele não pode retornar um grid de registros
DECLARE
  vCidade varchar(50);
BEGIN
  SELECT nm_cidade INTO vCidade FROM tb_cidades WHERE cd_cidade = 2;
  dbms_output.put_line('Cidade : ' || vCidade);
END;

SELECT nm_cidade FROM tb_cidades;

-- Exemplo
-- Dado um código diga o aluno /  a aluna realizou X inscricoes
DECLARE
  vCodigo number := 1;
  vNome varchar(50);
  vSexo char(1);
  vQtde number;
BEGIN
  SELECT nm_aluno, tx_sexo INTO vNome, vSexo     FROM tb_alunos     WHERE cd_aluno = vCodigo;
  IF vSexo = 'M' THEN
    dbms_output.put_line('O aluno : ' || vNome);
  ELSE 
    dbms_output.put_line('A aluna : ' || vNome);
  END IF;
  SELECT COUNT(*) INTO vQtde     FROM tb_inscricoes     WHERE cd_aluno = vCodigo;  
  dbms_output.put_line('Realizou ' || vQtde || ' inscricoes');
END;

-- tabelas e dados da modelagem escola:
     --  http://bit.ly/1yuoxif
-- Lab 05: SQL dentro do PL/SQL
-- Criar um PL/SQL onde você informará o código da turma: 4
-- Mostrar as informações:
   -- Nome do curso
   -- Data de início
   -- Data de fim
   -- Capacidade
   -- Quantidade de inscritos
   -- Vagas na turma
RENAME TABLE <nome> TO <novo nome>;


DECLARE
  vCodTurma number := 4;
  vNomeCurso varchar(50);
  vInicio date;
  vFim date;
  vCapacidade number;
  vInscritos number;
BEGIN
  SELECT dt_inicio, dt_fim, cd_curso, cd_sala, nm_curso, vl_capacidade
      INTO vInicio, vFim, vCodCurso, vCodSala, vNomeCurso, vCapacidade
    FROM tb_turmas
    NATURAL JOIN tb_cursos
    NATURAL JOIN tb_salas
    WHERE cd_turma = vCodTurma;
    
  SELECT COUNT(*) INTO vInscritos
    FROM tb_inscricoes
    WHERE cd_turma = vCodTurma;
  
  dbms_output.put_line('Nome do curso: ' || vNomeCurso);
  dbms_output.put_line('Data de inicio: ' || TO_CHAR(vInicio,'dd/mm/yyyy') );
  dbms_output.put_line('Data de fim: ' ||  TO_CHAR(vFim,'dd/mm/yyyy'));
  dbms_output.put_line('Capacidade: ' || vCapacidade);
  dbms_output.put_line('Inscritos: ' || vInscritos);
  dbms_output.put_line('Vagas: ' || (vCapacidade - vInscritos));
END;

-- HOMEWORKS 24/01/2015
1. Imprima o quadrado de 8 * por 10 * usando for e while (no mesmo bloco).
**********
**********
**********
**********
**********
**********
**********
********** 

-- resposta:
DECLARE
 j NUMBER;
 i NUMBER;
 ch VARCHAR2(80) := '';
BEGIN
 FOR j IN 1.. 8 LOOP
    i := 1;
    WHILE i <= 10 LOOP
        ch := ch || '*';
        i := i + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(ch);
    ch := '';
  END LOOP;
END;
	
2. Imprimir a tabuada do 5
exemplo:
	5 * 1 = 5
	5 * 2 = 10
	...
-- resposta:
BEGIN
  FOR i IN 1 .. 10 LOOP
    DBMS_OUTPUT.PUT_LINE(5 || ' * ' || i || ' = ' || 5 * i);
  END LOOP;
END;

3. Imprimir a tabuada do x:
Exemplo: 
	x := 5 
Saída na tela: 
	1 * 5 = 5 
	2 * 5 = 10 
	3 * 5 = 15
	...
-- resposta:
DECLARE
  x int := 7;
BEGIN
  FOR i IN 1 .. 10 LOOP
  END LOOP;
END;

4. Imprimir uma tabuada do 1 * 10 ao 10 * 10
exemplo:
	1 * 1 = 1
	1 * 2 = 2
	...
	10 * 9 = 90
	10 * 10 = 100
-- resposta:
DECLARE
 j NUMBER;
 i NUMBER;
BEGIN
 FOR j IN 1 .. 10 LOOP
    FOR i IN 1 .. 10 LOOP
      DBMS_OUTPUT.PUT_LINE(j || ' * ' || i || ' = ' || j * i);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END;

5. Anos bissexto entre os anos de 2000 e 2100
    (ano bissexto é quando é possível dividí-lo por 4 mas não por 100)
       ou
    (quando for possível dividí-lo por 400)
-- resposta:
BEGIN
  FOR i IN 2000 .. 2100 LOOP
    IF ( MOD(i, 4) = 0 AND MOD(i, 100) != 0 ) OR MOD(i, 400) = 0 THEN
      dbms_output.put_line('Ano bissexto encontrado: ' || i);
    ELSE 
      dbms_output.put_line('Não é um ano bissexto: ' || i);
    END IF;
  END LOOP;
END;

6. Reescreva o código abaixo eliminando os IF desnecessarios
(exercício apenas lógico)
...
	IF salario < 1000 THEN
		bonus := 2000;
	ELSE 
		IF salario < 2000 THEN
			bonus := 1500;
		ELSE
			IF salario < 4000 THEN
				bonus := 1000;
			ELSE
				bonus := 500;
			END IF;
		END IF;
	END IF;

-- resposta:
bonus := 500;

IF salario < 1000 THEN
  bonus := 2000;
ELSIF salario > 1000 AND salario < 2000 THEN
  bonus := 1500;
ELSIF salario > 2000 AND salario < 4000 THEN
  bonus := 1000;
END IF;

IF salario < 1000 THEN
  bonus := 2000;
ELSIF salario BETWEEN 1000 AND 2000 THEN
  bonus := 1500;
ELSIF salario BETWEEN 2001 AND 4000 THEN
  bonus := 1000;
END IF;

7. Qual será o valor de a?
DECLARE
  a number := 0;
BEGIN 
  FOR x IN REVERSE 12 .. 1 LOOP
    a := a + 1;
  END LOOP;  
  dbms_output.put_line(a);
END;
-- resposta:
0

-- OPEN YOUR MIND - Abram as suas mentes
-- CURSOR
--- Cursor é um recurso que disponibiliza capturar <n> linhas de um SELECT

-- 1. Declaração do CURSOR
-- 2. Abrir o CURSOR
-- 3. Iterar sobre o CURSOR
-- 4. Captura as linhas do CURSOR
-- 5. Fechar o CURSOR

DECLARE
  CURSOR cAlunas IS
    SELECT nm_aluno, dt_nascimento
      FROM tb_alunos
      WHERE tx_sexo = 'F';
  vNome varchar(50);
  vData date;
BEGIN
  OPEN cAlunas;
  LOOP
    FETCH cAlunas INTO vNome, vData;
    EXIT WHEN cAlunas%notfound;
    dbms_output.put_line('Nome: ' || vNome || 
      ' - ' || TO_CHAR(vData, 'dd/mm/yyyy'));
  END LOOP;
  CLOSE cAlunas;
END;

-- Usando CURSOR com o WHILE
DECLARE
  CURSOR cAlunas IS
    SELECT nm_aluno, dt_nascimento
      FROM tb_alunos
      WHERE tx_sexo = 'F';
  vNome varchar(50);
  vData date;
BEGIN
  OPEN cAlunas;
  FETCH cAlunas INTO vNome, vData;
  WHILE (cAlunas%found) LOOP
    dbms_output.put_line('Nome: ' || vNome || 
      ' - ' || TO_CHAR(vData, 'dd/mm/yyyy'));
    FETCH cAlunas INTO vNome, vData;
  END LOOP;
  CLOSE cAlunas;
END;

-- Usando CURSOR com o FOR
DECLARE
  CURSOR cAlunas IS
    SELECT nm_aluno, dt_nascimento
      FROM tb_alunos
      WHERE tx_sexo = 'F';
BEGIN
  FOR vReg IN cAlunas LOOP
    dbms_output.put_line('Nome: ' || vReg.nm_aluno || 
      ' - ' || TO_CHAR(vReg.dt_nascimento, 'dd/mm/yyyy'));  
  END LOOP;
END;

-- Usando CURSOR com o SUPER FOR
BEGIN
  FOR vReg IN (SELECT nm_aluno, dt_nascimento FROM tb_alunos WHERE tx_sexo = 'F') LOOP
    dbms_output.put_line('Nome: '||vReg.nm_aluno ||' - '||TO_CHAR(vReg.dt_nascimento, 'dd/mm/yyyy'));  
  END LOOP;
END;

-- Lab: 06
Criar um resultado:
-- Categoria: <codigo> - <nome da categoria>
   -- Curso: <nome do curso> - <carga horária>
Saída DBMS
01 - Banco de dados
    Oracle - 24h
    Mysql  - 24h
02 - Programação
    PHP - 32h
    Java 32h
    Python 8h
03 - Design   
    Photoshop - 32h
    InDesign - 32h

-- Conteúdo do curso até agora
http://goo.gl/0S3fY3

-- resposta
DECLARE 
  CURSOR cCategorias IS
    SELECT cd_categoria, nm_categoria
      FROM tb_categorias;
  vCod number;
  vCategoria varchar(50);
  CURSOR cCursos IS
    SELECT nm_curso, vl_ch
      FROM tb_cursos
      WHERE cd_categoria = vCod;
  vCurso varchar(50);
  vCH number;
BEGIN
  OPEN cCategorias;
  LOOP
    FETCH cCategorias INTO vCod, vCategoria;
    EXIT WHEN cCategorias%notfound;
    dbms_output.put_line('Categoria: ' || vCod ||' - ' || vCategoria);
    
    OPEN cCursos;
    LOOP
      FETCH cCursos INTO vCurso, vCH;
      EXIT WHEN cCursos%notfound;
      dbms_output.put_line('     - ' || vCurso || ' - ('||vCH||')' );
    END LOOP;
    CLOSE cCursos;
    
  END LOOP;
  CLOSE cCategorias;
END;

-- Usando o SUPER FOR
BEGIN
  FOR vRegCat IN (SELECT cd_categoria, nm_categoria FROM tb_categorias) LOOP
    dbms_output.put_line('Categoria: ' || vRegCat.cd_categoria ||' - ' || vRegCat.nm_categoria);
    FOR vRegCur IN (SELECT nm_curso, vl_ch FROM tb_cursos WHERE cd_categoria = vRegCat.cd_categoria) LOOP
      dbms_output.put_line('     - ' || vRegCur.nm_curso || ' - ('||vRegCur.vl_ch||')' );
    END LOOP;
  END LOOP;
END;

-- lab 7:
-- Alterar a tabela Alunos adicionando uma coluna chamada vl_salario
-- Setar o salário de 1000 reais para todos

ALTER TABLE tb_alunos ADD vl_salario NUMBER(14,5) DEFAULT 0;
UPDATE tb_alunos SET vl_salario = 1000; -- nuuuunca façam isso na vida real
-- monkey mode:
UPDATE tb_alunos SET vl_salario = vl_salario + 230; -- nuuuunca façam isso na vida real

-- Com cursor
-- Atualizar o salário de todos os alunos em 230 reais * cd_aluno
-- resposta
DECLARE
  CURSOR cAlunos IS  -- 1000 alunos  567 => salario 500
    SELECT * 
      FROM tb_alunos
      FOR UPDATE OF vl_salario WAIT 60;
  vValor number(14,2) := 230;
BEGIN
  FOR vReg IN cAlunos LOOP
   -- updated 567 => salario 1000
    UPDATE tb_alunos
      SET vl_salario = vl_salario + (vValor * vReg.cd_aluno)
      -- WHERE cd_aluno = vReg.cd_aluno;
      WHERE CURRENT OF cAlunos;
  END LOOP;
END;

-- salario 567 ?
SELECT * FROM tb_alunos;


FOR UPDATE; -- qualquer alteração em toda a tabela só ocorrerá de pois de liberado os registros
FOR UPDATE WAIT x; -- x = segundos, toda a tabela será liberada depois de x segundos
FOR UPDATE OF <coluna> WAIT x; -- x =segundos, a coluna será liberada depois de x segundos

-- Tratamento de erros, erros mais comuns
   -- SELECT INTO que não retorne linhas
   -- SELECT INTO que retorne 2 ou mais linhas
   -- CASE sem DEFAULT
   -- Abrir/Fechar o CURSOR mais de uma vez
   -- Dividir um numero por 0 (zero)
   -- Erros de violação de CONSTRAINTS (PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE)

DECLARE
  x number := 100;
  y number := 20;
  total number(3);
BEGIN
  total := x * y;
  dbms_output.put_line('Total: ' || total);
EXCEPTION
  WHEN zero_divide THEN
    dbms_output.put_line('OOOOps, a divisão por zero non ecziste');
  WHEN value_error THEN
    dbms_output.put_line('OOOOps, ocorreu um erro com as variáveis');
  WHEN others THEN
    dbms_output.put_line('OOOOps, ocorreu algum erro');
END;

-- http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/07_errs.htm

-- Criando tipos de exceptions
DECLARE
  vIdade number := 15;
  eAcesso_negado exception;
BEGIN
  IF vIdade < 18 THEN
    RAISE eAcesso_negado;
  END IF;
EXCEPTION
  WHEN eAcesso_negado THEN
    dbms_output.put_line('Acesso negado');
  WHEN other THEN
    dbms_output.put_line('Corram para as montanhas!');
END;

-- QUIZ:
DECLARE
  A exception;      
  B exception;     
  C exception;
BEGIN
  BEGIN
    BEGIN
      BEGIN
        raise A;
      EXCEPTION
        WHEN C THEN    dbms_output.put_line('Error 1');
      END;
    EXCEPTION
      WHEN A OR B THEN    dbms_output.put_line('Error 2');
        raise NO_DATA_FOUND;  
    END;
  EXCEPTION
    WHEN B OR C THEN     dbms_output.put_line('Error 3');
  END;
EXCEPTION
  WHEN OTHERS THEN     dbms_output.put_line('Unknown Error');
END;

-- Resposta
-- Error 2
-- Unknown Error

-- FUNCOES
   -- Função retorna um valor
   -- Função pode ser utilizada dentro do DML e do DQL
   -- Função é utilizada para criar programas auxiliares

-- ESCOPO
CREATE [OR REPLACE] FUNCTION <nome da função>([params])
  RETURN <tipo de dado>
  AS
     <variaveis>
  BEGIN
     <codigo>
  END;

-- IMC - Índice de Massa Corporal
-- imc = peso / ( aultura * altura)

CREATE OR REPLACE FUNCTION fn_imc(pPeso number, pAltura number)
  RETURN varchar
  AS
    imc number;
  BEGIN
    imc := pPeso / (pAltura * pAltura);
    IF imc > 24 THEN
      RETURN 'Você está acima do peso ideal';
    ELSE
      RETURN 'Você está com o peso ideal';
    END IF;
  END;

SELECT fn_imc(80, 1.90) FROM dual;

-- Lab 07
-- Função para converter graus celsius para Fahrenheit
-- fn_fahrenheit(pCelsius number)
      -- fórmula 'ºF = (ºC * 1.8) + 32'
CREATE OR REPLACE FUNCTION fn_fahrenheit(pCelsius number)
  RETURN number
  AS
  BEGIN
    RETURN (pCelsius * 1.8) + 32;
  END;

SELECT fn_fahrenheit(44) FROM dual;  

SELECT nm_sala, vl_capacidade, fn_fahrenheit(vl_capacidade) celsius
  FROM tb_salas;

UPDATE tb_alunos SET vl_salario = fn_fahrenheit(vl_salario)
  WHERE cd_aluno = 555;

-- Lab 08 - Criar uma função para calculo de comissão
   -- Valor de vendas no mês
   -- Tempo de empresa (meses)
SELECT fn_comissao(<valor>, <tempo de empresa>);
-- Regras de comissão:
   -- Se o total de vendas:
      -- for menor que R$ 1000.00, comissão 0.5% sobre as vendas
      -- for menor que R$ 2500.00, comissão 0.75% sobre as vendas
      -- for menor que R$ 5000.00, comissão 0.75% + R$ 250.00 de bônus sobre as vendas
      -- for maior que R$ 5000.00, comissão 1% + R$ 350.00 de bônus sobre as vendas
-- Bônus extra
   -- Se o vendedor:
      -- tem menos que 1 ano de empresa: 0% sobre as vendas
      -- tem menos que 2 anos de empresa: 0.20% sobre as vendas
      -- tem menos que 5 anos de empresa: 0.40% sobre as vendas
      -- tem mais que 5 anos de empresa: 0.60% sobre as vendas
      
SELECT fn_comissao(6000, 29) FROM dual;

CREATE OR REPLACE FUNCTION fn_comissao(pValor number, pTempo number) 
	RETURN number
	AS
		vComissaoValor number;
		vComissaoTotal number;
  BEGIN
		CASE
			WHEN pValor < 1000 THEN
				vComissaoValor := pValor * 0.005;
			WHEN pValor BETWEEN 1000 AND 2500 THEN
				vComissaoValor := pValor * 0.0075;
			WHEN pValor BETWEEN 2500 AND 5000 THEN
				vComissaoValor := (pValor * 0.0075) + 250;
			ELSE
				vComissaoValor := (pValor * 0.01) + 350;
		END CASE;
		CASE
			WHEN pTempo < 12 THEN
				vComissaoTotal := vComissaoValor;
			WHEN pTempo BETWEEN 12 AND 24 THEN
				vComissaoTotal := vComissaoValor + (pValor * 0.002);
			WHEN pTempo BETWEEN 24 AND 60 THEN
				vComissaoTotal := vComissaoValor + (pValor * 0.004);
			ELSE
				vComissaoTotal := vComissaoValor + (pValor * 0.006);
		END CASE;
		
		RETURN vComissaoTotal;
	END;
  
SELECT fn_comissao(6000, 29) FROM dual;

SELECT ((6000 * 0.01) + (6000 * 0.004)) + 350 FROM dual;
  
SELECT (6000 * (1/100) + 350)) * 1.004 FROM dual;
SELECT fn_comissao(6000, 29) FROM dual;

-- Procedures - Stored Procedures
   - Procedure é utilizada para manipulação de dados

CREATE [OR REPLACE] PROCEDURE <nome> ([PARAM] [IN / OUT] <tipo>)
  AS
    <variaveis>
  BEGIN
    <codigo>
  END;

-- Exemplo
-- qual a qtde de alunos de um sexo M ou F
sp_qtd_alunos('M');
CREATE OR REPLACE PROCEDURE sp_qtd_alunos(pSexo IN char)
  AS
    vQtd OUT number;
  BEGIN
    SELECT COUNT(*) INTO pQtd
      FROM tb_alunos
      WHERE tx_sexo = pSexo;
    RETURN vQtd;
  END;
  
DECLARE
  vQtd number;
BEGIN
  dbms_output.put_line('ANTES: Foram encontrados '||vQtd||' alunos');
  sp_qtd_alunos('M', vQtd);
  dbms_output.put_line('DEPOIS: Foram encontrados '||vQtd||' alunos');
END;

-- Exemplo pratico
-- Procedure sp_add_sala(cod. sala, nome sala, capacidade)
   -- Verificar se o código da sala já existe
   -- Verificar se o nome da sala já existe
   -- Verifica se a capacidade está entre 2 e 20 alunos
CREATE OR REPLACE 
  PROCEDURE sp_add_sala(pCodSala char, pNmSala varchar, pVlCapacidade number)
  AS
    eVlCapacidadeInvalida exception;
    eSalaJaExiste exception;
    vNomeSala tb_salas.nm_sala%type;
  BEGIN
    IF pVlCapacidade NOT BETWEEN 2 AND 20 THEN
      RAISE eVlCapacidadeInvalida;
    END IF;
    
    BEGIN
      SELECT nm_sala INTO vNomeSala FROM tb_salas WHERE nm_sala = pNmSala;
      IF vNomeSala IS NOT NULL THEN
        RAISE eSalaJaExiste;
      END IF;
    EXCEPTION
      WHEN no_data_found THEN
        null;
    END;
    
    INSERT INTO tb_salas(cd_sala, nm_sala, vl_capacidade)
      VALUES (pCodSala, pNmSala, pVlCapacidade);
  EXCEPTION
    WHEN eVlCapacidadeInvalida THEN
      dbms_output.put_line('Capacidade Invalida');
    WHEN eSalaJaExiste THEN
      dbms_output.put_line('Sala ja existe');
    WHEN dup_val_on_index THEN
      dbms_output.put_line('Código da sala ja existe');
  END;
   
BEGIN
  sp_add_sala('S01','Sala 11', 20);
END;
   
SELECT * FROM tb_salas;

2013nosaes*
http://goo.gl/0S3fY3 - PL/SQL
http://bit.ly/1yuoxif - Oracle Essencial
-- Lab09 - 
Montar uma procedure sp_add_turma
    -- params:
        cod. da turma
        dt. inicio
        qtde de aulas
        periodo
        nm do instrutor
        nm curso
        cod sala
    -- Verificações:
       Instrutor existe?
       Curso existe?
       Data de início é maior que a data atual?
       O perído é válido (M, T ou N)?
       O cod. turma já existe?  -- dup_val_on_index
       Sala existe? -- constraint_violation -- FK. -02291
       -- como bonus... utilize exceptions ao invés de 
          -- dbms_output.put_line nas verificações
          
-- Lab09 - RESPOSTA
-- Montar uma procedure sp_add_turma
CREATE OR REPLACE PROCEDURE sp_add_turma(pCod number, pInicio date, pAulas number, 
  pPeriodo char, pInstrutor varchar, pCurso varchar, pSala char)
  AS
  vCodInstrutor number;
  vCodCurso number;
  eInstrutor exception;
  eCurso exception;
  eInicio exception;
  ePeriodo exception;
  constraint_violation exception;
  pragma exception_init(constraint_violation, -02291);
--  constraint_violation2 exception;
--  pragma exception_init(constraint_violation2, -022XX);
BEGIN
  IF pInicio < SYSDATE THEN
    RAISE eInicio;
  END IF;
  
  IF pPeriodo NOT IN ('M', 'T', 'N') THEN
    RAISE ePeriodo;
  END IF;

  BEGIN
    SELECT cd_instrutor INTO vCodInstrutor FROM tb_instrutores WHERE nm_instrutor = pInstrutor;
  EXCEPTION
    WHEN no_data_found THEN
      RAISE eInstrutor;
  END;

  BEGIN
    SELECT cd_curso INTO vCodCurso FROM tb_cursos WHERE nm_curso = pCurso;
  EXCEPTION
    WHEN no_data_found THEN
      RAISE eCurso;
  END;
  
  INSERT INTO tb_turmas(cd_turma,dt_inicio,dt_fim,tx_periodo,cd_instrutor,cd_curso,cd_sala)
    VALUES (pCod, pInicio, pInicio + pAulas - 1, pPeriodo, vCodInstrutor, vCodCurso, pSala); 

EXCEPTION
  WHEN eInstrutor THEN
    dbms_output.put_line('O instrutor não existe');
  WHEN eCurso THEN
    dbms_output.put_line('O curso não existe');
  WHEN eInicio THEN
    dbms_output.put_line('Data de início menor que a data atual');
  WHEN ePeriodo THEN
    dbms_output.put_line('Período inválido');    
  WHEN dup_val_on_index THEN
    dbms_output.put_line('Cod. Turma já existe');
  WHEN constraint_violation THEN
    dbms_output.put_line('A sala não existe');  
END;
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

BEGIN
sp_add_turma (7,'2015-10-10',8,'M','Pedro','PHP','S02');
END;

SELECT * FROM tb_turmas;

SELECT SYSDATE FROM DUAL;

SELECT SYSDATE, SYSDATE + 9 FROM dual;
conn sys as sysdba;
senha: season
create database teste
user sys identified by season
user system identified by season
DEFAULT tablespace;

-- WOMEWORK
1. Escreva uma função em PL/SQL chamada TamanhoString, que receba um texto como parâmetro e retorne seu tamanho.
2. Escreva uma função em PL/SQL chamada ConverteParaMaiuscula, que receba um texto como parâmetro e retorne esse texto em
maiúsculo.
3. Criar uma procedure que deverá receber o código de um cliente e a partir deste dado imprimir o seu
nome, e seu e‐mail. Os dados deverão ser obtidos a partir de uma tabela chamada CLIENTE com as
seguintes colunas (COD_CLI,NOME_CLI,EMAIL_CLI). Exemplo:
CLIENTE
-----------------------------------------------
COD_CLI NOME_CLI EMAIL_CLI
-----------------------------------------------
10 BEATRIZ BERNARDES bb@dominio.com..br
-----------------------------------------------
CREATE TABLE CLIENTE (
COD_CLI NUMBER(4) PRIMARY KEY,
NOME_CLI VARCHAR2(30),
EMAIL_CLI VARCHAR2(30));
INSERT INTO CLIENTE VALUES (10,'BEATRIZ BERNARDES','bb@dominio.com.br');

4. Criar uma procedure que receberá um RA, um NOME e quatro notas conforme a sequência:
(RA,NOME,A1,A2,A3,A4). A partir destes valores deverá efetuar o cálculo da média somando o maior valor
entre A1 e A2 às notas A3 e A4 e dividindo o valor obtido por três (achando a média). Se a média for menor
que 6 (seis) o aluno estará REPROVADO e se a média for igual ou superior a 6 (seis) o aluno estará
APROVADO. A procedure deverá inserir os valores acima numa tabela denominada ALUNO com as
seguintes colunas RA,NOME,A1,A2,A3,A4,MEDIA,RESULTADO. Exemplo:
ALUNO
--------------------------------------------------------
RA NOME A1 A2 A3 A4 MEDIA RESULTADO
--------------------------------------------------------
123 ANTONIO ALVES 6.5 3.5 9.5 5.0 7.0 APROVADO
--------------------------------------------------------
CREATE TABLE ALUNO (
RA NUMBER(9),
NOME VARCHAR2(30),
NOTA1 NUMBER(3,1),
NOTA2 NUMBER(3,1),
NOTA3 NUMBER(3,1),
NOTA4 NUMBER(3,1),
MEDIA NUMBER(3,1),
RESULTADO VARCHAR2(15));

5. Uma empresa oferece um bônus a seus funcionários com base no lucro liquido (tabela LUCRO) obtido
durante o ano e no valor do salário do funcionário (tabela SALARIO). O bônus é calculado conforme a
seguinte formula: BONUS = LUCRO * 0.01 + SALARIO * 0.05. Crie uma procedure que receba o ano (tabela
LUCRO) e número de matricula do funcionário e devolva (imprima) o valor do seu respectivo bônus.
LUCRO
-----------------
ANO VALOR
-----------------
2007 1200000
2008 1500000
2009 1400000
-----------------
SALARIO
-----------------
MATRICULA VALOR
-----------------
1001 2500
1002 3200
-----------------
CREATE TABLE LUCRO (
ANO NUMBER(4),
VALOR NUMBER(9,2));
CREATE TABLE SALARIO (
MATRICULA NUMBER(4),
VALOR NUMBER(7,2));
INSERT INTO LUCRO VALUES (2007,1200000);
INSERT INTO LUCRO VALUES (2008,1500000);
INSERT INTO LUCRO VALUES (2009,1400000);
INSERT INTO SALARIO VALUES (1001,2500);
INSERT INTO SALARIO VALUES (1002,3200); 


-- WOMEWORK - Respostas
1. Escreva uma função em PL/SQL chamada TamanhoString, que receba um texto como parâmetro e retorne seu tamanho.

CREATE OR REPLACE FUNCTION TamanhoString(pString varchar)
  RETURN number
  AS
    vTamanho number;
  BEGIN
    vTamanho := LENGTH(pString);
    RETURN vTamanho;
  END;

SELECT TamanhoString('Teste') FROM dual;

2. Escreva uma função em PL/SQL chamada ConverteParaMaiuscula, que receba um texto como parâmetro e retorne esse texto em
maiúsculo.
CREATE OR REPLACE FUNCTION ConverteParaMaiuscula(pTexto varchar)
  RETURN varchar
  AS
    vTexto varchar(50);
  BEGIN
    vTexto := UPPER(pTexto);
    RETURN vTexto;
  END;

SELECT ConverteParaMaiuscula('Edgar') FROM dual;


3. Criar uma procedure que deverá receber o código de um cliente e a partir deste dado imprimir o seu
nome, e seu e‐mail. Os dados deverão ser obtidos a partir de uma tabela chamada CLIENTE com as
seguintes colunas (COD_CLI,NOME_CLI,EMAIL_CLI). 

CREATE OR REPLACE PROCEDURE MOSTRA_CLIENTE(pCod NUMBER) 
  IS
    vCliente CLIENTE%rowtype;
  BEGIN
    SELECT * INTO vCliente FROM CLIENTE WHERE COD_CLI = pCod;
    DBMS_OUTPUT.PUT_LINE(vCliente.NOME_CLI || ' - ' || vCliente.EMAIL_CLI);
  END MOSTRA_CLIENTE;

BEGIN
  MOSTRA_CLIENTE(10);
END;

SELECT * FROM CLIENTE;

4. Criar uma procedure que receberá um RA, um NOME e quatro notas conforme a sequência:
(RA,NOME,A1,A2,A3,A4).
CREATE OR REPLACE PROCEDURE CALCULA_MEDIA(
  pRA NUMBER,
  pNome VARCHAR,
  pN1 NUMBER,
  pN2 NUMBER,
  pN3 NUMBER,
  pN4 NUMBER)
  AS
    vMaior NUMBER(3,1);
    vMedia NUMBER(3,1);
    vResultado VARCHAR(15);
  BEGIN
    IF pN1 > pN2 THEN
      vMaior := pN1;
    ELSE
      vMaior := pN2;
    END IF;
    
    vMedia := (vMaior + pN3 + pN4) / 3;
    
    IF vMedia < 6 THEN
      vResultado := 'REPROVADO';
    ELSE 
      vResultado := 'APROVADO';
    END IF;
    
    INSERT INTO ALUNO VALUES(pRA, pNome, pN1, pN2, pN3, pN4, vMedia, vResultado);
  END;

SELECT * FROM ALUNO;
123 ANTONIO ALVES 6.5 3.5 9.5 5.0 7.0 APROVADO

BEGIN
  CALCULA_MEDIA(123, 'ANTONIO ALVES',6.5, 3.5, 9.5,5.0);
END;

5. Uma empresa oferece um bônus a seus funcionários com base no lucro liquido (tabela LUCRO) obtido
durante o ano e 
CREATE OR REPLACE PROCEDURE CALCULA_BONUS(
    pAno LUCRO.ANO%TYPE,
    pMatricula SALARIO.MATRICULA%TYPE)
    AS
      vLucro LUCRO.VALOR%TYPE;
      vSalario SALARIO.VALOR%TYPE;
      vBonus NUMBER(7,2);
    BEGIN
      SELECT VALOR INTO vLucro FROM LUCRO 
        WHERE ANO = pAno;
      SELECT VALOR INTO vSalario FROM SALARIO
        WHERE MATRICULA = pMatricula;
      vBonus := (vLucro * 0.01) + (vSalario * 0.05);
      dbms_output.put_line('Valor do Bonus: ' || vBonus);
    END;

SELECT VALOR FROM LUCRO WHERE ANO = 2007;

SELECT VALOR FROM SALARIO WHERE MATRICULA = 1001;

BEGIN
  CALCULA_BONUS(2007,1002);
END;



-- Criando exceptions para códigos de erro
DECLARE
  eConstraintException exception;
  pragma exception_init(eConstraintException, -02291);
BEGIN
  INSERT INTO tb_cursos (cd_curso, nm_curso, vl_ch, cd_categoria)
    VALUES(20,'Curso fake', '40', 89988888);
EXCEPTION
  WHEN eConstraintException THEN
    dbms_output.put_line('A categoria não existe');
    dbms_output.put_line('Usuario: ' || user);
    dbms_output.put_line('Data: ' || TO_CHAR(sysdate, 'dd/mm/yyyy'));
    dbms_output.put_line('Erro: ' || sqlcode);
    dbms_output.put_line('Msg: ' || sqlerrm);
  WHEN value_error THEN 
    dbms_output.put_line('Erro' || TO_CHAR(SQLERRM(-01438)));
    dbms_output.put_line('Erro' || TO_CHAR(SQLERRM(-06512)));
END;

BEGIN
    dbms_output.put_line('Erro' || TO_CHAR(SQLERRM(-01438)));
END;

-- Packages
  -- é um grupo de n procedures, n funcoes, n variaveis, n tipos
  -- facilita a distribuição e controle de versão
  -- Funcionalidade nova: Permite variaveis instanciadas na sessao
  
-- Criada em dois passos
   -- 1o - Declação - cabeçalho - header
   -- 2o - Programação - corpo - body
   
Package pkg_calculadora
 procedure soma
 procedure subtrai
 function saldo

-- ESCOPO HEADER
CREATE OR REPLACE PACKAGE <nome>
AS

END;

-- ESCOPO BODY
CREATE OR REPLACE PACKAGE BODY <nome> 
AS

END;

CREATE OR REPLACE PACKAGE pkg_calculadora 
AS
  vTotal number(14, 2) := 0;  --> variavel da sessao
  PROCEDURE soma(x number);
  PROCEDURE subtrai(x number);
  FUNCTION saldo RETURN NUMBER;
END;

CREATE OR REPLACE PACKAGE BODY pkg_calculadora 
AS
  PROCEDURE soma(x number)
  AS
  BEGIN
    vTotal := vTotal + x;
  END;

  PROCEDURE subtrai(x number)
  AS
  BEGIN
    vTotal := vTotal - x;
  END;

  FUNCTION saldo RETURN NUMBER
  AS
  BEGIN
    RETURN vTotal;
  END;
END;

-- usando o package
-- saldo?
SELECT pkg_calculadora.saldo FROM tb_alunos;

-- somar ?
BEGIN
  pkg_calculadora.soma(100);
END;

-- saldo?
SELECT pkg_calculadora.saldo FROM dual; -- 100

-- subtrair ?
BEGIN
  pkg_calculadora.subtrai(30);
END;

-- saldo?
SELECT pkg_calculadora.saldo FROM dual; -- 70

-- Listas
   -- Vetores / Array
      -- Tabelas indexadas
      -- Tabelas aninhadas
      -- vArray

-- PHP
$array = array(1 => 'Taubate');

-- ESCOPO Tabela Indexada
TYPE <nome da lista> IS TABLE OF <tipo(tamanho)> INDEX BY BINARY_INTEGER;
TYPE <nome da lista> IS TABLE OF tb_alunos;

CREATE OR REPLACE TYPE customer IS TABLE OF customer;


DECLARE
  TYPE listaCidades IS TABLE OF VARCHAR(50) INDEX BY BINARY_INTEGER;
  TYPE listaEstados IS TABLE OF customer%rowtype INDEX BY BINARY_INTEGER;
  vCidades listaCidades;
  vEstados listaEstados;
BEGIN
  vCidades(1) := 'São Paulo';
  vCidades(2) := 'Osasco';
  vCidades(3) := 'SJCampos';
  vCidades(800) := 'Taubaté';
  vCidades(-5) := 'Caçapava';
  vCidades(-5) := 'Jacareí';
  
  dbms_output.put_line('Quantidade de cidades: ' || vCidades.count);
  dbms_output.put_line('Cidade escolhida: ' || vCidades(-5));
  dbms_output.put_line(vEstados(0).full_name.first_name);
END;

SELECT c.full_name.first_name FROM customer c;

DECLARE
  type listaSalas IS TABLE OF tb_salas%rowtype INDEX BY BINARY_INTEGER;
  vSalas listaSalas;
BEGIN
  vSalas(1).cd_sala := 'S08';
  vSalas(1).nm_sala := 'Sala 08';
  vSalas(1).vl_capacidade := 90;

  vSalas(2).cd_sala := 'S09';

  dbms_output.put_line(vSalas(1).cd_sala);
  dbms_output.put_line(vSalas(2).cd_sala);
END;

-- Tabelas aninhadas
   -- indice sequencial, iniciando em 1
   -- necessita ser inicializada

DECLARE
  TYPE listaNumeros IS TABLE OF NUMBER;
  vMegaSena listaNumeros;
BEGIN
  vMegaSena := listaNumeros(50, 19, 30, 40, 29, 33);
  vMegaSena(5) := 7;
  
  --vMegaSena.extend(10, 5);
  --  vMegaSena := listaNumeros(0);
  vMegaSena.extend(1, 1);
  
  vMegaSena(7) := 22;
  
  dbms_output.put_line('Quantidade: ' || vMegaSena.count);
  
  FOR x IN 1 .. vMegaSena.count LOOP
    dbms_output.put_line('Número: ' || x || ' : ' || vMegaSena(x));
  END LOOP;
END;

-- vArray
   -- Tem que ser instanciado
   -- O indice começa com zero
   -- pode determinar o limite maximo para o array

DECLARE
  TYPE listaString IS VARRAY(5) OF VARCHAR(50);
  vCidades listaString := listaString();
BEGIN
  vCidades.extend;
  vCidades(1) := 'Sao Paulo';
  vCidades.extend;
  vCidades(2) := 'Jacarei';

  dbms_output.put_line('Cidade: ' || vCidades.count);
END;

CREATE OR REPLACE TYPE ListaTelefone AS VARRAY(10) OF VARCHAR(20);

ALTER TABLE tb_alunos ADD nr_telefone ListaTelefone;

UPDATE tb_alunos
  SET nr_telefone = ListaTelefone('11 3434-5534','(12) 98844-3434','13 6346-5553')
  WHERE cd_aluno = 1;

DECLARE
  vNomeAluno varchar(50);
  vTelefone ListaTelefone;
BEGIN
  SELECT nm_aluno, nr_telefone INTO vNomeAluno, vTelefone
    FROM tb_alunos
    WHERE cd_aluno = 1;
    
  dbms_output.put_line('Nome do aluno: ' || vNomeAluno);
  dbms_output.put_line('Quantidade: ' || vTelefone.count || ' telefones');
  
  FOR x IN 1 .. vTelefone.count LOOP
    dbms_output.put_line('   ' || vTelefone(x));
  END LOOP;
END;

-- TRIGGERS
  -- Gatilhos
     -- Blocos que são executados automaticamente conforme eventos do BD
     -- Triggers do SGBD
        -- LOGON, LOGOFF, STARTUP, DROP, CREATE, ALTER
     -- Triggers DML
        -- INSERT, UPDATE, DELETE
     -- Triggers Instead Of
        -- VIEWS

-- ESCOPO TRIGGER
CREATE OR REPLACE TRIGGER <nome>
  (<BEFORE OR AFTER OR INSTEAD-OF) <evento> [ON <local>]
  BEGIN
  
  END;

Triggers do SGBD
AFTER STARTUP
AFTER LOGON
BEFORE SHUTDOWN
BEFORE LOGOFF

BEFORE STARTUP  --??
AFTER SHUTDOWN  --??


TRIGGERS DML
:NEW.nm_sala
:OLD.nm_sala

Triggers DML
          INSERT      UPDATE       DELETE
:NEW       SIM         SIM          NÃO       
:OLD       NÃO         SIM          SIM

-- Exemplo trigger SGBD
-- Evento: 'Usuário logou no banco de dados'
-- Evento: 'Usuário saiu do banco de dados'

CREATE TABLE tb_log_acessos(
  dt_acesso DATE DEFAULT SYSDATE,
  tx_usuario varchar(30),
  tx_acao varchar(50)
);

CREATE OR REPLACE TRIGGER tr_usuario_logon
  AFTER LOGON ON DATABASE
  BEGIN
    INSERT INTO aluno.tb_log_acessos(tx_usuario,tx_acao)
      VALUES (USER, 'usuario logou no banco de dados');
  END;

CREATE OR REPLACE TRIGGER tr_usuario_logoff
  BEFORE LOGOFF ON DATABASE
  BEGIN
    INSERT INTO aluno.tb_log_acessos(tx_usuario,tx_acao)
      VALUES (USER, 'usuario saiu do banco de dados');
  END;
  
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

SELECT * FROM tb_log_acessos;

-- Exemplo TRIGGER DML
  -- produtos
  -- movimentacao

CREATE TABLE tb_produtos(
  cd_produto number,
  nm_produto varchar(50),
  qtd_estoque number
);

-- cd_operacao === C (compra) ou V (venda)
CREATE TABLE tb_movimentacao(
  cd_operacao char,
  cd_produto number,
  qtd_movimento number
);

INSERT INTO tb_produtos VALUES(1, 'Notebook', 20);
INSERT INTO tb_produtos VALUES(2, 'Mouse', 50);
INSERT INTO tb_produtos VALUES(3, 'Monitor', 10);

INSERT INTO tb_movimentacao VALUES ('C', 1, 10);
INSERT INTO tb_movimentacao VALUES ('V', 2, 5);
INSERT INTO tb_movimentacao VALUES ('C', 1, 100);
INSERT INTO tb_movimentacao VALUES ('V', 1, 50);

DELETE FROM tb_movimentacao 
  WHERE cd_operacao = 'V' 
    AND cd_produto = 1 
    AND qtd_movimento = 50;

SELECT * FROM tb_produtos;
SELECT * FROM tb_movimentacao;

UPDATE tb_movimentacao SET qtd_movimento = 15
  WHERE cd_operacao = 'V' AND cd_produto = 2;

-- BEFORE UPDATE (por instrução)
-- BEFORE UPDATE EACH ROW (por registro)

INSERT C  -> Adicionar a (+ qtd no estoque + estoque) 
INSERT V  -> Remover a (- qtd no estoque + estoque)
UPDATE C  -> Adicionar a qtd no estoque (+ qtd no estoque + estoque)
UPDATE V  -> Remover a qtd no estoque  (- qtd no estoque + estoque)
DELETE C  -> Remover a qtd no estoque
DELETE V  -> Adicionar a qtd no estoque

Triggers DML
          INSERT      UPDATE       DELETE
:NEW       SIM         SIM          NÃO       
:OLD       NÃO         SIM          SIM

CREATE OR REPLACE TRIGGER tr_movimentacao
  AFTER INSERT OR UPDATE OR DELETE ON tb_movimentacao
  FOR EACH ROW
  BEGIN
    IF(:NEW.cd_operacao = 'C') THEN
      UPDATE tb_produtos SET qtd_estoque = qtd_estoque + :NEW.qtd_movimento
        WHERE cd_produto = :NEW.cd_produto;
    END IF;
    IF (:NEW.cd_operacao = 'V') THEN
      UPDATE tb_produtos SET qtd_estoque = qtd_estoque - :NEW.qtd_movimento
        WHERE cd_produto = :NEW.cd_produto;
    END IF;
    IF (:OLD.cd_operacao = 'C') THEN
      UPDATE tb_produtos SET qtd_estoque = qtd_estoque - :OLD.qtd_movimento
        WHERE cd_produto = :OLD.cd_produto;
    END IF;
    IF (:OLD.cd_operacao = 'V') THEN
      UPDATE tb_produtos SET qtd_estoque = qtd_estoque + :OLD.qtd_movimento
        WHERE cd_produto = :OLD.cd_produto;
    END IF;
  END;
  20 + 10
  50 - 15
  DROP TRIGGER tr_movimentacao;

TRUNCATE TABLE tb_movimentacao;
  
SELECT * FROM tb_produtos;
SELECT * FROM tb_movimentacao;

20 notebooks (cod 1)
C + 10  => 30 notebooks
C + 5   => 
NEW C
   estoque = (30 + 5)
OLD C 
   estoque = (35 - 10)
   
   31 /01 -> 50
+ 5
- 5
+ 20
+ 40
- 30
  28/02 -> 35
- 5
+ 30
- 80
+ 120

now -> (35 - 5 + 30 - 80 + 120)

-- Exemplo de validaçõa com trigger
-- uma restrição para a atualização de salário
   -- o salário novo TEM que ser maior que o atual
   
CREATE OR REPLACE TRIGGER valida_salario
  BEFORE UPDATE OF VALOR ON SALARIO
  FOR EACH ROW
    WHEN (NEW.VALOR < OLD.VALOR)
  BEGIN
    RAISE_APPLICATION_ERROR(-20508, 'O salário não pode ser reduzido');
  END;

SELECT * FROM SALARIO;

UPDATE SALARIO SET VALOR = 2000 WHERE MATRICUlA = 1001;
http://goo.gl/0S3fY3

-- Lab 10
-- Criar uma trigger para impedir que o salario seja reajustado acima
  -- de 20%

(valor novo - valor atual) > valor atual * 0.20
valor novo > valor atual * 1.20

-- lab 10 - resposta
CREATE OR REPLACE TRIGGER valida_aumento
  BEFORE UPDATE OF VALOR ON SALARIO
  FOR EACH ROW
  BEGIN
    IF (:NEW.VALOR - :OLD.VALOR) > :OLD.VALOR * 0.20 THEN
      RAISE_APPLICATION_ERROR(-20512, 'O aumento não pode ser maior do que 20%');
    END IF;
  END;

CREATE OR REPLACE TRIGGER valida_aumento
  BEFORE UPDATE OF VALOR ON SALARIO
  FOR EACH ROW
  BEGIN
    IF :NEW.VALOR > :OLD.VALOR * 1.20 THEN
      RAISE_APPLICATION_ERROR(-20512, 'O aumento não pode ser maior do que 20%');
    END IF;
  END;

CREATE OR REPLACE TRIGGER valida_aumento
  BEFORE UPDATE OF VALOR ON SALARIO
  FOR EACH ROW
    WHEN (NEW.VALOR > OLD.VALOR * 1.2)
  BEGIN
      RAISE_APPLICATION_ERROR(-20512, 'O aumento não pode ser maior do que 20%');
  END;

UPDATE SALARIO SET VALOR = 6000 WHERE MATRICULA = 1001;

-- TRIGGERS INSTEAD-OF
   -- as views simples permitem INSERT, UPDATE e DELETE
   -- as views complexas não permitem INSERT, UPDATE e DELETE
   
   -- o que são as views complexas
     -- JOINS
     -- GROUP BY
     -- UNION
     -- Subqueries
   
-- view simples   
CREATE OR REPLACE VIEW vw_teste
  AS
  SELECT nm_aluno AS NOME_DO_ALUNO FROM tb_alunos;

-- views complexas  
CREATE OR REPLACE VIEW vw_teste
  AS
  SELECT * FROM tb_alunos
    JOIN 
    GROUP BY;

-- Soluçao para views complexas
   -- Criar um TRIGGER INSTEAD-OF que irá tratar o evento escolhido
   
CREATE OR REPLACE VIEW ALUNO.vw_movimentacao 
  AS
  SELECT 
    cd_operacao,
    DECODE(cd_operacao, 'C', 'COMPRA', 'V', 'VENDA') as tx_operacao,
    nm_produto,
    qtd_movimento
  FROM ALUNO.tb_movimentacao M
  JOIN ALUNO.tb_produtos P ON (M.cd_produto = P.cd_produto);-- AND qtd_movimento > 40;

SELECT * FROM vw_movimentacao;

DELETE FROM vw_movimentacao WHERE nm_produto = 'Mouse';

CREATE VIEW ALUNO.vw_total AS
SELECT 
  DECODE(cd_operacao, 'C', 'COMPRA', 'V', 'VENDA') AS tx_operacao,
  SUM(qtd_movimento) AS total
  FROM ALUNO.tb_movimentacao
  GROUP BY DECODE(cd_operacao, 'C', 'COMPRA', 'V', 'VENDA');

SELECT * FROM vw_total;
SELECT * FROM tb_movimentacao;
DELETE FROM vw_total WHERE tx_operacao = 'VENDA';

CREATE OR REPLACE TRIGGER tr_vw_total
  INSTEAD OF DELETE ON vw_total
  FOR EACH ROW
  BEGIN
    IF (:OLD.tx_operacao = 'COMPRA') THEN
      DELETE FROM tb_movimentacao
        WHERE cd_operacao = 'C';
    ELSIF (:OLD.tx_operacao = 'VENDA') THEN
      DELETE FROM tb_movimentacao
        WHERE cd_operacao = 'V';
    END IF;
  END;

UPDATE SALARIO SET VALOR = 5000 WHERE MATRICUlA = 1001;