函數(shù)

　　函數(shù)是命名了的、存儲在數(shù)據(jù)庫中的PL/SQL程序塊。函數(shù)接受零個或多個輸入?yún)��?shù)，有一個返回值，返回值的數(shù)據(jù)類型在創(chuàng)建函數(shù)時定義。定義函數(shù)的語法如下：

FUNCTION name [{parameter[,parameter,...])] RETURN datatypes IS
[local declarations]
BEGIN
execute statements
[EXCEPTION
exception handlers]
END [name]


　　過程

　　存儲過程是一個PL/SQL程序塊，接受零個或多個參數(shù)作為輸入(INPUT)或輸出(OUTPUT)、或既作輸入又作輸出(INOUT)，與函數(shù)不同，存儲過程沒有返回值，存儲過程不能由SQL語句直接使用，只能通過EXECUT命令或PL/SQL程序塊內(nèi)部調(diào)用，定義存儲過程的語法如下：

PROCEDURE name [(parameter[,parameter,...])] IS
[local declarations]
BEGIN
execute statements
[EXCEPTION
exception handlers ]
END [name]


　　包(package)

　　包其實就是被組合在一起的相關(guān)對象的集合，當包中任何函數(shù)或存儲過程被調(diào)用，包就被加載入內(nèi)存中，包中的任何函數(shù)或存儲過程的子程序訪問速度將大大加快。
包由兩個部分組成：規(guī)范和包主體(body),規(guī)范描述變量、常量、游標、和子程序，包體完全定義子程序和游標。

　　觸發(fā)器(trigger)

　　觸發(fā)器與一個表或數(shù)據(jù)庫事件聯(lián)系在一起的，當一個觸發(fā)器事件發(fā)生時，定義在表上的觸發(fā)器被觸發(fā)。

變量和常量

　　變量存放在內(nèi)存中以獲得值，能被PL/SQL塊引用。你可以把變量想象成一個可儲藏東西的容器，容器內(nèi)的東西是可以改變的。

　　聲明變量

　　變量一般都在PL/SQL塊的聲明部分聲明，PL/SQL是一種強壯的類型語言，這就是說在引用變量前必須首先聲明，要在執(zhí)行或異常處理部分使用變量，那么變量必須首先在聲明部分進行聲明。

　　聲明變量的語法如下：

Variable_name [CONSTANT] databyte [NOT NULL][:=|DEFAULT expression]


　　注意:可以在聲明變量的同時給變量強制性的加上NOT NULL約束條件，此時變量在初始化時必須賦值。

　　給變量賦值

　　給變量賦值有兩種方式：

　　. 直接給變量賦值

　　　X:=200;
　　　Y=Y+(X*20);

　　. 通過SQL Select INTO 或FETCH INTO給變量賦值

Select SUM(SALARY),SUM(SALARY*0.1)
INTO TOTAL_SALARY,TATAL_COMMISSION
FROM EMPLOYEE
Where DEPT=10;

　　常量

　　常量與變量相似，但常量的值在程序內(nèi)部不能改變，常量的值在定義時賦予，，他的聲明方式與變量相似，但必須包括關(guān)鍵字CONSTANT。常量和變量都可被定義為SQL和用戶定義的數(shù)據(jù)類型。

ZERO_VALUE CONSTANT NUMBER:=0;


　　這個語句定了一個名叫ZERO_VALUE、數(shù)據(jù)類型是NUMBER、值為0的常量。

　　標量(scalar)數(shù)據(jù)類型

　　標量(scalar)數(shù)據(jù)類型沒有內(nèi)部組件，他們大致可分為以下四類：

　　　. number
　　　. character
　　　. date/time
　　　. boolean

　　表1顯示了數(shù)字數(shù)據(jù)類型；表2顯示了字符數(shù)據(jù)類型；表3顯示了日期和布爾數(shù)據(jù)類型。

　　表1 Scalar Types:Numeric

Datatype
Range
Subtypes
description

