PL SQL Tutorial For Beginners With Examples | What Is PL/SQL

This PL SQL tutorial introduces PL/SQL with its features, basic syntax with examples. Learn to set up PL/SQL Environment step by step:

We are starting with a series of PL/SQL tutorials for our readers. In this article, we will discuss the topics like an overview of PL SQL comprising its definition, its features, some of the advantages of using PL/SQL, and differences between PL/SQL and SQL. We will also explore the basic syntax along with its identifiers, delimiters, and comments.

We shall also explain how to set up of PL/SQL environment step by step. This tutorial is prepared considering the software engineers who want to gather knowledge on PL/SQL language systematically.

List Of PL/SQL Tutorials

Tutorial #1: PL SQL Tutorial For Beginners With Examples | What Is PL/SQL (This Tutorial)
Tutorial #2: PL SQL Data Types, Variables, Constants And Literals
Tutorial #3: How To Use PL SQL Insert, Update, Delete And Select Statement
Tutorial #4: PL SQL Operators And Control Statements Tutorial
Tutorial #5: Subprograms: PL SQL Procedures And Functions With Examples
Tutorial #6: PL SQL Collections: Nested Table, Associative Array And Varray
Tutorial #7: PL SQL Cursor And Strings: Tutorial With Code Examples
Tutorial #8: PL SQL Records Tutorial With Examples
Tutorial #9: PL SQL Package: Oracle PL/SQL Package Tutorial With Examples
Tutorial #10: PL SQL Transactions – COMMIT, ROLLBACK And SAVEPOINT
Tutorial #11: Triggers In PL SQL: Tutorial With Example Programs
Tutorial #12: PL SQL Datetime Format: Date and Time Functions In PL/SQL
Tutorial #13: Complete Guide To PL SQL Exception Handling With Examples

After completing this PL SQL tutorial, you will have a good understanding of the PL/SQL programming language.

Let’s dive in!!!  

What Is PL/SQL

PL/SQL is a fusion of SQL with procedural traits of programming languages. It was launched by Oracle to upgrade the features of SQL. PL SQL is considered as one of the important languages inside the Oracle database. It is primarily an extension of SQL.

This programming language was brought into the market by Oracle Corporation with the thought of extending SQL and Oracle databases. It is known as Procedural Language extensions to the Structured Query Language.

SQL is generally used for modifying and querying information in Relational Database Management Systems (RDBMS). PL SQL comes to plug in the shortcomings of SQL and enhances the characteristics of SQL.

While working with Oracle Database, PLSQL is the preferred choice for developing good code although, C++ or Java can also be used for this purpose.

Features of PL/SQL

PL/SQL has some of the salient features. These are listed below:

• PL SQL comes with a default, interpreted, and operating system independent language.
• It can be used from the command line.
• It is a portable, efficient, and transaction processing language.
• PL/SQL syntax is derived from Pascal programming language
• It has an efficient exception handling, error checks, and data hiding features.
• Works in close connection with SQL.
• It supports multiple data types.
• Assists a large variety of programming structures with the help of functions and procedures.
• It is used for web and server page development.
• It can be used for decision making and iteration programming.
• PL/SQL executes multiple queries with a single command.
• Functions, procedures, triggers, and so on are kept in the database after generation for reuse.
• Applications developed in PL/SQL are portable to other OS and hardware.
• PL/SQL allows the use of loops, conditions, constants, variables, procedures, functions, triggers, arrays, and so on.
• PL/SQL is case insensitive. A text in PL/SQL comprises either delimiters, comments, identifiers, and literals.

Advantages of PLSQL

• PL/SQL is connected to SQL. It assists in dynamic and static SQL. Static SQL does DML operations and the control of transaction is done from PL/SQL block. Dynamic SQL embeds DDL instructions in PL/SQL blocks.
• PL SQL is capable of sending the whole block of instructions to the database at one go. This helps in enhancing the performance of the applications.
• It helps the developers to query, change, and modify information in the database.
• Applications developed in PL/SQL are secured and support object-oriented concepts.
• PL/SQL can develop robust applications. It has default packages in SQL.
• It supports the variable declaration within the blocks. These variables are utilized during the processing of the query.
• It can present multiple records from more than one table simultaneously.
• PL/SQL enjoys a solid combination with the Oracle database.

Difference Between PL/SQL Vs SQL

Environment Set Up Of PL SQL

SQL* Plus is the tool that enables the use of SQL and PL/SQL statements in command prompt. These statements are processed by the database and reflected on the screen. Oracle server must be present to execute PL/SQL statements.

=> Click here to install Oracle RDBMS

There are usually two files to download for the installation process. These zipped files need to be extracted and saved in a particular location.

A setup file in .exe format is used to initiate the installation.

Listed below are the steps in sequence to be executed:

#1) At first, the mail id field needs to be entered if we want to receive updates or any other security features. However, it is not mandatory and can be ignored. Then click on the Next button.

In case the email is not mentioned, a confirmation message is thrown. Click on the Yes button to proceed.

#2) Select the option Create and Configure a Database and then click on the Next button. Since the Oracle database is installed for the first time, we need to select this option.

#3) We have to select the System Class as provided by the installer. As Oracle is being installed in our local system and not any server, we choose the Desktop class option. Then click on the Next button.

#4) In this step, we need to declare the user account of Windows for security features to configure Oracle Home. Here, we will select the Use Windows Built-in Account option.

