Last updated on 13th Oct 2020, Blog, Tutorials
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[contact-form-7 404 "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
|1||It is a block of code consisting of programming blocks, procedures, functions and so on.||It is a standalone query for DDL and DML operations.|
|2||It has a procedural format that describes how to perform an operation.||It defines what needs to be done in an operation.|
|3||It comprises a block or group of statements.||It comprises a single statement.|
|4||It is used to develop an application.||It is used to perform various operations on data.|
|5||It 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.
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”
Take Your Career to the Next level with Oracle PL SQL Training
- Instructor-led Sessions
- Real-life Case Studies
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:
- <declaration statements>;
- <Execution statements>;
- <Exception statements>;
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);
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 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.
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 Symbols||Significance|
|6||;||End of statement|
|7||@||Remote access indicator|
|15||:||Host variable indicator|
|17||‘||Delimiter for character string|
Learn Oracle PL SQL Certification Course from Top-Rated Social Media ExpertsWeekday / Weekend BatchesSee Batch Details
Compound symbols are enlisted in the table below:
|Sl. No.||Compound Symbols||Significance|
|1||||||Operator for concatenation|
|2||**||Operator for exponentiation|
|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|
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 */
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:
- Block of PL/SQL
- Body of Package
- Type body
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
- Oracle DBA Tutorial
- Oracle Application Framework(OAF) Tutorial
- Oracle PL/SQL Interview Questions and Answers
- What Are The Steps To Become Oracle Database Certified?
- SQL Server Tutorial
- E Learning Sample Resumes
- Apache Oozie Sample Resumes
- Business Objects Interview Questions and Answers
- Cassandra Interview Questions and Answers
- Sqoop Interview Questions and Answers