CODEWITHSUNDEEP
sqloracle-databasereturn
chou
chou

Reputation: 263

SQL function return many fields

I am writing an SQL function, which is returning the result of three fields of select, How should I return the three detected values?

CREATE OR REPLACE FUNCTION ATK_SMSPHONE(POLICY IN VARCHAR2, INSUREDNUMBER IN NUMBER) 
RETURN VARCHAR2 
IS
VPHONE VARCHAR2(100);
FIRSTNAME VARCHAR2(30);
LASTNAME VARCHAR2(30);
BEGIN
SELECT ADRESSE_ADHERANT, PRENOM_ADHERANT, NOM_ADHERANT INTO VPHONE, FIRSTNAME, LASTNAME
FROM ADHERANT WHERE POLICE = POLICY
AND NUMERO_ADHERANT = INSUREDNUMBER
AND ROLE_MEMBRE = 0;
RETURN VPHONE,FIRSTNAME,LASTNAME;
EXCEPTION WHEN OTHERS THEN 
RETURN ''; 
 END; 
 /

I have tried many options but in vain, sorry but i forgot how to use SQL :/

Upvotes: 0

Views: 70

Answers (1)

Aleksej
Aleksej

Reputation: 22959

This can be a way:

create or replace type threeValues as object ( VPHONE VARCHAR2(100),
                                               FIRSTNAME VARCHAR2(30),
                                               LASTNAME VARCHAR2(30)
                                             )
/
CREATE OR REPLACE FUNCTION ATK_SMSPHONE(POLICY IN VARCHAR2, INSUREDNUMBER IN NUMBER)
    RETURN threeValues IS
    retVal                                  threeValues;
BEGIN
    SELECT threeValues(
                       '99',
                       'aa',
                       'bb'
                      )
      INTO retVal
      FROM DUAL;
    return retVal;
EXCEPTION
    WHEN OTHERS
    THEN
        RETURN null;
END;
/

The call:

SQL> select ATK_SMSPHONE('', '').VPHONE from dual;

ATK_SMSPHONE('','').VPHONE
-----------------------------------------------------------------------------
99

SQL> select ATK_SMSPHONE('', '') from dual;

ATK_SMSPHONE('','')(VPHONE, FIRSTNAME, LASTNAME)
-----------------------------------------------------------------------------
THREEVALUES('99', 'aa', 'bb')

Upvotes: 1

Related Questions