關於Windows Oracle「ORA-12638 Credential retrieval failed」

1. 發生狀況：在資料庫(Server：Oracle 10g / Windows)進行抄寫時，出現「ORA-12638:Crendential retrieval failed(證明資料擷取失敗)」錯誤訊息。

2.說明：

Client端無法登入到遠端Server時，就會出現該狀況。

3.可能原因：
Server有開放OS認證模式，已知在Windows機器上，容易導致client端抄寫失敗。

4.解決：
(1)登入到抄寫的Server(也就是提供資料的Oracle 10g機器)上的Windows作業系統。
(2)編輯「%Oracle_HOME%\product\10.1.0\db_1\NETWORK\ADMIN\sqlnet.ora」檔案，做如下方框內的修正：

# This file is actually generated by netca. But if customers choose to # install "Software Only", this file wont exist and without the native # authentication, they will not be able to connect to the database on NT. #SQLNET.AUTHENTICATION_SERVICES = (NTS) --> 註解這一行，取消OS認證！ NAMES.DIRECTORY_PATH= (TNSNAMES)

(3)修改完成後存檔離開。離開後，可以再試試看DB抄寫，應該要能正常運作！

查看那些 table被lock

之前的工作從來沒有遇過 table 被 lock住, 但是到了新的環境居然table三天兩頭被lock, 我猜是ap開發習慣差異導致, anyway 以下的 sql是查看那些 table被lock的語法

select  

   oracle_username  os_user_name, 

   locked_mode, 

   object_name, 

   object_type

from v$locked_object a,dba_objects b

where a.object_id = b.object_id

DBA

• Clear the Shared Pool及ORA-04031
• 查看那些 table被lock
• 關於Windows Oracle「ORA-12638 Credential retrieval failed」
• Oracle 匯出/匯入 exp/imp

• ORA-02069: global_names parameter must be set to TRUE for this operation

  
   

Clear the Shared Pool及ORA-04031

有無清除Oracle先前Select的方法，

想用來評估新的SQL語句Perfromance優劣。

答案是有的，清空Shared Pool，語法如下：

ALTER SYSTEM FLUSH SHARED_POOL;

這通常也就是被拿來做Performance分析。

另外一個用途是當發生ORA-04031時，先拿來應急，

再慢慢找出根本原因及解決之道。

oracle 日期常用函數 (SYSDATE、日期格式)

SYSDATE
--◎ 可得到目前系統的時間 
     
 ex.  
   select sysdate from dual;
        
   sysdate  
   ----------  
   20-SEP-07
  
常用之日期格式

日期格式                 說明
------------------------------------------------------------------------
YYYY/MM/DD              -- 年/月/日
YYYY                    -- 年(4位)
YYY                     -- 年(3位)
YY                      -- 年(2位)
MM                      -- 月份
DD                      -- 日期
D                       -- 星期
                           -- 星期日 = 1  星期一 = 2 星期二 = 3
                       -- 星期三 = 4  星期四 = 5 星期五 = 6 星期六 = 7
                        
DDD                     -- 一年之第幾天
WW                      -- 一年之第幾週
W                       -- 一月之第幾週
YYYY/MM/DD HH24:MI:SS   -- 年/月/日 時(24小時制):分:秒
YYYY/MM/DD HH:MI:SS     -- 年/月/日 時(非24小時制):分:秒
J                       -- Julian day，Bc 4712/01/01 為1
RR/MM/DD                -- 公元2000問題
  　　　　　　　　　　　　-- 00-49 = 下世紀；50-99 = 本世紀
ex.
select to_char(sysdate,'YYYY/MM/DD') FROM DUAL;             -- 2007/09/20
select to_char(sysdate,'YYYY') FROM DUAL;                   -- 2007
select to_char(sysdate,'YYY') FROM DUAL;                    -- 007
select to_char(sysdate,'YY') FROM DUAL;                     -- 07
select to_char(sysdate,'MM') FROM DUAL;                     -- 09
select to_char(sysdate,'DD') FROM DUAL;                     -- 20
select to_char(sysdate,'D') FROM DUAL;                      -- 5
select to_char(sysdate,'DDD') FROM DUAL;                    -- 263
select to_char(sysdate,'WW') FROM DUAL;                     -- 38
select to_char(sysdate,'W') FROM DUAL;                      -- 3
select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') FROM DUAL;  -- 2007/09/20 15:24:13
select to_char(sysdate,'YYYY/MM/DD HH:MI:SS') FROM DUAL;    -- 2007/09/20 03:25:23
select to_char(sysdate,'J') FROM DUAL;                      -- 2454364
select to_char(sysdate,'RR/MM/DD') FROM DUAL;               -- 07/09/20

