Oracle12C--程序结构(二十八)

知识点的梳理：

• PL/SQL 的分支结构分为：IF 语句与CASE 语句

    

• 三种程序结构

顺序结构：

分支结构：

循环结构：

• 共同点：
  • 它们都只有一个入口，也只有一个出口；
  • 这些单一入口与出口，让程序易读，好维护，减少调试时间；

• 分支结构
  • IF语句

    

    • 示例1：IF语句

DECLARE v_countResult NUMBER ; BEGIN SELECT COUNT(empno) INTO v_countResult FROM emp ; IF v_countResult > 10 THEN DBMS_OUTPUT.put_line('EMP表的记录大于10条。') ; END IF ; END ; /

运行结果： EMP表的记录大于10条。

• 示例2：IF…ELSE语句

DECLARE v_countResult NUMBER ; BEGIN SELECT COUNT(deptno) INTO v_countResult FROM dept ; IF v_countResult > 10 THEN DBMS_OUTPUT.put_line('DEPT表的记录大于10条。') ; ELSE DBMS_OUTPUT.put_line('DEPT表的记录小于10条。') ; END IF ; END ; /

运行结果： DEPT表的记录小于10条。

• 示例3：IF…ELSIF…ELSE语句

DECLARE v_countResult NUMBER ; BEGIN SELECT COUNT(empno) INTO v_countResult FROM emp ; IF v_countResult > 10 THEN DBMS_OUTPUT.put_line('EMP表的记录大于10条。') ; ELSIF v_countResult < 10 THEN DBMS_OUTPUT.put_line('EMP表的记录小于10条。') ; ELSE DBMS_OUTPUT.put_line('EMP表的记录等于10条。') ; END IF ; END ; /

运行结果： EMP表的记录大于10条。

• 示例4：查询emp表的工资，输入员工编号，根据编号查询工资，如果工资高于3000元，则显示高工资，如果工资大于2000元，则显示中等工资，如果工资小于2000元，则显示低工资。

DECLARE v_empSal emp.sal%TYPE ; -- 定义变量与emp.sal字段类型相同 v_empName emp.ename%TYPE ; -- 定义变量与emp.ename字段类型相同 v_eno emp.empno%TYPE ; -- 定义变量与emp.empno字段类型相同 BEGIN v_eno := &inputEmpno; -- 用户输入要查找的雇员编号 -- 根据输入的雇员编号查找雇员姓名及工资 SELECT ename,sal INTO v_empName,v_empSal FROM emp WHERE empno=v_eno; IF v_empSal > 3000 THEN -- 判断 DBMS_OUTPUT.put_line(v_empName || '的工资属于高工资！') ; ELSIF v_empSal > 2000 THEN -- 判断 DBMS_OUTPUT.put_line(v_empName || '的工资属于中等工资！') ; ELSE DBMS_OUTPUT.put_line(v_empName || '的工资属于低工资！') ; END IF; END; /

运行结果： SMITH的工资属于低工资！

• 示例5：用户输入一个雇员编号，根据它所在的部门给上涨工资，规则：

  • 10部门上涨10%，20上涨20%，30上涨30%；

  • 但是要求最高不能超过5000，超过5000就停留在5000。

DECLARE v_empSal emp.sal%TYPE ; -- 定义变量与emp.sal字段类型相同 v_dno emp.deptno%TYPE ; -- 定义变量与emp.deptno字段类型相同 v_eno emp.empno%TYPE ; -- 定义变量与emp.empno字段类型相同 BEGIN v_eno := &inputEmpno; -- 用户输入要查找的雇员编号 SELECT deptno,sal INTO v_dno, v_empSal FROM emp WHERE empno = v_eno ; IF v_dno = 10 THEN IF v_empSal * 1.1 > 5000 THEN UPDATE emp SET sal=5000 WHERE empno=v_eno; ELSE UPDATE emp SET sal=sal*1.1 WHERE empno=v_eno; END IF; ELSIF v_dno = 20 THEN IF v_empSal * 1.2 > 5000 THEN UPDATE emp SET sal =5000 WHERE empno=v_eno; ELSE UPDATE emp SET sal=sal*1.2 WHERE empno=v_eno; END IF; ELSIF v_dno = 30 THEN IF v_empSal * 1.3 > 5000 THEN UPDATE emp SET sal=5000 WHERE empno=v_eno; ELSE UPDATE emp SET sal=sal*1.3 WHERE empno=v_eno; END IF; ELSE null; END IF; END; /

