狐的窩

上星期遇到的情況，當測試資料也建好了，就很直覺下了一個SQL語法去檢核該條件欄位是不是空的？
SQL> SELECT * FROM TXN FROM USERDATE != ''
結果卻找不到任何資料！當下覺得怎麼會這樣，明明資料才建好，不加條件就找得到資料。只好看文件找解答，最後的結果是
Oracle doesn't differentiate between empty strings and NULL, Use the IS NULL syntax to check if variable is an empty string.

突然來了個要計算每筆交易的時間，試著用Oracle提供的函式來解決，將最後的結果做個記錄。
計算兩日期的時間差：START_DATE, END_DATE
天：ROUND(TO_NUMBER(END_DATE - START_DATE))
小時：ROUND(TO_NUMBER(END_DATE - START_DATE) *24)
分：ROUND(TO_NUMBER(END_DATE - START_DATE) *24*60)
秒：ROUND(TO_NUMBER(END_DATE - START_DATE) *24*60*60)
毫秒：ROUND(TO_NUMBER(END_DATE - START_DATE) *24*60*60*1000)
上述的START_DATE, END_DATE為日期形態，如果遇到日期都用字串形態存入資料庫時，需在用TO_DATE轉換
TO_DATE(START_DATE, 'YYYYMMDDHH24MISS')
YYYYMMDDHH24MISS 這格式需帶入符合您的日期格式

往往在操作PL/SQL時會遇到所謂的靈異現象，明明兩字串肉眼看都一模一樣，可是程式就是不往設定的流程跑，會發生此問題，主要是PL/SQL和Oracle在對資料型態不同的字串處理方式不一樣。
PL/SQL以varchar2類型接收Oracle的char類型，將會自動去除後端的空白；以char類型接收varchar2類型，會補滿空白。
Oracle的varchar2接收char時，不會去除空白；Oracle的char接收varchar2會補滿空白。
因此當兩字串比較時，就會因空白的差異而得到非預期的結果。此時建議用
RTRIM

不管是資料庫操作或是Shell script撰寫，日期的運算加減是常會遇到的一個問題，Oracle提供了一些常用的運算函數來操作這些日期的運算問題。


1. SYSDATE + or - 天數

