PL / SQL 中的条件语句

2024 年 8 月 29 日 | 阅读 12 分钟

在许多情况下，我们都需要做出选择，例如“是”或“否”。这些类型的问题也被称为决策语句。在这里，用户被迫选择一个答案。在这种情况下，我们根据我们所处的状态（条件）来选择答案。同样，每个程序都有类似的情况，比如如果这个条件为真，我们就选择“是”语句，如果条件为假，我们就选择“否”语句，在编写代码时也是如此。

每种编程语言都有一些条件语句。这些条件语句也被称为决策语句。PL / SQL（过程语言 / 结构化查询语言）中有一些条件语句或决策语句。

条件语句只能在 BEGIN 状态下使用。

条件语句的类型

if condition then statement
if condition then else statement
嵌套 if condition then statements
if condition then elseif condition then else statements

1.) if Condition then Statement

这是第一个重要的 PL / SQL 条件语句之一。在此语句中，只有当条件为真时，才会显示输出。在任何其他情况下，都不会有输出。

语法

IF <condition> then
Statement 1 
Statement 2
.
.
.
.
Statement N
END IF;

示例查询

SQL> DECLARE
N1 INTEGER;
BEGIN
N1:=10;
IF N1 > 10 THEN
dbms_output.put_line('The Value is' || N1);
END IF;
END;
/

输出

PL/SQL procedure successfully completed.

SQL> DECLARE
N1 INTEGER;
BEGIN
N1:=10;
IF N1 >= 10 THEN
dbms_output.put_line('The Value is ' || N1);
END IF;
END;
/

输出

The Value is 10

PL/SQL procedure successfully completed.

SQL> DECLARE
N1 INTEGER;
BEGIN
N1:=&N1;
IF N1 > 10 THEN
dbms_output.put_line('The Value is' || N1);
END IF;
END;
/

输出

Enter value for n1: 5
old   4: N1:=&N1;
new   4: N1:=5;

PL/SQL procedure successfully completed.

SQL> DECLARE
N1 INTEGER;
BEGIN
N1:=&N1;
IF N1 > 10 THEN
dbms_output.put_line('The Value is ' || N1);
END IF;
END;
/

输出

Enter value for n1: 15
old   4: N1:=&N1;
new   4: N1:=15;
The Value is 15

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    BEGIN
    N1:=&N1;
    IF N1 >= 10 THEN
    dbms_output.put_line('The Value is ' || N1);
    END IF;
    END;
    /

输出

Enter value for n1: 9
old   4:     N1:=&N1;
new   4:     N1:=9;

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    BEGIN
    N1:=&N1;
    IF N1 >= 10 THEN
    dbms_output.put_line('The Value is ' || N1);
    END IF;
    END;
    /

输出

Enter value for n1: 10
old   4:     N1:=&N1;
new   4:     N1:=10;
The Value is 10

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    BEGIN
    N1:=&N1;
    IF N1 >= 10 THEN
    dbms_output.put_line('The Value is ' || N1);
    END IF;
    END;
    /

输出

Enter value for n1: 23
old   4:     N1:=&N1;
new   4:     N1:=23;
The Value is 23

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=&N1;
    N2:=&N2;
    IF N1 >= N2 THEN
    dbms_output.put_line('The Value is ' || N1*N2);
    END IF;
 11
 12
    END;
    /

输出

Enter value for n1: 5
old   6:     N1:=&N1;
new   6:     N1:=5;
Enter value for n2: 4
old   7:     N2:=&N2;
new   7:     N2:=4;
The Value is 20

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=&N1;
    N2:=&N2;
    IF N1 >= N2 THEN
    dbms_output.put_line('The Value is ' || N1*N2);
    END IF;
 11
 12
    END;
    /

输出

Enter value for n1: 9
old   6:     N1:=&N1;
new   6:     N1:=9;
Enter value for n2: 11
old   7:     N2:=&N2;
new   7:     N2:=11;

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=&N1;
    N2:=&N2;
    IF N1 >= N2 THEN
    dbms_output.put_line('The Value is ' || N1*N2);
    END IF;
 11
 12
    END;
    /

输出

Enter value for n1: 10
old   6:     N1:=&N1;
new   6:     N1:=10;
Enter value for n2: 10
old   7:     N2:=&N2;
new   7:     N2:=10;
The Value is 100

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=&N1;
    N2:=&N2;
    IF N1 >= N2 THEN
    dbms_output.put_line('The Value is ' || N1*N2);
    END IF;
 11
 12
    END;
    /

输出

