Oracle Database entry-level DML and transaction control

Insert statement

Syntax: insert into table [(column1, column2. ..)] values (value1, value2 ...);

Note: An insert statement can insert only one record to a table at a time. When you list the default field names, you should explicitly set new values for each field in the new insert record.

You can also specify the value assignment field list in the insert statement. Only new values are explicitly set for some fields, and other fields are assigned null by default.

Example: insert into dept values (88, 'r & D department ', 'beijing'); -- this sentence is the default field name list.

Insert into dept values (66, 'art Department '); -- this statement will cause an error and the prompt is that there are not enough values. That is, the value must be set for all fields.

Insert into dept (deptno, dname) values (99, 'Finance authorization'); -- you can assign values to the deptno and dname fields separately.

Select table_name from user_tables; -- the data dictionary table is used. The purpose is to query the names of all tables under the current user solution.

Select user from dual; -- query the user name currently connected to the database

Supplement: subqueries can be used in insert statements to copy data between tables. It is rarely used in real development. It is similar to copying arrays in Java.

For example, insert into dept1 (id, name) select deptno, dname from dept;

The values clause is unnecessary. The Value List in the subquery should match the field list in the insert clause.

This method can also be used for data backup at the application layer when data replication is implemented. However, this backup is generally not reliable.

Backup should be performed at the database level, or DBA can perform automatic data backup for the entire database, including recovery in case of problems

Update statement

Syntax: update table set column1 = value1 [, column2 = value2,...] [where condition];

Note: The update statement is used to update data in a table. The update statement can update multiple records at a time.

You can use the where clause to limit the records to be updated. If the where clause is used by default, all records in the table are updated.

Example: update emp set sal = sal + 88; -- increase the salary of all employees by 88 yuan

Update student2 set phone = '010-51288984 'where name = 'zhang san'; -- change Michael's phone number to 010-51288984.

Delete statement

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

Note: The delete statement is used to delete data from a table. The delete statement can delete multiple records at a time.

You can use the where clause to limit the records to be deleted. If the where clause is used by default, all records in the table are deleted.

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

Example: delete emp; -- equivalent to delete from emp;

Delete emp where empno = 7778; -- delete all records whose empno field value is 7778

Merge statement

Overview: The merge statement is used to merge data. It is used to modify or insert data in a table based on conditions.

If the record to be inserted already exists in the target table, update the record; otherwise, insert the record.

There are not many opportunities to use it during actual development.

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 (10), name varchar2 (20), birth date, salary number (8, 2 ));

Insert into test1 values (1001, 'stone ', '21-August 10-10', 8888 );

Insert into test1 values (1002, 'Smith ', '04-August 1-09', 6666 );

Select * from test1;

Create table test2 (eid number (10), name varchar2 (20), 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;

Note: set sets fields in the target table. Assign the field value in the source table to the field in the target table

Insert also inserts field values into the target table. Column_list can be omitted if you want to insert values of all fields.

Note: Because set and insert operations are performed on the target table by default, the fields following them cannot be prefixed with the target table.

In this example, if set test2.name = test1.name or insert (test2.eid) is displayed, an error occurs and the prompt message "invalid identifier" appears.

Transaction Control

Summary: it is also called transaction processing. By combining a group of related operations into a logical unit of work that either succeeds or fails

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

You can also perform similar processing at the application layer. For example, multiple DML commands are sent to the database one by one, and two tables are updated in succession to transfer data.

If an error occurs in the subsequent operation, cancel the previous command or perform the opposite hedging operation.

However, such an atomic combination at the application layer is actually unreliable. Integration at the database level or at the underlying layer is more effective.

Transactions: a series of operations that constitute a single logical unit of work are called transactions ). In fact, transactions are not limited to a concept in the database field.

Database transactions are usually composed of 0 to more DML statements, one DDL (Data Define Language) statement, or one DCL (Data Control Language) statement.

A single logical unit of work can complete a relatively independent function, or an operation series that should not be separated.

For example, A bank's transfer business can at least be divided into transfer-out of account A and transfer-in of Account B. That is, to reduce the amount from the balance of account.

Then, increase the balance of Account B to a certain amount. The whole process is equivalent to modifying two records. These two operations can be considered as a transaction.

They should be a single logical unit of work, that is, a single business of a bank.

ACID: the transaction must meet the ACID attribute, that is, Atomicity, Consistency, Isolation, and Durability)

Atomicity: all operations in a transaction are either completely successful or fail. They should be processed as a whole.

