熱點推薦：
您现在的位置： 電腦知識網 >> 編程 >> Oracle >> 正文

OraclePL/SQL語言基礎

2022-06-13   來源: Oracle 

　　PL/SQL是ORACLE對標准數據庫語言的擴展ORACLE公司已經將PL/SQL整合到ORACLE 服務器和其他工具中了近幾年中更多的開發人員和DBA開始使用PL/SQL本文將講述PL/SQL基礎語法結構和組件以及如何設計並執行一個PL/SQL程序
 
　　PL/SQL的優點
 
　　從版本開始PL/SQL就被可靠的整合到ORACLE中了一旦掌握PL/SQL的優點以及其獨有的數據管理的便利性那麼你很難想象ORACLE缺了PL/SQL的情形PL/SQL 不是一個獨立的產品他是一個整合到ORACLE服務器和ORACLE工具中的技術可以把PL/SQL看作ORACLE服務器內的一個引擎sql語句執行者處理單個的sql語句PL/SQL引擎處理PL/SQL程序塊當PL/SQL程序塊在PL/SQL引擎處理時ORACLE服務器中的SQL語句執行器處理pl/sql程序塊中的SQL語句
 
　　PL/SQL的優點如下
 
　　 PL/SQL是一種高性能的基於事務處理的語言能運行在任何ORACLE環境中支持所有數據處理命令通過使用PL/SQL程序單元處理SQL的數據定義和數據控制元素
 
　　 PL/SQL支持所有SQL數據類型和所有SQL函數同時支持所有ORACLE對象類型
 
　　 PL/SQL塊可以被命名和存儲在ORACLE服務器中同時也能被其他的PL/SQL程序或SQL命令調用任何客戶/服務器工具都能訪問PL/SQL程序具有很好的可重用性
 
　　 可以使用ORACLE數據工具管理存儲在服務器中的PL/SQL程序的安全性可以授權或撤銷數據庫其他用戶訪問PL/SQL程序的能力
 
　　 PL/SQL代碼可以使用任何ASCII文本編輯器編寫所以對任何ORACLE能夠運行的操作系統都是非常便利的
 
　　 對於SQLORACLE必須在同一時間處理每一條SQL語句在網絡環境下這就意味作每一個獨立的調用都必須被oracle服務器處理這就占用大量的服務器時間同時導致網絡擁擠而PL/SQL是以整個語句塊發給服務器這就降低了網絡擁擠
 
　　PL/SQL塊結構
 
　　PL/SQL是一種塊結構的語言組成PL/SQL程序的單元是邏輯塊一個PL/SQL 程序包含了一個或多個邏輯塊每個塊都可以劃分為三個部分與其他語言相同變量在使用之前必須聲明PL/SQL提供了獨立的專門用於處理異常的部分下面描述了PL/SQL塊的不同部分
 
　　聲明部分(Declaration section)
 
　　聲明部分包含了變量和常量的數據類型和初始值這個部分是由關鍵字DECLARE開始如果不需要聲明變量或常量那麼可以忽略這一部分需要說明的是游標的聲明也在這一部分
 
　　執行部分(Executable section)
 
　　執行部分是PL/SQL塊中的指令部分由關鍵字BEGIN開始所有的可執行語句都放在這一部分其他的PL/SQL塊也可以放在這一部分
 
　　異常處理部分(Exception section)
 
　　這一部分是可選的在這一部分中處理異常或錯誤對異常處理的詳細討論我們在後面進行
 
　　PL/SQL塊語法
 
[DECLARE]
declaration statements
BEGIN
executable statements
[EXCEPTION]
exception statements
END
  
 
　　PL/SQL塊中的每一條語句都必須以分號結束SQL語句可以使多行的但分號表示該語句的結束一行中可以有多條SQL語句他們之間以分號分隔每一個PL/SQL塊由BEGIN或DECLARE開始以END結束注釋由標示
 
　　PL/SQL塊的命名和匿名
 
