Trending September 2023 # Learn The Usage And Implementation Of Plsql # Suggested October 2023 # Top 14 Popular

You are reading the article Learn The Usage And Implementation Of Plsql updated in September 2023 on the website Cersearch.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Learn The Usage And Implementation Of Plsql

Introduction to PLSQL procedure

PL/ SQL procedure is the block of code written for the purpose of reuse when required and has certain business logic written inside it, which may involve conditional execution or repetitive code execution in it. PLSQL procedure is stored in Oracle databases as the schema object and the block where it is stored in the named block. The main usage of the PLSQL procedures is done for reusing a particular business logic again and again as it encapsulates these logical statements in it. In this article, we will learn about the syntax, usage, and implementation of PLSQL procedures, along with the help of certain examples.

Start Your Free Software Development Course

Syntax

The general syntax of creating a procedure in PLSQL is as shown below –

CREATE [ OR REPLACE] PROCEDURE name of procedure (list of parameters) IS [ statements to be declared] BEGIN [ statements to be executed] EXCEPTION [ exception handler used for handling the exceptions] END [name of the procedure]

The terminologies used above are explained one by one in the below section –

PLSQL header for procedure

Along with the name of the procedure, we can also optionally specify the list of parameters used for input. Each of the individual parameters declared inside the parenthesis can be either OUT/ IN or INOUT parameter. These are called the modes of parameters. This mode helps us specify whether the parameter we have created is used for writing to or reading from the procedure. Let us see one by one the usage of each of the modes –

OUT – The parameter having the OUT mode can only be used for writing the values to it, and the value is used further for returning. So even though while calling the procedure, they will be completely ignored as they don’t take the values outside the procedure.

IN – This type of mode is used for specifying that the parameter value is only in the read mode and cannot be modified further inside the PL/ SQL procedure. However, even though you cannot modify it, we can still access and use the value of this parameter inside the procedure in PL/ SQL.

IN/ OUT – When a parameter is given the mode of INOUT, it will be allowed for reading as well as modification and writing by the PL/ SQL procedure.

The important point to be considered here is that when we make the use of the OR REPLACE optional clause, it will lead to the replacement of the current existing procedure in the PL/ SQL database, which will overwrite it and there is a possibility of losing the old procedure.

PL/ SQL body of the procedure

The body of the procedure is divided into three different parts, which include the declaration, execution, and, finally, handling of exceptions. The second part of that procedure which is executable parts is compulsory while the other two are optional. Also, it is important to specify at least one statement in the executable part. Let us understand each of the parts in detail –

Declarative part – In this part of the body, we can declare all the constants, cursors, and variables. This part of the body does not begin with the keyword DECLARE as in the case of other anonymous blocks.

Exception Handling – In this part of the body, we try to handle those exceptions that might arise while executing the code of the executable part.

Example of PLSQL procedure

Let us learn the implementation of the PL/ SQL stored procedures with the help of some examples –

Let us look at the example that demonstrates how the cursor

SELECT * FROM [customers_details];

The output of the execution of the above query statement is as shown below, showing the contents of the table customer details –

CREATE OR REPLACE PROCEDURE displayContact( in_customer_id NUMBER ) IS customerInfo customers_details%ROWTYPE; BEGIN -- Get the mobile numbers of the customers SELECT * INTO customerInfo FROM customers_details WHERE customer_id = in_customer_id ; -- display the infomation of the customer name and its mobile number EXCEPTION WHEN OTHERS THEN dbms_output.put_line( SQLERRM ); END;

In order to execute the above-stored procedure for a particular value of customer id, you can make the use of the following syntax of execution –

EXECUTE name of the procedure (arguments that are to be passed)

In order to call the above procedure, we can make the use of the following query statement any number of times for any customer id as the argument –

EXECUTE displayContact(101);

The execution of the above query statement gives out the following output displaying the mobile number and first name of the customer having customer id as 101.

Conclusion

The PL/ SQL stored procedures are a single unit that contains the business logic written inside it and which can also involve multiple data manipulation and retrieval of database values in its statements. This procedure can be given call multiple times.