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;
/
 
本文出自 “迈小步、不停步！” 博客