oracle笔记pl/sql流程控制

/*

pl/sql流程控制

*/

/*
查询出 150号 员工的工资, 若其工资大于或等于 10000 则打印 'salary >= 10000'; 
若在 5000 到 10000 之间, 则打印 '5000<= salary < 10000'; 否则打印 'salary < 5000'
*/
DECLARE
v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE employee_id = 150;
  dbms_output.put_line('150号员工的薪水为' || v_salary);
  IF v_salary >= 10000 THEN dbms_output.put_line('salary >= 10000');
  ELSIF v_salary >= 5000 THEN dbms_output.put_line('5000<= salary < 10000');
  ELSE dbms_output.put_line('salary < 5000');
  END IF; --END IF;相当于java中的结束大括号，pl/sql中使用END IF关键字充当结束大括号
END;

--
DECLARE
v_salary employees.salary%TYPE;
v_employee_id employees.employee_id%TYPE;
BEGIN
  v_employee_id := 150; --给v_employee_id变量赋值
  SELECT salary INTO v_salary FROM employees WHERE employee_id = v_employee_id; --使用v_employee_id变量
  dbms_output.put_line(v_employee_id || '号员工的薪水=' || v_salary);
  IF v_salary >= 10000 THEN dbms_output.put_line('salary >= 10000');
  ELSIF v_salary >= 5000 THEN dbms_output.put_line('5000<= salary < 10000');
  ELSE dbms_output.put_line('salary < 5000');
  END IF; --END IF;相当于java中的结束大括号，pl/sql中使用END IF关键字充当结束大括号
END;


--另外一种写法
DECLARE
v_salary employees.salary%TYPE;
v_employee_id employees.employee_id%TYPE;
v_temp VARCHAR2(50);
BEGIN
  v_employee_id := 150; --给v_employee_id变量赋值
  SELECT salary INTO v_salary FROM employees WHERE employee_id = v_employee_id; --使用v_employee_id变量
  dbms_output.put_line(v_employee_id || '号员工的薪水=' || v_salary);
  IF v_salary >= 10000 THEN v_temp := 'salary >= 10000';
  ELSIF v_salary >= 5000 THEN v_temp := '5000<= salary < 10000';
  ELSE  v_temp := 'salary < 5000';
  END IF; --END IF;相当于java中的结束大括号，pl/sql中使用END IF关键字充当结束大括号
  dbms_output.put_line('薪水=' || v_salary || ',' || v_temp);
END;

--改成case的写法
/*
--以下这种写法是错误的，会报错
DECLARE
v_salary employees.salary%TYPE;
v_employee_id employees.employee_id%TYPE;
v_temp VARCHAR2(50);
BEGIN
  v_employee_id := 150; --给v_employee_id变量赋值
  SELECT salary INTO v_salary FROM employees WHERE employee_id = v_employee_id; --使用v_employee_id变量
  dbms_output.put_line(v_employee_id || '号员工的薪水=' || v_salary);
  v_temp :=
  CASE v_salary WHEN v_salary >= 10000 THEN  'salary >= 10000'
                WHEN v_salary >= 5000 THEN '5000<= salary < 10000'
  ELSE  'salary < 5000'
  END;
  dbms_output.put_line('薪水=' || v_salary || ',' || v_temp);             
END;

*/

--以下写法正确
DECLARE
v_salary employees.salary%TYPE;
v_employee_id employees.employee_id%TYPE;
v_temp VARCHAR2(50);
BEGIN
  v_employee_id := 150; --给v_employee_id变量赋值
  SELECT salary INTO v_salary FROM employees WHERE employee_id = v_employee_id; --使用v_employee_id变量
  dbms_output.put_line(v_employee_id || '号员工的薪水=' || v_salary);
  v_temp :=
  CASE WHEN v_salary >= 10000 THEN  'salary >= 10000'
                WHEN v_salary >= 5000 THEN '5000<= salary < 10000'
  ELSE  'salary < 5000'
  END;
  dbms_output.put_line('薪水=' || v_salary || ',' || v_temp);             
