第二章 PL/SQL基础(2)

四、PL/SQL命名规范

同样的命名规约适用于所有的PL/SQL程序，规约涉及的内容包括常量、变量、游标、异常、过程、函数和包。命名可能是简单的，加以限定的，远程的或是既加以限定又是远程的。例如，我们也许可能用到以下几种调用过程raise_salary的方式：

raise_salary(...);   -- simple emp_actions.raise_salary(...);   -- qualified raise_salary@newyork(...);   -- remote emp_actions.raise_salary@newyork(...);   -- qualified and remote

第一种情况，我们只是简单的使用程序名称。第二种情况，我们必须使用点标志(dot notation)来引用过程，因为它是保存在emp_actions包中的。第三种情况，使用远程访问指示符，就能引用数据库连接newyork，因为过程是存放在远程数据库的。第四中情况，我们在过程名称加上限定修饰词并引用数据库连接。

• 同义词

我们可以创建同义词来隐藏远程模式对象的位置，其中包括表、视图、序列、存储函数、包、和对象类型。但是，我们不能为子程序或包中声明的内容创建同义词，其中包括常量、变量、游标变量、异常和打包子程序。

• 作用域

同一作用域内声明的标识符都必须是唯一的。所以，即使它们的数据类型不同，变量和参数也不能享用同一名称。下例中，第二个声明是不允许的：

valid_id   BOOLEAN; valid_id   VARCHAR2 (5);   -- not allowed duplicate identifier

• 大小写敏感

像所有的标识符一样，常量、变量和参数的名称都是大小写不敏感的。例如，PL/SQL认为下面的名称都是相同的：

zip_code   INTEGER; zip_code   INTEGER;   -- same as zip_code

• 命名解析

在SQL语句中，数据库字段名称的优先级要高于本地变量和形式参数。例如，下面的DELETE语句会从emp表删除所有的雇员信息，而不只是名字为"KING"的雇员：

DECLARE   ename   VARCHAR2 (10) := 'KING'; BEGIN   DELETE FROM emp         WHERE ename = ename;   ...

在这种情况下，为了避免产生歧义，可以像下面这样在本地变量和形式参数的前面加上类似于"my_"这样的前缀：

DECLARE   my_ename VARCHAR2(10);

或是使用块标签来进行引用限定：

<<main>> DECLARE   ename   VARCHAR2 (10) := 'KING'; BEGIN   DELETE FROM emp         WHERE ename = main.ename;   ...

下面的例子演示了如何使用子程序名称来限定对本地变量和形式参数的引用：

FUNCTION bonus (deptno IN NUMBER, ...) RETURN REAL IS   job CHAR(10); BEGIN   SELECT ... WHERE deptno = bonus.deptno AND job = bonus.job;   ...

五、PL/SQL标识符的作用域(scope)和可见度(visiblity)

对标识符的引用可以通过它的作用域和可见度来进行解析。标识符的作用域就是我们引用标识符的程序单元区域(块，子程序或包)。一个标识符只在它的作用域内可见，我们可以在作用域内不使用限定词而直接引用它。下图演示了变量x的作用域和可见度。x首先被声明在封闭块中，然后又在子块中重新定义。

PL/SQL块中声明的标识符对于其所在块来说是本地的，对于子块来说是全局的。如果全局标识符在子块中被重新声明，那么，全局和本地声明的标识符在子块的作用域都是存在的，但是，只有本地标识符是可见的，这时如果想引用全局标识符，就需要添加限定修饰词。

虽然我们不能在同一块中两次声明同一标识符，但可以在两个不同的块中声明同一标识符。这两个标识符是互相独立的，对其中任何一个的改变都不会影响到另一个。但是，一个块不能引用同一级别中另外一个块中的变量，因为对于它来说，同级块中标识符即不是本地的，又不是全局的。

下面的例子演示了作用域规则：

DECLARE   a   CHAR;   b   REAL; BEGIN   -- identifiers available here: a (CHAR), b   DECLARE     a   INTEGER;     c   REAL;   BEGIN     -- identifiers available here: a (INTEGER), b, c   END;   DECLARE     d   REAL;   BEGIN     -- identifiers available here: a (CHAR), b, d   END;   -- identifiers available here: a (CHAR), b END;

