《oracle数据库基础及应用实验八》由会员分享，可在线阅读，更多相关《oracle数据库基础及应用实验八（6页珍藏版）》请在金锄头文库上搜索。
1、实验8 Oracle数据库综合练习一.实验目的（1）熟悉SQL*PLUS常用命令的功能和使用方法（2）掌握SQL语言中查询语句的功能和使用方法（3）学会对ORACLE数据表进行用户权限等相关数据控制命令。 二.实验内容1创建和修改表（1）登录scott账户，建立一个表（表名自定），表结构与EMP相同。create table my_emp as select * from emp;（2） 用INSERT语句输入1条记录，并提交。insert into my_emp values(200,Winty,CLERK,300,to_date(6-20-2014,mm-dd-yyyy),1500,500
2、,40);commit;（3） 建立一个与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);（4） 对在BOSTON工作的雇员加工资，每人加200。update my_emp set sal=sal+200 where
3、 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.deptnoand b.deptno=40;（2） 建立一个视图v_my_emp，视图包括my_emp表的empno、ename、sal，并按sal从大到小排列。create view V_my_emp as select empno,ename,sal from emporder by sal desc;
4、（3） 在my_emp表中建立ename的唯一性索引。alter table my_emp add constraint sfds unique(ename);测试截图如下：3. 计算emp表中COMM最高与最低的差值，COMM值为空时按0计算。Declarevar1 number;var2 number;val_comm number;beginselect 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(
5、val_comm);end;4 插入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;5 在my_emp表中利用简单循环统计并输出各个部门的平均工资（用
6、游标来完成）DECLARE CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal FROM my_emp GROUP BY deptno; v_dept c_dept_stat%ROWTYPE;BEGINOPEN 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.
7、建立一个存储过程，根据输入的部门编号输出该部门的名称和位置，如果部门不存在，提示不存在该部门！信息。创建存储过程：CREATE OR REPLACE PROCEDURE get_dept(p_deptno my_dept.deptno%TYPE,dname OUT my_dept.dname%TYPE,loc OUT my_dept.loc%TYPE)ISBEGIN SELECT dname,loc INTO dname,loc FROM my_dept WHERE deptno = p_deptno;EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.
8、put_line(不存在该部门！);END;执行存储过程get_dept：Declare v_deptno my_dept.deptno%type; v_dname my_dept.dname%type; v_loc my_dept.loc%type;Begin v_deptno:=&p_deptno; get_dept(v_deptno,v_dname,v_loc); dbms_output.put_line(部门号：|v_deptno|，部门名：|v_dname|，位置：|v_loc);End;7. 建立一个触发器，当my_emp表中部门号存在时，该部门不允许删除。create or replace trigger dept_line_delete1before delete on my_dept for each rowdeclarev1 number;beginselect count(*) into v1 from my_emp where deptno=:old.deptno;if v1=1 then RAISE_APPLICATION_ERROR(-20000,错误);end if;end;
