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 CASE statements support an optional ELSE clause.

Simple PL/SQL 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:

CASE selector
WHEN selector_value_1 THEN
    statements_1
WHEN selector_value_1 THEN 
    statement_2
...
ELSE
    else_statements
END CASE;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Let’s examine the syntax of the simple CASE statement in detail:

1) selector

The selector is an expression that 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.

2) WHEN selector_value THEN statements

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.

3) ELSE else_statements

If no values in WHERE 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:

ELSE 
    RAISE CASE_NOT_FOUND;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In other words, PL/SQL raises a CASE_NOT_FOUND error if you don’t specify an ELSE clause and the result of the CASE expression does not match any value in the WHEN clauses.

Note that this behavior of the CASE statement is different from the IF THEN statement. When the IF THEN statement has no ELSE clause and the condition is not met, PL/SQL does nothing instead raising an error.

Simple CASE statement example

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

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;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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:

CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
...
WHEN condition_n THEN statements_n
[ ELSE
  else_statements ]
END CASE;]
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The searched CASE statement follows the rules below:

• The conditions in the WHEN clauses 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.

Searched CASE statement example

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

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the sales revenue was set to 150,000. The first expression evaluated to FALSE:

n_sales    > 200000
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

But the second expression evaluates to TRUE and the sale commission was set to 15%:

n_commission := 0.15;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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.

Simple or searched CASE statement

As a rule of thumb, use a searched CASE statement when you want to execute a sequence of statements based on the results of multiple Boolean expressions and use a simple CASE statement when you want to execute a sequence of statements based on the result of a single expression.

PL/SQL CASE statement vs. CASE expression

PL/SQL also has CASE expression which is similar to the CASE statement.

A CASE expression evaluates a list of conditions and returns one of multiple possible result expressions.

The result of a CASE expression is a single value whereas the result of a CASE statement is the execution of a sequence of statements.

PL/SQL IF introduction

The IF statement allows you to either execute or skip a sequence of statements, depending on a condition. The IF statement has 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:

IF condition THEN
    statements;
END IF;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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.

PL/SQL IF THEN statement example

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

DECLARE n_sales NUMBER := 2000000; 
BEGIN 
   IF n_sales > 100000 THEN 
      DBMS_OUTPUT.PUT_LINE( 'Sales revenue is greater than 100K ' ); 
   END IF; 
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Tip # 1: Avoid clumsy IF statement

Consider the following example:

DECLARE
  b_profitable BOOLEAN;
  n_sales      NUMBER;
  n_costs      NUMBER;
BEGIN
  b_profitable := false;   
  IF n_sales > n_costs THEN
    b_profitable := true;
  END IF;
END;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the IF statement determines whether the sales revenue is higher than the cost and updates the b_profitable variable accordingly.

This IF statement called a clumsy IF statement because you can assign the result of a Boolean expression directly to a Boolean variable as follows:

b_profitable := n_sales > n_costs;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Tip #2: Avoid evaluating Boolean variables

A Boolean variable is always TRUE, FALSE, or NULL. Therefore the following comparison is unnecessary:

IF b_profitable = TRUE THEN
   DBMS_OUTPUT.PUT_LINE( 'This sales deal is profitable' );
END IF;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Instead, use:

IF b_profitable THEN
   DBMS_OUTPUT.PUT_LINE( 'This sales deal is profitable' );
END IF;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PL/SQL IF THEN ELSE statement

The IF THEN ELSE statement has the following structure:

IF condition THEN
    statements;
ELSE
    else_statements;
END IF;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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.

IF THEN ELSE statement example

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%.

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;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PL/SQL IF THEN ELSIF statement

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

IF condition_1 THEN
  statements_1
ELSIF condition_2 THEN
  statements_2
[ ELSIF condition_3 THEN
    statements_3
]
...
[ ELSE
    else_statements
]
END IF;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this structure, the condition between IF and THEN, which is the first condition, is always evaluated. Each other condition between ELSEIF 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 ELSE clause and no condition is TRUE, then the IF THEN ELSIF does nothing

IF THEN ELSIF statement example

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

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;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Nested IF statement

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

IF condition_1 THEN
    IF condition_2 THEN
        nested_if_statements;
    END IF;
ELSE
    else_statements;
END IF; 
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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 procedure syntax

