Oracle and SQL Server Functions differences

Oracle—Function Syntax:
-------------------------------

CREATE OR REPLACE FUNCTION function_name (
param1 IN datatype,
param2 IN datatype
)
RETURN return_datatype
IS
result return_datatype;
BEGIN   
-- Your logic here
result := ...;
RETURN result;
END;

PROGRAM:
---------------
CREATE OR REPLACE FUNCTION GetFullName (
firstName IN VARCHAR2,
lastName IN VARCHAR2
)
RETURN VARCHAR2
IS
fullName VARCHAR2(100);
BEGIN
fullName := firstName || ' ' || lastName;
RETURN fullName;
END;

SQL Server – Function Syntax:
--------------------------------------
1. Scalar Function (returns a single value)

CREATE FUNCTION function_name (@param1 datatype, @param2 datatype)
RETURNS return_datatype
AS
BEGIN
DECLARE @result return_datatype;   
   -- Your logic here
    SET @result = ...;
    RETURN @result;
END;

PROGRAM:
---------------
CREATE FUNCTION GetFullName (@firstName NVARCHAR(50), @lastName NVARCHAR(50))
RETURNS NVARCHAR(100)
AS
BEGIN
    RETURN (@firstName + ' ' + @lastName);
END;

2. Table-Valued Function

Syntax:
---------
CREATE FUNCTION function_name (@param datatype)
RETURNS TABLE
AS
RETURN
(
    SELECT * FROM SomeTable WHERE Column = @param
);

PROGRAM:
---------------
CREATE FUNCTION GetEmployeesByDept(@deptId INT)
RETURNS TABLE
AS
RETURN
(
    SELECT * FROM Employees WHERE DeptID = @deptId
);

* Write a program to get employees based on deptno (Table-Valued Function)

ORACLE:
-------------
CREATE OR REPLACE FUNCTION GetEmployeesByDept
(
    p_deptno IN NUMBER
)
RETURN SYS_REFCURSOR
AS
    emp_cursor SYS_REFCURSOR;
BEGIN
    OPEN emp_cursor FOR
        SELECT * FROM emp WHERE deptno = p_deptno;
    RETURN emp_cursor;
END;
/

SQL:
------
CREATE OR ALTER FUNCTION GetEmployeesByDept
(
@deptno INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT * FROM Emp WHERE Deptno = @deptno
);

SELECT * FROM dbo.GetEmployeesByDept(10);

* Write a program to get Concat employees name, job and sal (Scalar function)

SQL:
------
CREATE OR ALTER FUNCTION CONCAT_EMP_DETAILS
(
@ename NVARCHAR(50),
@job NVARCHAR(50),
@sal int
)
RETURNS NVARCHAR(100)
AS
BEGIN
    RETURN (@ename + ' ' + @job + ' ' + CAST(@sal AS nvarchar));
END;

SELECT dbo.CONCAT_EMP_DETAILS('SMITH', 'CLERK', 3000);

ORACLE:
-------------
CREATE OR REPLACE FUNCTION CONCAT_EMP_DETAILS (
    p_ename IN VARCHAR2,
    p_job   IN VARCHAR2,
    p_sal   IN NUMBER
) RETURN VARCHAR2
IS
    v_result VARCHAR2(200);
BEGIN
    v_result := p_ename || ' ' || p_job || ' ' || TO_CHAR(p_sal);
    RETURN v_result;
END;
/

* Functions do not use EXEC or EXECUTE like stored procedures.
   You can also join it with other tables.

SELECT e.ename, d.dname
FROM dbo.GetEmployeesByDept(20) e
JOIN Dept d ON e.deptno = d.deptno;

* What is the difference between scalar and table-valued functions?

1. Scalar Function

Returns: single value (like `int`, `varchar`, `date`, etc.)

Use case: When you need to compute or return one value from logic or input.

CREATE FUNCTION GetBonus (@salary INT)

RETURNS INT

AS

BEGIN

    RETURN (@salary * 0.10)

END;

* How to use?

SELECT ename, sal, dbo.GetBonus(sal) AS Bonus FROM Emp;

2. Table-Valued Function (TVF)

Returns: table (multiple rows and columns)

Use case: When you want to filter, transform, or return sets of data like a table.

CREATE FUNCTION GetEmployeesByDept(@deptno INT)

RETURNS TABLE

AS

RETURN (

    SELECT * FROM Emp WHERE Deptno = @deptno

);

* How to use?

SELECT * FROM dbo.GetEmployeesByDept(10);

Key Differences:

--------------------

Feature                | Scalar Function                            | Table-Valued Function (TVF)            

---------------------| ------------------------------             | -----------------------------------

Return Type        | Single value                                 | Table (set of rows/columns)            

Used In               | SELECT, WHERE, JOIN, etc.    | FROM clause like a table               

Performance Impact | Often slower in large queries | Usually faster and better for sets     

Example Usage  | `SELECT dbo.GetBonus(sal)`     | `SELECT * FROM dbo.GetEmployees(...)`  

Types                  | Always returns a scalar                | Inline or Multi-statement table-valued