Introduction to PL/SQL

1. Introduction to PL/SQL

2. Objectives • After completing this lesson, you should be able to do the following: • Explain the need for PL/SQL • Explain the benefits of PL/SQL • Identify the different types of PL/SQL blocks • Output messages in PL/SQL

3. About PL/SQL • PL/SQL: • Stands for “Procedural Language extension to SQL” • Is Oracle Corporation’s standard data access language for relational databases • Seamlessly integrates procedural constructs with SQL

4. About PL/SQL • PL/SQL: • Provides a block structure for executable units of code. Maintenance of code is made easier with such a well-defined structure. • Provides procedural constructs such as: • Variables, constants, and data types • Control structures such as conditional statements and loops • Reusable program units that are written once and executed many times

5. PL/SQL Environment PL/SQL engine procedural Procedural statement executor PL/SQLblock SQL SQL statement executor Oracle database server

6. Benefits of PL/SQL • Integration of procedural constructs with SQL • Improved performance SQL 1 SQL 2 … SQL IF...THEN SQL ELSE SQL END IF; SQL

7. Benefits of PL/SQL • Modularized program development • Integration with Oracle tools • Portability • Exception handling

8. PL/SQL Block Structure • DECLARE (optional) • Variables, cursors, user-defined exceptions • BEGIN (mandatory) • SQL statements • PL/SQL statements • EXCEPTION (optional) • Actions to performwhen errors occur • END; (mandatory)

9. Block Types • Anonymous Procedure Function [DECLARE] BEGIN --statements [EXCEPTION] END; PROCEDURE name IS BEGIN --statements [EXCEPTION] END; FUNCTION name RETURN datatype IS BEGIN --statements RETURN value; [EXCEPTION] END;

10. Program Constructs Database Server Constructs Tools Constructs Anonymous blocks Anonymous blocks Stored procedures or functions Application procedures • or functions Stored packages Application packages Database triggers Application triggers Object types Object types

11. Create an Anonymous Block • Enter the anonymous block in the SQL Developer workspace:

12. Execute an Anonymous Block • Click the Run Script button to execute the anonymous block: Run Script

13. Enable DBMS Output DBMS Output Tab Test the Output of a PL/SQL Block • Enable output in SQL Developer by clicking the Enable DBMS Output button on the DBMS Output tab: • Use a predefined Oracle package and its procedure: • DBMS_OUTPUT.PUT_LINE DBMS_OUTPUT.PUT_LINE(' The First Name of the Employee is ' || f_name); …

14. Test the Output of a PL/SQL Block

15. Summary • In this lesson, you should have learned how to: • Integrate SQL statements with PL/SQL program constructs • Describe the benefits of PL/SQL • Differentiate between PL/SQL block types • Output messages in PL/SQL

16. Practice 1: Overview • This practice covers the following topics: • Identifying the PL/SQL blocks that execute successfully • Creating and executing a simple PL/SQL block

17. Declaring PL/SQL Variables

18. Objectives • After completing this lesson, you should be able to do the following: • Recognize valid and invalid identifiers • List the uses of variables • Declare and initialize variables • List and describe various data types • Identify the benefits of using the %TYPE attribute • Declare, use, and print bind variables

19. Use of Variables • Variables can be used for: • Temporary storage of data • Manipulation of stored values • Reusability SELECT first_name, department_id INTO v_fname,v_deptnoFROM … v_fname Jennifer v_deptno 10

