Oracle使用手册

前言

本篇以 19C 版本0racle编写，不同版本之间存在语法差异，但也类似，所以遇到报错，可以先查看当前版本和版本对用的语法。

分页

offset fetch

-- 取第二十条开始往后的10条数据，offset：偏移量，表示跳过多少数据，next：表示取多少条数据，12C版本以上
SELECT * FROM 表名 OFFSET 2O ROWS FETCH NEXT 10 ROWS ONLY;

rownum

rownum是oracle中的一个数据序号，可以根据它获取数据，需妻注意的是这个值它是直接标记的，排序不会影响，可以理解为它是在排序前标记的。

-- 序号从1开始，则下面这个语句是获取前9行数据
SELECT * FROM 表名 where rownum<10

函数

字符串

字符串拼接

在Oracle中，字符拼接有两种方式；

方式一：

SELECT 'xxx'||'abc' FROM DUAL;

方式二：字符串连接函数concat只有了两个参数。

SELECT CONCAT('xxx','abc') FROM DUAL;

长度：LENGTH

获取字符长度

select length(concat('xxx','abc')) from dual;

字符截取：SUBSTR

3个参数：

第一个参数：需要截取的原字符串；

第二个参数：开始索引，及开始截取的索引值（索引从1开始）；

第三个参数：截取字符的长度，从开始索引开始计算；

-- 结果：xa
select substr('xxxabc',3,2) from dual;

字符索引：INSTR

可能场景：根据某个字符位置开始截取。

-- 直接从头开始匹配
-- 结果：4
select instr('xxxabc','a') from dual;
-- 从指定索引位置开始匹配
-- 结果：7
select instr('xxxabca','a',5) from dual;	

反转：REVERSE

用于字符反转，每个字符串中的字符都是有序号的，从1标记到最后一位，当使用这个函数时，便会从反反向读取返回。

-- 结果：cbaxxx
select reverse('xxxabc') from dual;

正则表达式匹配：REGEXP

regexp开始的函数都是正则表达式规则的。

REGEXP_COUNT

第一个参数：原字段值；

第二个参数：现需要匹配的字符的正则表达式；

-- 判断字符'x'出现了多少次
-- 结果：3
select regexp_count('xxxabc','x') from dual;

-- 结果：3
select regexp_count('xxxabc111','\d') from dual;

REGEXP_INSTR

正则表达式找到字符的索引位置。

-- 结果：7
select regexp_instr('xxxabc111','\d') from dual;

REGEXP_LIKE

正则表达式匹配，类似like，但功能更强大，其结果时trueorfalse，所以不能单纯的直接返回结果。

-- 、\d 匹配到了111，所以条件成立
-- 结果：x
select 'x' from dual where regexp_like('xxxabc111','\d');

-- 结果：x
select 'x' from dual where regexp_like('xxxabc111','[a-b]');

-- 这里字符不包含 d e 字符，所以没有匹配到，条件不成立
-- 结果：空
select 'x' from dual where regexp_like('xxxabc111','[d-e]');

REGEXP_REPLACE

如果说，你比较两个字符字段值是否相等，但是你复杂到编辑器里，看着还是一样，没编码问题，也没有空格，甚至于你用了trim取空格也不行，那么它很可能包含有空白制表符，如回车，不显示的空白符等；

第一个参数：原字段值；

第二个参数：要匹配字符的正则表达式；

第三个参数：替换后的值；

-- 结果：xxxabc
select regexp_replace('xxxabc111','\d','') from dual;

REGEXP_SUBSTR

提取字符，正则表达式匹配到的将会被提取。

第一个参数：原字段值；

第二个参数：要批评字符的正则表达式；

第三个参数：开始索引（从哪一个索引开始匹配）；

第四个参数：返回匹配到的第几个项，默认1；

-- 结果：xxx
select regexp_substr('xxxabc111','x+') from dual;

-- 结果：x
select regexp_substr('xxxabc111','x+',3) from dual;

-- 结果：空
select regexp_substr('xxxabc111','x+',4) from dual;

-- 结果：xx
select regexp_substr('xxxabc111','x+',1,2) from dual;

数字

加减乘除分别对应：+ - * /。

取模/取余

-- 结果：16
select mod(16,24) from dual;

四舍五入

第一个参数：要处理的值；

第二个参数：保留的小数；

-- 结果：4
select round(3.5) from dual;

-- 结果：3.5
select round(3.5,2) from dual;

向下取整

