.NET程序员细数Oracle与众不同的那些奇葩点

2023-02-13 22:19:32

扯淡

距上次接触 Oracle 数据库已经是 N 年前的事了，Oracle 的工作方式以及某些点很特别，那会就感觉，这货就是一个奇葩！最近重拾记忆，一直在折腾 Oracle，因为 Oracle 与众不同，所以，想在这儿记录下 Oracle 不同于其他数据库的一些地方以及使用 Oracle 过程遇上的点点滴滴，同时，也让对 Oracle 陌生的同学有所了解。

导航

安装与创建数据库

Oracle 安装文件从官网下载就行，安装过程基本也就是一路点击下一步的事，在这就不多说。
安装完后，根据软件尿性，一般会依赖一些服务，Oracle 会不会也有呢？我们打开服务：
.NET程序员细数Oracle与众不同的那些奇葩点

嘿，还真发现多出了几个带“Oracle”字眼的服务，虽然不知道是干嘛用的，但肯定很重要，我们知道有这么个服务就好。如果这些服务没启动，我们手动把它们都给启动，反正启动了准没错。
Oracle 创建数据库有多种方式，我学了最简单的一种。就是利用 Oracle 自带的 Database Configuration Assistant 图形界面工具：

.NET程序员细数Oracle与众不同的那些奇葩点

.NET程序员细数Oracle与众不同的那些奇葩点

输入数据库名称后，一路下一步就好。
待数据库创建完毕，我们看下Oracle给我们生成了什么样的数据库文件。如果用 Database Configuration Assistant 创建数据库时没有指定数据库文件保存目录，则默认保存在 oracle 安装目录下名为 oradata 的文件夹中。打开 oradata 文件夹：

.NET程序员细数Oracle与众不同的那些奇葩点

我们可以看到，Oracle 会为我们创建的每个数据库都用一个文件夹保存，点进去看下有什么文件：

.NET程序员细数Oracle与众不同的那些奇葩点

生成了好几个.DBF和.LOG后缀的文件，这些估计就是 Oracle 的主数据文件和日志文件了，作为小白，咱们熟悉就好。
同时，我们再转到服务管理那，意外发现多出了新的服务：

.NET程序员细数Oracle与众不同的那些奇葩点

这两个服务名后缀就是我们创建的数据名，也不知道干嘛用的，我们尽管知道有这么回事就行啦，嘿嘿。
建好数据库，我们就可以正式开启 Oracle 之旅了。我是个对命令行极度恐惧的人，所以，我找了可视化界面工具 Navicat 连接 Oracle。Oracle 的基础语法就不一一介绍了，网上随便一搜就是一堆，接下来我只记录一些 Oracle 与众不同的基础点。

Oracle 奇葩点

限定符

SqlServer 和 MySql 的限定符分别是 [] 和 ``，Oracle 的则是双引号""。写 sql 语句时，它们都支持不加限定符。但在 Oracle 中，我们不得不了解一点的是，如果在 sql 中对象名(表名/列名)不加双引号，Oracle 在解析 sql 语句时会自动将对象名转成大写，同时加上双引号限定起来。比如这么个 sql：select * from table，Oracle 在运行的时候会转成 select * from "TABLE" 后再执行。又比如在创建表时 sql 是：create table MyTable..最终生成的表名是 MYTABLE，而不是 MyTable，因为没加限定符自动转大写的原因；如果加了限定符如：create table "MyTable"..,这时候创建出来的表名就是 MyTable了。因为 Oracle 严格区分大小写，所以，一定要理解加双引号和不加双引号的区别，很重要。

区分大小写

Oracle 与其他数据库不同，它对表名/列名严格区分大小写。比如有一个名为 MyTable 的表，查询 sql：select * from "myTable" 会无法执行，报表不存在的错误。Oracle 对 sql 语句要求很严谨，必须大小写一致，所以将 sql 改成 select * from "MyTable" 就能成功执行。照我们的习惯，手写 sql 的时候，都不会加限定符，也就是写成：select * from MyTable，这样也是不能成功执行的，因为 Oracle 自动转成 select * from "MYTABLE" 后执行，显然表 MYTABLE 是不存在的。也就是因为这点，通常做 Oracle 开发都有这么个潜规则：建表时，表名和字段全是大写！这样方便手写 sql，同时也为了统一规范！Oracle 这个特性，对于用惯了 SqlServer 或 MySql 的程序员来说，的确好不适应。刚开始知道这么回事的时候，我心里一万只羊驼路过...

字符类型比较大小写

在 SqlServer 中，字符类型字段值比较是不区分大小写的，但...万恶的 Oracle 却是区分大小写！比如执行：select case when 'A'='a' then 1 else 0 end from dual;返回的是 0 而不是 1！
好吧，表名/列名区分大小写我忍了，但这点...我真想爆粗口！

dual 表

