開始學習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 --還原資料庫操作到上一個commit
	SET 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;  --回傳 89

select TRUNC（89.985 , 2） from dual;  --回傳 89.98

select 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;              --回傳 123

select round(123.456, 0) from dual;          --回傳 123

select round(123.456, 1) from dual;          --回傳 123.5

select round(123.456, 3) from dual;          --回傳 123.456

select round(-123.456, 2) from dual;        --回傳 -123.46

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

CEIL(數值)

無條件進入

select ceil(123.6) from dual;              --回傳 124

select ceil(123.4) from dual;          --回傳 124

FLOOR(數值)

無條件捨去

select floor(123.6) from dual;              --回傳 123

select 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 是否成功與處理的筆數

筆記內容如果有錯誤歡迎留言告知，可以幫忙糾正錯誤的觀念，感謝！