1 / 60

Chapter 10 Advanced PL/SQL Concepts

Chapter 10 Advanced PL/SQL Concepts. Overview Section A: Explicit Cursors Records Cursors Cursor Loops Section B: Exceptions Predefined Exceptions User-defined Exceptions. Explicit Cursors. Cursors are used by all DML commands

vielka-cline
Télécharger la présentation

Chapter 10 Advanced PL/SQL Concepts

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. Chapter 10 Advanced PL/SQL Concepts Overview Section A: Explicit Cursors • Records • Cursors • Cursor Loops Section B: Exceptions • Predefined Exceptions • User-defined Exceptions

  2. Explicit Cursors • Cursors are used by all DML commands • A cursor is used to select rows from a table, and then advance through them one at a time while acting as a pointer to the current row

  3. Explicit Cursors • Two types of cursors: • Implicit cursors • The software automatically handles all statements involving their manipulation • Explicit cursors • Are user-defined • Can be used to implement a wide range of solutions within PL/SQL blocks

  4. Records • Explicit cursors usually involve the usage of a record variable • A record is a type of variable that can be used to hold data from an entire row or just part of a row • Consists of several storage locations (one for each column value) • Each of the storage locations within a record is called a field

  5. Records • The record must be declared before it can be used • A suffix of _REC should be included on the record name • For the type, the %ROWTYPE specification is generally used, preceded by the table name

  6. Records • After the values are stored in the record, they can be accessed by qualifying the field name (or column name) with the record name, as in record-name.field-name • This access can occur in an assignment statement, another DML statement, or an output statement

  7. Records Example: For illustration purposes only, use a record to store the values from one row of the patient table. Then display the patient's name.

  8. Records SET SERVEROUTPUT ON SET VERIFY OFF ACCEPT pt_id PROMPT 'Enter patient''s ID: ' DECLARE patient_rec patient%ROWTYPE; BEGIN SELECT * INTO patient_rec FROM patient WHERE pt_id = &pt_id;

  9. Records DBMS_OUTPUT.PUT_LINE('Patient''s name is ' || patient_rec.pt_fname || ' ' || patient_rec.pt_lname); END; / SET SERVEROUTPUT OFF SET VERIFY ON Execution results: Enter patient's ID: 108 Patient's name is Ryan Baily

  10. Cursors: Declaration • Cursor declaration creates a named area of memory for the data used by the cursor • Among other things, this data includes the SELECT statement that is linked to the cursor • When processed, the cursor will then act as a pointer as it moves through the rows retrieved by the linked SELECT statement

  11. Cursors: Declaration General syntax: CURSOR cursor-name IS select-statement; where naming conventions include using a suffix of _CURSOR on the cursor-name and the select-statement is any valid SQL SELECT statement (no INTO clause included)

  12. Cursors: Declaration Declare a cursor that can be used to move through the DOCTOR table. DECLARE CURSOR doc_cursor IS SELECT * FROM doctor;

  13. Cursors: Declaration Declare a cursor that can be used to move through the IDs, due dates, and balances of the patients that see the doctor whose ID is entered by the user. DECLARE CURSOR bills_cursor IS SELECT pt_id, duedate, balance FROM billing WHERE doc_id = &doc_id;

  14. Cursors: OPEN • The OPEN statement is used to open the cursor • This causes the query linked to the cursor name to be executed • The data retrieved by the query is called the "active set" • General syntax: OPEN cursor-name;

  15. Cursors: OPEN Open the cursor that was declared in the previous example. BEGIN OPEN bills_cursor;

  16. Cursors: Declaration and OPEN Put it together: declare and open a cursor that can be used to move through the customer name, movie ID, and due date for each current rental.

  17. Cursors: Declaration and OPEN DECLARE CURSOR cust_rent_cursor IS SELECT lname, fname, m_id, duedate FROM customer c, rental r WHERE c.c_id = r.c_id; BEGIN OPEN cust_rent_cursor;

  18. Cursors: FETCH • The FETCH statement retrieves the next row from the active set and stores it in the variables or record listed • Values can then be accessed by using the variable names or the fully-qualified field names from the record • The FETCH statement also moves the cursor up so that it points to the next row in the active set

  19. Cursors: FETCH General syntax is as follows: FETCH cursor-name INTO { variables | record-name }; where the braces indicate that one of the items is used (either variables or a record); the braces are not a part of the syntax.

  20. Cursors: FETCH Fetch the first row specified by the cursor that was declared and opened in the previous example (assuming that an appropriate record variable was also declared). FETCH cust_rent_cursor INTO cust_rent_rec;

  21. Cursors: Declare, Open, Fetch Example: Declare a cursor and record that can be used to move through rows of the billing table. Then open the cursor and fetch the first row of values into the record.

  22. Cursors: Declare, Open, Fetch DECLARE CURSOR billing_cursor IS SELECT * FROM billing; billing_rec billing%ROWTYPE; BEGIN OPEN billing_doc_cursor; FETCH billing_cursor INTO billing_rec;

  23. Cursors: CLOSE • The CLOSE statement is used when the cursor's active set is no longer needed. • General syntax is CLOSE cursor-name; Close the cursor used in the previous example. CLOSE ped_doc_cursor;

  24. Cursor Loops • A loop is used to repeatedly fetch rows from the active set linked to a cursor • This loop is of the simple form LOOP – END LOOP • The loop will repeatedly execute until a condition is met with an EXIT statement

  25. Cursor Loops • The condition on the EXIT statement includes a reference to one of four cursor attributes, %NOTFOUND • Precede the attribute with the desired cursor name, such as PED_DOC_CURSOR%NOTFOUND • This returns a true value when there are no more rows left in the active set, and false otherwise

  26. Cursor Loops General syntax: LOOP FETCH cursor-name INTO record-name; EXIT WHEN cursor-name%NOTFOUND; -- Include statements needed to process -- one row of values here END LOOP;

  27. Cursor Loops Example: Increase each doctor's annual bonus according to the following table: Area Amount of Increase Neurology 15 times ChgPerAppt Pediatrics 10 times ChgPerAppt Rehab 10 times ChgPerAppt Family Practice 8 times ChgPerAppt Orthopedics 5 times ChgPerAppt

  28. Cursor Loops DECLARE doc_rec doctor%ROWTYPE; v_increase NUMBER(4); CURSOR doc_cursor IS SELECT * FROM doctor;

  29. Cursor Loops BEGIN OPEN doc_cursor; LOOP -- Retrieve row from active set FETCH doc_cursor INTO doc_rec; -- Leave loop if no rows left to retrieve EXIT WHEN doc_cursor%NOTFOUND;

  30. Cursor Loops -- Calculate amount of bonus increase IF doc_rec.area = 'Neurology' THEN v_increase := doc_rec.chgperappt * 15; ELSIF doc_rec.area IN ('Pediatrics', 'Rehab') THEN v_increase := doc_rec.chgperappt * 10; ELSIF doc_rec.area = 'Family Practice' THEN v_increase := doc_rec.chgperappt * 8; ELSE v_increase := doc_rec.chgperappt * 5; END IF;

  31. Cursor Loops -- Store new bonus amount in table UPDATE doctor SET annual_bonus = doc_rec.annual_bonus + v_increase WHERE doc_id = doc_rec.doc_id; END LOOP; CLOSE doc_cursor; END; /

  32. Cursor Loops Selected doctor IDs, areas and bonuses before increase and after increase are shown here to illustrate the execution effects of this block. DOC_ID AREA BEFORE AFTER --------- -------------------- --------- --------- 235 Family Practice 2610 2970 289 Neurology 7925 9350 389 Pediatrics 2850 3450

  33. Cursor FOR Loops The cursor FOR loop implicitly handles the following tasks: • Declaration of the record variable to be used with the cursor • Opening the cursor • Repeatedly fetching a row from the active set • Exiting the loop when %FOUND returns false • Closing the cursor

  34. Cursor FOR Loops General syntax: FOR record-name IN cursor-name LOOP -- Include various statements to process -- one row of values here END LOOP;

  35. Cursor FOR Loops Example: Modify the previous example so that a cursor FOR loop is used instead of explicit record declaration, OPEN, FETCH, EXIT, and CLOSE.

  36. Cursor FOR Loops DECLARE v_increase NUMBER(4); CURSOR doc_cursor IS SELECT * FROM doctor;

  37. Cursor FOR Loops BEGIN FOR doc_rec IN doc_cursor LOOP IF doc_rec.area = 'Neurology' THEN v_increase := doc_rec.chgperappt * 15; ELSIF doc_rec.area IN ('Pediatrics', 'Rehab') THEN v_increase := doc_rec.chgperappt * 10; ELSIF doc_rec.area = 'Family Practice' THEN v_increase := doc_rec.chgperappt * 8; ELSE v_increase := doc_rec.chgperappt * 5; END IF;

  38. Cursor FOR Loops UPDATE doctor SET annual_bonus = doc_rec.annual_bonus + v_increase WHERE doc_id = doc_rec.doc_id; END LOOP; END; /

  39. Exceptions • An exception is a facility provided for error handling • When a run-time error occurs: • An exception is "raised" • It can then be "trapped" with code written in the EXCEPTION section of the block • Trapping exceptions allows you to choose the course of action to be taken when an error occurs. • If an error occurs and it is not trapped, then the procedure will crash.

  40. Exceptions • Two types of exceptions • Those that are predefined by the software • Those that are defined by the user for special cases • Both types of exceptions are used within PL/SQL blocks.

  41. Common Predefined Exceptions ErrorException raised by error • More than 1 row returned by a TOO_MANY_ROWS SELECT…INTO statement • Less than 1 row returned by a NO_DATA_FOUND SELECT…INTO statement • Attempted division by zero ZERO_DIVIDE • Attempted an illegal cursor operation INVALID_CURSOR

  42. Trapping Exceptions • When one of these four errors occurs, the specified exception name is raised • If code is included in the EXCEPTION section for that exception name, then the exception is trapped and the code is executed • If no code is included for the exception, then the procedure will crash, ending abnormally

  43. Trapping Exceptions The EXCEPTION section is included after the main section and just before the END of the block. General syntax: EXCEPTION WHEN exception-name THEN statement(s); [WHEN exception-name THEN statement(s);] . . . [WHEN OTHERS THEN statement(s);]

  44. Trapping Exceptions • When an exception is raised, the flow of execution moves to the EXCEPTION section • Each exception name is examined until the name of the raised exception is found • The statements listed there are executed, and all other parts of the section are skipped

  45. Trapping Exceptions • If the name of the raised exception is not found: • The statements listed following the WHEN OTHERS THEN clause are executed (if it is included). • If there is no WHEN clause available to trap the exception, then the procedure crashes, ending abnormally. • Execution of the block ends after the exception is handled

  46. Trapping Exceptions Example: Use an anonymous block to retrieve and display the current balance for the customer whose last name is entered by the user. Use exceptions to handle possible errors of zero rows retrieved and more than one row retrieved.

  47. Trapping Exceptions SET SERVEROUTPUT ON SET VERIFY OFF ACCEPT lname PROMPT 'Customer last name? ' DECLARE v_balance customer.curr_bal%TYPE; BEGIN SELECT curr_bal INTO v_balance FROM customer WHERE lname = '&lname'; DBMS_OUTPUT.PUT_LINE('Current balance: ' || TO_CHAR(v_balance,'fm$999.00'));

  48. Trapping Exceptions EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('There is no customer' ' with a last name of ' || '&lname'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('There is more than' 'one customer with a last name of ' || '&lname'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Unknown error!'); END; /

  49. Trapping Exceptions Execution results (first case): Customer last name? Sinclair There is no customer with a last name of Sinclair PL/SQL procedure successfully completed. Execution results (second case): Customer last name? Akers There is more than one customer with a last name of Akers PL/SQL procedure successfully completed. Execution results (third case): Customer last name? Williams Current balance: $20.00 PL/SQL procedure successfully completed.

  50. User-defined Exceptions • The user can define, raise, and trap exceptions other than those that are predefined • They are often used to enforce business rules • Examples: • Hourly wage must be between $5 and $10 • Hours worked must be positive • Marital status must be S, M, or D

More Related