oca筆記及部分真題

SQL語(yǔ)句分類(lèi)

DDL:datedefinelanguage數(shù)據(jù)定義語(yǔ)句

createdropalter

DCL:datecontrollanguage數(shù)據(jù)控制語(yǔ)句

grantrevoke

DML:datemaniplationlanuage數(shù)據(jù)操作語(yǔ)句

deleteupdateinsert

ONDELETECASCADE

ALTERTABLEOrder_Items

ADDCONSTRAINTOrder_Items_Product_ID_FK

FOREIGNKEY(Product_ld)

REFERENCESProduct_Information

ONDELETECASCADE;

為表order_items(Product_id)添力口一個(gè)外鍵Order_ltems_Product_lD_FK

級(jí)聯(lián)product」nformation(product_id),加上ONDELETECASCADE后，如果主表

ordejitems刪除一條記錄，那么product_Information中對(duì)應(yīng)的關(guān)聯(lián)的數(shù)據(jù)也跟著刪除

PL/SQL

PL/SQL基礎(chǔ)

PL/SQL基礎(chǔ)

PL/SQL是ORACLE對(duì)標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)語(yǔ)言的擴(kuò)展，ORACLE公司已經(jīng)將PL/SQL整合

到ORACLE服務(wù)器和其他工具中了，近幾年中更多的開(kāi)發(fā)人員和DBA開(kāi)始使用PL/SQL,

本文將講述PL/SQL基礎(chǔ)語(yǔ)法，結(jié)構(gòu)和組件、以及如何設(shè)計(jì)并執(zhí)行一個(gè)PL/SQL程序。

PL/SQL的優(yōu)點(diǎn)

從版本6開(kāi)始PL/SQL就被可靠的整合到ORACLE中了，?旦掌握PL/SQL的優(yōu)點(diǎn)以

及其獨(dú)有的數(shù)據(jù)管理的便利性，那么你很難想象ORACLE缺了PL/SQL的情形。PL/SQL不

是一個(gè)獨(dú)立的產(chǎn)品，他是一個(gè)整合到ORACLE服務(wù)器和ORACLE工具中的技術(shù)，可以把

PL/SQL看作ORACLE服務(wù)器內(nèi)的一個(gè)引擎，sql語(yǔ)句執(zhí)行者處理單個(gè)的sql語(yǔ)句，PL/SQL

引擎處理PL/SQL程序塊。當(dāng)PL/SQL程序塊在PL/SQL引擎處理時(shí)，ORACLE服務(wù)器中的

SQL語(yǔ)句執(zhí)行器處理pl/sql程序塊中的SQL語(yǔ)句。

PL/SQL的優(yōu)點(diǎn)如下：

.PL/SQL是一種高性能的基于事務(wù)處理的語(yǔ)言，能運(yùn)行在任何ORACLE環(huán)境中，支持

所有數(shù)據(jù)處理命令。通過(guò)使用PL/SQL程序單元處理SQL的數(shù)據(jù)定義和數(shù)據(jù)控制元素。

.PL/SQL支持所有SQL數(shù)據(jù)類(lèi)型和所有SQL函數(shù)，同時(shí)支持所有ORACLE對(duì)象類(lèi)型

.PL/SQL塊可以被命名和存儲(chǔ)在ORACLE服務(wù)器中，同時(shí)也能被其他的PL/SQL程序

或SQL命令調(diào)用，任何客戶/服務(wù)器工具都能訪問(wèn)PL/SQL程序，具有很好的可重用性。

,可以使用ORACLE數(shù)據(jù)工具管理存儲(chǔ)在服務(wù)器中的PL/SQL程序的安全性?？梢允?/p>

權(quán)或撤銷(xiāo)數(shù)據(jù)庫(kù)其他用戶訪問(wèn)PL/SQL程序的能力。

.PUSQL代碼可以使用任何ASCII文本編輯器編寫(xiě)，所以對(duì)任何ORACLE能夠運(yùn)行的

操作系統(tǒng)都是非常便利的

.對(duì)于SQL,ORACLE必須在同一時(shí)間處理每一條SQL語(yǔ)句，在網(wǎng)絡(luò)環(huán)境下這就意味

作每一個(gè)獨(dú)立的調(diào)用都必須被oracle服務(wù)器處理，這就占用大量的服務(wù)器時(shí)間，同時(shí)導(dǎo)致網(wǎng)

絡(luò)擁擠。而PL/SQL是以整個(gè)語(yǔ)句塊發(fā)給服務(wù)器，這就降低了網(wǎng)絡(luò)擁擠。

PL/SQL塊結(jié)構(gòu)

PL/SQL是一種塊結(jié)構(gòu)的語(yǔ)言，組成PL/SQL程序的單元是邏輯塊，一個(gè)PL/SQL程序

包含了一個(gè)或多個(gè)邏輯塊，每個(gè)塊都可以劃分為三個(gè)部分。與其他語(yǔ)言相同，變量在使用之

前必須聲明，PL/SQL提供了獨(dú)立的專(zhuān)門(mén)用于處理異常的部分，下面描述了PL/SQL塊的不

同部分：

聲明部分(Declarationsection)

聲明部分包含了變量和常量的數(shù)據(jù)類(lèi)型和初始值。這個(gè)部分是由關(guān)鍵字DECLARE開(kāi)

始，如果不需要聲明變量或常量，那么可以忽略這一部分；需要說(shuō)明的是游標(biāo)的聲明也在這

一部分。

執(zhí)行部分(Executablesection)

執(zhí)行部分是PL/SQL塊中的指令部分，由關(guān)鍵字BEGIN開(kāi)始，所有的可執(zhí)行語(yǔ)句都放

在這一部分，其他的PL/SQL塊也可以放在這一部分。

異常處理部分(Exceptionsection)

