Oracle 常用函式笔记 1

开始学习PL/SQL的阵痛期，将一些没使用过的涵式及术语定义纪录下来

---

分类

在oracle 中的sql 语法区分以下四种分类 :

DDL 资料定义语言 Data Definition Language (DDL)

    CREATE -- 建立ALTER -- 变更DROP -- 移除TRUNCATE --清空资料COMMENT -- 注解RENAME -- 更名

DML 资料操作语言 Data Manipulation Language (DML)

    SELECT -- 搜寻INSERT -- 新增UPDATE -- 更新DELETE -- 删除MERGE -- 比对CALL -- 呼叫EXPLAIN PLAN --执行计画LOCK TABLE --锁定资料表避免重覆操作

DCL 资料控制语言 Data Control Language (DCL)

    GRANT -- 授权REVOKE -- 取消授权

TCL 交易控制语言 Transaction Control (TCL)

    COMMIT --储存当前操作SAVEPOINT --设定可以roll back的交易点    ROLLBACK --还原资料库操作到上一个commitSET TRANSACTION --设定整个交易是唯读或是可读写的状态

参考 : Oracle SQL 的分类DML、DCL、DDL
参考 : Oracle 用 Merge 进行两个 Table 之间的资料比对
参考 : Oracle PL/SQL 中, Savepoint 与 Rollback 的用法
参考 : SET TRANSACTION READ介绍

函式

dual 资料表

dual是一个虚拟表，用来构成select的语法规则，oracle 9 之后保证dual里面永远只有一条记录，目前最常使用虚拟表来测试一些涵式结果，或当作运算结果测试都非常好用。

-- 取得当前资料库系统时间select sysdate from dual

输出:

-- 取得格式化当前资料库系统时间select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual

输出:

后面涵式介绍也多会以dual来展示结果

参考 : Oracle中dual表的用途

DECODE(欲判断的资料, 判断值1, 相等的设定值1, 判断值2, 相等的设定值2 , ..., 不相等的设定值)

SELECT DECODE( 资料, '100', 'AAA'                    , '200', 'BBB'                    , '300', 'CCC'                    , '400', 'DDD'                    , 'NONE'             )  FROM DUAL; -- 结果: 若资料为 '100'，则输出 AAA，若资料为 '1050'，则输出 NONE

CASE WHEN() THEN

SELECT CASE WHEN(1=5) THEN 'ABC'             WHEN(1=1) THEN 'DEF'             ELSE 'None'             End test FROM dual             -- 结果: DEF

参考 : Oracle PL/SQL: Select 子句如何使用判断式 (大于, 小于, 等于)

TO_DATE('日期时间字串', 'yyyy-mm-dd hh24:mi:ss')

将字串转为日期格式

SELECT TO_DATE('2018-05-19 00:00:00','yyyy-mm-dd hh24:mi:ss') FROM dual

输出:

NVL(资料, null or '' 时的资料值)

资料为 null

select 1  from dual where nvl( null, '1') = '1'; -- 结果: 传回 1 笔资料

资料为 '' 空字串

select 1  from dual where nvl( '', '1') = '1';  -- 结果: 传回 1 笔资料

资料为 ' ' 空白字串

select 1  from dual where nvl( ' ', '1') = '1'; -- 结果: 传回 0 笔资料

参考 : Oracle PL/SQL: NVL 的用法

TRUNC()

处理数字 TRUNC(数字, 截断位置)
截断位置为负数可将指定位数后面的部分截去，即均以0记

select TRUNC（89.985） from dual;  --回传 89select TRUNC（89.985 , 2） from dual;  --回传 89.98select TRUNC（18589.985 , -2） from dual;  --回传 18500

处理日期 TRUNC(日期,截断格式)

select trunc(sysdate, 'yyyy') from dual --回传当年第一天 select trunc(sysdate, 'mm') from dual --回传当月第一天.select trunc(sysdate, 'd') from dual --回传当前星期的第一天

ROUND(数值, [四捨五入到小数点后几位])

四捨五入

select round(123.456) from dual;              --回传 123select round(123.456, 0) from dual;          --回传 123select round(123.456, 1) from dual;          --回传 123.5select round(123.456, 3) from dual;          --回传 123.456select round(-123.456, 2) from dual;        --回传 -123.46

参考 : plsql中round()、trunc()、to_char()的使用

CEIL(数值)

无条件进入

select ceil(123.6) from dual;              --回传 124select ceil(123.4) from dual;          --回传 124

FLOOR(数值)

无条件捨去

select floor(123.6) from dual;              --回传 123select floor(123.4) from dual;          --回传 123

SQL%RowCount

在执行 DML 后可透过SQL%RowCount来检查执行结果:

SQL%RowCount = 0, 则表示 "DML 无处理任何资料" or "DML 执行失败"SQL%RowCount > 0, 则表示 "DML 有处理资料成功", 且 "其值" 便是处理的资料笔数

Oracle PL/SQL 如何得知 DML 是否成功与处理的笔数

笔记内容如果有错误欢迎留言告知，可以帮忙纠正错误的观念，感谢！