PL/SQL

Introduction to PL/SQL

What is PL/SQL

Oracle's procedural extension to SQL
includes statements found in any procedural language: variable declarations, assignment statements, if statements, loops etc
can include most SQL statements - exception is DDL statements like CREATE TABLE

Language Structure

Block structure:

DECLARE

- declarations

BEGIN

- program statements

EXCEPTION

- error handling statements

END;

Notes:

DECLARE and EXCEPTION sections are optional
the block can contain any number of nested sub-blocks
each statement ends with a semi-colon
PL/SQL is not case-sensitive

Data Types and Variable Names

variables can have any SQL datatype such as CHAR, VARCHAR2, NUMBER and DATE
PL/SQL has a number of other data types, for example: binary_integer (32 bit integer), boolean (true, false)
For variable names, use same naming conventions as used in SQL

Examples:

Using %type

often in a PL/SQL program, data elements will be retrieved from a table
in this case, the data type of a variable can be defined to be that of the table column by using the keyword: %type
in the following example, a variable called pfno is declared to have the same data type as portfoliono in the Portfolio table:

pfno portfolio.portfoliono%type; Assignment Statement

Variable := expression;

Examples:

Comments

single line comments: --; any text after the -- is ignored
can also use rem
for multi-line comments, can use /* ... */

Arithmetic Operators

+, -, * and /

String Concatenation Operator

use || to concatenate two strings: 'Last name is: ' || last_name

Relational Operators

=, !=, <, > , <= and >=

Logical Operators

and, or, not

IF Statement

Format:

condition

statements

Example:

Loops

PL/SQL has several loop structures
while loop - loops while condition is true

while condition loop
loop statements
end loop;

for loop - executes a counter controlled loop

for loop_counter in [reverse] startvalue..endvalue loop
loop statements
end loop; Example:

Note: the start and end points can be variables or expressions; they don't have to be constants.

Using The dbms_output Package

this package allows the user to display information on the screen
dbms_output includes three procedures for placing values in the output buffer:
put - places a single data value in the buffer
put_line - places a single data value plus a new line character in the buffer
new_line - places a new line character in the buffer
only varchar2, number and date data types can be output; use the function to_char to convert if necessary

Example:

dbms_output.put_line('Hello ' || user_name); The SET SERVEROUTPUT Command

the dbms_output package does not display data on the screen - it simply places the data in a first in, first out stack
to display the data in the buffer, use the SET SERVEROUTPUT command
syntax

SET SERVEROUTPUT ON SIZE buffersize

buffersize is the size of the output buffer. The default size is 20,000 bytes; the maximum size is 1,000,000 bytes

Exceptions

the EXCEPTION block can be used to handle program errors
these can be predefined or user defined error conditions
predefined exceptions are raised automatically by the system when an error occurs; for example, zero_divide
user defined conditions are declared in the declare section:

declare
invalid_limit exception;

and raised with the raise statement:

raise invalid_limit;

the action taken as a result of the exception is coded in the exception section of the program

exception
when invalid_limit then
.....;

note several exceptions can be coded by including more than one when clause.

Accessing a Single Row From a Table Data

if a single row is returned by a select statement, an INTO clause can be coded with the select to store the returned values:

select select-list
    into variable-list
    from table-name
    where ....

you can ensure the data types will be compatible by using the %type keyword when defining the receiving variables
to determine if the select was successful, use the predefined exception: no_data_found

Using a Cursor to Retrieve Multiple Rows from a Table

if more than row is returned by a select statement, a cursor must be declared to process each row
the cursor is declared in the declare section:

declare
cursor cursor-name is select-statement;

note that an INTO clause is not coded in the declaration
the rows returned by the cursor can be accessed by coding a loop similar to a file processing loop: OPEN - to open the cursor, FETCH - to retrieve the next row and CLOSE - to close the cursor
a form of the for loop can also be used:

for row_index in cursor-name loop
statements to process the current row
end loop;

the for loop does an implicit open of the cursor, steps through each row returned by the select statement and does an implicit close when the loop is completed.
row_index is an implicitly declared variable; it is only available inside the for loop; it is used to qualify the column names in the row currently being processed.