Oracle9i learning notes 18 Oracle9i extensions to DML and DDL statements

1. Statement Review
1) Use the insert statement to add a new row to the table.

Insert into table [(column [, column...])]
Values (value [, value...]);

With this syntax, only one row can be inserted at a time:
Example:
Insert into dept (deptno, dname, Loc)
Values (70, 'public relations ', 'shenyang ');

2) use the update statement to modify existing rows.

Update table
Set column = value [, column = value,...]
[Where condition];

-You can modify multiple rows at a time.
-If the WHERE clause is used, you can specify one or more rows to be modified.
Example:
Update EMP
Set deptno = 30
Where e-mapreduce = 7369;

2. insert statements for multiple tables
1) The insert... select statement can be used as part of a single DML statement to insert rows into multiple tables.
2) Multi-table insert statements can be used in the data warehouse system to transfer data from one or more operation sources to a group of destination tables.
3) Oracle9i introduces the following multi-table insert statement types:
-Unconditional insert
-Condition all insert
-Condition first insert
-Pivot insert

3. Unconditional insert statement

1) syntax
Insert all
[Insert_into_value] [values_clause]
(Subquery)

2) Example
-Select empno, hiredate, Sal, and Mgr values for employees whose empno is greater than 7698 from the EMP table.
-Insert these values to the sal_history (empno, hiredate, Sal) and mgr_history (empno, Mgr, Sal) tables using multi-table insert statements.

Insert all
Into sal_history values (empno, hiredate, Sal)
Into mgr_history values (empno, Mgr, Sal)
Select empno, hiredate, Sal, Mgr
From EMP
Where empno> 7698;

4. Conditional insert statements
1) syntax
Insert all
[When condition then]
[Insert_into_clause] [values_clause]
(Subquery)

2) Example
-Select empno, hiredate, Sal, and Mgr values for employees whose empno is greater than 7698 from the EMP table.
-If Sal is greater than $2500, insert these values to the sal_history table using a conditional multi-table insert statement.
-If MGR is greater than 7782, insert these values to the mgr_history table using a multi-table insert statement.

Insert all
When SAL> 2500 then
Into sal_history values (empno, hiredate, Sal)
When Mgr & gt; 7782 then
Into mgr_history values (empno, Mgr, Sal)
Select empno, hiredate, Sal, Mgr
From EMP
Where empno> 7698;

5. condition first insert
1) syntax

Insert first
[When condition then]
[Insert_into_clause] [values_clause]
[Else]
[Insert_into_clause] [values_clause]
(Subquery)

2) Example
-Select empno, hiredate, Sal, and Mgr values for employees whose empno is greater than 7698 from the EMP table.
-If Sal is greater than $2500, use the first multi-table insert statement to insert these values to the sal_history table.
-If the value of the first when clause is true, the when clause after the row is skipped.
-If MGR is greater than 7782, use the first multi-table insert statement to insert these values to the mgr_history table.

Insert first
When SAL> 2500 then
Into sal_history values (empno, hiredate, Sal)
When Mgr & gt; 7782 then
Into mgr_history values (empno, Mgr, Sal)
Select empno, hiredate, Sal, Mgr
From EMP
Where empno> 7698;

6. Pivot insert

1) supports accepting a group of sales records from non-relational database tables
The format of sales_source_data is as follows:
Empno, week_id, sales_mon, sales_tue, sales_wed, sales_thur, sales_fri
2) You may want to store these records in a typical format to the sales_info (empno, week, sales) table.
3) Use pivoting insert to convert the sales record set from a non-relational database table to the relational format

Insert all
Into sales_info values (empno, week_id, sales_mon)
Into sales_info values (empno, week_id, sales_tue)
Into sales_info values (empno, week_id, sales_wed)
Into sales_info values (empno, week_id, sales_thur)
Into sales_info values (empno, week_id, sales_fri)
Select empno, week_id, sales_mon, sales_tue, sales_wed, sales_thur, sales_fri
From sales_source_data;