oracle数据库基础及应用实验八

《oracle数据库基础及应用实验八》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、实验8Oracle数据库综合练习一.实验目的（1）熟悉SQL*PLUS常用命令的功能和使用方法（2）掌握SQL语言中查询语句的功能和使用方法（3）学会对ORACLE数据表进行用户权限等相关数据控制命令。。二.实验内容1．创建和修改表（1）登录scott账户，建立一个表（表名自定），表结构与EMP相同。createtablemy_empasselect*fromemp;（2）用INSERT语句输入1条记录，并提交。insertintomy_empvalues(200,'Winty','CLERK',300,to_date('6-20-2014','mm-d

2、d-yyyy'),1500,500,40);commit;（3）建立一个与DEPT表结构和记录完全相同的新表，并与前项新表建立参照完整性约束。createtablemy_deptasselect*fromdept;altertablemy_deptaddconstraints1primarykey(deptno);altertablemy_empaddconstraints2foreignkey(deptno)referencesdept(deptno);（4）对在'BOSTON'工作的雇员加工资，每人加200。updatemy_empsetsal=sa

3、l+200wheremy_emp.deptnoin(selectdeptnofrommy_deptwheremy_dept.loc='BOSTON');2．查询技术（1）查询部门号为40的所有人员。select*frommy_empa,my_deptbwherea.deptno=b.deptnoandb.deptno=40;（2）建立一个视图v_my_emp，视图包括my_emp表的empno、ename、sal，并按sal从大到小排列。createviewV_my_empasselectempno,ename,salfromemporderbysald

4、esc;（3）在my_emp表中建立ename的唯一性索引。altertablemy_empaddconstraintsfdsunique(ename);测试截图如下：3.计算emp表中COMM最高与最低的差值，COMM值为空时按0计算。Declarevar1number;var2number;val_commnumber;beginselectmax(nvl(comm,0))intovar1fromemp;selectmin(nvl(comm,0))intovar2fromemp;val_comm:=var1-var2;dbms_output.put_

5、line(val_comm);end;4．插入my_emp表中的数据记录，考虑可能出现的例外，并提示。（主要的例外提示：唯一性索引值重复DUP_VAL_ON_INDEX）Begininsertintomy_empvalues(7369,'winty','CLERK',7902,to_date('17-12-80','dd-mm-yy'),800,NULL,20);exceptionwhenDUP_VAL_ON_INDEXthendbms_output.put_line('记录重复');whenothersthennull;end;5．在my_emp表中利

6、用简单循环统计并输出各个部门的平均工资（用游标来完成）DECLARECURSORc_dept_statISSELECTdeptno,avg(sal)avgsalFROMmy_empGROUPBYdeptno;v_deptc_dept_stat%ROWTYPE;BEGINOPENc_dept_stat;LOOPFETCHc_dept_statINTOv_dept;EXITWHENc_dept_stat%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_dept.deptno

7、

8、''

9、

10、v_dept.avgsal);ENDLOOP;CLOSEc

11、_dept_stat;END;6.建立一个存储过程，根据输入的部门编号输出该部门的名称和位置，如果部门不存在，提示'不存在该部门！'信息。创建存储过程：CREATEORREPLACEPROCEDUREget_dept(p_deptnomy_dept.deptno%TYPE,dnameOUTmy_dept.dname%TYPE,locOUTmy_dept.loc%TYPE)ISBEGINSELECTdname,locINTOdname,locFROMmy_deptWHEREdeptno=p_deptno;EXCEPTIONWHENNO_DATA_FOUND

12、THENdbms_output.put_line('不存在该部门！');END;