在本章中，无涯教程将讨论PL/SQL中的运算符， PL/SQL语言具有丰富的内置运算符，并提供以下类型的运算符-

  • 算术运算符
  • 关系运算符
  • 比较运算符
  • 逻辑运算符

在这里，无涯教程将一一理解算术，关系，比较和逻辑运算符。

算术运算符

下表显示了PL/SQL支持的所有算术运算符。假设变量A =10，而变量B=5，则-

运算符 描述 示例
+ 相加 A + B=15
- 相减 A-B=5
* 相乘 A * B=50
/ 相除 A/B=2
** 幂 A ** B=100000 
BEGIN  
   dbms_output.put_line( 10 + 5); 
   dbms_output.put_line( 10 - 5); 
   dbms_output.put_line( 10 * 5); 
   dbms_output.put_line( 10/5); 
   dbms_output.put_line( 10 ** 5); 
END; 
/

当以上代码在SQL提示符下执行时，将产生以下输出-

15 
5 
50 
2 
100000  

PL/SQL procedure successfully completed.

关系运算符

关系运算符比较两个表达式或值，然后返回布尔输出，下表显示了PL/SQL支持的所有关系运算符。假设变量A =10，而变量B=20，则-

运算符 描述 示例
= 相等 (A=B) is not true.

!=

<>

~=

 不相等 (A != B) is true.
> 大于 (A > B) is not true.
< 小于 (A < B) is true.
>= 大于或等于 (A >= B) is not true.
<= 小于或等于 (A <= B) is true 
DECLARE 
   a number (2) := 21; 
   b number (2) := 10; 
BEGIN 
   IF (a = b) then 
      dbms_output.put_line('Line 1 - a is equal to b'); 
   ELSE 
      dbms_output.put_line('Line 1 - a is not equal to b'); 
   END IF;  
   IF (a < b) then 
      dbms_output.put_line('Line 2 - a is less than b'); 
   ELSE 
      dbms_output.put_line('Line 2 - a is not less than b'); 
   END IF; 
    
   IF ( a > b ) THEN 
      dbms_output.put_line('Line 3 - a is greater than b'); 
   ELSE 
      dbms_output.put_line('Line 3 - a is not greater than b'); 
   END IF;  
   -- Lets change value of a and b 
   a := 5; 
   b := 20; 
   IF ( a <= b ) THEN 
      dbms_output.put_line('Line 4 - a is either equal or less than b'); 
   END IF; 
   IF ( b >= a ) THEN 
      dbms_output.put_line('Line 5 - b is either equal or greater than a'); 
   END IF;
   IF ( a <> b ) THEN 
      dbms_output.put_line('Line 6 - a is not equal to b'); 
   ELSE 
      dbms_output.put_line('Line 6 - a is equal to b'); 
   END IF;  
END; 
/

当以上代码在SQL提示符下执行时，将产生以下输出-

Line 1 - a is not equal to b 
Line 2 - a is not less than b 
Line 3 - a is greater than b 
Line 4 - a is either equal or less than b 
Line 5 - b is either equal or greater than a 
Line 6 - a is not equal to b  

PL/SQL procedure successfully completed

比较运算符

比较运算符用于将一个表达式与另一个表达式进行比较。输出始终为 TRUE，FALSE 或 NULL 。

运算符 描述 示例
LIKE LIKE 模糊匹配运算符。 If 'Zara Ali' like 'Z% A_i' returns a Boolean true。
BETWEEN BETWEEN 范围运算符。 If x=10 then, x between 5 and 20 returns true,  but x between 11 and 20 returns false.
IN IN 判断是否在集合中 If x='m' then, x in ('a', 'b', 'c') returns Boolean false but x in ('m', 'n', 'o') returns Boolean true.
IS NULL 判断是否为NULL If x='m', then 'x is null' returns Boolean false.

LIKE运算符

该程序测试LIKE运算符。在这里，无涯教程将使用一个小的 procedure() 来展示LIKE运算符的函数-

DECLARE 
PROCEDURE compare (value  varchar2,  pattern varchar2 ) is 
BEGIN 
   IF value LIKE pattern THEN 
      dbms_output.put_line ('True'); 
   ELSE 
      dbms_output.put_line ('False'); 
   END IF; 
END;  
BEGIN 
   compare('Zara Ali', 'Z%A_i'); 
   compare('Nuha Ali', 'Z%A_i'); 
END; 
/

当以上代码在SQL提示符下执行时，将产生以下输出-

True 
False  

PL/SQL procedure successfully completed.

BETWEEN运算符

以下程序显示BETWEEN运算符的用法-

DECLARE 
   x number(2) := 10; 
BEGIN 
   IF (x between 5 and 20) THEN 
      dbms_output.put_line('True'); 
   ELSE 
      dbms_output.put_line('False'); 
   END IF; 
    
   IF (x BETWEEN 5 AND 10) THEN 
      dbms_output.put_line('True'); 
   ELSE 
      dbms_output.put_line('False'); 
   END IF; 
    
   IF (x BETWEEN 11 AND 20) THEN 
      dbms_output.put_line('True'); 
   ELSE 
      dbms_output.put_line('False'); 
   END IF; 
END; 
/

当以上代码在SQL提示符下执行时，将产生以下输出-

True 
True 
False 
 
PL/SQL procedure successfully completed.

IN和IS NULL运算符

以下程序显示了IN和IS NULL运算符的用法-

ECLARE 
   letter varchar2(1) := 'm'; 
BEGIN 
   IF (letter in ('a', 'b', 'c')) THEN 
      dbms_output.put_line('True'); 
   ELSE 
      dbms_output.put_line('False'); 
   END IF; 
  
   IF (letter in ('m', 'n', 'o')) THEN 
       dbms_output.put_line('True'); 
   ELSE 
      dbms_output.put_line('False'); 
   END IF; 
    
   IF (letter is null) THEN 
    dbms_output.put_line('True'); 
   ELSE 
      dbms_output.put_line('False'); 
   END IF; 
END; 
/

当以上代码在SQL提示符下执行时，将产生以下输出:

False
True
False

PL/SQL procedure successfully completed.

逻辑运算符

下表显示了PL/SQL支持的逻辑运算符。所有这些运算符都对布尔操作数起作用并产生布尔输出。假设变量A=true，而变量B=false ，则-

运算符 描述 示例
and 称为逻辑AND运算符，如果两个操作数都为真,则条件为真。 (A and B) is  false
or 称为逻辑OR运算符，如果两个操作数中的任何一个为true,则条件为true。 (A or B) is true
not 称为逻辑NOT运算符，用于反转其操作数的逻辑状态。 not (A and B) is true 
DECLARE 
   a boolean := true; 
   b boolean := false; 
BEGIN 
   IF (a AND b) THEN 
      dbms_output.put_line('Line 1 - Condition is true'); 
   END IF; 
   IF (a OR b) THEN 
      dbms_output.put_line('Line 2 - Condition is true'); 
   END IF; 
   IF (NOT a) THEN 
      dbms_output.put_line('Line 3 - a is not true'); 
   ELSE 
      dbms_output.put_line('Line 3 - a is true'); 
   END IF; 
   IF (NOT b) THEN 
      dbms_output.put_line('Line 4 - b is not true'); 
   ELSE 
      dbms_output.put_line('Line 4 - b is true'); 
   END IF; 
END; 
/

当以上代码在SQL提示符下执行时，将产生以下输出-

Line 2 - Condition is true 
Line 3 - a is true 
Line 4 - b is not true  

PL/SQL procedure successfully completed.

参考链接

https://www.learnfk.com/plsql/plsql-operators.html