PL/SQL Tutorial

Posted on | Post modified: | In >
Words count in article: 9.5k word(s) | Reading time ≈ 59 min

This post tells basic things about PL/SQL in Oracle.

What’s PL/SQL

PL/SQL stands for Procedural Language extensions to the Structured Query Language.

PL/SQL Block

PL/SQL is a block-structured language whose code is organized into blocks. A PL/SQL block consists of three sections: declaration, executable, and exception-handling sections.

In a block, the executable section is mandatory while the declaration and exception-handling sections are optional.

A PL/SQL block has a name. Functions or Procedures is an example of a named block. A named block is stored into the Oracle Database server and can be reused later.

A block without a name is an anonymous block. An anonymous block is not saved in the Oracle Database server, so it is just for one-time use. However, PL/SQL anonymous blocks can be useful for testing purposes.

The following picture illustrates the structure of a PL/SQL block:

image.png

Declaration section

A PL/SQL block has a declaration section where you declare variables, allocate memory for cursors, and define data types.

Executable section

A PL/SQL block has an executable section. An executable section starts with the keyword BEGIN and ends with the keyword END. The executable section must have a least one executable statement, even if it is the NULL statement which does nothing.

Note a block itself is an executable statement, therefore you can nest a block within other blocks.

Exception-handling section

A PL/SQL block has an exception-handling section that starts with the keyword EXCEPTION. The exception-handling section is where you catch and handle exceptions raised by the code in the execution section.

Example of an anonymous block

1
2
3
BEGIN
   DBMS_OUTPUT.put_line ('Hello World!');
END;

The executable section calls the DMBS_OUTPUT.PUT_LINE procedure to display the “Hello World” message on the screen.

Here is another example.

1
2
3
4
5
DECLARE
	t STRING := 'Hello world';
BEGIN
	dbms_output.put_line( t );
END ;

PL/SQL data types

Introduction to PL/SQL data types

Each value in PL/SQL such as a constant, variable and parameter has a data type that determines the storage format, valid values, and allowed operations.

PL/SQL has two kinds of data types: scalar and composite. The scalar types are types that store single values such as number, Boolean, character, and datetime whereas the composite types are types that store multiple values, for example, record and collection.

PL/SQL divides the scalar data types into four families:

  • Number
  • Boolean
  • Character
  • Datetime

A scalar data type may have subtypes. A subtype is a data type that is a subset of another data type, which is its base type. A subtype further defines a base type by restricting the value or size of the base data type.

Note that PL/SQL scalar data types include SQL data types and its own data type such as Boolean.

Numeric data types

The numeric data types represent real numbers, integers, and floating-point numbers. They are stored as NUMBER, IEEE floating-point storage types (BINARY_FLOAT and BINARY_DOUBLE), and PLS_INTEGER.

The data types NUMBER, BINARY_FLOAT, and BINARY_DOUBLE are SQL data types.

The PLS_INTEGER datatype is specific to PL/SQL. It represents signed 32 bits integers that range from -2,147,483,648 to 2,147,483,647.

Because PLS_INTEGER datatype uses hardware arithmetic, they are faster than NUMBER operations, which uses software arithmetic.

In addition, PLS_INTEGER values require less storage than NUMBER. Hence, you should always use PLS_INTEGER values for all calculation in its range to increase the efficiency of programs.

The PLS_INTEGER datatype has the following predefined subtypes:

image.png

Boolean data type

The BOOLEAN datatype has three data values: TRUE, FALSE, and NULL. Boolean values are typically used in control flow structure such as IF-THEN, CASE, and loop statements like LOOP, FOR LOOP, and WHILE LOOP.

Note that SQL does not have the BOOLEAN data type.

Character data types

The character data types represent alphanumeric text. PL/SQL uses the SQL character data types such as CHAR, VARCHAR2, LONG, RAW, LONG RAW, ROWID, and UROWID.

Datetime data types

The datetime data types represent dates, timestamp with or without time zone and intervals. PL/SQL datetime data types are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND.

PL/SQL Variables

Introduction to PL/SQL variables

In PL/SQL, a variable is named storage location that stores a value of a particular data type. The value of the variable changes through the program. Before using a variable, you must declare it in the declaration section of a block.

Declaring variables

The syntax for a variable declaration is as follows:

1
variable_name datatype [NOT NULL] [:= initial_value];

In this syntax:

  • First, specify the name of the variable. The name of the variable should be as descriptive as possible.

  • Second, choose an appropriate data type for the variable, depending on the kind of value which you want to store, for example, number, character, Boolean, and datetime.

  • By convention, local variable names should start with l_ and global variable names should have a prefix of g_ .

Here is an example.

1
2
3
4
5
6
7
DECLARE
    l_total_sales NUMBER(15,2);
    l_credit_limit NUMBER (10,0);    
    l_contact_name VARCHAR2(255);
BEGIN
    NULL;
END;

Default values

PL/SQL allows you to set a default value for a variable at the declaration time. To assign a default value to a variable, you use the assignment operator (:=) or the DEFAULT keyword.

Here are 2 examples to declare a variable named l_product_name with an initial value ‘Laptop’:

1
2
3
4
5
DECLARE
  l_product_name VARCHAR2( 100 ) := 'Laptop';
BEGIN
  NULL;
END;
1
2
3
4
5
DECLARE 
   l_product_name VARCHAR2(100) DEFAULT 'Laptop';
BEGIN 
   NULL; 
END;

NOT NULL constraint

If you impose the NOT NULL constraint on a value, then the variable cannot accept a NULL value. Besides, a variable declared with the NOT NULL must be initialized with a non-null value. Note that PL/SQL treats a zero-length string as a NULL value.

Variable assignments

To assign a value to a variable, you use the assignment operator (:=), for example:

1
2
3
4
5
6
DECLARE
    l_customer_group VARCHAR2(100) := 'Silver';
BEGIN
    l_customer_group := 'Gold';
    DBMS_OUTPUT.PUT_LINE(l_customer_group);
END;

You can assign a value of a variable to another as shown in the following example:

1
2
3
4
5
6
7
DECLARE
    l_business_parter VARCHAR2(100) := 'Distributor';
    l_lead_for VARCHAR2(100);
BEGIN
    l_lead_for := l_business_parter; 
    DBMS_OUTPUT.PUT_LINE(l_lead_for);
END;

Anchored declarations

Typically, you declare a variable and select a value from a table column to this variable. If the data type of the table column changes, you must adjust the program to make it work with the new type.

PL/SQL allows you to declare a variable whose data type anchor to a table column or another variable. Consider the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DECLARE
  l_customer_name customers.name%TYPE;
  l_credit_limit customers.credit_limit%TYPE;
BEGIN
  SELECT
    name, credit_limit
  INTO
    l_customer_name, l_credit_limit
  FROM
    customers
  WHERE
    customer_id = 38;

  DBMS_OUTPUT.PUT_LINE(l_customer_name || ':' || l_credit_limit );
END;

In this example:

  • First, declare two variables l_customer_name and l_credit_limit whose data type anchors to the name and credit_limit columns respectively, in the declaration section of the block.
  • Second, query customer name and credit limit of the customer id 38 and assign these column values to the l_customer_name and l_credit_limit variables in the execution block.
  • Third, display the customer name and credit limit.

This example illustrates how to declare variables that anchor to another variable:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
DECLARE
    l_credit_limit   customers.credit_limit%TYPE;
    l_average_credit l_credit_limit%TYPE;
    l_max_credit     l_credit_limit%TYPE;
    l_min_credit     l_credit_limit%TYPE;
