跳转到内容

Oracle 数据库/表

来自维基教科书，开放世界中的开放书籍

架构

[编辑 | 编辑源代码]

Oracle 架构考虑每个服务器一个数据库，其中我们可以找到多个 表空间，等效于 MySQL 和 MS-SQL 数据库对象，包含表和存储过程。

vignette
小插图

在 Windows Express 版本中，这些数据存储在 C:\oraclexe\app\oracle\oradata\XE 中。

这些变量和关键字对大小写不敏感。

创建表空间

[编辑 | 编辑源代码]

连接后，就可以直接在默认表空间中开始创建一些表。但是之前，我们可以将一些表空间添加到一些定义的文件中

   CREATE TABLESPACE Wikibooks
   DATAFILE 'C:\oraclexe\app\oracle\oradata\XE\Wikibooks.dbf' size 10M reuse
   DEFAULT STORAGE (INITIAL 10K NEXT 50K MINEXTENTS 1 MAXEXTENTS 999) 
   ONLINE;

创建模式

[编辑 | 编辑源代码]

模式是授予一组元素 [1] 的权限，例如表和存储过程。关键字 AUTHORIZATION 指定用户名

CREATE SCHEMA AUTHORIZATION root
  CREATE TABLE TableName1...
  CREATE TABLE TableName2...
;

创建表

[编辑 | 编辑源代码]

示例

CREATE TABLE client1 (last VARCHAR(10), first VARCHAR(10), address VARCHAR(20));

Table created.

在 SQL Developer 中，右键单击表，选择“新建表...”，我们可以以数组的形式生成和执行此创建，该数组在 DDL 选项卡中转换为 PL/SQL

CREATE TABLE client1
( id INT NOT NULL 
, last VARCHAR2(50) 
, first VARCHAR2(50) 
, address VARCHAR2(255) 
, CONSTRAINT client1_PK PRIMARY KEY (ID) ENABLE
) TABLESPACE Wikibooks;

我们也可以通过在 GUI 中选择表空间或在创建语句中使用关键字 TABLESPACE 来设置表表空间。

可用数据类型

[编辑 | 编辑源代码]

可能的列类型有：[2]

  1. 字符
    1. CHAR：2 kB。
    2. VARCHAR：4 kB。
    3. VARCHAR2：4 kB，与 VARCHAR 同义。
    4. NCHAR：2 kB。
    5. NVARCHAR2：4 kB。
  2. 数字
    1. NUMBER.
    2. BINARY_INTEGER.
    3. BINARY_FLOAT.
    4. BINARY_DOUBLE.
  3. 日期
    1. DATE.
    2. TIMESTAMP.
  4. RAW.
  5. LONG RAW.
  6. BLOB.
  7. CLOB.
  8. NCLOB.
  9. ROWID.
  10. UROWID.
  11. BFILE.
  12. XMLType.
  13. UriType.

列出表

[编辑 | 编辑源代码]

以下系统视图可以显示系统表和用户表

SELECT owner, table_name FROM all_tables;

管理表内容

[编辑 | 编辑源代码]

插入行

[编辑 | 编辑源代码]
INSERT INTO client1 (id, last, first, address) VALUES (1, 'Doe', 'Jane', 'UK');

1 line created.

多行

INSERT ALL
 INTO client1 (id, last, first, address) VALUES (2, 'Doe', 'Jack', 'US')
 INTO client1 (id, last, first, address) VALUES (3, 'Doe', 'John', 'US')
 SELECT 1 FROM DUAL;

2 lines created.

DUAL 表 是 Oracle 中默认存在的特殊的一行一列表，因为它用于 SELECT 语句需要 FROM 语句，但一些查询不需要任何表。

更新行

[编辑 | 编辑源代码]
UPDATE client1 SET address = 'US' WHERE id = 1;

删除行

[编辑 | 编辑源代码]
DELETE client1 WHERE ID = 2;

读取表

[编辑 | 编辑源代码]

结构

[编辑 | 编辑源代码]

要获取其结构，我们可以使用函数 desc（描述）或系统视图 ALL_TAB_COLUMNS。

desc

[编辑 | 编辑源代码]
desc client1;

Name    NULL     Type          
------- -------- ------------- 
ID      NOT NULL NUMBER(38)    
LAST             VARCHAR2(10)  
FIRST            VARCHAR2(10)  
ADDRESS          VARCHAR2(20)

如果表不存在，则会发生以下错误：ORA-00923: 缺少预期位置的 FROM 关键字。

ALL_TAB_COLUMNS

[编辑 | 编辑源代码]
 SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = "client1"

OWNER
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
DATA_TYPE
--------------------------------------------------------------------------------
DAT
---
DATA_TYPE_OWNER
--------------------------------------------------------------------------------
DATA_LENGTH DATA_PRECISION DATA_SCALE N  COLUMN_ID DEFAULT_LENGTH
----------- -------------- ---------- - ---------- --------------
DATA_DEFAULT
--------------------------------------------------------------------------------
NUM_DISTINCT
------------
LOW_VALUE
--------------------------------------------------------------------------------
HIGH_VALUE
--------------------------------------------------------------------------------
   DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANA SAMPLE_SIZE
---------- ---------- ----------- -------- -----------
CHARACTER_SET_NAME                           CHAR_COL_DECL_LENGTH GLO USE
-------------------------------------------- -------------------- --- ---
AVG_COL_LEN CHAR_LENGTH C V80 DAT HISTOGRAM       DEF IDE
----------- ----------- - --- --- --------------- --- ---
EVALUATION_EDITION
--------------------------------------------------------------------------------
UNUSABLE_BEFORE
--------------------------------------------------------------------------------
UNUSABLE_BEGINNING
--------------------------------------------------------------------------------

内容

[编辑 | 编辑源代码]

要获取其内容

SELECT * from client1;

LAST        FIRST     ADDRESS
---------- ---------- --------------------
Doe         Jane      UK

连字符的数量表示字段大小。

索引

[编辑 | 编辑源代码]
 本节是一个存根。
您可以通过 扩展它 来帮助维基教科书。

使用 CREATE TABLE 语句创建索引

[编辑 | 编辑源代码]

创建基于函数的索引

[编辑 | 编辑源代码]

约束

[编辑 | 编辑源代码]

本质上，约束保护并验证数据。

主键 (PK) 和唯一约束都确保数据不重复。PK 还确保数据不为空。Oracle 会自动为 PK 和唯一约束生成索引。一张表只能有一个 PK，但可以有多个唯一约束。

外键 (FK) 确保数据存在于它所引用的父表列中。每个父记录可以有多个子记录，但每个子记录只能关联到一个父记录。带有 FK 的列不一定需要索引。

FK 只能引用具有 PK 或唯一约束的列。示例

    create table tblA (colX number, colY char);
    create table tblB (colX number);

    alter table tblB add (constraint colX_FK foreign key (colX) references tblA(colX));
    -- ORA-02270: no matching unique or primary key for this column-list

    alter table tblA add (constraint colX_PK primary key (colX));
    alter table tblB add (constraint colX_FK foreign key (colX) references tblA(colX));
    -- alter table success.

一张表只能有一个主键，但可以有多个唯一键。如果子表需要引用主键以外的列，那么父表上的该列必须具有唯一约束。

    alter table tblA add (constraint colY_PK primary key (colY));
    -- ORA-02260: table can have only one primary key

不能在包含重复数据的列上创建 PK 或唯一键。

    insert into tblA values(1,'A');
    insert into tblA values(2,'A');
    alter table tblA add (constraint colY_UK unique (colY));
    -- ORA-02299: cannot validate (HR.COLY_UK) - duplicate keys found


    delete from tblA where colx = 2;
    alter table tblA add (constraint colY_UK unique (colY));
    -- alter table success.

    create table tblC (colY char);
    alter table tblC add (constraint colY_FK foreign key (colY) references tblA(colY));
    -- alter table success.

向带有 FK 的列中插入数据时，该值必须已经存在于 FK 所引用的列中。

    insert into tblC values ('B');
    -- ORA-02291: integrity constraint (HR.COLY_FK) violated - parent key not found

    insert into tblC values ('A');
    -- 1 rows inserted

只要存在外键，父表就可以截断/删除数据或禁用 PK 或唯一约束。

    truncate table tblA;
    -- ORA-02266: unique/primary keys in table referenced by enabled foreign keys

在 Oracle 中查找约束信息

    desc all_constraints;

    select
      a.owner, a.table_name, a.constraint_name,
      a.constraint_type, a.status, a.r_owner, a.r_constraint_name,
      b.table_name as r_table_name, b.status as r_status
    from all_constraints a
      left join all_constraints b on a.owner = b.owner and a.r_constraint_name = b.constraint_name
    where a.table_name like 'TBL%';

    select *
    from all_cons_columns
    where table_name like 'TBL%';

禁用有外键引用的约束是不允许的，要执行此操作，必须先禁用外键。

    alter table tblA disable constraint colX_PK;
    -- ORA-02297: cannot disable constraint (HR.COLX_PK) - dependencies exist
    alter table tblA disable constraint colY_UK;
    -- ORA-02297: cannot disable constraint (HR.COLY_UK) - dependencies exist

    alter table tblC disable constraint colY_FK;
    alter table tblB disable constraint colX_FK;

    alter table tblA disable constraint colX_PK;
    alter table tblA disable constraint colY_UK;
    truncate table tblA;

如果父表中的数据被删除，则不允许重新启用包含对丢失数据引用的数据的 FK。

    select * from tblC;
    alter table tblA enable constraint colY_UK;
    alter table tblC enable constraint colY_FK;
    -- ORA-02298: cannot validate (HR.COLY_FK) - parent keys not found

生成 SQL 语句以禁用指定表上的所有 FK

    select
      'alter table '||a.owner||'.'||a.table_name||
      ' disable constraint '||a.constraint_name||';' as STMT
    from all_constraints a, all_constraints b
    where a.constraint_type = 'R'
      and a.r_constraint_name = b.constraint_name
      and a.r_owner = b.owner
      and b.table_name = 'TBLA';

修改表结构

[编辑 | 编辑源代码]

重命名的示例

ALTER TABLE client1 RENAME to client2

添加第一个字段值约束

ALTER TABLE client1 CHECK id > 1;

添加主键

ALTER TABLE client1 ADD CONSTRAINT client1_pk PRIMARY KEY (id);

删除主键

ALTER TABLE client1 ADD PRIMARY KEY (id) DISABLE;

添加外键

ALTER TABLE client1
ADD CONSTRAINT fk_client2
  FOREIGN KEY (client2_id)
  REFERENCES client2(id);

删除表

[编辑 | 编辑源代码]
DROP TABLE client1;

分区

[编辑 | 编辑源代码]

Oracle 分区是一个将大型表拆分成多个较小表的流程，以提高性能。

范围

[编辑 | 编辑源代码]

示例

CREATE TABLE t_range 
( t1      VARCHAR2(10) NOT NULL,
  t2      NUMBER       NOT NULL,
  t3      NUMBER  
) 
PARTITION BY RANGE (t2) 
( PARTITION part1 VALUES LESS THAN (1),
  PARTITION part2 VALUES LESS THAN (11),
  PARTITION part3 VALUES LESS THAN (MAXVALUE)
);

哈希

[编辑 | 编辑源代码]

示例

CREATE TABLE t_hash 
( t1      VARCHAR2(10) NOT NULL,
  t2      NUMBER       NOT NULL,
  t3      NUMBER  
PARTITION BY HASH (t2)
PARTITIONS 4
;

列表

[编辑 | 编辑源代码]

示例

CREATE TABLE t_list 
( ort     VARCHAR2(30) NOT NULL,
  t2      NUMBER,
  t3      NUMBER  
)
PARTITION BY LIST(ort) 
( PARTITION part_nord VALUES IN ('Hamburg','Berlin'),
  PARTITION part_sued VALUES IN ('Muenchen', 'Nuernberg'),
  PARTITION part_west VALUES IN ('Koeln','Duesseldorf'),
  PARTITION part_ost VALUES IN ('Halle'),
  PARTITION part_def VALUES (DEFAULT)
);

间隔

[编辑 | 编辑源代码]

示例

CREATE TABLE t_interval 
( buchungs_datum  DATE NOT NULL,
  buchungs_text   VARCHAR2(100),
  betrag          NUMBER(10,2)
)  
PARTITION BY RANGE (buchungs_datum)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p_historie VALUES LESS THAN (TO_DATE('2014.01.01', 'YYYY.MM.DD')),
  PARTITION p_2014_01  VALUES LESS THAN (TO_DATE('2014.02.01', 'YYYY.MM.DD')),
  PARTITION p_2014_02  VALUES LESS THAN (TO_DATE('2014.03.01', 'YYYY.MM.DD'))
);

删除列并设置列为 UNUSED

[编辑 | 编辑源代码]
 本节是一个存根。
您可以通过 扩展它 来帮助维基教科书。

执行 FLASHBACK 操作

[编辑 | 编辑源代码]

创建和使用外部表

[编辑 | 编辑源代码]

参考文献

[编辑 | 编辑源代码]
  1. ↑ https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6013.htm
  2. ↑ https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm
取自 "https://wikibooks.cn/w/index.php?title=Oracle_Database/Tables&oldid=3676974"
华夏公益教科书