END;

/*

--以下这种写法是错误的，会报错
DECLARE
v_salary employees.salary%TYPE;
v_employee_id employees.employee_id%TYPE;
v_temp VARCHAR2(50);
BEGIN
  v_employee_id := 150; --给v_employee_id变量赋值
  SELECT salary INTO v_salary FROM employees WHERE employee_id = v_employee_id; --使用v_employee_id变量
  dbms_output.put_line(v_employee_id || '号员工的薪水=' || v_salary);
  
  (CASE WHEN v_salary >= 10000 THEN  v_temp := 'salary >= 10000'
                WHEN v_salary >= 5000 THEN v_temp := '5000<= salary < 10000'
  ELSE v_temp := 'salary < 5000'
  END);
  dbms_output.put_line('薪水=' || v_salary || ',' || v_temp);             
END;

--以下这种写法是错误的，会报错
DECLARE
v_salary employees.salary%TYPE;
v_employee_id employees.employee_id%TYPE;
v_temp VARCHAR2(50);
BEGIN
  v_employee_id := 150; --给v_employee_id变量赋值
  SELECT salary INTO v_salary FROM employees WHERE employee_id = v_employee_id; --使用v_employee_id变量
  dbms_output.put_line(v_employee_id || '号员工的薪水=' || v_salary);
  
  CASE WHEN v_salary >= 10000 THEN  v_temp := 'salary >= 10000'
                WHEN v_salary >= 5000 THEN v_temp := '5000<= salary < 10000'
  ELSE v_temp := 'salary < 5000'
  END;
  dbms_output.put_line('薪水=' || v_salary || ',' || v_temp);             
END;

*/

--以下写法正确
DECLARE
v_salary employees.salary%TYPE;
v_employee_id employees.employee_id%TYPE;
v_temp VARCHAR2(50);
BEGIN
  v_employee_id := 150; --给v_employee_id变量赋值
  SELECT salary INTO v_salary FROM employees WHERE employee_id = v_employee_id; --使用v_employee_id变量
  dbms_output.put_line(v_employee_id || '号员工的薪水=' || v_salary);
  v_temp :=
  (CASE WHEN v_salary >= 10000 THEN  'salary >= 10000'
                WHEN v_salary >= 5000 THEN '5000<= salary < 10000'
  ELSE  'salary < 5000'
  END);
  dbms_output.put_line('薪水=' || v_salary || ',' || v_temp);             
END;

--
DECLARE
v_salary employees.salary%TYPE;
v_employee_id employees.employee_id%TYPE;
v_temp VARCHAR2(50);
BEGIN
  v_employee_id := 150; --给v_employee_id变量赋值
  SELECT salary INTO v_salary FROM employees WHERE employee_id = v_employee_id; --使用v_employee_id变量
  dbms_output.put_line(v_employee_id || '号员工的薪水=' || v_salary);
  v_temp :=
  CASE trunc(v_salary / 5000) 
    WHEN 0 THEN  'salary < 5000'
    WHEN 1 THEN '5000<= salary < 10000'
    ELSE 'salary >= 10000'
  END;
  dbms_output.put_line('薪水=' || v_salary || ',' || v_temp);             
END;

--
DECLARE
v_salary employees.salary%TYPE;
v_employee_id employees.employee_id%TYPE;
v_temp VARCHAR2(50);
BEGIN
  v_employee_id := 150; --给v_employee_id变量赋值
  SELECT salary INTO v_salary FROM employees WHERE employee_id = v_employee_id; --使用v_employee_id变量
  dbms_output.put_line(v_employee_id || '号员工的薪水=' || v_salary);
  v_temp :=
  CASE WHEN v_salary >= 10000 THEN 'salary >= 10000'
                WHEN v_salary >= 5000 THEN '5000<= salary < 10000'
  ELSE 'salary < 5000'
  END;
  dbms_output.put_line('薪水=' || v_salary || ',' || v_temp);             
END;

