Oracle IF语句的使用

来源：互联网发布：淘宝发饰店推荐知乎编辑：程序博客网时间：2024/04/30 15:36

IF语句的使用
A.基本的IF条件语句：
基本语法:

程序代码

IF THEN


END IF;

Example:

程序代码

SQL> set serveroutput on;
SQL> declare
   x number(3):=9;
    begin
    if x<10 then
    dbms_output.put_line('x is less than10');
    end if;
   end;
   /

结果：
x is less than10

PL/SQL procedure successfully completed

B.IF - ELSE 语句

基本语法：

程序代码

IF THEN


ELSE
  
END IF;

Example:

程序代码

DECLARE
x NUMBER(3) := 10;
BEGIN
  IF x < 10 THEN
    dbms_output.put_line('X is less than 10');
  ELSE
    dbms_output.put_line('X is not less than 10');
  END IF;
END;
/

结果：
X is not less than 10

PL/SQL procedure successfully completed

C:IF - ELSIF - ELSE 语句

基本语法:

程序代码

IF THEN


ELSIF THEN
  
ELSIF THEN
  
ELSE
  
END IF;

Example:

程序代码

set serveroutput on

DECLARE
x NUMBER(3) := 47;
BEGIN
  IF x < 10 THEN
    dbms_output.put_line('X is less than 10');
  ELSIF x = 10 THEN
    dbms_output.put_line('X is equal to 10');
  ELSIF x < 100 THEN
    dbms_output.put_line('X is between 11 and 99');
  ELSE
    dbms_output.put_line('X is greater than 99');
  END IF;
END;
/

结果：
X is between 11 and 99

PL/SQL procedure successfully completed

D:与NULL值比较处理

Example:

程序代码

declare
       v NUMBER;
   begin
       if v = 1 then
           DBMS_OUTPUT.put_line('Equal to 1');
       elsif v!= 1 then
           DBMS_OUTPUT.put_line('Not equal to 1');
       elsif v = v then
           DBMS_OUTPUT.put_line('Equal to itself');
       else
           DBMS_OUTPUT.put_line('Undefined result');
       end if;
       v:=v+1;
       DBMS_OUTPUT.put_line('New value: <'||v||'>');
   end;
/