A PL/SQL procedure is a reusable unit that encapsulates the specific business logic of the application. Technically speaking, a PL/SQL procedure is a named block stored as a schema object in the Oracle Database.

The following illustrates the basic syntax of creating a procedure in PL/SQL:

CREATE [OR REPLACE ] PROCEDURE procedure_name (parameter_list)     
IS
    

[declaration statements]

BEGIN

[execution statements]

EXCEPTION [exception handler] END [procedure_name ];Code language: SQL (Structured Query Language) (sql)

PL/SQL procedure header

A procedure begins with a header that specifies its name and an optional parameter list.

Each parameter can be in either IN, OUT, or INOUT mode. The parameter mode specifies whether a parameter can be read from or written to.

IN

An IN parameter is read-only. You can reference an IN parameter inside a procedure, but you cannot change its value. Oracle uses IN as the default mode. It means that if you don’t specify the mode for a parameter explicitly, Oracle will use the IN mode.

OUT

An OUT parameter is writable. Typically, you set a returned value for the OUT parameter and return it to the calling program. Note that a procedure ignores the value that you supply for an OUT parameter.

INOUT

An INOUT parameter is both readable and writable. The procedure can be read and modified.

Note that OR REPLACE option allows you to overwrite the current procedure with the new code.

PL/SQL procedure body

Similar to an anonymous block, the procedure body has three parts. The executable part is mandatory whereas the declarative and exception-handling parts are optional. The executable part must contain at least one executable statement.

1) Declarative part

In this part, you can declare variables, constants, cursors, etc. Unlike an anonymous block, a declaration part of a procedure does not start with the DECLARE keyword.

2) Executable part

This part contains one or more statements that implement specific business logic. It might contain only a NULL statement.

3) Exception-handling part

This part contains the code that handles exceptions.

Creating a PL/SQL procedure example

The following procedure accepts a customer id and prints out the customer’s contact information including first name, last name, and email:

CREATE OR REPLACE PROCEDURE print_contact(
    in_customer_id NUMBER 
)
IS
  r_contact contacts%ROWTYPE;
BEGIN
  -- get contact based on customer id
  SELECT *
  INTO r_contact
  FROM contacts
  WHERE customer_id = p_customer_id;

  -- print out contact's information
  dbms_output.put_line( r_contact.first_name || ' ' ||
  r_contact.last_name || '<' || r_contact.email ||'>' );

EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line( SQLERRM );
END;
Code language: SQL (Structured Query Language) (sql)

To compile the procedure, you click the Run Statement button as shown in the following picture:

If the procedure is compiled successfully, you will find the new procedure under the Procedures node as shown below:

Executing a PL/SQL procedure

The following shows the syntax for executing a procedure:

EXECUTE procedure_name( arguments);Code language: SQL (Structured Query Language) (sql)

Or

EXEC procedure_name( arguments);Code language: SQL (Structured Query Language) (sql)

For example, to execute the print_contact procedure that prints the contact information of customer id 100, you use the following statement:

EXEC print_contact(100);Code language: SQL (Structured Query Language) (sql)

Here is the output:

Sanjay Shonak<sanjay.shonak@gmail.com>

You can also execute a procedure from the Oracle SQL Developer using the following steps:

1)  Right-click the procedure name and choose Run… menu item

2) Enter a value for the  in_customer_id parameter and click the OK button.

3) The following shows the result

Connecting to the database Local.
Sanjay Shonak<sanjay.shonak@gmail.com>
Process exited.
Disconnecting from the database Local.

Editing a procedure

To change the code of an existing procedure, you can follow these steps:

• Step 1. Click the procedure name under the Procedures node.
• Step 2. Edit the code of the procedure.
• Step 3. Click the Compile menu option to recompile the procedure.

Removing a procedure

To delete a procedure, you use the DROP PROCEDURE followed by the procedure’s name that you want to drop as shown in the following syntax:

DROP PROCEDURE procedure_name; Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement drops the print_contact procedure :

DROP PROCEDURE print_contact;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following illustrates the steps of dropping a procedure using SQL Developer:

• Step 1. Right-click on the procedure name that you want to drop
• Step 2. Choose the Drop… menu option
• Step 3. In the Prompts dialog, click the Apply button to remove the procedure.

