Stored Procedures are created to perform one or more DML operations on Database. It is nothing but the group of SQL statements that accepts some input in the form of parameters and performs some task and may or may not returns a value.
Syntax : Creating a Procedure
CREATE or REPLACE PROCEDURE name(parameters) IS variables; BEGIN //statements; END;
The most important part is parameters. Parameters are used to pass values to the Procedure. There are 3 different types of parameters, they are as follows:
This is the Default Parameter for the procedure. It always recieves the values from calling program.
This parameter always sends the values to the calling program.
- IN OUT:
This parameter performs both the operations. It Receives value from as well as sends the values to the calling program.
Imagine a table named with emp_table stored in Database. We are Writing a Procedure to update a Salary of Employee with 1000.
CREATE or REPLACE PROCEDURE INC_SAL(eno IN NUMBER, up_sal OUT NUMBER) IS BEGIN UPDATE emp_table SET salary = salary+1000 WHERE emp_no = eno; COMMIT; SELECT sal INTO up_sal FROM emp_table WHERE emp_no = eno; END;
Steps to execute the procedure:
- Declare a Variable to Store the value comming out from Procedure :
VARIABLE v NUMBER;
- Execution of the Procedure:
EXECUTE INC_SAL(1002, :v);
- To check the updated salary use SELECT statement:
SELECT * FROM emp_table WHERE emp_no = 1002;
- or Use print statement :
- SQL | Difference between functions and stored procedures in PL/SQL
- Advantages and Disadvantages of Using Stored Procedures - SQL
- SQL | Procedures in PL/SQL
- Different types of Procedures in MySQL
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- MySQL | Creating stored function
- Difference between Stored and Derived Attribute
- Stored Procedure for prime numbers in MYSQL
- Difference between T-SQL and PL-SQL
- Difference between SQL and T-SQL
- Mitigation of SQL Injection Attack using Prepared Statements (Parameterized Queries)
- SQL | SELECT Query
- SQL | Distinct Clause
- SQL | WHERE Clause
- SQL | AND and OR operators
- SQL | INSERT INTO Statement
- SQL | DELETE Statement
- SQL | UPDATE Statement
- SQL | SELECT TOP Clause
- SQL | ORDER BY
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.