SQL語法 | PL/SQL | Function | TRIGGER | StoreProc | DBA

2009年12月28日 星期一

關於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抄寫,應該要能正常運作!

2009年12月18日 星期五

查看那些 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

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

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

答案是有的,清空Shared Pool,語法如下:

ALTER SYSTEM FLUSH SHARED_POOL;

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

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

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

2009年12月17日 星期四

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

2009年10月16日 星期五

最大值 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;

2009年9月16日 星期三

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'

2009年9月4日 星期五

自我連接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'] 這個條件。

2009年8月25日 星期二

基本語法

程式碼

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

2009年8月16日 星期日

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 算排名

2009年8月6日 星期四

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 也可以含有 WHEREGROUP 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。

2009年8月5日 星期三

UNION 指令

UNION

UNION 指令的目的是將兩個 SQL 語句的結果合併起來。從這個角度來看, UNIONJOIN 有些許類似,因為這兩個指令都可以由多個表格中擷取資料。 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 ALLUNION 不同之處在於 UNION ALL 會將每一筆符合條件的資料都列出來,無論資料值有無重複。

UNION ALL 的語法如下:

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

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

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;