SQL命令

概述

本节的内容是在 AntDB 中与 Oracle 相兼容的 SQL 命令综述。本节中介绍的 SQL 命令可以同时在 Oracle 数据库和 AntDB 数据库中使用。

请注意下面这些要点：

• 由于 AntDB 中其它命令与 Oracle 不兼容，或者由于它们可以提供 Oracle 的 SQL 命令能实现类似或者相同的功能，但是在语法上不同，所以没有在本节列出。

• 在本节中介绍的 SQL 命令没有列出完整的语法，选项和命令中有效的功能。在命令描述和语法中省略了与 Oracle 不兼容的语法，选项和功能。

ALTER INDEX

名称

ALTER INDEX —— 改变索引的定义。

概要

ALTER INDEX name RENAME TO new_name

描述

ALTER INDEX 命令改变一个已存在索引的定义。RENAME 命令用于改变索引的名称。该命令不影响存储的数据。

参数

name

要修改的索引名称（可以采用模式限定的方式引用）。

new_name

新的索引名称。

示例

重新命名一个索引的名称

ALTER INDEX name_idx RENAME TO empname_idx; 

ALTER TABLE

名称

ALTER TABLE —— 改变数据表的定义。

概要

ALTER TABLE name action [, ...]

ALTER TABLE name RENAME COLUMN column TO new_column

ALTER TABLE name RENAME TO new_name

其中 action 是下面这些操作之一：

MODIFY column type [column type] 

MODIFY (column type [,column type][, ... ]) 

ADD column type [ column_constraint [ ... ] ]

DROP COLUMN column

ADD table_constraint

DROP CONSTRAINT constraint_name [ CASCADE ]

描述

ALTER TABLE 改变一个已存在表的定义。下面是在这条命令后面能跟随的子命令：

MODIFY column type

这条子命令的作用是重新定义某一列的数据类型，可以一次修改一列或者多列。

ADD column type

这条子命令的语法和在 CREATE TABLE 命令中的相同，它的作用是为一张表添加了一个新列。

DROP COLUMN

这条子命令的作用是从一张表中删除了一个列。同时也会删除涉及到这一列的索引和表约束。

ADD table_constraint

这条子命令的语法和在 CREATE TABLE 命令中使用的相同，它的作用是为一张表添加了一个新的约束。

DROP CONSTRAINT

这条子命令从一张数据表上删除了约束。通常不要求在表上的约束有一个唯一的名称，所以这里可能有多个约束符合指定名称。所有符合指定名称的约束都会被删除。

RENAME

RENAME 命令改变一张表（或索引，序列或者视图）或者在一张表上单独列的名称，而对存储的数据没有任何作用。

只有表的所有者，才能使用 ALTER TABLE 命令。

参数

name

一张要修改的表的名称（可以采用模式限定的方式引用）。

column

一个新的或者已存在列的名称。

new_column

一个已存在列的新名称。

new_name

表的新名称。

type

新增加列的数据类型。

table_constraint

表上新的表约束。

constraint_name

要删除的约束得名称。

CASCADE

自动删除依赖于被删除约束的对象。

注意

当使用 ADD COLUMN 命令增加一列的时候，在所有记录中这一个列的值都会被初始化为该列的缺省值（如果没有指定 DEFAULT 子句，那么缺省值就是空值）。

增加一个带有非空缺省值列的时候将会要求对整个表的重写。对于大表来说，将会花费很长的时间；并且临时会要求双倍的磁盘空间。

增加 CHECK 或者 NOT NULL 约束可以要求在扫描表的时候检测已存在记录是否符合约束。

DROP COLUMN 命令实际上不删除列，只是将该列对于 SQL 操作设置成不可见。后面的插入和更新操作对于这个列只存储一个空值。因此，删除列的操作很快，但是不会立即减少数据表在磁盘上的存储空间。因为没有回收所删除列的空间。只有当对已存在记录执行更新操作的时候，才会回收相关存储空间。

不允许修改系统视图的任何部分。

更多关于有效参数的描述，请参照 CREATE TABLE 命令。

示例

为数据表增加一个类型为 VARCHAR2 的列：

ALTER TABLE emp ADD COLUMN address VARCHAR2(30); 

从表中删除一列：

ALTER TABLE emp DROP COLUMN address; 

重新命名已存在的列：

ALTER TABLE emp RENAME COLUMN address TO city; 

重新命名已存在的表：

ALTER TABLE emp RENAME TO employee; 

为一张表增加一个检查约束：

ALTER TABLE emp ADD CONSTRAINT sal_chk CHECK (sal \> 500); 

从表中删除一个检查约束：

ALTER TABLE emp DROP CONSTRAINT sal_chk; 

COMMENT

名称

COMMENT —— 定义或者改变对象的注释。

概要

COMMENT ON

{

TABLE table_name |

COLUMN table_name.column_name

} IS 'text'

描述

COMMENT 命令用于保存一个数据库对象的注释。如果修改对象的注释，需要对同一对象下运行新的 COMMENT 命令。每一个对象只能存储一个注释字符串。如果想删除注释，为’text’指定一个空字符串（两个连续的单引号，中间没有空格）就可以了。当删除对象后，相关的注释也会被自动删除。

参数

table_name

被注释表的名称，表名可以采用模式限定的方式引用。

table_name.column_name

由参数 table_name 指定的被注释表的列名。表名可以采用模式限定的方式引用。

text

新的注释。

注意

对于注释来说，没有相应的安全机制。任何登录到数据库上的用户可以看到数据库中所有对象的注释（尽管只有超级用户能够修改它所不拥有的对象的注释）。因此不要在注释中写上对于安全非常重要的信息。

示例

为表 emp 增加一个注释：

COMMENT ON TABLE emp IS 'Current employee information'; 

为表emp的列empno增加注释：

COMMENT ON COLUMN emp.empno IS 'Employee identification number';

删除上面添加的注释：

COMMENT ON TABLE emp IS ''; COMMENT ON COLUMN emp.empno IS '';

COMMIT

名称

COMMIT —— 提交当前的事务。

概要

COMMIT [ WORK ]

描述

COMMIT 命令用于提交当前事务。当这个命令完成后，由事务产生的所有更新操作对于其他事务均可见。如果系统产生崩溃，所提交的更新操作依然永久有效。

参数

WORK

可选的关键字-没有任何作用。

注意

使用 ROLLBACK 命令可以终止一个事务。不在事务的里面执行 COMMIT 命令不会产生任何副作用。

示例

提交当前的事务并且使所有的更改操作永久有效：

COMMIT;

CREATE FUNCTION

名称

CREATE FUNCTION —— 定义一个新的函数，执行此操作之前，要先设置将 PLSQL_MODE 打开，方法如下：

\set PLSQL_MODE ON

概要

CREATE [ OR REPLACE ] FUNCTION name

[ (argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]

[, ...]) ]

RETURN rettype

{ IS | AS }

[ declaration; ] [, ...]

BEGIN

statement; [...]

[ EXCEPTION

{ WHEN exception [ OR excepti``on ] [...] THEN

statement; [, ...] } [, ...]

]

END [ name ]

描述

CREATE FUNCTION 命令用于定义了一个新的函数。而 CREATE OR REPLACE FUNCTION 命令既可以创建一个新的函数，也可以用新创建的函数定义替代已存在的函数定义。

如果在命令中包含了模式的名称，那么函数是在指定模式下创建。否则就在当前模式下创建。新创建函数的名称不能和同一模式下任何带有相同参数类型函数的名称相同。然而，带有不同参数类型的函数可以使用相同的函数名称（这种方式称为重载）。（函数的重载是一个 AntDB 的特性，与 Oracle 不兼容）

通过使用 CREATE OR REPLACE FUNCTION 命令，可以更新已存在函数的定义。但是不能用这种方法改变函数的名称或者参数类型（如果进行这样的尝试话，实际上是另外创建一个新的函数）。而且 CREATE OR REPLACE FUNCTION 命令不会改变已存在函数的返回值，除非删除函数后重新创建。

创建函数的用户是新创建函数的所有者。

关于函数的更多信息，参见章节 2.5。

参数

name

要创建函数的名称（可以采用模式限定的方式引用）。

argname

一个参数的名称。参数是通过在函数体内使用这个名称来引用的。

IN | IN OUT | OUT

参数模式。IN 将参数声明为输入参数。这是缺省的情况。 IN OUT 允许参数既可以接收值，也可以返回值。 而 OUT 则指定参数只用于输出。

argtype

函数参数的数据类型。参数类型可以是基本数据类型，带有 %TYPE 的列的类型拷贝，或者是一个用户定义类型，比如嵌套表或者是一个对象类型。对于任何基础数据类型来说，不应该指定长度。例如，应该使用 VARCHAR2 这种形式，而不是 VARCHAR2(10)。

通过使用 tablename.columnname%TYPE 这种形式，可以引用一个列类型。这样当表定义发生改变的时候，可以不用对函数的具体实现进行修改。

DEFAULT value

当调用函数时没有为参数提供值，使用这个参数为输入参数提供一个缺省值。而对于模式为 IN OUT 或者 OUT 的参数来说，可以不指定参数 DEFAULT 的值。

rettype

返回值的数据类型，可以是已列出参数类型中的任何一种。和参数 argtype 一样，对参数 rettype 不应该指定长度。

declaration

变量，类型或者是 REF CURSOR 的声明。

statement

SPL 程序语句。需要注意的是对于一个 DECLARE - BEGIN - END 的代码块来说，它本身就被认为一条 SPL 语句。因此，函数体内部可以包含嵌套代码块。

exception

异常条件的名称，例如 NO_DATA_FOUND，OTHERS 等等。

注意

AntDB 允许进行函数重载；这就是说不同函数只要参数类型不同，就可以使用相同的名称。

如果您是使用 AntDB 命令创建函数、存储过程和触发器，或者执行匿名块语句，您需要将 SQL 语句的最后一个分号“;”替换为反斜杠“/”。

示例

函数 emp_comp 函数把2个数值作为输入参数，然后返回一个计算后的值。使用 SELECT 命令来演示这个函数的使用方法（ emp 表格定义见【示例参考表格】）。

\set PLSQL_MODE ON
CREATE OR REPLACE FUNCTION emp_comp (
p_sal	NUMBER,
p_comm	NUMBER
) RETURN NUMBER
IS
BEGIN
RETURN (p_sal + NVL(p_comm, 0)) * 24;
END;
/
\set PLSQL_MODE OFF
SELECT ename "Name", sal "Salary", comm "Commission", emp_comp(sal, comm)
"Total Compensation"	FROM emp;

  Name  | Salary | Commission | Total Compensation 
--------------+--------+-------------------+---------------------------------
 SMITH  |    800 |            |              19200
 ALLEN  |   1600 |        300 |              45600
 WARD   |   1250 |        500 |              42000
 JONES  |   2975 |            |              71400
 MARTIN |   1250 |       1400 |              63600
 BLAKE  |   2850 |            |              68400
 CLARK  |   2450 |            |              58800
 SCOTT  |   3000 |            |              72000
 KING   |   5000 |            |             120000
 TURNER |   1500 |          0 |              36000
 ADAMS  |   1100 |            |              26400
 JAMES  |    950 |            |              22800
 FORD   |   3000 |            |              72000
 MILLER |   1300 |            |              31200
(14 rows)

函数 sal_range 返回在指定薪水范围内雇员的总数。下面的匿名代码块调用几次函数，其中在前两次调用中使用了参数的缺省值（ emp 表格定义见【示例参考表格】）。

\set PLSQL_MODE ON
CREATE OR REPLACE FUNCTION sal_range (
p_sal_min	NUMBER DEFAULT 0,
p_sal_max	NUMBER DEFAULT 10000
) RETURN INTEGER
IS
v_count	INTEGER;
BEGIN
SELECT COUNT(*) INTO v_count FROM emp
WHERE sal BETWEEN p_sal_min AND p_sal_max;
RETURN v_count;
END;
/
\set PLSQL_MODE OFF

SELECT sal_range();
SAL_RANGE 
--------------------
        14
(1 row)

CREATE INDEX

名称

CREATE INDEX —— 定义一个新的索引。

概要

CREATE [ UNIQUE ] INDEX name ON table

( { column | ( expression ) } )

[ TABLESPACE tablespace ]

描述

CREATE INDEX 命令用于在指定的表上创建一个索引。索引主要是用来提高数据库的性能（不正确的索引使用方法会降低性能）。

索引的关键字字段可以是列名，也可以是写在括号中的表达式。可以指定多个字段来创建多列索引。

索引字段可以是由表记录中一列或者多列表字段值计算出来的一个表达式。可以使用这种特性来快速访问基于基础数据转换后的数据。例如，由 UPPER(col) 计算得出的索引值允许子句 WHERE UPPER(col) = 'JIM' 使用索引。

