STORED

MySQL

Example

DELIMITER //
CREATE PROCEDURE SalaryIncrease(IN emp_id INT)
BEGIN
    UPDATE employees
    SET salary = salary * 1.10
    WHERE employee_id = emp_id;
END //
DELIMITER ;

Output

Query OK, 0 rows affected (0.00 sec)

Explanation

The above code defines a STORED PROCEDURE in MySQL. The procedure, named ‘SalaryIncrease’, takes one parameter ‘emp_id’ of type INT. When invoked, it increases the salary of the employee with the specified id by 10%.

SQL Server

Example

CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees
    WHERE Employee_ID = @EmployeeID
END

Output

Employee_ID	First_Name	Last_Name	Role	Employee_Age
101	John	Doe	Manager	30

Explanation

The above example demonstrates a stored procedure in SQL Server. The GetEmployeeByID procedure accepts an integer parameter, @EmployeeID and returns employee details from the Employees table where Employee_ID matches the provided parameter.

Oracle

Example

CREATE OR REPLACE PROCEDURE greet(name IN VARCHAR2)
IS
BEGIN
  DBMS_OUTPUT.put_line('Hello, ' || name);
END;
/

Output

Procedure created.

Explanation

The above SQL block creates a stored procedure named greet, which accepts a string input and outputs a customized greeting message using the DBMS_OUTPUT.put_line command.