--
DECLARE
v_salary employees.salary%TYPE;
v_employee_id employees.employee_id%TYPE;
v_temp VARCHAR2(50);
BEGIN
  v_employee_id := 150; --给v_employee_id变量赋值
  SELECT salary INTO v_salary FROM employees WHERE employee_id = v_employee_id; --使用v_employee_id变量
  dbms_output.put_line(v_employee_id || '号员工的薪水=' || v_salary);
  v_temp :=
  (CASE WHEN v_salary >= 10000 THEN 'salary >= 10000'
                WHEN v_salary >= 5000 THEN '5000<= salary < 10000'
  ELSE 'salary < 5000'
  END);
  dbms_output.put_line('薪水=' || v_salary || ',' || v_temp);             
END;




/*

以前的知识回顾

*/
--以下这2种写法都可以
--
SELECT (CASE department_id WHEN 80 THEN '80号部门' WHEN 90 THEN '90号部门' END) FROM employees
--
SELECT (CASE WHEN department_id = 80 THEN '80号部门' WHEN department_id = 90 THEN '90号部门' END) FROM employees

--
SELECT job_id FROM employees

/*

回顾知识点 CASE... WHEN... THEN... WHEN... THEN... ELSE... END

*/

--以下写法正确
SELECT (CASE job_id WHEN 'IT_PROG' THEN 'IT岗位' WHEN 'SA_REP' THEN '销售岗位' END) FROM employees

--以下写法错误，会报错
SELECT (CASE salary WHEN salary >= 5000  THEN '薪水大于5000' WHEN salary < 5000 THEN '薪水小于5000' END) FROM employees

--以下写法正确
SELECT (CASE WHEN salary >= 5000  THEN '薪水大于5000' WHEN salary < 5000 THEN '薪水小于5000' END) FROM employees

--以下写法错误
SELECT (CASE WHEN salary >= 5000  THEN salary = 10 WHEN salary < 5000 THEN salary = 20 END) FROM employees

--以下写法正确
SELECT (CASE salary WHEN 24000  THEN '薪水等于24000' WHEN 8000 THEN '薪水等于8000' END) FROM employees


--以下写法正确
SELECT (CASE salary WHEN 24000  THEN '薪水等于24000' WHEN 8000 THEN '薪水等于8000' ELSE 'hello world' END) FROM employees

--以下写法正确
SELECT salary, (CASE salary WHEN 24000  THEN salary + 10 WHEN 8000 THEN salary + 20 ELSE salary + 30 END) FROM employees

--以下写法错误，会报错
SELECT salary, (CASE salary WHEN 24000  THEN salary = salary + 10 WHEN 8000 THEN salary = salary + 20 ELSE salary = salary + 30 END) FROM employees

--以下写法错误，会报错
SELECT salary, (CASE salary WHEN 24000  THEN salary = 10 WHEN 8000 THEN salary = 20 ELSE salary = 30 END) FROM employees

SELECT * FROM employees


-----------------------------------------
/*
查询出 122 号员工的 JOB_ID, 若其值为 'IT_PROG', 则打印 'GRADE: A'; 
'AC_MGT', 打印 'GRADE B', 
'AC_ACCOUNT', 打印 'GRADE C'; 
否则打印 'GRADE D'
*/
--使用if ELSIF 
DECLARE 
v_job_id employees.job_id%TYPE;
v_grade VARCHAR2(50);
BEGIN
  SELECT job_id INTO v_job_id FROM employees WHERE employee_id = 122;
  IF v_job_id = 'IT_PROG' THEN v_grade := 'GRADE: A';
  ELSIF v_job_id = 'AC_MGT' THEN v_grade := 'GRADE: B';
  ELSIF v_job_id = 'AC_ACCOUNT' THEN v_grade := 'GRADE: C';
  ELSE v_grade := 'GRADE: D';
  END IF;
  dbms_output.put_line(v_job_id || ' ,grade = ' || v_grade);
END;

