Oracle PL/SQL Programming 第9章：Numbers 读书笔记

总的目录和进度，请参见开始读 Oracle PL/SQL Programming 第6版

本章谈3点：

1. 可使用的数字数据类型
2. 如何在数字和文本间转换
3. PL/SQL 内置数值函数

Numeric Datatypes

• NUMBER：平台无关的实现，适合处理货币金额
• PLS_INTEGER 和 BINARY_INTEGER：硬件机器指令执行，数据库中无法存储
• SIMPLE_INTEGER：同BINARY_INTEGER，除了不能存NULL，以及溢出时不会引发异常
• BINARY_FLOAT 和 BINARY_DOUBLE：单精度和双精度浮点，不适合存金额，但运算速度快
• SIMPLE_FLOAT 和 SIMPLE_DOUBLE：与 BINARY_FLOAT 和 BINARY_DOUBLE 具有相同的范围，但不允许 NULL，发生溢出则不引发异常

其他如 FLOAT, INTEGER, 和 DECIMAL只是别名而已。

The NUMBER Type

最常用，可以存整数、定点数和浮点数，平台无关的实现，也就是在任何平台计算结果都一样。

number(p, s) /* 定点数 */
number(p, 0) /* 整数 */
number /* 浮点数 */

文档中的描述为：

Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. Both precision and scale are in decimal digits. A NUMBER value requires from 1 to 22 bytes.

精度（p）是数字中有效数字的总数。 标度或小数位数（s）决定了小数点右侧（正数位数）或左侧（负数位数）的位数，并且还会影响舍入发生的点。 精度和标度都必须是字面整数值； 不能在声明中使用变量或常量。

标度通常小于精度，例如NUMBER(5, 2)的数字可能是999.99。

标度大于精度时，例如NUMBER(5, 7)。则形式为.0099999
5表示有5个有效数字（significant digits），从右往左数7，则确定了小数点位置。

以下代码是正确的：

set serveroutput on
declarea number(5,7) := 0.0099999;
beginDBMS_OUTPUT.PUT_LINE(a);
end;
/

但如果将0.0099999改为0.0999999或0.099999。则会报错：

Error report -
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 2
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint erroroccurred. For example, this error occurs if an attempt is made toassign the value NULL to a variable declared NOT NULL, or if anattempt is made to assign an integer larger than 99 to a variabledeclared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared sothat values do not violate constraints.

标度为负数时，则小数点是从最右边的有效数字再往右数。

例如NUMBER(5, -3)，则形式为99999000.，但赋值199999000则报与以上相同的错。

标度是可选地，默认值为0。

精度和标度与正负无关。例如NUMBER(3, 0)可表示-999到999。

The PLS_INTEGER Type

这是PL/SQL数据类型，不算数据库内置数据类型。范围-2,147,483,648 到2,147,483,647。

值使用硬件平台的本机整数格式表示，所以运算速度快。

当您使用 PLS_INTEGER 进行整数算术（以及循环计数器）时，如果可以避免与 NUMBER 类型之间的多次转换，您将获得最大的效率。 当此数据类型用于整数算术时，结果值将四舍五入为整数。例如50/100等于1，49/100则等于0。

The BINARY_INTEGER Type

等同于PLS_INTEGER，不建议使用，因和版本相关。

The SIMPLE_INTEGER Type

是PLS_INTEGER的子类型，有NOT NULL约束，并且在溢出语义上与PLS_INTEGER不同。理论上，如果可以保证没有NULL或溢出，则此类型会快于PLS_INTEGER。

看一下原文的例子，其比较了PLS_INTEGER和SIMPLE_INTEGER的运算速度：

