无涯教程-PL/SQL - 运算符

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

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

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

算术运算符

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

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，则-

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运算符。在这里，无涯教程将使用一个小的 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 ，则-

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