AntDB 提供 B- 树结构的索引。

在缺省情况下，不能为 IS NULL 子句使用索引。

在索引定义中用到的所有函数和操作符必须是不可改变的，也就是说，它们的结果只取决于它们的参数而与外面的影响无关（例如另外一张表的内容或者当前系统时间的改变）。这个限制确保索引的行为是明确界定的，如果在索引中使用用户定义的函数，必须记住在创建它的时候把函数标识为不可改变。

参数

UNIQUE

当创建索引（如果数据已经存在）和每一次添加数据的时候，这个参数会让系统去检查表中的重复值。如果进行的插入或更新操作产生了重复值，将产生错误。

name

要创建索引的名称。它不包含模式名称。索引只能它的源表所在模式中创建。

table

要创建索引的表的名称（可以采用模式限定的方式引用）。

column

表中列的名称。

expression

基于表中一列或多列的表达式。如语法所示，表达式通常必须用括号括起来。然而，如果表达式是函数调用的形式，可以不用写括号。

tablespace

用于创建索引的表空间。如果没有指定这个参数，那么使用参数 default_tablespace 所指定的表空间。如果 default_tablespace 参数值是空字符串，那么使用数据库的缺省表空间。

注意

最多可为多列索引指定 32 个字段。

示例

在表 emp 中名称为 ename 的列上创建一个 B- 树索引：

CREATE INDEX name_idx ON emp (ename); 

在表空间 index_tblspc 上创建一个和上面一样的索引：

CREATE INDEX name_idx ON emp (ename) TABLESPACE index_tblspc; 

CREATE PROCEDURE

名称

CREATE PROCEDURE —— 定义一个新的存储过程。执行此操作之前，要先设置将 PLSQL_MODE 打开，创建好存储过程后，再把此参数设置为 OFF，否则，事务不会自动提交，命令“/”可以提交事务。方法如下：

\set PLSQL_MODE ON

\set PLSQL_MODE OFF

概要

CREATE [ OR REPLACE ] PROCEDURE name

([argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ]

[, ...] ] )

{ IS | AS }

[ declaration; ] [, ...]

BEGIN

statement; [...]

[ EXCEPTION

{ WHEN exception [ OR exception ] [...] THEN

statement; [, ...] } [, ...]

]

END [ name ]

描述

CREATE PROCEDURE 命令定义一个新的存储过程。CREATE OR REPLACE PROCEDURE 命令既可以创建一个新的存储过程，也可以用新创建的存储过程定义替代已存在的存储过程定义。如果包含了模式的名称，那么存储过程是在指定模式下创建，否则就是在当前模式下创建。新的存储过程名不应与同一模式下任何存储过程的名称相同。除非打算使用 CREATE OR REPLACE PROCEDURE 命令更新一个已存在存储过程的定义。

创建存储过程的用户是新创建存储过程的所有者。

关于存储过程的更多内容参见章节 3。

参数

name

所创建存储过程的名称（可以采用模式限定的方式引用）。

argname

参数的名称。参数是通过在存储过程体内部的名称来引用的。

IN | IN OUT | OUT

参数模式。IN 将参数声明为输入参数。这是缺省的情况。IN OUT 允许参数既可以接收值，也可以返回值。 OUT 指定参数只是用作输出。

argtype

存储过程参数的数据类型。参数类型可以是基本数据类型，使用 %TYPE 作为数据类型的已存在列的拷贝，或者一个用户定义类型，比如嵌套表或者一个对象类型。对于任何基础数据类型来说，不应该指定长度-例如，指定 VARCHAR2，而不是 VARCHAR2(10)。

通过使用 tablename.columnname%TYPE，可以引用一个列的类型。当表的定义发生改变的时候，使用这种方式可以不用修改存储过程的主体。

DEFAULT value

当调用存储过程的时候没有为参数提供值时，它可以为输入参数提供一个缺省值。对于模式为 IN OUT 或者 OUT 的参数来说，不指定参数 DEFAULT。

DEFINER | CURRENT_USER

用来指定是否能使用存储过程所有者（DEFINER）或者当前执行存储过程的用户（CURRENT_USER）的权限来确定是否允许访问在存储过程中引用的数据库对象。在存储过程所有者（DEFINER）这种情况下，使用存储过程所有者的搜索路径来解析对非限定的数据库对象的引用。同时，在当前执行存储过程的用户（CURRENT_USER）这种情况下，使用当前正在执行存储过程用户的搜索路径来解析对非限定数据库对象的引用。DEFINER 是缺省选项。

declaration

变量，类型或者是 REF CURSOR 的声明。

statement

SPL 程序语句。需要注意的是一个 DECLARE-BEGIN-END 的代码块本身就被认为是一条 SPL 语句。因此，函数体内部可以包含嵌套代码块。

exception

异常条件的名称，例如 NO_DATA_FOUND，OTHERS 等等。

示例

下面这个存储过程列出了表 emp 中的雇员（ emp 表格定义见【示例参考表格】）：

\set PLSQL_MODE ON
CREATE OR REPLACE PROCEDURE list_emp()
IS
v_empno	NUMBER(4);
v_ename	VARCHAR2(10);
CURSOR emp_cur IS
SELECT empno, ename FROM emp ORDER BY empno;
BEGIN
OPEN emp_cur;
DBMS_OUTPUT.PUT_LINE('EMPNO	ENAME');
DBMS_OUTPUT.PUT_LINE('-----	-------');
LOOP
FETCH emp_cur INTO v_empno, v_ename;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || '	' || v_ename);
END LOOP;
CLOSE emp_cur;
END;
/
\set PLSQL_MODE OFF
SELECT list_emp();

NOTICE:  7369   SMITH
NOTICE:  7499   ALLEN
NOTICE:  7521   WARD
NOTICE:  7566   JONES
NOTICE:  7654   MARTIN
NOTICE:  7698   BLAKE
NOTICE:  7782   CLARK
NOTICE:  7788   SCOTT
NOTICE:  7839   KING
NOTICE:  7844   TURNER
NOTICE:  7876   ADAMS
NOTICE:  7900   JAMES
NOTICE:  7902   FORD
NOTICE:  7934   MILLER
 LIST_EMP 
----------
 
(1 row)

下面这个存储过程首先根据雇员编号查询雇员编号，雇员姓名和工作种类，如果没有找到结果，那么使用雇员名称查询，最后使用 IN OUT 参数和 OUT 参数将结果返回。这个存储过程是在一个匿名代码块中调用的。

注意，下面的例子中，emp_job 的最后一个参数是输出，在 Oracle 中，可以通过 emp_job(v_empno,v_ename, v_job) 将结果直接赋给 v_job 变量。但是在 AntDB 中，调用存储过程的时候，结果变量不能放在参数中，只能通过这样的方式调用：re= emp_job(v_empno, v_ename);再从 re 中将结果提取出来（ emp 表格定义见【示例参考表格】）。

\set PLSQL_MODE ON
CREATE OR REPLACE PROCEDURE emp_job (
p_empno	IN OUT numeric,
p_ename	IN OUT varchar2,
p_job	OUT	varchar2
)
IS
v_empno	emp.empno%TYPE; --函数参数不支持此种格式的定义
v_ename	emp.ename%TYPE;
v_job	emp.job%TYPE;
BEGIN
SELECT ename, job INTO v_ename, v_job FROM emp WHERE empno = p_empno;
p_ename := v_ename;
p_job	:= v_job;
DBMS_OUTPUT.PUT_LINE('Found employee # ' || p_empno);
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
SELECT empno, job INTO v_empno, v_job FROM emp
WHERE ename = p_ename;
p_empno := v_empno;
p_job	:= v_job;
DBMS_OUTPUT.PUT_LINE('Found employee ' || p_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Could not find an employee with ' ||
'number, ' || p_empno || ' nor name, '	|| p_ename);
p_empno := NULL;
p_ename := NULL;
p_job	:= NULL;
END;
END;
/

DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_job   emp.job%TYPE;
re record;
BEGIN
v_empno := 7782;
v_ename := 'CLARK';
re := emp_job(v_empno, v_ename);
DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
DBMS_OUTPUT.PUT_LINE('Name  : ' || v_ename);
DBMS_OUTPUT.PUT_LINE('Job   : ' || re.p_job);
END;
/
\set PLSQL_MODE OFF

Found employee CLARK
Employee No: 7782
Name	: CLARK
Job	: MANAGER

CREATE PUBLIC SYNONYM

名称

CREATE PUBLIC SYNONYM —— 定义一个新的公有同义词。

CREATE SYNONYM —— 定义一个新的私有同义词。

概要

CREATE [ OR REPLACE ] [PUBLIC] SYNONYM name FOR object

描述

使用 CREATE PUBLIC SYNONYM 命令能够为某一特定类型的数据库对象创建一个公有同义词。同义词是指向数据库对象的另外一个名称。公有同义词是一个在数据库中有效，可由数据库集群的任何用户引用的同义词。而不加 public 字段，CREATE SYNONYM 命令则是创建一个私有同义词，私有同义词只能被当前用户引用。

CREATE OR REPLACE PUBLIC SYNONYM 命令的功能类似，只是如果相同名称的公有同义词已存在，新创建的同义词替代已存在的那个。

为了在 SQL 语句中合理的引用数据库对象，要求使用模式名称来对数据库对象完全限定，这种情况下同义词非常有用。

参数

name

所创建同义词的名称。

object

同义词所指向数据库对象的名称（可以采用模式限定的方式引用）。这个数据库对象可以是视图、函数、存储过程或者是其他同义词。

注意

在任何 SQL 语句中，每个用户都能够引用公有同义词，然而，只有当用户在同义词所指定的对象上具有相应的权限，语句才能够执行成功。

在 AntDB 中，公有同义词也是属于某个模式的成员。

可以为不存在的对象创建公有同义词。

只有使用公有同义词的当前用户所具备的权限才能决定是否允许访问通过公有同义词来引用的数据库对象。因此公有同义词用户在特定的数据库对象上必须有相应的权限。

示例

为模式 antdb 中的表 emp 创建一个公有同义词：

CREATE PUBLIC SYNONYM personnel FOR antdb.emp;

为模式 antdb 中的表 emp 创建一个私有有同义词：

CREATE SYNONYM personnel2 FOR antdb.emp;

CREATE ROLE

名称

CREATE ROLE —— 定义一个新的数据库角色。

概要

CREATE ROLE name

描述

CREATE ROLE 命令用于在 AntDB 集群中添加一个新的角色。角色是一个实体，能够拥有数据库对象和数据库权限。可以把一个角色认为是一个用户，一个组或者根据用途来决定角色的性质。新创建的角色没有 LOGIN 权限，所以它不能启动一个会话。可以使用 ALTER ROLE 命令来为角色授予 LOGIN 权限。拥有 CREATEROLE 权限的用户或者超级用户才能使用 CREATE ROLE 命令。

需要注意的是，角色是在数据库集群层面内定义的，所以新创建的角色对所有集群内数据库有效。

参数

name

新创建角色的名称。

示例

创建一个角色，名称是 admins。

CREATE ROLE admins;

CREATE SEQUENCE

名称

CREATE SEQUENCE —— 定义一个新的序列生成器。

概要

CREATE SEQUENCE name [ INCREMENT BY increment ]

[ { NOMINVALUE | MINVALUE minvalue } ]

[ { NOMAXVALUE | MAXVALUE maxvalue } ]

[ START WITH start ] [ CACHE cache | NOCACHE ] [ CYCLE | NOCYCLE ]

描述

CREATE SEQUENCE 命令用于创建一个新的序列数字生成器。这个操作涉及到创建和初始化一个名称为参数 name 所指定，带有单条记录的表。运行这条命令的用户是新创建序列生成器的所有者。

如果指定了模式名称，那么序列在指定模式中创建，否则就在当前模式中创建。在同一模式中，序列的名称不能与其他的序列、表、索引或者视图相同。

序列创建完成后，使用函数 NEXTVAL 和 CURRVAL 对序列进行操作。这些函数的用法在章节 2.5.6 部分有详细说明。

参数

name

创建的序列的名称（可以采用模式限定的方式引用）。

increment

可以使用可选的 INCREMENT BY increment 子句把当前序列的值加上指定增量值，这样可以产生一个新的数值。一个正数增量将会产生一个升序序列，一个负数增量产生一个降序序列值。这个参数的缺省值是 1。

NOMINVALUE | MINVALUE minvalue

可选子句 MINVALUE minvalue 决定一个序列可以产生的最小值。如果没有使用这个子句，将使用缺省值。升序的缺省值是 1，降序的缺省值是 -2 ^ 63。需要注意的是可以使用关键字 NOMINVALUE 把序列可产生的最小值设定回缺省值。

NOMAXVALUE | MAXVALUE maxvalue