dual 表在 Oracle 中是一张神奇的表，它真正的表名是大写的 DUAL，但为了看得舒服，我们还是用 dual 小写表示。
刚接触 Oracle，熟悉了基本的语法后我们通常会去学习它的一些函数，如 upper、lower、trim、cast 这些，在 SqlServer 中我们可以这样写，select upper('a'),lower('A'),cast(1 as targettype)，但在 Oracle 中，抱歉，这样写是不能直接执行的。Oracle 规定，任何 select 查询语句都必须从一个表对象中查，显然，select upper('a'),lower('A'),cast(1 as targettype) 这样的写法，在 Oracle 看来是语法上的错误，因为没有指定查询的表对象！所以我们得改成这样：select upper('a'),lower('A'),cast(1 as targettype) from dual。其实我在想，orcale 你就不能智能点吗？如果我们没有写 from xx 子句，你就默认使用 dual 表不就行了？
就不多吐槽了，我们还是看看 dual 是什么鬼东西吧！我们直接执行 select * from dual 看下里面有什么：

.NET程序员细数Oracle与众不同的那些奇葩点

如上，我们看到 dual 其实也就是一张“不同寻常”的常规表而已，只不过它只包含一个字段和一行数据，这样，我们就不难理解，为什么 select upper('a'),lower('A'),cast(1 as targettype) from dual 可以查出来数据了。
其实，我们也可以自定义一个只有一个字段和一行数据表，完全可以做到代替 dual 表的作用，但是，oracle 自身已经帮我们建好了这么个 dual 表，我们在建就完全没必要了。但为什么说 dual “不同寻常”呢？既然是一个表，我们是不是可以对这个表进行增删改呢？嘻嘻。我们试着插入一条数据：insert into dual(DUMMY) values('Y')：
.NET程序员细数Oracle与众不同的那些奇葩点

权限不足，哈哈，看来 Orcale 是对这个表进行了管制的。好吧，我们知道怎么用这个表就行了，更多有关 dual 的信息我们不用太关心(其实我很想知道 oracle 建这么一个表的时候取名 dual， 这个词翻译起来是啥意思，一直不明白- -)。

不能执行多条sql

众多数据都支持批量 sql 执行，但它就是不支持，说 Oracle 奇葩一点也不为过！可能有同学会问，我在 pl/sql 或 Navicat 里为什么可以一次执行多条语句？那是因为我们在写 sql 的时候用分号隔开，pl/sql 或 Navicat 自动帮我们拆开，实际上也是一条一条的发送给 Oracle 执行。

sys_guid 函数

Oracle 类型字典中是没有 GUID 类型，但它提供了一个生成 guid 的函数：sys_guid。但我想说的是，这函数并没什么X用！因为它返回的是二进制类型！我们设计表的时候总不能用二进制做主键吧！同时，我用的 Oracle 访问驱动是 Oracle.ManagedDataAccess，这个驱动的 DataReader 根本也不支持 GetGuid。

number 类型

在 Oracle 中 number 类型有点“广”，我们可以理解所有数值类型都可以用 number 表示。但有些东西还是适当了解比较好。如果一个 number 类型字段未指定精度，Oracle.ManagedDataAccess 驱动 DataReader 获得的数据类型将都会是 decimal 类型。虽然也可以调用 GetInt32、GetInt64 等方法获取值，但会有数据转换的损耗。因此，我们设计表的时候，对于数值类型，我们最好还是适当的指定精度为好。因为，指定了精度，Oracle.ManagedDataAccess 驱动会根据精度使用相应的C#类型存储，例如，如果一个字段类型是 NUMBER(9,0)，Oracle 驱动会使用 int 类型存储值，如果一个字段类型是 NUMBER(4,0)，Oracle 驱动会使用 Int16 类型存储值。从下面输出我们可以看出：

.NET程序员细数Oracle与众不同的那些奇葩点

同时，指定了精度，我想估计也会让数据库节省一定的空间，总之，如果做 Oracle 开发，我建议要养成指定精度的习惯。

自增实现

SqlServer 和 MySql 都有自增标识列，很方便，Oracle 不支持(听说新版Oracle开始支持了)。如果想要实现自增，得利用序列实现。我给 Chloe.ORM 扩展的 Oracle Provider 支持序列，但有个不好的点，就是每次插入数据的时候，得先从数据库将序列值查出来，然后再插入真实数据表，这相对 SqlServer 就多了一次数据库交互，多少有点不爽。

时间类型

Oracle 时间类型有 date 和 timestamp 两种类型。
Oracle 的 date 类型与 SqlServer 的 date 类型不同。SqlServer 的 date 类型精度精确到日，属真正意义的日期，而 Oracle 的 date 类型是精确到秒，可以说是时间类型了，不算日期了。不过，Oracle 这个奇葩，它有一个精确到毫秒级别的 timestamp 类型！但它与 date 类型有区别的：

