Oracle PL/SQL Training

Ho ho ho ... 上週到蠻荒的內湖科學園區參加Oracle PL/SQL 教育訓練課程, 說真的, 學到蠻多東西的.

首先, 就是我對內湖科學園區的印象 ... 真是ooxx的荒涼.
拿咱們小布工作的地方來說, 方圓百尺內, 工地、大樓、馬路, 頂多一家7-11, 好可怕阿~ >_<
我看的都快哭了, 難怪他上次吃摩斯漢堡的時候泛著感激的淚光(有嗎? 我亂說的啦).
想找家餐廳吃午餐, 得慢步+散步個差不多以前我們在南港走出去吃飯的距離一樣遠(只是在內湖得在戶外風吹日曬雨淋)
餐廳不多, 到處都要等等等, 若要找巷子裡的自助餐廳, 你得自己多到巷弄走走才知道有沒有.

這還不打緊, 說到我同學工作的內湖路一段, 那才真神奇.
明明辦公大樓就在Benq附近, Benq是瑞光路, 但他們公司卻是內湖路一段xx巷, 害我在那邊繞圈圈老半天.
方圓百尺內, 工地、大樓、巷弄, 比小布他們公司那更荒涼, 要走更遠的距離才找得到餐廳.
我同學平日填飽肚子的地方就是"全家", 了不起吃個"萊爾富", 要是怕吃一堆防腐劑會變木乃伊, 那就改去吃健康有勁的"冰冰蒟蒻".
這到底是什麼人生啊?

更重要的是, 我在內湖迷路2天 ... >_<


其次, Oracle PL/SQL的課程, 是很概略式的說明PL/SQL是什麼東西、可以做什麼, 說穿了就是寫程式, 只是語法是Oracle PL/SQL (我好像在說廢話).
對我來說還蠻受用的, 以前常常會和同事討論在程式與資料庫面的SQL執行與效能, 多學一點總是好的(工程師的無奈? ^_^)

1. Tool
Tool: PL/SQL Developer : PL/SQL Developer 是一套整合性開發環境, 特別針對Oracle Databases 的 stored program units 的開發. 這比較偏重程式的開發，與TOAD相比較，TOAD 比較適合DBA人員使用

2. PL/SQL is part of Oracle Complete Solution.
It can improved performance with procedural processing.
It is portable over different version of database.(只是有些指令可能不適用)
You can program with procedural language control structures and declare program identifiers in database.
It can handle errors.

3. PL/SQL Block Structure.
不管你是寫function(可回傳值)還是procedure(無回傳值), 一定要有BEGIN與END;
你可以不宣告任何變數, 但BEGIN...END; 一定要有.

4. Types of Varaiables
Scalar (char, date, number, boolean...)
Composite (table, record, record of table, 這段我最弱, 搞不太清楚說)
Reference (ointer)
LOB(large objects) (clob, blob, bfile, nclob). CLOB-> character large objects; BLOB-> binary large objects; NCBLOB-> nation character large objects, 計算字數長度的方式和CBLOB不同. 如varchar2(4)可以放2個中文, 但nvarchar2(4)則可放4個中文.


5. 變數宣告
變數名稱 型態 是否為null := 預設值;
Declare
v_string varchar2(10) not null := 'hello'; --字串用單引號包起來
v_number number(2) := 10;
v_boolean boolean; --可以指定true, false, 或null
v_type emp.ename%TYPE; --指定v_type變數和emp資料表內的ename欄位型態一樣
%TYPE: 可把變數的型態指定和talbe.field資料型態一樣, 如 v_ename emp.ename%TYPE , v_name變數的資料型態和emp.ename一樣.

Non-PL/SQL Variables
可以在SQL command下宣告一個Non-PL/SQL的變數, 讓後面的PL/SQL使用, 或從AP傳variable給stored procedure使用.
如 SQL> VARIABLE g_monthly_num; , 然後在PL/SQL內用 :g_monthly_num 使用.

可以在SQL command下要求使用者輸入變數值, 讓PL/SQL使用.
如 SQL> ACCEPT p_num PROMP 'please input p_num'; , 然後在PL/SQL內用 &p_num 使用.

變數的scope, 端賴變數存在的位置.
在block內, 變數名稱不可和資料表內的欄位名稱一樣

6. DBMS_OUTPUT.PUT_LINE
Oracle提供的procedure, 可以把PL/SQL程序與結果印出在sql plus內(需與set serveroutput on搭配, 不然也看不到)

7. 註記: -- 或 /*...*/

8. Not available in procedure statements
- GREATEST
- LEAST
- DECODE
- Group functions

9. Data Type Conversion
- TO_CHAR
- TO_DATE
- TO_NUMBER

幾個測試的小範例
declare v_id number(3) :=10;
begin
DBMS_OUTPUT.put_line(v_id + 123);
end;
/
結果是133

