用PL/SQL实现了多个员工的显示，如下：declare
---查询部门的人员
v_deptnumber;
v_full_name varchar2(4000);
v_namevarchar2(20);
cursor cur_dept is
select distinct dept from emp where dept is not null order by 1;
cursor cur_name is
select emp_name from emp where dept = v_dept order by 1;
cursor cur_name_n is
select emp_name from emp where dept is null order by 1;
begin
open cur_dept;
loop
fetch cur_dept
into v_dept;
exit when cur_dept%notfound;
open cur_name;
loop
fetch cur_name
into v_name;
exit when cur_name%notfound;
v_full_name := trim(rtrim(v_full_name) || chr(9) || trim(v_name));
end loop;
close cur_name;
dbms_output.put_line(v_dept || ': ' || v_full_name);
v_full_name := '';
end loop;
close cur_dept;
open cur_name_n;
v_full_name := '';
loop
fetch cur_name_n
into v_name;
exit when cur_name_n%notfound;
v_full_name := trim(rtrim(v_full_name) || chr(9) || trim(v_name));
end loop;
dbms_output.put_line('NULL: ' || v_full_name);
close cur_name_n;
end;
/复制代码如下是执行结果：
SQL> create table emp (dept number,emp_name varchar2(20));
insert into emp values (10,'aaa');
Table created.
SQL>
1 row created.
SQL> insert into emp values (10,'bbb');
1 row created.
SQL> insert into emp values (10,'ccc');
1 row created.
SQL> insert into emp values (20,'dddd');
1 row created.
SQL> insert into emp values (20,'eeee');
1 row created.
SQL> insert into emp values (20,'ffff');
1 row created.
SQL> insert into emp values (30,'ggggg');
1 row created.
SQL> insert into emp values (40,'hhhhh');
1 row created.
SQL> insert into emp (emp_name) values ('ii');
1 row created.
SQL> insert into emp (emp_name) values ('jj');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from emp;
DEPT EMP_NAME
---------- ----------------------------------------
10 aaa
10 bbb
10 ccc
20 dddd
20 eeee
20 ffff
30 ggggg
40 hhhhh
ii
jj
10 rows selected.
SQL> set serveroutput on
SQL> declare
2---查询同一个部门的人员
3v_deptnumber;
4v_full_name varchar2(4000);
5v_namevarchar2(20);
6cursor cur_dept is
7select distinct dept from emp where dept is not null order by 1;
8cursor cur_name is
9select emp_name from emp where dept = v_dept order by 1;
10cursor cur_name_n is
11select emp_name from emp where dept is null order by 1;
12begin
13open cur_dept;
14loop
15fetch cur_dept
16into v_dept;
17exit when cur_dept%notfound;
18open cur_name;
19loop
20fetch cur_name
21
into v_name;
22exit when cur_name%notfound;
23v_full_name := trim(rtrim(v_full_name) || chr(9) || trim(v_name));
24end loop;
25close cur_name;
26dbms_output.put_line(v_dept || ': ' || v_full_name);
27v_full_name := '';
28end loop;
29close cur_dept;
30open cur_name_n;
31v_full_name := '';
32loop
33fetch cur_name_n
34into v_name;
35exit when cur_name_n%notfound;
36v_full_name := trim(rtrim(v_full_name) || chr(9) || trim(v_name));
37end loop;
38dbms_output.put_line('NULL: ' || v_full_name);
39close cur_name_n;
40end;
41/
10: aaa bbb ccc
20: ddddeeeeffff
30: ggggg
40: hhhhh
NULL: iijj
PL/SQL procedure successfully completed. |
