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

Batch file – Delete files by date, count the output of other commands

介紹個相當好用的指令: Forfiles

用途: 檔案選取，並可針對篩選出來的檔案再下其他指令
注意: WinSvr2003, R2, SP1, PS2才有此指令
常用參數: /m SearchMark: Searches files according to SearchMask.
             /c command: Runs the specified Command on each file.
             /d [{+ | -}] [{MM/DD/YYYY | DD}]

假如某個資料夾下每天都會有一個(以上)檔案產生，並且希望定期清理，只保留一個月份的檔案量時，可以用forfiles:

下面這個指令就是刪除日期為30天以前的檔案

> forfile /D –30 /C “cmd /c del @file”

• /C 這參數預設值是"cmd /c echo @file"，也就是只列出檔名。
• "cmd /c" 是下指令固定的寫法。
• "@file" 是可以用在/C參數內的變數之一，@file表示檔案名稱。其他還有變數可以列出檔案副檔名(@ext)、完整路徑(@path)、相對路徑(@relpath)、判斷是否為目錄(@isdir)、檔案大小(@fsize)、日期(fdate)、時間(@ftime)等。
• /D 這參數用法有很多變化(詳請參考reference)，"-30"的意思就是30天前。

另外一個是For...DO迴圈語法，用來跟上面的forfiles搭配，可以先計算出forfiles總共篩選出幾個檔案:

> set cnt=0
> FOR /F %G IN(‘forfile /D -10’) DO set /a cnt+=1

• 上面即是先以forfile找出10天前的檔案，並丟進去for迴圈內
• DO裡針對每個forfile都把變數cnt加1，所以最後只要echo %cnt%即可得到檔案數目
• for-do的詳細用法也請參考reference

Ref:

1. Forfiles
2. For(loop)

Using binding vareable with “LIKE” condition

這感覺就是一個相當簡單又會很常用到的東西... 卻一時寫不出來OTZ

通常我們用到LIKE時，大多就是想做模糊搜尋吧...

像下面這個SQL是要找名字裡包含大寫SM的人:
SELECT * FROM EMPLOYEE WHERE NAME LIKE ‘%SM%’

像上面這樣寫，就變成傳說中的hard coding，有時候為了效能考量，我們要把值的地方以變數(:variable)取代，所以這兩種組合揪竟該怎麼兜在一起呢!?

這樣… LIKE '%:variable%'          (X, :variable直接被當成字串=   =)
這樣… LIKE ''%:variable%''        (X, :variable終於不被當成字串，還是失敗)
這樣… LIKE '%'||:variable||'%'     (O, 原來要自己組....)

BUG! “Environment variable ORACLE_UNQNAME not defined”

Environment:
DB version: Oracle 11gr2
Platform: Linux RedHat EL5

今天本來是在做"Automating Database Startup and Shutdown on Linux" setp by step。照著文件做，也處理了最後"Known Issues"的部分後，發現.... Oracle em(dbconsole)的服務並沒有起來...

也不是什麼大問題，就是dbstart這支script裡並沒有把他寫進去而已，於是自己把他補在"/etc/init.d/dbora"(依照上面文件做的一個檔案)裡面:

su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole” ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^上面這行

補了一行code之後又重機一次，發現竟然還是一樣... 失敗了=   =，如果以oracle的身分直接去跑"emctl start dbconsole"是可以成功的，但是如果一開始是以root的身分切換到oracle去下指令(就是補上的那一行做的事情)，會被抱怨:

Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.

ORACLE_UNQNAME... 還真沒看過這個東西... 結果在oracle文件裡看到這是一個BUG(Bug 1716161)!! 不過他只跟你說，把ORACLE_UNQNAME這個環境變數的值設成db_unique_name這個初始化參數而已... how!?!?

再來看另一篇文章，他也是做一樣的事情，並且在最後一個步驟(6.)說明:
在"/etc/profile"裡補上一段code即可!!

if [ $USER = "oracle" ] ; then
if [ $SHELL = "/bin/ksh" ] ; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
export ORACLE_UNQNAME=db_unique_name
umask 022
fi

真的只是個bug!!!!

Ref:

1. [ORACLE-BASE] Automating Database Startup and Shutdown on Linux
2. [Oracle Database Readme] section38 “Open Bugs”
3. [JOBACLE] Automatic Start/stop in 11g 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是資料庫表格的欄位時，就不會繼承。

Linux例行性工作排程by crontab

env: Red Hat Enterprise Linux 5

如果要在linux上排定一些類似檔案備份的例行性排程，可以利用crontab這指令。

step1: 準備好要下的指令，通常都會先編一個.sh file, 像這樣:

[say_hello.sh]

#!/bin/bash

echo say hello `date` >> log.txt

    上面這個檔案會印一行say hello跟系統時間在log.txt這個檔案裡
    像這樣: say hello Wed May 12 16:20:01 CST 2010

step2: 使用crontab排程

$ crontab -e

-e是edit的意思。下了這個指令後會進入vi編輯畫面。都未曾使用過crontab的話內容就是一片空白。

step3: 新增工作排程

crontab的內容有固定格式，一個工作一行，大概長像這樣:

20 16 * * * /home/oracle/say_hello.sh

先簡單說一下這條工作內容: 每到16:20執行say_hello.sh
指令的結構是這樣(詳細說明請參閱[鳥哥]基礎文件CH16)

分	時	日	月	周	指令
0-59	0-23	1-31	1-12	0-7	指令=  =

這邊要注意指令裡有用到任何檔案，都要用絕對路徑。
編輯完存檔退出後，會有一行訊息: crontab: installing new crontab
表示排程已經排定。如果要檢視所有排定的工作，可以下:

$ crontab -l

step4: 就等囉~

等到已經排定的時間，你可能會發現.... 失敗了=   =，並且會收到一封mail，由root(Cron Deamon)寄出，內容最後有一行類似這樣的訊息:

/bin/sh: /home/oracle/say_hello.sh: Permission denied

permission denied… 也不知道在deny什麼鬼，因為明明可以在自己的環境裡直接下"sh /home/oracle/say_hello.sh"執行這個script。一直以為是路徑的問題(阿不就是寫完整路徑!?)，改來改去就是不行。

最後發現竟然真的是權限問題，下"ls -l"可以看到新建的script檔權限長這樣:
－ｒｗ－ｒ－－ｒ－－
owner自己竟然沒權限執行.... orz||，chmod一下

$ chmod u+x /home/oracle/say_hello.sh

確認一下執行權限－ｒｗｘｒ－－ｒ－－
再重新安排一次時間比較近的排程試試看，應該就OK囉。

linux排程初體驗，成功!!!

Ref:

1. [鳥哥] 第十六章、例行性工作排程 (crontab)
2. Disable The Mail Alert By Crontab Command