最大值 MAX()

MAX() 函數來計算一個欄位的最大值。計算最大值的語法是：

SELECT MAX("欄位名")
FROM "表格名"

舉例來說，若要由我們的範例表格中求出 Sales 欄位的最大值，

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

我們就鍵入，

SELECT MAX(Sales) FROM Store_Information

結果:

MAX(Sales)

$1500

$1500 代表所有 Sales 欄位 ($1500 、$250 、$300 、$700) 內的最大值。

最小值 MIN()

MIN() 函數來計算一個欄位的最小值。計算最大值的語法是：

SELECT MIN("欄位名")
FROM "表格名"

舉例來說，若我們要由我們的範例表格中求出 Sales 欄位的最小值，

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

我們就鍵入，

SELECT MIN(Sales) FROM Store_Information

結果:

MIN(Sales)

$200

$200 代表所有 Sales 欄位 ($1500 、$250 、$300 、$700) 內的最小值。

總合 SUM()

SUM() 函數來計算一個欄位的總合。計算總合的語法是：

SELECT SUM("欄位名")
FROM "表格名"

舉例來說，若我們要由我們的範例表格中求出 Sales 欄位的總合，

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

我們就鍵入，

SELECT SUM(Sales) FROM Store_Information

結果:

SUM(Sales)

$2750

$2750 代表所有 Sales 欄位 ($1500 、$250 、$300 、$700) 的總合。

平均值 AVG()

AVG() 函數來計算平均值。計算平均值的語法是：

SELECT AVG("欄位名")
FROM "表格名"

舉例來說，若我們要由我們的範例表格中求出 Sales 欄位的平均值，

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

我們就鍵入，

SELECT AVG(Sales) FROM Store_Information

結果:

AVG(Sales)

$678.5

$678.5 代表所有 Sales 欄位的平均值： ($1500 + $250 + $300 + $700) / 4。

CONCAT 及 ||

有的時候，我們有需要將由不同欄位獲得的資料串連在一起。

CONCAT() 的語法如下：

CONCAT(字串1, 字串2, 字串3, ...): 將字串1、字串2、字串3，等字串連在一起。請注意，Oracle的CONCAT()只允許兩個參數；換言之，一次只能將兩個字串串連起來。不過，在Oracle中，我們可以用'||'來一次串連多個字串。

來看一個例子。假設我們有以下的表格：

Geography 表格

region_name	store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

例子1:

MySQL/Oracle:
SELECT CONCAT(region_name,store_name) FROM Geography
WHERE store_name = 'Boston';

結果：

'EastBoston'

例子2:

Oracle:
SELECT region_name || ' ' || store_name FROM Geography
WHERE store_name = 'Boston';

結果：

'East Boston'

Replace

Replace函數是用來改變一個字串的內容。這個函數的語法如下：

Replace(str1, str2, str3): 在字串 str1 中，當 str2 出現時，將其以 str3 替代。

舉個例子。假設我們有以下的表格：

Geography 表格

region_name	store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

以下的 Replace 函數，

SELECT REPLACE(region_name, 'ast', 'astern')
FROM Geography;

會獲得如下的結果：

region_name

Eastern

Eastern

West

West

如何關/閉 table下所有TRIGGERS?

ALTER TABLE TABLE_NAME  DISABLE ALL TRIGGERS;

ALTER TABLE TABLE_NAME  ENABLE ALL TRIGGERS;

substring

SQL 中的 substring 函數是用來抓出一個欄位資料中的其中一部分。這個函數的名稱在不同的資料庫中不完全一樣：

• MySQL: SUBSTR(), SUBSTRING()
• Oracle: SUBSTR()
• SQL Server: SUBSTRING()

最常用到的方式如下 (在這裡我們用SUBSTR()為例)：

SUBSTR(str,pos): 由<str>中，選出所有從第<pos>位置開始的字元。請注意，這個語法不適用於SQL Server上。