可选子句 MAXVALUE maxvalue 决定一个序列可以产生的最大值。如果没有使用这个子句，将使用缺省值。升序的缺省值是 2 ^ 63 -1，降序的缺省值是 -1。需要注意的是可以使用关键字 NOMAXVALUE 把序列可产生的最大值设定回缺省值。

start

可选子句 START WITH start 允许序列从任意一个位置开始。对于升序序列来说，缺省值是 minvalue，对于降序序列来说缺省值是 maxvalue。指定的start值必须大于等于 minvalue。

cache

可选子句 CACHE cache 指定了为快速访问，有多少序列数预先分配，并存储在内存中。这个参数的最小值是 1（每次只产生一个值，例如, NOCHACHE）, 并且这也是缺省值。

CYCLE | NOCYCLE

当升序序列和降序序列达到各自的最大值和最小值，CYCLE 选项允许序列返回到初始位置。如果达到了限制值，那么下一次产生的数值就是各自的最小值和最大值。

如果使用 NOCYCLE 或者不写（默认是 NOCYCLE），当序列达到最大值的时候，任何对 NEXTVAL 的调用将返回一个错误。

注意

序列基于大整数算法，所以数值不能超过 8 字节长度的整数（从 -9223372036854775808 到 9223372036854775807）。在一些版本比较老的平台，可能没有编译器支持 8 字节长度的整数，在这种情况下，序列只能使用规则整数算法（范围是从 -2147483648 to +2147483647）。

当多个会话并发访问一个缓存设置大于 1 的序列对象的时候，可能会取得不希望的结果。这是因为每一个会话在访问序列对象的过程中都要分配和缓存连续的序列值，因此都要增加序列对象的最新值。然后，会话中接下来 cache-1 次 NEXTVAL 函数的调用仅仅返回预先分配的值，而没有实际与序列对象联系。这样，当会话结束的时候，任何已分配但是没有在会话中使用的值将会丢失。这样就会在序列中产生一个漏洞。

此外，尽管可以保证对多个会话分配各自不同的序列值，但是当考虑到所有会话的时候，产生的相关数值还是会超出序列范围。例如，当缓存设置为 10 的时候，会话 A 可以保留数值 1..10 并且返回 NEXTVAL=1，然后会话 B 可能保留数值11..20，然后在会话 A 产生 NEXTVAL=2 的前面返回 NEXTVAL=11。因此把缓存设置设定为 1 是安全的，能够保证 NEXTVAL 值顺序产生：缓存设置大于 1 的时候，只能保证 NEXTVAL 值都是不同的，不能保证他们是顺序产生的。而且最后的值只是反映了任意一个会话所保留的值，无论它是不是已经通过 NEXTVAL 返回。

示例

创建一个名称为 serial 的升序序列，从 101 开始：

CREATE SEQUENCE serial START WITH 101; 

从这个序列中选择下一个数值：

SELECT serial.NEXTVAL FROM DUAL;

SERIAL.NEXTVAL  
-----------------
             101
(1 row)

创建一个名称为 supplier_seq 的序列，使用 NOCACHE 选项：

CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE;

从这个序列中选择下一个值：

SELECT supplier_seq.NEXTVAL FROM DUAL;

SUPPLIER_SEQ.NEXTVAL  
---------------------------------------
                     1
(1 row)

CREATE TABLE

名称

CREATE TABLE —— 定义一张新表。

概要

CREATE [ GLOBAL TEMPORARY ] TABLE table_name (

{ column_name data_type [ DEFAULT default_expr ]

[ column_constraint [ ... ] ] | table_constraint } [, ...]

)

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ]

[ TABLESPACE tablespace ]

[ table_partitioning_clauses ]

[WITH NOLOGGING]

column_constraint 是下列选项之一：

[ CONSTRAINT constraint_name ]

{ NOT NULL |

NULL |

UNIQUE [ USING INDEX TABLESPACE tablespace ] |

PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |

CHECK (expression) |

REFERENCES reftable [ ( refcolumn ) ]

[ ON DELETE action ] }

[ DEFERRABLE | NOT DEFERRABLE ]

table_constraint 是下列选项之一：

[ CONSTRAINT constraint_name ]

{ UNIQUE ( column_name [, ...] )

[ USING INDEX TABLESPACE tablespace ] |

PRIMARY KEY ( column_name [, ...] )

[ USING INDEX TABLESPACE tablespace ] |

CHECK ( expression ) |

FOREIGN KEY ( column_name [, ...] )

REFERENCES reftable [ ( refcolumn [, ...] ) ]

[ ON DELETE action ] }

[ DEFERRABLE | NOT DEFERRABLE ]

其中的 table_partitioning_clauses 是：

PARTITION BY RANGE ( column_name [, ... ] )

(

PARTITION partition VALUES LESS THAN {( expr [, ... ] )| MAXVALUE }

[ TABLESPACE tablespace ]

[, ... ]

)

或者

PARTITION BY LIST ( column_name [, ... ] )

(

PARTITION partition VALUES(expre [, ... ])

[ TABLESPACE tablespace ] [, ... ])

)

或者

PARTITION BY HASH ( column_name [, ... ] )

(

PARTITION partition VALUES(expre [, ... ])

[ TABLESPACE tablespace ] [, ... ])

)

描述

CREATE TABLE 命令在当前数据库中创建一张新表，新创建的表初始状态下没有内容。执行 CREATE TABLE 命令的用户是新创建表的所有者。

如果指定了模式名称（例如，CREATE TABLE myschema.mytable ...），那么就在指定的模式中创建表，否则就是在当前模式下创建表。而临时表只在一个特定的模式下存在，所以在创建临时表的时候不需要指定模式名称。表名不能和同一模式下其他表，序列，索引或者视图的名称一样。

CREATE TABLE 命令同时也会自动创建一个数据类型用来表示与表中一条记录相对应的复合类型。因此，在同一模式中，表不能和已存在数据类型有相同的名称。

一张表可以有最多 1600 个数据列（而在实际情况下，由于字段长度的限制，真正有效的限制值更低一些）。

可选的约束子句指定约束（或者是测试）条件。只有满足这些约束（或者测试）条件，才能执行插入或更新记录的操作。约束实际是一个 SQL 对象，以各种方式帮助定义在表中有效值的集合。

这里有两种方式定义约束：表级约束和列级约束。列级约束做为列定义的一部分而进行定义的。表级约束不和特定的列相联系，它包括多个列。可以把每个列级约束写成一个表级约束：如果约束只影响一列，那么一个列级约束只是一种简单的表示方法。

参数

GLOBAL TEMPORARY

如果指定了这个参数，那么会创建一张临时表。在会话结束时会自动删除临时表，也可以在当前事务结束时进行这个操作（详细信息参照下面提到的参数 ON COMMIT）。如果相同名称的永久表存在，那么当临时表存在的时候，永久有效的表对于当前会话来说是不可见的，除非使用模式限定的名称来引用这些表。除此之外，临时表在创建它的会话范围之外也是不可见的（全局临时表在这个方面与 Oracle 不兼容）。任何在临时表上创建的索引也是临时存在的。

table_name

所创建表的名称（可以采用模式限定的方式引用）。

column_name

在新表中要创建列的名称。

data_type

列的数据类型。数据类型可能包括数组定义。

DEFAULT default_expr

DEFAULT 子句为列分配一个缺省值。这个值可以是任意表达式（但不允许使用子查询和当前表中对其他列的引用），缺省表达式的数据类型必须匹配列的数据类型。当在插入操作中没有为列指定值的时候，可以使用缺省表达式。如果没有为列指定缺省值。那么缺省值为空。

CONSTRAINT constraint_name

为列或者表约束所指定的名称（这个名称可选）。如果没有指定的话，那么系统会为这个约束自动产生一个名称。

NOT NULL

不允许列中包含空值。

NULL

列中允许包含空值。这是缺省状态。这个子句只是为了与非标准的 SQL 数据库相兼容而提供的。在新的应用中不提倡这个参数。

UNIQUE – 列级约束

UNIQUE (column_name [, ...] ) – 表级约束

UNIQUE 约束指定表中一组列只能包含唯一值。唯一性表级约束的操作与列级约束相同，只是它具有在多个列上进行约束的能力。

对于一个唯一性约束来说，它不认为空值是相等的。

每一个唯一性表级约束所命名的列集合和在表上定义的其它唯一性或者主键约束命名的列集合不能一样。（否则就会列出两个一样的约束）

PRIMARY KEY – 列级约束

PRIMARY KEY ( column_name [, ...] ) – 表级约束

主键约束指定表上的一列或者多列数据只包含唯一（不可重复），非空的值。从技术上来说，主键仅仅是唯一性约束和非空约束的组合。但是将一个列的集合标识为主键提供了关于模式设计的元数据，因为主键会使其它表将这个列集合做为记录的唯一性标识。

无论是做为列级约束，还是表级约束，每个表只能指定一个主键。

主键约束命名的列集合应该与同一表上为其他唯一性约束命名的列集合不同。

CHECK (expression)

CHECK 子句指定了一个产生布尔结果值的表达式。只有满足这个布尔表达式，插入或者更新记录的操作才能成功。如果表达式的计算结果为“true”或者是“unkonw”，那么表示满足条件。任何无法满足表达式的插入或者更新操作记录都将产生一个错误异常，并且插入和更新操作不能改变数据库的记录值。指定为列级约束的检查约束只能访问相关列的值，同时出现在表级约束的表达式可以引用多列值。

当前，CHECK 表达式不能包含子查询或者引用非当前记录列的变量。

REFERENCES reftable [ ( refcolumn ) ] [ ON DELETE action ] - 列级约束

FOREIGN KEY ( column [, ...] ) REFERENCES reftable [ ( refcolumn [, ...] ) ] [ ON DELETE action ] - 表级约束

这些子句指定了一个外键约束，要求新创建表的相关列只能包含符合所引用表中的列的值。如果没有使用 refcolumn，那么使用 reftable 的主键。被引用的列必须是被引用表中具有唯一性或者主键约束的列。

除此之外，当所引用列的数据发生改变时，在这个表上相关列上也要执行相关的操作。当删除引用表的记录的时候，ON DELETE 子句会指定要执行的操作。尽管约束可以是延迟的，但是引用操作不能延迟。针对每个子句，下面是一些可以允许的操作。

CASCADE

分别删除任何引用被删除记录的记录，或者将引用列上的值更新为被引用列的值。

SET NULL

将引用列的值设定为空。如果被引用的列的值经常改变，在外键列上最好加上一个索引。这样与外键列相关的引用操作能够执行的更有效率。

DEFERRABLE

NOT DEFERRABLE

用于控制约束是否可以延迟。每一条命令执行后，非延迟的约束就会立即进行检查，可延迟的约束检查只有在事务结束后才进行。NO DEFERRABLE 是缺省选项。当前只有外键约束使用这个子句，其他所有的约束类型是不可延迟的。

ON COMMIT

在事务块结束时处理临时表的操作可以由 ON COMMIT 来控制。这里有两个选项：

PRESERVE ROWS

在事务结束时不进行特定的操作。这是缺省系统行为（需要注意的它与 Oracle 不兼容。Oracle 在缺省状态下是 DELETE ROWS）。

DELETE ROWS

在每一个事务结束的时候，删除临时表中的所有记录。实质上，就是在每一次提交后，自动执行一个 TRUNCATE 操作。

TABLESPACE tablespace

tablespace 是新创建表所在表空间的名称。如果没有指定的话，就是用参数 default_tablespace 指定的表空间。如果参数default_tablesapce 的值是空的，那么就用数据库的缺省表空间。

USING INDEX TABLESPACE tablespace

这个子句允许选择创建与唯一性或者主键相关的索引所在的表空间。如果没有指定的话，就是用参数 default_tablespace 指定的表空间。如果 default_tablesapce 的参数值是空的，那么就用数据库的缺省表空间。

PARTITION BY RANGE ( column_name [, ... ] )

分区子句用于创建分区表。其中 PARTITION BY RANGE 关键字用于创建范围分区。它使用一套 VALUES LESS THAN 操作符显示了某一范围内的值。当使用范围分区时，必须使用 VALUES LESS THAN 定义至少一个分区。

PARTITION BY LIST ( column_name [, ... ] )

创建列表分区

PARTITION BY HASH ( column_name [, ... ] )

创建哈希分区

WITH NOLOGGING

指不记录 wal 日志，写入速度快，备库无数据，只有结构。当数据库 crash 后，数据库重启时自动清空 unlogged table 的数据。正常关闭数据库，再启动时，unlogged table 有数据。

注意

为实现强制唯一性，AntDB 自动为每一个唯一性约束或者主键约束创建一个索引。因此对于标识为主键的列，不用显式地创建一个索引。（更多信息参见 CREATE INDEX 命令）

示例

创建表 dept 和 emp：

CREATE TABLE dept (
deptno	NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname	VARCHAR2(14),
loc	VARCHAR2(13)
);

