2.1 字符集
任何PL/SQL处理的或存储在数据库中的字符数据都必须由一个字节序列来表示.
单个字符的字节序列被称为字符码(character code),一系列的字符码被称为字符集(character set)
每个Oracle数据库都支持数据库字符集和国家字符集,PL/SQL也支持这些字符集.
2.1.1 数据库字符集
PL/SQL使用数据库字符集来表示:
- 存储的PL/SQL程序单元的源码文本
- 数据类型CHAR,VARCHAR2,CLOB和LONG的字符值
数据库字符集可以是单字节的(一个字节映射一个支持的字符),也可以是可变宽多字节的
(一个,两个,三个或四个字节序列映射一个支持的字符),一个字符码的最大字节数取决于字符集
每个数据库字符集都包含如下的基本字符: - 拉丁字母: A到Z和a到z
- 数字: 0到9
- 标点符号: 如下
- 空白字符: 空格,制表符,新行和回车
标点符号列表如下:
( ) < > + - * / = , ; : . ! ? ' " @ % # $ _ |
2.1.2 国家字符集
PL/SQL使用国家字符集来表示数据类型NCHAR,NVARCHAR2和NCLOB数据类型的值
2.2 词法单元
词法单元是PL/SQL的最小独立元素
2.2.1 分隔符(Delimiters)
分隔符是一个字符或字符组合,在PL/SQL中有特殊含义,不要在分隔符中嵌入任何字符(包括空白字符)
PL/SQL的分隔符有如下这些:
+ 加操作符
:= 赋值操作符
=> Association操作符
% 属性指示符
' 字符串分隔符
. 元素指示符
|| 连接操作符
/ 除操作符
** Exponentiation操作符
( 表达式或列表分隔符(开始)
) 表达式或列表分隔符(结束)
: 宿主变量指示符
, 分隔符
<< 标签分隔符(开始)
>> 标签分隔符(结束)
/* 多行注释分隔符(开始)
*/ 多行注释分隔符(结束)
* 乘操作符
" Quoted identifier分隔符
.. 范围操作符
= 关系操作符(相等)
<> 关系操作符(不相等)
!= 关系操作符(不相等)
~= 关系操作符(不相等)
^= 关系操作符(不相等)
< 关系操作符(小于)
> 关系操作符(大于)
<= 关系操作符(小于等于)
>= 关系操作符(大于等于)
@ 远程访问指示符
-- 当行注释指示符
; 语句结束符
- 减或负数操作符
2.2.2 标识符(Identifiers)
标识符用于命名PL/SQL的各种元素,包括:
常量 游标 异常 关键字 标签 包 保留字 子程序 类型 变量
除了用户自定义的引用类型的名字(双引号包围的),PL/SQL对大小写是不敏感的,
例如lastname, LastName和LASTNAME都是相同的同一个变量
保留字与关键字
保留字和关键字是那些在PL/SQL中具有特殊含义的标识符
在你定义自己的普通标识符时,不能使用保留字,你可以在双引号包围的自定义标识符里使用保留字,但是不推荐这么做
在你定义自己的普通标识符时,可以使用关键字,但同样不推荐这么做
预定义的标识符
预定义的标识符在系统包STANDARD中进行了预先声明,例如INVALID_NUMBER是一个预定义的异常
要查询所有的预定义标识符,可以以DBA角色登录数据库,并执行SQL
SELECT type_name FROM ALL_TYPES WHERE predefined = 'YES';
在你定义自己的普通标识符时,可以使用这些预定义的标识符,但是不推荐这么做,因为
你的定义会覆盖掉这些全局声明
用户自定义标识符
一个用户自定义标识符是:
-
来自数据库字符集中的字符的组合
-
普通的 或者是 引用的(双引号包围) 尽量使自定义标识符具有明显的含义,例如cost_per_thousand要比cpt更好
普通的用户自定义标识符
遵循如下规则: -
以字母开头
-
可以包含字母,数字,和这三个符号: $ # _
-
不可以是保留字 数据库字符集定义了哪些是字母和数字
如果 COMPATIBLE 被设置为12.2或更高,那么标识符的长度不能超过128字节,如果设置为
12.1或更低,这个限制就是30个字节
下面是一些合法的自定义标识符:X t2 phone# credit_limit LastName oracle$number money$$tree SN## try_again_
下面是一些不合法的自定义标识符:
mine&yours
debit-amount
on/off
user id
引用的用户自定义标识符
引用的用户自定义标识符由双引号包围起来
在双引号中,任何来自数据库字符集中的字符都被允许使用(除了双引号本身),例如:
"X+Y"
"last name"
"on/off switch"
"employee(s)"
"*** header info ***"
如果 COMPATIBLE 被设置为12.2或更高,那么标识符的长度不能超过128字节,
如果设置为12.1或更低,这个限制就是30个字节
引用的用户自定义标识符是大小写敏感的,但是有一个例外: 如果去掉包围的双引号后标识符仍然是一个有效
的普通用户自定义标识符,那么双引号对于引用这个标识符就是可有可无的,
如果你省略了双引号,标识符就是大小写不敏感的
不推荐使用保留字作为引用的用户自定义标识符
样例程序1
-- 引用的用户自定义标识符 "HELLO" 如果去掉包围的双引号,仍然是一个有效的普通
-- 用户自定义标识符,所以对于标识符 Hello 的引用仍是有效的
DECLARE
"HELLO" VARCHAR2(10) := 'hello';
BEGIN
DBMS_OUPUT.PUT_LINE(Hello);
END;
/
输出结果为
hello
样例程序2
-- 使用保留字作为引用的用户自定义标识符
DECLARE
"BEGIN" VARCHAR2(15) := 'UPPERCASE';
"Begin" VARCHAR2(15) := 'Initial Capital';
"begin" VARCHAR2(15) := 'lowercase';
BEGIN
DBMS_OUTPUT.PUT_LINE("BEGIN");
DBMS_OUTPUT.PUT_LINE("Begin");
DBMS_OUTPUT.PUT_LINE("begin");
END;
/
输出结果为
UPPERCASE
Initial Capital
lowercase
2.2.3 字面量(Literals)
字面量既不是标识符,又不是通过别的值计算得来
例如 123 是一个整数字面量,'abc' 是一个字符串字面量,但是 1+2 就不是一个字面量
PL/SQL包含所有的SQL字面量,另外还有BOOLEAN字面量(这是SQL字面量所没有的)
一个BOOLEAN字面量是预定义的逻辑值: TRUE, FALSE 或 NULL
NULL表示一个未知的值
当在PL/SQL中使用字符字面量时要记住:
- 字符字面量是大小写敏感的,例如'Z'和'z'是不同的
- 空白字符是有意义的,例如'abc', ' abc', 'abc ', ' a bc '它们都是不同的
- PL/SQL中没有"行连续符"(表示该字符串和上一行应该连接在一起),如果你在
- 源码中的字符串中有了换行,那就会在字符串中包含一个换行符
- 如果你想保持一个较长的字符串连续不换行,可以使用 || 来连接两个或多个
- 字符串
- '0'到'9'不等同于整数字面量0到9,但是PL/SQL会转换它们,所以你可以在arithmetic
- 表达式中使用它们
- 没有字符的字符字面量的值是NULL,被称为空字符串(null string),但是这个NULL
- 不是BOOLEAN值NULL
- 一个普通的字符字面量由数据库字符集中的字符组成
- 一个国家字符字面量由国家字符集中的字符组成
2.2.4 编译指令(Pragmas)
编译指令用于在编译时如何进行处理
编译指令以保留字 PRAGMA 开头,后跟编译指令的名字,一些编译指令有参数
编译指令可能会出现在声明或语句的前面,额外的限制可能会应用于特别的编译指令上
不同的编译指令有不同的影响范围,编译指令的名称或参数如果不被编译器识别,则不会产生效果
有如下的一些编译指令:
- AUTONOMOUS_TRANSACTION
- COVERAGE
- DEPRECATE
- EXCEPTION_INIT
- INLINE
- RESTRICT_REFERENCES
- SERIALLY_REUSABLE
- UDF
2.2.5 注释(Comments)
PL/SQL会忽略代码中的注释,注释的作用是让别人理解你的代码
你应该使用注释来描述每一个代码块的目的和用法,对于未完成的或废弃的代码,
也可以使用注释将它们屏蔽掉
单行注释
以 -- 开头并一直延伸作用到行的末尾
多行注释
以 /* 开头, 以 */ 结尾, 可以注释多行
多行注释不能嵌套,但是多行注释中可以包含单行注释
2.2.6 词法单元之间的空白字符
在词法单元间放入空白,可以增加代码的可读性,例如:
DECLARE
x NUMBER := 10;
y NUMBER := 5;
max NUMBER;
BEGIN
IF x>y THEN max:=x; ELSE max:=y; END IF; -- 代码是正确的但难以阅读
-- 下面是易于阅读的
IF x > y THEN
max := x;
ELSE
max := y;
END IF;
END;
/
2.3 声明
声明会为一个指定数据类型的值分配存储空间,并给这块存储空间命名,以便于你引用它
声明可以出现在代码块,子程序或包的声明部分
2.3.1 非空约束
你可以在一个标量变量或常量(也可以在复合变量或常量中的一个标量元素)上强制非空约束
非空约束会阻止分配空值,非空约束可以显式或隐式(从其所属数据类型)的获得
PL/SQL把零长度的字符串也作为空值(NULL)处理,要想测试空值,可以使用"IS NULL"或
"IS NOT NULL"操作符,字符函数或表达式计算返回的零长度字符串也会被当做NULL值
样例程序如下:
DECLARE
acct_id INTEGER(4) NOT NULL := 9999;
a NATURALN := 9999;
b POSITIVEN := 9999;
c SIMPLE_INTEGER := 9999;
BEGIN
NULL;
END;
/
2.3.2 声明变量
变量的声明总是会指定变量的名称和数据类型,对于大多数数据类型,变量声明时可以同时
指定其初始值
变量的数据类型可以是任何PL/SQL数据类型,数据类型可以是标量的(没有内部元素),也可以
是复合的(有内部元素)
样例程序如下:
DECLARE
part_number NUMBER(6); -- SQL数据类型
part_name VARCHAR2(20); -- SQL数据类型
in_stock BOOLEAN; -- PL/SQL独有的数据类型
part_price NUMBER(6,2); -- SQL数据类型
part_description VARCHAR2(50); -- SQL数据类型
BEGIN
NULL;
END;
/
2.3.3 声明常量
常量的值是不可以更改的,常量的声明比变量的声明要多两个部分: CONSTANT关键字和初始值
样例程序如下:
DECLARE
credit_limit CONSTANT REAL := 5000.00;
max_days_in_year CONSTANT INTEGER := 366;
urban_legend CONSTANT BOOLEAN := FALSE;
BEGIN
NULL;
END;
/
2.3.4 初始化变量和常量的值
在变量声明中,初始值是可选的,除非你指定了NOT NULL(非空约束),而在常量声明中,
初始值是需要的
如果声明是在代码块或子程序里,赋给变量或常量的初始值由代码块或子程序控制,
而如果声明在包头里,则赋给变量或常量的初始值是全局的(无论变量或常量是公开的还是私有的)
为了指定初始值,你可以使用赋值操作符(:=)或者DEFAULT关键字,后跟一个表达式,
这个表达式可以包含之前声明的变量值或常量值
在使用变量之前必须要给变量赋一个值
样例程序如下:
DECLARE
counter INTEGER; -- 初始化值是NULL
BEGIN
counter := counter + 1; -- NULL+1的结果仍然是NULL
IF counter IS NULL THEN
DBMS_OUTPUT.PUT_LINE('counter is NULL');
END IF;
END;
/
2.3.5 使用 %TYPE 来声明常量或变量
%TYPE 属性可以让你声明一个与之前声明的变量或数据库表字段具有同样数据类型的
数据项,如果被引用者的类型改变了,那么引用者的类型也会相应改变
语法格式如下:
引用者 被引用者%TYPE
引用者继承了被引用者的如下属性:
-
数据类型和大小
-
约束(当被引用者不是表字段时)
引用者并不继承被引用者的初始值,所以如果引用者指定或继承了NOT NULL约束,
那么你必须给引用者一个初始值
当声明一个变量用来存放数据库表的字段值时,使用%TYPE特别有用
语法格式如下:变量名 表名.字段名%TYPE
样例程序如下:
DECLARE
surname employees.last_name%TYPE; -- employees表的last_name有非空约束
name VARCHAR2(25) NOT NULL := 'Smith';
lname name%TYPE := 'Jones'; -- 会继承name的非空约束,所以赋初始值
BEGIN
DBMS_OUTPUT.PUT_LINE('name=' || name);
DBMS_OUTPUT.PUT_LINE('surname=' || surname); -- surname并没有继承非空约束,也没有初始值
DBMS_OUTPUT.PUT_LINE('lname=' || lname);
END;
/
2.4 标识符的引用
当引用一个标识符时,你可以使用它的: 简单名, 限定名, 远程(remote)名
简单名就是标识符在声明时的名字
如果一个标识符是在一个已命名的PL/SQL程序单元里,你可以(有时是必须)
使用限定名来引用它,例如
程序单元名.标识符简单名
如果标识符命名的对象是在远程数据库服务器上,你就必须使用远程名来引用它,
例如:
标识符简单名@远程数据库链接
如果标识符声明在远程数据库的一个已命名的PL/SQL程序单元里,你必须使用
带限定的远程名来引用它
程序单元名.标识符简单名@远程数据库链接
你可以为数据库对象创建synonym,但是不能为PL/SQL子程序或包里声明的
对象声明synonym
2.5 标识符的作用域与可见性
标识符的作用域,是在PL/SQL程序单元中你可以引用它的区域,而标识符的可见性,
是在PL/SQL程序单元里你不使用限定名就可以引用它的区域
在一个PL/SQL程序单元内声明的标识符,对于这个程序单元来说是本地(local)标识符,
而如果这个程序单元还有子单元,那么这个标识符对于这些子单元来说就是全局(global)的
如果在子单元内也声明了与全局标识符同名的标识符,那么在子单元内,只有本地标识符是可见的,
要想引用全局标识符,必须使用全局标识符声明所在的程序单元名称作为限定名
一个PL/SQL程序单元不能引用与其同级的其他单元的标识符,因为那些标识符既不是
local也不是global的标识符
在同一个PL/SQL程序单元内部,你不能声明同一个标识符两次,如果这样做了会报错
样例程序1:
-- Outer block
DECLARE:
a CHAR; -- Scope of a (CHAR) begins
b REAL; -- Scope of b begins
BEGIN
-- Visible: a (CHAR), b
-- First sub-block
DECLARE
a INTEGER; -- Scope of a (INTEGER) begins
c REAL; -- Scope of c begins
BEGIN
-- Visible: a (INLTEGER), b, c
NULL;
END; -- Scope of a (INTEGER) and c end
-- Second sub-block
DECLARE
d REAL; -- Scope of d begins
BEGIN
-- Visible: a (CHAR), b, d
NULL
END; -- Scope of d ends
-- Visible: a (CHAR), b
END; -- Scope of a (CHAR) and b end
/
样例程序2:
<<outer>> -- label
DECLARE
birthdate DATE := TO_DATE('09-AUG-70', 'DD-MON-YY');
BEGIN
DECLARE
birthdate DATE := TO_DATE('20-SEP-70', 'DD-MON-YY');
BEGIN
IF birthdate = outer.birthdate THEN -- 通过outer来限定引用外部块中声明的同名变量
DBMS_OUTPUT.PUT_LINE('Same Birthday');
ELSE
DBMS_OUTPUT.PUT_LINE('Different Birthday');
END IF;
END;
END;
/
样例程序3:
CREATE OR REPLACE PROCEDURE check_credit (credit_limit NUMBER) AS
rating NUMBER := 3;
FUNCTION check_rating RETURN BOOLEAN IS
rating NUMBER := 1;
over_limit BOOLEAN;
BEGIN
IF check_credit.rating <= credit_limit THEN
over_limit := FALSE;
ELSE
over_limit := TRUE;
rating := credit_limit;
END IF;
RETURN over_limit;
END check_rating;
BEGIN
IF check_rating THEN
DBMS_OUTPUT.PUT_LINE('Credit rating over limit (' ||
TO_CHAR(credit_limit) ||
'). ' ||
'Rating: ' ||
TO_CHAR(rating));
ELSE
DBMS_OUPUT.PUT_LINE('Credit rating OK. ' ||
'Rating: ' ||
TO_CHAR(rating));
END IF;
END;
/
BEGIN
check_credit(1);
END;
/
2.6 变量的赋值
在声明了变量之后,你可以通过以下三种方式给其赋值
- 使用赋值语句
- 使用 SELECT INTO 或 FETCH 语句来从一个表中获取给变量的赋值
- 将变量作为OUT或IN OUT类型的参数传入一个子程序,然后在子程序中给其赋值 变量和值必须是可以相互兼容(可以隐式转换)的数据类型
2.6.1 使用赋值语句来给变量赋值
将一个表达式的值赋给一个变量,可以使用赋值语句:
变量名 := 表达式;
2.6.2 使用SELECT INTO语句来给变量赋值
一个SELECT INTO语句的简单形式如下:
SELECT 字段_1 [, 字段_2] ...
INTO 变量_1 [, 变量_2] ...
FROM 表名;
每一个字段,都必须有一个对应的,类型兼容的变量,由于SQL没有BOOLEAN类型,
所以变量不能是BOOLEAN类型的
2.6.3 作为子程序的参数来给变量赋值
如果你把一个变量作为IN或IN OUT类型的参数传递给一个子程序,并且在子程序内部给这个
变量进行赋值操作,那么在子程序运行结束后,这个变量的值将会保留下来
2.6.4 给BOOLEAN类型的变量赋值
你给BOOLEAN类型的变量只能赋值TRUE, FALSE和NULL
样例程序如下:
DECLARE
done BOOLEAN; -- 默认的初识值是NULL
counter NUMBER := 0;
BEGIN
done := FALSE;
WHILE done != TRUE
LOOP
counter := counter + 1;
done := (counter > 500);
END LOOP;
END;
/
2.7 表达式
表达式由值,操作符,SQL函数组成,通常会产生一个值
一个表达式总是会产生一个值,如下是最简单的表达式,慢慢增加复杂性:
- 单个常量或变量(例如 a)
- 一元操作符和它的操作数(例如 -a)
- 二元操作符和它的两个操作数(例如 a+b)
操作数可以是变量,常量,字面量,操作符,函数调用,或占位符,又或者是另一个表达式,
因此,表达式可以非常复杂的
操作数的数据类型决定了表达式的数据类型,也决定了表达式计算结果的数据类型
2.7.1 连接运算符
连接运算符(||)用于将一个字符串与另一个字符串相接
连接运算符会忽略空值
样例程序如下:
DECLARE
x VARCHAR2(4) := 'suit';
y VARCHAR2(4) := 'case';
BEGIN
DBMS_OUTPUT.PUT_LINE(x || NULL || y);
END;
/
输出结果为
suitcase
2.7.2 运算优先级
一个运算要么是一元的(针对一个运算数)要么是二元的(针对两个运算数)
表达式中的运算的顺序取决于运算符的优先级
下面列出了运算符优先级(从高到低):
** 指数(乘方)
+, - 正负号
*, / 乘,除
+, -, || 加,减,连接
=, <, >, <=, >= <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN 比较
NOT 非
AND 与
OR 或
如果想控制运算顺序,可以把想先计算的部分封装入小括号,多个嵌套的小括号,先计算
最里面的小括号部分
在不影响运算顺序的前提下,可以使用小括号来提升代码可读性
2.7.3 逻辑运算符
逻辑运算符 AND, OR 和 NOT 跟在三态逻辑后
AND 和 OR 是二元操作符,而 NOT 是一元操作符
下面是逻辑真值表:
x y x AND y x OR y NOT x
TRUE TRUE TRUE TRUE FALSE
TRUE FALSE FALSE TRUE FALSE
TRUE NULL NULL TRUE FALSE
FALSE TRUE FALSE TRUE TRUE
FALSE FALSE FALSE FALSE TRUE
FALSE NULL FALSE NULL TRUE
NULL TRUE NULL TRUE NULL
NULL FALSE FALSE NULL NULL
NULL NULL NULL NULL NULL
只有两个操作数都是TRUE的情况下AND才会返回TRUE
任何一个操作数是TRUE的情况下OR都会返回TRUE
NOT会返回操作数的相反值,除非操作数是NULL, NOT NULL会返回NULL
2.7.4 短路运算
当计算逻辑表达式时,PL/SQL使用短路运算规则: 当可以确定结果时就停止继续运算
例如:
DECLARE
on_hand INTEGER := 0;
on_order INTEGER := 100;
BEGIN
-- 不会引起 divide-by-zero错误
-- 因为计算在第一个表达式处就停止了
IF (on_hand = 0) OR ((on_order/on_hand) < 5) THEN
DBMS_OUTPUT.PUT_LINE('On hand quantity is zero');
END IF;
END;
/
2.7.5 比较运算符
比较运算符用于两个表达式之间进行比较,结果通常是TRUE, FALSE或NULL
如果其中一个表达式的值是NULL,那么比较的结果也是NULL
字符的比较会受到NLS参数的影响
对CLOB类型的值进行比较,会产生临时的LOB值,确保你的临时表空间足够大,
可以处理LOB值
IS [NOT] NULL运算符
如果操作数是NULL,那么IS NULL会返回TRUE,如果操作数不是NULL则返回FALSE
IS NOT NULL刚好相反
包含NULL的比较运算通常结果也是NULL
关系运算符
下面列出了关系运算符:
= 相等
<> != ~= ^= 不相等
< 小于
> 大于
<= 小于等于
>= 大于等于
数字比较
REAL类型的数字存储的值是近似的,因此Oracle推荐仅对它们做相等或不相等的比较
布尔比较
根据定义,TRUE大于FALSE,任何与NULL的比较,结果都是NULL
字符比较
默认情况下,如果一个字符比另一个字符的二进制值更大,那它就更大
例如 'y' > 'r' 的结果是TRUE
字符串之间的比较会逐个字符进行比较,例如 'Kathy' > 'Kathryn' 的结果是TRUE
如果你设置了数据库初始化参数 NLS_COMP=ANSI,字符串比较将会使用NLS_SORT初始化
参数确定的校对序列
校对序列(collating sequence)是字符集的内部顺序值(一定范围的数字代码),如果
一个字符的内部数字代码比另一个字符的更大,那它就大于另一个字符更大
每种语言中,字符在校准序列中可能不会相同,例如,一个带重音的字母的排序取决于
不同的数据库字符集,即使这个字母的二进制值在不同的数据库字符集中都是一样的
通过改变NLS_SORT参数的值,你可以做到大小写不敏感的比较,重音不敏感的比较
大小写不敏感的比较将大写字母和小写字母作为同一个字母进行处理,例如 'a'='A'
和 'Alpha'='ALPHA' 的结果都是TRUE
为了使字符比较成为大小写不敏感的,可以对NLS_SORT参数的值追加"_CI",例如
"BINARY_CI"或"XGERMAN_CI"
重音不敏感的比较是大小写不敏感的,同时也将只具有不同重音或标点的字母作为
相同的字母,例如 'Cooperate' = 'Co-Operate' 和 'Co-Operate' = 'coöperate'
结果都是TRUE
为了使字符比较成为大小写不敏感和重音不敏感的,可以对NLS_SORT参数的值追加
"_AI", 例如"BINARY_AI"或"FRENCH_M_AI"
CHAR和VARCHAR2数据类型在语义上的不同,会影响字符比较
日期比较
一个日期距离现在更近的话,就更大,例如 '01-JAN-91' > '31-DEC-90' 的结果是TRUE
LIKE运算符
LIKE运算符将一个字符,字符串或CLOB值与一个模式(pattern)进行比较,如果匹配则返回
TRUE,否则返回FALSE
字符大小写在这里是有意义的
模式可以包含两种通配符: 下划线(_) 和 百分号(%)
下划线匹配单个字符,而百分号匹配0个或多个字符
如果要匹配下划线或百分号本身,需要在它们前面使用转义字符\
样例程序如下:
DECLARE
PROCEDURE half_of_off (sale_sign VARCHAR2) IS
BEGIN
IF sale_sign LIKE '50\% off!' ESCAPE '\' THEN
DBMS_OUTPUT.PUT_LINE('TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('FALSE');
END IF;
END;
BEGIN
half_off('Going out of business');
half_off('50% off!');
END;
/
BETWEEN运算符
BETWEEN运算符测试一个值是否在一个给定范围内
x BETWEEN a AND b 等同于 (x >= a) AND (x <= b)
IN运算符
IN运算符测试一个值是否是一个给定集合的成员
x IN (set) 在x等于set中的一个成员时返回TRUE
2.7.6 布尔表达式
布尔表达式是返回布尔值(TRUE,FALSE或NULL)的表达式
最简单的布尔表达式是一个布尔字面量,常量或变量,像下面这些也是布尔表达式:
NOT 布尔表达式
布尔表达式 关系运算符 布尔表达式
布尔表达式 AND 布尔表达式
布尔表达式 OR 布尔表达式
通常在PL/SQL控制语句中使用布尔表达式,或者在DML语句的WHERE子句中
你可以使用一个布尔类型的变量作为一个条件,你不需要把它和TRUE或FALSE进行比较
2.7.7 CASE表达式
简单型CASE表达式
语法格式如下:
CASE selector
WHEN selector_value_1 THEN result_1
WHEN selector_value_2 THEN result_2
WHEN selector_value_3 THEN result_3
...
WHEN selector_value_n THEN result_n
[ELSE
else_result]
END
selector是一个表达式(通常是单个变量),每一个selector_value和result可以是
字面量或表达式,至少有一个result不能是字面量NULL
简单型CASE表达式会返回第一次匹配的result值,剩下的表达式将不再继续计算
如果没有selector_value可以匹配selector,如果有ELSE部分则返回else_result,
否则返回NULL
搜索型CASE表达式
语法格式如下:
CASE
WHEN 布尔表达式_1 THEN result_1
WHEN 布尔表达式_2 THEN result_2
WHEN 布尔表达式_3 THEN result_3
...
WHEN 布尔表达式_n THEN result_n
[ELSE
else_result]
END
按次序计算布尔表达式,当遇到第一个布尔表达式的结果为TRUE,则返回对应的result
剩下的布尔表达式将不再继续计算,如果所有的布尔表达式的计算结果都不是TRUE,如果
有ELSE部分则返回else_result,否则返回NULL
2.7.8 PL/SQL表达式中的SQL函数
在PL/SQL表达式中可以使用所有的SQL函数,除了以下:
- 聚合函数(例如AVG, COUNT)
- 分析函数(例如LAG, RATIO_TO_REPORT)
- 数据挖掘函数(例如CLUSTER_ID, FEATURE_VALUE)
- 编码解码函数(例如DECODE, DUMP)
- 模型函数(例如ITERATION_NUMBER, PREVIOUS)
- 对象引用函数(例如REF, VALUE)
- XML函数(例如APPENDCHILDXML, EXISTSNODE)
- 转换函数(BIN_TO_NUM)
- JSON SQL运算符(JSON_ARRAYAGG, JSON_OBJAGG, JSON_TABLE, JSON_TEXTCONTAINS)
- 校对SQL运算符和函数(COLLATE, COLLATION, NLS_COLLATION_ID, NLS_COLLATION_NAME)
- 其他一些函数(CUBE_TABLE, DATAOBJ_TO_PARTITION, LNNVL, NVL2, SYS_CONNECT_BY_PATH,SYS_TYPEID, WIDTH_BUCKET)
PL/SQL支持对BITAND的重载,参数和结果都是BINARY_INTEGER
2.7.9 静态表达式
静态表达式指的是在编译时,表达式的值就可以确定了.也就是说它不包含字符比较,变量,或函数调用
静态表达式是唯一能出现在条件编译指令中的表达式
静态表达式的定义:
-
如果它是NULL字面量,那就是静态表达式
-
如果它是字符,数字,布尔字面量,那就是静态表达式
-
如果是对一个静态常量的引用,那就是静态表达式
-
如果是对一个条件编译变量(以$$开头)的引用,那就是静态表达式
-
如果有允许出现在静态表达式中的运算符,它的所有操作数都是静态的,而且在计算时不会抛出异常,那就是静态表达式 下面是在静态表达式中允许出现的运算符
() ** * / + - = != < <= > >= IS [NOT] NULL NOT [NOT] LIKE [NOT] LIKE2 [NOT] LIKE4 [NOT] LIKEC XOR
下面是允许出现在静态表达式中的函数
ABS ACOS ASCII ASCIISTR ASIN ATAN ATAN2 BITAND CEIL
CHR COMPOSE CONVERT COS COSH DECOMPOSE EXP FLOOR HEXTORAW
INSTR INSTRB INSTRC INSTR4 IS [NOT] INFINITE IS [NOT] NAN
LENGTH LENGTH2 LENGTH4 LENGTHB LENGTHC LN LOG LOWER LPAD
LTRIM MOD NVL POWER RAWTOHEX REM REMAINDER REPLACE ROUND
RPAD RTRIM SIGN SIN SINH SQRT SUBSTR SUBSTR2 SUBSTR4
SUBSTRB SUBSTRC TAN TANH TO_BINARY_DOUBLE TO_BINARY_FLOAT
TO_CHAR TO_NUMBER TRIM TRUNC UPPPER
静态表达式可以用在如下的子类型声明中:
-
字符类型的长度(VARCHAR2, NCHAR, CHAR, NVARCHAR2, RAW和ANSI等价物)
-
NUMBER类型及其子类型(例如FLOAT)的规模(scale)和精度(precision)
-
内部类型精度(例如year, month, second)
-
时间和时间戳精度
-
VARRAY绑定(bound)
-
类型声明中的范围绑定(bounds of ranges) 在每一种情况中,静态表达式的结果的类型,都必须与声明的子类型一致,范围也要一致
PLS_INTEGER静态表达式
PLS_INTEGER静态表达式是: -
PLS_INTEGER字面量
-
PLS_INTEGER静态常量
-
NULL BOOLEAN静态表达式
BOOLEAN静态表达式是: -
BOOLEAN字面量(TRUE, FALSE或NULL)
-
BOOLEAN静态常量
-
PLS_INTEGER静态表达式(例如x和y)组成的比较表达式
x > y x < y x >= y x <= y x = y x <> y -
BOOLEAN表达式(例如x和y)组成的比较表达式
NOT y x AND y x OR y x > y x >= y x = y x <= y x <> y -
静态表达式(例如x)组成的比较表达式
x IS NULL x IS NOT NULL
VARCHAR2静态表达式
VARCHAR2静态表达式是:
- 最大32767字节的字符串字面量
- NULL
- TO_CHAR(x), x是PLS_INTEGER静态表达式
- TO_CHAR(x,f,n), x是PLS_INTEGER静态表达式,f和n是VARCHAR2静态表达式
- x || y, x和y是VARCHAR2或PLS_INTEGER静态表达式
静态常量
静态常量在包头中声明,语法如下:
常量名 CONSTANT 数据类型 := 静态表达式;
静态表达式的类型必须与数据类型一致(要么是BOOLEAN要么是PLS_INTEGER)
静态常量必须总是使用 包名.常量名 来引用,即使是在包体内
2.8 错误报告函数
PL/SQL有两个错误报告函数: SQLCODE 和 SQLERRM 可以用在PL/SQL异常处理代码中
你不能在SQL语句中使用它们
2.9 有条件编译
条件编译让你可以不需要移除源码就可以定制PL/SQL应用程序的功能
例如: 在最新版本的数据库中使用新的特性,而在旧版本的数据库中禁用它们
在开发环境中激活debug或跟踪语句,而在生产环境中禁用它们
2.9.1 条件编译是如何工作的
条件编译使用选择指令(类似IF语句)来选择源码进行编译
选择指令中的条件通常包括一个查询指令,而错误指令抛出一个用户自定义错误.
所有的条件编译指令都构造自预处理器控制令牌和PL/SQL文本
预处理器控制令牌(Preprocessor Control Tokens)
预处理器控制令牌用于标识出在PL/SQL程序单元编译之前就被处理的那些代码
语法格式如下
$PL/SQL标识符
字符 $ 也可以在PL/SQL标识符中出现
如下这些预处理器控制令牌是保留的:
$IF $THEN $ELSE $ELSIF $ERROR
选择指令
选择指令用于选择代码进行编译
语法格式如下:
$IF 布尔静态表达式 $THEN
text
[$ELSIF 布尔静态表达式 $THEN
text
]...
[$ELSE
text
$END
]
text可以是任何代码,但通常它要么是一个语句,要么是一个错误指令
错误指令
错误指令用于在编译期间产生一个用户自定义错误
语法格式如下
$ERROR VARCHAR2静态表达式 $END
它会在编译时产生一个错误信息:
PLS-00179: $ERROR: string
查询指令
查询指令提供编译环境的信息
语法格式如下(name是一个普通PL/SQL标识符):
$$name
查询指令通常出现在一个选择指令的布尔静态表达式里
预定义的查询指令
有如下这些预定义的查询指令:
-
$$PLSQL_LINE
用一个PLS_INTEGER值来表示一个指令出现在PL/SQL程序单元中的行数,例如$IF $$PLSQL_LINE = 32 $THEN ... -
$$PLSQL_UNIT
用一个VARCHAR2值表示当前所在PL/SQL程序单元的名称,如果PL/SQL程序单元
是匿名块,那么$$PLSQL_UNIT将会是空值NULL -
$$PLSQL_UNIT_OWNER
用一个VARCHAR2值表示当前所在PL/SQL程序单元的拥有者,如果PL/SQL程序单元
是匿名块,那么$$PLSQL_UNIT_OWNER将会是空值NULL -
$$PLSQL_UNIT_TYPE
用一个VARCHAR2值表示当前所在PL/SQL程序单元的类型
有如下类型:ANONYMOUS BLOCK FUNCTION PACKAGE PACKAGE BODY PROCEDURE TRIGGER TYPE TYPE BODY当在一个匿名块或非DML触发器里时,$$PLSQL_UNIT_TYPE将会是ANONYMOUS BLOCK
-
$$plsql_compilation_parameter
plsql_compilation_parameter是一个PL/SQL编译参数,例如PLSCOPE_SETTINGS
因为选择指令需要一个布尔静态表达式,你 不能 使用$$PLSQL_UNIT,$$PLSQL_UNIT_OWNER
或 $$PLSQL_UNIT_TYPE 在一个VARCHAR2比较中,例如:-
$IF $$PLSQL_UNIT = 'AWARD_BONUS' $THEN ... -
$IF $$PLSQL_UNIT_OWNER IS HR $THEN ... -
$IF $$PLSQL_UNIT_TYPE IS FUNCTION $THEN ...
然而,你可以将它们与NULL进行比较.例如: -
$IF $$PLSQL_UNIT IS NULL $THEN ... -
$IF $$PLSQL_UNIT_OWNER IS NOT NULL $THEN ... -
$IF $$PLSQL_UNIT_TYPE IS NULL $THEN ...
-
给查询指令赋值
你可以使用PLSQL_CCFLAGS编译参数来给查询指令赋值,例如
ALTER SESSION SET PLSQL_CCFLAGS = 'name1:value1, name2:value2, ... namen:valuen'
每个value必须要么是BOOLEAN字面量(TRUE,FALSE或NULL),要么是PLS_INTEGER字面量
同样的name可以出现多次,而后面的赋值会覆盖前面的赋值
Oracle推荐使用PLSQL_CCFLAGS来设置预定义的查询指令,推荐使用ALTER SESSION语句
不可解析的查询指令
如果源码文本没有被包裹,PL/SQL会提出一个警告(查询指令没有正确结束)
如果一个查询指令(例如$$name)不能被解析,并且源码文本没有被包裹,PL/SQL会抛出一个
警告PLW-6003,且给给查询指令赋一个NULL值
DBMS_DB_VERSION包
DBMS_DB_VERSION包可以提供Oracle版本号和其他一些有用信息,包括简单的条件编译选择指令
DBMS_DB_VERSION包提供下面这些静态常量
- PLS_INTEGER常量VERSION标识了当前Oracle数据库版本
- PLS_INTEGER常量RELEASE标识了当前Oracle数据库发布号
- 每个布尔常量(形如 VER_LE_v),如果是TRUE,表示数据库版本小于等于v,否则是FALSE
- 每个布尔常量(形如 VER_LE_v_r),如果是TRUE,表示数据库版本小于等于v,发布号小于等于r,否则是FALSE
2.9.2 条件编译的例子
检查数据库版本
BEGIN
$IF DBMS_DB_VERSION.VER_LE_10_1 $THEN -- 选择指令的开始
$ERROR 'unsupported database release' $END -- 错误指令
$ELSE
DBMS_OUTPUT.PUT_LINE(
'Release ' || DBMS_DB_VERSION.VERSION || '.' ||
DBMS_DB_VERSION.RELEASE || ' is supported'
)
-- 这个COMMIT语法是10.2版本才开始支持的
COMMIT WRITE IMMEDIATE NOWAIT;
$END -- 选择指令的结束
END
/
为不同版本的数据库编译不同的代码
ALTER SESSION SET PLSQL_CCFLAGS = 'my_debug:FALSE, my_tracing:FALSE';
CREATE OR REPLACE PACKAGE my_pkg AUTHID DEFINER AS
SUBTYPE my_real IS
$IF DBMS_DB_VERSION.VERSION < 10 $THEN
NUMBER;
$ELSE
BINARY_DOUBLE -- 只有10g或更高版本才能支持这个类型
$END
my_pi my_real;
my_e my_real;
END my_pkg;
/
CREATE OR REPLACE PACKAGE BODY my_pkg AS
BEGIN
$IF DBMS_DB_VERSION.VERSION < 10 $THEN
my_pi := 3.14159265358979323846264338327950288420;
my_e := 2.71828182845904523536028747135266249775;
$ELSE
my_pi := 3.14159265358979323846264338327950288420d;
my_e := 2.71828182845904523536028747135266249775d;
$END
END my_pkg;
/
CREATE OR REPLACE PROCEDURE circle_area(radius my_pkg.my_real) AUTHID DEFINER
IS
my_area my_pkg.my_real;
my_data_type VARCHAR2(30);
BEGIN
my_area := my_pkg.my_pi * (radius ** 2);
DBMS_OUTPUT.PUT_LINE(
'Radius: ' || TO_CHAR(radius) || 'Area: ' || TO_CHAR(my_area)
);
$IF $$my_debug $THEN
SELECT data_type INTO my_data_type
FROM user_arguments
WHERE object_name = 'CIRCLE_AREA'
AND argument_name = 'RADIUS';
DBMS_OUTPUT.PUT_LINE(
'Data type of the RADIUS argument is: ' || my_data_type
);
$END
END;
/
CALL DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE(
'PACKAGE', 'HR', 'MY_PKG'
);
输出结果如下
PACKAGE my_pkg AUTHID DEFINER AS
SUBTYPE my_real IS
BINARY_DOUBLE;
my_pi my_real;
m_e my_real;
END my_pkg;
2.9.3 获取和打印处理后的(Post-Processed)源码文本
DBMS_PREPROCESSOR包中的子程序可以获取和打印处理后的PL/SQL程序单元源码
2.9.4 条件编译指令的限制
条件编译指令收到一些语义限制.
条件编译指令不能出现在模式级(schema-level)用户自定义类型的定义中,在类型的定义中
描述了类型的属性结构
如果在类型定义中使用了条件编译指令,编译器会报错
PLS-00180: preprocessor directives are not supported in this context