BEGIN
    -- get credit limits
    SELECT 
        MIN(credit_limit), 
        MAX(credit_limit), 
        AVG(credit_limit)
    INTO 
        l_min_credit,
        l_max_credit, 
        l_average_credit
    FROM customers;
    
    
    SELECT 
        credit_limit
    INTO 
        l_credit_limit
    FROM 
        customers
    WHERE 
        customer_id = 100;

    -- show the credits     
    dbms_output.put_line('Min Credit: ' || l_min_credit);
    dbms_output.put_line('Max Credit: ' || l_max_credit);
    dbms_output.put_line('Avg Credit: ' || l_average_credit);

    -- show customer credit    
    dbms_output.put_line('Customer Credit: ' || l_credit_limit);
END;

PL/SQL Comments

Introduction to PL/SQL comments

PL/SQL comments allow you to describe the purpose of a line or a block of PL/SQL code.

PL/SQL has two comment styles: single-line and multi-line comments.

Single-line comments

A single-line comment starts with a double hyphen (--) that can appear anywhere on a line and extends to the end of the line.

Here is an example.

1
2
-- valued added tax 10%
DECLARE co_vat_rate CONSTANT NUMBER := 0.1;

Multi-line comments

A multi-line comment starts with a slash-asterisk ( /* ) and ends with an asterisk-slash ( */ ), and can span multiple lines:

1
2
3
4
/*
  This is a multi-line comment
  that can span multiple lines
*/

PL/SQL Constants

Introduction to PL/SQL constants

Unlike a variable, a constant holds a value that does not change throughout the execution of the program.

Constants make your code more readable.

To declare a constant, you specify the name, CONSTANT keyword, data type, and the default value. The following illustrates the syntax of declaring a constant:

1
constant_name CONSTANT datatype [NOT NULL]  := expression

In this syntax:

  • constant_name is the name of the constant that you are declaring.
  • datatype specifies the type of value that the constant will hold.
  • NOT NULL optionally imposes a NOT NULL constraint on the constant. This prevents the constant from storing NULL or an empty string.
  • expression is used as the initial value for the constant. The type of the return value of the expression must be compatible with the data type of the constant.

PL/SQL constant examples

The following example declares two constants co_payment_term and co_payment_status:

1
2
3
4
5
6
DECLARE
    co_payment_term   CONSTANT NUMBER   := 45; -- days 
    co_payment_status CONSTANT BOOLEAN  := FALSE; 
BEGIN
    NULL;
END;

The following illustrates how to declare a constant whose value is derived from an expression:

1
2
3
4
5
6
7
DECLARE 
    co_pi     CONSTANT REAL := 3.14159;
    co_radius CONSTANT REAL := 10;
    co_area   CONSTANT REAL := (co_pi * co_radius**2);
BEGIN
    DBMS_OUTPUT.PUT_LINE(co_area);
END;

PL/SQL IF Statement

Introduction to PL/SQL IF statement

The IF statement allows you to either execute or skip a sequence of statements, depending on a condition. The IF statement has the three forms:

  • IF THEN
  • IF THEN ELSE
  • IF THEN ELSIF

PL/SQL IF THEN statement

The following illustrates the structure of the IF THEN statement:

1
2
3
IF condition THEN
    statements;
END IF;

The condition is a Boolean expression that always evaluates to TRUE, FALSE, or NULL.

If the condition evaluates to TRUE, the statements after the THEN execute. Otherwise, the IF statement does nothing.

In the following example, the statements between THEN and END IF execute because the sales revenue is greater than 100,000.

1
2
3
4
5
6
DECLARE n_sales NUMBER := 2000000; 
BEGIN 
   IF n_sales > 100000 THEN 
      DBMS_OUTPUT.PUT_LINE( 'Sales revenue is greater than 100K ' ); 
   END IF; 
END;

PL/SQL IF THEN ELSE statement

The IF THEN ELSE statement has the following structure:

1
2
3
4
5
IF condition THEN
    statements;
ELSE
    else_statements;
END IF;

If the condition evaluates to TRUE, then the statements between THEN and ELSE execute. In case the condition evaluates to FALSE or NULL, the else_statements between ELSE and END IF executes.

The following example sets the sales commission to 10% if the sales revenue is greater than 200,000. Otherwise, the sales commission is set to 5%.

1
2
3
4
5
6
7
8
9
10
DECLARE
  n_sales NUMBER := 300000;
  n_commission NUMBER( 10, 2 ) := 0;
BEGIN
  IF n_sales > 200000 THEN
    n_commission := n_sales * 0.1;
  ELSE
    n_commission := n_sales * 0.05;
  END IF;
END;

PL/SQL IF THEN ELSIF statement

The following illustrates the structure of the IF THEN ELSIF statement:

1
2
3
4
5
6
7
8
9
10
11
12
IF condition_1 THEN
  statements_1
ELSIF condition_2 THEN
  statements_2
[ ELSIF condition_3 THEN
    statements_3
]
...
[ ELSE
    else_statements
]
END IF;

In this structure, the condition between IF and THEN, which is the first condition, is always evaluated. Each other condition between ELSIF and THEN is evaluated only if the preceding condition is FALSE. For example, the condition_2 is evaluated only if the condition_1 is false, the condition_3 is evaluated only if the condition_2 is false, and so on.

If a condition is true, other subsequent conditions are not evaluated. If no condition is true, the else_statements between the ELSE and ENDIF execute. In case you skip the the ELSE clause and no condition is TRUE, then the IF THEN ELSIF does nothing.

The following example uses the IF THEN ELSIF statement to set the sales commission based on the sales revenue.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
  n_sales NUMBER := 300000;
  n_commission NUMBER( 10, 2 ) := 0;
BEGIN
  IF n_sales > 200000 THEN
    n_commission := n_sales * 0.1;
  ELSIF n_sales <= 200000 AND n_sales > 100000 THEN 
    n_commission := n_sales * 0.05;
  ELSIF n_sales <= 100000 AND n_sales > 50000 THEN 
    n_commission := n_sales * 0.03;
  ELSE
    n_commission := n_sales * 0.02;
  END IF;
END;

Nested IF statement

You can nest an IF statement within another IF statement as shown below:

1
2
3
4
5
6
7
IF condition_1 THEN
    IF condition_2 THEN
        nested_if_statements;
    END IF;
ELSE
    else_statements;
END IF;

However, if you have too many levels of nesting, the code will be hard to read and maintain, so you should avoid nesting the IF statements.

PL/SQL CASE Statement

Introduction to PL/SQL CASE statement

The CASE statement chooses one sequence of statements to execute out of many possible sequences.

The CASE statement has two types: simple CASE statement and searched CASE statement. Both types of the CASE statements support an optional ELSE clause.

Simple CASE statement

A simple CASE statement evaluates a single expression and compares the result with some values.

The simple CASE statement has the following structure:

1
2
3
4
5
6
7
8
9
CASE selector
WHEN selector_value_1 THEN
    statements_1
WHEN selector_value_1 THEN 
    statement_2
...
ELSE
    else_statements
END CASE;

In this syntax:

  • The selector is an expression which is evaluated once. The result of the selector is used to select one of the several alternatives e.g., selector_value_1 and selector_value_2.

  • The selector values i.e., selector_value_1, selector_value_2, etc., are evaluated sequentially. If the result of a selector value equals the result of the selector, then the associated sequence of statements executes and the CASE statement ends. In addition, the subsequent selector values are not evaluated.

  • If no values in WHEN clauses match the result of the selector in the CASE clause, the sequence of statements in the ELSE clause executes.

Because the ELSE clause is optional, you can skip it. However, if you do so, PL/SQL will implicitly use the following:

1
2
ELSE 
    RAISE CASE_NOT_FOUND;

The following example compares single value (c_grade) with many possible values ‘A’, ‘B’, ‘C’, ‘D’, and ‘F’:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DECLARE
  c_grade CHAR( 1 );
  c_rank  VARCHAR2( 20 );
BEGIN
  c_grade := 'B';
  CASE c_grade
  WHEN 'A' THEN
    c_rank := 'Excellent' ;
  WHEN 'B' THEN
    c_rank := 'Very Good' ;
  WHEN 'C' THEN
    c_rank := 'Good' ;
  WHEN 'D' THEN
    c_rank := 'Fair' ;
  WHEN 'F' THEN
    c_rank := 'Poor' ;
  ELSE
    c_rank := 'No such grade' ;
  END CASE;
  DBMS_OUTPUT.PUT_LINE( c_rank );