创建外键约束，注意单机版本支持以下 SQL，但是在 AntDB 集群版中，外键和主键必须在一个字段上：

CREATE TABLE emp (
empno	NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename	VARCHAR2(10),
job	VARCHAR2(9),
mgr	NUMBER(4),
hiredate	DATE,
sal	NUMBER(7,2),
comm	NUMBER(7,2),
deptno	NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);

为表 dept 定义一个唯一性表级约束。单机版本可以在表中多列上定义唯一性表级约束，但是集群版本只能在一列上创建唯一性约束：

CREATE TABLE dept (
deptno	NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname	VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
loc	VARCHAR2(13)
);

定义一个列级检查约束：

CREATE TABLE emp (
empno	NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename	VARCHAR2(10),
job	VARCHAR2(9),
mgr	NUMBER(4),
hiredate	DATE,
sal	NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm	NUMBER(7,2),
deptno	NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);

定义一个表级检查约束：

CREATE TABLE emp (
empno	NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename	VARCHAR2(10),
job	VARCHAR2(9),
mgr	NUMBER(4),
hiredate	DATE,
sal	NUMBER(7,2),
comm	NUMBER(7,2),
deptno	NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno),
CONSTRAINT new_emp_ck CHECK (ename IS NOT NULL AND empno > 7000)
);

为表 jobhist 定义一个主键表级约束。可以在表中多列上定义主键表级约束。注意，集群版本中，主键列必须是分片键：

CREATE TABLE jobhist (
empno	NUMBER(4) NOT NULL,
startdate	DATE NOT NULL,
enddate	DATE,
job	VARCHAR2(9),
sal	NUMBER(7,2),
comm	NUMBER(7,2),
deptno	NUMBER(2),
chgdesc	VARCHAR2(80),
CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate));

为列 job 分配一个文本常量缺省值，并将记录插入的日期设定为列 hiredate 的缺省值：

CREATE TABLE emp (
empno	NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename	VARCHAR2(10),
job	VARCHAR2(9) DEFAULT 'SALESMAN',
mgr	NUMBER(4),
hiredate	DATE DEFAULT SYSDATE,
sal	NUMBER(7,2),
comm	NUMBER(7,2),
deptno	NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);

在表空间 diskvol1 创建表 dept：

CREATE TABLE dept (
deptno	NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname	VARCHAR2(14),
loc	VARCHAR2(13)
) TABLESPACE diskvol1;

创建范围分区表：

CREATE TABLE CUSTOMER 
( 
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
    FIRST_NAME  VARCHAR2(30) NOT NULL, 
    LAST_NAME   VARCHAR2(30) NOT NULL, 
    PHONE        VARCHAR2(15) NOT NULL, 
    EMAIL        VARCHAR2(80), 
    STATUS       CHAR(1) 
) 
PARTITION BY RANGE (CUSTOMER_ID) 
( 
    PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE custs01, 
    PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE custs01 
);

创建 LIST 分区表：

CREATE TABLE CUSTOMER 
( 
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
    FIRST_NAME  VARCHAR2(30) NOT NULL, 
    LAST_NAME   VARCHAR2(30) NOT NULL, 
    PHONE        VARCHAR2(15) NOT NULL, 
    EMAIL        VARCHAR2(80), 
    STATUS       CHAR(1) 
) 
PARTITION BY LIST (CUSTOMER_ID) 
( 
    PARTITION CUS_PART1 VALUES(100000,200000) TABLESPACE custs01, 
    PARTITION CUS_PART2 VALUES(300000) TABLESPACE custs01 
);

CREATE TABLE AS

名称

CREATE TABLE AS —— 根据查询的结果定义一张新表。

概要

CREATE [ GLOBAL TEMPORARY ] TABLE table_name

[ (column_name [, ...] ) ]

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ]

[ TABLESPACE tablespace ]

AS query

描述

CREATE TABLE AS 创建一张新表，并且将 SELECT 命令返回的结果集填充到新表中。新表列的名称和数据类型与 SELECT 命令输出的列一样（除非明确给出新创建表的列名）。

CREATE TABLE AS 命令和创建视图的命令有一些类似的地方，但实际上两个命令的作用完全不一样：CREATE TABLE AS 创建一张新表并且只运行一次查询，将查询的内容填充到新表中。新表不跟踪源表中内容的变化，而无论什么时候查询视图，总是重新运行定义视图的 SELECT 命令。

参数

GLOBAL TEMPORARY

如果指定了这个参数，那么所创建的表是临时表。详细内容请参考 CREATE TABLE 命令。

table_name

要创建的数据表的名称（可以采用模式限定的方式引用）。

column_name

新表中列的名称。如果没有提供列名，那么列名就是查询输出中的列名。

query

查询语句（SELECT 命令）。所允许语法描述请参考 SELECT 命令。

CREATE VIEW

名称

CREATE VIEW —— 定义一个新的视图。

概要

CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ]

AS query

描述

CREATE VIEW 命令定义一个新的查询视图。视图不是实际物化的，而是每一次查询视图的时候，都是实际运行视图定义中查询语句。

CREATE OR REPLACE VIEW 命令的功能类似，但是如果相同名称的视图已经存在，那么将用新创建的视图替代已存在视图。

如果指定了模式名称（例如，CREATE VIEW myschema.myview ...），那么视图就在指定模式下创建，否则就在当前模式下创建。所创建视图的名称必须和同一模式下其它视图、表、序列或者索引有所区别。

参数

name

所创建视图的名称（可以采用模式限定的方式引用）。

column_name

所创建视图列的名称（可选）。如果没有使用这个参数，那么就是用查询输出中的列名。

query

为视图提供列和行的查询语句（是 SELECT 命令）。

关于有效查询的更多信息，参考 SELECT 命令。

注意

当前，视图是只读的-系统不允许在视图上执行插入，更新或者删除操作。通过在视图上创建规则，将视图上插入，更新等操作重写为到在其他表上的相应操作，可以得到可更新视图的效果。

是否允许访问视图中引用的表由视图所有者拥有权限来决定的。如果在使用视图的查询中直接调用函数，那么在视图中调用的函数也做相同的处理。因此，使用视图的用户必须有权限调用视图用到的所有函数。

示例

创建一个视图，包含所有在部门编号为 30 的雇员：

CREATE VIEW dept_30 AS SELECT * FROM emp WHERE deptno = 30; 

DELETE

名称

DELETE —— 删除表中的记录。

概要

DELETE FROM table

[ WHERE condition ]

[ RETURNING return_expression [, ...]

{ INTO { record | variable [, ...] }

| BULK COLLECT INTO collection [, ...] } ]

描述

DELETE 命令用于从指定的表中删除满足 WHERE 子句条件的记录。如果没有使用 WHERE 子句，那么就会删除表中所有的记录。命令结果虽然有效，但是清空了表的所有内容。

注意：TRUNCATE 命令提供更快速的机制从表中删除所有记录。

只有在 SPL 程序中使用 DELETE 命令时，才能指定 RETURNING INTO { record |variable [, ...] }子句，除此之外，DELETE 命令的结果集只能有一条记录，否则会产生异常。如果结果集为空，那么就会把目标记录的内容或者变量设定为空。

只有在 SPL 程序中使用 DELETE 命令，才能指定 RETURNING BULK COLLECT INTO collection [, ...] 子句。如果指定了多个集合作为 BULK COLLECT INTO 子句的目标对象，那么每一个集合必须有一个单独的标量字段组成，集合不能是记录。DELETE 命令的结果集既可以不包括记录，也可包括一条至多条记录。为结果集中每条记录进行计算的 return_expression 成为一个集合的成员，这个集合的位置从第一个成员开始。任何在集合中已存在记录都会被删除。如果结果集是空的，那么集合也将是空的。

如果要从表中删除记录，则必须在表上拥有 DELETE 权限，为了在条件表达式中表中读取值，还需要有 SELECT 权限。

参数

table

一个已存在的数据表的名称（可以采用模式限定的方式引用）。

condition

一个表达式，返回一个布尔类型值，用于决定需要删除的记录。

return_expression

一个包含表中一列或多列的表达式。如果在 return_expression 中指定了表的列名，那么当计算 return_expression 的时候，所替代的值就是被删除记录中的值。

record

这是一个记录，计算后 return_expression 的值赋给了这个记录的字段。顺序是这样的，第一个 return_expression 分配给记录中的第一个字段，第二个分配给记录中的第二个字段，以此类推。记录中的字段数量必须和表达式的数量相吻合，并且字段类型必须和分配给它们的表达式类型兼容。

variable

一个变量，对参数 return_expression 计算完后会把结果值分配给这个变量。如果指定了多个表达式和变量，那么按照第一个 return_expression 分配给第一个变量，第二个 return_expression 分配给第二个变量，这样的顺序进行。在关键字 INTO 后面的变量数量必须和关键字 RETURNING 后面的相一致，并且变量的类型必须和分配给它们的表达式相兼容。

collection

集合的成员是通过计算后的 return_expression 来创建的。这里既可以是一个单独的集合，属于单一字段的集合或者一个记录类型的集合，也可以有多个集合，其中每个集合由一个单一字段组成。返回表达式在数量和顺序上必须和所有指定集合的字段相匹配。每一个相对应的 return_expression 和集合的字段必须是类型兼容的。

示例

从表 jobhist 中删除所有雇员编号为 7900 的记录：

DELETE FROM jobhist WHERE empno = 7900; 

清空表 jobhist 的所有内容：

DELETE FROM jobhist; 

SPL 语言中使用 delete：

CREATE OR REPLACE FUNCTION delete_sal (eno in int) return void
IS
type t_table is table of emp;
var_sal t_table;
temp int;
BEGIN
DELETE FROM emp WHERE empno = eno returning * bulk collect into var_sal ;
for i in 1..var_sal.count loop
    DBMS_OUTPUT.PUT_LINE(var_sal[i].empno); 
	DBMS_OUTPUT.PUT_LINE(var_sal[i].ENAME); 
	DBMS_OUTPUT.PUT_LINE(var_sal[i].JOB);
	DBMS_OUTPUT.PUT_LINE(var_sal[i].HIREDATE);
end loop;

END;
/

DROP FUNCTION

名称

DROP FUNCTION —— 删除一个函数。

概要

DROP FUNCTION name [ ([ type [, ...] ]) ]

描述

DROP FUNCTION 命令删除一个已经存在函数的定义。只有数据库的超级用户或者函数的所有者才能执行这个命令。如果函数有参数，就要指定参数类型。（这个要求与 Oracle 不兼容，AntDB 允许对函数名称的进行重载，所以在 AntDB 中的 DROP FUNCTION 命令中需要完整的函数签名）。

参数

name

已存在函数的名称（可以采用模式限定的方式引用）。

type

在函数中参数的数据类型。

示例

下面的命令删除了函数 emp_comp：

| DROP FUNCTION emp_comp(NUMBER, NUMBER); 

DROP INDEX

命令

DROP INDEX —— 删除一个索引。

概要

DROP INDEX name

描述

DROP INDEX 命令从数据库系统中删除一个已存在的索引。只有数据库的超级用户或者索引的所有者才能执行这条命令。如果有任何对象对索引有依赖关系，那么执行这条命令将会报错，不能删除索引。

参数

name

要删除索引的名称（可以采用模式限定的方式引用）。

示例

这条命令删除名称为 name_idx 的索引。

DROP INDEX name_idx; 

DROP PROCEDURE

名称

DROP PROCEDURE —— 删除一个存储过程。

概要

DROP PROCEDURE name

描述

DROP PROCEDURE 命令删除一个已存在的存储过程。只有数据库的超级用户或者存储过程的所有者才能执行这条命令。

参数

name

一个已存在存储过程的名称（可以采用模式限定的方式引用）。

示例

下面这条命令删除存储过程 select_emp。

DROP PROCEDURE select_emp;

DROP SEQUENCE

名称

DROP SEQUENCE —— 删除一个序列。

概要

DROP SEQUENCE name [, ...]

描述

DROP SEQUENCE 命令删除一个序列生成器。只有数据库的超级用户或者序列的所有者才能执行这条命令。

参数

name

序列的名称（可以采用模式限定的方式引用）。

示例

删除序列 serial：

DROP SEQUENCE serial; 

DROP TABLE

名称

DROP TABLE —— 删除一张表。

概要

DROP TABLE name

描述

DROP TABLE 命令用于从数据库中删除表。只有表的所有者才能删除一张表。如果想清空表的内容，而不是删除表，请使用 DELETE 命令。

DROP TABLE 命令同时删除表中的索引，规则，触发器和约束。

参数

name

要删除表的名称（可以采用模式限定的方式引用）。

示例

删除表 emp：

DROP TABLE emp;

DROP VIEW

名称

DROP VIEW —— 删除一个视图。

概要

DROP VIEW name

