存在一个雇员 employee 表, 创建该表的语句如下:
CREATE TABLE employee(emp_id NUMBER(3), emp_name VARCHAR2(20), salary NUMBER(10,2));
该表的查询结果如下:
select emp_id, emp_name, salary from employee;
emp_id emp_name salary
-------- ------------------------------------------------------------------------------------
1 关羽 4632
2 张飞 4632
3 刘备 4932
4 赵云 5832
5 黄忠 4932
6 马超 5832
7 孔明 6632
8 姜维 4932
9 吕布 6432
请创建一个PL/SQL 存储过程,它确定employee表中薪水排名在前5个名次的雇员,输出显示这些雇员的名次,姓名,薪水。如果有雇员薪水相同,它们显示在同一行,用分号分隔; 如果某个名次的雇员为n(n大于等于2)个, 则下n-1个名次的雇员空缺。 显示结果示例如下:
1.孔明, 6632
2.吕布, 6432
3.赵云, 5832; 马超, 5832
4.
5.刘备, 4932; 黄忠, 4932; 姜维, 4932这个需求用PLSQL怎么写呢?
CREATE TABLE employee(emp_id NUMBER(3), emp_name VARCHAR2(20), salary NUMBER(10,2));
该表的查询结果如下:
select emp_id, emp_name, salary from employee;
emp_id emp_name salary
-------- ------------------------------------------------------------------------------------
1 关羽 4632
2 张飞 4632
3 刘备 4932
4 赵云 5832
5 黄忠 4932
6 马超 5832
7 孔明 6632
8 姜维 4932
9 吕布 6432
请创建一个PL/SQL 存储过程,它确定employee表中薪水排名在前5个名次的雇员,输出显示这些雇员的名次,姓名,薪水。如果有雇员薪水相同,它们显示在同一行,用分号分隔; 如果某个名次的雇员为n(n大于等于2)个, 则下n-1个名次的雇员空缺。 显示结果示例如下:
1.孔明, 6632
2.吕布, 6432
3.赵云, 5832; 马超, 5832
4.
5.刘备, 4932; 黄忠, 4932; 姜维, 4932这个需求用PLSQL怎么写呢?
解决方案 »
- Oracle 赋权的问题
- SQL性能优化(hint)
- 给表增加一个记录,默认值是字符1,如何写
- 用java开发软件,可是就是连接不上安装在本机内的oracle数据库,总是抛出异常,请详阅!!!!急急急急!!!!!!
- 怎样修改我的SQL语句
- 求1条SQL 语句 再开一贴 求ORACLE写法
- 关于Sql Developer连接本地数据库问题???
- 各位:卸载oracle,重新安装卸载,如何删除原来的数据库sid
- 怎样才能在oracle9i用SQL语句自动生成数据表??请指导。。。。
- 高分求救!!!!!
- 插入数据的问题
- java.sql.SQLException: ORA-01000: maximum open cursors exceeded
如果10g以下,则需要用sys_connect_by_path实现其功能。
from (select emp_id, emp_name,
salary,
rank() over(order by salary desc NULLS LAST) rank
from employee
group by emp_id, emp_name)
where rank <= 5;
SELECT id, REPLACE(wmsys.wm_concat(col), ',', ';') str
FROM
(
select empno||','||sal as col ,rn from
(
select empno,sal,dense_rank() over( order by sal desc) as rn from emp
) where rn<6
)
GROUP BY rn;
SELECT id, MAX(substr(sys_connect_by_path(col, ';'), 2)) str
FROM (
select id,ename||','||sal as col ,row_number()over(PARTITION BY id ORDER BY ename) rn from
(
select ename,sal,dense_rank() over( order by sal desc) as id from emp
)
where id<6
)
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id
GROUP BY id;
结果:
ID STR
1 杨,6080;杨平,6080
2 KING,5000
3 FORD ,3000;SCOTT,3000;YA_PING,3000
4 JONES,2975
5 BLAKE,2850
insert into employee values(1,'关羽',4632);
insert into employee values(2,'张飞',4632);
insert into employee values(3,'刘备',4932);
insert into employee values(4,'赵云',5832);
insert into employee values(5,'黄忠',4932);
insert into employee values(6,'马超',5832);
insert into employee values(7,'孔明',6632);
insert into employee values(8,'姜维',4932);
insert into employee values(9,'吕布',6432);SELECT rn, REPLACE(REPLACE(wmsys.wm_concat(col), ',', ';'),'.',',') str
FROM
(
select emp_name||'.'||salary as col ,rn from
(
select emp_name,salary,dense_rank() over( order by salary desc) as rn from employee
) where rn<6
)
GROUP BY rn;
FROM
(
select emp_name||'.'||salary as col ,rn from
(
select emp_name,salary,dense_rank() over( order by salary desc) as rn from employee
) where rn <6
)
GROUP BY rn;
select 1 emp_id,'关羽' emp_name,4632 salary from dual
union all
select 2 emp_id,'张飞' emp_name,4632 salary from dual
union all
select 3 emp_id,'刘备' emp_name,4932 salary from dual
union all
select 4 emp_id,'赵云' emp_name,5832 salary from dual
union all
select 5 emp_id,'黄忠' emp_name,4932 salary from dual
union all
select 6 emp_id,'马超' emp_name,5832 salary from dual
union all
select 7 emp_id,'孔明' emp_name,6632 salary from dual
union all
select 8 emp_id,'姜维' emp_name,4932 salary from dual
union all
select 9 emp_id,'吕布' emp_name,6432 salary from dual
)
create or replace procedure getresult() is
begin
select REPLACE(wmsys.wm_concat(ls), ',', ';'),rn from(
select emp_name ||','||salary ls,rn from(
select emp_id,emp_name,salary,rank() over(order by salary desc) rn from employee
)
)group by rn
end getresult;
楼主别人都已经告诉你sql语句了啊
你强调要用plsql
是要把sql 语句查出的结果用游标接受 然后再用dbms_output.put打印出来么
declare
type emp_rd is record (num number,en varchar2(256));
type arr is varray(100) of emp_rd;
a arr := arr(null);
m arr := arr(null);
v_count number;
v_flag number :=0;
n number := 1;
c number :=1;
x number :=1;
begin
select count(*) into v_count from employee;
a.extend(v_count);
m.extend(v_count);
select rn,wm_concat(emp_name||','||salary) en bulk collect into a
from (
select emp_id,emp_name,salary,
dense_rank()over(order by salary desc) rn
from employee
)
group by rn;
for b in 1..a.count loop
if instr(a(b).en,',',1,2) = 0 then
m(x).num := x;
m(x).en := a(b).en;
x := x+1;
else
v_flag := 0;
n := 1;
while instr(a(b).en,',',1,n) <> 0 loop
if instr(a(b).en,',',1,n) <> 0 then
v_flag := v_flag +1;
n := n+1;
end if;
end loop;
m(x).num := x;
m(x).en := a(b).en;
x := x+1;
for c in 1..trunc(v_flag/2) loop
m(x+c-1).num := x+c-1;
m(x+c-1).en := null;
end loop;
x := x+trunc(v_flag/2);
end if;
end loop;
dbms_output.put_line('--------------------------------');
for e in 1..m.count loop
dbms_output.put_line('m('||e||').num='||m(e).num||'**m('||e||').en='||m(e).en);
end loop;
end;
declare
type emp_rd is record (num number,en varchar2(256));
type arr is varray(100) of emp_rd;
a arr := arr(null);
m arr := arr(null);
v_count number;
v_flag number :=0;
n number := 1;
c number :=1;
x number :=1;
begin
select count(*) into v_count from employee;
a.extend(v_count);
m.extend(v_count);
select rn,wm_concat(emp_name||','||salary) en bulk collect into a
from (
select emp_id,emp_name,salary,
dense_rank()over(order by salary desc) rn
from employee
)
group by rn;
for b in 1..a.count loop
if instr(a(b).en,',',1,2) = 0 then
m(x).num := x;
m(x).en := a(b).en;
x := x+1;
else
v_flag := 0;
n := 1;
while instr(a(b).en,',',1,n) <> 0 loop
if instr(a(b).en,',',1,n) <> 0 then
v_flag := v_flag +1;
n := n+1;
end if;
end loop;
m(x).num := x;
m(x).en := a(b).en;
x := x+1;
for c in 1..trunc(v_flag/2) loop
m(x+c-1).num := x+c-1;
m(x+c-1).en := null;
end loop;
x := x+trunc(v_flag/2);
end if;
end loop;
dbms_output.put_line('--------------------------------');
for e in 1..m.count loop
dbms_output.put_line(m(e).num||' '||m(e).en);
--dbms_output.put_line('m('||e||').num='||m(e).num||'**m('||e||').en='||m(e).en);
end loop;
end;
1 孔明,6632
2 吕布,6432
3 马超,5832,赵云,5832
4
5 刘备,4932,黄忠,4932,姜维,4932
6
7
8 关羽,4632,张飞,4632
9