實(shí)驗(yàn)四 PLSQL高級(jí)編程

1、實(shí)驗(yàn)四 PL/SQL高級(jí)編程 開發(fā)語言及實(shí)現(xiàn)平臺(tái)或?qū)嶒?yàn)環(huán)境： Oracle 11g 實(shí)踐目的 (1) 掌握游標(biāo)、存儲(chǔ)過程、存儲(chǔ)函數(shù)、包、觸發(fā)器的基本作用。 (2) 掌握存儲(chǔ)過程、存儲(chǔ)函數(shù)、包、觸發(fā)器的建立、修改、查看、刪除操作。 實(shí)驗(yàn)要求 (1) 記錄執(zhí)行命令和操作過程中遇到的問題及解決方法，注意從原理上解釋原因。 (2) 掌握游標(biāo)、存儲(chǔ)過程、存儲(chǔ)函數(shù)、包、觸發(fā)器的命令。 實(shí)驗(yàn)內(nèi)容 1創(chuàng)建存儲(chǔ)過程 (1) 將下列的未命名的PL/SQL，轉(zhuǎn)換成存儲(chǔ)過程，存儲(chǔ)過程名自己設(shè)定，注意比較未命名的 PL/SQL 與命名的 PL/SQL 的差別，如沒有where current of是什么情況。 decl

2、are cursor emp_cursor is select * from emp where deptno=10 for update; begin for emp_record in emp_cursor loop dbms_output.put_line(emp_record.sal); update emp set sal=sal*1.1 where current of emp_cursor; end loop; end;/執(zhí)行sal=sal*1.1語句時(shí)，只對(duì)當(dāng)前游標(biāo)所指向的對(duì)象進(jìn)行操作。執(zhí)行sal=sal*1.1語句時(shí)，對(duì)所有找到的 游標(biāo)對(duì)象進(jìn)行操作。（2）（3）任選一個(gè) (2

3、) 創(chuàng)建存儲(chǔ)過程“dept_count_pro_學(xué)號(hào)后四位”，實(shí)現(xiàn)顯示scott方案中dept表中各本門編號(hào)與名稱，并顯示對(duì)應(yīng)部門的員工姓名和工資。顯示結(jié)果可如下圖，也可自己設(shè)計(jì)： (3) 創(chuàng)建存儲(chǔ)過程“num_pro_學(xué)號(hào)后四位”，通過傳入?yún)?shù)傳入 3個(gè)數(shù)，完成 3 個(gè)數(shù)的從小到大排序，通過 3 個(gè)傳出參數(shù)保存排序后的 3 個(gè)數(shù)，并執(zhí)行該存儲(chǔ)過程，顯示排序結(jié)果。(4) 通過user_source 數(shù)據(jù)字典中查看存儲(chǔ)過程。 2創(chuàng)建函數(shù) （1）（2）任選一個(gè) (1) 創(chuàng)建存儲(chǔ)函數(shù)“emp_fun_學(xué)號(hào)后四位”，通過傳入?yún)?shù)傳入員工的編號(hào)，根據(jù)傳入的員工編號(hào)，檢查該員工是否存在。如果存在，則返回員

4、工的姓名，否則返回“此員工不存在”，并執(zhí)行該存儲(chǔ)函數(shù)。 (2) 假定某電信公司電話收費(fèi)表call_fee_account，包括繳費(fèi)號(hào)碼（telno，字符型）、客戶交費(fèi)日期（pay_date，日期型）、話費(fèi)（charge，數(shù)值型）和滯納金（late_fee，數(shù)值型），不是每筆交費(fèi)記錄都有滯納金。創(chuàng)建一個(gè)存儲(chǔ)函數(shù)，返回指定日期的收費(fèi)總和。 (3) 從 user_source 數(shù)據(jù)字典中查看存儲(chǔ)函數(shù)。 3包 (1) 創(chuàng)建一個(gè)包，包體中包括上面創(chuàng)建過的一個(gè)過程，一個(gè)函數(shù)。 （2）創(chuàng)建一個(gè)包體。s （3）執(zhí)行包。 （4）刪除剛才建立的包名和包體。 DROP PACKAGE BODY 包名； DROP P

5、ACKAGE 包名；4創(chuàng)建觸發(fā)器 （1）新建一個(gè)部門平均工資表，編寫觸發(fā)器實(shí)現(xiàn)當(dāng)雇員表中新增、刪除數(shù)據(jù)或者修改工資時(shí)，重新統(tǒng)計(jì)各部門平均工資。（2）創(chuàng)建一個(gè)替代觸發(fā)器，通過更新視圖來更新基本表（如向通過向視圖插入一條記錄，來實(shí)現(xiàn)對(duì)部門表和員工表插入數(shù)據(jù)的操作。（3）（4）選做一個(gè)(3) 利用 SQL*Plus或 iSQL*Plus創(chuàng)建行級(jí)觸發(fā)器“update_row_tri_學(xué)號(hào)后四位”，當(dāng)dept表的某一“deptno”值更改時(shí)，emp表中對(duì)應(yīng)的“deptno”值也跟著進(jìn)行相應(yīng)的更改。更改“dept”表的某一“deptno”值，查看“emp”表中對(duì)應(yīng)的“deptno”值是否發(fā)生變化。 (4)

6、 利用 SQL*Plus 或 iSQL*Plus 創(chuàng)建語句級(jí)觸發(fā)器“delete_tri_學(xué)號(hào)后四位”，當(dāng)刪除dept表中某個(gè)部門編號(hào)時(shí)，將就emp表中該員工的所有信息一并刪除。刪除“dept”表中某個(gè)員工的信息，查看“emp”表是否還有該部門員工的信息。 (5) 從 user_triggers 數(shù)據(jù)字典中查看觸發(fā)器。 函數(shù)的遞歸調(diào)用：求整數(shù)N的階乘。create or replace function fact(n int) return int is begin if n=1 then return 1; else return fact(n-1)*n; end if;end;/存儲(chǔ)過程的

