大型數(shù)據(jù)庫-2過程與函數(shù)

過程與函數(shù)塊不能在數(shù)據(jù)庫中。不能從其他PL/SQL塊中進行調(diào)用。過程、函數(shù)、包和觸發(fā)器都是帶名塊，可以在數(shù)據(jù)庫中，可以在需要的任何地方運行。7.1

使用過程7.1.1創(chuàng)建過程例：--addstud.sqlCreate

or

replace

procedure

addnewstudent(p_

name

students.

_name%type,p_lastname

students.last_name%type,p_major

students.major%type)asbegininsert

into

students(id,

_name,last_name,major,current_credits)name,values

(student_sequence.nextval,p_p_lastname,p_major,0);commit;End

addnewstudent;在創(chuàng)建了這個塊以后，

可以從另一個塊中調(diào)用他，如：Beginaddnewstudent(‘david’,’dinsmore’,’music’);End;首先使用create

or

replace

procedure語句創(chuàng)建過程

addnewstudent。創(chuàng)建過程的時候，他首先被編譯，然后以編譯過的形式在數(shù)據(jù)庫中。以后可以從其他的PL/SQL塊調(diào)用這段被編譯過的代碼。在調(diào)用過程的時候可以傳遞參數(shù)一個過程調(diào)用本身就是一個PL/SQL語句。該調(diào)用不作為表達式的一部分。當(dāng)調(diào)用過程的時候，控制將轉(zhuǎn)給該過程的第一條可執(zhí)行語句。當(dāng)過程結(jié)束時，控制重新交還給緊挨著該過程調(diào)用的下一個語句。過程是一個PL/SQL塊，帶有部分、執(zhí)行部分和異常部分，只有執(zhí)行部分是必須的。]

as

|

is創(chuàng)建過程語法如下：create

or

replace

procedure

