-- Select aninhado
select f.nome, f.cpf from funcionario f, funcao fun where f.id_funcao = fun.id_funcao and fun.id_funcao in 
(select id_funcao from funcao where nome = 'Gerente' );

-- Sequence
CREATE SEQUENCE tablea_seq MINVALUE 1 MAXVALUE 99999 INCREMENT BY 1 CACHE 20;

--view
CREATE VIEW viewFuncao as select fun.nome as funcao, count (fun.nome) as qnt from Funcao Fun, funcionario f where 
fun.id_funcao = f.id_funcao group by fun.nome;

--Procedure
CREATE PROCEDURE hello (p_nome varchar2) as
 begin  
 dbms_output.put_line ('Hello ' || p_nome);
END hello;
/
set serveroutput on;
execute helloworld ('Vinicius');

--FUNCTION
CREATE FUNCTION media return number as
 cursor c_media is select avg (salario) from funcao;
 v_retorno number;
 
 begin
 open c_media;
 fetch c_media into v_retorno;
 close c_media;
 return v_retorno;
end media;
/
select media from dual;

--TRIGGER
CREATE TRIGGER insertFuncao
AFTER INSERT ON funcao
FOR EACH ROW
 BEGIN
 dbms_output.put_line ('Inseriu nova funcao');
END insertFuncao;
/

--PACKAGE
CREATE PACKAGE pacoteHello as
      procedure hello (p_nome varchar2);
END pacoteHello;
/
CREATE PACKAGE BODY pacoteHello as
      procedure hello (p_nome varchar2) as
      BEGIN
      dbms_output.put_line ('Hello ' || p_nome);
      END hello;
END pacoteHello;
/
execute pacotehello.hello ('Vinicius');