SUBSTR(str,pos,len): 由<str>中的第<pos>位置開始，選出接下去的<len>個字元。

假設我們有以下的表格：

Geography 表格

region_name	store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

例1:

SELECT SUBSTR(store_name, 3)
FROM Geography
WHERE store_name = 'Los Angeles';

結果:

's Angeles'

例2:

SELECT SUBSTR(store_name,2,4)
FROM Geography
WHERE store_name = 'San Diego';

結果:

'an D'

自我連接Self-join

 

同 一 個 表 格 連 接 到 自 己 ， 這 就 稱 為 「 自 我 連 接 」 (Self-join) 。假 設 你 想 知 道 每 個 僱 員 的 上 司 名 稱 ， 你 可 以 把 EMP 進 行 自 我 連 接 ：

select  EMP.EMPLOYEEID  ,EMP.CHN_NAME ,EMP.TITLE   ,
EMP.MANAGER_ID 
from EMPLOYEES EMP

EMPLOYEEID	CHN_NAME	TITLE	MANAGER_ID
1	張瑾雯	業務	2
2	陳季暄	業務經理	5
3	趙飛燕	業務	2
4	林美麗	業務	1
5	劉天王	業務經理	13
6	黎國明	業務	5
7	郭國臹	業務	5
8	蘇涵蘊	業務主管	2
9	孟庭亭	業務	5
12	賴俊良	資深工程師	2
13	何大樓	助手	1
14	王大德	工程師	2

select  EMP.EMPLOYEEID "員工編號",EMP.CHN_NAME "員工姓名",EMP.TITLE "員工職稱",'---->',
MANAGER.EMPLOYEEID "所屬主管編號",MANAGER.CHN_NAME "所屬主管姓名",MANAGER.TITLE "所屬主管職稱"
from EMPLOYEES EMP,EMPLOYEES MANAGER 
where  EMP.MANAGER_ID = MANAGER.EMPLOYEEID(+)

 

員工編號	員工姓名	員工職稱	---->'	所屬主管編號	所屬主管姓名	所屬主管職稱
13	何大樓	助手	---->	1	張瑾雯	業務
4	林美麗	業務	---->	1	張瑾雯	業務
14	王大德	工程師	---->	2	陳季暄	業務經理
12	賴俊良	資深工程師	---->	2	陳季暄	業務經理
8	蘇涵蘊	業務主管	---->	2	陳季暄	業務經理
3	趙飛燕	業務	---->	2	陳季暄	業務經理
1	張瑾雯	業務	---->	2	陳季暄	業務經理
9	孟庭亭	業務	---->	5	劉天王	業務經理
7	郭國臹	業務	---->	5	劉天王	業務經理
6	黎國明	業務	---->	5	劉天王	業務經理
2	陳季暄	業務經理	---->	5	劉天王	業務經理
5	劉天王	業務經理	---->	13	何大樓	助手

SQL 算排名

列出每一行的排名是一個常見的需求，可惜 SQL 並沒有一個很直接的方式達到這個需求。要以 SQL 列出排名，基本的概念是要做一個表格自我連結 (self join)，將結果依序列出，然後算出每一行之前 (包含那一行本身) 有多少行數。這樣講讀者聽得可能有點困惑，所以最好的方式是用一個實例來介紹。假設我們有以下的表格：

Total_Sales 表格

Name	Sales
John	10
Jennifer	15
Stella	20
Sophia	40
Greg	50
Jeff	20

要找出每一行的排名，我們就打入以下的 SQL 語句：

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

結果:

Name	Sales	Sales_Rank
Greg	50	1
Sophia	40	2
Stella	20	3
Jeff	20	3
Jennifer	15	5
John	10	6

我們先來看 WHERE 子句。在字句的第一部分 (a1.Sales <= a2.Sales)，我們算出有多少筆資料 Sales 欄位的值是比自己本身的值小或是相等。如果在 Sales 欄位中沒有同樣大小的資料，那這部分的 WHERE 子句本身就可以產生出正確的排名。

子句的第二部分，(a1.Sales=a2.Sales and a1.Name = a2.Name)，則是讓我們在 Sales 欄位中有同樣大小的資料時 (像 Stella 及 Jeff 這兩筆資料)，仍然能夠產生正確的排名。

EXISTS

