字段约束

约束类型：

UNIQUE (唯一性约束)
NOT NULL (非空约束)
CHECK (自定义约束)
PRIMARY KEY (主键约束)
FOREIGN KEY (外键约束)

外键策略：

默认：存在外键的主键表列数据不允许被删除
ON DELETE CASCADE：级联删除，主键表列数据被删除时，外键表对应列数据也被删除
ON DELETE SET NULL：主键表列数据被删除时，外键表对应列数据会变成null

创建表时同时创建约束

--主键表
CREATE TABLE Tablenamepk(User_Id    NUMBER Primary Key    --主键约束
                        ,User_Name VARCHAR2(20) NOT NULL --非空约束
                        ,Deptno    VARCHAR2(10) UNIQUE   --唯一性约束
                        ,Sal         NUMBER CHECK(Sal BETWEEN 1 AND 100) --自定义约束
                        );
--外键表
CREATE TABLE Tablenamefk(User_Id NUMBER
                        ,CONSTRAINT Tablenamefk_Fk Foreign Key(User_Id) References
                         Tablenamepk(User_Id) ON DELETE Cascade --Tablenamepk(user_id)必须是主键约束或唯一性约束
                         );

单独创建约束(增加主外键约束用Add，其他用Modify )

1
2
3

ALTER TABLE Tablenamepk Modify Deptno NOT NULL;             
ALTER TABLE Tablenamepk Add CONSTRAINT Tablenamepk_Pk Primary Key(User_Id);
ALTER TABLE Tablenamefk Add CONSTRAINT Tablenamefk_Fk Foreign Key(User_Id) References Tablenamepk(User_Id) ON DELETE SET NULL;

失效\启用\ 删除约束

1
2
3

ALTER TABLE Tablenamefk DISABLE CONSTRAINT Tablenamefk_Fk;--失效
ALTER TABLE Tablenamefk ENABLE CONSTRAINT Tablenamefk_Fk; --启用
ALTER TABLE Tablenamefk DROP CONSTRAINT Tablenamefk_Fk;   --删除

临时表

临时表中保存的数据只对当前会话有效，即使提交了事务在别的会话中也看不到数据

会话级-PRESERVE：会话临时表执行commit和rollback操作得到效果和一般表是一样的，但是表中数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据
事务级-DELETE：执行commit和rollback操作时会清除表中数据

1	CREATE Global Temporary TABLE Temptable(Id NUMBER) ON COMMIT PRESERVE/DELETE ROWS;

分区表

优点：

改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度
增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用
维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可
均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能
缺点：已经存在的表没有方法可以直接转化为分区表

范围分区表

每一个分区都必须有一个VALUES LESS THEN子句，分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个的分区中。除了第一个分区，其他分区都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。如果指定MAXVALUE，这个值高于其它分区中的任何分区键的值，同时包括空值。

