PL/SQL Basics.

PL/SQL Blocks

The basic unit in any PL/SQL program is a block. All PL/SQL programs are composed of blocks, which can occur one after the other or one inside the other. There are two kinds of blocks: anonymous and named. Anonymous blocks are generally constructed dynamically and executed only once. Named blocks are the blocks that have a name associated with them. Named blocks can be further categorized as follows: Examples:

Basic Block Structure

All blocks have three distinct sections:
  • the declarative section is where the declaration of all the variables, cursors, and types used in this block are located,
  • the executable section is where the work of the block is done,
  • and the exception section is where errors handled.
    Only the executable section is required; the other two are optional. The keywords DECLARE, BEGIN, EXCEPTION, and END delimit each section. The final semicolon is also required - this is a syntactic part of the block. Thus, the skeleton of an anonymous block is: 
    declare
  /* declarative section */
begin
  /* executable section */
exception
  /* exception section */
end;
    Here you can see an example that has all three sections.

    Comments

    There are two types of comments in PL/SQL:
  • one line comments. These comments may start at any position on a line and include everything till the end of the line. To start the comments use two minus symbols: 
    PI := 3.1415; -- number PI
  • multiline comments. These comments start with /* and end with */. Everything between these pairs of symbols are to be ignored by PL/SQL interpreter.

    PL/SQL datatypes and variable declarations

    Variables are declared in the declarative section of the block. The general syntax for declaring a variable is:
    variable_name [constant] variable_type [not null] [:= value];
    where variable_name is the name of the variable, variable_type is the type of the variable, and value is the initial value of the variable. These are examples of legal declarations: 
    PI constant number := 3.141592;
Counter binary_integer := 0;
Description varchar2(128);
    This is a small example of an anonymous block that declares one numeric constant and print it to the terminal: 
    set serveroutput on
declare
 pi constant number := 3.1415;
begin
 dbms_output.put_line('pi = ' || to_char(pi));
end;

    PL/SQL has the following categories of types:

  • scalar (does not have any components within the type),
  • composite (does have any components within the type),
  • reference,
  • LOB,
  • object types.
    The predefined PL/SQL types are defined in a package called STANDARD. The contents of this package is available to any PL/SQL block. This package also defines the built-in SQL and conversion functions.

    Now we will shortly discuss some families of the scalar type.

    Numeric family

    Types in the numeric family store integer or real values. There are three basic types:
  • number
  • pls_integer
  • binary_integer
    Variables of the first type are able to hold both integer and real values while variables of the other two types can hold only integers. The syntax with declaring a number with the number type is:
    number(precision[, scale])
    The precision is the number of digits in the value, and the scale is number of digits to the right of the decimal point. The scale can be negative, which indicates that the value is rounded to the specified number of places to the left of the decimal point.

    The number type is stored in a decimal point format, which was designed for accuracy and storage efficiency. because of this, arithmetic operations cannot be performed directly on numbers. In order to do so, numbers must be converted into a binary type. However, if you have a whole number value that won't be stored in the database, but will only be used for computations, the binary_integer datatype (or pls_integer) is available. These datatypes can store integer values from -2147483647 up to +2147483647. The only difference between these two types is that when a calculation involving pls_integer overflow, an error is raised. If a calculation involving a binary_integer overflows, the result can be assigned to a number variable (which has a greater range)with no error. The difference between two datatypes is illustrated by the following example.

    Besides these three basic numeric types there a set of subtypes like:

  • dec
  • decimal
  • double precision
  • real
  • numeric
  • natural
  • positive
    and others, which are identical to one of the basic types (sometimes with additional constraints).

    Character family

    Variables in the character family are used to hold strings, or character data. The types in this family are:
  • varchar2
  • char
  • long
  • nchar
  • nvarchar2.

    varchar2 type behaves similarly to the same varchar database type. The general syntax for declaring a varchar2 variable is:

    varchar2(length [char | byte])
    where length is the maximum length of the variable in bytes (by default). The length is required -- there is no default. The maximum length for a varchar2 variable in PL/SQL is 32767 (please notice that it's greater than the max length of a varchar2 variable in database - only 4000).

    Variables of char type are fixed-length character strings. The syntax for declaring a char variable is:

    char[(length [char | byte])]
    The length is optional. The default length is 1. Because char variables are fixed length, they are blank padded, if necessary, to fill out the maximum length. Because of this, they won't necessarily match in character comparison. The max length of PL/SQL char is 32767, the max length of database char is 2000.

    PL/SQL long type can contain only upto 32767 bytes (database long can contain up 2Gb).

    Raw family

    Raw family contains datatypes raw(length) (upto 32767 vs 2000 bytes for database raw) and long raw (upto 32760 vs 2Gb for database long raw).

    Date/Interval family

    There are three types this family:

    Boolean family

    This family contains only one datatype boolean. A variable of this type can hold TRUE, FALSE, or NULL only.

    Using %TYPE

    In many cases, a PL/SQL variable will be used to manipulate data stored in a database table. In this case, the variable should have the same type as the table field.
    Example: 
    declare
  fname varchar2(32);
begin
  select firstname into fname
    from Student;
  dbms_output.put_line('First student''s name is ' || fname);
end;
    However, if for some reason we have to alter the table Student to increase the number of characters in the student first name field, then we also must edit all the PL/SQL scripts to declare a proper variable to hold it. Instead of doing this we can declare a variable of the same type as a particular field: 
      fname Student.firstname%TYPE;

    For more details about datatypes see Oracle PL/SQL User's Guide and Reference

    User-defined Sybtypes

    A subtype is a PL/SQL type that is based on an existing type. A subtype can be used to give an alternate name for a type, which describes its intended use. The syntax for defining a subtype is:
    subtype new_type is original_type;
    where new_type is the name of the new subtype, and original_type refers to the base type. The base type can be predefined subtype, or a %TYPE reference, Example: 
    decare
  subtype TLoopCounter is binary_integer;
  counter TLoopCounter;
  subtype TSSN is number(9, 0);
  string varchar2(256);
  subtype TString is string%type;
    A subtype is considered to be in the same family as its base type.

    Converting Between Datatypes

    Function Description Applied to
    to_char Converts its argument to a varchar2 type, depending on the optional format specifier. numeric, date
    to_date Converts its argument to a date type, depending on the optional format specifier. character
    to_timestamp Converts its argument to a timestamp type, depending on the optional format specifier. character
    to_dsinterval Converts its argument to a interval day to second type, depending on the optional format specifier. character
    to_yminterval Converts its argument to a interval year to month type, depending on the optional format specifier. character
    to_number Converts its argument to a number type, depending on the optional format specifier. character
    rawtohex Converts a raw value to a hexadecimal representation of the binary quantity. raw
    hextoraw Converts a hexadecimal representation to the equivalent binary quantity. character (must be in hex representation)
    chartorowid Converts a character representation of a rowid into the internal binary format. char (must be in 18-character rowid format)
    rowidtochar Converts an internal binary rowid variable into 18-character external format. rowid

    Operators

    Operator Description
    Arithmetic
    ** Exponentiation
    * Multiplication
    / Division
    + Addition
    - Subtraction
    || String concatenation
    Logical
    not Logical negation
    and Conjunction
    or Inclusion
     
    := Assignment operator
    Operator Description
    Comparison
    = Equality
    != Inequality
    < Less than
    > greater than
    <= Less than or equal to
    >= Greater than or equal to
    is null Comparing with NULL
    like Pattern comparison
    between  
    in Checks if an element is inside a given set

    References