分析： 此程序将根据用户输入的雇员编号查找出其对应的雇员工资和部门编号，然后再根据题目的要求，按照不同的部门进行工资的增长。在进行工资增长前会判断其增长后是否超过5000元。如果不超过，则按照一定的增长比率增长，如果超过了5000元。则将工资设置为5000元。

• CASE语句
  • CASE是多条件的判断语句，功能与IF...ELSEIF....ELSE类似；
  • 语法：

CASE [变量] WHEN [值| 表达式] THEN 执行语句块； WHEN [值| 表达式] THEN 执行语句块； ELSE 条件都不满足时执行语句块； END CASE;

执行流程图：

• 示例1：使用CASE语句判断数值

DECLARE v_choose NUMBER := 1 ; BEGIN CASE v_choose WHEN 0 THEN DBMS_OUTPUT.put_line('您选择的是第0项。') ; WHEN 1 THEN DBMS_OUTPUT.put_line('您选择的是第1项。') ; ELSE DBMS_OUTPUT.put_line('没有选项满足。') ; END CASE ; END ; /

运行结果： 您选择的是第1项。

• 示例2：使用CASE进行多条件判断

DECLARE v_salary emp.sal%TYPE ; v_eno emp.empno%TYPE ; BEGIN v_eno := &inputEmpno ; SELECT sal INTO v_salary FROM emp WHERE empno=v_eno ; CASE WHEN v_salary >= 3000 THEN DBMS_OUTPUT.put_line('雇员：' || v_eno || '的收入为高工资。') ; WHEN v_salary >= 2000 AND v_salary <3000 THEN DBMS_OUTPUT.put_line('雇员：' || v_eno || '的收入为中等工资。') ; ELSE DBMS_OUTPUT.put_line('雇员：' || v_eno || '的收入为低工资。') ; END CASE ; END ; /

运行结果：提示输入的参数为：7369 雇员：7369的收入为低工资。

• 示例3：输入雇员编号，根据雇员的职位进行工资提升，提升要求如下

  • 如果职位是办事员（CLERK），工资增长5%；

  • 如果职位是销售人员（SALESMAN），工资增长8%；

  • 如果职位为经理（MANAGER），工资增长10%；

  • 如果职位为分析员（ANALYST），工资增长20%；

  • 如果职位为总裁（PRESIDENT），工资不增长。

DECLARE v_job                        emp.job%TYPE ; v_eno                emp.empno%TYPE ; BEGIN v_eno := &inputEmpno ; SELECT job INTO v_job FROM emp WHERE empno=v_eno ; CASE v_job WHEN 'CLERK' THEN UPDATE emp SET sal=sal*1.05 WHERE empno=v_eno ; WHEN 'SALESMAN' THEN UPDATE emp SET sal=sal*1.08 WHERE empno=v_eno ; WHEN 'MANAGER' THEN UPDATE emp SET sal=sal*1.10 WHERE empno=v_eno ; WHEN 'ANALYST' THEN UPDATE emp SET sal=sal*1.20 WHERE empno=v_eno ; ELSE DBMS_OUTPUT.put_line('雇员：' || v_eno || '工资不具备上涨资格！') ; END CASE ; END ; /

运行结果：提示输入的参数为：7839 雇员：7839工资不具备上涨资格！

• 循环结构
  • 循环结构由3个重要的组成部分：
    • 循环的初始条件；
    • 每次循环的判断条件；
    • 循环条件的修改；
  • 循环结构定义了两种，LOOP循环和FOR循环
    • LOOP使用在不确定循环次数的操作中；
      • LOOP循环语法：

LOOP循环： WHILE...LOOP循环： LOOP 循环执行的语句块； EXIT WHEN 循环结束条件； 循环结束条件修改； END LOOP; WHILE(循环结束条件) LOOP 循环执行的语句块； 循环结束条件修改； END LOOP; EXIT可以退出包含它的最内层循环体。 加上此关键字，可以保证循环在某种条件下退出。 一般LOOP语句与EXIT语句联用。 而WHILE...LOOP语句是在循环之前先进行判断，满足条件之后再执行循环体；

两种语法的区别： LOOP属于先执行后判断，不管条件是否满足，一定会执行一次； WHILE...LOOP是先判断，在执行；

• FOR使用在明确知道循环次数的操作中；
  • FOR循环语法

FOR 循环索引 IN [REVERSE] 循环区域下限...循环区域上限 LOOP 循环执行的语句块； END LOOP;

• 示例1：使用LOOP循环

DECLARE v_i NUMBER := 1 ; -- 定义一个变量，用于循环 BEGIN LOOP DBMS_OUTPUT.put_line('v_i = ' || v_i) ; EXIT WHEN v_i >= 3 ; v_i := v_i + 1 ; END LOOP ; END ; /

