1 / 19

Cursor

Cursor. A pointer to memory location on database server Used to: Retrieve and manipulate database data in PL/SQL programs Types: Implicit cursor Explicit cursor. Implicit Cursors. Context area A memory location created by INSERT, UPDATE, DELETE, or SELECT

eberg
Download Presentation

Cursor

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Cursor • A pointer to memory location on database server • Used to: • Retrieve and manipulate database data in PL/SQL programs • Types: • Implicit cursor • Explicit cursor

  2. Implicit Cursors • Context area • A memory location created by INSERT, UPDATE, DELETE, or SELECT • Contains information about query (# rows, etc.) • Active set • Set of data rows that query retrieves when a SELECT query is issued • Implicit cursor • A pointer to the context area • Called so, because you do not need to write codeto explicitly create the cursor or retrieve its values • Used to assign output of SELECT query to PL/SQL program variables when query will return only one record* * Error occurs if query returns no records or more than one record

  3. Implicit Cursors (continued) • To retrieve data using implicit cursor in PL/SQL, you add an INTO clause to the SELECT query • Syntax: SELECT field1, field2, ... INTO variable1, variable2, ... FROM table1, table2, ... WHERE join_conditions AND search_condition_to_retrieve_1_record; • Variables must be declared in Declaration section • Variables must have same data types as fields • To avoid errors, %TYPE reference data type should be used

  4. Implicit Cursors (continued)

  5. Explicit Cursors • Retrieve and display data in PL/SQL programs for query that might • Retrieve multiple records • Return no records at all • Must explicitly write the code to • Declare cursor • Open cursor • Fetch data rows • Close cursor

  6. Explicit Cursors (continued) • Declare explicit cursor syntax: • CURSOR cursor_name IS select_query; • Open explicit cursor syntax: • OPEN cursor_name; • Fetch values using LOOP…EXIT WHEN loop: LOOP FETCH cursor_name INTO variable_name(s); EXIT WHEN cursor_name%NOTFOUND; • Close cursor syntax: • CLOSE cursor_name; Note: When the cursor is declared, system doesn’t check errors in the query. It creates the memory structure to store the active set. The PL/SQL interpreter checks for error and interprets the query when opening the cursor

  7. Q: At this point, what is the value for current_bldg_code? The declared variable is used to retrieve the cursor content and to display

  8. Using a cursor and a single variable to retrieve multiple fields values • Using %ROWTYPE variable to display explicit cursor values

  9. Handling Runtime Errors in PL/SQL Programs • Runtime errors • Occur when an exception (unwanted event) is raised • Cause program to fail during execution • Possible causes (exceptions): • Division by zero - inserting incompatible data • Constraint violation - retrieving 0/several rows with implicit cursor • Exception handling • Programmers place commands in EXCEPTION section • Handle exception options • Correct error without notifying user of problem • Inform user of error without taking corrective action • After exception handler executes • Program ends DECLARE variable declarationsBEGIN program statementsEXCEPTION error-handling statementsEND;

  10. Handling error procedure depends the type of exception: • Predefined exception - undefined exception • User-defined exception

  11. Predefined Exceptions • Most common errors that occur in programs • PL/SQL language: • Assigns exception name • Provides built-in exception handler for each predefined exception • System automatically displays error message informing user of nature of problem

  12. Exception Handler Syntax • Can create exception handlers to display alternate error messages

  13. Using the WHEN OTHERS exception • The SQLERRM built-in function is used to handle other exception • To use the SQLERRM function, you must • Declare a VARCHAR2 variable • Assign the declared variable the error’s text and code

  14. Undefined Exceptions • Less common errors • Do not have predefined names • Must explicitly declare exception in program’s declaration section • Associate new exception with specific Oracle error code • Create exception handler in exception section • Using same syntax as for predefined exceptions

  15. Loc_id 60 doesn’t exist in LOCATION Example of undefined exception • The ORA-02291 exception is not predefined. • Need to explicitly declare the exception and write a handler

  16. Syntax DECLARE e_exception_name EXCEPTION; PRAGMA EXCEPTION_UNIT(e_exception_name, -Oracle_error_code); Creating an exception handler

  17. User-defined Exceptions • Used to handle an exception that • Does not raise Oracle runtime error • But requires exception handling to • Enforce business rules or • Ensure integrity of database • Example: • Internal Northwoods’ rule is “Users can delete row from the ENROLLMENT table only if s_grade is NULL” • Trying to delete a delete an ENROLLMENT row where the s_grade is not NULL will raise an exception that needs to be handled

More Related