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;

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:

Each IF statement is followed by its own THEN. There is no semicolon (;) terminator on the line that starts with IF.
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;

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

There can be one and only one ELSE with every IF statement.
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.

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

…

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.

DO NOTHING STRUCTURE

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:

an inner block
a loop
an IF statement
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.

IMPLICIT CURSORS

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.

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 1^st 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:

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

WHEN <exception2-name> THEN

….

WHEN OTHERS THEN

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.

be happy and make others to be happy

Infolinks

Wednesday, 20 February 2013

PL/SQL CHAPTER 2

CHAPTER 2

PL/SQL BASICS:

CONTROL STRUCTURES, CURSORS AND EXCEPTIONS

EXPLICIT CURSORS

1 comment: