Four categories of Oracle Data languages

The SQL language can be divided into four categories: Oracle Data Query Language (DQL), Oracle Data Control Language (DML), Data Definition Language (DDL), and data control language (DCL. It is used to define the data structure, such as creating, modifying, or deleting a database;

DCL is used to define Database User Permissions. In this article, I will detail how to use these two languages in Oracle.

DML Language

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

Statement usage
INSERT Add rows to the table
UPDATE the data stored in the table
DELETE row
Select for update prohibits other users from accessing the rows being processed by the DML statement.
Lock table prohibits 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 Oracle data fields, or a new row can be created from existing data using subqueries.

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 of inserted rows and the Oracle Data Type must match the number of columns and the 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 customers(cust_id,state,post_code)  
  
  
VALUE('Ariel',NULL,'94501');  
 
 

Or

 
 

  
  
INSERT INTO customers(cust_id,state,post_code)  
  
  
VALUE('Ariel',,'94501');  
 
 

Update Data

The UPDATE command is used to modify Oracle 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< td>  
 
 

Clear table

If you want to delete all Oracle 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 Oracle data on this 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  
 
 

The above content is a detailed description of Oracle Data Operations and Control Language, hoping to help you in this regard.

Article by: http://www.programbbs.com/doc/class10-3.htm