PL SQL Tutorial

PL/SQL Tutorial

Last updated on 13th Oct 2020, Blog, Tutorials

About author

Nandhakumar (Oracle Plsql Developer )

He is a Award Winning Respective Industry Expert with 11+ Years Of Experience Also, He is a TOP Rated Technical Blog Writer Share's 1000+ Blogs for Freshers. Now He Share's this For Us.

(5.0) | 14256 Ratings 2408

What is PL/SQL?

PL/SQL stands for Procedural Language extensions to the Structured Query Language (SQL). SQL is a powerful language for both querying and updating data in relational databases.

Oracle created PL/SQL that extends some limitations of SQL to provide a more comprehensive solution for building mission-critical applications running on Oracle database. Getting to know more information about PL/SQL language.

Before getting started, we highly recommend setting up Oracle database in your system to help you practice and learn PL/SQL effectively.

The PL/SQL programming language was developed by Oracle Corporation in the late 1980s as a procedural extension language for SQL and the Oracle relational database. Following are certain notable facts about PL/SQL −

  • PL/SQL is a completely portable, high-performance transaction-processing language.
  • PL/SQL provides a built-in, interpreted and OS independent programming environment.
  • PL/SQL can also directly be called from the command-line SQL*Plus interface.
  • Direct call can also be made from external programming language calls to database.
  • PL/SQL’s general syntax is based on that of ADA and Pascal programming language.
  • Apart from Oracle, PL/SQL is available in TimesTen in-memory database and IBM DB2.

Features of PL/SQL

PL/SQL has the following features −

  • PL/SQL is tightly integrated with SQL.
  • It offers extensive error checking.
  • It offers numerous data types.
  • It offers a variety of programming structures.
  • It supports structured programming through functions and procedures.
  • It supports object-oriented programming.
  • It supports the development of web applications and server pages.
Subscribe For Free Demo

Error: Contact form not found.

Advantages of PL/SQL

PL/SQL has the following advantages −

  • SQL is the standard database language and PL/SQL is strongly integrated with SQL. PL/SQL supports both static and dynamic SQL. Static SQL supports DML operations and transaction control from PL/SQL block. In Dynamic SQL, SQL allows embedding DDL statements in PL/SQL blocks.
  • PL/SQL allows sending an entire block of statements to the database at one time. This reduces network traffic and provides high performance for the applications.
  • PL/SQL gives high productivity to programmers as it can query, transform, and update data in a database.
  • PL/SQL saves time on design and debugging by strong features, such as exception handling, encapsulation, data hiding, and object-oriented data types.
  • Applications written in PL/SQL are fully portable.
  • PL/SQL provides high security level.
  • PL/SQL provides access to predefined SQL packages.
  • PL/SQL provides support for Object-Oriented Programming.
  • PL/SQL provides support for developing Web Applications and Server Pages.

Difference Between PL/SQL Vs SQL

Serial No.PL/SQLSQL
1It is a block of code consisting of programming blocks, procedures, functions and so on.It is a standalone query for DDL and DML operations.
2It has a procedural format that describes how to perform an operation.It defines what needs to be done in an operation.
3It comprises a block or group of statements.It comprises a single statement.
4It is used to develop an application.It is used to perform various operations on data.
5It can have SQL statements inside its code.It cannot have PL/SQL logic inside.

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.

Mail-Id-Field

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.

Configure-Database

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.

Use-Windows-Build-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.

Summary

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

Oracle-Database-Installation

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.

Password-Management

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”

Course Curriculum

Take Your Career to the Next level with Oracle PL SQL Training

  • Instructor-led Sessions
  • Real-life Case Studies
  • Assignments
Explore Curriculum

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.

New-Database-Connection

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. 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. 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. 3. Exception Handling: This section begins with the keyword EXCEPTION. It comprises the types of exceptions that the code will handle.
  4. 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. 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.

Sample-Code-Structure-Output

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:

Sl. No.Simple SymbolsSignificance
1.Component selector
2/Operator division
3*Operator multiplication
4–Operator negation
5+Operator addition
6;End of statement
7@Remote access indicator
8>Greater than
9<Lesser than
10=Relational operator
11“Quoted identifier
12,Item separator
13(List delimiter
14)List delimiter
15:Host variable indicator
16%Attribute indicator
17‘Delimiter for character string
Course Curriculum

Learn Oracle PL SQL Certification Course from Top-Rated Social Media Experts

Weekday / Weekend BatchesSee Batch Details

Compound symbols are enlisted in the table below:

Sl. No.Compound SymbolsSignificance
1||Operator for concatenation
2**Operator for exponentiation
3<<Delimiter begin
4>>Delimiter end
5=>Operator for association
6:=Operator for assignment
7..Operator for range
8/*multi-line comment indicator for begin
9*/multi-line comment indicator for end
10<>Not equality operator
11>=Greater than equal to operator
12<=Less than equal to operator
13!=Not equality operator
14~=Not equality operator
15^=Not equality operator
16– –Single line comment delimiter

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:

Comments-Sample-Code-Output

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
Oraclel Pl Sql Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

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.

Are you looking training with Right Jobs?

Contact Us

Popular Courses