oracle insert all,oracle与mysql语法不同之处

oracle insert all,oracle与mysql语法不同之处详细介绍

本文目录一览： Oracle insert all用法简介

insert all是oracle中用于批量写数据的
现在直接通过例子学习一下，比较简单直观，例子来自《收获，不止SQL优化》一书
环境准备
创建两张测试表，不用写数据
然后演示一下insert all的用法
无条件写数据的情况
有条件写数据的情况
insert first insert first情况，介绍一下insert first的用法，insert first用法和insert all类似，区别的是insert first多了筛选的步骤，简单来说就是和insert all一样，符合条件的同样会写数据，不过已经存在数据了，insert first是不会写入的，而insert all是会出现重复数据的情况
pivoting insert 然后再演示一下pivoting insert的情况，pivoting insert可以说是insert all的一直特殊情况，不过oracle官方还是区分出来，pivoting insert可以翻译为旋转写入，名称的不重要，看一下例子就懂了
环境准备
按照条件进行写数据

oracle的多表插入

　　在业务处理过程中 经常会碰到将业务数据按照条件分别插入不同的数据表的问题 按照传统的处理方式 需要分条件执行多次检索后分别插入不同的表单 这样因为执行了重复的检索造成cpu和内存的浪费 从oracle i开始引入了insert all关键字支持将某张表的数据同时插入多张表单 语法如下
　　Insert all Insert_into_clause [value_clause] subquery;
　　Insert conditional_insert_clause subquery;
　　如上所示 insert_into_clause用于指定insert子句 value clause用于指定值子句 subquery用于指定提供数据的子查询 condition_insert_clause用于指定insert条件子句
　　当使用all操作符执行多表插入时 在每个条件子句上都要执行into子句后的子查询 并且条件中使用的列必须在插入和子查询的结果集中

　　 创建测试用表
　　createtable tdate(
　　idvarchar ( )
　　namevarchar ( )
　　birthday datedefaultsysdate
　　);
　　 插入数据
　　insertinto tdate values( zhangsan to_date( YYYY MM DD ));
　　insertinto tdate values( zhangsan to_date( YYYY MM DD ));
　　insertinto tdate values( lisi to_date( YYYY MM DD ));
　　insertinto tdate values( wangwu default);
　　insertinto tdate(id name) values( zhangsan );
　　mit;
　　 创建接收用测试表
　　createtable tdate asselect * from tdate where = ;
　　createtable tdate asselect * from tdate where = ;
　　mit;
　　 使用all关键字执行多表插入操作
　　insertall
　　when birthday > 月 theninto tdate
　　when birthday < 月 theninto tdate
　　whenname = zhangsan theninto tdate
　　whenname = lisi theninto tdate
　　select * from tdate;
　　在上述操作语句中 如果原表tdate中存在既满足birthday > 月 又满足name = zhangsan 的数据 那么将执行两次插入 而使用first关键字就可以避免这个问题 使用first关键字时 如果有记录已经满足先前条件 并且已经 *** 入到某个表单中（未必非要是同一个表） 那么该行数据在后续插入中将不会被再次使用 也就是说使用first关键字 原表每行数据按照执行顺序只会 *** 入一次
　　insertfirst
　　when birthday > 月 theninto tdate
　　when birthday < 月 theninto tdate
　　whenname = zhangsan theninto tdate
　　whenname = lisi theninto tdate
lishixinzhi/Article/program/Oracle/201311/18168

在Oracle中，通过一个INSERT ALL语句批量插入数据

Oracle:
INSERT ALL
INTO A(field_1,field_2) VALUES (value_1,value_2)
INTO A(field_1,field_2) VALUES (value_3,value_4)
INTO A(field_1,field_2) VALUES (value_5,value_6)
SELECT 1 FROM DUAL;

在Mybatis中使用

<insert id="insertData" parametertype="java.util.List"
useGeneratedKeys="false">

INSERT ALL

INTO T_GZL_BZ

(

ID,

JGH,

JGMC,

DW,

JGLX,

BZLB,

JGZWLB,

EDGZL,

ZGGZL,

BZ,

CJRBH,

CJRXM,

CJSJ,

SFSZ

) VALUES