#5) Now we need to specify the location where the Oracle server will be stored. Oracle Base needs to be input and the other locations will be by default configured. Also, we need to set a password that the DBA will use. Pluggable database names should also be provided.

#6) Next, all the prerequisite checks are performed by the installer. Once it is done, we shall proceed further.

#7) The entire summary of information around the database and global settings will be ready for viewing. Once this information is verified, we will move to the next step.

#8) Next, the Oracle database will be triggered for installation. It may take a while to complete until Oracle gets the necessary configurations.

#9) As the database files are finished copying, we will have Database Configuration Assistant pop up. On clicking the Password Management button, we need to provide a password for SYS and SYSTEM parameters and then proceed.

#10) After the installation, we will be notified of a successful message.

#11) To verify the installation, execute the following command in the command prompt.

sqlplus "/ as sysdba"

Oracle Database Connection

In the SQL developer application, click on New Connection from the top left of the screen. Enter the necessary information as provided during the installation process and click on the Connect button.

Now the SQL Developer will show the list consisting of tables, views, and so on.

And thus, we have completed the installation of the Oracle Database.

Basic Syntax Of PL SQL

PL SQL is structured in logical blocks of code. Each block has multiple subsections comprising of the following:

1. Declaration: This section begins with the DECLARE keyword. It is not considered as the required one and has variables, subprograms, and so on.
2. Executable Commands: This section begins with BEGIN and END keywords respectively. It is considered a required one and contains PL/SQL statements. It consists of at least one executable line of code.
3. Exception Handling: This section begins with the keyword EXCEPTION. It comprises the types of exceptions that the code will handle.
4. Begin: This is the keyword used for pointing to the execution block. It is required in a PL/SQL code where actual business logic is described.
5. End: This is the keyword used to determine the end of the block of code.

Structure of PL/SQL block:

[DECLARE]
<declaration statements>;
[BEGIN]
<Execution statements>;
[EXCEPTION]
<Exception statements>;
END;

A sample code using the above block structure is given below.

DECLARE 
    msg varchar (40):= 'Software Testing Help – PL/SQL series'; 
    BEGIN 
    dbms_output.put_line(msg); 
    END; 
/

Output of the above code should be.

We need to add ‘/’ at the start of the first blank line after the last code statement to execute the block of code from the SQL command line.

PL/SQL Identifiers

PL SQL identifiers include variables, constants, procedures, cursors, and so on. Their length should not be more than thirty characters and is case insensitive. A keyword in PLSQL cannot be used as an identifier.

PL/SQL Delimiters

These are basically symbols having certain characteristics. Some of the common delimiters are +, -, @, =, ||, <<>>, (,), –, <, >, <=, >=, %. There are two types of delimiters: simple and compound symbols.

Simple symbols are enlisted in the table below:

Compound symbols are enlisted in the table below:

PL/SQL Comments

PLSQL code includes comments that explain the intent of the code. PL/SQL has both multiple lines and single-line comments. The single-line comments begin with delimiter double hyphen — and double line comments start with /* and end with */.

Sample Code snippet is given below:

 
DECLARE 
   -- Variable declaration 
   msg varchar(30):= 'Software Test'; 
BEGIN 
   /* 
   * PL/SQL executable output 
   */ 
   dbms_output.put_line(msg); 
END; 
/

The output of the above code should be:

Programs In PL/SQL

PL/SQL programs can contain any of the following listed blocks of code:

• Trigger
• Package
• Function
• Procedure
• Type
• Block of PL/SQL
• Body of Package
• Type body

Frequently Asked Questions And Answers

Q #1) What is PL/SQL? 

Answer: PL SQL is a programming language that allows the developers to integrate the features of SQL with procedural statements. The entire statement block is fed to the Oracle engine at a time. This enhances the performance of execution.

Q #2) What are the features of PL SQL?

Answer: Some of the features of PL/SQL are listed below:

• It supports multiple data types.
• Works in close connection with SQL.
• It has good exception handling techniques.
• Provides the option of using functions and procedures.

Q #3) What are the types of PL/SQL blocks?

Answer: Each statement in PLSQL is known as blocks. Thus a block includes constants, control statements, error checks, SQL queries, variables, and so on.

Q #4) How do you write a block in PLSQL?

Answer: We can write a block in PL/SQL with the help of three units known as the declaration, executable, and exception handling. While the declaration is a required section, the other two consisting of executable and exception handling sections can be ignored.

Q #5) What is the difference between SQL and PL SQL?

Answer: The primary difference between SQL and PL/SQL is that SQL runs only a query at a time but PL/SQL processes a block of code at a time. SQL is known as Structured Query Language and PL/SQL is known as Procedural Language/Structured Query Language.

Q #6) Is PL SQL better than SQL?

Answer: Yes PL/SQL is better than SQL. SQL only serves the purpose of writing queries in DDL and DML statements. PL/SQL integrates the features of SQL with procedural statements.

Conclusion

We hope that the basics of PL/SQL, its overview, features, advantages, and differences with SQL are understood now. We have also seen the steps for the environment set up of PL/SQL along with its connection with the Oracle database.

In this article, we discussed the basic syntax of PLSQL code that has more than one section, the structure of the code, various types of PL SQL delimiters, and comments.

Read through the content and gradually you will develop a strong understanding and knowledge on PL/SQL. Being an important programming language, it is used extensively by developers in developing modern-day applications.

In the next tutorial, we shall discuss the PL/SQL variables, constants, control statements, and various other related topics.

NEXT Tutorial>>