基本上， EXISTS 是用來測試內查詢有沒有產生任何結果。如果有的話，系統就會執行外查詢中的 SQL。若是沒有的話，那整個 SQL 語句就不會產生任何結果。

EXISTS 的語法是：

SELECT "欄位1"
FROM "表格1"
WHERE EXISTS
(SELECT *
FROM "表格2"
WHERE [條件])

在內查詢中，我們並不一定要用 * 來選出所有的欄位。我們也可以選擇表格2中的任何欄位。這兩種做法最後的結果是一樣的。

來看一個例子。假設我們有以下的兩個表格：

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

Geography 表格

region_name	store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

而我們打入的 SQL 是:

SELECT SUM(Sales) FROM Store_Information
WHERE EXISTS
(SELECT * FROM Geography
WHERE region_name = 'West')

我們會得到以下的答案：

SUM(Sales)

2750

乍看之下，這個答案似乎不太正確，因為內查詢有包含一個 [region_name = 'West'] 的條件，可是最後的答案並沒有包含這個條件。實際上，這並沒有問題。在這個例子中，內查詢產生了超過一筆的資料，所以 EXISTS 的條件成立，所以外查詢被執行。而外查詢本身並沒有包含 [region_name = 'West'] 這個條件。

基本語法

程式碼

DECLARE V_STRING VARCHAR2(20); V_DIAMETER NUMBER(5, 2); V_CIRCUMFERENCE NUMBER(5, 2); V_RADIUS NUMBER(5, 2) := 5; C_PI CONSTANT NUMBER(3,2) := 3.14; V_COUNTER NUMBER(5); BEGIN -- THIS IS SINGLE-LINE COMMENT /* THIS IS MULTI-LINE COMMENT */ V_DIAMETER := 9; V_CIRCUMFERENCE := V_DIAMETER * C_PI; DBMS_OUTPUT.PUT_LINE('V_CIRCUMFERENCE = ' || V_CIRCUMFERENCE); IF V_RADIUS IS NULL THEN DBMS_OUTPUT.PUT_LINE('V_RADIUS IS NULL.'); ELSIF V_RADIUS > 9 THEN DBMS_OUTPUT.PUT_LINE('V_RADIUS IS GREATER THAN 9.'); ELSIF V_RADIUS < 9 THEN DBMS_OUTPUT.PUT_LINE('V_RADIUS IS LESS THAN 9.'); ELSE DBMS_OUTPUT.PUT_LINE('V_RADIUS IS EQUAL TO 9.'); END IF; V_COUNTER := 1; LOOP EXIT WHEN V_COUNTER >= 6; DBMS_OUTPUT.PUT_LINE('LOOP: V_COUNTER = ' || V_COUNTER); V_COUNTER := V_COUNTER + 1; END LOOP; V_COUNTER := 1; WHILE (V_COUNTER <= 5) LOOP DBMS_OUTPUT.PUT_LINE('WHILE LOOP: V_COUNTER = ' || V_COUNTER); V_COUNTER := V_COUNTER + 1; EXIT WHEN V_COUNTER >= 9; END LOOP; FOR V_LOCAL_COUNTER IN 1 .. 5 LOOP DBMS_OUTPUT.PUT_LINE('FOR LOOP: V_LOCAL_COUNTER = ' || V_LOCAL_COUNTER); END LOOP; FOR V_LOCAL_COUNTER IN REVERSE 1 .. 5 LOOP DBMS_OUTPUT.PUT_LINE('FOR LOOP REVERSE: V_LOCAL_COUNTER = ' || V_LOCAL_COUNTER); END LOOP; END; /

 

執行結果

V_CIRCUMFERENCE = 28.26 V_RADIUS IS LESS THAN 9. LOOP: V_COUNTER = 1 LOOP: V_COUNTER = 2 LOOP: V_COUNTER = 3 LOOP: V_COUNTER = 4 LOOP: V_COUNTER = 5 WHILE LOOP: V_COUNTER = 1 WHILE LOOP: V_COUNTER = 2 WHILE LOOP: V_COUNTER = 3 WHILE LOOP: V_COUNTER = 4 WHILE LOOP: V_COUNTER = 5 FOR LOOP: V_LOCAL_COUNTER = 1 FOR LOOP: V_LOCAL_COUNTER = 2 FOR LOOP: V_LOCAL_COUNTER = 3 FOR LOOP: V_LOCAL_COUNTER = 4 FOR LOOP: V_LOCAL_COUNTER = 5 FOR LOOP REVERSE: V_LOCAL_COUNTER = 5 FOR LOOP REVERSE: V_LOCAL_COUNTER = 4 FOR LOOP REVERSE: V_LOCAL_COUNTER = 3 FOR LOOP REVERSE: V_LOCAL_COUNTER = 2 FOR LOOP REVERSE: V_LOCAL_COUNTER = 1

