PL/SQLPL/SQLA BRIEF OVERVIEWA BRIEF OVERVIEW

DAVID WILSONDAVID WILSON

PL/SQL User’s Guide and ReferencePL/SQL User’s Guide and Reference

MAIN FEATURESMAIN FEATURES

Block StructureBlock Structure Variables and ConstantsVariables and Constants CursorsCursors Control StructuresControl Structures ModularityModularity Data AbstractionData Abstraction Information HidingInformation Hiding Error HandlingError Handling

Block StructureBlock Structure

DECLARE – declaration of variables, DECLARE – declaration of variables, constants, and subprogramsconstants, and subprograms

BEGIN – statementsBEGIN – statements EXCEPTON – exception handlersEXCEPTON – exception handlers ENDEND

VARIABLES and VARIABLES and CONSTANTSCONSTANTS

May have any SQL or PL/SQL May have any SQL or PL/SQL datatypedatatype

Composite datatypes allowedComposite datatypes allowed Three methods of assigning valuesThree methods of assigning values

Simple assignmentSimple assignment Fetch value from databaseFetch value from database Return value from subprogramReturn value from subprogram

May be declared as constantMay be declared as constant

ASSIGNMENT ASSIGNMENT EXAMPLESEXAMPLES

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;

ASSIGNMENT ASSIGNMENT EXAMPLESEXAMPLES

DECLAREmy_sal REAL(7,2);PROCEDURE adjust_salary (emp_id INT,

salary IN OUT REAL) IS ...BEGINSELECT AVG(sal) INTO my_sal FROM

emp;adjust_salary(7788, my_sal); -- assigns a

new value to my_sal

CURSORSCURSORS Implicit vs. ExplicitImplicit vs. Explicit FOR loopsFOR loops AttributesAttributes

%TYPE%TYPE %ROWTYPE%ROWTYPE

DECLARECURSOR c1 ISSELECT ename, sal, hiredate, job FROM emp;emp_rec c1%ROWTYPE; -- declare record

variable that represents a row fetched from the emp table

CONTROL STRUCTURESCONTROL STRUCTURES

IF – THEN –ELSEIF – THEN –ELSE CASECASE FOR – LOOPFOR – LOOP WHILE – LOOPWHILE – LOOP EXIT – WHENEXIT – WHEN GOTOGOTO

IF – THEN - ELSEIF – THEN - ELSEDECLAREacct_balance NUMBER(11,2);acct CONSTANT NUMBER(4) := 3;debit_amt CONSTANT NUMBER(5,2) := 500.00;BEGINSELECT bal INTO acct_balance FROM accountsWHERE account_id = acctFOR UPDATE OF bal;IF acct_balance >= debit_amt THENUPDATE accounts SET bal = bal - debit_amtWHERE account_id = acct;ELSEINSERT INTO temp VALUES(acct, acct_balance, ’Insufficient funds’);-- insert account, current balance, and messageEND IF;COMMIT;END;

WHILEWHILEDECLAREsalary emp.sal%TYPE := 0;mgr_num emp.mgr%TYPE;last_name emp.ename%TYPE;starting_empno emp.empno%TYPE := 7499;BEGINSELECT mgr INTO mgr_num FROM empWHERE empno = starting_empno;WHILE salary <= 2500 LOOPSELECT sal, mgr, ename INTO salary, mgr_num, last_nameFROM emp WHERE empno = mgr_num;END LOOP;INSERT INTO temp VALUES (NULL, salary, last_name);COMMIT;EXCEPTIONWHEN NO_DATA_FOUND THENINSERT INTO temp VALUES (NULL, NULL, ’Not found’);COMMIT;END;

MODULARITYMODULARITY

BlocksBlocks SubprogramsSubprograms

ProceduresProcedures FunctionsFunctions

PackagesPackages

PROCEDURESPROCEDURESPROCEDURE award_bonus (emp_id NUMBER) ISbonus REAL;comm_missing EXCEPTION;BEGIN -- executable part starts hereSELECT comm * 0.15 INTO bonus FROM emp WHERE empno

= emp_id;IF bonus IS NULL THENRAISE comm_missing;ELSEUPDATE payroll SET pay = pay + bonus WHERE empno =

emp_id;END IF;EXCEPTION -- exception-handling part starts hereWHEN comm_missing THEN...END award_bonus;

PACKAGESPACKAGESCREATE PACKAGE emp_actions AS -- package specificationPROCEDURE hire_employee (empno NUMBER, ename

CHAR, ...);PROCEDURE fire_employee (emp_id NUMBER);END emp_actions;

CREATE PACKAGE BODY emp_actions AS -- package bodyPROCEDURE hire_employee (empno NUMBER, ename

CHAR, ...) ISBEGININSERT INTO emp VALUES (empno, ename, ...);END hire_employee;PROCEDURE fire_employee (emp_id NUMBER) ISBEGINDELETE FROM emp WHERE empno = emp_id;END fire_employee;END emp_actions;

DATA ABSTRACTIONDATA ABSTRACTION CollectionsCollections

TABLESTABLES VARRAYVARRAY

RecordsRecordsDECLARETYPE TimeRec IS RECORD (hours SMALLINT,

minutes SMALLINT);TYPE MeetingTyp IS RECORD (date_held DATE,duration TimeRec, -- nested recordlocation VARCHAR2(20),purpose VARCHAR2(50));

DATA ABSTRACTIONDATA ABSTRACTION

Object typesObject types EncapsulationEncapsulation Attributes and MethodsAttributes and Methods ModularModular MaintainableMaintainable ReusableReusable

INFORMATION HIDINGINFORMATION HIDING

AlgorithmsAlgorithms Hidden through Top - Down – DesignHidden through Top - Down – Design Accessed by interface specificationAccessed by interface specification

Data StructuresData Structures Hidden through data encapsulationHidden through data encapsulation Accessed by utility subprogramsAccessed by utility subprograms Can be made publicCan be made public

ERROR HANDLINGERROR HANDLING

DECLARE...comm_missing EXCEPTION; -- declare exceptionBEGIN...IF commission IS NULL THENRAISE comm_missing; -- raise exceptionEND IF;bonus := (salary * 0.10) + (commission * 0.15);EXCEPTIONWHEN comm_missing THEN ... -- process the

exception

OBJECT TYPESOBJECT TYPESCREATE 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);

ADVANTAGES OF PL/SQLADVANTAGES OF PL/SQL

Support for SQLSupport for SQL Support for Object - Oriented – Support for Object - Oriented –

ProgrammingProgramming Better performanceBetter performance Higher productivityHigher productivity Full portabilityFull portability Tight integration with SQLTight integration with SQL Tight securityTight security