Notes 0007

PL/SQL

Besides plain vanilla SQL, oracle supports PL/SQL. The PL stands for Procedural Language, which means you can have things like IF statements, loops, variables, and other procedural things along with declarative SQL statements.

PL/SQL Variables

Just as most procedural languages, PL/SQL has some sort of variables. The types of variables are plain SQL column types that you're all used to. You can also refer to a type of a particular column explicitly by specifying the fully qualified column name (tablename.columname) followed by %TYPE. For example: PRODUCT.PRICE%TYPE.

Similarly, we can refer to a particular row as a single type. Again, you declare it by referring to a database table directly: PRODUCT%ROWTYPE. This would refer to a single record stored in the PRODUCT table.

Along with the above mentioned, some common types are: BOOLEAN, DATE, NUMBER, CHAR, and VARCHAR2.

We declare variables of these types similarly to specifying columns in tables. First, we list the name of the variable, then the type we want it to have. For example, to declare a price variable of a fixed point NUMBER, we might do something like this:

PRICE NUMBER(6,2);

PL/SQL Program Blocks

PL/SQL programs are structured in blocks and have the following format:

DECLARE
    variable_declarations
BEGIN
    procedural_code
EXCEPTION
    error_handling
END;

Declare

The declare part is where variable declaration goes. All used variables must be declared in this section. This is also the place where other more exotic variable types are declared, like cursors and exceptions.

Begin

This is the part we're most interested in. This is where the bulk of your programs shall be placed. Here, you can have IF statements, loops, etc.

Exceptions

The exception section is where we place error handling code. We will talk about it more depth in subsequent lessons.

End

The end signifies the end of this program block.

Operators

PL/SQL supports several operators to do various things. Some of them are listed below:

** Exponentiation
* Multiplication
/ Division
+ Addition
- Subtraction
- Negation
:= Assignment
= Equals Comparison
<> Not Equals Comparison
!= Not Equals Comparison
> Greater Than Comparison
< Less Than Comparison
>= Greater Than or Equal Comparison
<= Less Than or Equal Comparison
AND The obvious AND operation
OR The obvious OR operation
:= Assignment
|| String Concatenation

Hello World and a bit Beyond...

Well, lets start with the PL/SQL hello world example. Before we write it however, there are a couple of things that need to be setup. First, start SQL*Plus, login, and type:

SET SERVEROUTPUT ON

What this does is enable you to view output in SQL*Plus window whenever your programs attempts to write some output to the screen.

Now, let's get on with the show. Type the following into the SQL*Plus window as is:

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World');
END;

You'll notice that it doesn't run as an average SQL statement. To make it run, you must type the '/' character on a line by itself. And you'll notice:

SQL> BEGIN
  2      DBMS_OUTPUT.PUT_LINE('Hello World');
  3  END;
  4  /
Hello World

PL/SQL procedure successfully completed.

You can think of DBMS_OUTPUT.PUT_LINE() as sort of a printf() in C language. It writes output to the console; but it only works for strings (or data that can be implicitly converted to a string).

You can also do more complicated things, like access global variables like SYSDATE. For example:

SQL> BEGIN
  2      DBMS_OUTPUT.PUT_LINE('The time now is: ');
  3      DBMS_OUTPUT.PUT_LINE(SYSDATE);
  4  END;
  5  /
The time now is:
31-JUL-02

We're not done with this simple example yet. We can also modify the DATE format:

SQL> BEGIN
  2      DBMS_OUTPUT.PUT_LINE('The time now is: ');
  3      DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'MM/DD/YYYY'));
  4  END;
  5  /
The time now is:
07/31/2002

Type Conversion Functions

From the previous example, you can see we've used the TO_CHAR function to format the date. There are several of these useful functions. We have:

TO_DATE, which works exactly as explained before in Notes 0003.

TO_NUMBER, which converts a character string to a number (now, that's assuming the character string is a number).

TO_CHAR, which converts numbers or dates to character strings.

Character String Functions

There are a number of functions for handling character string data in PL/SQL, these include the easy to use string catenation operator. For example, we could have written our time now is example as:

SQL> BEGIN
  2      DBMS_OUTPUT.PUT_LINE('The time now is: ' || SYSDATE);
  3  END;
  4  /
The time now is: 31-JUL-02

Note that || was used to concatenate the string 'The time is now: ' with the SYSDATE.

Some of the more useful functions are described next:

RTRIM(STR): Trims the string – removes blank spaces which may be padded on in CHAR fields.

LENGTH(STR): Returns the length of the string. Space characters are also counted in the length.

UPPER(STR): Converts the string to upper case.

LOWER(STR): Converts the string to lower case.

INSTR(STR1,STR2): Looks for STR2 in STR1, and returns the location when found, or 0 when not found (Strings start at location 1).

SUBSTR(STR,START,END): Returns a substring that starts at START and ends at END of the original STR. For example:

SQL> SELECT SUBSTR('HELLO',2,4) FROM DUAL;

SUBS
----
ELLO

The end... Yep, that's right. This is it. THE END.

© 2006, Particle