create table Cadastro(
       id number not null,
       firs_name varchar2(20),
       last_name varchar2(25)
       )


create or replace trigger Cadastro_insert
  before insert on cadastro  
  for each row
declare
  -- local variables here
begin
 -- :NEW.id:=Cadastro_F('Ok');
 CADASTRO_P('Ok', :New.id);
end Cadastro_insert;


create or replace function Cadastro_F
       (mensagem in varchar2) 
  return number 
  is
  V_ID number;
begin
  select sequence_cadastro.nextval
         into V_ID
         from dual;
  return(V_ID);
end;


CREATE OR REPLACE PROCEDURE CADASTRO_P
       (MENSAGEM IN VARCHAR2,
       P_ID      OUT NUMBER)
IS
BEGIN
  select sequence_cadastro.nextval
         into P_ID
         from dual;
END;


-- Created on 7/12/2014 by ADMINISTRADOR 
declare 
  -- Local variables here
  i integer;
begin
  -- Test statements here  
  insert into cadastro
  (first_name, last_name)
  values('Aluno8', 'Proway8');
  
  commit;
end;

-- Created on 7/12/2014 by ADMINISTRADOR 
declare 
  -- Local variables here
  V_CODIGO           NUMBER;
  V_NOME             VARCHAR2(2000);
  V_NOME_FANTASIA    VARCHAR2(2000);
  V_CNPJ             VARCHAR2(100);
  
begin
  -- Test statements here
  V_CODIGO          := 1;
  V_NOME            := 'GPO';
  V_NOME_FANTASIA   := 'GPO (GRUPO DE PROFISSIONAIS ORACLE)';
  V_CNPJ            := 99999999999999;

INSERT INTO EMPRESA_TESTE VALUES
(V_CODIGO,
V_NOME,
V_NOME_FANTASIA,
V_CNPJ
);

COMMIT;  
end;



-- Created on 7/12/2014 by ADMINISTRADOR 
declare 
  -- Local variables here
  V_CODIGO           empresa_teste.codigo%type;
  V_NOME             empresa_teste.NOME%type;
  V_NOME_FANTASIA    empresa_teste.NOME_FANTASIA%type;
  V_CNPJ             empresa_teste.CNPJ%type;
  
begin
  -- Test statements here
  V_CODIGO          := 2;
  V_NOME            := 'GPO';
  V_NOME_FANTASIA   := 'GPO (GRUPO DE PROFISSIONAIS ORACLE)';
  V_CNPJ            := 99999999999999;

INSERT INTO EMPRESA_TESTE VALUES
(V_CODIGO,
V_NOME,
V_NOME_FANTASIA,
V_CNPJ
);

COMMIT;  
end;



-- Created on 7/12/2014 by ADMINISTRADOR 
declare 
  -- Local variables here
  V_EMPTESTE         EMPRESA_TESTE%ROWTYPE;  
begin
  -- Test statements here
  V_EMPTESTE.CODIGO          := 3;
  V_EMPTESTE.NOME            := 'GPO';
  V_EMPTESTE.NOME_FANTASIA   := 'GPO (GRUPO DE PROFISSIONAIS ORACLE)';
  V_EMPTESTE.CNPJ            := 99999999999999;

INSERT INTO EMPRESA_TESTE VALUES V_EMPTESTE;

COMMIT;  
end;


-- Created on 7/12/2014 by ADMINISTRADOR 
declare 
  -- Local variables here
  TYPE NUMLIST IS TABLE OF NUMBER;
  COD NUMLIST:= NUMLIST(1,2,3,4);
begin
  -- Test statements here
  COD.DELETE(3); -- GERA ERRO POR APAGAR O ITEM 3 DA LISTA
  FORALL I IN COD.FIRST..COD.LAST
  DELETE FROM EMPRESA_TESTE WHERE CODIGO = COD(I);
end;




-- Created on 7/12/2014 by ADMINISTRADOR 
declare 
  -- Local variables here
  cursor CAD is
    select first_name,
           last_name
       from cadastro;
       
  V_First_Name varchar2(50);
  V_Last_Name varchar2(50);
begin
  -- Test statements here
  open cad;
  loop
   fetch cad into v_first_name, v_last_name;
  exit when cad%notfound;
  dbms_output.put_line(v_first_name||' - '||v_last_name);
  end loop;
end;