function can return multiple value?how give give sample coding
Answer Posted / rahul poptani
Function returns only 1 value
BUT you can return multiple value Indireclty..
1) By returning an result set -
CREATE OR REPLACE FUNCTION GET_DEPT_INFO (P_DEPTNO IN NUMBER) RETURN SYS_REFCURSOR
AS
V_RC SYS_REFCURSOR;
BEGIN
OPEN V_RC FOR SELECT DEPTNO, DNAME, LOC FROM DEPT WHERE DEPTNO = P_DEPTNO;
RETURN V_RC;
END;
SQL> VAR RESULTSET REFCURSOR;
SQL> EXEC :RESULTSET := GET_DEPT_INFO(10)
PL/SQL procedure successfully completed.
SQL> PRINT :RESULTSET
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
2)PROVIDING OUT MODE PARAMETERS IN FUNCTION (RARELY USED)
SQL> CREATE OR REPLACE FUNCTION OUTMODE_EXAMPLE (P_DEPTNO NUMBER, P_DNAME OUT VARCHAR2)
2 RETURN NUMBER
3 AS
4 BEGIN
5 SELECT DNAME INTO P_DNAME FROM DEPT WHERE DEPTNO = P_DEPTNO;
6 RETURN NULL;
7 END;
8 /
Function created.
SQL> DECLARE
2 V_DNAME DEPT.DNAME%TYPE;
3 V NUMBER;
4 BEGIN
5 V := OUTMODE_EXAMPLE(10,V_DNAME);
6 DBMS_OUTPUT.PUT_LINE(V_DNAME);
7 END;
8 /
ACCOUNTING
PL/SQL procedure successfully completed.
Is This Answer Correct ? | 6 Yes | 0 No |
Post New Answer View All Answers
Can we write dml inside a function in sql server?
Can you do multiple joins in sql?
Lookups are a key component in sql server integration services (ssis). Explain its purpose?
What is right join in sql?
Why coalesce is used in sql?
how is exception handling handled in mysql? : Sql dba
Show code of a cursor for loop.
How to add, remove, modify users using sql?
How to avoid duplicate records in a query?
How do you declare a variable in pl sql?
What is multiple partition?
What is clustered index sql?
How many columns should be in an index?
How to convert comma separated string to array in pl/sql?
What is type and rowtype in pl sql?