-- criando tabela para popular com procedure
--DROP TABLE EMPLOYEES_COPIA;
  CREATE TABLE EMPLOYEES_COPIA 
   (EMPLOYEE_ID NUMBER(6,0) PRIMARY KEY, 
	FIRST_NAME VARCHAR2(20 BYTE), 
	LAST_NAME VARCHAR2(25 BYTE), 
	EMAIL VARCHAR2(25 BYTE), 
	PHONE_NUMBER VARCHAR2(20 BYTE), 
	HIRE_DATE DATE, 
	JOB_ID VARCHAR2(10 BYTE), 
	SALARY NUMBER(8,2), 
	COMMISSION_PCT NUMBER(2,2), 
	MANAGER_ID NUMBER(6,0), 
	DEPARTMENT_ID NUMBER(4,0)
   );
   
-- VERIFICANDO TABELA CRIADA
SELECT * FROM EMPLOYEES_COPIA
 
-- criando procedure

CREATE OR REPLACE PROCEDURE PROC_COPIA_FUNC

is

BEGIN
    For func in (SELECT * FROM HR.EMPLOYEES)
  LOOP
    insert into EMPLOYEES_COPIA (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,
                                    MANAGER_ID,DEPARTMENT_ID) 
                                   values
                                   (func.EMPLOYEE_ID,func.FIRST_NAME,func.LAST_NAME,func.EMAIL,func.PHONE_NUMBER,
                                    func.HIRE_DATE,func.JOB_ID,func.SALARY,func.COMMISSION_PCT,func.MANAGER_ID,func.DEPARTMENT_ID);
  END LOOP;
 dbms_output.put_line('OK CARGA REALIZADA'); 
 COMMIT;
 
 exception
  when others then
    dbms_output.put_line('ERRO: '||sqlerrm);
    ROllBACK;
    
end PROC_COPIA_FUNC;

-- EXECUTANDO PROCEDURE
EXECUTE PROC_COPIA_FUNC;

--Verificando dados;
   
-- VERIFICANDO TABELA CRIADA
SELECT * FROM EMPLOYEES_COPIA;
SELECT COUNT(*) FROM EMPLOYEES_COPIA;

-- EXECUTANDO PROCEDURE
EXECUTE PROC_COPIA_FUNC;

--DROP TABLE EMPLOYEES_COPIA
--DROP PROCEDURE PROC_COPIA_FUNC


