Oracle9i Developer:

1
Oracle9i Developer PL/SQL Programming
Chapter 3
PL/SQL Processing
2
Chapter Objectives

• Use control structures to make decisions
• Use looping structures to repeat code
• Work with the GOTO statement
• Manage errors with exception handlers
• Address exception-handling issues, such as
  RAISE_APPLICATION_ERROR and propagation
• Document code with comments

3
Brewbeans Challenge
4
Control Structures

• IF Statements
• Basic IF
• IF/THEN/ELSE
• IF/THEN/ELSIF/ELSE
• CASE Statements
• Basic CASE statement
• Searched CASE statement
• CASE expression

5
Simple IF Statement
6
Add the ELSE clause
7
Add ELSIF clauses
8
NOT ELSEIF
9
Can check multiple conditions

• IF rec_order.state IN ('VA','PA','ME') THEN
• lv_tax_num rec_order.sub .06
• ELSE
• lv_tax_num rec_order.sub .04
• END IF

10
Basic CASE Statement
11
No CASE Found Error
12
Searched CASE Statement
13
CASE Expression
14
Basic Loop
15
WHILE Loop
16
FOR Loop
17
Use Variables in FOR Loop
DECLARE lv_upper_num NUMBER(3) BEGIN SELECT
COUNT(idBasket) INTO lv_upper_num FROM
bb_basket FOR i IN 1..lv_upper_num LOOP
DBMS_OUTPUT.PUT_LINE(i) END LOOP END
18
Reverse Option in FOR Loop

• Instructs counter to start with the upper bound
  value and increment by 1 to the lower bound
  value
• Example
• FOR I IN REVERSE 1..5 LOOP

19
GOTO Statement

• Jumping control that instructs the program to
  move to another area of code to continue
  processing
• Most developers discourage the use of GOTO as it
  complicates the flow of execution

20
Exception Handlers

• Used to capture error conditions and handle the
  processing to allow the application to continue
• Placed in the EXCEPTION section of a PL/SQL block
• Two types of errors
• Oracle errors (Predefined and Non-Predefined)
• User-defined errors
• RAISE_APPLICATION_ERROR

21
Predefined Oracle Errors
22
Predefined Error Example
23
Add Exception Handler
24
Non-Predefined Handler
Declare an exception
Associate an Oracle error number to the
exception name
Foreign key error occurs if item lines are still
in the bb_basketitem table
Exception handler executes if Foreign key error
2292 is raised by the DELETE
25
User-Defined Exception
Declare an exception
If no rows updated, raise the exception
Exception handler
26
Additional Exception Concepts

• WHEN OTHERS traps all errors not specifically
  addressed by an exception handler and used for
  handling unanticipated errors
• SQLCODE and SQLERRM
• Functions used to identify the error code and
  message especially in application testing to
  identify unanticipated errors

27
SQLCODE and SQLERRM
Log error information to a database table using
these functions
28
RAISE_APPLICATION_ERROR

• Allows developers to associate their own error
  number and message to an error
• Can only be used with stored programs which is
  covered in Chapter 4

29
Exception Propagation

• When an error occurs in a block, processing
  immediately moves to the EXCEPTION section of the
  block
• If the block is nested, and no handlers address
  the error, the processing will then move to the
  EXCEPTION section of the enclosing block

30
Commenting Code

• Add comments within code to identify code purpose
  and processing steps
• Use / / to enclose a multiline comment
• Use -- to add a single or partial line comment

31
Comment Examples

• DECLARE
• ex_prod_update EXCEPTION --For UPDATE of no
  rows exception
• BEGIN
• / This block is used to update product
  descriptions
• Constructed to support the Prod_desc.frm app
  screen
• Exception raised if no rows updated /
• UPDATE bb_product
• SET description 'Mill grinder with 5 grind
  settings!'
• WHERE idProduct 30
• --Check if any rows updated
• IF SQLNOTFOUND THEN
• RAISE ex_prod_update
• END IF
• EXCEPTION
• WHEN ex_prod_update THEN
• DBMS_OUTPUT.PUT_LINE('Invalid product id
  entered')
• END

32
Summary

• Add conditional logic using IF and CASE
  statements
• Repeat lines of code using Loop structures
• Add error handlers in the EXCEPTION area to
  manage Oracle and user-defined errors
• Use comments in code for documentation