Oracle实验练习汇总.doc

实验二 Oracle函数的使用二、实验内容【说明：第一部分是基于scott用户的emp表的查询】 第一部分：字符型函数 - 12 -/ 151、以首字母大写的方式显示所有员工的姓名：select initcap(ename) ENAME from emp ;2、将员工的名字分别用大写和小写显示 ：select ename from emp; select lower(ename) ename from emp;3、显示员工姓名为5个字符的员工：select ename from emp where length(ename)=5 ;4、显示所有员工姓名的前三个字符：select substr(ename,0,3) ENAME3 from emp ;5、显示所有员工姓名的后三个字符：select substr(ename,-3,3) ENAME3 from emp ;6、以字符长度为10的方式显示员工职位，多余的位数在右边以*来填充：select rpad(ename,10,*) ENAME from emp;7、找出字符串oracle training中第二个ra出现的位置：select instr(oracle training,ra,1,2) from dual;8、去除字符串 aadde gf 两边的空格：select trim( aadde gf ) from dual;9、以指定格式显示员工的工资(格式：*的工资是 800)：select concat(concat(ename,的工资是),sal) sal from emp;10、显示所有员工的姓名,用a替换所有A：select replace(ename,A,a) ENAME from emp;11、显示员工姓名中第二个字符是L的员工：select ename from emp where instr(ename,L)=2;12、显示员工姓名中最后一个字符是T的员工：select ename from emp where substr(ename,-1)=T;13、显示在月为30天的情况所有员工的日薪,忽略余数：select trunc(sal/30),ename from emp; 或者 select floor(sal/30),ename from emp;14、显示所有12月份入职的员工：select ename,hiredate from emp where to_char(hiredate,mm)=12;15、显示员工的年薪（12个月的工资+补贴）：select ename,(sal+nvl(comm,0)*12 salary from emp;16、显示所有员工的姓名、加入公司的年份和月份，并且按照年份排序：select ename,to_char(hiredate,yy)year,to_char(hiredate,mm) month from emp order by 2,3; /(order by 2,3结果第二、三项表达式的值，升序排列记录)17、 显示每月倒数第3天入职的所有员工：select ename,hiredate from emp where last_day(hiredate)-2=hiredate;18、写出查询目的:Select empno,hiredate,months_between(sysdate,hiredate) tenure,add_nonths(hiredate,6) review,next_day(hiredate,星期五),last_day(hiredate) from emp where months_between(sysdate,hiredate)350;目的：查询员工信息，信息包括（员工编号，入职时间，在职时间，半年后的日期，入职日期的第二天，入职月份的最后一天日期）。19、将系统日期显示转换成中文的年月日。select to_char(sysdate,yyyy年mm月dd日) 系统日期 from dual;20、 查找每个部门的最低薪水，并只显示最低薪水小于1000的部门，输出结果升序排列select deptno,min(sal+nvl(comm,0) 最低薪水 from emp group by deptno having min(sal)=65000order by sum；第二部分：数值型函数（基于dual表的查询）22、将数845.558舍入到两位小数select round(845.558,2) from dual;23、将数845.558舍入到小数点前两位select round(845.558,-2) from dual;24、将数845.558的小数部分舍去select trunc(845.558) from dual;25、将数2.3454中的454从小数位中截去select trunc(2.3454,1) from dual;实验三 PL/SQL基础1、定义一个PL/SQL块，完成如下功能：输入一个三位数，输出其各个数位上的数字。declare abc number(20):=&abc; a number(4); b number(4); c number(4); begin a:=floor(abc/100); b:=mod(floor(abc/10),10); c:=mod(abc,10); dbms_output.put_line(个位： | a); dbms_output.put_line(十位： | b); dbms_output.put_line(百位： | c); end;/ 2、用搜索式的CASE语句，写出下列脚本：在teacher表中，根据教师的收入计算个人所得税。收入低于或等于1000元，无个人所得税，收入在1000元以上，3000元以下的个人所得税为3%，收入在3000元以上的个人所得税为5%。（收入为工资和奖金之和）DECLARE v_id teachers.teacher_id%TYPE; v_bonus teachers.bonus%TYPE; v_wage teachers.wage%TYPE; v_income NUMBER(7,2);BEGIN v_id := &teacher_id; SELECT bonus, wage INTO v_bonus, v_wage FROM teachers WHERE teacher_id = v_id; v_income := v_bonus + v_wage; CASE WHEN v_income 1000 AND v_income = 3000 THEN DBMS_OUTPUT.PUT_LINE (个人所得税：|v_income*0.05); END CASE;END;3、使用FOR循环，分别计算110的阶乘，并将结果存入total表中。DECLARE v_i INT:=1; v_factorial INT:=1;BEGIN FOR v_i IN 1.10 LOOP v_factorial := v_factorial*v_i; INSERT INTO TOTAL VALUES(v_i,v_factorial); END LOOP;END;4、在teachers表中插入教师记录(11101,王彤, 教授, 01-9月-1990,1000,3000,999)，考虑teachers表可能引起违反参照完整性的错误。由于Departments表中不存在部门号999，因此，发生异常。（其中异常名：e_deptid, 异常号：-2291）注：本题使用非预定义异常。DECLARE e_deptid EXCEPTION; PRAGMA EXCEPTION_INIT(e_deptid, -2291); BEGIN INSERT INTO Teachers values(11101,王彤, 教授, 01-9月-1990,1000,3000,999); EXCEPTION WHEN e_deptid THENDbms_output.put_line (插入的部门号在父表中不存在！); END;5、向teachers表中插入一教师记录(10111,王彤, 教授, 01-9月-1990,1000,v_wage,101)，如果教师工资为负值，则抛出并处理异常，提示“教师工资不能为负值！”，（其中异常名：e_wage, 异常号：-20001）,注：本题使用自定义异常。扩展：若上述异常没有捕获到，则提示“查询教师奖金时出错！”。DECLARE e_wage EXCEPTION; v_wage Teachers.wage%TYPE; v_deptid Teachers.department_id%TYPE; v_bonus Teachers.bonus%TYPE; BEGIN v_wage := &wage; v_deptid := &department_id; INSERT INTO Teachers VALUES(10111,王彤, 教授, 01-9月-1990,1000,v_wage,101); SELECT bonus INTO v_bonus FROM Teachers WHERE department_id = v_deptid; IF v_wage 0 then dbms_output.put_line(添加新教师成功！); else dbms_output.put_line(系部号 | :new.department_id | 在系部表中不存在！); end if;end TEACHER_INSERT_TGR;【结果查询】 insert into teachers values(10333,王五,助教,05-9月-2000,500,1900,103);2、定义一个触发器TEACHER_UPDATE_TGR，当需要更新用户方案SCOTT中教师表的数据记录时，需要同时判断该数据记录的系部号是否存在，若存在，则显示表示正确的消息，否则显示表示错误的信息。create or replace trigger TEACHER_UPDATE_TGR before update on teachers for each rowdeclare counter integer;begin select count(*) into counter from departments where department_id = :new.department_id; if counter 0 then dbms_output.put_line(更新教师信息成功！); else dbms_output.put_line(系部号 | :new.department_id | 在系部表中不存在，更新失败！); end if; end TEACHER_UPDATE_TGR;【结果查询】 update teachers set wage=9999 where teacher_id=10228; Select * from teachers where teacher_id=10228;3、定义一个触发器TEACHER_DELETE_TGR，当需要删除用户方案SCOTT中系部表的数据记录时，需要同时删除教师表的相关数据记录，并显示相应的消息。create or replace trigger TEACHER_DELETE_TGR before delete on departments for each rowdeclarebegin delete from teachers a where a.department_id = :old.department_id; dbms_output.put_line(成功对应删除教师信息！);end TEACHER_DELETE_TGR;【结果查询】 delete from departments where department_id=102; Select * from departments; Select * from teachers;实验六 管理表空间【十六章】计算机文件位置： E:appAdministratororadataorcl1、创建本地管理方式下的自动分区管理的表空间USERTBS1,其对应的数据文件为USERTBS1_1.DBF，大小为20MB。CREATE TABLESPACE USERTBS1 DATAFILE E:appAdministratororadataorclUSERTBS1_1.DBF SIZE 20M ;2、使用SQL命令创建一个本地管理方式下的表空间USERTBS2，其对应的数据文件名为USERTBS2_1.DBF，大小为20MB，要求每个分区大小为512KB。CREATE TABLESPACE USERTBS2 DATAFILE E:appAdministratororadataorclUSERTBS2_1.DBF SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K；3、为EXAMPLE表空间添加一个数据文件，文件名为example02.dbf，大小为20MB。ALTER TABLESPACE EXAMPLE ADD DATAFILE E:appAdministratororadataorclexample02.dbf SIZE 20MB;4、修改USERTBS1表空间的大小，将该表空间的数据文件改为自动扩展方式，每次扩展5MB，最大值为100MB。ALTER DATABASE DATAFILE E:appAdministratororadataorclUSERTBS1_1.DBF AUTOEXTEND ON NEXT 5MB MAXSIZE 100M;5、将USERTBS2表空间的数据文件USERTBS2_1.DBF大小增加到50MB，以改变该表空间的大小。ALTER DATABASE DATAFILE E:appAdministratororadataorclUSERTBS2_1.DBF RESIZE 50M;6、修改EXAMPLE表空间的example02.dbf文件的大小为40MB。ALTER database datafile E:appAdministratororadataorclexample02.dbf resize 40M;7、使用SQL命令创建本地管理方式下的临时表空间TEMPTBS，并将该表空间作为当前数据库实例的默认临时表空间。CREATE TEMPORARY TABLESPACE TEMPTBS TEMPFILEE:appAdministratororadataorclTEMPTBS_1.DBF SIZE 20MALTER DATABASE DEFAULT TABLESPACE TABLESPACE TEMPTBS;select temporary_tablespace from dba_users where username =SCOTT;8、创建一个回滚表空间UNDOTBS，并作为数据库的撤销表空间。CREATE UNDO TABLESPACE UNDOTBS DATAFILE E:appAdministratororadataorcl UNDOTBS_1.DBF SIZE 20M;alter system set UNDO_TABLESPACE=UNDOTBS;select tablespace_name , status from dba_rollback_segs;9、删除表空间USERTBS2，同时删除该表空间的内容以及对应的操作系统文件。DROP TABLESPACE USERTBS2 INCLUDING CONTENTS AND DATAFILES;第四章练习2、打开sqlplus，查看emp表和dept表字段的数据类型desc scott.emp;创建、修改和删除表1、创建emp表的副本emp_copy(表结构和记录行都有) 提示：用子查询实现 CREATE TABLE EMP_COPY AS SELECT * FROM SCOTT.EMP;2、创建emp表的副本emp_no(有表结构，没有记录行) 提示：增加 where 1=2 CREATE TABLE EMP_NO AS SELECT * FROM SCOTT.EMP WHERE 1=2;3、修改emp_no表，向该表中增加一列address（字段类型是varchar2，长度200） Alter table emp_no add address varchar2(200);4、修改emp_no表，修改sal的字段类型，改为number(8,2) Alter table emp_no modify sal number(8,2);5、修改emp_no表，删除address字段 Alter table emp_no drop column address;（二）约束的使用补充查看约束：select constraint_name,constraint_type,column_name from user_constraints natural join user_cons_columns where table_name=EMP;1、primary key约束 增删emp_copy表中的primary key约束。（empno） Alter table emp_copy add constraints pk_empno primary key(empno);2、foreign key约束 增删emp_copy表中的foreign key约束。（deptno） Alter table emp_no Add constraint fk_deptno foreign key(deptno) References emp_a(deptno) On delete cascade;3、check约束 增删emp_copy表中的check约束。（要求性别只能输入“男”或“女”） alter table emp_copy add constraint chk_sek check(sex=男 or sex=女); drop constrain chk_sex;4、unique约束 增删emp_copy表中的unique约束。（ename） alter table emp_copy add constraint unq_ename unique(ename);5、not null约束 增删emp_copy表中的not null约束。（job） Alter table emp_copy modify job Not Null;（三）DML和DQL1、添加数据 : insert into emp_copy(empne,ename,job,sex) Values(001,Jane,tea boss,男);2、修改数据 : update emp_copy set ename=Tom;3、删除数据 :delete from emp_copy where ename=Tom; 被删除了所有行利用分级查询显示emp表中员工与领导之间的关系（从高到低）。pSELECT empno,ename,mgr FROM emp START WITH empno=7839pCONNECT BY PRIOR empno=mgr;查询显示工资大于2000且最高领导为JONES的员工信息。pSELECT empno,ename,mgr,sal FROM emp WHERE sal2000pSTART WITH ename=JONES CONNECT BY PRIOR empno=mgr;查询员工信息，不包括以7698号员工为最高领导的员工pSELECT empno,ename,mgr FROM emp START WITH empno=7839pCONNECT BY PRIOR empno=mgr AND empno!=7698;p-pSELECT lpad( ,5*LEVEL-1)|empno EMPNO, lpad( ,5*LEVEL-1)|ename ENAME pFROM emp START WITH empno=7839 CONNECT BY PRIOR empno=mgr; p使用ROLLUP 和CUBE: 如果在GROUP BY子句中使用ROLLUP选项，则还可以生成横向统计和不分组统计；如果使用CUBE选项，则还可以生成横向统计、纵向统计和不分组统计。SELECT deptno,job,avg(sal) FROM emp GROUP BY ROLLUP(deptno,job);SELECT deptno,job,avg(sal) FROM emp GROUP BY CUBE(deptno,job);p合并分组查询：使用GROUPING SETS可以将几个单独的分组查询合并成一个分组查询 SELECT deptno,job,avg(sal) FROM emp GROUP BY GROUPING SETS(deptno,job);第五、六章练习 分析函数1、按照deptno分组，姓名升序排序，然后计算每组值的总和。SELECT E.EMPNO,E.ENAME,E.DEPTNO,E.SAL,sum(sal) OVER(PARTITION BY E.DEPTNO ORDER BY e.ename ROWS BETWEEN unbounded preceding AND unbounded following) sumFROM EMP E;2、对各部门进行分组，并附带显示第一行至当前行的汇总。SELECT E.EMPNO,E.ENAME,E.DEPTNO,E.SAL,sum(sal) OVER(PARTITION BY E.DEPTNO ORDER BY e.ename ROWS BETWEEN unbounded preceding AND current row) sumFROM EMP E; 3、对各部门进行分组，当前行至最后一行的汇总。SELECT E.EMPNO,E.ENAME,E.DEPTNO,E.SAL, sum(sal) OVER(PARTITION BY E.DEPTNO ORDER BY e.ename ROWS BETWEEN current row AND unbounded following) sumFROM EMP E;4、显示各雇员的姓名、部门号、工资及其预增工资，其中10号、20号、30号部门分别增加10%，12%和15%，其余不变。（要求使用decode函数）SELECT ENAME,DEPTNO,SAL, DECODE(deptno,10,sal*1.1,20,sal*1.2,30,sal*1.15) 预增工资 FROM EMP;第七章练习1在command窗口做。定义表的结构students表结构CREATE TABLE students ( student_id NUMBER(5) CONSTRAINT student_pk PRIMARY KEY, sex VARCHAR2(6) CONSTRAINT sex_chk CHECK(sex IN (男,女), dob DATE,);students_grade表结构CREATE TABLE students_grade( student_id NUMBER(5) CONSTRAINT students_grade_fk_students REFERENCES students(student_id), score NUMBER(4,1);例7.1_1 编写一个PL/SQL块，输出字符串This a minimum anonymous block。SET SERVEROUTPUT ONBEGIN Dbms_output.put_line(This a minimum anonymous block);END;例7.1_2 编写一个PL/SQL块，输出学号为10318的学生的姓名。DECLARE v_sname VARCHAR2(10);BEGIN SELECT name INTO v_sname FROM Students WHERE student_id = 10318; Dbms_output.put_line(学生姓名：|v_sname); END;例7.1_3 编写一个PL/SQL块，根据输入的学号，输出该名学生的姓名，并且考虑输入不存在的学号的情况。 DECLARE v_sname VARCHAR2(10); BEGIN SELECT name INTO v_sname from Students where student_id = &student_id; Dbms_output.put_line(学生姓名：|v_sname); EXCEPTION WHEN NO_DATA_FOUND THEN Dbms_output.put_line (输入的学号不存在！); END; 例7.2_1 在departments表中查询部门编号为101的记录，并把系部姓名和系部所在地显示出来。使用标量类型。DECLARE v_id Departments.department_id%type; v_name Departments.department_name%type; v_address Departments.address%type;BEGIN Select* into v_id,v_name,v_address From Departments where department_id = 101; Dbms_output.put_line (系部名称：|v_name); Dbms_output.put_line (系部地址：|v_address);END;1、在student表中查询学号为10112的记录，并显示该生的姓名，性别，出生日期。使用标量类型。set serveroutput on; Declarev_name %type;v_sex students.sex%type;v_dob students.dob%type;beginselect name,sex,dob into v_name,v_sex,v_dobfrom students where student_id = 10112;dbms_output.put_line(学生姓名：|v_name);dbms_output.put_line(学生性别：|v_sex);dbms_output.put_line(学生出生日期：|v_dob);end;2、在student表中查询姓王同学的记录，并显示该生的姓名，性别，出生日期。使用记录类型。扩展：可以查找其他姓氏的同学的记录。DECLARE v_student students%rowtype;BEGIN SELECT * INTO v_student FROM studentsWHERE name like 王%; dbms_output.put_line(学生姓名： | v_);dbms_output.put_line( (学生性别： | v_student.sex);dbms_output.put_line(学生出生日期： |v_student.dob);EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line( (此姓氏不存在！);END;扩展前的代码，是对的第七章练习21、用IFTHENELSIFTHENELSEEND IF格式，写出下列脚本：在teachers表中，将教授职称的某位教师的工资提高10%，若教师的职称不是教授，而是高工或副教授则工资提高5%，否则（既不是教授，也不是高工或副教授），工资提高100元。set serveroutput on;DECLARE v_id Teachers.teacher_id%TYPE; v_title Teachers.title%TYPE;BEGIN v_id := &teacher_id; SELECT title INTO v_title FROM Teachers WHERE teacher_id = v_id; IF v_title = 教授 THEN UPDATE Teachers SET wage = 1.1*wage WHERE teacher_id = v_id; ELSIF v_title = 高工 or v_title=副教授 THEN UPDATE TeachersSET wage = 1.05*wage where teacher_id = v_id; ELSE UPDATE TeachersSET wage = wage+100 where teacher_id = v_id; END IF;END;2、用简单CASE语句，在teachers表中，将教授职称的某位教师的工资提高10%，若教师的职称不是教授，而是高工或副教授则工资提高5%，否则（既不是教授，也不是高工或副教授），工资提高100元。set serveroutput on;DECLARE v_id Teachers.teacher_id%TYPE; v_title Teachers.title%TYPE;BEGIN v_id := &teacher_id; SELECT title INTO v_title FROM Teachers WHERE teacher_id = v_id; case v_title when 教授 THEN UPDATE Teachers SET wage = 1.1*wage where teacher_id = v_id; when 高工 | 副教授 THEN UPDATE TeachersSET wage = 1.05*wage where teacher_id = v_id; else UPDATE TeachersSET wage = wage+100 where teacher_id = v_id; END case;END;3、用搜索式的CASE语句，在teachers表中，将教授职称的某位教师的工资提高15%，若教师的职称是高工则工资提高5%，若教师的职称是副教授则工资提高10%，否则（既不是教授，也不是高工或副教授），工资提高100元。set serveroutput on;DECLARE v_id Teachers.teacher_id%TYPE; v_title Teachers.