Introduction to Oracle Cursor FOR UPDATE

Sometimes, you want to lock a set of rows before you can update them in your program. Oracle provides the FOR UPDATE clause of the SELECT statement in an updatable cursor to perform this kind of locking mechanism.

Here is the syntax for declaring an updatable cursor:

CURSOR cursor_name IS
    SELECT select_clause
    FROM from_clause
    WHERE where_clause
    FOR UPDATE;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The new syntax here is the FOR UPDATE keywords.

Once you open the cursor, Oracle will lock all rows selected by the SELECT ... FOR UPDATE statement in the tables specified in the FROM clause. And these rows will remain locked until the cursor is closed or the transaction is completed with either COMMIT or ROLLBACK.

Note that Oracle locks all rows returned by the SELECT ... FOR UPDATE during the update, therefore, you should have a WHERE clause to select only necessary rows to be locked.

If you have a specific column that you want to update, you can list it in the FOR UPDATE clause as follows:

CURSOR cursor_name IS
    SELECT select_clause
    FROM from_clause
    WHERE where_clause
    FOR UPDATE OF column_name;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this case, Oracle only locks rows of the table that has the column name listed in the FOR UPDATE OF clause.

Note that if you use only FOR UPDATE clause and do not include one or more column after the OF keyword, Oracle will then lock all selected rows across all tables listed in the FROM clause.

Oracle Cursor FOR UPDATE example

Consider the following example.

DECLARE
    -- customer cursor
    CURSOR c_customers IS 
        SELECT 
            customer_id, 
            name, 
            credit_limit
        FROM 
            customers
        WHERE 
            credit_limit > 0 
        FOR UPDATE OF credit_limit;
    -- local variables
    l_order_count PLS_INTEGER := 0;
    l_increment   PLS_INTEGER := 0;
    
BEGIN
    FOR r_customer IN c_customers
    LOOP
        -- get the number of orders of the customer
        SELECT COUNT(*)
        INTO l_order_count
        FROM orders
        WHERE customer_id = r_customer.customer_id;
        -- 
        IF l_order_count >= 5 THEN
            l_increment := 5;
        ELSIF l_order_count < 5 AND l_order_count >=2 THEN
            l_increment := 2;
        ELSIF l_increment = 1 THEN
            l_increment := 1;
        ELSE 
            l_increment := 0;
        END IF;
        
        IF l_increment > 0 THEN
            -- update the credit limit
            UPDATE 
                customers
            SET 
                credit_limit = credit_limit * ( 1 +  l_increment/ 100)
            WHERE 
                customer_id = r_customer.customer_id;
            
            -- show the customers whose credits are increased
            dbms_output.put_line('Increase credit for customer ' 
                || r_customer.NAME || ' by ' 
                || l_increment || '%' );
        END IF;
    END LOOP;
    
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Error code:' || SQLCODE);
            dbms_output.put_line('Error message:' || sqlerrm);
            RAISE;
            
END;
/
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How it works.

• First, declare an updatable cursor that updates credits of the customers whose credits are greater than zero.
• Next, loop over the rows in the cursors.
• Then, get the number of orders for each customer.
• After that, assign the credit increment based on the order count.
• Finally, update the credit of the customer.

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 a 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.

The following illustration shows the execution cycle of an explicit cursor:

Let’s examine each step in detail.

Declare a cursor

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

CURSOR cursor_name IS query;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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.

Open a cursor

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

OPEN cursor_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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.

More about parameterized cursor in the next tutorial.

Fetch from a cursor

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

FETCH cursor_name INTO variable_list;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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:

CLOSE cursor_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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 ends.

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.

Explicit Cursor Attributes

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

cursor_name%attributeCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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 is returned
• INVALID_CURSOR if the cursor is not opened

3) %NOTFOUND

This attribute has four values:

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

3) %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.

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

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;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

Suppose you need to develop an anonymous block that:

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

The following anonymous block illustrates the logic:

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In the declaration section, we declare three variables.

The first one is l_budget whose initial value is 1,000,000.

The second variable is an explicit cursor variable named c_sales whose SELECT statement retrieves data from the sales view:

CURSOR c_sales IS
      SELECT  *  FROM sales  
      ORDER BY total DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The third variable is a cursor-based record named c_sales.

