函數(shù)與存儲(chǔ)過程

函數(shù)與存儲(chǔ)過程第一頁(yè)，共二十五頁(yè)，2022年，8月28日8.1函數(shù)函數(shù)是Oracle數(shù)據(jù)庫(kù)中常用對(duì)象之一，與其他編程語(yǔ)言的函數(shù)一樣，Oracle中的函數(shù)也必須返回一個(gè)值。這也是函數(shù)區(qū)別于存儲(chǔ)過程的重要特征。第二頁(yè)，共二十五頁(yè)，2022年，8月28日8.1.1函數(shù)簡(jiǎn)介1．函數(shù)與功能的劃分2．函數(shù)的參數(shù)3．函數(shù)的返回值第三頁(yè)，共二十五頁(yè)，2022年，8月28日8.1.2創(chuàng)建函數(shù)1．創(chuàng)建函數(shù)createorreplacefunctionget_hello_msgreturnvarchar2asbeginreturn'helloworld';endget_hello_msg;2．在數(shù)據(jù)字典中查看函數(shù)的信息selectobject_name,object_type,statusfromuser_objectswherelower(object_name)='get_hello_msg'3．查看函數(shù)的返回值

setserverouton;declaremsgvarchar2(20);beginmsg:=get_hello_msg;dbms_output.put_line(msg);end;第四頁(yè)，共二十五頁(yè)，2022年，8月28日8.1.3函數(shù)中的括號(hào)其他標(biāo)準(zhǔn)編程語(yǔ)言中，函數(shù)的括號(hào)都是必需的，但是函數(shù)get_hello_msg并沒有使用小括號(hào)。當(dāng)函數(shù)需要傳入?yún)?shù)時(shí)，參數(shù)列表必須使用小括號(hào)括起來，但是當(dāng)函數(shù)沒有參數(shù)時(shí)，小括號(hào)可以省略。當(dāng)函數(shù)沒有小括號(hào)時(shí)，在形式上和變量相同，那么有可能會(huì)產(chǎn)生變量沖突。第五頁(yè)，共二十五頁(yè)，2022年，8月28日8.1.4函數(shù)的參數(shù)在節(jié)中創(chuàng)建的函數(shù)get_hello_msg是無參數(shù)函數(shù)，本節(jié)將演示帶參數(shù)函數(shù)的創(chuàng)建和使用。createorreplacefunctionget_tax(p_salarynumber)returnnumberasbegindeclaretax_salarynumber;begintax_salary:=p_salary-2000;

iftax_salary<=0thenreturn0;endif;

returntax_salary*5/100;endendget_tax第六頁(yè)，共二十五頁(yè)，2022年，8月28日8.1.5函數(shù)的確定性每次調(diào)用函數(shù)，Oracle總是根據(jù)傳入的參數(shù)，執(zhí)行相同的步驟，并返回最終值。函數(shù)的確定性是指，傳入的參數(shù)一定，無論函數(shù)被調(diào)用多少次，都會(huì)返回相同的值。例如，對(duì)于get_tax函數(shù)，每次輸入相同的工資額，那么，返回值都不會(huì)改變。createorreplacefunctionget_tax(p_salarynumber)returnnumberdeterministicas

