Oracle表设计与使用

1、设计表

Oracle 表名命名规则

• 必须以字母开头
• 长度不能超过 30 个字符
• 避免使用 Oracle 的关键字
• 只能使用 A-Z、a-z、0-9、_#$

请勿使用带有特殊符号的表名

Oracle 在创建表时，表名会自动转换大写。Oracle 对表名大小写不敏感。

如果在定义表名时含有特殊符号，或者用小写字母来定义表名则需要在表名两侧添加双

引号。

1.1. 确定表名

        表名 tb_user
        表名 tb_txt

1.2 确定字段名和类型

字符串类型
Char
char数据类型存储固定长度的字符值。一个CHAR数据类型可以包括1到2000个字符。如果对CHAR没有明确地说明长度， 
它的默认长度则设置为1。 如果对某个CHAR类型变量赋值，其长度小于规定的长度，那么Oracle自动用空格填充。

注意：如果给了一个比固定长度更长的值，其尾部带有空格，则空格会被删除到固定的长度。如果值太大Oracle会返回一个错误

Varchar
用于保存变长的字符串数据。其中最大字节长度由(size)指定。每行长度可变，最大长度为每行4000字节。 
设置长度(size)前需考虑字符集为单字节或多字节。 
注意：varchar是被废弃的类型，oracle目前都推荐使用varchar2。虽然varchar也可以用，但不确保未来的版本是否还支持

Varchar2
用于保存变长的字符串数据。其中最大字节长度由(size)指定。每行长度可变，最大长度为每行4000字节。 
设置长度(size)前需考虑字符集为单字节或多字节。由于VARCHAR2数据类型只存储为该列所赋的字符（不加空格），所以VARCHAR2需要的存储空间比CHAR数据类型要小。

区别：varchar2把所有字符都占两字节处理(一般情况下)，varchar只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节；
VARCHAR2把空串等同于null处理，而varchar仍按照空串处理；大部分情况下建议使用varchar2类型，可以保证更好的兼容性。

数字类型
Number
NUMBER数据类型精度可以高达38位，它有两个限定符，如：column NUMBER(precision,scale)。precision表示数字中的有效位。 
如果没有指定precision的话，Oracle将使用38 作为精度。scale表示小数点右边的位数，scale默认设置为0。 
如果把scale设成负数，Oracle将把该数字取舍到小数点左边的指定位数。

Decimal
Oracle只是在语法上支持decimal类型，但是在底层实际上它就是number类型，支持decimal类型是为了能把数据从Oracle数据库移到其他数据库中。

Float
FLOAT类型也是NUMBER的子类型。其格式Float(n),数 n 指示位的精度，可以存储的值的数目。N 值的范围可以从 1 到 126。 
若要从二进制转换为十进制的精度，请将n 乘以 0.30103。要从十进制转换为二进制的精度，请用 3.32193 乘小数精度。 
126 位二进制精度的最大值是大约相当于 38 位小数精度。

BINARY_FLOAT
BINARY_FLOAT 是一种 32 位，单精度浮点数字数据类型。每个 BINARY_FLOAT 值需要5 字节存储空间，其中 1 字节用于存储数据值的长度。支持 NUMBER 数据类型所提供的基本功能。但采用二进制精度，而NUMBER 采用十进制精度。因此能够提供更快的数学运算速度，且能减少占用的存储空间。

BINARY_DOUBLE
BINARY_DOUBLE 是一种 64 位，双精度浮点数字数据类型。每个 BINARY_DOUBLE 值需要9 字节存储空间， 
其中 1 字节用于存储数据值的长度。支持 NUMBER 数据类型所提供的基本功能。但采用二进制精度，而NUMBER 采用十进制精度。 

日期数据类型
Date
ORACLE最常用的日期类型，它可以保存日期和时间，常用日期处理都可以采用这种类型。 
DATE表示的日期范围可以是公元前4712年1月1日至公元9999年12月31日。 
date类型在数据库中的存储固定为7个字节， 第1字节：世纪+100、 第2字节：年、第3字节：月、第4字节：天、 
第5字节：小时+1、第6字节：分+1、第7字节：秒+1。

Timestamp
ORACLE常用的日期类型，它与date的区别是不仅可以保存日期和时间，还能保存小数秒，小数位数可以指定为0-9，默认为6位，所以最高精度 可以到ns(纳秒)，数据库内部用7或者11个字节存储，如果精度为0，则用7字节存储，与date类型功能相同，如果精度大于0则用11字节存储。第1字节：世纪+100、第2字节：年、第3字节：月、第4字节：天、 第5字节：小时+1、第6字节：分+1、第7字节：秒+1、 第8-11字节：纳秒，采用4个字节存储，内部运算类型为整形

注：TIMESTAMP日期类型如果与数值进行加减运算会自动转换为DATE型，也就是说小数秒会自动去除。

