Oracle之PL/SQL的變數小結

PLSQL變數有四種類型，分別是：
－ 標量類型（Scalar）
－ 複合類型（Composite）
－ 參照類型（Reference）
－ LOB類型（Large Object）

1、標量類型：

－ 只能存放單個數值的變數
－ 定義時，必須要指定標量的資料類型

1.1常用標量類型

（1）VARCHAR2(n)
定義可變長度的字串
n指定字串最大長度
n最大值是32767位元組
使用時必須指定長度
當在PLSQL塊鐘使用該資料類型操縱VARCHAR2表列時，起數值長度不應超過4000位元組

（2）CHAR(n)
定義固定長度字串
n指定字串的最大長度
n最大值是32767自己
使用時指定長度，若沒指定，則使用預設值1
當在PLSQL塊鐘使用該資料類型操縱CHAR2表列時，起數值長度不應超過2000位元組

（3）NUMBER(p，s)
定義固定長度的整數和浮點數
p表示精度，用於指定數位總位元
s表示標度，用於指定小數點後的數字位元

（4）DATE
定義日期和資料資料

（5）BOOLEAN
定義布爾變數
值為：TRUE、FALSE、NULL
為PLSQL專屬資料類型，表列不能採用該資料類型
NULL表示missing、inapplicable或者unknown

（6）LONG和LONG ROW
long：資料類型用於定義變長字串，類似於VARCHAR2資料類型，但其字串的最大長度為32760位元組
long row：資料類型用於定義變長的位元據，其資料最大長度為32760位元組

（7）BINARY_INTEGER
定義整數
數值範圍-2147483647和2174483647之間

（8）BINARY_FLOAT和BINARY_DOUBLE
BINARY_FLOAT定義單精確度浮點數（oracle 10g新增加）
BINARY_DOUBLE定義雙精確度浮點數（oracle 10g新增加）

（9）TIMESTAMP
定義時間和日期資料（oracle 9i新增加）
對其賦值方法與對date變數賦值方法完全相同
當顯示TIMESTAMP變數資料時，不僅會顯示日期，而且還會顯示時間和上下午標記

1.2、定義標量類型
文法：
identifier [CONSTANT] datatype [NOT NULL] [ :=|DEFAULT expr]
identifier：指定變數或常量名稱
CONSTANT：指定是常量
datatype：用於指定變數或常量的類型
NOT NULL：非空，有它是必須初始化
:= ：賦值符合
DEFAULT：預設值
expr：初始值

1.3、標量定義規則
每行聲明一個變數
變數的賦值使用：identifier := value;
變數名稱最多30個字元

1.4使用%TYPE屬性
安裝資料庫列或其他變數來確定新變數的類型和長度（具有動態性和綁定性）
文法：identifier Table.column_name%TYPE;
或 identifier other_variable_name%TYPE;

2、複合變數
指用於存放多個值的變數
當定義複合變數時，必須要使用PL/SQL的複合資料型別
PL/SQL的複合資料型別包括：
PL/SQL記錄
PL/SQL表（索引表）
PL/SQL巢狀表格
VARRAY
關於這些複合資料型別，由相關章節給出

3、參照變數
指用於存放數值指標的變數
可以通過參照變數來共用相同對象，從而降低佔用空間
兩種參照變數：
遊標變數（REF CURSOR）
物件類型變數（REF obj_type）
關於這些參照變數，由相關章節給出

4、LOB變數
用於儲存大批量資料的變數
分為兩種：
內部LOB：包括CLOB、BLOB、NCLOB，它們的資料被儲存在資料庫中，並且支援事務操作
外部LOB：BFILE，該類型資料被儲存在OS檔案中，並且不支援事務操作
CLOB：儲存大批量字元資料
NCLOB：儲存大批量字元資料，unicode編碼
BLOB：儲存大批量位元據
BFILE：儲存指向OS檔案的指標

5、非PL/SQL變數