END;

Searched CASE statement

The searched CASE statement evaluates multiple Boolean expressions and executes the sequence of statements associated with the first condition that evaluates to TRUE.

The searched CASE statement has the following structure:

1
2
3
4
5
6
7
8
CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
...
WHEN condition_n THEN statements_n
[ ELSE
  else_statements ]
END CASE;]

The searched CASE statement follows the rules below:

  • The conditions in the WHEN clauses in are evaluated in order, from top to bottom.
  • The sequence of statements associated with the WHEN clause whose condition evaluates to TRUE is executed. If more than one condition evaluates to TRUE, only the first one executes.
  • If no condition evaluates to TRUE, the else_statements in the ELSE clause executes. If you skip the ELSE clause and no expressions are TRUE, a CASE_NOT_FOUND exception is raised.

The following example illustrates how to use the searched CASE statement to calculate sales commission based on sales revenue.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DECLARE
  n_sales      NUMBER;
  n_commission NUMBER;
BEGIN
  n_sales := 150000;
  CASE
  WHEN n_sales    > 200000 THEN
    n_commission := 0.2;
  WHEN n_sales   >= 100000 AND n_sales < 200000 THEN
    n_commission := 0.15;
  WHEN n_sales   >= 50000 AND n_sales < 100000 THEN
    n_commission := 0.1;
  WHEN n_sales    > 30000 THEN
    n_commission := 0.05;
  ELSE
    n_commission := 0;
  END CASE;

  DBMS_OUTPUT.PUT_LINE( 'Commission is ' || n_commission * 100 || '%'
  );
END;

PL/SQL stops evaluating the subsequent condition once it finds the first condition that evaluates to TRUE. Therefore, in this example, PL/SQL will never evaluate the last two conditions in the CASE statement. The ELSE statement clause will also never execute.

PL/SQL GOTO Statement

Introduction to PL/SQL GOTO statement

The GOTO statement allows you to transfer control to a labeled block or statement. The following illustrates the syntax of the GOTO statement:

1
GOTO label_name;

The label_name is the name of a label that identifies the target statement. In the program, you surround the label name with double enclosing angle brackets as shown below:

1
<<label_name>>;

When PL/SQL encounters a GOTO statement, it transfers control to the first executable statement after the label.

PL/SQL GOTO statement example

The following shows an example of using the GOTO statements.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
BEGIN
  GOTO second_message;

  <<first_message>>
  DBMS_OUTPUT.PUT_LINE( 'Hello' );
  GOTO the_end;

  <<second_message>>
  DBMS_OUTPUT.PUT_LINE( 'PL/SQL GOTO Demo' );
  GOTO first_message;

  <<the_end>>
  DBMS_OUTPUT.PUT_LINE( 'and good bye...' );

END;

The following explains the sequence of the block in detail:

  • First, the GOTO second_message statement is encountered, therefore, the control is passed to the statement after the second_message label.
  • Second, the GOTO first_message is encountered, so the control is transferred to the statement after the first_message label.
  • Third, the GOTO the_end is reached, hence the control is passed to the statement after the the_end label.

PL/SQL GOTO statement restrictions

The GOTO statement is subject to the following restrictions.

First, you cannot use a GOTO statement to transfer control into an IF, CASE or LOOP statement, the same for sub-block.

Second, you cannot use a GOTO statement to transfer control from one clause to another in the IF statement e.g., from IF clause to ELSIF or ELSE clause, or from one WHEN clause to another in the CASE statement.

Third, you cannot use a GOTO statement to transfer control out of a subprogram or into an exception handler.

Fourth, you cannot use a GOTO statement to transfer control from an exception handler back into the current block.

PL/SQL NULL Statement

Introduction to PL/SQL NULL statement

The PL/SQL NULL statement has the following format:

1
NULL;

The NULL statement is a NULL keyword followed by a semicolon (;). The NULL statement does nothing except that it passes control to the next statement.

Usages of PL/SQL NULL statement

The NULL statement is useful to:

  • Improve code readability.
  • Provide a target for a GOTO statement.
  • Create placeholders for subprograms.

PL/SQL LOOP Statement

PL/SQL LOOP statement syntax

The PL/SQL LOOP statement has the following structure:

1
2
3
[<<label>>] LOOP
    statements;
END LOOP [loop_label];

This structure is the most basic of all the loop constructs including FOR LOOP and WHILE LOOP. This basic LOOP statement consists of a LOOP keyword, a body of executable code, and the END LOOP keywords.

The LOOP statement executes the statements in its body and returns control to the top of the loop. Typically, the body of the loop contains at least one EXIT or EXIT WHEN statement for terminating the loop. Otherwise, the loop becomes an infinite loop.

The LOOP statement can have an optional label that appears at the beginning and the end of the statement.

It is a good practice to use the LOOP statement when:

  • You want to execute the loop body at least once.
  • You are not sure the number of times you want the loop to execute.

EXIT statement

The EXIT statement allows you to unconditionally exit the current iteration of a loop.

1
2
3
LOOP
    EXIT;
END LOOP;

Typically, you use the EXIT statement with an IF statement to terminate a loop when a condition is true:

1
2
3
4
5
LOOP
    IF condition THEN
        EXIT;
    END IF;
END LOOP;

The following example illustrates how to use the LOOP statement to execute a sequence of code and EXIT statement to terminate the loop.

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE
  l_counter NUMBER := 0;
BEGIN
  LOOP
    l_counter := l_counter + 1;
    IF l_counter > 3 THEN
      EXIT;
    END IF;
    dbms_output.put_line( 'Inside loop: ' || l_counter )  ;
  END LOOP;
  -- control resumes here after EXIT
  dbms_output.put_line( 'After loop: ' || l_counter );
END;

The following explains the logic of the code:

  • First, declare and initialize a variable l_counter to zero.
  • Second, increase the l_counter by one inside the loop and exit the loop if the l_counter is greater than three. If the l_counter is less than or equal three, show the l_counter value. Because the initial value of l_counter is zero, the code in the body of the loop executes three times before it is terminated.
  • Third, display the value of the l_counter after the loop.

EXIT WHEN statement

The EXIT WHEN statement has the following syntax:

1
EXIT WHEN condition;

The EXIT WHEN statement exits the current iteration of a loop when the condition in the WHEN clause is TRUE. Essentially, the EXIT WHEN statement is a combination of an EXIT and an IF THEN statement.

Each time the control reaches the EXIT WHEN statement, the condition is evaluated. If the condition evaluates to TRUE, then the loop terminates. Otherwise, the EXIT WHEN clause does nothing. Inside the loop body, you must make the condition TRUE at some point to prevent an infinite loop.

The following example uses the EXIT WHEN statement to terminate a loop.

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
  l_counter NUMBER := 0;
BEGIN
  LOOP
    l_counter := l_counter + 1;
    EXIT WHEN l_counter > 3;
    dbms_output.put_line( 'Inside loop: ' || l_counter ) ;
  END LOOP;

  -- control resumes here after EXIT
  dbms_output.put_line( 'After loop: ' || l_counter );
END;

Nested loops

It is possible to nest a LOOP statement within another LOOP statement as shown in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DECLARE
  l_i NUMBER := 0;
  l_j NUMBER := 0;
BEGIN
  <<outer_loop>>
  LOOP
    l_i := l_i + 1;
    EXIT outer_loop WHEN l_i > 2;    
    dbms_output.put_line('Outer counter ' || l_i);
    -- reset inner counter
    l_j := 0;
    <<inner_loop>> LOOP
      l_j := l_j + 1;
      EXIT inner_loop WHEN l_j > 3;
      dbms_output.put_line(' Inner counter ' || l_j);
    END LOOP inner_loop;
  END LOOP outer_loop;