它只有一个此参数。

-- 结果：3
select floor(3.5) from dual;

格式化

可以通过to_char格式化时间、日期、金钱以及其他一些特殊的字符转换。

需要注意的是：to_char格式化的结果前面存在空格，需要使用FM去除。

FM：去除格式化后前面的空格；

金额

L：本地货币符号，根据服务器生成；

$：美元符号；

G：千分位分隔符；

D：小数点；

-- 结果：￥25
select to_char('25','FML99999999') from dual;

-- 结果：￥25.00
select to_char('25','FML99999999.00') from dual;

-- 结果：￥25.00
select to_char('25','FML999G999G999D00') from dual;

数字

9：可选位；

0：必填位，不足补0；

-- 结果：0006.0
select to_char('6','FM0000.00') from dual;

-- 结果：6.00
select to_char('6','FM9999.00') from dual;

日期

一般格式：YYYYMMDD HH24:MI:SS，毫秒格式：YYYYMMDD HH24:MI:SS.FF3。

-- 结果：2401
select to_char(to_date('20240101','YYYYMMDD'),'YYMM') from dual;

-- 字符转时间戳
select to_timestamp('20240101 10:25:33 111','YYYYMMDD HH24:MI:SS FF3') from dual;

-- 结果：20240101 10:25:33.111
select to_char(to_timestamp('20240101 10:25:33.111','YYYYMMDD HH24:MI:SS.FF3'),'YYYYMMDD HH24:MI:SS FF3') from dual;

TRIM

去除字符左右两边的空格，是LTRIM和RTRIM的结合。

如果对一个空字符串trim，则该值会被判断位空，也就是null值。

验证：

-- case when 就是 if else 的判断
-- 结果：is null
select case when trim('') is null then 'is null' else 'is not null' end from dual;

CASE WHEN

case when 对应到开发语言就是if else。

需要注意的是：

1. 存在多个when时，走了其中一个when之后，就不会走其他when了；

2. 一个case when中，最后返回的值要保持统一，不然会报错；

其格式如下：

case when  -> if 
     when  -> if else 
     when  -> if else 
     else  -> else
     end

DECODE

简化版的case when；

格式：decode(字段, 值1, 结果1 [, 值2，结果3] [, 默认值])

-- 判断 1 是 1 就返回true，是 2 则返回 false, 都不是 就默认 0
-- 结果：true
select decode('1','1','true','2','false','0') from dual;

NVL

如果原值位空，就返回指定的值。

-- 结果：0
select nvl(null,0) from dual;

GREATEST

返回多个值中最大的一个，它并不局限于数字，也支持时间、字符。

如果对比的是字符，在通常情况夏比较的是字符的ASCII值，并且其他笑也受长度影响，长度长的会被认定位更大。

-- 结果：4
SELECT GREATEST(1,2,3,4) FROM dual;

-- 结果：c
SELECT GREATEST('a','b','C') FROM dual;

-- 结果：2024-02-01 00:00:00
SELECT GREATEST(TO_DATE('20240101','YYYYMMDD'), TO_DATE('20240201','YYYYMMDD')) from dual;

TRUNC

它应用于数字和日期，数字是去除小数，日期是提取指定的部分。

-- 结果：1
select trunc(1.23) from dual;

日期提取:

-- 返回当年第一天
-- 结果：20240101 00:00:00
SELECT trunc(to_date('20240204','YYYYMMDD'),'YYYY') from dual;

-- 返回当月第一个
-- 结果：20240201 00:00:00
SELECT trunc(to_date('20240204','YYYYMMDD'),'MM') from dual;

-- 返回当季第一天
-- 结果：：20240401 00:00:00
SELECT trunc(to_date('20240604','YYYYMMDD'),'Q') from dual;

日期

加一天

直接加数字就行；

-- 结果：2024-01-03 00:00:00
select to_date('20240102','YYYYMMDD') + 1 from dual;

加一个月：ADD_MONTHS

如果你加月前是月底，比如30号，下一个月有31号，那么加一个月，结果就是31.

-- 加一个月
-- 结果：：2024-02-02 00:00:00
SELECT ADD_MONTHS(TO_DATE('20240102','YYYYMMDD'),1) from dual;

-- 结果：：2024-03-31 00:00:00
SELECT ADD_MONTHS(TO_DATE('20240229','YYYYMMDD'),1) from dual;

分组

格式：

SELECT groUpBy字段,MIN(计算的字段) FROM 表名 GROUP BY 字段;