描述

DROP VIEW 删除一个已存在视图。只有视图的所有者才能执行这条命令。如果其它对象和这个视图有依赖关系（例如通过这个视图产生另外一个视图），那么将不能删除该视图。

参数

name

要删除的视图名称（可以采用模式限定的方式引用）。

示例

这条命令删除视图 dept_30：

DROP VIEW dept_30;

INSERT

名称

INSERT —— 在表中创建新记录。

概要

INSERT INTO table [ ( column [, ...] ) ]

{ VALUES ( { expression | DEFAULT } [, ...] )

[ RETURNING return_expression [, ...]

{ INTO { record | variable [, ...] }

| BULK COLLECT INTO collection [, ...] } ]

| query }

描述

INSERT 命令用于向表中插入新的记录。既可以一次插入一条记录，也可以根据一个查询的结果插入多条记录。

在目的列表中的列可是以以任意顺序列出。对于目的列表中没有出现的列，INSERT 命令插入缺省值，这个缺省值既可以是在声明列时指定的，也可以是空值。

如果与每一列对应的表达式不是正确的数据类型，那么将尝试进行自动类型转换。

只有在 SPL 程序中使用 INSERT 命令，并且是在仅使用了 VALUES 子句的时候，才可以指定 RETURNING INTO { record | variable [, ...] } 子句。

只有在 SPL 程序中使用 INSERT 命令的时候，才能使用 RETURNING BULK COLLECT INTO collection [, ...] 子句。如果指定了多个集合作为 BULK COLLECT INTO 子句的目标，那么每一个集合必须由一个单独的标量字段组成。针对每一条插入的记录进行计算的 return_expression 变成了集合中的成员，这个集合的位置从第一个成员开始。任何在集合中已存在的记录将会被删除。如果结果集是空的，那么集合也将是空的。

为了向表中插入数据，必须在表上拥有 INSERT 权限。如果是将查询子句中查询结果插入到表中，那么还需要在查询用到的表上拥有 SELECT 权限。

参数

table

已存在表的名称（可以采用模式限定的方式引用）。

column

在表中列的名称。

expression

分配给列的表达式或者值。

DEFAULT

使用缺省值填充到列中。

query

提供要插入记录的 SELECT 语句。具体语法请参见 SELECT 命令。

return_expression

包含表中一列或多列的表达式。如果在 return_expression 中指定了表的一个列，那么当计算 return_expression 的时候，用于替代列的值由以下情况决定：

如果在 INSERT 命令中为 return_expression 中指定的列分配了值，那么在计算 return_expression 时就使用被分配的值。

如果在 INSERT 命令中没有为 return_expression 中指定的列分配值，并且在表中列定义中没有缺省值，那么在计算 return_expression 时就使用空值。

如果在 INSERT 命令中没有为 return_expression 中指定的列分配值，但是在表中列定义中有缺省值，那么在计算 return_expression 时就使用缺省值。

record

记录的字段值是计算后的 return_expression。按照第一个计算后的 return_expression 分配给记录中的第一个字段，第二个计算后的 return_expression 分配给记录中的第二个字段这样的顺序进行分配。记录中字段的数量必须和表达式的数量一致。字段的类型和分配给它们的表达式必须是类型兼容的。

variable

变量的值是计算后的 return_expression。如果指定了多个 return_expression 和变量，那么按照第一个 return_expression 分配给第一个变量，第二个 return_expression 分配给第二个变量这样的一个顺序进行分配。关键字 INTO 后面变量的数量必须和 RETURNING 子句后面表达式的数量一样，并且变量和分配给它们的表达式必须类型兼容。

collection

集合中的成员是通过计算后的 return_expression 来创建的。这里即可以是一个单独的集合，包含单独字段的集合或者记录类型的集合，也可以有多个集合，每个集合由一个单独字段组成。返回表达式的数量和出现顺序必须和所有指定集合中的字段一致。每个与 collection 中字段相对应的 return_expression 的类型必须兼容。

示例

向表 emp 插入一条记录：

INSERT INTO emp VALUES (8021,'JOHN','SALESMAN',7698,'22-FEB-07',1250,500,30); 

在第二个示例中，目标列表中的没有列 comm，因此将向这个列插入空值作为缺省值：

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, deptno) VALUES (8022,'PETERS','CLERK',7698,'03-DEC-06',950,30); 

在第三个示例中，在列 hiredate 和 comm 上没有指定值，而是使用了 DEFAULT 子句：

INSERT INTO emp VALUES (8023,'FORD','ANALYST',7566,NULL,3000,NULL,20);

在这个示例中为部门名称创建一张表，将从表 dept 中（dept 表格定义见【示例参考表格】）查询出 dname 列的值插入表 deptname 中：

CREATE TABLE deptnames (
deptname	VARCHAR2(14)
);
INSERT INTO deptnames SELECT dname FROM dept;

LOCK

名称

LOCK —— 锁定一张表。

概要

LOCK TABLE name [, ...] IN lockmodeMODE [ [ WAIT [, integer] | NOWAIT ]

其中 lockmode 是下列五个选项之一：

lock_mode	描述
ROW SHARE	允许同时访问表，但阻止用户锁定整个表以进行独占访问。
ROW EXCLUSIVE	允许对表进行并发访问，但阻止用户以独占访问方式锁定整个表并以共享方式锁定表。
SHARE	允许并发查询，但用户无法更新锁定的表。
SHARE ROW EXCLUSIVE	用户可以查看表中的记录，但是无法更新表或锁定 SHARE 表中的表。
EXCLUSIVE	允许查询锁定的表格，但不能进行其他活动。

描述

LOCK TABLE 命令用于获取一个表级锁，在必要的时候等待任何冲突的锁释放。如果指定了 NOWAIT，那么 LOCK TABLE 命令不会等待获取期望的锁：如果不能马上获取锁，那么这条命令会中断并且产生一个错误。当获得一个锁的时候，锁作为当前事务的剩余部分被保持（这里没有“UNLOCK TABLE”命令。当事务结束的时候，会自动释放锁的）。

当命令中引用到表自动获取一个锁的时候，AntDB 总是尽量使用最小限制级别的锁模式。当需要更高限制级别锁的时候，可以使用 LOCK TABLE 命令。例如，可能希望一个程序在运行级别为 read committed 的事务期间，需要在表里的数据保持稳定状态。要实现这个目的，可以在查询前在表上获取一个 SHARE 锁模式。这将防止并发的更改数据操作，并将确保随后对表的读操作看到已提交数据的稳定视图，因为 SHARE 锁模式和写进程获取的行级 EXCLUSIVE 锁冲突，并且在任何行级 EXCLUSIVE 锁的并发持有者提交或者回滚事务之前，LOCK TABLE name IN SHARE MODE 命令将处于等待状态。

当运行一个串行隔离级别的事务时，为了达到上面提到的类似效果，在执行任何数据修改语句之前，必须执行 LOCK TABLE 命令。当开始执行第一条数据修改语句的时候，一个串行事务能看到的数据处于冻结状态。随后的 LOCK TABLE 命令将防止同步写操作，但是它不能保证事务读到的数据与最新提交的值相对应。

如果这种排序方式的事务改变表中的数据，那么它应该使用 SHARE ROW EXCLUSIVE 锁模式，而不是 SHARE 模式。

这条命令确保一次只运行一个这种类型的事务。否则就会产生死锁：2 个事务可能会同时获取 SHARE 模式锁，并且将不能获取 ROW EXCLUSIVE 模式锁来实际执行数据更新操作。（需要注意的是一个事务本身拥有的锁从来不会产生冲突，所以当一个事务获取 SHARE 模式锁，但不是其它事务获取 SHARE 模式的时候，它可以获取 ROW EXCLUSIVE 模式锁）。为避免死锁，必须确保所有事务以相同的顺序在同一的对象获取锁，并且如果对于单独对象，涉及到多个锁模式，那么事务应该首先获取最高限制的锁模式。

参数

name

要锁定表的名称（可以采用模式限定的方式引用）。命令 LOCK TABLE a,b 等同于 LOCK TABLE A; LOCK TABLE b。 锁定表的顺序是在LOCK TABLE 命令中指定的。

lockmode

锁模式。

WAIT

指定数据库将等待（达到指定整数的特定秒数）以获取 DML 锁定。

NOWAIT

指定 LOCK TABLE 命令不应该等待任何相冲突的锁释放：如果在没有等待的情况下不能立即获得指定的锁，那么就会中断事务。

注意

所有形式的锁都要求 UPDATE 或 DELETE 权限。

LOCK TABLE 只是在事务块的内部有用，因为当事务结束后，马上就删除锁。

LOCK TABLE 只处理表级锁，所以涉及到 ROW 的模式名称都是错误名词。这些模式名称通常能够指示出用户要在被锁定的表内部获取行级锁的意图。ROW EXCLUSIVE 锁模式是一个可共享的表级锁。要记住的是，当关注 LOCK TABLE 的时候所有的锁模式都有相同的语义，只是在关于哪几种模式之间有冲突的规则上有所区别。

示例

--session1：
VREATE TABLE tb(id int, num int);
INSERT INTO tb VALUES(1,2);
BEGIN;
LOCK TABLE tb in SHARE mode nowait;

--session2执行下列命令会一直等待锁释放
UPDATE tb SET num = 3 WHERE id = 1;

ROLLBACK

名称

ROLLBACK —— 中断当前事务。

概要

ROLLBACK

描述

ROLLBACK 命令回滚当前事务，并且放弃事务进行的所有更新操作。

参数

WORK

可选的关键字 – 在实际操作中没有任何作用。

注意

使用 COMMIT 命令可以成功终止一个事务。

在事务的外部执行 ROLLBACK 命令不会产生副作用。

示例

放弃所有数据更改操作：

ROLLBACK;

ROLLBACK TO SAVEPOINT

名称

ROLLBACK TO SAVEPOINT —— 将事务回滚到一个保存点。

概要

ROLLBACK TO [ SAVEPOINT ] savepoint_name

描述

对所有在保存点建立后执行的命令进行回滚。如果需要的话，保存点依然保持有效并且可以再次用来回滚。在命名保存点后，ROLLBACK TO SAVEPOINT 命令在隐含状态下消除先前建立的所有保存点。

参数

savepoint_name

要回滚到的保存点的名称。

注意

指定一个没有建立的保存点名称会产生一个错误。

在 SPL 程序内部不支持 ROLLBACK TO SAVEPOINT 命令。

示例

撤销保存点 depts 后面命令的操作：

INSERT INTO dept VALUES (50, 'HR', 'NEW YORK');
SAVEPOINT depts;
INSERT INTO emp (empno, ename, deptno) VALUES (9001, 'JONES', 50);
INSERT INTO emp (empno, ename, deptno) VALUES (9002, 'ALICE', 50);
ROLLBACK TO SAVEPOINT depts;

SAVEPOINT

名称

SAVEPOINT —— 在当前事务中定义一个新的保存点。

概要

SAVEPOINT savepoint_name

描述

SAVEPOINT 命令在当前事务中建立一个新的保存点。

保存点是在事物内部的一个特定标记，允许保存点建立后执行的命令回滚到保存点所在的位置，恢复在保存点创建时事务的状态。

参数

savepoint_name

保存点的名称。

注意

使用 ROLLBACK TO SAVEPOINT 命令回滚到一个保存点的位置。

只能在事务块内部建立保存点。在事物内部可以建立多个保存点。

当新的保存点名称与先前建立的保存点名称一样的时候，尽管当进行回滚操作的时候只使用最近建立的保存点，但是仍保持原有的保存点。

当前在 SPL 程序内部不支持 SAVEPOINT 命令。

示例

建立一个保存点，然后回滚在保存点后面执行的所有命令（ emp 表格定义见【示例参考表格】）：

BEGIN;
INSERT INTO dept VALUES (50, 'HR', 'NEW YORK');
SAVEPOINT depts;
INSERT INTO emp (empno, ename, deptno) VALUES (9001, 'JONES', 50);
INSERT INTO emp (empno, ename, deptno) VALUES (9002, 'ALICE', 50);
SAVEPOINT emps;
INSERT INTO jobhist VALUES (9001,'17-SEP-07',NULL,'CLERK',800,NULL,50,'New Hire');
INSERT INTO jobhist VALUES (9002,'20-SEP-07',NULL,'CLERK',700,NULL,50,'New Hire');
ROLLBACK TO depts;
COMMIT;

上面的事务向表 dept 提交一条数据，但是对向表 emp 和 jobhist 插入记录的操作进行了回滚。

SET TRANSACTION

名称

SET TRANSACTION —— 设置当前事务的特性。

概要

SET TRANSACTION transaction_mode

其中 transaction_mode 是下列几种选项之一

ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }

READ WRITE | READ ONLY

描述

