华南农业大学数据库系统概念实验报告六.doc

《华南农业大学数据库系统概念实验报告六.doc》由会员分享，可在线阅读，更多相关《华南农业大学数据库系统概念实验报告六.doc（27页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库系统实验报告六学号姓名实验时间2014-12-17实验名称存储过程、函数和事务实验学时2准备材料1. SQL Plus命令手册2. PL/SQL用户手册3. 实验教材中实验五、六扩展实验1. 利用企业管理器完成存储过程和函数的定义、执行与管理操作 (此部分内容不要求在实验室完成，不用写入实验报告。)实验环境Oracle 9i(及以上版本)服务器SQL Plus/ SQL Plus work sheet客户端实验目的1 熟悉PL/SQL语法2. 利用PL/SQL编程访问数据库实验内容及步骤1. 完成实验指导书实验五所有实验内容，掌握PL/SQL语言中数据类型、变量、输入输出语句、分支、循环

2、语句的基本用法set serveroutput ondeclarev_name varchar2(10);v_sal number(5);begin select ename,salinto v_name,v_salfrom empwhere empno=7788;dbms_output.put_line(7788号雇员是：|v_name|，工资为：|to_char(v_sal);end;SET SERVEROUTPUT ON DECLARE-声明部分标识v_jobVARCHAR2(9);v_countBINARY_INTEGER DEFAULT 0;v_total_salNUMBER(9,2

3、) := 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);-输出变量v_job的值 DBMS_OUTPUT.PUT_LINE(v_count);-输出变量v_count的值 DBMS_OUTPUT.PUT_LINE(v_date);-输出变量v_date的值 DBMS_OUTPUT.PUT_LINE(c_tax_rate);-输出变量

4、c_tax_rate的值END;SET SERVEROUTPUT ON DECLARE v_enameemp.ename%TYPE;-根据字段定义变量BEGIN SELECTename INTOv_ename FROMemp WHEREempno = 7788;DBMS_OUTPUT.PUT_LINE(v_ename);-输出变量的值END;VARIABLE g_ename VARCHAR2(100)SET SERVEROUTPUT ON BEGIN :g_ename:=:g_ename| Hello ;-在程序中使用结合变量 DBMS_OUTPUT.PUT_LINE(:g_ename);-输

5、出结合变量的值END;SET SERVEROUTPUT ON DECLAREemp_recordemp%ROWTYPE;-定义记录变量BEGIN SELECT * INTOemp_record FROMemp WHEREempno = 7788;-取出一条记录 DBMS_OUTPUT.PUT_LINE(emp_record.ename);-输出记录变量的某个字段 END;SET SERVEROUTPUT ON DECLARE TYPE type_table IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; -类型说明v_t type_table