BINARY_INTEGER
-214748-2147483647
NATURAL
NATURAL
NPOSITIVE
POSITIVEN
SIGNTYPE
用于存儲單字節(jié)整數(shù)。
要求存儲長度低于NUMBER值。
用于限制范圍的子類型(SUBTYPE):
　NATURAL:用于非負數(shù)
　POSITIVE:只用于正數(shù)
　NATURALN:只用于非負數(shù)和非NULL值
　POSITIVEN:只用于正數(shù)，不能用于NULL值
　SIGNTYPE:只有值:-1、0或1.

NUMBER
1.0E-130-9.99E125
DEC
DECIMAL
DOUBLE
PRECISION
FLOAT
INTEGERIC
INT
NUMERIC
REAL
SMALLINT
存儲數(shù)字值，包括整數(shù)和浮點數(shù)�？梢赃x擇精度和刻度方式，語法：
number[（[,]）]。
缺省的精度是38,scale是0.

PLS_INTEGER
-2147483647-2147483647

與BINARY_INTEGER基本相同，但采用機器運算時，PLS_INTEGER提供更好的性能 。


　　表2 字符數(shù)據(jù)類型

datatype
rang
subtype
description

CHAR
最大長度32767字節(jié)
CHARACTER
存儲定長字符串，如果長度沒有確定，缺省是1

LONG
最大長度2147483647字節(jié)

存儲可變長度字符串

RAW
最大長度32767字節(jié)

用于存儲二進制數(shù)據(jù)和字節(jié)字符串，當在兩個數(shù)據(jù)庫之間進行傳遞時，RAW數(shù)據(jù)不在字符集之間進行轉(zhuǎn)換。

LONGRAW
最大長度2147483647

與LONG數(shù)據(jù)類型相似，同樣他也不能在字符集之間進行轉(zhuǎn)換。

ROWID
18個字節(jié)

與數(shù)據(jù)庫ROWID偽列類型相同，能夠存儲一個行標示符，可以將行標示符看作數(shù)據(jù)庫中每一行的唯一鍵值。

VARCHAR2
最大長度32767字節(jié)
STRINGVARCHAR
與VARCHAR數(shù)據(jù)類型相似，存儲可變長度的字符串。聲明方法與VARCHAR相同


　　表3 DATE和BOOLEAN

datatype
range
description

BOOLEAN
TRUE/FALSE
存儲邏輯值TRUE或FALSE,無參數(shù)

DATE
01/01/4712 BC
存儲固定長的日期和時間值，日期值中包含時間


　　LOB數(shù)據(jù)類型

　　LOB(大對象,Large object) 數(shù)據(jù)類型用于存儲類似圖像，聲音這樣的大型數(shù)據(jù)對象，LOB數(shù)據(jù)對象可以是二進制數(shù)據(jù)也可以是字符數(shù)據(jù)，其最大長度不超過4G。LOB數(shù)據(jù)類型支持任意訪問方式，LONG只支持順序訪問方式。LOB存儲在一個單獨的位置上，同時一個"LOB定位符"(LOB locator)存儲在原始的表中，該定位符是一個指向?qū)嶋H數(shù)據(jù)的指針。在PL/SQL中操作LOB數(shù)據(jù)對象使用ORACLE提供的包DBMS_LOB.LOB數(shù)據(jù)類型可分為以下四類：

　　. BFILE
　　. BLOB
　　. CLOB
　　. NCLOB

　　操作符

　　與其他程序設計語言相同，PL/SQL有一系列操作符。操作符分為下面幾類：

　　. 算術(shù)操作符

　　. 關(guān)系操作符

　　. 比較操作符

　　. 邏輯操作符

　　算術(shù)操作符如表4所示

operator
operation

+ 加
- 減
/ 除

* 乘
** 乘方


　　關(guān)系操作符主要用于條件判斷語句或用于where子串中，關(guān)系操作符檢查條件和結(jié)果是否為true或false,表5是PL/SQL中的關(guān)系操作符

operator
operation


大于操作符

>=
大于或等于操作符

=
等于操作符

!=
不等于操作符


不等于操作符

:=
賦值操作符


　　表6 顯示的是比較操作符

operator
operation

IS NULL
如果操作數(shù)為NULL返回TRUE

LIKE
比較字符串值

