oracleskill

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.      
            
      What is PL/SQL?     
I described PL/SQL in a previous article, What is the difference between Oracle, SQL and PL/SQLIf you haven't read that one, you should do so now.
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
      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.