(

#{item.id,jdbcType=VARCHAR},

#{item.teacherId,jdbcType=VARCHAR},

#{item.teacherName,jdbcType=VARCHAR},

#{item.departmentId,jdbcType=VARCHAR},

#{item.teacherType,jdbcType=VARCHAR},

#{item.compilationCategory,jdbcType=VARCHAR},

#{item.jobCategory,jdbcType=VARCHAR},

#{item.ratedWorkload,jdbcType=NUMERIC},

#{item.maxWorkload,jdbcType=NUMERIC},

#{item.remark,jdbcType=VARCHAR},

#{item.creator,jdbcType=VARCHAR},

#{item.creatorName,jdbcType=VARCHAR},

#{item.createTime,jdbcType=DATE},

#{item.settings,jdbcType=VARCHAR}

)

SELECT 1 FROM DUAL

mapper：
/**
* @param createList
* @return
*/
int insertData(@Param("list") List

createList);

oracle insert into 脚本怎么写

insert into table1 (a,b,c,d) values('','','','') 这是指定列名的，当然之前自己要知道列是什么类型的才好插入；
insert into table1 values(XXXXXXXXXXXXXXXXXXXXXX) 这是插全部字段的，后面括号里要一一对应，但开发不建议这样写，因为如果业务表增加一个字段，那么这个SQL就废了
insert into XXXX
Select A,B, ..............
与Mysql,sqlserver一样的语法。
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;

oracle与mysql语法不同之处

insert all
into dept(deptno,dname) values(50,'测试一部')
into dept(deptno,dname) values(51,'测试二部')
SELECT 1 FROM DUAL;

insert all into dept(deptno,dname)
into dept(deptno,dname) values(60,'测试一部')
into dept(deptno,dname) values(61,'测试二部')
SELECT 62,'测试三' FROM DUAL;

begin
insert into dept(deptno,dname) values(70,'测试一部');
insert into dept(deptno,dname) values(71,'测试二部');
end;

oracle的序列 sequences(mysql自增长)
sequences的使用分两步
1、新建一个序列(工具建)
2、如何使用

3、函数对比

https://blog.csdn.net/qq_39137554/article/details/76034335
3.1）数学函数
oracle: ceil(-1.001) mysql: ceiling(-1.001)
3.2)字符串函数

1）ascii(str)一样：返回一个字符对应的ascii码值
2）chr||char （不同）
ORACLE:chr(97) MYSQL:char(97) 返回这个整数所代表的 ASCII 码值
oracle示例：

3）INSTR||locate（mysql新增 mysql可以用instr locate）
INSTR(s1,s2,[,n1],[n2]) 返回s1中，子串s2从n1开始，第n2次出现的位置。n1,n2默认值为1
oracle例子：

-- 查询s字符串在sdsq中第一次出现的位置，从头开始查找,索引从1开始计算 返回1
select INSTR('sdsq','s') value from dual;
-- 查询s字符串在sdsq中第一次出现的位置，从索引从2开始查找 返回3
select INSTR('sdsq','s',2) value from dual;
-- 查询s字符串在sdsq中第一次出现的位置，从索引从2开始查找，如果没有查找到，返回0
select INSTR('sdsq','s',2,2) value from dual;

mysql:
instr||locate

4）length||char_length(不同)
SELECT length('AAAASDF') VALUE FROM DUAL;

CONCAT：连接

LPAD：在左边加点内容（左追加）

RPAD：在右边加点内容（右追加）
把sal变成10位，不足10位的，右侧 补*
select RPAD(sal,10,'*') as v from emp;

REPLACE 替换 一样
LOWER 转小写 一样
UPPER 转大写 一样
initcap首字母大写

SELECT REPLACE('13800', '0400', '****') value from dual;
select LOWER(ename) username from emp; 转小写
select UPPER(ename) username from emp;
select initcap(ename) username from emp;

3.2.4.1TRIM:去掉首尾的指定字符，不能去除中间的
oracle:trim(子串 from 父串)

select TRIM('S' FROM 'SSMISSTH') value from dual; //MISSTH
select TRIM(' ' FROM ' a b c ') value from dual; //a b c

3.2.4.2SUBSTR(oracle)||substring(mysql)

SUBSTR(原字符串，开始处位置，截取长度)
select SUBSTR('String',2,5) value from dual; //tring

