＜Oracle database 10gSQL＞開發指南筆記

1. Oracle中的函數

函數可以進行組合，如：select name UPPER(SUBSTR(name, 2, 8)) ...
1) 單行函數
  字元函數、數字函數、轉換函式、日期函數、Regex函數(10g)
  轉換函式就是從一種類型轉換為另一種資料類型的函數。
2) 彙總函式
彙總函式同時對一組行進行操作，對每組行返回一行輸出結果。
AVG/COUNT/MAX/MEDIAN/MIN/STDDEV/SUM/VARIANCE

 

2. 日期時間的儲存與處理

可以由DATE類型儲存時間值。
使用時間戳(timestamp)，時間戳記可以儲存一個特定的日期和時間。與DATE相比，它的優點是可以儲存帶有小數位的秒，以及能儲存時區。
使用時間間隔(interval)，可以儲存時間的長度。
TO_DATE()和TO_CHAR()可在時間值和字串之間進行轉換。
TO_CHAR(x [, format]) TO_DATE(x [, format]) format為格式控制
時間值函數：
ADDMONTH(x, y)
LAST_DAY(x)
MONTHS_BETWEEN(x,y)
ROUND(x [, unit])
SYSDATE()
TRUNC(x[, unit])
與時區有關的函數：
CURRENT_DATE()
DBTIMEZONE()
NEW_TIME(x, time_zone1, time_zone2)
SESSIONTIMEZONE()
TZ_OFFSET(time_zone)
與時間戳記相關的也有一些函數，如CURRENT_TIMESTAMP()等。
時間間隔也有幾個相應的函數。

 

3. SQL*Plus的使用

命令：a c clbuff del l r / x等來編輯操作緩衝區命令等。
儲存、檢索、運行：SAVE GET START @filename ED SPOOL SPOOL OFF
格式化列：COLUMN 清除列格式：COLUMN CLEAR
設定頁面大小：SET PAGESIZE
設定行大小：SET LINESIZE
使用變數
  臨時變數
使用字元 & 來定義臨時變數。sql語句中包含變數時，如果執行該語句，就會提示為該變數輸入一個值。
關於變數的一些控制命令：
SET VERIFY OFF|ON
SET DEFINE '#'
  已定義變數
可以在sql語句中多次使用已定義變數，直到顯示地將其刪除、重定義或退出SQL*Plus
使用DEFINE定義查看變數。(UNDEFINE刪除)
如：DEFINE product_id_var = 7
ACCEPT也可以定義變數，不過要等待使用者的輸入。
可以用SQL*Plus建立簡單報表。
在指令碼中可以通過 $1 $2 等來引用傳給指令碼的參數。
TTITLE BTITLE可以用來添加頁首頁尾。
自動產生SQL語句。通過輸出固定格式的查詢結果，這樣的結果就是可以直接使用的SQL語句。

 

4. 進階查詢

DECODE(value, search_value, result, default_value)
value與search_value進行比較，如果這兩個值相等，則返回result，否則就返回default_value。 DECODE()允許在SQL中執行if-then-else類型的邏輯處理。
CASE運算式，也可以在SQL中實現if-then-else型的邏輯，工作方式與DECODE類似。
簡單CASE運算式：
CASE search_expression
  WHEN expression1 THEN result1
  WHEN expression2 THEN result2
  ...
  WHEN expressionN THEN resultN
  ELSE default_result
END
搜尋CASE運算式：
CASE
  WHEN condition1 THEN result1
  ...
  WHEN conditionN THEN resultN
  ELSE default_result
END
在表中可以進行自引用，如：
REFERENCES table_name(name_id)
table_name為自身表明，name_id為表的某列
這樣表就是有層次的了，要進行層次化查詢，使用CONNECT BY和START WITH
可以使用偽列LEVEL來顯示節點在樹中的層次。
使用LEVEL和LPAD可以對層次化查詢結果進行格式化處理。
ROLLUP，是GROUP BY子句的一種擴充，可以為每個分組返回小計記錄。
CUBE，也是GROUP BY子句的擴充，可以返回每一個列組合的小計記錄，同時在末尾加上總計記錄。
GROUPING()函數，可以接受一列，返回0或1.如果列值為空白，那麼GROUPING返回1；如果列值非空，則返回0。
GROUPING SET可以只返回小計記錄。GROUPING_ID()可以藉助HAVING子句對記錄進行過濾，將不包含小計或者總計的記錄除去。GROUP_ID()，可以用於消除GROUP BY子句返回的重複
記錄。
資料庫中還有很多分析函數，如評級函數、視窗函數、報表函數等等……
ORACLE 10g 中的MODEL子句可以用來進行行間計算。

 

5. PL/SQL編程

塊結構：
[DECLARE
   declaration_statements
]
BEGIN
   executable_statements