但
declare v_id number(3) :=10;
begin
DBMS_OUTPUT.put_line(v_id + '123');
end;
也是133

declare v_id number(3) :=10;
begin
DBMS_OUTPUT.put_line(v_id + 'sss');
end;
ORA-06502: PL/SQL: 數字或值錯誤: 字元到數字轉換錯誤
ORA-06512: 在 line 3


declare v_id number(3) :=10;
begin
DBMS_OUTPUT.put_line(v_id || 123);
end;
是10123

declare v_id number(3) :=10;
begin
DBMS_OUTPUT.put_line(v_id || '123');
end;
是10123


10. SQL Statements in PL/SQL
Extract a row of data from the database by using the SELECT command. Only a single set of values can be returned.
Make changes to rows in the database by using DML commands.
Control a transaction with the COMMIT, ROLLBACK, or SAVEPOINT command.
Determine DML outcome with implicit cursors.

11. Manipluting Data Using PL/SQL
Make changes to database tables by using DML command:
INSERT
UPDATE
DELETE

12. SQL Cursors
A cursor is a private SQL work area.
There are 2 types of cursors: 顯式游標(Explicit Cursor)和隱式游標(Implicit Cursor). The Oracle server uses implicit cursors to parse execute your SQL statements. Explicit cursors are explicitly declared by the programmer.
Implicit cursor attributes verify the outcome of DML statements:
SQL%ROWCOUNT 傳回SQL語法影響的資料行數, 一整數值.
SQL%FOUND Boolean, 若SQL語法有影響一至多筆資料, 傳回True.
SQL%NOTFOUND Boolean, 若SQL語法沒有影響到任何行, 傳回True.
SQL%ISOPEN 總是為FALSE, 因為PL/SQL總是執行結束後, 立即關閉Implicit Cursor.
Explicit cursors are defined by the programmer.
如
Declare
v_empno emp.empno%type;
v_ename emp.ename%type;
cursor c1 is
select empno, ename from emp;
Begin
open c1;
for i in 1..10
loop
fetch c1 into v_empno, v_ename;
end loop;
close c1;
end;


13. Control Structures
IF...THEN...END IF
IF...THEN...ELSE...END IF
IF...THEN...ELSEIF...END IF
在判斷字串內容時, 大小寫有別

LOOP...EXIT [condition]; END LOOP;
FOR counter in [範圍]...LOOP...END LOOP; --沒有condition
WHILE condition LOOP...END LOOP;

14. Composite Data Types
說真的, 這邊我搞不太懂, 後補
PL/SQL Records --
PL/SQL Tables --
%ROWTYPE -- Declare a variable according to a collection of columns in a database table or view. Useful when retrieving a row with the SELECT statement. 如 declare emp_rec emp%ROWTYPE; Begin select * into emp_rec from emp where empno=100; end; 把找到的資料放到emp_rec內.


15. Handling Exception
有2種類型的, 一種是內部異常(Predefined Oracle Server Errors or Non-predefined Oracle Server Errors), 一種是使用者自訂的.
內部異常是執行期間傳回到PL/SQL端的ORACLE錯誤, 或由PL/SQL的操作引起的錯誤, 如除數為0等.
使用者自訂的異常是由Programmer自行定義的, 在PL/SQL中傳遞訊息以控制錯誤處理.

Predefined Oracle Server Errors: Sample predefined exceptions: NO_DATA_FOUND, TOO_MANY_ROWS, INVALID_CURSOR ...
Decalre ...
Begin
....
Exception
when NO_DATA_FOUND then
....
when OTHERS then'
...
End;

Non_Predified Oracle Server Errors: Code the PRAGMA EXCEPTION_INIT.
Declare e_e1 Exception;
PRAGMA EXCEPTION_INIT {e_e1, -2292};
Begin
...
Exception
when e_e1 then ...
END;

User Defined Errors: RAISE.
Declare e_e1 Exception;
Begin
...
If SQL%NOTFOUND then
RAISE e_e1;
end if;
...
Exception
when e_e1 then ...
END;

還有一個raise_application_error: 是為了防止PL/SQL返回不明確的未經處理的異常錯誤消息, 錯誤號碼範圍是-20,000到-20,999, 錯誤訊息最多2048個字.
IF product_not_found THEN
RAISE_APPLICATION_ERROR(-20123,'Invald product code' TRUE);
END IF;


16. Create Package
第一步, 先建立Package框架.
Create or Replace Package xxx is
Procedure p1; -- 這個package內有procedure p1
Procedure p2; -- 這個package內有procedure p2
end;
/

第二步, 建立Package xxx的body.
Create or Replace Package Body xxx is
procedure p1 as
begin ... end;
procedure p2 as
begin ... end;
end;
/


懇請各位大德給點意見, 要是有寫錯, 請賜教 ... Orz