pl sql transactions commit
Aprenda sobre as transações PL SQL com exemplos de instruções COMMIT, ROLLBACK e SAVEPOINTS:
Neste artigo, continuaremos com PL / SQL series . No Pacote PL SQL tutorial, aprendemos sobre Pacote, suas vantagens, especificações e estrutura.
Aqui, exploraremos as transações PL SQL e suas características. Discutiremos algumas instruções PL / SQL como COMMIT, ROLLBACK, SAVEPOINTS e assim por diante, que estão relacionadas a transações PL SQL.
Também aprenderemos sobre as instruções LOCK TABLe e AUTOCOMMIT em PL SQL.
Vamos começar com a discussão !!
O que você aprenderá:
Transações PL SQL
Um componente atômico de trabalho em um banco de dados que possui várias instruções SQL é chamado de transação. É conhecido como atômico porque, uma vez que haja alterações afetadas pelas instruções SQL, elas podem ser confirmadas (atualizações indefinidas no banco de dados) ou revertidas (desfazer as atualizações no banco de dados).
Uma instrução SQL precisa ser confirmada, caso contrário, ela pode ser revertida e todas as modificações que a instrução SQL deveria fazer permanecem desfeitas. Se o programa PL / SQL for interrompido no meio da transação, não haverá impacto no banco de dados e ele será restaurado ao seu estado original.
COMMIT e ROLLBACK cuidam do fato de que as alterações no banco de dados são eternas ou desfeitas. A instrução SAVEPOINT aponta para a posição atual no processamento da transação.
Iniciar o processamento da transação
Cada transação tem um início e um término.
Os seguintes incidentes marcam o início de uma transação:
- Após a conexão com o banco de dados, a primeira instrução SQL é executada.
- Uma nova instrução SQL é executada após a instrução SQL anterior ser concluída.
Processamento de transação final
Cada programa PL / SQL deve ter uma instrução COMMIT ou ROLLBACK. Depende apenas da lógica de programação se devemos usar um COMMIT ou um ROLLBACK após a transação.
Se as instruções COMMIT ou ROLLBACK não forem usadas, isso dependerá do ambiente do host como o estado final do banco de dados deve ser. Por exemplo, em um ambiente SQL * PLUS, se um bloco de código PL / SQL estiver sem uma instrução COMMIT ou ROLLBACK, o estado do banco de dados dependerá do bloco de código que é executado logo em seguida.
Em um ambiente Oracle, se incluirmos uma instrução COMMIT ou executarmos o comando DISCONNECT, EXIT ou QUIT após executar uma definição de dados ou consulta de controle de dados, a transação será confirmada. Além disso, se incluirmos uma instrução ROLLBACK, a transação será desfeita.
Assim, uma transação pode ser encerrada devido a qualquer um dos cenários abaixo:
- Uma instrução ROLLBACK ou COMMIT é executada.
- Uma consulta DDL ( por exemplo, uma instrução de criação de tabela) é executada, após a qual o COMMIT é executado por padrão.
- Uma consulta DCL ( por exemplo, uma instrução GRANT) é executada, após a qual o COMMIT é executado por padrão.
- O usuário encerra a conexão com o banco de dados.
- O usuário executa a instrução EXIT para sair do SQL * PLUS, após a qual COMMIT é executado por padrão.
- O SQL * PLUS encontra uma terminação incomum, após a qual ROLLBACK é executado por padrão.
- Uma consulta DML não é executada com êxito, após o que ROLLBACK é executado por padrão para trazer de volta o banco de dados ao seu estado original.
COMMIT Para Mudanças Permanentes
A instrução COMMIT é capaz de encerrar a presente transação e fazer uma modificação permanente conforme realizada pela transação. Assim que o COMMIT for executado, podemos obter os dados modificados.
Conforme a instrução COMMIT é executada, todas as linhas afetadas da tabela são liberadas dos bloqueios. Também nos ajuda a nos livrar do SAVEPOINT. Uma instrução COMMIT pode ser opcionalmente acompanhada pela instrução WORK (COMMIT WORK), que é adicionada apenas para melhorar a legibilidade do código.
Caso uma transação falhe no momento do COMMIT e o estado da transação não seja conhecido, nesse momento o texto COMMENT no código PL / SQL é armazenado em um dicionário de dados junto com o id da transação.
Sintaxe para transação COMMIT:
COMMIT;Vamos considerar uma tabela chamada PROFESSORES.
Criamos a tabela TEACHERS com a ajuda da instrução SQL fornecida a seguir:
CREATE TABLE TEACHERS ( CODE INT NOT NULL, SUBJECT VARCHAR (15) NOT NULL, NAME VARCHAR (15) NOT NULL, PRIMARY KEY (CODE) ); Insira valores nesta tabela e, em seguida, confirme a transação da seguinte maneira:
INSERT INTO TEACHERS VALUES (1, 'SELENIUM', 'TOM'); INSERT INTO TEACHERS VALUES (2, 'UFT', 'SAM'); INSERT INTO TEACHERS VALUES (3, 'JMETERE', 'TONK'); COMMIT; Em seguida, a consulta abaixo é executada:
SELECT * FROM TEACHERS;Resultado:
ROLLBACK para desfazer alterações
Se uma transação atual for encerrada com uma instrução ROLLBACK, ela desfará todas as modificações que deveriam ocorrer na transação.
Internet das coisas que as empresas devem assistir
Uma instrução ROLLBACK tem os seguintes recursos, conforme listado abaixo:
- O banco de dados é restaurado ao seu estado original com uma instrução ROLLBACK, caso tenhamos excluído por engano uma linha importante da tabela.
- No caso de uma exceção que levou à falha na execução de uma instrução SQL, uma instrução ROLLBACK nos permite pular para o ponto de partida do programa, de onde podemos tomar medidas corretivas.
- As atualizações feitas no banco de dados sem uma instrução COMMIT podem ser revogadas com uma instrução ROLLBACK.
Sintaxe para transação ROLLBACK:
ROLLBACK;Sintaxe para transação ROLLBACK com SAVEPOINT:
ROLLBACK (TO SAVEPOINT );Aqui o save_n é o nome do SAVEPOINT.
Vamos considerar a tabela PROFESSORES que criamos anteriormente.
Implementação de código com ROLLBACK:
DELETE FROM TEACHERS WHERE CODE= 3; ROLLBACK; Em seguida, a consulta abaixo é executada:
SELECT * FROM TEACHERS;A saída do código acima deve ser:
No código acima, executamos uma instrução DELETE que supostamente exclui o registro do professor com CODE igual a 3. No entanto, por causa da instrução ROLLBACK, não há impacto no banco de dados e a exclusão não é feita.
SAVEPOINT para reverter alterações parciais
SAVEPOINT fornece nome e identificação ao ponto de processamento da transação atual. Geralmente está associado a uma instrução ROLLBACK. Ele nos permite reverter algumas seções de uma transação sem tocar na transação inteira.
Conforme aplicamos ROLLBACK a um SAVEPOINT, todos os SAVEPOINTS incluídos após esse SAVEPOINT em particular são removidos (isto é, se tivermos marcado três SAVEPOINTS e aplicado um ROLLBACK no segundo SAVEPOINT, automaticamente o terceiro SAVEPOINT será excluído.)
Uma instrução COMMIT ou ROLLBACK exclui todos os SAVEPOINTS. Os nomes dados a SAVEPOINT são identificadores não declarados e podem ser reaplicados várias vezes dentro de uma transação. Há um movimento de SAVEPOINT da posição antiga para a atual dentro da transação.
Um ROLLBACK aplicado a um SAVEPOINT afeta apenas a parte em andamento da transação. Assim, um SAVEPOINT ajuda a dividir uma transação longa em pequenas seções, posicionando os pontos de validação.
Sintaxe para a transação SAVEPOINT:
SAVEPOINT ;Aqui, save_n é o nome do SAVEPOINT.
Vamos considerar novamente a tabela PROFESSORES que criamos anteriormente.
Implementação de código de ROLLBACK WITH SAVEPOINT:
INSERT INTO TEACHERS VALUES (4, 'CYPRESS', 'MICHEAL'); SAVEPOINT s; INSERT INTO TEACHERS VALUES (5, 'PYTHON', 'STEVE'); INSERT INTO TEACHERS VALUES (6, 'PYTEST', 'ARNOLD'); ROLLBACK TO s; INSERT INTO TEACHERS VALUES (7, 'PROTRACTOR', 'FANNY'); COMMIT; Em seguida, a consulta abaixo é executada:
SELECT * FROM TEACHERS;A saída do código acima deve ser:
No código acima, após ROLLBACK com SAVEPOINT s for aplicado, apenas mais duas linhas foram inseridas, ou seja, professores com CÓDIGO 4 e 7, respectivamente. Observe que os professores com os códigos 1, 2 e 3 foram adicionados durante a criação da tabela.
Tabela LOCK em PL / SQL
A instrução LOCK no PL / SQL permite o bloqueio de todo o banco de dados em um modo de bloqueio. Isso determina se queremos compartilhar ou proibir o acesso à mesa.
Vamos dar um exemplo de uma tabela de bloqueio:
LOCK TABLE teachers IN ROW SHARE MODE NOWAIT;A consulta acima bloqueia a tabela TEACHERS no modo de compartilhamento de linha. Um modo de compartilhamento de linha dá permissão para um uso simultâneo de uma tabela. Ele não permite que os usuários bloqueiem a tabela inteira para uso individual. Após a execução de um COMMIT ou ROLLBACK, os bloqueios da tabela são liberados. A palavra-chave NOWAIT é usada para mencionar que o banco de dados não tem permissão para esperar que um bloqueio seja liberado.
O modo de bloqueio é importante para concluir quais outros bloqueios podem ser aplicados a uma tabela.
Vamos discutir alguns modos LOCK disponíveis no PL / SQL:
| Sl. Não | MODO DE BLOQUEIO | PROPÓSITO |
|---|---|---|
| 1 | ROW SHARE | Ele permite que vários usuários usem a mesa simultaneamente. No entanto, os usuários não têm permissão para bloquear a tabela completa para uso exclusivo. |
| dois | ROW EXCLUSIVE | Ele permite que vários usuários usem a mesa simultaneamente. No entanto, os usuários não têm permissão para bloquear a tabela completa para uso exclusivo no modo de compartilhamento. |
| 3 | COMPARTILHAR ATUALIZAÇÃO | Ele permite que vários usuários usem a mesa simultaneamente. No entanto, os usuários não têm permissão para bloquear a tabela completa para uso exclusivo. |
| 4 | COMPARTILHAR | Ele nos permite executar várias consultas na mesa simultaneamente. No entanto, os usuários não têm permissão para modificar a tabela bloqueada. |
| 5 | COMPARTILHAR LINHA EXCLUSIVA | Ele permite que os usuários com acesso somente leitura à tabela. Eles não têm permissão para modificar a tabela ou bloqueá-la no modo de compartilhamento. |
| 6 | EXCLUSIVO | Ele só permite a execução de consultas na tabela bloqueada. |
Controle automático de transações em PL / SQL
Podemos fazer a configuração de forma que uma instrução COMMIT seja executada por padrão sempre que uma instrução INSERT ou DELETE for executada. Isso é feito tornando a variável de ambiente AUTOCOMMIT em ON.
Sintaxe:
SET AUTOCOMMIT ON;Novamente, isso pode ser desativado tornando a variável de ambiente AUTOCOMMIT em OFF.
Sintaxe:
SET AUTOCOMMIT OFF;Perguntas e respostas mais frequentes
P # 1) Qual é a transação no PL SQL?
Responda: Uma transação é um grupo de instruções de computação de dados SQL que funcionam como uma unidade atômica. Todas as transações são de natureza atômica, que são confirmadas ou revertidas.
P # 2) O que é COMMIT no PL SQL?
Responda: Uma instrução COMMIT é usada para fazer uma mudança permanente no banco de dados pela transação presente. Isso torna a modificação no banco de dados visível para os usuários.
P # 3) Como você encerra uma transação?
Responda: Uma transação termina explicitamente com uma instrução COMMIT ou ROLLBACK. Ele também pode ser encerrado incondicionalmente após a execução de uma instrução DML.
P # 4) Podemos comprometer em uma trigger?
Responda: Sim, podemos confirmar no gatilho apenas se essa transação do gatilho for independente de sua transação pai.
P # 5) SELECT é uma transação?
Responda: Sim, SELECT é uma transação com todas as características de uma transação.
P # 6) Como faço para reverter uma transação no Oracle?
Responda: Precisamos executar uma instrução ROLLBACK para reverter uma transação no Oracle. Ele deve desfazer todas as alterações feitas pela transação no banco de dados e deve ser restaurado ao seu estado original.
Conclusão
Neste tutorial, discutimos em detalhes alguns conceitos básicos de transações PL SQL que são essenciais para o gerenciamento de transações PL SQL. Cobrimos os seguintes tópicos listados abaixo:
- Transação.
- Uso de COMMIT em uma transação.
- O que é um ROLLBACK em uma transação?
- Uso de ROLLBACK com SAVEPOINT em uma transação.
- LOCK table.
Leitura recomendada
- Tutorial PL SQL para iniciantes com exemplos | O que é PL / SQL
- Desenvolvimento de aplicativos de banco de dados Oracle: Oracle SQL e PL / SQL
- Pacote PL SQL: Tutorial do pacote Oracle PL / SQL com exemplos
- Diferença entre SQL Vs MySQL Vs SQL Server (com exemplos)
- Formato de data e hora PL SQL: funções de data e hora em PL / SQL
- Guia completo para tratamento de exceções PL SQL com exemplos
- Diferenças exatas de SQL vs NoSQL e saber quando usar NoSQL e SQL
- Tutorial do banco de dados Oracle - O que é o banco de dados Oracle?