--Bloco anonimo com cursor e com Laço (Retorna todos os cpf de funcionarios que sao pedreiros)

set serveroutput on
DEclare
CURSOR curs IS SELECT funccpf FROM funcionarios  WHERE funccargo like 'Pedreiro';
CPF funcionarios.funcCPF%type;

BEGIN

open curs;
loop
fetch curs INTO  CPF;
exit when curs%notfound;
dbms_output.put_line (CPF);
end loop;

END;
/



-- Procedure com cursor (Retorna todos os campos do funcionario cujo Cpf é passado por parametro, usando cursor e loop) 

set serveroutput on

create or replace PROCEDURE retorna_linha ( parcpf funcionarios.funccpf% TYPE)IS

CURSOR curs IS SELECT funccpf, funccargahoraria, funccargo, funccpfchefe, funcnome, funcsetordelocacao, conccnpj FROM funcionarios WHERE funccpf = parcpf;
CPF funcionarios.funcCPF%type;
ch funcionarios.funccargahoraria%type;
cpfchefe funcionarios.funccpfchefe%type;
cargo funcionarios.funccargo%type;
nome funcionarios.funcnome%type;
sl funcionarios.funcsetordelocacao%type;
concnpj funcionarios.conccnpj%type;

BEGIN

open curs;

loop
fetch curs INTO  CPF, ch, cargo,cpfchefe, nome, sl, concnpj;
exit when curs%notfound;
dbms_output.put_line (CPF ||' '|| ch ||' '|| cargo ||' '|| cpfchefe ||' '|| nome ||' '|| sl ||' '|| concnpj);
end loop;

CLOSE curs;
END retorna_linha;
/



--Procedures:  (Procedimento que altera construtora por parametro)

create or replace
PROCEDURE construtora_alterar (cnpj construtora.conccnpj% TYPE, 
nome_fantasia  construtora.concnomefantasia% TYPE , 
razao_social construtora.concrazaosocial% TYPE , 
endereco construtora.concendereco% TYPE)IS
BEGIN

  UPDATE construtora 
  SET concnomefantasia = nome_fantasia, 
      concrazaosocial = razao_social,
      concendereco = endereco
  WHERE conccnpj = cnpj;

END construtora_alterar;

--Procedures:

create or replace
PROCEDURE construtora_consultar ( parcnpj construtora.conccnpj% TYPE)IS

CURSOR curs IS SELECT conccnpj,concnomefantasia, concrazaosocial, concendereco FROM construtora WHERE conccnpj = parcnpj;
cnpj construtora.conccnpj%type;
nf construtora.concnomefantasia%type;
rs construtora.concrazaosocial%type;
endereco construtora.concendereco%type;

BEGIN

open curs;

loop
fetch curs INTO  cnpj, nf, rs, endereco;
exit when curs%notfound;
dbms_output.put_line (cnpj ||' '|| nf ||' '|| rs ||' '|| endereco);
end loop;

CLOSE curs;
END construtora_consultar;

--Procedure (Exclui contrutora por parametro CNPJ)

create or replace
PROCEDURE construtora_excluir (cnpj construtora.conccnpj% TYPE)IS
BEGIN

  DELETE
  FROM Construtora 
  WHERE conccnpj = cnpj;

END construtora_excluir;

-- Procedure (Inclui uma construtora por parametro)

create or replace
PROCEDURE construtora_incluir (cnpj construtora.conccnpj% TYPE, nome_fantasia construtora.concnomefantasia% TYPE, razao_social construtora.concrazaosocial% TYPE, endereco construtora.concendereco% TYPE)IS
BEGIN

INSERT INTO construtora 
VALUES (cnpj, nome_fantasia, razao_social, endereco );

END construtora_incluir;

--Procedura + uso do exception

create or replace
PROCEDURE funcUpdt_excecao (cnpj_construtora construtora.conccnpj%TYPE) IS

  cpf_funcionario funcionarios.funccpf%TYPE;
  
BEGIN
  
    SELECT func.funccpf INTO cpf_funcionario
    FROM funcionarios func
    WHERE func.funccargo Like 'Gerente' and func.conccnpj = cnpj_construtora;
  
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        dbms_output.put_line ('Nao foi encontrado nenhum gerente nessa construtora.');
    WHEN OTHERS THEN
        dbms_output.put_line ('O gerente ja foi definido na cosntrutora tem o seguinte cpf: ' || cpf_funcionario);
        
END funcUpdt_excecao;

-- Procedures + funcao imprimir

create or replace
PROCEDURE imprimeEndereco ( pesquisaCnpj fornecedores.forccnpj% TYPE)IS

CURSOR cursEndereco IS SELECT forccnpj,forcnomefantasia, forcrazaosocial, forcendereco FROM fornecedores WHERE forccnpj = pesquisaCnpj;
cnpj fornecedores.forccnpj %type;
nf fornecedores.forcnomefantasia%type;
rs fornecedores.forcrazaosocial%type;
endereco fornecedores.forcendereco%type;

BEGIN

open cursEndereco;

loop
fetch cursEndereco INTO  cnpj, nf, rs, endereco;
exit when cursEndereco%notfound;
dbms_output.put_line (nf ||' '|| endereco);
end loop;

CLOSE cursEndereco;
END imprimeEndereco;

-- Procedures (insere construtora por parametros)