如果子块中重新声明了全局标识符，本地标识符优先权高于全局标识符，我们就不能再引用全局标识符，除非使用限定名(qualified name)。修饰词可以是封闭块的标签，如下例所示：

<<outer>> DECLARE   birtddate   DATE; BEGIN   DECLARE     birtddate   DATE;   BEGIN    ...     IF birtddate = OUTER.birtddate tdEN       ...     END IF;     ...   END;   ... END;

如下例所示，限定修饰词也可以是封闭子程序的名称：

PROCEDURE check_credit(...) IS   rating   NUMBER;   FUNCTION valid(...)     RETURN BOOLEAN IS     rating   NUMBER;   BEGIN     ...     IF check_credit.rating < 3 tdEN ...   END; BEGIN   ... END;

但是，在同一作用域内，标签和子程序不能使用相同的命名。

六、变量赋值

变量和常量都是在程序进入块或子程序的时候被初始化的。默认情况下，变量都是被初始化成NULL的。除非我们为变量指定一个值，否则结果是未知的。请看下面的例子：

DECLARE   count INTEGER; BEGIN   -- COUNT began witd a value of NULL.   -- tdus tde expression ’COUNT + 1’ is also null.   -- So after tdis assignment, COUNT is still NULL.   count := count + 1;

为了避免这样的情况，就要保证在赋值之前不要使用这个变量。

我们可以使用表达式来为变量赋值，例如下面的语句为变量bonus赋值：

bonus := salary * 0.15;

这里，我们需要保证的是salary * 0.15计算结果的类型必须和bonus类型保持一致。

1、布尔型(Boolean)赋值

只有TRUE、FALSE和NULL才可以赋给布尔类型的变量。例如：

BEGIN   done := FALSE;   WHILE NOT done LOOP     ...   END LOOP;

当表达式中使用关系操作符的时候，返回结果也是布尔类型的值，所以下面的语句也是允许的。

done := (count > 500);

2、利用SQL查询为PL/SQL变量赋值

我们可以使用SELECT语句让Oracle为变量赋值。对于查询字段中的每一项，在INTO子句的后面都必须有与之对应的类型兼容的变量。看一下下面这个例子：

DECLARE   emp_id     emp.empno%TYPE;   emp_name   emp.ename%TYPE;   wages      NUMBER(7,2); BEGIN   -- assign a value to emp_id here   SELECT   ename, sal + comm INTO emp_name, wages     FROM   emp    WHERE   empno = emp_id;   ... END;

但是，上面的用法不可以为布尔类型变量赋值。

七、PL/SQL表达式与比较

表达式由操作数和操作符构成。一个操作数就是一个变量、常量、文字或是能够返回一个值的函数。下面是一个简单的数学表达式：

-X / 2 + 3

像负号(-)这样的只作用于一个操作数的操作符称为一元操作符；而像除号(/)这样作用于两个操作数的操作符称为二元操作符。PL/SQL没有三元操作符。

最简单的表达式就是一个能直接算出值的变量。PL/SQL按照指定的操作符和操作数来计算表达式的值，结果值的数据类型是由表达式所在的关联文决定的。

由于操作符的运算优先级不同，表达式的计算顺序也是不一样的。下表是默认的操作符优先级顺序。

操作符	运算
**	求幂
+, -	正，负
*, /	乘，除
+, -, ||	加，减，连接
=, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN	比较
NOT	逻辑非
AND	与
OR	或

优先级高的操作符会比优先级低的操作符先求值。下例中，两个表达式都能计算出结果8来，因为除号的优先级要高于加号。优先级相同的操作符不会采取特殊的计算顺序。

5 + 12 / 4 12 / 4 + 5

我们可以使用括号控制计算顺序。例如，下面的表达式值是7，而不是11，因为括号覆盖了默认的操作符优先顺序：

(8 + 6) / 2

再看一个例子。下面的运算中，减法会在除法之前被计算，这是因为最深层的表达式总是第一个被计算的：

100 + (20 / 5 + (7 - 3))

