Infolinks

Wednesday 20 February 2013

PL/SQL CHAPTER 2



CHAPTER 2

PL/SQL BASICS:

CONTROL STRUCTURES, CURSORS AND EXCEPTIONS

------------------------------------------------------------------------------------------------------------


OBJECTIVES

In this chapter, we will briefly cover the following topics:

  • Conditional controls
    • IF-THEN
    • IF-THEN-ELSE, and
    • IF-THEN-ELSIF
  • Iterative controls
    • Simple loops
    • WHILE loops
    • FOR loops
  • Do nothing structure
  • GOTO statement and labels
  • Cursor Manipulation.
  • Using Cursor For Loops.
  • Using Parameters with Cursors.
  • Exception Handling.
  • Cursor variables

CONTROL STRUCTURES

According to the structure theorem, any computer program can be written using the basic control structures, which can be combined in any way necessary to deal with a given problem.

The selection structure tests a condition, and then executes one sequence of statements instead of another, depending on whether the condition is true or false. A condition is any variable or expression that returns a Boolean value (TRUE, FALSE, or NULL).

The iteration structure executes a sequence of statements repeatedly as long as a condition holds true.

The sequence structure simply executes a sequence of statements in the order in which they occur.

 

CONDITIONAL CONTROL

Conditional control allows you to control the flow of the execution of the program based on a condition. In programming terms, it means that the statements in the program are not executed sequentially. Rather, one group of statements, or another will be executed, depending on how the condition is evaluated.

The IF statement lets you execute a sequence of statements conditionally. That is, whether the sequence is executed or not depends on the value of a condition. There are three forms of IF statements - IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF.

IF-THEN

This construct tests a simple condition. If the condition evaluates to TRUE, one or more lines of code are executed. If the condition evaluates to FALSE, program control is passed to the next statement after the test. The following code illustrates implementing this logic in PL/SQL.

If varl > 10 then
     var2 := varl + 20;
END IF;

The test, in this case ">", is a relational operator we discussed in the "PL/SQL­ Character Set”' section. The statement could have been using the following instead with the same result.

IF NOT(varl <= 10) THEN
     var2 := varl + 20;
END IF;

You may code nested IF-THEN statements as shown in the following.

IF varl > 10 THEN
     IF var2 < varl THEN
          var2 := varl + 20;
     END IF;
END IF;

Notice that there are two END IF in the above example ‑ one for each IF. This leads us into two rules about implementing IF logic in PL/SQL:

  1. Each IF statement is followed by its own THEN. There is no semicolon (;) terminator on the line that starts with IF.
  2. Each IF statement block is terminated by a matching END IF.

IF-THEN-ELSE

This construct is similar to IF, except that when the condition evaluates to FALSE, one or more statements following the ELSE are executed. The following code illustrates implementing this logic in PL/SQL.

IF varl > 10 THEN
     var2 := varl + 20;
ELSE
     var2 := varl * varl;
END IF;

Note that the same logic can be expressed in the other way ‑ adding 20 to varl with the ELSE and squaring varl with the IF branch of the statement.

IF varl <= 10 THEN
     var2 := varl * varl;
ELSE
     var2 := varl + 20;
END IF;

This statement can be nested also, as shown below.

IF varl > 10 THEN
     var2 := varl + 20;
ELSE
     IF varl BETWEEN 7 AND 8 THEN
          var2 := 2 * varl;
     ELSE
          var2 := varl * varl;
     END IF;
END IF;


This leads us to two more rules about implementing if logic in PL/SQL:

  1. There can be one and only one ELSE with every IF statement.
  2. There is no semicolon (;) terminator after ELSE.

IF-THEN-ELSIF

This format is an alternative to using the nested IF-THEN‑ELSE construct. The code in the previous listing could be reworded to read:

IF varl > 10 THEN
     var2 := varl + 20;
ELSIF varl BETWEEN 7 AND 8 THEN
     var2 := var2 * varl;
ELSE
     var2 := varl * varl;
END IF;


NOTE : The third form of IF statement uses the keyword ELSIF (NOT ELSEIF) to introduce additional conditions.

This leads us to one final rule about implementing IF logic in PL/SQL.

  1. There is no matching END IF with each ELSIF.

In the following code segment, the END IF appears to go with its preceding ELSIF:

IF varl > 10 THEN
     var2 := varl + 20;
ELSIF varl BETWEEN 7 AND 8 THEN
     var2 := 2 * varl;
END IF;


In fact, the END IF belongs to the IF that starts the whole block rather than the ELSIF keyword.

ITERATIVE CONTROL

LOOP statements let you execute a sequence of statements multiple times. There are three forms of LOOP statements: LOOP, WHILE-LOOP, and FOR-LOOP.

LOOP