BETWEEN
驗證值是否在范圍之內(nèi)

IN
驗證操作數(shù)在設定的一系列值中


　　表7.8顯示的是邏輯操作符

operator
operation

AND
兩個條件都必須滿足

or
只要滿足兩個條件中的一個

NOT
取反


　　執(zhí)行部分

　　執(zhí)行部分包含了所有的語句和表達式,執(zhí)行部分以關(guān)鍵字BEGIN開始，以關(guān)鍵字EXCEPTION結(jié)束，如果EXCEPTION不存在，那么將以關(guān)鍵字END結(jié)束。分號分隔每一條語句，使用賦值操作符:=或Select INTO或FETCH INTO給每個變量賦值，執(zhí)行部分的錯誤將在異常處理部分解決，在執(zhí)行部分中可以使用另一個PL/SQL程序塊，這種程序塊被稱為嵌套塊

　　所有的SQL數(shù)據(jù)操作語句都可以用于執(zhí)行部分，PL/SQL塊不能再屏幕上顯示Select語句的輸出。Select語句必須包括一個INTO子串或者是游標的一部分，執(zhí)行部分使用的變量和常量必須首先在聲明部分聲明，執(zhí)行部分必須至少包括一條可執(zhí)行語句，NULL是一條合法的可執(zhí)行語句，事物控制語句COMMIT和ROLLBACK可以在執(zhí)行部分使用，數(shù)據(jù)定義語言(Data Definition language)不能在執(zhí)行部分中使用，DDL語句與EXECUTE IMMEDIATE一起使用或者是DBMS_SQL調(diào)用。

　　執(zhí)行一個PL/SQL塊

　　SQL*PLUS中匿名的PL/SQL塊的執(zhí)行是在PL/SQL塊后輸入/來執(zhí)行，如下面的例子所示：

declare
　v_comm_percent constant number:=10;
begin
　update emp
　set comm=sal*v_comm_percent
　where deptno=10;
　end
SQL> /
PL/SQL procedure successfully completed.

SQL>


　　命名的程序與匿名程序的執(zhí)行不同，執(zhí)行命名的程序塊必須使用execute關(guān)鍵字：

create or replace procedure update_commission
　(v_dept in number,v_pervent in number default 10) is
begin
　update emp
　set comm=sal*v_percent
　where deptno=v_dept;
end

SQL>/

Procedure created

SQL>execute update_commission(10,15);

PL/SQL procedure successfully completed.

SQL>


　　如果在另一個命名程序塊或匿名程序塊中執(zhí)行這個程序，那么就不需要EXECUTE關(guān)進字。

declare
　v_dept number;
begin
　select a.deptno
　into v_dept
　from emp a
　where job='PRESIDENT'
　update_commission(v_dept);
end
SQL>/
　PL/SQL procedure successfully completed
SQL>


　控制結(jié)構(gòu)

　　控制結(jié)構(gòu)控制PL/SQL程序流程的代碼行,PL/SQL支持條件控制和循環(huán)控制結(jié)構(gòu)。

　　語法和用途

　　　IF..THEN

　　語法：

IF condition THEN
　Statements 1;
　Statements 2;
　....
END IF


　　IF語句判斷條件condition是否為TRUE，如果是，則執(zhí)行THEN后面的語句，如果condition為false或NULL則跳過THEN到END IF之間的語句，執(zhí)行END IF后面的語句。

　　IF..THEN...ELSE

　　語法:

IF condition THEN
　Statements 1;
　Statements 2;
　....
ELSE
　Statements 1;
　Statements 2;
　....
END IF


　　如果條件condition為TRUE,則執(zhí)行THEN到ELSE之間的語句，否則執(zhí)行ELSE到END IF之間的語句。

　　IF 可以嵌套，可以在IF 或IF ..ELSE語句中使用IF或IF..ELSE語句。

if (a>b) and (a>c) then
　　g:=a;
else
　　g:=b;
　　if c>g then
　　　g:=c;
　　end if
end if


　　IF..THEN..ELSIF

　　語法：

