PL-SQL01-Control Structures

对于一个即将有成就的DBA来说，不懂PLSQL，真的有些说不过去，本人下定决心开始玩PLSQL了，

教材：Oracle官方文档 Oracle® Database PL/SQL User's Guide and Reference  10g Release 2 (

时间：15--30天

比起C，java来说PLSQL是简单多了，本人先做一个实验来说明控制结构

1.Conditional Control

Background：Often, it is necessary to take alternative actions depending on circumstances. The
IF-THEN-ELSE statement lets you execute a sequence of statements conditionally.
The IF clause checks a condition, the THEN clause defines what to do if the condition
is true and the ELSE clause defines what to do if the condition is false or null.

Example：shows the use of IF-THEN-ELSE to determine the salary raise an employee receives based on the current salary of the employee.To choose among several values or courses of action, you can use CASE constructs. The CASE expression evaluates a condition and returns a value for each case. The case statement evaluates a condition and performs an action, such as an entire PL/SQL block, for each case.

DECLARE 
   jobid      employees.job_id%TYPE; 
   empid      employees.employee_id%TYPE := 115; 
   sal        employees.salary%TYPE; 
   sal_raise  NUMBER(3,2); 
BEGIN 
  SELECT job_id, salary INTO jobid, sal from employees WHERE employee_id = empid; 
  CASE 
    WHEN jobid = 'PU_CLERK' THEN 
        IF sal < 3000 THEN sal_raise := .12; 
          ELSE sal_raise := .09; 
        END IF; 
    WHEN jobid = 'SH_CLERK' THEN 
        IF sal < 4000 THEN sal_raise := .11; 
          ELSE sal_raise := .08; 
        END IF; 
    WHEN jobid = 'ST_CLERK' THEN 
        IF sal < 3500 THEN sal_raise := .10; 
          ELSE sal_raise := .07; 
        END IF; 
    ELSE 
     BEGIN 
       DBMS_OUTPUT.PUT_LINE('No raise for this job: ' || jobid); 
     END; 
  END CASE; 
  UPDATE employees SET salarysalary = salary + salary * sal_raise  
    WHERE employee_id = empid; 
  COMMIT; 
END; 
/ 

 2.Iterative Control

Background：LOOP statements let you execute a sequence of statements multiple times. You place the keyword LOOP before the first statement in the sequence and the keywords END LOOP after the last statement in the sequence. The following example shows the simplest kind of loop, which repeats a sequence of statements continually:
        LOOP
        -- sequence of statements
        END LOOP;
The FOR-LOOP statement lets you specify a range of integers, then execute a sequence
of statements once for each integer in the range.

Example： Loop inserts 100 numbers, square roots, squares, and the sum of squares into a database table:

CREATE TABLE sqr_root_sum (num NUMBER, sq_root NUMBER(6,2), 
                           sqr NUMBER, sum_sqrs NUMBER); 
DECLARE 
   s PLS_INTEGER; 
BEGIN 
  FOR i in 1..100 LOOP 
    s := (i * (i + 1) * (2*i +1)) / 6; -- sum of squares 
    INSERT INTO sqr_root_sum VALUES (i, SQRT(i), i*i, s ); 
  END LOOP; 
END; 
/ 

Background：The WHILE-LOOP statement associates a condition with a sequence of statements. Before each iteration of the loop, the condition is evaluated. If the condition is true, the
sequence of statements is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and control passes to the next statement.

Example： you find the first employee who has a salary over $15000 and is higher in the chain of command than employee 120:

CREATE TABLE temp (tempid NUMBER(6), tempsal NUMBER(8,2), tempname VARCHAR2(25)); 
DECLARE 
   sal            employees.salary%TYPE := 0; 
   mgr_id         employees.manager_id%TYPE; 
   lname          employees.last_name%TYPE; 
   starting_empid employees.employee_id%TYPE := 120; 
BEGIN 
   SELECT manager_id INTO mgr_id FROM employees  
      WHERE employee_id = starting_empid; 
   WHILE sal <= 15000 LOOP -- loop until sal > 15000 
      SELECT salary, manager_id, last_name INTO sal, mgr_id, lname 
         FROM employees WHERE employee_id = mgr_id; 
   END LOOP; 
   INSERT INTO temp VALUES (NULL, sal, lname); -- insert NULL for tempid 
   COMMIT; 
EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
      INSERT INTO temp VALUES (NULL, NULL, 'Not found'); -- insert NULLs 
      COMMIT; 
END; 
/ 

3.Sequential Control

Background：The GOTO statement lets you branch to a label unconditionally. The label, an
undeclared identifier enclosed by double angle brackets, must precede an executable statement or a PL/SQL block. When executed, the GOTO statement transfers control to the labeled statement or block

Example：

DECLARE 
  total   NUMBER(9) := 0; 
  counter NUMBER(6) := 0; 
BEGIN 
  <<calc_total>> 
    counter := counter + 1; 
    total := total + counter * counter; 
    -- branch to print_total label when condition is true 
    IF total > 25000 THEN GOTO print_total; 
      ELSE GOTO calc_total; 
    END IF; 
  <<print_total>> 
  DBMS_OUTPUT.PUT_LINE('Counter: ' || TO_CHAR(counter) || ' Total: ' ||  
TO_CHAR(total)); 
END; 
/