EX:
SQL> SELECT SYSDATE FROM dual;
SYSDATE
----------
2011/08/15
2. 日期加數值
EX:
SQL> SELECT SYSDATE+10 FROM dual;
SYSDATE+10
----------
2011/08/25
3. 日期減數值
EX:
SQL> SELECT SYSDATE-15 FROM dual;
SYSDATE-15
----------
2011/07/31
4. 日期相減
EX:
SQL> SELECT SYSDATE- TO_DATE('2011/08/14') FROM dual;
SYSDATE-TO_DATE('2011/08/14')
-----------------------------
1.51289352
SQL> SELECT TRUNC(SYSDATE- TO_DATE('2011/08/14')) FROM dual;
TRUNC(SYSDATE-TO_DATE('2011/08/14'))
------------------------------------
1
5. 日期相減獲得小時差距
EX:
SQL> SELECT TRUNC((SYSDATE - TO_DATE('2011/08/14'))*24) FROM dual;
TRUNC((SYSDATE-TO_DATE('2011/08/14'))*24)
-----------------------------------------
36
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'YY
-------------------
2011/08/15 12:21:52
6. 日期相減獲得分鐘差距
EX:
SQL> SELECT TRUNC((SYSDATE - TO_DATE('2011/08/14'))*24*60) FROM dual;
TRUNC((SYSDATE-TO_DATE('2011/08/14'))*24*60)
--------------------------------------------
2182
7. 日期相減獲得秒數差距
EX:
SQL> SELECT TRUNC((SYSDATE - TO_DATE('2011/08/14'))*24*60*60) FROM dual;
TRUNC((SYSDATE-TO_DATE('2011/08/14'))*24*60*60)
-----------------------------------------------
130993
8. 日期加 N 小時
EX:
SQL> SELECT TO_CHAR(SYSDATE+(1/24), 'YYYY/MM/DD HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE+(1/
-------------------
2011/08/15 13:24:37
9. 日期加 N 分鐘
EX:
SQL> SELECT TO_CHAR(SYSDATE+(1/1440), 'YYYY/MM/DD HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE+(1/
-------------------
2011/08/15 12:26:11
10. 日期加 N 秒數
EX:
SQL> SELECT TO_CHAR(SYSDATE+(1/86400), 'YYYY/MM/DD HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE+(1/
-------------------
2011/08/15 12:25:33
11. ADD_MONTHS(d, n)
從時間點 d 加上 n 小時
EX:
SQL> SELECT SYSDATE, ADD_MONTHS(SYSDATE, 3) FROM dual;
SYSDATE ADD_MONTHS
---------- ----------
2011/08/15 2011/11/15
12. LAST_DAY(d)
從時間點 d 起，當月的最後一天
EX:
SQL> SELECT SYSDATE, LAST_DAY(SYSDATE) 月底 FROM dual;
SYSDATE 月底
---------- ----------
2011/08/15 2011/08/31
13. NEXT_DAY(d, char)
從時間點 d 開始，下星期幾的日期
char: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY

EX:
SQL> SELECT SYSDATE, NEXT_DAY(SYSDATE, 'MONDAY') "下星期一" FROM dual;
SYSDATE 下星期一
---------- ----------
2011/08/15 2011/08/22
SQL> SELECT SYSDATE, NEXT_DAY(SYSDATE, 'MONDAY')+1 FROM dual;
SYSDATE NEXT_DAY(S
---------- ----------
2011/08/15 2011/08/23
14. MONTHS_BETWEEN(d1, d2)
計算兩日期之間的相隔月數

EX:
SQL> SELECT TRUNC(MONTHS_BETWEEN('2011/08/31','2011/07/01')) FROM dual;
TRUNC(MONTHS_BETWEEN('2011/08/31','2011/07/01'))
------------------------------------------------
1
以15號為四捨五入
SQL> SELECT ROUND(MONTHS_BETWEEN('2011/08/31','2011/07/01')) FROM dual;
ROUND(MONTHS_BETWEEN('2011/08/31','2011/07/01'))
------------------------------------------------
2
15. NEW_TIME(d, z1, z2)
轉換新時區

EX:
SQL> SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') "遠東地區" ,
2 TO_CHAR(NEW_TIME(SYSDATE,'EST','GMT'),'YYYY/MM/DD HH24:MI:SS') "格林威治"
3 FROM dual;
遠東地區 格林威治
------------------- -------------------
2011/08/15 13:57:47 2011/08/15 18:57:47
16. ROUND(d[, fmt])
對日期作四捨五入的運算
月份以每月15號為基準
年份以六月為基準

EX:
SQL> SELECT SYSDATE,ROUND(SYSDATE,'MONTH') FROM dual;
SYSDATE ROUND(SYSD
---------- ----------
2011/08/15 2011/08/01
SQL> SELECT SYSDATE,ROUND(SYSDATE,'YEAR') FROM dual;
SYSDATE ROUND(SYSD
---------- ----------
2011/08/15 2012/01/01
17. TRUNC(d[, fmt])
對日期作無條件捨去的運算

EX:
SQL> SELECT SYSDATE, TRUNC(SYSDATE,'YEAR') FROM dual;
SYSDATE TRUNC(SYSD
---------- ----------
2011/08/15 2011/01/01
SQL> SELECT SYSDATE, TRUNC(SYSDATE,'MONTH') FROM dual;
SYSDATE TRUNC(SYSD
---------- ----------
2011/08/15 2011/08/01

Oracle 用來取得目前系統時間的函數為sysdate。
EX:
SQL> SELECT sysdate FROM dual;
SYSDATE
---------
15-AUG-11
*更改目前session日期顯示格式
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
Session altered.
SQL> SELECT sysdate FROM dual;
SYSDATE
----------
2011-08-15
常用的日期格式：
1. YYYY/MM/DD
YYYY 年(4位)
MM 月份(2位)
DD 日期(2位)
SQL> SELECT TO_CHAR(sysdate, 'YYYY/MM/DD') FROM dual;
TO_CHAR(SY
----------
2011/08/15
2. 取得星期幾
Sunday=1, Monday=2, ...
SQL> SELECT TO_CHAR(sysdate, 'D') FROM dual;
T
-
2
SQL> SELECT TO_CHAR( TO_DATE('2011/08/14'), 'D') FROM dual;
T
-
1
3. DDD 一年的第幾天
SQL> SELECT TO_CHAR(sysdate, 'DDD') FROM dual;
TO_
---
227
4. WW 一年的第幾週
SQL> SELECT TO_CHAR(sysdate, 'WW') FROM dual;
TO
--
33
5. W 一月的第幾週
SQL> SELECT TO_CHAR(sysdate, 'W') FROM dual;
T
-
3
6. YYYY/MM/DD HH24:MI:SS AM
YYYY 年
MM 月份
DD 日期
HH24/HH HH24表採24小時制
MI 分鐘
SS 秒數
AM/PM 顯示上/下午
SQL> SELECT TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS AM') FROM dual;
TO_CHAR(SYSDATE,'YYYY/
----------------------
2011/08/15 11:48:43 AM
SQL> SELECT TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS PM') FROM dual;
TO_CHAR(SYSDATE,'YYYY/
----------------------
2011/08/15 11:49:03 AM
7. J 顯示Juilan Day, BC 4712/01/01為1
SQL> SELECT TO_CHAR(sysdate, 'J') FROM dual;
TO_CHAR
-------
2455789
SQL> SELECT TO_CHAR(TO_DATE('2011/08/14'),'J') FROM dual;
TO_CHAR
-------
2455788
8. RR/MM/DD
公元 2000 問題
00-49 表下世紀
50-99 表本世紀
SQL> SELECT to_DATE('99/12/31','RR/MM/DD') FROM dual;
TO_DATE('9
----------
1999-12-31
SQL> SELECT TO_DATE('02/02/02','RR/MM/DD') FROM dual;
TO_DATE('0
----------
2002-02-02
SQL> SELECT TO_DATE('49/12/31','RR/MM/DD') FROM dual;
TO_DATE('4
----------
2049-12-31
SQL> SELECT TO_DATE('50/01/01','RR/MM/DD') FROM dual;
TO_DATE('5
----------
1950-01-01

字串的開始位置是1
字串函數傳回字串值
CHR, CONCAT, INITCAP, LOWER, LPAD, LTRIM, REPLACE, RPAD, RTRIM, SUBSTR, TRANSLATE, UPPER
字串函數傳回數字值
ASCII, INSTR, LENGTH

Oracle內建常用數字函數：
CEIL, FLOOR, ROUND, TRUNC, ABS, MOD.

Oracle SQL*PLUS 環境指令常應用於shell script撰寫時，實在非常有用。
SET：設定目前SQL*PLUS使用環境
SHOW：察看目前SQL*PLUS使用環境
STORE：儲存目前SQL*PLUS使用環境
STORE SET finename.sql [Create|Replace|append]
SET ECHO OFF (可壓抑start, @執行時，顯示SQL指令)
SET FEEDBACK OFF (不return查詢筆數)
SET HEADING OFF (不顯示column Heading)
SET LINESIZE 1024 (設定紀錄長度最大顯示)
SET NEWPAGE 0 (不換頁)
SET PAGESIZE 0 (每頁長度)
SET SPACE 0 (設定欄位間顯示間格)
SET VERIFY OFF (不顯示置換SQL指令)

SQL*PLUS操作應用
VARIABLE: Define sql*plus bind variable。
Note: 變數只能被用於PL/SQL Block中，且使用定義的變數，須加前置":"。
PRINT: Print out sql*plus bind variable
定義變數可用的資料類別：
NUMBBER
CHAR, CHAR(n): n = 1~255
VARCHAR2(n): n = 1~2000
REFCURSOR: Is a reference PL/SQL Cursor variable.
Sample:
$ sqlplus youruser/yourpass@yourdb;
SQL> var ss number
SQL> var aa varchar2(10)
SQL> var bb varchar2(10)
SQL> BEGIN (進入 anonymous PL/SQL Block)
2 :aa := 'Test';
3 :bb := '20110101';
4 :ss := pk_seq_pool.getSeqNo(:aa, :bb);
5 END ;
6 /
PL/SQL procedure successfully completed.
SQL> PRINT (顯示出在sqlplus定義變數內容，也可設定set autoprint on，這樣procedure執行完後會自動顯示內容)
SS
----------
1
BB
--------------------------------
20110101
AA
--------------------------------
Test
SQL> quit;
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL查詢時，一個資料表的某欄位可能會有多相同值的情況，此時透過SELECT的關鍵字 DISTINCT 來去除重複的紀錄，相同資料只顯示一筆紀錄。
測試資料： 資料表 UsrData
ID    NAME    CITY
----------------------------------------
01    張三    台北
02    李四    高雄
03    王五    台北
04    老六    台中
05    七七    台中
一般查詢：
SELECT city FROM UsrData;
結果：
台北
高雄
台北
台中
台中
使用關鍵字查詢：
SELECT DISTINCT city FROM UsrData;
結果：
台北
高雄
台中
查詢結果去除重複性資料，單一資料一樣會列出顯示。