　　PL/SQL程序塊可以是一個命名的程序塊也可以是一個匿名程序塊匿名程序塊可以用在服務器端也可以用在客戶端
 
　　命名程序塊可以出現在其他PL/SQL程序塊的聲明部分這方面比較明顯的是子程序子程序可以在執行部分引用也可以在異常處理部分引用
 
　　PL/SQL程序塊可背獨立編譯並存儲在數據庫中任何與數據庫相連接的應用程序都可以訪問這些存儲的PL/SQL程序塊ORACLE提供了四種類型的可存儲的程序
 
　　　 函數
 
　　　 過程
 
　　　 包
 
　　　 觸發器
 
　　       函數
 
　　函數是命名了的存儲在數據庫中的PL/SQL程序塊函數接受零個或多個輸入參數有一個返回值返回值的數據類型在創建函數時定義定義函數的語法如下
 
FUNCTION name [{parameter[parameter])] RETURN datatypes IS
[local declarations]
BEGIN
execute statements
[EXCEPTION
exception handlers]
END [name]
  
 
　　過程
 
　　存儲過程是一個PL/SQL程序塊接受零個或多個參數作為輸入(INPUT)或輸出(OUTPUT)或既作輸入又作輸出(INOUT)與函數不同存儲過程沒有返回值存儲過程不能由SQL語句直接使用只能通過EXECUT命令或PL/SQL程序塊內部調用定義存儲過程的語法如下
 
PROCEDURE name [(parameter[parameter])] IS
[local declarations]
BEGIN
execute statements
[EXCEPTION
exception handlers ]
END [name]
  
 
　　包(package)
 
　　包其實就是被組合在一起的相關對象的集合當包中任何函數或存儲過程被調用包就被加載入內存中包中的任何函數或存儲過程的子程序訪問速度將大大加快
包由兩個部分組成規范和包主體(body)規范描述變量常量游標和子程序包體完全定義子程序和游標
 
　　觸發器(trigger)
 
　　觸發器與一個表或數據庫事件聯系在一起的當一個觸發器事件發生時定義在表上的觸發器被觸發
 
變量和常量
 
　　變量存放在內存中以獲得值能被PL/SQL塊引用你可以把變量想象成一個可儲藏東西的容器容器內的東西是可以改變的
 
　　聲明變量
 
　　變量一般都在PL/SQL塊的聲明部分聲明PL/SQL是一種強壯的類型語言這就是說在引用變量前必須首先聲明要在執行或異常處理部分使用變量那麼變量必須首先在聲明部分進行聲明
 
　　聲明變量的語法如下
 
Variable_name [CONSTANT] databyte [NOT NULL][:=|DEFAULT expression]
  
 
　　注意:可以在聲明變量的同時給變量強制性的加上NOT NULL約束條件此時變量在初始化時必須賦值
 
　　給變量賦值
 
　　給變量賦值有兩種方式
 
　　 直接給變量賦值
 
　　　X:=;
　　　Y=Y+(X*);
 
　　 通過SQL SELECT INTO 或FETCH INTO給變量賦值
 
SELECT SUM(SALARY)SUM(SALARY*)
INTO TOTAL_SALARYTATAL_COMMISSION
FROM EMPLOYEE
WHERE DEPT=;
 
　　常量
 
　　常量與變量相似但常量的值在程序內部不能改變常量的值在定義時賦予他的聲明方式與變量相似但必須包括關鍵字CONSTANT常量和變量都可被定義為SQL和用戶定義的數據類型
 
ZERO_VALUE CONSTANT NUMBER:=;
  
 
　　這個語句定了一個名叫ZERO_VALUE數據類型是NUMBER值為的常量
 
　　標量(scalar)數據類型
 
　　標量(scalar)數據類型沒有內部組件他們大致可分為以下四類
 
　　　 number
　　　 character
　　　 date/time
　　　 boolean
 
　　表顯示了數字數據類型表顯示了字符數據類型表顯示了日期和布爾數據類型
 
