Oracle数据库基础学习（八）PLSQL综合练习
- 格式：pdf
- 大小：86.21 KB
- 文档页数：5
Oracle数据库基础学习（⼋）PLSQL综合练习
1、定义游标：列出每个员⼯的姓名、部门名称并编程显⽰第10个到第20个记录。
declare
cursor zemp_cursor is (select temp.ename, temp.dname
from (select e.ename, d.dname, ROWNUM rn
from zemp e, zdept d
where e.deptno=d.deptno(+)
and ROWNUM<=20) temp
where temp.rn >10) ;
begin
for zemp_record in zemp_cursor loop --隐式打开游标
dbms_output.put_line('ename is ' || zemp_record.ename || ' dname is ' || zemp_record.dname) ;
end loop; --隐式关闭游标
end;
/
2、定义游标：从雇员表中显⽰⼯资⼤于3000的记录，只要姓名、部门编号和⼯资。编程显⽰其中的奇数记录。
declare
cursor zemp_cursor is (select ROWNUM rn, ename, deptno, sal
from zemp
where sal > 3000) ;
begin
for zemp_record in zemp_cursor loop
--if mod(zemp_record.rn, 2)<>0 then
if mod(zemp_cursor%rowcount, 2)<>0 then
dbms_output.put_line( zemp_cursor%rowcount
--zemp_record.rn
||' ename is ' || zemp_record.ename
||' deptno is ' || zemp_record.deptno
||' sal is ' || zemp_record.sal) ;
end if ;
end loop;
end;
/
3、计算下⾯级数当末项⼩于0.001时的部分和。
1/(1*2)+1/(2*3)+1/(3*4)+…+1/(n*(n+1))+ ……
declare
n number :=1 ;
total number :=0 ;
begin
loop
total:=total+1/(n*(n+1)) ;
exit when (1/(n*(n+1)))<0.001 ;
--select sum(total+1/(n*(n+1))) into total from dual ;
n:=n+1;
end loop ;
dbms_output.put_line('The final sum is：'|| total) ;
end ;
/
4、计算s=1*2+2*3+…+N*(N+1),当N=50的值。
declare
n number :=1 ;
total number(10,3):=0 ;
begin
loop
total := total + n*(n+1) ;
exit when n=50 ;
--select sum(total+(1/((n+1)*(n+2)))) into total from dual ; n:=n+1;
end loop ;
dbms_output.put_line('The final sum is：'|| total) ;
end ;
/
5、两重循环，计算S=1!+2!+…+10!。
declare
a number:=0;
s number:=0;
begin
for i in 1..10 loop
a:=1;
for j in 1..i loop
a:=a*j;
end loop;
s:=s+a;
dbms_output.put_line(a);
end loop;
dbms_output.put_line('s='||to_char(s));
end;
/
6、编程序求满⾜不等式 1+3^2+5^2+…+N^2>2000的最⼩N值。
declare
n number :=1 ;
total number :=0 ;
begin
loop
total := total+power(n,2) ;
n:=n+2;
exit when total>2000 ;
end loop ;
dbms_output.put_line('The min N is：'|| n) ;
end ;
/
7、将雇员表中的所有⼯资⼩于3000增加400，统计出增加⼯资的⼈数及增加的⼯资数量。
declare
cursor sal_cursor is (select sal from zemp where sal<3000) for update of sal nowait ;
--锁定sal列，保证sal能够正常更新
n number ;
begin
select count(1) into n from zemp where sal<3000 ;
if n=0 then
RAISE_APPLICATION_ERROR(-20017, '数据不存在！') ;
else
for emp_record in sal_cursor loop
update zemp set sal=emp_record.sal+400 where current of sal_cursor ;
end loop ;
commit; --提交释放
end if ;
dbms_output.put_line('增加⼯资的⼈数：'|| n ||' 增加⼯资的⼈数：'|| 400*n) ;
exception
when others then
dbms_output.put_line('数据输⼊错误') ;
dbms_output.put_line('SQLCODE = '|| SQLCODE) ;
dbms_output.put_line('SQLERRM = '|| SQLERRM) ;
end;
/
8、将雇员表中的部门编号为30的所有员⼯删除，统计删除的⼈数及删除⼈的平均⼯资。
declare
v_avg number ;
v_count number ;
begin
select avg(sal),count(empno) into v_avg,v_count from zemp where deptno=30 ;
delete from zemp where deptno=30 ;
dbms_output.put_line('删除的⼈数：'|| v_count||' 删除⼈员的平均⼯资：'|| v_avg) ;
end ;
/
9、触发器的定义。
CREATE OR REPLACE TRIGGER TT
BEFORE DELETE OR UPDATE
OR INSERT ON zemp
BEGIN
DBMS_OUTPUT.PUT_LINE('触发器TT');
END;
/
调⽤：UPDATE zemp SET SAL=SAL-100 WHERE SAL>1000;
触发器TT16 rows updated
10、从雇员表中显⽰⼯资最⾼的前五个⼈的姓名，部门和⼯资。
declare
cursor zemp_cursor is select sal,ename,deptno from zemp order by sal desc,ename ;
i number :=0 ;
begin
dbms_output.put_line('最⾼⼯资的5个⼈：') ;
for zemp_xixi in zemp_cursor loop
exit when i=5;
dbms_output.put_line('姓名：'|| zemp_xixi.ename||' 部门：'|| zemp_xixi.deptno
||' ⼯资：'|| zemp_xixi.sal) ;
i:=i+1;
end loop ;
end;
/
11、编写过程：当给定的部门编号存在时，显⽰部门的信息。如果不存在则将该部门插⼊其中。
create or replace procedure show_insert_proc(v_deptno zdept.deptno%type)
as
v_count number ;
begin
select count(1) into v_count from zdept where deptno=v_deptno;
if v_count=0 then
dbms_output.put_line('不存在该部门！！');
insert into zdept(deptno, dname, loc) values(v_deptno,'资讯','昆⼭');
commit ;
dbms_output.put_line('插⼊成功！！');
else
for x in (select * from zdept where deptno=v_deptno) loop
dbms_output.put_line('部门编号：'||x.deptno
||' 部门名称：'||x.dname
||' 部门位置：'||x.loc) ;
end loop ;
end if ;
end ;
/
12、编写函数：对雇员表按⼯资从⼩到⼤排序后的第n条到第m条记录的⼯资总和。
