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

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;