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

《数据库系统》实验报告六学号姓名实验时间2014-12-17实验名称存储过程、函数和事务实验学时2准备材料1.SQLPlus命令手册2.PL/SQL用户手册3.实验教材中实验五、六扩展实验1.利用企业管理器完成存储过程和函数的定义、执行与管理操作(此部分内容不要求在实验室完成，不用写入实验报告。)实验环境Oracle9i(及以上版本)服务器SQLPlus/SQLPlusworksheet客户端实验目的1熟悉PL/SQL语法2.利用PL/SQL编程访问数据库实验内容及步骤1.完成实验指导书实验五所有实验内容，掌握PL/SQL语言中数据类型、变量、输入输出语句、分支、循环语句的基本用法setserveroutputondeclarev_namevarchar2(10);v_salnumber(5);beginselectename,salintov_name,v_salfromempwhereempno=7788;dbms_output.put_line('7788号雇员是：'||v_name||'，工资为：'||to_char(v_sal));end;SETSERVEROUTPUTONDECLARE--声明部分标识v_jobVARCHAR2(9);v_countBINARY_INTEGERDEFAULT0;v_total_salNUMBER(9,2):=0;v_dateDATE:=SYSDATE+7;c_tax_rateCONSTANTNUMBER(3,2):=8.25;v_validBOOLEANNOTNULL:=TRUE;BEGINv_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);--输出变量c_tax_rate的值END;SETSERVEROUTPUTONDECLAREv_enameemp.ename%TYPE;--根据字段定义变量BEGINSELECTenameINTOv_enameFROMempWHEREempno=7788;DBMS_OUTPUT.PUT_LINE(v_ename);--输出变量的值END;VARIABLEg_enameVARCHAR2(100)SETSERVEROUTPUTONBEGIN:g_ename:=:g_ename||'Hello~';--在程序中使用结合变量DBMS_OUTPUT.PUT_LINE(:g_ename);--输出结合变量的值END;SETSERVEROUTPUTONDECLAREemp_recordemp%ROWTYPE;--定义记录变量BEGINSELECT*INTOemp_recordFROMempWHEREempno=7788;--取出一条记录DBMS_OUTPUT.PUT_LINE(emp_record.ename);--输出记录变量的某个字段END;SETSERVEROUTPUTONDECLARETYPEtype_tableISTABLEOFVARCHAR2(10)INDEXBYBINARY_INTEGER;--类型说明v_ttype_table;--定义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;SETSERVEROUTPUTONDECLAREV_tempratureNUMBER(5):=32;V_resultBOOLEAN:=false;BEGINV_result:=v_temprature30;IFV_resultTHENDBMS_OUTPUT.PUT_LINE('温度'||V_temprature||'度,偏高');ENDIF;END;SETSERVEROUTPUTONDECLAREv_sexVARCHAR2(2);v_titilVARCHAR2(10);BEGINv_sex:='男';IFv_sex='男'THENv_titil:='先生';ELSEv_titil:='女士';ENDIF;DBMS_OUTPUT.PUT_LINE(v_titil||'您好！');END;SETSERVEROUTPUTONDECLAREv_sexVARCHAR2(2);v_titilVARCHAR2(10);BEGINv_sex:='';IFv_sex='男'THENv_titil:='先生';ELSIFv_sex='女'THENv_titil:='女士';ELSEv_titil:='朋友';ENDIF;DBMS_OUTPUT.PUT_LINE(v_titil||'您好！');END;SETSERVEROUTPUTONDECLAREv_salNUMBER(5);v_taxNUMBER(5,2);BEGINSELECTsalINTOv_salFROMempWHEREempno=7788;IFv_sal=3000THENV_tax:=v_sal*0.08;--税率8%ELSIFv_sal=1500THENV_tax:=v_sal*0.06;--税率6%ELSEV_tax:=v_sal*0.04;--税率4%ENDIF;DBMS_OUTPUT.PUT_LINE('应缴税金:'||V_tax);END;SETSERVEROUTPUTONDECLAREv_jobVARCHAR2(10);BEGINSELECTjobINTOv_jobFROMempWHEREempno=7788;CASEv_jobWHEN'PRESIDENT'THENDBMS_OUTPUT.PUT_LINE('雇员职务:总裁');WHEN'MANAGER'THENDBMS_OUTPUT.PUT_LINE('雇员职务:经理');WHEN'SALESMAN'THENDBMS_OUTPUT.PUT_LINE('雇员职务:推销员');WHEN'ANALYST'THENDBMS_OUTPUT.PUT_LINE('雇员职务:系统分析员');WHEN'CLERK'THENDBMS_OUTPUT.PUT_LINE('雇员职务:职员');ELSEDBMS_OUTPUT.PUT_LINE('雇员职务:未知');ENDCASE;END;SETSERVEROUTPUTONDECLAREv_gradeVARCHAR2(10);v_resultVARCHAR2(10);BEGINv_grade:='B';v_result:=CASEv_gradeWHEN'A'THEN'优'WHEN'B'THEN'良'WHEN'C'THEN'中'WHEN'D'THEN'差'ELSE'未知'END;DBMS_OUTPUT.PUT_LINE('评价等级:'||V_result);END;SETSERVEROUTPUTONDECLAREv_salNUMBER(5);BEGINSELECTsalINTOv_salFROMempWHEREempno=7788;CASEWHENv_sal=3000THENDBMS_OUTPUT.PUT_LINE('工资等级：高');WHENv_sal=1500THENDBMS_OUTPUT.PUT_LINE('工资等级：中');ELSEDBMS_OUTPUT.PUT_LINE('工资等级：低');ENDCASE;END;SETSERVEROUTPUTONDECLAREv_totalNUMBER(5):=0;v_countNUMBER(5):=1;BEGINLOOPv_total:=v_total+v_count**2;EXITWHENv_count=15;--条件退出v_count:=v_count+2;ENDLOOP;DBMS_OUTPUT.PUT_LINE(v_total);END;SETSERVEROUTPUTONBEGINFORIIN1..8LOOPDBMS_OUTPUT.PUT_LINE(to_char(i)||rpad('*',I,'*'));ENDLOOP;END;BEGINFORIIN1..9LOOPIFI=1ORI=9THENDBMS_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,'')||'*');ENDIF;ENDLOOP;END;SETSERVEROUTPUTONDECLAREv_countNUMBER(2):=1;BEGINWHILEv_count6LOOPINSERTINTOemp(empno,ename)VALUES(5000+v_count,'临时');v_count:=v_count+1;ENDLOOP;COMMIT;END;SELECTempno,enameFROMempWHEREename='临时';DELETEFROMempWHEREename='临时';COMMIT;SETSERVEROUTPUTONDECLAREv_totalNUMBER(8):=0;v_niNUMBER(8):=0;JNUMBER(5);BEGINFORIIN1..10LOOPJ:=1;v_ni:=1;WHILEJ=ILOOPv_ni:=v_ni*J;J:=J+1;ENDLOOP;--内循环求n!v_total:=v_total+v_ni;ENDLOOP;--外循环求总和DBMS_OUTPUT.PUT_LINE(v_total);END;SETSERVEROUTPUTONDECLAREv_totalNUMBER(8):=0;v_niNUMBER(8):=1;BEGINFORIIN1..10LOOPv_ni:=v_ni*I;--求n!v_total:=v_total+v_ni;ENDLOOP;--循环求总和DBMS_OUTPUT.PUT_LINE(v_total);END;2.编写存储过程或函数，要求查询instructor和department表，依据系名输出系名称、资产、所包含员工姓名等信息，并输出其所包含员工个数。执行存储过程（执行时输入部门号参数），察看输出结果是否正确SETSERVEROUTPUTON;CREATEORREPLACEPROCEDUREGET_INSTRUCTOR(p_denameinvarchar)ASnumNUMBER(5);CURSORinstructor_cursorISSELECTdept_name,budget,nameFROMinstructornaturaljoindepartmentwheredept_name=p_dename;BEGINFORinstructor_recordINinstructor_cursorLOOPDBMS_OUTPUT.PUT_LINE(instructor_record.dept_name||''||instructor_record.budget||''||instructor_record.name);ENDLOOP;SELECTcount(ID)intonumFROMinstructornaturaljoindepartmentwheredept_name=p_dename;DBMS_OUTPUT.PUT_LINE('员工总人数为:'||num);END;executeGET_INSTRUCTOR('Comp.Sci.');3.编写函数