SET TRANSACTION 设置当前事务的特性，而对后面的事务没有任何作用。允许使用的事务处理特性是事务隔离别级别和事务访问模式（读/写或者只读）。当其它事务处于并发运行状态时候，事务的隔离级别决定了事务能看到的数据：

READ COMMITTED

语句只能看到这个语句运行前提交的记录，这是缺省设置。

SERIALIZABLE

在事务中执行完成第一个查询或者数据修改语句之前，所有当前事务的语句只能看到已提交的记录。

在事务中第一个查询或者数据修改语句（包括 SELECT、INSERT、DELETE、UPDATE 或者 FETCH）运行后，事务隔离级别不能改变。

事务访问模式决定了事务是读/写模式，还是只读模式。读/写模式是缺省状况。当事务是只读模式的时候，不允许执行下面这些 SQL 命令：INSERT，UPDATE，如果他们写入的不是临时表那么也包括 DELETE 命令；所有的 CREATE、ALTER 和 DROP 命令。COMMENT、GRANT、REVOKE、TRUNCATE；并且如果执行的命令包括上面列出的命令，也包括 EXECUTE 命令。这是一个高级别关于只读的概念，不能防止到磁盘的所有写操作。

TRUNCATE

名称

TRUNCATE —— 清空一张表。

概要

TRUNCATE TABLE name

描述

TRUNCATE 命令快速从表中清除所有记录，和非限定的 DELETE 语句的作用相同，但是因为它不实际扫描表，所以执行速度更快。对清空大表内容这样的操作来说，这条命令是非常有用的。

参数

name

要截断表的名称（可以采用选模式限定的方式引用）。

注意

如果从其它表到要截断的表有外键引用，则不能使用 TRUNCATE 命令。在这种情况下，检查有效性的操作将会要求进行表扫描，在实际应用中不推荐这么做。

TRUNCATE 命令将不会触发任何用户在表上定义的 ON DELETE 触发器。

示例

截断表 bigtable。

TRUNCATE TABLE bigtable; 

UPDATE

名称

UPDATE —— 更新表的记录。

概要

UPDATE table

SET column = { expression | DEFAULT } [, ...]

[ WHERE condition ]

[ RETURNING return_expression [, ...]

{ INTO { record | variable [, ...] }

| BULK COLLECT INTO collection [, ...] } ]

描述

UPDATE 命令改变所有满足条件的记录中指定列的值。只需要SET子句中显示要修改值的列，而没有被修改的列保持原先的值。

RETURNING INTO { record | variable [, ...] }子句只能在 SPL 程序中使用。如果结果集是空的，就会把目标变量或变量的内容设定为空。

用户在表上必须有 UPDATE 权限，才能更新记录，而且如果需要从任何表中为表达式或者条件读取值，那么也需要相关 SELECT 权限。

参数

table

要更新数据表的名称。（可以采用模式限定的方式引用）

column

表中一个列的名称。

expression

分配给列的表达式。表达式可以是表中列原来的值和其他列的值。

DEFAULT

将列设定为它的缺省值。（如果没有指定表达式，那么将分配空值）

condition

一个返回 BOOLEAN 类型值的表达式，只有符合表达式返回值为真的记录才能被更新。

return_expression

包含表中一列或多列的表达式。如果表中列名在 return_expression 中指定，那么当计算 return_expression 的时候，替代列的值以下列方式决定：

如果 UPDATE 命令中，对 return_expression 中指定的列分配了值，那么所分配的值在计算 return_expression 时使用。

如果 UPDATE 命令中，没有对 return_expression 中指定的列分配值，那么在所操作记录中列的当前值就是 return_expression 的计算结果。

record

记录的字段值是计算后的 return_expression。按照第一个 return_expression 分配给记录中的第一个字段，第二个 return_expression 分配给记录中的第二个字段..这样的顺序进行赋值。记录中字段的数量必须匹配表达式的数量，并且字段的数据类型必须和所分配表达式的类型相兼容。

variable

计算后的表达式分配给了一个变量。如果指定了多个 return_expression 和变量，那么按照第一个 return_expression 分配给第一个变量，第二个 return_expression 分配给记录中的第二个变量，这样的顺序进行赋值分配。在关键字后所指定的变量数量必须和关键字 RETURNING 后面表达式的数量完全一致，并且变量必须和所分配表达式在类型上相兼容。

collection

集合的成员是由计算后的表达式来创建的。这里既可以是一个单独的集合（可以是单独字段的集合或者是记录类型的集合）也可以是多个由单独字段组成的集合。所返回表达式必须在数量，和出现顺序上与所有指定集合中的字段相匹配。每一个对应的 return_expression 和集合字段必须是类型兼容的。

示例

在表 dept 中，将部门编号为 20 的地理位置改成 AUSTIN：

UPDATE dept SET loc = 'AUSTIN' WHERE deptno = 20; 

把表 emp 中所有工作是 SALESMAN 的雇员薪水增加百分之十，佣金增加 500 元。

UPDATE emp SET sal = sal * 1.1, comm = comm + 500 WHERE job = 'SALESMAN'; 

把某一个员工的工资增加 1000 块钱，并且打印这个员工的信息（ emp 表格定义见【示例参考表格】）。

\set PLSQL_MODE ON
CREATE OR REPLACE FUNCTION update_sal (eno in int) return int
IS
type t_table is table of emp;
var_sal t_table;
BEGIN
UPDATE emp SET sal = sal + 1000  WHERE empno = eno returning * bulk collect into var_sal;

for i in 1..var_sal.count loop
 DBMS_OUTPUT.PUT_LINE(var_sal[i].empno); 
	DBMS_OUTPUT.PUT_LINE(var_sal[i].ENAME); 
	DBMS_OUTPUT.PUT_LINE(var_sal[i].JOB);
	DBMS_OUTPUT.PUT_LINE(var_sal[i].HIREDATE);
END loop;
RETURN var_sal.count;
END;
/
\set PLSQL_MODE OFF
SELECT update_sal(7876);
NOTICE:  7876
NOTICE:  ADAMS
NOTICE:  CLERK
NOTICE:  1987-05-23 00:00:00
 UPDATE_SAL 
------------
          1
(1 row)

SELECT

名称

SELECT —— 从表或者视图中获取记录。

概述

SELECT [ optimizer_hint ] [ ALL | DISTINCT ]

* | expression [ AS output_name ] [, ...]

FROM from_item [, ...]

[ WHERE condition ]

[ [ START WITH start_expression ]

CONNECT BY { PRIOR parent_expr = child_expr |

child_expr = PRIOR parent_expr }

[ ORDER SIBLINGS BY expression [ ASC | DESC ] [, ...] ]

]

[ GROUP BY expression [, ...] [ LEVEL ] ]

[ HAVING condition [, ...] ]

[ { UNION [ ALL ] | INTERSECT | MINUS } SELECT ]

[ ORDER BY expression [ ASC | DESC ] [, ...] ]

[ FOR UPDATE ]

from_item是下列选项之一

table_name [ alias ]

( select ) alias

from_item [ NATURAL ] join_type from_item

[ ON join_condition | USING ( join_column [, ...] ) ]

描述

SELECT 命令从一张或多张表上获取记录，命令的处理流程如下：

• 首先计算在 FROM 列表中的所有成员。（在 FROM 列表中每一个成员都是一个真实或虚拟的表）如果在 FROM 列表中指定了多个成员，那么这些成员就会执行连接操作。（参见下面的 FROM 子句）

• 如果指定了 WHERE 子句，那么所有不满足查询条件的记录将不会出现在输出中。（参见下面的 WHERE 子句）

• 如果指定了 GROUP BY 子句，那么就会把输出的记录根据一个或者多个值分成组。如果使用了 HAVING 子句，那么只显示出符合指定条件的组（参见下面的 GROUP BY 和 HAVING 子句部分）

• 如果使用了 UNION，INTERSECT 和 MINUS 操作符，那么就会把多个 SELECT 命令的输出组合成一个单独的结果集。操作符 UNION 返回一个或全部结果集的记录。INTERSECT 返回在所有结果集中都存在的记录。操作符 MINUS 返回出现在第一个结果集中的记录，但不返回出现在第二个结果集中的记录。在这所有三种情况中，不存在重复记录。在使用 UNION 操作符的时候。如果指定了关键字 ALL，则不会删除重复记录（详细信息请看 UNION，INTERSECT 和 MINUS 子句）。

• 实际的输出记录是通过对每条记录使用 SELECT 输出表达式来计算得出的（参见下面的 SELECT 列表）。

• CONNECT BY 子句用于查询有层次关系的数据。这样的数据在记录之间是有父子关系的（参见 CONNECT BY 子句）。

• 如果指定了 ORDER BY 子句，那么返回的记录依照指定的顺序排序。如果没有使用 ORDER BY 子句，那么返回记录的顺序就是系统以最快速度产生记录时的顺序（参见下面的 ORDER BY 子句）。

• DISTINCT 子句从结果集中删除重复的记录。如果指定了 ALL（这是缺省情况），那么将返回所有记录，这其中包括重复数据（参见下面的 DISTINCT 子句）。

• FOR UPDATE 子句使 SELECT 语句锁定了所选择的记录行，避免其它事务对这些记录的同步修改。（参见下面的 FOR UPDATE 子句）。

用户必须在表上有 SELECT 权限才能读取它的数据，如果使用 FOR UPDATE 子句，则同时要求 UPDATE 权限。

FROM 子句

FROM 子句为 SELECT 命令指定一张或多张数据表。如果指定了多张数据表，那么结果集是所有数据表的的笛卡尔乘积。通常会使用查询条件限制查询返回的记录，以减小笛卡尔乘积的结果集。

FROM 子句包含下列成员：

table_name

是已存在表或视图的名称（可以采用模式限定的方式引用）。

alias

在 FROM 子句后面对真实表名进行替代的名称。化名用于简写表名或者消除自连接时表名不明确的情况（这是由于相同的表需要扫描多次）。当提供了化名后，它将完全隐含表或者函数的真实名称。例如，对于语句 FROM foo as f 来说，SELECT 命令实际上要参考的是 FROM 子句后面的 f， 而不是 foo。

select

在 FROM 子句中，可以出现另外一个层次的 SELECT 语句。这个层次 SELECT 命令的输出可以当作为单一 SELECT 命令运行期间所创建的临时表。需要注意的是这个层次的 SELECT 命令必须用括号括起来，并且要为这个语句的输出提供一个化名。

join_type

join_type 属于如下这几种连接类型之一。

[ INNNER ] JOIN

LEFT [ OUTER ] JOIN

RIGHT [ OUTER ] JOIN

FULL [ OUTER ] JOIN

CROSS JOIN

对于连接类型 INNER 和 OUTER 来说，必须指定一个连接条件，也就是 NATUREL，ON join_condition 或者 USING (join_column [, ...] )这三个中的一个。具体含义参见下面的解释。而对于 CROSS JOIN 来说，不会出现这些子句。

JOIN 子句的作用是将 FROM 子句后面的两个表或视图结合起来进行查询。如果必要的话，可以使用括号来确定嵌套的顺序。如果没有括号的话，JOINS 嵌套的顺序是从左到右。在任何情况下，JOIN 子句的绑定级别比 FROM 后用逗号分隔的表或视图要高。

CROSS JOIN 和 INNER JOIN 的操作结果都是一个简单的笛卡尔乘积。它的结果集和在最上层 FROM 子句中两个表或视图进行的连接操作所获取的一样，但是连接条件是有限制的。CROSS JOIN 等同于 INNER JOIN ON(TRUE)，这样就不会使用限定条件删除某些记录。这些连接类型仅仅是为了记法方便，如果不与 FROM 和 WHERE 子句一起工作，将没有任何意义。

LEFT OUTER JOIN 返回符合条件的笛卡尔乘积中所有记录（例如，所有通过连接条件的组合记录），加上与子句右侧表中记录不匹配的左侧表中记录的拷贝。通过为子句右侧表的列插入空值，左侧的记录行延伸到连接表的整个宽度。

与 LEFT OUTER JOIN 相反，RIGHT OUTER JOIN 返回所有通过连接条件的记录，加上与子句右侧表中不匹配的记录，这只是记法的方便。通过将子句左侧的表转换成右侧的表，可以将 RIGHT OUTER JOIN 转换为 LEFT OUTER JOIN。

FULL OUTER JOIN 返回所有符合连接条件的记录，加上不符合条件的子句左侧表中的记录（用空值延伸到右侧）和加上不符合条件的子句右侧表中的记录（用空值延伸到左侧）。

ON join_condition

join_condition 是产生布尔类型返回值的表达式（和 WHERE 子句类似），用于指定在连接中哪些记录匹配连接条件。

USING (join_column [, ...] )

这里有一种形式的子句，USING (a, b, ... ) ，是 ON left_table.a = right_table.a AND left_table.b = right_table.b .... 的缩写。USING 子句意味着每对相等的列只有一个能够被包含在连接输出，而不是全部。

