DML and transaction control for getting started with Oracle database

Insert statement

Syntax: INSERT into table [(Column1, Column2 ...)] VALUES (value1, value2 ...);

Description: INSERT statement inserts a record into the table at a time. When the default field Name list is listed, you should explicitly set the new value for each field in the newly inserted record

You can also specify an assignment field list in an INSERT statement, explicitly set a new value for a partial field, and the remaining fields are assigned NULL by default

Example: INSERT INTO Dept VALUES (88, ' research and Development ', ' Beijing ');--This is the case of the default field name list

INSERT INTO Dept VALUES (66, ' art Department ');--This sentence will perform an error, indicating that there is not enough value. That is, you must set values for all fields

INSERT INTO Dept (Deptno,dname) VALUES (99, ' Finance Department ');--You can also assign values to Deptno and dname fields individually

The select table_name from user_tables;--uses the data dictionary table. The purpose is to query the names of all tables under the current user scenario

Select User from dual;--query the username currently connected to the database

Add: You can use subqueries in INSERT statements to make copies of data between tables. Rarely used in real-world development, a bit like an array copy in Java

For example, insert into DEPT1 (ID, name) Select Deptno, dname from dept;

You do not have to give the values clause again at this time. The list of values in a subquery should match the list of fields in the INSERT clause

Data replication can also be done in this way at the application level, but in general this backup is unreliable

Backups should be done at the level of the database, or the DBA can automate data backup of the entire database, including recovery of problems

UPDATE statement

Syntax: Update table Set column1=value1 [, Column2=value2, ...] [Where condition];

Note: The UPDATE statement is used to update the data in the table. UPDATE statement updates more than one record at a time

You can use the WHERE clause to qualify the record to be updated, and to update all records in the table if the default WHERE clause is used

Example: Update emp set sal=sal+88;--will pay all employees 88 yuan

Update Student2 set phone= ' 010-51288984 ' where name= ' John ';--change John's phone to 010-51288984

Delete statement

Syntax: delete [from] table [where condition];

Description: The DELETE statement is used to delete data from the table. Delete statement deletes more than one record at a time

You can use the WHERE clause to qualify the record to be deleted, or delete all records in the table if the default WHERE clause is used

The DELETE statement does not delete the entire table, just deletes the records in the table, the table still exists, and can be used to store the data

For example: The delete emp;--is equivalent to the delete from EMP;

Delete emp where empno=7778;--deletes all records with a value of 7778 in the Empno field

Merge statement

Overview: The merge statement is used for data merging, which performs modification or insertion of data in a table based on criteria

If the record to be inserted already exists in the destination table, the update operation is performed, otherwise the insert operation is performed

There are not many opportunities to use it in the actual development process.

Syntax: Merge into table [alias]

using (Table|view|sub_query) [alias]

On (join_condition)

When matched then

Update set Col1=col1_val, Col2=col2_val

When not matched then

Insert (column_list) values (column_values);

Example: Create table Test1 (Eid number (), name Varchar2 (), Birth date, salary number (8,2));

INSERT into test1 values (1001, ' Stone ', ' 2 January-January-10 ', 8888);

INSERT into test1 values (1002, ' Smith ', ' April-January-09 ', 6666);

SELECT * from Test1;

CREATE TABLE Test2 (Eid number (), name Varchar2 (), Birth date, salary number (8,2));

SELECT * from Test2;

Merge into Test2

Using Test1

On (Test1.eid=test2.eid)

When matched then

Update Set Name=test1.name,birth=test1.birth,salary=test1.salary

When not matched then

Insert (Eid, name, birth) values (Test1.eid, Test1.name, Test1.birth);

SELECT * from Test2;

Description: Set set is the field in the target table. Assign a field value from a source table to a field in the destination table

Insert also sets the value of the field into the destination table. If you want to insert the values of all the fields, then column_list can be omitted

Note: Since set and insert default to operate on the target table, the fields following them cannot be prefixed with the target table

In this case, if the set test2.name=test1.name or insert (Test2.eid) is present, an error occurs, and the message is invalid for the identifier

Transaction control

Overview: Also known as transaction processing. is by combining a set of related operations into a logical unit of work that is either all successful or all failed

To simplify error recovery and improve application reliability. This refers to an integrated or modular control of a database operation at the database level.

You can also perform similar processing at the application level. For example, send multiple DML instructions to the database one at a a, and update two tables to correspond the transfer operation

If one of the following actions goes wrong, then cancel the previous instruction, or do the opposite hedging action

But the combination of these atoms at the application level is actually unreliable. This integration is more effective at the level of the database or at the bottom.

Transactions: A series of operations that comprise a single logical unit of work is called a transaction (Transaction). In fact, transactions are not limited to one concept in the database field

Database transactions typically consist of 0 to multiple DML statements or 1 DDL (data Define Language) statements or 1 DCL (Data Control Language) statements

The so-called single logical unit of work is the ability to perform a relatively independent function, or a series of operations that should not be separated.

For example, the bank's transfer business, the business can be divided into a account at least the transfer and B account transfer. Which is to lose a certain amount from the balance of a account.

The balance of the B account is then increased to a certain amount. The entire process, and so on, modifies the two records, and these two operations can be considered to constitute a transaction

They should be a single logical unit of work, a single business for banks

Acid: The transaction must satisfy ACID properties, i.e. atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability)

Atomicity: That is, all operations in a transaction are either wholly successful or fail completely, and they should be handled as a whole

Consistency: After the transaction has finished executing, the data must be in a consistent state and the data state error cannot occur. such as raising the employee salary scale from C to B.

But wages did not rise from the expected 4000 to 6000, so there is inconsistent data. Which means that the wage level, though, is B.

But the amount of wages is in the C-class range. This means that at the end of the transaction, the data is in an inconsistency state and can affect subsequent use

Isolation: This is relative to other transactions. Sometimes multiple transactions can occur concurrently, such as when multiple users operate on the same database table at the same time

It happens to be manipulating the same data on the table. At this point there should be a guarantee that the data state of the current transaction during execution is changed

is not affected by other transactions. Data read or viewed by other transactions is still the state before the current transaction is executed

Until the end of the current transaction, other transactions will see the data state after the end of the current transaction

The state in the middle of all transactions is isolated and unaffected by other transactions.

Persistence: After the transaction completes and submits the operation, the data will be permanently in effect, permanently stored in the database, irrevocable and unrecoverable in the future

Start: Transaction starts with the first executable statement

End: The transaction ends when a commit or ROLLBACK statement is encountered, a DDL or DCL statement is encountered, a user session ends, a system crashes, and so on

Commit: Transactions are automatically committed when a DDL statement is executed, a DCL statement is executed, a normal end session, and so on

The submission is to make the transaction permanently effective and irrevocable. The rollback is the undo of the previous operation, but the data is still consistent when it rolls back to the state before the transaction started

Rollback: Transactions are automatically rolled back when the session terminates abnormally or the system crashes. Explicit commit and rollback operations for transactions to better ensure data consistency

Status: After transaction rollback: Data modification is revoked. The data reverts to the state before the modification. Record Lock is released

After the transaction is committed: the modification of the data is permanently valid and irrevocable. The previous state of the data was permanently lost and could not be recovered. Save Point (savepoints) is cleared

All users (sessions) will see the results after the operation. The record lock is freed for other users to modify the data at this time

Before committing or rolling back: The result of a DML operation in a transaction is only visible to the current user (session), and other users (sessions) do not see the change in data in the current transaction until the end of the transaction

The rows involved in a DML statement in a transaction are locked and cannot be modified by other users (sessions), but can be queried

Changes in the state of data in a transaction can be restored

Automatic submission of Sqlplus

Overview: When executing SQL statements in Sqlplus, you can set whether to commit automatically, by default, not autocommit. The submission here refers not to the transaction, but to the sqlplus of each statement

In the future, statements in SQL Plus are automatically committed when the local to database connection is closed in a commit or normal shutdown window.

Settings: Show autocommit;--view settings. Where autocommit off indicates that the current setting is not autocommit, and autocommit immediate represents autocommit

Set autocommit on;--changed to Autocommit. Set autocommit off;--changed to not autocommit

For example: INSERT INTO Dept values (the ' Stone ', ' Beijing ');

SELECT * FROM dept;

Description: This is an uncommitted transaction in the case of autocommit status of off. The action to insert a new record does not take effect permanently, except that the current user (session) is visible

This uncommitted transaction is not visible in other sessions, even if the current user connects to the database again, a new session is established, and no 88th records are queried

For example, do not close the current sqlplus and then open a Sqlplus window and log on to the newly opened Sqlplus window again using the currently logged-on Scott user

The query is then executed, and there is no query at all in the results of the operation instructions that were not submitted in the previous session, that is, there are no 88th records in the query results

At this point, you can explicitly submit in the original Sqlplus window, that is, execute a commit instruction. When the submission is complete, inserting the 88th record will take effect permanently.

Then return to the Open Sqlplus window to execute the query, the query results naturally found in the number 88th records

This means that the transaction that has been committed affects all other transactions and sessions. And for uncommitted transactions, other users (sessions) are invisible

Save Point (SavePoint)

Overview: Create a tag in the current transaction by saving the point and fall back to the specified mark (savepoint) to implement a partial rollback of the transaction

For example: INSERT INTO Dept values ("Adv", ' Beijing ');

INSERT INTO Dept values ("Sec", ' Shanghai ');

SavePoint P1;

INSERT INTO Dept values (' ACC ', ' Dalian ');

SELECT * FROM dept;

Rollback to P1;

SELECT * FROM dept;

Note: The prerequisite is that the settings for the current sqlplus are not autocommit. So these are uncommitted transactions in the current session

The change in the state of the data after P1 is undone when the code executes, and the previous operation P1 still exists

If the rollback is executed, rollback to the beginning of the entire transaction