第二章 PL/SQL基础

第二章 PL/SQL基础

我们可以使用标识符来为PL/SQL程序中的常量、变量、异常、游标、游标变量、子程序和包命名。

标识符可以由字母、数字、美元符号($)、下划线(_)和数字符号(#)组成。而像连字符(-)、斜线(/)等符号都是不允许使用的。标识符的长度不能超过30如下例:

mine&yours -- 不允许使用连字符(not allowed because of ampersand)

debit-amount -- 不允许使用连字符(not allowed because of hyphen)

on/off -- 不允许使用斜线(not allowed because of slash)

user id -- 不允许使用空格(not allowed because of space)

而使用美元符号、下划线和数字符号都是允许的：

money$$$tree

SN##

try_again_

如果字符文字的范围不在1E-130到10E125之间，就会产生编译错误：

DECLARE
  n NUMBER;
BEGIN
  n := 10E127;   -- causes a 'numeric overflow or underflow' error

 

我们可以使用关键字DEFAULT来替换赋值操作符为变量初始化。下面这个声明

blood_type   CHAR := 'o';

就可以用DEFAULT来替换：

blood_type   CHAR DEFAULT 'o';

我们可以使用DEFAULT来初始化子程序参数、游标参数和用户定义的记录中的域。

 

除了在声明中做初始化操作外，还可以使用NOT NULL进行约束：

acct_id INTEGER(4) NOT NULL := 9999; 

这样一来，我们就不能为变量acct_id指派空值了。如果这样做的话，PL/SQL就会抛出预定义异常VALUE_ERROR。NOT NULL约束后面必须跟着初始化子句。像下面这样的声明是不允许的：

acct_id INTEGER(5) NOT NULL;   -- not allowed; not initialized 

NATURALN和POSITIVEN是PL/SQL提供的两个不可为空的预定义子数据类型。下面这两个声明是等价的：

emp_count NATURAL NOT NULL := 0;

emp_count NATURALN         := 0; 

在NATURALN和POSITIVEN声明中，类型分类符后面必须跟上一个初始化子句。否则就会发生编译错误。例如，下面的声明就是不合法的：

line_items POSITIVEN;   -- not allowed; not initialized

 

对于同样数据类型的每一个变量，都必须单独声明：

i   SMALLINT;
j   SMALLINT;
k   SMALLINT;

像下面这样的声明方式是不允许的：

i, j, k   SMALLINT;   -- not allowed

 

命名解析

在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;

 

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

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

 

布尔字符表达式

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

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

 

布尔日期表达式

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

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

 

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块。

 

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

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

比较中如果有空值的话，那么计算结果总为NULL

对空值应用逻辑操作符NOT，结果还是NULL

条件控制语句中，如果条件的运算结果值为NULL的话，与之相关的语句就不会被执行

简单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; 

 

零长度字符串

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

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

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

IF my_string IS NULL tdEN ...

 

函数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就直接返回第一个参数。