SQL Reference - RETURN

Example

CREATE OR REPLACE FUNCTION getStock(p_id INT)
RETURNS INT AS $$
DECLARE
  stock INT;
BEGIN
  SELECT product_stock INTO stock FROM product WHERE product_id = p_id;
  RETURN stock;
END; $$
LANGUAGE plpgsql;

Output

CREATE FUNCTION

Explanation

The given function getStock takes p_id as an input parameter. It queries the product table with p_id and returns the product_stock. It is written using procedural language PostgreSQL PL/pgSQL. The output CREATE FUNCTION states that function was created successfully.

Example

BEGIN
   DECLARE
      num number(2);
   BEGIN
      SELECT 20 INTO num FROM dual;
      IF num > 15 THEN
         RETURN;
      END IF;
      dbms_output.put_line('The number is: ' || num);
   END;
END;

Output

(No output is displayed)

Explanation

In this code, a number is declared and given the value 20. Then a conditional statement checks whether this number is larger than 15. If it is, the RETURN; statement is executed and the control is immediately returned from the block, so the dbms_output.put_line function is not executed. Therefore, nothing is displayed in the output window.

Example

CREATE FUNCTION TotalSales(@ProductId int)
RETURNS decimal(10,2)
AS
BEGIN
    RETURN (SELECT SUM(Quantity * Price) FROM Sales WHERE ProductId = @ProductId)
END;
GO

SELECT dbo.TotalSales(1); -- Assume 1 is an existing ProductId

Output

152.80

Explanation

The CREATE FUNCTION statement creates a scalar function named TotalSales. This function accepts a ProductId as a parameter and returns the total sales for the product. The RETURN keyword is used to specify the result that the function should return, which in this case is the sum of Quantity * Price for the provided ProductId in the Sales table. In the SELECT statement, we then call the function with a ProductId. The function calculates and returns the total sales for this product.