Cursors

1
Cursors

• Pointer to a server memory location
• Contains information about a SQL command in a
  PL/SQL program
• Called the commands context area

2
Cursors
Database Server Memory
Cursor
Number of rows processed
Parsed command statement
context area
active set
3
Types of Cursors

• Implicit
• Explicit

4
Implicit Cursors

• Created automatically every time you use an
  INSERT, UPDATE, DELETE, or SELECT command
• Doesnt need to be declared
• Can be used to assign the output of a SELECT
  command to one or more PL/SQL variables
• Can only be used if query returns one and only
  one record

5
Implicit Cursor Syntax

• SELECT field1, field2,
• INTO variable1, variable2,
• FROM tablename
• WHERE search_condition_that_will_
• return_a_single_record

6
Implicit Cursors

• Update Delete Insert
• Single row select into
• Select from flight into v_flight
• Where flight_no 101
• Update
• Update flight
• Set fare 350 where flight_no 101

7
Implicit Cursor

• Update flight
• Set fare 350 where flight_no 101
• If SQLNOTFound
• Insert into flight (flight_no, fare)
• Values (101, 350)
• DELETE
• Delete from flight where flight_no 101

8
Using SQL Commands in PL/SQL Programs
9
Explicit Cursors

• Must be declared in program DECLARE section
• Can be used to assign the output of a SELECT
  command to one or more PL/SQL variables
• Can be used if query returns multiple records or
  no records

10
Using an Explicit Cursor

• Declare the cursor
• Open the cursor
• Fetch the cursor result into PL/SQL program
  variables
• Close the cursor

11
Explicit Cursor Attributes
12
PL/SQL Loops

• Loop repeats one or more program statements
  multiple times until an exit condition is reached
• Pretest loop exit condition is tested before
  program statements are executed
• Posttest loop exit condition is tested after
  program statements are executed

13
LOOP EXIT WHEN Loop
LOOP program statements EXIT WHEN condition END
LOOP
Pretest or Posttest
14
WHILE Loop
WHILE condition LOOP program statements END LOOP
Pretest

• WHILE LOOP

15
Numeric FOR Loop
FOR counter_variable IN start_value ..
end_value LOOP program statements END LOOP
Preset number of iterations
16
For Loops

• BEGIN
• FOR i IN 1 . . 50 LOOP
• INSERT INTO temp_table (num_col)
• VALUES (i)
• END LOOP
• END
• For i IN REVERSE 10. .1 LOOP
• For loop can also exit

17
Processing an Explicit Cursor

• LOOP ..EXIT WHEN approach
• OPEN cursor_name
• LOOP
• FETCH cursor_name INTO variable_name(s)
• EXIT WHEN cursor_nameNOTFOUND
• (Processing statements)
• END LOOP
• CLOSE cursor_name

18
Processing an Explicit Cursor

• LOOP ..While approach
• OPEN cursor_name
• FETCH cursor_name INTO variable_name(s)
• WHILE cursor_namefound LOOP
• (Processing statements)
• FETCH cursor_name INTO variable_name(s)
• END LOOP
• CLOSE cursor_name

19
The Cursor for loop Syntax

• FOR rowtype_var_name IN cursor_name LOOP
• processing statement(s)
• END LOOP
• Table referenced by the PL/SQL rowtype must match
  the table accessed by the cursor

20
The Cursor FOR loop

• Automatically
• Opens the cursor
• Fetches the next row of the cursor each time
  through the loop
• Drops out of the loop and closes the cursor after
  the last row is read

21
CURSOR FOR LOOP Example

• declare
• v_flight_rec d_flightrowtype
• cursor v_flight_cur is
• select from d_flight where
• orig 'PHX'
• begin
• for v_flight_rec in v_flight_cur
• loop
• dbms_output.put_line(v_flight_rec.flight_no '
  '
• v_flight_rec.dest ' '
  v_flight_rec.fare)
• end loop
• end

22
NULL Values in Assignment Statements

• Until a value is assigned to a variable, the
  variables value is NULL
• Performing an arithmetic value on a NULL value
  always results in a NULL value
• Advice Always initialize variable values

23
Referencing PL/SQL Variables in Cursors

• Any variables referenced must have their values
  established before the cursor is opened

DECLARE V_Dest varchar2(3) Cursor c_flight is
Select fare from flight where Orig PHX
and Dest v_Dest BEGIN V_Dest LAX OPEN
C_flight . . . WORKS
DECLARE V_Dest varchar2(3) Cursor c_flight is
Select fare from flight where Orig PHX
and Dest v_Dest BEGIN OPEN C_flight V_Dest
LAX . . . FAILS! V_Dest is null when the
cursor is executed.
24
Cursors and Records

