唐磊的个人博客

大型数据库实验(三)

接着看第三个题目吧。题目描述如下:

————————————————————————————-

使用游标,利用数组对EMP进行批检索(员工号、姓名、工作、工资和奖金,每次提取5行),并用print_rows(自定义过程)打印结果。

————————————————————————————-

这里说的emp就是scott/tiger下面的数据吧。我们还是别乱用最原始的数据,自己把scott中emp数据copy一份出来。用CREATE TABLE emp AS SELECT * FROM scott.EMP;来copy。注意登陆的用户要有使用scott中数据的权限。例如给个dba角色。咋看一下这个题目没怎么明白意思。揣测了下赵明老师的意思,应该要用到的知识点有:cursor,bulk collect 还要自定义存储过程。至于bulk collect有啥好处,我这里也不说了,网上一大堆,大概就是提高效率啊之类的。原理呢。网上也有。这里摘一段他的课件上的东西

In the context of bulk querying, set-at-a-time row retrieval means fetching the required rows in sets instead of fetching all rows at once.

This amounts to limiting the number of rows fetched at a time—that is, in a single fetch—instead of fetching all the rows in a single fetch.

This may result in more context switches than when all the rows are fetched in a single fetch but is optimal when the limiting value is correctly specified.

An accurate limiting value results in both performance and memory benefits.

然后课件上有个大概框架如下:

DECLARE
t_outtab_type IS TABLE OF input_table%ROWTYPE;
v_outtab t_outtab_type;
CURSOR c_in IS SELECT * FROM input_table WHERE key_col = '<val1>';
BEGIN
OPEN c_in;
LOOP
FETCH c_in BULK COLLECT INTO v_outtab LIMIT 100;
FOR i IN 1..v_outtab.COUNT LOOP
-- Process the rows one by one according to required logic.
NULL;
END LOOP;
EXIT WHEN c_in%NOTFOUND;
END LOOP;
CLOSE c_in;
END;
/

就仿造例子写一个吧。实现这个题目意思的存储过程如下所示:

create or replace procedure fetchPer5fromEmp
as
begin
declare
type emp_array is table of emp%rowtype;
emp_list emp_array:=emp_array();
cursor cur_emp is
select * from emp;
fifth number:=1;--记录第几个五行记录
begin
open cur_emp;
loop
fetch cur_emp bulk collect into emp_list limit 5;--限制取出个数
DBMS_OUTPUT.put_line('--------------第'||fifth||'个五行记录---------');
for i in emp_list.first..emp_list.last loop
print_rows(emp_list(i));--调用自定义过程,传入数字里的一个值
end loop;
fifth:=fifth+1;
exit when cur_emp%notfound;--注意这句话,比如这个题目中只有14条记录,当循环2次后,第三次如果没有这条语句就会报错啦。刚开始我也没加,后来单步调试才知道这里少了这句话。
end loop;
DBMS_OUTPUT.put_line('总过得到记录总数为:'||cur_emp%rowcount);--cowcount是去当前游标的记录数,这里当然就是最后游标里面的总数量了。
close cur_emp;
end;
end;
/

————————————————————————————-实现打印的存储过程如下:

create or replace procedure print_rows(e emp%rowtype)
as
begin
DBMS_OUTPUT.PUT_LINE(e.empno || '--' ||e.ename || '--' || e.job || '--' || e.sal || '--' || e.comm );
end;
/

实现效果如下图所示:

注意limit的使用。

整个实验就完了,oracle就3个题目。由于自己以前学过一段时间的oracle,可以说这次实验既是对以前的复习,也是重新学习plsql编程。才发现自己以前学的忘了差不多了。不过总的说来还是满有收获滴。也沿用了以前的一些小技巧。例如将sql写在文本文件当中,在sqlplus中直接通过@命令导入执行,可以提高效率。再写存储过程啊之类的plsql代码的时候还是容易忘把serveroutput打开,dbms_output.putline调用了没有反映。仍然有粗心的地方。就比如dbms_output.putline写成了dbms_output_putline还始终找不到出了啥错误,到处google也好百度也罢,没搞定。最后才发现是点用了下划线。以前存储过程、触发器有些印象,不过像bulk collect之类的还是没有用过,这对自己来说又是提高了眼界了吧。在写存储过程时,还是得依靠像plsql-devloper这样的工具帮忙,虽然赵老师说科班出身不依赖工具,但像这种存储过程借助那样的工具实在是要方便得多,单步调试找错要方便些。不过自己还是得注意不要太依赖与工具。以后的路还长啊,仍需不断努力,给自己加油吧。

tanglei wechat
欢迎扫描二维码关注我的微信公众号