控制语句和函数
选择结构
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;
Comments NOTHING