CODEWITHSUNDEEP
sqloracle-databaseplsql
Sasuke1312
Sasuke1312

Reputation: 23

pl/sql a function that returns a table

CREATE TYPE obj AS OBJECT (
    id     NUMBER,
    salary NUMBER
);
/
CREATE TYPE tabl IS TABLE OF obj;
/
CREATE OR REPLACE FUNCTION f (v_name1 employee.last_name%TYPE DEFAULT 'Robinson', v_name2 employee.last_name%TYPE DEFAULT 'Lee', v_name3 employee.last_name%TYPE DEFAULT 'Hill') RETURN tabl AS
    l_tabl tabl := tabl();
    salariy1 employee.salary%type;
    salariy2 employee.salary%type;
    salariy3 employee.salary%type;
BEGIN
    SELECT salary 
    INTO salary1 
    FROM employee 
    WHERE last_name = v_name1;
    
    SELECT salary 
    INTO salary2 
    FROM employee 
    WHERE last_name = v_name2;
    
    SELECT salary 
    INTO salary3 
    FROM employee 
    WHERE last_name = v_name3; 
    
    l_tab.extend;
    l_tab(l_tab.last) := obj(1, salary1);
    
    l_tab.extend;
    l_tab(l_tab.last) := obj(2, salary2);
    
    l_tab.extend;
    l_tab(l_tab.last) := obj(3, salary3);
    
    RETURN l_tab;
END;
/
SELECT *
FROM TABLE(f);

Hi! I want to create a function that takes some names and returns a table with their salaries. This is what I did, how can I make this works? I'm rookie and i need help.

Upvotes: 1

Views: 139

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

You can prefer creating your function like this assuming those last names are unique throughout the table

CREATE OR REPLACE FUNCTION 
                  fn_get_salaries (
                                   v_name1 employee.last_name%TYPE DEFAULT 'Robinson',
                                   v_name2 employee.last_name%TYPE DEFAULT 'Lee',
                                   v_name3 employee.last_name%TYPE DEFAULT 'Hill'
                                  ) 
           RETURN tabl AS

    l_tabl   tabl := tabl();
    
    CURSOR crs_emp IS 
    SELECT *
      FROM employee e
     WHERE last_name IN (v_name1,v_name2,v_name3);

BEGIN       
    FOR c IN crs_emp 
    LOOP
     l_tabl.extend;
     l_tabl(l_tabl.last) := obj(c.id, c.salary);      
    END LOOP;

    RETURN l_tabl;
END;
/

where

  • the more meaningful name should be picked for the function
  • using a cursor would suit better for your case as having an iterative structure

Demo

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59557

Your function is fine, however this would be an optimized version:

CREATE OR REPLACE FUNCTION f (v_name1 employee.last_name%TYPE DEFAULT 'Robinson', v_name2 employee.last_name%TYPE DEFAULT 'Lee', v_name3 employee.last_name%TYPE DEFAULT 'Hill') RETURN tabl AS
    l_tabl tabl := tabl();
BEGIN
    SELECT obj(ROWNUM, salary) 
    BULK COLLECT INTO l_tabl 
    FROM employee 
    WHERE last_name IN (v_name1, v_name2, v_name3)
    ORDER BY case last_name  
      WHEN v_name1 THEN 1
      WHEN v_name2 THEN 2
      WHEN v_name3 THEN 3
      END;
    
    RETURN l_tab;
END;
/

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

When compiling a package, check for errors. Most tools (such as Oracle's free tool SQL Developer) tell you the error right away. Otherwise

select * from user_errors;

This would show you your typos

  • salariy1 instead of salary1
  • salariy2 instead of salary2
  • salariy3 instead of salary3
  • l_tab instead of l_tabl

Correct the typos and your function runs just fine.

Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=932f0d92745ed0019c33b9b10f868eab

Upvotes: 2

Related Questions