/* File on web: simple_integer_demo.sql */
-- First create a compute-intensive procedure using PLS_INTEGER
CREATE OR REPLACE PROCEDURE pls_test (iterations IN PLS_INTEGER)
ASint1      PLS_INTEGER := 1;int2      PLS_INTEGER := 2;begints   timestamp;endts     timestamp;
BEGINbegints := SYSTIMESTAMP;FOR cnt IN 1 .. iterationsLOOPint1 := 0; /* 这句是我加的，否则会溢出 */int1 := int1 + int2 * cnt;END LOOP;endts := SYSTIMESTAMP;DBMS_OUTPUT.put_line(   iterations|| ' iterations had run time of:'|| TO_CHAR (endts - begints));
END;
/CREATE OR REPLACE PROCEDURE simple_test (iterations IN SIMPLE_INTEGER)
ASint1      SIMPLE_INTEGER := 1;int2      SIMPLE_INTEGER := 2;begints   timestamp;endts     timestamp;
BEGINbegints := SYSTIMESTAMP;FOR cnt IN 1 .. iterationsLOOPint1 := 0; /* 这句是我加的，否则会溢出 */int1 := int1 + int2 * cnt;END LOOP;endts := SYSTIMESTAMP;DBMS_OUTPUT.put_line(   iterations|| ' iterations had run time of:'|| TO_CHAR (endts - begints));
END;
/

测试如下：

BEGIN pls_test(123456789); END;
/
BEGIN simple_test(123456789); END;
/

从以上例子中可以学习如何计算运算时间。

另外PL/SQL有两种编译模式，INTERPRETED 和 NATIVE，默认为前者。

第二次运行时，使用NATIVE模式。

ALTER PROCEDURE pls_test COMPILE PLSQL_CODE_TYPE=INTERPRETED;
/ALTER PROCEDURE simple_test COMPILE PLSQL_CODE_TYPE=INTERPRETED
/

两次的运行结果（Windows宿主机上的Linux虚机）：

-- INTERPRETED 模式
123456789 iterations had run time of:+000000000 00:00:01.971535000
12345678 iterations had run time of:+000000000 00:00:00.397607000-- NATIVE 模式
123456789 iterations had run time of:+000000000 00:00:00.777152000
12345678 iterations had run time of:+000000000 00:00:00.113956000

结论为：

• NATIVE模式比INTERPRETED块
• SIMPLE_INTEGER比PLS_INTEGER快

The BINARY_FLOAT and BINARY_DOUBLE Types

遵从IEEE-754浮点数标准，数据库表定义和PL/SQL均支持。可以加后缀f或d表示。如：

0.9f
0.9d

使用 IEEE-754 浮点类型时还可以使用一些特殊文字。

PL/SQL 和 SQL 都支持的：

• BINARY_FLOAT_NAN、BINARY_DOUBLE_NAN表示“不是数字”。
• BINARY_FLOAT_INFINITY、BINARY_DOUBLE_INFINITY表示无穷大。

仅 PL/SQL 支持的：

• BINARY_FLOAT_MIN_NORMAL、BINARY_FLOAT_MAX_NORMAL定义您应该计划存储在变量中的值的正常范围。
• BINARY_FLOAT_MIN_SUBNORMAL、BINARY_FLOAT_MAX_SUBNORMAL
  定义所谓的次正常值范围。 次正常值是 IEEE-754 标准的一部分，旨在将下溢引起的问题减少到零。

与数据类型一起使用的谓词：

• IS NAN, IS NOT NAN 确定值是否为数字。
• IS INFINITE, IS NOT INFINITE 确定值是否表示无穷大。

特别注意，BINARY类型是二进制表示的，因此其不精确，不适合存放和钱相关的值。涉及到钱的，用NUMBER。
例如：

BEGINDBMS_OUTPUT.PUT_LINE(0.95f); -- BINARY_FLOATDBMS_OUTPUT.PUT_LINE(0.95d); -- BINARY_DOUBLEDBMS_OUTPUT.PUT_LINE(0.95);  -- NUMBEREND;

输出为：

9.49999988E-001
9.4999999999999996E-001
.95

此类型用于比较时，要特别注意。
例如0.95f和0.95d是不相等的，0.95d精度要高些。

而以下则是相等的：

declarea number := 0.95f - 0.95d;
BEGINIF ABS(a) < 0.000001dTHENDBMS_OUTPUT.PUT_LINE('TRUE');ELSEDBMS_OUTPUT.PUT_LINE('FALSE');END IF;
END;

