Introduction to PL-SQL

1. Introduction to PL-SQL Lecture by Aj. ประวัฒน์ เปรมธีรสมบูรณ์ prawat237@yahoo.com

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 Week 9 Introduction to PL-SQL.,

3. Objectives (Con’t) After completing this lesson, you should be able to do the following: • Recognize the significance of the executable section • Write statements in the executable section • Describe the rules of nested blocks • Execute and test a PL/SQL block • Use coding conventions Week 9 Introduction to PL-SQL.,

4. PL/SQL Procedural Language Extension to SQL What is 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. Week 9 Introduction to PL-SQL.,

5. Benefits of PL/SQL • Integration Application Oracle Server Shared library Week 9 Introduction to PL-SQL.,

6. SQL Other DBMSs Application SQL SQL SQL SQL IF...THEN SQL ELSE SQL END IF; SQL Oracle with PL/SQL Application Benefits of PL/SQL • Improved Performance Week 9 Introduction to PL-SQL.,

7. DECLARE BEGIN EXCEPTION END; Benefits of PL/SQL • Modularize program development Week 9 Introduction to PL-SQL.,

8. Benefits of PL/SQL • It is portable. • You can declare identifiers. • You can program with procedural language control structures. • It can handle errors. Week 9 Introduction to PL-SQL.,

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

10. DECLARE v_variable VARCHAR2(5); BEGIN SELECTcolumn_name INTOv_variable FROMtable_name; EXCEPTION WHEN exception_name THEN ... END; DECLARE BEGIN EXCEPTION END; PL/SQL Block Structure Week 9 Introduction to PL-SQL.,

11. Block Types • PL/SQL code is built of Blocks, with a unique structure • There are two types of blocks in PL/SQL -: 1. Anonymous Blocks : have no name (like scripts) • Can be written and executed immediately in SQLPLUS • Can be used in a trigger 2. Named Blocks: • Procedures • Functions Week 9 Introduction to PL-SQL.,

12. [DECLARE] BEGIN --statements [EXCEPTION] END; PROCEDURE name IS BEGIN --statements [EXCEPTION] END; FUNCTION name RETURN datatype IS BEGIN --statements RETURN value; [EXCEPTION] END; AnonymousProcedureFunction Block Types Week 9 Introduction to PL-SQL.,

13. Stored procedure/function Anonymousblock DECLARE BEGIN Applicationprocedure/function Applicationtrigger EXCEPTION END; Databasetrigger Packagedprocedure/ function Program Constructs Week 9 Introduction to PL-SQL.,

14. Use of Variables Use variables for: • Temporary storage of data • Manipulation of stored values • Reusability • Ease of maintenance Week 9 Introduction to PL-SQL.,

15. Handling Variables in PL/SQL • Declare and initialize variables in the declaration section. • Assign new values to variables in the executable section. • Pass values into PL/SQL blocks through parameters. • View results through output variables. Week 9 Introduction to PL-SQL.,

16. Types of Variables • PL/SQL variables: • Scalar • Composite • Reference • LOB (large objects) • Non-PL/SQL variables: Bind and host variables Week 9 Introduction to PL-SQL.,

17. Types of Variables • PL/SQL variables: • Scalar • Composite • Reference • LOB (large objects) • Non-PL/SQL variables: Bind and host variables Week 9 Introduction to PL-SQL.,

18. 25-OCT-99 TRUE “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.” 256120.08 Atlanta Types of Variables Week 9 Introduction to PL-SQL.,

19. identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; Declare v_hiredateDATE; v_deptnoNUMBER(2) NOT NULL := 10; v_locationVARCHAR2(13) := 'Atlanta'; c_commCONSTANT NUMBER := 1400; Declaring PL/SQL Variables • Syntax • Examples Week 9 Introduction to PL-SQL.,

20. Declaring PL/SQL Variables Guidelines • Follow naming conventions. • Initialize variables designated as NOT NULL. • Initialize identifiers by using the assignment operator (:=) or the DEFAULT reserved word. • Declare at most one identifier per line. Week 9 Introduction to PL-SQL.,

21. 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. DECLARE empnoNUMBER(4); BEGIN SELECTempno INTOempno FROMemp WHERE ename = 'SMITH'; END; Adopt a naming convention for PL/SQL identifiers: for example, v_empno Week 9 Introduction to PL-SQL.,

