PL/SQL is Oracle's procedural programming language. It allows you to use various control structures and conditional arguments to construct more complicated logical structures than are available through pure SQL.
All of the standard Oracle SQL functions are available for use, and some have been extended increasing their functionality or range. Furthermore, some SQL functions are available as stand alone functions within PL/SQL.
Conditional Statements
IF - THEN - ELSE - END IFThere is a time when you need to test something for its validity. The IF statement allows you to test something and depending on the result (either true or false) proceed through the logic of your program.
IF ( 1 = 1 ) THEN
.... proceed with program
END IF;
This can be further extended to allow for processing if the condition is not met...
IF ( 1 = 1 ) THEN
.... proceed with program
ELSE
.... 1 does not = 1 wow!
END IF;
And even further extended to allow for alternate nested processing if the condition is not met...and another conditionis to be tested.
IF ( 1 = 1 ) THEN
.... proceed with program
ELSIF ( 1 = 2 ) THEN
.... 1 = 2 wow!
END IF;
If statements can be nested one inside the other - as long as the nested statement is fully enclosed within one of the processing blocks of the IF or ELSE sections. Be careful when nesting IF statements as they can be difficult to read, even if you develop them. If you need more than 3 or 4 levels of nesting then you should probably rework the code to eliminate this.
An IF statement always evaluates to TRUE or FALSE and several evaluations can be strung together in a single IF statement using AND or OR.
IF ( 1= 1 ) AND ( 2 = 2 ) THEN
... proceed with program
END IF;
Taking a fairly typical scenario, where we need to test a VARCHAR2 to see if it contains all numbers, we could attempt to cast the VARCHAR2 to a number using the TO_NUMBER function and then trap any error in the EXCEPTION section (more about exception handling later in this course), but that defeats the purpose of this exercise where we want control to remain within the IF statement. Breaking down the problem we notice several things:
- We start with a VARCHAR2
- It may (or may not) be all numbers.
IF NVL(LENGTH(LTRIM('12345a','0123456789')),0) = 0 THEN
... we have a number
ELSE
... not a number
END IF;
As can be seen from the above example there may be more than one way to approach a problem, and invariably there usually is. So explaining above in more detail gives the following LTRIM the string of any numbers '0123456789'. Then see if anything is left in the string, hence the length function. The NVL is there because once the contents of a varchar have been removed it comtains NULL and its LENGTH is also NULL.
CASE - WHEN - END CASE
Looping
LOOP - END LOOP
WHILE LOOP - END WHILE
Cursors
Program Units
Functions
Procedures
Packages
Triggers
Overview of PL/SQL
PL/SQL is the best method available for writing and managing stored procedures that work with Oracle data. PL/SQL code consists of three subblocks-the declaration section, the executable section, and the exception handler. In addition, PL/SQL can be used in four different programming constructs. The types are procedures and functions, packages, and triggers. Procedures and functions are similar in that they both contain a series of instructions that PL/SQL will execute. However, the main difference is that a function will always return one and only one value. Procedures can return more than that number as output parameters. Packages are collected libraries of PL/SQL procedures and functions that have an interface to tell others what procedures and functions are available as well as their parameters, and the body contains the actual code executed by those procedures and functions. Triggers are special PL/SQL blocks that execute when a triggering event occurs. Events that fire triggers include any SQL statement.Declaring and Using Variables
The declaration section allows for the declaration of variables and constants. A variable can have either a simple or "scalar" datatype, such as NUMBER or VARCHAR2. Alternately, a variable can have a referential datatype that uses reference to a table column to derive its datatype. Constants can be declared in the declaration section in the same way as variables, but with the addition of a constant keyword and with a value assigned. If a value is not assigned to a constant in the declaration section, an error will occur. In the executable section, a variable can have a value assigned to it at any point using the assignment expression (:=).
Using Implicit Cursor Attributes
Using PL/SQL allows the developer to produce code that integrates seamlessly with access to the Oracle database. There are no special characters or keywords required for "embedding" SQL statements into PL/SQL, because SQL is an extension of PL/SQL. As such, there really is no embedding at all. Every SQL statement executes in a cursor. When a cursor is not named, it is called an implicit cursor. PL/SQL allows the developer to investigate certain return status features in conjunction with the implicit cursors that run.
These implicit cursor attributes include %notfound and %found to identify if records were found or not found by the SQL statement; %notfound, which tells the developer how many rows were processed by the statement; and %isopen, which determines if the cursor is open and active in the database.
Conditional Statements and Process Flow
Conditional process control is made possible in PL/SQL with the use of if-then-else statements. The if statement uses a Boolean logic comparison to evaluate whether to execute the series of statements after the then clause. If the comparison evaluates to TRUE, the then clause is executed. If it evaluates to FALSE, then the code in the else statement is executed. Nested if statements can be placed in the else clause of an if statement, allowing for the development of code blocks that handle a number of different cases or situations.
Using Loops
Process flow can be controlled in PL/SQL with the use of loops as well. There are several different types of loops, from simple loop-exit statements to loop-exit when statements, while loop statements, and for loop statements. A simple loop-exit statement consists of the loop and end loop keywords enclosing the statements that will be executed repeatedly, with a special if-then statement designed to identify if an exit condition has been reached. The if-then statement can be eliminated by using an exit when statement to identify the exit condition. The entire process of identifying the exit condition as part of the steps executed in the loop can be eliminated with the use of a while loop statement. The exit condition is identified in the while clause of the statement. Finally, the for loop statement can be used in cases where the developer wants the code executing repeatedly for a specified number of times.
Explicit Cursor Handling
Cursor manipulation is useful for situations where a certain operation must be performed on each row returned from a query. A cursor is simply an address in memory where a SQL statement executes. A cursor can be explicitly named with the use of the cursor cursor_name is statement, followed by the SQL statement that will comprise the cursor. The cursor cursor_name is statement is used to define the cursor in the declaration section only. Once declared, the cursor must be opened, parsed, and executed before its rows can be manipulated. This process is executed with the open statement. Once the cursor is declared and opened, rows from the resultant dataset can be obtained if the SQL statement defining the cursor was a select using the fetch statement. Both loose variables for each column's value or a PL/SQL record may be used to store fetched values from a cursor for manipulation in the statement.
CURSOR FOR Loops
Executing each of the operations associated with cursor manipulation can be simplified in situations where the user will be looping through the cursor results using the cursor for loop statement. The cursor for loops handle many aspects of cursor manipulation explicitly. These steps include including opening, parsing, and executing the cursor statement, fetching the value from the statement, handling the exit when data not found condition, and even implicitly declaring the appropriate record type for a variable identified by the loop in which to store the fetched values from the query.
Error Handling
The exception handler is arguably the finest feature PL/SQL offers. In it, the developer can handle certain types of predefined exceptions without explicitly coding error-handling routines. The developer can also associate user-defined exceptions with standard Oracle errors, thereby eliminating the coding of an error check in the executable section. This step requires defining the exception using the exception_init pragma and coding a routine that handles the error when it occurs in the exception handler.
For completely user-defined errors that do not raise Oracle errors, the user can declare an exception and code a programmatic check in the execution section of the PL/SQL block, followed by some routine to execute when the error occurs in the exception handler. A special predefined exception called others can be coded into the exception handler as well to function as a catchall for any exception that occurs that has no exception-handling process defined. Once an exception is raised, control passes from the execution section of the block to the exception handler. Once the exception handler has completed, control is passed to the process that called the PL/SQL block.
