Program with PL/SQL

1. Program with PL/SQL Lesson 4

2. Writing Control Structure

4. Controlling PL/SQL Flow of Execution • Change the logical execution 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

12. CASE Expressions • A CASE expressions selects a result and returns it • To select the result. The CASE expressions uses an expression whose values is used to select one of several alternatives CASE selector WHEN expressions1 THEN result1 WHEN expressions2 THEN result2 … WHEN expressionsN THEN resultN [ELSE resultN+1;] END;

13. declare vgrade char(1) := upper('&p_grade'); va varchar2(20); begin va := case vgrade when 'A' then 'Excellent' when 'B' then 'very good' when 'C' then 'Good' else 'no such grade' end; dbms_output.put_line('Grade: '||vgrade||'App'||va); end;

14. Handling Nulls • Simple comparison involving nulls always yield NULL • Applying the logical operator NOT to a null yields NULL • In conditional control statements, if the condition yields NULL, its associated sequence of statements is not executed

15. Logic Tables

17. 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

18. Basic Loops Syntax: LOOP -- delimiter Statement1;-- statements ... EXIT [WHEN condition]; -- EXIT statements END LOOP -- delimiter

19. Contoh…. DECLARE ord_id order.order_id%type := 97; vcount number(2) := 0; BEGIN LOOP INSERT INTO order(order_id,item_id,product_id,actual_price) VALUES (ord_id,vcount,1011,0); vcount := vcount + 1; EXIT WHEN vcount > 10; END LOOP; END; /

20. WHILE Loops Syntax: WHILE condition LOOP Statement1; Statement2; ... END LOOP Condition is evaluated at the beginning of each iteration Use the WHILE loop to repeat statements while a condition is TRUE

21. Contoh… DECLARE ord_id order.order_id%type := 97; vcount number(2) := 1; BEGIN WHILE vcount <= 10 LOOP INSERT INTO order(order_id,item_id,product_id,actual_price) VALUES (ord_id,vcount,1011,0); vcount := vcount + 1; END LOOP; END; /

22. FOR Loops Syntax: FOR counter IN [REVERSE] lower_bound .. Upper_bound LOOP Statement1; Statement2; ... END LOOP • Use a FOR loop to shortcut the test for the number of iterations • Do not declare the counter; it is declared implicitly • ‘lower_bound .. Upper_bound’ is required syntax

23. Contoh… DECLARE ord_id order.order_id%type := 97; BEGIN FOR vcount IN 1..10 LOOP INSERT INTO order(order_id,item_id,product_id,actual_price) VALUES (ord_id,vcount,1011,0); END LOOP; END; /

24. FOR Loops Guidelines • Reference the counter within the loop only; it is undefined outside the loop • Do not reference the counter as the targert of an assignment

25. Guidelines While Using Loops • Use the basic loop when the statements inside the loop must execute at least once • Use the WHILE loop if the condition has to be evaluated at the start of each iteration • Use a FOR loop if the number of iterations is known

26. Nested Loops and Labels • Nest loops to multiple levels • Use labels to distinguish between blocks and loops • Exit the outer loop with the EXIT statement that references the label

27. declare the_counter number(2) := 0; total_done varchar2(3) := 'NO'; inner_done varchar2(3) := 'NO'; begin <<outer_loop>> loop exit when the_counter > 10; dbms_output.put_line('outer loop : the_counter = '||the_counter); the_counter := the_counter + 1; <<inner_loop>> loop exit inner_loop when inner_done = 'YES'; dbms_output.put_line('inner loop:the_counter= '|| the_counter); if the_counter = 5 then inner_done := 'YES'; dbms_output.put_line('will exit inner loop'); else the_counter := the_counter + 1; end if; end loop inner_loop; exit outer_loop when total_done = 'YES'; end loop outer_loop; end;

28. Practice Overview

29. Practice 1 Create a PL/SQL block that rewards an employee by appending an asterisk in the STARS column for every $1000 of the employee’s salary. • Use the DEFINE command to provide the employee_id. Pass the value to the PL/SQL block through a iSQL*Plus substitution variable • Initialize a v_asterisk variable that contains a NULL • Append an asterisk to the string for every $1000 of the salary amount. For example, if the employee has a salary amount $8000, the string of asterisk should contain eight asterisk. If the employee has a salary amount of $12500, the string of asterisks should contains 13 asterisks • Update the STARS column for the employee with the string of asterisk • COMMIT

30. Practice 2 In a loop, use a cursor to retrieve the department number and department name from the DEPARTMENTS table for those departments whose DEPARTMENT_ID is less than 100. Pass the department number to another cursor to retrieve from the EMPLOYEES table the details of employee last name, job, hire_date, and salary of those employees whose EMPLOYEE_ID is less than 120 and who work in that department.

31. Practice 3 In a loop use the iSQL*Plus substitution parameter created in step 1 and gather the salaries of the top n people from the EMPLOYEES table. There should be no duplication in the salaries. If two employees earn the same salary, the salary should be picked up only once and store the salaries in the TOP_DOGS table