1.如果我们想用to_char函数获取一个时间的毫秒部分，通常我们写成这样to_char(date,'ff3')，如果一个字段是 date 类型就不支持，因为 date 类型只精确到秒。然而 timestamp 类型可以 to_char(timestamp,'ff3') 这样提取毫秒数。
2.两个 date 类型的数据相减返回的是一个number类型的天数(这个非常好，SqlServer 和 MySql 都不支持)，而两个 timestamp 类型相减，则返回的是 Oracle 一个特别的时间戳类型INTERVAL DAY TO SECOND，我真的醉了- -。返回INTERVAL DAY TO SECOND类型，我们真心没法用啊...因为我在给 Chloe.ORM 开发 Oracle Provider，需要支持求两个日期相差的天数/小时数/分钟数/秒数/毫秒数，我真不知道怎么从INTERVAL DAY TO SECOND里提取出来！最后只能用一个变通的方式给支持了，但多少感觉 Oracle 这设计好无语。顺便吐槽下 Navicat，在 Navicat 查询器里不支持 TO_TIMESTAMP 函数(Oracle 是支持的)，坑了我等小白好几天！

ROWNUM 理解

Oracle 分页可以用类似 SqlServer 的 ROW_NUMBER() 函数，也可以用 Oracle 特有的 ROWNUM 分页。通常，我们都是用 ROWNUM 方式，毕竟这是 Oracle 推荐语法。

ROWNUM 并不是真实存在的一个列，它是执行查询时 Oracle 动态给查询的每一行加上的一个伪列。我们必须深入理解才能用好它。既然 ROWNUM 列并不是真实存在表中，那它什么时候被定义呢？一条完整的查询 sql 语句由5部分组成(select、from 、where、group、order)，执行时，这5部分是有先后顺序的，即从 from 数据集中扫描每行数据-->where条件过滤-->group-->order-->select，Oracle 在扫描数据集的时候每扫描到一行就给其编号(从1开始)，即设置 ROWNUM，给行编号动作是在 where 条件过滤之前，编号后才进行 where 过滤，所以，我们写 sql 的时候，可以在 where 条件里可以用 ROWNUM 进行过滤，如 where ROWNUM<10 等。

这相信不难理解，但有一点我们必须要知道的是，Oracle 每扫描到一行就给那行编号，假设是10，然后进行 where 条件过滤，如果不符合 where 条件的话，这条数据就会被抛弃，然后继续扫描下一行数据，但给下一行数据编的号还是上行不符合条件数据的编号，即还是10，以此类推，所以就保证了仅经过 where 条件过滤的结果集中的编号都是从1开始且有顺序的。只要我们理解了 ROWNUM 的生成机制，就好写 sql 了。
为了加深理解我对 ROWNUM 机制阐述，我们先来分析一个简单的 sql：select * from users where rownum>1，这个 sql 在 Oracle 里运行是永远的得不到结果的，因为 Oracle 从表 users 里扫描的第一行数据时，给其编号1，然后经 rownum>1 判断过滤不符合条件，所以第一行数据被抛弃，然后扫面第二行数据，此时第二行数据被编的号还是1，再经 rownum>1 判断过滤还是不符合，以此类推，所以就永远也查不出任何数据了。因此，在 where 条件中如果需要进行 rownum 过滤，只能是用 <、<= 条件。

鉴于 ROWNUM 的生成机制，所以，我们用 ROWNUM 分页的时候，必须用嵌套的方式实现，即类似这样的写法：

select * from

(
select T.*, ROWNUM RN
from (select * from MYTABLE order by id desc) T
where ROWNUM <= 40

)
where RN >= 21

位运算

Oracle 支持与运算(BITAND函数)，但不支持或运算，我...@#￥%^&*!...(此处省略一万只羊驼)

结语

Oracle 在数据库行列中算是奇葩的一个，给我的感觉就是用户体验差！对它真没啥好感- -。最后的最后，给大家留个小礼物，分享一个求两个时间差的函数给大家：

create or replace function DATETIMEDIFF

(

    inDateTime1 in TIMESTAMP,

    inDateTime2 in TIMESTAMP,

    inInterval in VARCHAR  -- d/h/m/s/ms

)
return NUMBER
as

    ret NUMBER;

    diffMillisecond NUMBER;

    intervalDivisor NUMBER;
begin

diffMillisecond :=

(cast(inDateTime1 as date)-cast(inDateTime2 as date)) * 24 * 60 * 60 * 1000

+
cast(to_char(inDateTime1,'ff3') as number)

-
cast(to_char(inDateTime2,'ff3') as number);

intervalDivisor :=

(case inInterval
when 'd' then (24 * 60 * 60 * 1000)
when 'h' then (60 * 60 * 1000)
when 'm' then (60 * 1000)
when 's' then 1000
when '

ms

' then 1
else 0 end); -- 以 0 作除数，引发异常

ret :=diffMillisecond/intervalDivisor;

return ret;

end;

我对 Oracle 了解不是很深，这个求时间差方式估计不是最好的，但勉强能求出两个时间差。如哪位同学有更好的想法，望指点分享，thx！

上一篇：SQL语法集锦一：SQL语句实现表的横向聚合


下一篇：MySql （MariaDB）的varchar字段的存储的是字符还是字节