PL/SQL入門

pentium發表於2007-03-05
SQL

Oracle的體系結構
enter image description here
主要分為兩大區域：記憶體區與硬碟區。其中記憶體區由4大快取區域和5大後臺程式構成；而外存區域由3種不同的檔案構成，即Data files、Control files與Redo Log files.

用於修飾輸出結果的函式。這樣的函式一般包含兩大類，即：
enter image description here
首先要介紹的是單行函式。單行函式包含了五大類的函式：即Character、Number、Date、Conversion和General。其中Character類的函式又分為兩種：大小寫調整和字元操作。大小寫調整的函式有三個：

LOWER  /*使得字串變成小寫*/
UPPER   /*使得字串變成大寫*/
INITCAP /*首字母大寫*/

而字元操作函式包含有

CONCAT('hello', 'world')
SUBSTR('hello, world', 1,5)
LENGTH('hello')
INSTR('hello,world','w')  /返回7
LPAD('hello',10,'*')
RPAD('hello',10,'*')
TRIM('H' from 'Hello, world!')
REPLACE('jack and jue','j','BL')

Number函式主要為

ROUND(45.986,2)    45.99
TRUNC(45.986,2)     45.98
MOD(1600,300)       100

CASE expression用於對輸出進行更為方便的調整 

SELECT last_name, job_id, salary,
CASE  job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
ELSE salary END "REVISED_SALARY"
FROM employees;

而DECODE函式是對於CASE Expression的一個功能函式，使得使用更為方便： 

SELECT last_name, job_id,salary,
DECODE(job_id,'IT_PROG',1.10*salary,
'ST_CLERK', 1.15*salary,salay) REVISED_SALARY
FROM employees;

一般性函式（即可以作用於各種不同的資料型別） NVL (comm, 0) /將null值轉換為一個真實值/ NVL2 (expr1, expr2, expr3) NULLIF( expr1, expr2) COALESCE(expr1,expr2,...,exprn) DECLARE中每個變數的宣告都必須以分號結尾。

示例

MERGE INTO target t
       USING source s
       ON (t.id = s.id)
WHEN MATCHED THEN
       UPDATE SET
           t.firstName = s.firstName,
           .....
WHEN NOT MATCHED THEN
       INSERT VALUES(s.id, s.firstName....);
END;

關於Merge語句的語義

Use the ON clause to specify the condition upon which the MERGE operation either updates or inserts. For each row in the target table for which the search condition is true, Oracle Database updates the row with corresponding data from the source table. If the condition is not true for any rows, then the database inserts into the target table based on the corresponding source table row.

enter image description here
Merge語句中的ON子句可以將Target表和Source表作如圖所示的劃分，Target中的綠色部分在後繼操作中是不涉及的，而紅色部分則是UPDATE的物件，Source的黃色部分則為要插入Target表格的素材。也就是說，ON子句的作用是將兩個目標表格做一個劃分，同時將兩者的紅色部分逐條對齊。若ON子句不能滿足上述功能，則會對Oracle的執行造成困擾。

PL/SQL中的迴圈

  1. Basic Loop

    LOOP statement1;
    ...
    EXIT [WHEN condition];
    END;

  2. While Loop

    WHILE condition LOOP statement1; .... END LOOP;

  3. For Loops

    FOR counter IN lower..higher statement1; ... END LOOP;

如何使用帶引數的CURSOR？

DECLARE v_cursor(p_note varchar2) IS
    SELECT * FROM table WHERE field=p_note;
OPEN v_cursor('test');

%ROWTYPE與CURSOR

  1. %ROWTYPE

    DECLARE emp_rec employees%ROWTYPE; BEGIN SELECT * INTO emp_rec FROM table; emp_rec.id ...

  2. CURSOR

對CURSOR的顯式使用
首先是對CURSOR的定義、開啟、遍歷、關閉這四個步驟.

DECLARE  
CURSOR emp_cur IS  
SELECT * FROM EMP_INFO;  
emp_rec emp_cur%ROWTYPE;  
BEGIN  
OPEN emp_cur;  
LOOP  
FETCH  emp_cur INTO emp_rec;  
EXIT WHEN emp_cur%ROWCOUNT>10 
OR emp_cur%NOTFOUND;  
...  
END LOOP;   
END;

對CURSOR的隱式使用 

DECLARE
CURSOR emp_cur IS 
SELECT * FROM EMPINFO;
emp_rec emp_cur%ROWTYPE;
BEGIN
FOR emp_rec IN emp_cur LOOP
//do something
END FOR;
END;

處理異常 系統定義的異常和使用者定義的異常 預定義的異常包括：NO_DATA_FOUND, TOO_MANY_ROWS, INVALID_CURSOR, ZERO_DIVIDE,DUP_VAL_ON_INDEX.

相關文章