PROC

MySQL

Example

DELIMITER //
CREATE PROCEDURE GetTotalOrders(IN customerID INT)
BEGIN
    SELECT COUNT(*)
    FROM orders
    WHERE customer_id = customerID;
END//
DELIMITER ;

Output

Query OK, 0 rows affected (0.00 sec)

Explanation

The example provided shows a Stored Procedure in MySQL. The procedure GetTotalOrders takes in a customerID as an input parameter. It counts the total number of orders for the particular customer with the input customerID from the orders table.

The DELIMITER command is used to change the standard delimiter which is semicolon (;) to another symbol (// in this case). This is important because the stored procedure contains semicolons - without changing the delimiter, MySQL would interpret these as statement ending. The delimiter is changed back to semicolon at the end.

SQL Server

Example

CREATE PROCEDURE spGetEmployees
AS
BEGIN
    SELECT * FROM Employees
END
GO

Output

| Employee_Id | Employee_Name | Employee_Role |
|-------------|---------------|---------------|
| 1           | John Doe      | Developer     |
| 2           | Jane Doe      | Tester        |
| 3           | Mike Ross     | Analyst       |

Explanation

In the above example, a stored procedure named spGetEmployees is created to retrieve all the data from the Employees table. The output table displays the Employee_Id, Employee_Name, and Employee_Role columns.

Oracle

Example

CREATE PROCEDURE simpleproc AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, World!');
END simpleproc;

EXEC simpleproc;

Output

Hello, World!

Explanation

The above SQL code defines a stored procedure named ‘simpleproc’ in Oracle. When this procedure is invoked using the EXEC simpleproc; statement, it executes the DBMS_OUTPUT.PUT_LINE function to display the string 'Hello, World!'.