　　表 Scalar Types:Numeric
 
Datatype
  Range
  Subtypes
  description
  
BINARY_INTEGER
 
  NATURAL
NATURAL
NPOSITIVE
POSITIVEN
SIGNTYPE 
  用於存儲單字節整數
要求存儲長度低於NUMBER值
用於限制范圍的子類型(SUBTYPE):
　NATURAL:用於非負數
　POSITIVE:只用於正數
　NATURALN:只用於非負數和非NULL值
　POSITIVEN:只用於正數不能用於NULL值
　SIGNTYPE:只有值:或
  
NUMBER
  EE
  DEC
DECIMAL
DOUBLE 
PRECISION
FLOAT 
INTEGERIC
INT
NUMERIC
REAL
SMALLINT
  存儲數字值包括整數和浮點數可以選擇精度和刻度方式語法
number[（[]）]
缺省的精度是scale是
  
PLS_INTEGER
 
   
  與BINARY_INTEGER基本相同但采用機器運算時PLS_INTEGER提供更好的性能
  
 
　　表 字符數據類型 
 
datatype
  rang
  subtype
  description
  
CHAR
  最大長度字節 
  CHARACTER
  存儲定長字符串如果長度沒有確定缺省是
  
LONG
  最大長度字節
   
  存儲可變長度字符串
  
RAW 
  最大長度字節
   
  用於存儲二進制數據和字節字符串當在兩個數據庫之間進行傳遞時RAW數據不在字符集之間進行轉換
  
LONGRAW
  最大長度
   
  與LONG數據類型相似同樣他也不能在字符集之間進行轉換
  
ROWID
  個字節
   
  與數據庫ROWID偽列類型相同能夠存儲一個行標示符可以將行標示符看作數據庫中每一行的唯一鍵值
  
VARCHAR
  最大長度字節
  STRINGVARCHAR
  與VARCHAR數據類型相似存儲可變長度的字符串聲明方法與VARCHAR相同 
  
 
　　表 DATE和BOOLEAN
 
datatype
  range 
  description
  
BOOLEAN
  TRUE/FALSE
  存儲邏輯值TRUE或FALSE無參數
  
DATE
  // BC 
  存儲固定長的日期和時間值日期值中包含時間
  
 

　　LOB數據類型
 
　　LOB(大對象Large object) 數據類型用於存儲類似圖像聲音這樣的大型數據對象LOB數據對象可以是二進制數據也可以是字符數據其最大長度不超過GLOB數據類型支持任意訪問方式LONG只支持順序訪問方式LOB存儲在一個單獨的位置上同時一個LOB定位符(LOB locator)存儲在原始的表中該定位符是一個指向實際數據的指針在PL/SQL中操作LOB數據對象使用ORACLE提供的包DBMS_LOBLOB數據類型可分為以下四類
 
　　 BFILE
　　 BLOB
　　 CLOB
　　 NCLOB
 
　　操作符
 
　　與其他程序設計語言相同PL/SQL有一系列操作符操作符分為下面幾類
 
　　 算術操作符
 
　　 關系操作符
 
　　 比較操作符
 
　　 邏輯操作符
 
　　算術操作符如表所示
 
operator
  operation
  
+  加
    減
  /  除
  
*  乘 
** 乘方
  
 
　　關系操作符主要用於條件判斷語句或用於where子串中關系操作符檢查條件和結果是否為true或false表是PL/SQL中的關系操作符
 
operator
  operation
  
< 
  小於操作符
  
<= 
  小於或等於操作符
  
> 
  大於操作符
  
>=
  大於或等於操作符
  
= 
  等於操作符 
  
!= 
  不等於操作符
  
<> 
  不等於操作符
  
:= 
  賦值操作符
  
 
　　表 顯示的是比較操作符
 
operator 
  operation
  
IS NULL
  如果操作數為NULL返回TRUE
  
LIKE
  比較字符串值
  
BETWEEN
  驗證值是否在范圍之內
  
