PL/SQL - 运算符

PL/SQL - 运算符 首页 / PL/SQL入门教程 / PL/SQL - 运算符

在本章中,无涯教程将讨论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 。

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

LIKE运算符

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

链接:https://www.learnfk.comhttps://www.learnfk.com/plsql/plsql-operators.html

来源:LearnFk无涯教程网

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. 

祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)

技术教程推荐

白话法律42讲 -〔周甲徳〕

Linux实战技能100讲 -〔尹会生〕

小马哥讲Spring AOP编程思想 -〔小马哥〕

Spark性能调优实战 -〔吴磊〕

高楼的性能工程实战课 -〔高楼〕

大厂广告产品心法 -〔郭谊〕

Web 3.0入局攻略 -〔郭大治〕

云原生架构与GitOps实战 -〔王炜〕

结构思考力 · 透过结构看问题解决 -〔李忠秋〕

好记忆不如烂笔头。留下您的足迹吧 :)