NATURAL

NATURAL 是 USING 列表的简写，用于提及两张表中的所有列有相同的名字。

WHERE 子句

WHERE子 句是可选的，使用方法如下：

WHERE condition

condition 是计算结果为布尔类型的表达式。在输出中不会出现任何不满足这个条件的记录。当实际记录中的值为任何变量所引用的时候，如果表达式返回“true”，那么记录满足查询条件。

GROUP BY 子句

可选的 GROUP BY 子句使用方法如下：

GROUP BY expression [, ...]

GROUP BY 子句将所有共享分组表达式值的输出记录精简为一条记录。分组表达式可以是输入列名，或者输出列名或者序数，或者是从输入列值中形成的任意表达式。在出现语义不明确的时候，在 GROUP BY 子句后面出现的名称被解释为输入列的名称而不是输出列的名称。

如果使用了聚合函数，那么函数就会在每个组中所有记录上运算。为每个组产生一个单独的值。（与之相反，如果没有 GROUP BY 子句，聚合函数在所有查询出的记录上产生一个单独的值） 当出现 GROUP BY 子句的时候，在 SELECT 列表中没有用于分组的列是无法使用聚合函数的，因为这里有多种可能的值来为非分组的列返回值。

示例：

（ jobhist 表格定义见【示例参考表格】）

SELECT job,sum(SAL) FROM jobhist GROUP BY job;

      JOB    | SUM(SAL)  
-------------+--------------
 CLERK       |     7140
 PRESIDENT   |     5000
 MANAGER     |     8275
 SALESMAN    |     5600
 ANALYST     |     6000
(5 rows)

HAVING 子句

HAVING 子句不是必选的，它的使用方法如下：

HAVING condition

其中 condition 和在 WHERE 子句中指定的一样。

HAVING 子句用于消除已分组的记录中不满足条件的记录。HAVING 和 WHERE 子句不同；WHERE 子句在应用 GROUP BY 子句前过滤出特定的记录，而 HAVING 子句过滤出由 GROUP BY 子句所创建的分组记录。除非是在一个聚合函数中引用，在条件中引用的每一列都必须明确地引用一个分组列。

SELECT 列表

SELECT 列表（位于关键字 SELECT 和 FROM 之间）指定了形成 SELECT 命令输出记录的表达式。表达式能够参考在 FROM 子句中计算的列。通过使用子句 AS output_name， 可以为输出列指定另外一个名称。这个名字主要是用来标识要显示的列。它也可以用来参考在 ORDER BY 和 GROUP BY 子句中列的值，而不是在 WHERE 或者 HAVING 子句中的；在因为那里必须明确的写出表达式。

可以在输出列表中写上*，而不是表达式名称，作为简写来表示选择记录中的所有列。

UNION 子句

UNION 子句的使用方式如下：

select_statement UNION [ ALL ] select_statement

select_statement 是不带有 ORDER BY 或 FOR UPDATE 子句的 SELECT 语句。（如果 ORDER BY 是在括号中，那么这个子句添加在子表达式中，如果没有括号的话，这些子句将应用到 UNION 子句的结果中，而不是 UNION 右侧的输入表达式）。

UNION 操作符用于计算 SELECT 语句所返回记录的联合结果集。如果记录至少出现在一个结果集中，那么这条记录就会出现在两个结果集中的联合中，在 UNION 操作符中出现的两个 SELECT 语句必须产生相同数量的列，并且对应的列必须是数据类型相兼容的。

除非使用了 ALL 选项，否则 UNION 子句的结果不包含任何重复的记录。ALL 选项保留所有的重复记录。

除非使用括号指定计算顺序，否则在 SELECT 语句中的多个 UNION 操作符是以从左到右的顺序进行计算的。

当前，既不能为 UNION 结果集指定 FOR UPDATE 子句，也不能为 UNION 的输入指定这个子句。

示例：

CREATE TABLE tb1(id int,sal int);
INSERT INTO tb1 VALUES(1, 20000);
INSERT INTO tb1 VALUES(2, 30000);
CREATE TABLE tb2(id int, sal int);
INSERT INTO tb2 VALUES(1, 30000);
INSERT INTO tb2 VALUES(2, 40000);
SELECT * FROM tb1 UNION SELECT * FROM tb2;
ID  |  SAL  
----+----------
  1 | 30000
  2 | 40000
  1 | 20000
  2 | 30000
(4 rows)

MINUS 子句

MINUS 子句的使用方式如下:

select_statement MINUS select_statement

select_statement 是不带 ORDER BY 或 FOR UPDATE 子句的 SELECT 语句。

操作符 MINUS 计算出的记录集是属于操作符左边的的 SELECT 语句，而不是操作符右边语句的结果集。指的是左边有，右边没有的结果集。

操作符 MINUS 的结果集不包含任何重复的记录。

除非用括号指定计算顺序，否则在同一 SELECT 语句中多个 MINUS 操作的计算顺序都是从左到右。

MINUS 的绑定级别和 UNION 一样。

示例：

CREATE TABLE tb1(id int, sal int);
INSERT INTO tb1 VALUES(1, 20000);
INSERT INTO tb1 VALUES(2, 30000);
CREATE TABLE tb2(id int, sal int);
INSERT INTO tb2 VALUES(1, 30000);
INSERT INTO tb2 VALUES(2, 40000);
SELECT sal FROM tb1 minus SELECT sal FROM tb2;

       SAL
---------------------
     20000

CONNECT BY 子句

当执行层次查询的时候，使用 CONNECT BY 子句可以决定记录之间的父-子关系。这个子句使用方式如下：

CONNECT BY { PRIOR parent_expr = child_expr | child_expr = PRIOR parent_expr }

parent_expr 是在候选父记录上计算的表达式。如果对于由 FROM 子句返回的记录，表达式 parent_expr = child_expr 的结果值是“true”，那么就把这条记录当作父记录上的子节点记录。

下面的可选子句可以在带有 CONNECT BY 子句的关联指定：

START WITH start_expression

在 start_expression 上计算值为“true”的 FROM 子句返回的记录变成层次的根节点。

ORDER SIBLINGS BY expression [ ASC | DESC ] [, ...]

层次中同层次节点记录是按照结果集中表达式来排序的。

ORDER BY 子句

ORDER BY 子句是可选的，它的使用方式如下：

ORDER BY expression [ ASC | DESC ] [, ...]

expression 可以是输出列（SELECT 列表项）的名称和序数，也可以是从输入列值中形成的任意表达式。

可以使用 ORDER BY 子句根据指定的表达式来为结果集排序。具体的比较顺序是从最左边的表达式开始，如果两条记录相等，然后就再根据下一个表达式进行比较。如果根据所有指定表达式，记录相等，那么他们就根据应用的顺序返回。

序数是指结果列的位置（以从左到右的顺序）。这个特性可以用来根据没有唯一名称列为基础定义排序的顺序。当然，这不是绝对必要的，因为可以使用 AS 子句为列分配一个名称。

可以在 ORDER BY 子句中使用任意的表达式，包含没有出现在 SELECT 命令列表中的列。因此，下面的语句是有效的：

SELECT ename FROM emp ORDER BY empno; 

这个特性的限制是应用到 UNION， INTERSECT 或者 MINUS 结果集的 ORDER BY 子句，只可能输出列的名称或者数量，而不是表达式。

如果 ORDER BY 表达式是一个简单的名称，匹配所有结果列和输入列的名称，ORDER BY 将把它解释为结果列的名称，这在相同情况下与 GROUP BY 子句的选择相反。这个不一致的现象是为了和 SQL 标准相兼容。

也可以在 ORDER BY 子句中表达式的后面加上关键字 ASC（升序）或者DESC（降序）。如果没有这些关键字，那么在缺省情况下，ASC 作为缺省的关键字。

空值排序级别比其他任何值都高，换句话说，在升序排序中，空值在是最后的位置，在降序排列中，空值在开始的位置。

字符串数据的排序是根据在数据库集群初始化时建立的本地指定的核对顺序来排序的。

DISTINCT 子句

如果指定了 DISTINCT 子句，那么会从结果集中的删除所有重复记录（每一组重复记录中只保留一条记录）。ALL 子句的效果相反，将保留所有的记录，这是缺省情况。

FOR UPDATE 子句

FOR UPDATE 子句的使用方式如下：

FOR UPDATE

FOR UPDATE 子句锁定 SELECT 命令返回的记录，和为进行更新操作而锁定记录的效果是一样的。这样做可以在当前事务结束之前，防止其它事务修改或删除这些记录。也就是说，在当前事务结束之前，其他进程尝试进行对这些记录进行 UPDATE，DELETE 或者 SELECT FOR UPDATE，将会进入阻塞状态。如果在其它事务中的 UPDATE，DELETE 或 SELECT FOR UPDATE 语句已经锁定了一条或多条记录，那么 SELECT FOR UPDATE 操作将会等到其他事务完成，然后锁定并返回被更新的记录（如果记录被删除，就不会有记录）。

当所返回的记录不能以表记录的形式明确识别，就不能使用 FOR UPDATE 子句。例如，这个子句不能带着聚合函数操作。

示例

连接表 dept 和表 emp（ emp 和 dept 表格定义见【示例参考表格】）：

SELECT d.deptno, d.dname, e.empno, e.ename, e.mgr, e.hiredate
FROM emp e, dept d
WHERE d.deptno = e.deptno;

 deptno |   dname    | empno | ename  | mgr  |      hiredate       
--------+------------+-------+--------+------+---------------------
     30 | SALES      |  7499 | ALLEN  | 7698 | 1981-02-20 00:00:00
     30 | SALES      |  7521 | WARD   | 7698 | 1981-02-22 00:00:00
     20 | RESEARCH   |  7566 | JONES  | 7839 | 1981-04-02 00:00:00
     30 | SALES      |  7654 | MARTIN | 7698 | 1981-09-28 00:00:00
     30 | SALES      |  7698 | BLAKE  | 7839 | 1981-05-01 00:00:00
     10 | ACCOUNTING |  7782 | CLARK  | 7839 | 1981-06-09 00:00:00
     20 | RESEARCH   |  7788 | SCOTT  | 7566 | 1987-04-19 00:00:00
     10 | ACCOUNTING |  7839 | KING   |      | 1981-11-17 00:00:00
     30 | SALES      |  7844 | TURNER | 7698 | 1981-09-08 00:00:00
     20 | RESEARCH   |  7876 | ADAMS  | 7788 | 1987-05-23 00:00:00
     30 | SALES      |  7900 | JAMES  | 7698 | 1981-12-03 00:00:00
     20 | RESEARCH   |  7902 | FORD   | 7566 | 1981-12-03 00:00:00
     10 | ACCOUNTING |  7934 | MILLER | 7782 | 1982-01-23 00:00:00
     20 | RESEARCH   |  7369 | SMITH  | 7902 | 1980-12-17 00:00:00
(14 rows)

计算出所有雇员的薪水总和，并且按照部门编号对结果进行分组（ emp 表格定义见【示例参考表格】）：

SELECT deptno, SUM(sal) AS total
FROM emp
GROUP BY deptno;

 deptno |  total   
--------+----------------
     10 |  8750.00
     30 |  9400.00
     20 | 11075.00
(3 rows)

下面首先计算出所有雇员的薪水总和，按照部门编号对结果进行分组。并且显示总和小于 10000 的组（ emp 表格定义见【示例参考表格】）：

SELECT deptno, SUM(sal) AS total
FROM emp
GROUP BY deptno
HAVING SUM(sal) < 10000;

 deptno |  total  
--------+---------
     10 | 8750.00
     30 | 9400.00
(2 rows)

下面 2 个示例是以相同的方式，根据第二列（dname）的内容排序查询结果（dept 表格定义见【示例参考表格】）：

SELECT * FROM dept ORDER BY dname;

 deptno |   dname    |   loc    
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     40 | OPERATIONS | BOSTON
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
(4 rows)

SELECT * FROM dept ORDER BY 2;

 deptno |   dname    |   loc    
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     40 | OPERATIONS | BOSTON
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
(4 rows)

分区表

分区有利于管理非常大的表和索引，它使用了一种“分而治之”的逻辑。分区引入了一种分区键（partition key）的概念，分区键用于根据某个区间值（或范围值）、特定值列表或散列函数值执行数据的聚集。下面按某种顺序列出分区的好处。

**提高数据的可用性：**这个特点对任何类型的系统都适用，而不论系统本质上是 OLTP 还是仓库系统。

**由于从数据库中去除了大段，相应地减轻了管理的负担。**在一个 100GB 的表上执行管理操作时（如重组来删除移植到行，或者在“净化”旧信息后回收表左边的“空白”空间），与在各个 10GB 的表分区上执行同样的操作相比，前者负担大得多。