這一部分是可選的，在這一部分中處理異?；蝈e(cuò)誤，對(duì)異常處理的詳細(xì)討論我們?cè)诤竺?/p>

進(jìn)行。

PL/SQL塊語(yǔ)法

[DECLARE]

declarationstatements

BEGIN

executablestatements

[EXCEPTION]

exceptionstatements

END

PL/SQL塊中的每一條語(yǔ)句都必須以分號(hào)結(jié)束，SQL語(yǔ)句可以使多行的，但分號(hào)表示該

語(yǔ)句的結(jié)束。一行中可以有多條SQL語(yǔ)句，他們之間以分號(hào)分隔。每一個(gè)PL/SQL塊由BEGIN

或DECLARE開(kāi)始，以END結(jié)束。注釋由一標(biāo)示。

PL/SQL塊的命名和匿名

PL/SQL程序塊可以是一個(gè)命名的程序塊也可以是一個(gè)匿名程序塊。匿名程序塊可以用

在服務(wù)器端也可以用在客戶端。

命名程序塊可以出現(xiàn)在其他PL/SQL程序塊的聲明部分，這方面比較明顯的是子程序，

子程序可以在執(zhí)行部分引用，也可以在異常處理部分引用。

PL/SQL程序塊可背獨(dú)立編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中，任何與數(shù)據(jù)庫(kù)相連接的應(yīng)用程序都可

以訪問(wèn)這些存儲(chǔ)的PL/SQL程序塊。ORACLE提供了四種類(lèi)型的可存儲(chǔ)的程序：

.函數(shù)

.過(guò)程

.包

.觸發(fā)器

函數(shù)

函數(shù)是命名了的、存儲(chǔ)在數(shù)據(jù)庫(kù)中的PL/SQL程序塊。函數(shù)接受零個(gè)或多個(gè)輸入?yún)?shù)，

有一個(gè)返回值，返回值的數(shù)據(jù)類(lèi)型在創(chuàng)建函數(shù)時(shí)定義。定義函數(shù)的語(yǔ)法如下：

FUNCTIONname[{parameter[,parameter,...])]RETURNdatatypesIS

[localdeclarations]

BEGIN

executestatements

[EXCEPTION

exceptionhandlers]

END[name]

過(guò)程

存儲(chǔ)過(guò)程是一個(gè)PL/SQL程序塊，接受零個(gè)或多個(gè)參數(shù)作為輸入(INPUT)或輸出

(OUTPUT),或既作輸入又作輸出(INOUT),與函數(shù)不同，存儲(chǔ)過(guò)程沒(méi)有返回值，存儲(chǔ)過(guò)程

不能由SQL語(yǔ)句直接使用，只能通過(guò)EXECUT命令或PL/SQL程序塊內(nèi)部調(diào)用，定義存儲(chǔ)

過(guò)程的語(yǔ)法如下：

