Q: What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

[類別] PL/SQL
[問題] 什麼是SQLCODE與SQLERRM，為什麼他們對PL/SQL開發者而言很重要?

Answer:

SQLCODE與SQLERRM都是用在exception handler裡。

SQLCODE會回傳exception的號碼，SQLERRM則是錯誤訊息內容。
最常見的用法就是搭配WHER OTHERS EXCEPTION使用:

DECLARE

   v_code NUMBER;

   v_errm VARCHAR2(64);

BEGIN

   ………

EXCEPTION

   WHEN OTHERS THEN

        v_code := SQLCODE;

        v_errm := TO_CHAR(SQLERRM(v_code));

        DBMS_OUTPUT.PUTLINE(‘ERRCODE ’ || v_code);

        DBMS_OUTPUT.PUTLINE(‘ERRMSG ’ || v_errm);

END;

重要性? 當然是... 你可以處理exceptions，知道更詳細的訊息囉。

Q: When is a DECLARE statement needed?

[類別] PL/SQL
[問題] 什麼時候需用到DECLARE語法?

Answer:
DECLARE是用在PL/SQL區塊(block)內。一個PL/SQL block被DECLARE, BEGIN, EXCEPTION, END四個關鍵字分成三個部份:

DECLARE

  -- Declarative part(optional)

  -- for declartions of local types, variables, subprograms

BEGIN

  -- Executable part(required)

  -- statements

  EXCEPTION

    -- Exception-handling part(optional)

END;

所以，當你有需要宣告變數等，就會把它放在DECLARE的區塊內。

Q: Describe the use of PL/SQL tables.

混了一個禮拜... 該是回來面對這系列問題的時候了... 其實我也不是故意要跳過的=P。因為上禮拜的問題是: "What packages (if any) has Oracle provided for use by developers?" .... 這.. 就算我去讀了一些文件，還是認為自己無法具體的回答... 所以就放棄這題了.. (如果我在面試的時候被問到這個，大概也沒時間拖過一個星期XD)

[類別] PL/SQL
[問題] 請描述PL/SQL tables該如何使用

Answer:
先講講揪竟PL/SQL table(又叫index-by tables)是什麼? 這是它在9iR2以前的稱呼，9iR2起稱為"associative arrays”。他是一種:

• collection型別
• 有index
• 可用BINARY INTEGER或VARCHAR2做索引(indexed/associated)

他跟另一個collection型別: PL/SQL nested tables很相似:

• 是一維陣列(one-dimensional arrays)
• 無(大小)限制(unbounded, 理論上...只要記憶體夠大)
• 同質性(homogeneous, 就是array裡的每個element要一樣型別的意思..)

先來看一下宣告語法，先定義一個型別，接著才宣告變數:

TYPE 型別名稱 IS TABLE OF 一已知存在型別 INDEX BY [BINART_INTEGER | VARCHAR2(5)];

變數名稱 型別名稱;

ex:

TYPE position_table IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(10);

who_list position_table;

所以上面我們就定義了一個position_table(使用者定義)data type, 是以字串(string)作index, 然後宣告變數who_list是這個型別。該怎麼使用呢?

who_list('CEO') := ‘Fisher Liang’;

who_list('CDO') := ‘Aileen Wu';

DBMS_OUTPUT.PUT_LINE('The CEO is ' || who_list('CEO'));

這樣~ 'CEO', 'CDO'就是index，而且是unique的，若是重複指定，就是取代舊有值的意思。雖然是個array，用起來卻很像是有index的table對巴~?

PL/SQL Tables/associative arrays只做暫時存放資料用，也不是一個真正的table，並無法對其使用insert或select into等SQL statement。不過可以做到session level的life cycle, 就是把以上宣告及使用放在package裡。

完畢!

Ref:

1. PLSQL Tutorial – PLSQL Collections
2. PLSQL Tutorial – Oracle PLSQL Collection Types
3. PL/SQL User’s Guide and Reference – PL/SQL Collections and Records
4. [Oracle-Base] Associative Array in Oracle9i Release 2

Q: Describe the use of %ROWTYPE and %TYPE in PL/SQL

[類別] PL/SQL
[問題] 請描述%ROWTYPE跟%TYPE該如何使用

Answer:
%ROWTYPE可以用來宣告一個record跟
(1)資料庫內某個table/view  OR  (2)從cursor fetch出來  的資料列有相同結構。
宣告語法如下:

變數名稱 表格名稱(OR view名稱 OR cursor名稱)%ROWTYPE;

ex: tmpRow employee%ROWTYPE;

如上就會有一個tmpRow結構跟employee(這個表格)裡的列一樣，包括欄位名稱與資料型別。但並不繼承constraints!!

