DO BÁSICO AO AVANÇADO PARA

MANIPULAÇÃO E OTIMIZAÇÃO DE DADOS

Fábio Roberto Octaviano

Manipulação de Dados

Após o término do Capítulo:

Descrever a Linguagem de Manipulação de Dados (DML).

Inserts, Updates, Deletes.

Controle de Transações.

Uma instrução DML é executada quando: ◦ Uma linha é adicionada à tabela.

◦ Uma linha é atualizada na tabela.

◦ Uma linha é removida da tabela.

Uma transação é uma coleção de instruções DML que formam uma unidade lógica de trabalho (UOW).

A instrução INSERT permite adicionar novas linhas a uma tabela.

Sintaxe:

Com essa sintaxe, apenas uma linha é inserida por vez.

INSERT INTO table [(column [, column...])]

VALUES (value [, value...]);

É possível especificar as colunas na instrução:

Opcionalmente, pode-se omitir as colunas:

Nesse caso, a ordem das colunas durante a criação da tabela é utilizada.

INSERT INTO departments(department_id,

department_name, manager_id, location_id)

VALUES (70, 'Public Relations', 100, 1700);

1 row created.

INSERT INTO departments

VALUES (70, 'Public Relations', 100, 1700);

1 row created.

Método Implícito: omite-se a coluna da lista de colunas.

Método Explícito: especifica-se a palavra-chave NULL na cláusula Values.

INSERT INTO departments (department_id,

department_name )

VALUES (30, 'Purchasing');

1 row created.

INSERT INTO departments

VALUES (100, 'Finance', NULL, NULL);

1 row created.

Função SYSDATE registra a data e hora correntes.

INSERT INTO employees (employee_id,

first_name, last_name,

email, phone_number,

hire_date, job_id, salary,

commission_pct, manager_id,

department_id)

VALUES (113,

'Louis', 'Popp',

'LPOPP', '515.124.4567',

SYSDATE, 'AC_ACCOUNT', 6900,

NULL, 205, 100);

1 row created.

Adicione um novo empregado.

Verifique a linha inserida.

INSERT INTO employees

VALUES (114,

'Den', 'Raphealy',

'DRAPHEAL', '515.127.4561',

TO_DATE('03/02/1999', 'DD/MM/YYYY'),

'AC_ACCOUNT', 11000, NULL, 100, 30);

1 row created.

É possível escrever a instrução INSERT com uma subquery:

Não use a cláusula VALUES.

É preciso igualar o número de colunas na cláusula INSERT com o da subquery.

INSERT INTO sales_reps(id, name, salary, commission_pct)

SELECT employee_id, last_name, salary, commission_pct

FROM employees

WHERE job_id LIKE '%REP%';

4 rows created.

A instrução UPDATE permite modificar linhas em uma tabela.

Sintaxe:

Atualize mais de uma linha ao mesmo tempo (se necessário).

UPDATE table

SET column = value [, column = value, ...]

[WHERE condition];

Especifique a(s) linha(s) a serem modificadas utilizando a cláusula WHERE.

Todas as linhas serão atualizadas se a cláusula WHERE for omitida:

UPDATE employees

SET department_id = 70

WHERE employee_id = 113;

1 row updated.

UPDATE copy_emp

SET department_id = 110;

22 rows updated.

Atualize o cargo e o salário do empregado #114 com o mesmo cargo e salário do empregado #205.

UPDATE employees

SET job_id = (SELECT job_id

FROM employees

WHERE employee_id = 205),

salary = (SELECT salary

FROM employees

WHERE employee_id = 205)

WHERE employee_id = 114;

1 row updated.

Utilize subqueries em instruções UPDATE para atualizar linhas em uma tabela com base em outra.

UPDATE copy_emp

SET department_id = (SELECT department_id

FROM employees

WHERE employee_id = 100)

WHERE job_id = (SELECT job_id

FROM employees

WHERE employee_id = 200);

1 row updated.

A instrução DELETE permite remover linhas de uma tabela.

Sintaxe:

DELETE [FROM] table

[WHERE condition];

Especifique a(s) linha(s) a serem removidas utilizando a cláusula WHERE.

Todas as linhas serão removidas se a cláusula WHERE for omitida:

DELETE FROM departments

WHERE department_name = 'Finance';

1 row deleted.

DELETE FROM copy_emp;

22 rows deleted.

Utilize subqueries em instruções DELETE para remover linhas de uma tabela com base em outra.

DELETE FROM employees

WHERE department_id =

(SELECT department_id

FROM departments

WHERE department_name

LIKE '%Public%');

1 row deleted.

Remove todas as linhas de uma tabela, deixando a tabela em branco, porém mantendo sua estrutura intacta.

É um comando DDL (Data de Definição da Linguagem)

Sintaxe:

Exemplo:

TRUNCATE TABLE table_name;

TRUNCATE TABLE copy_emp;

Exemplo (Delete da tabela toda):

Exemplo (Truncate):

Delete gera informações nos redo logs, permitindo recuperar eventuais linhas removidas por engano.

Truncate não gera informações nos redo logs, sendo muito mais difícil recuperar as linhas.

DELETE FROM copy_emp;

TRUNCATE TABLE copy_emp;

A cláusula FROM pode conter uma subquery ao invés do nome explícito da tabela:

Insira o registro e verifique se tudo está ok.

INSERT INTO

(SELECT employee_id, last_name,

email, hire_date, job_id, salary,

department_id

FROM employees

WHERE department_id = 50)

VALUES (99999, 'Taylor', 'DTAYLOR',

TO_DATE('07-JUN-99', 'DD-MON-RR'),

'ST_CLERK', 5000, 50);

1 row created.

Uma transação de banco de dados consiste de um dos seguintes pontos:

◦ Instruções DML que compõem uma mudança consistente ao dado.

◦ Uma instrução DDL.

◦ Uma instrução DCL (Data Control Language).

Começa quando a primeira instrução DML é executada.

Termina com um dentre os seguintes eventos:

◦ Uma instrução COMMIT ou ROLLBACK é executada.

◦ Uma instrução DDL ou DCL é executada.

◦ O usuário sai do iSQLPlus.

◦ O sistema falha ou entra em colápso.

Vantagens em utilizar COMMIT e ROLLBACK:

◦ Assegurar consistência de dados.

◦ Visualizar mudanças de dados antes de torná-las permanentes.

◦ Agrupar operações relacionadas logicamente.

SAVEPOINT B

SAVEPOINT A

DELETE

INSERT

UPDATE

INSERT

COMMIT Tempo

Transação

ROLLBACK

Um COMMIT automático ocorre quando:

◦ Um comando DDL é executado.

◦ Um comando DCL é executado.

◦ Saída normal do iSQLPlus, sem rodar explicitamente os comandos COMMIT ou ROLLBACK.

Um ROLLBACK automático ocorre quando:

◦ Acontece uma saída anormal do iSQLPlus.

◦ Há uma falha no sistema.

O estado anterior do dado pode ser recuperado.

O usuário da sessão atual pode revisar os resultados de operações DML por meio de comandos SELECT.

Outros usuários NÃO conseguem visualizar os resultados de comandos DML do usuário atual.

As linhas afetadas ficam com lock. Outros usuários não podem alterar dados nas linhas afetadas.

Valores alterados são gravados permanentemente.

O estado anterior do dado é totalmente perdido.

Todos os usuários podem visualizar os resultados.

Locks nas linhas afetadas são liberados, liberando as linhas para outros usuários manipularem.

Todos os SAVEPOINT são apagados.

Execute as instruções DML:

Faça COMMIT das instruções:

DELETE FROM employees

WHERE employee_id = 99999;

1 row deleted.

INSERT INTO departments

VALUES (290, 'Corporate Tax', NULL, 1700);

1 row created.

COMMIT;

Commit complete.

O Rollback descarta todas as alterações pendentes:

◦ Alterações de dados são desfeitas.

◦ Estado anterior do dado é restaurado.

◦ Locks nas linhas afetadas são liberados.

DELETE FROM copy_emp;

20 rows deleted.

ROLLBACK ;

Rollback complete.

DELETE FROM test;

25,000 rows deleted.

ROLLBACK;

Rollback complete.

DELETE FROM test WHERE id = 100;

1 row deleted.

SELECT * FROM test WHERE id = 100;

No rows selected.

COMMIT;

Commit complete.

Se uma instrução DML falha durante a execução, apenas essa instrução é desfeita.

O servidor Oracle implementa um savepoint implícito.

Todas as demais alterações são mantidas.

O usuário deve ser o responsável por terminar explicitamente transações utilizando COMMIT ou ROLLBACK!

Consistência de Leitura garante uma visão

consistente dos dados sempre.

Alteraçães feitas por um usuário não conflitam com

alterações feitas por outro usuário.

Consistência de Leitura garante que para o mesmo

