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	Prohibit other users from accessing the rows being processed by the DML 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.

Transaction Control
Transaction control includes coordinating multiple synchronous accesses to the same data. When a user changes the data that another user is using, oracle uses transactions to control who can operate the data.
Transactions
A transaction represents a basic unit of work. It is a series of SQL statements that are successfully or fail to be operated as a unit. There are many statements in SQL and PL/SQL that allow programmers to control transactions. Programmers can:
1. Start a transaction explicitly and select statement-level consistency or transaction-level consistency.
2. Set the Undo rollback point and roll back to the rollback point.
3. Complete the transaction to Change Data forever or discard the modification.
Transaction control statement

Statement	Purpose
Commit	The transaction is completed, the data is modified successfully, and is open to other users.
Rollback	Undo transactions and cancel all operations
Rollback to savepoint	Undo the operation after the set rollback point
Set transaction	Returns the consistency of transactions or statements, especially for transactions that use rollback segments.

Example:

BEGIN UPDATE checking SET balance=balance-5000 WHERE account='Kieesha'; INSERT INTO checking_log(action_date,action,amount) VALUES (SYSDATE,'Transfer to brokerage',-5000); UPDATE brokerage SET cash_balance=cash_balance+5000 WHERE account='Kiesha'; INSERT INTO brokerage_log(action_date,action,amount) VALUES (SYSDATE,'Tracfer from checking',5000) COMMIT EXCEPTION WHEN OTHERS ROLLBACK END