END;

PL/SQL FOR LOOP Statement

Introduction to PL/SQL FOR LOOP statement

PL/SQL FOR LOOP executes a sequence of statements a specified number of times. The PL/SQL FOR LOOP statement has the following structure:

1
2
3
4
FOR index IN lower_bound .. upper_bound
LOOP 
    statements; 
END LOOP;

The index is an implicit variable. It is local to the FOR LOOP statement. In other words, you cannot reference it outside the loop.

Inside the loop, you can reference index but you cannot change its value. After the FOR LOOP statement executes, the index becomes undefined.

Both lower_bound and upper_bound are numbers or expressions that evaluate to numbers. The lower_bound and upper_bound are evaluated once when the FOR LOOP statement starts. Their values are stored as temporary PLS_INTEGER values. The results of lower_bound and upper_bound are rounded to the nearest integer if necessary.

If you modify the values of lower_bound or upper_bound inside the loop, the change will have no effect because they are evaluated once only before the first loop iteration starts.

Typically, lower_bound is less than upper_bound. In this case, index is set to lower_bound, the statements execute, and control returns to the top of the loop, where index is compared to upper_bound. If index is less than upper_bound, index is incremented by one, the statements execute, and control again returns to the top of the loop. When index is greater than upper_bound, the loop terminates, and control transfers to the statement after the FOR LOOP statement.

If lower_bound is equal to upper_bound, the statements execute only once. When lower_bound is greater than upper_bound, the statements do not execute at all.

PL/SQL FOR LOOP examples

Let’s take some examples of using the FOR LOOP statement to understand how it works.

Simple PL/SQL FOR LOOP example

In this example, the loop index is l_counter, lower_bound is one, and upper_bound is five. The loop shows a list of integers from 1 to 5.

1
2
3
4
5
6
BEGIN
  FOR l_counter IN 1..5
  LOOP
    DBMS_OUTPUT.PUT_LINE( l_counter );
  END LOOP;
END;

Simulating STEP clause in FOR LOOP statement

The loop index is increased by one after each loop iteration and you cannot change the increment e.g., two, three and four. However, you can use an additional variable to simulate the increment by two, three, four, etc., as shown in the example below:

1
2
3
4
5
6
7
DECLARE
  l_step  PLS_INTEGER := 2;
BEGIN
  FOR l_counter IN 1..5 LOOP
    dbms_output.put_line (l_counter*l_step);
  END LOOP;
END;

Referencing variable with the same name as the loop index

In this example, we had a variable named l_counter, which is also the name of the index. To reference the variable l_counter inside the loop, you must qualify it using a block label as shown below:

1
2
3
4
5
6
7
8
9
10
11
<<outer>>
DECLARE
  l_counter PLS_INTEGER := 10;
BEGIN
  FOR l_counter IN 1.. 5 loop
    DBMS_OUTPUT.PUT_LINE ('Local counter:' ||  l_counter);
    outer.l_counter := l_counter;
  end loop;
  -- after the loop
  DBMS_OUTPUT.PUT_LINE ('Global counter' || l_counter);
END outer;

FOR LOOP with REVERSE keyword

The following shows the structure of the FOR LOOP statement with REVERSE keyword:

1
2
3
4
FOR index IN REVERSE lower_bound .. upper_bound
    LOOP 
    statements; 
END LOOP;

With the REVERSE keyword, the index is set to upper_bound and decreased by one in each loop iteration until it reaches lower_bound.

See the following example:

1
2
3
4
5
6
BEGIN
  FOR l_counter IN REVERSE 1..3
  LOOP
    DBMS_OUTPUT.PUT_LINE( l_counter );
  END LOOP;
END;

PL/SQL WHILE Loop Statement

The WHILE loop statement syntax

Here is the syntax for the WHILE loop statement:

1
2
3
4
WHILE condition
LOOP
    statements;
END LOOP;

The condition in the WHILE is a Boolean expression that evaluates to TRUE, FALSE or NULL.

The WHILE loop statement continues to execute the statements between the LOOP and END LOOP as long as the condition in the WHILE clause evaluates to TRUE.

PL/SQL evaluates the condition in the WHILE clause before each loop iteration. If the condition is TRUE, then the loop body executes. In case it is FALSE or NULL, the loop terminates.

If the condition is FALSE before entering the loop, the WHILE loop does not execute at all. This behavior is different from the LOOP statement whose loop body always executes once.

To terminate the loop prematurely, you use an EXIT or EXIT WHEN statement.

PL/SQL WHILE loop examples

Let’s take some examples of using the WHILE loop statement to see how it works.

Simple WHILE loop example

The following example illustrates how to use the WHILE loop statement:

1
2
3
4
5
6
7
8
9
DECLARE
  n_counter NUMBER := 1;
BEGIN
  WHILE n_counter <= 5
  LOOP
    DBMS_OUTPUT.PUT_LINE( 'Counter : ' || n_counter );
    n_counter := n_counter + 1;
  END LOOP;
END;

WHILE loop example terminated by EXIT WHEN statement

The following example is the same as the one above except that it has an additional EXIT WHEN statement.

1
2
3
4
5
6
7
8
9
10
DECLARE
   n_counter NUMBER := 1;
BEGIN
   WHILE n_counter <= 5
      LOOP
        DBMS_OUTPUT.PUT_LINE( 'Counter : ' || n_counter );
        n_counter := n_counter + 1;
        EXIT WHEN n_counter = 3;
      END LOOP;
   END;

PL/SQL CONTINUE Statement

PL/SQL CONTINUE statement

The CONTINUE statement allows you to exit the current loop iteration and immediately continue on to the next iteration of that loop.

The CONTINUE statement has a simple syntax:

1
CONTINUE;

Typically, the CONTINUE statement is used within an IF THEN statement to exit the current loop iteration based on a specified condition as shown below:

1
2
3
IF condition THEN
    CONTINUE;
END IF;

The CONTINUE can be used in all loop constructs including LOOP, FOR LOOP and WHILE LOOP.

The following is a simple example of using the CONTINUE statement to skip over loop body execution for odd numbers:

1
2
3
4
5
6
7
8
9
10
BEGIN
  FOR n_index IN 1 .. 10
  LOOP
    -- skip odd numbers
    IF MOD( n_index, 2 ) = 1 THEN
      CONTINUE;
    END IF;
    DBMS_OUTPUT.PUT_LINE( n_index );
  END LOOP;
END;

PL/SQL CONTINUE WHEN statement

The CONTINUE WHEN statement exits the current loop iteration based on a condition and immediately continue to the next iteration of that loop.

The syntax of CONTINUE WHEN statement is:

1
CONTINUE WHEN condition;

The condition in the WHEN clause is evaluated each time the CONTINUE WHEN statement is reached. If the condition is TRUE, the current loop is skipped, and control is transferred to the next iteration of the loop. If the condition is not TRUE, either FALSE or NULL, the CONTINUE WHEN statement does nothing.

Essentially, the CONTINUE WHEN statement is the combination of an IF THEN statement and CONTINUE statement:

1
2
3
IF condition THEN
    CONTINUE;
END IF;

Similar to the CONTINUE statement, you can use the CONTINUE WHEN statement in LOOP, FOR LOOP and WHILE LOOP.

The following example illustrates how to use the CONTINUE WHEN statement to skip over loop body execution for even numbers:

1
2
3
4
5
6
7
8
9
BEGIN
  FOR n_index IN 1 .. 10
  LOOP
    -- skip even numbers
    CONTINUE
  WHEN MOD( n_index, 2 ) = 0;
    DBMS_OUTPUT.PUT_LINE( n_index );
  END LOOP;
END;

PL/SQL SELECT INTO Statement

Introduction to PL/SQL SELECT INTO statement

PL/SQL SELECT INTO statement is the simplest and fastest way to fetch a single row from a table into variables. The following illustrates the syntax of the PL/SQL SELECT INTO statement:

