Oracle Data Operation and Control Language (1)

Wanwentao. blog.51cto. com2406488457598SQL has four types: Data Query Language DQL, 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 the permissions of database users. In this article, I will

Http://wanwentao.blog.51cto.com/2406488/457598 SQL language is divided into four categories: Data Query Language DQL, Data Control Language DML, Data Definition Language DDL, 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 the permissions of database users. In this article, I will

Http://wanwentao.blog.51cto.com/2406488/457598

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 = 'kiesha ';

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
　　Savepoint and Partial Rollback)
In SQL and PL/SQL, Savepoint is an intermediate sign within a transaction range. It is often used to divide a long transaction into small parts. A reserved point Savepoint can mark any point in a long transaction and allow operations after the point to be rolled back. Savepoint is often used in applications. For example, a process contains several functions, and a reserved point can be created before each function. If the function fails, it is easy to return to the beginning of each function. After a Savepoint is rolled back, the data obtained after the Savepoint is blocked and released. TO implement partial ROLLBACK, you can use the ROLLBACK statement with the TO Savepoint clause TO roll back the transaction TO the specified position.
Example
BEGIN
Insert into ATM_LOG (who, when, what, where)
VALUES ('kiesha', SYSDATE, 'withdrawal of $100 ', 'atm 54 ')
SAVEPOINT ATM_LOGGED;

UPDATE checking
SET balance = balance-100
RETURN balance INTO new_balance;

IF new_balance <0
THEN
Rollback to ATM_LOGGED;
COMMIT
RAISE insufficient_funda;
END IF

END
The keyword SAVEPOINT is optional, so the following two statements are equivalent:
Rollback to ATM_LOGGED;
Rollback to savepoint ATM_LOGGED;
　　Consistency and transactions
Consistency is the key concept of thing control. Having mastered the oracle consistency model, you can use transaction control more appropriately. Oracle ensures that data can be viewed and used only after all transactions are completed through consistency. This technology has a huge effect on multi-user databases.
Oracle often uses state-level consistency to ensure that data is visible but cannot be changed between statement lifecycles. A transaction is composed of multiple statements. When a transaction is used, transaction-level consistency ensures that data is visible to all statements throughout the transaction lifecycle.

Oracle implements consistency through SCN (syatem change number. An SCN is a time-oriented internal database key. The SCN only increases and does not decrease. The SCN indicates a point in time, and each data block has an SCN. operations are performed by comparing this point.

　　Transaction-level consistency

One function of set transaction is to ensure that there is an implementation in TRANSACTION-level consistency or statement-level consistency. ORACLE uses these terms:

Isolation level read commit indicates statement-LEVEL consistency

Isolation level serializable indicates transaction-LEVEL consistency.

Example:

Set transaction isolation level read commit;

SET TRANSACTION ISOLATION LEVEL READ COMMIT
The following statement can also ensure transaction-level consistency:

SET TRANSCATION READ ONLY
Any operation that attempts to modify data in a READ-ONLY transaction throws an exception. However, READ-ONLY transactions can ONLY be used in the following statements:

SELECT (no for update clause)
LOCK TABLE
SET ROLE
ALTER SYSTEM
ALTER ALARM
Even if no data is changed, the read only transaction must still use a COMMIT or ROLLBACK to end the entire transaction.

Another application of set transction uses ROLLBACK segments directly during ROLLBACK ). A rollback segment is a special data object of ORACLE. The rollback segment header contains information about transactions using This rollback segment. When you roll back a transaction, ORACLE uses the data pre-image in the ROLLBACK segment to restore the modified data to the original value. Oracle uses round-robin to randomly allocate rollback segments to transactions. A large transaction can allocate any rollback segment, which may lead to a large size of the rollback segment. Therefore, we should avoid randomly allocating rollback segments for large transactions.

The TRANSACTION starts with set transaction, as shown below:

Set transaction use rollback segment rb_large;
Rb_large is the name of a large rollback segment. Now a large rollback segment is allocated to a large transaction. Other small rollback segments cannot be managed by dynamic space, this makes it more efficient.

The following is an example. we have a rollback segment with a tablespace size of 2 GB. during peak hours, we need 10 rollback segments to meet users' needs. These peak online users only have small transactions. We run four major transactions in a week consecutively. These transactions need to delete and load data. Each revocation requires 1 GB. The rollback segment size is as follows:

Rb_large (initialize 100 M minextenta 2)

Rb1. (initial 1 M next minextents 5)
Rb2 (initial 1 M next minextents 5)
Rb3 (initial 1 M next minextents 5)
Rb4 (initial 1 M next minextents 5)
Rb5 (initial 1 M next minextents 5)
Rb6 (initial 1 M next minextents 5)
Rb7 (initial 1 M next minextents 5)
Rb8 (initial 1 M next minextents 5)
Rb9 (initial 1 M next minextents 5)
Rb10 (initial 1 M next minextents 5)
All are properly arranged in 2 GB tablespaces. If our default round-robin assigns rollback segments to the transaction, four large transactions will have four independent rollback segments, the size of each rollback segment will be 1 GB. If so, our 2 GB tablespace will not be enough, and the database administrator will have to work at 2 o'clock in the evening, each transaction starts with the following statement:

Set transaction use rollback segment rb_large
Currently, four transactions reuse the same tablespace, And the tablespace with four rollback segments should be within 2 GB. The database administrator can sleep until dawn.