7、遞歸調(diào)用create or replace procedure manager(v_empno emp.empno%type)is name emp.ename%type; manager_no emp.empno%type; manager_name emp.ename%type;begin select ename,mgr into name,manager_no from emp where empno=v_empno; if manager_no is not null then select ename into manager_name from emp where empno=m

8、anager_no; dbms_output.put_line(name|'->'|manager_name); manager(manager_no);else dbms_output.put_line(name|'是最高層領(lǐng)導(dǎo)');end if;end;/Set serveroutput onexec manager(7369);常見問題分析說明 1創(chuàng)建或修改存儲(chǔ)過程/存儲(chǔ)函數(shù)時(shí)出現(xiàn)“名稱已由現(xiàn)有對(duì)象使用”，創(chuàng)建或修改觸發(fā)器時(shí)出現(xiàn)觸發(fā)器“XXX”已經(jīng)存在 數(shù)據(jù)庫中已存在同名對(duì)象，修改數(shù)據(jù)庫對(duì)象名稱或在“CREATE”關(guān)鍵字后加上“OR REPLACE”

9、即可。 2查看數(shù)據(jù)字典信息時(shí)，SELECT 命令正確，卻查不到數(shù)據(jù),雖然 Oracle 的命令中是不區(qū)分大小寫的，但查看 Oracle 系統(tǒng)數(shù)據(jù)字典信息時(shí)所有的字母均需大寫，即便是用戶定義的表名。 例如，正確的命令是： SELECT * FROM DBA_SOURCE WHERE NAME= 'CSMONEY1_PRO'錯(cuò)誤的命令是：SELECT * FROM DBA_SOURCE WHERE NAME= 'csmoney1_pro' 3定義相沖突功能的觸發(fā)器時(shí)會(huì)出錯(cuò)，如定義兩個(gè)觸發(fā)器，都是對(duì)于同一個(gè)表，當(dāng)更新被參照表時(shí)，參照表一個(gè)觸發(fā)器是級(jí)聯(lián)置空，一個(gè)是觸發(fā)器

10、是級(jí)聯(lián)刪除 ，則觸發(fā)器在執(zhí)行時(shí)會(huì)報(bào)錯(cuò)。 是觸發(fā)器只能完成不沖突的動(dòng)作。 4利用存儲(chǔ)過程/觸發(fā)器增強(qiáng)參照完整性約束 參照完整性是指若兩個(gè)表之間具有父子關(guān)系，當(dāng)刪除父表數(shù)據(jù)時(shí)，必須確保相關(guān)的子表數(shù)據(jù)已經(jīng)被刪除；當(dāng)修改父表的主鍵列數(shù)據(jù)時(shí)，必須確保相關(guān)子表數(shù)據(jù)已經(jīng)被修改。為了實(shí)現(xiàn)級(jí)聯(lián)刪除，可以在定義外鍵約束時(shí)指定 ON DELETE CASCADE 關(guān)鍵字，或是創(chuàng)建存儲(chǔ)過程/觸發(fā)器完成，但使用約束卻不能實(shí)現(xiàn)級(jí)聯(lián)更新，此時(shí)需要使用存儲(chǔ)過程/觸發(fā)器增強(qiáng)參照完整性約束。如果在級(jí)聯(lián)更新的同時(shí)又想接收參數(shù)，那么只能使用存儲(chǔ)過程了。 5如何在 Oracle中實(shí)現(xiàn)類似自動(dòng)增加 ID的功能 Oracle本身并未提供像

11、 Access中的自動(dòng)編號(hào)類型，但同樣也可以實(shí)現(xiàn)類似自動(dòng)增加 ID的功能，即字段值自動(dòng)增長(zhǎng)并自動(dòng)插入到字段中，這時(shí)需要借助序列和觸發(fā)器共同來實(shí)現(xiàn)。 例如，水果表“fruit”中有兩個(gè)字段“num”、“name”，分別記錄序號(hào)和水果的名稱，第一個(gè)字段值隨著第二個(gè)字段值的插入自動(dòng)按順序添加并插入。 CREATE TABLE fruit ( num VARCHAR2(10) PRIMARY KEY, name VARCHAR2(10) ); 首先，創(chuàng)建一個(gè)序列 NUM。 CREATE SEQUENCE num INCREMENT BY 1 START WITH 1 MAXVALUE 9999 MIN

12、VALUE 1 NOCYCLE CACHE 20 ORDER; 其次，創(chuàng)建一個(gè)觸發(fā)器。 CREATE TRIGGER fruit_tri BEFORE INSERT ON fruit FOR EACH ROW BEGIN SELECT TO_CHAR(NUM.nextval) INTO :NEW.num FROM DUAL; /將序列的下一個(gè)取值存儲(chǔ)到 fruit 表中的“name”字段，DUAL 為系統(tǒng)表 END; 插入新記錄后再查看“fruit”表中的現(xiàn)有記錄。 INSERT INTO fruit(name) VALUES(菠蘿); SELECT * FROM fruit; 6函數(shù)執(zhí)行幾種方法：1）Select function_name (參數(shù)) from dual;2）varible 變量 類型（長(zhǎng)度）; exec :變量:= function_name (參數(shù)) ; print 變量;（或者為 select :變量 from dual）7存儲(chǔ)過程執(zhí)行幾種方法1)exec procedure_name(parameter_