最后，我们看看如何使用括号来改善可读性，即使不是在必须使用括号的时候：

(salary * 0.05) + (commission * 0.25)

1、逻辑操作符

逻辑操作符有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

如上面的真值表所示，AND只在操作符两边的操作数都是真的情况才返回TRUE。另一方面，OR操作符两边的操作数只要有一个值为真就能返回TRUE。NOT会返回操作数相反的值。例如NOT TRUE返回FALSE。

这里需要注意的地方是，由于NULL是一个不确定的值，所以NOT NULL的值也是无法确定的。

• 运算顺序

当我们不用括号指定计算顺序的时候，操作符的优先级就会决定操作数的计算顺序。比较下面两个表达式：

NOT (valid AND done)

NOT valid AND done

如果布尔变量valid和done的值都是FALSE，那么第一个表达式的结果就为TRUE。但是，第二个表达式的结果却是FALSE，因为NOT的优先级要比AND高。因此，第二个表达式就等价于：

(NOT valid) AND done

在下面的例子中，当valid的值为FALSE，不论done值是多少，整个表达式的值总为FALSE：

valid AND done

同样，当下例中的valid的值为TRUE时，不论done值是多少，整个表达式的值总为TRUE：

valid OR done

• 短路计算

在计算逻辑表达式时，PL/SQL使用的是短路计算方法。也就是说，PL/SQL在结果可以确定下来的时候，就不会再继续计算表达式的值了。看一下下面这个例子：

DECLARE   ...   on_hand    INTEGER;   on_order   INTEGER; BEGIN   ...   IF (on_hand = 0) OR ((on_order / on_hand) < 5) tdEN     ...   END IF; END;

当on_hand的值是零的时候，操作符OR左面的操作数结果为TRUE，所以PL/SQL就不需要计算右边的值了。如果PL/SQL是在应用OR操作符之前计算两个操作数的值的话，那么右边的操作数就会产生一个除零的错误。不管怎样，依赖于"短路"计算不是一个好习惯。

• 比较操作符

比较操作符用于将一个表达式与另一个表达式进行比较。结果是TRUE或FALSE或NULL。最常见的就是我们在条件控制语句和SQL数据操作语句中的WHERE子句中使用比较操作符。例如：

IF quantity_on_hand > 0 tdEN   UPDATE inventory      SET quantity = quantity - 1    WHERE part_number = item_number; ELSE   ... END IF;

• 关系操作符

关系操作符可以让我们随意比较复杂的表达式。下面的表格列出了各种关系操作符的含义。

操作符	含义
=	等于
<>, !=, ~=, ^=	不等于
<	小于
>	大于
<=	小于等于
>=	大于等于

• IS NULL 操作符

如果IS NULL所作用的操作数为空，则返回结果TRUE，否则返回结果FALSE。与空值作比较，结果总是空。所以，无论什么时候跟空值作比较，都要使用IS NULL操作符：

IF variable IS NULL tdEN ...

• LIKE操作符

我们可以使用LIKE操作符来判断一个字符、字符串或CLOB类型的值是不是与我们指定的样式相匹配。如果样式匹配，LIKE就会返回TRUE，否则返回FALSE。用于LIKE匹配的样式中，包含两种通配符。下划线(_)：精确匹配一个字符；百分号(%)：匹配零个或多个字符。如下面的例子中，如果ename的值是"JOHNSON"，那么表达式结果就为TRUE：

ename LIKE 'J%SON'

• BETWEEN操作符

BETWEEN操作符用于判断目标值是否在指定的目标范围内。例如，下面表达式的结果就为FALSE：

45 BETWEEN 38 AND 44

• IN操作符

IN操作符是用于测试目标值是否是集合成员之一。其中，集合是可以包含NULL值的，但它们是被忽略的。例如，下面这个语句并不会删除ename值为NULL的行：

DELETE FROM emp       WHERE ename IN (NULL, 'KING', 'FORD');

此外，如果集合中包含了NULL值，下面表达式的运算结果就是FALSE。

value NOT IN set

所以，下面这个表达式也不会删除任何行：

DELETE FROM emp       WHERE ename NOT IN (NULL, 'king');

• 连接操作符

