1 / 22

Cursors & Triggers

Cursors & Triggers. INFSY 445 Fall 2005. PL/SQL. A unit of PL/SQL code is called a block [DECLARE] identifies variables & constants BEGIN identifies executable statements [EXCEPTION] identifies error handlers END; terminates the block. PL/SQL.

cfrierson
Download Presentation

Cursors & Triggers

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. Cursors & Triggers INFSY 445 Fall 2005

  2. PL/SQL • A unit of PL/SQL code is called a block [DECLARE] identifies variables & constants BEGIN identifies executable statements [EXCEPTION] identifies error handlers END; terminates the block

  3. PL/SQL • Declarative Section • Variables or constants used in block identified • Variable used to reserve temporary storage • Constant is a variable who’s value does not change during execution

  4. PL/SQL • Executable Section • SQL statements or PL/SQL statements • SQL used to access data in tables • PL/SQL focus on data within block • Mandatory section

  5. PL/SQL • Exception-Handling Section • Actions which should occur during errors in execution of block • For example, no rows returned from SELECT statement

  6. PL/SQL • END Section • Close PL/SQL block • Followed by semicolon

  7. PL/SQL DECLARE c_rateincrease CONSTANT NUMBER(3,2) :=1.2; v_title VARCHAR2(30); v_retail books.retail%TYPE; v_newretail NUMBER(5,2); BEGIN SELECT title, retail, retail*c_rateincrease INTO v_title, v_retail, v_newretail FROM books WHERE isbn = ‘1059831198’ DBMS_OUTPUT.PUT_LINE (‘The new price for ‘ || v_title || ‘ is $’ || v_newretail); END;

  8. PL/SQL • SELECT statement in PL/SQL does not display results as in SQL*Plus • PUT_LINE function of DBMS_OUTPUT package is used to display results • May need to turn on buffer to see results • SET SERVEROUTPUT ON • Command entered at SQL> • SQL> enter / to execute code in block

  9. Cursors • Implicit cursor = automatic, memory used to stored returned row of data • Explicit cursor • Created and managed by user • Needed when PL/SQL block retrieves more than one row of data

  10. Cursors • Explicit cursor • Declared • Opened • Closed

  11. Cursors • Declare Cursor DECLARE CURSOR books_cursor IS SELECT title, retail FROM books NATURAL JOIN orderitems WHERE order# = 1012;

  12. Cursors • Open Explicit Cursor • Memory is allocated for data returned by query OPEN books_cursor;

  13. Cursors • Close Explicit Cursor • After all data is retrieved from cursor, close cursor CLOSE books_cursor;

  14. Cursors • SELECT statement can’t retrieve data from explicit cursor • FETCH • Retrieves rows from the cursor • Assigns data to variables declared in PL/SQL block

  15. Cursors DECLARE v_title books.title%TYPE; v_retail books.retail%TYPE; CURSOR books_cursor IS SELECT title, retail FROM books NATURAL JOIN orderitems WHERE order# = 1012; BEGIN OPEN books_cursor; FETCH books_cursor INTO v_title, v_retail; DBMS_OUTPUT.PUT_LINE (‘Book title: ‘ || v_title || ‘ Retail price: ‘ || v_retail); CLOSE books_cursor; END; /

  16. Cursors • Four attributes used as conditions • %ROWCOUNT • Identifies number of rows processed • %FOUND • TRUE is one or more rows processed • FALSE if no rows processed

  17. Cursors • %NOTFOUND • TRUE if no rows processed • FALSE is one or more rows processed • %ISOPEN • TRUE if cursor not closed after processing • FALSE if cursor is closed

  18. Cursors DECLARE v_title books.title%TYPE; v_retail books.retail%TYPE; CURSOR books_cursor IS SELECT title, retail FROM books NATURAL JOIN orderitems WHERE order# = 1012; BEGIN OPEN books_cursor; LOOP FETCH books_cursor INTO v_title, v_retail; EXIT WHEN books_curosr%NOTFOUND; DBMS_OUTPUT.PUT_LINE (‘Book title: ‘ || v_title || ‘ Retail price: ‘ || v_retail); END LOOP; CLOSE books_cursor; END; /

  19. Cursors • FOR Loop • Used to process explicit cursors • Can automatically open and close a cursor • Fetch data from cursor • Row data is assigned to a record • Record is a composite datatype with same structure as data being retrieved • %ROWTYPE

  20. Cursors DECLARE CURSOR books_cursor IS SELECT title, retail FROM books NATURAL JOIN orderitems WHERE order# = 1012; r_books books%ROWTYPE; BEGIN FOR r_books IN books_cursor LOOP DBMS_OUTPUT.PUT_LINE (‘Book title: ‘ || r_books.title || ‘ Retail price: ‘ || r_books.retail); END LOOP; END; /

  21. Exception Handling • Signal that an error has occurred during execution of PL/SQL block • Most common exceptions: • NO_DATA_FOUND • TOO_MANY_ROWS • ZERO_DIVIDE

  22. Exception Handling DECLARE v_title books.title%TYPE; v_retail books.retail%TYPE; BEGIN SELECT title, retail INTO v_title, v_retial FROM books WHERE retail > 100; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (‘No rows were retrieved from the table’); END; /

More Related