340 likes | 516 Views
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.
E N D
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 • 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
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
PL/SQL • Exception-Handling Section • Actions which should occur during errors in execution of block • For example, no rows returned from SELECT statement
PL/SQL • END Section • Close PL/SQL block • Followed by semicolon
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;
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
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
Cursors • Explicit cursor • Declared • Opened • Closed
Cursors • Declare Cursor DECLARE CURSOR books_cursor IS SELECT title, retail FROM books NATURAL JOIN orderitems WHERE order# = 1012;
Cursors • Open Explicit Cursor • Memory is allocated for data returned by query OPEN books_cursor;
Cursors • Close Explicit Cursor • After all data is retrieved from cursor, close cursor CLOSE books_cursor;
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
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; /
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
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
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; /
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
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; /
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
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; /