PL / SQL - 精确提取返回超过请求的行数

I am trying to create function that will return message with maximum salary for each job inside department and order by Maximum salary.

我正在尝试创建一个函数，它将为部门内的每个工作返回最大工资的消息，并按最大工资订单。

Message need to be:

消息需要是：

Department: Department name,

部门：部门名称，

Job/Position: Name of the job, Maximum salary: salary amount,

工作/职位：工作名称，最高工资：工资金额，

create or replace PACKAGE BODY Salary AS

FUNCTION max_sal(DEPTNO_F NUMBER)
RETURN VARCHAR2 IS  
dept_name VARCHAR2(25); 
job_possition VARCHAR(25);
maximum_salary NUMBER;
message VARCHAR2(255);

BEGIN



SELECT DNAME, JOB, MAX(SAL) AS "SAL" 
INTO  job_possition, maximum_salary
FROM EMP
WHERE DEPTNO = DEPTNO_F
GROUP BY JOB, DNAME
ORDER BY SAL DESC;


message := 'Department name: '||dept_name|| 'Job positin: '   ||job_possitin||, 'Maximum Salary: ' ||maximum_salary;

return message;



END max_sal;

END Salary;

2 个解决方案

#1

The problem is your query is returning one than one row and it can't select into your 2 variables. Have you tried running the SELECT SQL statement outside of the package and checked the rows you are getting back?

问题是您的查询返回一行而不能选择2个变量。您是否尝试在包外部运行SELECT SQL语句并检查要返回的行？

For any given DEPTNO_F value you should not have more than one permutation of DNAME and JOB

对于任何给定的DEPTNO_F值，您不应该有多个DNAME和JOB的排列

#2

To process several rows in query result you have to use loops:

要在查询结果中处理多行，您必须使用循环：

create or replace PACKAGE BODY Salary AS

FUNCTION max_sal(DEPTNO_F NUMBER)
RETURN VARCHAR2 IS  
dept_name VARCHAR2(25); 
job_possition VARCHAR(25);
maximum_salary NUMBER;
message VARCHAR2(255);

BEGIN


for i in (SELECT DNAME, JOB, MAX(SAL) SAL 
            FROM EMP
           WHERE DEPTNO = DEPTNO_F
           GROUP BY JOB, DNAME
           ORDER BY SAL DESC) loop

  message := message || 'Department name: ' || i.DNAME || ', Job position: ' ||
             i.JOB || ', Maximum Salary: ' || i.SAL || chr(10);

end loop;
return message;

END max_sal;

END Salary;

#1