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)
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, 'Jeremiah', 'Wilton')
INTO t (pid, fname, lname)
VALUES (3, 'Helen', 'Lofstrom')
SELECT * FROM dual;

SELECT * FROM t;

 

INSERT ALL WHEN

Demo Insert ALL Variation

INSERT ALL
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);

INSERT FIRST
WHEN customer_id < 'I' THEN
INTO cust_ah
VALUES (customer_id, program_id, delivered_date)
WHEN customer_id < 'Q' THEN
INTO cust_ip
VALUES (customer_id, program_id, delivered_date)
WHEN customer_id > 'PZZZ' THEN
INTO cust_qz
VALUES (customer_id, program_id, delivered_date)
SELECT program_id, delivered_date, customer_id, order_date
FROM airplanes;

SELECT customer_id, COUNT(*)
FROM cust_ah
GROUP BY customer_id;

SELECT customer_id, COUNT(*)
FROM cust_ip
GROUP BY customer_id;

SELECT customer_id, COUNT(*)
FROM cust_qz
GROUP BY customer_id;

 

INSERT WITH CHECK OPTION

Note: Use WITH CHECK OPTION to indicate that Oracle prohibits any changes to the table or view that would produce rows that are not included in the subquery

CHECK OPTION demo

INSERT INTO (
<SQL_statement> WITH CHECK OPTION)
VALUES
(value_list);

CREATE TABLE dept (
deptno NUMBER(2),
dname VARCHAR2(15),
loc    VARCHAR2(15));

INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
COMMIT;

SELECT * FROM dept;

INSERT INTO (
SELECT deptno, dname, loc
FROM dept
WHERE deptno < 30)
VALUES (98, 'TRAVEL', 'SEATTLE');

SELECT * FROM dept;

INSERT INTO (
SELECT deptno, dname, loc
FROM dept
WHERE deptno < 30 WITH CHECK OPTION)
VALUES (99, 'TRAVEL', 'SEATTLE');

SELECT * FROM dept;

 

Anonymous Block Insert

Demo Insert Statement

BEGIN
   <INSERT Statements>
END;
/

TRUNCATE TABLE zip_new;

-- copy the following 10 lines into SQL*Plus as is:
INSERT INTO zip_new
VALUES ('98101', 'WA', 'Seattle');
INSERT INTO zip_new
VALUES ('98004', 'WA', 'Bellevue');
INSERT INTO zip_new
VALUES ('98040', 'WA', 'Mercer Is.');
INSERT INTO zip_new
VALUES ('98072', 'WA', 'Woodinville');
INSERT INTO zip_new
VALUES ('98065', 'CA', 'Los Angeles');

SELECT * FROM zip_new;

ROLLBACK;

BEGIN
INSERT INTO zip_new
VALUES ('98101', 'WA', 'Seattle');
INSERT INTO zip_new
VALUES ('98004', 'WA', 'Bellevue');
INSERT INTO zip_new
VALUES ('98040', 'WA', 'Mercer Is.');
INSERT INTO zip_new
VALUES ('98072', 'WA', 'Woodinville');
INSERT INTO zip_new
VALUES ('98065', 'CA', 'Los Angeles');
END;
/

SELECT * FROM zip_new;

 

Inserting Into A SELECT Statement

Demo Insert Statement

INSERT INTO (<SELECT Statement>);

SELECT empno, ename, job, sal, deptno
FROM emp;

INSERT INTO (
SELECT empno, ename, job, sal, deptno
FROM emp)
VALUES
(1, 'MORGAN', 'DBA', '1', 40);

SELECT * FROM emp;

 

Create Table INSERT (CTAS)

Demo Table Creation Insert

CREATE TABLE <table_name> AS <SELECT Statement>;

CREATE TABLE servers_bakAS
SELECT *
FROM servers;

desc servers_bak

SELECT COUNT(*)
FROM servers_bak;

 

INSERT With Returning Clause

Demo Insert With Returning Clause

INSERT INTO <table_name>
(column_list)
VALUES
(values_list)
RETURNING <value_name>
INTO <variable_name>;

-- Use emp table from INSERT-WHEN demo above

CREATE SEQUENCE seq_emp;

set serveroutput on

DECLARE
x emp.empno%TYPE;
BEGIN
INSERT INTO emp
(empno, ename)
VALUES
(seq_emp.NEXTVAL, 'Morgan')
RETURNING empno
INTO x;

dbms_output.put_line(x);
END;
/

DECLARE
r rowid;
BEGIN
INSERT INTO emp
(empno, ename)
VALUES
(seq_emp.NEXTVAL, 'Morgan')
RETURNING rowid
INTO r;

dbms_output.put_line(r);
END;
/

DECLARE
x emp.empno%TYPE;
r rowid;
BEGIN
INSERT INTO emp
(empno, ename)
VALUES
(seq_emp.NEXTVAL, 'Morgan')
RETURNING rowid, empno
INTO r, x;

dbms_output.put_line(r);
dbms_output.put_line(x);
END;
/

/

/

/

 

Inserting Dates

Date Format Specification

CREATE TABLE t (
scol VARCHAR2(20),
dcol DATE);

INSERT INTO t
(scol, dcol)
VALUES
('Test', TO_DATE('01-12-2007', 'MM-DD-YYYY'));

SELECT * FROM t;

INSERT INTO t
(scol, dcol)
VALUES
('Test', TO_DATE('01-12-2007', 'DD-MM-YYYY'));

SELECT * FROM t;

INSERT INTO t
(scol, dcol)
VALUES
('Test', DATE '2007-07-01');

SELECT * FROM t;

 

 

为了您的安全，请只打开来源可靠的网址

打开网站   取消

来自: http://hi.baidu.com/redflower1004/blog/item/47434903dba68382d53f7cc8.html