1、查询表结构

  desc 表名

2、显示当前连接用户

  show user

3、查看系统拥有哪些用户

  select * from all_users; 

4、查询当前用户下所有对象

  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='';
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;

最新文章

  1. Spring Boot
  2. BZOJ4596: [Shoi2016]黑暗前的幻想乡
  3. CodeSmith 介绍
  4. Xcode模拟器不显示SDK版本,反而显示设备ID的解决办法
  5. iOS启动页设置
  6. linux apache 自动监护脚本
  7. CI_Autocomplete_2.0.php轻松实现Bebeans与Codeigniter的智能提示
  8. 传感器仿真平台——UI绘制模块(二)
  9. js中字符串方法
  10. 网络资源(8) - JAX-RS视频
  11. SQL - Order By如何处理NULL
  12. Django之ORM操作
  13. mac环境下mongodb的安装和使用
  14. Java编程实现多线程TCP服务器完整实例
  15. jfinal中excel表格导出
  16. [poj 2453] An Easy Problem
  17. 接口的鉴权&amp;响应数据解密
  18. postgresql----COPY之表与文件之间的拷贝
  19. search() 方法解析
  20. 【C】——使用creat()函数需要注意的事项

热门文章

  1. Java--正则表达式-简单的在字符串中找数字
  2. python基础之运算符
  3. spring入门(七)【springMVC返回json串】
  4. GJM :C#开发 异步处理是目的,多线程是手段
  5. [Cordova] 手机网页里的1px
  6. 利用伪类:before&amp;&amp;:after实现图标库图标
  7. cocos2d-x3.3 以前版本 工程Xcode6编译时的问题
  8. Azure IoT带来更高效的新能源生产和会看人脸色的无人超市
  9. SJGestureUnlock快速集成手势解锁
  10. MySQL 的相关语句(增删改查)(SQLyog软件实现)