1 / 20

PL/SQL A BRIEF OVERVIEW

PL/SQL A BRIEF OVERVIEW. DAVID WILSON. PL/SQL User’s Guide and Reference. MAIN FEATURES. Block Structure Variables and Constants Cursors Control Structures Modularity Data Abstraction Information Hiding Error Handling. Block Structure.

wilda
Download Presentation

PL/SQL A BRIEF OVERVIEW

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. PL/SQLA BRIEF OVERVIEW DAVID WILSON

  2. PL/SQL User’s Guide and Reference

  3. MAIN FEATURES • Block Structure • Variables and Constants • Cursors • Control Structures • Modularity • Data Abstraction • Information Hiding • Error Handling

  4. Block Structure • DECLARE – declaration of variables, constants, and subprograms • BEGIN – statements • EXCEPTON – exception handlers • END

  5. VARIABLES and CONSTANTS • May have any SQL or PL/SQL datatype • Composite datatypes allowed • Three methods of assigning values • Simple assignment • Fetch value from database • Return value from subprogram • May be declared as constant

  6. ASSIGNMENT EXAMPLES tax := price * tax_rate; valid_id := FALSE; SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id; credit_limit CONSTANT REAL := 5000.00;

  7. ASSIGNMENT EXAMPLES DECLARE my_sal REAL(7,2); PROCEDURE adjust_salary (emp_id INT, salary IN OUT REAL) IS ... BEGIN SELECT AVG(sal) INTO my_sal FROM emp; adjust_salary(7788, my_sal); -- assigns a new value to my_sal

  8. CURSORS • Implicit vs. Explicit • FOR loops • Attributes • %TYPE • %ROWTYPE DECLARE CURSOR c1 IS SELECT ename, sal, hiredate, job FROM emp; emp_rec c1%ROWTYPE; -- declare record variable that represents a row fetched from the emp table

  9. CONTROL STRUCTURES • IF – THEN –ELSE • CASE • FOR – LOOP • WHILE – LOOP • EXIT – WHEN • GOTO

  10. IF – THEN - ELSE DECLARE acct_balance NUMBER(11,2); acct CONSTANT NUMBER(4) := 3; debit_amt CONSTANT NUMBER(5,2) := 500.00; BEGIN SELECT bal INTO acct_balance FROM accounts WHERE account_id = acct FOR UPDATE OF bal; IF acct_balance >= debit_amt THEN UPDATE accounts SET bal = bal - debit_amt WHERE account_id = acct; ELSE INSERT INTO temp VALUES (acct, acct_balance, ’Insufficient funds’); -- insert account, current balance, and message END IF; COMMIT; END;

  11. WHILE DECLARE salary emp.sal%TYPE := 0; mgr_num emp.mgr%TYPE; last_name emp.ename%TYPE; starting_empno emp.empno%TYPE := 7499; BEGIN SELECT mgr INTO mgr_num FROM emp WHERE empno = starting_empno; WHILE salary <= 2500 LOOP SELECT sal, mgr, ename INTO salary, mgr_num, last_name FROM emp WHERE empno = mgr_num; END LOOP; INSERT INTO temp VALUES (NULL, salary, last_name); COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO temp VALUES (NULL, NULL, ’Not found’); COMMIT; END;

  12. MODULARITY • Blocks • Subprograms • Procedures • Functions • Packages

  13. PROCEDURES PROCEDURE award_bonus (emp_id NUMBER) IS bonus REAL; comm_missing EXCEPTION; BEGIN -- executable part starts here SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id; IF bonus IS NULL THEN RAISE comm_missing; ELSE UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id; END IF; EXCEPTION -- exception-handling part starts here WHEN comm_missing THEN ... END award_bonus;

  14. PACKAGES CREATE PACKAGE emp_actions AS -- package specification PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...); PROCEDURE fire_employee (emp_id NUMBER); END emp_actions; CREATE PACKAGE BODY emp_actions AS -- package body PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS BEGIN INSERT INTO emp VALUES (empno, ename, ...); END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM emp WHERE empno = emp_id; END fire_employee; END emp_actions;

  15. DATA ABSTRACTION • Collections • TABLES • VARRAY • Records DECLARE TYPE TimeRec IS RECORD (hours SMALLINT, minutes SMALLINT); TYPE MeetingTyp IS RECORD ( date_held DATE, duration TimeRec, -- nested record location VARCHAR2(20), purpose VARCHAR2(50));

  16. DATA ABSTRACTION • Object types • Encapsulation • Attributes and Methods • Modular • Maintainable • Reusable

  17. INFORMATION HIDING • Algorithms • Hidden through Top - Down – Design • Accessed by interface specification • Data Structures • Hidden through data encapsulation • Accessed by utility subprograms • Can be made public

  18. ERROR HANDLING DECLARE ... comm_missing EXCEPTION; -- declare exception BEGIN ... IF commission IS NULL THEN RAISE comm_missing; -- raise exception END IF; bonus := (salary * 0.10) + (commission * 0.15); EXCEPTION WHEN comm_missing THEN ... -- process the exception

  19. OBJECT TYPES CREATE TYPE Bank_Account AS OBJECT ( acct_number INTEGER(5), balance REAL, status VARCHAR2(10), MEMBER PROCEDURE open (amount IN REAL), MEMBER PROCEDURE verify_acct (num IN INTEGER), MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL), MEMBER PROCEDURE deposit (num IN INTEGER, amount IN REAL), MEMBER PROCEDURE withdraw (num IN INTEGER, amount IN REAL), MEMBER FUNCTION curr_bal (num IN INTEGER) RETURN REAL );

  20. ADVANTAGES OF PL/SQL • Support for SQL • Support for Object - Oriented – Programming • Better performance • Higher productivity • Full portability • Tight integration with SQL • Tight security

More Related