background image

<< Contents 2 | Contents 4 >>

<< Contents 2 | Contents 4 >>

Contents 3

v

Using Variables and Constants ..................................................................................................... 4-16

Using Comments...................................................................................................................... 4-16
Using Identifiers....................................................................................................................... 4-16
Declaring Variables and Constants ....................................................................................... 4-16
Declaring Variables with Structure Identical to Database Columns................................ 4-17

Assigning Values to Variables....................................................................................................... 4-19

Assigning Values with the Assignment Operator .............................................................. 4-19
Assigning Values from the Database .................................................................................... 4-20

Controlling Program Flow

................................................................................................................... 4-22

Using Conditional Selection Control............................................................................................ 4-22

Using IF...THEN...ELSE Selection Control ........................................................................... 4-22
Using CASE...WHEN Selection Control ............................................................................... 4-24

Using Iterative Control................................................................................................................... 4-25

Using the FOR...LOOP ............................................................................................................ 4-25
Using the WHILE...LOOP....................................................................................................... 4-27
Using the LOOP...EXIT WHEN ............................................................................................. 4-28

Using Composite Data Structures; Records

..................................................................................... 4-29

Retrieving Data from a Set Using Cursors and Cursor Variables

................................................ 4-32

Using Explicit Cursors.................................................................................................................... 4-33
Using Cursor Variables: REF Cursors .......................................................................................... 4-35

Using Collections; Index-By Tables

................................................................................................... 4-38

Creating Cursors for Index-by Tables .......................................................................................... 4-39
Defining Index-by Tables............................................................................................................... 4-40
Populating Index-by PLS_INTEGER Tables; BULK COLLECT............................................... 4-40
Populating Index-by VARCHAR2 Tables ................................................................................... 4-40
Iterating Through an Index-by Table ........................................................................................... 4-41

Handling Errors and Exceptions

......................................................................................................... 4-41

Existing PL/SQL and SQL Exceptions......................................................................................... 4-42
Custom Exceptions ......................................................................................................................... 4-43

5 Using Triggers

Designing Triggers

................................................................................................................................... 5-1

Types of Triggers................................................................................................................................ 5-2
Timing Triggers .................................................................................................................................. 5-3
Guidelines and Restrictions for Trigger Design ............................................................................ 5-3

Creating and Using Triggers

.................................................................................................................. 5-4

Creating a Statement Trigger ........................................................................................................... 5-4
Creating a Row Trigger ..................................................................................................................... 5-5
Creating an INSTEAD OF Trigger................................................................................................... 5-7
Creating LOGON and LOGOFF Triggers ...................................................................................... 5-7
Modifying Triggers ............................................................................................................................ 5-8
Disabling and Enabling Triggers ..................................................................................................... 5-8
Compiling Triggers............................................................................................................................ 5-9
Dropping Triggers .......................................................................................................................... 5-10