UPDATE

O comando UPDATE permite alterar linhas numa tabela. Como todos os outros comandos do conjunto DML faz sempre parte de uma transacção. Neste módulo vamos ver:

• UPDATE
• SELECT FOR UPDATE
• Pseudo coluna ORA_ROWSCN

UPDATE

Uma instrução UPDATE permite alterar:

• Uma coluna de uma linha da tabela;
• Várias colunas de uma linha;
• Uma coluna em várias linhas;
• Várias colunas em várias linhas;

Para obter o que foi descrito acima usamos:

• A cláusula SET para escolher a(s) coluna(s);
• A cláusula WHERE para escolher a(s) linha(s);

O exemplo abaixo será analisado no módulo de criação de tabelas. Ele permite criar uma tabela a partir do resultado de um comando SELECT. As restrições existentes nas tabelas originais não são recriadas na nova tabela:

drop table EMP_SALS;

create table EMP_SALS (emp_name, salary, grade) as (
  select e.ename, e.sal, s.grade
  from emp e, salgrade s
  where e.sal between s.losal and s.hisal
);

Error starting at line 1 in command:
drop table EMP_SALS
Error report:
SQL Error: ORA-00942: table or view does not exist

create table succeeded.

• Se a tabela EMP_SALS não existir o comando DROP TABLE dá o erro listado acima;

O comando abaixo altera o salário do empregado cujo nome é 'MILLER'. Se existirem vários empregados com esse nome, todos são alterados.

update EMP_SALS
set salary = 3000 
where EMP_NAME = 'MILLER';

1 rows updated

Como se pode ver no comando que cria a tabela EMP_SALS, a coluna GRADE vem da tabela SALGRADE e representa o "nível" do salário. O salário tem nível 1 se estiver entre 700 e 1200, tem nível 2 se estiver entre 1201 e 1400 e assim sucessivamente. No comando executado no exemplo anterior a coluna GRADE pode ficar inconsistente após a actualização do valor de salário.

No comando abaixo todos os empregados cujo nome começa por M recebem um incremento salarial de 1000 e recorremos a uma subconsulta para actualizar a coluna GRADE:

update EMP_SALS e
set e.salary = e.salary + 1000,
    e.grade = (  select s.grade 
                 from salgrade s
                 where e.salary between s.losal and s.hisal
               )
where e.emp_name like 'M%';

2 rows updated

• Neste UPDATE são actualizadas duas colunas e duas linhas;
• A actualização da coluna GRADE recorre a uma subconsulta correlacionada: o valor de SALARY do comando externo é usado para pesquisar SALGRADE e devolver o valor de GRADE adequado para a actualização;

Se a cláusula WHERE for omitida todas as linhas da tabela serão alteradas.

A base de dados coloca um LOCK (bloqueio) sobre todas as linhas alteradas pelo comando UPDATE. Este LOCK impede que outras sessões da base de dados alterem estas linhas enquanto a transacção actual não terminar.

A existência de LOCKS pode gerar tempos de espera na base de dados pelo que deve ser usada com muita cautela. O tempo em que o LOCK está activo deve ser minimizado.

Topo

SELECT FOR UPDATE

O comando SELECT possui a cláusula FOR UPDATE que permite activar um LOCK sobre todas as linhas seleccionadas pelo comando. A descrição desta cláusula é feita na página sobre bloqueios.

Esta cláusula é útil quando num conjunto de dados existem relações de coerência que requerem algum processamento, que ao demorar tempo permite que outra transacção altere um dado do conjunto e coloque em causa a coerência. Desta forma podemos ler os valores actuais de todos os dados do conjunto ao mesmo tempo que impedimos a sua alteração por outra transacção. Em seguida fazemos o processamento e finalmente colocamos os valores finais no conjunto, que fica em estado coerente.

A existência de LOCKS pode gerar tempos de espera na base de dados pelo que deve ser usada com muita cautela. O tempo em que o LOCK está activo deve ser minimizado.

Topo

Pseudocoluna ORA_ROWSCN

As aplicações multiutilizador que fazem alterações de dados podem ter problemas de sincronismo. Suponha que uma aplicação inicia sessão na base de dados, lê uma linha de dados e os coloca num formulário para que o utilizador faça alterações em alguns campos. Como as várias alterações vão demorar algum tempo, o programador optou por não fazer SELECT FOR UPDATE, não colocando um LOCK. Enquanto essa alteração está a decorrer outra sessão lê a mesma linha, faz diversas alterações que são gravadas antes da primeira sessão terminar o seu trabalho. Quando a primeira sessão gravar as alterações está numa situação inconsistente do ponto de vista aplicacional, pois vai gravar alterações sobre uma versão que não é o seu ponto de partida. A base de dados não tem nada a ver com isto, pois do seu ponto de vista a primeira sessão apenas fez SELECT.

Do ponto de vista aplicacional temos um erro, que poderia ter sido evitado se o programador tivesse feito SELECT FOR UPDATE. No entanto, do ponto de vista de desempenho da base de dados a opção do programador está correcta, porque o trabalho do utilizador na primeira sessão pode demorar minutos (foi à casa de banho) durante os quais os recursos estão bloqueados, impedindo outros utilizadores de fazer o seu trabalho.

Quando optou por não usar SELECT FOR UPDATE o programador deveria ter dotado a aplicação de um mecanismo de validação de versões de dados. O mais usual é guardar os dados iniciais num conjunto de variáveis, guardar as alterações noutro conjunto e antes de gravar as alterações confirmar se os dados que estão na tabela são iguais aos originais. Se não forem a alteração é abortada.

Para facilitar a vida do programador a versão 10g da base de dados Oracle incorpora em todas as tabelas uma nova pseudo coluna que guarda o SCN (System Change Number) da última transacção que alterou a linha. Desta forma o programador em vez de guardar a versão inicial de todos os dados, guarda apenas a versão inicial do SCN, comparando-a com a versão final. Se foram idênticas significa que ninguém mexeu nessa linha e portanto as alterações podem ser gravadas com segurança.

O desempenho é levado muito a sério pelos engenheiros da Oracle, pelo que, por omissão, as tabelas não são criadas com um valor ORA_ROWSCN para cada linha, mas sim para cada bloco de dados Oracle, o que na prática significa um conjunto de linhas. A dimensão do bloco Oracle e das próprias linhas condicionam quantas linhas estão guardadas num bloco. Os engenheiros assumem que na maior parte das tabelas o problema acima não se coloca, pelo que não vale a pena sobrecarregar o sistema. Se o programador quiser um valor ORA_ROWSCN para cada linha deve criar a tabela com a opção ROWDEPENDENCIES, o que acarreta o consumo acrescido de 6 bytes por linha.

O exemplo abaixo mostra o funcionamento de ORA_ROWSCN:

drop table teste;
create table teste (
   id number(10,0),
   nome varchar2(10),
   constraint pk_teste primary key (id)
) ROWDEPENDENCIES;

insert into teste (id,nome) 
    values (1,'aaaaaa');
insert into teste (id,nome) 
    values (2,'bbbbbb');
insert into teste (id,nome) 
    values (3,'cccccc');
commit;

select ora_rowscn, id, nome 
  from teste;

update teste
  set nome='AAAAAA'
  where id=1;
select ora_rowscn, id, nome 
  from teste;  

select ora_rowscn, id, nome 
  from teste;  

commit;
select ora_rowscn, id, nome 
  from teste;  

select ora_rowscn, id, nome 
  from teste;  

Topo

Realizado por Turtle Learning ®. Última alteração em 2011-02-26