6、; -定义TABLE变量BEGINv_t(1):=MONDAY;v_t(2):=TUESDAY; v_t(3):=WEDNESDAY;v_t(4):=THURSDAY; v_t(5):=FRIDAY;DBMS_OUTPUT.PUT_LINE(v_t(3); -输出变量的内容END;SET SERVEROUTPUT ONDECLARE V_tempratureNUMBER(5):=32; V_result BOOLEAN:=false;BEGIN V_result:= v_temprature 30; IF V_result THEN DBMS_OUTPUT.PUT_LINE(温度| V_tem

7、prature |度,偏高); END IF; END;SET SERVEROUTPUT ONDECLARE v_sexVARCHAR2(2); v_titil VARCHAR2(10);BEGIN v_sex:=男; IF v_sex =男 THEN v_titil:=先生; ELSE v_titil:=女士; END IF; DBMS_OUTPUT.PUT_LINE(v_titil|您好！);END;SET SERVEROUTPUT ONDECLARE v_sexVARCHAR2(2); v_titil VARCHAR2(10);BEGIN v_sex:=; IF v_sex =男 THE

8、N v_titil:=先生; ELSIF v_sex = 女 THEN v_titil:=女士; ELSE v_titil:=朋友; END IF; DBMS_OUTPUT.PUT_LINE(v_titil|您好！);END;SET SERVEROUTPUT ONDECLARE v_sal NUMBER(5); v_tax NUMBER(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_ta

9、x:= v_sal*0.06; -税率6% ELSE V_tax:= v_sal*0.04; -税率4% END IF; DBMS_OUTPUT.PUT_LINE(应缴税金:|V_tax);END;SET SERVEROUTPUT ONDECLAREv_job VARCHAR2(10);BEGINSELECT job INTO v_jobFROM empWHERE empno=7788;CASE v_jobWHEN PRESIDENT THEN DBMS_OUTPUT.PUT_LINE(雇员职务:总裁);WHEN MANAGER THEN DBMS_OUTPUT.PUT_LINE(雇员职务:经

10、理);WHEN SALESMAN THEN DBMS_OUTPUT.PUT_LINE(雇员职务:推销员);WHEN ANALYST THEN DBMS_OUTPUT.PUT_LINE(雇员职务:系统分析员);WHEN CLERK THEN DBMS_OUTPUT.PUT_LINE(雇员职务:职员);ELSE DBMS_OUTPUT.PUT_LINE(雇员职务:未知);END CASE;END;SET SERVEROUTPUT ONDECLARE v_gradeVARCHAR2(10); v_result VARCHAR2(10);BEGIN v_grade:=B; v_result:=CASE

11、 v_grade WHEN A THEN 优 WHEN B THEN 良 WHEN C THEN 中 WHEN D THEN 差ELSE 未知END; DBMS_OUTPUT.PUT_LINE(评价等级:|V_result);END;SET SERVEROUTPUT ONDECLARE v_salNUMBER(5);BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=7788;CASE WHEN v_sal=3000 THEN DBMS_OUTPUT.PUT_LINE(工资等级：高);WHEN v_sal=1500 THENDBMS_OUTPUT.

12、PUT_LINE(工资等级：中);ELSE DBMS_OUTPUT.PUT_LINE(工资等级：低);END CASE;END;SET SERVEROUTPUT ONDECLARE v_totalNUMBER(5):=0; v_countNUMBER(5):=1;BEGINLOOP v_total:=v_total+v_count*2; EXIT WHEN v_count=15;-条件退出v_count:=v_count+2; END LOOP; DBMS_OUTPUT.PUT_LINE(v_total);END;SET SERVEROUTPUT ONBEGINFOR I IN 1.8 LOO

13、P DBMS_OUTPUT.PUT_LINE(to_char(i)|rpad(*,I,*);END LOOP;END;BEGIN FOR I IN 1.9LOOP IF I=1 OR I=9 THEN DBMS_OUTPUT.PUT_LINE(to_char(I)|rpad( ,12-I, )|rpad(*,2*i-1,*); ELSEDBMS_OUTPUT.PUT_LINE(to_char(I)|rpad( ,12-I, )|*|rpad( ,I*2-3, )|*); END IF;END LOOP; END;SET SERVEROUTPUT ONDECLAREv_countNUMBER(2

14、) := 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;SELECT empno,ename FROM emp WHERE ename=临时;DELETE FROM emp WHERE ename=临时;COMMIT;SET SERVEROUTPUT ONDECLARE v_totalNUMBER(8):=0; v_niNUMBER(8):=0; JNUMBER(5);BEGINFO

15、R I IN 1.10 LOOP J:=1;v_ni:=1; WHILE J=I LOOP v_ni:= v_ni*J; J:=J+1; END LOOP;-内循环求n!v_total:=v_total+v_ni; END LOOP;-外循环求总和 DBMS_OUTPUT.PUT_LINE(v_total);END;SET SERVEROUTPUT ONDECLARE v_totalNUMBER(8):=0; v_niNUMBER(8):=1;BEGIN FOR I IN 1.10 LOOP v_ni:= v_ni*I;-求n! v_total:= v_total+v_ni; END LOOP

16、;-循环求总和 DBMS_OUTPUT.PUT_LINE(v_total);END;2. 编写存储过程或函数，要求查询instructor和department表，依据系名输出系名称、资产、所包含员工姓名等信息，并输出其所包含员工个数。执行存储过程（执行时输入部门号参数），察看输出结果是否正确SET SERVEROUTPUT ON;CREATE OR REPLACE PROCEDURE GET_INSTRUCTOR ( p_dename in varchar)ASnum NUMBER(5); CURSOR instructor_cursor IS SELECT dept_name,budget

17、,name FROM instructor natural join department where dept_name = p_dename;BEGINFOR instructor_record IN instructor_cursor LOOP DBMS_OUTPUT.PUT_LINE(instructor_record.dept_name| |instructor_record.budget| |instructor_record.name);END LOOP;SELECT count(ID) into num FROM instructor natural join departme

18、nt where dept_name = p_dename ;DBMS_OUTPUT.PUT_LINE(员工总人数为: |num);END;execute GET_INSTRUCTOR(Comp. Sci.);3. 编写函数EmpSalStatics完成instructor工资情况统计，按=1000，=2000, =5000, 10000五种情况分别统计职工人数，并输出实验结果CREATE OR REPLACE FUNCTION EmpSalStatics RETURN NUMBERASnum NUMBER(5);BEGIN SELECT count(ID) into num FROM ins

19、tructor where salary = 1000; DBMS_OUTPUT.PUT_LINE(salary=1000: |num); SELECT count(ID) into num FROM instructor where salary = 2000; DBMS_OUTPUT.PUT_LINE(salary=2000: |num); SELECT count(ID) into num FROM instructor where salary = 5000; DBMS_OUTPUT.PUT_LINE(salary=5000: |num); SELECT count(ID) into

20、num FROM instructor where salary = 10000; DBMS_OUTPUT.PUT_LINE(salary 10000; DBMS_OUTPUT.PUT_LINE(salary10000: |num); RETURN 0;END;BEGINDBMS_OUTPUT.PUT_LINE(EmpSalStatics);END;4. 与另一同学交换函数 EmpSalStatics的执行权限、察看对方的EmpSalStatics函数执行结果，并与自己的函数执行结果比较。grant execute ON EmpSalStatics TO CS113 ;BEGINDBMS_OU

21、TPUT.PUT_LINE(EmpSalStatics);DBMS_OUTPUT.PUT_LINE(CS113.EmpSalStatics);END;5. 使用SQL命令察看存储过程和函数的信息DESCRIBE USER_SOURCE;select TEXT from user_source WHERE NAME = EMPSALSTATICS ;DESCRIBE EMPSALSTATICS;SHOW ERRORS;SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME=EMPSALSTATICS;DESCRIBE USER_DEPENDENCIES;SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME=EMPSALSTATICS;出现问题解决方案（列出遇到的问题及其解决方法）