PROCEDUREname[(parameter[,parameter,...|)|IS

[localdeclarations]

BEGIN

executestatements

[EXCEPTION

exceptionhandlers]

END[name]

包(package)

包其實(shí)就是被組合在一起的相關(guān)對(duì)象的集合，當(dāng)包中任何函數(shù)或存儲(chǔ)過(guò)程被調(diào)用，包就

被加載入內(nèi)存中，包中的任何函數(shù)或存儲(chǔ)過(guò)程的子程序訪問(wèn)速度將大大加快。

包由兩個(gè)部分組成：規(guī)范和包主體(body),規(guī)范描述變量、常量、游標(biāo)、和子程序，包體完全

定義子程序和游標(biāo)。

觸發(fā)器(trigger)

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

觸發(fā)器被觸發(fā)。變量和常量

變量存放在內(nèi)存中以獲得值，能被PUSQL塊引用。你可以把變量想象成一個(gè)可儲(chǔ)藏東

西的容器，容器內(nèi)的東西是可以改變的。

聲明變量

變量一般都在PL/SQL塊的聲明部分聲明，PL/SQL是一種強(qiáng)壯的類(lèi)型語(yǔ)言，這就是說(shuō)

在引用變量前必須首先聲明，要在執(zhí)行或異常處理部分使用變量，那么變量必須首先在聲明

部分進(jìn)行聲明。

聲明變量的語(yǔ)法如下：

Variable_name[CONSTANT]databyte[NOTNULL][:=IDEFAULTexpression]

注意:可以在聲明變量的同時(shí)給變量強(qiáng)制性的加上NOTNULL約束條件，此時(shí)變量在初

始化時(shí)必須賦值。

給變量賦值

給變量賺值有兩種方式：

.直接給變量賦值

X:=200;

Y=Y+(X*20);

.通過(guò)SQLSELECTINTO或FETCHINTO給變量賦值

SELECTSUM(SALARY),SUM(SALARY*O.1)

INTOTOTAL_SALARY,TATAL_COMMISSION

FROMEMPLOYEE

WHEREDEPT=10;

常量

常量與變量相似，但常量的值在程序內(nèi)部不能改變，常量的值在定義時(shí)賦予，，他的聲

明方式與變量相似，但必須包括關(guān)鍵字CONSTANT。常量和變量都可被定義為SQL和用戶

定義的數(shù)據(jù)類(lèi)型。

ZERO_VALUECONSTANTNUMBER:=0;

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

標(biāo)量(scalar)數(shù)據(jù)類(lèi)型

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

.number

.character

.date/time

.boolean

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

表1ScalarTypes:Numeric

Datatype

Range

Subtypes

description

BINARYJNTEGER

-214748-2147483647

NATURAL

NATURAL

NPOSITIVE

POSITIVEN

SIGNTYPE

用于存儲(chǔ)單字節(jié)整數(shù)。

要求存儲(chǔ)長(zhǎng)度低于NUMBER值。

用于限制范圍的子類(lèi)型(SUBTYPE):

NATURAL:用于非負(fù)數(shù)

POSITIVE:只用于正數(shù)

NATURALN:只用于非負(fù)數(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

存儲(chǔ)數(shù)字值，包括整數(shù)和浮點(diǎn)數(shù)?？梢赃x擇精度和刻度方式，語(yǔ)法：

number[([,])

缺省的精度是38,scale是0.

PLS_INTEGER

-2147483647-2147483647

與BINARY」NTEGER基本相同，但采用機(jī)器運(yùn)算時(shí)，PLS」NTEGER提供更好的性能。

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

datatype

rang

subtype

description

CHAR

最大長(zhǎng)度32767字節(jié)

CHARACTER

存儲(chǔ)定長(zhǎng)字符串，如果長(zhǎng)度沒(méi)有確定，缺省是1

LONG

最大長(zhǎng)度2147483647字節(jié)

存儲(chǔ)可變長(zhǎng)度字符串

RAW

最大長(zhǎng)度32767字節(jié)

用于存儲(chǔ)二進(jìn)制數(shù)據(jù)和字節(jié)字符串，當(dāng)在兩個(gè)數(shù)據(jù)庫(kù)之間進(jìn)行傳遞時(shí)，RAW數(shù)據(jù)不在字符

集之間進(jìn)行轉(zhuǎn)換。

LONGRAW

最大長(zhǎng)度2147483647

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

ROWID

18個(gè)字節(jié)

與數(shù)據(jù)庫(kù)ROWID偽列類(lèi)型相同，能夠存儲(chǔ)一個(gè)行標(biāo)示符，可以將行標(biāo)示符看作數(shù)據(jù)庫(kù)中每

一行的唯一鍵值。

VARCHAR2

最大長(zhǎng)度32767字節(jié)

STRINGVARCHAR

與VARCHAR數(shù)據(jù)類(lèi)型相似，存儲(chǔ)可變長(zhǎng)度的字符串。聲明方法與VARCHAR相同

表3DATE和BOOLEAN

datatype

range

description

BOOLEAN

TRUE/FALSE

存儲(chǔ)邏輯值TRUE或FALSE,無(wú)參數(shù)

DATE

01/01/4712BC

存儲(chǔ)固定長(zhǎng)的日期和時(shí)間值，日期值中包含時(shí)間

LOB數(shù)據(jù)類(lèi)型

LOB(大對(duì)象,Largeobject)數(shù)據(jù)類(lèi)型用于存儲(chǔ)類(lèi)似圖像，聲音這樣的大型數(shù)據(jù)對(duì)象,LOB

數(shù)據(jù)對(duì)象可以是二進(jìn)制數(shù)據(jù)也可以是字符數(shù)據(jù)，其最大長(zhǎng)度不超過(guò)4G。LOB數(shù)據(jù)類(lèi)型支持

任意訪問(wèn)方式，LONG只支持順序訪問(wèn)方式。LOB存儲(chǔ)在一個(gè)單獨(dú)的位置上，同時(shí)一個(gè)“LOB

定位符”(LOBlocator)存儲(chǔ)在原始的表中，該定位符是一個(gè)指向?qū)嶋H數(shù)據(jù)的指針。在PL/SQL

中操作LOB數(shù)據(jù)對(duì)象使用ORACLE提供的包DBMSJLOB.LOB數(shù)據(jù)類(lèi)型可分為以下四類(lèi)：

.BFILE

.BLOB

.CLOB

.NCLOB

操作符

與其他程序設(shè)計(jì)語(yǔ)言相同，PL7SQL有一系列操作符。操作符分為下面兒類(lèi):

.算術(shù)操作符

.關(guān)系操作符

.比較操作符

.邏輯操作符

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

operator

operation

除

**

乘方

關(guān)系操作符主要用于條件判斷語(yǔ)句或用于where子串中，關(guān)系操作符檢查條件和結(jié)果是

否為true或false,表5是PL/SQL中的關(guān)系操作符

operator

operation

<

小于操作符

<=

小于或等于操作符

>

大于操作符

>=

大于或等于操作符

等于操作符

不等于操作符

<>

不等于操作符

賦值操作符

表6顯示的是比較操作符

operator

operation

ISNULL

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

LIKE

比較字符串值

BETWEEN

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

IN

驗(yàn)證操作數(shù)在設(shè)定的一系列值中

表7.8顯示的是邏輯操作符

operator

operation

AND

兩個(gè)條件都必須滿足

OR

只要滿足兩個(gè)條件中的一個(gè)

NOT

取反

執(zhí)行部分

執(zhí)行部分包含了所有的語(yǔ)句和表達(dá)式，執(zhí)行部分以關(guān)鍵字BEGIN開(kāi)始，以關(guān)鍵字

EXCEPTION結(jié)束，如果EXCEPTION不存在，那么將以關(guān)鍵字END結(jié)束。分號(hào)分隔每一

條語(yǔ)句，使用賦值操作符二或SELECTINTO或FETCHINTO給每個(gè)變量賦值，執(zhí)行部分的

錯(cuò)誤將在異常處理部分解決，在執(zhí)行部分中可以使用另一個(gè)PL/SQL程序塊，這種程序塊被

稱(chēng)為嵌套塊

所有的SQL數(shù)據(jù)操作語(yǔ)句都可以用于執(zhí)行部分，PL/SQL塊不能再屏幕上顯示SELECT

語(yǔ)句的輸出。SELECT語(yǔ)句必須包括一個(gè)INTO子串或者是游標(biāo)的一部分，執(zhí)行部分使用的

變量和常量必須首先在聲明部分聲明，執(zhí)行部分必須至少包括一條可執(zhí)行語(yǔ)句，NULL是一

條合法的可執(zhí)行語(yǔ)句，事物控制語(yǔ)句COMMIT和ROLLBACK可以在執(zhí)行部分使用，數(shù)據(jù)

定義語(yǔ)言(DataDefinitionlanguage)不能在執(zhí)行部分中使用，DDL語(yǔ)句與EXECUTE

IMMEDIATE一起使用或者是DBMS_SQL調(diào)用。

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

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

示：

declare

v_comm_percentconstantnumber:=10;

begin

updateemp

setcomm=sal*v_comm_percent

wheredeptno=10;

end

SQL>/

PL/SQLproceduresuccessfullycompleted.

SQL>

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

createorreplaceprocedureupdate_commission

(v_deptinnumber,v_perventinnumberdefault10)is

begin

updateemp

setcomm=sal*v_percent

wheredeptno=v_dept;

end

SQL>/

Procedurecreated

SQL>executeupdate_commission(10,15);

PL/SQLproceduresuccessfullycompleted.

SQL>

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

進(jìn)字。

declare

v_deptnumber;

begin

selecta.deptno

intov_dept

fromempa

wherejob=,PRESIDENT,

update_commission(v_dept);

end

SQL>/

PL/SQLproceduresuccessfullycompleted

SQL>

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

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

語(yǔ)法和用途

IF..THEN

語(yǔ)法：

IFconditionTHEN

Statements1;

Statements2;

ENDIF

IF語(yǔ)句判斷條件condition是否為T(mén)RUE,如果是，則執(zhí)行THEN后面的語(yǔ)句，如果

condition為false或NULL則跳過(guò)THEN到ENDIF之間的語(yǔ)句，執(zhí)行ENDIF后面的語(yǔ)句。

IF..THEN...ELSE

語(yǔ)法：

IFconditionTHEN

Statements1;

Statements2;

ELSE

Statements1;

Statements2;

ENDIF

如果條件condition為T(mén)RUE,則執(zhí)行THEN到ELSE之間的語(yǔ)句，否則執(zhí)行ELSE至ljEND

IF之間的語(yǔ)句。

IF可以嵌套，可以在IF或加.上1^￡語(yǔ)句中使用中或正石1^￡語(yǔ)句。

if(a>b)and(a>c)then

g：=a;

else

g：=b;

ifc>gthen

g：=c;

endif

endif

IF..THEN..ELSIF

語(yǔ)法：

IFcondition1THEN

statement1;

ELSIFcondition?THEN

statement?;

ELSIFconditionsTHEN

statement?;

ELSE

statement4;

ENDIF;

statement5;

如果條件condition1為T(mén)RUE則執(zhí)行statement,然后執(zhí)行statements,否貝(I判斷condition2

是否為T(mén)RUE,若為T(mén)RUE則執(zhí)行statement2,然后執(zhí)行statements,對(duì)于conditions也是相同的，

condition),condition2,conditions都不成立，那么將執(zhí)行statement4然后執(zhí)行statementso

循環(huán)控制

循環(huán)控制的基本形式是LOOP語(yǔ)句，LOOP和ENDLOOP之間的語(yǔ)句將無(wú)限次的執(zhí)行。

LOOP語(yǔ)句的語(yǔ)法如下：

LOOP

statements;

ENDLOOP

LOOP和ENDLOOP之間的語(yǔ)句無(wú)限次的執(zhí)行顯然是不行的，那么在使用LOOP語(yǔ)句

時(shí)必須使用EXIT語(yǔ)句，強(qiáng)制循環(huán)結(jié)束，例如：

X:=100;

LOOP

X:=X+10;

IFX>1000THEN

EXIT;

ENDIF

ENDLOOP;

Y:=X;

此時(shí)Y的值是1010.

EXITWHEN語(yǔ)句將結(jié)束循環(huán)，如果條件為T(mén)RUE,則結(jié)束循環(huán)。

X:=100;

LOOP

X:=X+10;

EXITWHENX>1000;

X:=X+10;

ENDLOOP;

Y:=X;

WHILE..LOOP

WHILE..LOOP有一個(gè)條件與循環(huán)相聯(lián)系，如果條件為T(mén)RUE,則執(zhí)行循環(huán)體內(nèi)的語(yǔ)句,

如果結(jié)果為FALSE,則結(jié)束循環(huán)。

X:=100;

WHILEX<=1000LOOP

X:=X+10;

ENDLOOP;

Y=X;

FOR...LOOP

語(yǔ)法：

FORcounterIN[REVERSE]start_rangeend_rangeLOOP

statements;

ENDLOOP;

LOOP和WHILE循環(huán)的循環(huán)次數(shù)都是不確定的,FOR循環(huán)的循環(huán)次數(shù)是固定的,counter

是一個(gè)隱式聲明的變量，他的初始值是start_range,第二個(gè)值是start_range+l,直到end_range,

如果觸!1」《^等于€起」3*3那么循環(huán)將執(zhí)行一次。如果使用了REVERSE關(guān)鍵字，那么

范圍將是一個(gè)降序。

X:=100;

FORv_counterin1..10loop

X：=X+10；

endloop

y：=x;

如果要退出for循環(huán)可以使用EXIT語(yǔ)句。

標(biāo)簽

用戶可以使用標(biāo)簽使程序獲得更好的可讀性。程序塊或循環(huán)都可以被標(biāo)記。標(biāo)簽的形式

是<>。

標(biāo)記程序塊

<>

[DECLARE]

BEGIN

[EXCEPTION]

ENDlabel_name

標(biāo)記循環(huán)

<>

LOOP

<>

loop

<>

loop

EXITouter_loopWHENv_condition=0;

endloopinnermost_loop;

ENDLOOPinner_loop;

ENDLOOPouter_loop;

GOTO語(yǔ)句

語(yǔ)法：

GOTOLABEL;

執(zhí)行GOTO語(yǔ)句時(shí)，控制會(huì)立即轉(zhuǎn)到由標(biāo)簽標(biāo)記的語(yǔ)句。PL/SQL中對(duì)GOTO語(yǔ)句有

一些限制，對(duì)于塊、循環(huán)、IF語(yǔ)句而言，從外層跳轉(zhuǎn)到內(nèi)層是非法的。

X：=100;

FORV_COUNTERIN1..10LOOP

IFV_COUNTER=4THEN

GOTOend_ofLloop

ENDIF

X:=X+10;

<>

NULL

ENDLOOP

Y:=X;

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

嵌套

程序塊的內(nèi)部可以有另一個(gè)程序塊這種情況稱(chēng)為嵌套。嵌套要注意的是變量，定義在最

外部程序塊中的變量可以在所有子塊中使用，如果在子塊中定義了與外部程序塊變量相同的

變量名，在執(zhí)行子塊時(shí)將使用子塊中定義的變量。子塊中定義的變量不能被父塊引用。同樣

GOTO語(yǔ)句不能由父塊跳轉(zhuǎn)道子塊中，反之則是合法的。

《OUTERBLOCK》

DECLARE

A_NUMBERINTEGER；

B_NUMBERINTEGER；

BEGIN

-A.NUMBERandB_NUMBERareavailablehere

<>

DECLARE

C.NUMBERINTEGER

B.NUMBERNUMBER(20)

BEGIN

C_NUMBER:=A_NUMBER;

C_NUMBER=OUTER_BLOCK.B_NUMBER;

ENDSUB.BLOCK;

ENDOUT.BLOCK;

小結(jié)

我們?cè)谶@篇文章中介紹了PUSQL的基礎(chǔ)語(yǔ)法以及如何使用PL/SQL語(yǔ)言設(shè)計(jì)和運(yùn)行

PL/SQL程序塊，并將PL/SQL程序整合到Oracle服務(wù)器中，雖然PL/SQL程序作為功能塊嵌

入Oracle數(shù)據(jù)庫(kù)中，但PL/SQL與ORACLE數(shù)據(jù)庫(kù)的緊密結(jié)合使得越來(lái)越多的Oracle數(shù)據(jù)

庫(kù)管理員和開(kāi)發(fā)人員開(kāi)始使用PL/SQLo

extentmanagement

extentmanagement有兩種方式extentmanagementlocal;extentmanagementdictionary

默認(rèn)的是local

每種也有兩種大小增長(zhǎng)方式：

uniform：默認(rèn)為1M大小，在lemp表空間里為默認(rèn)的，但是不能被應(yīng)用在undo表空間

autoallocate:

Youcanconvertatablespacefromdictionaryextentmanagementtolocalextentmanagement

andbackwiththeOracle-suppliedPL/SQLpackageDBMS_SPACE_ADMIN.TheSYSTEM

tablespaceandanytemporarytablespaces,however,cannotbeconvertedfromlocaltothe

olderstyledictionarymanagem

兩種extent管理方式是可以相互轉(zhuǎn)換的，利用PL/SQLDBMS_SPACE_ADMIN

但是系統(tǒng)表空間和臨時(shí)表空間不能從local管理轉(zhuǎn)化到dictionary管理。

segmentmanagement

當(dāng)你利用了localextentmanagement,你可以手工還是自動(dòng)來(lái)管理段。

建立temp表空間

要是建立一個(gè)新的臨時(shí)表空間的話，必須要求system表空間是本地管理的

而且不需要備份和不會(huì)對(duì)redolog產(chǎn)生變化。

undotablespaces

undo表空間的用途

RollingbackatransactionexplicitlywithaROLLBACKstatement

Rollingbackatransactionimplicitly(forexample,throughrecoveryofafailedtransaction)

Reconstructingaread-consistentimageofdata

Recoveringfromlogicalcorruptions

表空間的改變

改名：

altertablespacefinrenametoyanwei;

添加數(shù)據(jù)文件：

altertablespaceyanweiadddatafile*****

size10g

轉(zhuǎn)移表空間中的數(shù)據(jù)文件步驟

I,把表空間offline

altertablespacereceivalesoffline;

2,利用系統(tǒng)命令重新copy

3,tell數(shù)據(jù)庫(kù)新位置

ALTERTABLESPACEreceivablesRENAMEDATAFILE

H:\ORACLE\ORADATA\ORA10\RECEIVABLES02.DBF'

TO'G:\ORACLE\ORADATA\ORA10\RECEIVABLES02.DBF;

4,onlinetablespace

altertablespace*online

當(dāng)一個(gè)表空間設(shè)置為readonly時(shí)候，做數(shù)據(jù)庫(kù)備份的時(shí)候不備份。

命令

altertablespace**readonly;

altertablespace**readwrite;

表空間備份

PuttingaTablespaceinBackupMode

ALTERTABLESPACEsystemBEGINBACKUP;

ALTERTABLESPACEsystemendBACKUP;

當(dāng)表空間在backupmode時(shí)候。數(shù)據(jù)文件還是可以讀寫(xiě)的，但是,些checkpoint的發(fā)生不會(huì)

記錄在數(shù)據(jù)文件的表頭。

獲取表空間的信息

dba_tablespaces

dba_data_files

dba_temp_files

v$tablespace

數(shù)據(jù)文件

重新定義大小

alterdatabasedatafile****resize100m;

可以定義自動(dòng)增長(zhǎng)

alterdatabasedatafile***autoextentonnext100mmaxsize1000m;

移動(dòng)文件位置

1,數(shù)據(jù)文件offline

alterdatabasedatafile****offline;

2,在系統(tǒng)操作copy

3,告訴database更新controlfile

alterdatabaserenamefile

***to****?

4,同步文件的表頭

recoverdatafile*****；

5,online文件

alterdatabasedatafile**online;

schemaobject

schemaobject計(jì)劃對(duì)象

計(jì)劃對(duì)象包括表索引這些在表空間中看到的

還包括contraints,view,synonyms,prtcedures,package但是一些數(shù)據(jù)庫(kù)對(duì)象:比如role,

表空間和目錄這些屬于計(jì)劃對(duì)象.

表

添加表或者column評(píng)論(comments)

最大可以寫(xiě)到4000bytes

語(yǔ)法：commentontable表名名字'內(nèi)容'

修改表名

rename表名to新表名

或者

altertable表名renameto新表名；

給表添加專(zhuān)欄(column)

altertable表名addcolumn專(zhuān)欄名數(shù)據(jù)類(lèi)型

刪除專(zhuān)欄

altertable表名dropcolumn專(zhuān)欄名；

改變專(zhuān)欄

altertable表名modify專(zhuān)欄名數(shù)據(jù)類(lèi)型；

參看約束

SELECTconstraint_name,constraint_type,r_constraint_name

FROMuser_constraints

WHEREtable_name='EMPLOYEES,;

CONS

CONSTRAINT_NAMETYPER_CONSTRAINT_NAME

NN_EMP_IDC

SYS_C005286C

EMPLOYEES.PKP

UNIQ_PAYROLL_IDU

EMP_DEPT_FKRDEPARTMENT_PK

MGR_EMP_FKREMPLOYEES_PK

HIRE_DATE_CHECKC

C就是check

P=PRIMARYKEY

R=Referential或者foreignkey

U=unique

或者

SELECTsearch_condition

FROMuser_constraints

WHEREconstraint_name=,SYS_C005286,;

臨時(shí)表

oracle下創(chuàng)建臨時(shí)表

兩種方法

當(dāng)前會(huì)話提交事務(wù)時(shí)保存臨時(shí)表數(shù)據(jù)，當(dāng)前會(huì)話結(jié)束時(shí)刪除臨時(shí)表數(shù)據(jù)，其它會(huì)話不能看

到該會(huì)話中該臨時(shí)表的數(shù)據(jù)。

CREATEGLOBALTEMPORARYTABLEyanwei

(murkeyvarchar2(10)))；

ONCOMMITPRESERVEROWS;

上不同的是提交事務(wù)時(shí)刪除臨時(shí)表中數(shù)據(jù)。

CREATEGLOBALTEMPORARYTABLE

(NORMALHELDDESC)

ONCOMMITDELETEROWS;

約束

約束包括：

notnull,unique,primarykey,renferntial,check,

UNIQUEconstraintscannotbecreatedoncolumns

oftypeCLOB,NCLOB,BLOB,LONG,LONGRAW,orTIMESTAMPWITHTIMEZONE.

不能在上面所說(shuō)的上面建立UNIQUE,null不算是，換句話就是說(shuō)可以有多個(gè)NULL?？?/p>

以利用索引來(lái)加強(qiáng)約束。

CREATETABLEemployees

(employee.idNUMBERNOTNULL

,hire_dateDATENOTNULL

,first_nameVARCHAR2(42)

,last_nameVARCHAR2(42)

,payroll_idVARCHAR2(10)

CONSTRAINTuniq_payroll_idUNIQUE(payroll_id)

USINGINDEXTABLESPACEindx

)；

在建立表后還可以加UNIQUE限制

ALTERTABLEemployeesADD

CONSTRAINTuniq_payroll_idUNIQUE(payrolljd)

USINGINDEXTABLESPACEindx

primaykey

一個(gè)表只能有一個(gè)primaykey,主要定義了primaykey,那么同時(shí)也就是notnull和unique,

強(qiáng)行利用indexo

和unique一樣，不能在CLOB,NCLOB,BLOB,LONG,LONGRAW,orTIMESTAMPWITH

TIMEZONE.上建立

改變約束

刪除

ALTERTABLEemployeesDROPCONSTRAINTvalidate_hire_date;

刪除主鍵

ALTERTABLEemployeesDROPPRIMARYKEYCASCADE;

修改約束名字

ALTERTABLEemployees

RENAMECONSTRAINTvalidate_hire_dateTOhire_date_check;

foreignkey

ALTERTABLEemployees

ADDCONSTRAINTemp_dept_fkFOREIGNKEY(dept_nbr)

REFERENCESdepartments(dept_nbr)ONDELETECASCADE;

ALTERTABLEdepartmentsADDCONSTRAINT

dept_mgr_fT<FOREIGNKEY(managerjd)REFERENCES

employees(employee_id)ONDELETESETNULL;

INDEX

PRIMARYKEYorUNIQUE是BTREE索引

索引有兩個(gè)：BTREE和BITMAP

建立索引

默認(rèn)都是btree

如

createindexemp_dept_nbrONemployees(dept_nbr)

也可以建立一個(gè)uniqueindex

如

createuniqueindexdname_uixONdepartments(dept_name);

同時(shí)可以在一個(gè)表上建立多個(gè)索引

CREATEINDEXemp_seniorityON

employees(dept_nbr,hire_date)

TABLESPACEindx;

建立位圖索引

CREATEBITMAPINDEXstate_bixONgeography(state);

CREATEBITMAPINDEXregion_bixONgeography(region);

CREATEBITMAPINDEXmetro_bixONgeogniphy(metro_area);

刪除索引

dropindex索引名字

重建索引

alterindex索引名字rebuild

把索引轉(zhuǎn)移表空間

alterindex索引名rebuildtablespace表空間名字

結(jié)合索引coalescing

ALTERINDEXuniq_payroll_idCOALESCE;

對(duì)索弓I重新命名

ALTERINDEXsys_c001428RENAMETOemployee_pk;

SEQUENCE

Oracle提供了sequence對(duì)象，由系統(tǒng)提供自增長(zhǎng)的序列號(hào),通常用于生成數(shù)據(jù)庫(kù)數(shù)據(jù)記錄的自

增長(zhǎng)主鍵或序號(hào)的地方.

下面介紹一下關(guān)于sequence的生成，修改，刪除等常用的操作：

1.創(chuàng)建Sequence

使用如下命令新建sequence(用戶需要有CREATESEQUENCE或者CREATEANY

SEQUENCE權(quán)限)：

CREATESEQUENCEtest_sequence

INCREMENTBY1-每次加的個(gè)數(shù)據(jù)

STARTWITH1一從1開(kāi)始計(jì)數(shù)

NOMAXVALUE-不設(shè)置最大值

NOCYCLE-一直累加，不循環(huán)

CACHE10;

［注意］

如果設(shè)置了CACHE值，ORACLE將在內(nèi)存里預(yù)先放置一些sequence,以使存取速度更快。

cache里面的取完后，oracle自動(dòng)再取一組到cache。但是，使用cache可能會(huì)跳號(hào)，當(dāng)遇

到數(shù)據(jù)庫(kù)突然異常down掉(shutdownabort),cache中的sequence就會(huì)丟失.

因此，推薦在createsequence的時(shí)候使用nocache選項(xiàng)。

2.使用sequence:

sequenee.CURRVAL—返回sequence的當(dāng)前值

sequence.NEXTVAL—增加sequence的值，然后返回sequence值

［注意］

第一次NEXTVAL返回的是初始值；

隨后的NEXTVAL會(huì)自動(dòng)增加你定義的INCREMENTBY值，然后返回增加后的值。

CURRVAL總是返回當(dāng)前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用

CURRVAL,否則會(huì)出錯(cuò)。

一次NEXTVAL會(huì)增加?次SEQUENCE的值，所以如果你在同一個(gè)語(yǔ)句里面使用多個(gè)

NEXTVAL,其值就是不?樣的。

sequence存儲(chǔ)在數(shù)據(jù)字典中,存儲(chǔ)于user_sequences表

LAST_NUMBER為最終序列號(hào)，也就是sequence游標(biāo)當(dāng)前所在的位置。

//getsequencelast_number

SELECTLAST_NUMBERFROMUSER_SEQUENCESWHERE

SEQUENCE.NAME=TEST_SEQNAME

//NEXTVAL使游標(biāo)指向下一位(增一或減一)

SELECTSEQNAME.NEXTVALFROMUSER_SEQUENCES得到下一位游標(biāo)的值

3.修改Sequence

用戶必須擁有ALTERANYSEQUENCE權(quán)限才能修改sequence.可以alter?除start至以外的

所有sequence參數(shù).

如果想要改變start值，必須dropsequence再re-create.

命令格式如下:

ALTERSEQUENCEtest_sequence

INCREMENTBY10

MAXVALUE10000

CYCLE-到10000后從頭開(kāi)始

NOCACHE;

4.刪除Sequence

DROPSEQUENCEorder_seq;

CADDBBCADCDBACDEDABBDBDCBDCAD

做錯(cuò)的題目

2.Automaticsegmentspacemanagementonthetablespacecauseswhichofthefollowingtable

attributesinthattablespacetobeignored?

A.Thewholestorageclause

B.NEXTandPCTINCREASE

C.BUFFERPOOLandFREEPOOL

D.PCTFREEandPCTUSED

D

D.Segmentspacemanagementreferstofreespacemanagement,withautomaticsegmentspace

managementusingbitmapsinsteadofFREELISTS,PCTFREE,andPCTUSED.

3.Whichobjectssharethesamenamespaceandthereforecannothavethesamename?

A.Tablesandindexes

B.Tablesandprocedures

C.Tablesandconstraints

D.Tablesandtriggers

3.B.Indexes,constraints,andtriggersallhaveseparatenamespaces.Tablesshareanamespace

with

views,sequences,privatesynonyms,procedures,functions,packages,materializedviews,and

user-definedtypes.Objectswithinthesameschemasharinganamespacemusthaveuniquenames

5.Withwhichparametersdoyouspecifyunlimiteddatafilegrowth?

A.MAXSIZEUNLIMITED

B.UNLIMITEDGROWTH

C.MAXEXTENTSUNLIMITED

D.Datafilesizecannotchange.

5.A.TheautoextendMAXSIZEparametertellsOraclehowlargeadataortempfilecangrowto.

UNLIMITEDspecifiesnoboundstotheautomaticgrowth.

8.Atablenamecanneverincludethespecialmeta-characterdollarsign($).

A.True

B.False

C.Onlyifthetablenameisenclosedindoublequotes

D.Onlyifthetablenameisenclosedinsinglequotes

11.WhichoperationcanyounotdotoatablethatiscreatedwiththefollowingSQLstatement?

CREATETABLEproperties

("Location"NUMBERprimarykey

,valueNUMBER(15)

Jotvarchar2(12)

,constraintpositive_valuecheck

(value>0)

)；

A.Renametheprimarykeytoproperties.

B.Insertanullintothevaluecolumn.

C.Addacolumnnamedowner.

D.Renametheindexsupportingprimarykeytoproperties.

E.Noneoftheabove.

11.E.Youcanrenamebothaconstraintandanindextothesamenameasatable-theyarein

separatenamespaces.Columnscanbeadded,andownerisavalidcolumnname.Ifthecheck

constraintconditionevaluatestoFALSE,thedatavaluewillnotbeallowed;ifthecondition

evaluates

toeitherTRUEorNULL,thevalueisallowed.

13.Whichconstraint-checkingmodelisthedefault?

A.Initiallyimmediateanddeferrable

B.Initiallyimmediateandnotdeferrable

C.Initiallydeferredandnotimmediately

D.Initiallydeferrableandnotimmediate

13.B.Constraintscanbecreatedasdeferrableandinitiallydeferred,butdeferredconstraint

checking

isnotthedefault.

14.Whichstatementonviewsistrue?

A.Aviewcanonlybeononebasetable,althoughthatbasetablecanbejoinedtoitself.

B.Aviewcannotbecreatedwiththesamenameandcolumnsasthebasetable.

C.Insertsintoaviewarenotallowed.

D.Privilegesonaviewcanbedifferentfromthoseonthebasetable.

14.D.Viewscanbecreatedononeormorebasetable.Viewssharethesamenamespaceastables

and

thereforecannothavethesamename;columns,however,canbenamedthesameasthebasetable.

SELECT,INSERT,UPDATE,andDELETEareallvalidoperationsonaview.Oneoftheusesfor

aview

istohideportionsofthebasetable,bygrantingdifferentprivilegestotheviewthanthebasetable.

15.Whatcantablespacesbeusedfbr?

A.Toorganizetablesandindexesintomanageablegroupings

B.Tomakesurethatdatastoredinthetablespacedoesnotchange

C.Tomovedatafromonedatabasetoanother

D.Alloftheabove

15.D.Theprimaryuseforatablespaceistoorganizetablesandindexesintomanageableunits.

Someofthemanageableoperationsthatyoucandotoatablespaceincludemakingitread-only

ormovingitfromonedatabasetoanother.

19.Howdoyouspecifythatatemporarytablewillbeemptiedattheendofauser'ssession?

A.CreatethetemporarytablewiththeONCOMMITPRESERVEROWSoption.

B.CreatethetemporarytablewiththeONDISCONNECTPRESERVEROWSoption.

C.CreatethetemporarytablewiththeONDISCONNECTPURGEROWSoption.

D.CreatethetemporarytablewiththeONCOMMITDELETEROWSoption.

19.A.TheoptionsfortemporarytablesareeitherONCOMMITDELETEROWS,withcauses

thetableto

flushattheendofeachtransaction,orONCOMMITPRESERVEROWS,whichcausesthetable

toflush

attheendofeachsession.

oraclenet連接方式

oraclenet連接方式

發(fā)表人:bulkauntI發(fā)表時(shí)間：2007年八月25S,17:15

有些文檔說(shuō)是五種，我看了10g官方文檔，只有以下4種，粗略記下。

1localnaming

連接方式如：CONNECTusemame/password@SERVICE_NAME（我們常用的就是這個(gè)）

修改tnsnames.ora文件，如下:

sales=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))