ORACLE:
练习1：
1.写一个查询,用首字母大写，其它字母小写显示雇员的 ename，显
示名字的长度，并给每列一个适当的标签，条件是满足所有雇员名字
的开始字母是J、A 或 M 的雇员，并对查询结果按雇员的ename升序
排序。（提示：使用initcap、length、substr）

练习2 都用函数解决
? 1.查询员工姓名中中包含大写或小写字母A的员工姓名。
select ename
from emp
where instr(ename,'A')>0 OR instr(ename,'a')>0

? 2.查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中
包含大写字母A的员工姓名，员工姓名长度（提示，要求使用INSTR函
数，不能使用like进行判断)

select ename,length(ename) len
from emp
where deptno in(10,20)
and hiredate>'1-5月-1981'
and instr(ename,'A')>0;

? 3.查询每个职工的编号,姓名，工资
– 要求将查询到的数据按照一定的格式合并成一个字符串.
– 前10位：编号,不足部分用 填充,左对齐(右填充)
– 中间10位：姓名，不足部分用 填充,左对齐
– 后10位：工资，不足部分用*填充,右对齐

select rpad(empno,10,' ')||rpad(ename,10,' ')||lpad(sal,10,'*') as value
from emp;

select concat(concat(rpad(empno,10,' ') , rpad(ename,10,' ')),lpad(sal,10,'*')) as value
from emp;

https://www.cnblogs.com/aipan/p/7941917.html
3.4.1 to_char vs date_format/ time_format

select to_char(hiredate,'YYYY-MM-DD HH24:MI:SS') VALUE FROM emp;
select to_char(-125.8, '999D99S') from dual;

3.4.2 to_date vs STR_TO_DATE(str,format)

select to_date('2021-04-23 15:12:20','YYYY-MM-DD HH24:MI:SS') VALUE FROM DUAL;

3.4.3 to_number vs CAST("123" AS SIGNED INTEGER)
select to_number('12,454.8-', '99G999D9S') value from dual;
select to_number('-12,454.8', 'S99G999D9') value from dual;

1.显示服务器系统当前时间，格式为2007-10-1217:11:11(提示：使用to_char函数)
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')
from dual;

3.查询员工姓名，工资，格式化的工资（￥999,999.99）(提示：使用to_char函数)

select to_char(sal,'L999G999D99')
from emp;

4.把字符串2015-3月-18 13:13:13 转换成日期格式
select to_date('2015-3月-18 13:13:13','YYYY-MON-DD HH24:MI:SS')
from dual;

select cast(11 as unsigned int) / 整型 /
select cast(11 as decimal(10,2)) / 浮点型 /

3.5.1 nvl/COALESCE vs ifnull
**NVL函数等价于mysql的ifnull
– 语法:NVL (expr1,expr2)
– 如果expr1不是null,返回expr1,否则返回expr2

select comm,nvl(comm,0) from emp;

select comm,coalesce(comm,sal,0) from emp;

3.5.2 nvl2 vs if
NVL2函数
– 语法:NVL2(expr1,expr2,expr3)
– 如果expr1不是null,返回expr2,否则返回expr3

select nvl2(comm,'无奖金','有奖金') from emp;

3.5.3 NULLIF vs 无(可以考虑用if 建议了解，不用掌握)
NULLIF函数
– 语法: NULLIF(expr1,expr2)
– 比较两个表达式,如果相等，返回null,否则，返回第一个表达式

3.5.4 DECODE（解密的意思）/case vs if/case 多分支情况
DECODE(字段| 表达式, 条件1,结果1[,条件2,结果2…，][,缺省值] )

SELECT ename, deptno,
decode(deptno,
10,' 销售部',
20,' 技术部',
30 , ' 管理部',
' 无') deptname
FROM emp;

CASE expr
WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END

SELECT ename, deptno,
(CASE deptno
WHEN 10 THEN ' 销售部'
WHEN 20 THEN ' 技术部'
WHEN 30 THEN ' 管理部'
ELSE ' 无' END) deptname FROM emp;

EXTRACT ([YEAR] [MONTH][DAY] FROM [日期表达式])

SELECT ename, sal, MONTHS_BETWEEN(SYSDATE,hiredate) months
FROM emp
ORDER BY months;