原文没有定义变量a，而是将0.95f - 0.95d作为ABS的参数，但总是报错，不知为何：

Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint erroroccurred. For example, this error occurs if an attempt is made toassign the value NULL to a variable declared NOT NULL, or if anattempt is made to assign an integer larger than 99 to a variabledeclared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared sothat values do not violate constraints.

使用这些IEEE-754类型，原因可能是为要符合IEEE-754标准（不符合的部分请查看Oracle SQL参考手册），或为了运算速度。

看一下原文中测试的主体：

...-- Compute area 5,000,000 times using binary doublesbd_begin := SYSTIMESTAMP;bd := 1d;LOOPbd_area := bd * bd * pi_bd;bd := bd + 1d;EXIT WHEN bd > 5000000;END LOOP;bd_end := SYSTIMESTAMP;
...

我的结果为：

-- 算圆的面积，500万次
BINARY_DOUBLE area = +00 00:00:00.298879000
NUMBER        area = +00 00:00:01.094699000
-- 算Sin函数，500万次
BINARY_DOUBLE sine = +00 00:00:00.738123000
NUMBER        sine = +00 00:00:58.136234000

数据类型优先级
详见Data Type Precedence。

Oracle 使用数据类型优先级来确定隐式数据类型转换，Oracle 数据类型具有以下优先级（由高到低）：

• Datetime 和interval数据类型
• BINARY_DOUBLE
• BINARY_FLOAT
• NUMBER
• Character 数据类型
• 所有其他内置数据类型

如果使用隐式转换，0.95f+0.95d的类型是BINARY_DOUBLE。

如果不想隐式转换，则可以用TO_NUMBER, TO_BINARY_FLOAT或 TO_BINARY_DOUBLE.显式转换，诸如此类。

总之，Oracle不建议隐式转换。

Oracle recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions

因为：

• 使用显式数据类型转换函数时，SQL 语句更容易理解。
• 隐式数据类型转换可能会对性能产生负面影响。
• 隐式转换的结果取决于它发生的上下文。 例如，从datetime 到 VARCHAR2 值的隐式转换可能会返回意外的年份，具体取决于 NLS_DATE_FORMAT 设置。
• 隐式转换的算法可能会因软件版本和 Oracle 产品而异。 显式转换的行为更可预测。
• 如果索引表达式中发生隐式数据类型转换，则 Oracle 数据库可能不会使用该索引，因为它是为转换前的数据类型定义的。 这可能会对性能产生负面影响。

详见这里

The SIMPLE_FLOAT and SIMPLE_DOUBLE Types

BINARY_FLOAT 和 BINARY_DOUBLE 数据类型的性能增强版，但不支持 NULL 值、特殊 IEEE 文字（BINARY_FLOAT_NAN、BINARY_DOUBLE_INFINITY 等）或特殊 IEEE 谓词（IS NAN、IS INFINITY 等)。 他们也不检查溢出情况。

Numeric Subtypes

大多是基础类型的别名，为了支持ISO SQL, SQL/DS和 DB2 数据类型。例如INT，SMALLINT等。

NATURAL 和 POSITIVE 都是 PLS_INTEGER 的子类型。 这些子类型限制了可以存储在变量中的值，它们的使用可以使程序自我说明。

数字转换

Number Conversions

使用TO_CHAR 和 TO_NUMBER进行转换。

The TO_NUMBER Function

说明见这里。

调用格式为：

TO_NUMBER(string [,format [,nls_params]])

通常只需要指定第一个参数，例如：

• 字符串仅使用数字和一个小数点表示。
• 前导符合必须是减号 (–) 或加号 (+)。 如果不存在，则假定该数字为正数。
• 使用科学记数法

Using TO_NUMBER with a format model

格式详见这里。

例如：

select to_number('$1234', '$9999') from dual;
select to_number('12,34', '99,99') from dual;

Passing NLS settings to TO_NUMBER

