SQL更新，删除和同时插入 [英] SQL Update,Delete And Insert In Same Time

问题描述

我只是对某事感到好奇.让我说我有一个表，我将更新该值，然后将其删除，然后插入新的1.如果我以这种方式编写代码，这将非常容易:

I just curious about something. Let said i have a table which i will update the value, then deleted it and then insert a new 1. It will be pretty easy if i write the coding in such way:

  UPDATE PS_EMAIL_ADDRESSES SET PREF_EMAIL_FLAG='N' WHERE EMPLID IN ('K0G004');

  DELETE  FROM PS_EMAIL_ADDRESSES WHERE EMPLID='K0G004' AND E_ADDR_TYPE='BUSN';

  INSERT INTO PS_EMAIL_ADDRESSES VALUES('K0G004', 'BUSN', '[e-mail]', 'Y');

但是，如果使用"update"语句，它将更加容易.但是我的问题是，是否有可能同时完成这3个步骤?

however, it will be much more easy if using 'update' statement. but My question was, it that possible that done this 3 step in the same time?

推荐答案

交易是一种逻辑的，原子的工作单元，其中包含一个或多个 更多的SQL语句.事务对SQL语句进行分组，以便它们 要么全部提交，这意味着它们已应用于 数据库，或全部回滚，这意味着它们已从 数据库. Oracle数据库为每个事务分配一个唯一的 标识符，称为交易ID.

A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database. Oracle Database assigns every transaction a unique identifier called a transaction ID.

此外，引用Wikipedia交易帖子:

在计算机科学中，ACID(原子性，一致性，隔离性， 耐用性)是一组属性，可确保数据库 交易得到可靠处理.

In computer science, ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably.

原子性要求每一笔交易都是全有或全无" :如果有 交易的一部分失败，整个交易失败，并且 数据库状态保持不变.

Atomicity requires that each transaction is "all or nothing": if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged.

以您的情况，您可以将所有三个句子括在一个交易中:

In your case, you can enclose all three sentences in a single transaction:

COMMIT;         ''This statement ends any existing transaction in the session.
SET TRANSACTION NAME 'my_crazy_update'; ''This statement begins a transaction 
                                         ''and names it sal_update (optional).
 UPDATE PS_EMAIL_ADDRESSES 
    SET PREF_EMAIL_FLAG='N' 
  WHERE EMPLID IN ('K0G004');

 DELETE FROM PS_EMAIL_ADDRESSES 
  WHERE EMPLID='K0G004' AND E_ADDR_TYPE='BUSN';

 INSERT INTO PS_EMAIL_ADDRESSES 
 VALUES('K0G004', 'BUSN', '[e-mail]', 'Y');

COMMIT;

这是满足您的要求的最佳方法一次完成所有句子" .

This is the best approach to catch your requirement 'do all sentences at a time'.

这篇关于SQL更新，删除和同时插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！