IN
  驗證操作數在設定的一系列值中
  
 
　　表顯示的是邏輯操作符
 
operator
  operation
  
AND 
  兩個條件都必須滿足
  
OR
  只要滿足兩個條件中的一個
  
NOT
  取反
  
 
　　執行部分
 
　　執行部分包含了所有的語句和表達式執行部分以關鍵字BEGIN開始以關鍵字EXCEPTION結束如果EXCEPTION不存在那麼將以關鍵字END結束分號分隔每一條語句使用賦值操作符:=或SELECT INTO或FETCH INTO給每個變量賦值執行部分的錯誤將在異常處理部分解決在執行部分中可以使用另一個PL/SQL程序塊這種程序塊被稱為嵌套塊
 
　　所有的SQL數據操作語句都可以用於執行部分PL/SQL塊不能再屏幕上顯示SELECT語句的輸出SELECT語句必須包括一個INTO子串或者是游標的一部分執行部分使用的變量和常量必須首先在聲明部分聲明執行部分必須至少包括一條可執行語句NULL是一條合法的可執行語句事物控制語句COMMIT和ROLLBACK可以在執行部分使用數據定義語言(Data Definition language)不能在執行部分中使用DDL語句與EXECUTE IMMEDIATE一起使用或者是DBMS_SQL調用
 
　　執行一個PL/SQL塊
 
　　SQL*PLUS中匿名的PL/SQL塊的執行是在PL/SQL塊後輸入/來執行如下面的例子所示
 
declare 
　v_comm_percent constant number:=;
begin
　update emp
　set comm=sal*v_comm_percent
　where deptno=;
　end
SQL> /
PL/SQL procedure successfully completed
 
SQL>
  
 
　　命名的程序與匿名程序的執行不同執行命名的程序塊必須使用execute關鍵字
 
create or replace procedure update_commission
　(v_dept in numberv_pervent in number default ) is 
begin
　update emp
　set comm=sal*v_percent
　where deptno=v_dept;
end
 
SQL>/
 
Procedure created
 
SQL>execute update_commission();
 
PL/SQL procedure successfully completed
 
SQL> 
  
 
　　如果在另一個命名程序塊或匿名程序塊中執行這個程序那麼就不需要EXECUTE關進字
 
declare
　v_dept number;
begin
　select adeptno
　into v_dept
　from emp a
　where job=PRESIDENT
　update_commission(v_dept);
end
SQL>/
　PL/SQL procedure successfully completed
SQL>
  
 
　控制結構
 
　　控制結構控制PL/SQL程序流程的代碼行PL/SQL支持條件控制和循環控制結構
 
　　語法和用途
 
　　　IFTHEN
 
　　語法
 
IF condition THEN
　Statements ;
　Statements ;
　
END IF 
  
 
　　IF語句判斷條件condition是否為TRUE如果是則執行THEN後面的語句如果condition為false或NULL則跳過THEN到END IF之間的語句執行END IF後面的語句
 
　　IFTHENELSE
 
　　語法:
 
IF condition THEN
　Statements ;
　Statements ;
　
ELSE
　Statements ;
　Statements ;
　
END IF 
  
 
　　如果條件condition為TRUE則執行THEN到ELSE之間的語句否則執行ELSE到END IF之間的語句
 
　　IF 可以嵌套可以在IF 或IF ELSE語句中使用IF或IFELSE語句
 
if (a>b) and (a>c) then
　　g:=a;
else
　　g:=b;
　　if c>g then
　　　g:=c;
　　end if
end if
  
 
　　IFTHENELSIF
 
　　語法
 
IF condition THEN
　statement;
ELSIF condition THEN
　statement;
ELSIF condition THEN
　statement;
ELSE
　statement;
END IF;
　statement;
  
 
　　如果條件condition為TRUE則執行statement然後執行statement否則判斷condition是否為TRUE若為TRUE則執行statement然後執行statement對於condition也是相同的如果conditionconditioncondition都不成立那麼將執行statement然後執行statement
 