CREATE TABLE Area_Range(
     Range_Key DATE  --分区字段，根据插入数值自动存入对应分区
    ,User_Id   NUMBER
    ,User_Name VARCHAR2(240)
 ) PARTITION BY RANGE(Range_Key)
   INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) --每个月系统自动生成一个分区,使用之后不能再用MAXVALUE
   --根据年月：NUMTOYMINTERVAL(n ,{'YEAR'|'MONTH'})
   --根据天时分秒：NUMTODSINTERVAL(n ,{'DAY'|'HOUR'|'MINUTE'|'SECOND'})
 (
     PARTITION part20171101 VALUES LESS THAN (TO_DATE('2017-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE tbs01,
     PARTITION part20171201 VALUES LESS THAN (TO_DATE('2017-12-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE tbs02
     --PARTITION partmax VALUES LESS THAN (MAXVALUE)
);

增加分区

--有MAXVALUE分区条件时创建分区
ALTER TABLE Area_Range SPLIT PARTITION partmax AT (TO_DATE('2018-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'))
INTO (PARTITION  part20180101, PARTITION partmax) UPDATE GLOBAL INDEXES;
--没有maxvalue分区条件时创建分区
ALTER TABLE Area_Range ADD PARTITION part20180101 VALUES LESS THAN (TO_DATE('2018-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'));

删除分区

1	ALTER TABLE Area_Range DROP PARTITION part20180101;

列表分区表

某列的值只有确定的几个，基于这样的特点可以采用列表分区

CREATE TABLE List_Range(
     List_Key  VARCHAR2(10)
    ,User_Id   NUMBER
    ,User_Name VARCHAR2(240)
)PARTITION BY LIST(List_Key)
(
      PARTITION part01 VALUES('WIP','INV','PO'),
      PARTITION part02 VALUES('AP','AR')
);

增加分区

1	ALTER TABLE List_Range ADD PARTITION part03 VALUES ('OM');

修改分区列表值

1 2	ALTER TABLE List_Range MODIFY PARTITION part03 ADD VALUES('GL'); ALTER TABLE List_Range MODIFY PARTITION part03 DROP VALUES('GL');

删除分区

1	ALTER TABLE List_Range DROP PARTITION part03;

哈希分区表

当列的值没有合适的条件时，建议使用散列分区，随机的插入到某个区中，不受人为的干预，因为通过在I/O设备上进行散列分区，使得这些分区大小一致

CREATE TABLE Hash_Range(
     Hash_Key  VARCHAR2(10)
    ,User_Id   NUMBER
    ,User_Name VARCHAR2(240)
) PARTITION BY HASH(Hash_Key)
(
     PARTITION part01,
     PARTITION part02
);

增加分区

1	ALTER TABLE Hash_Range ADD PARTITION part03;

删除分区
一次减少一个，不能指定减少partition的名称

1	ALTER TABLE Hash_Range COALESCE PARTITION;

其他操作

查询指定分区

1	SELECT * FROM Area_Range PARTITION(Part20171201);

查询分区信息

1	SELECT * FROM Sys.User_Tab_Partitions t WHERE t.Tablespace_Name = 'APPS' AND t.Table_Name = 'AREA_RANGE';

分区合并

1 2	ALTER TABLE Area_Range MERGE PARTITIONS part20171101,part20171201 INTO PARTITION part20171201 UPDATE INDEXES;

如果分区合并最后没添加update indexes子句的话，可以重建受影响的分区的索引

1	ALTER TABLE Area_Range MODIFY PARTITION part20171201 REBUILD UNUSABLE LOCAL INDEXES;

分区改名

1	ALTER TABLE Hash_Range RENAME PARTITION PART01 TO PART02;

物化视图

物化视图主要是用于分布式数据库，在数据库B中要使用数据库A中a表，先在A数据库建立DBLINK，并在数据库B中创建数据库A中a表的物化视图，提高查询效率
刷新方式：

FAST:快速刷新，利用SNAPSHOT LOG只更新时间段变动部分
COMPLETE:完全刷新
FORCE:自动判断刷新，介于FAST和COMPLETE之间

如果是FAST方式需要在A数据库建立Snapshot日志，否则跳过此步骤

1	CREATE SNAPSHOT Log ON Emp WITH ROWID;

创建物化视图

1
2
3

CREATE SNAPSHOT C##apps_Emp_c REFRESH COMPLETE START
    WITH SYSDATE NEXT SYSDATE + 1 AS
    SELECT * FROM Emp@Tablename_Dbl;

修改物化视图

1 2	ALTER SNAPSHOT C##apps_Emp_c REFRESH COMPLETE START WITH SYSDATE + 1 NEXT SYSDATE + SYSDATE + (1 / (24 * 60));

查看物化视图最后刷新的日期

1	SELECT * FROM All_Snapshot_Refresh_Times;

手动刷新物化视图（F：FAST、C：COMPLETE）

1
2
3

BEGIN
  Dbms_Snapshot.Refresh('C##APPS_EMP_C', 'C');
END;

DBLINK

查询用户是否有创建DBLINK权限

1	SELECT * FROM User_Sys_Privs WHERE Privilege LIKE Upper('%DATABASE LINK%');

使用 sysdba 角色给用户赋权

1	GRANT CREATE PUBLIC DATABASE LINK TO C##APPS;

创建DBLINK
DBLINK分为公有和私有两类，公有dblink对所有人开放，私有dblink只有创建者可以访问。公有DBLINK使用PUBLIC修饰关键字。在CREATE和DROP的时候都需要使用PUBLIC，如：DROP PUBLIC DATABASE LINK 或者DROP DATABASE LINK

--方法一：要求数据库服务器 A上tnsnames.ora中有数据库B的映射 
CREATE DATABASE LINK TABLENAME_DBL CONNECT TO C##APPS IDENTIFIED BY apps USING 'ORCL';
--方法二：直接配置 
CREATE DATABASE LINK tablename_dbl 
　　CONNECT TO C##APPS IDENTIFIED BY apps 
　　USING '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )';

DBLINK表查询

1	SELECT * FROM tablename@tablename_dbl;

查询已建立的DBLINK

1	SELECT * FROM All_Db_Links;

删除DBLINK

1	DROP DATABASE LINK tablename_dbl;

9fcbb32a8d46cf43165003b6346a1968

定时任务

查询定时任务

SELECT Uj.Job         唯一标示
      ,Uj.Log_User    提交用户
      ,Uj.Priv_User   赋予任务权限的用户
      ,Uj.Schema_User 用户模式
      ,Uj.Last_Date   最后运行时间
      ,Uj.This_Date   正在运行时间
      ,Uj.Next_Date   下一次运行时间
      ,Uj.Total_Time  运行总时间
      ,Uj.Broken      是否失效
      ,Uj.Interval    运行时间间隔
      ,Uj.Failures    失败次数
      ,Uj.What        执行脚本
      ,Uj.Nls_Env     环境参数
  FROM Sys.User_Jobs Uj;

设置定时任务

DECLARE
  Jobno NUMBER;
BEGIN
  --创建
  Sys.Dbms_Job.Submit(Job       => Jobno
                     ,What      => 'INSERT INTO TableName VALUES(1);'
                     ,Next_Date => To_Date('2017-01-01 01:01:01', 'YYYY-MM-DD HH24:MI:SS')
                     ,INTERVAL  => 'SYSDATE+(1/(24*60))'); ----每分钟执行一次
  --启动
  Dbms_Job.Run(Jobno);
  --停止
  Dbms_Job.Broken(Jobno, TRUE, SYSDATE);
  --删除
  Dbms_Job.Remove(Jobno);
  --修改执行操作
  Dbms_Job.What(Jobno, 'INSERT INTO tablename VALUES (1);');
  --修改下次执行时间
  Dbms_Job.Next_Date(Jobno, To_Date('2017-01-01 01:01:01', 'YYYY-MM-DD HH24:MI:SS'));
  --修改间隔时间
  Dbms_Job.Interval(Jobno, 'SYSDATE+(1/(24*60))');
  COMMIT;
END;

表链接

(+)

外链接，与JION用法类似，区别在于不能实现多表外链接

SELECT a.Empno
      ,a.Ename
      ,a.Sal
      ,b.*
  FROM Emp      a
      ,Salgrade b
 WHERE a.Sal = b.Hisal(+) --a表没(+)表示主表，没匹配上也要显示出来，b表列置空显示
   AND a.Deptno = 20;

bdda7f7d47673e754de161bc15221b33

JOIN

LEFT JOIN
左外连接（LEFT OUTER JOIN 或 LEFT JOIN ）
ecc345049e6ffdb763a578e3a175329e

SELECT a.Empno
      ,a.Ename
      ,a.Sal
      ,b.*
  FROM Emp a
  LEFT JOIN Salgrade b
    ON 1 = 1
   AND a.Sal = b.Hisal
 WHERE a.Deptno = 20;

5c4dc14361663f7724f6e8c620d6b1d9
RIGHT JOIN
右外连接（RIGHT OUTER JOIN 或 RIGHT JOIN）
a6b8243e64c0effa97c800338b31a540

SELECT a.Empno
      ,a.Ename
      ,a.Sal
      ,b.*
  FROM Emp a
 RIGHT JOIN Salgrade b
    ON 1 = 1
   AND a.Sal = b.Hisal
 WHERE a.Deptno = 20;

9c347275f7c12a118eb05c5c5dcdd3e5
INNER JOIN
内连接，取两表交集（INNER JOIN 或 JOIN）
23e537ab2d2fbcda5aedcc235e1653e7

SELECT a.Empno
      ,a.Ename
      ,a.Sal
      ,b.*
  FROM Emp a
 INNER JOIN Salgrade b
    ON 1 = 1
   AND a.Sal = b.Hisal
 WHERE a.Deptno = 20;

c5b45b17290673275153f33be391a53c
FULL JOIN
全连接,取两表并集（FULL OUTER JOIN 或 FULL JOIN）
25ce88620ce24bce8b9e727c847eb67e

SELECT a.Empno
      ,a.Ename
      ,a.Sal
      ,b.*
  FROM Emp a
  FULL JOIN Salgrade b
    ON 1 = 1
   AND a.Sal = b.Hisal;

d626d862b5f52c4e211aaa1af33bc06b
全连接,取两表无交集
fbdcecb3b2383ad4ae457f56d62d7bc8

SELECT a.Empno
      ,a.Ename
      ,a.Sal
      ,b.*
  FROM Emp a
  FULL JOIN Salgrade b
    ON 1 = 1
   AND a.Sal = b.Hisal
 WHERE a.Sal IS NULL
    OR b.Hisal IS NULL;

70e784d1836ea5032c63f21d7608d2e4
CROSS JOIN
交叉连接(CROSS JOIN)：结果集是个笛卡尔乘积

SELECT e.Empno
      ,e.Ename
      ,e.Sal
      ,s.*
  FROM Emp e
 CROSS JOIN Salgrade s;

UNION

取并集

UNION ：结果集去除重复记录
UNION ALL：结果集保留重复记录

bf527b40daccdb3dd53775f22275d014

SELECT e.Ename
      ,e.Empno
  FROM Emp e
 WHERE e.Sal > 1500
UNION
SELECT e.Ename
      ,e.Empno
  FROM Emp e
 WHERE e.Deptno <> 10;

INTERSECT

取交集
4e78c64e6110c780aba13fe0bb2631b2

SELECT e.Deptno
  FROM Emp e
 WHERE e.Hiredate > To_Date('1982/1/23', 'YYYY-MM-DD HH24:MI:SS')
INTERSECT
SELECT d.Deptno
  FROM Dept d;

MINUS

取差集
962dedfe6f45e1e5425c085acf3fd7d9

1	SELECT d.Deptno FROM Dept d MINUS SELECT e.Deptno FROM Emp e;

触发器

触发器记录表操作日志

CREATE OR REPLACE TRIGGER Tablenamelog_Tri
  AFTER INSERT OR UPDATE OR DELETE ON Tablename
  FOR EACH ROW
DECLARE
  l_Operation_Type VARCHAR2(10);
BEGIN
  IF Inserting THEN
    l_Operation_Type := 'INSERT';
  ELSIF Updating THEN
    l_Operation_Type := 'UPDATE';
  ELSIF Deleting THEN
    l_Operation_Type := 'DELETE';
  END IF;
  INSERT INTO Tablename_Log
  VALUES
    (l_Operation_Type
    ,Nvl(:New.User_Id, :Old.User_Id)
    ,Nvl(:New.User_Name, :Old.User_Name)
    ,SYSDATE
    ,-1
    ,-1
    ,SYSDATE
    ,-1);
END Tablenamelog_Tri;