The simplest form of LOOP statement is the basic (or infinite) loop, which encloses a sequence of statements between the keywords LOOP and END LOOP, as follows:

LOOP
     statement1;
     statement2;
     statement3;
     ...
END LOOP;


All the sequence of statements is executed for each iteration of the loop. Then, the control resumes at the top of the loop and the cycle starts again. If further processing is undesirable or impossible, you can use the EXIT statement to complete the loop. You can place one or more EXIT statements anywhere inside a loop, but nowhere outside a loop.

There are two forms of EXIT statements: EXIT and EXIT-WHEN.

The EXIT statement forces a loop to complete unconditionally. When an EXIT statement is encountered, the loop completes immediately and the control is passed to the next statement after the loop.

LOOP
    
     IF … THEN
         
          EXIT; -- exit loop immediately
     END IF;
END LOOP;
-- control resumes here


The EXIT-WHEN statement allows a loop to complete conditionally. When the EXIT statement is encountered, the condition in the WHEN clause is evaluated. If the condition evaluates to TRUE, the loop completes and the control is passed to the next statement after the loop.

LOOP
     FETCH c1 INTO …
     EXIT WHEN c1%NOTFOUND;  -- exit loop if condition is true
     ...
END LOOP;

Until the condition evaluates to TRUE, the loop cannot complete. So, statements within the loop must change the value of the condition.

Like the PL/SQL blocks, loops can be labeled. The label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the LOOP statement, as follows:

<<label_name>>
LOOP
     statement1;
     statement2;
     statement3;
     ...
END LOOP [label_name];


Optionally, the label name can also appear at the end of the LOOP statement.
With either form of EXIT statement, you can complete not only the current loop, but also any enclosing loop. Simply label the enclosing loop that you want to complete, and then use the label in an EXIT statement.

<<outer>>
LOOP
     LOOP
         
          EXIT outer WHEN … -- exit both loops
     END LOOP;
END LOOP outer;

       
WHILE-LOOP

A WHILE loop has the following structure:

WHILE <condition> LOOP
     statement 1;
     statement 2;
     statement 3;
     ...
     statement N;
END LOOP;


The reserved word WHILE marks the beginning of a loop construct. The word “<condition>” is the test condition of the loop that evaluates to TRUE or FALSE. The result of this evaluation determines whether the loop is executed. Statements 1 through N are a sequence of statements that is executed repeatedly. The END LOOP is a reserved phrase that indicates the end of the loop construct. The following is an example of using WHILE LOOP.

DECLARE
     v_counter         NUMBER := 1;
BEGIN
     WHILE v_counter < 5 LOOP
          DBMS_OUTPUT.PUT_LINE('v_counter = ' || v_counter);
          -- increment the value of v_counter by one
           v_counter := v_counter + 1;
     END LOOP;
END;
/

While the test condition of the loop must evaluate to TRUE at least once for the statements in the loop to execute, it is important to ensure that the test condition will eventually evaluate to FALSE as well. Otherwise, the WHILE loop will execute continually.

DECLARE
     v_counter         NUMBER := 1;
BEGIN
     WHILE v_counter < 5 LOOP
          DBMS_OUTPUT.PUT_LINE('v_counter = ' || v_counter);
          -- decrement the value of v_counter by one
           v_counter := v_counter - 1;
     END LOOP;
END;
/

The above code shows an example of the infinite WHILE loop. The test condition always evaluates to TRUE because the value of v_counter is decremented by 1, which is always less than 5.

FOR-LOOP

Whereas the number of iteration through a WHILE loop is unknown until the loop completes, the number of iterations through a FOR loop is known before the loop is entered. FOR loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP.

FOR counter IN [REVERSE] lower_bound..upper_bound LOOP
     statement 1;
     statement 2;
     statement 3;
     ...
     statement N;
END LOOP;

 
The lower bound may not be 1. However, the loop counter increment (or decrement) must be 1. Between the lower bound and the upper bound is a double dot (..), which serves as the range operator.

PL/SQL lets you determine the loop range dynamically at run time, as the following example shows:

SET SERVEROUTPUT ON
DECLARE
     cnt_employee   NUMBER;
BEGIN
     SELECT COUNT(*) INTO cnt_employee FROM employee;
     FOR v_counter IN 1..cnt_employee LOOP          
           DBMS_OUTPUT.PUT_LINE('v_counter = ' || v_counter);
     END LOOP;
END;
/

The loop counter is defined only within the loop. You cannot reference it outside the loop. You need not explicitly declare the loop counter (i.e. v_counter) because it is implicitly declared as a local variable of type INTEGER.

You can also use an EXIT statement inside a FOR loop to make it complete prematurely.

 


Occasionally, we create an IF statement without any logic within it.  It happens under those situations where the way it is presented makes more sense to the reader, or there is a chance that some code will be added within it in the future. To handle this situation, a NULL construct can be used, as illustrated in the following example:

IF var_count <= 90 THEN
     NULL; -- do nothing
ELSIF var_count > 90 AND var_count <= 110 THEN
    
END IF;

GOTO

PL/SQL also includes a GOTO statement to branch from one point to another.

The syntax is:

GOTO <label>;

where “<label>” is a label defined in the PL/SQL block. Labels are enclosed in double angle brackets (<<label>>). When a GOTO statement is evaluated, control immediately passes to the statement identified by the label. An example follows:

SET SERVEROUTPUT ON
DECLARE
     v_counter NUMBER(2) := 1;
BEGIN
     LOOP
          v_counter := v_counter+1;
          IF v_counter > 5 THEN
               GOTO 1_ENDOFLOOP; -- print v_counter 5 times
          END IF;
          DBMS_OUTPUT.PUT_LINE('v_counter = ' || v_counter);
     END LOOP;
     <<1_ENDOFLOOP>>
END;
/

PL/SQL, however, do enforce some restrictions on the use of GOTO.

It is illegal to use GOTO to branch into:
  1. an inner block
  2. a loop
  3. an IF statement
  4. an exception

The following code shows some example of illegal use of GOTO:

DECLARE
    
BEGIN
     GOTO 1_InnerBlock;        -- illegal, cannot branch to an inner block.
     BEGIN
         
          <<1_InnerBlock>>
         
     END;
     GOTO 1_InnerBlock;        -- illegal, cannot branch into an IF statement.
     IF x < 3 THEN
          <<2_InnerIF>>
         
     END IF;
END;
/

CURSORS

In order for Oracle to process an SQL statement, it needs to create an area of memory known as the context area. This area contains the information needed to process the statement. The information includes the number of rows processed by the statement, and a pointer to the parsed representation of the statement (parsing an SQL statement is the process whereby information is transferred to the server, at which point the SQL statement is evaluated as being valid). In a query, the active set refers to the rows that will be returned.

A cursor is a handle, or a pointer, to the context area. Through the cursor, a PL/SQL program lets you control the context area, access the information, and process the rows individually.

TYPES OF CURSORS

there are two types of cursors:
An Implicit cursor is automatically declared by Oracle every time an SQL statement is executed.
An Explicit cursor is defined by the program for any query that returns more than one row of data.


Whenever a SQL statement is issued, the Database server opens an area of memory in which the command is parsed and executed. This area is called a cursor. In Microsoft SQL Server, this refers to datasets. If a PL/SQL block executes a SELECT command that returns multiple rows, Oracle will displays an error message, which will also invoke the TOO_MANY_ROWS Exception (discussed later in the chapter). To get around this problem, Oracle uses a mechanism called CURSOR. Do not confuse the name CURSOR with the mouse pointer that appears on the screen.

A cursor may be like a temporary file, which stores and controls the rows returned by a SELECT command. SQL*PLUS automatically generates cursors for the queries executed. In PL/SQL, on the other hand, it is necessary for the user to create specific cursors.

When the executable part of a PL/SQL block issues an SQL command, PL/SQL creates an implicit cursor, which has the identifier SQL. PL/SQL manages this cursor for you.

PL/SQL provides some attributes, which allow you to evaluate what happened when the implicit cursor was last used. You can use these attributes in PL/SQL statements like some functions but you cannot use then within SQL statements.

The SQL cursor attributes are: -

%ROWCOUNT
When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row.
%FOUND
TRUE when a cursor has some remaining rows to fetch, and FALSE when a cursor has no rows left to fetch
%NOTFOUND
TRUE if a cursor has no rows to fetch, and FALSE when a cursor has some remaining rows to fetch.
%ISOPEN
TRUE if cursor is opened, or FALSE if cursor has not been opened or has been closed. Only used with explicit cursors.

An example follows: -

DECLARE
     row_del_no NUMBER(2);
BEGIN
     DELETE * FROM employee;
     row_del_no := SQL%ROWCOUNT;
END;
/

EXPLICIT CURSORS


The set of rows returned by a query can consist of zero, one, or multiple rows, depending on how many rows meet your search criteria. When a query returns multiple rows, you can explicitly declare a cursor to process the rows. You can declare a cursor in the declarative part of any PL/SQL block, subprogram, or package. The steps for using an Explicit Cursor are:

DECLARE

Declaring an explicit cursor names the cursor and defines the query associated with the cursor. The general format for this command is:
CURSOR <cursorname> IS <SELECT statement>;
Cursor name can be any valid PL/SQL variable name. You can use any legal SELECT statements except the one containing Union or Minus operators.

OPEN
Opening the cursor causes the SQL commands to parse the SQL Query (i.e. check for syntax errors). The general format for this command is:
OPEN <cursorname>;
The OPEN command causes the cursor to identify the data rows
that satisfy SELECT query. However the data values are not actually retrieved.

FETCH
Loads the row addressed by the cursor pointer into variables and moves the cursor pointer on to the next row ready for the next fetch. The general format for this command is:
FETCH <cursorname> INTO <record variable(s)>;
The record variable is either a single variable or a list of variables that will receive data from the field or fields currently being processed.


CLOSE
Releases the data within the cursor and closes it. The cursor can be reopened to refresh its data. The general format for this command is:
CLOSE <cursorname>;\

Cursors are defined within a DECLARE section of a PL/SQL block. An example follows:

DECLARE
     CURSOR mycur IS SELECT emp_ssn, emp_last_name FROM employee;
    


The cursor is defined using the CURSOR keyword, followed by the cursor identifier (MYCUR in this case), and the SELECT statement used to populate it. The SELECT statement can be any legal query. In the example shown above, a cursor is created to retrieve all the employee’s SSNs and last names from the EMPLOYEE table.

An OPEN cursor statement is used to execute the SELECT statement, populate the cursor with data, and assign a pointer to the first record of the result set.

DECLARE
     CURSOR mycur IS SELECT emp_ssn, emp_last_name FROM employee;
BEGIN
    OPEN mycur;
    

To access the rows of data within the cursor we use the FETCH statement.

DECLARE
    CURSOR mycur IS SELECT emp_ssn,emp_last_name FROM employee;
    thisemp_ssn number(10);
    thisempname varchar2(20);
BEGIN
    OPEN mycur;
    FETCH mycur INTO thisemp_ssn, thisemp_name;
    

The FETCH statement reads one record at a time from the result set. In the above example, a FETCH statement is used to fetch the column values for the current cursor row (in this case, it is the 1st row) and puts them into either some declared variables (i.e. thisemp_ssn and thisemp_name), or a ROWTYPE variable (we will discuss this later). The cursor pointer is then updated to point at the next row. If the cursor has no returned row, the variables will be set to null on the first FETCH attempt, and subsequent FETCH attempts will raise an exception.

The CLOSE statement releases the cursor and any rows within it. The cursor can be re-opened to fetch the same records.

The following is the complete code for the example we have been discussing.

-- emp.sql
SET SERVEROUTPUT ON
DECLARE
    CURSOR mycur IS SELECT emp_ssn, emp_last_name FROM employee;
    thisemp_ssn number(10);
    thisemp_name varchar2(20);
BEGIN
   OPEN mycur;
        FETCH mycur INTO thisemp_ssn, thisemp_name;
        DBMS_OUTPUT.PUT_LINE(thisemp_ssn || ‘:‘ || thisemp_name);
   CLOSE mycur;
END;
/

The DBMS_OUTPUT.PUT_LINE displays the first result of the SELECT statement, as shown below.

SQL> @ emp.sql

9996666666:Bordoloi

To process all the rows within a cursor, we simply need to place the FETCH statement inside a loop, as illustrated in the following example. The loop constantly fetch a record into the declared variables, and check the cursor NOTFOUND attribute to see if it has successfully fetched a row or not. In other word, its purpose is to retrieve all the results from the SELECT statement, and exits when no more rows are returned. Unlike the example shown earlier, which only outputs the first record, the following code displays every row retrieved from the SELECT statement.

-- emp2.sql
SET SERVEROUTPUT ON
DECLARE
     CURSOR mycur IS SELECT emp_ssn, emp_last_name FROM employee;
     thisemp_ssn number(10);
     thisemp_name varchar2(20);
BEGIN
     OPEN mycur;
     loop
         FETCH mycur INTO thisemp_ssn, thisemp_name;
         exit when mycur%NOTFOUND;    
         DBMS_OUTPUT.PUT_LINE(thisemp_ssn || ‘:‘ || thisemp_name);
     END LOOP;
     CLOSE mycur;
END;
/

The output of the above example is:

SQL> @ emp2.sql

9996666666:Bordoloi
9995555555:Joyner
9994444444:Zhu
9998877777:Markis
9992222222:Amin
9991111111:Bock
9993333333:Joshi
9998888888:Prescott


An entire PL/SQL record may also be fetched into a ROWTYPE variable. Doing so reduces the number of variables needed. To access a specific field of a record, simply do the following:

<record>.<column name>

in which <record> is a variable of type <cursor name>%ROWTYPE.

An example follows: -

-- salary.sql
DECLARE
     CURSOR mycur IS SELECT emp_ssn, emp_salary FROM employee;
     emprec            mycur%ROWTYPE; -- type
BEGIN
     OPEN mycur;
     LOOP
          FETCH mycur INTO emprec;
          EXIT WHEN mycur%NOTFOUND;
          -- use emprec.emp_ssn to get the emp_ssn of current record.
          IF emprec.emp_ssn = ‘9996666666’ THEN
              DBMS_OUTPUT.PUT_LINE(emprec.emp_ssn  || ‘:’ || emprec.emp_salary);
          END IF;
     END LOOP;
     CLOSE mycur;
END;
/

The output of the above example is:

SQL> @ salary

9996666666:55000


You can use the WHERE CURRENT OF clause to execute DML commands against the current row of a cursor. This feature makes it easier to update rows. An example follows:

DECLARE
     CURSOR mycur IS SELECT emp_ssn,emp_salary FROM employee;
     emprec          mycur%ROWTYPE;
BEGIN
     OPEN mycur;
     LOOP
          FETCH mycur INTO emprec;
          EXIT WHEN mycur%NOTFOUND;
          IF emprec.emp_ssn = ‘9996666666’ THEN
               DELETE FROM employee WHERE CURRENT OF mycur;
          END IF;
     END LOOP;
    CLOSE mycur;
END;
/

Note that it is not necessary to explicitly specify the row that is to be deleted, PL/SQL supplies the required row identifier from the current record in the cursor to ensure that only the correct row is deleted.

It’s possible to vary the returned result set by using one or more parameters; parameters allow you to specify the query selection criteria when you open the cursor.

-- salary2.sql
SET SERVEROUTPUT ON
DECLARE
     CURSOR mycur (param1 NUMBER) IS SELECT emp_ssn, emp_salary  FROM employee WHERE emp_ssn = param1;
     emprec         mycur%ROWTYPE;
BEGIN
     OPEN mycur(‘9996666666’);
          FETCH mycur INTO emprec;
          DBMS_OUTPUT.PUT_LINE(‘Salary for ‘ || emprec.emp_ssn || ‘:’ || emprec.emp_salary);
     CLOSE mycur;
     OPEN mycur(‘9995555555’);
          DBMS_OUTPUT.PUT_LINE(‘Salary for ‘ || emprec.emp_ssn || ‘:’ || emprec.emp_salary);
          FETCH mycur INTO emprec;
     CLOSE mycur;
END;
/

The output of the above example is:

SQL> @ salary2

9996666666:55000
9995555555:43000


CURSOR FOR LOOPS

There is an alternative method of handling cursors. It is called the cursor FOR loop, in which the processes of opening, fetching, and closing are implicitly handled. This makes the blocks much simpler to code and easier to maintain.

Use the cursor FOR loop to fetch and process each and every record from the cursor.

SET SERVEROUTPUT ON
DECLARE
     CURSOR mycur IS SELECT emp_ssn, emp_salary FROM employee WHERE emp_dpt_number = 7;
BEGIN
     FOR tempcur IN mycur – assign all the values from mycur to tempcur
     LOOP
           DBMS_OUTPUT.PUT_LINE(tempcur.emp_ssn || ‘:’ || tempcur.emp_salary);
     END LOOP;
END;
/

EXCEPTION (ERROR) HANDLING

In PL/SQL, a warning or error condition is called an exception. A block is always terminated when PL/SQL raises an exception, but you can define your own error handler to capture exceptions and perform some final actions before quitting the block.

When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions.

To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment.

There are two classes of exceptions, these are:

Predefined - Oracle predefined errors which are associated with specific error codes.

User-defined - Declared by the user and raised when specifically requested within a block. You may associate a user-defined exception with an error code if you wish.

The "exception section" usually appears at the end of the PL/SQL‑ block.

The syntax is:

EXCEPTION
            WHEN <exception1‑name> THEN
                        <Exception1 handling statements>
            WHEN <exception2-name> THEN
                        <Exception2 handling statements>
….
            WHEN OTHERS THEN
            <Other handling statements>
END;

The <exception handling statements> are the code lines that inform the user of the error. The combination of the WHEN <exception name>, the THEN statement, and the associated exception-handling statements is called the exception handler. The WHEN OHERS statement is a catch-all exception handler that allows you to present general message to describe errors not handles by a specific error handling statement.

If an error occurs within a block, PL/SQL passes the control to the EXCEPTION section of the block. If no EXCEPTION section exists within the program, or the EXCEPTION section doesn't handle the error that has occurred, the block is terminated with an unhandled exception.

Exceptions propagate up through nested blocks until an exception handler that can handle the error is found. If no exception handler is found in any block, the error is passed out to the host environment. Exceptions occur when either an Oracle error occurs (this automatically raises an exception), or you explicitly raise an error using the RAISE statement.

Here are examples of exceptions:

Exception
Explanation
NO_DATA_FOUND
If a SELECT statement attempts to retrieve data based on its conditions, this exception is raised when no rows satisfy the SELECT criteria.
TOO_MANY_ROWS
Since each implicit cursor is capable of retrieving only one row, this exception is raised when more than one row are returned.
DUP_VAL_ON_INDEX
This exception detects an attempt to create an entry in an index whose key column values exist. For example, suppose a billing application is keyed on the invoice number. If a program tries to create a duplicate invoice number, this exception would be raised.
VALUE_ERROR
This exception indicates that there has been an assignment operation WHERE the target field is not long enough to hold the value being placed in it. For example, if the text ABWEFGH is assigned to a variable defined as "varchar2(6)", then this exception is raised.

“NO_DATA_FOUND” and “TOO_MANY_ROWS” are the two most common errors found when executing a SELECT statement. The example below takes care of these two conditions.

SET SERVEROUTPUT ON
DECLARE
     ssn        employee.emp_ssn%TYPE;
     name     employee.emp_last_name%TYPE;
BEGIN
     SELECT emp_ssn,emp_last_name INTO ssn,name
     FROM employee WHERE emp_dpt_number=45;
EXCEPTION
     WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE(‘There is no employee in that Department');
     WHEN TOO_MANY_ROWS THEN
          DBMS_OUTPUT.PUT_LINE('Return too many rows');
END;
 /

However, it happens sometimes when other errors other than those two mentioned above occurs. To cover all possible errors you can specify a catch all action named OTHERS.

SET SERVEROUTPUT ON
DECLARE
     ssn                 employee.emp_ssn%TYPE;
     name              employee.emp_last_name%TYPE;
     var_err_msg  VARCHAR2(512);
BEGIN
     SELECT emp_ssn,emp_last_name INTO ssn,name FROM employee WHERE emp_dpt_number=45;
EXCEPTION
     WHEN NO_DATA_FOUND THEN
           DBMS_OUTPUT.PUT_LINE(‘There is no employee in that Department');
     WHEN TOO_MANY_ROWS THEN
          DBMS_OUTPUT.PUT_LINE('Return too many rows');
     WHEN OTHERS THEN
          var_err_msg := SQLERRM;
          DBMS_OUTPUT.PUT_LINE('This program encountered the following error:'); 
          DBMS_OUTPUT.PUT_LINE(var_err_msg);
END;
/

PL/SQL provides two special functions for use within an EXCEPTION section, SQLCODE and SQLERRM. SQLCODE contains the Oracle error code of the exception. SQLERRM contains the Oracle error message of the exception. You can use these functions to detect what error has occurred (very useful in an OTHERS action). The above example illustrated how SQLERRM can be used.

SQLCODE and SQLERRM should be assigned to some variables before you attempt to use them. Notice that the variable var_err_msg in the above example is declared as a character of length 512 bytes. It is because the maximum length of an Oracle error message is 512.

A User-defined exception should be declared and raised explicitly by a RAISE statement. It can be declared only in the declarative part of the Pl/SQL block.

The syntax is:

In the declarative section,

<exception_name>      EXCEPTION;

The syntax for the RAISE statement is:

RAISE <exception_name>;

An example follows:

DECLARE
     exp_low_value           EXCEPTION;    -- declared here.
     var_equip_cnt           equipment.eqp_qty_on_hand%TYPE;
     var_min_equip          equipment.eqp_qty_on_hand%TYPE := 2;       
BEGIN
     SELECT eqp_qty_on_hand INTO var_equip_cnt
     FROM equipment WHERE eqp_no = 4321;
     IF var_equip_cnt < var_min_equip THEN
          RAISE exp_low_value;
     END IF;
EXCEPTION
     WHEN exp_low_value THEN
          DBMS_OUTPUT.PUT_LINE('Quantity is less than reorder level - replenish immediately');
END;
/


REVIEW EXERCISES

Learn Theses Terms

1.      Conditional control: It allows you to control the flow of the execution of the program based on a condition.
2.      IF-THEN: This construct tests a simple condition. If the condition evaluates to TRUE, one or more lines of code are executed. If the condition evaluates to FALSE, program control is passed to the next statement after the test.
3.      IF-THEN‑ELSE: This construct is similar to IF, except that when the condition evaluates to FALSE, one or more statements following the ELSE are executed.
4.      IF-THEN‑ELSIF: This format is an alternative to using the nested IF-THEN‑ELSE construct.
5.      LOOP: This statement lets you execute a sequence of statements multiple times.
6.      Goto and Label: When a GOTO statement is evaluated, control immediately passes to the statement identified by the label.
7.      Cursor: A cursor is a handle, or pointer, to the context area. Through the cursor, a PL/SQL program can control the context and what happens to it as the statement is processed.
8.      %ROWCOUNT: When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row.
9.      %FOUND: TRUE when a cursor has rows remaining to fetch, and FALSE when a cursor has no rows left to fetch.
10.  %NOTFOUND: TRUE if a cursor has no rows to fetch, and FALSE when a cursor has remaining rows to fetch.
11.  %ISOPEN: TRUE if cursor is open or FALSE if cursor has not been opened or has been closed. Only used with explicit cursor.
12.  Open: Opening the cursor causes the SQL commands to parse the SQL Query.
13.  Fetch: Loads the row addressed by the cursor pointer into variables and moves the cursor pointer on to the next row ready for the next fetch
14.  Cursor variables: Cursor variables are similar to PL/SQL variables, which can hold different values at run time.
15.  REF: The REF keyword indicates that the new type will be a pointer to the defined type. The type of cursor is therefore a REF cursor.
16.  Exception:  a warning or error condition in PL/SQL.
17.  Predefined exception: Oracle predefined errors that are associated with specific error codes.
18.  User-defined exception: Declared by the user and raised when specifically requested within a block. You may associate a user-defined exception with an error code if you wish.

Concepts Quiz:

1.      How many IF statements can be nested in another?
a) One
b) Two
c) Any number
2.      When the condition of the outer IF statement evaluates to FALSE, which of the following happens?
a) The control is transferred to the inner IF statement.
b) The error message is generated.
c) The control is transferred to the first executable statement after the outer END IF statement.
3.      What part of the ELSIF statement is executed when all of the conditions specified evaluate to NULL?
a) IF part
b) One of the ELSIF parts
c) ELSE part
d) ELSIF statement is not executed at all
4.      An ELSIF statement without ELSE part causes a syntax error.
a) True
b) False
5.      What does the exit-when statement do?
6.      What are the different forms of IF statements?
7.      What is a Do Nothing structure?
8.      What are two types of cursors?
9.      What is the use of %ROWCOUNT?
10.  What are steps in using an explicit cursor?
11.  What is syntax for opening a cursor variable?
12.  When is the exception DUP_VAL_ON_INDEX raised?
13.  Explain Value_error.
14.  If an error occurs within a block PL/SQL, the control goes to which part of the PL/SQL block?
15.  How can we declare a user defined exception?
16.  emprec employee%ROWTYPE. What does this declaration do?
17.  What is the use of the statement When Others in the exception-handling section?

Coding Exercises and Questions

1.      [easy] Using an IF-THEN statement, write a PL/SQL block to test if the date provided by the user falls on weekend. In other words, if the day happens to be Saturday or Sunday.
2.      [easy] Write a PL/SQL block to test if the entered number is a multiple of five or not .
3.      [easy] Write a PL/SQL block to ask the user to enter the student’s marks and determine the GRADE according to the following criteria.
>90   A
>80 AND <90 B
>70 AND <80 C
>60 AND <70  D
<60 F

4.      [easy] Write a program to assign your age and name to variables. The program should check if your age is greater that 18 or not. If it is, it should print “Major” along with your name else it should print “Minor”.
5.      [moderate] Write a program to find whether the assigned year is a “Century and leap year” or “Century and not leap” or “Not century not leap” or “Not century but leap year”.

Hint: Use the MOD operator to find whether a year is century or not. If the remainder is zero after dividing by 100, it is century. For a century year to be a leap year, it should be divisible by 400(i.e. remainder zero), and for a non-century year to be a leap year it should be divisible by 4.

6.      [moderate] Create a database table with the following fields:
Field name                 Data type
Ship_id                        Number -- This is the ID of a particular Ship
Date_expected                        Date --The date at which the goods are expected to arrive
Qty_expected             Number --The quantity that is supposed to arrive
Description                  Varchar2 --The description of the items
Color                           Varchar2 --The color of the items
Qty_hand                    Number –The quantity on hand for these items
Itemrate                       Number—Price of each item.

Sample data
Ship id    Date expected          QtyExpected   Description      Color   QtyHand Rate           
212         15-Nov-2001            25                    3-SeasonTents forest   3               500
212         25-Nov-2001            50                    3-SeasonTents Red     5               500
213         15-Mar-2003            75                    Caps                            10             250
              
Write a PL/SQL program that uses implicit cursor to display the data expected, quantity expected, item description, color and quantity on hand for any particular Ship ID number. Include exception handlers for the cases where no data is returned or where multiple records are returned. Format the output so that it is displayed as follows (assuming 212 is the Ship ID):

Shipment 212 is expected to
Arrive on 15-Nov-01
And will contain 25
3-Season Tents, Color forest