In the execution section, we perform the following:

• First, reset the credit limits of all customers to zero using an UPDATE statement.
• Second, open the c_sales cursor.
• Third, fetch each row from the cursor. In each loop iteration, we updated the credit limit and reduced the budget. The loop terminates when there is no row to fetch or the budget is exhausted.
• Finally, close the cursor.

The following query retrieves data from the  customers table to verify the update:

SELECT customer_id,
       name,
       credit_limit
FROM customers
ORDER BY credit_limit DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Result:

As you can see clearly from the result, only the first few customers have credit limits. If you sum up all credit limits, the total should be 1 million as shown follows:

SELECT
  SUM( credit_limit )
FROM
  customers;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

SUM(CREDIT_LIMIT)
-----------------
          1000000

PL/SQL SELECT INTO

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:

SELECT
  select_list
INTO
  variable_list
FROM
  table_name
WHERE
  condition; Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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.

1) 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.

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( l_customer_name );
END;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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’s name.
• Second, use the SELECT INTO statement to select a value from the name column and assign it to the l_customer_name variable.
• Third, show the customer’s name using the dbms_output.put_line procedure.

Because the customers table has only one row with customer ID 100, the code block displays the customer name.

VerizonCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If there were no such row, the code block would fail with an unhandled NO_DATA_FOUND exception.

2) Selecting a complete row example

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

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here is the output:

Verizon, website: http://www.verizon.comCode language: JavaScript (javascript)

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.

3) Selecting data into multiple variables example

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

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Oracle issued the following output:

Verizon, Contact Person: Elisha LloydCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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 the 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 SELECT INTO common errors

If the number of columns and expressions in the SELECT clause is greater than the number of variables in the INTO clause, Oracle issues this error:

ORA-00947: not enough values The INTO list contains fewer variables than the SELECT list.Code language: PHP (php)

Oracle issues the following error if the number of columns and expressions in the SELECT clause is less than the number of variables in the INTO clause:

ORA-00913: too many values The INTO list contains more variables than the SELECT list.Code language: PHP (php)

If the number of variables and elements in the select list are the same, but their corresponding datatypes are not compatible, Oracle cannot implicitly convert from one type to the other. It will issue the following error:

ORA-06502: PL/SQL: numeric or value errorCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Now, you should know how to use the PL/SQL SELECT INTO statement to fetch a single row from a table into variables.

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:

CONTINUE;Code language: SQL (Structured Query Language) (sql)

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:

IF condition THEN
    CONTINUE;
END IF;Code language: SQL (Structured Query Language) (sql)

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

PL/SQL CONTINUE statement example

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

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;Code language: SQL (Structured Query Language) (sql)

The output is:

2
4
6
8
10Code language: SQL (Structured Query Language) (sql)

PL/SQL CONTINUE WHEN statement

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

The syntax of CONTINUE WHEN statement is:

CONTINUE WHEN condition;Code language: SQL (Structured Query Language) (sql)

The condition of 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:

IF condition THEN
    CONTINUE;
END IF;Code language: SQL (Structured Query Language) (sql)

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

PL/SQL CONTINUE statement example

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

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;Code language: SQL (Structured Query Language) (sql)

Here is the output:

1
3
5
7
9Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to exit the current loop iteration and continue to the next one using the PL/SQL CONTINUE or CONTINUE WHEN statement.

Introduction to PL/SQL WHILE loop statement

PL/SQL WHILE loop is a control structure that repeatedly executes a code block as long as a specific condition remains true.

Here’s the syntax for the WHILE loop statement:

WHILE condition
LOOP
    statements;
END LOOP;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the condition 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 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. If the condition 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.

1) Simple PL/SQL WHILE loop example

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

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;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here is the output:

Counter : 1
Counter : 2
Counter : 3
Counter : 4
Counter : 5Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

• First, initialize the counter to one.
• Second, the condition in the WHILE clause was evaluated before each loop iteration.
• Third, inside the loop body, the counter was increased by one in each loop iteration. After five iterations, the condition was FALSE that caused the loop to terminate.

2) WHILE loop example terminated by EXIT WHEN statement

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

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;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following is the output:

Counter : 1
Counter : 2Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The condition of the EXIT WHEN clause evaluated to true when the counter is three. Therefore, the loop body was only executed two times before termination.

