带大小写的SQL语句

Question

我想要一个接收salary值和job_id的函数。此函数需要验证作为参数传递的salary值是否介于最大和最小salary之间。

CREATE FUNCTION check(revenue Number, id integer) 
RETURN message 
IS message varchar2;
BEGIN  
select min(revenue), max(revenue) from  users;

RETURN(message);
END;

Answer 1

除非message是一个数据类型(可能不是(，否则不能返回它。您要返回的是字符串-varcahr2数据类型。

因此：

SQL> create or replace function f_test (salary in number)
2    return varchar2
3  is
4    l_min number;
5    l_max number;
6  begin
7    select min(sal), max(sal)
8      into l_min, l_max
9      from emp;
10
11    return case when salary between l_min and l_max then 'Between'
12                when salary > l_max then 'Above max'
13                else 'Other'
14           end;
15  end;
16  /
Function created.
SQL> select f_test(6000) result from dual;
RESULT
-------------------------------------------------------------------------------
Above max
SQL>

请随意改进(通过增加工作、部门等等(。

Answer 2

您可以通过使用分析函数创建一个函数，而无需返回极值的局部变量作为选项：

CREATE OR REPLACE FUNCTION verifyIfSalaryIsBetweenMinAndMaxForThatJob(i_salary NUMBER,
i_jobId  INT)
RETURN VARCHAR2 IS
message VARCHAR2(50);
BEGIN
SELECT MAX(CASE WHEN i_salary < min_sal THEN 
'Below' 
WHEN i_salary > max_sal THEN 
'Above Max'     
WHEN i_salary BETWEEN min_sal AND max_sal THEN 
'Between'                                 
END) 
INTO message
FROM
(
SELECT MIN(salary) OVER (PARTITION BY job_Id) AS min_sal,
MAX(salary) OVER (PARTITION BY job_Id) AS max_sal,
e.*
FROM employees e
)
WHERE job_id = i_jobId; 
RETURN message;
END;
/

演示