数据库SQL基本操作
1、查询表结构
desc 表名
2、显示当前连接用户
show user
3、查看系统拥有哪些用户
select * from all_users;
４、查询当前用户下所有对象
select * from tab;
5、回滚事务
roll;
rollback;
6、提交事务
commit;
1. 创建教材的学生－课程数据库中的三个表．定义表时请注意数据类型问题，Oracle的数据类型请参照本实验讲义．要求学号在20000000至99999999之间，年龄<29,性别只能是’男’或’女’，姓名非空。
create table student( sno number(12) constraint pk_student primary key constraint ck_sno check(sno>=20000000 and sno<=99999999), sname char(20) not null constraint u_sname unique, sage number(3) constraint ck_sage check(sage<29), ssex char(2) constraint ck_ssex check(ssex='男' or ssex='女'), sdept char(10) ); create table course( cno number(4) constraint pk_course primary key, cname char(20), cpno number(4) constraint fk_cpno references course (cno), ccredit number(4) ); create table sc( sno number(12) constraint ck_sno check(sno>=20000000 and sno<=99999999), cno number(4), grade number(3), constraint pk_sc primary key(sno,cno), constraint fk_sc foreign key(cno) references course(cno) );
2. 修改学生-课程数据库的3个表的定义，分别为每个表建立主码和外码(如果有)，并输入数据，验证实体完整性和参照完整性。
insert into student values(20140000,'哈哈',20,'男','软件工程'); insert into student values(20140001,' hehe',20,'男','软件工程') insert into student values(20140002,' nike',19,'男','软件工程') insert into course values(10,'数据库',null,2); insert into course values(11,'数据库2',null,2); insert into course values(12,'数据库3',null,2); insert into course values(13,'c++',10,2); insert into course values(14,'java',12,3); insert into sc values(20140000,10,99); insert into sc values(20140001,11,98); insert into sc values(20140002,10,99); insert into sc values(20140003,11,98);
3. 建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码；建立职工表EMP，要求每个职工的应发工资不得超过3000元。应发工资实际上就是实发工资列Sal与扣除项Deduct之和。
create table dept( dname char(10) constraint u_dname unique, deptno number(12) constraint pk_dept primary key ) create table emp( wage number(5) constraint ck_wage check(wage<=3000), sal number(5), deduct number(5) --constraint ck_deduct check(deduct=wage-sal) ) 4. 对以上建立的表建立索引。 create unique index idx_student on student(sname); create unique index idx_course on course(cname); create unique index idx_sc on sc(sno); create unique index idx_dept on dept(deptno); create unique index idx_emp on emp(wage);
/*重建表*/
drop table sc cascade constraint; drop table student cascade constraint; drop table course cascade constraint; Create table Student (Sno number(8) Constraint ck_sno check (Sno>20000000 and sno<99999999) , Sname varchar2(8) not null, Ssex char(2), Sage number(2) Constraint ck_sage check (sage<29) , Sdept varchar2(20), Constraint ck_ss check (Ssex in ('男','女')), constraint PK_student primary key (sno)); Create table Course (Cno number(4) constraint pk_course primary key, Cname varchar2(20), Cpno number(2), Ccredit number(2)); Create table SC (Sno number(8), Cno number(4), Grade number(3), Constraint pk_SC Primary key (Sno,Cno), Constraint fk_s Foreign key (sno ) references student(Sno), Constraint fk_c Foreign key (cno ) references course(Cno) ); Insert Into Student(Sno,Sname,Ssex,Sage,Sdept) Values(20070001,'李佳','女',20,'MA'); Insert Into Student(Sno,Sname,Ssex,Sage,Sdept) Values(20070002,'刘明','男',19,'IS'); Insert Into Student(Sno,Sname,Ssex,Sage,Sdept) Values(20070003,'王添','男',18,'MA'); Insert Into Student(Sno,Sname,Ssex,Sage,Sdept) Values(20070004,'张力','女',21,'IS'); Insert Into Student(Sno,Sname,Ssex,Sage,Sdept) Values(20070005,'张力','女',19,'CS'); Insert Into Student(Sno,Sname,Ssex,Sage,Sdept) Values(20070006,'张力','男',19,'MA'); Insert Into Course(Cno,Cname,Cpno,Ccredit) Values(1,'数据库原理',5,2); Insert Into Course(Cno,Cname, Ccredit) Values(2,'数学',2); Insert Into Course(Cno,Cname,Cpno, Ccredit) Values(3,'信息系统',1,4); Insert Into Course(Cno,Cname,Cpno, Ccredit) Values(4,'操作系统',6,3); Insert Into Course(Cno,Cname,Cpno,Ccredit) Values(5,'数据结构',7,4); Insert Into Course(Cno,Cname,Ccredit) Values(6,'数据处理',2); Insert Into Course(Cno,Cname,Cpno,Ccredit) Values(7,'PASCAL语言',6,4); Insert Into SC(Sno,Cno, Grade) Values(20070001,1,92); Insert Into SC(Sno,Cno, Grade) Values(20070001,2,85); Insert Into SC(Sno,Cno, Grade) Values(20070001,3,88); Insert Into SC(Sno,Cno, Grade) Values(20070002,2,90); Insert Into SC(Sno,Cno, Grade) Values(20070002,3,80); Insert Into SC(Sno,Cno, Grade) Values(20070001,4,92); Insert Into SC(Sno,Cno, Grade) Values(20070001,5,85); Insert Into SC(Sno,Cno, Grade) Values(20070001,6,88); Insert Into SC(Sno,Cno, Grade) Values(20070003,2,90); Insert Into SC(Sno,Cno, Grade) Values(20070003,3,80); Insert Into SC(Sno,Cno, Grade) Values(20070001,7,92); Insert Into SC(Sno,Cno, Grade) Values(20070005,4,92); Insert Into SC(Sno,Cno, Grade) Values(20070003,1,85); Insert Into SC(Sno,Cno, Grade) Values(20070003,4,88); Insert Into SC(Sno,Cno, Grade) Values(20070002,1,90); Insert Into SC(Sno,Cno, Grade) Values(20070002,5,80); 二、数据查询，对实验一建立的学生-课程数据库中的表进行查询 1、查询学生的基本信息； select * from student; 2、查询“CS”系学生的基本信息； select * from student where sdept='CS'; 3、查询“CS”系学生年龄不在19到21之间的学生的学号、姓名； select sno,sname from student where sdept='CS' and ( sage <19 or sage >21 ); 4、找出最大年龄； select max(sage) from student; 5、找出“CS”系年龄最大的学生，显示其学号、姓名； select sno , sname from student where sdept='CS' and sage= (select max(sage) from student where sdept='CS') 6、找出各系年龄最大的学生的年龄； select sdept, max(sage) from student Group by sdept 7、统计“CS”系学生的人数； select count(*) from student where sdept='CS' 8、统计各系学生的人数，结果按升序排列； select sdept,count(*) from student group by sdept order by count(*) asc 9、按系统计各系学生的平均年龄，结果按降序排列； select sdept,avg(sage) from student group by sdept order by avg(sage) desc 10、查询每门课程的课程名； select cname from course 11、查询无先修课的课程的课程名和学时数； select cname,ccredit from course where cpno is null 12、统计无先修课的课程的学时总数； select count(*) from course where cpno is null 13、统计每位学生选修课程的门数、学分及其平均成绩； 统计选修每门课程的学生人数及各门课程的平均成绩； select sno,count(sc.cno),sum(ccredit),avg(grade) from sc,course where course.cno=sc.cno group by sno； select course.cno,count(sno),avg(grade) from course,sc where course.cno=sc.cno group by course.cno； 14、找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列； - select sc.sno,sdept,avg(grade) from sc,student where sc.sno=student.sno and (select avg(grade) from sc)>85 group by sc.sno,sdept order by avg(grade) asc 15、查询选修了“1”或“2”号课程的学生学号和姓名； select student.sno,student.sname from student,sc where student.sno=sc.sno and (1 in cno or 2 in cno) group by student.sno,student.sname 16、查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩； select student.sno,student.sname,grade from student,course,sc where course.cname='数据库原理' and grade<60 and student.sno=sc.sno and course.cno=sc.cno 17、查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）； select student.sno,student.sname,course.cno,cname,grade from student,course,sc where student.sno=sc.sno and course.cno=sc.cno group by student.sno,student.sname,course.cno,cname,grade 18、查询没有选修课程的学生的基本信息； - select student.sno,sname,ssex,sage,sdept from student,sc where cno is null and student.sno=sc.sno group by student.sno,sname,ssex,sage,sdept 19、查询选修课程成绩至少有一门在80分以上的学生学号； select sno from sc where grade>80 group by sno 20、查询选修了3门以上课程的学生学号； select sno from sc s1 where (select count(cno) from sc s2 where s1.sno=s2.sno group by sno)>3 group by sno 21、查询选修课程成绩均在80分以上的学生学号； select sno from sc s1 where 80<all (select grade from sc s2 where s1.sno=s2.sno group by sno,grade) group by sno 三、 视图操作： 1、 定义“IS”系学生基本情况视图V_IS 并查询结果； create view V_IS as select SNO,SNAME,SSEX,SAGE,SDEPT from student where sdept='IS'; select * from V_IS; 2、 将S，C，SC表中学生的学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G并查询结果; create view V_S_C_G as select s.sno,s.sname,c.cno,c.cname,grade from student s,course c,sc; select * from V_S_C_G; 3、 将各系学生人数，平均年龄定义为视图V_NUM_AVG并查询结果; create view V_NUM_AVG(sdept,count,avg) as select sdept,count(sno),avg(sage) from student group by sdept; select * from V_NUM_AVG; 4、 定义一个反映学生出生年份的视图V_YEAR并查询结果; create view V_YEAR(name,birth) as select sname,2016-sage from student select * from V_YEAR; 5、 将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果; create view V_AVG_S_G(sno,cno_count,grade_avg) as select sno,count(cno),avg(grade) from sc group by sno select * from V_AVG_S_G 6、 将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G并查询结果; create view V_AVG_C_G(cno,count,avg) as select cno,count(sno),avg(grade) from sc group by cno select * from V_AVG_C_G 7、 建立视图V_AVG_A查询平均成绩为90分以上的学生学号、姓名和成绩； create view V_AVG_A(sno,sname,grade_avg) as select student.sno,sname,avg(grade) from student,sc where student.sno=sc.sno group by sno having avg(grade)>90 8、 建立视图V_AVG_B查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩； create view V_AVG_B as select student.sno,sname,s1.cno,s1.grade from student,sc s1,sc s0 where student.sno=s1.sno and s0.grade>all(select avg(grade) from sc s2 where s2.cno=s1.cno group by cno) group by student.sno,sname,s1.cno,s1.grade; /*SELECT Sno,Sname,Cname,Grade FROM V_S_C_G vscg WHERE NOT EXISTS (SELECT Sno,Sname,V_S_C_G.Cno,Cname,Grade,avg_grade FROM V_S_C_G,V_AVG_C_G WHERE V_S_C_G.Cno = V_AVG_C_G.Cno AND Grade < avg_grade AND vscg.Sno = V_S_C_G.Sno);*/ 9、 建立视图V_AVG_C按系统计各系平均成绩在80分以上的人数，结果按降序排列； create view V_AVG_C(sc_sno,sdept,avg) as select sc.sno,sdept,avg(grade) from sc,student where sc.sno=student.sno and (select avg(grade) from sc)>80 group by sc.sno,sdept order by avg(grade) desc 10、 通过视图V_IS，分别将学号为“S1”和“S4”的学生姓名更改为“S1_MMM”,”S4_MMM” 并查询结果; create view V_IS as select * from student; update V_IS set sname='S1_MMM' where sno=20070001; update V_IS set sname='S4_MMM' where sno=20070004; 11、 通过视图V_IS，新增加一个学生记录 (‘S12’,’YAN XI’,19,’IS’)，并查询结果； insert into V_IS(sno,sname,sage,sdept) values(20421412,'YAN XI',19,'IS') select * from V_IS 12、 通过视图V_IS，新增加一个学生记录 ('S13','YAN XI',19,'MA')，并查询结果； insert into V_IS(sno,sname,sage,sdept) values(20421413,'YAN XI',19,'MA') select * from V_IS 13、 通过视图V_IS，删除学号为“S12”和“S3”的学生信息，并查询结果； delete from V_IS where sno=20421412 or sno=20070003 select * from V_IS
1 利用 LOOP 循环方式计算 1+3+5+7+9。代码如下： SET SERVEROUTPUT ON DECLARE i NUMBER; sum1 NUMBER; BEGIN i:=1; sum1:=0; LOOP IF mod(i,2)!=0 THEN sum1:=sum1+i; END IF; i:=i+1; IF(i>9) THEN EXIT; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE(sum1); END; SET SERVEROUTPUT ON DECLARE E_name emp.ename%type; E_no emp.empno%type; E_sal emp.sal%type; E_deptno emp.deptno%type; E_hiredate emp.hiredate%type; BEGIN select empno,ename,hiredate,sal,deptno INTO E_no,E_name,E_hiredate,E_sal , E_deptno FROM emp WHERE empno='7788'; DBMS_OUTPUT.PUT_LINE(E_no||'姓名是:'||E_name ||'入职日期:'||E_hiredate ||'工资为:'||E_sal || E_deptno); end; 3.检索工资大于700的员工工资并输出，同时对可能发生的异常进行处理。 declare v_sal emp.sal%type; begin select sal into v_sal from emp where sal>=700; exception when no_data_found then dbms_output.put_line('no data!'); when too_many_rows then dbms_output.put_line('too many row'); when others then null; end; 4.编写PL/SQL程序块，完成如下功能：输入一个员工号，修改该员工的工资，如果该员工为10号部门，工资增加100；若为20号部门，工资增加160；若为30号部门，工资增加200；否则增加300。用if 和case 结构实现。 /*if*/ set serveroutput on declare v_eno emp.empno%TYPE:=&empno; v_esal emp.sal%TYPE; v_edept emp.deptno%TYPE; begin select deptno into v_edept from emp where v_eno=empno; if v_edept=10 then update emp set sal=sal+100 where v_eno=empno; elsif v_edept=20 then update emp set sal=sal+160 where v_eno=empno; elsif v_edept=30 then update emp set sal=sal+200 where v_eno=empno; else update emp set sal=sal+300 where v_eno=empno; end if; end; /*case*/ set serveroutput on declare v_eno emp.empno%TYPE:=&empno; v_esal emp.sal%TYPE; v_edept emp.deptno%TYPE; begin select deptno into v_edept from emp where v_eno=empno; case when v_edept=10 then update emp set sal=sal+100 where v_eno=empno; when v_edept=20 then update emp set sal=sal+160 where v_eno=empno; when v_edept=30 then update emp set sal=sal+200 where v_eno=empno; else update emp set sal=sal+300 where v_eno=empno; end case; end; 5.用3种循环语句来实现将1到50，50个数输入到某个表中。 create table num( num number(10) ); /*case1*/ set SERVEROUTPUT ON; declare v_num num.num%TYPE; begin v_num:=0; loop v_num:=v_num+1; insert into num values(v_num); exit when v_num=50; end loop; end; /*case2*/ set SERVEROUTPUT ON; declare v_num num.num%TYPE; begin v_num:=0; while v_num<50 loop v_num:=v_num+1; insert into num values(v_num); end loop; end; /*case3*/ set SERVEROUTPUT ON; declare v_num num.num%TYPE; begin for v_num in 0..50 loop insert into num values(v_num); end loop; end; 6.系统的自定义异常 查询某个部门的平均工资，如果此工资没有在2000~3000之间，触发自定义异常，请编写PL/SQL来处理此异常。并输出“此部门的平均工资不在2k到3k之间”。 如果你能给此自定义异常分配一个异常号，及异常名（昵称），那自然是极好的。如果不能，也没关系。 set SERVEROUTPUT ON declare v_avg emp.sal%TYPE; e_salout exception; begin select avg(sal) into v_avg from emp; if v_avg < 2000 or v_avg > 3000 then raise e_salout; end if; exception when e_salout then dbms_output.put_line('此部门的平均工资不在2k到3k之间'); end;
1.操作职工号为7788的职工信息，如果存在该职工，修改他的工资，如果没有匹配则插入一新行。（隐式游标的应用）. set serveroutput on begin update emp set sal=sal+100 where emp.empno=7788; if sql%notfound then insert into emp(empno,ename,job,mgr,sal,comm,deptno) values(7778,"strstr","analyst",7566,3640,0,20); end if; end; 2. 游标变量的应用，阅读并执行下列代码，体会游标变量的应用。 PL/SQL中，可以声明游标变量，在运行时刻设置游标对应的查询语句，可以动态实现查询。游标变量的使用步骤如下： 1）声明游标变量：TYPE type_name IS REF CURSOR RETURN return_type; 2）打开游标变量： 如果要将一个游标变量与一个特定的select 语句关联，则需 Open 将其打开，语法如下：OPEN cursor_variable FOR select_statements; 3）循环处理游标的内容。 4）关闭游标变量： CLOSE cursor_variable; 游标变量的应用：使用同一游标打开多个查询 create or replace procedure print_emp as type rc is ref cursor; v_rc rc; v_emp emp%rowtype; v_ename varchar2(40); v_dname varchar2(40); v_loc varchar2(40); begin open v_rc for select * from emp order by job; dbms_output.put_line(' 雇员信息 ' ); dbms_output.put_line(' ======================'); dbms_output.put_line(' 序号 姓名 职务 薪水'); loop fetch v_rc into v_emp; exit when v_rc%NOTFOUND; dbms_output.put_line(rpad(to_char(v_rc%ROWCOUNT),8,' ')||rpad(V_emp.ename,10,' ')|| rpad(V_emp.job,10,' ')|| rpad(V_emp.sal,10,' ')); end loop; open v_rc for select ename,dname,loc from emp,dept where emp.deptno=dept.deptno order by dname; dbms_output.put_line(' 工作信息 ' ); dbms_output.put_line(' ======================='); dbms_output.put_line(' 序号 姓名 部门 地点'); loop fetch v_rc into v_ename,v_dname,v_loc; exit when v_rc%NOTFOUND; dbms_output.put_line(rpad(to_char(v_rc%ROWCOUNT),8,' ')||rpad(V_ename,12,' ')|| rpad(V_dname,12,' ')|| rpad(V_loc,10,' ')); end loop; close v_rc; end print_emp; 3.游标参数的应用。并执行下列代码，输入不同的参数值，体会游标参数的应用。 游标的SELECT语句没有WHERE子句，或者用WHERE子句指定了一个固定的条件，这样每次都查询同样的数据。在更多的情况下，可能要根据实际情况查询不同的数据。为了通过游标对数据进行更加灵活的处理，可以为游标定义参数，这些参数可以用在WHERE子句中。在打开游标时，指定实际的参数值，这样游标在每次打开时，可以根据不同的实际参数值，返回所需的不同数据。 定义带参数的游标的语法格式为： DECLARE CURSOR 游标名(参数1, 参数2, ...) IS SELECT语句; 其中参数的定义方法与子程序中的参数定义完全相同，可以指定默认值。在用OPEN命令打开游标时，要向游标提供实际参数，游标根据提供的参数值，查询符合条件的数据。打开游标的语法格式为： OPEN游标名(实际参数1, 实际参数2...) 阅读下面的查询 例子： DECLARE CURSOR cur_6(d_no IN emp.deptno%type, min_sal IN emp.sal%type := 1000) IS SELECT ename, sal, hiredate FROM emp WHERE deptno = d_no and sal >= min_sal; e cur_6%rowtype; BEGIN if not cur_6%ISOPEN then --如果游标没有打开，则打开它 open cur_6(20, 2000); END if; fetch cur_6 INTO e; --取出第一行数据 while cur_6%found loop dbms_output.put_line('姓名：' || e.ename || '工资：' || e.sal || ' 工作时间：' || e.hiredate); fetch cur_6 INTO e; END loop; dbms_output.put_line('员工总数：' || cur_6%rowcount); --获取的总行数 close cur_6; --关闭游标 END; 4. 编写程序利用3种循环统计并输出各个部门的平均工资。 --case1 set serveroutput on declare v_sal emp.sal%type; v_deptno emp.deptno%type; v_cnt10 number;v_cnt20 number;v_cnt30 number; v_sum10 number;v_sum20 number;v_sum30 number; v_avg10 number;v_avg20 number;v_avg30 number; cursor c_emp is select sal,deptno from emp; begin if (not c_emp%isopen)then open c_emp; end if; v_sum10:=0;v_sum20:=0;v_sum30:=0; v_cnt10:=0;v_cnt20:=0;v_cnt30:=0; loop fetch c_emp into v_sal,v_deptno; exit when c_emp%notfound; if v_deptno = 10 then v_cnt10:=v_cnt10+1; v_sum10:=v_sum10+v_sal; end if; if v_deptno = 20 then v_cnt20:=v_cnt20+1; v_sum20:=v_sum20+v_sal; end if; if v_deptno = 30 then v_cnt30:=v_cnt30+1; v_sum30:=v_sum30+v_sal; end if; end loop; v_avg10:=v_sum10/v_cnt10; v_avg20:=v_sum20/v_cnt20; v_avg30:=v_sum30/v_cnt30; dbms_output.put_line('deptno '||'avg_sal'); dbms_output.put_line('10 '||v_avg10); dbms_output.put_line('20 '||v_avg20); dbms_output.put_line('30 '||v_avg30); end; --case2 set serveroutput on declare v_sal emp.sal%type; v_deptno emp.deptno%type; v_cnt10 number;v_cnt20 number;v_cnt30 number; v_sum10 number;v_sum20 number;v_sum30 number; v_avg10 number;v_avg20 number;v_avg30 number; cursor c_emp is select sal,deptno from emp; begin if (not c_emp%isopen)then open c_emp; end if; v_sum10:=0;v_sum20:=0;v_sum30:=0; v_cnt10:=0;v_cnt20:=0;v_cnt30:=0; fetch c_emp into v_sal,v_deptno; while c_emp%found loop if v_deptno = 10 then v_cnt10:=v_cnt10+1; v_sum10:=v_sum10+v_sal; end if; if v_deptno = 20 then v_cnt20:=v_cnt20+1; v_sum20:=v_sum20+v_sal; end if; if v_deptno = 30 then v_cnt30:=v_cnt30+1; v_sum30:=v_sum30+v_sal; end if; fetch c_emp into v_sal,v_deptno; end loop; v_avg10:=v_sum10/v_cnt10; v_avg20:=v_sum20/v_cnt20; v_avg30:=v_sum30/v_cnt30; dbms_output.put_line('deptno '||'avg_sal'); dbms_output.put_line('10 '||v_avg10); dbms_output.put_line('20 '||v_avg20); dbms_output.put_line('30 '||v_avg30); end; --case3 set serveroutput on declare v_sal emp.sal%type; v_deptno emp.deptno%type; v_cnt10 number;v_cnt20 number;v_cnt30 number; v_sum10 number;v_sum20 number;v_sum30 number; v_avg10 number;v_avg20 number;v_avg30 number; cursor c_emp is select sal,deptno from emp; begin v_sum10:=0;v_sum20:=0;v_sum30:=0; v_cnt10:=0;v_cnt20:=0;v_cnt30:=0; for v_row in c_emp loop if v_row.deptno = 10 then v_cnt10:=v_cnt10+1; v_sum10:=v_sum10+v_row.sal; end if; if v_row.deptno = 20 then v_cnt20:=v_cnt20+1; v_sum20:=v_sum20+v_row.sal; end if; if v_row.deptno = 30 then v_cnt30:=v_cnt30+1; v_sum30:=v_sum30+v_row.sal; end if; end loop; v_avg10:=v_sum10/v_cnt10; v_avg20:=v_sum20/v_cnt20; v_avg30:=v_sum30/v_cnt30; dbms_output.put_line('deptno '||'avg_sal'); dbms_output.put_line('10 '||v_avg10); dbms_output.put_line('20 '||v_avg20); dbms_output.put_line('30 '||v_avg30); end;
1.编写过程，打印全体雇员信息PrintEmpRecord。 set serveroutput on; create or replace procedure PrintEmpRecord as v_emp emp%rowtype; cursor cs is select * from emp; begin if not cs%isopen then open cs; end if; fetch cs into v_emp; while cs%found loop dbms_output.put_line(rpad(to_char(cs%rowcount),8,' ')||rpad(v_emp.ename,10,' ')||rpad(to_char(v_emp.empno),10,' ')||rpad(v_emp.job,10,' ')||rpad(to_char(v_emp.sal),10,' ')||rpad(to_char(v_emp.hiredate),16,' ')); fetch cs into v_emp; end loop; close cs; end PrintEmpRecord; exec PrintEmpRecord; 2.把雇员编号empno作为参数，设计过程SearchEmp查询并打印该雇员信息。 set serveroutput on; create or replace procedure SearchEmp(p_empno in emp.empno%type) as v_emp emp%rowtype; begin select * into v_emp from emp where empno = p_empno; dbms_output.put_line(rpad(v_emp.ename,10,' ')||rpad(to_char(v_emp.empno),10,' ')||rpad(v_emp.job,10,' ')||rpad(to_char(v_emp.sal),10,' ')||rpad(to_char(v_emp.hiredate),16,' ')); exception when no_data_found then dbms_output.put_line('no_data_found'); end SearchEmp; 3. 创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。 create or replace procedure dept_mt_avg(p_deptno in emp.deptno%type) as cursor cs is select ename,empno from emp where deptno = p_deptno and sal>(select avg(sal) from emp where deptno = p_deptno group by deptno); begin for v_emp in cs loop dbms_output.put_line(v_emp.ename||' '||v_emp.empno); end loop; end dept_mt_avg; exec dept_mt_avg(10); 4. 创建一个存储过程，以部门号为参数，返回该部门的人数和最高工资。 create or replace procedure dept_maxSal_count(p_deptno in emp.deptno%type) as cursor cs is select count(*) count,max(sal) max_sal from emp where deptno = p_deptno; begin for v_emp in cs loop dbms_output.put_line(v_emp.count||' '||v_emp.max_sal); end loop; end dept_maxSal_count; exec dept_maxSal_count(10); 5.创建一个以部门号为参数，返回该部门最高工资的函数。 create or replace function dept_maxSal(p_deptno in emp.deptno%type) return number as v_sal emp.sal%type; begin select max(sal) into v_sal from emp where deptno = p_deptno; return v_sal; end; select dept_maxSal(10) from dual; 6.设计一个函数，实现按雇员编号empno得到雇员的新的工资额。新的工资的计算方法为参加工作5年以下的，工资上涨5%；参加工作6-10年的，工资上涨8%；参加工作11-15年的，工资上涨10%；参加工作16-20年的，工资上涨15%；参加工作20年以上的，工资上涨20%； CREATE OR REPLACE FUNCTION NewSal( v_empno emp.deptno%TYPE ) RETURN emp.sal%TYPE AS v_newsal emp.sal%TYPE := 0; v_sysdate DATE; v_hiredate emp.hiredate%TYPE; v_workdate NUMBER; BEGIN SELECT hiredate INTO v_hiredate FROM emp WHERE empno = v_empno; SELECT SYSDATE() INTO v_sysdate FROM DUAL; SELECT v_sysdate - v_hiredate INTO v_workdate FROM DUAL; SELECT sal INTO v_newsal FROM emp WHERE empno = v_empno; IF v_workdate < 365*5 THEN v_newsal := v_newsal * 1.05; ELSIF v_workdate BETWEEN 365*6 AND 365*10 THEN v_newsal := v_newsal * 1.08; ELSIF v_workdate BETWEEN 365*11 AND 365*15 THEN v_newsal := v_newsal * 1.1; ELSIF v_workdate BETWEEN 365*16 AND 365*20 THEN v_newsal := v_newsal * 1.15; ELSE v_newsal := v_newsal * 1.20; END IF; RETURN v_newsal; END; DECLARE BEGIN dbms_output.put_line( Newsal( &empno ) ); END;
（1） 为emp表创建一个触发器，当执行插入操作时，统计操作后员工人数；当执行更新工资操作时，统计更新后员工平均工资；当执行删除操作时，统计删除后各个部门的人数 CREATE OR REPLACE TRIGGER trg_emp_dml AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE v_count NUMBER; v_sal NUMBER(6,2); BEGIN IF INSERTING THEN SELECT count(*) INTO v_count FROM emp; DBMS_OUTPUT.PUT_LINE(v_count); ELSIF UPDATING THEN SELECT avg(sal) INTO v_sal FROM emp; DBMS_OUTPUT.PUT_LINE(v_sal); ELSE FOR v_dept IN (SELECT deptno,count(*) num FROM emp GROUP BY deptno) LOOP DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.num); END LOOP; END IF; END trg_emp_dml; （2） 为emp表创建一个触发器，修改员工工资时，保证修改后的工资高于修改前的工资。 CREATE OR REPLACE TRIGGER trg_emp_update_row BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN(new.sal<=old.sal) BEGIN RAISE_APPLICATION_ERROR (-20001,'The salary is lower!')； END trg_emp_update_row; （3） 创建一个包括员工及其所在部门信息的视图empdept，然后向视图中插入一条记录（2345,’TOM’,3000,’SALES’）。 CREATE OR REPLACE VIEW empdept AS SELECT empno,ename,sal,dname FROM emp,dept WHERE emp.deptno=dept.deptno WITH CHECK OPTION; INSERT INTO empdept VALUES(2345, 'TOM',3000, 'SALES'); * ERROR 位于第 1 行: ORA-01733: 此处不允许虚拟列 CREATE OR REPLACE TRIGGER trig_view INSTEAD OF INSERT ON empdept FOR EACH ROW DECLARE v_deptno dept.deptno%type; BEGIN SELECT deptno INTO v_deptno FROM dept WHERE dname=:new.dname; INSERT INTO emp(empno,ename,sal,deptno) VALUES(:new.empno,:new.ename,v_deptno,:new.sal); END trig_view; （4）创建一个系统级别触发器，记录所有用户的数据库访问时间。 将每个用户的登录信息写入temp_table表中。 CREATE OR REPLACE TRIGGER log_user_connection AFTER LOGON ON DATABASE BEGIN INSERT INTO scott.temp_table VALUES (user,sysdate); END log_user_connection; 4．如何在 Oracle中实现类似自动增加 ID的功能 Oracle本身并未提供像 Access中的自动编号类型，但同样也可以实现类似自动增加 ID的功能，即字段值自动增长并自动插入到字段中，这时需要借助序列和触发器共同来实现。 例如，水果表“fruit”中有两个字段“num”、“name”，分别记录序号和水果的名称，第一个字段值随着第二个字段值的插入自动按顺序添加并插入。 CREATE TABLE fruit ( num VARCHAR2(10) PRIMARY KEY, name VARCHAR2(10) ); 首先，创建一个序列 NUM。 CREATE SEQUENCE num INCREMENT BY 1 START WITH 1 MAXVALUE 9999 MINVALUE 1 NOCYCLE CACHE 20 ORDER; 其次，创建一个触发器。 CREATE TRIGGER fruit_tri BEFORE INSERT ON fruit FOR EACH ROW BEGIN SELECT TO_CHAR(NUM.nextval) INTO :NEW.num FROM DUAL; //将序列的下一个取值存储到 fruit 表中的“name”字段，DUAL 为系统表 END; 插入新记录后再查看“fruit”表中的现有记录。 INSERT INTO fruit(name) VALUES(‘菠萝’); SELECT * FROM fruit;
