By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE employees (first_name, last_name, phone_number, salary) AS
SELECT 'Alice', 'Abbot', '0123456', 100000 FROM DUAL UNION ALL
SELECT 'Betty', 'Baron', '1111111', 250000 FROM DUAL UNION ALL
SELECT 'Carol', 'Chris', '9876543', 300000 FROM DUAL;
3 rows affected
create type emp_high_sal_ot is object
(
full_name varchar2(64),
phone_number varchar(20),
salary number(10,2)
);
create type emp_high_sal_nt is table of emp_high_sal_ot;
create or replace function get_highest_paid_emps return emp_high_sal_nt
AS
rec emp_high_sal_nt;
avg_sal NUMBER;
begin
SELECT AVG(salary)
INTO avg_sal
FROM employees;
SELECT emp_high_sal_ot(
last_name || ' ' || first_name,
phone_number,
salary
)
BULK COLLECT INTO rec
FROM employees
WHERE salary > avg_sal;
return rec;
end;
/
SELECT * FROM TABLE(get_highest_paid_emps());
| FULL_NAME | PHONE_NUMBER | SALARY |
|---|---|---|
| Baron Betty | 1111111 | 250000 |
| Chris Carol | 9876543 | 300000 |