1
2
3
4
5
6
7
8
SELECT
  select_list
INTO
  variable_list
FROM
  table_name
WHERE
  condition;

In this syntax, the number of columns in the variable_list must be the same as the number of variables (or the number of components of a record) in the select_list. In addition, their corresponding data type must be compatible.

Besides the WHERE clause, you can use other clauses in the SELECT statement such as INNER JOIN, GROUP BY, HAVING, and UNION.

If the SELECT statement returns more than one row, Oracle will raise the TOO_MANY_ROWS exception. If the SELECT statement does not return any row, Oracle will raise the NO_DATA_FOUND exception.

PL/SQL SELECT INTO examples

Let’s use the customers and contacts tables in the sample database for demonstration.

image.png

PL/SQL SELECT INTO – selecting one column example

The following example uses a SELECT INTO statement to get the name of a customer based on the customer id, which is the primary key of the customers table.

1
2
3
4
5
6
7
8
9
10
DECLARE
  l_customer_name customers.name%TYPE;
BEGIN
  -- get name of the customer 100 and assign it to l_customer_name
  SELECT name INTO l_customer_name
  FROM customers
  WHERE customer_id = 100;
  -- show the customer name
  dbms_output.put_line( v_customer_name );
END;

In this example:

  • First, declare a variable l_customer_name whose data type anchors to the name columns of the customers table. This variable will hold the customer name.
  • Second, use the SELECT INTO statement to select value from the name column and assign it to the l_customer_name variable.
  • Third, show the customer name using the dbms_output.put_line procedure.

PL/SQL SELECT INTO – selecting a complete row example

The following example fetches the entire row from the customers table for a specific customer ID:

1
2
3
4
5
6
7
8
9
10
DECLARE
  r_customer customers%ROWTYPE;
BEGIN
  -- get the information of the customer 100
  SELECT * INTO r_customer
  FROM customers
  WHERE customer_id = 100;
  -- show the customer info
  dbms_output.put_line( r_customer.name || ', website: ' || r_customer.website );
END;

In this example:

  • First, declare a record based on the row of the customers table. This record will hold the entire row of the customers table.
  • Second, select the customer whose id is 100 into the r_customer record.
  • Third, show the customer’s name and website.

PL/SQL SELECT INTO – selecting data into multiple variables example

The following example fetches the names of customer and contact from the customers and contacts tables for a specific customer id.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DECLARE
  l_customer_name customers.name%TYPE;
  l_contact_first_name contacts.first_name%TYPE;
  l_contact_last_name contacts.last_name%TYPE;
BEGIN
  -- get customer and contact names
  SELECT
    name, 
    first_name, 
    last_name
  INTO
    l_customer_name, 
    l_contact_first_name, 
    l_contact_last_name
  FROM
    customers
  INNER JOIN contacts USING( customer_id )
  WHERE
    customer_id = 100;
  -- show the information  
  dbms_output.put_line( 
    l_customer_name || ', Contact Person: ' ||
    l_contact_first_name || ' ' || l_contact_last_name );
END;

In this example:

  • First, declare three variables l_customer_name, l_contact_first_name, l_contact_last_name to hold the customer and contact’s name.
    Second, use the SELECT INTO statement to fetch customer and contact names of the customer id 100 from the customers and contacts tables into the corresponding variables l_customer_name, l_contact_first_name, l_contact_last_name.
  • Third, display the customer and contact names.

PL/SQL Record

Introduction to PL/SQL record

A PL/SQL record is a composite data structure which consists of multiple fields; each has its own value.

PL/SQL record helps you simplify your code by shifting from field-level to record-level operations.

PL/SQL has three types of records: table-based, cursor-based, programmer-defined. Before using a record, you must declare it.

Declaring records

You define and declare records in the declaration section of a block or via package specification.

Table-based record

To declare a table-based record, you use the %ROWTYPE attribute with a table name. A table-based record has each field corresponding to a column in a table.

1
2
DECLARE
   record_name table_name%ROWTYPE;

Cursor-based record

A cursor-based record has each field corresponding a column or alias in the cursor SELECT statement.

To declare a cursor-based record, you use the %ROWTYPE attribute with an explicit cursor as shown below:

1
2
DECLARE
    record_name cursor_name%ROWTYPE;

Programmer-defined record

The table-based and cursor-based records are good enough when you need to create records based on existing structures.

If you want to create a record whose structure is not based on the existing ones, then you use programmer-defined record.

To declare a programmer-defined record, you use the following steps:

  1. Define a record type that contains the structure you want in your record.

  2. Declare a record based on the record type.

1
2
3
4
5
TYPE record_type IS RECORD (
    field_name1 data_type1 [[NOT NULL] := | DEFAULT default_value],
    field_name2 data_type2 [[NOT NULL] := | DEFAULT default_value],
    ...
);

To declare a record based on the predefined record type, you use the following syntax:

1
record_name record_type;

Referencing a record’s field

You reference a field in a record via the dot notation as shown below:

1
record_name.field_name

Assigning records

You can assign a record to another record of the same type, for example:

1
r_contact1 := r_contact2;

But you cannot compare two records of the same type via a comparisn operator.

You can assign a value to the individual field of a record, for example:

1
2
3
r_contact.first_name := 'John';
r_contact.last_name := 'Doe';
r_contact.phone := '(408-654-2865)';

You can use SELECT INTO a whole record (or individual fields):

1
2
3
4
5
6
7
8
SELECT
  first_name, last_name, phone
INTO
  r_contact
FROM
  contacts
WHERE
  contact_id = 100;

You can FETCH INTO a whole record or individual fields:

1
2
3
4
5
6
7
8
9
10
-- fetch a whole record
FETCH cur_contacts INTO r_contact;

-- fetch individual fields
FETCH
  cur_contacts
INTO
  r_contact.first_name, 
  r_contact.last_name, 
  r_contact.phone;

Records and INSERT / UPDATE statements

You can insert a new row into a table using a %ROWTYPE record without having to specify each field. The following statement creates a new table named persons for demonstration:

1
2
3
4
5
6
CREATE TABLE persons  (
    person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2( 50 ) NOT NULL, 
    last_name VARCHAR2( 50 ) NOT NULL,
    primary key (person_id)
);

The following block inserts a new row into the persons table using a %ROWTYPE record:

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
  r_person persons%ROWTYPE;

BEGIN
  -- assign values to person record
  r_person.person_id  := 1;
  r_person.first_name := 'John';
  r_person.last_name  := 'Doe';

  -- insert a new person
  INSERT INTO persons VALUES r_person;
END;

To update a row from a %ROWTYPE record, you use the SET ROW keywords as shown in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DECLARE
  r_person persons%ROWTYPE;

BEGIN
  -- get person data of person id 1
  SELECT * INTO r_person 
  FROM persons 
  WHERE person_id = 1;

  -- change the person's last name
  r_person.last_name  := 'Smith';

  -- update the person
  UPDATE persons
  SET ROW = r_person
  WHERE person_id = r_person.person_id;
END;

Nested record

A record can contain a field which is another record. Nesting records is a powerful way to structure your program data and hide complexity in your code.

The following example declares a record type named address. Then in the record type customer has two fields ship_to and bill_to which are based on the address record type.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
DECLARE
  TYPE address IS RECORD (
    street_name VARCHAR2(255),
    city VARCHAR2(100),
    state VARCHAR2(100),
    postal_code VARCHAR(10),
    country VARCHAR2(100)
  );
  TYPE customer IS RECORD(
      customer_name VARCHAR2(100),
      ship_to address,
      bill_to address
  );
  r_one_time_customer customer;
BEGIN

  r_one_time_customer.customer_name := 'John Doe';
  -- assign address
  r_one_time_customer.ship_to.street_name := '4000 North 1st street';
  r_one_time_customer.ship_to.city := 'San Jose';
  r_one_time_customer.ship_to.state := 'CA';
  r_one_time_customer.ship_to.postal_code := '95134';
  r_one_time_customer.ship_to.country := 'USA';
  -- bill-to address is same as ship-to address
  r_one_time_customer.bill_to := one_time_customer.ship_to;