過程名[

參數(shù)名1 in

|out

|

in

out

類型名，參數(shù)名2

過程體創(chuàng)建過程是DDL語句，因此，在語句之后有一條隱含的commit語句。1.參數(shù)的模式及約束例：調(diào)用addnewstudent過程塊--callproc.sqlDeclarev_new name

students.

_name%type:=‘margeret’;v_lastnmae

students.last_name%type:=‘mason’;v_major

students.major%type:=‘history’;Beginaddnewstudent(v_

name,v_lastname,v_major);End;在上面的塊中v_

name,v_lastname,v_major

是實參，

p_

name,p_lastname,p_major是形參，調(diào)用時，實參的值賦給形參。形參可以有三種模式－in、out、in out。沒有指定形參的模式時，形參缺省的模式是in。下例給出了每種模式之間的差異：Create

or

replace

procedure

modetest(p_inparameter innumber,p_outparameterout

number,p_inoutparameter

in

out

number)

isv_loactionvariable

number;Beginv_loactionvariable:=p_inparameter;--合法p_inparameter:=7;--

p_outparameter:=7--合法v_loactionvariable:=p_outparameter;----合法V_locationvariable:=p_inoutparameter;P_inoutparameter:=7;--合法End

modetest;**select into

和fetch

into也可以在into子句中進行賦值，這些變量也遵循同樣的規(guī)則的例子。例：下面的例子是對modetest的合法調(diào)用Declarev_number1

number:=2;v_number2

number:=3;Beginmodetest(12,v_number1,v_number2);End;如果用文字替換v_variable2，就會得到下面Declarev_number1

number;Beginmodetest(12,v_number1,11);End;模式說明in當(dāng)調(diào)用過程時，實參的值被傳遞給形參。在該過程內(nèi)

部，形參被認為是只讀的，不能被改變。過程結(jié)束時，實參的值不會被改變out在調(diào)用過程時，實參所擁有的任何值都會被忽略。在過程，形參被認為是只寫的，只能進行賦值，不能從中數(shù)據(jù)。當(dāng)過程結(jié)束時，形參的值被賦給實參。Inout這種模式是in

和out

的組合。在調(diào)用過程時，實參的值被賦給形參。在過程，形參可以被讀出，也可以被賦值。當(dāng)過程結(jié)束時，形參的值被賦給實參。**如果過程沒有參數(shù)，在過程說明和調(diào)用時都不需要括號了。此規(guī)則對函數(shù)同樣適用。2.

過程主體過程主體是一個擁有

、執(zhí)行和異常部分的PL/SQL塊。部分在is或as和begin之間，執(zhí)行部分在begin和exception之間，異常部分在exception和end之間。過程的結(jié)構(gòu)是這樣的：Create

or

replace

procedure

過程名as/*

部分*/Begin/*執(zhí)行部分*/Exception/*異常部分*/End

過程名；對形參的約束在調(diào)用一個過程中，不僅實參的值被傳遞給形參，而且對變量的限制也傳遞進去。因此在過程

中限制char，varchar2參數(shù)的長度，number參數(shù)的精度和刻度都是

的。如：下面的過程

是

的：Ceate

or

replace

procedure

parameterlenth(p_parameter1

in

out

varchar2(10),p_parameter2

in

out

number(3,2)

)asBeginp_parameter1:=‘a(chǎn)1bcdefghijklmn’;p_parameter2:=12.3;End

parameterlength;這個過程的正確

應(yīng)該是Create

or

replace

procedure

parameterlength(p_parameter1

in

out

varchar2,p_parameter2

in

out

number)

asBeginp_parameter1:=‘bcdefghijklmnop’;p_parameter2:=12.3;End

parameterlength;對p_parameter1和p_parameter2的限制是傳遞給的實參的限制。例：以下代碼調(diào)用過程parameterlength過程Declarev_variable1

varchar2(40);v_variable2

number(3,4);Beginparameterlength(v_variable1,v_variable2);End

;Declarev_variable1

varchar2(10);v_variable2

number(3,4);Beginparameterlength(v

_variable1,v_variable2);End

;上面的塊和前面的塊的唯一不同是v_variable1,parameterlength將一個長度為15的字符串賦值給p_parameter1會產(chǎn)生錯誤。**為避免發(fā)生以上的錯誤，請在創(chuàng)建過程的時候?qū)嶋H參數(shù)的限制要求給予詳細的文檔說明。文檔注釋應(yīng)該包括該過程要用什么樣的處理和參數(shù)。％type和過程參數(shù)：取得對形參唯一限制的方法是用％

type。如果使用了％type對形參進行說明，而基準(zhǔn)類型是受限的，則該限制將作用于形參而不是實際參數(shù)。如：Create

or

replace

procedure

parameterlength(p_parameter1

in

out

varachr2,p_parameter2

in

out

studets.current_credits%type)

asBeginp_parameter2:=12345;End

parameterlength;那么p_parameter2將被限制為精度為3。這是因為current_credits列的精度。甚至

采用足夠精度的實參調(diào)用parameterlength時，處理中所采用的也是形參的精度。例：Declarev_variable1

varchar2(1);v_variable2

number;Beginparameterlength(v_variable1,v_variable2);--產(chǎn)生ora-6502錯誤。End;位置標(biāo)識法和帶名標(biāo)識法到目前為止，實參都是通過位置和形參相對應(yīng)的。假設(shè)有下面的過程

：Create

or

replace

procedure

callme(p_parametera

varchr2,p_parameterb

number,p_parameterc

boolean,p_parameterd

date)

asBeginnull;End

callme;以及象下面這樣的調(diào)用塊：Declarev_variable1

varchar2(10);v_

variable2

number(7,6);v_variable3

boolean;v_variable4

date;Begincallme(v_variable1,v_variable2,v_variable3,v_variable4);End;實參v_variable1與形參p_parametera對應(yīng)，v_variable2與p_parameterb對應(yīng)，v_variable3與p_parameterc對應(yīng)，v_variable4與p_parameterd對應(yīng)。這就是所謂的位置標(biāo)識法。也可以使用帶名標(biāo)識法來調(diào)用過程Declarev_variable1