20. Requirements for Variable Names • A variable name: • Must start with a letter • Can include letters or numbers • Can include special characters (such as $, _, and # ) • Must contain no more than 30 characters • Must not include reserved words

21. Handling Variables in PL/SQL • Variables are: • Declared and initialized in the declarative section • Used and assigned new values in the executable section • Passed as parameters to PL/SQL subprograms • Used to hold the output of a PL/SQL subprogram

22. Declaring and Initializing PL/SQL Variables • Syntax: • Examples: 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;

23. Declaring and Initializing PL/SQL Variables DECLARE v_myName VARCHAR2(20); BEGIN DBMS_OUTPUT.PUT_LINE('My name is: '|| v_myName); v_myName := 'John'; DBMS_OUTPUT.PUT_LINE('My name is: '|| v_myName); END; / 1 DECLARE v_myName VARCHAR2(20):= 'John'; BEGIN v_myName := 'Steven'; DBMS_OUTPUT.PUT_LINE('My name is: '|| v_myName); END; / 2

24. Delimiters in String Literals DECLARE v_event VARCHAR2(15); BEGIN v_event := q'!Father's day!'; DBMS_OUTPUT.PUT_LINE('3rd Sunday in June is : '|| v_event ); v_event := q'[Mother's day]'; DBMS_OUTPUT.PUT_LINE('2nd Sunday in May is : '|| v_event ); END; /

25. Types of Variables • PL/SQL variables: • Scalar • Composite • Reference • Large object (LOB) • Non-PL/SQL variables: Bind variables

26. Types of Variables TRUE 25-JAN-01 Snow White Long, long ago, in a land far, far away, there lived a princess called Snow White. . . 256120.08 Atlanta

27. Guidelines for Declaring and InitializingPL/SQL Variables • Follow naming conventions. • Use meaningful identifiers for variables. • Initialize variables designated as NOT NULL and CONSTANT. • Initialize variables with the assignment operator (:=) or the DEFAULT keyword: • Declare one identifier per line for better readability and code maintenance. v_myName VARCHAR2(20):='John'; v_myName VARCHAR2(20) DEFAULT 'John';

28. Guidelines for Declaring PL/SQL Variables • Avoid using column names as identifiers. • Use the NOT NULL constraint when the variable must hold a value. DECLARE employee_id NUMBER(6); BEGIN SELECT employee_id INTO employee_id FROM employees WHERE last_name = 'Kochhar'; END; /

29. The soul of the lazy man desires, and he has nothing; but the soul of the diligent shall be made rich. Scalar Data Types • Hold a single value • Have no internal components TRUE 25-JAN-01 256120.08 Atlanta

30. Base Scalar Data Types • CHAR [(maximum_length)] • VARCHAR2 (maximum_length) • NUMBER [(precision, scale)] • BINARY_INTEGER • PLS_INTEGER • BOOLEAN • BINARY_FLOAT • BINARY_DOUBLE

31. Base Scalar Data Types • DATE • TIMESTAMP • TIMESTAMP WITH TIME ZONE • TIMESTAMP WITH LOCAL TIME ZONE • INTERVAL YEAR TO MONTH • INTERVAL DAY TO SECOND

32. Declaring Scalar Variables • Examples: DECLARE v_emp_job VARCHAR2(9); v_count_loop BINARY_INTEGER := 0; v_dept_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; ...

33. %TYPE Attribute • Is used to declare a variable according to: • A database column definition • Another declared variable • Is prefixed with: • The database table and column • The name of the declared variable

34. Declaring Variables with the %TYPE Attribute • Syntax • Examples identifier table.column_name%TYPE; ... emp_lname employees.last_name%TYPE; ... ... balance NUMBER(7,2); min_balance balance%TYPE := 1000; ...

35. Declaring Boolean Variables • Only the TRUE, FALSE, and NULL values can be assigned to a Boolean variable. • Conditional expressions use the logical operators AND and OR and the unary operator NOT to check the variable values. • The variables always yield TRUE, FALSE, or NULL. • Arithmetic, character, and date expressions can be used to return a Boolean value.

36. Bind Variables • Bind variables are: • Created in the environment • Also called host variables • Created with the VARIABLE keyword • Used in SQL statements and PL/SQL blocks • Accessed even after the PL/SQL block is executed • Referenced with a preceding colon

37. Printing Bind Variables • Example: VARIABLE b_emp_salary NUMBER BEGIN SELECT salary INTO :b_emp_salary FROM employees WHERE employee_id = 178; END; / PRINT b_emp_salary SELECT first_name, last_name FROM employees WHERE salary=:b_emp_salary;

38. Printing Bind Variables • Example: VARIABLE b_emp_salary NUMBER SET AUTOPRINT ON DECLARE v_empno NUMBER(6):=&empno; BEGIN SELECT salary INTO :b_emp_salary FROM employees WHERE employee_id = v_empno; END; Output: 7000

39. LOB Data Type Variables Book (CLOB) Photo (BLOB) Movie (BFILE) NCLOB

40. Composite Data Types 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 PLS_INTEGER PLS_INTEGER

41. Summary • In this lesson, you should have learned how to: • Recognize valid and invalid identifiers • Declare variables in the declarative section of a PL/SQL block • Initialize variables and use them in the executable section • Differentiate between scalar and composite data types • Use the %TYPE attribute • Use bind variables

42. Practice 2: Overview • This practice covers the following topics: • Determining valid identifiers • Determining valid variable declarations • Declaring variables within an anonymous block • Using the %TYPE attribute to declare variables • Declaring and printing a bind variable • Executing a PL/SQL block

43. Writing Executable Statements

44. Objectives • After completing this lesson, you should be able to do the following: • Identify lexical units in a PL/SQL block • Use built-in SQL functions in PL/SQL • Describe when implicit conversions take place and when explicit conversions have to be dealt with • Write nested blocks and qualify variables with labels • Write readable code with appropriate indentation • Use sequences in PL/SQL expressions

45. Lexical Units in a PL/SQL Block • Lexical units: • Are building blocks of any PL/SQL block • Are sequences of characters including letters, numerals, tabs, spaces, returns, and symbols • Can be classified as: • Identifiers: v_fname, c_percent • Delimiters: ; , +, - • Literals: John, 428, True • Comments: --, /* */

46. PL/SQL Block Syntax and Guidelines • Literals • Character and date literals must be enclosed in single quotation marks. • Numbers can be simple values or in scientific notation. • Statements can span several lines. name := 'Henderson'; 1 2 3

47. Commenting Code • Prefix single-line comments with two hyphens (--). • Place multiple-line comments between the symbols /* and */. • Example: DECLARE ... v_annual_sal NUMBER (9,2); BEGIN /* Compute the annual salary based on the monthly salary input from the user */ v_annual_sal := monthly_sal * 12; --The following line displays the annual salary DBMS_OUTPUT.PUT_LINE(v_annual_sal); END; /

48. SQL Functions in PL/SQL • Available in procedural statements: • Single-row functions • Not available in procedural statements: • DECODE • Group functions

49. SQL Functions in PL/SQL: Examples • Get the length of a string: • Get the number of months an employee has worked: v_desc_size INTEGER(5); v_prod_description VARCHAR2(70):='You can use this product with your radios for higher frequency'; -- get the length of the string in prod_description v_desc_size:= LENGTH(prod_description); v_tenure:= MONTHS_BETWEEN (CURRENT_DATE, v_hiredate);

50. Using Sequences in PL/SQL Expressions • Starting in 11g: • Before 11g: DECLARE v_new_id NUMBER; BEGIN v_new_id := my_seq.NEXTVAL; END; / DECLARE v_new_id NUMBER; BEGIN SELECT my_seq.NEXTVAL INTO v_new_id FROM Dual; END; /