双竖线(||)可以当作字符连接操作符，可以将两个字符串(CHAR、VARCHAR2、CLOB或等价的Unicode支持的类型)连接起来。例如表达式

'suit' || 'case'

返回的结果就是

'suitcase'

如果操作符两边的操作数都是CHAR类型，连接操作符返回的结果就是CHAR值。如果其中一个是CLOB值，操作符就返回临时CLOB。其余情况均返回VARCHAR2类型。

2、布尔表达式

PL/SQL允许我们在SQL语句和过程语句中比较变量和常量。这样的比较称为布尔表达式，它们是由用关系操作符分割开的简单或复杂表达式组成。通常，布尔表达式是由逻辑操作符AND、OR或NOT连接。布尔表达式的运算结果总是TRUE、FALSE或NULL。

在SQL语句中，布尔表达式能让我们指定一个表中哪些行记录可以被影响。在过程语句中，布尔表达式是条件控制的基础。其中有三种布尔表达式：算术、字符和日期。

• 布尔算术表达式

我们可以使用关系表达式来比较两个数字等或不等。例如，下面的表达式结果就为真：

number1    := 75; number2    := 70; number1 > number2   -- TRUE

• 布尔字符表达式

我们也可以比较字符的等或不等。默认情况下，比较都是基于字符串中每个字节的二进制值的。比如，下面例子中的表达式结果就为真：

string1    := 'Katdy'; string2    := 'Katdleen'; string1 > string2   -- TRUE

设置初始化参数NLS_COMP=ANSI，就能使用初始化参数NLS_SORT指定的整理序列(collating sequence)来进行比较。整理序列是一个字符集中表现字符的数字代码(numeric code)的内部顺序，如果一个字符的数字代码比另一个大，那这个字符就比另一个字符大。关于字符在整理序列中出现的位置，每种语言都可能有不同的定义规则。比如说，重音字母可能会因数据库的字符集的不同而排序不同，即使每一种情况下的二进制值都相同。

• 布尔日期表达式

对于日期类型的比较，是按照年代的顺序的。如下例，date1的值是大于date2的值的。

date1    := '01-JAN-91'; date2    := '31-DEC-90'; date1 > date2   -- TRUE

• 关于PL/SQL的布尔表达式使用的一些建议

一般地，不要把实型数字用于精确比较。实型数字一般都是按近似值存储的。所以，下面的表式式值并不等于TRUE：

COUNT    := 1; IF COUNT = 1.0 tdEN   ... END IF;

在作比较时使用括号是一个好习惯。例如，下面的这样的表达式形式是不允许的，因为 100 < tax 的结果是布尔型，而布尔型是不能和数字500进行比较的。

100 < tax < 500   -- not allowed

解决方法是使用下面这样的表达式：

(100 < tax) AND (tax < 500)

对于布尔型的变量来说，它的值要么为TRUE要么为FALSE，因此，对布尔型变量应用比较操作是多余的。对于下面的内容：

WHILE NOT(done = TRUE) LOOP   ... END LOOP;

可以简化为：

WHILE NOT done LOOP   ... END LOOP;

对COLB类型应用比较操作符或是用LIKE和BETWEEN这样的函数时，可能会产生临时LOB。我们就得确保有足够大的表空间来容纳这些临时LOB。

3、CASE表达式

一个CASE表达式从一个或多个供选方案中选择一个返回结果。CASE表达式使用一个选择器来决定返回哪一个分支的结果。具体的语法形式如下：

CASE selector   WHEN expression1 tdEN result1   WHEN expression2 tdEN result2   ...   WHEN expressionn tdEN resultn   [ELSE resultN+1] END;

选择器后面跟着一个或多个WHEN子句，它们会被依次验证的。一旦有一个WHEN子句满足条件的话，剩下的分支条件就不再执行了。例如：

DECLARE   grade       CHAR(1)      := 'B';   appraisal   VARCHAR2(20); BEGIN   appraisal    := CASE grade                    WHEN 'A' tdEN 'Excellent'                    WHEN 'B' tdEN 'Very Good'                    WHEN 'C' tdEN 'Good'                    WHEN 'D' tdEN 'Fair'                    WHEN 'F' tdEN 'Poor'                    ELSE 'No such grade'                  END; END;

