如何在plsql中更新此功能? [英] How can I update this function in plsql?

问题描述

此功能检查所有员工的薪水是否在最高和最低薪水的正确范围内.我想对其进行更新，以使其返回工资超出范围的员工ID? 我尝试定义了一个集合，但是它总是给我错误.有什么想法吗?

this function check if all employees have their salary between the correct range of maximum and minimum salary. I want to update it so it would return the employees' ids whose salary is out of range ?? I tried and define a collection but it keeps giving me error . Any Ideas ??

create or replace function min_max return number as
cursor job_cur is
    select job_id, min_salary, max_salary from jobs;
cursor emp_cur(p_job_id jobs.job_id%type) is
    select employee_id, salary from employees where job_id = p_job_id;
Type id_table is table of number index by binary_integer;
return_id id_table;
i number := 1;
begin
for job_rec in job_cur loop
    for emp_rec in emp_cur(job_rec.job_id) loop
        if (emp_rec.salary > job_rec.max_salary)
            or (emp_rec.salary < job_rec.min_salary) then
            --return 0;
            return_id(i).emlpoyee_id := emp_rec.employee_id; // error here
            i := i+1;
        end if;
    end loop;
end loop;
--return 1;
return return_id;  // error here
end min_max;

这是更新的代码，但是不起作用.错误: 错误(15,17):PL/SQL:语句被忽略. 错误(15,30):PLS-00487:对变量"NUMBER"的无效引用. 错误(21,5):PL/SQL:语句被忽略. 错误(21,12):PLS-00382:表达式的类型错误.

Here is the updated code but it's not working . The errors : Error(15,17): PL/SQL: Statement ignored. Error(15,30): PLS-00487: Invalid reference to variable 'NUMBER'. Error(21,5): PL/SQL: Statement ignored. Error(21,12): PLS-00382: expression is of wrong type.

推荐答案

如果在函数外部定义类型，则该函数可以返回一个集合.同样，不要循环两个游标.如果可以，请始终将SQL操作作为一组操作进行.这样您将获得更好的性能.

If you define the type outside the function, the function can return a collection. Also, do not loop over two cursors. Always do SQL operations as a set if you can. You get vastly better performance that way.

只要雇员人数不太多，此方法就可行:

This works, as long as the number of employees is not too large:

CREATE TYPE id_table AS TABLE OF NUMBER;

CREATE OR REPLACE FUNCTION min_max RETURN id_table AS
  l_emp_list id_table;
BEGIN
  SELECT e.employee_id 
    BULK COLLECT INTO l_emp_list
    FROM employees e 
   INNER JOIN jobs j ON j.job_id = e.job_id
   WHERE e.salary NOT BETWEEN j.min_salary AND j.max_salary;

  RETURN l_emp_list;
END;

由于某种原因，SQLFiddle给我一个错误，但这在我的数据库中有效.

For some reason SQLFiddle is giving me an error, but this works in my database.

这篇关于如何在plsql中更新此功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！