oracle数据库分册

经营分析系统员工培训文档-数据库分册 内部资料注意保密第 1 页 共 70 页 经营分析系统员工培训文档-ORACLE数据库分册Vesion 1.04. SQL4.2. PL/SQL4.2.1. PL/SQL 来源针对 SQL92 标准，各数据库厂商在标准基础上做了一定的扩展，SQL SERVER 的扩展语言叫 T-SQL，PL/SQL 是 ORACLE 对标准数据库语言的扩展，ORACLE 公司已经将PL/SQL 整合到 ORACLE 服务器和其他工具中了，近几年中更多的开发人员和 DBA 开始使用 PL/SQL，本文将讲述 PL/SQL 基础语法，结构和组件、以及如何设计并执行一个PL/SQL 程序。 PL/SQL 从版本 6 开始 PL/SQL 就被可靠的整合到 ORACLE 中了。PL/SQL 不是一个独立的产品，他是一个整合到 ORACLE 服务器和 ORACLE 工具中的技术，可以把 PL/SQL看作 ORACLE 服务器内的一个引擎，sql 语句执行者处理单个的 sql 语句，PL/SQL 引擎处理 PL/SQL 程序块。当 PL/SQL 程序块在 PL/SQL 引擎处理时，ORACLE 服务器中的 SQL语句执行器处理 pl/sql 程序块中的 SQL 语句。 PL/SQL 的优点如下： PL/SQL 是一种高性能的基于事务处理的语言，能运行在任何 ORACLE 环境中，支持所有数据处理命令。通过使用 PL/SQL 程序单元处理 SQL 的数据定义和数据经营分析系统员工培训文档-数据库分册 内部资料注意保密第 2 页 共 70 页 控制元素。 PL/SQL 支持所有 SQL 数据类型和所有 SQL 函数，同时支持所有 ORACLE 对象类型 PL/SQL 块可以被命名和存储在 ORACLE 服务器中，同时也能被其他的 PL/SQL程序或 SQL 命令调用，任何客户/服务器工具都能访问 PL/SQL 程序，具有很好的可重用性。 可以使用 ORACLE 数据工具管理存储在服务器中的 PL/SQL 程序的安全性。可以授权或撤销数据库其他用户访问 PL/SQL 程序的能力。 PL/SQL 代码可以使用任何 ASCII 文本编辑器编写，所以对任何 ORACLE 能够运行的操作系统都是非常便利的 对于 SQL，ORACLE 必须在同一时间处理每一条 SQL 语句，在网络环境下这就意味作每一个独立的调用都必须被 oracle 服务器处理，这就占用大量的服务器时间，同时导致网络拥挤。而 PL/SQL 是以整个语句块发给服务器，这就降低了网络拥挤。4.2.2. PL/SQL 基本结构PL/SQL 是 Oracle 对 SQL 规范的扩展，是一种块结构语言，即构成一个 PL/SQL 程序的基本单位（过程、函数和无名块）是逻辑块，可包含任何数目的嵌套了快。这种程序结构支持逐步求精方法解决问题。一个块（或子块）将逻辑上相关的说明和语句组合在一起，其形式为：DECLARE-说明BEGIN-语句序列EXCEPTION-例外处理程序END；PL/SQL 机可执行过程性语句，而将 SQL 语句发送到 ORACLE 服务器上的 SQL 语句执行器。在 ORACLE 预编译程序或 OCI 程序中可嵌入无名的 PL/SQL 块。如果 ORACLE具有 PROCEDURAL 选件，有名的 PL/SQL 块（子程序）可单独编译，永久地存储在数据库中，准备执行。 除非特别指明，SQL 不区分大小写 SQL 可以在一行或者多行输入 关键字不能分开 子句通常放在单独的行中 使用缩进是为了提高代码可读性 关键字通常使用大写字母输入，其他文字用小写输入经营分析系统员工培训文档-数据库分册 内部资料注意保密第 3 页 共 70 页 4.2.3. 数据类型4.2.3.1. 预定义数据类型Oracle 的数据类型可以分为四类，分别是标量类型，复合类型，引用类型和 LOB 类型。标量类型没有内部组件；而复合类型包含了能够被单独操作的内部组件；引用类型类似于3G 语言中的指针，能够引用一个值； LOB 类型的值就是一个 lob 定位器，能够指示出大对象(如图像 )的存储位置。下图是在 PL/SQL 中可以使用的预定义类型，其中标量类型又分为 四类：数字、字符、布尔和日期/时间。数字型数字类型可以存储整数、实数和浮点数，可以表示数值的大小，参与计算。 BINARY_INTEGER 我们可以使用 BINARY_INTEGER 数据类型来存储有符号整数。它的范围是-2*31 至2*31。跟 PLS_INTEGER 一样，BINARY_INTEGER 所需的存储空间也要小于经营分析系统员工培训文档-数据库分册 内部资料注意保密第 4 页 共 70 页 NUMBER。但是,大多数的 BINARY_INTEGER 操作要比 PLS_INTEGER 操作慢。 BINARY_INTEGER 子类型 所谓的基类型，就是有子类型继承于它。子类型在基类型的基础上添加一些约束限制，也可能重新定义数值范围。为了使用方便，PL/SQL 预定义了下面几个 BINARY_INTEGER的子类。 NATURAL NATURALN POSITIVE POSITIVEN SIGNTYPE 子类型 NATURAL 和 POSITIVE 能让我们将一个整数变量的取值范围分别限制在非负数和正整数之内。NATURALN 和 POSITIVEN 不允许为整数类型变量赋空值。 SIGNTYPE把整数的取值范围限定在-1， 0，1，这在编程中很适合表示三态逻辑(tri-state logic)。 NUMBER 我们可以使用 NUMBER 数据类型来存储定点或浮点数。它的范围是 1E-130 至10E125。如果表达式的值超过这个范围，我们就会得到数字溢出错误(a numeric overflow or underflow error)。我们可以为要存储的数字指定精度，包括数字的全长和小数长度。语法如下： NUMBER(precision,scale) 其中 precision 表示数字的总长度，scale 代表可以有几位小数。如果要使用浮点数的话，就不能指定长度和精度，像下面这样声明就可以了：NUMBER 声明整数直接使用下面的语法：NUMBER(precision) - same as NUMBER(precision,0) 不可以用常量或变量指定 NUMBER 的长度和精度。NUMBER 类型最大的长度是 38位。如果不指定 NUMBER 类型的最大长度，就会默认采用这个长度或是使用系统所支持的最大长度。scale 的范围从-84 到 127，能够决定舍入规则。例如，一个 scale 值为 2 的数字，舍入后的小数部分将是最接原小数部分的百分位数(3.456 舍入为 3.46)。如果 scale 是负数，它就会从小数点左边开始进行舍入操作。如 scale 值为-3 的数字舍入后的结果将是最接近原值的千位数(3456 舍入为 3000)。scale 为零的数字舍入后的结果还是本身。如果我们不指定scale 的值，默认就为 0。NUMBER 子类型 为了能和 ANSI/ISO 和 IBM 类型兼容或是想使用一个更加有描述性意义的名字，我们就可以使用下面的 NUMBER 子类型。 DEC DECIMAL DOUBLE PRECISION FLOAT INTEGER INT NUMERIC REAL SMALLINT 经营分析系统员工培训文档-数据库分册 内部资料注意保密第 5 页 共 70 页 使用 DEC、DECIMAL 和 NUMBERIC 可以声明最大精度为 38 位十进制数字的定点数字。而使用 DOUBLE PRECISION 和 FLOAT 可以声明最大精度为 126 位二进制数字的浮点数字，大约相当于 38 位十进制数字。或是使用 REAL 声明最大精度为 63 位二进制数字的浮点数字，大约相当于 18 位十进制数字。INTEGER、 INT 和 SMALLINT 可以声明最大精度为 38 位十进制数字的整数。 PLS_INTEGER 我们可以使用 PLS_INTEGER 数据类型来存储有符号整数。它的取值范围在-2*31 至2*31 之间。PLS_INTEGER 所需的存储空间要比 NUMBER 少，运算的速度要高于NUMBER 和 BINARY_INTEGER。虽然 PLS_INTEGER 和 BINARY_INTEGER 的取值范围一样，但它们不完全兼容。PLS_INTEGER 在运算时如果有溢出，则会有异常抛出，而BIANRY_INTEGER 发生溢出时，如果结果是要赋给一个 NUMBER 类型的变量时，就不会有异常抛出。为了考虑兼容性，我们仍可以在旧的应用程序中使用BINARY_INTEGER；但在新的应用程序中，PLS_INTEGER 会带来更好的性能。 字符型字符类型可以存放字符和数字混合的数据，表现词和文章，操作字符串。CHAR 我们可以使用 CHAR 类型来存储定长的字符数据。但该数据的内部表现形式是取决于数据库字符集的。CHAR 类型有一个用于指定最大长度的可选参数，长度范围在 1 到32767 字节之间。我们可以采用字节或字符的形式来设置该参数。语法如下：CHAR(maximum_size CHAR | BYTE ) maximum_size 不能是常量或变量，只能是范围在 1 到 32767 之间的整数文字。如果我们不指定最大值，它默认是 1。如果我们用字节形式指定最大值，有时就会出现空间不足的问题(多字节字符会占用多于一个字节的空间) 。为了避免这样的问题发生，我们可以采用按照字符的方式指定其最大值，这样，即使是那些包含多个字节的参数可以被灵活地存储下来。按照字符指定长度的方式，上限大小仍旧是 32767 字节，所以，对于双字节和多字节的字符集，我们可以使用字节最大长度的一半或三分之一作为最大字符个数。 虽然 PL/SQL 字符变量的长度相对来说比较长，但 CHAR 类型在数据库的字段中最大存储长度为 2000 个字节，所以，我们不能往数据库 CHAR 类型字段中插入超过 2000 个字节的字符。但是，我们可以把任意 CHAR(n)插入 LONG 类型的字段中，因为 LONG 的最大长度是 2*31 字节或是 2G(gigabyte)。如果我们不使用 CHAR 或 BYTE 来对字符类型长度进行限制的话，初始化参数 NLS_LENGTH_SEMANTICS 会决定默认长度大小的。CHAR 的子类型 CHARACTER 和 CHAR 有着相同的取值范围。也就是说， CHARACTER只是 CHAR 的一个别名而已。这个子类型能与 ANSI/ISO 和 IBM 类型相兼容。 LONG 和 LONG RAW 我们可以使用 LONG 类型来存储变长的字符串。除了 LONG 类型的最大长度是 32760字节之外，LONG 类型和 VARCHAR2 很相像。我们还可以使用 LONG RAW 类型来存储二进制数据或二进制字符串。LONG RAW 和 LONG 类似，但是它不会被 PL/SQL 解析。LONG RAW 的最大长度也是 32760 字节。从 9i 开始，LOB 类型变量可以与 LONG 和LONG RAW 类型交换使用。Oracle 推荐将 LONG 和 LONG RAW 都对应的转换成 COLB和 BLOB 类型。 我们可以将 LONG 类型的值插入字段类型为 LONG 的数据库中，因为在数据库中LONG 的长度是 2*31 字节；但是，不可以从 LONG 字段中检索超过 32760 字节的字符放到 LONG 类型变量中去。同样，对于 LONG RAW 类型来说，这条规则同样适用，在数据经营分析系统员工培训文档-数据库分册 内部资料注意保密第 6 页 共 70 页 库中它的最大长度也是 2*31 字节，而变量的长度在 32760 字节以内。LONG 类型字段可以存储文本、字符数组或短文等。我们可以对 LONG 字段进行 UPDATE、INSERT 和SELECT 操作，但不能在表达式、SQL 函数调用、或某个 SQL 子句( 如：WHERE、GROUP BY 和 CONNECT BY)中使用它。 注意：在 SQL 语句中，PL/SQL 会将 LONG 类型的值绑定成 VARCHAR2 类型，而不是 LONG。但是，如果被绑定的 VARCHAR2 值超过 4000 个字节，Oracle 会自动地将绑定类型转成 LONG，但 LONG 并不能应用在 SQL 函数中，所以，这时我们就会得到一个错误消息。 RAW 我们可以使用 RAW 数据类型来存储二进制数据或字节串。例如，一个 RAW 类型的变量可以存储一个数字化图形。RAW 类型数据和 VARCHAR2 类型数据类似，只是PL/SQL 不对其进行解析而已。同样，在我们把 RAW 数据从一个系统传到另一个系统时，Oracle Net 也不会对它做字符集转换。 RAW 类型包含了一个可以让我们指定最大长度的可选参数，上限为 32767 字节，语法如下：RAW(maximum_size) 我们不能使用常量或变量来指定这个参数；而且参数的范围必须是在 1 到 32767 范围内。在数据库中 RAW 类型字段的最大长度是 2000 个字节，所以，不可以把超过 2000 字节的内容放到 RAW 类型字段中。我们可以把任何 RAW 类型插入到 LONG RAW 类型的数据库字段中，因为 LONG RAW 在数据库中是 2*31，但是不能把超过 32767 字节的LONG RAW 类型放入 RAW 变量中。 ROWID 和 UROWID 在 Oracle 内部，每个数据表都有一个伪列 ROWID，用于存放被称为 ROWID 的二进制值。每个 ROWID 代表了一行数据的存储地址。物理 ROWID 能够标识普通数据表中的一行信息，而逻辑 ROWID 能够标识索引组织表 (index-organized table)中的一行信息。其中ROWID 类型只能存储物理内容，而 UROWID(universal rowid)类型可以存储物理，逻辑或外来(non-Oracle)ROWID。 建议：只有在旧的应用程序中，为了兼容性我们才使用 ROWID 数据类型。对于新的应用程序，应该使用 UROWID 数据类型。 当我们把查询出来的 ROWID 放到 ROWID 变量时，可以使用内置函数ROWIDTOCHAR，这个函数能把二进制内容转换成 18 个字节的字符串；还有一个与之对应的 CHARTOROWID 函数，可以对该过程进行反操作，如果转换过程中发现字符串并不是一个有效的 ROWID 时，PL/SQL 就会抛出预定义异常SYS_INVALID_ROWID。UROWID 变量和字符串之间进行转换也可以直接使用赋值操作符。这时，系统会隐式地实现 UROWID 和字符类型之间的转换。 物理 ROWID(Physical Rowid)可以让我们快速的访问某些特定的行。只要行存在，它的物理 ROWID 就不会改变。高效稳定的物理 ROWID 在查询行集合、操作整个集合和更新子集是很有用的。例如，我们可以在 UPDATE 或 DELETE 语句的 WHERE 子句中比较UROWID 变量和 ROWID 伪列来找出最近一次从游标中取出的行数据。 物理 ROWID 有两种形式。 10 字节扩展 ROWID 格式(10-byte extended rowid format)支持相对表空间块地址并能辨识分区表和非分区表中的行记录。6 字节限定 ROWID 格式支持向后兼容。 扩展 ROWID 使用检索出来的每一行记录的物理地址的 base-64 编码。例如，在SQL*Plus(隐式地将 ROWID 转换成字符串)中的查询： SQL SELECT ROWID, ename经营分析系统员工培训文档-数据库分册 内部资料注意保密第 7 页 共 70 页 SQL FROM empSQL WHERE empno = 7788;ROWID ENAME- -AAAAqcAABAAADFNAAH SCOTT OOOOOOFFFBBBBBBRRR 这样的形式有四部分组成: “000000“代表数据对象号(data object number)，如上例中的“AAAAqc“ ，能够辨识数据库段。同一段中的模式对象，都有着相同的数据对象号。 “FFF“代表文件号(file number)，如上例中的“AAB“，能辨识出包含行的数据文件。在数据库中，文件号是唯一的。 “BBBBBB“代表块号(block number)，如上例中的“AAADFN“，能辨识出包含行的数据块。块号是与它们所在的数据文件相关，而不是表空间。所以，两个在同一表空间的行数据，如果它们处于不同的数据文件中，也可能有着相同的块号。 “RRR“代表了行号(row number)，如上例中的“AAH“，可以辨识块中的行数据。 逻辑 ROWID 为访问特定行提供了最快的访问速度。Oracle 在索引组织表基础上使用它们构建二级索引。逻辑 ROWID 没有持久的物理地址，当新数据被插入时，它的值就会在数据块上偏移。但是，如果一个行数据的物理位置发生变化，它的逻辑 ROWID 就无效了。 VARCHAR2 我们可以使用 VARCHAR2 类型来存储变长的字符数据。至于数据在数据库中的内部表现形式要取决于数据库的字符集。VARCHAR2 类型需要指明数据长度，这个参数的上限是 32767 字节。语法如下： VARCHAR2(maximum_size CHAR | BYTE) 我们不能使用常量或变量来指定 maximum_size 值，maximum_size 值的有效范围在 1到 32767 之间。对于长度不同的 VARCHAR2 类型数据，PL/SQL 对它们的处理方式也是不同的。值小的 PL/SQL 会优先考虑到它的性能问题，而对于值大的 PL/SQL 会优先考虑到内存的使用效率问题。截止点(cutoff point)为 2000 个字节。在 2000 字节以下，PL/SQL 会一次性分配我们声明时所指定大小的空间容纳实际的值；2000 字节或 2000 字节以上时，PL/SQL 会动态的分配 VARCHAR2 的存储容量。比如我们声明两个 VARCHAR2 类型的变量，一个长度是 1999 字节，一个是 2000 字节，如果我们把长度为 500 字节的值分别分配给这两个变量，那么前者占用的空间就是 1999 字节而后者只需 500 字节。如果我们采用字节形式而非字符形式指定最大值时，VARCHAR2(n) 变量就有可能太小而不能容纳 n 个多字节字符。为了避免这个问题，就要使用 VARCHAR2(n CHAR)形式进行定义，这样，即使字符中包含多字节字符也不会出问题。所以，对于双字节或多字节字符集，我们可以指定单字节字符集中字符个数的 1/2 或 1/3。虽然 PL/SQL 字符变量相对比较长，但 VARCHAR2 类型的数据库字段最大长度为4000 个字节。所以，不能把字节超过 4000 的 VARCHAR2 类型值插入 VARCHAR2 类型的数据库字段中。我们可以把任何 VARCHAR2(n)值插入一个 LONG 类型的数据库字段，因为 LONG 字段最大长度为 2*31 字节。但是，不能把 LONG 字段中检索出来的长度超过 32767 字节的值放到 VARCHAR2(n)变量中。如果声明时不使用 CHAR 或 BYTE 限定修饰词，初始化参数经营分析系统员工培训文档-数据库分册 内部资料注意保密第 8 页 共 70 页 NLS_LENGTH_SEMANTICS 会决定默认的设置。当 PL/SQL 过程编译时，这个参数的设置就会被记录下来，这样，当过程失效后被重新编译时就会被重新使用。VARCHAR2 的子类型 下面 VARCHAR2 的子类型的范围与 VARCHAR2 完全相同，它们只是 VARCHAR2的一个别名而已。 STRING VARCHAR 我们可以使用这些子类型来与 ANSI/ISO 和 IBM 类型兼容。注意：目前，VARCHAR和 VARCHAR2 有着相同意义，但是在以后的 PL/SQL 版本中，为了符合 SQL 标准，VARCHAR 有可能会作为一个单独的类型出现。所以最好使用 VARCHAR2，而不是VARCHAR。 本地字符型被广泛使用的单字节 ASCII 和 EBCDIC 字符集很适合表现罗马字符，但有些亚洲语言，如汉语、日语等包含了成千上万个字符，这些语言中的一个字符就需要用两个或三个字节来表示。为了处理这些语言，Oracle 提供了全球化支持，允许我们处理单字节和多字节字符数据，并在字符集之间进行数据转换。Oracle 还能让我们的应用程序运行在不同的语言环境中。有了全球化支持，数字和日期格式会根据用户会话中所指定的语言约定(language convention)而自动进行调节。因此，全世界的用户可以使用他们母语来使用 Oracle。 Oracle 支持两种数据库字符集和一种国家特有字符集，前者用于标识符和源代码，后者用于国家特有语言数据。NCHAR 和 NVARCHAR2 类型用于存储本地字符集。注意，当在数据库的不同字符集之间转换 CHAR 或 VARCHAR2 数据时，要确保数据保持良好的形式(well-formed)。比较 UTF8 和 AL16UTF16 编码 国家特有字符集使用 Unicode 来表现数据，采用 UTF8 或 AL16UTF16 编码。 每个使用 AL16UTF16 编码的字符都占用 2 个字节。这将简化字符串的长度计算，避免采用混合语言编程时发生截断错误，但是这会比 ASCII 字符所组成的字符串需要更多空间。 每个使用 UTF8 编码的字符占用 1、2 或 3 个字节。这就能让我们把更多的字符放到变量或数据表的字段中，但这只是在大多数字符用单字节形式表现的条件下才能做到。这种编码在传递数据到字节缓冲器时可能会引起截断错误。 Oracle 公司推荐使用默认的 AL16UTF16 编码，这样可以获取最大的运行时可靠性。如果想知道一个 Unicode 字符串占用多少字节，就要使用 LENGTHB 函数，而不是LENGTH。 NCHAR 我们用 NCHAR 类型来储存定长国家特有字符数据。数据的内部表现取决于数据库创建时指定的国家特有字符集，字符集可能采用变长编码(UTF8)或定长编码(AL16UTF16)。因为这种类型总是与多字节字符兼容，所以我们可以使用它支持任何 Unicode 字符数据。 NCHAR 数据类型可接受一个可选参数来让我们指定字符的最大长度。语法如下： NCHAR(maximum_size) 因为物理限制是 32767 个字节，所以在 AL16UTF16 编码格式下最大长度为32767/2，UTF8 编码格式下是 32767/3。我们不能使用常量或变量来指定最大值，只能使用整数文字。如果我们没有指定最大长度，它默认值就为 1。这个值总是代表字符的个数，不像经营分析系统员工培训文档-数据库分册 内部资料注意保密第 9 页 共 70 页 CHAR 类型，既可以采用字符形式又可以采用字节形式。my_string NCHAR(100); - maximum size is 100 characters NCHAR 在数据库字段中的最大宽度是 2000 字节。所以，我们不能向 NCHAR 字段中插入值超过 2000 字节的内容。如果 NCHAR 的值比 NCHAR 字段定义的宽度要小，Oracle 就会自动补上空格，填满定义的宽度。我们可以在语句和表达式中交互使用 CHAR 和 NCHAR 值。从 CHAR 转到 NCHAR总是安全的，但在 NCHAR 值转换到 CHAR 的过程中，如果 CHAR 类型不能完全表现NCHAR 类型的值，就会引起数据丢失。这样的数据丢失会导致字符看起来像问号(?)。NVARCHAR2 我们可以使用 NVARCHAR2 数据类型来存储变长的 Unicode 字符数据。数据的内部表现取决于数据库创建时所指定的国家特有字符集，它有可能采用变长编码(UTF8)或是定长编码(AL16UTF16) 。因为这个类型总与多字节兼容，我们可以用它来支持 Unicode 字符数据。NVARCHAR2 数据类型需要接受一个指定最大大小的参数。语法如下：NVARCHAR2(maximum_size) 因为物理限制是 32767 个字节，所以在 AL16UTF16 编码格式下最大长度为32767/2，UTF8 编码格式下是 32767/3。我们不能使用常量或变量来指定最大值，只能使用整数文字。最大值总是代表字符的个数，不像 CHAR 类型，既可以采用字符形式又可以采用字节形式。my_string NVARCHAR2(200); - maximum size is 200 characters NVARCHAR2 在数据库字段中的最大宽度是 4000 字节。所以，我们不能向NVARCHAR2 字段中插入长度超过 4000 字节的值。我们可以在语句和表达式中交互使用 VARCHAR2 和 NVARCHAR2 值。从VARCHAR2 向 NVARCHAR2 转换总是安全的，但在 NVARCHAR2 值转换到 VARCHAR2的过程中，如果 VARCHAR2 类型不能完全表现 NVARCHAR2 类型的值，就会引起数据丢失。这样的数据丢失会导致字符看起来像问号(?)。LOB 类型LOB(large object)数据类型 BFILE、BLOB、CLOB 和 NCLOB 可以最大存储 4G 的无结构数据( 例如：文本、图形、视频剪辑和音频等 )块。并且，它们允许高效地随机地分段访问数据。LOB 类型和 LONG、LONG RAW 类型相比有几个不同的地方。比如， LOB(除了NCOLB)可以作为对象类型的一个属性，但 LONG 类型不可以。LOB 的最大值是 4G，而LONG 只有 2G。LOB 支持随机访问数据，但 LONG 只支持顺序访问。LOB 类型中可以存储了 LOB 定位器，它能够指向存放于外部文件中的“大对象“ ，in-line (inside the row)或 out-of-line (outside the row)的形式。BLOB 、CLOB、NCLOB 或BFILE 类型的数据库字段存储了定位器。其中 BLOB、CLOB 和 NCLOB 的数据存在数据库中，in-line (inside the row)或 out-of-line (outside the row)的形式，而 BFILE 数据存在数据库之外的操作系统文件中。PL/SQL 是通过定位器来操作 LOB 的。例如，当我们查询出一个 BLOB 值，只有定位器被返回。如果在事务中得到定位器，LOB 定位器就会包含事务的 ID 号，这样我们就不能在另外一个事务中更新 LOB 内容了。同样，我们也不能在一个会话中操作另外一个会话中的定位器。 经营分析系统员工培训文档-数据库分册 内部资料注意保密第 10 页 共 70 页 从 9i 开始，我们也可以把 CLOB 类型转成 CHAR 和 VARCHAR2 类型或是把 BLOB转成 RAW，反之亦然，这样，我们就能在大多数 SQL 和 PL/SQL 语句和函数中使用 LOB类型了。要读、写和分段的操作 LOB，我们可以使用 Oracle 系统包 DBMS_LOB。BFILE BFILE 数据类型用于存储二进制对象，它将存储的内容放到操作系统的文件中，而不是数据库内。每个 BFILE 变量都存储一个文件定位器，它指向服务器上的一个大的二进制文件。定位器包含目录别名，该别名给出了文件全路径。BFILE 类型是只读的，而且它的大小要依赖于系统，不能超过 4G。我们的 DBA 要确保给定的 BFILE 存在且 Oracle 有读取它的权限。BFILE 并不参与事务，是不可恢复，不能被复制。能够被打开的 BFILE 最大数是由Oracle 初始化参数 SESSION_MAX_OPEN_FILES 决定的。 BLOB、 CLOB 和 NCLOB BLOB 数据类型可以在数据库中存放不超过 4G 的大型二进制对象；CLOB 和 NCLOB可以在数据库中分别存储大块 CHAR 类型和 NCHAR 类型的字符数据，都支持定宽和变宽字符集。同 BFILE 一样，这三个类型也都储存定位器，指向各自类型的一个大数据块。数据大小都不能超过 4G。BLOB、CLOB 和 NCLOB 都可以在事务中使用，能够被恢复和复制。DBMS_LOB 包可以对它们更改过的内容进行提交或回滚操作。BLOB 、CLOB 和NCLOB 的定位器都可以跨事务使用，但不能跨会话使用。 布尔类型布尔类型能存储逻辑值 TRUE、FALSE 和 NULL(NULL 代表缺失、未知或不可用的值)。只有逻辑操作符才允许应用在布尔变量上。数据库 SQL 类型并不支持布尔类型，只有 PL/SQL 才支持。所以就不能往数据库中插入或从数据库中检索出布尔类型的值。 Datetime 和 Interval 类型Datetime 就是日期时间类型，而 Interval 指的是时间的间隔。 Datetime 和 Interval 类型都由几个域组成，下表是对每个域及其它们对应的有效值描述： 域名称 有效日期时间值 有效间隔值 YEAR -4712 到 9999 (不包括 0) 任意非零整数 MONTH 01 到 12 0 到 11 DAY 01 到 31 (根据当地的历法规则，受 MONTH 和 YEAR 值的限制 任意非零整数 HOUR 00 到 23 0 到 23 MINUTE 00 到 59 0 到 59 SECOND 00 到 59.9(n)，9(n)是秒小数部分的精度 0 to 59.9(n)，9(n)间隔秒的小数部分的精度 TIMEZONE_HOUR -12 到 14 (随日光节约时间的变化而变化) 不可用 TIMEZONE_MINUTE 00 到 59 不可用 TIMEZONE_REGION 查看视图 V$TIMEZONE_NAMES 不可用 TIMEZONE_ABBR 查看视图 V$TIMEZONE_NAMES 不可用 除了 TIMESTAMP WITH LOCAL TIMEZONE 以外，剩下的都是 SQL92 所支持的。 DATE DATE 数据类型能够存储定长的日期时间。日期部分默认为当月的第一天；时间部分为午夜时间。函数 SYSDATE 能够返回当前的日期和时间。 经营分析系统员工培训文档-数据库分册 内部资料注意保密第 11 页 共 70 页 提示：如果只进行日期的等值比较，忽略时间部分，可以使用函数TRUNC(date_variable)。 有效的日期范围是从公元前 4721 年 1 月 1 日到公元 9999 年 12 月 31 日。儒略日(Julian date)是自公元前 4712 年 1 月 1 日起经过的天数。我们可以使用日期模式“J“配合函数 TO_DATE 和 TO_CHAR 来把日期值转换成等值的儒略日。在日期表达式中 PL/SQL 会自动地将格式为默认日期格式的字符值转成 DATE 类型值。默认的日期格式由 Oracle 的初始化参数 NLS_DATE_FORMAT 决定的。例如，默认格式可能是“DD-MON-YY“，它包含两位数字表示一个月中的第几日，月份名称的缩写和两位记年用的数字。我们可以对日期进行加减运算。例如，下面的语句就能返回员工自被雇用日起，至今所经过的天数：SELECT SYSDATE - hiredate INTO days_worked FROM empWHERE empno = 7499; 在算术表达式中，PL/SQL 会将整数文字当作“日“来处理，如 “SYSDATE + 1“就代表明天的时间。TIMESTAMP TIMESTAMP 是对 DATE 的扩展，包含了年月日时分秒，语法如下： TIMESTAMP(precision) precision 是可选参数，用于指定秒的小数部分数字个数。参数 precision 不可以是常量或变量，其有效范围是 0 到 9，默认值是 6。默认的时间戳(timestamp)格式是由 Oracle 初始化参数 NLS_TIMESTAMP_FORMAT 决定的。 在下面的例子中，我们声明了一个 TIMESTAMP 类型的变量，然后为它赋值：DECLAREcheckout TIMESTAMP ( 3 );BEGINcheckout := 1999-06-22 07:48:53.275;.END; 这个例子中，秒的小数部分是 0.275。TIMESTAMP WITH TIME ZONE TIMESTAMP WITH TIME ZONE 扩展了 TIMESTAMP，能够表现时区位移 (time-zone displacement)。时区位移在本地时间和通用协调时间(UTC) 中是不同的。语法如下： TIMESTAMP(precision) WITH TIME ZONE precision 的用法同 TIMESTAMP 语法中的 precision。默认格式由 Oracle 初始化参数NLS_TIMESTAMP_TZ_FORMAT 决定。下例中，我们声明一个 TIMESTAMP WITH TIME ZONE 类型的变量，然后为其赋值：DECLARELOGOFF TIMESTAMP ( 3 ) WITH TIME ZONE;BEGINLOGOFF := 1999-10-31 09:42:37.114 +02:00;.经营分析系统员工培训文档-数据库分册 内部资料注意保密第 12 页 共 70 页 END; 例子中时区位移是+02:00 。我们还可以使用符号名称 (symbolic name)来指定时区位移，名称可以是完整形式也可以是缩写形式，如“US/Pacific“和“PDT“，或是组合的形式。例如，下面的文字全都表现同一时间。第三种形式最可靠，因为它指定了切换到日光节约时间时的规则。TIMESTAMP 1999-04-15 8:00:00 -8:00TIMESTAMP 1999-04-15 8:00:00 US/PacificTIMESTAMP 1999-10-31 01:30:00 US/Pacific PDT 我们可以在数据词典 V$TIMEZONE_NAMES 的 TIMEZONE_REGION 和TIMEZONE_ABBR 字段中找到相应的时区名称和它的缩写。如果两个 TIMESTAMP WITH TIME ZONE 值在通用协调时间中的值一样，那么系统就会认为它们的值相同而忽略它们的时区位移。下例两个值被认为是相同的，因为在通用协调时间里，太平洋标准时间 8:00 AM 和(美国) 东部时区 11:00 AM 是相同的：1999-08-29 08:00:00 -8:001999-08-29 11:00:00 -5:00 TIMESTAMP WITH LOCAL TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE 是对 TIMESTAMP WITH TIME ZONE 的扩展，它的语法如下： TIMESTAMP(precision) WITH LOCAL TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE 的用途和 TIMESTAMP WITH TIME ZONE相似，它们不同之处在于，当我们往数据库中插入 TIMESTAMP WITH LOCAL TIME ZONE 类型数据的时候，数据会被转成数据库的时区，并且时区位移并不会存放在数据库中。当进行数据检索时，Oracle 会按我们本地会话的时区设置返回值。下面就是一个使用TIMESTAMP WITH LOCAL TIME ZONE 的例子： DECLARELOGOFF TIMESTAMP ( 3 ) WITH LOCAL TIME ZONE;BEGINNULL;.END; 我们不可以用文字值为这种类型的变量赋值。INTERVAL YEAR TO MONTH 我们可以使用 INTERVAL YEAR TO MONTH 类型用来保存年月的间隔，语法如下： INTERVAL YEAR(precision) TO MONTH precision 指定间隔的年数。参数 precision 不能是常量或变量，其范围在 1 到 4 之间，默认值是 2，下面的例子中声明了一个 INTERVAL YEAR TO MONTH 类型的变量，并把间隔值 101 年 3 个月赋给它：DECLARElifetime INTERVAL YEAR(3)TO MONTH;BEGINlifetime := INTERVAL 101-3 YEAR TO MONTH; - interval literallifetime := 101-3; - implicit conversion from character typelifetime := INTERVAL 101 YEAR; - Can specify just the yearslifetime := INTERVAL 3 MONTH; - Can specify just the months经营分析系统员工培训文档-数据库分册 内部资料注意保密第 13 页 共 70 页 .END; INTERVAL DAY TO SECOND 我们可以使用 INTERVAL DAY TO SECOND 数据类型存储和操作天、小时、分钟和秒，语法如下：INTERVAL DAY(leading_precision) TO SECOND(fractional_seconds_precision) leading_precision 和 fractional_seconds_precision 分别指定了天数和秒数。这两个值都不可以用常量或变量指定，且只能使用范围在 0 到 9 之间的整数文字为其赋值。它们的默认值分别为 2 和 6。下面的例子中，我们声明了一个 INTERVAL DAY TO SECOND 类型的变量：DECLARElag_time INTERVAL DAY(3)TO SECOND(3);BEGINIF lag_time INTERVAL 6 DAY THEN .END; 4.2.3.2. 用户自定义子类型暂时不考虑4.2.3. 基本函数4.2.3.1. 字符函数ASCII返回与指定的字符对应的十进制数;SQL select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual;A A ZERO SPACE- - - -65 97 48 322.CHR给出整数,返回对应的字符;SQL select chr(54740) zhao,chr(65) chr65 from dual; ZH C- -赵 A3.CONCAT连接两个字符串;SQL select concat(010-,88888888)|转 23 高乾竞电话 from dual;经营分析系统员工培训文档-数据库分册 内部资料注意保密第 14 页 共 70 页 高乾竞电话转 234.INITCAP返回字符串并将字符串的第一个字母变为大写;SQL select initcap(smith) upp from dual;UPP-Smith5.INSTR(C1,C2,I,J)在一个字符串中搜索指定的字符,返回发现指定的字符的位置;C1 被搜索的字符串C2 希望搜索的字符串I 搜索的开始位置,默认为 1J 出现的位置,默认为 1SQL select instr(oracle traning,ra,1,2) instring from dual;INSTRING-96.LENGTH返回字符串的长度;SQL select name,length(name),addr,length(addr),sal,length(to_char(sal) from .nchar_tst;NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL)- - - - - -高乾竞 3 北京市海锭区 6 9999.99 77.LOWER返回字符串,并将所有的字符小写SQL select lower(AaBbCcDd)AaBbCcDd from dual;AABBCCDD-aabbccdd8.UPPER返回字符串,并将所有的字符大写SQL select upper(AaBbCcDd) upper from dual;UP