数字格式模型元素最终都从 NLS 参数之一得出其含义。具体可查看设置：

SELECT * FROM nls_session_parameters;PARAMETER                      VALUE                                                           
------------------------------ -------------------------------------
NLS_LANGUAGE                   AMERICAN                                                        
NLS_TERRITORY                  AMERICA                                                         
NLS_CURRENCY                   $                                                               
NLS_ISO_CURRENCY               AMERICA                                                         
NLS_NUMERIC_CHARACTERS         .,                                                              
NLS_CALENDAR                   GREGORIAN                                                       
NLS_DATE_FORMAT                DD-MON-RR                                                       
NLS_DATE_LANGUAGE              AMERICAN                                                        
NLS_SORT                       BINARY                                                          
NLS_TIME_FORMAT                HH.MI.SSXFF AM                                                  
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM                                        
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                                              
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR                                    
NLS_DUAL_CURRENCY              $                                                               
NLS_COMP                       BINARY                                                          
NLS_LENGTH_SEMANTICS           BYTE                                                            
NLS_NCHAR_CONV_EXCP            FALSE                                                           17 rows selected. 

其中只能指定NLS_NUMERIC_CHARACTERS, NLS_CURRENCY 和 NLS_ISO_CURRENCY。例如：

select TO_NUMBER('F123.456,78','L999G999D99','NLS_NUMERIC_CHARACTERS='',.'''|| ' NLS_CURRENCY=''F'''|| ' NLS_ISO_CURRENCY=FRANCE') as result from dual;RESULT
----------123456.78

说明：

• NLS_NUMERIC_CHARACTERS控制G，即组分隔符
• NLS_CURRENCY控制L，即本地货币符号
• NLS_ISO_CURRENCY控制D，即小数字符

这些最好通过会话设置来控制，而不是像上面例子，硬编码到代码中。

The TO_CHAR Function

是TO_NUMBER的逆函数。调用格式类似：

TO_CHAR(number [,format [,nls_params]])

Using TO_CHAR with no format

较少使用，一般都需要格式化使其更具可读性。

Using TO_CHAR with a format model

select TO_CHAR(123456789.01,'L999G999G999D99') from dual;TO_CHAR(123456789.01,'L99
-------------------------$123,456,789.01

The V format element
V 格式元素比较特殊，允许您缩放值。

以股市为例，股票的标准交易单位是 100 股。因此，销售数字 123 实际上代表 123 个 100 股，即 12,300 股。
例如：

select TO_CHAR(123.45,'999V99') from dual;
TO_CHA
------12345select TO_CHAR(123.45,'999V9999') from dual;
TO_CHAR(
--------1234500

Rounding when converting numbers to character strings
将字符串转换为数字时，只要小数点左侧或右侧的数字多于格式模型允许的数字，您就会收到错误。 但是，将数字转换为字符时，仅当数字需要的小数点左侧位数多于格式模型允许的位数时，您才会收到错误。 如果您在格式模型中指定的小数位数（即小数点右侧的位数）少于数字所需的数字，则该数字将被四舍五入，以便小数部分适合您的模型。

例如：

-- 错误
select TO_CHAR(123.4567,'99.99') from dual;
TO_CHA
------
######-- 舍入
select TO_CHAR(123.4567,'999.99') from dual;
TO_CHAR
-------123.46select TO_NUMBER('123.45', '999.9') from dual;
ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.select TO_NUMBER('123.45', '99.99') from dual;
Error report -
SQL Error: ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

Dealing with spaces when converting numbers to character strings

注意以下输出的区别：

select TO_CHAR(-123.4), LENGTH(TO_CHAR(-123.4)) from dual;
select TO_CHAR(123.4), LENGTH(TO_CHAR(123.4)) from dual;select TO_CHAR(-123.4, '999.9'), LENGTH(TO_CHAR(-123.4, '999.9')) from dual;
select TO_CHAR(123.4, '999.9'), LENGTH(TO_CHAR(123.4, '999.9')) from dual;TO_CHA LENGTH(TO_CHAR(-123.4))
------ -----------------------
-123.4                       6TO_CH LENGTH(TO_CHAR(123.4))
----- ----------------------
123.4                      5TO_CHA LENGTH(TO_CHAR(-123.4,'999.9'))
------ -------------------------------
-123.4                               6TO_CHA LENGTH(TO_CHAR(123.4,'999.9'))
------ ------------------------------123.4                              6

注意最后2个SQL，当指定了格式模型时，TO_CHAR 总是为正负号留出空间。

如果你不需要前导空格，可以指定其他的格式模型，如TM，或使用LTRIM函数。

Passing NLS settings to TO_CHAR
和TO_NUMBER一样，也是只能指定NLS_NUMERIC_CHARACTERS, NLS_CURRENCY 和 NLS_ISO_CURRENCY。

The CAST Function

CAST 函数用于将数字转换为字符串，或反之。

手册见这里。

调用格式为：

CAST (expression AS datatype)

例如：

select cast(999.99 as varchar2(12)) from dual;
select cast('999.99' as number) from dual;

在SQL中写成如下会报错：

select cast(999.99 as varchar2) from dual;

但PL/SQL中不会：

DECLAREa NUMBER := -123.45;a1 VARCHAR2(30);
BEGINa1 := CAST (a AS VARCHAR2);
END;

CAST 的缺点是不支持使用数字格式模型。 CAST 的优点是它是 ISO SQL 标准的一部分，而 TO_CHAR 和 TO_NUMBER 函数则不是。 如果编写 100% 符合 ANSI 的代码对您很重要，那么应该用CAST。
不过，PL/SQL本身就不是 ISO 标准的一部分，因此根据定义不可能编写 100% 符合 ISO 的 PL/SQL 代码。

Implicit Conversions

如果你想控制代码的行为，知道转换何时发生，最好使用显式转换。

总之，不要使用隐式转换，坏处前面都说过了。

例如：

-- 不好
select '123.400' || 999 from dual;-- 好
select to_number('123.400' || to_char(999)) from dual;

作者也给出了一个例子，就是前面了个性能比较的例子：

DECLAREbd BINARY_DOUBLE;...
BEGIN...FOR bd IN 1..1000000 LOOPbd_area := bd**2 * pi_bd;END LOOP;...

其中有两个问题：

• FOR循环中的bd，实际上是隐式新声明了一个变量，他的类型是PLS_INTEGER。从而导致FOR循环体中使用PLS_INTEGER进行运算
• bd**2，由于2原因，是结果隐式转换为NUMBER。此处2应写成2d。

Numeric Operators

注意：PL/SQL和SQL的运算符优先级（Operator Precedence）是不一样的。前者参考这里，后者参考这里。

以下是PL/SQL的，优先级从高到低，高优先级的先算：

例如：

DECLAREa INTEGER := 1+2**2;b INTEGER := (1+2)**2;
BEGINDBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));DBMS_OUTPUT.PUT_LINE('b = ' || TO_CHAR(b));
END;
/

Numeric Functions

前面介绍过TO_CHAR, TO_NUMBER。

Rounding and Truncation Functions

TRUNC函数将数字截断至指定的小数位数。 TRUNC 简单地丢弃超出调用中提供的小数位数的所有值。

作者设计这个练习挺好的：

Trigonometric Functions

PL/SQL 中提供了许多三角函数。 使用它们时，请注意所有角度均以弧度表示，而不是度数。你可以进行转换：

radians = pi * degrees / 180 -- 从度数到弧度
degrees = radians * 180 / pi -- 从弧度到度数

PL/SQL 本身没有实现 π (pi) 函数。 但是，您可以通过以下调用获取 π 的值：

select ACOS(-1) from dual;ACOS(-1)
----------
3.14159265

Numeric Function Quick Reference

下面的列表简要描述了 PL/SQL 的每个内置数值函数。 在适用的情况下，函数会针对不同的数字类型进行重载。

