博客 聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表-SQL开发实战系列(八)

聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表-SQL开发实战系列(八)

   数栈君   发表于 2023-02-28 18:35  214  0

一、插入数据
先创建一个测试表,然后插入一条数据

create table test(
aa varchar2(20) default 'aa',
bb varchar2(20) default 'bb',
cc varchar2(20) default 'cc',
dd date default sysdate
);
insert into test(aa,bb,cc) values(default,null,'zyd');
SQL> select * from test;

AA BB CC DD
-------------------- -------------------- -------------------- -----------
aa zyd 2023-2-7 16

注意以下几点:

如果INSERT语句中没有含默认值的列,则会添加默认值,如dd列。
如果包含有默认值的列,需要用DEFAULT关键字,才会添加默认值,如aa列。
如果已显示设定了NULL或其他值,则不会再生成默认值,如bb列。
建立表时,有时明明设定了默认值,可生成的数据还是NULL,原因在于我们在代码中不知不觉地加入了NULL。
二、阻止对某几列插入
这种需求我们可以创建一个视图,视图中暴露出来可以变更的列,然后DML操作均基于此视图来做,看下面这个案例:

SQL> insert into vtest values('a','b',sysdate);

1 row inserted


SQL> select * from vtest;

AA BB DD
-------------------- -------------------- -----------
aa 2023-2-7 16
a b 2023-2-7 16

SQL> insert into vtest values('c',default,sysdate);
insert into vtest values('c',default,sysdate)

ORA-32575: 对于正在修改的视图, 不支持显式列默认设置


ok,看上面的操作我们可以通过视图实现需求,但是视图这种方式有个弊端就是通过VIEW新增数据,不能再使用关键字DEFAULT

三、复制表的定义和数据以及注意事项
我们可以用以下语句复制表 TEST:

create table test2 as select * from test;
1
也可以先复制表的定义,再新增数据:

create table test3 as select * from test where 1=2;
insert into test3 select * from test;
1
2
但是!看下面这个操作:

SQL> create table test4 as
2 select '1' as aa, null bb, '' cc, sysdate dd from test;
create table test4 as
select '1' as aa, null bb, '' cc, sysdate dd from test

ORA-01723: 不允许长度为 0 的列

当我们CTAS操作的时候,如果你的字段中包含null时候,会创建失败,因为你没有指定这个列的数据类型, oracle咋个推断你的数据类型呢?

四、比创建约束功能更强大!用 WITH CHECK OPTION限制数据录入
当约束条件比较简单时,可以直接加在表中,如工资必须>0:

SQL> alter table emp add constraints ck_sal check(sal > 0);
Table altered
1
2
但有些复杂或特殊的约束条件是不能这样放在表里的,如雇佣日期大于当前日期

SQL> alter table emp add constraints ck_hiredate check(hiredate>sysdate);

ORA-02436: 日期或系统变量在 CHECK 约束条件中指定错误

这时我们可以使用加了WITH CHECK OPTION关键字的VIEW来达到目的。下面的示例中,我们限制了不符合内联视图条件的数据(SYSDATE+1):

SQL> insert into (
2 select * from test where dd<=sysdate with check option
3 )
4 select 'a1','b1','c1',sysdate
5 from dual;

1 row inserted


SQL> select * from test;

AA BB CC DD
-------------------- -------------------- -------------------- -----------
aa zyd 2023-2-7 16
a b cc 2023-2-7 16
a1 b1 c1 2023-2-7 17


SQL> rollback;

Rollback complete


SQL> select * from test;

AA BB CC DD
-------------------- -------------------- -------------------- -----------
aa zyd 2023-2-7 16
a b cc 2023-2-7 16

SQL>
SQL> insert into (
2 select * from test where dd<=sysdate with check option
3 )
4 select 'a1','b1','c1',sysdate+1
5 from dual;
insert into (
select * from test where dd<=sysdate with check option
)
select 'a1','b1','c1',sysdate+1
from dual

ORA-01402: 视图 WITH CHECK OPTION where 子句违规

因为里面有关键字WITH CHECK OPTION,所以INSERT的数据不符合其中的条件(hiredate<=SYSDATE)时,就不允许利用INSERT。
当规则较复杂,无法用约束实现时,这种限制方式就比较有用。

