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
how can we submit a form without a submit button? : Sql dba
Why is %isopen always false for an implicit cursor?
How do I install sql?
What are different clauses used in sql?
What is rank dense_rank and partition in sql?
What are the rules to be applied to nulls whilst doing comparisons?
What is the purpose of the partition table?
What is a trigger word?
Why use stored procedures?
can a stored procedure call itself or recursive stored procedure? How much level sp nesting is possible? : Sql dba
Why are cursors used?
How do you explain an index number?
How to sort the rows in sql.
what are the performance and scalability characteristics of mysql? : Sql dba
Which query operators in sql is used for pattern matching?