HELLO

SET SERVEROUTPUT ON

DECLARE   V_STRING VARCHAR(200); BEGIN   V_STRING := 'HELLO';   DBMS_OUTPUT.PUT_LINE(V_STRING); END; /

Oracle Personal Edition 安裝

http://www.oracle.com/technology/software/products/8i_personal/index.html

GROUP BY "欄位1" HAVING (函數條件)

舉例來說，我們可能只需要知道哪些店的營業額有超過 $1,500。在這個情況下，我們不能使用 WHERE 的指令。那要怎麼辦呢？很幸運地，SQL 有提供一個 HAVING 的指令，而我們就可以用這個指令來達到這個目標。 HAVING 子句通常是在一個 SQL 句子的最後。一個含有 HAVING 子句的 SQL 並不一定要包含 GROUP BY 子句。HAVING 的語法如下：

SELECT "欄位1", SUM("欄位2")
FROM "表格名"
GROUP BY "欄位1"
HAVING (函數條件)

請讀者注意： 如果被 SELECT 的只有函數欄， 那就不需要 GROUP BY 子句。

在我們 Store_Information 表格這個例子中,

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

若我們要找出 Sales 大於 $1,500 的 store_name，我們就鍵入，

SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500

結果:

store_name	SUM(Sales)
Los Angeles	$1800

GROUP BY "欄位1"

記得我們用 SUM 這個指令來算出所有的 Sales (營業額)吧！如果我們的需求變成是要算出每一間店 (store_name) 的營業額 (sales)，那怎麼辦呢？在這個情況下，我們要做到兩件事：第一，我們對於 store_name 及 Sales 這兩個欄位都要選出。第二，我們需要確認所有的 sales 都要依照各個 store_name 來分開算。這個語法為：

SELECT "欄位1", SUM("欄位2")
FROM "表格名"
GROUP BY "欄位1"

在我們的範例上，

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

我們就鍵入，

SELECT store_name, SUM(Sales)
FROM Store_Information
GROUP BY store_name

結果:

store_name	SUM(Sales)
Los Angeles	$1800
San Diego	$250
Boston	$700

當我們選不只一個欄位，且其中至少一個欄位有包含函數的運用時，我們就需要用到 GROUP BY 這個指令。在這個情況下，我們需要確定我們有 GROUP BY 所有其他的欄位。換句話說，除了有包括函數的欄位外，我們都需要將其放在 GROUP BY 的子句中。

Length(str): 找出 str 字串的長度

在 SQL 中，長度函數是用來找出一個字串的長度。在不同的資料庫中不完全一樣：

• MySQL: LENGTH()
• Oracle: LENGTH()
• SQL Server: LEN()

長度函數的用法如下：

Length(str): 找出 str 字串的長度。

來看看幾個例子。假設我們有以下的表格：

Geography 表格

region_name	store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

例 1：

SELECT Length(store_name)
FROM Geography
WHERE store_name = 'Los Angeles';

結果:

11

例 2：

SELECT region_name, Length(region_name)
FROM Geography;

結果:

region_name	Length(region_name)
East	4
East	4
West	4
West	4

DUAL 表 格

 

SQL> SELECT 1 + 2 FROM DUAL;

       1+2
----------
         3

因 為 每 個 SELECT 句 子 都 必 需 有 FROM 子 句 ， 所 以 Oracle 就 做 個 DUAL 表 格 來 符 合 這 個 守 則 了 。

DUAL 表 格 還 可 以 用 來 提 供 一 些 資 訊 ， 例 如 使 用 者 名 稱 、 系 統 時 間 等 。

SQL> SELECT USER FROM DUAL;

USER
------------------------------
SCOTT

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
03-NOV-02

SELECT CASE

CASE 是 SQL 用來做為 if-then-else 之類邏輯的關鍵字。 CASE 的語法如下：

