PLSQL _ basic series 7_insertion method: initial Ting/Unconditional/Conditional ALL/Conditional first insert, plsql1_ting

PLSQL _ basic series 7_insertion method: initial Ting/Unconditional/Conditional ALL/Conditional first insert, plsql1_ting

BaoXinjian

I. Summary

Multiple Oracle Insert Methods

1. standard insert

1. inserting ting Insert

2. unconditional insert

3. conditional all insert/conditional first insert

 

Ii. Standard Insert-Insert a single form row

Standard Insert-Insert a single form row

1. Syntax:

Insert into table [(column1, column2,...)] VALUE (value1, value2 ,...)

2. Example:

Insert into dep (dep_id, dep_name) VALUES (1, 'technical Department ');

3. Remarks:

Only one data entry can be inserted in the standard syntax, and only one data entry can be inserted in one table.

 

Iii. Unconditional Insert All-Multi-Table multi-row Insert

Unconditional Insert all -- Insert multiple rows in multiple tables

1. Syntax:

INSERT [ALL] [condition_insert_clause]

[Insert_into_clause values_clause] (subquery)

2. Example:

INSERT ALL  INTO sal_history(emp_id,hire_date,salary) values (empid,hiredate,sal)  INTO mgr_history(emp_id,manager_id,salary) values (empid,hiredate,sal)SELECT employee_id empid,hire_date hiredate,salary sal,manager_id mgr  FROM employees WHERE employee_id>200;

 

4. Conditional Insert

Conditional Insert

1. Syntax:

INSERT [ALL | FIRST]

WHEN condition THEN insert_into_clause values_clause

[WHEN condition THEN] [insert_assist_clause values_clause]

......

[ELSE] [insert_into_clause values_clause]

Subquery;

2. Example:

INSERT AllWHEN id>5 THEN INTO z_test1(id, name) VALUES(id,name)WHEN id<>2 THEN INTO z_test2(id) VALUES(id)ELSE INTO z_test3 VALUES (name)SELECT id,name FROM z_test;

When the ALL keyword is used, oracle judges each condition from top to bottom. When the condition is met, the following into statement is executed.

In the preceding example, if ID = 6, a record is inserted in z_test1, and a record is inserted in z_test2.

3. Remarks:

When the FIRST keyword is used, oracle determines each condition from top to bottom. When the FIRST condition is met, the following into statement is executed,

In the preceding example, if ID = 6, only one piece of data is inserted in z_test1.

 

5. Rotate the Pivoting Insert statement

Rotating Insert)

create table sales_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 into sales_source_data values (176,6,2000,3000,4000,5000,6000);create table sales_info (employee_id number(6),week number(2),sales number(8,2));

Looking at the table structure above, we want to convert the data in the sales_source_data table to the sales_info table. In this case, we need to use the rotating Insert

2. Example:

insert allinto sales_info values(employee_id,week_id,sales_mon)into sales_info values(employee_id,week_id,sales_tue)into sales_info values(employee_id,week_id,sales_wed)into sales_info values(employee_id,week_id,sales_thur)into sales_info values(employee_id,week_id,sales_fri)select employee_id,week_id,sales_mon,sales_tue,sales_wed,sales_thur,sales_frifrom sales_source_data;

 

3. From this example, we can see that the so-called rotating Insert is a special application of the unconditional insert all, but this application is officially assigned the name of the rotating insert, that is, the rotating insert

 

Thanks and Regards