五、如何一个insert将数据同时插入多个表
多表插入语句分为以下四种:

无条件INSERT
有条件INSERT ALL
转置INSERT
有条件INSERT FIRST
接下来先创建两个临时表

create table empa as select empno,ename,job from emp where 1=2;
create table empb as select empno,ename,deptno from emp where 1=2;
1
2
无条件INSERT
INSERT ALL
into empa(empno,ename,job)VALUES(empno,ename,job)
into empb(empno,ename,deptno)VALUES(empno,ename,deptno)
SELECT empno,ename,job,deptno FROM emp WHERE deptno in (10,20);
16 rows inserted

SQL> select * from empa;

EMPNO ENAME JOB
----- ---------- ---------
7369 SMITH CLERK
7566 JONES MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7876 ADAMS CLERK
7902 FORD ANALYST
7934 MILLER CLERK

8 rows selected


SQL> select * from empb;

EMPNO ENAME DEPTNO
----- ---------- ------
7369 SMITH 20
7566 JONES 20
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7876 ADAMS 20
7902 FORD 20
7934 MILLER 10

8 rows selected


SQL> rollback;

Rollback complete

因为没有加条件,所以会同时向两个表中插入数据,且两个表中插入的条数一样。

有条件 INSERT ALL
INSERT ALL
when job in('SALESMAN','MANAGER') then
into empa(empno,ename,job)VALUES(empno,ename,job)
when deptno in('20','30')then
into empb(empno,ename,deptno)VALUES(empno,ename,deptno)
SELECT empno,ename,job,deptno FROM emp WHERE deptno in (10,20);
7 rows inserted


SQL> select * from empa;

EMPNO ENAME JOB
----- ---------- ---------
7566 JONES MANAGER
7782 CLARK MANAGER

SQL> select * from empb;

EMPNO ENAME DEPTNO
----- ---------- ------
7369 SMITH 20
7566 JONES 20
7788 SCOTT 20
7876 ADAMS 20
7902 FORD 20

SQL> rollback;

Rollback complete

当增加条件后,就会按条件插入。如EMPNO=7654等数据在两个表中都有。

INSERT FIRST就不一样
INSERT FIRST
when job in('SALESMAN','MANAGER') then
into empa(empno,ename,job)VALUES(empno,ename,job)
when deptno in('20','30')then
into empb(empno,ename,deptno)VALUES(empno,ename,deptno)
SELECT empno,ename,job,deptno FROM emp WHERE deptno in (10,20);

6 rows inserted


SQL> select * from empa;

EMPNO ENAME JOB
----- ---------- ---------
7566 JONES MANAGER
7782 CLARK MANAGER

SQL> select * from empb;

EMPNO ENAME DEPTNO
----- ---------- ------
7369 SMITH 20
7788 SCOTT 20
7876 ADAMS 20
7902 FORD 20

SQL> rollback;

INSERT FIRST语句中,当第一个表符合条件后,第二个表将不再插入对应的行,表empb中不再有与表empa相同的数据EMPN0=7654,这就是INSERT FIRST与INSERT ALL的不同之处。

转置INSERT与其说是一个分类,不如算作"INSERT ALL"的一个用法。
创建一个临时表

create table deptb(
dname varchar2(200),
remark varchar2(200)
);
insert all
into deptb values(aa,'aa')
into deptb values(bb,'bb')
into deptb values(cc,'cc')
select * from (select 'zhaoyd' aa,'zhaoyandong' bb ,'赵延东'cc from dual
);
SQL> select * from deptb;

DNAME REMARK
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
zhaoyd aa
zhaoyandong bb
赵延东 cc


可以看到,转置INSERT的实质就是把不同列的数据插入到同一表的不同行中。

总结
本章主要是介绍关于insert的一系列常用操作。
————————————————

免责申明:

本文系转载,版权归原作者所有,如若侵权请联系我们进行删除!

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友,浏览袋鼠云官网:https://www.dtstack.com/?src=bbs
同时,欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」,交流最新开源技术信息,群号码:30537511,项目地址:https://github.com/DTStack

0条评论
社区公告
  • 大数据领域最专业的产品&技术交流社区,专注于探讨与分享大数据领域有趣又火热的信息,专业又专注的数据人园地

最新活动更多
微信扫码获取数字化转型资料
钉钉扫码加入技术交流群