--使用CASE WHEN THEN
DECLARE 
v_job_id employees.job_id%TYPE;
v_grade VARCHAR2(50);
BEGIN
 SELECT job_id INTO v_job_id FROM employees WHERE employee_id = 122;
 v_grade := 
 CASE v_job_id 
   WHEN 'IT_PROG' THEN 'A'
   WHEN 'AC_MGT' THEN 'B'
   WHEN 'AC_ACCOUNT' THEN 'C'
   ELSE 'D'
 END;
 dbms_output.put_line(v_job_id || ' , ' || v_grade);
END;
 
 
/*

循环知识点

*/  

--使用循环打印数字1-100
-- 1初始化条件  2循环体  3循环条件  4迭代条件 
DECLARE
--1
v_number NUMBER(6) := 1;--定义一个变量并赋值 (:=是赋值符号)
BEGIN
  LOOP 
    --2
    dbms_output.put_line('数字' || v_number);
    --3
  EXIT WHEN v_number > 100;
  --4
  v_number := v_number + 1;
  END LOOP;
END;

--
DECLARE
v_number NUMBER(6) := 1;
BEGIN
  LOOP 
    dbms_output.put_line('数字' || v_number);
  EXIT WHEN v_number >= 100;
  v_number := v_number + 1; -- 可以放在这里
  END LOOP;
END;

--以下这样写也可以
DECLARE
v_number NUMBER(6) := 1;
BEGIN
  LOOP 
    dbms_output.put_line('数字' || v_number);
    v_number := v_number + 1; --也可以放在这里
  EXIT WHEN v_number >= 100;
  END LOOP;
  
END;

--
DECLARE
v_number NUMBER(6) := 1;
BEGIN
  LOOP 
    dbms_output.put_line('数字' || v_number);
    v_number := v_number + 1; --
  EXIT WHEN v_number > 100;
  END LOOP;
  
END;

--
DECLARE
v_number NUMBER(6) := 1;
BEGIN
  LOOP 
    dbms_output.put_line('数字' || v_number);
    v_number := v_number + 1; --
  EXIT WHEN v_number > 100;
  END LOOP;
  
END;


--第二种循环方式 while
DECLARE
v_number NUMBER(6) := 1;
BEGIN
 WHILE v_number <= 100 LOOP
   dbms_output.put_line('数字' || v_number);
   v_number := v_number + 1;
   END LOOP;
END;

/*
每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1。
跟在IN REVERSE 后面的数字必须是从小到大的顺序，而且必须是整数，
不能是变量或表达式。可以使用EXIT 退出循环
*/

--第三种循环方式 for循环
BEGIN
  FOR mynumber IN 1..100 LOOP
   dbms_output.put_line('数字' || mynumber); 
   END LOOP;
END;

--使用REVERSE(反转)关键字
BEGIN
  FOR mynumber IN REVERSE 1..100 LOOP
   dbms_output.put_line('数字' || mynumber);
   END LOOP;
END;

--null语句
/*
NULL：在PL/SQL 程序中，可以用 null 语句来说明“不用做任何事情”的意思，相当于一个占位符，可以使某些语句变得有意义，提高程序的可读性
*/
--从employees表中查数据，如果薪水大于20000，则打印'土豪，我们做个朋友吧'，如果薪水小于20000，不做任何处理
DECLARE v_salary employees.salary%Type ; 
BEGIN 
SELECT salary INTO v_salary FROM employees WHERE employee_id = 100; 
IF v_salary > 20000 THEN dbms_output.put_line('土豪，我们做个朋友吧');
ELSE NULL; --null语句
END IF; 
END;

--
SELECT SQRT(2), SQRT(3), SQRT(4), SQRT(16), SQRT(9), SQRT(36) FROM dual;
SELECT MOD(4, 2), MOD(36, 4), MOD(25, 6) FROM dual;
--