TIMESTAMP WITH TIME ZONE
对TIMESTAMP进行了扩展，用于存储时区。时间戳以及时区位移值，其中fractional_seconds_precision是数字在第二日期时间字段的 
小数部分数字的所有值。可接受的值是0到9。默认是6。默认格式是确定明确的NLS_DATE_FORMAT参数或隐式的NLS_TERRITORY参数。 
大小固定为13字节。此数据类型包含日期时间字段YEAR，MONTH，日，小时，分钟，秒TIMEZONE_HOUR和TIMEZONE_MINUTE。 
它有一个明确的分数秒和时区。

TIMESTAMP WITH LOCAL TIME ZONE
所有的TIMESTAMP WITH TIME ZONE值，但下列情况除外：数据标准化数据库的时区时，存储在数据库中。当数据被检索， 
用户可以看到在会话时区中的数据。默认格式是确定明确的NLS_DATE_FORMAT参数或隐式的NLS_TERRITORY参数。 
的大小不同的7至11个字节，取决于精度。

INTERVAL YEAR TO MONTH
存储期间年数和月的时间，其中year_precision是数字的年份日期时间字段的数量。可接受的值是0到9。默认是2。大小固定为5个字节。

INTERVAL DAY TO SECOND
其格式为：INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)]，是存储一段时间以天，小时，分钟和秒， 
其中day_precision是数字在DAY日期时间字段的最大数量。可接受的值是0到9。默认是2。fractional_seconds_precision是数字中的 
第二个字段的小数部分的数量。可接受的值是0到9。默认是6。大小固定为11个字节。

INTERVAL YEAR TO MONTH
Oracle语法：INTERVAL YEAR [（year_precision ）] TO MONTH，用来表示一段时间差, 
只精确到年和月. year_precision是数字年的时间段，接受的值为0到9。默认值是2。大小固定为5个字节。

大型对象（LOB）数据类型
Clob
最大4G，存储单字节字符型数据。适用于存储超长文本。

Nclob
最大4G，存储多字节国家字符型数据。适用于存储超长文本。

Blob
最大4G，存储二进制数据。适用于存储图像、视频、音频等。

BFile
最大长度是4GB，在数据库外部保存的大型二进制对象文件，最大长度是4GB。这种外部的LOB类型，通过数据库记录变化情况，但是数据的具体保存是在数据库外部进行的。 Oracle 可以读取、查询BFILE，但是不能写入，不参与事务。

1.3. 确定字段约束

        1.3.1 约束的作用

        约束用于规定表中的数据规则，如果存在违反约束的数据行为，行为会被约束终止。

        1.3.2 约束类型

                主键约束（Primay Key Constraint ）：唯一标识一条记录（唯一并且非空）

                唯一约束 （Unique Constraint ）：唯一性，可以空，但只能有一个。

                检查约束 （Check Constraint ）： 对该列数据的范围、格式的限制（如：年龄、性别等）。

                创建表test_tab3，给字段gerder（性别）添加检查约束，规定此列的值只能是 man 或者                 women。结果如下。

create table test_tab3(
    id number not null primary key,
    gender varchar2(5) not null check(gender in ('man','women')),
    name varchar2(20) 
)
;
insert into test_tab3 values (1,'man','ada');
insert into test_tab3 values (1,'02','ada');
 

                非空约束 （Not Null Constraint ）：该列不允许包含空值。

                外键约束 （Foreign Key Constraint ）： 需要建立两表间的关系并引用主表的列。

2、创建表

        

3、索引

        待补充

4、函数

4.1、数值型函数

4.2、字符型函数

4.3、日期函数

4.4、转换函数

4.5、聚合函数

4.6、分析函数

4.7、其它函数

5、与mysql的差异

5.1、本质的区别

MySQL是一个开源的关系数据库管理系统（RDBMS）。它是世界上使用最多的RDBMS，作为服务器运行，提供对多个数据库的多用户访问。它是一个开源、免费的数据库
Oracle数据库是一个对象关系数据库管理系统（ORDBMS）。它通常被称为Oracle RDBMS或简称为Oracle，是一个收费的数据库。

5.2、数据库安全性

MySQL使用三个参数来验证用户，即用户名，密码和位置
Oracle使用了许多安全功能，如用户名，密码，配置文件，本地身份验证，外部身份验证，高级安全增强功能等

5.3、SQL语法的区别

Oracle的SQL语法与MySQL有很大不同。Oracle为称为PL / SQL的编程语言提供了更大的灵活性。Oracle的SQL * Plus工具提供了比MySQL更多的命令，用于生成报表输出和变量定义

5.4、对事务的提交

MySQL默认是自动提交，
Oracle默认不自动提交，需要用户手动提交，需要在写commit;指令或者点击commit按钮