(CONNECT_DATA=

(SERVICE_NAME=)))

sqlnet.ora文件如下:

NAMES.DIRECTORY_PATH=(tnsnames,hostname)

2directorynaming

創(chuàng)建一個(gè)目錄，建立SERVICE_NAME的alias,然后再去訪問(wèn)，訪問(wèn)方式和localnaming類(lèi)

似

CONNECTusername/password@SERVICE_NAME

3easyconnectnaming

CONNECTusername/password@[//]host[:port][/service_name]

需要配置HOSTS文件，TNSNAMES.ORA和監(jiān)聽(tīng)文件

4externalnaming

eccadabcdccacwuadabwuad

cdcbddda

16.YoumanagealargenetworkofserverswithmanyOracle1Oginstancesconfiguredoneach

server.ThecompanywouldliketocentralizetheOracleservicesintoacommonlocationfor

easeofmaintenance.Whichofthefollowingwouldbethebestchoiceforcentralizednaming

services?

A.OracleEasyConnectNaming

B.Localnaming

C.OracleNamesServer

D.OracleInternetDirectory

dl6.D.OIDisOracle'spreferredcentralizednamingservice.OracleNamesServerisnolonger

supported

inOracle10g.

11.Whatarethewaysinwhichaclientcanresolveanetservicename?(Chooseallthatapply.)

A.Localnaming

B.Hostnaming

C.EasyConnectNaming

D.OracleGlobalNaming

E.Alltheabove

11.A,B,C.Oracleusesservicenamesinnetworksinmuchthesamewayitusessynonymsinthe

database.Servicenamesprovidelocationtransparencyandhidethecomplexityofconnect

stringinformation.YoucanconfigureOracleNettoconnectinseveralways,including

hostnaming,

localnaming,OID,andOracleEasyConnectNaming.OracleGlobalNamingisnot

avalidOracleoption.

13.Whichisarequirementforusinghostnaming?

A.Youmustusetnsnames.oraontheclient.

B.YoumustbeusingTCP/IP.

C.YoumusthaveanOIDpresent.

D.Youmusthaveasqlnet.orafilepresentontheclient.

E.Noneoftheabove.

13.B.HostnamingistypicallyusedinsmallinstallationsthathavefewOracledatabases.Thisis

an

attractiveoptionwhenyouwanttominimizeclient-sideconfiguration.TCP/IPisarequirement

whenyouusehostnaming.

14.Towhichofthechoicesbelowdoesthefollowingstatementapply?''Preventsdirect

communication

betweenaclientoutsidethecorporatenetworkandapplicationsinsidethecorporate

network.”

A.Proxy-basedfirewalls

B.Filter-basedfirewalls

C.Bothtypesoffirewalls

D.Neithertypeoffirewall

14.A.Proxy-basedfirewallspreventanydirectcontactbetweenaclientoutsidethecorporate

firewall

andapplicationsinsideacorporatefirewall.Filter-basedfirewallsinspectthepacketheadersbut

passthepacketonwithoutmodificationtothedestinationapplication.Proxy-basedfirewallsact

moreasarelaybetweenexternalclientsandinternalapplications.

4.YouwanttocentrallyadministeralltheOraclenetworkservicesinalargeOracle10g

installation

thatrunsmanynetworkservices.Whichofthefollowingfacilitieswouldbestprovidethis

functionalityatminimalcost?

A.AdvancedSecurity

B.HeterogeneousServices

C.OracleSharedServer

D.OracleInternetDirectory

4.D.The