• Cursor c_flight IS Select from flight
• where flight_no 101
• flight_record c_flightrowtype
• Begin - - uses open and fetch
• Open c_flight
• Fetch c_flight into flight_record

25
Processing an Explicit Cursor

• Cursor FOR Loop approach
• FOR rowtype_variable_name in cursor_name LOOP
• additional processing statements
• END LOOP

26
Exceptions

• RAISE_APPLICATION_ERROR
• (error no ,error message, keep errors)
• Error number -20000 to -20999
• Message 512
• Keep Errors 1 or 0 Default 0
• Use in Block or in Exception area

27
TESTING

• Procedures Exec Show errors
• Functions Select Show errors
• Triggers Fire Use the
  usererrors

28
PL/SQL Exception Handling

• All error handling statements are placed in the
  EXCEPTION program block
• Exception handler program command that provides
  information about an error, and suggest
  correction actions

29
Predefined Exceptions

• Common errors that have been given predefined
  names that appear instead of error numbers

30
Exception Handler SyntaxFor Predefined Exceptions

• WHEN exception1_name THEN
• exception handling statements
• WHEN exception2_name THEN
• exception handling statements
• WHEN OTHERS THEN
• exception handling statements

31
Undefined Exceptions

• Less-common errors that have not been given
  predefined names
• ORA- error code appears
• Exception handler tests for ORA- error code and
  provides alternate error message

32
User-Defined Exceptions

• Errors that will not cause a run-time error, but
  will violate business rules
• Programmer creates a custom error message

33
Nested PL/SQL Program Blocks

• An inner program block can be nested within an
  outer program block

DECLARE variable declarations BEGIN program
statements EXCEPTION error handling
statements END
Outer block
DECLARE variable declarations BEGIN program
statements EXCEPTION error handling
statements END
Inner block
34
Exception Handling in Nested Program Blocks

• If an exception is raised and handled in an inner
  block, program execution resumes in the outer
  block

35
Exception Handling in Nested Program Blocks

• DECLARE
• variable declarations
• BEGIN
• program statements
• additional program statements
• EXCEPTION
• error handling statements
• END

DECLARE exception_A BEGIN RAISE
exception_A EXCEPTION exception_A error
handler END
Exception is raised and handled in inner block
Program execution resumes here
36
Exception Handling in Nested Program Blocks

• Exceptions raised in inner blocks can be handled
  by exception handlers in outer blocks

37
Exceptions

• DECLARE
• e_FlightFull EXCEPTION
• BEGIN
• .
• EXECEPTION
• WHEN e_FlightFull THEN
• ..
• WHEN OTHERS THEN

38
Exceptions

• WHEN e_flightFull
• INSERT INTO log_table (Err_info)
• VALUES
• (to_char(sysdate) blkName flight_no
• flight_date Flight Full )

39
Exceptions

• WHEN TOO_MANY_ROWS THEN
• RAISE_APPLICATION_ERROR
• (-20004, Found Multiple Rows)
• WHEN NO_DATA_FOUND THEN
• . . . . .
• WHEN OTHERS THEN
• . . . . .

40
EXCEPTIONS

• DUP_VAL_ON_INDEX
• TIMEOUT_ON_RESOURCE
• TRANSACTION_BACKED_OUT
• INVALID_CURSOR
• NOT_LOGGED_ON
• LOGIN_DENIED
• NO_DATA_FOUND

41
Exceptions

• TOO_MANY_ROWS
• ZERO_DIVIDE
• INVALID_NUMBER
• STORAGE_ERROR
• PROGRAM_ERROR
• VALUE_ERROR
• ROWTYPE_MISMATCH
• CURSOR_ALREADY_OPEN

42
Exceptions - All Other Oracle Errors

• WHEN OTHERS THEN
• v_ErrorCode SQLCODE
• v_ErrorText SUBSTR(SQLERRM,1,200)
• INSERT INTO log_table (code, message, info)
• VALUES (v_ErrorCode, v_ErrorText,
• Oracle Error Occurred)

43
Debugging PL/SQL Programs

• Syntax error
• Does not follow language guidelines
• Causes a PLS- compile error
• Examples misspelling a reserved word, using a
  function incorrectly
• Logic error
• Program compiles correctly, but does not give
  correct output

44
Locating and Correcting Syntax Errors

• Isolate the line that is causing the error
• This may be before or after the line that is
  flagged by the compiler
• Comment out lines as necessary until program runs
• One error may cause several cascading errors, so
  re-run program after fixing each error

45
Locating and Fixing Logic Errors

• Identify the output variable(s) that have the
  error.
• Identify the inputs and calculations that
  contribute to the error.
• Display the values of the inputs using
  DBMS_OUTPUT commands.
• Take a break and look at it again later.
• Ask a fellow student for help.
• Ask your instructor for help.