[EXCEPTION
   exception_handling_statements
]
END;
在DECLARE部分可以聲明變數、遊標等。並且這種變數只能在塊內部訪問。
EXCEPTION部分是出現異常的時候被執行的代碼。
PL/SQL中可以有條件邏輯，如IF、THEN、ELSE、以及ENDIF等關鍵字。也有FOR和WHILE迴圈。
遊標的聲明方法為：
CURSOR cursor_name IS
  SELECT_statement;
在執行開啟遊標操作的時候，就會執行SELECT語句。OPEN cursor_name;
FETCH從遊標中取得記錄。
FETCH cursor_name
  INTO variable[, variable ...];
CLOSE cursor_name;關閉遊標。
可以通過CREATE PROCEDURE建立過程。(預存程序)
建立的過程可以被任何能夠訪問資料庫的程式所使用。
可以通過CALL語句來調用過程
函數與過程類似，惟一的區別是函數必須向調用它的語句返回一個值。 預存程序和函數有時合起一被稱為儲存子程式。
CREATE FUNCTION可以用於建立函數。
調用函數：
SELECT function_name(param)
FROM dual;
包：可以將過程和函數一起組織到包中，包可以將彼此相關的功能劃分到一個自包含的單元中。通過這種方式將PL/SQL代碼模組化，可以構建供其他編程人員重用的程式碼程式庫。
CREATE PACKAGE建立包規範。
CREATE PACKAGE BODY建立包體。
用SELECT FROM dual或CALL都可以調用包中的函數或過程，在函數或過程名之前加上包名限定。
包、函數、過程的資訊都可以從user_procedures視圖中擷取。

用DROP能刪除建立的包、函數、過程。
觸發器：當特定的SQL DML語句，如INSERT、UPDATE、DELETE等在特定的資料庫表上運行時，由資料庫自動啟動並執行過程。
觸發器可以在SQL語句運行之前和之後啟用。
語句級觸發器，行級觸發器。
CREATE TRIGGER建立觸發器。
在user_triggers視圖中可以獲得觸發器的資訊。
ALTER TRIGGER可以啟用或禁用觸發器。 DROP TRIGGER可以刪除觸發器。

 

6. 資料庫物件

CRATE TYPE可以建立對象
對象中可以包含函數，用MEMBER FUNCTION來聲明。
DESCRIBE可以擷取有關物件類型的資訊
在PL/SQL中也可以使用對象
物件類型可以被繼承，只要在CREATE TYPE的時候在最後指定 NOT FINAL，即表示可以被繼承。
如果某個對象僅用作超類，而且並不執行個體化，則可用NOT INSTANTIABLE來表示類不可被執行個體化。
可以自訂建構函式。

 

7. 集合

oracle8後引入了兩種新的資料庫類型，稱為集合(collection)，它允許儲存元素集合。
集合類型：
變長數組：一維的，有最大大小，在建立時設定。不過以後可以更改該大小。（CREATE TYPE來建立）
巢狀表格：嵌套在另一張表中的表。大小也沒有限制。（CREATE TYPE來建立）
關聯陣列：(10g新增) 關聯陣列是一個索引值對集合。類似於雜湊表。（CREATE PROCEDURE來建立）
集合也有一些函數，如COUNT、DELETE、EXISTS、EXTEND、FIRST、LAST、NEXT、PRIOR、TRIM等。

 

８. 大對象

在Oracle8之前，儲存大對象，必須用LONG或LONGRAW(二進位)。
LONG RAW和LONG最多可以儲存2GB資料，RAW只能儲存4KB位元據。
Oracle8之後可以使用大對象，大對象稱為LOB，最大可儲存128T資料。
LOB的四種類型：
CLOB 字元LOB，用來儲存字元
BLOB 二進位LOB，儲存二進位
BFILE 隱藏檔指標，檔案位於檔案系統中，即位於資料庫之外。
NCLOB 國家語言字元的LOB
在插入資料之前，大對象必須被初始化。可用EMPTY＿BLOB() EMPTY_CLOB()等來初始化
如：INSERT INTO mytable(id, clob_colom) VALUES ( 1, EMPTY_CLOB() ); 用UPDATE來更新其中的資料。
在使用BFILE之前，要先在資料庫中用CREATE DIRECTORY來建立一個目錄對象
使用方法：INSERT INTO bfile_content(id, bfile_colom) VALUES (1, BFILENAME('FILES_DIRS', 'text.txt') )
FILES_DIRS即是建立的目錄對象
在PL/SQL中使用大對象，有很多函數可以使用，如OPEN、READ、SUBSTR、APPEND、CLOSE等等…

 

９. SQL最佳化

1) 使用WHERE子句過濾行
2) 使用錶鏈接而不是多個查詢
3) 執行串連時使用完整列引用
4) 使用case運算式，而不是多個查詢
5) 添加表索引
6) 使用where而不是HAVING
7) 使用UNION ALL而不是UNION
8) 使用EXISTS而不是IN
9) 使用EXISTS而不是DISTINCT
10) 使用綁定變數