PLSQL_ Basic Series 7_ insert mode pivoting/unconditional/conditional all/conditional First insert

2014-12-08 Baoxinjian

I. Summary

Multiple ways of Oracle insert

1. Standard Insert

1. Pivoting Insert

2. Unconditional Insert

3. Conditional all insert/conditional First insert

Second, standard insert-single table single-line insert

Standard Insert-single-row insert

1. Syntax:

INSERT into table [(Column1,column2,...)] VALUE (value1,value2,...)

2. Example:

INSERT  into VALUES (1,' Technical Department ');

3. Remark:

Only one piece of data can be inserted in standard syntax, and data can only be inserted in one table

Three, unconditional insert all-multi-table multi-row Insert

Unconditional Insert all-multi-table multi-row Insert

1. Syntax:

INSERT [All] [Condition_insert_clause]

[Insert_into_clause Values_clause] (subquery)

2. Example:

INSERT  All   into Values (empid,hiredate,sal)    into Values (empid,hiredate,sal) SELECT employee_id empid,hire_date hiredate,salary sal,manager_id Mgr    from  WHERE employee_id>;

Iv. Conditional Insert

Conditional insert

1. Syntax:

INSERT [All | First]

When condition then Insert_into_clause values_clause

[When condition then] [Insert_into_clause Values_clause]

......

[ELSE] [Insert_into_clause Values_clause]

subquery;

2. Example:

INSERT  All whenId>5  Then  intoZ_test1 (ID, name)VALUES(Id,name) whenId<>2  Then  intoZ_test2 (ID)VALUES(ID)ELSE  intoZ_test3VALUES(name)SELECTId,name fromZ_test;

When using the ALL keyword, Oracle judges each condition from top to bottom, and executes the following into statement when the condition is met

In the example above, if Id=6 then inserts a record in the Z_test1, and also inserts a record in the Z_test2

3. Remark:

When using the first keyword, Oracle judges each condition from top to bottom, and executes the subsequent into statement when it encounters the initial satisfaction.

At the same time interrupt the judgment of the condition judgment, in the above example, if id=6, will only insert a piece of data in the Z_test1

V. Rotating pivoting Insert

Rotate insert (pivoting insert)

Create TableSales_source_data (employee_id Number(6), week_id Number(2), Sales_mon Number(8,2), Sales_tue Number(8,2), sales_wed Number(8,2), Sales_thur Number(8,2), Sales_fri Number(8,2));Insert  intoSales_source_dataValues(176,6, -, the,4000, the,6000);Create TableSales_info (employee_id Number(6), Week Number(2), Sales Number(8,2));

Looking at the table structure above, you will now want to convert the data in the Sales_source_data table to the Sales_info table, which requires the use of a rotated insert

2. Examples are as follows:

Insert  All intoSales_infoValues(Employee_id,week_id,sales_mon) intoSales_infoValues(Employee_id,week_id,sales_tue) intoSales_infoValues(employee_id,week_id,sales_wed) intoSales_infoValues(Employee_id,week_id,sales_thur) intoSales_infoValues(EMPLOYEE_ID,WEEK_ID,SALES_FRI)SelectEmployee_id,week_id,sales_mon,sales_tue,sales_wed,sales_thur,sales_fri fromSales_source_data;

3. As can be seen from this example, the so-called rotary Insert is an unconditional insert all of a special application, but this application by the Oracle official, gave a pivoting insert name, that is, rotating insert

Thanks and regards

Reference: http://blog.itpub.net/25724608/viewspace-695818

PLSQL_ Basic Series 7_ insert mode pivoting/unconditional/conditional all/conditional First insert