sql cookbook实验记录（6）

环境介绍：

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

目前可以下载到的最新版本啦！

---

关于Insert default列

创建以下表T2
scott@ORCL>create table t2 (id integer default 1,name varchar2(20));

Table created.

简单测试
当然insert要指定足够的值了。
scott@ORCL>insert into t2 values (default);
insert into t2 values (default)
            *
ERROR at line 1:
ORA-00947: not enough values

插入合适的值
scott@ORCL>insert into t2 values(default,'hdp');

1 row created.

这里使用下default关键字 
scott@ORCL>select id,name from t2;

	ID NAME
---------- --------------------
	 1 hdp

另外default列其实是可以插入null值的。
scott@ORCL>insert into t2 values(null,'tom');

1 row created.

scott@ORCL>select * from t2;

	ID NAME
---------- --------------------
	 1 hdp
	   tom
	  
再而三，default列在插入时，可以被忽略。
scott@ORCL>insert into t2(name) values('jay');

1 row created.

scott@ORCL>select * from t2;

	ID NAME
---------- --------------------
	 1 hdp
	   tom
	 1 jay
default列可以自动赋值。所以可以忽略。当然如果你想指定列值为NULL，那就明确指出。



CTAS 复制表结构
scott@ORCL>create table t2_bak as select * from t2 where 1=2;

Table created.

scott@ORCL>desc t2_bak;
 Name											        Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ID											         NUMBER(38)
 NAME											         VARCHAR2(20)

scott@ORCL>


在where中指定false条件，可以生成没有数据的表。


scott@ORCL>insert into t2_bak
  2  select * from t2;

3 rows created.

scott@ORCL>select * from t2_bak;

	ID NAME
---------- --------------------
	 1 hdp
	   tom
	 1 jay

scott@ORCL>insert into t2_bak values(null,'hebe');

1 row created.

scott@ORCL>insert into t2_bak(name) values('janson');

1 row created.


scott@ORCL>select * from t2_bak;

	ID NAME
---------- --------------------
	 1 hdp
	   tom
	 1 jay
	   hebe
	   janson


这里可以看出来ctas创建表的方式，default属性没有带过来。所以这里如果要创建一个一模一样的表，最好把创建表的SQL抓取出来。这样保证所有的属性不丢失。


scott@ORCL>alter table t2_bak modify id default 1;

Table altered.

insert into t2_bak(name) values('Stefanie');

scott@ORCL>select * from t2_bak;

	ID NAME
---------- --------------------
	 1 hdp
	   tom
	 1 jay
	   hebe
	   janson
	 1 Stefanie

6 rows selected.


DBMS_METADATA.GET_DDL的用法
SELECT DBMS_METADATA.GET_DDL('TABLE','T2') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('TABLE','T2','SCOTT') FROM DUAL;

---

insert all,insert first的用法

delete from t2_bak;

select * from t2_bak;

when中出现的列，也要在后面select列表中出现 所以最好是select *,或者select 需要的列表
insert all
when id in (1,2)then  into t2_bak(name) values(name)
select id,name from t2;


select * from t2_bak;

insert all
when id in (1,2)then  into t2_bak(name) values(name)
select * from t2;



select * from t2_bak;

这里不在默认使用的default列
insert all
when id in (1,2)then  into t2_bak(id,name) values(id,name)
select * from t2;


select * from t2_bak;

输出的值

scott@ORCL>select * from t2_bak;

	ID NAME
---------- --------------------
	 1 hdp
	 1 jay
	 1 hebe
	 1 hdp
	 1 jay
	 1 hebe
	 1 hdp
	 1 jay
	 2 hebe

9 rows selected.

insert first,insert all 在这里虽然会输出相同的结果，但是insert first在遇到第一个when条件为真时，会跳出when-then-else的循环。

---

研究下19C创建TABLE背后做了哪些事情

scott@ORCL>create table t3(id number PRIMARY KEY using INDEX ,name varchar2(20),salary number); 

Table created.

scott@ORCL>SELECT DBMS_METADATA.GET_DDL('TABLE','T3','SCOTT') FROM DUAL;

其输出如下：

CREATE TABLE "SCOTT"."T3"
(
    "ID"        NUMBER,
    "NAME"      VARCHAR2 (20),
    "SALARY"    NUMBER,
    PRIMARY KEY
        ("ID")
        USING INDEX
            PCTFREE 10
            INITRANS 2
            MAXTRANS 255
            STORAGE (INITIAL 65536
                     NEXT 1048576
                     MINEXTENTS 1
                     MAXEXTENTS 2147483645
                     PCTINCREASE 0
                     FREELISTS 1
                     FREELIST GROUPS 1
                     BUFFER_POOL DEFAULT
                     FLASH_CACHE DEFAULT
                     CELL_FLASH_CACHE DEFAULT)
            TABLESPACE "USERS"
        ENABLE
)
SEGMENT CREATION IMMEDIATE
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
NOCOMPRESS
LOGGING
STORAGE (INITIAL 65536
         NEXT 1048576
         MINEXTENTS 1
         MAXEXTENTS 2147483645
         PCTINCREASE 0
         FREELISTS 1
         FREELIST GROUPS 1
         BUFFER_POOL DEFAULT
         FLASH_CACHE DEFAULT
         CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS";