PL/SQL

1. PL/SQL • Declaring Variables • Writing Executable Statements • Interacting with the Oracle Server • Writing Control Structures • Working with Composite Datatypes • Cursors • Handling Exceptions

2. Objectives • After completing this lesson, you should be able to do the following: • List the benefits of PL/SQL • Recognize the basic PL/SQL block and its sections • Describe the significance of variables in PL/SQL • Declare PL/SQL variables • Execute a PL/SQL block

3. About PL/SQL • PL/SQL is an extension to SQL with design features of programming languages. • Data manipulation and query statements of SQL are included within procedural units of code. • PL/SQL not case sensitive language • A semicolon ; must end each PL/SQL command

4. Benefits of PL/SQL • Integration Application Oracle Server Shared library

5. Benefits of PL/SQL • Improve performance SQL Other DBMSs Application SQL SQL SQL SQL IF...THEN SQL ELSE SQL END IF; SQL Oracle with PL/SQL Application

6. DECLARE BEGIN EXCEPTION END; PL/SQL Block Structure • DECLARE – Optional Variables, cursors, user-defined exceptions • BEGIN – Mandatory • SQL statements • PL/SQL statements • EXCEPTION – Optional Actions to perform when errors occur • END; – Mandatory

7. DECLARE BEGIN EXCEPTION END; PL/SQL Block Structure DECLARE v_variable VARCHAR2(5); BEGIN SELECT column_name INTO v_variable FROM table_name; EXCEPTION WHEN exception_name THEN ... END;

8. Block Types • Anonymous Procedure Function PROCEDURE name IS BEGIN --statements [EXCEPTION] END; FUNCTION name RETURN datatype IS BEGIN --statements RETURN value; [EXCEPTION] END; [DECLARE] BEGIN --statements [EXCEPTION] END; • These blocks can be entirely separate or nested one within another. • Anonymous blocks are unnamed blocks. • Subprograms are named PL/SQL blocks that can take parameters and • can be invoked.

9. Declaring Variables

10. Use of Variables • Use variables for: • Temporary storage of data • Manipulation of stored values • Reusability • Ease of maintenance

11. Handling Variables in PL/SQL • Declare and initialize variables in the declaration section. • When you declare variable, the variables default value is always NULL • Assign new values to variables in the executable section. • Pass values into PL/SQL blocks through parameters. • View results through output variables.

12. “Four score and seven years ago our fathers brought forth upon this continent, a new nation, conceived in LIBERTY, and dedicated to the proposition that all men are created equal.” Types of Variables 25-OCT-99 TRUE 256120.08 Atlanta

13. Types of Variables • The slide illustrates the following variable datatypes: • TRUE represents a Boolean value. • 25-OCT-99 represents a DATE. • The photograph represents a BLOB. • The text of a speech represents a LONG RAW. • 256120.08 represents a NUMBER datatype with precision and scale. • The movie represents a BFILE. • The city name represents a VARCHAR2.

14. Types of Variables • PL/SQL variables: • Scalar • Composite • Reference • LOB (large objects) • Non-PL/SQL variables: Bind and host variables

15. Types of Variables - scalar • All PL/SQL variables have a datatype, which specifies a storage format, constraints, and valid range of values. • Scalar datatypes hold a single value. • The main datatypes are those that correspond to column types. • PL/SQL also supports Boolean variables.

16. Types of Variables - scalar

17. identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; Declare v_hiredate DATE; v_deptno NUMBER(2) NOT NULL := 10; v_location VARCHAR2(13) := 'Atlanta'; c_comm CONSTANT NUMBER := 1400; Declaring PL/SQL Variables Syntax Examples

18. Declaring PL/SQL Variables • Guidelines • Follow naming conventions. • Initialize variables designated as NOT NULL and CONSTANT. • Initialize identifiers by using the assignment operator (:=) or the DEFAULT reserved word. • Declare at most one identifier per line.

