数据库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;