dado:

◦ Leitores não esperem por manipuladores

◦ Manipuladores não esperem por leitores

SELECT *

FROM userA.employees;

UPDATE employees

SET salary = 7000

WHERE last_name = 'Grant';

Data

blocks

Undo

segments

Changed and unchanged data

Before

change

(“old” data)

User A

User B

Read-

consistent

image

Objetivos:

◦ Inserção de dados em tabelas.

◦ Alteração e remoção de dados em tabelas.

◦ Controle de transações.

Criação de uma tabela auxiliar:

CREATE TABLE my_employee

(id NUMBER(4) CONSTRAINT my_employee_id NOT NULL,

last_name VARCHAR2(25),

first_name VARCHAR2(25),

userid VARCHAR2(8),

salary NUMBER(9,2));

1. Crie uma instrução INSERT que insira a seguinte linha à tabela MY_EMPLOYEE:

NÃO liste as colunas na cláusula Insert!

INSERT INTO my_employee

VALUES (1, 'Patel', 'Ralph', 'rpatel', 895);

ID Last_Name First_Name Userid Salary

1 Patel Ralph rpatel 895

2. Crie uma instrução INSERT que insira a seguinte linha à tabela MY_EMPLOYEE:

Desta vez liste as colunas na cláusula Insert!

INSERT INTO my_employee (id, last_name, first_name, userid, salary)

VALUES (2, 'Dancs', 'Betty', 'bdancs', 860);

ID Last_Name First_Name Userid Salary

2 Dancs Betty bdancs 860

3. Crie um script que contenha um comando Insert reutilizável, solicitando o id, sobrenome, nome e salário. O Userid deve ser montado dinamicamente, sendo a primeira letra no nome concatenada ao sobrenome informados. Salve o script e insira os 2 registros abaixo rodando o script criado.

ID Last_Name First_Name Userid Salary

3 Biri Ben bbiri 1100

4 Newman Chad cnewman 750

3. Solução:

SET ECHO OFF SET VERIFY OFF INSERT INTO my_employee VALUES (&p_id, '&&p_last_name', '&&p_first_name', lower(substr('&p_first_name', 1, 1) || substr('&p_last_name', 1, 7)), &p_salary); SET VERIFY ON SET ECHO ON UNDEFINE p_first_name UNDEFINE p_last_name UNDEFINE p_id UNDEFINE p_salary

4. Liste as tuplas da tabela MY_EMPLOYEE e confirme que ela possui 4 linhas. Depois de confirmado, faça com que as alterações sejam efetivadas permanentemente.

SELECT * FROM my_employee;

COMMIT;

5. Altere o sobrenome do empregado com id 3 para Drexler.

Altere o salário para $1000 para todos os empregados cujos salários sejam menor que $900.

Verifique as alterações realizadas.

UPDATE my_employee SET last_name = 'Drexler'

WHERE id = 3;

UPDATE my_employee SET salary = 1000

WHERE salary < 900;

6. Remova Betty Dancs da tabela MY_EMPLOYEE.

Verifique o resultado. Se ok, faça com que as alterações pendentes se tornem permanentes.

DELETE FROM my_employee

WHERE last_name = 'Dancs';

SELECT * FROM my_employee;

COMMIT;

7. Execute o script de insert salvo no exercício 3 e insira a tupla abaixo:

Verifique se a linha foi inserida.

ID Last_Name First_Name Userid Salary

5 Ropeburn Audrey aropebur 1550

7. Solução:

SET ECHO OFF SET VERIFY OFF INSERT INTO my_employee VALUES (&p_id, '&&p_last_name', '&&p_first_name', lower(substr('&p_first_name', 1, 1) || substr('&p_last_name', 1, 7)), &p_salary); SET VERIFY ON SET ECHO ON UNDEFINE p_first_name UNDEFINE p_last_name UNDEFINE p_id UNDEFINE p_salary

8. Marque um ponto de controle intermediário no processamento da transação.

Feito isso, apague todas as linhas da tabela.

Verifique que a tabela esteja limpa.

Descarte o DELETE mais recente executado.

Confirme que as linhas ainda estão intactas na tabela MY_EMPLOYEE.

Por fim, torne a inserção da última linha permanente no banco de dados.

8. Solução:

SAVEPOINT step_7;

DELETE FROM MY_EMPLOYEE;

SELECT * FROM MY_EMPLOYEE;

ROLLBACK TO step_7;

SELECT * FROM MY_EMPLOYEE;

COMMIT;