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 spGetEmployeesASBEGIN SELECT * FROM EmployeesENDGO
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 ASBEGIN 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!'
.