Oracle data manipulation and control language detailed __oracle

SQL language is divided into four categories: Data Query Language DQL, Data manipulation language DML, data definition language DDL, Data Control Language DCL. It is used to define the structure of the data, such as creating, modifying, or deleting the database; DCL is used to define the permissions of the database user; In this article I will describe in detail how these two languages are used in Oracle.

DML Language

DML is a subset of SQL that is used primarily to modify data, and the following table lists the DML statements supported by Oracle.

Statement	Use
INSERT	Add a row to a table
UPDATE	Update data stored in a table
DELETE	Delete Row
SELECT for UPDATE	Prevents other users from accessing the rows that the DML statement is processing.
LOCK TABLE	Prevent other users from using DML statements in tables

Inserting Data

Insert statements are often used to insert rows into a table, there can be special data fields in rows, or you can use subqueries to create new rows from existing data.

The column directory is optional, and the default column's directory is all column names, including comlumn_id,comlumn_id can be found in the data dictionary view all_tab_columns,user_tab_columns, or Dba_tab_columns.

The number of data inserted into the row and the data type must match the number of columns and the data type. Data types that do not conform to the column definition implement implicit data conversion for the inserted value. The null string inserts a null value into the appropriate column. Keyword NULL is often used to indicate that a column is defined as a null value.

The following two examples 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 the data in the 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, which contains two statements:

1, keyword Delete from followed by the name of the table from which you want to delete data.

2, where followed by the deletion condition

DELETE from Po_lines

WHERE ship_to_state in (' TX ', ' NY ', ' IL ')

and order_date

Empty Table

If you want to delete all the data in the table and clear the table, consider using the TRUNCATE statement in the 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 that is a DDL statement, and he differs from the delete right.

TRUNCATE table (Schema) Table DROP (reuse) STORAGE

The STORAGE substring is optional and the default is drop STORAGE. When you use drop storage, the table and table indexes are shortened, the table is shrunk to a minimum, 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 on all kinds of tables, whether large or small are very fast. If a rollback command delete is revoked, the truncate is not revoked.

2, truncate is a DDL language, like all other DDL languages, he will be implicitly submitted, not to truncate use rollback command.

3, TRUNCATE will reset the high-level line and all indexes. When you fully 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, can not give anyone the right to empty other people's table.

6, when the table is emptied after the table and table index is reset to the initial size, and delete is not.

7, can not empty the parent table.

SELECT for UPDATE

The Select FOR UPDATE statement is used to lock the row, preventing other users from modifying the data on that row. When the row is locked, other users can query the row's data with a SELECT statement, but cannot modify or lock the row.

Lock Table

Lock statements are often used to lock an entire table. When a table is locked, most DML languages cannot be used on that table. The lock syntax is as follows:

LOCK schema table in Lock_mode

Where Lock_mode has two options:

Share sharing mode

Exclusive Only Way

Example: LOCK TABLE intentory in EXCLUSIVE MODE

Dead lock

This is known as a deadlock when two transactions are locked and each other is waiting for another to be unlocked. When a deadlock occurs, Oracle detects the deadlock condition and returns an exception.

Transaction control

Transaction control includes coordinating access to multiple synchronizations of the same data. When a user changes the data that another user is using, Oracle uses transaction control who can manipulate the data.

Transaction

A transaction represents a basic unit of work that is a series of SQL statements that are successfully or unsuccessfully manipulated as a unit. There are many statements in SQL and pl/sql that allow programmers to control transactions. Programmers can:

1, explicitly start a thing, 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 change data forever or discard changes.

Transaction Control Statement

Statement	Use
Commit	Complete transaction, data modification successful and open to other users
Rollback	Undo transaction, Undo all actions
Rollback to SavePoint	Undo the action after a set rollback point
Set transaction	Responds to the consistency of a transaction or statement, especially for a transaction using a rollback segment

Cases:

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