7.      [moderate] Write a PL/SQL program that uses an explicit cursor to display the item and the inventory information for each product. The cursor will also calculate the values for each inventory item (qty_hd * itemrate), and the total value of all inventory items for that product category.
a.        Create an explicit cursor that returns and then displays the itemdesc, itemrate, quantity on hand, and total price (qty_hd * itemrate) for each individual inventory item. Format each inventory item return values as follows:
Description:--This is the item description
Price: -- This is the itemrate
QOH:--This is the quantity on hand
Value:--This is qty_hd multiplied by itemrate
b.        Create a variable that sums up the total value of all inventory items and then display the total value after all rows are processed. Format the following output as:
TOTAL VALUE: 45789.6 – This is just an example
c.         Create a predefined exception handler for the case where no data is returned.

8.      [moderate] Create a database table, which has the following fields:
Field name                 Data type
Student Name             Varchar2
CourseID                    Number
Course description      Varchar2
Course credits             Number
Grade                          varchar2

Enter the following sample data.
Student Name       CourseID     Course description         Course credits       Grade
Bordoloi                MIS 101       Intro to Info. Systems         3                       A
Bock                      MIS 301       System Analysis                  3                       A
John                       MIS 451       Client/Server Systems         3                       C
Bordoloi                MIS 451       Client/Server Systems         3                       A
John                       MIS 301       System Analysis                  3                       C
Bock                      MIS 451       Client/Server Systems         3                       B
John                       MIS 101       Intro to Info. Systems         3                       B

Calculate the total credits and the overall grade point for each student. Course Grade Points are awarded as follows:
Grade                         Grade Points
A                                 4
B                                 3
C                                 2
D                                 1
F                                  0

Format the output as follows:
Student Name:                        John
MIS 101 Intro to Info. Systems               3                B
MIS 301 System Analysis                        3                C
MIS 451 Client/Server Systems                3                C
Total Credits: 9
Overall GPA:2.33 (i.e. (3+2+2)/3)

Hint: Round the overall GPA using the ROUND function.

From question 9 to question 13, please refer to the COMPANY database illustrated in Appendix A.

9.            [easy]The employees’ salary is to be classified as following:
Class A: <= 30000
Class B: > 30000 and <= 60000
Class C: > 60000

Using CURSOR and LOOP, write a PL/SQL program to display the full name (i.e. first name followed by middle name and last name), salary, and class of salary for each employee who works in the department 'Production.' The output must be ordered alphabetically by the employee’s first name, and displayed in the following format:

Full Name:Bijoy Bordoloi
Salary:55000 (Class B)

10.        [easy] Redo previous question for every department. Be sure to display the department name followed by the employees’ information. The result should be ordered by the department name, then by the employee’s first name.

Hint: Use cursor with parameters.

11.        [easy] Use LOOP to find and display 2 employees who are born on a Tuesday. Display only their full names. Raise and display a user-defined exception if the criterion is not met.

12.        [moderate] Using the salary classification listed above, find and display the department with the highest average salary in the following format.

Department: HEADQUARTER
Average Total Salary: 22000 (Class A)

13.        [moderate] Write a program to display the following information for each department:

a)      department’s name
b)      department’s manager
c)      all the project’s names
d)     total work hours
e)      total work hours planned



From question 14 to question 19, please refer to the RIVERBEND HOSPITAL database illustrated in Appendix B.

14.        [easy] Write a program to display the full name and patient id of those patients who are assigned to room 'SW3008'.

15.        [easy] Referring to previous question, write a program that accepts a room id as the input, and display the full names of all the patients who are assigned to it, and the staff members who have treated those patients residing in this room.

16.        [moderate] The staff member’s salary is to be classified as following:
Class A: <= 50000
Class B: > 50000 and <= 100000
Class C: > 100000
           
Using CURSOR and LOOP, write a PL/SQL program to display the full name (i.e. first name followed by last name), employee’s type (Salaried or Hourly), ANNUAL salary, and salary’s “class” of each employee who works in a ward/department with ward id starts with ‘ADM’. The output must be ordered alphabetically by the staff’s first name, and shown in the following format:

Full Name:Bijoy Bordoloi
Employee’s type: Salaried
Salary:55000 (Class B)

Hint: To calculate the annual salary of the hourly employee, use the following formula:
Annual salary = hourly rate * 40 * 52       /* 40 hours per week for 52 weeks */


17.        [moderate] Assuming a patient should not receive both treatment and prescription from the same staff, write a program to find out all the staff members who provide both treatment and prescription to the same patient. The output should be shown in the following format:

Staff member's Name:Robert A Schultheis
Patient's Name:Nancy Near Nunn

In addition, raise and display an exception if this situation occurs.

Hint: Be sure to display all the staff members before raising an exception.

18.        [easy] Using the cursor FOR loop, write a program to display all the staff members with more than one specialty.

19.        [moderate] Display the name and room of those patients who have been given a prescription at least twice for the past seven days. 

Hint: Assume that date_last_updated is the date that a prescription is ordered.

20.    [easy]  Write a program to find the note date and note comment for a specific patient.


1 comment: