Oracle 数据类型，表空间(undo)、用户与权限，DCl、DDL、DML 语句、约束，表与视图注释_oracle ddl dcl

目录

Oracle 数据类型

NUMBER 数字类型

Long 大文本类型

CLOB 超大文本类型

BLOB 二进制对象

NVARCHAR2

Oracle 表空间

undo 表空间

用户创建、删除、查询，修改密码

用户授权、权限查询

一：角色与权限

二：授权语法

三、权限查询

四：为用户设置某个表/视图的 CRUD 权限

五：权限收回

用户锁定与解锁

create 创建表与 as 子查询建表

表与视图设置注释

修改表结构 & 删除表

五大约束：主、外键，唯一，非空，检查

检查约束

外键约束

禁用与删除

插入、更新、删除表数据

新增插入

 更新

truncate 和 delete 删除

---

1、Oracle SQL 语句由如下命令组成：

 2、Oracle 数据库中，默认情况下，所有系统的数据， SQL关键字等都是大写的，在操作过程中，Oracle 会自动把这些内容转换为大写，因此用户操作时不需考虑大小写问题，一般情况下，为了 良好的程序风格，程序中建议关键字用大写，非关键字可以使用小写。

Oracle 数据类型

1、在学习建表、操作表之前，应该先清楚 Oralce 的数据类型。Oracle 数据库的核心是表，表中的列使用到的常见数据类型如下：

NUMBER 数字类型

Long 大文本类型

1、LONG 数据类型中存储的是可变长字符串，最大长度限制是 2GB。

2、对于超出一定长度的文本，基本只能用LONG类型来存储，数据字典中很多对象的定义就是用LONG来存储的。Oracle 的 user_views 中的 text 列就是典型的 Long 类型。

3、LONG类型主要用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。

4、LONG类型操作时有以下限制：

// PL/SQL块中的变量可以定义为LONG类型。
declare
  var_view_sql long;
begin
  SELECT T.TEXT into var_view_sql FROM user_views T where t.VIEW_NAME = upper('VW_GBM_BS_TO_SALARY');
  dbms_output.put_line('视图内容->' || var_view_sql);
end;

4、因为是大文本类型，所以 PLSQL Developer 等工具中无法直接完整展示内容，需要手动点击旁边的'...'(大数据编辑器)，或者手动导出都可以看到完整内容。

5、程序处理上与普通的 varchar2 类型一样，如下所示使用 JdbcTemplate 正常读取。 

CLOB 超大文本类型

1、CLOB（Character Large Object）类型是用于存储大型字符数据的列类型。与普通的VARCHAR2和CHAR类型不同，CLOB类型支持更大的数据存储，最大可以存储4GB的字符数据，并且可以使用存储过程和函数来处理这些数据。

2、CLOB 比 Long 存储的内容更长，且没有 Long 类型那么多的限制。

3、因为是大文本类型，所以 PLSQL Developer 等工具中无法直接完整展示内容，需要手动点击旁边的'...'(大数据编辑器)，或者手动导出都可以看到完整内容。

alter table emp add err_log clob;
update emp t set t.err_log = '天下武功，唯快不破' where empno = 1;
select t.empno,
       t.err_log,
       dbms_lob.getlength(t.err_log),
        -- 表示从第1个字节开始取出10个长度，最长不要超过4000
        -- 如果是在plsql中，则可以直接取32767个长度，全部取出
       dbms_lob.substr(err_log, 10, 1)
  from emp t
 where empno = 1;

4、程序处理上与普通的 varchar2 类型一样，如下所示使用 JdbcTemplate 正常读取。  

BLOB 二进制对象

1、BLOB 存储二进制对象，如图形、视频、声音等。 存取都需要一点特殊处理，否则就会报错：ORA-01465: invalid hex number（无效的十六进制数字）。

-- Create table
create table DEPT_2
(
  deptno NUMBER NOT NULL,
  ico_blob BLOB
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table DEPT_2 add constraint DEPT_PK primary key (DEPTNO);
comment on column DEPT_2.DEPTNO is '部门号(主键)';
comment on column DEPT_2.ico_blob is '图标';

-- blob 数据类型 - 插入/更新方式1 - 使用 utl_raw.cast_to_raw 函数进行转换
-- utl_raw.cast_to_raw 函数可以将输入数据转换为RAW数据类型，而且不会更改输入的数据内容，然后再插入到BLOB列中。
insert into dept_2(deptno, ico_blob) values(1, utl_raw.cast_to_raw(1314159.574857));
UPDATE dept_2 T SET t.ico_blob = utl_raw.cast_to_raw('中华民族') WHERE t.deptno = 1; 
 
-- blob 数据类型 - 插入/更新方式2 - 使用 rawtohex 函数进行转换
insert into dept_2(deptno, ico_blob) values(2, rawtohex('蚩尤后裔ABC123。'));
UPDATE dept_2 T SET t.ico_blob = rawtohex('ABC123蚩尤后裔.') WHERE t.deptno = 2; 

-- blob 数据类型 - 查询 - blob 转成字符串：utl_raw.cast_to_varchar2
-- 注意：内容太长时会报错：
-- ORA-22835: 缓中区对于 CLOB 到 CHAR 转换或 BLOB 到RAW 转换而言太小(实际: 11223,最大: 2000)
SELECT T.*,utl_raw.cast_to_varchar2(t.ico_blob) FROM dept_2 t where utl_raw.cast_to_varchar2(t.ico_blob) like '%后裔%';

NVARCHAR2

1、NVARCHAR2 是可以存储 Unicode 字符的 Unicode 数据类型，NVARCHAR2 的字符集是在数据库创建时指定的国家字符集。

--查找NVARCHAR2的字符集
SELECT * FROM nls_database_parameters WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET';
--查询VARCHAR2数据类型使用的缺省字符集
SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

2、NVARCHAR2 数据类型使用AL16UTF16字符集时，它使用UTF-16编码对Unicode数据进行编码，AL16UTF16使用2个字节来存储一个字符。

3、VARCHAR2 的最大长度可以是字节或字符，而NVARCHAR2的最大大小只能是字符。 另外 NVARCHAR2的最大字节长度取决于配置的国家字符集。

create table DEPT (
    DEPTNO  NUMBER(2) not null constraint DEPT_PK primary key,
    DNAME   VARCHAR2(14),
    LOC     NVARCHAR2(32),
    DELETED NUMBER(1) not null check ( DELETED in (1, 2) )
);
 
comment on table DEPT is '部门信息表';
comment on column DEPT.DEPTNO is '主键';
comment on column DEPT.DNAME is '部门名称';
comment on column DEPT.LOC is '所在位置';
comment on column DEPT.DELETED is '是否删除(1是2否)';
 
--不含生僻字时，增删改查加与不加前缀'N'都没有关系；
--含有生僻字时，增删改查必须加前缀'N'，否则入库后生僻字会变成'？'；
INSERT INTO DEPT (DEPTNO, DNAME, DELETED, LOC) VALUES (2, '研发部', 2, N'䶮长玲市');
update DEPT set DEPT.loc = N'䶮长玲自治区' where DEPTNO=2;
select * from DEPT where DEPT.loc=N'䶮长玲自治区';
delete from DEPT where  DEPT.loc=N'䶮长玲自治区';

Oracle 表空间

Oracle 表空间概述

1、Oracle 可以存放海量数据，所有数据都在数据文件中存储，而数据文件大小受操作系统限制，并且过大的数据文件对数据的存取性能影响非常大。同时 Oracle 是跨平台的数据库, 可以轻松的在不同平台上移植，那么如何才能提供统一存取格式的大容量呢? Oracle 采用表空间来解决。

2、若干操作系统文件(文件可以不是很大)可以组成一个表空间，表空间统一管理空间中的数据文件，一个数据文件只能属于一个表空间。一个数据库空间由若干个表空间组成。

3、Oracle 中所有的数据(包括系统数据)，全部保存在表空间中，常见的表空间有:

Oracle 表空间创建

1、新建 Java 项目后，需要先新建表空间，然后创建用户并分配表空间，接着给用户授权，最后由此用户来建表操作。

2、表空间创建语法：

3、表空间只能由管理员创建，如下所示创建表空间 "dongShan"（datafile 的存储目录必须存在，.dbf 文件会自动创建），默认一个数据文件最大只能到 32G，后续可以再新加数据文件:

create tablespace dongShan
  datafile 'E:\oracle_tablesapce\dongShan.dbf'
  size 500M 
  autoextend on
  next 50M;

4、上面的语句执行之后，在 oralce 服务器的指定位置就会自动生成 dbf 文件，且大小为初始大小500M。

5、数据文件(.dbf文件)无法直接右键删除，因为它被 Oracle 占用了，如果真的不再需要，可以先删除表空间关系：drop tablespace 表空间名，然后再手动删除数据文件。

表空间命令汇总

undo 表空间

1、undo 表空间对 Oracle 来说，它保存着数据的前镜像，即修改之前的内容。它是非常重要的的一个表空间。常用于 rollback，recover，一致性读，Flashback。

2、Oracle 的 Undo 有两种方式：一是使用 undo 表空间，二是使用回滚段。

3、通过 undo_management 参数来控制使用哪种方式，如果设为 auto，就使用 UNDO 表空间，这时必须要指定一个 UNDO 表空间。如果设为 manual，系统启动后使用 rollback segment 方式存储 undo 信息。

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_tablespace                      string      UNDOTBS1

4、当 undo_management 设置成 AUTO 时使用 UNDO tablespace 来管理回滚段。此时将有多个 undo segment，并且这些 segment 是存放在 UNDO 表空间里的，这样对 DB 的性能就会提高。

--查看undo表空间段
SQL> select segment_name, tablespace_name, bytes, next_extent from dba_segments where segment_type = 'TYPE2 UNDO';
 
--查看 undo 表空间里 空闲和非空闲比例
--UNEXPIRED 和 EXPIRED 是已使用的 undo 表空间
--其中 expired 说明是已经 过期的数据，也就是 15 分钟（默认情况）以外的数据，以被覆盖 可以认为是空闲的。
SQL> SELECT tablespace_name, status, SUM(bytes) / 1024 / 1024 "Bytes(M)"  FROM dba_undo_extents GROUP BY tablespace_name, status;

--几个常用的操作
--新建undo表空间
SQL> create undo tablespace undo表空间名称 datafile 'F:/backup/xxx1.dbf' size 500m autoextend on next 50M;
 
--为undo表空间指定undo表空间
SQL> alter system set undo_tablespace = undo表空间名称;
 
--当undo表空间不知足时，增加数据文件
SQL> ALTER TABLESPACE undo表空间名称 ADD DATAFILE 'F:/backup/xxx2.dbf' size 500M reuse;
 
--当undo表空间不知足时，直接切换undo表空间
SQL> alter system set undo_tablespace = undo表空间名称 scope=both;

5、undo_retention 参数用来指定 undo 记录保存的最长时间，以秒为单位(默认900s,即15分钟)，可以在实例运行时随时修改。默认情况下的 undo_retention=900(15分钟) 值通常无法满足系统的需求。一般建议是改成 10800(3小时)，这样给万一的情况，多争取一些时间。undo_retention 值越大，所需要的 undo tablespace 也就越大。 这个需要结合自己的系统来设置这个参数。

SQL> alter system set undo_retention=10800; --3 个小时
System altered

6、undo_retention 只是指定 undo 数据的过期时间，并不是说 undo 中的数据一定会在 undo 表空间中保存 15 分钟，比如说刚一个新事务开始的时候，如果 undo 表空间已经被写满，则新事务的数据会自动覆盖已提交事务的数据，而不管这些数据是否已过期。

7、undo_retention 中指定的时间一过，已经提交事务中的数据就立刻无法访问，它只是失效，只要不被别的事务覆盖，它会仍然存在，并可随时被 flashback 特性引用。如果你的 undo 表空间足够大，而数据库又不是那么繁忙，那么其实 undo_retention 参数的值并不会影响到你，哪怕你设置成 1 ，只要没有事务去覆盖 undo 数据，它就会持续有效。 总之，要注意 undo 表空间的大小，保证其有足够的存储空间。

8、只有在一种情况下，undo 表空间能够确保 undo 中的数据在 undo_retention 指定时间过期前一定有效，就是为 undo 表空间指定 Retention Guarantee，指定之后，oracle 对于 undo 表空间中未过期的 undo 数据不会覆盖。

SQL> Alter tablespace undotbs1 retention guarantee;

9、UNDO 表空间是会被重用的，只有当事务没结束或开了 retention guarantee, 或在undo_retention 时间内不能被重用。在 undo_retention 规定的时间内，数据都是有效的，过期后都会设为无效，状态被改为 Expired，这些回滚段将会被看作 Free Space 。但是只要数据没有被覆盖就可以使用。如果空间已满，新事务的数据会自动 覆盖掉已经提交的事务数据，即使在 undo_retention 的时间内。除非指定 Retention Guarantee 模式，才能保证在 undo_retention 内不被覆盖。

用户创建、删除、查询，修改密码

1、新建 Java Web 项目后，需要先新建表空间，然后创建用户并分配表空间，接着授权给用户，最后由此用户来建表操作。

2、创建用户语法：create user 用户名 identified by 密码 default tablespace 表空间名; 为用户指定了表空间，那么该用户下的所有数据对象(比如表)默认都存在该表空间下。

3、alter user 用户名 identified by "新密码"; --修改用户密码

用户授权、权限查询

一：角色与权限

1、用户创建之后只是普通用户，没有任何权限，无法登陆 oralce，接下来需要为它分配角色/授权。Oracle 中有3个重要的角色：connect、resource、dba（角色拥有对应右边的权限）：

二：授权语法

1、grant 角色1,角色2... | 权限1,权限2... to 用户名1,用户名2... (授权操作必须是 dba 操作)

三、权限查询

1、查询用户所拥有的权限是一个很有用的功能：

四：为用户设置某个表/视图的 CRUD 权限

1、非 dba 角色的账户默认只能查看自己建立的表、视图等信息，无法查看以及操作其它用户下的表和视图；而拥有 dba 角色的账户则可以 增删改查 自己以及其它账户（包括其它 dba角色的账户）下的表和视图等信息。

2、项目中遇到过这种情况，可以为用户开通自己数据库账户下的一个视图、一张表的权限（即使它不是 dba 角色），或者使用同义词-Synonym。

3、授权语法：grant select [,update,delete,insert,alter] on 表名或者视图名 to 用户名1,用户名2...;  

4、如为 scott 用户授权当前登陆用户 gbm_bs_ageninfo 表的查询与更新权限: grant select,update on gbm_bs_ageninfo to scott;

五：权限收回

1、收回语法与授权语法一致，只是将 grant 改为 revoke，to 改为 from：

revoke 角色1,角色2...|权限1,权限2 from 用户名；

revoke select [,update,delete,insert,alter] on 表名或者视图名 from 用户名;  

用户锁定与解锁

create 创建表与 as 子查询建表

1、标准建表语法：create table 表名( 列名 数据类型 [约束],列名 数据类型 [约束],...) [TABLESPACE 表空间];

2、as 子查询建表语法：create table 表名 as select 查询语句。

3、Oracle 12C 以前的版本表名、列名、约束名、索引名称、视图名、视图列名等等的长度不允许大于30个字符。

4、表名以字母开头，必须只能包含 A-Z,a-z，0-9，，$，和 #，必须不能和用户定义的其他对象重名，不能是Oracle 的保留字，Oracle默认存储是都存为大写。

--创建学生表
CREATE TABLE STUDENT ( 
  STUID VARCHAR2(7) NOT NULL, --学号 学号=‘S’+班号+2位序号 
  STUNAME VARCHAR2(10) NOT NULL, --姓名 
	GENDER VARCHAR2(2) NOT NULL, --性别 
	AGE NUMBER(2) NOT NULL, --年龄 
	SEAT NUMBER(2) default 0 NOT NULL , --座号，注意：默认值必须在 not null 前面
	ENROLLDATE DATE default sysdate, --入学时间 
	STUADDRESS VARCHAR2(50) DEFAULT '地址不详', --住址 
	CLASSNO VARCHAR2(4) NOT NULL --班号 班号=学期序号+班级序号 
);
comment on table STUDENT  is '学生信息表'; 
comment on column STUDENT.STUID is '主键';  

3、插入时明确指定了的列，默认值不会再生效。假设为xxx表的 c3、c4 列设置了默认值，如下所示，c3 的默认值不生效，c4 的默认值会生效。

insert into xxx (c1, c2, c3) values (v1, v2, null);

create as 子查询建表

1、如果对于一张以及存在的表，现在想要复制它，则可以使用子查询复制表来实现。

2、语法：create table 表名 as select 查询语句：

3、如果子查询有结果，则复制表结构的同时也会复制表中的数据，如果子查询没有结果，则只复制表结构。子查询建的表只复制了目标表的结构（如字段名称、数据类型），却不复制约束、索引（如主外键、约束、索引），也不会复制表以及字段的注释。

4、如果视图中的某个字段长度为 0，则无法建表：ORA-01723：不允许长度为 0 的列。

表与视图设置注释

Oracle 12C 以前的版本表名、列名、约束名、索引名称、视图名、视图列名等等的长度不允许大于30个字符。

1、假如接手一个新项目，想查看一下所有表及其字段的注释信息，以便了解一下每张表大概的用途，或者想根据某个注释名称来检索整个用户下所有表的字段信息。

2、查询列的注释信息，可以操作 Oracle 内置的 user_col_comments 视图，可以查询当前登陆用户下的所有表和视图的列的注释，如果没有添加注释信息，则值为 null，但是列同样会展示。

 1、为表及其字段添加注释：

2、为视图以及列设置注释：

修改表结构 & 删除表

Oracle 12C 以前的版本表名、列名、约束名、索引名称、视图名、视图列名等等的长度不允许大于30个字符。

select * from person;--查询 person 表
 
alter table person add phone varchar2(11);--添加 phone 列
alter table person add (     --同时添加多列
      fatherName varchar2(16)  not null,
      fatherPhone varchar2(11) 
);
 
--为 saf_fund_account 表添加 fund_name 列，不允许为 null
alter table saf_fund_account add fund_name varchar2(60) not null;
-- 修改表 student 的列 age 的数据类型为 number(2)，默认值为 0，不允许为null
alter table student modify (age number(2) default 0 not null);
--修改字段允许为null
alter table dept modify loc null;
--修改字段不允许为null
alter table dept modify loc not null;
 
-- 为 saf_fund_account 表的 fund_name 列设置描述
comment on column saf_fund_account.fund_name is '专户账户名';
 
alter table person drop column fatherName;--删除列
alter table person drop column fatherPhone;--删除列
 
alter table person modify fatherName varchar2(12);--修改列的数据类型
alter table gbm_bs_affix modify AFFIX_TITLE VARCHAR2(200);--修改列数据类型长度
alter table person rename column gender to sex;--修改列名
 
rename person to person_1;--修改表名
rename person_1 to person;--修改表名
 
drop table person_1;--删除表

3、Oracle 中删除表时，没有像 Mysql 一样的 drop table if exists 语法，如果想要实现同样的功能，需要借助存储过程来实现：

--表名作为参数，如果表已经存在，则删除它。
create or replace procedure pro_drop_table_by_name(tableName in user_tables.TABLE_NAME%type)
is
  flag number := 0; --表是否存在的表示，大于0表示表已经存在
begin
    --user_tables 是系统定义的视图，可以查看当前用户下的所有表信息，表中的表名区分大小写，而且是大写
    select count(1) into flag from user_tables where table_name = upper(tableName) ;
    if flag > 0 then
        execute immediate 'drop table '|| tableName ;--如果表已经存在，则删除它
    end if;
end;
 
-- 数据库中调用存储过程：call pro_drop_table_by_name('student');

同理 Oracle 建表也没有像 Mysql 一样的 create table if not exists 语法，也需要借助存储过来实现。

4、删除表：数据和结构都被删除、所有正在运行的相关事物被提交、所有相关索引被删除、DROP TABLE 语句不能回滚，但是可以闪回。

五大约束：主、外键，唯一，非空，检查

1、表的约束目的是为了保持数据的完整性，约束表中数据的规则。主要有：主键约束（PRIMARY KEY）、非空约束（NOT NULL ）、唯一约束（UNIQUE）、检查约束（CHECK）、外键约束（FOREIGN KEY ）。

2、主键约束：主键（primary key）不能为空，且必须唯一。

-- 查看当前用户创建的全部约束信息：如约束名称、约束类型、所属的表、最后修改时间、约束名称是用户自定义还是系统自动新建等等
select * from USER_constraints b order by b.TABLE_NAME;
-- 查看全部用户创建的全部约束信息：如约束名称、约束类型、所属的表、最后修改时间、约束名称是用户自定义还是系统自动新建等等
select * from dba_constraints b ;
-- constraint type：约束类型：常见的值为：C(普通约束)、R(外键约束)、P(主键约束)、U(唯一约束)

建表的同时设置约束

--建表的同时创建约束
create table student_1(
  stuid number(32) primary key,--主键
  stuname varchar2(16) unique,--唯一约束
  age varchar2(4) default 0 not null,--默认值 与 非空约束，默认值必须放在前面
  gender varchar2(8) check (gender in ('男','女','保密'))
);
insert into student_1 values(1,'张三',22,'男');--插入数据，必须符合约束
insert into student_1 values(2,'李四',22,'女');--插入数据，必须符合约束
insert into student_1(stuid,stuname,gender) values(3,'王五','保密');--插入数据，必须符合约束
select * from student_1;--查询

建表后设置约束

--创建学生表
CREATE TABLE STUDENT ( 
STUID VARCHAR2(7) NOT NULL, --学号 学号=‘S’+班号+2位序号 
STUNAME VARCHAR2(10) NOT NULL, --姓名 
GENDER VARCHAR2(2) NOT NULL, --性别 
AGE NUMBER(2) NOT NULL, --年龄 
SEAT NUMBER(2) NOT NULL, --座号 
ENROLLDATE DATE, --入学时间 
STUADDRESS VARCHAR2(50) DEFAULT '地址不详', --住址 
CLASSNO VARCHAR2(4) NOT NULL --班号 班号=学期序号+班级序号 
);
 
--添加主键约束。constraint 关键字英文意思为约束、限制，PK_INFOS 为约束名称（自定义），primary key 约束类型
ALTER TABLE STUDENT ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID);
ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_GENDER CHECK(GENDER = '男' OR GENDER = '女');--添加检查约束，性别只能是男或者女
ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_SEAT CHECK(SEAT >=0 AND SEAT <=50);--添加检查约束，坐位号只能在 [0,50] 之间
ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_AGE CHECK(AGE >=0 AND AGE<=100);--与上同理
 
-添加唯一约束，姓名不能重复
ALTER TABLE STUDENT ADD CONSTRAINTS UN_STUNAME UNIQUE(STUNAME);-
 
--添加 GBM_BS_QTRY 唯一约束：ID_CARD,AGENCY_ID，END_DATETIME
ALTER TABLE GBM_BS_QTRY ADD CONSTRAINTS UK_ID_CART_AGENCY_ID_QTRY UNIQUE(ID_CARD,AGENCY_ID,END_DATETIME);
 
--为 saf_income_src 表的 SRC_CODE 列添加非空约束
--因为 not null 约束类型必须声明在列上,无法声明在表上,所以必须用 modify 方式来写 
alter table saf_income_src modify SRC_CODE not null;
 
--删除 saf_income_src 表的 SRC_CODE 列的非空约束
alter table saf_income_src modify SRC_CODE null; 

检查约束

--添加检查约束，限制班号范围
ALTER TABLE STUDENT ADD CONSTRAINT CK_INFOS_CLASSNO CHECK((CLASSNO >='1001' AND CLASSNO<='1999') OR (CLASSNO >='2001' AND CLASSNO<='2999'))
--添加检查约束
ALTER TABLE bas_non_tax ADD CONSTRAINT 非税名称与收入分类科目名称不允许相同 CHECK(non_tax_name != income_sort_name);
ALTER TABLE bas_non_tax ADD CONSTRAINT 非税名称与收入分类科目名称必须相同 CHECK(non_tax_name = income_sort_name);
ALTER TABLE BAS_PERSON_INFO ADD CONSTRAINT 版本号前4位必须与年度相同 CHECK(FISCAL_YEAR = SUBSTR(VERSION, 1, 4));
 
ALTER TABLE GFM_BB_CHECK_INFO ADD CONSTRAINT "检查SQL与规则描述同时为空或者同时有值" CHECK(
(check_sql is not null and remark is not null ) or (check_sql is null and remark is null ) 
);
 
ALTER TABLE BAS_PERSON_INFO ADD CONSTRAINT "身份证正确长度为7/8/11/15/18位" CHECK(
(IDEN_TYPE_CODE = '01'  AND LENGTH(IDEN_NO) IN (7, 8, 11, 15, 18)) or
 (IDEN_TYPE_CODE != '01' and 1=1 )
);

外键约束

1、外键约束被引用的另一张表的列必须是主键 或 unique 约束。

• ON DELETE CASCADE(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除
• ON DELETE SET NULL(级联置空): 子表中相应的列置空

--创建商品类型表。商品类型与商品 1 对多 关系，商品依赖类型表，类型表为主表，商品为从表
create table productType(
pid number(32) primary key,
pname varchar2(16) not null
);
--创建商品表。建表的同时设置外键
create table product(
pid number(32) primary key,
pname varchar2(16) not null,
price number(8,2) not null,--价格
ptId number(32) ,--外键
foreign key(ptId) references productType(pid)--设置外键，关联类型表的主键
);
 
--如果建表的时候没有设置外键，则修改表，为其添加外键。默认情况下，当主表记录被从表依赖时，主表记录无法删除，除非先删除依赖项
alter table product add constraints fk_ptId foreign key(ptId) references productType(pid);
--添加外键的同时设置级联删除。即主表删除记录时，会自动搜索，如果有其它记录依赖自己，则先删除依赖项，然后删除自己。
alter table product add constraints fk_ptId foreign key(ptId) references productType(pid) on delete cascade;

--插入数据
insert into productType values(1,'IT');
insert into productType values(2,'书籍');
insert into productType values(3,'美食');
select * from productType;
 
insert into product values(1,'鹿鼎记',35.55,2);
insert into product values(2,'蚂蚁上树',66.55,3);
select * from product;
 
delete from productType where pid =1;--正常删除，因为 pid =1 没有被任何记录依赖
--如果依赖自己的外键没有设置级联删除，则这里无法删除自己，报错：违反完整约束条件，已找到子记录。除非先删除所有依赖的子记录，否则只能强制删除（不建议）
--如果依赖自己的外键设置了级联删除，则会一并删除
delete from productType where pid =2 or pid = 3;

禁用与删除

如果想修改一个约束类型，只能先删除约束再添加新的约束，如果禁用约束后加了一些非法数据，再开启约束是会失败的。

插入、更新、删除表数据

新增插入

1、插入时明确指定了的列，默认值不会再生效。假设为xxx表的 c3、c4 列设置了默认值，如下所示，c3 的默认值不生效，c4 的默认值会生效。

insert into xxx (c1, c2, c3) values (v1, v2, null);

--当前使用的是拥有管理员权限的用户 wmx 登录，管理员可以查询/操作任何用户的数据，包括其它管理员
select * from dept;
select * from system.dept;--查询系统用户 system 的部门表
 
insert into dept select * from system.dept;--子查询插入。将系统用户的部门表数据全部插入到 wmx 用户的部门表中
insert into dept(deptno,dname) values(50,'财务部');--为部分列添加数据
insert into dept values(60,'开发部','深圳');--为所有列添加值
 
--往员工表插入数据，对于日期使用 to_date 函数进行专户，Java 代码中也是同理
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(8001,'李四','主管',7782,to_date('2019/06/25 15:42:00','YYYY-MM-DD HH24:MI:SS'),9800.80,100,20);
 
--复制部门数据并重新插入，主键 deptno 再原基础上减1
insert into dept (deptno, loc, dname) select t2.deptno - 1, t2.loc, t2.dname from dept t2 where t2.deptno <= 30;

可以配合子查询新增插入。

 更新

-- 单表更新 格式：update 表名 set 列名1=value,列名2=value,... [where 条件];
update dept set dname='测试部',loc='长沙' where deptno = 50;--如果不加 where 条件，则整个表的 dname 都会被更新
update dept set dname='人事部' where deptno = 60;

多表更新 格式：UPDATE table1 SET (col11[,col12...]) = (SELECT col21,[,col22 ...] FROM table12 [WHERE 条件]) where 条件;

a：如果右侧的子查询没有查询到值，则左侧列的值默认会赋值为 null，此时使用 nvl 函数让它默认等于0
b：如果右侧的子查询结果出现多条，则报错：单行子查询返回多个行，此时使用 rownum 只取第一个结果

-- 将工资(sal)最低员工的奖金(comm)设置和奖金最高的员工相同
update emp t1
   set comm = nvl((select comm
                    from emp t2
                   where t2.comm = (SELECT max(T3.comm) FROM emp t3)
                     and rownum <= 1),
                  0)
 where t1.sal = (SELECT min(T4.Sal) FROM emp t4);
 
-- 将工资(sal)最低员工的奖金(comm)、以及工资设置和奖金最高的员工相同
update emp t1 set（sal, comm) = (select sal, comm
                                  from emp t2
                                 where t2.comm =
                                       (SELECT max(T3.comm) FROM emp t3)
                                   and 1 = 2
                                   and rownum <= 1)
 where t1.sal = (SELECT min(T4.Sal) FROM emp t4);

可以配合子查询更新。。

truncate 和 delete 删除

--delete 删除数据：delete from 表名 [where 条件];  delete 是逐条删除，支持事务操作
--truncate 删除删除：truncate 先删除整个表，然后再创建表结构，不支持事务操作，如果是删除全部数据，效率会高些
--delete 删除的数据可以恢复，truncate 删除的数据不可恢复，必须慎重操作.
 
delete from dept where deptno = 60 or deptno = 50;
truncate table  dept;--删除表中所有数据

truncate 和 delete 删除数据的区别

1、truncate 是 ddl 命令，删除的数据不能恢复、delete 命令是 dml 命令，删除后的数据可以通过日志文件恢复。

2、如果一个表中数据记录很多，truncate 相对 delete 速度快。

3、由于 truncate 命令比较危险，因此在实际开发中，truncate 命令慎用。