using flow of control statements
DESCRIPTION
Using Flow of Control Statements. Unit objectives. After completing this unit, you should be able to: Use Compound statements (ATOMIC and NOT ATOMIC) Use labels in both compound statements and loops Work with Conditional Statements (IF and CASE) - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/1.jpg)
USING FLOW OF CONTROL STATEMENTS
![Page 2: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/2.jpg)
UNIT OBJECTIVESAfter completing this unit, you should be able to: Use Compound statements (ATOMIC and NOT
ATOMIC) Use labels in both compound statements and
loops Work with Conditional Statements (IF and
CASE) Implement Looping (FOR, WHILE, REPEAT,
and LOOP) Transfer Control using (GOTO, LEAVE,
ITERATE, RETURN, COMMIT, and ROLLBACK)
![Page 3: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/3.jpg)
COMPOUND STATEMENTS Can have labels:
P1: BEGIN … END P1 Can be atomic:
BEGIN ATOMIC Can contain:
Declarations Procedural statements Other compound statements
![Page 4: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/4.jpg)
EXAMPLE: NESTED COMPOUND STATEMENTSCREATE
![Page 5: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/5.jpg)
COMPOUND STATEMENTS Order of statements in compound statement:
SQL variables and condition declarations–Cursor declarations
Handler declarations Procedure body statements
Terminating statements with ; Procedure body has no terminating character Statement nested within other statements ends
with ;
![Page 6: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/6.jpg)
CONTROL FLOW STATEMENTS (1 OF 2) CASE statement
Select execution path based on multiple conditions
IF statement Select execution path based on evaluation of
conditions LOOP statement
Execute statements multiple times REPEAT statement
Execute statements until condition is true
![Page 7: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/7.jpg)
CONTROL FLOW STATEMENTS (2 OF 2) WHILE statement
Execute statements while condition is true FOR statement
Execute statements for each row of a table ITERATE
Transfers flow on control to labeled block or loop LEAVE statement
Transfer control out of loop or block for FOR, LOOP, REPEAT or WHILE
![Page 8: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/8.jpg)
CONDITIONAL STATEMENTS Syntax:
IF cond1 THEN statement ; ELSEIF cond2 THEN statement ; ELSE statement ;END IF;•
Example:IF rating = 1 THEN
UPDATE EMPLOYEE SET salary = salary*1.10 WHERE empno = i_num;
ELSEIF rating = 2 THENUPDATE EMPLOYEESET salary = salary*1.05 WHERE empno = i_num;ELSEUPDATE EMPLOYEE SET salary = salary*1.03 WHERE empno = i_num;END IF;
![Page 9: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/9.jpg)
CASE STATEMENT (1 OF 2) Simple CASE statement:
![Page 10: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/10.jpg)
CASE STATEMENT (2 OF 2) Searched CASE statement:
![Page 11: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/11.jpg)
LOOP STATEMENT Syntax:
[label] LOOP SQL-procedure-statement(s);
END LOOP [label] Example:
fetch_loop:LOOP
FETCH c1 INTO v_firstname, v_lastname;SET counter = counter + 1;IF counter = 51 THEN
LEAVE fetch_loop;END IF;
END LOOP fetch_loop;
![Page 12: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/12.jpg)
FOR STATEMENT
![Page 13: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/13.jpg)
OTHER CONTROL FLOW STATEMENTS
![Page 14: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/14.jpg)
TRANSFER CONTROL USING LEAVE AND ITERATEFETCH
![Page 15: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/15.jpg)
TRANSFER CONTROL USING GOTO GOTO label
IF v_DEPT= ‘D11’GOTO bye;
Bye:
![Page 16: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/16.jpg)
RETURN RETURN expression
IF v_DEPT= ‘E11’ RETURN 1;
![Page 17: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/17.jpg)
COMMIT AND ROLLBACK COMMIT
COMMIT WORK; ROLLBACK
ROLLBACK WORK;
![Page 18: Using Flow of Control Statements](https://reader035.vdocuments.us/reader035/viewer/2022062310/56816356550346895dd401fe/html5/thumbnails/18.jpg)
UNIT SUMMARY Having completed this unit, you should be able to: Use Compound statements (ATOMIC and NOT
ATOMIC) Use labels in both compound statements and
loops Work with Conditional Statements (IF and
CASE) Implement Looping (FOR, WHILE, REPEAT,
and LOOP) Transfer Control using (GOTO, LEAVE,
ITERATE, RETURN, COMMIT, and ROLLBACK)