In this chapter, you’ll learn:
- What PL/SQL is and what it stands for
- The basic structure and syntax of a PL/SQL program
- Write and run your first PL/SQL program
Let’s get right into it!
What Is PL/SQL?
PL/SQL stands for Procedural Language/Structured Query Language and is an expansion of the SQL language developed by Oracle. It includes a set of procedural features (IF THEN ELSE logic, loops, variables) in addition to the SQL language. The code is written and executed on an Oracle database.
The SQL language includes many commands and features for reading and updating data in your database, such as SELECT, INSERT, UPDATE, and DELETE. Many applications and websites are built using SQL to interact with a database. These applications can do all sorts of things such as:
- Updating user profiles with an UPDATE statement
- Displaying forum posts with a SELECT statement
- Showing bank transactions with a SELECT statement
- Adding new products with an INSERT statement
What about all the business logic that is needed along with this?
Business logic is needed for things such as:
- Checking for existing users before adding a new user
- Ensuring account numbers are valid
- Recording deleted records in a backup or audit table rather than just deleting them
This logic is often added to the application code: in PHP, C#, ASP, or many other languages.
But you can also use PL/SQL for this logic. You’ll learn how to do that in this tutorial.
Why Use PL/SQL?
You don’t need to use PL/SQL to add business logic to your applications, but there are several reasons you may want to.
Implementation Details are Stored with the Database
When you write your business logic and implementation details on the database, it’s closely tied to the database that uses it. Bryn Llewellyn writes here:
“The implementation details are the tables and the SQL statements that manipulate them. These are hidden behind a PL/SQL interface. This is the Thick Database paradigm: select, insert, update, delete, merge, commit, and rollback are issued only from database PL/SQL. Developers and end-users of applications built this way are happy with their correctness, maintainability, security, and performance.”
Improved Performance
Storing your business logic in PL/SQL code means that it may perform better than application code because PL/SQL is closely tied to SQL and the Oracle database. It all runs on the same server, which in theory will provide a performance improvement.
Consistent Across Many Front-Ends
If you have your business logic stored on the database, you can provide that to applications that use it. You can have different applications use this database, and if they all use the same PL/SQL function then they will all manipulate data in the same way.
There are several disadvantages to using PL/SQL, such as being harder to manage source control, splitting logic between applications and databases making it harder to manage and being tied to an Oracle database. However I think these are minor disadvantages, as source control with PL/SQL code is fairly good, and it’s not very often that organisations change major database vendors – and if they do, code is likely to be rewritten anyway.
You’re here to learn about PL/SQL, so now we know what it is and why you would want to use it, let’s get started with the code.
The Basic Structure of a PL/SQL Program
A piece of PL/SQL code is often called a program. A PL/SQL program is structured in blocks. It’s a bit different to how functions inside a class work.
A PL/SQL program includes several blocks:
- Declarative section: this is where variables are declared (which you’ll learn about later).
- Executable section: this is the code that is run as part of the program.
- Exception section: this defines what happens if something goes wrong.
The only required part of a PL/SQL program is the executable section. The other two sections (declarative and exception) are optional.
How do we create these blocks? We use special keywords.
BEGIN and END
The executable part of a PL/SQL program starts with the keyword BEGIN and ends with the keyword END. They are often written on separate lines, like this:
BEGIN
--your code goes here
END;The END keyword ends with a semicolon, but the BEGIN keyword doesn’t need a semicolon.
The BEGIN keyword starts the executable section of your program. Everything after the BEGIN statement is executed, until the END statement is reached.
This could be your entire PL/SQL program. Just three lines like this. It won’t do anything, but it will run.
How can you run PL/SQL code? There are a few places:
- A command line (such as SQL*Plus or SQLcl)
- An IDE (such as SQL Developer or Toad)
Where you run this code depends on how you can access an Oracle database.
- Are you reading this at work, and have access to an Oracle database for development or testing? Use an IDE that your company or team has available, such as SQL Developer.
- Are you using this at home? Install Oracle Express and SQL Developer (both free) to be able to run code on your own computer.
- Use Oracle LiveSQL, Oracle’s free web-based SQL editor.
In this tutorial, we’ll be using LiveSQL as it’s the easiest to get started on.
Open LiveSQL
Here’s how to get started with LiveSQL:
Browse to https://livesql.oracle.com
Click on SQL Worksheet on the left. You’ll be taken to the Sign On page.
If you have an Oracle account (it’s free), enter your username and password. If not, click Create Account.
On the Create Account page, fill out the form with your details.
Once you have created your account, log in to Live SQL using these details.
The SQL Worksheet is then displayed. This is where you can run SQL statements and see your output.
Now, let’s write our first PL/SQL program.
Your First PL/SQL Program: Hello World
If you’ve learned to program before, you’ll probably remember writing your first Hello World program. Hello World is a term in programming where you learn how to write some text to the computer screen in a programming language. The text is often called “Hello World” as a tradition.
So, we’ll use PL/SQL code to write the text “Hello World” to the screen.
Enter this code onto the SQL Worksheet:
BEGIN
END;Leave a blank line between BEGIN and END. We’ll put some code inside there in the moment.
As mentioned before, we’ll be using LiveSQL for this tutorial. If you’re using an IDE such as SQL Developer, enter your code there.
Now, how do we display data to the screen? We use a function called PUT_LINE.
To call the PUT_LINE function, we need to specify two things:
- The package that the function is contained in
- The text we want to display.
The PUT_LINE function is contained in a package called DBMS_OUTPUT. A package is like a library in other programming languages. It contains a set of related PL/SQL programs. We’ll learn about packages and how to create them later in this tutorial.
The text we want to display is “Hello World”. This is specified as a parameter, which is inside the brackets that appear after the PUT_LINE function.
So, update your code so it runs the DBMS_OUTPUT.PUT_LINE function in between the BEGIN and END blocks, with the parameter of “Hello World”.
The line to include looks like this:
DBMS_OUTPUT.PUT_LINE('Hello World');Your program will look like this:
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;The PUT_LINE function includes the text “Hello World” in single quotes, as that’s the standard in SQL for working with strings or text values. We also end the line with a semicolon, so the database knows that we have reached the end of the line.
So, your LiveSQL window should look like this:
Click the Run button on the top right to run the program. The code will run and display the text in the output section at the bottom of the screen:
In LiveSQL, it shows one line saying “Statement processed”, which means the PL/SQL program has run successfully. The second line says “Hello World”, which is the text inside your code.
If you’re using SQL Developer, you can click the Run button to run the PL/SQL code and you’ll see a similar output. If you don’t see the output, it’s because the DBMS_OUTPUT is disabled by default in SQL Developer. I’ve written a post with steps and screenshots on how to enable it here.
Congratulations! You’ve just written your first PL/SQL program!
Next, you’ll learn about the declarative section in PL/SQL and how to use variables.
Chapter Quiz
