Function Returning Multiple Values SQL

Question

I have been trying to return 2 values from a function in PL/SQL . The first value i want it to be the salary of the guy i have to search for. The second i want it to be the number of rows affected by this. I searched google for a while and i found out that i must first make a type so that i can return the data. However i get an error :

Error(9,1): PL/SQL: SQL Statement ignored
Error(9,36): PL/SQL: ORA-00936: missing expression

The code that i have is :

CREATE OR REPLACE TYPE return_type AS OBJECT(val1 NUMBER,val2 NUMBER);


CREATE OR REPLACE FUNCTION f2
(v_nume employees.last_name%TYPE DEFAULT 'Bell')
RETURN return_type IS

out_var return_type;
salariu employees.salary%type;

BEGIN

SELECT salary INTO salariu
FROM employees
WHERE last_name = v_nume;

INSERT INTO out_var values(salariu,@@ROWCOUNT);

RETURN out_var;

EXCEPTION

WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, 'Nu exista angajati cu numele dat');
WHEN TOO_MANY_ROWS THEN
RAISE_APPLICATION_ERROR(-20001, 'Exista mai multi angajati cu numele dat');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002,'Alta eroare!');
END f2;
/

Replace also the INSERT INTO out_var values(salariu,@@ROWCOUNT); with the assignment statements out_var.val1 := salariu; out_var.val1 := 1;, you can't insert values into object, in case of pl/sql — mikron
– mikron, Commented Nov 28, 2014 at 12:35

Lucian Tarna · Accepted Answer · 2014-11-28 12:39:09Z

1

I did it this way:

CREATE OR REPLACE FUNCTION f2
(v_nume employees.last_name%TYPE DEFAULT 'Bell',
 nr OUT employees.salary%TYPE )
RETURN NUMBER IS

salariu employees.salary%type;

BEGIN

SELECT salary INTO salariu
FROM employees
WHERE last_name = v_nume;
nr := SQL%ROWCOUNT;
RETURN salariu;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000,
'Nu exista angajati cu numele dat');
WHEN TOO_MANY_ROWS THEN
RAISE_APPLICATION_ERROR(-20001,
'Exista mai multi angajati cu numele dat');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002,'Alta eroare!');
END f2;
/

answered Nov 28, 2014 at 12:39

Lucian Tarna

1,8394 gold badges27 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Glenn Over a year ago

nr is meaningless in this scenario: it will always be on1. Any other case results in an exception thrown (no data found, or too many rows).

Lucian Tarna Over a year ago

yes, i do realise that :D however this function will be further developed and nr will have a role there

Lucian Tarna Over a year ago

thank you very much for both your interest and your good wishes :P

Collectives™ on Stack Overflow

Function Returning Multiple Values SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related