　　循環控制
 
　　循環控制的基本形式是LOOP語句LOOP和END LOOP之間的語句將無限次的執行LOOP語句的語法如下
 
　　LOOP 
　　　statements;
　　END LOOP
 
　　LOOP和END LOOP之間的語句無限次的執行顯然是不行的那麼在使用LOOP語句時必須使用EXIT語句強制循環結束例如
 
X:=;
LOOP
　X:=X+;
　IF X> THEN
　　EXIT;
　END IF
END LOOP;
Y:=X;
  
 
　　此時Y的值是
 
　　EXIT WHEN語句將結束循環如果條件為TRUE則結束循環
 
X:=;
LOOP
X:=X+;
EXIT WHEN X>;
X:=X+;
END LOOP;
Y:=X;
  
 
　　WHILELOOP
 
　　WHILELOOP有一個條件與循環相聯系如果條件為TRUE則執行循環體內的語句如果結果為FALSE則結束循環
 
X:=;
WHILE X<= LOOP
　X:=X+;
END LOOP;
Y=X; 
  
 
　　FORLOOP
 
　　語法
 
FOR counter IN [REVERSE] start_rangeend_range LOOP
statements;
END LOOP;
  
 
　　LOOP和WHILE循環的循環次數都是不確定的FOR循環的循環次數是固定的counter是一個隱式聲明的變量他的初始值是start_range第二個值是start_range+直到end_range如果start_range等於end _range那麼循環將執行一次如果使用了REVERSE關鍵字那麼范圍將是一個降序
 
X:=;
FOR v_counter in loop
x:=x+;
 
end loop
y:=x;
  
 
　　如果要退出for循環可以使用EXIT語句
 
　　標簽
 
　　用戶可以使用標簽使程序獲得更好的可讀性程序塊或循環都可以被標記標簽的形式是<>
 
　　標記程序塊
 
<>
[DECLARE]

BEGIN

[EXCEPTION]

END label_name
  
 
　　標記循環
 
<>
LOOP

<>
loop

<>
loop

 
EXIT outer_loop WHEN v_condition=;
end loop innermost_loop;

END LOOP inner_loop;
END LOOP outer_loop; 
  
 
　　GOTO語句
 
　　語法
 
　　GOTO LABEL;
 
　　執行GOTO語句時控制會立即轉到由標簽標記的語句PL/SQL中對GOTO語句有一些限制對於塊循環IF語句而言從外層跳轉到內層是非法的
 
X =;
FOR V_COUNTER IN LOOP
　IF V_COUNTER = THEN
　　GOTO end_of_loop
　END IF
　X:=X+;
　<>
　NULL
END LOOP
 
Y:=X; 
  
 
　　注意NULL是一個合法的可執行語句
 
　　嵌套
 
　　程序塊的內部可以有另一個程序塊這種情況稱為嵌套嵌套要注意的是變量定義在最外部程序塊中的變量可以在所有子塊中使用如果在子塊中定義了與外部程序塊變量相同的變量名在執行子塊時將使用子塊中定義的變量子塊中定義的變量不能被父塊引用同樣GOTO語句不能由父塊跳轉道子塊中反之則是合法的
 
《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()
BEGIN
　C_NUMBER:=A_NUMBER;
　C_NUMBER=OUTER_BLOCKB_NUMBER;
END SUB_BLOCK;
END OUT_BLOCK;
  
 
　　小結
 
　 我們在這篇文章中介紹了PL/SQL的基礎語法以及如何使用PL/SQL語言設計和運行PL/SQL程序塊並將PL/SQL程序整合到Oracle服務器中雖然PL/SQL程序作為功能塊嵌入Oracle數據庫中但PL/SQL與ORACLE數據庫的緊密結合使得越來越多的Oracle數據庫管理員和開發人員開始使用PL/SQL

　　
Oracle常用Script

　　查看當前所有對象 
 