create or replace
PROCEDURE inserir_por_parametros ( cnpj construtora.conccnpj% TYPE, nome_fantasia construtora.concnomefantasia% TYPE, razao_social construtora.concrazaosocial% TYPE, 
endereco construtora.concendereco% TYPE)IS
begin
insert into construtora
VALUES (cnpj,nome_fantasia,razao_social, endereco);
end inserir_por_parametros;



-- Funções (Retorna o tipo de funcionario a partir do CPF)

create or replace FUNCTION funcao_retorna_tipo (cpf funcionarios.funcCPF%TYPE) RETURN VARCHAR2 IS
cargo funcionarios.funcCargo%TYPE;

BEGIN
Select f.funcCargo into Cargo from funcionarios f where f.funcCpf like cpf;

return cargo;
end funcao_retorna_tipo;



-- Triggers (Trigger de linha que insere na tabela de Logs, os dados de quando a tabela construtora foi manipulada )

create or replace
trigger gera_logs_construtora
BEFORE INSERT OR UPDATE OR DELETE ON Construtora
REFERENCING OLD AS v NEW AS n
FOR EACH ROW
BEGIN
  
  IF inserting THEN
    INSERT INTO logs_construtora VALUES (sysdate, user, 'Foi inserido.', :v.concnomefantasia, :v.concrazaosocial, :v.concendereco, :n.concnomefantasia, :n.concrazaosocial, :n.concendereco);
  ELSIF deleting THEN
    INSERT INTO logs_construtora VALUES (sysdate, user, 'Foi deletado.', :v.concnomefantasia, :v.concrazaosocial, :v.concendereco, :n.concnomefantasia, :n.concrazaosocial, :n.concendereco);
  ELSE
    INSERT INTO logs_construtora VALUES (sysdate, user, 'Foi alterado.', :v.concnomefantasia, :v.concrazaosocial, :v.concendereco, :n.concnomefantasia, :n.concrazaosocial, :n.concendereco);
    
  END IF;

END;
/

--Triggers (de comando)



-- Package
Create Or Replace Package Pacote_Construtora IS

PROCEDURE construtora_alterar (cnpj construtora.conccnpj% TYPE, 
nome_fantasia  construtora.concnomefantasia% TYPE , 
razao_social construtora.concrazaosocial% TYPE , 
endereco construtora.concendereco% TYPE);

PROCEDURE construtora_consultar ( parcnpj construtora.conccnpj% TYPE);

PROCEDURE construtora_excluir (cnpj construtora.conccnpj% TYPE);

PROCEDURE construtora_incluir (cnpj construtora.conccnpj% TYPE, nome_fantasia construtora.concnomefantasia% TYPE, razao_social construtora.concrazaosocial% TYPE, endereco construtora.concendereco% TYPE);

End Pacote_construtora;
/
Create or Replace Package Body Pacote_Construtora As

PROCEDURE construtora_alterar (cnpj construtora.conccnpj% TYPE, 
nome_fantasia  construtora.concnomefantasia% TYPE , 
razao_social construtora.concrazaosocial% TYPE , 
endereco construtora.concendereco% TYPE)IS
BEGIN

  UPDATE construtora 
  SET concnomefantasia = nome_fantasia, 
      concrazaosocial = razao_social,
      concendereco = endereco
  WHERE conccnpj = cnpj;

END construtora_alterar;

PROCEDURE construtora_cons ( parcnpj construtora.conccnpj% TYPE)IS

CURSOR curs IS SELECT conccnpj,concnomefantasia, concrazaosocial, concendereco FROM construtora WHERE conccnpj = parcnpj;
cnpj construtora.conccnpj%type;
nf construtora.concnomefantasia%type;
rs construtora.concrazaosocial%type;
endereco construtora.concendereco%type;

BEGIN

open curs;

loop
fetch curs INTO  cnpj, nf, rs, endereco;
exit when curs%notfound;
dbms_output.put_line (cnpj ||' '|| nf ||' '|| rs ||' '|| endereco);
end loop;

CLOSE curs;
END construtora_consultar;

PROCEDURE construtora_excluir (cnpj construtora.conccnpj% TYPE)IS
BEGIN

  DELETE
  FROM Construtora 
  WHERE conccnpj = cnpj;

END construtora_excluir;

PROCEDURE construtora_incluir (cnpj construtora.conccnpj% TYPE, nome_fantasia construtora.concnomefantasia% TYPE, razao_social construtora.concrazaosocial% TYPE, endereco construtora.concendereco% TYPE)IS
BEGIN

INSERT INTO construtora 
VALUES (cnpj, nome_fantasia, razao_social, endereco );

END construtora_incluir;

End Pacote_Construtora;
/


-- MINUS: Resultado: CPF's dos gerentes. Sem muita funcionalidade =/

SELECT FUNCCPF FROM FUNCIONARIOS
MINUS
SELECT FUNCCPF FROM FUNCIONARIOS WHERE FUNCCARGO <> 'Gerente';

-- Except: Deu a murrinha e eu não consegui fazer isso rodar. O resultado deve ser o oposto do exemplo acima.

SELECT FUNCCPF FROM FUNCIONARIOS
EXCEPT
SELECT DISTINCT FUNCCPFCHEFE FROM FUNCIONARIOS;

--Trigger de comando

CREATE OR REPLACE
TRIGGER notificacao_construtora
AFTER UPDATE ON CONSTRUTORA
BEGIN
  DBMS_OUTPUT.PUT_LINE('Atualização feita com sucesso!');
END;