运行结果： v_i = 1 v_i = 2 v_i = 3

• 示例2：使用WHILE…LOOP循环

DECLARE v_i NUMBER := 1 ; -- 定义一个变量，用于循环 BEGIN WHILE (v_i <= 3) LOOP DBMS_OUTPUT.put_line('v_i = ' || v_i) ; v_i := v_i + 1 ; END LOOP ; END ; /

运行结果： v_i = 1 v_i = 2 v_i = 3

• 示例3：使用FOR循环

DECLARE v_i NUMBER := 1 ; -- 定义一个变量，用于循环 BEGIN FOR v_i IN 1 .. 3 LOOP DBMS_OUTPUT.put_line('v_i = ' || v_i) ; END LOOP ; END ; /

运行结果： v_i = 1 v_i = 2 v_i = 3

• 示例4：使用REVERSE操作

DECLARE v_i NUMBER := 1 ; -- 定义一个变量，用于循环 BEGIN FOR v_i IN 1 .. 10 LOOP IF v_i = 3 THEN -- 当v_i变量增长到3时结束循环 EXIT ; END IF ; DBMS_OUTPUT.put_line('v_i = ' || v_i) ; END LOOP ; END ; /

运行结果： v_i = 3 v_i = 2 v_i = 1

• 循环控制
  • 在循环中，可以使用exit来结束循环，使用continue退出当前循环；
  • 示例1：使用EXIT结束循环操作

DECLARE v_i NUMBER := 1 ; -- 定义一个变量，用于循环 BEGIN FOR v_i IN 1 .. 10 LOOP IF v_i = 3 THEN -- 当v_i变量增长到3时结束循环 EXIT ; END IF ; DBMS_OUTPUT.put_line('v_i = ' || v_i) ; END LOOP ; END ; /

运行结果： v_i = 1 v_i = 2

• 示例2：使用CONTINUE控制循环操作

DECLARE v_i NUMBER := 1 ; -- 定义一个变量，用于循环 BEGIN FOR v_i IN 1 .. 10 LOOP IF MOD(v_i,2) = 0 THEN -- 为偶数的时候不执行后续方法体 CONTINUE ; END IF ; DBMS_OUTPUT.put_line('v_i = ' || v_i) ; END LOOP ; END ; /

运行结果： v_i = 1 v_i = 3 v_i = 5 v_i = 7 v_i = 9

• GOTO语句
  • 该语句为无条件转移语句，直接转移到指定标号处；
    • GOTO语句不能转入IF语句，循环体和子块，但可以从IF语句，循环体和子块中转出；
  • 示例1：使用GOTO进行跳转

DECLARE v_result NUMBER := 1; BEGIN FOR v_result IN 1 .. 10 LOOP IF v_result = 2 THEN GOTO endPoint ; END IF ; DBMS_OUTPUT.put_line('v_result = ' || v_result) ; END LOOP ; <<endPoint>> DBMS_OUTPUT.put_line('FOR循环提前结束。') ; END ; /

运行结果： v_result = 1 FOR循环提前结束。

• 内部程序块
  • 对于每一个PL/SQL程序块，其基本的组成部分就是DECLARE,BEING,END，可以在一个程序块中定义多个子程序块；
    • 语法：内部程序块

DECLARE --声明部分，例如定义变量，常量，游标 BEGIN --程序编写，SQL语句 DECLARE --子程序声明部分，例如定义变量，常量，游标 BEGIN -- 子程序编写，SQL语句 EXECTPION --子程序处理异常 END; EXECTPION --处理异常 END; /

• 示例1：定义内部程序块

DECLARE v_x NUMBER := 30 ; -- 此为全局变量 BEGIN DECLARE v_x VARCHAR2(40) := 'MLDNJAVA' ; -- 此为局部变量，只能在内部程序块中使用 v_y NUMBER := 20 ; BEGIN DBMS_OUTPUT.put_line('内部程序块输出：v_x = ' || v_x) ; DBMS_OUTPUT.put_line('内部程序块输出：v_y = ' || v_y) ; END ; DBMS_OUTPUT.put_line('外部程序块输出：v_x = ' || v_x) ; END ; /

运行结果： 内部程序块输出：v_x = MLDNJAVA 内部程序块输出：v_y = 20 外部程序块输出：v_x = 30 分析： 本程序在外部程序块和内部程序块中都定义了相同的V_X变量名称，由于第二个V_X变量定义在了内部程序块，所以两个变量不会互相影响，但是在内部程序块中定义的变量只能在内部程序主体部分使用。