SQL> select * from tab; 
 
建一個和a表結構一樣的空表 
 
SQL> create table b as select * from a where =; 
 
SQL> create table b(bbb) as select aaa from a where =; 
 
察看數據庫的大小和空間使用情況 
 
SQL> col tablespace format a
SQL> select bfile_id　　文件ID
　　btablespace_name　　表空間
　　bfile_name　　　　　物理文件名
　　bbytes　　　　　　　總字節數
　　(bbytessum(nvl(abytes)))　　　已使用
　　sum(nvl(abytes))　　　　　　　　剩余
　　sum(nvl(abytes))/(bbytes)*　剩余百分比 
　　from dba_free_space adba_data_files b 
　　where afile_id=bfile_id 
　　group by btablespace_namebfile_namebfile_idbbytes 
　　order by btablespace_name
　　/
　　dba_free_space 表空間剩余空間狀況
　　dba_data_files 數據文件空間占用情況
 
查看現有回滾段及其狀態 
 
SQL> col segment format a
SQL> SELECT SEGMENT_NAMEOWNERTABLESPACE_NAMESEGMENT_IDFILE_IDSTATUS FROM DBA_ROLLBACK_SEGS; 
 
查看數據文件放置的路徑 
 
SQL> col file_name format a
SQL> select tablespace_namefile_idbytes//file_name from dba_data_files order by file_id; 
 
顯示當前連接用戶 
 
SQL> show user 
 
把SQL*Plus當計算器 
 
SQL> select * from dual; 
 
連接字符串 
 
SQL> select 列||列 from 表;
SQL> select concat(列列) from 表; 
 
查詢當前日期 
 
SQL> select to_char(sysdateyyyymmddhh:mi:ss) from dual; 
 
用戶間復制數據 
 
SQL> copy from user to user create table using select * from table; 
 
視圖中不能使用order by但可用group by代替來達到排序目的 
 
SQL> create view a as select bb from b group by bb; 
 
通過授權的方式來創建用戶 
 
SQL> grant connectresource to test identified by test; 
 
SQL> conn test/test

CHARVARCHARVARCHAR類型的區別與使用

區別
．CHAR的長度是固定的而VARCHAR的長度是可以變化的 比如存儲字符串abc對於CHAR ()表示你存儲的字符將占個字節(包括個空字符)而同樣的VARCHAR ()則只占用個字節的長度只是最大值當你存儲的字符小於時按實際長度存儲
．CHAR的效率比VARCHAR的效率稍高
．目前VARCHAR是VARCHAR的同義詞工業標准的VARCHAR類型可以存儲空字符串但是oracle不這樣做盡管它保留以後這樣做的權利Oracle自己開發了一個數據類型VARCHAR這個類型不是一個標准的VARCHAR它將在數據庫中varchar列可以存儲空字符串的特性改為存儲NULL值如果你想有向後兼容的能力Oracle建議使用VARCHAR而不是VARCHAR
 
何時該用CHAR何時該用varchar？
           CHAR與VARCHAR是一對矛盾的統一體兩者是互補的關系
VARCHAR比CHAR節省空間在效率上比CHAR會稍微差一些即要想獲得效率就必須犧牲一定的空間這也就是我們在數據庫設計上常說的以空間換效率
   VARCHAR雖然比CHAR節省空間但是如果一個VARCHAR列經常被修改而且每次被修改的數據的長度不同這會引起行遷移(Row Migration)現象而這造成多余的I/O是數據庫設計和調整中要盡力避免的在這種情況下用CHAR代替VARCHAR會更好一些


From:http://tw.wingwit.com/Article/program/Oracle/201311/16905.html
    《OraclePL/SQL語言基礎》本文是由Oracle免費提供，內容來源於互聯網,本文歸原作者所有。
  • 上一篇文章：

  • 下一篇文章：
    • 相關文章
    主板 CPU 硬盤 內存 顯卡 聲卡 顯示器 光驅 死機
    推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.