%TYPE可以用來宣告一個data item跟
(1)已宣告的變數  OR  (2)表格裡的某欄位  有相同的資料型別。
宣告語法如下:

變數名稱 表格名稱.欄位名稱%TYPE;

ex: tmpItem employee.salary%TYPE;

以上面的例子，tmpitem是所謂的referencing item，而employee.salary是referenced item。

使用%TYPE一定會繼承資料型別，但不一定會繼承contraints。當referenced item是資料庫表格的欄位時，就不會繼承。

Q: What is a mutating table error and how can you get around it?

相當慚愧的... 在看到這題以前，我也完全沒聽過mutating tabel這東西OTZ

[類別] PL/SQL
[問題] 什麼是mutating table error? 該如何處理這類錯誤?

Answer:
在回答什麼是mutating table error前，先來說說什麼是"mutating error"。當一個表格正在被UPDATE, DELETE或INSERT語法修改(也就是這個statement尚未commit, 表格正在"變更中")，或是一個table有可能是DELETE CASCADE的對象時，就是所謂的mutsting table。

只有row-level trigger(定義時使用FOR EACH ROW子句)嘗試要查詢或更新一個mutating table時，才會發生mutating table error! 主要是為了避免trigger存取不一致的資料。這時候trigger本身還有觸發trigger的語法(triggering statement)都會被rollback。

中文講的好爛請參閱英文講解 orz

A mutating table error (ORA-4091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement).

Mutating table error的中文訊息長這樣:

ORA-04091: 表格 Schema.Table 正在變更中, 觸發程式/函數無法檢視它

一般避免mutating table error最常見的方法是:

使用compound triggers或temporary tables(or views)，讓同一session update/select不同表格

喋喋不休版
舉個會發生mutating table error的實例: 假如hr.employee的表格上有定義一個row-level的trigger: 每次有row被刪除時，會計算employee的總筆數。所以當你對hr.employee下一個SQL去刪除某列資料的同時(此時employee就是一個mutating table)，也會觸發該trigger，這個trigger嘗試要存取employee，以上條件(mutating table, row-level trigger)，即會發生mutating table error。

Q: Difference between oracle procedure, function and anonymous pl/sql block?

耶!! 前陣子的"回答oracle interview questions計畫"終於要開始惹!!
先簡單說明一下，基本上這些問題會用中文來回答，但是專有名詞的部分... 還是維持它原本的面貌... 因為我根本不知道他中文是要翻成什麼鬼=  =… 而且這些東西翻成中文感覺反而會失去原本的意思或被誤解。

[類別] PL/SQL
[問題] procedure, function還有anonymous pl/sql block間有什麼不同?

Answer
procedure跟function最大的差別在於function會有一個回傳值(a single value)。而anonymous pl/sql block如他字面上定義，是一段沒有名字的pl/sql，通常直接用在像SQL*PLUS這樣的工具裡，來叫用procedure, function或package。

喋喋不休版
procedure跟function兩者可以合稱為"subprogram"，特色是具名、可以給予參數、可以有回傳值(only function)；存在schema層級的subprogram又叫做standalone stored subprogram，而定義在package內的就叫package subprograms。

Anonymous blocks由三個部分組成: 宣告(declarative part)、執行(executable part)、例外處理(exception handlers)，其中宣告與例外處理可有可無。特色是不具名(也就無法永久存於資料庫內)，只做暫時之用(不像procedure跟function可以重複被叫用)。

比較表:

	Anonymous Blocks	Subprograms
具名?	N	Y
每次使用時都進行編譯?	N	N
存於資料庫內?	N	Y
可被其他應用程式呼叫?	N	Y
可回傳bind variable值?	Y	Y
可回傳函數值?	N	Y
可接受參數?	N	Y

*bind variable就是"連結變數"... 就是我們sql語法裡為了效能考量寫成”column_value=:v”的這個:v

Interview Questions with Answers for Oracle, DBA, and developer candidates

Interview Questions with Answers for Oracle, DBA, and developer candidates
今天看資料的時候忽然找到這個網站，感覺起來滿有趣的，他把問題分為8大類:

1. PL/SQL
2. DBA
3. SQL/SQL PLUS
4. Tuning
5. Installation/Configuration
6. Data Modeler
7. UNIX
8. Oracle Troubleshooting

花了一些時間把他們整理起來。然後我決定... 要回答全部的問題!!! (好大的願望XD)，目前預計能將進度控制在每個禮拜回答一題XDD(沒辦法，我還有半本Oracle DBA Fundamentals還沒讀完Q_Q)。至於開始時間呢... 就看什麼時候開始回答第一題吧~ =P (最近好懶散..)
敬請期待!!!