22. Assigning Values to Variables • Syntax • Examples • Set a predefined hiredate for new employees. • Set the employee name to “Maduro.” • identifier := expr; v_hiredate := '31-DEC-98'; v_ename := 'Maduro'; Week 9 Introduction to PL-SQL.,

23. 25-OCT-99 TRUE “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.” 256120.08 Atlanta Scalar Datatypes • Hold a single value • Have no internal components Week 9 Introduction to PL-SQL.,

24. Base Scalar Datatypes • VARCHAR2 (maximum_length) • NUMBER [(precision, scale)] • DATE • CHAR [(maximum_length)] • LONG • LONG RAW • BOOLEAN • BINARY_INTEGER • PLS_INTEGER Week 9 Introduction to PL-SQL.,

25. Scalar Variable Declarations Examples v_jobVARCHAR2(9); v_countBINARY_INTEGER := 0; v_total_salNUMBER(9,2) := 0; v_orderdateDATE := SYSDATE + 7; c_tax_rateCONSTANT NUMBER(3,2) := 8.25; v_validBOOLEAN NOT NULL := TRUE; Week 9 Introduction to PL-SQL.,

26. Controlling PL/SQL Flow of Execution You can change the logical flow of statements using conditional IF statements and loop control structures. Conditional IF statements: • IF-THEN-END IF • IF-THEN-ELSE-END IF • IF-THEN-ELSIF-END IF Week 9 Introduction to PL-SQL.,

27. IF Statements Syntax Simple IF statement: Set the manager ID to 22 if the employee name is Osborne. IF condition THEN statements; [ELSIF condition THEN statements;] [ELSE statements;] END IF; IF v_ename = 'OSBORNE' THEN v_mgr := 22; END IF; Week 9 Introduction to PL-SQL.,

28. Simple IF Statements Set the job title to Salesman, the department number to 35, and the commission to 20% of the current salary if the last name is Miller. Example . . . IF v_ename = 'MILLER' THEN v_job := 'SALESMAN'; v_deptno := 35; v_new_comm := sal * 0.20; END IF; . . . Week 9 Introduction to PL-SQL.,

29. IF condition TRUE FALSE THEN actions (including further IFs) ELSE actions (including further IFs) IF-THEN-ELSE Statement Execution Flow Week 9 Introduction to PL-SQL.,

30. IF-THEN-ELSE Statements Set a flag for orders where there are fewer than five days between order date and ship date. Example ... IF v_shipdate - v_orderdate < 5 THEN v_ship_flag := 'Acceptable'; ELSE v_ship_flag := 'Unacceptable'; END IF; ... Week 9 Introduction to PL-SQL.,

31. IF condition FALSE TRUE ELSIF condition THEN actions TRUE FALSE ELSE actions THEN actions IF-THEN-ELSIF Statement Execution Flow Week 9 Introduction to PL-SQL.,

32. IF-THEN-ELSIF Statements For a given value, calculate a percentage of that value based on a condition. Example . . . IF v_start > 100 THEN v_start := 2 * v_start; ELSIF v_start >= 50 THEN v_start := .5 * v_start; ELSE v_start := .1 * v_start; END IF; . . . Week 9 Introduction to PL-SQL.,

33. Building Logical Conditions • You can handle null values with the IS NULL operator. • Any arithmetic expression containing a null value evaluates to NULL. • Concatenated expressions with null values treat null values as an empty string. Week 9 Introduction to PL-SQL.,

34. NOT AND TRUE FALSE NULL OR TRUE FALSE NULL TRUE TRUE FALSE NULL TRUE TRUE TRUE TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE NULL FALSE TRUE NULL NULL FALSE NULL NULL TRUE NULL NULL NULL NULL Logic Tables Build a simple Boolean condition with a comparison operator. Week 9 Introduction to PL-SQL.,

35. V_REORDER_FLAG V_AVAILABLE_FLAG V_FLAG TRUE FALSE NULL FALSE TRUETRUE TRUEFALSE NULLTRUE NULLFALSE Boolean Conditions What is the value of V_FLAG in each case? v_flag := v_reorder_flag AND v_available_flag; Week 9 Introduction to PL-SQL.,

