function can return multiple value?how give give sample coding
Answers were Sorted based on User's Feedback
Answer / manikanta.srinu
Function can't return multiple value.
There are 3 types of functions are in sql server.
1.Scalar valued function:This function can return only one
value.
2.Inline table-valued functions:return a single table
variable that was created by a select statement.
3.Multitable valed function
:Returns a table variable whose structure was created by
hand, similar to a Create Table statement. It is useful
when complex data manipulation inside the function is
required.
1). Scalar Function
create function scalarvalue()
returns int
as
begin
return 1
end
Go
Scalar functions are usually deemed a performance hit,
because they are executed on a per-row basis.
Calling Function
select dbo.scalarvalue()
Note: We can create @temp variable in sclar function.
2). Inline table-valued functions
create function inlinetable()
returns table
as
RETURN SELECT a.SaleId, a.CustomerID, b.Qty
FROM Sales.Sales a INNER JOIN Sales.SaleDetail b
ON a.SaleId = b.SaleId
INNER JOIN Production.Product c ON b.ProductID =
c.ProductID
WHERE a.ShipDate IS NULL
go
Calling Function
select * from dbo.fun1()
Note: We can’t create @temp variable in inline table
valued function.
2). Multi-statement table-valued functions
create function fun2()
RETURNS @CustomerOrder TABLE
(SaleOrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL,
OrderQty INT NOT NULL)
AS
BEGIN
DECLARE @MaxDate DATETIME
SELECT @MaxDate = MAX(OrderDate)
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID
INSERT @CustomerOrder
SELECT a.SalesOrderID, a.CustomerID, a.OrderDate,
b.OrderQty
FROM Sales.SalesOrderHeader a INNER JOIN
Sales.SalesOrderHeader b
ON a.SalesOrderID = b.SalesOrderID
INNER JOIN Production.Product c ON b.ProductID =
c.ProductID
WHERE a.OrderDate = @MaxDate
AND a.CustomerID = @CustomerID
RETURN
END
Calling Function
select * from dbo.fun2()
Note: We can create @temp variable in multi statement
table valued function.
Is This Answer Correct ? | 7 Yes | 1 No |
Answer / 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 |
What is trigger types in sql?
How many databases can sql express handle?
What is a design view?
What is error ora-12154: tns:could not resolve the connect identifier specified?
How many database objects (trigger, packages, sequence etc) uses a particular field in a given table. For ex: I want to know how many database object uses the ATTRIBUTE1 in the PO_VENDORS table. What query will give me the result showing the database object name(package, trigger etc), field_name used (in this case ATTRIBUTE1) and table_name (in this case PO_VENDORS).
What are the two types of exceptions in pl/sql?
What is difference between stored procedures and application procedures?
What is not in sql?
What is sqlerrm?
What is schema in sql example?
Why does sql need a server?
Is inner join same as self join?