SQL Reference

Example

DELIMITER //

CREATE PROCEDURE GetTotalOrders(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM orders;
END //

CALL GetTotalOrders(@total);

SELECT @total;

Output

+-------+
| @total|
+-------+
|  100  |
+-------+

Explanation

The SQL procedure GetTotalOrders is created to get the total number of orders from the table orders. The OUT parameter total is used to store the result. After the procedure is called with a user-defined variable @total, it extracts the total order count and stores it in @total. A SELECT statement is then used to display the total count of the orders.

Example

CREATE OR REPLACE FUNCTION get_factorial(n int)
RETURNS int AS $$
DECLARE
  product int = 1;
BEGIN
  FOR i IN 1..n LOOP
    product := product * i;
  END LOOP;
  RETURN product;
EXCEPTION
  WHEN OTHERS THEN
    RAISE EXCEPTION 'Input must be a positive integer.';
END; $$
LANGUAGE plpgsql;

SELECT get_factorial(5);

Output

get_factorial
---------------
         120

Explanation

The provided SQL code defines a stored function using PL/pgSQL to calculate the factorial of a number. IN parameter ‘n’ is the input value for which we calculate the factorial. The function uses a FOR loop to calculate the factorial of ‘n’ and returns the result. We then call this function with the number ‘5’ and it returns ‘120’, which is the factorial of ‘5’.

Example

DECLARE
    p_name VARCHAR2(50) := 'John Doe';
BEGIN
    dbms_output.put_line(p_name);
END;
/

Output

John Doe

Explanation

The example is a simple PL/SQL anonymous block where a variable p_name is declared and initialized with a value ‘John Doe’. The dbms_output.put_line() procedure is then used to print the value of p_name.

Example

CREATE PROCEDURE dbo.GetUserCount @UserCount INT OUTPUT
AS
BEGIN
SELECT @UserCount = COUNT(*) FROM Users;
END;

DECLARE @Count INT;
EXEC dbo.GetUserCount @UserCount = @Count OUTPUT;
SELECT @Count AS 'User Count';

Output

User Count
-----------------------
10

Explanation

In the above example, an output parameter ‘@UserCount’ is used in the stored procedure ‘GetUserCount’ to store the count of users from the ‘Users’ table. Further, this output parameter is used to fetch the count value, which is displayed in the SELECT statement. The output displays the total count of users.