函數(shù)與存儲過程

函數(shù)與存儲過程第一頁，共二十五頁，2022年，8月28日8.1函數(shù)函數(shù)是Oracle數(shù)據(jù)庫中常用對象之一，與其他編程語言的函數(shù)一樣，Oracle中的函數(shù)也必須返回一個值。這也是函數(shù)區(qū)別于存儲過程的重要特征。第二頁，共二十五頁，2022年，8月28日8.1.1函數(shù)簡介1．函數(shù)與功能的劃分2．函數(shù)的參數(shù)3．函數(shù)的返回值第三頁，共二十五頁，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;第四頁，共二十五頁，2022年，8月28日8.1.3函數(shù)中的括號其他標準編程語言中，函數(shù)的括號都是必需的，但是函數(shù)get_hello_msg并沒有使用小括號。當函數(shù)需要傳入?yún)?shù)時，參數(shù)列表必須使用小括號括起來，但是當函數(shù)沒有參數(shù)時，小括號可以省略。當函數(shù)沒有小括號時，在形式上和變量相同，那么有可能會產(chǎn)生變量沖突。第五頁，共二十五頁，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第六頁，共二十五頁，2022年，8月28日8.1.5函數(shù)的確定性每次調(diào)用函數(shù)，Oracle總是根據(jù)傳入的參數(shù)，執(zhí)行相同的步驟，并返回最終值。函數(shù)的確定性是指，傳入的參數(shù)一定，無論函數(shù)被調(diào)用多少次，都會返回相同的值。例如，對于get_tax函數(shù)，每次輸入相同的工資額，那么，返回值都不會改變。createorreplacefunctionget_tax(p_salarynumber)returnnumberdeterministicas

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

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

3．執(zhí)行存儲過程executeupdate_students;第十一頁，共二十五頁，2022年，8月28日8.2.3存儲過程的參數(shù)——IN參數(shù)IN參數(shù)，顧名思義，是指傳入?yún)?shù)，即只進不出的參數(shù)。它由調(diào)用者傳遞給存儲過程之后，存儲過程在執(zhí)行過程中，無論怎樣使用該參數(shù)，都無法改變該參數(shù)的值。該參數(shù)對于存儲過程來說，是只讀的。例如，在更新學生信息的存儲過程update_stduents中，可以傳入一個年齡參數(shù)，用以標識需要將學生年齡修改為多少歲。createorreplaceprocedureupdate_students(in_ageinnumber)asbeginupdatestudentssetstudent_age=in_age;---in_age:=in_age+10;commit;endupdate_students;executeupdate_students(12);第十二頁，共二十五頁，2022年，8月28日8.2.4存儲過程的參數(shù)——OUT參數(shù)函數(shù)可以有返回值，存儲過程并沒有顯式的返回值。但是可以通過OUT參數(shù)獲得存儲過程的處理結(jié)果。在范例8-11中，更新了表students中的學生年齡，可以通過OUT參數(shù)返回更新之后的值，以便驗證更新是否成功。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;第十三頁，共二十五頁，2022年，8月28日8.2.5存儲過程的參數(shù)——INOUT參數(shù)INOUT參數(shù)既可以作為輸入?yún)?shù)，也可以作為輸出參數(shù)。因此，INOUT參數(shù)一般用于對參數(shù)的值進行處理，并處理結(jié)果輸出。一個典型實例就是交換兩個變量的值。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;第十四頁，共二十五頁，2022年，8月28日8.2.6存儲過程的參數(shù)——參數(shù)順序像其他編程語言一樣，存儲過程的參數(shù)順序同樣重要。在以上范例中，所有參數(shù)在調(diào)用時的值都是按照順序分配給存儲過程。那么順序就顯得格外重要，如果順序顛倒，不僅得不到正確結(jié)果，而且有可能返回不可預知的錯誤。createorreplaceprocedureupdate_students(in_ageinnumber,in_nameinvarchar2)asbeginupdatestudentssetstudent_age=in_agewherestudent_name=in_name;commit;endupdate_students;名稱表示法：beginupdate_students(in_name=>'柳青',in_age=>19);end;第十五頁，共二十五頁，2022年，8月28日8.2.7存儲過程的參數(shù)——參數(shù)的默認值有時，存儲過程的參數(shù)有很多個。對于用戶來說，部分參數(shù)并非必需，那么，在定義存儲過程時應該為可選參數(shù)設定默認值，以允許用戶不為該參數(shù)傳值。需要注意的是，默認值是僅對IN參數(shù)而言，OUT和INOUT參數(shù)沒有默認值。范例8-17演示了如何使用IN參數(shù)的默認值。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;第十六頁，共二十五頁，2022年，8月28日8.2.8存儲過程的參數(shù)——參數(shù)順序總結(jié)在講解了三種參數(shù)的用法之后，可以對參數(shù)的順序總結(jié)如下：具有默認值的參數(shù)應該置于參數(shù)列表的末尾，因為有時用戶需要省略該參數(shù)；沒有默認值的參數(shù)可以遵循“IN參數(shù)”——>“OUT參數(shù)”——>“INOUT參數(shù)”。第十七頁，共二十五頁，2022年，8月28日8.3程序包程序包可以將若干個函數(shù)或者存儲過程組織起來，作為一個對象進行存儲。程序包通常由兩部分構(gòu)成：規(guī)范（specification）和主體（body）。程序包也可以包含常量和變量，包中的所有函數(shù)和存儲過程都能夠使用這些變量和常量。第十八頁，共二十五頁，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'第十九頁，共二十五頁，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(