SELECT CASE ("欄位名")
  WHEN "條件1" THEN "結果1"
  WHEN "條件2" THEN "結果2"
  ...
  [ELSE "結果N"]
  END
FROM "表格名"

"條件" 可以是一個數值或是公式。 ELSE 子句則並不是必須的。

在我們的 Store_Information 中

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

若我們要將 'Los Angeles' 的 Sales 數值乘以2，以及將 'San Diego' 的 Sales 數值乘以1.5，我們就鍵入以下的 SQL：

SELECT store_name, CASE store_name
  WHEN 'Los Angeles' THEN Sales * 2
  WHEN 'San Diego' THEN Sales * 1.5
  ELSE Sales
  END
"New Sales",
Date
FROM Store_Information

"New Sales" 是用到 CASE 那個欄位的欄位名。

結果:

store_name	New Sales	Date
Los Angeles	$3000	Jan-05-1999
San Diego	$375	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

SUBSTRING 函數

substring 函數是用來抓出一個欄位資料中的其中一部分。這個函數的名稱在不同的資料庫中不完全一樣：

• MySQL: SUBSTR(), SUBSTRING()
• Oracle: SUBSTR()
• SQL Server: SUBSTRING()

最常用到的方式如下 (在這裡我們用SUBSTR()為例)：

SUBSTR(str,pos): 由<str>中，選出所有從第<pos>位置開始的字元。請注意，這個語法不適用於SQL Server上。

SUBSTR(str,pos,len): 由<str>中的第<pos>位置開始，選出接下去的<len>個字元。

假設我們有以下的表格：

Geography 表格

region_name	store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

例1:

SELECT SUBSTR(store_name, 3)
FROM Geography
WHERE store_name = 'Los Angeles';

結果:

's Angeles'

例2:

SELECT SUBSTR(store_name,2,4)
FROM Geography
WHERE store_name = 'San Diego';

結果:

'an D'

Function

1. Length(str): 找出 str 字串的長度
2. to_date
3. to_char
4. Replace函數
5. SUBSTRING 函數
6. SELECT CASE
7. 平均值 AVG()
8. 總合 SUM()
9. 最大值 MAX()
10. 最小值 MIN()
11. substring
12. Replace
13. CONCAT 及 ||

14. oracle 日期常用函數 (SYSDATE、日期格式)

PL/SQL Developer

• 如何看DBMS_OUTPUT
• 基本語法
• 從StoreProc中尋找程式
• HELLO
• 如何關/閉 table下所有TRIGGERS?

• ORA-02069: global_names parameter must be set to TRUE for this operation

  
   

SQL 語法

1. select 指令

2. UPDATE

3. DELETE

4. INSERT

5. UNION 與 UNION ALL

6. TRIGGER--記下資料被異動時間

7. SQL Order By

8. GROUP BY "欄位1"

9. GROUP BY "欄位1" HAVING (函數條件)

10. MINUS

11. 將撈出來資料以新的table來存放或存放在指定的table中!!

12. DUAL 表 格

13. EXISTS

14. 自我連接Self-join

15. SQL 算排名

UPDATE

UPDATE "表格名"
SET "欄位1" = [新值]
WHERE {條件}

最容易瞭解這個語法的方式是透過一個例子。假設我們有以下的表格：

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

我們發現說 Los Angeles 在 01/08/1999 的營業額實際上是 $500，而不是表格中所儲存的 $300，因此我們用以下的 SQL 來修改那一筆資料：

UPDATE Store_Information
SET Sales = 500
WHERE store_name = "Los Angeles"
AND Date = "Jan-08-1999"

現在表格的內容變成：

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$500	Jan-08-1999
Boston	$700	Jan-08-1999

在這個例子中，只有一筆資料符合 WHERE 子句中的條件。如果有多筆資料符合條件的話，每一筆符合條件的資料都會被修改的。

我們也可以同時修改好幾個欄位。這語法如下：

UPDATE "表格"
SET "欄位1" = [值1], "欄位2" = [值2]
WHERE {條件}

DELETE

在某些情況下，我們會需要直接由資料庫中去除一些資料。這可以藉由 DELETE FROM 指令來達成。它的語法是：

DELETE FROM "表格名"
WHERE {條件}

