- 浏览: 771961 次
- 性别:
- 来自: 广州
文章分类
最新评论
转载Oracle笔记-Multitable INSERT 的用法
http://blog.chinaunix.net/uid-8504518-id-3310531.html
一、Insert基础用法
语法:
Insert Into 表名 (字段1,字段2,字段3...)
Values (值1,值2,值3...)
例子:
INSERT INTO departments(department_id, department_name,
manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);
语法:
Insert Into 表名 (字段1,字段2,字段3...)
select 语句
不做任何解释,实在是没啥好说的〇_〇,注意别跟create table ...as select一样,insert中的select前面可没as ^_^
二、Unconditional INSERT ALL 用法
直接拿例子了:
INSERT ALL
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID, hire_date HIREDATE,
salary SAL, manager_id MGR
FROM employees
WHERE employee_id > 200;
解释:将select查询出来的结果,每返回一行就分别插入表sal_history 和mgr_history 中,优点就是只做一次查询即可分别查询2个表,假如使用基础用法,将进行2次查询。
三、Conditional INSERT ALL
还是直接拿例子:
INSERT ALL
WHEN SAL > 10000 THEN
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR > 200 THEN
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID,hire_date HIREDATE,
salary SAL, manager_id MGR
FROM employees
WHERE employee_id > 200;
解释:将select查询出来的结果,每返回一行就判断,SAL > 10000 就插入表sal_history ,MGR > 200就插入mgr_history ,优点和前面提到一样。
四、Conditional FIRST INSERT
仍然是例子:
INSERT FIRST
WHEN SAL > 25000 THEN
INTO special_sal VALUES(DEPTID, SAL)
WHEN HIREDATE like ('%00%') THEN
INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN
INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
ELSE
INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL,
MAX(hire_date) HIREDATE
FROM employees
GROUP BY department_id;
解释:将select查询出来的结果,每返回一行就判断,SAL > 25000就插入表special_sal ,否则HIREDATE like ('%00%') ,符合就插入hiredate_history_00,前面2个条件还是不成力,就判断HIREDATE like ('%99%') ,符合就插入表hiredate_history_99 ,前面3个条件都不符合,只好插入表hiredate_history 了。
打完这我都头晕了,假如学过程序设计,看下面的清晰明了:
if SAL > 25000 then
INTO special_sal VALUES(DEPTID, SAL)
else
(
if HIREDATE like ('%00%') THEN
INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
else
(
if HIREDATE like ('%99%') THEN
INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
else
INTO hiredate_history VALUES(DEPTID, HIREDATE)
)
)
假如还看不理解,看官方的这句话吧,“If the first WHEN clause evaluates to true, the subsequent WHEN clauses for this row should be skipped.”
五、Pivoting INSERT
最后还是例子:
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;
解释:老实说,看不出有啥用法,真的非要说,咱就把它当作行列转换吧,如果上面表在加多一列,比如INTO sales_info VALUES (employee_id,week_id,week_which,sales_MON) 改成 INTO sales_info VALUES (employee_id,week_id,'星期一',sales_MON)
多表INSERT语句
1)INSERT...SELECT语句能够作为单个的DML语句的一部分用于插入行到多表中
2)多表INSERT语句能够被用在数据仓库系统中从一个或多个操作源转移数据到一组目的表中
3)Oracle9i引入下面的多表插入语句的类型:
-无条件INSERT
-条件ALL INSERT
-条件FIRST INSERT
-枢轴式(Pivoting)INSERT
无条件INSERT语句
语法:
INSERT ALL
[insert_into_value][values_clause]
(subquery)
例如:
-从emp表中选择empno大于7698雇员的empno,hiredate,sal和mgr值
-用多表INSERT插入这些值到sal_history(empno,hiredate,sal)和mgr_history(empno,mgr,sal)表中
INSERT ALL
INTO sal_history VALUES(empno,hiredate,sal)
INTO mgr_history VALUES(empno,mgr,sal)
SELECT empno,hiredate,sal,mgr
FROM emp
WHERE empno>7698;
有条件INSERT语句
语法
INSERT ALL
[WHEN condition THEN]
[insert_into_clause][values_clause]
(subquery)
例子
-从emp表中选择empno大于7698雇员的empno,hiredate,sal和mgr值
-如果sal大于$2500,用一个条件多表INSERT语句插入这些值到sal_history表中
-如果mgr大于7782,用一个多表INSERT语句插入这些值到mgr_history表中
INSERT ALL
WHEN sal>2500 THEN
INTO sal_history VALUES(empno,hiredate,sal)
WHEN mgr>7782 THEN
INTO mgr_history VALUES(empno,mgr,sal)
SELECT empno,hiredate,sal,mgr
FROM emp
WHERE empno>7698;
条件FIRST INSERT
语法
INSERT FIRST
[WHEN condition THEN]
[insert_into_clause][values_clause]
[ELSE]
[insert_into_clause][values_clause]
(subquery)
例子
-从emp表中选择empno大于7698雇员的empno,hiredate,sal和mgr值
-如果sal大于$2500,则用一个条件FIRST多表INSERT语句插入这些值到sal_history表中
-如果第一个WHEN子句的值为true,则该行后面的WHEN子句被跳过
-如果mgr大于7782,用一个条件FIRST多表INSERT语句插入这些值到mgr_history表中
INSERT FIRST
WHEN sal>2500 THEN
INTO sal_history VALUES(empno,hiredate,sal)
WHEN mgr>7782 THEN
INTO mgr_history VALUES(empno,mgr,sal)
SELECT empno,hiredate,sal,mgr
FROM emp
WHERE empno>7698;
枢轴式(Pivoting) INSERT
支持从非关系数据库表中接受一组销售记录
sales_source_data的格式如下:
empno,week_id,sales_MON,sales_TUE,sales_WED,sales_THUR,sales_FRI
你可能想要以一种典型的相关格式存储这些记录到sales_info(empno,week,sales)表中使用pivoting INSERT,从非关系数据库表转换销售记录集到关系格式
INSERT ALL
INTO sales_info VALUES(empno,week_id,sales_MON)
INTO sales_info VALUES(empno,week_id,sales_TUE)
INTO sales_info VALUES(empno,week_id,sales_WED)
INTO sales_info VALUES(empno,week_id,sales_THUR)
INTO sales_info VALUES(empno,week_id,sales_FRI)
SELECT empno,week_id,sales_MON,sales_TUE,sales_WED,sales_THUR,sales_FRI
FROM sales_source_data;
什么是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,
行转列
http://blog.chinaunix.net/uid-8504518-id-3310531.html
一、Insert基础用法
语法:
Insert Into 表名 (字段1,字段2,字段3...)
Values (值1,值2,值3...)
例子:
INSERT INTO departments(department_id, department_name,
manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);
语法:
Insert Into 表名 (字段1,字段2,字段3...)
select 语句
不做任何解释,实在是没啥好说的〇_〇,注意别跟create table ...as select一样,insert中的select前面可没as ^_^
二、Unconditional INSERT ALL 用法
直接拿例子了:
INSERT ALL
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID, hire_date HIREDATE,
salary SAL, manager_id MGR
FROM employees
WHERE employee_id > 200;
解释:将select查询出来的结果,每返回一行就分别插入表sal_history 和mgr_history 中,优点就是只做一次查询即可分别查询2个表,假如使用基础用法,将进行2次查询。
三、Conditional INSERT ALL
还是直接拿例子:
INSERT ALL
WHEN SAL > 10000 THEN
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR > 200 THEN
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID,hire_date HIREDATE,
salary SAL, manager_id MGR
FROM employees
WHERE employee_id > 200;
解释:将select查询出来的结果,每返回一行就判断,SAL > 10000 就插入表sal_history ,MGR > 200就插入mgr_history ,优点和前面提到一样。
四、Conditional FIRST INSERT
仍然是例子:
INSERT FIRST
WHEN SAL > 25000 THEN
INTO special_sal VALUES(DEPTID, SAL)
WHEN HIREDATE like ('%00%') THEN
INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN
INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
ELSE
INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL,
MAX(hire_date) HIREDATE
FROM employees
GROUP BY department_id;
解释:将select查询出来的结果,每返回一行就判断,SAL > 25000就插入表special_sal ,否则HIREDATE like ('%00%') ,符合就插入hiredate_history_00,前面2个条件还是不成力,就判断HIREDATE like ('%99%') ,符合就插入表hiredate_history_99 ,前面3个条件都不符合,只好插入表hiredate_history 了。
打完这我都头晕了,假如学过程序设计,看下面的清晰明了:
if SAL > 25000 then
INTO special_sal VALUES(DEPTID, SAL)
else
(
if HIREDATE like ('%00%') THEN
INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
else
(
if HIREDATE like ('%99%') THEN
INTO hiredate_history_99 VALUES(DEPTID, HIREDATE)
else
INTO hiredate_history VALUES(DEPTID, HIREDATE)
)
)
假如还看不理解,看官方的这句话吧,“If the first WHEN clause evaluates to true, the subsequent WHEN clauses for this row should be skipped.”
五、Pivoting INSERT
最后还是例子:
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;
解释:老实说,看不出有啥用法,真的非要说,咱就把它当作行列转换吧,如果上面表在加多一列,比如INTO sales_info VALUES (employee_id,week_id,week_which,sales_MON) 改成 INTO sales_info VALUES (employee_id,week_id,'星期一',sales_MON)
多表INSERT语句
1)INSERT...SELECT语句能够作为单个的DML语句的一部分用于插入行到多表中
2)多表INSERT语句能够被用在数据仓库系统中从一个或多个操作源转移数据到一组目的表中
3)Oracle9i引入下面的多表插入语句的类型:
-无条件INSERT
-条件ALL INSERT
-条件FIRST INSERT
-枢轴式(Pivoting)INSERT
无条件INSERT语句
语法:
INSERT ALL
[insert_into_value][values_clause]
(subquery)
例如:
-从emp表中选择empno大于7698雇员的empno,hiredate,sal和mgr值
-用多表INSERT插入这些值到sal_history(empno,hiredate,sal)和mgr_history(empno,mgr,sal)表中
INSERT ALL
INTO sal_history VALUES(empno,hiredate,sal)
INTO mgr_history VALUES(empno,mgr,sal)
SELECT empno,hiredate,sal,mgr
FROM emp
WHERE empno>7698;
有条件INSERT语句
语法
INSERT ALL
[WHEN condition THEN]
[insert_into_clause][values_clause]
(subquery)
例子
-从emp表中选择empno大于7698雇员的empno,hiredate,sal和mgr值
-如果sal大于$2500,用一个条件多表INSERT语句插入这些值到sal_history表中
-如果mgr大于7782,用一个多表INSERT语句插入这些值到mgr_history表中
INSERT ALL
WHEN sal>2500 THEN
INTO sal_history VALUES(empno,hiredate,sal)
WHEN mgr>7782 THEN
INTO mgr_history VALUES(empno,mgr,sal)
SELECT empno,hiredate,sal,mgr
FROM emp
WHERE empno>7698;
条件FIRST INSERT
语法
INSERT FIRST
[WHEN condition THEN]
[insert_into_clause][values_clause]
[ELSE]
[insert_into_clause][values_clause]
(subquery)
例子
-从emp表中选择empno大于7698雇员的empno,hiredate,sal和mgr值
-如果sal大于$2500,则用一个条件FIRST多表INSERT语句插入这些值到sal_history表中
-如果第一个WHEN子句的值为true,则该行后面的WHEN子句被跳过
-如果mgr大于7782,用一个条件FIRST多表INSERT语句插入这些值到mgr_history表中
INSERT FIRST
WHEN sal>2500 THEN
INTO sal_history VALUES(empno,hiredate,sal)
WHEN mgr>7782 THEN
INTO mgr_history VALUES(empno,mgr,sal)
SELECT empno,hiredate,sal,mgr
FROM emp
WHERE empno>7698;
枢轴式(Pivoting) INSERT
支持从非关系数据库表中接受一组销售记录
sales_source_data的格式如下:
empno,week_id,sales_MON,sales_TUE,sales_WED,sales_THUR,sales_FRI
你可能想要以一种典型的相关格式存储这些记录到sales_info(empno,week,sales)表中使用pivoting INSERT,从非关系数据库表转换销售记录集到关系格式
INSERT ALL
INTO sales_info VALUES(empno,week_id,sales_MON)
INTO sales_info VALUES(empno,week_id,sales_TUE)
INTO sales_info VALUES(empno,week_id,sales_WED)
INTO sales_info VALUES(empno,week_id,sales_THUR)
INTO sales_info VALUES(empno,week_id,sales_FRI)
SELECT empno,week_id,sales_MON,sales_TUE,sales_WED,sales_THUR,sales_FRI
FROM sales_source_data;
什么是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,
行转列
发表评论
-
Oracle 10g 的clusterware 32位 下载地址
2013-04-19 23:03 1164Oracle 10g 的clusterware 32位 下载地 ... -
oracle 分析函数 RANK()
2013-04-11 00:05 1066RANK()既是一个聚合函数,也是一个分析函数 其具体的语法 ... -
oracle 分析函数
2013-04-09 23:25 1137分析函数是用于计算一组中多行的聚合值,与聚合函数的区别在于聚合 ... -
批量执行 bulk collect与forall用法
2013-04-08 23:49 1341BULK COLLECT 子句会批量检 ... -
pl/sql集合类型
2013-03-26 10:12 1542--集合类型 /* 单行单列的数据,使用标量变量 单行 ... -
oracle 行链接与行迁移
2013-03-16 01:06 1069表里的一行对于一个数据块太大的情况有二种(一行在一个数据块里放 ... -
oracle Health Monitor
2013-01-20 00:02 1574About Health Monitor Beginning ... -
oracle moving window size与 AWR retention period关系
2013-01-19 15:58 8455转自: http://tomszrp.itpub.net/po ... -
Oracle11.2新特性之INSERT提示IGNORE_ROW_ON_DUPKEY_INDEX
2013-01-12 00:20 2836insert提示IGNORE_ROW_ON_DUPKEY_IN ... -
oracle 11g新特性Flashback data archive
2013-01-09 22:52 30431. 什么是flashback data archive F ... -
RMAN List和report 命令
2012-12-25 00:07 2830LIST 命令 使用RMAN LIST 命令显示有关资料档案库 ... -
oracle ASM中ASM_POWER_LIMIT参数
2012-12-24 23:46 6368ASM_POWER_LIMIT 该初始化参数用于指定ASM例程 ... -
oracle I/O 从属进程
2012-12-24 23:24 1372I/O 从属进程 I/O从 ... -
easy connect 之 ORA-12154: TNS: 无法解析指定的连接标识符
2012-12-19 23:43 5592用easy connect连接出现“tns无法解析指定的连接标 ... -
Flashback Database --闪回数据库
2012-12-19 23:38 1338Flashback 技术是以Undo segment中的内容为 ... -
Oracle 11g新特性:Automatic Diagnostic Repository
2012-12-19 22:35 1349Oracle Database 11g的FDI(Fault D ... -
RMAN配置中通道(CHANNEL)相关参数 PARALLELISM 、FILESPERSET的关系
2012-12-19 22:09 2598RMAN配置中通道(CHANNEL)相 ... -
oracle 空间RESUMABLE
2012-12-14 22:05 3010空间RESUMABLE操作 转 Oracle从9i开始 ... -
oracle 创建视图 with check option
2012-12-13 23:14 1464我们来看下面的例子: create or replace vi ... -
flashback transaction闪回事务查询
2012-11-26 22:00 1463闪回事务查询有别于闪回查询的特点有以下3个: (1) ...
相关推荐
Overview of Multitable INSERT Statements 6-5 Types of Multitable INSERT Statements 6-7 Multitable INSERT Statements 6-8 Unconditional INSERT ALL 6-10 Conditional INSERT ALL 6-11 Conditional FIRST ...
题目要求将NEW_CUST表上所有的记录插入CUST表,credit limit大于10000的记录要同时插入CUST_SPECIAL表,应该使用有条件的insert命令,sql如下: insert all when credit_limit>=10000 into CUST_SPECIAL when 1=1 ...
1. You need to load information about new customers from the NEW_CUST table into the tables CUST ... the multitable INSERT command D. INSERT using WITH CHECK OPTION
支持表格的列排列改变,支持分页,有存储功能,表格主行点击展开,子行显示,嗯,大概就是这样,表头可编辑显示不显示。
联表查询,有分页情况,单个id查询,整体显示,开发格式规范,适合初学者
问题描述:我用oledb的方式向access数据里写数据,示例源码如下: 代码如下:sql=”select * from multitable”;oledbdataadapter olesub=new oledbdataadapter(sql,olecn);oledbcommandbuilder cb1=new ...
你好,mybatis通过仿写mybatis...模块说明: 持久性框架iframework-test自定义框架的使用端测试mybatis-multitable mybatis框架使用,包含CRUD /缓存/分页等2.功能迭代说明2021-03-06初始框架实现,完成数据库查询功能
With a multitable query, SQL Server must sometimes search a table multiple times so each page is scanned much more than once. Having useful indexes speeds up finding individual rows in a table, as ...