Consistency: after a transaction is executed, the data must be in a consistent State and cannot contain errors in the data state. For example, raise the employee's salary level from C to B.

However, the wage has not risen from the expected 4000 to 6000, so there is a data inconsistency. That is to say, although the wage level is B

However, the amount of wages falls within the scope of class C. This indicates that at the end of the transaction, the data is in an inconsistent state, which will affect subsequent use.

Isolation: This is relative to other transactions. Sometimes multiple transactions may be executed concurrently. For example, multiple users simultaneously operate on the same database table.

What happens to be manipulated is the same data of the table. At this time, there should be such a guarantee, that is, the data status changes made by the current transaction during execution.

Is not affected by other transactions. The data read or viewed by other firms is still in the status before the current transaction is executed.

Until the current transaction ends, other transactions will see the data status after the current transaction ends.

The statuses in all transactions are isolated from other transactions.

Persistence: after a transaction is executed and committed, the data takes effect permanently and is permanently stored in the database. In the future, the data cannot be undone or recovered.

Start: the transaction starts with the first executable statement.

End: the transaction ends when a commit or rollback statement, DDL or DCL statement, user session ends, or the system crashes.

Commit: the transaction is automatically committed when a DDL statement is executed, a DCL statement is executed, and a session ends normally.

Commit is to make the transaction take effect permanently and cannot be undone. Rollback is to cancel the previous operation, but the data remains consistent when it is rolled back to the status before the start of the transaction.

Rollback: when the session ends abnormally or the system crashes, the transaction is automatically rolled back. Explicit commit and rollback operations of transactions are designed to better ensure data consistency.

Status: after the transaction is rolled back: the data modification is canceled. Recover data to the status before modification. Record lock released

After the transaction is committed: the data modification takes effect permanently and cannot be undone. The previous data state is permanently lost and cannot be recovered. Savepoints is cleared

All users (sessions) will see the results after the operation. The record lock is released so that other users can modify the data.

Before committing or rolling back: the DML operation results in the transaction are only visible to the current user (Session), while other users (sessions) cannot see the data changes in the current transaction until the transaction ends.

The row involved in the DML statement in the transaction is locked, and other users (sessions) cannot modify it, but can query

Changes to the data status in the transaction can be recovered.

Automatic submission of SqlPlus

Overview: When executing an SQL statement in SqlPlus, you can set whether to submit the statement automatically. By default, the statement is not automatically submitted. The commit here is not a transaction, but every SQL plus statement.

In the future, when the local database connection is closed by the commit or normally close window, the statements in SQL Plus will be automatically submitted.

Settings: show autocommit; -- View settings. Autocommit OFF indicates that the current settings are not automatically submitted, while autocommit IMMEDIATE indicates automatic submission.

Set autocommit on; -- changed to automatic submission. Set autocommit off; -- changed to non-automatic commit

Example: insert into dept values (88, 'stone ', 'beijing ');

Select * from dept;

Note: When the automatic commit status is OFF, this is an uncommitted transaction. The Insert new record operation does not take effect permanently, but is visible to the current user (session ).

This uncommitted transaction cannot be seen in other sessions. Even if the current user connects to the database again, a new session is established, No 88 records can be found.

For example, if you do not close the current SQLPlus, then open a SQLPlus window, and use the currently logged-on scott user to log on to the newly opened SQLPlus window again.

Then execute the query. The operation commands not submitted in the previous session are not found at all in the result, that is, there is no No. 88 record in the query result.

In this case, you can explicitly submit the statement in the original SQLPlus window, that is, execute the commit command. After the submission is complete, the operation to insert the No. 88 record takes effect permanently.

Then return to the SQLPlus window that opens to execute the query. The query result naturally finds the 88 record.

That is to say, committed transactions affect all other transactions and sessions. For uncommitted transactions, other users (sessions) are invisible.

Savepoint)

Overview: by creating a tag in the current transaction, you can roll back to the specified tag (save point) in the future to implement partial transaction rollback.

Example: insert into dept values (55, 'adv ', 'beijing ');

Insert into dept values (56, 'sec ', 'shanghai ');

Savepoint p1;

Insert into dept values (57, 'Acc ', 'dalian ');

Select * from dept;

Rollback to p1;

Select * from dept;

Note: The current SqlPlus settings are not automatically submitted. Therefore, these are uncommitted transactions in the current session.

During code execution, the data status changes after p1 are revoked, and operations before p1 still exist.

If rollback is executed, the system rolls back to the beginning of the entire transaction.