Can a Function return more than one value ?

This is a popular interview question that I have been asked ( & I have asked in turn ) from time immemorial.

A Function can return more than one value in PL/SQL.

We can do it in two ways :-

1. Use OUT Parameters in the Function :-

CREATE OR REPLACE FUNCTION FN_BAD ( RETVAL OUT NUMBER )
RETURN INTEGER
AS
BEGIN
RETVAL:= 200;
RETURN 100;
END;
/

SELECT FN_BAD FROM DUAL;

ORA-06553: PLS-306: wrong number or types of arguments in call to 'FN_BAD'

SET SERVEROUTPUT ON;

DECLARE
ln_BAD NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(FN_BAD(ln_BAD));
DBMS_OUTPUT.PUT_LINE(ln_BAD);
END;
/


100
200

PL/SQL procedure successfully completed.

Well, this is bad coding....but it works ! I just tried it for fun :)

2. Use a PipleLined Function. A Piplined Function needs to have an assosicated Collection Type.

How to append '0' to single Digit Numbers

DROP TABLE AX;

CREATE TABLE AX ( COL NUMBER );

INSERT INTO AX ( SELECT ROWNUM FROM ALL_OBJECTS WHERE ROWNUM < 11 );

SELECT TO_CHAR(COL,'00') FROM AX;

TO_
---
01
02
03
04
05
06
07
08
09
10

9 rows selected.

SQL> SELECT TO_NUMBER(TO_CHAR(COL,'00')) NUM FROM AX;

NUM
----------
1
2
3
4
5
6
7
8
9

9 rows selected.

A Function that returns a Table

This is an Example of a Pipelined function that returns a Table of Results :-


DROP TYPE TYP_NAME_AND_AGES;

DROP TYPE OBJ_NAME_AND_AGES

DROP FUNCTION FN_NAME_AND_AGES;

DROP TABLE TBL_NAME_AND_AGES;

CREATE TABLE TBL_NAME_AND_AGES
(
NAME VARCHAR2(10),
AGE NUMBER
);

INSERT INTO TBL_NAME_AND_AGES VALUES ('SANDEEP',20);
INSERT INTO TBL_NAME_AND_AGES VALUES ('SCOTT',21);
INSERT INTO TBL_NAME_AND_AGES VALUES ('TIGER',22);

COMMIT;

CREATE OR REPLACE TYPE TYP_NAME_AND_AGES AS OBJECT
(
NAME VARCHAR2(10),
AGE NUMBER
);

CREATE OR REPLACE TYPE OBJ_NAME_AND_AGES AS TABLE OF TYP_NAME_AND_AGES;

CREATE OR REPLACE FUNCTION FN_NAME_AND_AGES
RETURN OBJ_NAME_AND_AGES
PIPELINED
AS
BEGIN

FOR rec IN ( SELECT * FROM TBL_NAME_AND_AGES )
LOOP
PIPE ROW ( TYP_NAME_AND_AGES( rec.NAME, rec.AGE ) );
END LOOP;

END FN_NAME_AND_AGES;

SELECT * FROM TABLE(FN_NAME_AND_AGES);

NAME AGE
---------- ----------
SANDEEP 20
SCOTT 21
TIGER 22