




版權說明:本文檔由用戶提供并上傳,收益歸屬內容提供方,若內容存在侵權,請進行舉報或認領
文檔簡介
第7章
過程、函數和程序包
7.1子程序
7.2程序包
7.3小結
習題七
上機實驗七
第7章過程、函數和程序包7.1子程序 7.1子程序
以前我們寫的PL/SQL語句程序都是瞬時的,都沒有命名。其缺點是:在每次執行的時候都要被編譯,并且不能被存儲在數據庫中,其他PL/SQL塊也無法調用它們。現在我們把命名的PL/SQL塊叫做子程序,它們存儲在數據庫中,可以為它們指定參數,可以在數據庫客戶端和應用程序中調用。命名的PL/SQL程序包括存儲過程和函數。程序包是存儲過程和函數的集合。
子程序結構與PL/SQL匿名塊的相同點在于都由聲明、執行、異常三大部分構成,不同之處在于,PL/SQL匿名塊的聲明可選,而子程序的聲明則是必需的。 7.1子程序
以前我們寫的PL/SQL子程序的優點如下:
(1)模塊化:通過子程序可以將程序分解為可管理的、明確的邏輯模塊。
(2)可重用性:子程序在創建并執行后,就可以在任何應用程序中使用。
(3)可維護性:子程序可以簡化維護操作。
(4)安全性:用戶可以設置權限,保護子程序中的數據,只能讓用戶提供的過程和函數訪問數據。這不僅可以讓數據更加安全,同時可保證正確性。
子程序有兩種類型:過程和函數。其中,過程用于執行某項操作;函數用于執行某項操作并返回值。
子程序的優點如下:
(1)模塊化:通過子程序可以將7.1.1過程
1.過程的創建和執行
過程可使用createprocedure語句創建,語法如下:
createorreplaceprocedure[<方案名>.]<存儲過程名>
[parameterlist]
{Is|as}
[local_declarations];
Begin
executablestatements
[exception]
[Exception_handlers]
End[procedure_name];
7.1.1過程
1.過程的創建和執行
過程可使用保留字Is前面的過程定義稱為過程頭。
過程頭包括過程名和具有數據類型的參數列表。過程體包括聲明部分、執行部分和異常處理部分。過程體從保留字Is之后開始。其中,聲明部分和異常處理部分是可選的;
執行部分至少包含一條語句。這里的Is|as就相當于declare聲明部分,除了擁有前面的一個過程聲明語句外,其他和以前的匿名PL/SQL塊一樣。其中,replace表示在創建存儲過程中,如果已經存在同名的存儲過程,則重新創建;如果沒有此關鍵詞,則當數據庫中有同名的過程時會報錯“ORA-00955號錯誤:名稱已被現有對象占用”。必須將同名的過程刪除后才能創建。
保留字Is前面的過程定義稱為過程頭。?過程頭包括過程名和1)創建不帶參數的過程。
【例7.1】
創建一個過程multiplication,用來實現九九乘法表。
SQL>createorreplaceproceduremultiplication
2as
3iinteger;
4jinteger;
5begin
6dbms_output.put_line('printmultiplication');
7foriin1..9loop
8forjin1..9loop
9ifi>=jthen1)創建不帶參數的過程。
【例7.1】創建一個過
10dbms_output.put(to_char(j)||'*'||
11to_char(i)||'='||to_char(i*j)||'');
12endif;
13endloop;
14dbms_output.put_line('');
15endloop;
16end;
17/
過程已創建。
出現編譯錯誤的時候可以用showerror或者descuser_errors來調試。
10dbms_output.put(2)執行過程
創建過程的時候并不會執行過程,必須在這之后調用過程來執行。執行過程的方法有兩種:一種是在SQL提示符下,使用execute語句來執行過程;另一種是在匿名塊中調用。
execute執行過程的語法如下:
executeprocedure_name(parameters_list);2)執行過程
創建過程的時候并不會執行過程,必須在【例7.2】
執行multiplication過程。
SQL>setserverouton--將SQL*Plus的輸出打開
SQL>executemultiplication--執行過程multiplication用execute命令。
printmultiplication
1*1=1
1*2=22*2=4
1*3=32*3=63*3=9
1*4=42*4=83*4=124*4=16
1*5=52*5=103*5=154*5=205*5=25
1*6=62*6=123*6=184*6=245*6=306*6=36
1*7=72*7=143*7=214*7=285*7=356*7=427*7=49
1*8=82*8=163*8=244*8=325*8=406*8=487*8=568*8=64
1*9=92*9=183*9=274*9=365*9=456*9=547*9=638*9=729*9=81
PL/SQL過程已成功完成。
【例7.2】執行multiplication過程。
【例7.3】
在匿名塊中調用過程multiplication。
SQL>begin
2multiplication;
3end;
4/
程序運行結果同上。
【例7.3】在匿名塊中調用過程multiplicati
2.創建帶參數的過程
調用程序通過參數可向被調用子程序傳遞值。在上述語法[parameterlist]中,參數的具體形式如下:
<參數1,[方式1]<數據類型1>,
<參數2,[方式2]<數據類型2>,
…
參數方式有以下三種:
(1)IN表示接受值為默認值。
(2)OUT表示將值返回給子程序的調用程序。
(3)?INOUT表示接受值并返回已更新的值。
2.創建帶參數的過程
調用程序通過參數可向被調用子程參數的書寫格式為:[(參數1IN?|?OUT?|?INOUT參數類型,參數2
IN?|?OUT?|?INOUT參數類型,…)]。參數IN模式是默認模式。如果未指定參數的模式,則認為該參數是IN參數。對于OUT和INOUT參數,必須明確指定,并且這兩種類型的參數在返回到調用環境之前必須先賦值。IN參數可以在調用時賦默認值,而OUT參數和INOUT參數不可以。
參數的書寫格式為:[(參數1IN?|?OUT?|?IN1)創建帶IN模式參數的過程
【例7.4】
創建一個過程,以雇員號為參數查詢雇員的姓名和職位。
SQL>createorreplaceprocedurequeryEmpName(sFindNoemp.EmpNo%type)
2as
3sNameemp.ename%type;
4sJobemp.job%type;
5begin
6selectename,jobintosName,sJobfromemp
7whereempno=sFindNo;
8dbms_output.put_line('IDis'||sFindNo||'dezhigongnameis'||1)創建帶IN模式參數的過程
【例7.4】創建
9sName||'gongzuois'||sJob);
10exception
11whenno_data_foundthen
12dbms_output.put_line('nodata');
13whentoo_many_rowsthen
14dbms_output.put_line('toomanydata');
15whenothersthen
16dbms_output.put_line('error');
17end;
18/
過程已創建。
9sName||'g【例7.5】
執行queryEmpName過程。
SQL>execqueryEmpName('7900');
IDis7900dezhigongnameisJAMESgongzuoisCLERK
同樣,也可通過匿名塊調用過程queryEmpName。
SQL>Begin
2queryEmpName('7900');
3end;
4/【例7.5】執行queryEmpName過程。
S2)創建帶OUT模式參數的過程
【例7.6】
創建一個過程,以雇員號查詢雇員的薪水。
SQL>createorreplaceprocedurequeryEmpSal(sFindNoemp.EmpNo%type,v_salout
emp.sal%type)
2as
3begin
4selectsalintov_salfromemp
5whereempno=sFindNo;
6dbms_output.put_line('Thesalaryof'||sFindNo||'is:'||v_sal);2)創建帶OUT模式參數的過程
【例7.6】創建
7exception
8whenno_data_foundthen
9dbms_output.put_line('nodata');
10whentoo_many_rowsthen
11dbms_output.put_line('toomanydata');
12whenothersthen
13dbms_output.put_line('error');
14end;
/
過程已創建。
此過程帶有一個輸入參數sFindNo和輸出參數v_sal,程序根據輸入參數到表中查詢記錄,以返回該員工的薪水值。
7exception
8【例7.7】
執行queryEmpSal過程。
可以聲明一個變量,用如下的方式調用該過程。
SQL>varsalarynumber;
SQL>execqueryEmpSal('7900',:salary);
Thesalaryof7900is:950
PL/SQL過程已成功完成。
【例7.7】執行queryEmpSal過程。
可以另外,也可以從一個匿名的PL/SQL程序中執行上述過程,以顯示sal_out變量的輸出結果。以下代碼可以顯示queryEmpSal過程的返回值。
Declare
valuenumber;
Begin
queryEmpSal(7934,value);
DBMS_OUTPUT.PUT_LINE('VALUE的值為'||to_char(value));
End;
/
另外,也可以從一個匿名的PL/SQL程序中執行上述過程,3)創建帶INOUT模式參數的過程
【例7.8】
創建兩個數進行交換的過程。
SQL>createorreplaceprocedureswap(p1INOUTnumber,p2INOUTnumber)
2as
3tempnumber;
4begin
5temp:=p1;
6p1:=p2;
7p2:=temp;
8end;
9/
SQL>/
過程已創建。
3)創建帶INOUT模式參數的過程
【例7.8】【例7.9】
執行swap過程。
SQL>Declare
2N1number:=10;
3N2number:=20;
4Begin
5swap(N1,N2);
6DBMS_OUTPUT.PUT_LINE('N1的值是'||N1);
7DBMS_OUTPUT.PUT_LINE('N2的值是'||N2);
8End;
9/
N1的值是20
N2的值是10
PL/SQL過程已成功完成。
【例7.9】執行swap過程。
SQL>Decl
3.過程的授權
只有創建過程的用戶和管理員才有使用過程的權限,如以上示例,創建的過程就像創建的表一樣,屬于當前操作的用戶,其他用戶如果要調用過程,則需要得到該過程的EXECUTE權限,然后通過點標記(dotnotation)(即“用戶名.過程名”)來調用過程(數據字典是user_source)。以下演示如何授權:
SQL>GRANTEXECUTEONswapTOJohn;
SQL>GRANTEXECUTEONqueryEmpNameTOPUBLIC;
前者將swap過程的執行權限授予John用戶,后者將queryEmpName的執行權限授予所有數據庫用戶。
3.過程的授權
只有創建過程的用戶和管理員才有使用過
4.刪除過程
刪除存儲過程的命令的一般格式如下:
DROPPROCEDURE[<方案名>.]<存儲過程名>;
【例7.10】
刪除過程multiplication。
SQL>DROPPROCEDUREmultiplication
過程已丟棄。
4.刪除過程
刪除存儲過程的命令的一般格式如下:
7.1.2函數
函數與過程相似,也是數據庫中存儲的已命名PL/SQL程序塊。與過程不同的是,函數除了完成一定的功能外,還必須返回一個值。
1.創建函數
創建函數是指通過RETURN子句指定函數返回值的數據類型。在函數體的任何地方,用戶都可以通過RETURNexpression語句從函數返回。
7.1.2函數
函數與過程相似,也是數據庫中存儲定義函數的語法如下:
CREATE[ORREPLACE]FUNCTION[<方案名>.]<函數名>
[parameterslist]
RETURN<返回值類型>
IS|AS
[local_declarations];
Begin
executablestatements
[exception]
[Exception_handlers]
End;
其中,FUNCTION為PL/SQL函數的關鍵字。
定義函數的語法如下:
CREATE[ORREPL【例7.11】
創建一個函數,以雇員號查詢雇員的姓名。
SQL>createorreplacefunctiongetName(snovarchar2)
2returnvarchar
3is
4namevarchar(12);
5begin
6selectenameintonamefromemp
7whereempno=sno;
8returnname;
9exception
10whentoo_many_rowsthen
11dbms_output.put_line('toomanydata');
12whenothersthen
13dbms_output.put_line('error');
14end;
15/
函數已創建。
【例7.11】創建一個函數,以雇員號查詢雇員的姓名。
2.執行函數及授權
1)在匿名塊中調用
函數調用與過程調用很相似,在匿名過程中通過函數名(或參數)可以調用一個函數。因為過程沒有顯式的RETURN語句,所以過程調用可以是一條單獨的語句,寫在單獨的行中。而函數則必須有一個返回值,所以函數調用要借助于可執行語句來完成,例如賦值語句、選擇語句和輸出語句。下面的例子通過匿名過程調用getname函數,將雇員號作為參數,此函數將雇員姓名傳給調用塊,然后顯示雇員姓名。
2.執行函數及授權
1)在匿名塊中調用
函數調【例7.12】
通過匿名過程調用getname函數。
SQL>declare
2namevarchar(12);
3begin
4name:=getname('7902');
5dbms_output.put_line(name);
6end;
7/
FORD
PL/SQL過程已成功完成。
【例7.12】通過匿名過程調用getname函數。
2)在SQL語句中調用
除了在匿名塊中調用外,也可以在SQL語句中調用函數。
【例7.13】
在SQL語句中調用getname,顯示雇員號為“7369”的雇員姓名。
SQL>selectgetname('7369')fromdual;
GETNAME('7369')
------------------------
SMITH2)在SQL語句中調用
除了在匿名塊中調用外,也可【例7.14】
從雇員表中查找雇員號為“7369”的雇員信息。
SQL>Select*fromempwhereename=getname(‘7369’);
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
--------------------------------------------------
7369SMITHCLERK??790217-12月-8080020
【例7.14】從雇員表中查找雇員號為“7369”的雇員
3.函數的授權
與過程一樣,只有創建函數的用戶和管理員才有權使用函數,其他用戶如果要調用函數,則需要得到該函數的EXECUTE權限。
【例7.15】
將getname函數的執行權限授予John用戶。
SQL>GRANTEXECUTEONgetnameTOJohn;3.函數的授權
與過程一樣,只有創建函數的用戶和管理
4.刪除函數
刪除函數的命令的一般格式如下:
DROPFUNCTION[<方案名>.]<函數名>;
【例7.16】
刪除函數getname。
SQL>DROPFUNCTIONgetname;4.刪除函數
刪除函數的命令的一般格式如下:
D7.1.3過程和函數的比較
表7-1過程和函數的比較
7.1.3過程和函數的比較
表7-1過程和函 7.2程
序
包
7.2.1程序包概述
程序包是數據庫中的一個實體,包含一系列公共常量、變量、數據類型、游標、過程及函數的定義。使用包更加體現了模塊化編程的優點,使得開發工作能靈活自如地進行。程序包包括兩個部分:程序包規范說明部分和程序包主體部分。
7.2程序包
7.2.1程序包概述
1.程序包規范說明部分
該部分相當于一個包的頭,類似于接口,可以對包的部件作簡單說明。其中的過程、函數、變量、常量和游標都是公共的,可在應用程序中訪問。
(1)
使用createpackage進行創建。
(2)
包含公用對象和類型。
(3)
聲明類型、變量、常量、異常、游標和子程序。
(4)
?可以在沒有程序包主體部分的情況下存在。
1.程序包規范說明部分
該部分相當于一個包的頭,類似
2.程序包主體部分
(1)
使用createpackagebody進行創建。
(2)
包含子程序和游標的定義。
(3)
包含私有聲明。
(4)
?不能在沒有程序包規格說明的情況下存在。
2.程序包主體部分
(1)
使用createpac
7.2.2創建程序包
程序包包括兩部分:規范和主體。規范是程序包的公共接口;主體是規范的實現,以及私有例程、數據和變量。
1.創建程序包規范
CREATEORREPLACEPACKAGEpackage_name
IS|AS
公用類型或變量常量的聲明;
公用過程或函數的聲明;
ENDpackage_name;
/7.2.2創建程序包
程序包包括兩部分:規范和主
2.創建程序包主體
CREATEORREPLACEPACKAGEBODYpackage_name
IS|AS
私有類型或變量常量的聲明;
公用過程或函數的實現;
ENDpackage_name
規范是程序包的接口,規范中定義的所有內容都可以由調用者使用(當然需要具有EXECUTE特權),比如規范中定義的過程函數可以被執行,類型可以被訪問,變量可以被引用。
2.創建程序包主體
CREATEORREPLAC【例7.17】
使用兩個過程print_ename()和print_sal(),定義名為employee_pkg的程序包。
SQL>CREATEORREPLACE
2PACKAGEemployee_pkgas
3Procedureprint_ename(p_empnonumber);
4Procedureprint_sal(p_empnonumber);
5End;
6/
程序包已創建。
【例7.17】?使用兩個過程print_ename()此時并沒有為過程提供代碼,只是定義了名稱和參數。
這個時候如果試圖使用這個包,則會報錯。
SQL>execemployee_pkg.print_ename(1234);
ERROR位于第1行:
ORA-04068:已丟棄程序包的當前狀態
ORA-04067:未執行,packagebody"SCOTT.EMPLOYEE_PKG"不存在
ORA-06508:PL/SQL:無法在調用之前找到程序單元
ORA-06512:在line1此時并沒有為過程提供代碼,只是定義了名稱和參數。這個時程序包是過程函數的具體實現部分,實現規范中定義的接口。
SQL>CREATEORREPLACE
2PACKAGEBODYemployee_pkgas
3Procedureprint_ename(p_empnonumber)is
4L_enameemp.ename%type;
5Begin
6Selectenameintol_enamefromempwhereempno=p_empno;
7Dbms_output.put_line(l_ename);程序包是過程函數的具體實現部分,實現規范中定義的接口。
8Exception
9Whenno_data_foundthen
10Dbms_output.put_line('Invalidemployeenumber');
11Endprint_ename;
12Procedureprint_sal(p_empnonumber)is
13L_salemp.sal%type;
14Begin
15Selectsalintol_salfromempwhereempno=p_empno;
16Dbms_output.put_line(l_sal);8Exception
9
17Exception
18WhenNO_DATA_FOUNDthen
19Dbms_output.put_line('Invalidemployeenumber');
20Endprint_sal;
21Endemployee_pkg;
22/
程序包主體已創建。
17Exception
187.2.3執行程序包
執行程序包中的過程可以使用如下語句:
EXECUTE包名.過程名
【例7.18】
執行employee_pkg包里的print_ename過程。
SQL>setserveroutputon
SQL>execemployee_pkg.print_ename(7782);
運行結果如下:
CLARK
PL/SQL過程已成功完成。
SQL>execemployee_pkg.print_sal(7782);
運行結果如下:
2450
PL/SQL過程已成功完成。
執行程序包中的函數可以通過一段代碼塊來實現。
7.2.3執行程序包
執行程序包中的過程可以使用如下
7.2.4程序包中的游標
程序包中可以定義和使用游標。游標的定義分為游標規范和游標主體兩部分。在游標規范說明部分必須通過RETURN語句指定游標的返回類型。RETURN語句指定游標獲取和返回的數據元素由select語句確定。select語句只出現在主體定義中,而不出現在規范說明中。
7.2.4程序包中的游標
程序包中可以定義和使用【例7.19】
下面使用程序包中的游標。student_package程序包中包含4個過程和1個函數。其中,select_student過程中使用了游標。詳細過程如下:
(1)首先創建student表結構。
SQL>createtablestudent(stuidvarchar2(4),stunamevarchar2(10),sechar(1));
表已創建。
【例7.19】下面使用程序包中的游標。student_
(2)定義聲明。
SQL>createorreplacepackagestudent_packageis
2typestudent_curisrefcursorreturnstudent%rowtype;
3procedureinsert_student(stuinstudent%rowtype);
4procedureupdate_student(stustudent%rowtype);
5proceduredelete_student(snostudent.stuid%type);
6procedureselect_student(stucurinoutstudent_cur);
7functiongetStudentCountreturnnumber;
8endstudent_package;
9/
程序包已創建。
(2)定義聲明。
SQL>createorr
(3)定義主體。
SQL>createorreplacepackagebodystudent_packageis
2procedureinsert_student(stustudent%rowtype)is
3icountint;
4begin
5selectcount(*)intoicountfromstudentwherestuid=stu.stuid;
6ificount>0then
7dbms_output.put_line('insertdataisalreadyexsist');
8else
9insertintostudentvalues(stu.stuid,stu.stuname,stu.se);
10commit;
11endif;(3)定義主體。
SQL>createorr
12exception
13whentoo_many_rowsthen
14dbms_output.put_line('insertdataisalreadyexsist');
15endinsert_student;
16procedureupdate_student(stustudent%rowtype)is
17icountint;
18begin
19selectcount(*)intoicountfromstudentwherestuid=stu.stuid;
20ificount>0then
21updatestudentsetstuname=stu.stuname,se=stu.sewherestuid=stu.stuid;12exception
13whe
22commit;
23else
24dbms_output.put_line('updatedatanotexist!');
25endif;
26endupdate_student;
27proceduredelete_student(snostudent.stuid%type)is
28icountint;
29begin
30ificount>0then
31deletefromstudentwherestuid=sno;
32commit;
33else22commit;
23els
34dbms_output.put_line('deletedatanotexist');
35endif;
36enddelete_student;
37procedureselect_student(stucurinoutstudent_cur)is
38begin
39openstucurforselect*fromstudent;
40endselect_student;
41functiongetStudentCountreturnnumberis
42icountint;
43begin
44selectcount(*)intoicountfromstudent;
45returnicount;
46endgetStudentCount;
47endstudent_package;
48/
程序包主體已創建。
34dbms_output.put_line
(4)調用程序包插入一行數據。
SQL>declare
2stustudent%rowtype;
3begin
4stu.stuid:=1009;
5stu.stuname:='tonglei';
6stu.se:='f';
7student_package.insert_student(stu);
8end;
9/
PL/SQL過程已成功完成。
(4)調用程序包插入一行數據。
SQL>decl
(5)調用程序包中的過程select_student顯示數據。
SQL>varssrefcursor--定義游標變量
SQL>execstudent_package.select_student(:ss);
PL/SQL過程已成功完成。
顯示游標變量內容如下:
SQL>printss
STUISTUNAMES
-----------------
1009tongleif(5)調用程序包中的過程select_student顯7.2.5程序包的優點
程序包的優點如下:
(1)
模塊化。包可以使邏輯上相關聯的類型、項目和子程序等封裝進一個命名PL/SQL塊中。每個包劃分功能清晰,包的接口簡單、明了。
(2)可重用性。一旦命名并保存在數據庫中,任何應用都可以使用。
(3)簡單的應用程序設計。當設計應用程序時,只需知道包的接口部分的信息,可以只創建并編譯包的規范而不創建包體。同樣可以在程序中引用包,等整個應用程序完成后再來定義具體的包體。
7.2.5程序包的優點
程序包的優點如下:
(1
(4)
抽象和數據隱藏。可以指定公有信息和私有信息。只有公有信息才可以被外部應用程序訪問。
(5)
更好的執行效能。包里的子程序第一次被調用時,整個包被調到內存中,以后的調用就可以直接從內存中讀取。這樣可以減少不必要的重新編譯。
(4)
抽象和數據隱藏。可以指定公有信息和私有信息。只有
7.2.6有關子程序和程序包的信息
子程序和程序包是數據庫中存儲的對象,Oracle會在數據字典中存儲所有對象的信息。通過查詢數據字典可以獲得它們的信息。通過查詢USER_OBJECTS數據字典視圖可以獲取有關在會話中創建的字程序和程序包的信息。
7.2.6有關子程序和程序包的信息
子程序和程序【例7.20】
獲取程序包中子程序和程序包的信息。
SQL>COLUMNOBJECT_NAMEFORMATA18
SQL>SELECTOBJECT_NAME,OBJECT_TYPEFROMUSER_OBJECTSWHERE
OBJECT_TYPEIN('PROCEDURE','FUNCTION','PACKAGE','PACKAGEBODY');
OBJECT_NAMEOBJECT_TYPE
---------------------------
EMPLOYEE_PKGPACKAGE
EMPLOYEE_PKGPACKAGEBODY
QUERYEMPNAMEPROCEDURE
QUERYEMPSALPROCEDURE
SWAPPROCEDURE【例7.20】獲取程序包中子程序和程序包的信息。
【例7.21】
要獲取存儲子程序的文本,可以查詢USER_SOURCE。
SQL>COLUMNLINEFORMAT9999
SQL>COLUMNTEXTFORA50
SQL>SELECTLINE,TEXTFROMUSER_SOURCEWHERENAME=’SWAP’;
LINETEXT
-------------------------------------------
1procedureswap(p1INOUTnumber,p2INOUTnumber)
2as
3tempnumber;
4begin
5temp:=p1;
6p1:=p2;
7p2:=temp;
8end;【例7.21】要獲取存儲子程序的文本,可以查詢USER【例7.22】
獲得程序包中子程序規范employee_pkg信息。
SQL>DESCemployee_pkg;
PROCEDUREPRINT_ENAME【例7.22】獲得程序包中子程序規范employee_ 7.3小
結
過程、函數和子程序都可以用于執行對數據庫的操作,可以帶上用戶自定義的參數。它們封裝了數據類型定義、變量說明、游標、異常等,方便了用戶管理操縱數據庫數據。
7.3小結
過程、函數和子程序都可
習題七
一、選擇題
1.執行特定任務的子程序是()。
A.函數 B.過程C.程序包D.游標
2.子程序的()模式參數可以在調用子程序時指定一個常量。
A.IN B.OUT C.INOUT
3.如果存儲過程的參數類型為OUT,那么調用時傳遞的參數應該為()。
A.常量 B.表達式 C.變量 D.都可以
習題七
一、選擇題
1.執行特定任務的子程
4.下列有關存儲過程的特點,說法錯誤的是()。
A.存儲過程不能將值傳回調用的主程序
B.存儲過程是一個命名的模塊
C.編譯的存儲過程存放在數據庫中
D.一個存儲過程可以調用另一個存儲過程
5.包中不能包含的元素為()。
A.存儲過程 B.存儲函數 C.游標 D.表
4.下列有關存儲過程的特點,說法錯誤的是()。
6.下列有關包的使用,說法錯誤的是()。
A.在不同的包內模塊可以重名
B.包的私有過程不能被外部程序調用
C.包體中的過程和函數必須在包頭部分說明
D.必須先創建包頭,然后創建包體
6.下列有關包的使用,說法錯誤的是()。
A.二、編程題
1.編寫程序包,此程序包有兩個過程和一個函數,第一個過程根據職員編號打印職員姓名,第二個過程根據職員編號打印職員的部門編號。函數根據職員編號返回職員的薪水。
提示:使用scott用戶的emp表作為數據源。
2.編寫函數接受學生的學號,并計算該學生3門課程的總分。
3.編寫一個過程,將10號部門員工薪水上漲10%,20號部門員工薪水上漲20%,其他部門員工薪水保持不變。
二、編程題
1.編寫程序包,此程序包有兩個過程和一個 上機實驗七
實驗1過程
目的和要求:
1.掌握編寫過程的方法。
2.掌握調用過程的方法。
實驗內容:
編寫一個過程,要求根據用戶輸入的員工號(emp_no)查詢EMP表,返回員工的姓名和工作職位(empName和empJob)。并編寫一個匿名塊調用此過程(使用SCOTT用戶的EMP表)。
上機實驗七
實驗1過程
目的和要求:
SQL>createorreplaceprocedurepro_emp(emp_nonumber)
2as
3empNamevarchar2(20);
4empJobvarchar2(20);
5begin
6selectename,jobintoempName,empJobfromempwhereempno=emp_no;
7DBMS_OUTPUT.PUT_LINE('雇員的姓名是:'||empName);
8DBMS_OUTPUT.PUT_LINE('雇員的職位是:'||empJob);
9EXCEPTION
10whenNO_DATA_FOUNDthen
11DBMS_OUTPUT.PUT_LINE('雇員編號未找到!');
12endpro_emp;
13/SQL>createorreplaceproce過程已創建。
SQL>setserveroutputon;
SQL>executepro_emp(7369);
雇員的姓名是:SMITH
雇員的職位是:CLERK
PL/SQL過程已成功完成。
過程已創建。
SQL>setserveroutp實驗2函數
目的和要求:
1.掌握編寫函數的方法。
2.掌握調用函數的方法。
實驗2函數
目的和要求:
1.掌握編寫函數的
實驗內容:
1.編寫函數以接受學生的學號,并計算此學生3門課程的平均分。
SQL>createorreplacefunctionfun_score(student_nonumber)
2returnfloat
3as
4s1float(10);
5s2float(10);
6s3float(10);
7score_avgfloat(10);
8begin
9selectoracle,java,csharpintos1,s2,s3fromscorewherestuID=student_no;
實驗內容:
1.編寫函數以接受學生的學號,并計算此學
10score_avg:=(s1+s2+s3)/3.0;
11returnscore_avg;
12EXCEPTION
13whenNO_DATA_FOUNDthen
14DBMS_OUTPUT.PUT_LINE('學號未找到!');
15end;
16/
函數已創建。
SQL>setserveroutputon;
SQL>declare10score_avg:=(s1+s2+s3)
2score_avgfloat(10);
3begin
4score_avg:=fun_score(1);
5DBMS_OUTPUT.PUT_LINE('該學生的成績是:'||score_avg);
6end;
7/
該學生的成績是:62.67
PL/SQL過程已成功完成。
2score_avgfloat(10);
2.編寫一個函數,要求根據用戶輸入的部門號查詢DEPT表。如果存在這個部門號,則返回TRUE,否則返回FALSE。在調用程序中根據結果顯示正確的消息(使用SCOTT用戶的DEPT表)。
2.編寫一個函數,要求根據用戶輸入的部門號查詢DEPT表
實驗3程序包
目的和要求:
1.掌握程序包的編寫規范。
2.掌握執行程序包中過程和函數的方法。
實驗內容:
1.編寫一個程序包,此程序包有一個過程和一個函數。過程根據職員編號打印薪水。函數根據職員編號返回職員的就職日期。編寫調用程序執行(使用SCOTT用戶的emp表)。
實驗3程序包
目的和要求:
1.掌握程序包的
SQL>createorreplacepackagepack_emp
2is
3procedurepro_salary(emp_nonumber);
4functionfun_date(emp_nonumber)returndate;
5endpack_emp;
6/
程序包已創建。
SQL>createorreplacepackagebodypack_emp
2as
3procedurepro_salary(emp_nonumber)
4isSQL>createorreplacepack
5salarynumber(20);
6begin
7selectsalintosalaryfromempwhereempno=emp_no;
8DBMS_OUTPUT.PUT_LINE('該職員的薪水是:'||salary);
9EXCEPTION
10whenNO_DATA_FOUNDthen
11DBMS_OUTPUT.PUT_LINE('職員編號未找到!');
12endpro_salary;
13functionfun_date(emp_nonumber)
14returndate
15is
16h_datedate;5salarynumber(20)
17begin
18selecthiredateintoh_datefromempwhereempno=emp_no;
19returnh_date;
20EXCEPTION
21whenNO_DATA_FOUNDthen
22DBMS_OUTPUT.PUT_LINE('職員編號未找到!');
23endfun_date;
24endpack_emp;
25/17begin
18se程序包主體已創建。
SQL>setserveroutputon;
SQL>executepack__salary(7369);
該職員的薪水是:800
PL/SQL過程已成功完成。
SQL>setserveroutputon;
SQL>declare
2h_datedate;
3begin
4h_date:=pack_emp.fun_date(7369);程序包主體已創建。
SQL>setserver
5DBMS_OUTPUT.PUT_LINE('該職員的就職日期是:'||h_date);
6end;
7/
該職員的就職日期是:17-12月-80
PL/SQL過程已成功完成。
5DBMS_OUTPUT.PUT_L
2.編寫一個程序包,此程序包有一個過程和一個函數。過程利用傳入參數傳入員工的工作職位(emp表JOB字段),顯示該職位中的員工數量。函數利用傳入參數傳入員工的工作職位,返回該職位中的員工數量。編寫調用程序執行(使用SCOTT用戶的emp表)。
2.編寫一個程序包,此程序包有一個過程和一個函數。過程利第7章
過程、函數和程序包
7.1子程序
7.2程序包
7.3小結
習題七
上機實驗七
第7章過程、函數和程序包7.1子程序 7.1子程序
以前我們寫的PL/SQL語句程序都是瞬時的,都沒有命名。其缺點是:在每次執行的時候都要被編譯,并且不能被存儲在數據庫中,其他PL/SQL塊也無法調用它們。現在我們把命名的PL/SQL塊叫做子程序,它們存儲在數據庫中,可以為它們指定參數,可以在數據庫客戶端和應用程序中調用。命名的PL/SQL程序包括存儲過程和函數。程序包是存儲過程和函數的集合。
子程序結構與PL/SQL匿名塊的相同點在于都由聲明、執行、異常三大部分構成,不同之處在于,PL/SQL匿名塊的聲明可選,而子程序的聲明則是必需的。 7.1子程序
以前我們寫的PL/SQL子程序的優點如下:
(1)模塊化:通過子程序可以將程序分解為可管理的、明確的邏輯模塊。
(2)可重用性:子程序在創建并執行后,就可以在任何應用程序中使用。
(3)可維護性:子程序可以簡化維護操作。
(4)安全性:用戶可以設置權限,保護子程序中的數據,只能讓用戶提供的過程和函數訪問數據。這不僅可以讓數據更加安全,同時可保證正確性。
子程序有兩種類型:過程和函數。其中,過程用于執行某項操作;函數用于執行某項操作并返回值。
子程序的優點如下:
(1)模塊化:通過子程序可以將7.1.1過程
1.過程的創建和執行
過程可使用createprocedure語句創建,語法如下:
createorreplaceprocedure[<方案名>.]<存儲過程名>
[parameterlist]
{Is|as}
[local_declarations];
Begin
executablestatements
[exception]
[Exception_handlers]
End[procedure_name];
7.1.1過程
1.過程的創建和執行
過程可使用保留字Is前面的過程定義稱為過程頭。
過程頭包括過程名和具有數據類型的參數列表。過程體包括聲明部分、執行部分和異常處理部分。過程體從保留字Is之后開始。其中,聲明部分和異常處理部分是可選的;
執行部分至少包含一條語句。這里的Is|as就相當于declare聲明部分,除了擁有前面的一個過程聲明語句外,其他和以前的匿名PL/SQL塊一樣。其中,replace表示在創建存儲過程中,如果已經存在同名的存儲過程,則重新創建;如果沒有此關鍵詞,則當數據庫中有同名的過程時會報錯“ORA-00955號錯誤:名稱已被現有對象占用”。必須將同名的過程刪除后才能創建。
保留字Is前面的過程定義稱為過程頭。?過程頭包括過程名和1)創建不帶參數的過程。
【例7.1】
創建一個過程multiplication,用來實現九九乘法表。
SQL>createorreplaceproceduremultiplication
2as
3iinteger;
4jinteger;
5begin
6dbms_output.put_line('printmultiplication');
7foriin1..9loop
8forjin1..9loop
9ifi>=jthen1)創建不帶參數的過程。
【例7.1】創建一個過
10dbms_output.put(to_char(j)||'*'||
11to_char(i)||'='||to_char(i*j)||'');
12endif;
13endloop;
14dbms_output.put_line('');
15endloop;
16end;
17/
過程已創建。
出現編譯錯誤的時候可以用showerror或者descuser_errors來調試。
10dbms_output.put(2)執行過程
創建過程的時候并不會執行過程,必須在這之后調用過程來執行。執行過程的方法有兩種:一種是在SQL提示符下,使用execute語句來執行過程;另一種是在匿名塊中調用。
execute執行過程的語法如下:
executeprocedure_name(parameters_list);2)執行過程
創建過程的時候并不會執行過程,必須在【例7.2】
執行multiplication過程。
SQL>setserverouton--將SQL*Plus的輸出打開
SQL>executemultiplication--執行過程multiplication用execute命令。
printmultiplication
1*1=1
1*2=22*2=4
1*3=32*3=63*3=9
1*4=42*4=83*4=124*4=16
1*5=52*5=103*5=154*5=205*5=25
1*6=62*6=123*6=184*6=245*6=306*6=36
1*7=72*7=143*7=214*7=285*7=356*7=427*7=49
1*8=82*8=163*8=244*8=325*8=406*8=487*8=568*8=64
1*9=92*9=183*9=274*9=365*9=456*9=547*9=638*9=729*9=81
PL/SQL過程已成功完成。
【例7.2】執行multiplication過程。
【例7.3】
在匿名塊中調用過程multiplication。
SQL>begin
2multiplication;
3end;
4/
程序運行結果同上。
【例7.3】在匿名塊中調用過程multiplicati
2.創建帶參數的過程
調用程序通過參數可向被調用子程序傳遞值。在上述語法[parameterlist]中,參數的具體形式如下:
<參數1,[方式1]<數據類型1>,
<參數2,[方式2]<數據類型2>,
…
參數方式有以下三種:
(1)IN表示接受值為默認值。
(2)OUT表示將值返回給子程序的調用程序。
(3)?INOUT表示接受值并返回已更新的值。
溫馨提示
- 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯系上傳者。文件的所有權益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙,網頁內容里面會有圖紙預覽,若沒有圖紙預覽就沒有圖紙。
- 4. 未經權益所有人同意不得將文件中的內容挪作商業或盈利用途。
- 5. 人人文庫網僅提供信息存儲空間,僅對用戶上傳內容的表現方式做保護處理,對用戶上傳分享的文檔內容本身不做任何修改或編輯,并不能對任何下載內容負責。
- 6. 下載文件中如有侵權或不適當內容,請與我們聯系,我們立即糾正。
- 7. 本站不保證下載資源的準確性、安全性和完整性, 同時也不承擔用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。
最新文檔
- 2025屆上海市上海交大附中高一化學第二學期期末復習檢測試題含解析
- 2025屆山東省蓬萊第二中學化學高二下期末學業質量監測試題含解析
- 湖北省當陽市第二高級中學2025屆高一下化學期末達標檢測試題含解析
- 福建泉州市2025年高二下化學期末達標檢測試題含解析
- 機耕道路維護管理辦法
- 內部成員沖突管理辦法
- 杭州學校宿舍管理辦法
- 民企職工公寓管理辦法
- 新疆引進資金管理辦法
- 江西房產經紀管理辦法
- 建筑工地九牌一圖內容僅供參考模板
- 四川電大媒體輔助英語教學媒體形考任務02標準答案
- 發熱性疾病處理思路
- 12J4-1 《常用門窗》標準圖集
- GB/T 19802-2005無損檢測工業射線照相觀片燈最低要求
- 粘包鋼加固施工方案
- 思想道德與法治課件:第六章 第四節 自覺尊法學法守法用法
- 錐坡工程量計算
- T∕CACM 1064-2018 針刀醫學臨床 通用要求
- 加油站加油機風險告知卡
- 空調設計通用氣象參數
評論
0/150
提交評論