What PL/SQL is about

It should be enough to read out its complete name to understand what PL/SQL is about: Procedural Language extensions to the Structured Query Language.

So, through PL/SQL we should be able to to some "real" programming, integrating the underlying SQL functionality. Something like using JDBC for Java, but here we are in the database - so we have its full power in our hands. On the other side, we are strictly bound to it. So strictly that we can forget about other non-Oracle databases.

Here is an example, based on the well known hr sample database that is provided by Oracle itself, of PL/SQL code:

-- this is a PL/SQL script

declare
name_count integer;
begin
select count(*)
into name_count
from employees
where first_name = 'Steven';

dbms_output.put_line('found ' || name_count || ' contacts.');
END;

As you see, it is a curious mixing up of a procedural language of the seventies and SQL as we know it.

First line is a comment, that it is formatted just like a SQL one.
The code is structured in a DECLARE section for the local variables, and a BEGIN-END block where the algorithm lies.
The SELECT statement is very close to the one we already know, but it outputs it result INTO our local variable.
And finally we use the put_line() function from the DBMS_OUTPUT utility package to write something (included our local variable) to the associated output buffer.

I'm writing this post while reading Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein. I suggest you to get a copy of it, if you want to learn more on this stuff.

No comments:

Post a Comment