**改善某些查询的性能：**主要在大型仓库环境中有这个好处，通过使用分区，可以消除很大的数据区间，从而不必考虑它们，相应地根本不用访问这些数据。但这在事务性系统中并不适用，因为这种系统本身就只是访问少量的数据。

**可以把修改分布到多个单独的分区上，从而减少大容量 OLTP 系统上的竞争：**如果一个段遭遇激烈的竞争，可以把它分为多个段，这就可以得到一个副作用：能成比例地减少竞争。

创建分区表

说明：本章节介绍如何创建一个 Range 分区表、List 分区表和 Hash 分区表。

示例：

1、创建了一个带有 5 个分区的 Range 分区表 sales_range：

CREATE TABLE sales_range
(
salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
sales_date    DATE
)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2010 VALUES LESS THAN(TO_DATE('01/02/2010','DD/MM/YYYY')),
PARTITION sales_feb2010 VALUES LESS THAN(TO_DATE('01/03/2010','DD/MM/YYYY')),
PARTITION sales_mar2010 VALUES LESS THAN(TO_DATE('01/04/2010','DD/MM/YYYY')),
PARTITION sales_apr2010 VALUES LESS THAN(TO_DATE('01/05/2010','DD/MM/YYYY')),
PARTITION sales_may2010 VALUES LESS THAN(TO_DATE('01/06/2010','DD/MM/YYYY'))
);

2、创建一个 LIST 分区表：

CREATE TABLE CUSTOMER 
( 
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
    FIRST_NAME  VARCHAR2(30) NOT NULL, 
    LAST_NAME   VARCHAR2(30) NOT NULL, 
    PHONE        VARCHAR2(15) NOT NULL, 
    EMAIL        VARCHAR2(80), 
    STATUS       CHAR(1) 
) 
PARTITION BY LIST (CUSTOMER_ID) 
( 
    PARTITION CUS_PART1 VALUES(100000,200000) TABLESPACE custs01, 
    PARTITION CUS_PART2 VALUES(300000) TABLESPACE custs01 
);

3、创建一个 HASH 分区表：

CREATE TABLE tb_part(
  id        number,
  pro_num   varchar2(40), 
  app_type  varchar2(40)
)
PARTITION BY hash(pro_num)
(
 PARTITION part_01,
 PARTITION part_02,
 PARTITION part_03,
 PARTITION part_04,
 PARTITION part_05
);

4、创建带有子分区的分区表，即复合分区，总共支持 7 种类型，分别是 List-List、List-Range、List-Hash、Range-List、Range-Range、Range-Hash、Hash-Hash 。

下面的例子中，创建了一个 List-Range 类型的分区表，即主分区是 List 类型，子分区是 Range 类型。

CREATE TABLE subpar_t2(
    id int,
    procuctType varchar(50),
    dt date
    ) 
    PARTITION BY list(procuctType)
    SUBPARTITION BY range(dt)
       (PARTITION p1 VALUES ('Bus','Car','Trunk')
           (SUBPARTITION subp11 VALUES LESS THAN(to_date('2020-01-01','yyyy-mm-dd')),
            SUBPARTITION subp12 VALUES LESS THAN(to_date('2021-01-01','yyyy-mm-dd')),
            SUBPARTITION subp13 VALUES LESS THAN(to_date('2022-01-01','yyyy-mm-dd'))
           ),
        PARTITION p2 VALUES ('Bycle','Trolley')
           (SUBPARTITION subp21 VALUES LESS THAN(to_date('2020-01-01','yyyy-mm-dd')),
            SUBPARTITION subp22 VALUES LESS THAN(to_date('2021-01-01','yyyy-mm-dd')),
            SUBPARTITION subp23 VALUES LESS THAN(to_date('2022-01-01','yyyy-mm-dd'))
           ),
        PARTITION p3 VALUES ('Ship','Yacht','Boat')
           (SUBPARTITION subp31 VALUES LESS THAN(to_date('2020-01-01','yyyy-mm-dd')),
            SUBPARTITION subp32 VALUES LESS THAN(to_date('2021-01-01','yyyy-mm-dd')),
            SUBPARTITION subp33 VALUES LESS THAN(to_date('2022-01-01','yyyy-mm-dd'))
           )
       );

增加分区

使用 ALTER TABLE… ADD PARTITION 命令增加分区定义。

概要：

ALTER TABLE table_name ADD PARTITION partition_name; ——增加主分区

ALTER TABLE table_name MODIFY PARTITION partition_name ADD SUBPARTITION subpartition_name; ——增加子分区

参数：

table_name

分区表的名称（可选模式限定）。

partition_name

需要增加或者修改的分区名称。

subpartition_name

增加的子分区名称。

增加主分区

ALTER TABLE sales_range ADD PARTITION sales_jun2010 VALUES LESS THAN(TO_DATE('01/07/2010','DD/MM/YYYY'));

增加子分区

--增加主分区的同时增加子分区
ALTER TABLE subpar_t2 ADD PARTITION p4 VALUES('Airplain')
    (SUBPARTITION subp41 VALUES LESS THAN(to_date('2020-01-01','yyyy-mm-dd')),
     SUBPARTITION subp42 VALUES LESS THAN(to_date('2021-01-01','yyyy-mm-dd')),
     SUBPARTITION subp43 VALUES LESS THAN(to_date('2022-01-01','yyyy-mm-dd'))
    );
--只增加某一主分区的子分区
ALTER TABLE subpar_t2 modify PARTITION p4 
 ADD SUBPARTITION subp44 VALUES LESS THAN(to_date('2023-01-01','yyyy-mm-dd'));

删除分区

使用 ALTER TABLE…DROP PARTITION 命令删除分区定义和存储在该分区中的数据。

概要：

ALTER TABLE table_name DROP PARTITION partition_name; ——删除主分区

ALTER TABLE table_name DROP SUBPARTITION partition_name; ——删除子分区

参数：

table_name

分区表的名称（可选模式限定）。

partition_name

需要被删除的分区名称。

示例：

CREATE TABLE subpar_t1(
    id int,
    productID int,
    procuctType varchar(50)
    ) 
    PARTITION BY list(productID)
    SUBPARTITION BY list(procuctType)
       (PARTITION p1 VALUES (1,2,3,4,5)
           (SUBPARTITION subp11 VALUES('Bus','Car','Trunk'),
            SUBPARTITION subp12 VALUES('Bycle','Trolley'),
            SUBPARTITION subp13 VALUES('Ship','Yacht','Boat')
           ),
        PARTITION p2 VALUES (6,7,8,9,10)
           (SUBPARTITION subp21 VALUES('Bus','Car','Trunk'),
            SUBPARTITION subp22 VALUES('Bycle','Trolley'),
            SUBPARTITION subp23 VALUES('Ship','Yacht','Boat')
           ),
        PARTITION p3 VALUES (11,12,13,14,15)
           (SUBPARTITION subp31 VALUES('Bus','Car','Trunk'),
            SUBPARTITION subp32 VALUES('Bycle','Trolley'),
            SUBPARTITION subp33 VALUES('Ship','Yacht','Boat')
           )
       );
--删除主分区
ALTER TABLE subpar_t1 DROP PARTITION p1;
--删除子分区
ALTER TABLE subpar_t1 DROP SUBPARTITION subp34;

清空分区

使用 ALTER TABLE…TRUNCATE 命令从指定的分区中删除数据，保持分区结构不变。必须对表具有 TRUNCATE 权限才能调用 ALTER table…TRUNCATE 分区。

概要：

ALTER TABLE table_name TRUNCATE PARTITION partition_name

ALTER TABLE table_name TRUNCATE SUBPARTITION partition_name

参数：

table_name

分区表的名称（可选模式限定）。

partition_name

需要被清空数据的分区名称。

示例：

CREATE TABLE subpar_t4(
    id int,
    entype varchar(50),
    dt date,
    pert number
    ) 
    PARTITION BY range(dt)
    SUBPARTITION BY list(entype)
       (PARTITION p1 VALUES LESS THAN (to_date('2021-01-01','yyyy-mm-dd'))
           (SUBPARTITION subp11 VALUES ('Solar','Wind','Hydropower'),
            SUBPARTITION subp12 VALUES ('Geothermal','Tidal'),
            SUBPARTITION subp13 VALUES ('Oil','Coal')
           ),
        PARTITION p2 VALUES LESS THAN (to_date('2022-01-01','yyyy-mm-dd'))
           (SUBPARTITION subp21 VALUES ('Solar','Wind','Hydropower'),
            SUBPARTITION subp22 VALUES ('Geothermal','Tidal'),
            SUBPARTITION subp23 VALUES ('Oil','Coal')
           )
       );
INSERT INTO subpar_t4 VALUES (1, to_date('2020-01-01','yyyy-mm-dd'), 'Wind', 0.1);
INSERT INTO subpar_t4 VALUES (2, to_date('2020-02-25','yyyy-mm-dd'), 'Oil', 0.5);
INSERT INTO subpar_t4 VALUES (3, to_date('2020-12-01','yyyy-mm-dd'), 'Geothermal', 0.1);
INSERT INTO subpar_t4 VALUES (4, to_date('2021-01-01','yyyy-mm-dd'), 'Solar', 0.2);
INSERT INTO subpar_t4 VALUES (5, to_date('2021-12-01','yyyy-mm-dd'), 'Geothermal', 0.05);
INSERT INTO subpar_t4 VALUES (6, to_date('2021-04-01','yyyy-mm-dd'), 'Coal', 0.3);
INSERT INTO subpar_t4 VALUES (7, to_date('2021-12-01','yyyy-mm-dd'), 'Oil', 0.3);

--清空主分区中的数据
ALTER TABLE subpar_t4 truncate PARTITION p2;
--清空子分区中的数据
ALTER TABLE subpar_t4 truncate SUBPARTITION subp13;

查看 subpar_t4 表的结构，仍旧包含 p2 分区和 subp13，但是分区数据已经清空。

antdb=# \d+ subpar_t4
                                 Partitioned table "public.subpar_t4"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 dt     | oracle.date           |           |          |         | plain    |              | 
 entype | character varying(50) |           |          |         | extended |              | 
 pert   | numeric               |           |          |         | main     |              | 
Partition key: RANGE (dt)
Partitions: p1 FOR VALUES FROM (MINVALUE) TO ('2021-01-01 00:00:00'), PARTITIONED,
            p2 FOR VALUES FROM ('2021-01-01 00:00:00') TO ('2022-01-01 00:00:00'), PARTITIONED
DISTRIBUTE BY HASH(id) TO NODE(dm1, dm2, dm3, dm4, dm5)
antdb=# \d+ p1
                                      Partitioned table "public.p1"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer               |           |          |         | plain    |              | 
 dt     | oracle.date           |           |          |         | plain    |              | 
 entype | character varying(50) |           |          |         | extended |              | 
 pert   | numeric               |           |          |         | main     |              | 
Partition of: subpar_t4 FOR VALUES FROM (MINVALUE) TO ('2021-01-01 00:00:00')
Partition constraint: ((dt IS NOT NULL) AND ((dt)::timestamp without time zone < '2021-01-01 00:00:00'::oracle.date))
Partition key: LIST (entype)
Partitions: subp11 FOR VALUES IN ('Solar', 'Wind', 'Hydropower'),
            subp12 FOR VALUES IN ('Geothermal', 'Tidal'),
            subp13 FOR VALUES IN ('Oil', 'Coal')
DISTRIBUTE BY HASH(id) TO NODE(dm1, dm2, dm3, dm4, dm5)
antdb=# SELECT * FROM subpar_t4;
 ID |         DT          |   ENTYPE   | PERT 
----+---------------------+------------+------
  1 | 2020-01-01 00:00:00 | Wind       |  0.1
  3 | 2020-12-01 00:00:00 | Geothermal |  0.1
  2 | 2020-02-25 00:00:00 | Oil        |  0.5
(3 rows)

使用分区表

说明：对分区表的增删改查和普通表没有区别，本章节介绍如何对分区表进行修改、查询等操作。

示例：

下面往分区表 sales_range 中插入了 6 条记录，然后删除、修改和查询了各一条记录，最后查询了分区表 sales_range 中的子表 sales_may2010 里面的数据：

-- Insert Record
INSERT INTO subpar_t4 VALUES (8, to_date('2021-11-01','yyyy-mm-dd'), 'Oil', 0.3);
--update
UPDATE subpar_t4 SET entype = 'Tidal' WHERE id = 8;
SELECT * FROM subpar_t4;
--delete
DELETE FROM subpar_t4 WHERE id = 8;
-- Scan Single Partition
SELECT * FROM subpar_t4 PARTITION(p2);

使用限制：

• 分区表的每一个分区都是以表的形式存在，因此不同表的分区名字不能重复。

• 不支持分区的 split 和 merge。

• 不支持子分区不表创建 template。

• 不支持移动 tablespace。