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.
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:
PL/SQL Program Blocks
PL/SQL programs are structured in blocks and have the following format:
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.
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.
The exception section is where we place error handling code. We will talk about it more depth in subsequent lessons.
The end signifies the end of this program block.
PL/SQL supports several operators to do various things. Some of them are listed below:
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:
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:
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:
We're not done with this simple example yet. We can also modify the DATE format:
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:
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:
The end... Yep, that's right. This is it. THE END.