I'm going to be expanding my entries on learning Oracle to include PL/SQL. I've already written a few entries about basic SQL. Today I start writing about basic PL/SQL. I'm going to keep these entries bite-sized and never more than 2 or 3 topics per entry. Today is a discussion of the structure of PL/SQL code and how to comment your code.
If you would like to read a brief history of PL/SQL, check out The Oracle PL/SQL History. I didn't write this and I can't guarantee its accuracy but it looks pretty close to what I remember.
The Structure of PL/SQL
PL/SQL is a block oriented language. PL/SQL is directly descended from ADA which was itself descended from Pascal. One of the first languages I learned was Pascal and, while working for the US Government, I had to learn ADA. In that time frame and for a few years after, I worked as an xBase programmer (Clipper mainly). When I had to learn Oracle, I hated it as a database, but I loved the PL/SQL language.
A block oriented language is based on a delimited block of code. In the case of PL/SQL, code is wrapped in a BEGIN..END construct. The most basic block of code in PL/SQL is:
BEGIN <execute some code>
END;
In a BEGIN..END block, it is mandatory to have at least one executable statement. So taking our above example to it's most basic level:
BEGIN
NULL;
END;
NULL; in this case is a NO OP. It performs no action but qualifies as an executable statement.
A PL/SQL program (any program really) is not very useful without variables. To declare a variable, we add a declaration section.
A PL/SQL program with a declaration section looks like this:
DECLARE
BEGIN
NULL;
END;
The declaration section is completely optional (as long as you aren't declaring any variables) and does not require any executable statements (or variable declarations) between the DECLARE and BEGIN. The example above is a fully valid PL/SQL program (run it and see).
The final section of our block oriented language is the EXCEPTION section. An exception section is where we put our error handling. The exception section is optional. Optional but very useful. I will talk in great detail about exception handling in the future.
If you include an EXCEPTION section, you must include some kind of executable code telling Oracle what exception you're handling and how you're going to handle it.
The most basic PL/SQL block that you can write, that also contains exception handling, is:
BEGIN
NULL;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
This is a valid PL/SQL block that you can execute in SQL*Plus or any other Oracle tool that supports PL/SQL.
The EXCEPTION keyword identifies an EXCEPTION section just as DECLARE identifies the declaration section and BEGIN identifies the executable section. The line with the WHEN clause identifies the particular exception or exceptions being handled. The executable code after the THEN (in this case another NULL;) tells Oracle HOW to handle the exception.
In the case above, WHEN OTHERS says that for ANY error, do NULL;. Basically, this just turned off all error handling for our block. I'm sure there is some case somewhere that turning off error handling altogether is a good thing but I can't think of one off the top of my head. If you find yourself using a WHEN OTHERS THEN NULL in your code, think really hard if that's really what you mean to do.
Now, to reiterate, the block structure of a PL/SQL program is:
DECLARE
BEGIN
EXCEPTION
END
BEGIN and END are mandatory and must always include at least one executable statement. DECLARE and EXCEPTION are optional and if you do include an EXCEPTION, you must also include a WHEN clause that contains at least one executable statement.
Commenting PL/SQL
Comments in PL/SQL can take one of two forms: multi-line comments and single line comments. Multi-line comments are delimited with /*..*/ and a single line comment starts with two dashes, --.
Comments can begin in any column on any line. If you are embedding comments in SQL that will be embedded in PL/SQL you need to be careful (and I'll show why in a future article) but just about any other use is ok. A rule I like to follow is to always leave at least one space between program code and program comments and never mix multi-line comments with code.
Let's look at some example of comments:
DECLARE
/* Multi-line comments are not required
to actually use multiple lines.
*/
BEGIN -- This is a single line comment
NULL;
EXCEPTION
/* The exception section is where you handle errors */
WHEN OTHERS THEN
--A null doesn't do much
NULL;
END;
That's about it for comments.
And that's about it for today. Today we covered the basic structure of a PL/SQL program and how to comment your code. Next time, I'll describe the differences between a named block and an anonymous block and how to declare variables.
Let me know if you have any questions or comments about this series of articles!