varchar2(10);v_variable2

number(7,6);v_variable3

boolean;v_variable4

date;Begincallme(p_parametera=>v_variable1,p_parameterb=>v_variable2,p_parameterc=>v_variable3,p_parameterd=>v_variable4);End;在帶名標(biāo)識法中形參和實參都被每個參數(shù)所包含，這樣我們可以重新調(diào)整參數(shù)的順序。如果需要的話，下面的塊也可以使用相同的參數(shù)調(diào)用callme。Declarev_variable1

varchar2(10);v_variable2

number(7,6);v_variable3

boolean;v_variable4

date;Begincallme(p_parameterb=>v_variable2,p_parameterc=>v_variable3,p_parameterd=>v_variable4,p_parametera=>v_variable1);End;如果需要的話，在同一個調(diào)用中位置標(biāo)識法和帶名標(biāo)識法可以混合使用。下面的塊說明了這種方法。Declarev_variable1

varchar2(10);v_variable2

number(7,6);v_variable3

boolean;v_variable4

date;Begincallme(v_variable1,v_variable2,p_parameterc=>v_variable3,p_parameterd=>v_variable4);End;我通常使用帶名標(biāo)識法，因為更愿意編寫簡潔的代碼。但是如果過程使用太多的參數(shù)（>10),則使用帶名標(biāo)識法更好。**使用單個記錄取代太多的參數(shù)參數(shù)缺省值如果一個參數(shù)擁有缺省值，則他不必從調(diào)用環(huán)境中傳入數(shù)值。如果他被傳遞進來數(shù)值的話，那么他所使用的是實際的值，而不是缺省的值。參數(shù)的缺省值通過以下語法說明：Parameter_name

〔mode〕parameter_type[：=|

default]initial_value例：Create

or

replace

procedure

addnewstudent(p_ name

students.

_name%type;p_lastname

students.last_name%type;p_major

students.major%type

default

‘economics’)

asBegininsert

into

students

values

(student_sequence.nextval,p_

name,p_lastname,p_major,0);End

addnewstudent;如果在調(diào)用過程中p_major沒有找到實參，就用缺省值economics代替可以通過位置標(biāo)識法做到這點，如：beginaddnewstudent(‘babara’,’blues’);End;或者使用帶名標(biāo)識法：Beginaddnewstudent(p_

name=>’babara’,p_lastname=>’blues’);End;如果使用的是位置標(biāo)識法，沒有相關(guān)聯(lián)實際參數(shù)，而使用缺省值的所有參數(shù)都必須位于參數(shù)表的末尾。例：Create

or

replace

procedure

defaulttest(p_parametera

number

default

10,p_parameterb

varchar2

default

‘a(chǎn)bcdef’,p_parameterc

date

default

sysdate)

asBeginDbms_output.put_line(‘a(chǎn):’||p_parametera||’b:’);End

defaulttest;如果

想要僅p_parameterb使用缺省值，為p_parametera和p_parameterb指定值，則應(yīng)使用帶名標(biāo)識法：Begindefaulttest(p_parametera=>7,p_parameterc=>’30-dec-95’);End;在使用位置標(biāo)識法的時候，如果

想要p_parameterb使用缺省值，則p_parameterc也要使用缺省值。例：Begindefaulttest(7);End;7.1.2

調(diào)用過程Oracle允許在sql*plus、oracleforms和其它支持oracle的編程環(huán)境中調(diào)用過程。但是連接oracle的用戶必須有對過程的執(zhí)行權(quán)限。Grant

execute

on

procedurename

to

username;過程的執(zhí)行權(quán)限。用于為用戶賦予7.1.3

重新編譯過程7.2

使用函數(shù)創(chuàng)建函數(shù)函數(shù)十分類似于過程，都可以在塊內(nèi)被或在數(shù)據(jù)庫的。但是過程調(diào)用本身是一個語句，函數(shù)調(diào)用是表達式的一部分，不是語句。例：指定的班級有90%以上是滿的返回true，否則返回false。Create

or

replace

function

almostfull(p_department

classes.department%type,p_course

classes.cousr%type)Return

boolean

isV_currentstudents

number;v_maxstudents

number;v_returnvalue

boolean;v_fullpercent

constant

number:=90;Beginselect

current_students,max_studentsinto

v_currentstudents,v_maxstudentsfrom

classeswhere

department=p_departmentand

course=p_course;If

(v_currentstudents/v_maxstudents*100)>v_fullpercent

thenv_returnvalue:=true;elsev_returnvalue:=false;end

if;return

v_returnvalue;End

allmostfull;可以用下面的PL/SQL塊調(diào)用他Declarecursor

c_classes

isselect

department,course

from

classes;Beginfor

v_classesrecord

in

c_classes

loopifallmostfull(v_classesrecord.department,v_classesrecord.course)

theninsert

into

temp_table (char_col)

values(v_classesrecord.department

||

‘

‘||v_classesrecord.course

||

‘is

almost

full’);end

if;end

loop;End;函數(shù)語法Create

or

replace

function

function_name[

(

argument

[in

|

out

|in

out

]

type,…)]return

return_type [is

|

as

]Fuction_body與過程類似，參數(shù)是可選的。因此可以不必在函數(shù)和函數(shù)調(diào)用時使用括號，但是，函數(shù)的返回類型是必須的。因為函數(shù)調(diào)用是表達式的一部分。Return語句Return語句用來控制將一個數(shù)值返回調(diào)用環(huán)境。其語法如下：Return

expession;Expession為要返回的值，如果沒有相應(yīng)的類型，那么

expression的取值將被轉(zhuǎn)換為在函數(shù)定義的return子句指定的類型。在一個函數(shù)中可以有多個return語句，盡管只有一個return語句被執(zhí)行。函數(shù)沒有使用return語句結(jié)束是錯誤的。例：Create

or

replace

function

classinfo(p_department

classes.department%type,p_course

classes.course%type)Return

varchar2

isv_currentstudent

number;v_maxstudents

number;v_percentfull

number;Beginselect

current_students,max_studentsinto

v_currentstudents,v_maxstudentsfrom

classeswhere

department=p_departmentand

course=p_course;V_percentfull:=v_currentstudents/v_maxstudents*100;If

v_percentfull=100thenreturn

‘full’;Elsif

v_percentfull>80

thenreturn

‘some

room’;Elsif

v_percentfull>60

thenreturn

‘more

room’;Elsif

v_percentfull>0

thenreturn

‘lots

of

rooms’;Elsereturn

‘empty’;End

if;End

classinfo;Return語句也可以用在過程中，它不需要參數(shù)，這將使控制立即傳遞給調(diào)用環(huán)境。被為out或inout的形參值被傳遞給實參，程序從調(diào)用過程語句的下一個語句開始執(zhí)行。函數(shù)樣式函數(shù)與過程共

些相同的特性函數(shù)可以通過out參數(shù)返回多個數(shù)值函數(shù)代碼擁有

、執(zhí)行和錯誤處理部分函數(shù)可以接受缺省值函數(shù)可以通過位置標(biāo)識法或帶名標(biāo)識法進行調(diào)用如果有多于一個的返回值，那么使用過程，如果僅有一個返回值，那么可以使用函數(shù)。盡管函數(shù)可以使用out參數(shù)返回多個值，但是這種風(fēng)格不好。在子程序中進行異常處理如果在子程序中發(fā)生了錯誤就要觸發(fā)一個異常情態(tài)（exception).這種異常處理可能是用戶定義的或是預(yù)定義的。如果過程對于此錯誤沒有異常處理，那么控制

會立即從該過程傳遞給調(diào)用環(huán)境，這遵循異常處理傳

遞規(guī)則。但是，在這種情況下，out

和in

out的形參沒

有傳遞給實參。實參將擁有其在調(diào)用該過程以前的值。例：Create

or

replace

procedure

raiseerror(p_raise

in

boolean:=true;p_parametera

out

number)

asBeginp_parametera:=7;if

p_raise

thenraise

dup_val_on_index;Elsereturn;End

if;Endraiseerror;如果 從下面的塊中調(diào)用raiseerrorDeclarev_tempvarnumber:=1;Begindbms_output.put_line(‘initial

value:’||

v_tempvar);raiseerror(false,v_tempvar);Dbms_output.put_line(‘value

after

successful

call:’||

v_tempvar);V_tempvar:=2;Dbms_output.put_line(‘value

before

2nd

call:’||

v_tempvar);Raiseerror(true,v_tempvar);Exceptionwhen

others

thendbms_output.put_line(‘value

afterunsuccessfulecall||v_tempvar);End;在對raiseerror的第一次調(diào)用以前，v_tempvar取值為1，第一次調(diào)用成功，v_tempvar被賦值7。然后在第二次對raiseerror調(diào)用前v_tampvar被賦值為2，第二次調(diào)用沒有成功，v_tempvar

的值仍然為2。刪除過程和函數(shù)刪除過程和函數(shù)意味著過程和函數(shù)從數(shù)據(jù)字典中刪除。刪除過程的語法如下：Drop

procedure

過程名刪除函數(shù)的語法如下：Drop

function

函數(shù)名例：drop

procedure

addnewstudentsDrop是一個DDL語句，因此在執(zhí)行該語句的前后都會隱含地執(zhí)行commit命令。7.3

子程序位置部分子程序可以被

在數(shù)據(jù)字典中，也可以在塊的進行定義，此時該子程序被稱作本地子程序。7.3.1內(nèi)置子程序當(dāng)通過create

or

replace

命令創(chuàng)建的子程序被

在數(shù)據(jù)庫中，該子程序是以編譯的形式進行 的，這就是p_code。P_code對程序的所有 都進行求值，這樣，子程序就被轉(zhuǎn)換為被PL/SQL引擎很容易閱讀的形式。User_object視圖包括了所有對象的信息，其中也包括內(nèi)置子程序的信息。該信息包括對象創(chuàng)建的時間和最后修改的時間、對象的類型（表、序列、函數(shù)等）、以及對象的有效性。User_source視圖包含了對象的源代碼。

User_errors視圖包含了編譯器錯誤的信息。無效的子程序仍然

在數(shù)據(jù)庫中。但是，在消除錯誤以前，它是不能被執(zhí)行的。部分

的本地7.3.2本地子程序下面的例子說明了在PL/SQL塊的子程序Declarecursor

c_allstudent

isselect

_name,last_name

from

students;v_formattedname

varchar2(50);function

fornatname(p_ name

in

varchar2

,p_lastnamein

varchar2)Return

varchar2

isBeginreturn

p_ _name

||

‘

‘

||

p_lastname;End

formatname;Beginfor

v_studentrecord

in

c_allstudents

loopv_formattedname:=formatname(v_studentrecord.

_name,v_studentrecord.last_name);insert

into

temp_table(char_col)values

(v_formattedname);end

loop;commit;End;Formatname僅在所 的塊內(nèi)可見。他的作用域為從的位置開始到塊的結(jié)束。其他的塊不能調(diào)用formatname，因為他對于其他塊是不可見的。所有本地子程序都在

部分的尾部進行

。如果將formatname移到c_allstudent

的前面，將得到編譯錯誤。Declarefunction

fornatname(p_ name

in

varchar2

,p_lastname

in

varchar2)Return

varchar2

isBeginreturn

p_ _name

||

‘

‘

||

p_lastname;End

formatname;cursor

c_allstudent

isselect

_name,last_name

from

students;v_formattedname

varchar2(50);Beginfor

v_studentrecord

in

c_allstudents

loopv_formattedname:=formatname(v_studentrecord.

_name,v_studentrecord.last_name);insert

into

temp_table(char_col)values

(v_formattedname);end

loop;commit;End;1.向前因為本地子程序的名字是一個標(biāo)識符，所以應(yīng)該在被之前進行

，但是對于相互交叉

的子程序，會出現(xiàn)問題。如：Declarev_tempval

binary_integer

:=5;procedure

a(p_counter

inout

binary_integer)

isbeginif

p_counter>0

thenb(p_counter);p_counter:=p_cpunter-1;end

if;end

a;procedure

b(p_counter

in

out

binary_integer)

isbeginp_counter:=p_counter-1;a(p_counter);end

b;Beginb(v_tempval);End;這個例子是不可能通過編譯的。為了解決這個問題，可以使用向前

。這只是一個過程名和形參，這樣在程序過程了。中就可以使用相互的交叉例：Declarev_tempval

binary_integer

:=5;procedure

b(p_counter

in

out

binary_integer)；procedure

a(p_counter

in

out

binary_integer)isbeginif

p_counter>0

thenb(p_counter);p_counter:=p_counter-1;end

if;end

a;procedure

b(p_counter

in

out

binary_integer)isbeginp_counter:=p_counter-1;a(p_counter);end

b;Beginb(v_tempval);End;內(nèi)置子程序和本地子程序內(nèi)置子程序和本地子程序的行為不同，擁有不同的屬性。我通常喜歡用內(nèi)置子程序，他可以在數(shù)據(jù)庫中，被多個塊調(diào)用。子程序的大小和復(fù)雜性方面的收益也是應(yīng)該考慮因素。我唯一要在某個塊內(nèi)將其為本地子程序的過程和函數(shù)是那些比較短小的，并且是那些從程序的特定部分才能進行調(diào)用的子程序。（即只能在所在塊才能調(diào)用的子程序）。內(nèi)置子程序和本地子程序的差異內(nèi)置子程序本地子程序在數(shù)據(jù)庫中以被編譯的p_code形式 ，在調(diào)用該子程序時，不需要再編譯作為包含他的塊的一部分進行編譯，如果該塊被多次調(diào)用，則該子程序被多次編譯?？梢詮挠袑υ撟映绦驌碛?/p>

execute權(quán)限的用戶所提交的任何塊中進行調(diào)用。僅能從包含該子程序的塊中進行調(diào)用。通過將子程序和調(diào)用塊分離開來，使得調(diào)用塊更短小和易于理解。如果需要的話，調(diào)用塊和子程序可以分別進行。該子程序和調(diào)用塊是同一個，易引起誤解，如果要對調(diào)用塊進行修改，該子程序要重新進行編譯。被編譯的p_code可以通過使用DBMS_SHARED_POOL.KEEP包裝過程嵌入到共享池中。這可以提高性能。本地子程序自己不能嵌插到共享池中。2.子程序依賴性在編譯過程或函數(shù)的時候，他 的所有oracle對象都被記錄到數(shù)據(jù)字典中。該過程是依賴于這些對象。如果在其中一個對象上執(zhí)行了DDL操作，內(nèi)置子程序也可能成為無效的。例：almostfull函數(shù)對表classes進行查詢。allmostfull僅依賴于一個對象classes?，F(xiàn)在假設(shè)

創(chuàng)建了一個調(diào)用allmostfull并將結(jié)果

到temp_table中的過程。Create

or

replace

procedure

recordfullclasses

ascursor

c_classes

isselect

department,coursefrom

classes;Beginfor

v_classesrecord

in

c_classes

loopif

almostfull(v_classesrecord.department,v_classesrecord.course)

theninsert

intotemp_table(char_col)

valuesv_classesrecord.department ||

‘

‘

||v_classesrecord.course

||‘is

almost

full’);end

if;end

loop;End

recordfullclasses;Recordfullclasses直接依賴于almostfull和temp_table，因此recordfullclasses間接依賴于classes。如果在classes表上執(zhí)行DDL操作，則依賴于classes的所有對象都將是無效的。例

通過修改下面的列將使recordfullclasses和almostfull無效。Alter

table

classes add(student_rating

number(2));對于函數(shù)請使用：Alter

function

function_name

compile權(quán)限和內(nèi)置子程序Execute權(quán)限要使用內(nèi)置子程序和包，應(yīng)有execute的權(quán)限。Create

or

replace

procedure

recordfullclasses

ascursor

c_classesisselect

department,coursefrom

classes;Beginfor

v_classesrecord

in

c_classes

loopif

almostfull(v_classesrecord.department,v_classesrecord.course)

theninsert

into

temp_table(char_col)

valuesv_classesrecord.department ||

‘

‘

||v_classesrecord.course

||

‘is

almost

full’);end

if;end

loop;End

recordfullclasses;假設(shè)recordfullclasses所依賴的對象almostfull和表classes以及表temp_table都由用戶usera所擁有。

recordfullclasses也是由用戶usera所擁有。如果

用以下 令授予用戶b對recordfullclasses的execute權(quán)限：Grant

execute

on

recordfullclasses

to

userb;那么userb可以通過以下的塊來執(zhí)行recordfullclasses。Beginusera.

Recordfullclasses;End;現(xiàn)在假設(shè)useb也有temp_table表，useb調(diào)用usera.Recordfullclasses，那么哪一個表會被改變呢？答案是usea中的表。原因如下：子程序在其擁有者權(quán)限控制下執(zhí)行既然userb調(diào)用Recordfullclasses，而Recordfullclasses由usera所擁有，這樣標(biāo)識符temp_table應(yīng)該對屬于usera，而不是userb的表進行求值。內(nèi)置子程序和角色假設(shè)usea不擁有Recordfullclasses或temp_table，但是useb擁有他。然后修改了Recordfullclasses，將他顯式地索引到usea中的對象。Create

or

replace

procedure

Recordfullclasses

ascursor

c_classses

is

select

department,coursefrom

usea.classes;Beginfor

v_classrecord

in

c_classes

loopif

usera.almostfull(v_classrecord.department,v_classrecord.course)

theninsert

intotemp_table(char_col)

values(v_classrecord.department||

‘

‘||

v_classrecord.course

||‘is

almost

full’);End

if;End

loop;End

Recordfullclasses;為了能使Recordfullclasses正確編譯，usera必須將關(guān)于classes的select權(quán)限和關(guān)于almostfull的execute權(quán)限賦予userb。而且，這種

必須顯式地進行，不能通過角色進行。下面由usea所執(zhí)行的 將使得useb.Recordfullclasses編譯成功。Grant

select

on

classes

to

useb;Grant

execute

on

Recordfullclasses

to

useb;通過間接的

，比如：Create

role

usera_role;Grant

select

on

classes

to

usera_role;Grant

execute

on

Recordfullclasses

to

usera_role;Grant

usera_role

to

userb;將不能工作。所以：子程序在其擁有者的控制下執(zhí)行，這些權(quán)限要顯式地授予相應(yīng)的擁有者，

無法通過角色進行。這條法則也適用于包和觸發(fā)器。7.4

遞歸在使用遞歸的時候要

遞歸的位置。如果將遞歸調(diào)用放在一個游標(biāo)的FOR循環(huán)中或者是游標(biāo)的open和close之間，則在每次調(diào)用的時候都會打開一個游標(biāo)，這樣的結(jié)果可能會造成游標(biāo)數(shù)超過oracle初始化參數(shù)open_cursor指定的最大值。例：Create

or

replace

function

rec_test(n

integer)return

integer

isBeginif

n=1

thenreturn

1;elsereturn

n*rec_test(n-1);end

if;End

rec_test;調(diào)用函數(shù)rec_testDeclareI

integer;BeginI:=rec_test(7);Dbms_output.put_line(I);End;TRIGGERSDatabase

triggers

can

be

used

to

perform

any

of

the

following:Audit

data

modificationLog

events

transparentlyEnforce

complex

business

rulesDerive

column

values

automaticallyImplement

complex

security

authorizationsMaintain

replicate

tablesTRIGGERSYou

can

associate

up

to

12

database

triggers

with

a

given

table.

A

database

trigger

has

three

parts:

atriggering

event,an

optional

triggerconstraint,and

a

triggeraction.When

an

event

occurs,

a

database

trigger

is

fired,and

an

predefinedPL/SQL

block

will

perform

thenecessary

action.TRIGGERSSYNTAX:CREATE

[OR

REPLACE]

TRIGGER

trigger_name{BEFORE|AFTER}

triggering_event

ON

table_name[FOR

EACH

ROW][WHEN

condition]DECLAREDeclaration

statementsBEGINExecutable

statementsEXCEPTIONException-handling

statementsEND;Bordoloi

andBockTRIGGERSThe

trigger_name

references

the

name

of

the

trigger.BEFORE

or

AFTER

specify

when

the

trigger

is

fired

(before

orafter

the

triggering

event).The

triggering_event

references

a

DML

statement

issued

againstthe

table

(e.g.,

INSERT,

DELETE,

UPDATE).The

table_name

is

the

name

of

the

table

associated

with

the

trigger.The

clause,

FOR

EACH

ROW,

specifies

a

trigger

is

a

rowtriggerand

firesonce

for

ea odified

row.A

WHEN

clause

specifies

the

condition

for

a

trigger

to

be

fired.Bear

inmind

that

if

you

drop

a

table,

all

the

associated

triggers

forthe

table

are

dropped

as

well.TYPES

OF

TRIGGERSTriggers

may

be

called

BEFORE

or

AFTER

the

followingevents:INSERT,

UPDATE

and

DELETE.The

before/after

options

can

be

used

to

specify

when

thetrigger

body

should

be

fired

with

respect

to

the

triggeringstatement.

If

the

user

indicates

a

BEFORE

option,

thenOracle

fires

the

trigger

before

executing

the

triggeringstatement.

On

the

other

hand,

if

an

AFTER

is

used,Oracle

fires

the

trigger

after

executing

the

triggeringstatement.TYPES

OF

TRIGGERSA

trigger

may

be

a

ROW

or

STATEMENT

type.

If

thestatement

FOR

EACH

ROW

is

present

in

the

CREATETRIGGER

clause

of

a

trigger,

the

trigger

is

a

row

trigger.A

row

trigger

is

fired

for

each

row

affected

byantriggering

statement.

A

statement

trigger,

however,

is

fired

only

once

for

thetriggering

statement,

regardless

of

the

number

of

rowsaffected

by

the

triggering

statementTYPES

OF

TRIGGERSExample:

statement

triggerCREATE

OR

REPLACE

TRIGGER

mytrig1

BEFORE

DELETE

OR

INSERTOR

UPDATE

ON

employeeBEGINIF

(TO_CHAR(SYSDATE,

'day')

IN

('sat',

'sun'))

OR(TO_CHAR(SYSDATE,'hh:mi')

NOT

BETWEEN

'08:30'

AND

'18:30')RAISE_APPLICATION_ERROR(-20500,

'table

is

secured');THENEND

IF;END;/The

above

example

shows

a

trigger

that

limits

the

DMLactions

to

the

employee

table

to

weekdays

from

8.30am

to6.30pm.

If

a

user

tries

toinsert/update/delete

a

row

in

theEBoMrdPoLloOi

aYnEd

E

table,

a

warning

message

will

be

prompted.BockBordoloi

a