Chapter 15 Introduction to PL/SQL

1. Chapter 15Introduction to PL/SQL Oracle9i: SQL

2. Chapter Objectives • Explain the benefits of using PL/SQL blocks versus several SQL statements • Identify the sections of a PL/SQL block and describe their contents • Identify the mandatory and optional sections of a PL/SQL block • Identify an anonymous block and its use Oracle9i: SQL

3. Chapter Objectives • Describe how to execute a PL/SQL block • Explain the purpose of a variable • Explain the difference between a constant and a variable • Identify valid variable names • List the valid datatypes for PL/SQL variables Oracle9i: SQL

4. Chapter Objectives • Assign a dynamic datatype for a PL/SQL variable • Initialize a PL/SQL variable • Use DML statements in a PL/SQL block • Determine when it is appropriate to use an IF statement • Identify all the clauses of an IF statement, and state when they should be used Oracle9i: SQL

5. Chapter Objectives • Create an IF statement • Identify the purpose of a loop, and name the types of loops available in Oracle9i • Create a basic loop • Create a FOR loop • Create a WHILE loop Oracle9i: SQL

6. PL/SQL • Procedure Language SQL • Advanced 4th generation programming language Oracle9i: SQL

7. 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 Oracle9i: SQL

8. Types of Blocks • Function • Procedure • Anonymous block Oracle9i: SQL

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

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

11. 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 Oracle9i: SQL

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

13. 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 Oracle9i: SQL

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

15. 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 Oracle9i: SQL

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

17. Example PL/SQL Block Oracle9i: SQL

18. 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 Oracle9i: SQL

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

20. 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 Oracle9i: SQL

21. 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 Oracle9i: SQL

22. PL/SQL Scalar Datatypes Oracle9i: SQL

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

24. Variable Initialization Examples Oracle9i: SQL

25. SELECT Statement Requires use of INTO clause to identify variable assigned to each data element Oracle9i: SQL

26. SELECT Statement Example Oracle9i: SQL

27. 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 Oracle9i: SQL

28. 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 Oracle9i: SQL

29. IF Statement Syntax Oracle9i: SQL

30. IF Statement Example Oracle9i: SQL

31. Basic Loop Syntax Oracle9i: SQL

32. Basic Loop Example Oracle9i: SQL

33. FOR Loop Syntax Oracle9i: SQL

34. FOR Loop Example Oracle9i: SQL

35. WHILE Loop Syntax Oracle9i: SQL

36. WHILE Loop Example Oracle9i: SQL

37. 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 Oracle9i: SQL

38. Nested Loop Example Oracle9i: SQL