END;

PL/SQL Exception

Introduction to PL/SQL exceptions

PL/SQL treats all errors that occur in an anonymous block, procedure, or function as exceptions. The exceptions can have different causes such as coding mistakes, bugs, even hardware failures.

It is not possible to anticipate all potential exceptions, however, you can write code to handle exceptions to enable the program to continue running as normal.

The code that you write to handle exceptions is called an exception handler.

A PL/SQL block can have an exception-handling section, which can have one or more exception handlers.

Here is the basic syntax of the exception-handling section:

1
2
3
4
5
6
7
8
9
10
11
12
BEGIN
    -- executable section
    ...
    -- exception-handling section
    EXCEPTION 
        WHEN e1 THEN 
            -- exception_handler1
        WHEN e2 THEN 
            -- exception_handler1
        WHEN OTHERS THEN
            -- other_exception_handler
END;

In this syntax, e1, e2 are exceptions.

When an exception occurs in the executable section, the execution of the current block stops and control transfers to the exception-handling section.

If the exception e1 occurred, the exception_handler1 runs. If the exception e2 occurred, the exception_handler2 executes. In case any other exception raises, then the other_exception_handler runs.

After an exception handler executes, control transfers to the next statement of the enclosing block. If there is no enclosing block, then the control returns to the invoker if the exception handler is in a subprogram or host environment (SQL Developer or SQL*Plus) if the exception handler is in an anonymous block.

If an exception occurs but there is no exception handler, then the exception propagates.

PL/SQL exception examples

Let’s take some examples of handling exceptions.

PL/SQL NO_DATA_FOUND exception example

The following block accepts a customer id as an input and returns the customer name :

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE
    l_name customers.NAME%TYPE;
    l_customer_id customers.customer_id%TYPE := &customer_id;
BEGIN
    -- get the customer name by id
    SELECT name INTO l_name
    FROM customers
    WHERE customer_id = l_customer_id;

    -- show the customer name   
    dbms_output.put_line('Customer name is ' || l_name);
    
END;

If you execute the block and enter the customer id as zero, Oracle will issue the following error:

1
ORA-01403: no data found

The ORA-01403 is a predefined exception.

Note that the following line does not execute at all because control transferred to the exception handling section.

1
dbms_output.put_line('Customer name is ' || l_name);

To issue a more meaningful message, you can add an exception-handling section as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE
    l_name customers.NAME%TYPE;
    l_customer_id customers.customer_id%TYPE := &customer_id;
BEGIN
    -- get the customer
    SELECT NAME INTO l_name
    FROM customers
    WHERE customer_id = l_customer_id;
    
    -- show the customer name   
    dbms_output.put_line('customer name is ' || l_name);

    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Customer ' || l_customer_id ||  ' does not exist');
END;

PL/SQL TOO_MANY_ROWS exception example

First, modify the code block in the above example as follows and execute it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE
    l_name customers.name%TYPE;
    l_customer_id customers.customer_id%TYPE := &customer_id;
BEGIN
    -- get the customer
    SELECT name INTO l_name
    FROM customers
    WHERE customer_id <= l_customer_id;
    
    -- show the customer name   
    dbms_output.put_line('Customer name is ' || l_name);

    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Customer ' || l_customer_id ||  ' does not exist');
END;

Second, enter the customer id 10 and you’ll get the following error:

1
ORA-01422: exact fetch returns more than requested number of rows

This is another exception called TOO_MANY_ROWS which was not handled by the code.

Third, add the exception handler for the TOO_MANY_ROWS exception:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DECLARE
    l_name customers.NAME%TYPE;
    l_customer_id customers.customer_id%TYPE := &customer_id;
BEGIN
    -- get the customer
    SELECT NAME INTO l_name
    FROM customers
    WHERE customer_id > l_customer_id;
    
    -- show the customer name   
    dbms_output.put_line('Customer name is ' || l_name);
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Customer ' || l_customer_id ||  ' does not exist');
        WHEN TOO_MANY_ROWS THEN
            dbms_output.put_line('The database returns more than one customer');    
END;

PL/SQL exception categories

PL/SQL has three exception categories:

  • Internally defined exceptions are errors which arise from the Oracle Database environment. The runtime system raises the internally defined exceptions automatically. ORA-27102 (out of memory) is one example of Internally defined exceptions. Note that Internally defined exceptions do not have names, but an error code.
  • Predefined exceptions are errors which occur during the execution of the program. The predefined exceptions are internally defined exceptions that PL/SQL has given names e.g., NO_DATA_FOUND, TOO_MANY_ROWS.
  • User-defined exceptions are custom exception defined by users like you. User-defined exceptions must be raised explicitly.

The following table illustrates the differences between exception categories.

image.png

PL/SQL Raise Exceptions

Introduction to PL/SQL raise exceptions

To raise an exception explicitly, you use the RAISE statement. The RAISE statement allows you to:

  • Raise a user-defined exception.
  • Raise an internally defined exception.
  • Reraising the current exception.

Raising a user-defined exception

A user-defined exception is defined by users like you or other developers in the declaration section of a block or subprogram.

To define a user-defined exception, you use the following syntax:

1
2
3
DECLARE
    exception_name EXCEPTION;
    PRAGMA EXCEPTION_INIT (exception_name, error_code);

Similar to the variable declaration, you declare an exception in the declaration section of a block.

A user-defined exception must have assigned error_code. In this syntax, the error_code is an integer that ranges from -20,999 to -20,000.

The following example illustrates how to declare a user-defined exception and associate it with an error code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DECLARE
    e_credit_too_high EXCEPTION;
    PRAGMA exception_init( e_credit_too_high, -20001 );
    l_max_credit customers.credit_limit%TYPE;
    l_customer_id customers.customer_id%TYPE := &customer_id;
    l_credit customers.credit_limit%TYPE := &credit_limit;
BEGIN
    -- get the meax credit limit
    SELECT MAX(credit_limit) 
    INTO l_max_credit
    FROM customers;
    
    -- check if input credit is greater than the max credit
    IF l_credit > l_max_credit THEN 
        RAISE e_credit_too_high;
    END IF;
    
    -- if not, update credit limit
    UPDATE customers 
    SET credit_limit = l_credit
    WHERE customer_id = l_customer_id;
    
    COMMIT;
END;

In this example,

  • First, declare a user-defined exception e_credit_too_high and associates it with the error number -20001.
  • Second, select maximum credit from the customers table using the MAX() function and assign this value to the l_max_credit variable.
  • Third, check if the input credit with the maximum credit, if the input credit is greater than the max, then raise the e_credit_too_high exception.
  • Finally, update the customer whose id is entered by the user with the new credit limit.

Here is the output if you enter customer id 100 and credit limit 20000:

1
ORA-20001:

If you want to include a custom message, you can replace the line:

1
RAISE e_credit_too_high;

by the following line:

1
raise_application_error(-20001,'Credit is too high');

And execute the code block again, you will receive the following error:

1
ORA-20001: Credit is too high

Raising an internally defined exception

Typically, the runtime system raises internally defined exceptions implicitly when they occur. Besides, you can explicitly raise an internally defined exception with the RAISE statement if the exception has a name:

1
RAISE exception_name;

This example shows how to raise an internally defined exception INVALID_NUMBER:

1
2
3
4
5
6
7
8
DECLARE
    l_customer_id customers.customer_id%TYPE := &customer_id;
BEGIN
    -- get the max credit limit
    IF l_customer_id < 0 THEN
        RAISE invalid_number;
    END IF;
END;

Reraising the current exception

