# 常见的SQL DDL子句 本章节描述常见的SQL DDL子句兼容项，包含分配扩展子句、约束、取消分配未使用子句、文件规范、日志记录子句、并行子句、物理属性子句、大小子句、存储子句、聚集函数嵌套、创建序列、修改序列，如[表1]所示。 表1常用SQL DDL子句 | Oracle数据库 | GaussDB数据库 | 差异 | |:---|:---|:---| | 分配扩展子句 语法： ``` ALLOCATE EXTENT [ ( { SIZE size_clause | DATAFILE 'filename' | INSTANCE integer } ... ) ] ``` 例如：创建employees表后，改变表的分配扩展size为10M。 ``` SQL> CREATE TABLE employees(EMPLOYEE_ID NUMBER(38), JOB_ID NUMBER(38), SALARY NUMBER(38), LAST_NAME VARCHAR2(16)); Table created. SQL> ALTER TABLE employees ALLOCATE EXTENT (SIZE 10M); Table altered. ``` | 不支持。 | - | | 约束 语法： ``` { inline_constraint | out_of_line_constraint | inline_ref_constraint | out_of_line_ref_constraint } ``` 例如：创建表staff，约束子句中指定ID列、NAME列不为空。 ``` SQL> CREATE TABLE staff(ID INT NOT NULL, NAME char(8) NOT NULL, AGE INT, ADDRESS CHAR(50), SALARY REAL); Table created. ``` | 支持。 | - | | 取消分配未使用子句 语法： ``` DEALLOCATE UNUSED [ KEEP size_clause ] ``` 例如：创建employees表，进行了一些插入、删除操作后，希望使用取消分配未使用子句释放employees表未使用的空间。 ``` SQL> CREATE TABLE employees(EMPLOYEE_ID NUMBER(38), JOB_ID NUMBER(38), SALARY NUMBER(38), LAST_NAME VARCHAR2(16)); Table created. - 进行一些插入、删除操作 SQL> ALTER TABLE employees DEALLOCATE UNUSED; Table altered. ``` | 不支持。 | - | | 文件规范 语法： ``` {[ 'filename' | 'ASM_filename' ] [ SIZE size_clause ] [ REUSE ] [ autoextend_clause ]} | {[ 'filename | ASM_filename' | ('filename | ASM_filename' [, 'filename | ASM_filename' ]...) ] [ SIZE size_clause ] [ BLOCKSIZE size_clause [ REUSE ]} ``` 例如：创建一个临时表空间tbs_temp_01，SQL语句的文件规范子句中指定在表空间中创建一个临时数据库文件temp01.dbf，可以自动扩展，并将表空间分配给表空间组tbs_grp_01。 ``` SQL> CREATE TEMPORARY TABLESPACE tbs_temp_01 TEMPFILE 'temp01.dbf' AUTOEXTEND ON TABLESPACE GROUP tbs_grp_01; Tablespace created. ``` | 不支持。 | - | | 日志记录子句 语法： ``` { LOGGING | NOLOGGING | FILESYSTEM_LIKE_LOGGING } ``` | 部分支持，有差异。 | - GaussDB不支持LOGGING约束子句和FILESYSTEM_LIKE_LOGGING约束子句。 例如： GaussDB创建表，带LOGGING约束子句，语法报错。 ``` gaussdb=# CREATE LOGGING TABLE my_tab(id int, name char(16)); ERROR: syntax error at or near "LOGGING" LINE 1: CREATE LOGGING TABLE my_tab(id int, name char(16)); ^ ``` GaussDB创建表，带FILESYSTEM_LIKE_LOGGING约束子句，语法报错。 ``` gaussdb=# CREATE FILESYSTEM_LIKE_LOGGING TABLE my_tab(id int, name char(16)); ERROR: syntax error at or near "FILESYSTEM_LIKE_LOGGING" LINE 1: CREATE FILESYSTEM_LIKE_LOGGING TABLE my_tab(id int, name cha... ^ ``` - GaussDB仅支持表级的UNLOGGED约束，不支持列级的UNLOGGED约束。 例如：GaussDB创建表，带列级的UNLOGGED约束子句，语法报错。 ``` gaussdb=# CREATE UNLOGGED TABLE my_tab(id int UNLOGGED, name char(16)); ERROR: syntax error at or near "UNLOGGED" LINE 1: CREATE UNLOGGED TABLE my_tab(id int UNLOGGED, name char(16))... ^ ``` - GaussDB仅支持在CREATE TABLE、CREATE TABLE AS、SELECT INTO语句中使用日志记录子句。 例如：GaussDB创建TABLESPACE，带UNLOGGED约束子句，语法报错。 ``` gaussdb=# CREATE UNLOGGED TABLESPACE tbs1 RELATIVE LOCATION 'tablespace1/tablespace_1'; ERROR: syntax error at or near "TABLESPACE" LINE 1: CREATE UNLOGGED TABLESPACE tbs1 RELATIVE LOCATION 'tablespac... ^ ``` | | 并行子句 语法： ``` { NOPARALLEL | PARALLEL [ integer ] } ``` 例如：创建表t1，并在并行子句中指定PARALLEL 4，意为查询和更新表t1时最多使用4个并行进程操作。 ``` SQL> CREATE TABLE t1 (id NUMBER, name VARCHAR2(50)) PARALLEL 4; Table created. ``` | 不支持。 | - | | 物理属性子句 语法： ``` [ { PCTFREE integer | PCTUSED integer | INITRANS integer | storage_clause }... ] ``` | 部分支持，有差异。 | - GaussDB不支持PCTUSED。 例如：执行在表tbl1中创建一个tbl1_ind的索引的SQL语句，并在该语句的物理属性子句中指定索引的空间利用率PCTUSED为20%，GaussDB执行该SQL语句语法报错。 ``` gaussdb=# CREATE INDEX tbl1_ind ON tbl1 (name) PCTUSED 20; ERROR: syntax error at or near "PCTUSED" LINE 1: CREATE INDEX tbl1_ind ON tbl1 (name) PCTUSED 20; ^ ``` - GaussDB 仅支持在CREATE TABLE、CREATE INDEX语句中使用物理属性子句。 例如：尝试从表tbl1中获取数据，创建物化视图tbl1_mv，并在物理属性子句中指定该视图的初始化事务数为30，GaussDB执行该SQL语句语法报错。 ``` gaussdb=# CREATE MATERIALIZED VIEW tbl1_mv INITRANS 30 as select * from tbl1; ERROR: syntax error at or near "INITRANS" LINE 1: CREATE MATERIALIZED VIEW tbl1_mv INITRANS 30 as select * fro... ^ ``` | | 大小子句 语法： ``` integer [ K | M | G | T | P | E ] ``` 例如：创建一个临时表空间tbs_temp_01，并在表空间中创建一个临时数据库文件temp01.dbf，SQL语句的大小子句中指定初始大小是5M，可以自动扩展，并将表空间分配给表空间组tbs_grp_01。 ``` SQL> CREATE TEMPORARY TABLESPACE tbs_temp_01 TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON TABLESPACE GROUP tbs_grp_01; Tablespace created. ``` | 不支持。 | - | | 存储子句 语法： ``` STORAGE ({ INITIAL size_clause | NEXT size_clause | MINEXTENTS integer | MAXEXTENTS { integer | UNLIMITED } | maxsize_clause | PCTINCREASE integer | FREELISTS integer | FREELIST GROUPS integer | OPTIMAL [ size_clause | NULL ] | BUFFER_POOL { KEEP | RECYCLE | DEFAULT } | FLASH_CACHE { KEEP | NONE | DEFAULT } | ( CELL_FLASH_CACHE ( KEEP | NONE | DEFAULT ) ) | ENCRYPT } ... ) ``` | 部分支持，有差异。 | - Oracle中由STORAGE子句指定存储参数，而GaussDB中由WITH子句指定存储参数。 例如： Oracle中创建表my_tab1，在存储子句中指定表初始大小为10M，需要更多空间时每次增加5M的SQL语句如下： ``` SQL> CREATE TABLE my_tab1 (id NUMBER(10) PRIMARY KEY, name VARCHAR2(50)) STORAGE (INITIAL 10M NEXT 5M); Table created. ``` GaussDB中创建表my_tab2，在存储子句中指定存储引擎类型为USTORE的SQL语句如下： ``` gaussdb=# CREATE TABLE my_tab2 (id NUMBER(10) PRIMARY KEY, name VARCHAR2(50)) with (storage_type=ustore); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "my_tab2_pkey" for table "my_tab2" CREATE TABLE ``` - GaussDB中可选的存储参数和Oracle存在很大差异。GaussDB具体可参见[CREATE TABLE](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0271.html)的参数说明部分，"WITH ({storage_parameter = value} \[, ...\])"中描述了CREATE TABLE语句支持的存储参数。 | | 聚集函数嵌套 例如：创建由sales表的sales_amount列嵌套聚集函数MIN()、SUM()生成的表revenue。 ``` SQL> CREATE TABLE sales(ID INT, SALES_AMOUNT INT); Table created. SQL> INSERT INTO sales VALUES(1, 100); 1 row created. SQL> INSERT INTO sales VALUES (3, 200); 1 row created. SQL> CREATE TABLE revenue as SELECT SUM(MIN(sales_amount)) as total from sales group by sales_amount; Table created. ``` | 支持。 | - | | 删除系统schema 语法： ``` DROP USER schema_name CASCADE; ``` 例如：在SYS用户下删除SYS schema。 ``` SQL> DROP USER SYS; DROP USER SYS * ERROR at line 1: ORA-28050: specified user or role cannot be dropped ``` | 支持。 | - | | 删除表的主键约束 语法： ``` DROP PRIMARY KEY [ CASCADE ] [( KEEP | DROP ) INDEX ] [ ONLINE ] ``` | 部分支持，有差异。 | - GaussDB仅支持DROP PRIMARY KEY语法，不支持其他选项。 - 当进行该操作时，Oracle会同时将创建主键时自动为创建的主键列添加的NOT NULL约束进行删除；GaussDB不会删除，仍然保留NOT NULL约束。 ``` -- GaussDB: gaussdb=# CREATE TABLE t1 (id int PRIMARY KEY); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" CREATE TABLE gaussdb=# ALTER TABLE t1 DROP PRIMARY KEY; ALTER TABLE gaussdb=# INSERT INTO t1 VALUES(null); ERROR: The null value in column "id" violates the not-null constraint. DETAIL: Failing row contains (null). gaussdb=# DROP TABLE t1; -- Oracle: SQL> CREATE TABLE t1 (id int PRIMARY KEY); SQL> ALTER TABLE t1 DROP PRIMARY KEY; SQL> INSERT INTO t1 VALUES(null); -- 该操作可以插入成功，即主键列的NOT NULL约束也同时被删除。 SQL> DROP TABLE t1; ``` | | 创建序列 语法： ``` CREATE SEQUENCE [ IF NOT EXISTS ] [ schema. ] sequence [ SHARING = { METADATA | DATA | NONE } ] [ { INCREMENT BY | START WITH } integer | { MAXVALUE integer | NOMAXVALUE } | { MINVALUE integer | NOMINVALUE } | { CYCLE | NOCYCLE } | { CACHE integer | NOCACHE } | { ORDER | NOORDER } | { KEEP | NOKEEP } | { SCALE {EXTEND | NOEXTEND} | NOSCALE } | { SHARD {EXTEND | NOEXTEND} | NOSHARD } | { SESSION | GLOBAL } ]... ``` | 部分支持，有差异。 | - GaussDB不支持SHARING、CACHE、NOCACHE、ORDER、NOORDER、KEEP、NOKEEP、SCALE、NOSCALE、SHARD、NOSHARD、SESSION以及GLOBAL选项。 - 对于步长的绝对值是否小于最大值和最小值的差值的场景，GaussDB不进行检查。 ``` -- GaussDB: gaussdb=# CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 10 INCREMENT BY 10; CREATE SEQUENCE -- Oracle: SQL> CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 10 INCREMENT BY 10; CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 10 INCREMENT BY 10 * ERROR at line 1: ORA-04005: INCREMENT must be less than MAXVALUE minus MINVALUE ``` | | 修改序列 语法： ``` ALTER SEQUENCE [ schema. ] sequence { { (INCREMENT BY) | ( START WITH ) } integer | { MAXVALUE integer | NOMAXVALUE } | { MINVALUE integer | NOMINVALUE } | RESTART | { CYCLE | NOCYCLE } | { CACHE integer | NOCACHE } | { ORDER | NOORDER } | { KEEP | NOKEEP } | { SCALE {EXTEND | NOEXTEND} | NOSCALE } | { SHARD {EXTEND | NOEXTEND} | NOSHARD } | { SESSION | GLOBAL } } ... ``` | 部分支持，有差异。 | - GaussDB不支持INCREMENT BY、MINVALUE、NOMINVALUE、RESTART、CYCLE、NOCYLE、CACHE、NOCACHE、ORDER、NOORDER、KEEP、NOKEEP、SCALE、NOSCALE、SHARD、NOSHARD、SESSION以及GLOBAL选项。 - GaussDB修改最大值时，不检查步长的绝对值是否小于最大值和最小值的差值。 ``` -- GaussDB: gaussdb=# CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 100 INCREMENT BY 10; CREATE SEQUENCE gaussdb=# ALTER SEQUENCE seq_test MAXVALUE 10; ALTER SEQUENCE -- Oracle: SQL> CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 100 INCREMENT BY 10; Sequence created. SQL> ALTER SEQUENCE seq_test MAXVALUE 10; ALTER SEQUENCE seq_test MAXVALUE 10 * ERROR at line 1: ORA-04005: INCREMENT must be less than MAXVALUE minus MINVALUE ``` - GaussDB修改最大值时，检查序列的起始值是否在最大值和最小值范围内。 ``` -- GaussDB: gaussdb=# CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 10 START WITH 10 INCREMENT BY -1; CREATE SEQUENCE gaussdb=# SELECT seq_test.nextval FROM DUAL; nextval --------- 10 (1 row) gaussdb=# SELECT seq_test.nextval FROM DUAL; nextval --------- 9 (1 row) gaussdb=# SELECT seq_test.nextval FROM DUAL; nextval --------- 8 (1 row) gaussdb=# ALTER SEQUENCE seq_test MAXVALUE 9; ERROR: START value (10) cannot be greater than MAXVALUE (9). -- Oracle: SQL> CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 10 START WITH 10 INCREMENT BY -1; Sequence created. SQL> SELECT seq_test.nextval FROM DUAL; NEXTVAL ---------- 10 SQL> SELECT seq_test.nextval FROM DUAL; NEXTVAL ---------- 9 SQL> SELECT seq_test.nextval FROM DUAL; NEXTVAL ---------- 8 SQL> ALTER SEQUENCE seq_test MAXVALUE 9; Sequence altered. ``` - GaussDB首次执行nextval前内部位置是起始值，修改最大值时，检查序列的起始值是否在最大值和最小值范围内。 ``` -- GaussDB: gaussdb=# CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 10 START WITH 6 INCREMENT BY -5; CREATE SEQUENCE gaussdb=# ALTER SEQUENCE seq_test MAXVALUE 9; ALTER SEQUENCE -- Oracle: SQL> CREATE SEQUENCE seq_test MINVALUE 1 MAXVALUE 10 START WITH 6 INCREMENT BY -5; Sequence created. SQL> ALTER SEQUENCE seq_test MAXVALUE 9; ALTER SEQUENCE seq_test MAXVALUE 9 * ERROR at line 1: ORA-04009: MAXVALUE cannot be made to be less than the current value ``` |