IF condition1 THEN
　statement1;
ELSIF condition2 THEN
　statement2;
ELSIF condition3 THEN
　statement3;
ELSE
　statement4;
END IF;
　statement5;


　　如果條件condition1為TRUE則執(zhí)行statement1,然后執(zhí)行statement5,否則判斷condition2是否為TRUE,若為TRUE則執(zhí)行statement2,然后執(zhí)行statement5,對于condition3也是相同的，如果condition1，condition2，condition3都不成立，那么將執(zhí)行statement4,然后執(zhí)行statement5。

　　循環(huán)控制

　　循環(huán)控制的基本形式是LOOP語句，LOOP和END LOOP之間的語句將無限次的執(zhí)行。LOOP語句的語法如下：

　　LOOP
　　　statements;
　　END LOOP

　　LOOP和END LOOP之間的語句無限次的執(zhí)行顯然是不行的，那么在使用LOOP語句時必須使用EXIT語句，強制循環(huán)結(jié)束，例如：

X:=100;
LOOP
　X:=X+10;
　IF X>1000 THEN
　　EXIT;
　END IF
END LOOP;
Y:=X;


　　此時Y的值是1010.

　　EXIT WHEN語句將結(jié)束循環(huán)，如果條件為TRUE，則結(jié)束循環(huán)。

X:=100;
LOOP
X:=X+10;
EXIT WHEN X>1000;
X:=X+10;
END LOOP;
Y:=X;


　　WHILE..LOOP

　　WHILE..LOOP有一個條件與循環(huán)相聯(lián)系，如果條件為TRUE,則執(zhí)行循環(huán)體內(nèi)的語句，如果結(jié)果為FALSE,則結(jié)束循環(huán)。

X:=100;
WHILE X。

　　標記程序塊


[DECLARE]
... ... ...
BEGIN
........
[EXCEPTION]
.......
END label_name


　　標記循環(huán)


LOOP
.........

loop
..........

loop
....

EXIT outer_loop WHEN v_condition=0;
end loop innermost_loop;
..........
END LOOP inner_loop;
END LOOP outer_loop;


　　GOTO語句

　　語法：

　　GOTO LABEL;

　　執(zhí)行GOTO語句時，控制會立即轉(zhuǎn)到由標簽標記的語句。PL/SQL中對GOTO語句有一些限制，對于塊、循環(huán)、IF語句而言，從外層跳轉(zhuǎn)到內(nèi)層是非法的。

X ：=100;
FOR V_COUNTER IN 1..10 LOOP
　IF V_COUNTER =4 THEN
　　GOTO end_of_loop
　END IF
　X:=X+10;
　
　NULL
END LOOP

Y:=X;


　　注意：NULL是一個合法的可執(zhí)行語句。

　　嵌套

　　程序塊的內(nèi)部可以有另一個程序塊這種情況稱為嵌套。嵌套要注意的是變量，定義在最外部程序塊中的變量可以在所有子塊中使用，如果在子塊中定義了與外部程序塊變量相同的變量名，在執(zhí)行子塊時將使用子塊中定義的變量。子塊中定義的變量不能被父塊引用。同樣GOTO語句不能由父塊跳轉(zhuǎn)道子塊中，反之則是合法的。

《OUTER BLOCK》
DECLARE
　A_NUMBER INTEGER；
　B_NUMBER INTEGER；
BEGIN
　--A_NUMBER and B_NUMBER are available here
　
　DECLARE
　C_NUMBER INTEGER
　B_NUMBER NUMBER(20)
BEGIN
　C_NUMBER:=A_NUMBER;
　C_NUMBER=OUTER_BLOCK.B_NUMBER;
END SUB_BLOCK;
END OUT_BLOCK;


　　小結(jié)

　 我們在這篇文章中介紹了PL/SQL的基礎語法以及如何使用PL/SQL語言設計和運行PL/SQL程序塊，并將PL/SQL程序整合到Oracle服務器中,雖然PL/SQL程序作為功能塊嵌入Oracle數(shù)據(jù)庫中，但PL/SQL與ORACLE數(shù)據(jù)庫的緊密結(jié)合使得越來越多的Oracle數(shù)據(jù)庫管理員和開發(fā)人員開始使用PL/SQL。
---------------------------------------------------------------------------------------------------
oracle常用Script
----------------
1、查看當前所有對象
-------------------
SQL> select * from tab;