36. Iterative Control: LOOP Statements • Loops repeat a statement or sequence of statements multiple times. • There are three loop types: • Basic loop • FOR loop • WHILE loop Week 9 Introduction to PL-SQL.,

37. Basic Loop Syntax LOOP statement1; . . . EXIT [WHEN condition]; END LOOP; where: conditionis a Boolean variable or expression (TRUE, FALSE, or NULL); Week 9 Introduction to PL-SQL.,

38. Basic Loop Example DECLARE v_ordiditem.ordid%TYPE := 601; v_counterNUMBER(2) := 1; BEGIN LOOP INSERT INTO item(ordid, itemid) VALUES(v_ordid, v_counter); v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP; END; Week 9 Introduction to PL-SQL.,

39. FOR Loop Syntax • Use a FOR loop to shortcut the test for the number of iterations. • Do not declare the index; it is declared implicitly. FOR counter in [REVERSE] lower_bound..upper_bound LOOP statement1; statement2; . . . END LOOP; Week 9 Introduction to PL-SQL.,

40. FOR Loop Guidelines • Reference the counter within the loop only; it is undefined outside the loop. • Use an expression to reference the existing value of a counter. • Do not reference the counter as the target of an assignment. Week 9 Introduction to PL-SQL.,

41. FOR Loop Insert the first 10 new line items for order number 601. Example DECLARE v_ordiditem.ordid%TYPE := 601; BEGIN FOR i IN 1..10 LOOP INSERT INTO item(ordid, itemid) VALUES(v_ordid, i); END LOOP; END; Week 9 Introduction to PL-SQL.,

42. WHILE condition LOOP statement1; statement2; . . . END LOOP; Condition is evaluated at the beginning of each iteration. WHILE Loop Syntax Use the WHILE loop to repeat statements while a condition is TRUE. Week 9 Introduction to PL-SQL.,

43. WHILE Loop Example ACCEPT p_new_order PROMPT 'Enter the order number: ' ACCEPT p_items - PROMPT 'Enter the number of items in this order: ' DECLARE v_countNUMBER(2) := 1; BEGIN WHILE v_count <= &p_items LOOP INSERT INTO item (ordid, itemid) VALUES (&p_new_order, v_count); v_count := v_count + 1; END LOOP; COMMIT; END; / Week 9 Introduction to PL-SQL.,

44. 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 Week 9 Introduction to PL-SQL.,

45. Declaring Variables with the %TYPE Attribute Examples ... v_enameemp.ename%TYPE; v_balanceNUMBER(7,2); v_min_balancev_balance%TYPE := 10; ... Week 9 Introduction to PL-SQL.,

46. SELECT Statements in PL/SQL Retrieve data from the database with SELECT. Syntax SELECT select_list INTO {variable_name[, variable_name]... | record_name} FROM table WHERE condition; Week 9 Introduction to PL-SQL.,

47. DECLARE v_employee tbl_m_employee%ROWTYPE; BEGIN SELECT * INTO v_employee FROM tbl_m_employee WHERE emp_code=10001; EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE('No Data Found'); END; Week 9 Introduction to PL-SQL.,

48. SELECT Statements in PL/SQL The INTO clause is required. Example DECLARE v_deptno NUMBER(2); v_loc VARCHAR2(15); BEGIN SELECT deptno, loc INTO v_deptno, v_loc FROM dept WHERE dname = 'SALES'; ... END; Week 9 Introduction to PL-SQL.,

49. Retrieving Data in PL/SQL Retrieve the order date and the ship date for the specified order. Example DECLARE v_orderdate ord.orderdate%TYPE; v_shipdate ord.shipdate%TYPE; BEGIN SELECT orderdate, shipdate INTO v_orderdate, v_shipdate FROM ord WHERE id = 620; ... END; Week 9 Introduction to PL-SQL.,

50. DECLARE v_sum_sal emp.sal%TYPE; v_deptno NUMBER NOT NULL := 10; BEGIN SELECT SUM(sal) -- group function INTO v_sum_sal FROM emp WHERE deptno = v_deptno; END; Retrieving Data in PL/SQL Return the sum of the salaries for all employees in the specified department. Example Week 9 Introduction to PL-SQL.,