Stored procedure in PL/SQL
- A Procedure is a subprogram unit that contains a group of PL/SQL statements. A Stored procedure in PL/SQL defined as a series of declarative SQL statements that can be stored in the database record.
- It performs one or more specific tasks. It is the same as procedures on other programming languages. Actually, the procedure means a function or a method.
- They can be called through triggers, other procedures, or applications on Java, PHP tec.The procedure contains two sections. One is a Header and another one is a body. you can learn more through sql online training
- The Header section contains the name of the procedure and the parameters or variables passed to the procedure.
- The Body section contains the declaration, execution section and exception section similar to a PL/SQL block.
How to pass parameters in PL/SQL:
When you want to create a procedure or function, then you have to define parameters. So, first of all, we want to about what is a parameter?. Now let us discuss about parameters.
Parameter:
The parameter is a variable or method. They are used to exchange data between stored procedures and functions. They allow us to give input to the subprograms and helps to divide them. The parameters are defined at the time of subprograms creation. They contain calling statements of those subprograms as well as interact with the values of subprograms. The data type of the parameter in the subprogram and the calling statement must be the same. The size of the parameter should not mention at the time of parameter declaration, because size is dynamic for this type. become a professional developer in oracle through oracle pl sql training
The parameters are classified into 3 types:
1. IN Parameter:
This can be referenced by the procedure of function. It is used to give input to the subprograms. It is a read-only variable inside the programs. Hence, their values are not changing inside the subprogram. In the calling statement, these parameters can act as a variable or a literal value, or an expression.
2.OUT Parameter:
These are used to get output from the subprograms. They contain a read-write variable inside the subprograms. So, their values can be changed inside the subprograms. At the calling statement, these parameters always be a variable as well as they hold the value from the current program.
3. IN OUT Parameter:
This parameter is used for giving input as well as for getting output from the subprograms. This is one of the read-write variables inside the subprograms. So their values can be changed inside the subprograms. The parameters should always be a variable to hold the value from the subprograms in the calling statement. They can be called at the time of creating subprograms. here is a blog on oracle pl sql way to success
Syntax to create a Stored procedure:
The below syntax shows the creation of stored procedure in SQL
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
Declarative section:
In this section, a subprogram starts with the DECLARE keyword. It has a type, cursors, constants, variables, exceptions, and nested subprograms.prepare over the important pl sql interview questions
Executable section:
It is a mandatory section and it contains statements to perform the allocated actions.
Exception Section:
This is a section of code that used to handle the exceptions.
Example of creating a Stored procedure:
The below code shows the creation of the pl sql procedure example with the parameters of a customer. It contains first name, last name, and email.
CREATE OR REPLACE PROCEDURE print-contact( in_customer_id NUMBER)
IS
r_contact contacts%ROWTYPE;
BEGIN
get contact based on customer id
SELECT *
INTO r_contact
FROM contacts
WHERE customer_id = p_customer_id;
print out contact’s information
dbms_output.put_line( r_contact.first_name || ‘ ‘ ||
r_contact.last_name || ‘<’ || r_contact.email ||’>’ );
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( SQLERRM );
END;
To compile the procedure, click on the Run statement button as shown in the below diagram.
If the procedure is compiled successfully, then u will see the new procedure at the procedure node as shown below.
Editing the procedure:
If you want to change the code in an existing procedure, then you follow these steps.
- First, click the procedure name at the Procedure node.
- And edit the code of the procedure.
3. Finally, click the menu option to recompile the procedure.
It looks as below:
Removing a procedure:
If you want to delete a procedure, you can use “DROP PROCEDURE”. The syntax for the Removing procedure is given below.
DROP PROCEDURE procedure_name;
The following steps describe the dropping of a procedure in SQL.
1. First Right-click on the procedure name, then drop the procedure which you want.
2. Then choose the “Drop” menu option.
3. Finally, in the prompt dialog, click the “Apply” button to remove the procedure.
It is shown below.
Advantages of PL/SQL:
● They help to give a good performance, as well as gives an improvement in the application.
● They decrease the traffic between the database and the application. This because, the lengthy statements already spread into the database, so we don’t need to sent again via the application.
● These are having Code reusability, functions in pl sql and methods work in other languages such as C/C++ and Java.
● They improve database performance as well as provides reusability.
● They maintain integrity as well as security.
Disadvantages of PL/SQL:
● Stored procedures need a lot of memory usage. Hence, the database administrator should decide an upper bound as to how many stored procedures are useful for a particular application.
● They haven’t provided the functionality debugging in stored procedures.
In this article, I have explained about Stored procedures in PL/SQL and its creation. I hope this article helpful to get knowledge of stored procedures.
Get in depth knowledge on pl sql through sql certification from the real time experts in online