In this tutorial, you have learned how to use the PL/SQL WHILE loop statement to execute a sequence of statements as long as a specified condition is TRUE.

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:

FOR index IN lower_bound .. upper_bound
LOOP 
    statements; 
END LOOP;Code language: SQL (Structured Query Language) (sql)

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 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.

A) 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.

BEGIN
  FOR l_counter IN 1..5
  LOOP
    DBMS_OUTPUT.PUT_LINE( l_counter );
  END LOOP;
END;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

1
2
3
4
5
Code language: SQL (Structured Query Language) (sql)

B) 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:

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;Code language: SQL (Structured Query Language) (sql)

Result:

2
4
6
8
10
Code language: SQL (Structured Query Language) (sql)

The result shows that, after each loop iteration, the output number is incremented by two instead of one.

C) Referencing variable with the same name as the loop index

Consider the following example:

DECLARE
  l_counter PLS_INTEGER := 10;
BEGIN
  FOR l_counter IN 1.. 5 loop
    DBMS_OUTPUT.PUT_LINE (l_counter);
  end loop;
  -- after the loop
  DBMS_OUTPUT.PUT_LINE (l_counter);
END; 
Code language: SQL (Structured Query Language) (sql)

Here is the result:

1
2
3
4
5
10Code language: SQL (Structured Query Language) (sql)

In this example, we had a variable named l_counter, which is also the name of the index. The result shows that l_counter in the FOR loop hides the variable l_counter declared in the enclosing block.

To reference the variable l_counter inside the loop, you must qualify it using a block label as shown below:

<<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;
Code language: SQL (Structured Query Language) (sql)

D) Referencing loop index outside the FOR LOOP

The following example causes an error because it references the loop index, which is undefined, outside the FOR LOOP statement.

BEGIN
  FOR l_index IN 1..3 loop
    DBMS_OUTPUT.PUT_LINE (l_index);
  END LOOP;
  -- referencing index after the loop
  DBMS_OUTPUT.PUT_LINE (l_index);
END;
Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error:

PLS-00201: identifier 'L_INDEX' must be declared
Code language: SQL (Structured Query Language) (sql)

FOR LOOP with REVERSE keyword

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

FOR index IN REVERSE lower_bound .. upper_bound
    LOOP 
    statements; 
END LOOP;
Code language: SQL (Structured Query Language) (sql)

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:

BEGIN
  FOR l_counter IN REVERSE 1..3
  LOOP
    DBMS_OUTPUT.PUT_LINE( l_counter );
  END LOOP;
END;
Code language: SQL (Structured Query Language) (sql)

Result:

3
2
1
Code language: SQL (Structured Query Language) (sql)

Without the REVERSE keyword, the output will be:

1
2
3
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the PL/SQL FOR LOOP statement to execute a sequence of statements a specified number of times.

Introduction to PL/SQL LOOP statement

The PL/SQL LOOP statement is a control structure that repeatedly executes a block of code until a specific condition is met or until you manually exit the loop.

Here’s the syntax of the PL/SQL LOOP statement:

<<label>> LOOP
    statements;
END LOOP loop_label;Code language: SQL (Structured Query Language) (sql)

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 of 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.

LOOP
    EXIT;
END LOOP; 
Code language: SQL (Structured Query Language) (sql)

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

LOOP
    IF condition THEN
        EXIT;
    END IF;
END LOOP;
Code language: SQL (Structured Query Language) (sql)

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

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;Code language: SQL (Structured Query Language) (sql)

Here is the output:

Inside loop: 1
Inside loop: 2
Inside loop: 3
After loop: 4
Code language: SQL (Structured Query Language) (sql)

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 to 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:

EXIT WHEN condition;Code language: SQL (Structured Query Language) (sql)

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.

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;Code language: SQL (Structured Query Language) (sql)

Notice that this example is logically equivalent to the example that uses the EXIT statement above.

Constructing nested loops using PL/SQL LOOP statements

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

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;Code language: SQL (Structured Query Language) (sql)

Here is the output:

Outer counter 1
 Inner counter 1
 Inner counter 2
 Inner counter 3
Outer counter 2
 Inner counter 1
 Inner counter 2
 Inner counter 3Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the PL/SQL LOOP statement to repeatedly execute a block of code until a condition is met.