Curso Oracle 11g - Lesson 4 - Programação Part2

--Lesson 04
--Manipulação de dados
--Primeiro criar as tabelas

--Unconditional INSERT ALL
Create table sal_history
(empid number(4),
hiredate date,
sal number(8,2),
jobid varchar2(10));

create table mgr_history
(empid number(4),
mgr number(4),
sal number(8,2));

Insert all
    Into sal_history values (empid, hiredate, sal, jobid)
    Into mgr_history values (empid, mgr, sal)
          Select employee_id empid, hire_date hiredate, salary sal, manager_id mgr, job_id jobid
          from employees;

select * from sal_history;
select * from mgr_history;

--Conditional INSERT ALL

Create table emp_history
(empid number(4),
hiredate date,
sal number(8,2));

create table emp_sales
(empid number(4),
comm number(2,2),
sal number(8,2));

Insert All
When hiredate < '01-01-2005' then
    Into emp_history values (empid, hiredate, sal)
When comm is not null then
    Into emp_sales values (empid, comm, sal)
Select employee_id empid, hire_date hiredate, salary sal, commission_pct comm
From employees;

--Insert First
--É como o exemplo acima, mas atende a primeira condição não verifica as demais


--Pivoting Insert


--MERGE

Create table emp_new (
emp_id number(6),
first_name varchar2(40),
salary number(8,2));

Insert Into emp_new values(100, 'Anderon', 28000);
commit;
select * from emp_new;
alter table emp_new modify emp_id primary key;
insert into emp_new (emp_id, first_name, salary)
select employee_id, first_name, salary
from employees;

Merge Into Emp_new n
Using (Select employee_id, first_name, salary, commission_pct from employees) e
      on (n.emp_id = e.employee_id)
when matched then
    update set
    n.first_name = e.first_name,
    n.salary = e.salary
    delete where (e.commission_pct is not null)
When not matched then
    Insert Values (e.employee_id, e.first_name, e.salary);
/*
   Ele olha o emp_new, existe um valor correspondente, então substitui pelo registro da tabela employees;
   Caso não hava correspondente, insere os registros da tabela employees;
*/
select * from emp_new;

---Flashback UPDATE
--Verificação das modificações, com hora inicial e final. Mostrando as informações comitadas
--Obs.: por padrão essa informação fica durante 15 minutos
select employee_id, salary
from employees
where employee_id in (100,101);

update employees
set salary = salary * 1.01
where employee_id in (100,101);
commit;

select employee_id, salary, versions_starttime, versions_endtime
from employees
versions between scn minvalue and maxvalue
where employee_id in (100,101);


Fiquem a vontade para comentar e sugerir melhorias e/ou correção dos exemplos acima.