本文记录使用 Oracle 的日常摘要

DBLink

# 创建 dblink (public代表都能用, 不然就只有当前库能用)
CREATE public DATABASE LINK CUSTOM_LINK_NAME
connect to database_name identified by 'password'
using
'ip:1521/orcl';
# use dblink
select * from table_a@CUSTOM_LINK_NAME

同义词 (视图)

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]synonym_name 
FOR [schema.]object_name[@dblink];

备份表 (CTAS 语句)

• CREATE TABLE AS SELECT（CTAS）

-- 创建新表并复制数据
CREATE TABLE 新表名 AS
SELECT * FROM 原表名;

Oracle 分页查询 (rownum 和 offset)

一、Rownum 的概念

A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned .

1. rownum 主要有两类用处

   • 处理 top N ；
   • 分页查询；

2. rownum 为查询过程中的伪列(虚列)，只在查询时构造，从 1 开始计数，通常为自由分配，用户无法修改；

3. rownum 的值是在行记录通过了查询的过滤阶段、在排序或聚合之前被赋值。rownum 只有在被赋值之后才会递增。也就是先有结果集再有 rownum（在from/where过滤之后，group/order之前）

sql 语言的执行顺序为：from join where group having select order limit

4. select * from t where rownum > 1; 是永远查不到记录的。因为 rownum 永远从 1 开始计数，，所以选出的结果是不能跳过 1 的；比如 rownum = 2（其他任何大于 1 的值）结果集为空。

• 原理是：
  1. 先from找表
  2. 再筛除 rownum 之外的where查询条件
  3. 得到结果集进行分配，设置rownum = 1
  4. 发现当前行不满足rownum > 1进行剔除
  5. 进入下一行，设置 rownum = 1
  6. 还是不满足，结束。（经试验，会进行全表扫描）

5. 查询前十条有 4 种写法：

-- 范围
rownum<=10
rownum<11
-- 条件
rownum<>11  --不等于的写法会全表扫描，慢于上面两种
rownum!=11  --不等于的写法会全表扫描，慢于上面两种

二、Offset 处理范围

1. offset 代表跳过前 n 行，如果表少于 n+1 条记录，结果集将是空的；比如 n = 100，表示从 101 开始往后查。

2. fetch next 代表往后查 n 行；

3. next 和 first 可互换；

4. row 和 rows 可互换；

5. offset 语法：

OFFSET { integer-literal | ? } {ROW | ROWS}
FETCH { FIRST | NEXT } [integer-literal | ? ] {ROW | ROWS} ONLY

例子：（一般会结合 order by 使用，如果没有一般是记录插入的顺序）

-- 从第20条开始取下10条记录
SELECT * 
FROM table_name
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

1. 查看 Oracle 操作日志

-- 查看oracle操作日志
select t.SQL_TEXT, t.FIRST_LOAD_TIME
from v$sqlarea t
where t.FIRST_LOAD_TIME like '2021-10-04%'
order by t.FIRST_LOAD_TIME desc

2. Oracle 区分单引号和双引号

• 字符串 用 单引号
  where id='string'
• 字段名 和 表名 用 双引号
  select "tableField" from "Table"

可能原因: 字符串常常会包含 " 双引号, 而表名没有必要区分单双, 不如只限定双引号, 和字符串区分

单引号使用:

1. ORACLE 中的单引号叫做 quote_delimiter
2. 有两个作用: 1) 引用字符串 2)对自身转义
3. 字符串 'hello''s world' 中使用 '' (两个连续的单引号’') 表示单引号: hello's world
4. 最外面的两个单引号是相互匹配的. 表示这是 oracle 字符串, 而里面的连续的两个单引号代表字符串中的一个单引号
5. 复杂转义字符串可用连接符||拆分 'mydb?page=1'||'&'||'pagesize=10' or 'mydb?page=1'||'chr(38)'||'pagesize=10'

双引号使用:

1. 字符串中的双引号仅被当作一个普通字符进行处理
2. 在 to_char 格式字符串中, 需要将非法的格式符用 " 包装起来, 避免出现 ORA-01821: date format not recognized 也就是说去掉双引号和其包含的字符后，剩下的应该是一个合法的格式串
3. 在 to_char 处理格式字符串时，会忽略双引号

&(and符号):

1. 用于自定义变量, 后需跟变量名;
2. 如需作为普通字符, 需使用Chr(38), 或者接连两个 && (自身转义)

Escape (单独指定本次使用的转义符):

-- 指定一个非特殊符号为转义符, 多用在通配符转义 '=_' -> '_' i.e. 'tb=_%=_%' -> 'tb_%_%'
Select Table_Name From User_Tables Where Table_Name Like 'tb=_%=_%' Escape '=';

3. Oracle 区分大小写

表名和字段名如果在定义的时候就加了双引号是小写，则查询的时候 小写必须加双引号（不加双引号自动转大写）

4. insrt() 函数

instr(string1, string2, start_position,nth_appearance) 函数。instr函数在Oracle/PLSQL 中是返回要截取的字符串在源字符串中的位置。位置索引号从1开始。

- string1：源字符串，要在此字符串中查找。
- string2：要在string1中查找的字符串 。
- start_position：代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。
- nth_appearance：代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1.如果为负数系统会报错。

如果String2在String1中没有找到，instr函数返回 0。示例，
SELECT instr(‘syranmo’,‘s’) FROM dual; – 返回 1
SELECT instr(‘syranmo’,‘ra’) FROM dual; – 返回 3
SELECT instr(‘syran mo’,‘at’,1,2) FROM dual; – 返回 0

5. JSON 处理

oracle 对于json是按字符串从前往后解析的，并且是解析时才检测语法（不会提前检测语法错误，而是解析到哪就就能得到哪的值）

要使用 ’$.json属性’ 来对 json字段的对应属性 进行查询条件

select
json_value(push_json, '$.id'),
json_query(push_json, '$.roleList'),
json_value(push_json, '$.name'),
push_json
from table
where
isjson(push_json) and
push_json like '%id%'

-- json_value 仅解析 int string bool 3种类型。不符合返回 null
-- json_query 仅解析 array 和 object 等其他元素，把对应的元素返回。不符合返回 null
-- isjson() 判断json是否合法
-- push_json like '%id%' 表明 json属性 是可以当成 字符串 处理的

如何生成 UUID

1. GUID（全局唯一标识符）或 UUID（通用唯一标识符 Universally Unique Identifier）通常是一个 128 位的数值。其标准表示为 32 个十六进制字符串，通常以 8-4-4-4-12 的格式显示
2. SYS_GUID() 会产生一个跟 MAC 地址、生成时间相关的一个长度 32 的 16 进制随机数

虽然它的表现形式是一个长度 32 的 16 进制字符串，但实际上它是一个 128 位（16 字节）的值，所以类型是 RAW(16)

1. 通过查询获取

-- 数据类型是 raw(16) 有 32 个字符
SELECT SYS_GUID() FROM DUAL;
-- RAWTOHEX() 将二进制格式的数据转换成十六进制的字符串;
-- LOWER() 转小写;
SELECT LOWER(RAWTOHEX(SYS_GUID())) "字符串", SYS_GUID() "二进制数据" FROM DUAL;
-- 添加横杠
SELECT SUBSTR(GUID, 1, 8) || '-' || SUBSTR(GUID, 9, 4) || '-' || SUBSTR(GUID, 13, 4) || '-' ||
       SUBSTR(GUID, 17, 4) || '-' || SUBSTR(GUID, 21, 12) "UUID"
FROM (SELECT LOWER(RAWTOHEX(SYS_GUID())) AS GUID FROM DUAL);

2. 通过自定义函数获取

-- 创建函数
CREATE OR REPLACE FUNCTION GET_UUID
    RETURN VARCHAR
    IS
    GUID VARCHAR(50);
BEGIN
    GUID := LOWER(RAWTOHEX(SYS_GUID()));
    RETURN
        SUBSTR(GUID, 1, 8) || '-' || SUBSTR(GUID, 9, 4) || '-' || SUBSTR(GUID, 13, 4) || '-' ||
        SUBSTR(GUID, 17, 4) || '-' || SUBSTR(GUID, 21, 12);
END GET_UUID;

-- 使用函数 (函数名大小写不敏感)
select get_uuid() from dual;
-- 931eacf7-d8d7-4e6b-951a-bab166835a90

---

(END)