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

实验8 Oracle数据库综合练习 一.实验目的 （1）熟悉SQL*PLUS常用命令的功能和使用方法 （2）掌握SQL语言中查询语句的功能和使用方法 （3）学会对ORACLE数据表进行用户权限等相关数据控制命令。。 二.实验内容 1．创建和修改表 （1）登录scott账户，建立一个表（表名自定），表结构与EMP相同。 create table my_emp as select * from emp; 用INSERT语句输入1条记录，并提交。 insert into my_emp values(200,Winty,CLERK,300,to_date(6-20-2014,mm-dd-yyyy),1500,500,40); commit; 建立一个与DEPT表结构和记录完全相同的新表，并与前项新表建立参照完整性约束。 create table my_dept as select * from dept; alter table my_dept add constraint s1 primary key(deptno); alter table my_emp add constraint s2 foreign key(deptno) references dept(deptno); 对在BOSTON工作的雇员加工资，每人加200。 update my_emp set sal=sal+200 where my_emp.deptno in (select deptno from my_dept where my_dept.loc=BOSTON); 2．查询技术 （1） 查询部门号为40的所有人员。 select * from my_emp a,my_dept b where a.deptno=b.deptno and b.deptno=40; 建立一个视图v_my_emp，视图包括my_emp表的empno、ename、sal，并按sal从大到小排列。 create view V_my_emp as select empno,ename,sal from emp order by sal desc; 在my_emp表中建立ename的唯一性索引。 alter table my_emp add constraint sfds unique(ename); 测试截图如下： 计算emp表中COMM最高与最低的差值，COMM值为空时按0计算。 Declare var1 number; var2 number; val_comm number; begin select max(nvl(comm,0)) into var1 from emp; select min(nvl(comm,0)) into var2 from emp; val_comm:=var1-var2; dbms_output.put_line(val_comm); end; 插入my_emp表中的数据记录，考虑可能出现的例外，并提示。（主要的例外提示：唯一性索引值重复DUP_VAL_ON_INDEX ） Begin insert into my_emp values(7369,winty,CLERK,7902, to_date(17-12-80,dd-mm-yy),800,NULL,20); exception when DUP_VAL_ON_INDEX then dbms_output.put_line(记录重复); when others then null; end; 在my_emp表中利用简单循环统计并输出各个部门的平均工资（用游标来完成） DECLARE CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal FROM my_emp GROUP BY deptno; v_dept c_dept_stat%ROWTYPE; BEGIN OPEN c_dept_stat; LOOP FETCH c_dept_stat INTO v_dept; EXIT WHEN c_dept_stat%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_dept.deptno|| || v_dept.avgsal); END LOOP; CLOSE c_dept_stat; END; 6.建立一个存储过程，根据输入的部门编号输出该部门的名称和位置，如果部门不存在，提示不存在该部门！信息。 创建存储过程： CREA