Oracle Data Operation and Control Language (1)

There are four types of SQL languages: dql, DML, DDL, and DCL. It is used to define the data structure, such as creating, modifying, or deleting a database; DCL is used to define the permissions of database users; in this article, I will detail how these two languages are used in Oracle.

DML Language

DML is a subset of SQL statements used to modify data. The following table lists the DML statements supported by Oracle.

Statement	Purpose
Insert	Add rows to a table
Update	Update data stored in the table
Delete	Delete row
Select for update	No rows are being processed by the dream merge ml statement.
Lock table	Prohibit other users from using DML statements in the table

　　Insert data

Insert statements are often used to insert rows into a table. A row may contain special data fields, or you can use a subquery to create a new row from existing data.

The column directory is optional. The default column directory is all column names, including comlumn_id, which can be found in the data dictionary view all_tab_columns, user_tab_columns, or dba_tab_columns.

The number and Data Type of the inserted row must match the number of columns and data type. If the data type does not conform to the column definition, the inserted values are implicitly converted. A Null String inserts a null value into an appropriate column. The keyword null is often used to define a column as a null value.

The two examples below are equivalent.

Insert into MERs (cust_id, state, post_code) Value ('ariel', null, '123 ');

Or

Insert into MERs (cust_id, state, post_code) Value ('ariel', '123 ');

　　Update Data

The update command is used to modify data in a table.

Update order_rollup Set (qty, price) = (select sum (qty), sum (price) from order_lines where customer_id = 'kohl' Where cust_id = 'kohl' And order_period = to_date ('01-Oct-2000 ')

　　Delete data

The delete statement is used to delete one or more rows of data from a table. The command contains two statements:

1. The key word "delete from" is followed by the name of the table from which the data is to be deleted.

2. Where followed by deletion Conditions

Delete from po_lines Where ship_to_state in ('tx ', 'ny', 'Il ') And order_date

　　Clear table

If you want to delete all the data in the table and clear the table, you can use the truncate statement in DDL language. Truncate is like a DELETE command without a where clause. Truncate deletes all rows in the table. Truncate is not a DML statement, but a DDL statement. It is different from delete on the right.

Truncate table (schema) table drop (reuse) Storage

The storage substring is optional. The default value is drop storage. When drop storage is used, the table and table index are shortened, the table is reduced to the minimum range, and the next parameter is reset. Reuse storage does not shorten the table or adjust the next parameter.

Truncate and delete have the following differences:

1. truncate is very fast on various tables, whether large or small. If the rollback command Delete is used, the truncate command is not used.

2. truncate is a DDL language. Like all other DDL languages, it will be implicitly submitted and cannot use the rollback command for truncate.

3. truncate will reset the high horizontal line and all indexes. When you completely browse the entire table and index, the table after the truncate operation is much faster than the table after the delete operation.

4. truncate cannot trigger any Delete trigger.

5. You cannot grant anyone the permission to clear tables of others.

6. After the table is cleared, the index of the table and the table is reset to the initial size, while the delete statement is not.

7. The parent table cannot be cleared.

　　Select for update

The Select for update statement is used to lock rows and prevent other users from modifying data on the row. When the row is locked, other users can use the SELECT statement to query the data of the row, but cannot modify or lock the row.

　　Lock table

The lock statement is often used to lock the entire table. When a table is locked, most DML languages cannot be used on the table. The lock syntax is as follows:

Lock schema table in lock_mode

Lock_mode has two options:

Share

Exclusive unique mode

Example:

Lock table intentory in exclusive mode

　　Deadlock

When both transactions are locked and each other is waiting for the other to be unlocked, this situation is called a deadlock.

When a deadlock occurs, Oracle checks the deadlock condition and returns an exception.