19. Naming Rules • Two variables can have the same name, provided they are in different blocks. • The variable name (identifier) should not be the same as the name of table columns used in the block. Adopt a naming convention for PL/SQL identifiers: for example, v_empno DECLARE empno NUMBER(4); BEGIN SELECT empno INTO empno FROM emp WHERE ename = 'SMITH'; END;

20. Variable Initialization and Keywords • Using: • Assignment operator (:=) • DEFAULT keyword • NOT NULL constraint

21. Assigning Values to Variables Syntax Examples Set a predefined hiredate for new employees. • identifier := expr; v_hiredate := '31-DEC-98'; Set the employee name to Maduro. v_ename := 'Maduro';

22. Scalar Datatypes • Hold a single value • Have no internal components 25-OCT-99 TRUE 256120.08 Atlanta

23. Base Scalar Datatypes • VARCHAR2 (maximum_length) • NUMBER [(precision, scale)] • DATE • CHAR [(maximum_length)] • LONG • BOOLEAN • BINARY_INTEGER

24. Scalar Variable Declarations • Examples v_job VARCHAR2(9); v_count BINARY_INTEGER := 0; v_total_sal NUMBER(9,2) := 0; v_orderdate DATE := SYSDATE + 7; c_tax_rate CONSTANT NUMBER(3,2) := 8.25; v_valid BOOLEAN NOT NULL := TRUE;

25. Types of Variables – Reference • Directly reference specific database column or row • Assume data type of associated column or row • %TYPE data declaration syntax: • var_nametablename.fieldname%TYPE; • %ROWTYPE data declaration syntax: • variable_nametablename%ROWTYPE;

26. The %TYPE Attribute • Declare a variable according to: • A database column definition • Another previously declared variable • Prefix %TYPE with: • The database table and column • The previously declared variable name

27. Declaring Variables with the %TYPE Attribute • Examples ... v_ename emp.ename%TYPE; v_balance NUMBER(7,2); v_min_balance v_balance%TYPE := 10; ...

28. Declaring Boolean Variables • Only the values TRUE, FALSE, and NULL can be assigned to a Boolean variable. • The variables are connected by the logical operators AND, OR, and NOT. • The variables always yield TRUE, FALSE, or NULL. • Arithmetic, character, and date expressions can be used to return a Boolean value.

29. PL/SQL Record Structure TRUE 23-DEC-98 ATLANTA PL/SQL table structure PL/SQL table structure 1 SMITH 2 JONES 3 NANCY 4 TIM 1 5000 2 2345 3 12 4 3456 VARCHAR2 NUMBER BINARY_INTEGER BINARY_INTEGER

30. LOB Datatype Variables Book (CLOB) Photo (BLOB) Movie (BFILE) NCLOB

31. Displaying PL/SQL Program Output in SQL*Plus • PL/SQL output buffer • Memory area on database server • Stores program’s output values before they are displayed to user • Should increase size • SET SERVEROUTPUT ON SIZE buffer_size • Default buffer size • 2000 bytes

32. Displaying PL/SQL Program Output in SQL*Plus (continued) • Display program output DBMS_OUTPUT.PUT_LINE('display_text'); • Display maximum of 255 characters of text data

33. Writing a PL/SQL Program • Write PL/SQL program in Notepad or another text editor • Copy and paste program commands into SQL*Plus • Press Enter after last program command • Type front slash ( / ) • Then press Enter again

34. DBMS_OUTPUT.PUT_LINE • An Oracle-supplied packaged procedure • An alternative for displaying data from a PL/SQL block • Must be enabled in SQL*Plus with SET SERVEROUTPUT ON

35. PL/SQL Program Commands

36. DECLARE BEGIN EXCEPTION END; Summary • PL/SQL blocks are composed of the following sections: • Declarative (optional) • Executable (required) • Exception handling (optional) • A PL/SQL block can be an anonymous block, procedure, or function.

37. Summary • PL/SQL identifiers: • Are defined in the declarative section • Can be of scalar, composite, reference, or LOB datatype • Can be based on the structure of another variable or database object • Can be initialized