Copyright

What is PL/SQL?

Instructor: Sudha Aravindan

Sudha has a Doctor of Education Degree and is currently working as a Information Technology Specialist.

In order to communicate with Oracle databases, we use a language known as Procedural Language/Structured Query Language or PL/SQL. In this lesson we will learn about the syntax and format of PL/SQL.

What Is PL/SQL?

Just like people from different countries have different languages to communicate with each other, different databases have different languages to help humans communicate with them. They are stored on the computer and computer servers.

The standard language used by relational databases is known as Structured Query Language or SQL. Even though SQL is the standard language for databases in general, each database has its own variations of SQL that can be understood only by that database. PL/SQL is the Structured Query Language or SQL that is used to communicate with Oracle databases.

The PL/SQL Environment

If you meet a person from a different country and both of you do not know each other's language, you would get help from a translator who understands both your languages. Similarly, in order to write PL/SQL code and execute queries to an Oracle database, you would use the SQL *Plus query tool.

The SQL *Plus tool is one of the components of the Oracle database client. This tool has a command line interface that allows a person to write lines of text to issue commands to the database. These commands or statements are in essence queries to the database. The database server executes the query and transmits the results. The results are displayed in a window of SQL *Plus.

In short, the SQL *Plus environment provides a window for human interaction and acts as a go-between the Oracle database server and a person typing in the queries or commands.

Syntax of PL/SQL

All languages of the world have their own sentence structures and grammar. Similarly, PL/SQL follows its own syntax. PL/SQL is written in blocks of code, and each block consists of the following parts:

Declaration:

PL/SQL code usually starts with the Declare statement where variables and other elements used by the program are declared and initialized. This is an optional section, meaning that if there are no variables to be declared, this section is not needed.

Example:


DECLARE
   numItems integer := 10;


This statement will create an integer with the name numItems and give it an initial value of 10.

Commands:

The commands that will be executed by the program are in a command block that start with the Begin keyword and ends with the End; keyword.

Example:


Begin
  select itemsTable.itemName
  from itemsTable
  where itemsTable.itemNum = numItems;
End;


All executable statements are within this Begin and End; statement. In this particular example, the executable commands select the column itemName from the itemsTable in the database and return all itemNames for which the criteria itemNum matches the numItems variable in the Declare section.

Null Statement

There should be at least one executable statement between the Begin and End; executable block. If there are no executable statements there should be at least a NULL; to indicate a null statement.


Begin
  IF cost-price <= 25 THEN
   x := "Low Price";
  ELSE
   NULL;
End;


Here if the cost is greater than 25, the program does nothing as indicated by the NULL; statement.

Handling Exceptions or Errors:

When you are communicating in a different language with the help of a translator and you make an error, your translator will explain to the other person that what you said was a mistake, and probably even apologize, depending on the nature of the error.

When you write queries you also have the option to tell the database what to do if it encounters an error as it tries to execute your query. Within each Begin and End; command block is a section titled Exception that has instructions for the database on how to handle exceptions or errors encountered.

This section is optional, meaning even if the Exception section is not included, the query will be executed. If an Exception section is not included, the database will not be able to provide any details about the error it encountered.

Example:


EXCEPTION
  WHEN no_data_found THEN
   dbms_output.put_line("No matching item!");


To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support