SQL Reference

PROC

PROC, short for procedure, is a subprogram in a SQL database that performs an action. It is a saved block of SQL statements. PROC is compiled when it is called and stored in a database in binary form, which means the database engine does not need to compile it repeatedly, thus providing better efficiency. A PROC in SQL can both read and modify data in the database.

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.

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.

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!'.