Chapter 15 Introduction to PL/SQL

1. Chapter 15Introduction to PL/SQL

2. PL/SQL • Procedure Language SQL • Advanced 4th generation programming language

3. Advantages of PL/SQL • Can include error handling and control structures • Can be stored and used by various application programs or users • Allows for tighter security by granting privileges for executing stored procedures rather than directly on database object

4. Types of Blocks • Function • Procedure • Anonymous block

5. Function • Named block that is stored on the Oracle9i server • Accepts zero or more input parameters • Returns one value

6. Procedure • Named block • Can process several variables • Returns no values • Interacts with application program using IN, OUT, or INOUT parameters

7. Anonymous Block • Not stored since it cannot be referenced by a name • Usually embedded in an application program, stored in a script file, or manually entered when needed

8. Basic Structure of a Block • Has three sections: • Declarative • Executable • Exception-handling • Executable section is the only required section; the rest are optional

9. Declarative Section • Identified by the DECLARE keyword • Used to define variables and constants referenced in the block • Forward execution – variable and constants must be declared before they can be referenced

10. Executable Section • Identified by the BEGIN keyword • Mandatory • Can consist of several SQL and/or PL/SQL statements

11. Exception-handling Section • Identified by the EXCEPTION keyword • Used to display messages or identify other actions to be taken when an error occurs • Addresses errors that occur during a statement’s execution

12. END Keyword • Used to close a PL/SQL block • Always followed by a semicolon

13. Example PL/SQL Block

14. Declaring a Variable • Reserves a temporary storage area in the computer’s memory • Every variable must have: • A name • A datatype • Variables can be initialized

15. Variable Names • Variable name can consist of up to 30 characters, numbers, or special symbols • Variable name must begin with a character

16. Constants • Variables that have a value that does not change during the execution of the block • Optional CONSTANT keyword can be used to designate a constant in the block’s declarative section

17. PL/SQL Datatypes • Scalar – holds a single value • Composite – collection of grouped data treated as one unit • Reference – holds pointers to other program items • Large Object (LOB) – holds location of large objects

18. PL/SQL Scalar Datatypes

19. Variable Initialization • Use DEFAULT keyword or (:=) assignment operator • Variable must be initialized if it is assigned a NOT NULL constraint

20. Variable Initialization Examples

21. SELECT Statement Requires use of INTO clause to identify variable assigned to each data element

22. SELECT Statement Example

23. Cursors • Implicit cursor – created for DML operations or a SELECT statement that retrieves only one row of results • Explicit cursor – required for SELECT statements retrieving more than one row of results

24. Execution Control • IF statement – executes statements based on a condition • Basic loop – executes statements until condition in EXIT clause is TRUE • FOR loop – uses counter • WHILE loop – executes statements until condition is FALSE

25. IF Statement Syntax

26. IF Statement Example

27. Basic Loop Syntax

28. Basic Loop Example

29. FOR Loop Syntax

30. FOR Loop Example

31. WHILE Loop Syntax

32. WHILE Loop Example

33. Nested Loops • Any type of loop can be nested inside another loop • Execution of the inner loop must be completed before control is returned to the outer loop

34. Nested Loop Example