Why & How Should I Learn PL/SQL? (…T-SQL…PL/pgSQL…)

01 Nov 2018 🔖 databases sql triggers
💬 EN ( Lire cet article en français )

What if you need to perform more complex loops or conditions in your code than the SQL programming language allows? That’s why most major brands of database also understand commands written in a “procedural” programming language. Oracle calls theirs “PL/SQL,” Microsoft SQL Server calls theirs “T-SQL,” PostgreSQL calls theirs “PL/pgSQL,” etc. Let’s explore whether you need to learn one of these languages.

Why do these languages exist?

SQL is a “declarative” programming language that comes with most major databases that allows you to:

1. configure the database
2. populate it with data
3. perform simple “queries” to retrieve your data

SQL is meant to be simple and accessible to non-programmers. It’s meant to be relatively foolproof.

In other words, as rich and complicated and powerful as it is, in the grand scheme of programming, there’s a lot it can’t do.

When you want to write complex conditional logic or specialized loops over the rows in a data set, you may exceed SQL’s limits.

Languages like PL/SQL allow you to do this.

In most databases, they’re also what database “triggers” are written in.

Do I need know PL/SQL?

It depends what you’re going to do.

Most database administrators won’t let ordinary end users like you save your PL/SQL programs into the database as “triggers,” “functions,” or “stored procedures.”

Some database administrators will:

1. let you draft attempts at such programs
2. proofread them to make sure you did a good job
3. save them into the database for you

But for the most part, you may not be allowed to help your company write “triggers”, “functions”, or “stored procedures”. You may be limited to writing “anonymous blocks”. Let’s take a look at each of these.

Triggers

Triggers are code, saved within the database, that the database has been told to execute every single time someone attempts to insert, update, or delete a record meeting certain criteria (e.g. stored in a certain table).

They’re often used to:

• provide more advanced record-editing permissions than the database’s native permission settings allow
• “dummy-check” and prevent/fix/enhance end users’ data entry
• etc.

Functions

Functions are code, saved within the database, that become extensions to the SQL language (for users with permission to execute the functions).

For example, in Oracle, NVL(SomeColumn,SomeValue) is a function that comes pre-installed as part of Oracle’s version of SQL and means, “If the value in SomeColumn is blank, substitute SomeValue instead.”

Storing “functions” within the database lets you add your own CleverName(...) operations for end users to use in their SQL scripts.

For example, the university where I work has saved 3 functions into the database called:

1. Current_Semester_Code()
2. Previous_Semester_Code()
3. Next_Semester_Code()

These turn “today’s date” (as computed by the database computer’s clock) into useful information that makes more concise to write queries such as:

• Who is enrolled this semester?
• Who has already graduated?

Stored Procedures

Let’s say one of a company’s web developers is writing a self-service portal for customers that retrieves data from your database and displays it on the screen to those customers.

There will be at least one SQL query to your database, because at the very least, you have to tell the database whose data the web site needs to display.

So your web developer might write a SQL query saying:

select *
from customer_info
where customer_email='[email protected]'

See that [email protected]?

Where did [email protected] come from?

From some stranger on the internet claiming that that’s their email address?

What if they lie and put more SQL code – code that deletes all tables in your database – into the “what’s your email address?” box?

Such attacks are called “SQL injection” and are very dangerous (see “Bobby Tables” for more information about preventing SQL injection.)

You could leave it up to your web developer to check that the “email address” is truly just a normal email address, but what if she forgets? What if she goes on vacation and a junior web developer accidentally backspaces out the part of the code that double-checked the quality of the “email address?”

From a database administrator’s perspective, it’s considered best practice to prevent the username under which “public”-facing code like the customer self-service portal runs from directly executing any SQL queries against the database.

Instead, database programmers for the company will save “stored procedures” into the database and give “web” code permission only to call those procedures. Such procedures serve as an API that the web programmer can safely call. For example, there could be a stored procedure called:
fetch_cust_by_em(email_address_here)

It’s then those procedures, saved inside the database, that are responsible for making sure that email_address_here is a real email address, and safely refusing to fulfill the request if not.

The web programmer no longer has to lose as much sleep about “sanitizing” their “inputs.”

Anonymous Blocks

If you’re allowed to execute SQL against your database, you can write and run fragments of “procedural” code called “anonymous blocks.”

(Presuming your code doesn’t do anything to the database that you, typing SQL by hand, wouldn’t be allowed to do.)

As an end user, I can usually do everything I need to with SQL. But once or twice, I have found myself needing to do things that were a little bit “fancier” where a “PL/SQL anonymous block” provided me an extra boost in computing power.

However…

If you’re already using the data you’re fetching with SQL inside of another programming language, such as Java or Python, you might find that the language of your “main program” is more than sufficient to do that “little bit of extra processing.”

My advice: pick the approach that works best for your business needs (including security).

How to learn PL/SQL

1. Beginning PL/SQL: From Novice to Professional by Donald J. Bales.
   This is a lighthearted, easy read that holds your hand, uses humor to increase memorization, and presents the “why” before the “how.”
   • If you already know some PL/SQL but want to make sure you understand best practices, it’s written well enough that you don’t need to stop and do the exercises.
   • If you are a total beginner, it has great programming exercises.
2. Oracle certification prep study guide for 1Z0-144: Oracle Database 11g: program with PL/SQL by Matthew Morris.
   This is a relatively short “lightning review” that makes a great companion to Donald Bales’s book if you’re already a computer programmer, helping you get “off to the races” faster. It plunges you straight into the deep end of the pool, immediately having you practice conditional logic, looping, and using variables, but also covers “best practices.”
   It’s about an out-of-date version of Oracle, but the fundamentals haven’t changed enough to avoid reading a well-written book.
3. Database PL/SQL Language Reference by Oracle.
   This is a reference manual, not a newbie’s tutorial. It’s worth knowing about if you’re an advanced programmer who gets stuck on a fine point.

Sorry I’m not familar with any great beginner’s tutorials for T-SQL or other competitors to PL/SQL. Let me know if you have any!