PL/SQL A BRIEF OVERVIEW

1
PL/SQLA BRIEF OVERVIEW

• DAVID WILSON

2

• PL/SQL Users 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 c1ROWTYPE -- 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 gt 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.salTYPE 0
• mgr_num emp.mgrTYPE
• last_name emp.enameTYPE
• starting_empno emp.empnoTYPE 7499
• BEGIN
• SELECT mgr INTO mgr_num FROM emp
• WHERE empno starting_empno
• WHILE salary lt 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