oracle insert into 脚本怎么写

oracle insert into 脚本怎么写

INSERT INTO BOOK(bookid,name,price)  VALUES('100123','oracle ',54);

或者

INSERT INTO tablename(bookid,name,price)

SELECT '100123','oracle,54 FROM DUAL；

总结下Oracle 中的Insert用法

1.标准Insert --单表单行插入

语法:

INSERT INTO table [(column1,column2,...)] VALUE (value1,value2,...) 

例子:  

insert into dep (dep_id,dep_name) values(1,'技术部'); 

2, 无条件 Insert all --多表多行插入

语法:

INSERT [ALL] [condition_insert_clause]

[insert_into_clause values_clause] (subquery) 

示例:

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; 

3,有条件的Insert

语法:

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； 

示例:

Insert All

when 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; 

4, 旋转Insert (pivoting 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)

);

示例如下:

insert all

into 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_fri

from sales_source_data;