Enter value for n1: 12
old   6:     N1:=&N1;
new   6:     N1:=12;
Enter value for n2: 2
old   7:     N2:=&N2;
new   7:     N2:=2;
The Value is 24

PL/SQL procedure successfully completed.

2.) if Condition then else Statement

这也是第一个重要的 PL / SQL 条件语句之一。在此语句中，只有当条件为真时，才会显示输出。在任何其他情况下，都有机会看到输出。如果 if 条件失败，则会执行 else 语句。

这意味着如果条件为真，则执行 If 语句。如果不是，则执行 else 语句。

这种条件语句需要用于解决一些现实生活中的问题，例如查找奇数或偶数、检查数字是否能被二、三等整除、查找最大值、查找最小值等，还有许多类似的程序。

语法

IF >CONDITION> THEN
Statement 1 
Statement 2
.
.
.
.
Statement N


ELSE 
Statement 1 
Statement 2
.
.
.
.
Statement N

END IF;

示例查询

SQL> DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=10;
    N2:=5;
    IF N1 > N2 THEN
    dbms_output.put_line('The Values of N1 is greater than N2');
    ELSE
    dbms_output.put_line('The Values of N2 is greater than N1');
 12
    END IF;
 14
 15
    END;
    /

输出

The Values of N1 is greater than N2

PL/SQL procedure successfully completed.

SQL> DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=10;
    N2:=5;
    IF N1 > N2 THEN
    dbms_output.put_line('The Values of N2 is smaller than N1');
    ELSE
    dbms_output.put_line('The Values of N1 is smaller than N2');
 12
    END IF;
 14
 15
    END;
    /

输出

The Values of N2 is smaller than N1
PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=&N1;
    N2:=&N2;
    IF N1 > N2 THEN
    dbms_output.put_line('The Values of N2 is smaller than N1');
    ELSE
    dbms_output.put_line('The Values of N1 is smaller than N2');
 12
    END IF;
 14
 15
    END;
    /

输出

Enter value for n1: 15
old   6:     N1:=&N1;
new   6:     N1:=15;
Enter value for n2: 16
old   7:     N2:=&N2;
new   7:     N2:=16;

输出

The Values of N1 is smaller than N2

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=&N1;
    N2:=&N2;
    IF N1 > N2 THEN
    dbms_output.put_line('The Values of N2 is smaller than N1');
    ELSE
    dbms_output.put_line('The Values of N1 is smaller than N2');
 12
    END IF;
 14
 15
    END;
    /

输出

Enter value for n1: 10
old   6:     N1:=&N1;
new   6:     N1:=10;
Enter value for n2: 6
old   7:     N2:=&N2;
new   7:     N2:=6;
The Values of N2 is smaller than N1

PL/SQL procedure successfully completed.

SQL>  DECLARE
  2      N1 INTEGER;
  3      N2 INTEGER;
  4
  5      BEGIN
  6      N1:=&N1;
  7      N2:=&N2;
  8      IF N1 > N2 THEN
  9      dbms_output.put_line('The Values of N1 is greater than N2');
 10      ELSE
 11      dbms_output.put_line('The Values of N2 is greater than N1');
 12
 13
 14      END IF;
 15
 16
 17      END;
 18      /

输出

Enter value for n1: 15
old   6:     N1:=&N1;
new   6:     N1:=15;
Enter value for n2: 26
old   7:     N2:=&N2;
new   7:     N2:=26;
The Values of N2 is greater than N1

PL/SQL procedure successfully completed.

SQL>  DECLARE
  2      N1 INTEGER;
  3      N2 INTEGER;
  4
  5      BEGIN
  6      N1:=&N1;
  7      N2:=&N2;
  8      IF N1 > N2 THEN
  9      dbms_output.put_line('The Values of N1 is greater than N2');
 10      ELSE
 11      dbms_output.put_line('The Values of N2 is greater than N1');
 12
 13
 14      END IF;
 15
 16
 17      END;
 18      /

输出

Enter value for n1: 12
old   6:     N1:=&N1;
new   6:     N1:=12;
Enter value for n2: 11
old   7:     N2:=&N2;
new   7:     N2:=11;
The Values of N1 is greater than N2
PL/SQL procedure successfully completed.

SQL> DECLARE
  2      N1 INTEGER;
  3
  4
  5      BEGIN
  6      N1:=&N1;
  7
  8      IF MOD(N1,2)=0 THEN
  9      dbms_output.put_line('EVEN NUMBER');
 10      ELSE
 11      dbms_output.put_line('ODD NUMBER');
 12
 13
 14      END IF;
 15
 16
 17      END;
 18      /

Enter value for n1: 5
old   6:     N1:=&N1;
new   6:     N1:=5;
ODD NUMBER

PL/SQL procedure successfully completed.

SQL> DECLARE
  2      N1 INTEGER;
  3
  4
  5      BEGIN
  6      N1:=&N1;
  7
  8      IF MOD(N1,2)=0 THEN
  9      dbms_output.put_line('EVEN NUMBER');
 10      ELSE
 11      dbms_output.put_line('ODD NUMBER');
 12
 13
 14      END IF;
 15
 16
 17      END;
 18      /

Enter value for n1: 6
old   6:     N1:=&N1;
new   6:     N1:=6;
EVEN NUMBER

PL/SQL procedure successfully completed.

3.) if condition elsif condition else statements

这也是第一个重要的 PL / SQL 条件语句之一。在此语句中，只有当条件为真时，才会显示输出。在任何其他情况下，都有机会看到输出。如果 if 条件失败，则会执行带有条件的 else if 语句。即使 else if 条件为假，也会执行 else 语句。

语法

IF <CONDITION> THEN
Statement 1 
Statement 2
.
.
.
.
Statement N
ELSEIF <CONDITION> THEN
Statement 1 
Statement 2
.
.
.
.
Statement N

ELSE
Statement 1 
Statement 2
.
.
.
.
Statement N

END IF;

示例查询

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2 num1 INTEGER:= 7;
  3 num2 INTEGER:= 8;
  4
  5 BEGIN
  6
  7 IF num1 > num2 THEN
  8 dbms_output.put_line('NUM1 SMALLER THAN NUM2');
  9
 10 ELSIF num1 = num2 THEN
 11 dbms_output.put_line('EQUAL');
 12
 13 ELSE
 14 dbms_output.put_line('NUM2 SMALLER THAN NUM1');
 15 END IF;
 16
 17 END;
 18 /

输出

NUM2 SMALLER THAN NUM1

PL/SQL procedure successfully completed.

SQL> DECLARE
  2 num1 INTEGER:= 7;
  3 num2 INTEGER:= 7;
  4
  5 BEGIN
  6
  7 IF num1 > num2 THEN
  8 dbms_output.put_line('NUM1 SMALLER THAN NUM2');
  9
 10 ELSIF num1 = num2 THEN
 11 dbms_output.put_line('EQUAL');
 12
 13 ELSE
 14 dbms_output.put_line('NUM2 SMALLER THAN NUM1');
 15 end if;
 16
 17 END;
 18 /

输出

EQUAL

PL/SQL procedure successfully completed.

SQL> DECLARE
  2  num1 INTEGER:= 16;
  3  num2 INTEGER:= 7;
  4
  5  BEGIN
  6
  7  IF num1 > num2 THEN
  8  dbms_output.put_line('NUM1 SMALLER THAN NUM2');
  9
 10  ELSIF num1 = num2 THEN
 11  dbms_output.put_line('EQUAL');
 12
 13  ELSE
 14  dbms_output.put_line('NUM2 SMALLER THAN NUM1');
 15  end if;
 16
 17  END;
 18  /

输出

NUM1 SMALLER THAN NUM2

PL/SQL procedure successfully completed.

SQL> DECLARE
num1 INTEGER;
num2 INTEGER;
  4
BEGIN
num1:=&num1;
num2:=&num2;
IF num1 > num2 THEN
dbms_output.put_line('NUM1 SMALLER THAN NUM2');
 10
ELSIF num1 = num2 THEN
dbms_output.put_line('EQUAL');
 13
ELSE
dbms_output.put_line('NUM2 SMALLER THAN NUM1');
end if;
 17
END;
/

输入

Enter value for num1: 0
old   6: num1:=&num1;
new   6: num1:=0;
Enter value for num2: 0
old   7: num2:=&num2;
new   7: num2:=0;

输出

EQUAL

PL/SQL procedure successfully completed.

SQL> DECLARE
num1 INTEGER;
num2 INTEGER;
  4
BEGIN
num1:=&num1;
num2:=&num2;
IF num1 > num2 THEN
dbms_output.put_line('NUM1 SMALLER THAN NUM2');
 10
ELSIF num1 = num2 THEN
dbms_output.put_line('EQUAL');
 13
ELSE
dbms_output.put_line('NUM2 SMALLER THAN NUM1');
end if;
 17
END;
/

输入

Enter value for num1: 11
old   6: num1:=&num1;
new   6: num1:=11;
Enter value for num2: 6
old   7: num2:=&num2;
new   7: num2:=6;

输出

NUM1 SMALLER THAN NUM2

PL/SQL procedure successfully completed.

SQL> DECLARE
num1 INTEGER;
num2 INTEGER;
  4
BEGIN
num1:=&num1;
num2:=&num2;
IF num1 > num2 THEN
dbms_output.put_line('NUM1 SMALLER THAN NUM2');
 10
ELSIF num1 = num2 THEN
dbms_output.put_line('EQUAL');
 13
ELSE
dbms_output.put_line('NUM2 SMALLER THAN NUM1');
end if;
 17
END;
/

输入

Enter value for num1: 17
old   6: num1:=&num1;
new   6: num1:=17;
Enter value for num2: 19
old   7: num2:=&num2;
new   7: num2:=19;

输出

NUM2 SMALLER THAN NUM1

PL/SQL procedure successfully completed.

4.) 嵌套 if condition then statements

这也是 PL / SQL 中使用的条件语句之一。在这里，我们将编写相同的 if else 语句。但在 if 语句的位置，我们将编写另一个 if condition else 语句。这些语句的运作方式与之前的 if else 语句相同。

语法

Syntax 1
IF <CONDITION> THEN

IF <CONDITION> THEN
Statement 1 
Statement 2
.
.
.
.
Statement N
ELSE 
Statement 1 
Statement 2
.
.
.
.
Statement N
END IF;
ELSE
Statement 1 
Statement 2
.
.
.
.
Statement N
END IF;
Syntax 2:
IF <CONDITION> THEN
Statement 1 
Statement 2
.
.
.
.
Statement N


ELSE
IF <CONDITION> THEN
Statement 1 
Statement 2
.
.
.
.
Statement N
ELSE 
Statement 1 
Statement 2
.
.
.
.
Statement N
END IF;
END IF;

示例查询

 SQL> DECLARE
num1 INTEGER;
num2 INTEGER;
num3 INTEGER;
  5
BEGIN
num1:= 17;
num2:= 12;
num3:= 10;
IF num1 > num2 THEN
 11
 12
 13
 14
IF num1 > num3 THEN
  dbms_output.put_line('The greatest number is '|| num1);
  END IF;
ELSIF num2 > num3 THEN
 dbms_output.put_line('The greatest number is '|| num2);
ELSE
dbms_output.put_line('The greatest number is '|| num3);
END IF;
 23
END;
/

输出

The greatest number is 17

PL/SQL procedure successfully completed.

SQL> DECLARE
num1 INTEGER;
num2 INTEGER;
num3 INTEGER;
  5
BEGIN
num1:= 9;
num2:= 12;
num3:= 10;
IF num1 > num2 THEN
 11
 12
 13
 14
IF num1 > num3 THEN
  dbms_output.put_line('The greatest number is '|| num1);
  END IF;
ELSIF num2 > num3 THEN
 dbms_output.put_line('The greatest number is '|| num2);
ELSE
dbms_output.put_line('The greatest number is '|| num3);
END IF;
 23
END;
/ 

输出

The greatest number is 12

PL/SQL procedure successfully completed.

 SQL> DECLARE
num1 INTEGER;
num2 INTEGER;
num3 INTEGER;
  5
BEGIN
num1:= 9;
num2:= 1;
num3:= 10;
IF num1 > num2 THEN
 11
 12
 13
 14
IF num1 > num3 THEN
  dbms_output.put_line('The greatest number is '|| num1);
  END IF;
ELSIF num2 > num3 THEN
 dbms_output.put_line('The greatest number is '|| num2);
ELSE
dbms_output.put_line('The greatest number is '|| num3);
END IF;
 23
END;
/

输出

The greatest number is 10
PL/SQL procedure successfully completed.

SQL> DECLARE
  2  num1 INTEGER;
  3  num2 INTEGER;
  4  num3 INTEGER;
  5
  6  BEGIN
  7  num1:=&num1;
  8  num2:=&num2;
  9  num3:=&num3;
 10  IF num1 > num2 THEN
 11
 12
 13
 14
 15  IF num1 > num3 THEN
 16    dbms_output.put_line('The greatest number is '|| num1);
 17    END IF;
 18  ELSIF num2 > num3 THEN
 19   dbms_output.put_line('The greatest number is '|| num2);
 20
 21  ELSE
 22  dbms_output.put_line('The greatest number is '|| num3);
 23  END IF;
 24
 25  END;
 26  /

输入

Enter value for num1: 10
old   7: num1:=&num1;
new   7: num1:=10;
Enter value for num2: 16
old   8: num2:=&num2;
new   8: num2:=16;
Enter value for num3: 20
old   9: num3:=&num3;
new   9: num3:=20;

输出