5.1、替換變數（僅用於SQL *Plus或者用於原理和SQL *Plus相同的開發工具）：
臨時儲存值
利用它可以達到建立泛型指令碼的目的
利用它可以達到和使用者互動，故在SQL *Plus中又稱互動式命令
替換變數的格式式在變數名稱前加一個&，以便在運行SQL命令時提示使用者輸入替換資料，然後按輸入資料運行SQL命令
文法：
（1）& ：“&變數名”eg：&name;
生命週期：單次引用中，不需要聲明，如果替換字元或日期類型，最好用單引號擴起
使用範圍：where、order by、列運算式、表名、整個SELECT 語句中
（2）&& ：“&&變數名”eg：&&name;
生命週期：整個會話（session串連）,不需要聲明
（3）define ：“define 變數名=變數值”eg：DEFINE a = clark;
生命週期：整個會話，預先聲明，使用時用&引用聲明的變數
define variable=使用者建立的CHAR類型的值：define 變數名=值；
define column_name(變數名)：查看變數命令。
undefine 變數名：清除變數
define：查看在當前會話中所有的替換變數和它們的值
（4）accept
生命週期：整個會話
預先聲明，可以客戶化提示資訊，使用時用&引用聲明的變數。
定義：
accept 變數名name number/char/date prompt '提示資訊內容'即：ACC[EPT] variable [NUM[BER] | CHAR | DATE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]
解釋：
PROMPT命令：用於輸出提示使用者的資訊，以便使使用者瞭解指令檔的功能和運行情況
PAUSE命令：用於暫停指令檔的運行
HIDE選項：用於隱藏使用者的輸入，使別人不可見，安全
這條命令的意思是：當plsql程式段執行到變數name的時候，此時需要使用者的互動才能繼續執行下去，plsql程式段會顯示“提示資訊內容”讓使用者輸入相關資訊（如果指定hide選項，那麼在接下去使用者輸入的東西將被用星號顯示出來增加安全，有點像輸入密碼），使用者輸入的內容被接收到並且把它付給name，關於在“提示資訊內容”下使用者輸入的內容的類型，plsql程式段開發人員來通過number/char/date指定，變數name得到正確的值以後，繼續執行相關下面的程式！
例：accept a char prompt '請輸入員工的僱傭時間(yyyy-mm-dd):' hide
例：accept a char prompt 'input a:' hide

替換變數非常的依賴SQL *Plus的環境，當環境變數verify被關閉的時候它就不可用，只有通過開啟它才能使用：
set verify(環境變數) off;關閉調試命令（關掉替換過程）
set verify(環境變數) on;開啟調試命令(可以看到替換過程)

5.2、SQL *PLUS環境變數（僅用於SQL *Plus或者用於原理和SQL *Plus相同的開發工具）：
ECHO 顯示回顯
HEADING {OFF/ON}是否顯示欄位標題
ARRAYSIZE{20/n}每一次從查詢得到的返回量的大小
FEEDBACK{OFF/ON}回饋，反饋資訊
LONG{80/n}on/text} LONG類型
LINESIZE 行的寬度
SET LINESIZE n(最好是在200之內)
PAGESIZE ：設定頁的大小。SET PAGESIZE N
wrap{off/on} 折行
SET 修改
SHOW 顯示
SQL *PLUS中格式化顯示的命令：
COLUMN[column option]可以設定欄位或欄位別名的格式
COLUMN last_name HEADING employee|name '|'代表換行
col 欄位名 查看命令
CLE[AR]：清除列的格式
HEA[DING] TEXT：設定欄位標題
FOR[MAT] FORMAT：格式化顯示列的值，對字元和數字有效，對日期無效，eg：column salary justify left format ＄999,999.00，其中justify left:靠左對齊；col manager_id format 999999999
限制字串的長度有A+數字限制
限制數位長度有9，有幾為9就限制成幾位。
NOPRINT/PRINT NOPRINT：把一個欄位從輸出上屏蔽掉（返回但不顯示）。
col 欄位名 noprint/print.
NULL如果有NULL值，顯示什麼。
col name null 'on employee'
TTITLE[text/off/on]設定報表的表頭
BTITLE[text/off/on]設定報表的表尾
做報表的時候要先想好PAGESIZE的大小。
BREAK ON [REPORT_ELEMENT]
壓制重複值的顯示，只能跟一個欄位名才有效，eg：
select department_id,last_name
from employees
where rownum<30
order by 1,2;
break on department_id

5.3、做PL/SQL指令檔的過程：變數定義accept
環境變數設定SET
格式控制命令
SPOOL
使用變數的SQL
SPOOL OFF
清除格式控制
重設環境變數
釋放變數

5.4、引用非PL/SQL變數當要在PL/SQL塊中引用非PL/SQL變數時，也就是要引用PL/SQL塊所在地的host變數（或稱“環境變數”）時，必須要在非PL/SQL變數前加冒號（“：”），eg：”:name”,name為非PL/SQL變數。