Oracle基础教程

一、数据操作

1. 虚拟表

类似于 MySQL 当我们不根据已有的表进行查询时，即可通过虚拟表的方式实现。

虚拟表查询格式如下：

select '' as <列1>, ..., '' as <列n> from dual;

例如下行命结果为：

select 'Alex' as name, '12' as age from dual;

------------------- 
|   name  |  age  |
------------------- 
|   Alex  |  12   |  
------------------- 

2. 增量变更

Merge 函数可以对新增的记录进行判断，如果满足设定条件（如 ID 已存在），则进行更新操作，否则进行插入操作。通过 Merge 可以利用一条语句实现多种效果。

(1) 语法

Merge 基本语法结构如下：

merge into <t_table> <t_alias>
using <s_table> <s_alias>
on( 
   <addition> 
) 
when matched 
then
   update set <t_alias.column-1> = <s_alias.column-1>, ..., <t_alias.column-n> = <s_alias.column-n>
when not matched 
then 
   insert values(<t_alias.column-1>, ..., <s_alias.column-n>);

(2) 示例

假如目标表 TB_TEST 与来源表 TB_TEMP 如下：

-------------------------          ------------------------- 
|   id  |  name  |  age |          |   id  |  name  |  age |
-------------------------          -------------------------
|   1   |  Alex  |  18  |          |   2   |  Jack  |  30  |
|   2   |  Mark  |  22  |          |   3   |  Beth  |  42  |
-------------------------          -------------------------

我们希望的结果时将 TB_TEMP 的数据添加到 TB_TEST 中，但对于 ID 重复的数据进行更新操作，新数据则进行插入。即我们预期得到如下数据：

------------------------- 
|   id  |  name  |  age |
------------------------- 
|   1   |  Alex  |  18  |
|   2   |  Jack  |  30  |
|   3   |  Beth  |  42  |
-------------------------

最终实现的 SQL 语句如下：

merge into tb_test t 
using tb_temp p
on(t.id = p.id) 
when matched then
   update set t.name = p.name, t.age = p.age
when not matched then 
   insert values(p.id, p.name, p.age);

二、常用函数

1. 日期函数

Oracle 中常用的时间函数存在三类，描述信息参考下表：

函数	描述
to_char()	用于将时间类型字段数据转化为指定格式的字符串。
to_date()	用于将字符串按一定格式转化为时间类型。
to_timestamp()	用于将字符串按一定格式转化为时间戳类型。

-- 将时间转为字符串
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') as tt from dual;

-- 将字符串转为日期
select to_date('2022-06-10 02:17:54', 'yyyy-mm-dd hh24:mi:ss') as tt from dual;

-- 字符串转时间戳
select to_timestamp('2022-06-10 02:17:54.123', 'yyyy-mm-dd hh24:mi:ss.ff') as tt from dual;

2. 字符函数

(1) instr()

instr(source, target): 在 source 中搜索 target 第一次出现的位置。

instr(source, target, m, n): 在 source 中第 m 个位置搜索 target 第 n 次出现的位置。

-- 查找第一次出现的 'a' 的位置
select instr('abc', 'b') as result from dual;

-- 第 2 位开始，查找第二次出现的 'a' 的位置
select instr('abcafga', 'a', 2, 2) from dual; 

(2) substr()

substr(source, m, n): 截取字符串 source 下标为 m 至 n 间的内容。

-- 从第 0 位截取到第 3 位
select substr('HelloWorld', 0, 3) value from dual; 

三、分析统计

1. 数据分析

通过执行分析即可手动触发元数据表信息的更新，语句基本格式如下：

dbms_stats.gather_table_stats(
    ownname => '<schema>',
    tabname => '<table_name>',
    block_sample => true,
    granularity => 'GLOBAL',
    method_opt => 'for all indexed columns',
    cascade => true,
    degree => 10
);

上述中分析语句中涉及的参数及其描述如下：

参数	描述
ownname	所要分析的目标的表的 Schema。
tabname	所要分析的目标的表表名。
block_sample	是否用块采样代替行采样。
granularity	要收集的统计信息的粒度，针对分区表使用。
method_opt	决定 histograms 信息是怎样被统计的，默认值为 FOR ALL COLUMNS SIZE AUTO
cascade	是否收集索引的信息，默认为 FALSE。
degree	决定并行度，默认值为 null。

2. 元数据表

Oracle 元数据表 ALL_TABLES 中存放数据库表的基本信息。

如下语句查询 BUDAI 用户下所有表的元数据基本信息。

SELECT
    *
FROM
    ALL_TABLES
WHERE
    OWNER = '<schema>';