The greatest number is 20

PL/SQL procedure successfully completed.

SQL> DECLARE
  2  num1 INTEGER;
  3  num2 INTEGER;
  4  num3 INTEGER;
  5
  6  BEGIN
  7  num1:=&num1;
  8  num2:=&num2;
  9  num3:=&num3;
 10  IF num1 > num2 THEN
 11
 12
 13
 14
 15  IF num1 > num3 THEN
 16    dbms_output.put_line('The greatest number is '|| num1);
 17    END IF;
 18  ELSIF num2 > num3 THEN
 19   dbms_output.put_line('The greatest number is '|| num2);
 20
 21  ELSE
 22  dbms_output.put_line('The greatest number is '|| num3);
 23  END IF;
 24
 25  END;
 26  /

输入

Enter value for num1: 11
old   7: num1:=&num1;
new   7: num1:=11;
Enter value for num2: 16
old   8: num2:=&num2;
new   8: num2:=16;
Enter value for num3: 12
old   9: num3:=&num3;
new   9: num3:=12;

输出

The greatest number is 16
PL/SQL procedure successfully completed.

SQL> DECLARE
  2  num1 INTEGER;
  3  num2 INTEGER;
  4  num3 INTEGER;
  5
  6  BEGIN
  7  num1:=&num1;
  8  num2:=&num2;
  9  num3:=&num3;
 10  IF num1 > num2 THEN
 11
 12
 13
 14
 15  IF num1 > num3 THEN
 16    dbms_output.put_line('The greatest number is '|| num1);
 17    END IF;
 18  ELSIF num2 > num3 THEN
 19   dbms_output.put_line('The greatest number is '|| num2);
 20
 21  ELSE
 22  dbms_output.put_line('The greatest number is '|| num3);
 23  END IF;
 24
 25  END;
 26  /

输入

Enter value for num1: 21
old   7: num1:=&num1;
new   7: num1:=21;
Enter value for num2: 20
old   8: num2:=&num2;
new   8: num2:=20;
Enter value for num3: 19
old   9: num3:=&num3;
new   9: num3:=19;

输出

The greatest number is 21

PL/SQL procedure successfully completed.

SQL> DECLARE
  2  num1 INTEGER;
  3  num2 INTEGER;
  4  num3 INTEGER;
  5
  6  BEGIN
  7  num1:=&num1;
  8  num2:=&num2;
  9  num3:=&num3;
 10  IF num1 > num2 THEN
 11
 12
 13
 14
 15  IF num1 > num3 THEN
 16    dbms_output.put_line('The greatest number is '|| num1);
 17    END IF;
 18  ELSIF num2 > num3 THEN
 19   dbms_output.put_line('The greatest number is '|| num2);
 20
 21  ELSE
 22  dbms_output.put_line('The greatest number is '|| num3);
 23  END IF;
 24
 25  END;
 26  /

输入

Enter value for num1: 101
old   7: num1:=&num1;
new   7: num1:=101;
Enter value for num2: 129
old   8: num2:=&num2;
new   8: num2:=129;
Enter value for num3: 306
old   9: num3:=&num3;
new   9: num3:=306;

输出

The greatest number is 306

PL/SQL procedure successfully completed.

这就是关于 PL / SQL 条件表达式的全部内容。

下一主题PL / SQL 中的模式

我们提供所有技术（如 Java 教程、Android、Java 框架）的教程和面试问题

联系信息

G-13, 2nd Floor, Sec-3, Noida, UP, 201301, India

hr@tpointtech.com

+91-9599086977

关注我们

Python

Java

.Net Framework

AI, ML and Data Science

Cloud Technology

B.Tech and MCA

Web Technology

PHP

Software Testing

Technical Interview

Java Interview

Python

Web Interview

Database Interview

B.Tech / MCA

Important Interview

Software Testing Interview

Company Interviews

Online Compilers

Multiple Choice Questions

PL/SQL 教程

控制语句

PL/SQL 过程

PL/SQL 函数

PL/SQL 游标

PL/SQL 异常

PL/SQL 触发器

其他

面试题

测验

PL / SQL 中的条件语句

条件语句的类型

1.) if Condition then Statement

2.) if Condition then else Statement

3.) if condition elsif condition else statements

4.) 嵌套 if condition then statements

相关帖子

PL/SQL 触发器的组成部分

PL/SQL 的基本元素

PL/SQL 包

PL/SQL 中的 NULL 语句

在 PL/SQL 中将参数传递给游标

PL/SQL 中的模式

订阅 Tpoint Tech

联系信息

关注我们

教程

面试题

在线编译器