Oracle 笔记05

控制语句和函数

选择结构

IF 和 CASE 语句

IF语句

-- IF结构
IF condition1 THEN statements1;
[ELSIF condition2 THEN statements2;]
......
[ELSE else_statements;]
END IF;
-- 条件condition 的值只能是布尔值
-- 分支情况为 ELSIF 而非 ELSEIF

示例：输入一个员工号，修改该员工的工资

DECLARE
    v_deptno emp.deptno%TYPE;
    v_increment NUMBER(4);
    v_empno emp.empno%TYPE;
BEGIN
    v_empno := &x;
    SELECT deptno INTO v_deptno FROM emp
    WHERE empno = v_empno;
    IF v_deptno=10 THEN v_increment:=100;
    ELSIF v_deptno=20 THEN v_increment:=160;
    ELSIF v_deptno=30 THEN v_increment:=200;
    ELSE v_increment:=300;
    END IF;
    UPDATE emp SET sal=sal+v_increment
    WHERE empno=v_empno;
END;

PS：为了避免条件为NULL时出现歧义，在条件判断时应该进行NULL值检查

IF number1 IS NULL OR number2 IS NULL THEN
   result = 'UNKNOW';
ELSIF number1<number2 THEN
   result = 'YES';

CASE语句

-- CASE结构
CASE [expression]
    WHEN condition1 THEN statements1;
    WHEN condition2 THEN statements2;
    ......
    WHEN conditionN THEN statementsN;
    [ELSE   else_statements;]
END CASE;

当匹配到WHEN条件为真时，执行其操作，操作完后结束CASE语句块

循环结构

简单循环、WHILE循环、FOR循环

简单循环

--结构
LOOP
    sequence_of_statement;
    EXIT[WHEN condition]; --循环体必须包含结束情况
END LOOP;
--示例
DECLARE
    v_counter BINARY_INTEGER :=1;
BEGIN
    LOOP
        INSERT INTO my_test_table
        VALUES (v_counter,'Loop Index');
        v_counter := v_counter+1;
        EXIT WHEN v_counter>10;
    END LOOP ;
END;

WHILE循环

--结构
WHILE condition LOOP
    sequence_of_statement;
END LOOP;
--示例
BEGIN
    WHILE v_counter <= 10 LOOP
        INSERT INTO my_test_table VALUES(v_counter,'Loop Index');
        v_counter := v_counter+1;
        END LOOP;
END;

FOR循环

-- 结构
FOR loop_counter IN [REVERSE] low_bound..high_bound
LOOP
    sequence_of_statement;
END LOOP;
--示例
FOR v_counter IN 1..10
LOOP
    INSERT INTO my_test_table VALUES(v_counter,'Loop Index');
END LOOP;

跳转结构

--结构
<<标号>>
...
GOTO 标号;
--示例
--省略声明 v_counter 部分
BEGIN
    <<LABEL>>
    INSERT INTO my_test_table VALUES (v_counter,'Loop Index');
    v_counter := v_counter +1;
    IF v_counter <= 10 THEN
        GOTO LABEL;
    END IF;
END;

块内跳转：内层块可以跳到外层块，外层块不能跳到内层块
IF语句不能跳入
不能从循环体外跳入循坏体内
不能从子程序外跳到子程序中
尽量不要用跳转

函数

--结构
CREATE [OR REPLACE] FUNCTION func_name
(
    parameter1_name [mode] datatype [DEFAULT|:=value]
    [,parameter1_name [mode] datatype [DEFAULT|:=value],...]
)
RETURN return_datatype
AS|IS
    --声明标量区域
BEGIN
    --执行语句区域
EXCEPTION
    --异常处理区域
END [func_name];

示例：创建一个以部门号为参数，返回该部门最高工资的函数

CREATE OR REPLACE FUNCTION return_maxSal
(p_deptno emp.deptno%TYPE)
RETURN emp.sal%TYPE           --必须指明函数返回类型
AS
    v_maxSal emp.sal%TYPE;
BEGIN
    SELECT max(sal) INTO v_maxSal FROM emp
        WHERE deptno=p_deptno;
    RETURN v_maxsal;          --至少有一个RETURN语句来指明函数返回值
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('The deptno is invalid!');
END return_maxSal;
--调用上述函数
SELECT return_maxSal(20) FROM dual;

函数可以在SQL语句中被调用，也可以在PL/SQL中被调用

--函数的修改
CREATE OR REPLACE FUNCTION func_name;
--函数重编译
ALTER FUNCTION func_name COMPILE;
--删除函数
DROP FUNCTION func_name;

查看函数的源代码

--查询数据字典USER_SOURCE
SELECT TEXT FROM USER_SOURCE
WHERE TYPE='FUNCTION'
AND NAME='RETURN_MAXSAL'  --函数名必须大写
ORDER BY LINE;