基本語法

程式碼

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 的子句中。