1 / 18

Mastering Control Flow Statements in SQL Procedures

Learn to work with compound statements, conditional statements (IF and CASE), looping (FOR, WHILE, REPEAT, LOOP), and how to transfer control in SQL procedures. Understand the syntax and examples of different flow control statements.

odell
Télécharger la présentation

Mastering Control Flow Statements in SQL Procedures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using Flow of Control Statements

  2. 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) • Implement Looping (FOR, WHILE, REPEAT, and LOOP) • Transfer Control using (GOTO, LEAVE, ITERATE, RETURN, COMMIT, and ROLLBACK)

  3. Compound statements • Can have labels: • P1: BEGIN … END P1 • Can be atomic: • BEGIN ATOMIC • Can contain: • Declarations • Procedural statements • Other compound statements

  4. Example: Nested compound statementsCREATE

  5. 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 ;

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

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

  8. 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 THEN UPDATE EMPLOYEE SET salary = salary*1.05 WHERE empno = i_num; ELSE UPDATE EMPLOYEE SET salary = salary*1.03 WHERE empno = i_num;END IF;

  9. CASE statement (1 of 2) • Simple CASE statement:

  10. CASE statement (2 of 2) • Searched CASE statement:

  11. 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;

  12. FOR statement

  13. Other control flow statements

  14. Transfer control using LEAVE and ITERATEFETCH

  15. Transfer control using GOTO • GOTO label IF v_DEPT= ‘D11’ GOTO bye; Bye:

  16. RETURN • RETURN expression IF v_DEPT= ‘E11’ RETURN 1;

  17. COMMIT and ROLLBACK • COMMIT • COMMIT WORK; • ROLLBACK • ROLLBACK WORK;

  18. 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)

More Related