View Discussion
Improve Article
Save Article
View Discussion
Improve Article
Save Article
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
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:
- IN:
This is the Default Parameter for the procedure. It always receives the values from calling program. - OUT:
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.
Example:
Imagine a table named with emp_table stored in Database. We are Writing a Procedure to update a Salary of Employee with 1000.
- Declare a Variable to Store the value coming out from Procedure :
- Execution of the Procedure:
- To check the updated salary use SELECT statement:
- or Use print statement :
The pl sql stored procedure is a named PL/SQL block which performs one or more specific tasks. A pl sql stored procedure can be divided into two parts: Header and Body part. We can use the below modes to pass the parameters in a procedure: CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
IS | AS
//Declaration block
BEGIN
//Execution block
EXCEPTION
//Exception block
END; Procedure example without parameters: CREATE OR REPLACE PROCEDURE hello_world
AS
BEGIN
dbms_output.put_line('Hello World!');
END;
/ CREATE OR REPLACE PROCEDURE add_student(rollNo IN NUMBER, name IN VARCHAR2)
IS
BEGIN
insert into students values(rollNo,name);
END;
/ A procedure can be executed by using EXEC or EXECUTE statement. EXEC procedure_name();
EXEC procedure_name;Pl sql stored procedure:
Header: The header part contains the name of the procedure and the parameters passed to the procedure.
Body: The body
part contains declaration section, execution section and exception section.
Note: A pl sql stored procedure do not return a value directly.How to pass parameter in a procedure?
IN-parameters: These parameters are the read-only parameters. Procedure cannot change the value of IN parameters.
OUT-parameters: These parameters are the write-only parameters
and used to return values back to the calling program. Procedure can change the value of OUT parameters.
IN OUT-parameters: These parameters are read and write parameters i.e. a procedure can reads and change the IN OUT parameter value and return it back to the calling program.Syntax of pl sql stored procedure:
How to create a procedure?
Procedure example with
parameters:
How to execute stored procedure?
Note: Execute procedure with parameters:
EXEC procedure_name(param1,param2…paramN);
A procedure can also be invoked from other PL SQL block.
BEGIN procedure_name; END; /
How to drop stored procedure?
DROP PROCEDURE procedure_name;