以下我們用個實例說明。假設我們有以下這個表格：

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

而我們需要將有關 Los Angeles 的資料全部去除。在這裡我們可以用以下的 SQL 來達到這個目的:

DELETE FROM Store_Information
WHERE store_name = "Los Angeles"

現在表格的內容變成：

Store_Information 表格

store_name	Sales	Date
San Diego	$250	Jan-07-1999
Boston	$700	Jan-08-1999

INSERT

INSERT INTO "表格名" ("欄位1", "欄位2", ...)
VALUES ("值1", "值2", ...)

假設我們有一個架構如下的表格：

Store_Information 表格

Column Name	Data Type
store_name	char(50)
Sales	float
Date	datetime

而我們要加以下的這一筆資料進去這個表格：在 January 10, 1999，Los Angeles 店有 $900 的營業額。我們就打入以下的 SQL 語句：

INSERT INTO Store_Information (store_name, Sales, Date)
VALUES ('Los Angeles', 900, 'Jan-10-1999')

第二種 INSERT INTO 能夠讓我們一次輸入多筆的資料。跟上面剛的例子不同的是，現在我們要用 SELECT 指令來指明要輸入表格的資料。如果您想說，這是不是說資料是從另一個表格來的，那您就想對了。一次輸入多筆的資料的語法是：

INSERT INTO "表格1" ("欄位1", "欄位2", ...)
SELECT "欄位3", "欄位4", ...
FROM "表格2"

以上的語法是最基本的。這整句 SQL 也可以含有 WHERE、 GROUP BY、及 HAVING 等子句，以及表格連接及別名等等。

舉例來說，若我們想要將 1998 年的營業額資料放入 Store_Information 表格，而我們知道資料的來源是可以由 Sales_Information 表格取得的話，那我們就可以鍵入以下的 SQL：

INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998

在這裡，我用了 SQL Server 中的函數來由日期中找出年。不同的資料庫會有不同的語法。舉個例來說，在 Oracle 上，您將會使用 WHERE to_char(date,'yyyy')=1998。

UNION 指令

UNION

UNION 指令的目的是將兩個 SQL 語句的結果合併起來。從這個角度來看， UNION 跟 JOIN 有些許類似，因為這兩個指令都可以由多個表格中擷取資料。 UNION 的一個限制是兩個 SQL 語句所產生的欄位需要是同樣的資料種類。另外，當我們用 UNION 這個指令時，我們只會看到不同的資料值 (類似 SELECT DISTINCT)。

UNION 的語法如下：

[SQL 語句 1]
UNION
[SQL 語句 2]

假設我們有以下的兩個表格，

Store_Information 表格 store_name Sales Date Los Angeles $1500 Jan-05-1999 San Diego $250 Jan-07-1999 Los Angeles $300 Jan-08-1999 Boston $700 Jan-08-1999

Internet_Sales 表格 Date Sales Jan-07-1999 $250 Jan-10-1999 $535 Jan-11-1999 $320 Jan-12-1999 $750

而我們要找出來所有有營業額 (sales) 的日子。要達到這個目的，我們用以下的 SQL 語句：

SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales

結果:

Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

有一點值得注意的是，如果我們在任何一個 SQL 語句 (或是兩句都一起) 用 "SELECT DISTINCT Date" 的話，那我們會得到完全一樣的結果。

UNION ALL

UNION ALL 這個指令的目的也是要將兩個 SQL 語句的結果合併在一起。 UNION ALL 和 UNION 不同之處在於 UNION ALL 會將每一筆符合條件的資料都列出來，無論資料值有無重複。

UNION ALL 的語法如下：

[SQL 語句 1]
UNION ALL
[SQL 語句 2]

我們用和上一頁同樣的例子來顯示出 UNION ALL 和 UNION 的不同。同樣假設我們有以下兩個表格，

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

Internet_Sales 表格

Date	Sales
Jan-07-1999	$250
Jan-10-1999	$535
Jan-11-1999	$320
Jan-12-1999	$750

而我們要找出有店面營業額以及網路營業額的日子。要達到這個目的，我們用以下的 SQL 語句：

SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales

結果:

Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-08-1999

Jan-07-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

如何看DBMS_OUTPUT

如何看DBMS_OUTPUT
set   serveroutput   on;   
begin    
  dbms_output.put_line('hello!');   
end;