ABS：对于 BINARY_DOUBLE、BINARY_FLOAT、NUMBER、SIMPLE_INTEGER、SIMPLE_FLOAT、SIMPLE_DOUBLE 和 PLS_INTEGER 进行重载，因为您可以获取浮点值和整数值的绝对值

BITAND：对于 PLS_INTEGER 和 INTEGER（NUMBER 的子类型）重载，因为该函数被设计为仅对整数值进行 AND 运算

CEIL：对于 BINARY_DOUBLE、BINARY_FLOAT 和 NUMBER 重载，因为 CEIL 是一个并不真正适用于整数的函数

查看重载可以：

DESCRIBE SYS.STANDARD...

列表详见这里：

• ABS
• ACOS
• ASIN
• ATAN
• ATAN2
• BIN_TO_NUM
• BITAND
• CEIL
• COS
• COSH
• EXP
• FLOOR
• GREATEST
• LEAST
• LN
• LOG
• MOD
• NANVL
• POWER
• REMAINDER
• ROUND
• SIGN
• SIN
• SINH
• SQRT
• TAN
• TANH
• TRUNC (number)
• WIDTH_BUCKET

-- BIN_TO_NUM 将位向量转换为其等效数字
SELECT BIN_TO_NUM(1,0,1,0) FROM DUAL; BIN_TO_NUM(1,0,1,0)
-------------------10-- BITAND将其输入和输出视为位向量； 输出是输入的按位与。
-- 0110 & 0011 = 0010 
SELECT BITAND(6,3) FROM DUAL;BITAND(6,3)
-----------2-- GREATEST 返回一个或多个表达式列表中最大的一个
-- LEAST 返回一个或多个表达式列表中的最小值。
SELECT GREATEST (1, '3.925', '2.4') "Greatest"FROM DUAL;Greatest
--------3.925SELECT LEAST (1, '3.925', '2.4') "Least"FROM DUAL;Least
----------1-- REMAINDER 返回 n2 除以 n1 的余数。
SELECT remainder(10, 2.4) FROM DUAL;REMAINDER(10,2.4)
-----------------.4-- SIGN 返回 n 的符号。表示正，负或0
SELECT sign(3-10) FROM DUAL;SIGN(3-10)
-----------1-- 

WIDTH_BUCKET 允许您构建等宽直方图，其中直方图范围分为具有相同大小的间隔。 （将此函数与 NTILE 进行比较，NTILE 创建等高直方图。）理想情况下，每个桶都是实数轴的闭开区间。 例如，可以将一个桶分配给 10.00 到 19.999 之间的分数…以指示该区间中包含 10，并且排除 20。 有时这被表示为 [10, 20)。

对于给定的表达式，WIDTH_BUCKET 返回该表达式的值在计算后将落入的桶号。

SELECT customer_id, cust_last_name, credit_limit, WIDTH_BUCKET(credit_limit, 100, 5000, 10) "Credit Group"FROM customers WHERE nls_territory = 'SWITZERLAND'ORDER BY "Credit Group", customer_id, cust_last_name, credit_limit;CUSTOMER_ID CUST_LAST_NAME       CREDIT_LIMIT Credit Group
----------- -------------------- ------------ ------------825 Dreyfuss                      500            1826 Barkin                        500            1827 Siegel                        500            1853 Palin                         400            1843 Oates                         700            2844 Julius                        700            2835 Eastwood                     1200            3836 Berenger                     1200            3837 Stanton                      1200            3840 Elliott                      1400            3841 Boyer                        1400            3842 Stern                        1400            3848 Olmos                        1800            4849 Kaurusmdki                   1800            4828 Minnelli                     2300            5829 Hunter                       2300            5850 Finney                       2300            5851 Brown                        2300            5852 Tanner                       2300            5830 Dutt                         3500            7831 Bel Geddes                   3500            7832 Spacek                       3500            7833 Moranis                      3500            7834 Idle                         3500            7838 Nicholson                    3500            7839 Johnson                      3500            7845 Fawcett                      5000           11846 Brando                       5000           11847 Streep                       5000           11