You can re-raise the current exception with the RAISE statement. Reraising an exception passes it to the enclosing block, which later can be handled further. To reraise an exception, you don’t need to specify the exception name.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
DECLARE
    e_credit_too_high EXCEPTION;
    PRAGMA exception_init( e_credit_too_high, -20001 );
    l_max_credit customers.credit_limit%TYPE;
    l_customer_id customers.customer_id%TYPE := &customer_id;
    l_credit customers.credit_limit%TYPE     := &credit_limit;
BEGIN
    BEGIN
        -- get the max credit limit
        SELECT MAX(credit_limit) 
        INTO l_max_credit
        FROM customers;
        
        -- check if input credit is greater than the max credit
        IF l_credit > l_max_credit THEN 
            RAISE e_credit_too_high;
        END IF;
        EXCEPTION
            WHEN e_credit_too_high THEN
                dbms_output.put_line('The credit is too high' || l_credit);
                RAISE; -- reraise the exception
    END;
EXCEPTION
    WHEN e_credit_too_high THEN
        -- get average credit limit
        SELECT avg(credit_limit) 
        into l_credit
        from customers;
        
        -- adjust the credit limit to the average
        dbms_output.put_line('Adjusted credit to ' || l_credit);
    
        --  update credit limit
        UPDATE customers 
        SET credit_limit = l_credit
        WHERE customer_id = l_customer_id;
   
        COMMIT;
END;

In this example:

  • First, get the max credit limit from the customers table.
  • Second, compare the max credit with the user-input credit. If the user-input credit is greater than the max credit, then raise the e_credit_too_high exception.
  • Third, display a message and reraise the exception in the exception-handling section in the inner block.
  • Finally, in the outer block, reassign the average credit to the l_credit variable and update the customer with the newly adjusted credit.

PL/SQL RAISE_APPLICATION_ERROR

Introduction to raise_application_error procedure

The procedure raise_application_error allows you to issue an user-defined error from a code block or stored program.

By using this procedure, you can report errors to the callers instead of returning unhandled exceptions.

The raise_application_error has the following syntax:

1
2
3
4
5
raise_application_error(
    error_code, 
    message 
    [, {TRUE | FALSE}]
);

In this syntax:

  • The error_code is a negative integer with the range from -20999 to -20000.
  • The message is a character string that represents the error message. Its length is up to 2048 bytes.
  • If the third parameter is FALSE, the error replaces all previous errors. If it is TRUE, the error is added to the stack of previous errors.

The raise_application_error belongs to the package DBMS_STANDARD, therefore, you do not need to qualify references to it.

When the procedure raise_application_error executes, Oracle halts the execution of the current block immediately. It also reverses all changes made to the OUT or IN OUT parameters.

Note that the changes made to the global data structure such as packaged variables, and database objects like tables will not be rolled back. Therefore, you must explicitly execute the ROLLBACK statement to reverse the effect of the DML.

PL/SQL raise_application_error example

Let’s take a look at an example of using the raise_application_error procedure to raise exceptions.

This example uses the raise_application_error procedure to raise an exception with id -20111 and message Credit Limit Exceeded:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
DECLARE
    credit_limit_exceed EXCEPTION;
    PRAGMA exception_init(credit_limit_exceed, -20111);

    l_customer_id     customers.customer_id%TYPE := &customer_id;
    l_credit_limit    customers.credit_limit%TYPE := &credit_limit;

    l_customer_credit customers.credit_limit%TYPE;
    
BEGIN
    -- get customer credit limit
    SELECT credit_limit INTO l_customer_credit
    FROM customers
    WHERE customer_id = l_customer_id;
    
    -- raise an exception if the credit limit is exceeded
    IF l_customer_credit > l_credit_limit THEN
        raise_application_error(-20111,'Credit Limit Exceeded');
    END IF;
    
    dbms_output.put_line('Credit Limit is checked and passed');
    
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Customer with id ' || l_customer_id || ' does not exist.');
END;

In this example:

  • First, declare a user-defined exception credit_limit_exceed associated with the error number -20111.
  • Second, declare two variables l_customer_id and l_credit_limit to store customer id and credit limit entered by users.
  • Third, get the customer credit limit based on the customer id.
  • Finally, check the input credit with the customer credit and use the raise_application_error to raise an exception.

PL/SQL Exception Propagation

Introduction to the exception propagation

When an exception occurs, PL/SQL looks for an exception handler in the current block e.g., anonymous block, procedure, or function of the exception. If it does not find a match, PL/SQL propagates the exception to the enclosing block of the current block.

PL/SQL then attempts to handle the exception by raising it once more in the enclosing block. This process continues in each successive enclosing block until there is no remaining block in which to raise the exception. If there is no exception handler in all blocks, PL/SQL returns an unhandled exception to the application environment that executed the outermost PL/SQL block.

Note that an unhandled exception stops the execution of the block.

Unhandled exception propagation examples

See the following anonymous block:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
DECLARE
    e1 EXCEPTION;
    PRAGMA exception_init (e1, -20001);
    e2 EXCEPTION;
    PRAGMA exception_init (e2, -20002);
    e3 EXCEPTION;
    PRAGMA exception_init (e2, -20003);
    l_input NUMBER := &input_number;
BEGIN
    -- inner block
    BEGIN
        IF l_input = 1 THEN
            raise_application_error(-20001,'Exception: the input number is 1');
        ELSIF l_input = 2 THEN
            raise_application_error(-20002,'Exception: the input number is 2');
        ELSE
            raise_application_error(-20003,'Exception: the input number is not 1 or 2');
        END IF;
    -- exception handling of the inner block
    EXCEPTION
        WHEN e1 THEN 
            dbms_output.put_line('Handle exception when the input number is 1');
    END;
    -- exception handling of the outer block
    EXCEPTION 
        WHEN e2 THEN
            dbms_output.put_line('Handle exception when the input number is 2');
END;

First, execute the block and enter 1 as the input number.

Because the input is 1, the inner block raises the e1 exception. The exception-handling part of the inner block handles the e1 exception locally, therefore, the execution of the block resumes in the enclosing block.

This picture illustrates the process:

image.png

Second, execute the block and enter 2 as the input number.

The inner block raises the e2 exception. Because the inner block does not have an exception handler to handle the e2 exception, PL/SQL propagates the e2 exception to the enclosing block.

The enclosing block has an exception handler that handles e2 exception. Therefore control passes to the host environment (SQL*Plus or SQL Developer).

The following picture illustrates the propagation of the unhandled exception from the inner block to its enclosing block:

image.png

Third, execute the block and enter 3 as the input number.

In this case, both inner block and enclosing block has no exception handler to handle the e3 exception. Therefore, the block returns an unhandled exception to the host environment.

The following picture illustrates the propagation of the unhandled exception from the inner block to its enclosing block, and then host environment:

image.png

Handling other unhandled exceptions

In this exception handling section, you can include the WHEN OTHERS clause to catch any otherwise unhandled exceptions:

1
2
3
4
EXCEPTION
    ...
    WHEN OTHERS
        -- catch other exceptions

Because you handle other non-specific exceptions in the WHEN OTHERS clause, you will need to take advantages of the built-in error functions such as SQLCODE and SQLERRM.

Note that you cannot use SQLCODE or SQLERRM function directly in an SQL statement. Instead, you must first assign their returned values to variables, and then use the variables in the SQL statement.

SQLCODE function

The SQLCODE function accepts no argument and returns a number code of the most recent exception.

If the exceptions are internal, SQLCODE returns a negative number except for the NO_DATA_FOUND exception which has the number code +100.

If the exception is user-defined, SQLCODE returns +1 or the number that you associated with the exception via the pragma EXCEPTION_INIT.

The SQLCODE is only usable in the exception-handling section. If you use the SQLCODE function outside an exception handler, it always returns zero.

This example illustrates how to use the SQLCODE function:

1
2
3
4
5
6
7
8
9
10
11
DECLARE
    l_code NUMBER;
    r_customer customers%rowtype;
