Cursors & Triggers

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; /