begindeclaretax_salarynumber;endget_tax;對(duì)于具有確定性的函數(shù)，在定義時(shí)，可以使用deterministic選項(xiàng)，以告知Oracle創(chuàng)建確定性函數(shù)。第七頁(yè)，共二十五頁(yè)，2022年，8月28日8.1.6典型函數(shù)舉例行轉(zhuǎn)列問題是一個(gè)常見的問題，即將多行數(shù)據(jù)轉(zhuǎn)換為一列。例如，在學(xué)生表中，存儲(chǔ)了很多學(xué)生資料，現(xiàn)欲獲得所有學(xué)生的姓名列表，常見做法是將所有學(xué)生姓名串聯(lián)起來，即多行轉(zhuǎn)一列。【范例8-7】selectget_student_string()fromdual第八頁(yè)，共二十五頁(yè)，2022年，8月28日8.2存儲(chǔ)過程存儲(chǔ)過程（StoreProcedure）對(duì)應(yīng)于其他編程語(yǔ)言中的過程。存儲(chǔ)過程不必返回值，但是可以有參數(shù)。本節(jié)將詳細(xì)講述存儲(chǔ)過程的創(chuàng)建及使用。第九頁(yè)，共二十五頁(yè)，2022年，8月28日8.2.1存儲(chǔ)過程簡(jiǎn)介1．提高數(shù)據(jù)庫(kù)執(zhí)行效率2．提高安全性3．可復(fù)用第十頁(yè)，共二十五頁(yè)，2022年，8月28日8.2.2創(chuàng)建存儲(chǔ)過程1．創(chuàng)建存儲(chǔ)過程createorreplaceprocedureupdate_studentsasbeginupdatestudentssetSTUDENT_AGE=10commit;endupdate_students;

2．查看存儲(chǔ)過程在數(shù)據(jù)字典中的信息selectobject_name,object_type,statusfromuser_objectswherelower(object_name)='update_students'select*fromuser_sourcewherelower(name)='update_students'

3．執(zhí)行存儲(chǔ)過程executeupdate_students;第十一頁(yè)，共二十五頁(yè)，2022年，8月28日8.2.3存儲(chǔ)過程的參數(shù)——IN參數(shù)IN參數(shù)，顧名思義，是指?jìng)魅雲(yún)?shù)，即只進(jìn)不出的參數(shù)。它由調(diào)用者傳遞給存儲(chǔ)過程之后，存儲(chǔ)過程在執(zhí)行過程中，無論怎樣使用該參數(shù)，都無法改變?cè)搮?shù)的值。該參數(shù)對(duì)于存儲(chǔ)過程來說，是只讀的。例如，在更新學(xué)生信息的存儲(chǔ)過程update_stduents中，可以傳入一個(gè)年齡參數(shù)，用以標(biāo)識(shí)需要將學(xué)生年齡修改為多少歲。createorreplaceprocedureupdate_students(in_ageinnumber)asbeginupdatestudentssetstudent_age=in_age;---in_age:=in_age+10;commit;endupdate_students;executeupdate_students(12);第十二頁(yè)，共二十五頁(yè)，2022年，8月28日8.2.4存儲(chǔ)過程的參數(shù)——OUT參數(shù)函數(shù)可以有返回值，存儲(chǔ)過程并沒有顯式的返回值。但是可以通過OUT參數(shù)獲得存儲(chǔ)過程的處理結(jié)果。在范例8-11中，更新了表students中的學(xué)生年齡，可以通過OUT參數(shù)返回更新之后的值，以便驗(yàn)證更新是否成功。createorreplaceprocedureupdate_students(in_ageinnumber,out_ageoutnumber)asbeginupdatestudentssetstudent_age=in_age;selectstudent_ageintoout_agefromstudentswherestudent_id=1;commit;endupdate_students;declareupdated_agenumber;beginupdate_students(20,updated_age);dbms_output.put_line(updated_age);end;第十三頁(yè)，共二十五頁(yè)，2022年，8月28日8.2.5存儲(chǔ)過程的參數(shù)——INOUT參數(shù)INOUT參數(shù)既可以作為輸入?yún)?shù)，也可以作為輸出參數(shù)。因此，INOUT參數(shù)一般用于對(duì)參數(shù)的值進(jìn)行處理，并處理結(jié)果輸出。一個(gè)典型實(shí)例就是交換兩個(gè)變量的值。createorreplaceprocedureswap(in_out_param1inoutnumber,in_out_param2inoutnumber)asbegindeclareparamnumber;beginparam:=in_out_param1;in_out_param1:=in_out_param2;in_out_param2:=param;end;end;declareparam1number:=25;param2number:=35;beginswap(param1,param2);dbms_output.put_line('param1='||param1);dbms_output.put_line('param2='||param2);end;第十四頁(yè)，共二十五頁(yè)，2022年，8月28日8.2.6存儲(chǔ)過程的參數(shù)——參數(shù)順序像其他編程語(yǔ)言一樣，存儲(chǔ)過程的參數(shù)順序同樣重要。在以上范例中，所有參數(shù)在調(diào)用時(shí)的值都是按照順序分配給存儲(chǔ)過程。那么順序就顯得格外重要，如果順序顛倒，不僅得不到正確結(jié)果，而且有可能返回不可預(yù)知的錯(cuò)誤。createorreplaceprocedureupdate_students(in_ageinnumber,in_nameinvarchar2)asbeginupdatestudentssetstudent_age=in_agewherestudent_name=in_name;commit;endupdate_students;名稱表示法：beginupdate_students(in_name=>'柳青',in_age=>19);end;第十五頁(yè)，共二十五頁(yè)，2022年，8月28日8.2.7存儲(chǔ)過程的參數(shù)——參數(shù)的默認(rèn)值有時(shí)，存儲(chǔ)過程的參數(shù)有很多個(gè)。對(duì)于用戶來說，部分參數(shù)并非必需，那么，在定義存儲(chǔ)過程時(shí)應(yīng)該為可選參數(shù)設(shè)定默認(rèn)值，以允許用戶不為該參數(shù)傳值。需要注意的是，默認(rèn)值是僅對(duì)IN參數(shù)而言，OUT和INOUT參數(shù)沒有默認(rèn)值。范例8-17演示了如何使用IN參數(shù)的默認(rèn)值。createorreplaceprocedureinsert_student(in_student_idinnumber,in_student_nameinvarchar2,in_student_ageinnumberdefault20)asbegininsertintostudentsvalues(in_student_id,in_student_name,in_student_age);commit;endinsert_student;begininsert_student(11,'王蒙');end;第十六頁(yè)，共二十五頁(yè)，2022年，8月28日8.2.8存儲(chǔ)過程的參數(shù)——參數(shù)順序總結(jié)在講解了三種參數(shù)的用法之后，可以對(duì)參數(shù)的順序總結(jié)如下：具有默認(rèn)值的參數(shù)應(yīng)該置于參數(shù)列表的末尾，因?yàn)橛袝r(shí)用戶需要省略該參數(shù)；沒有默認(rèn)值的參數(shù)可以遵循“IN參數(shù)”——>“OUT參數(shù)”——>“INOUT參數(shù)”。第十七頁(yè)，共二十五頁(yè)，2022年，8月28日8.3程序包程序包可以將若干個(gè)函數(shù)或者存儲(chǔ)過程組織起來，作為一個(gè)對(duì)象進(jìn)行存儲(chǔ)。程序包通常由兩部分構(gòu)成：規(guī)范（specification）和主體（body）。程序包也可以包含常量和變量，包中的所有函數(shù)和存儲(chǔ)過程都能夠使用這些變量和常量。第十八頁(yè)，共二十五頁(yè)，2022年，8月28日8.3.1規(guī)范1．創(chuàng)建程序包規(guī)范createorreplacepackagepkg_studentsasstudentStringvarchar2(500);studentAgenumber:=18;functionget_student_stringreturnvarchar2;procedureupdate_student(in_student_idinnumber);procedureinsert_student(in_student_idinnumber,in_student_nameinvarchar2,in_student_ageinvarchar2);proceduredelete_student(in_student_idinnumber);endpkg_students;2．在數(shù)據(jù)字典中查看程序包規(guī)范的信息selectobject_name,object_type,statusfromuser_objectswherelower(OBJECT_NAME)='pkg_students'第十九頁(yè)，共二十五頁(yè)，2022年，8月28日8.3.2主體1．創(chuàng)建程序包主體createorreplacepackagebodypkg_studentsasendpkg_students;范例：createorreplacepackagebodypkg_studentsasfunctionget_student_stringreturnvarchar2isbeginreturn'students';endget_student_string;endpkg_students;2．在數(shù)據(jù)字典中查看該程序包主體的信息selectobject_name,object_type,statusfromuser_objectswherelower(