PL/SQL學習筆記-1

Oracle9i PL/SQL程式設計 的學習筆記

第1章到第3章

[@more@]

第1章 PL/SQL概述
1.1 為什麼使用PL/SQL
PL/SQL是Procedural Language/SQL的縮寫。
SQL: Structured Query Language. 結構化查詢語言 是第四代語言(4GL)，命令少，語法簡單。但是相對來說第三代語言(3GL)例如C或者COBOL，在本質上缺少過程化。因此開發出了PL/SQL-將SQL語言的強大性和靈活性與3GL的過程性結構融為一體。
擴充套件的有：變數和型別、控制結構、過程和函式、物件型別和方法。
使用PL/SQL,可以將SQL打包到一個PL/SQL語句塊中提交給資料庫，減少網路I/O。

1.2 PL/SQL的功能特性
PL/SQL的基本單位是語句塊。所有的PL/SQL程式都是由語句塊構成的，它們之間可以相互巢狀。
語句塊的結構：
DECLARE
...
BEGIN
...
EXCEPTION
...
END
1.語句塊結構 2.錯誤處理 3.變數和型別
4.條件語句 5.迴圈結構 6.遊標
7.過程和函式 8.包 9.集合


第2章 開發及執行環境
2.1 應用程式模型和PL/SQL
PL/SQL語句塊提交給PL/SQL引擎執行。PL/SQL引擎執行其中的過程性語句，但將語句塊中的SQL語句傳送給用一個SQL語句執行器。
PL/SQL引擎包括編譯器和執行系統。PL/SQL引擎可以在伺服器上，也可以在客戶機上(例如Form,Report)。PL/SQL引擎之間可以通訊。

2.2 PL/SQL開發工具
SQL*Plus、Rapid SQL、DBPartner Debugger、SQL Navigator、TOAD、SQL-Programmer、PL/SQL Developer

第二部分 PL/SQL基本特性

第3章 PL/SQL基礎
3.1 PL/SQL語句塊
有兩種型別的語句塊：匿名的和命名的。 命令的又可以分為：標號語句塊、子程式包含過程和函式、觸發器。
基本塊結構：宣告部分、執行部分和異常處理部分。只有執行部分是必須的，其他都是可選的。
DELCARE
/*Declarative section is here*/
BEGIN
/*Executable session is here*/
EXCEPTION
/*Exception section is here*/
END;
語句塊可以巢狀在一個外部語句塊的執行部分或者異常處理部分。
下面是一個例子：
-----------------------------------------------------------------------------------------
SQL> DECLARE
2 v_name varchar(50);
3 BEGIN
4 v_name:='this is outter';
5 DECLARE
6 v_name varchar(50);
7 BEGIN
8 v_name:='this is inner';
9 dbms_output.put_line(v_name);
10 END;
11 dbms_output.put_line(v_name);
12 select object_name into v_name from dba_objects where object_name='AS';
13 EXCEPTION
14 WHEN OTHERS THEN
15 DECLARE
16 v_error_code int;
17 v_error_msg varchar(100);
18 BEGIN
19 v_error_code:=SQLCODE;
20 v_error_msg:=SQLERRM;
21 dbms_output.put_line(v_error_code);
22 dbms_output.put_line(v_error_msg);
23 END;
24 END;
25 /
this is inner
this is outter
100
ORA-01403: 未找到資料

PL/SQL procedure successfully completed
-----------------------------------------------------------------------------------------

3.2 詞法單元
所有的PL/SQL程式都是由詞法單元構成的。詞法單元的字元序列包括：
* 大小寫字母
* 數字
* 空白：製表符、空格和回車
* 數字符號：+ - * / < > =
* 標點符號：( ) { } [ ] ? ! ; : . ' " @ # % $ ^ & |
PL/SQL是不區分大小寫的。
詞法單元可以分為 標示符、分界符、文字和註釋。
1.標示符
標示符是用來命令PL/SQL物件，例如變數、遊標、型別和子程式。標示符由字母開頭，後面跟 字母、數字、貨幣符號、下劃線字元和"#"字元。標示符的最大長度是30。
保留字：PL/SQL中有很多是保留字，使用這些單詞命令自己的標示符是非法的。
引證標示符：如果要在標示符中使用非法字元或者保留字，那麼可以將該標示符用雙引號括起來。此時是區分大小寫的。
技巧：如果表中列名用的是保留字，可以在表上建立檢視，修改列名，然後透過檢視來訪問表。

2.分界符
分界符是對PL/SQL有特殊意義的符號。用來將標示符相互分割開來。
例如：+ - * / ( ) ... (具體見P62 表3-1)

3.文字
1.字元型文字。 'four sec' '100%' '''' ...
2.數字型文字。 -17.1 1.345E7 -7.2e-2
3.布林型文字。 TRUE FALSE NULL

4.註釋
1.單行註釋。用 -- 標示。
2.多行註釋。用 /* 和 */ 標示。

3.3 變數宣告
我們透過PL/SQL語句塊中的變數與資料庫進行通訊。
變數宣告語法： variable_name type [CONSTANT] [NOT NULL] [:=value];
如果一個變數沒有被初始化，預設被賦值為NULL，如果被指名為 NOT NULL ，則必須初始化，否則會抱錯。
CONSTANT說明該變數是常量變數，不能被改變值。
在宣告中可以用 DEFALUT 替換 ":="。
例如： v_number NUMBER := 45 ; -> v_number NUMBER DEFAULT 45;


3.4 PL/SQL型別
1.標量型別
1.1 數字系列
BINARY_INTEGER, DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NATURAL,
NATURALN, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, POSITIVEN, REAL, SIGNTYPE, SMALLINT.
1.2 字元系列
CHAR, CHARACTER, LONG, NCHAR, NVARCHAR2, STRING, VARCHAR, VARCHAR2
1.3 日期/區間系列
DATE, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
1.4 行標識系列
ROWID, UROWID
1.5 布林系列
BOOLEAN
1.6 受託系列
MLSLABEL
1.7 原始系列
RAW, LONG RAW
2.複合型別
RECORD, NESTD TABLE, INDEX-BY TABLE, VARRAY
3.LOB型別
BFILE, CLOB, LOB, NLOB
4.引用型別
REF CURSOR, REF object type
5.物件型別
object type, SYS.ANYTYPE, SYS.ANYDATA, SYS.ANYDATASET

1.標量型別
1.1 數字系列
數字型別有3種基本型別-NUMBER、PLS_INTEGER和BINARY_INTEGER。
NUMBER型別的變數可以儲存整數或者實數值。
BINARY_INTEGER或PLS_INTEGER型別的變數僅可以儲存整數。
NUMBER(P,S) P是精度，最大值是38。S是刻度，範圍是 -84 到 127。
BINARY_INTEGER 資料型別。範圍從 -2147483647 到 +2147483647 。以二進位制形式儲存。
PLS_INTEGER 和 BINARY_INTEGER 的範圍相同。
BINARY_INTEGER的字型別：
NATURAL 0..2147483647
NATURALN 0..2147483647 NOT NULL
POSITIVE 1..2147483647
POSITIVEN 1..2147483647 NOT NULL
SSIGNTYPE -1,0,1

NUMBER是以十進位制儲存，因此在計算的時候會轉換成2進位制，然後進行計算。因此如果數值不是儲存在資料庫中但僅僅是用來計算，那隻需要用到 BINARY_INTEGER 資料型別了。BINARY_INTEGER 用來儲存帶符號的整數值。
PLS_INTEGER 和 BINARY_INTEGER 的最大區別在於：在計算過程中，如果結果溢位了，BINARY_INTEGER 會用一個更大的NUMBER變數來計算，不會發生錯誤。而 PLS_INTEGER 會抱錯。(注意這裡是計算過程中，如果最終結果溢位，則都回抱錯)。

-----------------------------------------------------------------------------------------
SQL> DECLARE
2 v_binaryinteger BINARY_INTEGER;
3 BEGIN
4 v_binaryinteger := 2147483647;
5 v_binaryinteger := v_binaryinteger+1-1;
6 dbms_output.put_line(v_binaryinteger);
7 END;
8 /
2147483647

PL/SQL procedure successfully completed

SQL>
SQL> DECLARE
2 v_pslinteger PLS_INTEGER;
3 BEGIN
4 v_pslinteger := 2147483647;
5 v_pslinteger := v_pslinteger+1-1;
6 dbms_output.put_line(v_pslinteger);
7 END;
8 /

DECLARE
v_pslinteger PLS_INTEGER;
BEGIN
v_pslinteger := 2147483647;
v_pslinteger := v_pslinteger+1-1;
dbms_output.put_line(v_pslinteger);
END;

ORA-01426: 數字溢位
ORA-06512: 在line 5

-----------------------------------------------------------------------------------------

1.2 字元型別
VARCHAR2：PL/SQL中VARCHAR2的最大長度是32767位元組。但是注意，資料庫中VARCHAR2僅能儲存4000位元組。因此如果超過只能放到資料庫中的LONG或CLOB型別。
在Oracle9i中可以這麼宣告： VARCHAR2(L[CHAR|BYTE])
CHAR： PL/SQL中CHAR的最大長度是32767位元組。但是注意，資料庫中 CHAR 僅能儲存2000位元組。因此如果超過只能放到資料庫中的LONG或CLOB型別。
在Oracle9i中可以這麼宣告： CHAR(L[CHAR|BYTE])
LONG: 與資料庫中的LONG型別不同，PL/SQL LONG 型別是一個變長的字串，其最大長度是 32760位元組。
還有NCHAR和NVARCHAR2，具體見Server SQL Reference。

1.3 原始系列
原始型別中用來儲存二進位制資料。在不同的字符集之間是不會發生轉換。
RAW： PL/SQL中 RAW 的最大長度是32767位元組。但是注意，資料庫中 RAW 僅能儲存2000位元組。因此如果超過只能放到資料庫中的 LONG RAW 型別。
在Oracle9i中可以這麼宣告： RAW(L)
LONG RAW：PL/SQL中CHAR的最大長度是32760位元組。

1.4 日期/區間系列
DATE： 長度是7個位元組。
TIMESTAMP： 包含毫米的時間型別。 TIMESTAMP[(P)]。 P是秒欄位的小數部分，預設為6。
TIMESTAMP[(P)] WITH TIME ZONE： 包含時區資訊的TIMESTAMP。
TIMESTAMP[(P)] WITH LOCAL ZONE： 儲存的是資料庫的時區。
INTERVAL型別用於儲存兩個時間戳之間的時間間隔。
INTERVAL[(P)] TO MONTH、INTERVAL[(DP)] TO SECOND[(SP)]

------------------------------------------------------------------------------------------------
SQL> create table bb
2 (
3 yminterval1 interval year to month,
4 yminterval2 interval year to month,
5 dsinterval1 interval day to second,
6 dsinterval2 interval day to second,
7 dsinterval3 interval day to second,
8 dsinterval4 interval day to second
9 );

Table created

SQL> insert into bb values(NUMTOYMINTERVAL(90,'year'),NUMTOYMINTERVAL(99,'month'),
NUMTODSINTERVAL(99,'DAY'),NUMTODSINTERVAL(99,'HOUR'),
NUMTODSINTERVAL(99,'MINUTE'),NUMTODSINTERVAL(99,'SECOND'));

1 row inserted

SQL> select * from bb;

YMINTERVAL1 YMINTERVAL2 DSINTERVAL1 DSINTERVAL2 DSINTERVAL3 DSINTERVAL4
------------ ------------ ------------------- ------------------- ------------------- --------------------
+90-00 +08-03 +99 00:00:00.000000 +04 03:00:00.000000 +00 01:39:00.000000 +00 00:01:39.000000

------------------------------------------------------------------------------------------------

1.5 行標識系列
ROWID： ROWID PL/SQL型別與資料庫ROWID偽列型別是相同的。可以用ROWIDTOCHAR將其轉換成為字串。這是一個18位的字串。
UROWID：UROWID即可以儲存ROWID，又可以儲存邏輯行識別符號。

1.6 布林系列
BOOLEAN

1.7 受託類
MLSLABEL： 這種資料型別用在Trusted Oracle中以儲存可變長的二進位制標記標籤。 (從來沒有用過，不懂)

2 複合型別
有 記錄、表(巢狀表和索引表)、陣列。具體的在後面討論。
3 引用型別
4 LOB型別
使用者儲存大型的物件。具體的參見後面。
5 物件型別
物件型別是一種複合型別，包括屬性和方法。具體的見後面。

推薦使用 %TYPE 來申明變數型別。原表列的屬性為NOT NULL的話，並不意味著變數必須也是NOT NULL的
使用者自定義子型別： SUBTYPE new_type IS original_type 。
例如：
SUBTYPE t_loopcount IS NUMBER;
v_count t_loopcount;
SUBTYPE定義不能在定義中直接進行限制。以下語法是錯誤的(除了Oracle8i)。
SUBTYPE t_loopcounter is NUMBER(4);
可以使用下面的方法實現：
v_dummyvar NUMBER(4);
SUBTYPE t_loopcounter is v_dummyvar%TYPE;
v_counter t_loopcounter;

資料型別之間可以有 顯式或隱式 的轉換。

3.5 表示式和運算子
賦值： variable:=expression;

運算子優先順序
**、NOT 求幕、邏輯非
+、- 正、負
*、/ 乘、除
+、-、|| 加、減、字串連線
=、!=、、<=、>=、IS NULL 邏輯比
LIKE、BETWEEN、IN
AND 邏輯與
OR 邏輯或

3.6 PL/SQL控制結構
IF-THEN-ELSE的語法：
IF ... THEN
...
ELSIF ... THEN
...
ELSE
...
END IF;

CASE語法
[<>]
CASE [test_var]
WHEN value1 THEN sequence_of_statements1;
WHEN value2 THEN sequence_of_statements2;
...
WHEN valuen THEN sequence_of_statementsn;
[ELSE else_sequence;]
END CASE [MyCase];
1.沒有ELSE的CASE語句。如果沒有ELSE，且表示式與任何一個檢測值都不匹配的時候，會抱錯ORA-6592，CASE_NOT_FOUND
2.設定標號的CASE語句。CASE語句可以設定標號。
3.搜尋式CASE語句。可以沒有檢測變數。
注意只碰到一個TRUE就會執行之後的語句，然後跳出CASE語句。

簡單迴圈：
LOOP
..
EXIT [WHEN condition];
END LOOP;

WHILE迴圈：
WHILE condition LOOP
...
END LOOP;
在迴圈中隨時可以用 EXIT 或者 EXIT WHEN 語句退出。

數字式FOR迴圈
FOR loop_counter IN [REVERSE] low_bound..high_bound LOOP
sequence_of_statement;
END LOOP;
FOR迴圈的迴圈索引被隱式宣告為 BINARY_INTEGER 。

GOTO： 從外層跳轉到內層語句塊、迴圈或者IF語句是非法的。從一個IF子句跳到另一個子句也是非法的。從一個異常處理語句塊內部跳轉到當前語句塊也是非法的。

編譯指示(Pragmas)是一些編譯器指令，類似於C語言中的#pragma或者#define指令。它是提供給PL/SQL編譯器的指令。

3.7 PL/SQL記錄
定義記錄型別的一般語法是：
TYPE record_type IS RECORD(
field1 type1 [NOT NULL][:=expr1],
...
)
可以使用 select into 的方法，給記錄負值。
%ROWTYPE 將返回一個基於表定義的型別。雷上定義的所有 NOT NULL 約束都不會被包含近來。但是VARCHAR2和CHAR的長度和NUMBER的精度會包含近來。