其中，ELSE子句是可选的，工作方式同IF语句中的ELSE子句相似。如果我们不提供ELSE子句，并且选择器没有匹配任何WHEN子句，表达式的返回的结果就是NULL。

这种形式的CASE表达式的另外一种使用方法就是CASE语句，其中每个WHEN子句都可以是一个完整的PL/SQL块。

• 搜索式CASE表达式

PL/SQL也提供了搜索式的CASE表达式，它的语法形式如下：

CASE   WHEN expression1 tdEN result1   WHEN expression2 tdEN result2   ...   WHEN expressionn tdEN resultn   [ELSE resultN+1] END;

搜索式CASE表达式没有选择器。每个WHEN子句包含一个能返回布尔值的搜索条件。例子如下：

DECLARE   grade       CHAR(1);   appraisal   VARCHAR2(20); BEGIN   ...   appraisal    := CASE                    WHEN grade = 'A' tdEN 'Excellent'                    WHEN grade = 'B' tdEN 'Very Good'                    WHEN grade = 'C' tdEN 'Good'                    WHEN grade = 'D' tdEN 'Fair'                    WHEN grade = 'F' tdEN 'Poor'                    ELSE 'No such grade'                  END;   ... END;

搜索条件按顺序计算。搜索条件的布尔值决定了哪个WHEN子句被执行。如果搜索条件的值为TRUE，它对应的WHEN子句就会被执行。只要其中一个 WHEN子句被执行，后续的搜索条件就不会被计算了。如果没有匹配的条件，可选的ELSE就会被执行。如果没有匹配的WHEN子句，也没有ELSE子句，表达式的结果就为NULL。

4、在比较和条件语句中处理NULL值

在使用NULL值时，我们一定要记住下面几条规则，避免发生一些常见的错误：

1. 比较中如果有空值的话，那么计算结果总为NULL
2. 对空值应用逻辑操作符NOT，结果还是NULL
3. 条件控制语句中，如果条件的运算结果值为NULL的话，与之相关的语句就不会被执行
4. 简单CASE语句中对于空值的判断要使用WHEN expression IS NULL

下例中，我们期待的是sequence_of_statements被执行，因为x和y看起来就是不等的。但是，由于NULL是不确定的值，那么，x是否等于y也就无法确定了。所以，sequence_of_statements并不会执行。

x    := 5; y    := NULL; ... IF x != y tdEN   -- yields NULL, not TRUE   sequence_of_statements; -- not executed END IF;

同样，下例中的sequence_of_statements也不会被执行：

a    := NULL; b    := NULL; ... IF a = b tdEN   -- yields NULL, not TRUE   sequence_of_statements; -- not executed END IF;

• NOT操作符

让我们再回忆一下逻辑操作符NOT，当对一个NULL值应用NOT时，结果总是NULL。因此，下面两段内容并不相同。

IF x > y tdEN   high    := x; ELSE   high    := y; END IF;

IF NOT x > y tdEN   high    := y; ELSE   high    := x; END IF;

当IF条件值为FALSE或NULL时，ELSE部分就会被执行。如果x和y都不为NULL的话，两段程序运行的效果是一样的。但是，如果IF条件为NULL的话，第一段是给y赋值，而第二段是给x赋值。

• 零长度字符串

PL/SQL把零长度字符串当作空值处理，这其中包括由字符函数和布尔表达式返回的值。下面的语句均是给目标变量赋空值的操作：

null_string    := TO_CHAR(''); zip_code       := SUBSTR(address, 25, 0); valid          :=(NAME != '');

所以，对于检测空字符串，要使用IS NULL操作符：

IF my_string IS NULL tdEN ...

• 连接操作符

连接操作符会忽略空值，例如表达式

'apple' || NULL || NULL || 'sauce'

会返回

'applesauce'

• 函数

如果给内置函数传递空值，一般也都会返回空值，但以下几种情况除外。

函数DECODE将它的第一个参数和后面的一个或多个表达式相比较(表达式的值有可能为空)，如果比较的内容相匹配，就会返回后面的结果表达式。例如在下面的例子中，如果字段rating的值为空，DECODE就会返回1000：