开窗

和group by一样，不过它不同于group by的是，他会返回所有记录，和你指定的字段；

对应的开窗可以这样理解：
对指定字段进行分组，结果会有多个分组，每个分组中，再进行数据处理，如排序，计算，然后再连同原数据一并返回。

它可以实现如：返回账户夏最早交易数据的金额。

select * from (
    select t1.*.row_number() over(partition by 账号 order by 交易时间) rm
    from 交易明细表 t1
)  t
where rm = 1 

综合应用

问题一：对于不确定字符串，截取倒数第一个0开始到第一位的字符串。

我们假设字符串：xxx01230567，那么就需要截取：xxx01230

方法一：

-- 结果：xxx01230
select regexp_substr('xxx01230567','[a-zA-Z0-9]+0') from dual;

方法二：

-- 这里 +1 是因为 instr 获取的索引包含最后一个 0 的索引，索引再length 减去 instr 的值会少一位
select substr('xxx01230567', 1, length('xxx01230567') - instr(reverse('xxx01230567'),'0') + 1) from dual;

索引

注意：只使用联合索引中一个的字段时，也是生效的，不一定要第一个字段匹配上。

创建索引

create index 索引名 on 表名(字段名 [, 字段名]);

-- 函数式索引，当执行时需要trim后才能使用的字段时
create index 索引名 on 表名(trim(字段名));

删除索引

drop index 索引名;

重命名

rename index 旧索引名 to 新索引名;

重建索引

alter index 索引名 rebuild;

强制使用索引

select /*+index(t, 索引名)*/ * from 表名 t;

select /*+index(表名, 索引名)*/ * from 表名 t;

select /*+index(表1, 索引名) index(t2, 索引名)*/ * from 表1 t,表2 t2;

索引是否生效

-- 区分大小写
select table_name ,index_name,status from all_indexes where index_name='索引名称';

查看索引定义

SELECT * FROM USER_INDEXES where index_name='索引名';

查看索引所涉及的列

SELECT * FROM USER_IND_COLUMNS where index_name ='索引名';

递归

格式：

with 递归名(字段名) as (
  select 字段名
  from 表名
  where 条件
  union all
  select 字段名
  from 递归名
  where 递归退出条件
)
select * from 递归名;

下面走一个简单例子帮助理解：

1. 建表

create table tet(
    id int not null,
    name varchar(23) not null,
    code varchar(4)
);

2. 插入数据

insert into tet(id,name,code) values (1,'测试数据','1278');

3. 根据code字段值，将数据拆分出多条数据，其他字段值不变

with tet_geo(id,name,code,n_code,code_length) as (
    select id,name,code,substr(code,1,1) n_code,length(code) as code_length
    from tet
    union all
    select id,name,code,substr(code,code_length,1), code_length -1 as code_length
    from tet_geo
    where code_length > 1
)
select * from tet_geo;

结果：

ID  NAME    CODE    N_CODE  CODE_LENGTH
1   测试数据    1278    1   4
1   测试数据    1278    8   3
1   测试数据    1278    7   2
1   测试数据    1278    2   1

解析：

-- with 定义递归函数，查询字段与定义里的查询字段一致
with tet_geo(id,name,code,n_code,code_length) as (
    -- 递归起点，也是一条数据
    -- 保留code原字段，截取code中的第一个字符赋予新字段（因为下面的逻辑时从最后往前截取，到第一个步处理），
    -- 并通过截取的长度来判断是否退出递归，所以定义了code_length
    select id,name,code,substr(code,1,1) n_code,length(code) as code_length
    from tet
    -- 这里可以加条件过滤数据
    
    -- union all 拼接下面的结果
    union all
    -- 在上一个结果上，再截取code最后一个字符位新字段，长度递减
    select id,name,code,substr(code,code_length,1), code_length -1 as code_length
    from tet_geo
    -- 当长度递减到1时，不再有可以处理数据
    where code_length > 1
)
select * from tet_geo;

好了，对递归认识了，那么，问题二：递归的同时，增加id值，每次增加1，怎么实现？

存储过程

存储过程可以达成比较复杂的逻辑就，设想一下，你写一个sql，从开始到结尾就一句，无法像开发语言那样设定变量，定义代码块，和做复杂逻辑，但是存储过程可以。

删除存储过程

drop procedure 存储过程名;

调用存储过程

注意别漏了;

-- 方式一
begin
    存储过程名();