5.5、存储上的区别

与Oracle相比，MySQL没有表空间，角色管理，快照，同义词和包以及自动存储管理。

5.6、分页查询

MySQL是直接在SQL语句中写"select… from …where…limit x, y",有limit就可以实现分页
Oracle则是需要用到伪列ROWNUM和嵌套查询

5.7、MySQL和Oracle的字符数据类型比较

两个数据库中支持的字符类型存在一些差异。对于字符类型，MySQL具有CHAR和VARCHAR，最大长度允许为65,535字节（CHAR最多可以为255字节，VARCHAR为65.535字节）
Oracle支持四种字符类型，即CHAR，NCHAR，VARCHAR2和NVARCHAR2; 所有四种字符类型都需要至少1个字节长; CHAR和NCHAR最大可以是2000个字节，NVARCHAR2和VARCHAR2的最大限制是4000个字节。可能会在最新版本中进行扩展

5.8、事务隔离级别

MySQL是repeatable read的隔离级别
Oracle是read commited的隔离级别，同时二者都支持serializable串行化事务隔离级别，可以实现最高级别的读一致性。每个session提交后其他session才能看到提交的更改。Oracle通过在undo表空间中构造多版本数据块来实现读一致性，每个session查询时，如果对应的数据块发生变化，Oracle会在undo表空间中为这个session构造它查询时的旧的数据块MySQL没有类似Oracle的构造多版本数据块的机制，只支持repeatable read的隔离级别。一个session读取数据时，其他session不能更改数据，但可以在表最后插入数据。session更新数据时，要加上排它锁，其他session无法访问数据

5.9、MySQL和Oracle中的备份类型

MySQL有mysqldump和mysqlhotcopy备份工具。在MySQL中没有像RMAN这样的实用程序。
Oracle提供不同类型的备份工具，如冷备份，热备份，导出，导入，数据泵。Oracle提供了最流行的称为Recovery Manager（RMAN）的备份实用程序。使用RMAN，我们可以使用极少的命令或存储脚本自动化我们的备份调度和恢复数据库。

5.10、字符串的模糊比较

MySQL里用 字段名 like ‘%字符串%’

Oracle里也可以用 字段名 like ‘%字符串%’ 但这种方法不能使用索引, 速度不快，用字符串比较函数 instr(字段名,‘字符串’)>0 会得到更精确的查找结果

5.11、并发性

MySQL以表级锁为主，对资源锁定的粒度很大，如果一个session对一个表加锁时间过长，会让其他session无法更新此表中的数据。虽然InnoDB引擎的表可以用行级锁，但这个行级锁的机制依赖于表的索引，如果表没有索引，或者sql语句没有使用索引，那么仍然使用表级锁

Oracle使用行级锁，对资源锁定的粒度要小很多，只是锁定sql需要的资源，并且加锁是在数据库中的数据行上，不依赖与索引。所以Oracle对并发性的支持要好很多

5.12、性能诊断

MySQL的诊断调优方法较少，主要有慢查询日志。

Oracle有各种成熟的性能诊断调优工具，能实现很多自动分析、诊断功能。比如awr、addm、sqltrace、tkproof等

5.13、日期字段的处理转换

MySQL中有2种日期格式DATE和TIME

Oracle只有一种日期格式DATE。

5.14、 空字符的处理

MySQL的非空字段也有空的内容
Oracle里定义了非空字段就不容许有空的内容。按MySQL的NOT NULL来定义Oracle表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串

6、触发器

        6.1、什么是触发器

触发器：trigger，是一种特殊的数据库对象，它可以在特定的事件发生时自动执行一些操作，可以用于实现复杂的数据约束、数据验证、数据审计等功能。

6.2触发器的类型

Oracle中的触发器类型有如下四种：

6.2.1DML触发器：

对表或视图执行DML操作时触发。其中包括：

6.2.1.1. 行级触发器（Row-Level Triggers）：

当对表中的一行数据进行INSERT、UPDATE或DELETE操作时，会触发行级触发器执行。行级触发器可以在每行数据发生变化时，对该行数据进行操作。

6.2.1.2. 语句级触发器（Statement-Level Triggers）：

当对表进行INSERT、UPDATE或DELETE操作时，会触发语句级触发器执行。语句级触发器可以在整个语句执行之前或之后对数据进行操作。

6.2.2. 触发器的BEFORE类型和AFTER类型：

BEFORE类型的触发器在数据插入、更新或删除之前触发，可以用来验证数据的正确性、设置默认值等；AFTER类型的触发器在数据插入、更新或删除之后触发，可以用来记录数据变化、更新其他表等。

6.2.3. INSTEAD OF触发器（替代触发器）：