2、建一個和a表結(jié)構(gòu)一樣的空表
-----------------------------
SQL> create table b as select * from a where 1=2;

SQL> create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;

3、察看數(shù)據(jù)庫的大小，和空間使用情況
--------------------------------
SQL> col tablespace format a20
SQL> select b.file_id　　文件ID,
　　b.tablespace_name　　表空間,
　　b.file_name　　　　　物理文件名,
　　b.bytes　　　　　　　總字節(jié)數(shù),
　　(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,
　　sum(nvl(a.bytes,0))　　　　　　　　剩余,
　　sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比
　　from dba_free_space a,dba_data_files b
　　where a.file_id=b.file_id
　　group by b.tablespace_name,b.file_name,b.file_id,b.bytes
　　order by b.tablespace_name
　　/
　　dba_free_space --表空間剩余空間狀況
　　dba_data_files --數(shù)據(jù)文件空間占用情況

4、查看現(xiàn)有回滾段及其狀態(tài)
-----------------------------
SQL> col segment format a30
SQL> Select SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;

5、查看數(shù)據(jù)文件放置的路徑
---------------------------
SQL> col file_name format a50
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

6、顯示當前連接用戶
---------------------
SQL> show user

7、把SQL*Plus當計算器
----------------------
SQL> select 100*20 from dual;

8、連接字符串
-----------
SQL> select 列1||列2 from 表1;
SQL> select concat(列1,列2) from 表1;

9、查詢當前日期
---------------
SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;

10、用戶間復制數(shù)據(jù)
-----------------
SQL> copy from user1 to user2 create table2 using select * from table1;

11、視圖中不能使用order by，但可用group by代替來達到排序目的
-----------------------------------------------------------
SQL> create view a as select b1,b2 from b group by b1,b2;

12、通過授權(quán)的方式來創(chuàng)建用戶
---------------------------
SQL> grant connect,resource to test identified by test;

SQL> conn test/test
--------------------------------------------------------------------------------------------------
CHAR,VARCHAR,VARCHAR2類型的區(qū)別與使用
-------------------------------------
區(qū)別：
1．CHAR的長度是固定的，而VARCHAR2的長度是可以變化的， 比如，存儲字符串"abc"，對于CHAR (20)，表示你存儲的字符將占20個字節(jié)(包括17個空字符)，而同樣的VARCHAR2 (20)則只占用3個字節(jié)的長度，20只是最大值，當你存儲的字符小于20時，按實際長度存儲。
2．CHAR的效率比VARCHAR2的效率稍高。
3．目前VARCHAR是VARCHAR2的同義詞。工業(yè)標準的VARCHAR類型可以存儲空字符串，但是oracle不這樣做，盡管它保留以后這樣做的權(quán)利。Oracle自己開發(fā)了一個數(shù)據(jù)類型VARCHAR2，這個類型不是一個標準的VARCHAR，它將在數(shù)據(jù)庫中varchar列可以存儲空字符串的特性改為存儲NULL值。如果你想有向后兼容的能力，Oracle建議使用VARCHAR2而不是VARCHAR。

何時該用CHAR，何時該用varchar2？
CHAR與VARCHAR2是一對矛盾的統(tǒng)一體，兩者是互補的關(guān)系.
VARCHAR2比CHAR節(jié)省空間，在效率上比CHAR會稍微差一些，即要想獲得效率，就必須犧牲一定的空間，這也就是我們在數(shù)據(jù)庫設計上常說的‘以空間換效率'。
VARCHAR2雖然比CHAR節(jié)省空間，但是如果一個VARCHAR2列經(jīng)常被修改，而且每次被修改的數(shù)據(jù)的長度不同，這會引起‘行遷移'(Row Migration)現(xiàn)象，而這造成多余的I/O，是數(shù)據(jù)庫設計和調(diào)整中要盡力避免的，在這種情況下用CHAR代替VARCHAR2會更好一些。