/*

输出2-100之间的质数 (java的面试中很多时候都会考这道题目，不光可以考察多层嵌套循环，还可以考察break、sqrt()函数、
小算法、条件判断结构、效率性能等等知识点,而且题目还不算太大太难，所以大家最好要会写)

*/
--使用while写法
DECLARE
  V_I    NUMBER(5) := 2;
  V_J    NUMBER(5) := 2;
  V_FLAG NUMBER(1) := 1;
BEGIN
  WHILE V_I <= 100 LOOP
    WHILE V_J <= SQRT(V_I) LOOP
      IF MOD(V_I, V_J) = 0 THEN
        V_FLAG := 0;
      END IF;
      V_J := V_J + 1;
    END LOOP;
    IF V_FLAG = 1 THEN
      DBMS_OUTPUT.PUT_LINE('质数' || V_I);
    END IF;
    V_I    := V_I + 1;
    V_FLAG := 1;
   -- V_J    := 2; --这行代码注释掉就无法求出正确的质数
   V_J    := 2;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('内层循环的V_J=' || V_J);
END;

--使用for循环的写法，如下
DECLARE
  V_FLAG NUMBER(1) := 1;
  V_COUNT NUMBER(10) := 0;
BEGIN
  FOR i IN 2..100 LOOP
    
   FOR j IN 2..sqrt(i) LOOP 
     IF(MOD(i, j) = 0) THEN V_FLAG := 0;
     END IF;
     END LOOP;
     IF(V_FLAG = 1) THEN dbms_output.put_line('质数' || i);
     V_COUNT := V_COUNT + 1; --统计有几个质数
     END IF;
     V_FLAG := 1;
   END LOOP;
   dbms_output.put_line('共有' || V_COUNT || '个质数');
END;

--改进上面的代码，使效率更高
DECLARE
  V_FLAG NUMBER(1) := 1;
  V_COUNT NUMBER(10) := 0;
BEGIN
  FOR i IN 2..100 LOOP
    
   FOR j IN 2..sqrt(i) LOOP 
     IF(MOD(i, j) = 0) THEN V_FLAG := 0;
     --PL/SQL中GOTO语句是无条件跳转到指定的标号去的意思
     GOTO LABEL; --(这里使用GOTO提高了效率性能)
     END IF;
     END LOOP;
     <<LABEL>>
     IF(V_FLAG = 1) THEN dbms_output.put_line('质数' || i);
     V_COUNT := V_COUNT + 1; --统计有几个质数
     END IF;
     V_FLAG := 1;
   END LOOP;
   dbms_output.put_line('共有' || V_COUNT || '个质数');
END;

--使用 goto
--打印1——100的自然数，当打印到50时，跳出循环，输出“打印结束”
--方式1
DECLARE
v_number NUMBER(5) := 100; --赋值
BEGIN
  FOR mynumber IN 1.. v_number LOOP
    IF mynumber = 50 THEN GOTO LABEL;
    END IF;
    dbms_output.put_line('自然数' || mynumber);
  END LOOP;
  <<LABEL>>
  dbms_output.put_line('打印结束');
END;

--
BEGIN
  FOR I IN 1 .. 100 LOOP
    DBMS_OUTPUT.PUT_LINE(I);
    IF (I = 50) THEN
      GOTO LABEL;
    END IF;
  END LOOP;

  <<LABEL>>
  DBMS_OUTPUT.PUT_LINE('打印结束');

END;

--方式2(使用exit关键字)
DECLARE
v_number NUMBER(5) := 100; --赋值
BEGIN
  FOR mynumber IN 1.. v_number LOOP
    IF mynumber = 50 THEN dbms_output.put_line('打印结束'); 
      EXIT; --使用exit关键字
    END IF;
    dbms_output.put_line('自然数' || mynumber);
  END LOOP;
END;

--(使用exit关键字)
DECLARE
v_number NUMBER(5) := 100; --赋值
BEGIN
  FOR mynumber IN 1.. v_number LOOP
    IF mynumber = 50 THEN EXIT; --使用exit关键字 
    END IF;
    dbms_output.put_line('自然数' || mynumber);
  END LOOP;
  dbms_output.put_line('打印结束');
END;