華南農(nóng)業(yè)大學數(shù)據(jù)庫系統(tǒng)概念實驗報告六

1、. 數(shù)據(jù)庫系統(tǒng) 實驗報告六 學 姓名 實驗時間 2014-12-17 號 實 存儲過程 、函數(shù)和事務 實驗學時 2 驗 名 稱 準 1. SQL Plus 命令手冊備 2. PL/SQL 用戶手冊 材 3. 實驗教材中實驗五 、六 料 擴 1. 利用企業(yè)管理器完成存儲過程和函數(shù)的定義 、執(zhí)行與管理操作展 (此部分內(nèi)容不要求在實驗室完成 ，不用寫入實驗報告 。) 實 驗 實 Oracle 9i( 及以上版本 )服務器 驗SQL Plus/ SQL Plus work sheet客戶端 環(huán) 境 實1 熟悉 PL/SQL 語法 驗 2. 利用 PL/SQL 編程訪問數(shù)據(jù)庫 .專業(yè)學習資料. . 目

2、的 實 1. 完成實驗指導書實驗五所有實驗內(nèi)容 ，掌握 PL/SQL 語言中數(shù)據(jù)類型 、 驗變量、輸入輸出語句 、分支、循環(huán)語句的基本用法 內(nèi) set serveroutput on 容 declare 及 v_name varchar2(10); 步 v_sal number(5); 驟 begin select ename,sal into v_name,v_sal from emp where empno=7788; dbms_output.put_line(7788號 雇 員 是 ： |v_name| ， 工 資 為 ： |to_char(v_sal); end; .專業(yè)學習資料. .

3、 SET SERVEROUTPUT ON DECLARE- 聲明部分標識 v_jobVARCHAR2(9); v_count BINARY_INTEGER DEFAULT 0; v_total_salNUMBER(9,2) := 0; v_dateDATE := SYSDATE + 7; c_tax_rateCONSTANT NUMBER(3,2) := 8.25; v_validBOOLEAN NOT NULL := TRUE; BEGIN v_job:=MANAGER; - 在程序中賦值 DBMS_OUTPUT.PUT_LINE(v_job); .專業(yè)學習資料. . - 輸出變量 v_jo

4、b 的值 DBMS_OUTPUT.PUT_LINE(v_count); - 輸出變量 v_count 的值 DBMS_OUTPUT.PUT_LINE(v_date); - 輸出變量 v_date 的值 DBMS_OUTPUT.PUT_LINE(c_tax_rate); - 輸出變量 c_tax_rate 的值 END; SET SERVEROUTPUT ON DECLARE v_enameemp.ename%TYPE;- 根據(jù)字段定義變量 BEGIN .專業(yè)學習資料. . SELECT ename INTOv_ename FROMemp WHERE empno = 7788; DBMS_OUT

5、PUT.PUT_LINE(v_ename); - 輸出變量的值 END; VARIABLEg_ename VARCHAR2(100) SET SERVEROUTPUT ON BEGIN :g_ename:=:g_ename| Hello ; - 在程序中使用結合變量 .專業(yè)學習資料. . DBMS_OUTPUT.PUT_LINE(:g_ename); - 輸出結合變量的值 END; SET SERVEROUTPUT ON .專業(yè)學習資料. . DECLARE emp_recordemp%ROWTYPE;- 定義記錄變量 BEGIN SELECT * INTO emp_record FROMem

6、p WHEREempno = 7788;-取出一條記錄 DBMS_OUTPUT.PUT_LINE(emp_record.ename); - 輸出記錄變量的某個 字段 END; SET SERVEROUTPUT ON DECLARE TYPEtype_tableISTABLEOFVARCHAR2(10)INDEXBY .專業(yè)學習資料. . BINARY_INTEGER; - 類型說明 v_ttype_table;- 定義 TABLE變量 BEGIN v_t(1):=MONDAY; v_t(2):=TUESDAY; v_t(3):=WEDNESDAY; v_t(4):=THURSDAY; v_t(

7、5):=FRIDAY; DBMS_OUTPUT.PUT_LINE(v_t(3); - 輸出變量的內(nèi)容 END; SET SERVEROUTPUT ON DECLARE .專業(yè)學習資料. . V_tempratureNUMBER(5):=32; V_resultBOOLEAN:=false; BEGIN V_result:= v_temprature 30; IF V_result THEN DBMS_OUTPUT.PUT_LINE(溫度 | V_temprature | 度 ,偏高 ); END IF; END; SET SERVEROUTPUT ON DECLARE v_sexVARCHAR

8、2(2); v_titilVARCHAR2(10); .專業(yè)學習資料. . BEGIN v_sex:= 男; IF v_sex = 男 THEN v_titil:= 先生 ; ELSE v_titil:= 女士 ; END IF; DBMS_OUTPUT.PUT_LINE(v_titil|您好！); END; SET SERVEROUTPUT ON DECLARE v_sexVARCHAR2(2); .專業(yè)學習資料. . v_titilVARCHAR2(10); BEGIN v_sex:=; IF v_sex = 男 THEN v_titil:= 先生 ; ELSIF v_sex = 女 TH

9、EN v_titil:= 女士 ; ELSE v_titil:= 朋友 ; END IF; DBMS_OUTPUT.PUT_LINE(v_titil|您好！); END; .專業(yè)學習資料. . SET SERVEROUTPUT ON DECLARE v_salNUMBER(5); v_taxNUMBER(5,2); BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=7788; IF v_sal =3000 THEN V_tax:= v_sal*0.08;- 稅率 8% ELSIF v_sal=1500 THEN V_tax:= v_sal*0.0

10、6; - 稅率 6% ELSE V_tax:= v_sal*0.04; - 稅率 4% END IF; DBMS_OUTPUT.PUT_LINE(應繳稅金 :|V_tax); END; .專業(yè)學習資料. . SET SERVEROUTPUT ON DECLARE v_jobVARCHAR2(10); BEGIN SELECT job INTO v_job FROM emp WHERE empno=7788; CASE v_job WHEN PRESIDENT THEN DBMS_OUTPUT.PUT_LINE(雇員職務 :總裁 ); WHEN MANAGER THEN DBMS_OUTPUT.

11、PUT_LINE(雇員職務 :經(jīng)理 ); .專業(yè)學習資料. . WHEN SALESMAN THEN DBMS_OUTPUT.PUT_LINE(雇員職務 :推銷員 ); WHEN ANALYST THEN DBMS_OUTPUT.PUT_LINE(雇員職務 :系統(tǒng)分析員 ); WHEN CLERK THEN DBMS_OUTPUT.PUT_LINE(雇員職務 :職員 ); ELSE DBMS_OUTPUT.PUT_LINE(雇員職務 :未知 ); END CASE; END; SET SERVEROUTPUT ON DECLARE .專業(yè)學習資料. . v_gradeVARCHAR2(10);

12、 v_resultVARCHAR2(10); BEGIN v_grade:=B; v_result:=CASE v_grade WHEN A THEN 優(yōu) WHEN B THEN 良 WHEN C THEN 中 WHEN D THEN 差 ELSE 未知 END; DBMS_OUTPUT.PUT_LINE(評價等級 :|V_result); END; .專業(yè)學習資料. . SET SERVEROUTPUT ON DECLARE v_salNUMBER(5); BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=7788; CASE WHEN v_s

13、al=3000 THEN DBMS_OUTPUT.PUT_LINE(工資等級 ：高 ); WHEN v_sal=1500 THEN DBMS_OUTPUT.PUT_LINE(工資等級 ：中); ELSE DBMS_OUTPUT.PUT_LINE(工資等級 ：低 ); END CASE; END; .專業(yè)學習資料. . SET SERVEROUTPUT ON DECLARE v_totalNUMBER(5):=0; v_countNUMBER(5):=1; BEGIN LOOP v_total:=v_total+v_count*2; EXIT WHEN v_count=15;-條件退出 v_co

14、unt:=v_count+2; END LOOP; DBMS_OUTPUT.PUT_LINE(v_total); END; .專業(yè)學習資料. . SET SERVEROUTPUT ON BEGIN FOR I IN 1.8 LOOP DBMS_OUTPUT.PUT_LINE(to_char(i)|rpad(*,I,*); END LOOP; END; .專業(yè)學習資料. . BEGIN FOR I IN 1.9 LOOP IF I=1 OR I=9 THEN DBMS_OUTPUT.PUT_LINE(to_char(I)|rpad(,12-I, )|rpad(*,2*i-1,*); ELSE D

15、BMS_OUTPUT.PUT_LINE(to_char(I)|rpad( ,12-I, )|*|rpad( ,I*2-3, )|*); END IF; END LOOP; END; .專業(yè)學習資料. . SET SERVEROUTPUT ON DECLARE v_count NUMBER(2) := 1; BEGIN WHILE v_count 6 LOOP INSERT INTO emp(empno, ename) VALUES (5000+v_count, 臨時 ); v_count := v_count + 1; END LOOP; COMMIT; END; .專業(yè)學習資料. . SEL

16、ECT empno,ename FROM emp WHERE ename=臨時 ; DELETE FROM emp WHERE ename= 臨時 ; COMMIT; .專業(yè)學習資料. . SET SERVEROUTPUT ON DECLARE v_totalNUMBER(8):=0; v_ni NUMBER(8):=0; J NUMBER(5); BEGIN FOR I IN 1.10 LOOP J:=1; v_ni:=1; WHILE J=I LOOP .專業(yè)學習資料. . v_ni:= v_ni*J; J:=J+1; END LOOP;- 內(nèi)循環(huán)求 n! v_total:=v_total

17、+v_ni; END LOOP;- 外循環(huán)求總和 DBMS_OUTPUT.PUT_LINE(v_total); END; SET SERVEROUTPUT ON DECLARE v_totalNUMBER(8):=0; v_niNUMBER(8):=1; BEGIN .專業(yè)學習資料. . FOR I IN 1.10 LOOP v_ni:= v_ni*I;- 求 n! v_total:= v_total+v_ni; END LOOP;- 循環(huán)求總和 DBMS_OUTPUT.PUT_LINE(v_total); END; 2. 編寫存儲過程或函數(shù) ，要求查詢 instructor 和 depart

18、ment 表，依據(jù)系名輸出系名稱 、資產(chǎn)、所包含員工姓名等信息 ，并輸出其所包含員工個數(shù) 。執(zhí)行存儲過程 （執(zhí)行時輸入部門號參數(shù) ），察看輸出結果是否正確 SET SERVEROUTPUT ON; .專業(yè)學習資料. . CREATE OR REPLACE PROCEDURE GET_INSTRUCTOR ( p_denamein varchar) AS num NUMBER(5); CURSOR instructor_cursor IS SELECT dept_name,budget,nameFROMinstructornaturaljoin department where dept_nam

19、e = p_dename; BEGIN FOR instructor_record IN instructor_cursor LOOP DBMS_OUTPUT.PUT_LINE(instructor_record.dept_name| |instructor_record.budget|instructor_); END LOOP; SELECT count(ID)intonumFROM instructornaturaljoindepartment where dept_name = p_dename ; DBMS_OUTPUT.PUT_LINE(員工總人數(shù)為 : |n

20、um); END; execute GET_INSTRUCTOR(Comp. Sci.); .專業(yè)學習資料. . 3. 編寫函數(shù)EmpSalStatics 完成instructor工資情況統(tǒng)計 ，按 =1000 ， =2000, =5000, 10000五種情況分別統(tǒng)計職工人數(shù)，并輸出 實驗結果 CREATE OR REPLACE FUNCTION EmpSalStatics RETURN NUMBER AS num NUMBER(5); BEGIN SELECT count(ID) into num FROMinstructor where salary = 1000; DBMS_OUTPU

21、T.PUT_LINE(salary=1000: |num); SELECT count(ID)intonumFROMinstructorwheresalary = 2000; DBMS_OUTPUT.PUT_LINE(salary=2000: |num); .專業(yè)學習資料. . SELECT count(ID)intonumFROMinstructorwheresalary = 5000; DBMS_OUTPUT.PUT_LINE(salary=5000: |num); SELECT count(ID)intonumFROMinstructorwheresalary = 10000; DBMS_OUTPUT.PUT_LINE(salary 10000; DBMS_OUTPUT.PUT_LINE(salary10000: |num);