SELECT ename, sal, hiredate, ADD_MONTHS(hiredate,3) new_date
FROM emp
WHERE hiredate>'01-1月-82';

SELECT NEXT_DAY(sysdate,'星期二') NEXT_DAY
FROM DUAL;

SELECT Last_DAY('1-2月-2019') last_DAY
FROM DUAL;
select EXTRACT (YEAR FROM HIREDATE) AS 年 from emp;

select EXTRACT (YEAR FROM HIREDATE) AS 年 from emp;
select EXTRACT (MONTH FROM HIREDATE) AS 年 from emp;
select EXTRACT (DAY FROM HIREDATE) AS 年 from emp;

4、外连接的加号定法。oralce支持，mysql不支持

查询所有雇员姓名，部门编号，部门名称， 包括没有员工
的部门也要显示出来
select e.ename,d.deptno,d.dname
from emp e,dept d
where e.deptno(+)=d.deptno;

4、分页的处理

比较日期相等
https://www.cnblogs.com/bailing80/p/11440927.html

oracle中是否可以同时向多个表中插入数据

请描述需求准确些哥们
-----可以一条insert插入多个表，使用insert
all或者insert
first，但是这种插入，有限制，就是插入的values必须从select出的data中获取，比如，insert
all
into
stu
values(id,name)
into
class(id)
values(id)
select
id,name
from
stu;这个select关键字在insert
all和insert
first中必须存在，具体使用方法可以查oracle手册或者直接百度。

oracle MERGE into跟insert all有什么区别

MERGE into 的操作包含了update,和insert操作，如果数据存在就执行update，如果数据不存在就执行insert操作
合并,自然是有的放过没有的添上,插入就是不管三七二十一,全塞进去再说
楼上正解

mybatis 批量操作数据

mybatis的批量操作有两种方式，一是使用foreach标签，二是使用mybatis的BATCH模型
在xml中通过foreach对表数据进行循环操作
在oracle中不支持insert into product(name, type, price) values ('a', 'tv', 1233), ('b', 'ac', 3455),....('','','')这种形式的sql，因此oracle批量插入使用 insert all into table(...) values(...) into table(...) values(...) select * from dual; 语句来解决以下方式，并且需要显示指定useGeneratedKeys=false
另一种方法是使用另外一种方法是 insert into table(...) (select ... from dual) union all (select ... from dual)
Mybatis内置的ExecutorType一共有三种，默认为SIMPLE，该模式下它为每个语句的执行创建一个新的预处理语句，并单条提交sql。
而 BATCH 模式会重复使用已经预处理的语句，并且批量执行所有更新语句，显然batch性能将更优； 注意 ： batch模式也有自己的问题，比如在Insert操作时，在事务没有提交之前，是没有办法获取到自增的id，这在某型情形下是不符合业务要求的。 具体用法如下：
ProductMapper.java 如下：
mapper.xml 如下：

oracle的grant语句，一次只能授权一张表的访问权限吗

grant语句，一次只能对一个对象进行赋权；不能同时对两个表进行赋权；但可以同时赋权多个不同的权限。解决方法如下：
1、在使用oracle数据库的时候，权限是极其重要的，如果新建的用户如果没有权限，甚至都无法登录数据库。
2、为新建用户分配权限主要有三种connect主要使用是让用户可以连接到数据库 ，resource主要使用是让用户可以创建表，dba它是一种比较特殊的权限，普通用户拥有之后可以成为数据库管理者。
3、还有一种权限主要是针对表的权限，select update delete insert all也就是拥有用户对某张表有查询 、更改、删除、插入及所有权限。
4、如果想要查询用户的权限可以通过下图中的方法进行查询。
5、与grant相对应的就是收回权限使用revoke如下图。

如何从oracle库某表中取值，插入到另外的表中

insert into dev_a
select devid,代码,0,0,0
from scd
where 类型 = 1
INSERT ALL
WHEN 类型 = 1 THEN
INTO dev_a
VALUES(devid,代码,0,0,0,)
WHEN 类型 = 2 THEN
INTO dev_b
VALUES(devid,代码,0,0,0,)
WHEN 类型 = 3 THEN
INTO dev_c
VALUES(devid,代码,0,0,0,)
SELECT devid,代码，类型
FROM scd
具体的字段你改改