end;

-- 方式二

call 存储过程名();

创建存储过程

格式：

create or replace procedure 存储过程名 as 
    -- 定义游标，获取数据
    cursor 游标名 is select 字段名 from 表名;

    -- 生命变量与数据库字段一致
    v_id 表名.字段名 %TYPE;
        
    -- 定义变量接收数据
    p_code varchar(2);
        
    -- begin起手，end收尾
    begin
        -- 打开游标
        open 游标名;
        loop 
            -- 游标数据赋予到变量
            fetch 游标名 into v_id;
            -- 检查是否到数据末尾
            exit when 游标名%notfound;
            -- 赋值，赋值符号 :=
            p_code := '0';
            
            if xx then 
                业务;
            elsif xx then 
                业务;
            else xx;
            end if;
        end loop;
        close 游标名;
        -- 提交
        commit;
        
        exception
            when others then
                -- 异常，关闭游标，回退
                close 游标名;
                rollback ;
                -- 记录错误信息
                raise;
    end 存储过程名;

基于递归的例子进行数据的拆分，规则是：将code拆分，每个字符单独存放一条数据；

先创建一个临时表：

create table tet_tmp(
    id int not null,
    name varchar(23) not null,
    code varchar(4)
);

写法一：

CREATE OR REPLACE procedure tet_rp as
    cursor ctr_cur is select id,name,code from tet;
    v_id    tet.id      %TYPE;
    v_name  tet.name    %TYPE;
    v_code  tet.code    %TYPE;

    -- 变量
    p_code  varchar(2);
    code_length number;

begin
    -- delete可以进行回退 
    delete from tet_tmp;

    open ctr_cur;
    loop
        fatch ctr_cur into v_id,v_name,v_code;
        -- 检查是否到末尾
        exit when ctr_cur%notfound;
        -- 赋值
        code_length := length(v_code);
        p_code := substr(v_code, code_length);
        -- 插入数据到新表
        insert into tet_tmp(id,name,code) values(v_id,v_name,p_code);
        -- 控制台打印
        DBMS_OUTPUT.PUT_LINE('recode:'||v_id||v_name||p_code);

        -- 赋值
        code_length := code_length - 1;
        p_code := substr(v_code, code_length, 1);
        -- 插入数据到新表
        insert into tet_tmp(id,name,code) values(v_id,v_name,p_code);

        -- 赋值
        code_length := code_length - 1;
        p_code := substr(v_code, code_length, 1);
        -- 插入数据到新表
        insert into tet_tmp(id,name,code) values(v_id,v_name,p_code);

        -- 赋值
        code_length := code_length - 1;
        p_code := substr(v_code, code_length, 1);
        -- 插入数据到新表
        insert into tet_tmp(id,name,code) values(v_id,v_name,p_code);
    end loop;
    close ctr_cur;
    commit ;

exception
    when others then
        close ctr_cur;
        rollback ;
        raise;

end tet_rp;

写法二：

create or replace procedure tet_rp as
    cursor ctr_cur is select id,name,code from tet;
    v_row    tet      %ROWTYPE;

    -- 变量
    p_code  varchar(2);
    code_length number;

begin
    -- delete可以进行回退 
    delete from tet_tmp;

    open ctr_cur;
    loop
        fetch ctr_cur into v_row;
        -- 检查是否到末尾
        exit when ctr_cur%notfound;
        -- 赋值
        code_length := length(v_row.code);
        p_code := substr(v_row.code, code_length);
        -- 插入数据到新表
        insert into tet_tmp(id,name,code) values(v_row.id,v_row.name,p_code);
        -- 控制台打印
        DBMS_OUTPUT.PUT_LINE('recode:'||v_row.id||v_row.name||p_code);

        -- 赋值
        code_length := code_length - 1;
        p_code := substr(v_row.code, code_length, 1);
        -- 插入数据到新表
        insert into tet_tmp(id,name,code) values(v_row.id,v_row.name,p_code);

        -- 赋值
        code_length := code_length - 1;
        p_code := substr(v_row.code, code_length, 1);
        -- 插入数据到新表
        insert into tet_tmp(id,name,code) values(v_row.id,v_row.name,p_code);

        -- 赋值
        code_length := code_length - 1;
        p_code := substr(v_row.code, code_length, 1);
        -- 插入数据到新表
        insert into tet_tmp(id,name,code) values(v_row.id,v_row.name,p_code);
    end loop;
    close ctr_cur;
    commit ;

exception
    when others then
        close ctr_cur;
        rollback ;
        raise;

end tet_rp;

如果我们再加入一个if判断，在某个条件下执行其他逻辑呢？

加入测试数据

insert into tet(id,name,code) values(6,'多加一条','0000');

当code='0000'时只插入一个code为null的数据，当code='9999'时，插入一条为9999的数据。

这里使用了if elsif else end if;，同样可以使用case when实现。

create or replace procedure tet_rp as
    cursor ctr_cur is select id,name,code from tet;
    v_row    tet      %ROWTYPE;

    -- 变量
    p_code  varchar(2);
    code_length number;

begin
    -- delete可以进行回退 
    delete from tet_tmp;

    open ctr_cur;
    loop
        fetch ctr_cur into v_row;
        -- 检查是否到末尾
        exit when ctr_cur%notfound;

        if v_row.code='0000' then
            insert into tet_tmp(id,name,code) values(v_row.id,v_row.name,null);
            exit;
        elsif v_row.code='9999' then
            insert into tet_tmp(id,name,code) values(v_row.id,v_row.name,'9999');
            exit;
        end if;

        -- 赋值
        code_length := length(v_row.code);
        p_code := substr(v_row.code, code_length);
        -- 插入数据到新表
        insert into tet_tmp(id,name,code) values(v_row.id,v_row.name,p_code);
        -- 控制台打印
        DBMS_OUTPUT.PUT_LINE('recode:'||v_row.id||v_row.name||p_code);

        -- 赋值
        code_length := code_length - 1;
        p_code := substr(v_row.code, code_length, 1);
        -- 插入数据到新表
        insert into tet_tmp(id,name,code) values(v_row.id,v_row.name,p_code);

        -- 赋值
        code_length := code_length - 1;
        p_code := substr(v_row.code, code_length, 1);
        -- 插入数据到新表
        insert into tet_tmp(id,name,code) values(v_row.id,v_row.name,p_code);

        -- 赋值
        code_length := code_length - 1;
        p_code := substr(v_row.code, code_length, 1);
        -- 插入数据到新表
        insert into tet_tmp(id,name,code) values(v_row.id,v_row.name,p_code);
    end loop;
    close ctr_cur;
    commit ;

exception
    when others then
        close ctr_cur;
        rollback ;
        raise;

end tet_rp;

性能

版本

-- 方式一
select * from v$version;
-- 方式二
select * from product_component_version;

查询临时表空间占用

注意：

普通用户是没有查询这些表的权限的，如果有必要，需要 sysdba 身份赋予用户查询的权限：

如果你是系统管理员，是没什么问题的。

# 登录
sqlplus / as sysdba;

# 授权
grant select on v_$session to 当前用户;
grant select on v_$sort_usage to 当前用户;
grant select on v_$sql to 当前用户;
grant select on v_$PROCESS to 当前用户;
grant select on v_$SQLAREA to 当前用户;

select s.SID, s."SERIAL#",s.SQL_ID, su.BLOCKS, sq.SQL_TEXT
from
v$sort_usage su,v$session s ,v$sql sq
where su.SESSION_ADDR=s.saddr and s.SQL_HASH_VALUE = sq.HASH_VALUE and s.SQL_ID=sq.SQL_ID
order by su.BLOCKS DESC

查询正在执行的sql

SELECT B.SID,B.SERIAL#,C.SQL_TEXT FROM V$PROCESS A,V$SESSION B,V$SQLAREA C
WHERE A.ADDR=B.PADDR AND B.SQL_HASH_VALUE=C.HASH_VALUE

结束在执行的sql

ALTER SYSTEM KILL SESSION 'sid,serial#';

sql分析/查询计划

方式一
使用工具，一般都会带有【解释查询计划】这样的菜单；

方式二
步骤一：

explain plan for
select * from 表名 where name='xxx';

步骤二：
上面执行完毕后，再执行:

select * from table (dbms_xplan.display());

最好用文本显示的方式，因为她的结果就是一个编排过的文本。

并行执行

对于性能较好的服务器，可以使用parallel(表名/别名, 并行数)增加执行的并行度，提高执行的效率；
需要注意的是，增加并行度会增加服务器的负载，降低性能。

select /*+parallel(t1,32) parallel(t2,32)*/ * from 表1 t1,表2 t2;
select /*+parallel(表1,32) parallel(表2,32)*/ * from 表1 t1,表2 t2;