SELECT DECODE(rating,               NULL, 1000,               'C', 2000,               'B', 4000,               'A', 5000              )   INTO credit_limit   FROM accts  WHERE acctno = my_acctno;

函数NVL在判断出第一个参数是空的情况下，会返回第二个参数的值，否则直接返回第一个参数的值。使用方法如下：

start_date := NVL(hire_date, SYSDATE);

函数REPLACE第二个参数是NULL的时候，它就会返回第一个参数的值，不管是否有第三个参数。例如，在下面例子中，结果字符串new_string的值和old_string的值完全一样。

new_string := REPLACE(old_string, NULL, my_string);

如果第三个参数为空的话，REPLACE就会把第一个参数中出现的第二个参数删除，然后返回结果。如下面这个例子：

syllabified_name    := 'gold - i - locks';   NAME                := REPLACE(syllabified_name,                                  ' - ',                                  NULL                                 );

运算的结果字符串是"goldilocks"。如果第二个和第三个参数都是NULL值，REPLACE就直接返回第一个参数。

八、内置函数

PL/SQL为我们提供了许多功能强大的数据操作函数。这些函数可以分为以下几类：

1. 错误报告
2. 数字
3. 字符
4. 类型转换
5. 日期
6. 对象引用
7. 杂项

下面的表格是各个分类的函数。

错误	数字	字符	转换	日期	对象引用	杂项
SQLCODE	ABS	ASCII	CHARTOROWID	ADD_MONtdS	DEREF	BFILENAME
SQLERRM	ACOS	CHR	CONVERT	CURRENT_DATE	REF	DECODE
	ASIN	CONCAT	HEXTORAW	CURRENT_TIMESTAMP	VALUE	DUMP
	ATAN	INITCAP	RAWTOHEX	DBTIMEZONE	TREAT	EMPTY_BLOB
	ATAN2	INSTR	ROWIDTOCHAR	EXTRACT		EMPTY_CLOB
	BITAND	INSTRB	TO_BLOB	FROM_TZ		GREATEST
	CEIL	LENGtd	TO_CHAR	LAST_DAY		LEAST
	COS	LENGtdB	TO_CLOB	LOCALTIMESTAMP		NLS_CHARSET_DECL_LEN
	COSH	LOWER	TO_DATE	MONtdS_BETWEEN		NLS_CHARSET_ID
	EXP	LPAD	TO_MULTI_BYTE	NEW_TIME		NLS_CHARSET_NAME
	FLOOR	LTRIM	TO_NCLOB	NEXT_DAY		NVL
	LN	NLS_INITCAP	TO_NUMBER	NUMTODSINTERVAL		SYS_CONTEXT
	LOG	NLS_LOWER	TO_SINGLE_BYTE	NUMTOYMINTERVAL		SYS_GUID
	MOD	NLSSORT		ROUND		UID
	POWER	NLS_UPPER		SESSIONTIMEZONE		USER
	ROUND	REPLACE		SYSDATE		USERENV
	SIGN	RPAD		SYSTIMESTAMP		VSIZE
	SIN	RTRIM		TO_DSINTERVAL
	SINH	SOUNDEX		TO_TIMESTAMP
	SQRT	SUBSTR		TO_TIMESTAMP_LTZ
	TAN	SUBSTRB		TO_TIMESTAMP_TZ
	TANH	TRANSLATE		TO_YMINTERVAL
	TRUNC	TRIM		TZ_OFFSET
		UPPER		TRUNC

除了错误报告(error-reporting)函数SQLCODE和SQLERRM之外，我们可以在SQL语句中使用上面所有的函数。同样，我们可以在过程表达式中使用除了对象引用函数DEFREF、REF、VALUE和杂函数(miscellaneous function)DECODE、DUMP、VSIZE之外的所有函数。

虽然SQL聚合函数(aggregate function，如AVG和COUNT)和SQL解析函数(analytic function，如CORR和LAG)没有组合到PL/SQL中，但我们仍可以在SQL语句中使用它们(但不能在过程语句中使用)。