BEGIN
    SELECT * INTO r_customer FROM customers;
    
    EXCEPTION 
        WHEN OTHERS THEN
        l_code := SQLCODE;  
        dbms_output.put_line('Error code:' || l_code);
END;

In this example, we try to fetch too many rows into a record, which results in a error with the following error code:

1
Error code:-1422

SQLERRM function

The function SQLERRM takes an argument as an error code and returns the error message associated with that error code:

1
SQLERRM([error_code])

In this syntax, the error_code can be any valid Oracle error code.

If you omit the error_code argument, the function will return the error message associated with the current value of SQLCODE.

Note that the SQLERRM function with no argument is only useful in an exception handler.

This example illustrates how to use the function SQLERRM in an exception handler:

1
2
3
4
5
6
7
8
9
10
11
DECLARE
    l_msg VARCHAR2(255);
    r_customer customers%rowtype;
BEGIN
    SELECT * INTO r_customer FROM customers;
    
    EXCEPTION 
        WHEN OTHERS THEN
        l_msg := SQLERRM;  
        dbms_output.put_line(l_msg);
END;

Here is the output:

1
ORA-01422: exact fetch returns more than requested number of rows

Using SQLCODE and SQLERRM functions example

The following example inserts a new contact into the contacts table in the sample database. It has an exception handler in the WHERE OTHERS clause of the exception handling section.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
DECLARE
    l_first_name  contacts.first_name%TYPE := 'Flor';
    l_last_name   contacts.last_name%TYPE := 'Stone';
    l_email       contacts.email%TYPE := 'flor.stone@raytheon.com';
    l_phone       contacts.phone%TYPE := '+1 317 123 4105';
    l_customer_id contacts.customer_id%TYPE := -1;
BEGIN
    -- insert a new contact
    INSERT INTO contacts(first_name, last_name, email, phone, customer_id)
    VALUES(l_first_name, l_last_name, l_email, l_phone, l_customer_id);
    
    EXCEPTION 
        WHEN OTHERS THEN
            DECLARE
                l_error PLS_INTEGER := SQLCODE;
                l_msg VARCHAR2(255) := sqlerrm;
            BEGIN
                CASE l_error 
                WHEN -1 THEN
                    -- duplicate email
                    dbms_output.put_line('duplicate email found ' || l_email);
                    dbms_output.put_line(l_msg);
                    
                WHEN -2291 THEN
                    -- parent key not found
                    dbms_output.put_line('Invalid customer id ' || l_customer_id);
                    dbms_output.put_line(l_msg);
                END CASE;
                -- reraise the current exception
                RAISE;
            END;
            
END;

In this example, the exception handler traps two exceptions -1 (duplicate email ) and -2291 (parent key not found). It shows a custom message and reraises the exception using the RAISE statement.

PL/SQL Cursor

Introduction to PL/SQL cursors

A cursor is a pointer that points to a result of a query. PL/SQL has two types of cursors: implicit cursors and explicit cursors.

Implicit cursors

Whenever Oracle executes an SQL statement such as SELECT INTO, INSERT, UPDATE, and DELETE, it automatically creates an implicit cursor.

Oracle internally manages the whole execution cycle of implicit cursors and reveals only the cursor’s information and statuses such as SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND, and SQL%NOTFOUND.

The implicit cursor is not elegant when the query returns zero or multiple rows which cause NO_DATA_FOUND or TOO_MANY_ROWS exception respectively.

Explicit cursors

An explicit cursor is an SELECT statement declared explicitly in the declaration section of the current block or a package specification.

For an explicit cursor, you have control over its execution cycle from OPEN, FETCH, and CLOSE.

Oracle defines an execution cycle that executes an SQL statement and associates a cursor with it.

Let’s examine each step in detail.

Declaring a cursor

Before using an explicit cursor, you must declare it in the declaration section of a block or package as follows:

1
CURSOR cursor_name IS query;

In this syntax:

  • First, specify the name of the cursor after the CURSOR keyword.
  • Second, define a query to fetch data after the IS keyword.

Opening a cursor

Before start fetching rows from the cursor, you must open it. To open a cursor, you use the following syntax:

1
OPEN cursor_name;

In this syntax, the cursor_name is the name of the cursor declared in the declaration section.

When you open a cursor, Oracle parses the query, binds variables, and executes the associated SQL statement.

Oracle also determines an execution plan, associates host variables and cursor parameters with the placeholders in the SQL statement, determines the result set, and sets the cursor to the first row in the result set.

Fetching from a cursor

The FETCH statement places the contents of the current row into variables. The syntax of FETCH statement is as follows:

1
FETCH cursor_name INTO variable_list;

To retrieve all rows in a result set, you need to fetch each row till the last one.

Closing a cursor

After fetching all rows, you need to close the cursor with the CLOSE statement:

1
CLOSE cursor_name;

Closing a cursor instructs Oracle to release allocated memory at an appropriate time.

If you declare a cursor in an anonymous block, procedure, or function, the cursor will automatically be closed when the execution of these objects end.

However, you must explicitly close package-based cursors. Note that if you close a cursor that has not opened yet, Oracle will raise an INVALID_CURSOR exception.

Attributes

A cursor has four attributes to which you can reference in the following format:

1
cursor_name%attribute

where cursor_name is the name of the explicit cursor.

  1. %ISOPEN

    This attribute is TRUE if the cursor is open or FALSE if it is not.

  2. %FOUND

    This attribute has four values:

    • NULL before the first fetch
    • TRUE if a record was fetched successfully
    • FALSE if no row returned
    • INVALID_CURSOR if the cursor is not opened

  3. %NOTFOUND

    • NULL before the first fetch
    • FALSE if a record was fetched successfully
    • TRUE if no row returned
    • INVALID_CURSOR if the cursor is not opened

  4. %ROWCOUNT

    The %ROWCOUNT attribute returns the number of rows fetched from the cursor. If the cursor is not opened, this attribute returns INVALID_CURSOR.

PL/SQL cursor example

We will use the orders and order_items tables from the sample database for the demonstration.

image.png

The following statement creates a view that returns the sales revenues by customers:

1
2
3
4
5
6
7
8
CREATE VIEW sales AS
SELECT customer_id,
       SUM(unit_price * quantity) total,
       ROUND(SUM(unit_price * quantity) * 0.05) credit
FROM order_items
INNER JOIN orders USING (order_id)
WHERE status = 'Shipped'
GROUP BY customer_id;

The values of the credit column are 5% of the total sales revenues.

Suppose you need to develop an anonymous block that:

  1. Reset credit limits of all customers to zero.
  2. Fetch customers sorted by sales in descending order and gives them new credit limits from a budget of 1 million.

The following anonymous block illustrates the logic:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
DECLARE
  l_budget NUMBER := 1000000;
   -- cursor
  CURSOR c_sales IS
  SELECT  *  FROM sales  
  ORDER BY total DESC;
   -- record    
   r_sales c_sales%ROWTYPE;
BEGIN

  -- reset credit limit of all customers
  UPDATE customers SET credit_limit = 0;

  OPEN c_sales;

  LOOP
    FETCH  c_sales  INTO r_sales;
    EXIT WHEN c_sales%NOTFOUND;

    -- update credit for the current customer
    UPDATE 
        customers
    SET  
        credit_limit = 
            CASE WHEN l_budget > r_sales.credit 
                        THEN r_sales.credit 
                            ELSE l_budget
            END
    WHERE 
        customer_id = r_sales.customer_id;

    --  reduce the budget for credit limit
    l_budget := l_budget - r_sales.credit;

    DBMS_OUTPUT.PUT_LINE( 'Customer id: ' ||r_sales.customer_id || 
' Credit: ' || r_sales.credit || ' Remaining Budget: ' || l_budget );

    -- check the budget
    EXIT WHEN l_budget <= 0;
  END LOOP;

  CLOSE c_sales;
END;

To be continued …

References

Oracle Tutorial PL/SQL Tutorial

Buy a cup of coffee for the author.