oca筆記及部分真題

SQL語句分類

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

createdropalter

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

grantrevoke

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

deleteupdateinsert

ONDELETECASCADE

ALTERTABLEOrder_Items

ADDCONSTRAINTOrder_Items_Product_ID_FK

FOREIGNKEY(Product_ld)

REFERENCESProduct_Information

ONDELETECASCADE;

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

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

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

PL/SQL

PL/SQL基礎(chǔ)

PL/SQL基礎(chǔ)

PL/SQL是ORACLE對標準數(shù)據(jù)庫語言的擴展，ORACLE公司已經(jīng)將PL/SQL整合

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

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

PL/SQL的優(yōu)點

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

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

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

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

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

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

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

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

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

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

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

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

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

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

.PUSQL代碼可以使用任何ASCII文本編輯器編寫，所以對任何ORACLE能夠運行的

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

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

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

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

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

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

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

前必須聲明，PL/SQL提供了獨立的專門用于處理異常的部分，下面描述了PL/SQL塊的不

同部分：

聲明部分(Declarationsection)

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

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

一部分。

執(zhí)行部分(Executablesection)

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

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

異常處理部分(Exceptionsection)

這一部分是可選的，在這一部分中處理異?；蝈e誤，對異常處理的詳細討論我們在后面

進行。

PL/SQL塊語法

[DECLARE]

declarationstatements

BEGIN

executablestatements

[EXCEPTION]

exceptionstatements

END

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

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

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

PL/SQL塊的命名和匿名

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

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

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

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

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

以訪問這些存儲的PL/SQL程序塊。ORACLE提供了四種類型的可存儲的程序：

.函數(shù)

.過程

.包

.觸發(fā)器

函數(shù)

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

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

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

[localdeclarations]

BEGIN

executestatements

[EXCEPTION

exceptionhandlers]

END[name]

過程

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

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

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

過程的語法如下：

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

[localdeclarations]

BEGIN

executestatements

[EXCEPTION

exceptionhandlers]

END[name]

包(package)

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

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

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

定義子程序和游標。

觸發(fā)器(trigger)

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

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

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

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

聲明變量

變量一般都在PL/SQL塊的聲明部分聲明，PL/SQL是一種強壯的類型語言，這就是說

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

部分進行聲明。

聲明變量的語法如下：

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

注意:可以在聲明變量的同時給變量強制性的加上NOTNULL約束條件，此時變量在初

始化時必須賦值。

給變量賦值

給變量賺值有兩種方式：

.直接給變量賦值

X:=200;

Y=Y+(X*20);

.通過SQLSELECTINTO或FETCHINTO給變量賦值

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

INTOTOTAL_SALARY,TATAL_COMMISSION

FROMEMPLOYEE

WHEREDEPT=10;

常量

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

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

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

ZERO_VALUECONSTANTNUMBER:=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ù)類型。

表1ScalarTypes:Numeric

Datatype

Range

Subtypes

description

BINARYJNTEGER

-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」NTEGER基本相同，但采用機器運算時，PLS」NTEGER提供更好的性能。

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

datatype

rang

subtype

description

CHAR

最大長度32767字節(jié)

CHARACTER

存儲定長字符串，如果長度沒有確定，缺省是1

LONG

最大長度2147483647字節(jié)

存儲可變長度字符串

RAW

最大長度32767字節(jié)

用于存儲二進制數(shù)據(jù)和字節(jié)字符串，當(dāng)在兩個數(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相同

表3DATE和BOOLEAN

datatype

range

description

BOOLEAN

TRUE/FALSE

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

DATE

01/01/4712BC

存儲固定長的日期和時間值，日期值中包含時間

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

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

數(shù)據(jù)對象可以是二進制數(shù)據(jù)也可以是字符數(shù)據(jù)，其最大長度不超過4G。LOB數(shù)據(jù)類型支持

任意訪問方式，LONG只支持順序訪問方式。LOB存儲在一個單獨的位置上，同時一個“LOB

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

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

.BFILE

.BLOB

.CLOB

.NCLOB

操作符

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

.算術(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

ISNULL

如果操作數(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é)束。分號分隔每一

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

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

稱為嵌套塊

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

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

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

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

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

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

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

SQL*PLUS中匿名的PL/SQL塊的執(zhí)行是在PUSQL塊后輸入/來執(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>

如果在另一個命名程序塊或匿名程序塊中執(zhí)行這個程序，那么就不需要EXECUTE關(guā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)。

語法和用途

IF..THEN

語法：

IFconditionTHEN

Statements1;

Statements2;

ENDIF

IF語句判斷條件condition是否為TRUE,如果是，則執(zhí)行THEN后面的語句，如果

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

IF..THEN...ELSE

語法：

IFconditionTHEN

Statements1;

Statements2;

ELSE

Statements1;

Statements2;

ENDIF

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

IF之間的語句。

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

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

g：=a;

else

g：=b;

ifc>gthen

g：=c;

endif

endif

IF..THEN..ELSIF

語法：

IFcondition1THEN

statement1;

ELSIFcondition?THEN

statement?;

ELSIFconditionsTHEN

statement?;

ELSE

statement4;

ENDIF;

statement5;

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

是否為TRUE,若為TRUE則執(zhí)行statement2,然后執(zhí)行statements,對于conditions也是相同的，

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

循環(huán)控制

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

LOOP語句的語法如下：

LOOP

statements;

ENDLOOP

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

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

X:=100;

LOOP

X:=X+10;

IFX>1000THEN

EXIT;

ENDIF

ENDLOOP;

Y:=X;

此時Y的值是1010.

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

X:=100;

LOOP

X:=X+10;

EXITWHENX>1000;

X:=X+10;

ENDLOOP;

Y:=X;

WHILE..LOOP

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

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

X:=100;

WHILEX<=1000LOOP

X:=X+10;

ENDLOOP;

Y=X;

FOR...LOOP

語法：

FORcounterIN[REVERSE]start_rangeend_rangeLOOP

statements;

ENDLOOP;

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

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

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

范圍將是一個降序。

X:=100;

FORv_counterin1..10loop

X：=X+10；

endloop

y：=x;

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

標簽

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

是<>。

標記程序塊

<>

[DECLARE]

BEGIN

[EXCEPTION]

ENDlabel_name

標記循環(huán)

<>

LOOP

<>

loop

<>

loop

EXITouter_loopWHENv_condition=0;

endloopinnermost_loop;

ENDLOOPinner_loop;

ENDLOOPouter_loop;

GOTO語句

語法：

GOTOLABEL;

執(zhí)行GOTO語句時，控制會立即轉(zhuǎn)到由標簽標記的語句。PL/SQL中對GOTO語句有

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

X：=100;

FORV_COUNTERIN1..10LOOP

IFV_COUNTER=4THEN

GOTOend_ofLloop

ENDIF

X:=X+10;

<>

NULL

ENDLOOP

Y:=X;

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

嵌套

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

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

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

GOTO語句不能由父塊跳轉(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é)

我們在這篇文章中介紹了PUSQL的基礎(chǔ)語法以及如何使用PL/SQL語言設(shè)計和運行

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

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

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

extentmanagement

extentmanagement有兩種方式extentmanagementlocal;extentmanagementdictionary

默認的是local

每種也有兩種大小增長方式：

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

autoallocate:

Youcanconvertatablespacefromdictionaryextentmanagementtolocalextentmanagement

andbackwiththeOracle-suppliedPL/SQLpackageDBMS_SPACE_ADMIN.TheSYSTEM

tablespaceandanytemporarytablespaces,however,cannotbeconvertedfromlocaltothe

olderstyledictionarymanagem

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

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

segmentmanagement

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

建立temp表空間

要是建立一個新的臨時表空間的話，必須要求system表空間是本地管理的

而且不需要備份和不會對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ù)庫新位置

ALTERTABLESPACEreceivablesRENAMEDATAFILE

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

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

4,onlinetablespace

altertablespace*online

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

命令

altertablespace**readonly;

altertablespace**readwrite;

表空間備份

PuttingaTablespaceinBackupMode

ALTERTABLESPACEsystemBEGINBACKUP;

ALTERTABLESPACEsystemendBACKUP;

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

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

獲取表空間的信息

dba_tablespaces

dba_data_files

dba_temp_files

v$tablespace

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

重新定義大小

alterdatabasedatafile****resize100m;

可以定義自動增長

alterdatabasedatafile***autoextentonnext100mmaxsize1000m;

移動文件位置

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

alterdatabasedatafile****offline;

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

3,告訴database更新controlfile

alterdatabaserenamefile

***to****?

4,同步文件的表頭

recoverdatafile*****；

5,online文件

alterdatabasedatafile**online;

schemaobject

schemaobject計劃對象

計劃對象包括表索引這些在表空間中看到的

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

表空間和目錄這些屬于計劃對象.

表

添加表或者column評論(comments)

最大可以寫到4000bytes

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

修改表名

rename表名to新表名

或者

altertable表名renameto新表名；

給表添加專欄(column)

altertable表名addcolumn專欄名數(shù)據(jù)類型

刪除專欄

altertable表名dropcolumn專欄名；

改變專欄

altertable表名modify專欄名數(shù)據(jù)類型；

參看約束

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,;

臨時表

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

兩種方法

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

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

CREATEGLOBALTEMPORARYTABLEyanwei

(murkeyvarchar2(10)))；

ONCOMMITPRESERVEROWS;

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

CREATEGLOBALTEMPORARYTABLE

(NORMALHELDDESC)

ONCOMMITDELETEROWS;

約束

約束包括：

notnull,unique,primarykey,renferntial,check,

UNIQUEconstraintscannotbecreatedoncolumns

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

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

以利用索引來加強約束。

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

一個表只能有一個primaykey,主要定義了primaykey,那么同時也就是notnull和unique,

強行利用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索引

索引有兩個：BTREE和BITMAP

建立索引

默認都是btree

如

createindexemp_dept_nbrONemployees(dept_nbr)

也可以建立一個uniqueindex

如

createuniqueindexdname_uixONdepartments(dept_name);

同時可以在一個表上建立多個索引

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;

對索弓I重新命名

ALTERINDEXsys_c001428RENAMETOemployee_pk;

SEQUENCE

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

增長主鍵或序號的地方.

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

1.創(chuàng)建Sequence

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

SEQUENCE權(quán)限)：

CREATESEQUENCEtest_sequence

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

STARTWITH1一從1開始計數(shù)

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

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

CACHE10;

［注意］

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

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

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

因此，推薦在createsequence的時候使用nocache選項。

2.使用sequence:

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

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

［注意］

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

隨后的NEXTVAL會自動增加你定義的INCREMENTBY值，然后返回增加后的值。

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

CURRVAL,否則會出錯。

一次NEXTVAL會增加?次SEQUENCE的值，所以如果你在同一個語句里面使用多個

NEXTVAL,其值就是不?樣的。

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

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

//getsequencelast_number

SELECTLAST_NUMBERFROMUSER_SEQUENCESWHERE

SEQUENCE.NAME=TEST_SEQNAME

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

SELECTSEQNAME.NEXTVALFROMUSER_SEQUENCES得到下一位游標的值

3.修改Sequence

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

所有sequence參數(shù).

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

命令格式如下:

ALTERSEQUENCEtest_sequence

INCREMENTBY10

MAXVALUE10000

CYCLE-到10000后從頭開始

NOCACHE;

4.刪除Sequence

DROPSEQUENCEorder_seq;

CADDBBCADCDBACDEDABBDBDCBDCAD

做錯的題目

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ā)表時間：2007年八月25S,17:15

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

1localnaming

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

修改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)建一個目錄，建立SERVICE_NAME的alias,然后再去訪問，訪問方式和localnaming類

似

CONNECTusername/password@SERVICE_NAME

3easyconnectnaming

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

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

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