Stored procedure PL/SQL
Advantages of PL/SQL
- Block structure: PL/SQL consist of a block of code, which can be nested within each other. Each block forms a unit of a task or a logic module. PL/SQL blocks can be stored in the database and reused.
- Procedural language capability: PL/SQL consist of procedural constructs such as conditional statements (if, if else, nested if, else if ladder) and loops (for, while, do-while).
- Better performance: PL/SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
- Error handling: PL/SQL handles errors or exceptions effectively during the execution of PL/SQL program. Once an exception is caught, specific action can be taken depending upon the type of the exception or it can be displayed to the user with the message.
Stored Procedure in PL/SQL
- A stored procedure (proc) is a group of PL/SQL statements that perform specific task.
- So, A procedure has two parts, header, and body.
- Moreover, The header consists of the name of the procedure and the parameters passed to the procedure.
- The body consists of declaration section, execution section and exception section.
- Also, A procedure may or may not return any value. A procedure may return more than one value.
General Syntax to create a procedure
CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
Create:-It will create a procedure.
Replace:- It will re-create a procedure if it already exists.
Moreover, We can pass parameters to the procedures in three ways.
- IN-parameters: – These types of parameters are used to send values to stored procedures.
- OUT-parameters: – These types of parameters are used to get values from stored procedures. So, This is similar to a return type in functions but the procedure can return values for more than one parameters.
- IN OUT-parameters: – Moreover, This type of parameter allows us to pass values into a procedure and get output values from the procedure.
IS indicates the beginning of the body of the procedure. The code between IS and BEGIN forms the Declaration section.
Begin:-It contains the executable statement.
Exception:- It contains exception handling part. This section is optional.
End:- It will end the procedure.
Also, The syntax within the brackets [ ] indicates that they are optional.
By using CREATE OR REPLACE together the procedure is created. So, if it does not exist and if it exists then it is replaced with the current code.
How to execute a Stored procedure PL/SQL?
Moreover, There are two ways to execute a Stored procedure PL/SQL.
1) From the SQL prompt.
Syntax: EXECUTE [or EXEC] procedure_name (parameter);
2) Within another procedure – simply use the procedure name.
Syntax: procedure_name (parameter);
Example 1 (Using IN)
CREATE OR REPLACE PROCEDURE get_studentname_by_id (id IN NUMBER)
WHERE studentID = id; END;
Execute: EXECUTE get_studentname_by_id(10); OR get_studentname_by_id(10);
Explanation:- So, Above procedure gives the name of the student whose id is 10.
Example 2 (Using OUT)
CREATE OR REPLACE PROCEDURE multiplication @mult1 int, @mult2 int, @result int
select @result = @mult1 * @mult2
Execute:- EXECUTE multiplication (5,6); OR multiplication (5,6);
Explanation:- So, Above procedure gives the multiplication of 4 and 6 that is equal to 30.
Advantages of procedure
- Security:- We can improve security by giving rights to selected persons only.
- Faster Execution:- It precompiled so compilation of procedure not required every time you call it.
- Sharing of code:- Moreover, Once a procedure created and stored, it can use by more than one user.
- Productivity:- Code written in the procedure shared by all programmers. Also, This eliminates redundant coding by multiple programmers so overall improvement in productivity.