SQL Reference - STORED

STORED

STORED in SQL refers to a certain type of column in a table that is physically stored in the database. This term is often used in contrast to VIRTUAL columns, which are not physically stored but are calculated on the fly when they are accessed. Values in STORED columns are computed and stored when data is inserted or updated, resulting in fast read operations.

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%.

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.

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.