oracle中的insert语句
oracle中的insert语句
关键字: ORACLE insert into table
oracle中的insert语句
在oracle中使⽤DML语⾔的insert语句来向表格中插⼊数据，先介绍每次只能插⼊⼀条数据的语法
INSERT  INTO  表名(列名列表)  VALUES(值列表);
注意：
当对表中所有的列进⾏赋值，那么列名列表可以省略，⼩括号也随之省略必须对表中的⾮空字段进⾏赋值
具有默认值的字段可以不提供值，此时列名列表中的相应的列名也要省略
举例：有如下表格定义
create table book(bookid char(10) not null , name varchar2(60),price number(5,3))
使⽤下⾯的语句来插⼊数据
INSERT INTO BOOK(bookid,name,price)  VALUES('100123','oracle sql',54.70);
INSERT INTO BOOK                        VALUES('100123','oracle sql',54.70);
INSERT INTO BOOK(bookid)          VALUES('100123');
由于bookid是⾮空，所以，对于book来说，⾄少要对bookid进⾏赋值，虽然这样的数据不完整
如果想往⼀个表格中插⼊多条数据，那么带有values⼦句的insert就不⾏了，这时候必须使⽤insert语句和select语句进⾏配合来实现同时插⼊多条数据：
例如：现在有⼀个空表a和⼀个有数据的表格b，他们的结构是⼀样, 把b表中的所有数据插⼊到a表中的语句是：
INSERT INTO A (列1，列2，列3)
SELECT 列1，列2，列3
FROM B ;
--查询语句中可以使⽤任意复杂的条件或者⼦查询
如果数据的来源不是现存表的数据，也想多条插⼊那么使⽤如下的⽅法：
INSERT INTO  tablename(列1，列2，列3，)
SELECT  值1，值2，值3  FROM  DUAL
UNION
SELECT  值1，值2，值3  FROM  DUAL
UNION
SELECT  值1，值2，值3  FROM  DUAL
如果上⾯的值有字符和⽇期型数据，那么使⽤单引号即可，每⼀个select语句得到⼀条数据，然后使⽤集合操作符union把多条数据合并到⼀个结果集中，来实现⼀次插⼊多条数据的功能。
引⽤
要点：
⼀条语句只能插⼊⼀张表中1⾏纪录
⼀条语句可以插⼊多张表中，每张表中1⾏纪录
如果想⼀条语句插⼊表中多⾏纪录，必须⽤这样的语句从别的表中选择数据才可以，
insert into .....select * from .....
oracle insert 语句语法介绍：
Basic Inserts
Single Column Table Or View
INSERT INTO <table_name>
(<column_name>)
VALUES
(<value>);
CREATE TABLE state (
state_abbrev VARCHAR2(2));
INSERT INTO state
(state_abbrev)
VALUES
('WA');
COMMIT;
SELECT * FROM state;
Multiple Column Table Or View - All Columns
INSERT INTO <table_name>
VALUES
(<comma_separated_value_list>);
ALTER TABLE state
ADD (state_name VARCHAR2(30));
INSERT INTO state
(state_abbrev, state_name)
VALUES
('OR', 'Oregon');
COMMIT;
SELECT * FROM state;
Multiple Column Table Or View - Not All Columns INSERT INTO <table_name>
(<comma_separated_column_name_list>) VALUES
(<comma_separated_value_list>);
RENAME state TO state_city;
ALTER TABLE state_city
ADD (city_name VARCHAR2(30));
INSERT INTO state_city
(state_abbrev, city_name)
VALUES
('CA', 'San Francisco');
COMMIT;
SELECT * FROM state_city;
Problem Not Specifying Column Names Demo INSERT INTO <table_name>
(<comma_separated_column_name_list>) VALUES
(<comma_separated_value_list>);
desc state_city
INSERT INTO state_city
VALUES
('NV', 'Nevada', 'Las Vegas');
desc state_city
INSERT SELECT
Insert From SELECT statement
INSERT INTO <table_name> <SELECT Statement>; CREATE TABLE zip_new (
zip_code    VARCHAR2(5) NOT NULL,
state_abbrev VARCHAR2(2) NOT NULL,
city_name    VARCHAR2(30));
INSERT INTO zip_new
SELECT zip_code, state_abbrev, city_name FROM postal_code;
SELECT * FROM zip_new;
RECORD INSERT
Insert Using A Record
INSERT INTO <table_name>
VALUES <record_name>;
CREATE TABLE t AS
SELECT table_name, tablespace_name
FROM all_tables;
SELECT COUNT(*)
FROM t;
DECLARE
trec  t%ROWTYPE;
BEGIN
trec.table_name := 'NEW';
trec.tablespace_name := 'NEW_TBSP';
INSERT INTO t
VALUES trec;
COMMIT;
END;
/
SELECT COUNT(*) FROM t;
INSERT WHEN
Demo Tables
CREATE TABLE emp (
empno  NUMBER(4) NOT NULL,
ename  VARCHAR2(10),
job    VARCHAR2(9),
mgr    NUMBER(4),
sal    NUMBER(7,2),
deptno NUMBER(2));
CREATE TABLE emp_10 AS SELECT * FROM emp WHERE 1=0; CREATE TABLE emp_20 AS SELECT * FROM emp WHERE 1=0; CREATE TABLE emp_30 AS SELECT * FROM emp WHERE 1=0; CREATE TABLE leftover AS SELECT * FROM emp WHERE 1=0; Demo Data
INSERT INTO emp VALUES
(7369, 'SMITH', 'CLERK', 7902, 800, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, 1600, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698, 1250, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839, 2975, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, 1250, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839, 2850, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839, 2450, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566, 3000, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL, 5000, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698, 1500, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788, 1100, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698, 950, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566, 3000, 60);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782, 1300, 10);
COMMIT;
Demo Insert Statement
INSERT
WHEN (<condition>) THEN
INTO <table_name> (<column_list>)
VALUES (<values_list>)
WHEN (<condition>) THEN
INTO <table_name> (<column_list>)
VALUES (<values_list>)
ELSE
INTO <table_name> (<column_list>)
VALUES (<values_list>)
SELECT <column_list> FROM <table_name>;
INSERT
WHEN (deptno=10) THEN
INTO emp_10 (empno,ename,job,mgr,sal,deptno)
VALUES (empno,ename,job,mgr,sal,deptno)
WHEN (deptno=20) THEN
INTO emp_20 (empno,ename,job,mgr,sal,deptno)
VALUES (empno,ename,job,mgr,sal,deptno)
WHEN (deptno=30) THEN
INTO emp_30 (empno,ename,job,mgr,sal,deptno)
sql中union多表合并VALUES (empno,ename,job,mgr,sal,deptno)
ELSE
INTO leftover (empno,ename,job,mgr,sal,deptno)
VALUES (empno,ename,job,mgr,sal,deptno)
SELECT * FROM emp;
SELECT * FROM emp_10;
SELECT * FROM emp_20;
SELECT * FROM emp_30;
SELECT * FROM leftover;
INSERT ALL Without the WHEN clause INSERT ALL performs all inserts unconditionally
INSERT ALL
INTO <table_name> VALUES <column_name_list)
INTO <table_name> VALUES <column_name_list)
.
..
<SELECT Statement>;
CREATE TABLE ap_cust (
customer_id VARCHAR2(4),
program_id VARCHAR2(3),
del_date DATE);
CREATE TABLE ap_orders (
order_date DATE,
program_id VARCHAR2(3));
INSERT ALL
INTO ap_cust VALUES (customer_id, program_id, delivered_date) INTO ap_orders VALUES (order_date, program_id)
SELECT program_id, delivered_date, customer_id, order_date
FROM airplanes;
SELECT * FROM ap_cust
WHERE rownum < 1001;
SELECT * FROM ap_orders
WHERE rownum < 1001;
CREATE TABLE t (
pid  NUMBER(5),
fname VARCHAR2(20),
lname VARCHAR2(25));
INSERT ALL
INTO t (pid, fname, lname)
VALUES (1, 'Dan', 'Morgan')
INTO t (pid, fname, lname)
VALUES (2, 'Jack', 'Cline')
INTO t (pid, fname, lname)
VALUES (3, 'Helen', 'Lofstrom')
SELECT * FROM dual;
SELECT * FROM t;
INSERT ALL WHEN
Demo Insert ALL Variation
INSERT
WHEN (<condition>) THEN
INTO <table_name> (<column_list>)
VALUES (<values_list>)
WHEN (<condition>) THEN
INTO <table_name> (<column_list>)
VALUES (<values_list>)
ELSE
INTO <table_name> (<column_list>)
VALUES (<values_list>)
SELECT <column_list> FROM <table_name>;
TRUNCATE TABLE emp_10;
TRUNCATE TABLE emp_20;
TRUNCATE TABLE emp_30;
TRUNCATE TABLE leftover;
INSERT ALL
WHEN (deptno=10) THEN
INTO emp_10 (empno,ename,job,mgr,sal,deptno)
VALUES (empno,ename,job,mgr,sal,deptno)
WHEN (deptno=20) THEN
INTO emp_20 (empno,ename,job,mgr,sal,deptno)
VALUES (empno,ename,job,mgr,sal,deptno)
WHEN (deptno<=30) THEN
INTO emp_30 (empno,ename,job,mgr,sal,deptno)
VALUES (empno,ename,job,mgr,sal,deptno)
ELSE
INTO leftover (empno,ename,job,mgr,sal,deptno)
VALUES (empno,ename,job,mgr,sal,deptno)
SELECT * FROM emp;
SELECT * FROM emp_10;
SELECT * FROM emp_20;
SELECT * FROM emp_30;
SELECT * FROM leftover;
INSERT FIRST WHEN
The WHEN clause is evaluated in the order in which it appears in the statement. For the first WHEN clause that evaluates to true, the database executes the corresponding INTO clause and skips subsequent WHEN clauses for the given row.
INSERT FIRST
INTO <table_name> VALUES <column_name_list)
INTO <table_name> VALUES <column_name_list)
...
<SELECT Statement>;
CREATE TABLE cust_ah (
customer_id VARCHAR2(4),
program_id VARCHAR2(3),
del_date DATE);
CREATE TABLE cust_ip (
customer_id VARCHAR2(4),
program_id VARCHAR2(3),
del_date DATE);
CREATE TABLE cust_qz (
customer_id VARCHAR2(4),
program_id VARCHAR2(3),
del_date DATE);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。