INSTEAD OF触发器是一种特殊的触发器，它可以用来替代INSERT、UPDATE或DELETE操作，当对视图进行INSERT、UPDATE或DELETE操作时，可以通过INSTEAD OF触发器对其进行操作。它只定义在视图上，用来替换实际的操作语句。

6.2.3.4系统触发器（数据库触发器）：

每当一个用户或数据库中一个数据事件发生时或系统事件（如登录或关闭系统）发生时触发，即对数据库系统进行操作（如DDL语句、启动或关闭数据库等系统事件)时触发。

--整体组成部分包括：
CREATE OR REPLACE TRIGGER<触发器名>
触发条件
触发体
---------------------------------------------------
 

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER} {INSERT | UPDATE | DELETE} [OF column_name [, column_name ...]] ON table_name [FOR EACH ROW]

[WHEN (condition)]

BEGIN

-- PL/SQL code

END; 
 
--理解如下：
create or replace trigger tri_name
before|after                                         --before是事前触发器,after是事后触发器
dml操作 [of 列] on 表 |   dml操作 or  dml操作 or....   --insert on update on delete   
如果是插入则..删除则..更新则..
前者锁定某个操作针对表，后者针对不同的dml操作
[for each row]--默认语句级,写上是行级
[declare 声明]
begin
  要执行的语句;
end;

 特别关键字：new：代表新的数据记录；old：代表数据表中原来的数据记录。insert事件具有new记录；update具有new和old；delete具有old记录。newheold本质上是一行记录，一个实体，可以通过new.字段名来获取记录中对应字段的值。

行级触发器

CREATE OR REPLACE TRIGGER AAAA_TRIGGER
BEFORE UPDATE or INSERT OF ENSPEC,CNSPEC,noutlength,noutwidth ON

AAAA
for each row
BEGIN
   IF :old.CNSPEC is not null  then 
     IF :new.CNSPEC is NULL THEN 
      raise_application_error(-20002,'中文需求描述不允许修改为空');
     END IF;
   END IF;
     IF :old.ENSPEC is not null  then 
     IF :new.ENSPEC is NULL THEN 
      raise_application_error(-20002,'英文需求描述不允许修改为空');
     END IF;
   END IF;
     IF :old.noutlength is not null  then 
     IF :new.noutlength is NULL THEN 
      raise_application_error(-20002,'外箱长度(CM)不允许修改为空');
     END IF;
   END IF;
     IF :old.noutwidth is not null  then 
     IF :new.noutwidth is NULL THEN 
      raise_application_error(-20002,'外箱宽度(CM)不允许修改为空');
     END IF;
   END IF;
END AAAA_TRIGGER;

每次修改，校验ENSPEC,CNSPEC,noutlength,noutwidth不能被置空

表级触发器

CREATE OR REPLACE TRIGGER so_saleorder_exe
AFTER INSERT ON so_saleorder_exe
BEGIN
UPDATE so_saleorder_exe SET version = version + 1 WHERE employee_id = :NEW.employee_id;
END;

每次新增之后，会更新当前表的version +1

INSTEAD OF触发器

CREATE TABLE employees (  
    emp_id NUMBER PRIMARY KEY,  
    emp_name VARCHAR2(50),  
    dept_id NUMBER,  
    CONSTRAINT fk_dept_id FOREIGN KEY (dept_id) REFERENCES departments(dept_id)  
);  
  
CREATE TABLE departments (  
    dept_id NUMBER PRIMARY KEY,  
    dept_name VARCHAR2(50)  
);

现在，我们创建一个视图，该视图将employees和departments表联接在一起：

CREATE VIEW emp_dept_view AS  
SELECT e.emp_id, e.emp_name, d.dept_name  
FROM employees e  
JOIN departments d ON e.dept_id = d.dept_id;
 

由于这个视图是一个联接视图，我们不能直接在其上执行DML操作（如UPDATE）。但是，如果我们想更新员工的部门名称，我们可以创建一个INSTEAD OF触发器来捕获这个UPDATE操作，并在基础表上执行相应的操作。

CREATE OR REPLACE TRIGGER trg_instead_of_update_emp_dept_view  
INSTEAD OF UPDATE ON emp_dept_view  
FOR EACH ROW  
BEGIN  
    -- 注意：这只是一个简单的示例，实际逻辑可能更复杂  
    UPDATE departments   
    SET dept_name = :NEW.dept_name  
    WHERE dept_id = :OLD.dept_id;  -- 假设emp_id与dept_id之间有1:1的映射关系，这通常不是实际情况  
END;

注意事项

• 触发器是隐式执行的，不需要手动调用。
• 触发器中的代码应该简洁、高效，避免执行复杂的计算或操作。
• 触发器可能会增加数据库的复杂性，因此应该谨慎使用，并确保在必要时进行充分的测试。

7、存储过程

待补充

8、各个数据库之间的差异

待补充