pl/sql的優(yōu)點
從版本6開始pl/sql就被可靠的整合到oracle中了，一旦掌握pl/sql的優(yōu)點以及其獨有的數(shù)據(jù)管理的便利性，那么你很難想象oracle缺了pl/sql的情形。pl/sql 不是一個獨立的產(chǎn)品，他是一個整合到oracle服務器和oracle工具中的技術(shù)，可以把pl/sql看作oracle服務器內(nèi)的一個引擎，sql語句執(zhí)行者處理單個的sql語句，pl/sql引擎處理pl/sql程序塊。當pl/sql程序塊在pl/sql引擎處理時，oracle服務器中的sql語句執(zhí)行器處理pl/sql程序塊中的sql語句。
pl/sql的優(yōu)點如下：
. pl/sql是一種高性能的基于事務處理的語言，能運行在任何oracle環(huán)境中，支持所有數(shù)據(jù)處理命令。通過使用pl/sql程序單元處理sql的數(shù)據(jù)定義和數(shù)據(jù)控制元素。
. pl/sql支持所有sql數(shù)據(jù)類型和所有sql函數(shù)，同時支持所有oracle對象類型
. pl/sql塊可以被命名和存儲在oracle服務器中，同時也能被其他的pl/sql程序或sql命令調(diào)用，任何客戶/服務器工具都能訪問pl/sql程序，具有很好的可重用性。
. 可以使用oracle數(shù)據(jù)工具管理存儲在服務器中的pl/sql程序的安全性�？梢允跈�(quán)或撤銷數(shù)據(jù)庫其他用戶訪問pl/sql程序的能力。
. pl/sql代碼可以使用任何ascii文本編輯器編寫，所以對任何oracle能夠運行的操作系統(tǒng)都是非常便利的
. 對于sql，oracle必須在同一時間處理每一條sql語句，在網(wǎng)絡環(huán)境下這就意味作每一個獨立的調(diào)用都必須被oracle服務器處理，這就占用大量的服務器時間，同時導致網(wǎng)絡擁擠。而pl/sql是以整個語句塊發(fā)給服務器，這就降低了網(wǎng)絡擁擠。
pl/sql塊結(jié)構(gòu)
pl/sql是一種塊結(jié)構(gòu)的語言，組成pl/sql程序的單元是邏輯塊，一個pl/sql 程序包含了一個或多個邏輯塊，每個塊都可以劃分為三個部分。與其他語言相同，變量在使用之前必須聲明，pl/sql提供了獨立的專門用于處理異常的部分，下面描述了pl/sql塊的不同部分：
聲明部分(declaration section)
聲明部分包含了變量和常量的數(shù)據(jù)類型和初始值。這個部分是由關(guān)鍵字declare開始，如果不需要聲明變量或常量，那么可以忽略這一部分；需要說明的是游標的聲明也在這一部分。
執(zhí)行部分(executable section)
執(zhí)行部分是pl/sql塊中的指令部分，由關(guān)鍵字begin開始，所有的可執(zhí)行語句都放在這一部分，其他的pl/sql塊也可以放在這一部分。
異常處理部分(exception section)
這一部分是可選的，在這一部分中處理異�；蝈e誤，對異常處理的詳細討論我們在后面進行。
pl/sql塊語法
[declare]
---declaration statements
begin
---executable statements
[exception]
---exception statements
end
pl/sql塊中的每一條語句都必須以分號結(jié)束，sql語句可以使多行的，但分號表示該語句的結(jié)束。一行中可以有多條sql語句，他們之間以分號分隔。每一個pl/sql塊由begin或declare開始，以end結(jié)束。注釋由--標示。
pl/sql塊的命名和匿名
pl/sql程序塊可以是一個命名的程序塊也可以是一個匿名程序塊。匿名程序塊可以用在服務器端也可以用在客戶端。
命名程序塊可以出現(xiàn)在其他pl/sql程序塊的聲明部分，這方面比較明顯的是子程序，子程序可以在執(zhí)行部分引用，也可以在異常處理部分引用。
pl/sql程序塊可背獨立編譯并存儲在數(shù)據(jù)庫中，任何與數(shù)據(jù)庫相連接的應用程序都可以訪問這些存儲的pl/sql程序塊。oracle提供了四種類型的可存儲的程序：
. 函數(shù)
. 過程
. 包
. 觸發(fā)器
函數(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
操作符
與其他程序設(shè)計語言相同，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ù)在設(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<=1000 loop
x:=x+10;
end loop;
y=x;
for...loop
語法：
for counter in [reverse] start_range....end_range loop
statements;
end loop;
loop和while循環(huán)的循環(huán)次數(shù)都是不確定的，for循環(huán)的循環(huán)次數(shù)是固定的，counter是一個隱式聲明的變量，他的初始值是start_range,第二個值是start_range+1,直到end_range,如果start_range等于end _range,那么循環(huán)將執(zhí)行一次。如果使用了reverse關(guān)鍵字，那么范圍將是一個降序。
x:=100;
for v_counter in 1..10 loop
x:=x+10;
end loop
y:=x;
如果要退出for循環(huán)可以使用exit語句。
標簽
用戶可以使用標簽使程序獲得更好的可讀性。程序塊或循環(huán)都可以被標記。標簽的形式是<>。
標記程序塊
<>
[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í)行g(shù)oto語句時，控制會立即轉(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的基礎(chǔ)語法以及如何使用pl/sql語言設(shè)計和運行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ù)庫設(shè)計上常說的‘以空間換效率'。
varchar2雖然比char節(jié)省空間，但是如果一個varchar2列經(jīng)常被修改，而且每次被修改的數(shù)據(jù)的長度不同，這會引起‘行遷移'(row migration)現(xiàn)象，而這造成多余的i/o，是數(shù)據(jù)庫設(shè)計和調(diào)整中要盡力避免的，在這種情況下用char代替varchar2會更好一些。