精华内容
下载资源
问答
  • 具有实体的数据类型
    千次阅读
    2020-01-11 15:14:37

    数据类型: 是一个值的集合和定义在此集合上一组操作的总称。
    数据类型有两种:简单型结构类型
    对于结构类型,比如数组,它的值是可以分割的,它是某个结构的值,因此数组这个数据类型的子集是一个数据结构。所以数据类型也可以看做是一个数据结构和定义在这个数据结构上一组操作的总称。

    抽象数据类型(Abstract Datat Type, ADT): 是一个数学模型和定义在此数学模型上一组操作。
    在数据结构中,这个数学模型指的就是数据结构。因此在数据结构中,讨论的抽象数据类型是:一个数据结构和定义在这个数据结构上的一组操作
    因为ADT有两个重要特征,所以从抽象数据类型这个角度讨论数据结构有很多好处。
    【ADT的两个重要特征】
    1.数据抽象
    数据的抽象性指的是:用ADT描述程序处理的实体时,强调的是其本质的特征、其所能完成的功能以及它和外部用户的接口(即外界使用它的方法)。
    强调的是本质特征,自然会忽略掉无关紧要的属性;描述的是外部使用的接口,能保证外部用户正确地使用它们。
    例如: 抽象复数的数据类型
    复数类型在高级语言里不能直接进行加减,为了使外部用户能够直接使用它,这时就可以定义抽象数据类型复数。
    抽象数据类型复数的定义:

    ADT Complex{
    数据对象:
        D={e1,e2 | e1,e2∈RealSet}
        
    数据关系:
        R1={<e1,e2> | e1是复数的实数部分,e2是复数的虚数部分}
        
    基本操作:
        InitComplex(&Z,v1,v2)   //初始化一个复数
        操作结果:构造复数Z,其实部和虚部分别被赋以参数v1和v2值。
        DestroyComplex(&Z)     //销毁一个复数
        操作结果:复数Z被销毁。
        GetReal(Z,&realPart)   //取复数的实部
        初始条件:复数已存在。
        操作结果:用realPart返回复数Z的实部值。
        GetImageNet(Z,&imagPart)   //取复数的虚部
        初始条件:复数已存在。
        操作结果:用imagPart返回复数Z的虚部值。
        Add(z1,z2,&sum)   // 两个复数相加
        初始条件:z1,z2是复数。
        操作结果:用sum返回两个复数z1,z2的和值。
    }ADT Complex
    

    外部用户对复数操作的时候,只需要简单地调用相加的函数就能实现,这样就实现了抽象数据类型的抽象性。
    2.数据封装
    数据的封装性指的是:将实体的外部特性和其内部实现的细节分离,并且对外部用户隐藏其内部实现细节。
    例如:对于刚才的抽象数据类型复数,外部用户想要实现两个复数的相加,只需要调用相加的函数就可以了,不需要关心复数在计算机内部是怎么表示的。

    抽象数据类型的描述方法: 抽象数据类型可用(D, S, P)三元组表示,其中,D是数据对象,即具有相同特性的数据元素的集合,S是D上的关系集,P是对D的基本操作集。
    说明: D+S实际上就是数据结构,P就是定义在这个数据结构上的操作集。当在讨论数据结构的时候,都是从抽象数据类型出发的,也就是说数据结构和它的操作集是一个整体。

    抽象数据类型的表示方法: 抽象数据类型通过已有的数据类型来实现。

    更多相关内容
  • ORACLE数据库数据类型

    千次阅读 2019-11-28 17:51:42
    1、字符类型 • CHAR:一个定长字符串,当位数不足自动用空格填充来达到其最大长度。如非NULL的CHAR(12)总是包含12字节信息。CHAR字段最多可以存储2,000字节的 信息。 • VARCHAR2:目前这也是VARCHAR 的同义词。这...

    Oracle的数据类型

    Oracle的数据类型分为标量(Scalar)类型、复合(Composite)类型、引用(Reference)类型和LOB(Large Object)类型4种类型。

    因为标量类型没有内部组件,所以,它又分为四类:数值、字符、布尔和日期/时间。

    复合类型也叫组合类型,它包含了能够被单独操作的内部组件,每个组件都可以单独存放值,所以,一个复合变量可以存放多个值。因为复合变量类型不是数据库中已经存在的数据类型,所以,复合变量在声明类型之前,首先要创建复合类型,复合类型创建后可以多次使用,以便定义多个复合变量。复合变量像标量变量一样也有数据类型,复合数据类型有记录(RECORD)、表(TABLE)、嵌套表(Nested TABLE)和数组(VARRAY)四种类型,其中,表、嵌套表和数组也称为集合,而集合类型(表、嵌套表和数组)在使用时必须先使用TYPE进行定义方可使用。记录是由一组相关但又不同的数据类型组成的逻辑单元。表是数据的集合,可将表中的数据作为一个整体进行引用和处理。嵌套表是表中之表。一个嵌套表是某些行的集合,它在主表中表示为其中的一列。对主表中的每一条记录,嵌套表可以包含多个行。在某种意义上,它是在一个表中存储一对多关系的一种方法。可变数组(VARRAY)存储固定数量的元素(在运行中,可以改变元素数量),使用顺序数字作下标,可以定义等价的SQL类型,可以存储在数据库中。可以用SQL进行存储和检索,但比嵌套表缺乏灵活性。

    引用类型类似于指针,能够引用一个值。

    LOB(Large Object)类型的值就是一个LOB定位器,能够指示出大对象的存储位置。目前Oracle支持的LOB类型具体包括四个子类型(Subtype),分别为CLOB、BLOB、NLOB和BFILE。其中,CLOB、BLOB和NLOB都是将数据保存在数据库内部,所以称为内部LOB,而BFILE类型保存的核心是文件指针,真正的文件是保存在数据库外,所以称为外部LOB。

    如果处理单行单列的数据那么可以使用标量变量;如果处理单行多列数据那么可以使用PL/SQL记录;如果处理单列多行数据那么可以使用PL/SQL集合。

    BOOLEAN数据类型用于定义布尔型(逻辑型)变量,其值只能为TRUE(真)、FALSE(假)或NULL(空)。需要注意的是,该数据类型是PL/SQL数据类型,不能应用于表列。

    下图是在PL/SQL中可以使用的预定义类型。

    在这里插入图片描述

    图 3-1 PL/SQL中可以使用的预定义类型

    数据类型的作用在于指明存储数值时需要占据的内存空间大小和进行运算的依据。Oracle的字段数据类型如下表所示:

    表中 Oracle的字段数据类型

    数据类型

    描述

    VARCHAR2(size)

    可变长字符数据。VARCHAR2(n)数据类型用于定义可变长度的字符串,其中,n用于指定字符串的最大长度,n的值必须是正整数且不超过32767。

    CHAR(size)

    定长字符数据。CHAR(n)数据类型用于定义固定长度的字符串,其中,n用于指定字符串的最大长度,n的值必须是正整数且不超过32767。

    NUMBER(p,s)

    可变长数值数据。NUMBER(precision,scale)数据类型用于定义固定长度的整数和浮点数,其中,precision表示精度,用于指定数字的总位数;scale表示标度,用于指定小数点后的数字位数,默认值为0,即没有小数位数。

    DATE

    日期型数据。DATE数据类型用于定义日期时间类型的数据,其数据长度为固定7个字节,分别描述年、月、日、时、分、秒。

    LONG

    可变长字符数据,最大可达到2G。LONG数据类型在其它的数据库系统中常被称为备注类型,它主要用于存储大量的可以在稍后返回的文本内容。

    TIMESTAMP

    TIMESTAMP数据类型也用于定义日期时间数据,但与DATE仅显示日期不同,TIMESTAMP类型数据还可以显示时间和上下午标记,如“11-9月-2007 11:09:32.213 AM”。

    CLOB

    字符数据,最大可达到4G。

    RAW和LONG RAW

    裸二进制数据。LONG RAW数据类型在其它数据库系统中常被称为大二进制类型(BLOB),它可以用来存储图形、声音视频数据,尽管关系型数据库管理系统最初不是为它们而设计的,但是多媒体数据可以存储在BLOB或LONG RAW类型的字段内。

    BLOB

    二进制数据,最大可达到4G。

    BFILE

    存储外部文件的二进制数据,最大可达到4G。

    ROWID

    行地址,十六进制串,表示行在所在的表中唯一的行地址,该数据类型主要用于返回ROWID伪列,常用在可以将表中的每一条记录都加以唯一标识的场合。

    真题1、PL/SQL中的%ROWTYPE和%TYPE的区别是什么?

    答案:%TYPE是定义一个变量,其数据类型与已经定义的某个数据变量的类型相同,或者与数据库表的某个列的数据类型相同,其使用示例如下所示:

    DECLARE

    V_SAL NUMBER(7) ;
    
    V_ENAME EMP.ENAME%TYPE;
    
    V_HIREDATE EMP.HIREDATE%TYPE;
    

    BEGIN

    SELECT SAL,ENAME,HIREDATE INTO V_SAL,V_ENAME,V_HIREDATE FROM EMP WHERE EMPNO = 7369;
    
    INSERT INTO EMP(EMPNO,ENAME) VALUES(1111,'LHR');
    
    UPDATE EMP SET SAL = 2000 WHERE EMPNO= 1010;
    
    DELETE EMP WHERE EMPNO = 1012;
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE(V_SAL || ',' || V_ENAME || ',' || V_HIREDATE);
    

    END;

    关于%ROWTYPE,需要了解以下内容:

    ① %ROWTYPE返回的是一个记录类型,其数据类型和数据库表的数据结构一致。

    ② 声明的变量对应于数据库表或视图中列的集合。

    ③ 在%ROWTYPE之前加上数据库表名。

    ④ 记录内字段名和数据类型与参照表或视图中的列相同。

    具体而言,%ROWTYPE有如下优点:

    ① 可以不必知道数据库中列的数量和类型。

    ② 在运行期间,数据库中列的数量和类型可能发生变化,但是却不用修改代码。

    ③ 在SELECT语句中使用该属性可以有效地检索表中的行。

    %ROWTYPE使用示例如下所示:

    DECLARE

    V_EMP EMP%ROWTYPE;
    

    BEGIN

    SELECT * INTO V_EMP FROM EMP WHERE EMPNO = 7521;
    
    DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO || ',' || V_EMP.ENAME);
    

    END;

    1.2 Oracle数据类型

    1.2.1 预定义数据类型

    Oracle的数据类型分为标量(Scalar)类型、复合(Composite)类型、引用(Reference)类型和LOB(Large Object)类型4种类型 。标量类型没有内部组件又分为四类:数字、字符、布尔和日期/时间;而复合类型包含了能够被单独操作的内部组件;引用类型类似于3G语言中的指针,能够引用一个值;LOB类型的值就是一个lob定位器,能够指示出大对象的存储位置。
    下图是在PL/SQL中可以使用的预定义类型。

    1.3 标量类型

    1.3.1 数字型

    数字类型可以存储整数、实数和浮点数,可以表示数值的大小,参与计算。
    1.3.1.1 BINARY_INTEGER
    我们可以使用BINARY_INTEGER数据类型来存储有符号整数。它的范围是-231至231。跟PLS_INTEGER一样,BINARY_INTEGER所需的存储空间也要小于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)。

    1.3.1.2 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
    使用DEC、DECIMAL和NUMBERIC可以声明最大精度为38位十进制数字的定点数字。而使用DOUBLE PRECISION和FLOAT可以声明最大精度为126位二进制数字的浮点数字,大约相当于38位十进制数字。或是使用REAL声明最大精度为63位二进制数字的浮点数字,大约相当于18位十进制数字。INTEGER、INT和SMALLINT可以声明最大精度为38位十进制数字的整数。

    1.3.1.3 PLS_INTEGER

    我们可以使用PLS_INTEGER数据类型来存储有符号整数。它的取值范围在-231至231之间。PLS_INTEGER所需的存储空间要比NUMBER少,运算的速度要高于NUMBER和BINARY_INTEGER。虽然PLS_INTEGER和BINARY_INTEGER的取值范围一样,但它们不完全兼容。PLS_INTEGER在运算时如果有溢出,则会有异常抛出,而BIANRY_INTEGER发生溢出时,如果结果是要赋给一个NUMBER类型的变量时,就不会有异常抛出。为了考虑兼容性,我们仍可以在旧的应用程序中使用BINARY_INTEGER;但在新的应用程序中,PLS_INTEGER会带来更好的性能。

    1.3.2 字符型

    字符类型可以存放字符和数字混合的数据,表现词和文章,操作字符串。

    1.3.2.1 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类型相兼容。

    1.3.2.2 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的长度是231字节;但是,不可以从LONG字段中检索超过32760字节的字符放到LONG类型变量中去。同样,对于LONG RAW类型来说,这条规则同样适用,在数据库中它的最大长度也是231字节,而变量的长度在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函数中,所以,这时我们就会得到一个错误消息。

    一、 oracle里long类型的总结

    LONG 数据类型中存储的是可变长字符串,最大长度限制是2GB。
    2、对于超出一定长度的文本,基本只能用LONG类型来存储,数据字典中很多对象的定义就是用LONG来存储的。
    1、LONG 数据类型中存储的是可变长字符串,最大长度限制是2GB。
    2、对于超出一定长度的文本,基本只能用LONG类型来存储,数据字典中很多对象的定义就是用LONG来存储的。
    3、LONG类型主要用于不需要作字符串搜索的长串数据,如果要进行字符搜索就要用varchar2类型。
    4、很多工具,包括SQL*Plus,处理LONG 数据类型都是很困难的。
    5、LONG 数据类型的使用中,要受限于磁盘的大小。

    能够操作 LONG 的 SQL 语句:
    1、Select语句
    2、Update语句中的SET语句
    3、Insert语句中的VALUES语句

    限制:

    1、一个表中只能包含一个 LONG 类型的列。
    2、不能索引LONG类型列。
    3、不能将含有LONG类型列的表作聚簇。
    4、不能在SQLPlus中将LONG类型列的数值插入到另一个表格中,如insert into …select。
    5、不能在SQL
    Plus中通过查询其他表的方式来创建LONG类型列,如create table as select。
    6、不能对LONG类型列加约束条件(NULL、NOT NULL、DEFAULT除外),如:关键字列(PRIMARY KEY)不能是 LONG 数据类型。
    7、LONG类型列不能用在Select的以下子句中:where、group by、order by,以及带有distinct的select语句中。
    8、LONG类型列不能用于分布查询。
    9、PL/SQL过程块的变量不能定义为LONG类型。
    10、LONG类型列不能被SQL函数所改变,如:substr、instr。

    因为long类型的数值不能用insert into … select的方法插入,故我们要分两步走,先插入其它字段,最后再插入long类型字段,这可以通过过程来实现.下面是一个我做实验实现的例子.

    SQL>; create table testlong (id number,name varchar2(12),history long);

    表已创建。

    SQL>; create table testlong1 (id number,name varchar2(12),history long);

    表已创建。

    SQL>; insert into testlong values(1,‘dwh’,‘work in foshan’);

    已创建 1 行。
    SQL>; insert into testlong values(2,‘sfg’,‘work in guangzhou’);

    已创建 1 行。

    SQL>; select * from testlong;

    ID NAME HISTORY


    1 dwh work in foshan
    2 sfg work in guangzhou

    SQL>; insert into testlong1 select * from testlong;
    insert into testlong1 select * from testlong
    *
    ERROR 位于第 1 行:
    ORA-00997: 非法使用 LONG 数据类型

    SQL>; Declare
    2 CURSOR bcur
    3 IS SELECT id,history from testlong;
    4 brec bcur%ROWTYPE;
    5 BEGIN
    6 insert into testlong1(id,name) select id,name from testlong;–其它类型
    先插入
    7 OPEN bcur;
    8 LOOP
    9 FETCH bcur INTO brec;
    10 EXIT WHEN bcur%NOTFOUND;
    11 update testlong1 set history=brec.history where id=brec.id;
    12 END LOOP;
    13 CLOSE bcur;
    14 END;
    15 /

    PL/SQL 过程已成功完成。

    SQL>; select * from testlong1;

    ID NAME HISTORY


    1 dwh work in foshan
    2 sfg work in guangzhou
    create table testlong (id number,name varchar2(12),history long);

    create table testlong1 (id number,name varchar2(12),history long);

    insert into testlong values(1,‘dwh’,‘work in foshan’);
    insert into testlong values(2,‘sfg’,‘work in guangzhou’);

    insert into testlong1 select * from testlong; —报错
    insert into testlong1 select t.id ,t.name,to_lob(t.history) from testlong t;

    二、 LONG转换为字符串
    ① 只能利用基础表来转换,或者如果是视图可以建立临时表来转换
    ② 利用to_lob函数来转换成lob,然后再转换为to_char

    long列是不允许出现在 create table xx as select * from yyy
    里的
    In addition, LONG columns cannot appear in these parts of SQL statements:

    SELECT lists of
    CREATE TABLE … AS SELECT statements


    oracle建议使用clob\blob\nclob来代替。
    如:
    create table t(a int,b long); insert into t values(1,‘1’);
    –可以这样创建,不过默认类型就成了clob
    create table t1 as select a,to_lob(b) from t;

    –也可以这样,先创建一个表,然后在插入数据时使用to_lob函数转换一下
    create table t2 (a int,b long); insert into t2 select a,to_lob(b) from t;

    1、 一次“ORA-00997: 非法使用 LONG 数据类型”的错误解决
    问题提出:
    当前用户下有一系列LIST类型的分区表,希望找出其中包含有DEFAULT分区的表以及对应的分区来。
    查询视图 USER_TAB_PARTITIONS,此视图对应每个分区有一条记录,包含的主要字段有
    I、 TABLE_NAME(表名)
    II、 PARTITION_NAME(分区名)
    III、 HIGH_VALUE(最大值(对应LIST分区的分区值))
    IV、 TABLESPACE_NAME(表空间)
    因此,要知道包含有DEFAULT分区表的分区,只要通过下面的语句就可以了。
    select * from user_tab_partitions WHERE high_value=‘DEFAULT’
    事实上,由于user_tab_partitions.high_value是LONG类型的数据,因而无法直接象VARCHAR2类型数据那样直接进行等值比对得到查询结果。所以上面的语句执行时会出现错误:

    ORA-00997: 非法使用 LONG 数据类型

    解决方法:

    1. 步骤一
      通过网上查找,得到这样一个函数,sys.dbms_metadata_util.long2varchar,这个sys.dbms_metadata_util包是不在Oracle文档中给出解释的。利用DESC,得到其函数的参数:

    FUNCTION LONG2VARCHAR RETURNS VARCHAR2
    参数名称 类型 输入/输出默认值?
    LENGTH NUMBER IN
    TAB VARCHAR2 IN
    COL VARCHAR2 IN
    ROW ROWID IN
    这样我们可以通过这个函数将LONG类型的数据转换为VARCHAR2类型的数据,从而得到最终的结果。
    执行查询
    SELECT *
    FROM user_tab_partitions
    WHERE UPPER(sys.dbms_metadata_util.long2varchar(1000, ‘USER_TAB_PARTITIONS’, ‘HIGH_VALUE’, ROWID)) = ‘DEFAULT’
    返回错误信息:
    第 1 行出现错误:
    ORA-01446: 无法使用 DISTINCT, GROUP BY 等子句从视图中选择 ROWID 或采样。
    由于USER_TAB_PARTITIONS不是基本表,因此不存在ROWID,看来sys.dbms_metadata_util.long2varchar2只对基本表有效。
    2. 步骤2
    下面我们就通过USER_TAB_PARTITIONS找到其基本表。
    连接到sys用户下,执行:

    SELECT owner, object_name, object_type
    FROM DBA_OBJECTS
    WHERE OBJECT_NAME=‘USER_TAB_PARTITIONS’

    得到结果如下:

    OWNER OBJECT_NAME OBJECT_TYPE
    SYS USER_TAB_PARTITIONS VIEW
    PUBLIC USER_TAB_PARTITIONS SYNONYM

    这里发现USER_TAB_PARTITIONS是一个视图,取得这个视图的脚本。

    SELECT DBMS_METADATA.GET_DDL(‘VIEW’,’ USER_TAB_PARTITIONS’)
    FROM DUAL;

    完整的查询结果这里不再给出,通过查询可以了解到USER_TAB_PARTITIONS的HIGH_VALUE对应tabpartv . H i b o u n d v a l , t a b p a r t v . Hiboundval,tabpartv .Hiboundvaltabpartv表其他的关键字段还有

    Obj# 分区对象的对象号,对应dba_objects.object_id
    Dataobj# 分区对象的数据对象号,对应dba_objects.data_object_id
    Bo# 分区表对应的对象号,对应dba_objects.object_id(记录对应为表)
    Part# 分区表号,对应user_tab_partitions. partition_position

    这样,我们只要执行下面的查询就可以了,
    SELECT *
    FROM tabpartv$
    WHERE sys.dbms_metadata_util.long2varchar(10, ‘SYS.TABPARTV$’, ‘HIBOUNDVAL’, ROWID) = ‘DEFAULT’

    根据得到的结果,我们利用user_objects查询对应的obj#,然后再查询USER_TAB_PARTITIONS就可以了。要指定特定的用户,比如SCOTT用户下的分区数据,还要关联obj 和 u s e r 和user user基础表。

    下面用一个SQL得到我们想要的结果,
    SELECT a.* FROM dba_tab_partitions a JOIN dba_objects b
    ON(a.table_owner=b.owner AND
    a.table_name=b.object_name and
    a.partition_name=b.subobject_name)
    WHERE b.object_id IN(
    SELECT a.obj#
    FROM tabpartv$ a join obj$ b ON(a.obj#=b.obj#)
    JOIN user$ c ON(b.owner#=c.user#)
    WHERE UPPER(sys.dbms_metadata_util.long2varchar(10, ‘SYS.TABPARTV ′ , ′ H I B O U N D V A L ′ , A . R O W I D ) ) = ′ D E F A U L T ′ A N D c . n a m e = ’ S C O T T ’ ) S E L E C T a . t a b l e o w n e r , a . t a b l e n a m e , a . p a r t i t i o n n a m e , a . p a r t i t i o n p o s i t i o n , s y s . d b m s m e t a d a t a u t i l . l o n g 2 v a r c h a r ( 10 , ′ S Y S . T A B P A R T V ', 'HIBOUNDVAL', A.ROWID)) = 'DEFAULT' AND c.name=’SCOTT’) SELECT a.table_owner, a.table_name, a.partition_name, a.partition_position, sys.dbms_metadata_util.long2varchar(10, 'SYS.TABPARTV ,HIBOUNDVAL,A.ROWID))=DEFAULTANDc.name=SCOTT)SELECTa.tableowner,a.tablename,a.partitionname,a.partitionposition,sys.dbmsmetadatautil.long2varchar(10,SYS.TABPARTV’,
    ‘HIBOUNDVAL’,
    c.ROWID) high_value
    FROM dba_tab_partitions a,
    dba_objects b,
    SYS.tabpartv$ c
    WHERE a.table_owner = b.owner
    AND a.table_name = b.object_name
    AND a.partition_name = b.subobject_name
    AND b.OBJECT_ID = c.obj#
    and a.table_owner not in (‘SYS’,‘SYSTEM’)
    ORDER BY a.table_owner,
    a.table_name,
    a.partition_position;

    SQL> alter table plan_table move;
    alter table plan_table move
    ORA-00997: 非法使用 LONG 数据类型
    SQL> desc plan_table
    Name Type Nullable Default Comments


    STATEMENT_ID VARCHAR2(30) Y
    PLAN_ID NUMBER Y

    PARTITION_STOP VARCHAR2(255) Y
    PARTITION_ID INTEGER Y
    OTHER LONG Y
    DISTRIBUTION VARCHAR2(30) Y

    QBLOCK_NAME VARCHAR2(30) Y
    查看表结构,表里含有类型为 long 的字段,是不能move
    那我们来把long改为clob
    SQL> alter table plan_table modify(other clob);
    Table altered
    SQL> alter table plan_table move;
    Table altered
    成功~
    QL> alter table plan_table modify(other long);
    alter table plan_table modify(other long)
    ORA-22859: 无效的列修改
    由此可见想要再把 字段类型改回 long 是不能的
    另外 plan_table move 以后会多2个对象出来
    SQL> /
    SEGMENT_NAME FILE_ID EXTENT_ID BLOCK_ID BYTES


    IND_T1_N1_V1 11 0 1081 65536
    FY_2004_2 11 3 1033 65536

    FY_2004_2 14 5 35489 65536
    FY_2006 15 0 38313 65536
    SYS_LOB0000030377C00026 150116165536 S Y S I L 0000030377 C 00026 15 0 1161 65536 SYS_IL0000030377C00026 150116165536SYSIL0000030377C00026 15 0 1169 65536
    FY_2006_IND 15 0 38369 65536
    SYS_C003157 15 0 38321 65536
    IND_T1_N1 15 0 38377 65536
    EMP_TEST 15 0 38361 65536
    PLAN_TABLE 15 0 1201 65536
    CHAINED_ROWS 15 0 1297 65536

    FY_2004_2 18 7 1169 65536
    已选择31行。
    其中SYS_LOB0000030377C00026 是 c l o b 类 型 字 段 的 内 容 , S Y S I L 0000030377 C 00026 是clob类型字段的内容,SYS_IL0000030377C00026 clobSYSIL0000030377C00026 是索引

    1.3.2.3 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变量中。
    1.3.2.4 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
    SQL> FROM emp
    SQL> WHERE empno = 7788;
    ROWID ENAME


    AAAAqcAABAAADFNAAH SCOTT

    OOOOOOFFFBBBBBBRRR这样的形式有四部分组成:
    1 “000000"代表数据对象号(data object number),如上例中的"AAAAqc”,能够辨识数据库段。同一段中的模式对象,都有着相同的数据对象号。
    2 “FFF"代表文件号(file number),如上例中的"AAB”,能辨识出包含行的数据文件。在数据库中,文件号是唯一的。
    3 “BBBBBB"代表块号(block number),如上例中的"AAADFN”,能辨识出包含行的数据块。块号是与它们所在的数据文件相关,而不是表空间。所以,两个在同一表空间的行数据,如果它们处于不同的数据文件中,也可能有着相同的块号。
    4 “RRR"代表了行号(row number),如上例中的"AAH”,可以辨识块中的行数据。
    逻辑ROWID为访问特定行提供了最快的访问速度。Oracle在索引组织表基础上使用它们构建二级索引。逻辑ROWID没有持久的物理地址,当新数据被插入时,它的值就会在数据块上偏移。但是,如果一个行数据的物理位置发生变化,它的逻辑ROWID就无效了。
    1.3.2.5 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限定修饰词,初始化参数NLS_LENGTH_SEMANTICS会决定默认的设置。当PL/SQL过程编译时,这个参数的设置就会被记录下来,这样,当过程失效后被重新编译时就会被重新使用。
    一、 VARCHAR2的子类型
    下面VARCHAR2的子类型的范围与VARCHAR2完全相同,它们只是VARCHAR2的一个别名而已。
    STRING
    VARCHAR
    我们可以使用这些子类型来与ANSI/ISO和IBM类型兼容。注意:目前,VARCHAR和VARCHAR2有着相同意义,但是在以后的PL/SQL版本中,为了符合SQL标准,VARCHAR有可能会作为一个单独的类型出现。所以最好使用VARCHAR2,而不是VARCHAR。
    3、本地字符型
    被广泛使用的单字节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。这个值总是代表字符的个数,不像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类型的值,就会引起数据丢失。这样的数据丢失会导致字符看起来像问号(?)。

    二、 Oracle数据库中char(),varchar2(),nvarchar2()三种数据类型的区别

    1.  char()类型: 
      

    (1)如果在数据库中定义的长度为10位,而我实际入力的数据长度不足10位,系统会在入力数据的后面用空字符串补足10位。
    (2)一个全角认作2位长度。
    2. varchar2()类型:
    (1) 不足数据库规定长度,不会补足长度。
    (2) 一个全角认作2位长度。
    3. nvarchar2()类型:
    (1) 不足数据库规定长度,不会补足长度。
    (2) 一个全角认作1位长度。
    结论:由此在做设计时,为节省系统资源会尽量选用varchar2()和nvarchar2()类型。
    三、 Oracle CHAR,VARCHAR,VARCHAR2,nvarchar类型的区别与使用
    一 varchar,nvarchar,
    四个类型都属于变长字符类型, varchar和varchar2的区别在与后者把所有字符都占两字节,前者只对汉字和全角等字符占两字节。 nvarchar和nvarchar2的区别和上面一样, 与上面区别在于是根据Unicode标准所进行的定义的类型,通常用于支持多国语言类似系统的定义。
    1.char
    char的长度是固定的,比如说,你定义了char(20),即使你你插入abc,不足二十个字节,数据库也会在abc后面自动加上17个空格,以补足二十个字节;
    char是区分中英文的,中文在char中占两个字节,而英文占一个,所以char(20)你只能存20个字母或10个汉字。
    char适用于长度比较固定的,一般不含中文的情况
    2.varchar/varchar2
    varchar是长度不固定的,比如说,你定义了varchar(20),当你插入abc,则在数据库中只占3个字节。
    varchar同样区分中英文,这点同char。
    varchar2基本上等同于varchar,它是oracle自己定义的一个非工业标准varchar,不同在于,varchar2用null代替varchar的空字符串
    varchar/varchar2适用于长度不固定的,一般不含中文的情况
    3.nvarchar/nvarchar2
    nvarchar和nvarchar2是长度不固定的
    nvarchar不区分中英文,比如说:你定义了nvarchar(20),你可以存入20个英文字母/汉字或中英文组合,这个20定义的是字符数而不是字节数
    nvarchar2基本上等同于nvarchar,不同在于nvarchar2中存的英文字母也占两个字节
    nvarchar/nvarchar2适用于存放中文
    char [ ( n ) ]
    固定长度,非 Unicode 字符数据,长度为 n 个字节。n 的取值范围为 1 至 8,000,存储大小是 n 个字节。
    varchar [ ( n | max ) ]
    可变长度,非 Unicode 字符数据。n 的取值范围为 1 至 8,000。max 指示最大存储大小是 2^31-1 个字节。存储大小是输入数据的实际长度加 2 个字节,用于反映存储的数据的长度。所输入数据的长度可以为 0 个字符。
    * 如果列数据项的大小一致,则使用 char。
    * 如果列数据项的大小差异相当大,则使用 varchar。
    * 如果列数据项大小相差很大,而且大小可能超过 8,000 字节,请使用 varchar(max)。
    如果未在数据定义或变量声明语句中char 或 varchar 数据类型指定 n,则默认长度为 1。如果在使用 CAST 和 CONVERT 函数时char 或 varchar 数据类型未指定 n,则默认长度为 30。
    当执行 CREATE TABLE 或 ALTER TABLE 时,如果 SET ANSI_PADDING 为 OFF,则定义为 NULL 的 char 列将作为 varchar 处理。
    另外帮助理解的,只供参考:转自http://www.51testing.com/?uid-258885-action-viewspace-itemid-141197
    也可参照学习http://ce.sysu.edu.cn/garden/dispbbs.asp?boardid=26&ID=8774&replyID=18180&skin=1
    1.NULL值(空值)。
    a. char列的NULL值占用存储空间。
    b. varcahr列的NULL值不占用存储空间。
    c. 插入同样数量的NULL值,varchar列的插入效率明显高出char列。
    2.插入数据
    无论插入数据涉及的列是否建立索引,char的效率都明显低于varchar。
    3. 更新数据
    如果更新的列上未建立索引,则char的效率低于varchar,差异不大;建立索引的话,效率较高。
    4. 修改结构
    a. 无论增加或是删除的列的类型是char还是varchar,操作都能较快的完成,而且效率上没有什么差异。
    b. 对于增加列的宽度而言,char与varchar有非常明显的效率差异,修改varcahr列基本上不花费时间,而修改char列需要花费很长的时间。
    5.数据检索
    无论是否通过索引,varchar类型的数据检索略优于char的扫描。
    选择char还是选择varchar的建议
    1.适宜于char的情况:
    a. 列中的各行数据长度基本一致,长度变化不超过50字节;
    b. 数据变更频繁,数据检索的需求较少。
    c. 列的长度不会变化,修改char类型列的宽度的代价比较大。
    d. 列中不会出现大量的NULL值。
    e. 列上不需要建立过多的索引,过多的索引对char列的数据变更影响较大。
    2.适宜于varchar的情况;
    a. 列中的各行数据的长度差异比较大。
    b. 列中数据的更新非常少,但查询非常频繁。
    c. 列中经常没有数据,为NULL值或为空值
    nchar [ ( n ) ]
    n 个字符的固定长度的 Unicode 字符数据。n 值必须在 1 到 4,000 之间(含)。存储大小为两倍 n 字节。
    nvarchar [ ( n | max ) ]
    可变长度 Unicode 字符数据。n 值在 1 到 4,000 之间(含)。max 指示最大存储大小为 2^31-1 字节。存储大小是所输入字符个数的两倍 + 2 个字节。所输入数据的长度可以为 0 个字符。
    注释
    如果没有在数据定义或变量声明语句中指定 n,则默认长度为 1。如果没有使用 CAST 函数指定 n,则默认长度为 30。
    如果列数据项的大小可能相同,请使用 nchar。
    如果列数据项的大小可能差异很大,请使用 nvarchar。
    sysname 是系统提供的用户定义数据类型,除了不可为空值外,在功能上与 nvarchar(128) 相同。sysname 用于引用数据库对象名。
    为使用 nchar 或 nvarchar 的对象分配的是默认的数据库排序规则,但可使用 COLLATE 子句分配特定的排序规则。
    SET ANSI_PADDING ON 永远适用于 nchar 和 nvarchar。SET ANSI_PADDING OFF 不适用于 nchar 或 nvarchar 数据类型。
    在Oracle中CHAR,NCHAR,VARCHAR,VARCHAR2,NVARCHAR2这五种类型的区别
    1.CHAR(size)和VARCHAR(size)的区别
    CHAR为定长的字段,最大长度为2K字节;
    VARCHAR为可变长的字段,最大长度为4K字节;
    2.CHAR(size)和NCHAR(size)的区别
    CHAR如果存放字母数字占1个字节,存放GBK编码的汉字存放2个字节,存放UTF-8编码的汉字占用3个字节;
    NCHAR根据所选字符集来定义存放字符的占用字节数,一般都为2个字节存放一个字符(不管字符或者汉字)
    3.VARCHAR(size)和VARCHAR2(size)的区别
    在现在的版本中,两者是没有区别的;最大长度为4K字节;推荐使用VARCHAR2;
    4.VARCHAR2(size)和NVARCHAR2(size)的区别
    最大长度为4K字节,区别同CHAR与NCHAR的区别;(如果数据库字符集长度是2,则NVARCHAR2最大为2K)
    5.共同特性
    当执行insert的时候,插入的值为’’,则转变成null,即insert … values(’’) <=> insert … values(null)
    搜索的条件须用where xx is null
    6.例子
    比如有一个性别字段,里面存放“男,女”的其中一个值,两种常用选择
    CHAR(2) 和 NCHAR(1)
    四、 NLS_LENGTH_SEMANTICS参数
    值设置 数据库字符集 长度
    BYTE ZHS16GBK 2
    AL32UTF8 3
    CHAR ZHS16GBK 1
    AL32UTF8

    ORACLE初始化参数:NLS_LENGTH_SEMANTICS
    初始化参数NLS_LENGTH_SEMANTICS用于指定CHAR列或VARCHAR2列的长度定义方式,默认值为BYTE。当设置该参数为BYTE时,表示定义CHAR列或VARCHAR2列采用字节长度方式;当设置该参数为CHAR时,表示定义CHAR列或VARCHAR2列采用字符个数方式。需要注意,设置该参数对于已存在列没有作用。
    该初始化参数是动态参数,可以使用ALTER SESSION或ALTER SYSTEM命令进行修改。示例如下:
    SQL>ALTER SESSION SET nls_length_semantics=char;
    SQL>CREATE TABLE t1(cola VACHAR2(4));
    SQL>INSERT INTO t1 VALUES(‘北京’);
    SQL>INSERT INTO t1 VALUES(‘BEIJING’);
    上例将报错ORA-12899。
    附甲骨文官方说明:
    属性 说明
    参数类型 String
    语法 NLS_LENGTH_SEMANTICS = string
    Example: NLS_LENGTH_SEMANTICS = ‘CHAR’
    默认值 BYTE
    允许动态修改 ALTER SESSION
    取值范围 BYTE | CHAR
    NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected.
    NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. You may be required to use byte semantics in order to maintain compatibility with existing applications.
    NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary always uses byte semantics.

    从9i开始,oracle提供了NLS_LENGTH_SEMANTICS这个参数,其有两个取值,CHAR和BYTE。当为CHAR时字符类型的长度是按字符个数来计算,而不是按BYTE来计算,这在使用变长字符集(AL32UTF8)的情况下非常有用,因为一个字符所占用的字节数是不定的,就给我们准确估计字段长度(BYTE)带来不便。同时当为CHAR时,对那些采用7/8bit的字符集(US7ASCII/WE8MSWIN1252)来说也不会带来空间上的浪费。
    下面就使用使用该参数需要注意的一些地方做出描述
    1、该参数分为三个级别,分别是数据库、实例、会话三个级别
    可以分别在NLS_DATABASE_PARAMETERS、NLS_INSTANCE_PARAMETERS、NLS_SESSION_PARAMETERS里查询到
    数据库级的值在创意数据库时被指定,实例级的值可以通过修改init.ora/Spfile来指定,会话级的可以使用alter session来指定
    2、实例/会话级的的参数只对其修改之后的对象(包括字段和pl/sql变量)产生作用,修改之前的维持不变。
    可以使用ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=spfile;来修改实例级的参数值
    注意,必须需要重启才会生效!(就算指定了scope=both)
    该参数取值决定顺序
    1、如果没有客户端(环境变量,后面会讲到)或者会话级指定该值,该参数由实例级的值决定(通过NLS_INSTANCE_PARAMETERS查询)
    2、从10g开始,NLS_LENGTH_SEMANTICS可以在环境变量或者注册表中设定,一旦设定之后会话级的值默认为环境变量或注册表中的取值
    (通过NLS_SESSION_PARAMETERS查询)
    3、可以使用ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR改变当前会话的取值。
    其它注意事项
    Oracle E-business Suite不支持该参数的CHAR语义
    在11g以下的数据库中,不要在创建数据库时指定 NLS_LENGTH_SEMANTICS=CHAR,而应该使用 NLS_LENGTH_SEMANTICS=BYTE(或者不指定)来创建数据库,然后在修改ini.ora/spfile里改参数的值。否则会导致数据创建期间部分XDB和SYS的对象使用不支持的CHAR语义,产生错误,不过11.1.0.6之后已经得到解决。详见 Bug 5545716 和 Bug 4886376
    数据库级取值为BYTE,实例级取值为CHAR 和 数据库级取值为CHAR,实例级取值为CHAR 是相同的,所以没必要纠结,详情可以参阅 Note 241047.1 The Priority of NLS Parameters Explained.
    你需要在BYTE语义下调用使用了STARTUP MIGRATE的脚本(比如patch脚本或者 $ORACLE_HOME/RDBMS/ADMIN 下的脚本(比如catalog.sql ))
    If you run patch scripts or scripts from $ORACLE_HOME/RDBMS/ADMIN like catalog.sql use STARTUP MIGRATE; and run then the scripts. ( run them with NLS_LENGTH_SEMANTICS=BYTE )

    • Oracle Text does NOT support instance wide NLS_LENGTH_SEMANTICS=CHAR If you are using Oracle Text then the database needs to be created and started with NLS_LENGTH_SEMANTICS=BYTE.
      Using CHAR semantics on column definitions itself is supported however.Bug 4465964
      This is not documented in the 10.2 docset, it is in the 11g doc: http://download.oracle.com/docs/cd/B28359_01/text.111/b28304/csql.htm#i997737 section “CHARSET COLUMN charset_column_name”
      9i以下的客户端不能识别CHAR语义,当你用8i客户端去连接9i UTF8数据库时,数据库的VARCHAR2(10 CHAR)会以BYTE语义显示为VARCHAR2(30)
      这样8i的的客户端才能插入不超过30byte的数据,鉴于此,当使用CHAR语义时,最好使用9i以上的客户端。
      可以使用以下方法限定客户端版本为9及以上:
      在10.1 中可以在init.ora中指定DB_ALLOWED_LOGON_VERSION=9
      10.2及以上,在sqlnet.ora中指定SQLNET.ALLOWED_LOGON_VERSION = 9
      注意:不要在10.2及以上使用DB_ALLOWED_LOGON_VERSION
      设置之后,如果用8i及以下的客户端访问数据库会报ORA-28040: No matching authentication protocol错误
      默认值
      当NLS_LENGTH_SEMANTICS取值为BYTE时,默认为BYTE;当取值为CHAR时,默认为CHAR。
      意思就是说,如果NLS_LENGTH_SEMANTICS=BYTE char(10)实际上就是 char(10 byte)
      如果NLS_LENGTH_SEMANTICS=CHAR char(10)实际上就是 char(10 CHAR)
      不管NLS_LENGTH_SEMANTICS取值为何,都可以在使用时显示的指定是按CHAR还是BYTE
      例如,实例NLS_LENGTH_SEMANTICS=BYTE,在创建表时可以指定char(10 char)就可以使用char语义了
      另外,对于单字节字符集编码来说,CHAR=BYTE
      NLS_LENGTH_SEMANTICS对于属于SYS的表(对SYSTEM有效)无效,如下:
      SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;
      Session altered.
      SQL> Create table sys.test1 (Col1 CHAR(20),Col2 VARCHAR2(100));
      Table created.
      SQL> Create table sys.test2 (Col1 CHAR(20 CHAR),Col2 VARCHAR2(100 CHAR));
      Table created.
      SQL> desc test1
      Name Null? Type

    COL1 CHAR(20 BYTE)
    COL2 VARCHAR2(100 BYTE)
    SQL> desc test2
    Name Null? Type


    COL1 CHAR(20)
    COL2 VARCHAR2(100)

    PL/SQL与NLS_LENGTH_SEMANTICS
    存储过程和包里的在创建或者重新编译的时候会读取当前SESSION的NLS_LENGTH_SEMANTICS值,并存入代码中。考虑下面的问题
    当前会话使用的是BYTE语义,你创建了一个存储过程,里面有一个变量的类型声明为CHAR(10),那么此时该变量实际为CHAR(10 BYTE),
    有一天你把BYTE改成了CHAR,然后去调用该存储过程,你会发现报 ORA-06502 PL/SQL Numeric or value error错误,因为虽然环境使用的是CHAR语义,
    但是存储过程里还是使用的BYTE语义,此时你需要重要编译该存储过程,它后重新读取当前SESSION的值,写入代码中。
    鉴于此,建议在书写存储过程的时候显示声明其语义(CHAR(10 CHAR)),以免不必要的麻烦。
    可以通过下在的SQL来查看现有存储过程使用的语义
    SELECT C.owner
    ||’.’
    || C.name
    ||’ - ’
    || C.type
    FROM DBA_PLSQL_OBJECT_SETTINGS C
    WHERE C.NLS_LENGTH_SEMANTICS = ‘CHAR’
    – to have a list of all using BYTE semantics use
    – WHERE C.NLS_LENGTH_SEMANTICS = ‘BYTE’
    – to check only for a certain users use
    – and C.owner IN (‘SCOTT’)
    ORDER BY C.owner,C.name
    NLS_LENGTH_SEMANTICS
    Property Description
    Parameter type String
    Syntax NLS_LENGTH_SEMANTICS = string
    Example: NLS_LENGTH_SEMANTICS = ‘CHAR’

    Default value BYTE
    Modifiable ALTER SESSION
    Range of values BYTE | CHAR

    The session-level value of NLS_LENGTH_SEMANTICS specifies the default length semantics to use for VARCHAR2 and CHAR table columns, user-defined object attributes, and PL/SQL variables in database objects created in the session. This default may be overridden by the explicit length semantics qualifiers BYTE and CHAR in column, attribute, and variable definitions.

    会话级别的nls_length_semantics说明默认的char和varchar2,用户定义的对象属性,和pl/sql变量在数据库对象创建的长度语法。默认值会被显示说明的参数值覆盖。

    The instance-level value of NLS_LENGTH_SEMANTICS provides a default for the session-level value if NLS_LENGTH_SEMANTICS it is not set explicitly by the database client through the NLS_LENGTH_SEMANTICS client environment variable (does not apply to JDBC Thin clients), or the ALTER SESSION SET NLS_LENGTH_SEMANTICS statement.

    实例级别的nls_length_semantics提供默认的会话级别的值,如果数据库客户端没有显示的设定。

    NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based.

    Sessions logged in as SYS do not use the NLS_LENGTH_SEMANTICS parameter. They use BYTE length semantics for all created objects unless overridden by the explicit BYTE and CHAR qualifiers in object definitions (SQL DDL statements).

    sys用户不使用nls_length_semantics参数,他们默认使用BYTE,除非在DDL列属性的时候显示的指定BYTE或CHAR.

    Caution:

    Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in runtime errors, including buffer overflows.
    oracle强烈建议你不要在实例或者服务参数文件中设置nls_length_semantics参数为CHAR。这是因为许多存在的安装脚本会在使用字符长度语义创建列的时候出现异常,导致运行失败,包括缓存溢出。

    Any DBA wanting to use NLS_LENGTH_SEMANTICS.

    NLS_LENGTH_SEMANTICS allows you to specify the length of a column datatype in terms of CHARacters rather than in terms of BYTEs. Typically this is when using an AL32UTF8 or other varying width NLS_CHARACTERSET database where one character is not always one byte. While using CHAR semantics has as such no added value in a 7/8 bit characterset it’s fully supported so any application code / table setup using CHAR can also be used in a 7/8bit characterset like US7ASCII/WE8MSWIN1252.
    This parameter is a 9i (and up) feature and is not available in older releases.

    nls_length_semantics允许你说明列类型的长度为CHAR而不是BYTES。典型的当使用AL32UTF8或者其他多字节字符类型。该参数在9i中出现。

    • NLS_LENGTH_SEMANTICS cannot be set as a client side environment/registry parameter in 9i, from 10g onwards NLS_LENGTH_SEMANTICS can be set (please define it in UPPERCASE). If NLS_LENGTH_SEMANTICS is set at client side then any session started from that client will use the value defined in the environment/registry and it can be checked in NLS_SESSION_PARAMETERS.
      9i客户端不能设置nls_length_semantics,从10g开始时可以的。如果在客户端设置了nls_length_semantics那么所有使用该客户端开始的会话将会使用定义在环境或者注册中,它将会检查nls_session_parameters中的设置。
    • If NLS_LENGTH_SEMANTICS is not set at client side or no alter session is done then the session will use the value found in NLS_INSTANCE_PARAMETERS.
      如果nls_length_semantics没有在客户端定义,那么会话将在nls_instance_parameters中查找。(pfile/spfile)
    • The NLS_LENGTH_SEMANTICS parameter can be set at instance level in the init.ora or Spfile. You will then see the parameter change in NLS_INSTANCE_PARAMETERS.

    更改nls_instance_parameters属性在init.ora或者spfile中。你可以使用alter system更改。 SQL>ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=spfile;

    Note :

    • Oracle advices to use explicit CHAR semantics in the SQL or PL/SQL syntax or to make sure your application does an alter session when connecting if CHAR semantics is required and the semantic is not defined explicit in SQL.
    • A lot of people think simply setting NLS_LENGTH_SEMANTICS=CHAR in the spfile/init.ora is the only thing to do to change an existing db to NLS_LENGTH_SEMANTICS=CHAR, this is not true.
    • Oracle recommends to NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance parameter file when possible.
      NLS_LENGTH_SEMANTICS parameter to CHAR in the init.ora/spfile.
    • use NLS_LENGTH_SEMANTICS=BYTE in init.ora/spfile and explicit define CHAR semantics in SQL or PL/SQL (= use VARCHAR2(10 CHAR) etc )
    • to have your application do an "ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR " when connecting if this application wants to use CHAR semantics .
    • use an explicit “ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR” in the beginning of schema creation scripts if the SQL does not use explicit CHAR semantics ( …VARCHAR2(10 CHAR) …etc ) but uses the session setting ( …VARCHAR2(10) …etc ) and the itention is to have them created using CHAR semantics.
      The whole idea is simply that many (third party / application install / even maybe some Oracle provided ) scripts still assume NLS_LENGTH_SEMANTICS=BYTE and by using NLS_LENGTH_SEMANTICS=BYTE as init.ora/spfile they will not be affected.This is not matter of “supported” it’s basically a matter of trying to avoid possible issues. There are currently no known issues with setting NLS_LENGTH_SEMANTICS=CHAR in the spfile/init.ora for Oracle RDBMS scripts for Oracle version 11.1.0.6 and higher.By explicit using CHAR semantics in SQL and PLS/SQL syntax or defining NLS_LENGTH_SEMANTICS=CHAR on session level each time when needed one also avoid problems who might arise from accidentally using a NLS_LENGTH_SEMANTICS=BYTE setting when CHAR is actually intended

    在Oracle数据库中和Oracle FORM中都 有这样一个参数的设置,有必要澄清一下:
    参数NLS_LENGTH_SEMANTICS的含义在Oracle文档中这样描述:
    Oracl文档中的说明: Syntax: NLS_LENGTH_SEMANTICS = string Range of values: BYTE | CHAR NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected. NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. You may be required to use byte semantics in order to maintain compatibility with existing applications. NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary always uses byte semantics.
    翻译过来的意思就是该参数有BYTE和CHAR两种取值,使你能够使用这两种语义创建CHAR或VARCHAR2类型的数据库表列,而对现有的列没有影响。
    二者的区别就是一个是按字符存放,另一个按字节存放;一般情况数据库都会使用UTF-8编码,一个汉字如果按字节存放,就需要3个字节。
    可以看到数据库中参数NLS_LENGTH_SEMANTICS的设置 ,一般默认为‘BYTE’。
    这是因为按字节存放,3个汉字就需要9个字节,如果把参数NLS_LENGTH_SEMANTICS设置为‘CHAR’,就可以将数据插入进去了。这是因为按数据库会按字符计数,而不是字节。
    Oracle Form中:
    ITEM属性设置 中也有Data Length Semantics这样一个属性设置,它有三种选择“NULL”、“CHAR”和“BYTE”,如果设置 为“NULL”则直接从数据库中获取,使用数据库的设置。设置为“CHAR”和“BYTE”则和上述数据库中的情况一样。经过测试发现,如果一个字符类型的ITEM的最大长度为100,则当Data Length Semantics设置为“CHAR”时,最多可以输入100个汉字或100个英文字符;当Data Length Semantics设置为“BYTE”时,最多可以输入33个汉字或100个英文字符。
    举例如下:
    SQL> show parameter nls_length
    NAME TYPE VALUE


    nls_length_semantics string BYTE
    SQL> create table byte_test (a varchar2(3));
    表已创建。
    SQL> insert into byte_test values(‘甲骨文’);
    insert into byte_test values(‘甲骨文’)
    *
    ERROR 位于第 1 行:
    ORA-01401: inserted value too large for column
    SQL> alter session set NLS_LENGTH_SEMANTICS=‘CHAR’;
    会话已更改。
    SQL> create table char_test (a varchar2(3));
    表已创建。
    SQL> insert into char_test values(‘甲骨文’);
    已创建 1 行。
    SQL> show parameter NLS_LENGTH_SEMANTICS
    NAME TYPE VALUE


    nls_length_semantics string CHAR
    SQL> desc byte_test;
    名称 是否为空? 类型


    A VARCHAR2(3 BYTE)
    SQL> desc char_test;
    名称 是否为空? 类型


    A VARCHAR2(3)

    NLS_LENGTH_SEMANTICS
    Property Description
    Parameter type String
    Syntax NLS_LENGTH_SEMANTICS = string
    Example: NLS_LENGTH_SEMANTICS = ‘CHAR’
    Default value BYTE
    Modifiable ALTER SESSION
    Range of values BYTE | CHAR

    The session-level value of NLS_LENGTH_SEMANTICS specifies the default length semantics to use forVARCHAR2 and CHAR table columns, user-defined object attributes, and PL/SQL variables in database objects created in the session. This default may be overridden by the explicit length semantics qualifiers BYTE and CHAR in column, attribute, and variable definitions.
    会话级别的nls_length_semantics说明默认的char和varchar2,用户定义的对象属性,和pl/sql变量在数据库对象创建的长度语法。默认值会被显示说明的参数值覆盖。
    The instance-level value of NLS_LENGTH_SEMANTICS provides a default for the session-level value ifNLS_LENGTH_SEMANTICS it is not set explicitly by the database client through theNLS_LENGTH_SEMANTICS client environment variable (does not apply to JDBC Thin clients), or theALTER SESSION SET NLS_LENGTH_SEMANTICS statement.
    实例级别的nls_length_semantics提供默认的会话级别的值,如果数据库客户端没有显示的设定。
    NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based.
    Sessions logged in as SYS do not use the NLS_LENGTH_SEMANTICS parameter. They use BYTE length semantics for all created objects unless overridden by the explicit BYTE and CHAR qualifiers in object definitions (SQL DDL statements).
    sys用户不使用nls_length_semantics参数,他们默认使用BYTE,除非在DDL列属性的时候显示的指定BYTE或CHAR.
    Caution:
    Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in runtime errors, including buffer overflows.
    oracle强烈建议你不要在实例或者服务参数文件中设置nls_length_semantics参数为CHAR。这是因为许多存在的安装脚本会在使用字符长度语义创建列的时候出现异常,导致运行失败,包括缓存溢出。

    Any DBA wanting to use NLS_LENGTH_SEMANTICS.

    NLS_LENGTH_SEMANTICS allows you to specify the length of a column datatype in terms of CHARacters rather than in terms of BYTEs. Typically this is when using an AL32UTF8 or other varying width NLS_CHARACTERSET database where one character is not always one byte. While using CHAR semantics has as such no added value in a 7/8 bit characterset it’s fully supported so any application code / table setup using CHAR can also be used in a 7/8bit characterset like US7ASCII/WE8MSWIN1252.
    This parameter is a 9i (and up) feature and is not available in older releases.

    nls_length_semantics允许你说明列类型的长度为CHAR而不是BYTES。典型的当使用AL32UTF8或者其他多字节字符类型。该参数在9i中出现。

    • NLS_LENGTH_SEMANTICS cannot be set as a client side environment/registry parameter in 9i, from 10g onwards NLS_LENGTH_SEMANTICS can be set (please define it in UPPERCASE). If NLS_LENGTH_SEMANTICS is set at client side then any session started from that client will use the value defined in the environment/registry and it can be checked in NLS_SESSION_PARAMETERS.
      9i客户端不能设置nls_length_semantics,从10g开始时可以的。如果在客户端设置了nls_length_semantics那么所有使用该客户端开始的会话将会使用定义在环境或者注册中,它将会检查nls_session_parameters中的设置。
    • If NLS_LENGTH_SEMANTICS is not set at client side or no alter session is done then the session will use the value found in NLS_INSTANCE_PARAMETERS.
      如果nls_length_semantics没有在客户端定义,那么会话将在nls_instance_parameters中查找。(pfile/spfile)
    • The NLS_LENGTH_SEMANTICS parameter can be set at instance level in the init.ora or Spfile. You will then see the parameter change in NLS_INSTANCE_PARAMETERS.
      更改nls_instance_parameters属性在init.ora或者spfile中。你可以使用alter system更改。
      SQL>ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=spfile;
      Note :
    • Oracle advices to use explicit CHAR semantics in the SQL or PL/SQL syntax or to make sure your application does an alter session when connecting if CHAR semantics is required and the semantic is not defined explicit in SQL.
    • A lot of people think simply setting NLS_LENGTH_SEMANTICS=CHAR in the spfile/init.ora is the only thing to do to change an existing db to NLS_LENGTH_SEMANTICS=CHAR, this is not true.
    • Oracle recommends to NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance parameter file when possible.
      NLS_LENGTH_SEMANTICS parameter to CHAR in the init.ora/spfile.
    • use NLS_LENGTH_SEMANTICS=BYTE in init.ora/spfile and explicit define CHAR semantics in SQL or PL/SQL (= use VARCHAR2(10 CHAR) etc )
    • to have your application do an "ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR " when connecting if this application wants to use CHAR semantics .
    • use an explicit “ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR” in the beginning of schema creation scripts if the SQL does not use explicit CHAR semantics ( …VARCHAR2(10 CHAR) …etc ) but uses the session setting ( …VARCHAR2(10) …etc ) and the itention is to have them created using CHAR semantics.
      The whole idea is simply that many (third party / application install / even maybe some Oracle provided ) scripts still assume NLS_LENGTH_SEMANTICS=BYTE and by using NLS_LENGTH_SEMANTICS=BYTE as init.ora/spfile they will not be affected.This is not matter of “supported” it’s basically a matter of trying to avoid possible issues. There are currently no known issues with setting NLS_LENGTH_SEMANTICS=CHAR in the spfile/init.ora for Oracle RDBMS scripts for Oracle version 11.1.0.6 and higher.By explicit using CHAR semantics in SQL and PLS/SQL syntax or defining NLS_LENGTH_SEMANTICS=CHAR on session level each time when needed one also avoid problems who might arise from accidentally using a NLS_LENGTH_SEMANTICS=BYTE setting when CHAR is actually intended.

    1.3.2.6 布尔类型(BOOLEAN)
    布尔类型能存储逻辑值TRUE、FALSE和NULL(NULL代表缺失、未知或不可用的值)。只有逻辑操作符才允许应用在布尔变量上。
    数据库SQL类型并不支持布尔类型,只有PL/SQL才支持。所以就不能往数据库中插入或从数据库中检索出布尔类型的值。
    1.3.2.7 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 T I M E Z O N E N A M E S 不 可 用 T I M E Z O N E A B B R 查 看 视 图 V TIMEZONE_NAMES 不可用 TIMEZONE_ABBR 查看视图V TIMEZONENAMESTIMEZONEABBRVTIMEZONE_NAMES 不可用
    除了TIMESTAMP WITH LOCAL TIMEZONE以外,剩下的都是SQL92所支持的。
    ? DATE
    DATE数据类型能够存储定长的日期时间。日期部分默认为当月的第一天;时间部分为午夜时间。函数SYSDATE能够返回当前的日期和时间。
    提示:如果只进行日期的等值比较,忽略时间部分,可以使用函数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 emp
    WHERE empno = 7499;
    在算术表达式中,PL/SQL会将整数文字当作"日"来处理,如"SYSDATE + 1"就代表明天的时间。
    一、 TIMESTAMP

    ? 它是精度更高的时间期数据,使用示例:
    ? Timestamp :缺省时小数秒精度为6
    ? TIMESTAMP(n) :指定小数秒精度为7
    ? Timestamp WITH TIME Zone
    ? Timestamp WITH LOCAL Time Zone

    我们看看下面的试验:
    Create Table ts_test
    (dt Date,
    ts Timestamp,
    ts1 TIMESTAMP(7),
    ts2 Timestamp WITH TIME Zone,
    ts3 Timestamp WITH LOCAL Time Zone);

    Insert Into Ts_Test
    Select Sysdate, Sysdate, Sysdate, Sysdate, Sysdate From Dual;

    Select *  From Ts_Test 
    

    DT TS TS1 TS2 TS3
    2008-9-7
    17:29
    07-9月 -08
    05.29.50.000000
    下午
    07-9月 -08
    05.29.50.0000000
    下午
    07-9月 -08
    05.29.50.000000
    下午 +08:00
    07-9月 -08
    05.29.50.000000
    下午
    在上面的例子中,我们创建了一个表TS_TEST,其中字段date的数据类型是date,ts 的
    数据类型为Timestamp,ts1 的数据类型为Timestamp(7),ts2 的数据类型为Timestamp WITH
    TIME ZONE。
    精度7指示小数秒的精度,如果不指定,小数秒的默认精度是6。

    TIMESTAMP WITH TIME ZONE是TIMESTAMP的一个变量,它对TIMESTAMP值进行
    一个时区转换,在本地时间和UTC 之间,小时和分钟的时区转换是不同的。
    UTC代表协调世界时—以前的格林尼治标准时间。如果两个TIMESTAMP WITH TIME
    ZONE在UTC中代表同一时刻,它们的值被认为是相同的,而不管存储在数据中的TIME
    ZONE偏移。
    因为TIMESTAMP WITH TIME ZONE也可以存储时区信息,它特别适合记录那些必须
    组合或协调地理区域的日期信息。
    例如,
    TIMESTAMP ‘1999-04-15 8:00:00 -8:00’

    TIMESTAMP ‘1999-04-15 11:00:00 -5:00’
    是相同的。
    美国西部标准时间 8:00 a.m. 和东部标准时间 11:00 a.m. 是相同的。
    该时间也可以被指定为:
    TIMESTAMP ‘1999-04-15 8:00:00 US/Pacific’
    注:小数秒精度指定SECOND日期时间字段的小数部分数字的数目,其范围是0到9,默
    认是6。

    TIMESTAMP WITH LOCAL TIME数据类型,TIMESTAMP WITH LOCAL TIME
    ZONE 是TIMESTAMP的另一个变量,它对TIMESTAMP值进行一个时区转换,存储在数据
    库中的数据被格式化为数据库时区,时区的转换不被作为列数据的一部分存储;Oracle 以
    本地会话时区返回数据,TIMESTAMP WITH LOCAL TIME ZONE数据类型被如下指定:
    TIMESTAMP[(fractional_seconds_precision)]WITH LOCAL TIME ZONE。
    不像TIMESTAMP WITH TIME ZONE,你可以指定TIMESTAMP WITH LOCAL TIME
    ZONE类型作为一个主键或唯一键的一部分。在本地时间和UTC之间的时区转换 (小时或分

    钟) 是不同的,对于TIMESTAMP WITH LOCAL TIME ZONE是非文字的。
    注:小数秒精度指定SECOND日期时间字段的小数部分数字的数目,其范围是0到9,默
    认是6。
    例如:
    CREATE TABLE time_example
    (order_date TIMESTAMP WITH LOCAL TIME ZONE);
    INSERT INTO time_example VALUES(‘15-NOV-00 09:34:28 AM’);
    SELECT *
    FROM time_example;
    order_date

    15-NOV-00 09.34.28.000000 AM
    TIMESTAMP WITH LOCAL TIME ZONE类型适合于两层应用程序,在其中你可以用客
    户系统的时区显示日期和时间。

    TIMESTAMP是对DATE的扩展,包含了年月日时分秒,语法如下:
    TIMESTAMP[(precision)]
    precision是可选参数,用于指定秒的小数部分数字个数。参数precision不可以是常量或变量,其有效范围是0到9,默认值是6。默认的时间戳(timestamp)格式是由Oracle初始化参NLS_TIMESTAMP_FORMAT决定的。
    在下面的例子中,我们声明了一个TIMESTAMP类型的变量,然后为它赋值:
    DECLARE
    checkout TIMESTAMP ( 3 );
    BEGIN
    checkout := ‘1999-06-22 07:48:53.275’;

    END;
    这个例子中,秒的小数部分是0.275。

    select systimestamp from dual;

    1、 比较
    oracle中TIMESTAMP与DATE比较
    oracle数据库中timestamp数据类型精度

    DATE数据类型
      这个数据类型我们实在是太熟悉了,当我们需要表示日期和时间的话都会想到date类型。它可以存储月,年,日,世纪,时,分和秒。它典型地用来表示什么时候事情已经发生或将要发生。DATE数据类型的问题在于它表示两个事件发生时间间隔的度量粒度是秒。这个问题将在文章稍后讨论timestamp的时候被解决。可以使用TO_CHAR函数把DATE数据进行传统地包装,达到表示成多种格式的目的。

    我见到的大多数人陷入的麻烦就是计算两个时间的间隔年数、月数、天数、小时数和秒数。你需要明白的是,当你进行两个日期的相减运算的时候,得到的是天数。你需要乘上每天的秒数(1天=86400秒),然后,你可以再次计算得到你想要的间隔数。下面就是我的解决方法,可以精确计算出两个时间的间隔。我明白这个例子可以更简短些,但是我是为了显示所有的数字来强调计算方式。

    SELECT TO_CHAR(date1,‘MMDDYYYY:HH24:MI:SS’) date1,
    TO_CHAR(date2,‘MMDDYYYY:HH24:MI:SS’) date2,  
    trunc(86400*(date2-date1))-60*(trunc((86400*(date2-date1))/60)) seconds,  
    trunc((86400*(date2-date1))/60)-60*(trunc(((86400*(date2-date1))/60)/60)) minutes,  
    trunc(((86400*(date2-date1))/60)/60)-24*(trunc((((86400*(date2-date1))/60)/60)/24)) hours,  
    trunc((((86400*(date2-date1))/60)/60)/24) days,  
    trunc(((((86400*(date2-date1))/60)/60)/24)/7) weeks  
    FROM date_table

    DATE1  DATE2  SECONDS  MINUTES  HOURS   DAYS  WEEKS
        ----------------- ----------------- ---------- ---------- ---------- ---------- ----------
    06202003:16:55:14 07082003:11:22:57   43  27  18   17  2
    06262003:11:16:36 07082003:11:22:57   21   6  0    12  1
     
    TIMESTAMP 数据类型
      DATE数据类型的主要问题是它粒度不能足够区别出两个事件哪个先发生。ORACLE已经在DATE数据类型上扩展出来了TIMESTAMP数据类型,它包括了所有DATE数据类型的年月日时分秒的信息,而且包括了小数秒的信息。如果你想把DATE类型转换成TIMESTAMP类型,就使用CAST函数。

    正如你看到的,在转换后的时间段尾部有了一段“.000000”。这是因为从date转换过来的时候,没有小数秒的信息,缺省为0。而且显示格式是按照参数NLS_TIMESTAMP_FORMAT定的缺省格式显示。当你把一个表中date类型字段的数据移到另一个表的timestamp类型字段中去的时候,可以直接写INSERT SELECT语句,oracle会自动为你做转换的。 
    TIMESTAMP数据的格式化显示和DATE 数据一样。注意,to_char函数支持date和timestamp,但是trunc却不支持TIMESTAMP数据类型。这已经清楚表明了在当两个时间的差别极度重要的情况下,使用TIMESTAMP数据类型要比DATE数据类型更确切  
      如果你想显示TIMESTAMP的小数秒信息,参考下面:

    在上例中,我只现实了小数点后3位的内容。  
      计算timestamp间的数据差别要比老的date数据类型更容易。当你直接相减的话,看看会发生什么。结果将更容易理解,第一行的17天,18小时,27分钟和43秒。
    SELECT time1,  time2,  
    substr((time2-time1),instr((time2-time1),’ ‘)+7,2)   seconds,  
    substr((time2-time1),instr((time2-time1),’ ‘)+4,2)   minutes,  
    substr((time2-time1),instr((time2-time1),’ ‘)+1,2)   hours,  
    trunc(to_number(substr((time2-time1),1,instr(time2-time1,’ ‘))))  days,  
    trunc(to_number(substr((time2-time1),1,instr(time2-time1,’ ')))/7) weeks
    FROM date_table

    TIME1  TIME2   SECONDS MINUTES HOURS DAYS WEEKS
      -------------------------  -------------------------- ------- ------- ----- ---- -----
    06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 43   27   18  17  2
    06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 21   06   00  12  1
      这就意味着不再需要关心一天有多少秒在麻烦的计算中。因此,得到天数、月数、天数、时数、分钟数和秒数就成为用substr函数摘取出数字的事情了
      
    系统日期和时间
    为了得到系统时间,返回成date数据类型。你可以使用sysdate函数。

    为了得到系统时间,返回成timestamp数据类型。你可以使用systimestamp函数。

    你可以设置初始化参数FIXED_DATE指定sysdate函数返回一个固定值。这用在测试日期和时间敏感的代码。注意,这个参数对于systimestamp函数无效。
      SQL> ALTER SYSTEM SET fixed_date = ‘2003-01-01-10:00:00’;
      System altered.
      SQL> select sysdate from dual;
      SYSDATE
      ---------
      01-JAN-03
      SQL> select systimestamp from dual;
      SYSTIMESTAMP
      ---------------------------------------------------------
      09-JUL-03 11.05.02.519000 AM -06:00
    当使用date和timestamp类型的时候,选择是很清楚的。你可以随意处置date和timestamp类型。当你试图转换到更强大的timestamp的时候,需要注意,它们既有类似的地方,更有不同的地方,而足以造成破坏。两者在简洁和间隔尺寸方面各有优势,请合理选择。另外,date类型一般很少用,建议大家在产品里面所有的date数据类型全部改为timestamp。

    2、 cast函数
    cast 是进行类型转换的, 可以针对各种Oracle数据类型. 修改的是用户的数据类型,可以将date类型转换为timestamp类型的数据。
    select cast(sysdate as timestamp) from dual;

    select cast(‘321312’ as number(8,2)) from dual ;

    select UTL_RAW.CAST_TO_RAW(‘123’) from dual;

    二、 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类型的变量,然后为其赋值:
    DECLARE
    LOGOFF TIMESTAMP ( 3 ) WITH TIME ZONE;
    BEGIN
    LOGOFF := ‘1999-10-31 09:42:37.114 +02:00’;

    END;
    例子中时区位移是+02:00。我们还可以使用符号名称(symbolic name)来指定时区位移,名称可以是完整形式也可以是缩写形式,如"US/Pacific"和"PDT",或是组合的形式。例如,下面的文字全都表现同一时间。第三种形式最可靠,因为它指定了切换到日光节约时间时的规则。
    TIMESTAMP ‘1999-04-15 8:00:00 -8:00’
    TIMESTAMP ‘1999-04-15 8:00:00 US/Pacific’
    TIMESTAMP ‘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:00’
    ‘1999-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的例子:
    DECLARE
    LOGOFF TIMESTAMP ( 3 ) WITH LOCAL TIME ZONE;
    BEGIN
    NULL;

    END;
    我们不可以用文字值为这种类型的变量赋值。

    三、 INTERVAL
    1、 INTERVAL YEAR TO MONTH

    ? 使用语法如下:
    INTERVAL YEAR [(year_precision)] TO MONTH
    ? 主要用于求日期n年、m月后的日期是那一天
    ? 是普通Data日期型的补充
    INTERVAL YEAR TO MONTH用年和月日期时间字段存储一段时间。
    用INTERVAL YEAR TO MONTH表示两个日期时间值的差,该差值只有年和月的部分。
    例如,你可能用该值设置一个往后120个月的提醒日期,或检查是否从某个特定的日期后6
    月已过去。
    指定 INTERVAL YEAR TO MONTH 如下语法:
    INTERVAL YEAR [(year_precision)] TO MONTH
    year_precision 是在YEAR日期时间字段中数字的数目,年精度的默认值是2。
    例如:
    CREATE TABLE te1
    (loan_duration INTERVAL YEAR (3) TO MONTH);

    INSERT INTO te1 (loan_duration)
    VALUES (INTERVAL ‘120’ MONTH(3));

    SELECT TO_CHAR( sysdate+loan_duration, ‘dd-mon-yyyy’)
    FROM te1;

    TO_CHAR(SYSDATE+LOAN_DURATION,
    07-9月 -2018

    前面的部分要大于后面的部分,例如:INTERVAL ‘0-1’ MONTH TO YEAR 是无效
    的,必须写成:INTERVAL ‘0-1’ YEAR TO MONTH。

    我们可以使用INTERVAL YEAR TO MONTH类型用来保存年月的间隔,语法如下:
    INTERVAL YEAR[(precision)] TO MONTH
    precision指定间隔的年数。参数precision不能是常量或变量,其范围在1到4之间,默认值是2,下面的例子中声明了一个INTERVAL YEAR TO MONTH类型的变量,并把间隔值101年3个月赋给它:
    DECLARE
    lifetime INTERVAL YEAR(3)TO MONTH;
    BEGIN
    lifetime := INTERVAL ‘101-3’ YEAR TO MONTH; – interval literal
    lifetime := ‘101-3’; – implicit conversion from character type
    lifetime := INTERVAL ‘101’ YEAR; – Can specify just the years
    lifetime := INTERVAL ‘3’ MONTH; – Can specify just the months

    END;
    2、 INTERVAL DAY TO SECOND

    INTERVAL DAY TO SECOND
    ? 使用语法:
    INTERVAL DAY [(day_precision)]
    TO SECOND [(fractional_seconds_precision)]
    ? 主要用于求日期n天、m小时后的准确时间
    ? 间隔型数据无法直接查看,必须和一个日期进行运行后才有效。

    INTERVAL DAY TO SECOND根据天、小时、分和秒存储一段时间。用INTERVAL DAY
    TO SECOND来表示两个日期时间值精确的差。例如,你可能用该值设置一个往后36个小时
    的提醒,或记录一个赛跑的开始和结束之间的时间。为了表示很长的时间跨度,包括很多年,
    用很高的精度,你可以用一个很大的值表示天的一部分。
    指定 INTERVAL DAY TO SECOND 如下:
    INTERVAL DAY [(day_precision)]
    TO SECOND [(fractional_seconds_precision)]
    day_precision 是在DAY日期时间字段中数字的数目,可接受的值的范围是0到9,默认是2。
    fractional_seconds_precision 是在SECOND日期时间字段中数字的数目,可接受的值的范围是0到9,默认是6。
    例如:
    CREATE TABLE te2
    (day_duration INTERVAL DAY (3) TO SECOND);
    INSERT INTO te2 (day_duration)
    VALUES (INTERVAL ‘180’ DAY(3));

    SELECT sysdate + day_duration “Half Year”
    FROM te2;

    Half Year
    2009-3-6 17:38

    我们可以使用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类型的变量:

    DECLARE
    lag_time INTERVAL DAY(3)TO SECOND(3);
    BEGIN
    IF lag_time > INTERVAL ‘6’ DAY THEN …

    END;

    Oracle语法:
    INTERVAL ‘{ integer | integer time_expr | time_expr }’
    { { DAY | HOUR | MINUTE } [ ( leading_precision ) ]
    | SECOND [ ( leading_precision [, fractional_seconds_precision ] ) ] }
    [ TO { DAY | HOUR | MINUTE | SECOND [ (fractional_seconds_precision) ] } ]

    leading_precision值的范围是0到9, 默认是2. time_expr的格式为:HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n], n表示微秒.
    该类型与INTERVAL YEAR TO MONTH有很多相似的地方,建议先看INTERVAL YEAR TO MONTH再看该文.

    范围值:
    HOUR: 0 to 23
    MINUTE: 0 to 59
    SECOND: 0 to 59.999999999

    eg:
    INTERVAL ‘4 5:12:10.222’ DAY TO SECOND(3)
    表示: 4天5小时12分10.222秒

    INTERVAL ‘4 5:12’ DAY TO MINUTE
    表示: 4天5小时12分

    INTERVAL ‘400 5’ DAY(3) TO HOUR
    表示: 400天5小时, 400为3为精度,所以"DAY(3)", 注意默认值为2.

    INTERVAL ‘400’ DAY(3)
    表示: 400天

    INTERVAL ‘11:12:10.2222222’ HOUR TO SECOND(7)
    表示: 11小时12分10.2222222秒

    INTERVAL ‘11:20’ HOUR TO MINUTE
    表示: 11小时20分

    INTERVAL ‘10’ HOUR
    表示: 10小时

    INTERVAL ‘10:22’ MINUTE TO SECOND
    表示: 10分22秒

    INTERVAL ‘10’ MINUTE
    表示: 10分

    INTERVAL ‘4’ DAY
    表示: 4天

    INTERVAL ‘25’ HOUR
    表示: 25小时

    INTERVAL ‘40’ MINUTE
    表示: 40分

    INTERVAL ‘120’ HOUR(3)
    表示: 120小时

    INTERVAL ‘30.12345’ SECOND(2,4)
    表示: 30.1235秒, 因为该地方秒的后面精度设置为4, 要进行四舍五入.

    INTERVAL ‘20’ DAY - INTERVAL ‘240’ HOUR = INTERVAL ‘10-0’ DAY TO SECOND
    表示: 20天 - 240小时 = 10天0秒

    INTERVAL DAY TO SECOND类型存储两个TIMESTAMP之间的时间差异,用日期、小时、分钟、秒钟形式表示。该数据类型的内部代码是183,长度位11字节:

    l 4个字节表示天数(增加0X80000000偏移量)
    l 小时、分钟、秒钟各用一个字节表示(增加60偏移量)
    l 4个字节表示秒钟的小时差异(增加0X80000000偏移量)

    以下是一个例子:

    SQL> alter table testTimeStamp add f interval day to second ;

    表已更改。

    SQL> update testTimeStamp set f=(select interval ‘5’ day + interval ‘10’ second from dual);

    已更新3行。

    SQL> commit;

    提交完成。

    SQL> select dump(f,16) from testTimeStamp;

    DUMP(F,16)


    Typ=183 Len=11: 80,0,0,5,3c,3c,46,80,0,0,0
    Typ=183 Len=11: 80,0,0,5,3c,3c,46,80,0,0,0
    Typ=183 Len=11: 80,0,0,5,3c,3c,46,80,0,0,0

    日期:0X80000005-0X80000000=5

    小时:60-60=0
    分钟:60-60=0
    秒钟:70-60=10
    秒钟小数部分:0X80000000-0X80000000=0

    INTERVAL DAY TO SECOND类型可以用来存储单位为天和秒的时间间隔。下面这条语句创建一个名为promotions的表,用来存储促销信息。promotions表包含了一个INTERVAL DAY TO SECOND类型的列duration,该列用来记录促销有效的时间间隔:
    CREATE TABLE promotions (
    promotion_id INTEGER CONSTRAINT promotions_pk PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    duration INTERVAL DAY(3) TO SECOND (4)
    );
    注意此处指定了duration列中天的精度为3,秒的小数部分精度为4。这就是说可以为该列的天存储3位数字,而为该列的秒最多可以在小数点右边存储4位数字。
    要向数据库提供一个INTERVAL DAY TO SECOND字面值,可以使用下面的简化语法:
    INTERVAL ‘[+|-][ d] [ h[: m[: s]]]’ [DAY[( days_precision)]])
    [TO HOUR | MINUTE | SECOND[( seconds_precision)]]
    其中
    ● + 或 - 是一个可选的指示符,用来说明时间间隔是正数还是负数(默认为正数)。
    ● d 是时间间隔的天数。
    ● h 是一个可选参数,表示时间间隔的小时数。如果指定了天和小时,必须在INTERVAL子句中包含TO HOUR。
    ● h 是一个可选参数,表示时间间隔的分钟数。如果指定了天和分,必须在INTERVAL子句中包含TO MINUTES。
    ● s 是一个可选参数,表示时间间隔的秒数。如果指定了天和秒,必须在INTERVAL子句中包含TO SECOND。
    ● days_precision是一个可选参数,用来说明天数的精度(默认值为2)。
    ● seconds_precision是一个可选参数,用来说明秒的精度(默认值为6)。
    表5-12给出了几个INTERVAL DAY TO SECOND类型的时间间隔字面量的例子。
    表5-12 时间间隔字面量的例子
    时间间隔字面量 说明
    INTERVAL ‘3’ DAY 时间间隔为3天
    INTERVAL ‘2’ HOUR 时间间隔为2小时
    INTERVAL ‘25’ MINUTE 时间间隔为25分钟
    INTERVAL ‘45’ SECOND 时间间隔为45秒
    INTERVAL ‘3 2’ DAY TO HOUR 时间间隔为3天零2小时
    INTERVAL ‘3 2:25’ DAY TO MINUTE 时间间隔为3天零2小时25分
    INTERVAL ‘3 2:25:45’ DAY TO SECOND 时间间隔为3天零2小时25分45秒
    INTERVAL ‘123 2:25:45.12’ DAY(3)
    TO SECOND(2) 时间间隔为123天零2小时25分45.12秒; 天的精度是3位数字,秒的小数部分的精度是2位数字
    INTERVAL ‘3 2:00:45’ DAY TO SECOND 时间间隔为3天2小时0分45秒
    INTERVAL ‘-3 2:25:45’ DAY TO SECOND 时间间隔为负数,值为3天零2小时25分45秒
    INTERVAL ‘1234 2:25:45’ DAY(3)
    TO SECOND 时间间隔无效,因为天的位数超过了指定的精度3
    INTERVAL ‘123 2:25:45.123’ DAY
    TO SECOND(2) 时间间隔无效,因为秒的小数部分的位数超过了指定的精度2
    SELECT INTERVAL ‘300’ MONTH,
    INTERVAL ‘3’ MONTH,
    INTERVAL ‘0-1’ YEAR TO MONTH,
    INTERVAL ‘54-2’ YEAR TO MONTH,
    INTERVAL ‘11:12:10.1234567’ HOUR TO SECOND,
    INTERVAL ‘6’ DAY,
    INTERVAL ‘120’ HOUR(3),
    INTERVAL ‘123 2 :25 :45.123’ DAY(3) TO SECOND(3)
    FROM dual;

    下面这个INSERT语句向promotions表添加一行记录:
    INSERT INTO promotions (promotion_id, name, duration)
    VALUES (1, ‘10% off Z Files’, INTERVAL ‘3’ DAY);
    INSERT INTO promotions (promotion_id, name, duration)
    VALUES (2, ‘20% off Pop 3’, INTERVAL ‘2’ HOUR);
    INSERT INTO promotions (promotion_id, name, duration)
    VALUES (3, ‘30% off Modern Science’, INTERVAL ‘25’ MINUTE);
    INSERT INTO promotions (promotion_id, name, duration)
    VALUES (4, ‘20% off Tank War’, INTERVAL ‘45’ SECOND);
    INSERT INTO promotions (promotion_id, name, duration)
    VALUES (5, ‘10% off Chemistry’, INTERVAL ‘3 2:25’ DAY TO MINUTE);
    INSERT INTO promotions (promotion_id, name, duration)
    VALUES (6, ‘20% off Creative Yell’, INTERVAL ‘3 2:25:45’ DAY TO SECOND);
    INSERT INTO promotions (promotion_id, name, duration)
    VALUES (7, ‘15% off My Front Line’,
    INTERVAL ‘123 2:25:45.12’ DAY(3) TO SECOND(2));
    下面这个查询对promotions表进行检索,注意duration列值的格式化:
    SELECT *
    FROM promotions;
    PROMOTION_ID NAME DURATION


    1 10% off Z Files +003 00:00:00.0000
    2 20% off Pop 3 +000 02:00:00.0000
    3 30% off Modern Science +000 00:25:00.0000
    4 20% off Tank War +000 00:00:45.0000
    5 10% off Chemistry +003 02:25:00.0000
    6 20% off Creative Yell +003 02:25:45.0000
    7 15% off My Front Line +123 02:25:45.1200

    四、 Datetime和Interval算法
    PL/SQL允许我们创建日期时间和间隔表达式。下面列表显示了可以在表达式中使用的操作符:
    操作数 1 操作符 操作数 2 返回类型
    日期时间 + 时间间隔 日期时间
    日期时间 - 时间间隔 日期时间
    时间间隔 + 日期时间 日期时间
    日期时间 - 日期时间 时间间隔
    时间间隔 + 时间间隔 时间间隔
    时间间隔 - 时间间隔 时间间隔
    时间间隔 * 数字 时间间隔
    数字 * 时间间隔 时间间隔
    时间间隔 / 数字 时间间隔
    我们还可以使用各种函数来操作日期时间类型,如EXTRACT等。
    8、使用日期和时间子类型来避免"切断"问题
    对于某些日期和时间类型的默认精度都要小于它们对应的最大精度。例如,DAY TO SECOND的默认精度是DAY(2) TO SECOND(6),而最大精度是DAY(9) TO SECOND(9)。为了避免在使用这些类型进行赋值和传递参数时丢失精度,我们可以声明它们的子类型,这些类型都采用了最大的精度:
    TIMESTAMP_UNCONSTRAINED

    TIMESTAMP_TZ_UNCONSTRAINED

    TIMESTAMP_LTZ_UNCONSTRAINED

    YMINTERVAL_UNCONSTRAINED

    DSINTERVAL_UNCONSTRAINED
    1.4 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内容了。同样,我们也不能在一个会话中操作另外一个会话中的定位器。
    从9i开始,我们也可以把CLOB类型转成CHAR和VARCHAR2类型或是把BLOB转成RAW,反之亦然,这样,我们就能在大多数SQL和PL/SQL语句和函数中使用LOB类型了。要读、写和分段的操作LOB,我们可以使用Oracle系统包DBMS_LOB。
    oracle中支持4种类型的LOB(large object)
    CLOB:字符LOB。这种类型用于存储大量的文本信息,如xml或者只是纯文本。这个数据类型需要进行字符集转换,也就是说,在获取时,这个字段中的字符会从数据库的字符集转换为客户的字符集,而在修改时会总客户的字符集转换为数据库的字符集。
    NCLOB:这是另一种类型的LOB。存储在这一列中的数据所采用的字符集是数据库的国家字符集,而不是数据库的默认字符集。
    BLOB:二进制LOB。这种类型用于存储大量的二进制信息,如处理文档、图像和你能想象的任何其他数据。它不会执行字符集转换。应用向BLOB写入什么位和字节,BLOB就会返回什么位和字节。
    BFILE:二进制文件LOB。这与其说是一个数据库存储实体,不如说是一个指针。带BFILE列的数据库存储的只是操作系统的某个文件的一个指针。这个文件在数据库之外维护,根本不是数据库的一部分。BFILE提供了文件内容的只读访问。

    因此可以将LOB分为内部LOB(CLOB、NCLOB、BLOB),外部LOB(BFILE内容存储在数据库之外,BFILE列只是一个指向操作系统中的文件的指针)
    内部LOB字段在数据库内部并不是在表中记录数据(也可以设置为在表中记录(in row(最多4000字节,超过就会移出)))。LOB字段内部存储分为3个部分,如下图所示:

    table T的Txt字段是CLOB类型的,LOB内部分为LOBINDEX、LOBSEGMENT,表T的Txt字段存储的为指向LOBINDEX的地址,LOBINDEX字段存储的为LOBSEGMENT的索引。所以查找的顺序是Txt->LOBINDEX->LOBSEGMENT。
    如果是行内(IN ROW)存储,那就和varchar2没有区别了,行内存储要比行外存储块。这里只是浅显的了解了LOB字段的内部存储,深入的参数不记录了

    一、 BFILE
    BFILE数据类型用于存储二进制对象,它将存储的内容放到操作系统的文件中,而不是数据库内。每个BFILE变量都存储一个文件定位器,它指向服务器上的一个大的二进制文件。定位器包含目录别名,该别名给出了文件全路径。
    BFILE类型是只读的,而且它的大小要依赖于系统,不能超过4G。我们的DBA要确保给定的BFILE存在且Oracle有读取它的权限。
    BFILE并不参与事务,是不可恢复,不能被复制。能够被打开的BFILE最大数是由Oracle初始化参数SESSION_MAX_OPEN_FILES决定的。
    第一步:创建一个目录对象用来存储数据
    CREATE OR REPLACE DIRECTORY record_bfile AS ‘/home/oracle/record_bfile/bfile.txt’
    第二步:建立一个bfile表
    CREATE TABLE articles
    (
    author_id NUMBER(4),
    author_name VARCHAR2 (14) ,
    release_date date,
    article_content BFILE
    );
    第三步:插入记录
    INSERT INTO articles
    VALUES(1111,‘SMITH’,to_date(‘2010-07-11’,‘YYYY-MM-DD’),bfilename(‘record_bfile’,‘bfile.txt’));

    You want to access employee details contained in flat files as part of the EMPLOYEE table. You plan
    to add a new column to the EMPLOYEE table to achieve this.
    Which data type would you use for the new column?
    A.CLOB
    B.BLOB
    C.BFILE
    D.LONG RAW
    Answer: C
    题目解答:表的数据放到file中,要么外部表,要么bfile列存储 本题是列 只能C
    BFILE的用法
    (1)、create or replace directory
    BFILE_TEST
    as
    ‘/oracle/oradata/bfiles’;
    (2)、grant read on directory BFILE_TEST to SCOTT;
    (3)、host ls -l /oracle/oradata/bfiles/1.TXT
    (4)、connect SCOTT/TIGER
    create table BFILES (ID number, TEXT bfile );
    (5)、insert into BFILES values ( 1,
    bfilename ( ‘BFILE_TEST’, ‘1.TXT’ ) );

    二、 BLOB、CLOB和NCLOB
    BLOB数据类型可以在数据库中存放不超过4G的大型二进制对象;CLOB和NCLOB可以在数据库中分别存储大块CHAR类型和NCHAR类型的字符数据,都支持定宽和变宽字符集。同BFILE一样,这三个类型也都储存定位器,指向各自类型的一个大数据块。数据大小都不能超过4G。BLOB、CLOB和NCLOB都可以在事务中使用,能够被恢复和复制。DBMS_LOB包可以对它们更改过的内容进行提交或回滚操作。BLOB、CLOB和NCLOB的定位器都可以跨事务使用,但不能跨会话使用。
    1.4.2 clob和字符串
    Oracle中CLOB和BLOB字段虽说在开发中满足了存放超大内容的要求,但是在一些简单使用中确频频带来麻烦。CLOB中存放的是指针,并不能直接取到实际值。而SQLServer中的text字段就很方便,可以直接拿来与需要的字符串比对,象什么等于呀小于呀Like呀不在话下。可是换成Oracle就麻烦死了,要开辟一个缓存,把内容一段段读取出来后转换,难道写个where条件都这么复杂?经过多方寻求资料,终于发现一个方便简单的方法:利用dbms_lob 包中的方法(放心,内置的)instr和substr 。具体的介绍如下:

    instr函数与substr函数

    instr函数用于从指定的位置开始,从大型对象中查找第N个与模式匹配的字符串。
    用于查找内部大对象中的字符串的instr函数语法如下:

    1: dbms_lob.instr(
    2: lob_loc in clob character set any_cs,
    3: pattern in varchar2 character set lob_loc%charset,
    4: offset in integer:=1,
    5: nth in integer := 1)
    6: return integer;
    7:

    lob_loc为内部大对象的定位器
    pattern是要匹配的模式
    offset是要搜索匹配文件的开始位置
    nth是要进行的第N次匹配
    substr函数
    substr函数用于从大对象中抽取指定数码的字节。当我们只需要大对象的一部分时,通常使用这个函数。
    操作内部大对象的substr函数语法如下:

    其中各个参数的含义如下:
    lob_loc是substr函数要操作的大型对象定位器
    amount是要从大型对象中抽取的字节数
    offset是指从大型对象的什么位置开始抽取数据。

    如果从大型对象中抽取数据成功,则这个函数返回一个 raw 值。如果有一下情况,则返回null:
    1 任何输入参数尾null
    2 amount < 1
    3 amount > 32767
    4 offset < 1
    5 offset > LOBMAXSIZE
    1: dbms_lob.instr(
    2: lob_loc in blob,
    3: pattern in raw,
    4: offset in integer := 1;
    5: nth in integer := 1)
    6: return integer;
    7: dbms_lob.substr(
    8: lob_loc in blob,
    9: amount in integer := 32767,
    10: offset in integer := 1)
    11: return raw;
    12: dbms_lob.substr(
    13: lob_loc in clob character set any_cs,
    14: amount in integer := 32767,
    15: offset in integer := 1)
    16: return varchar2 character set lob_loc%charset;

    照下面示例,很容易看懂:
    DECLARE
    source_lob CLOB;
    pattern VARCHAR2(6) := ‘Oracle’;
    start_location INTEGER := 1;
    nth_occurrence INTEGER := 1;
    position INTEGER;
    buffer VARCHAR2(100);
    BEGIN
    SELECT clob_locator INTO source_lob FROM mylobs WHERE lob_index = 4;
    position := dbms_lob.instr(source_lob,
    pattern,
    start_location,
    nth_occurrence);
    dbms_output.put_line(‘The first occurrence starts at position:’ ||
    position);

     nth_occurrence := 2;
     SELECT clob_locator INTO source_lob FROM mylobs WHERE lob_index = 4;
     position := dbms_lob.instr(source_lob,
                                pattern,
                                start_location,
                                nth_occurrence);
     dbms_output.put_line('The first occurrence starts at position:' ||
                          position);
    
     SELECT clob_locator INTO source_lob FROM mylobs WHERE lob_index = 5;
     buffer := dbms_lob.substr(source_lob, 9, start_location);
     dbms_output.put_line('The substring extracted is: ' || buffer);
    

    END;

    输出结果为:
    The first occurrence starts at position:8
    The first occurrence starts at position:24
    The substring extracted is: Oracle 9i

    drop table t;
    create table t(x int, y clob);
    insert into t values(1, rpad(’’, 5000, '’));

    往oracle函CLOB、LONG字段类型的数据表插入值超过4000字节时,在执行INSERT INTO提示:ORA-01704:文字字符串过长错误,这种情况下,如果想用SQL实现的话,可以通过ORACLE存储过程实现。

    –处理方法一:用EXECUTE IMMEDIATE 实现

    –ASQL:带参数的INSERT INTO 语句
    –ALongVar1 : 参数值,大于4000字节
    CREATEORREPLACEPROCEDUREEXEC_SQL1(ASQL varchar2, ALongVar1 clob)
    AS
    BEGIN
    EXECUTEIMMEDIATE ASQL USING ALongValue;
    ENDEXEC_SQL1;

    –处理方法二:用Dbms_sql接口实现

    –ASQL:带参数的INSERT INTO 语句
    –ALongVar1 : 参数值,大于4000字节
    CREATEORREPLACEPROCEDUREEXEC_SQL(ASQL clob, ALongValue clob)
    AS
    The_c1 Integer;
    The_result Integer;–dml_sql_result
    BEGIN
    The_C1 :=Dbms_sql.open_cursor;
    Dbms_sql.parse(the_C1, ASQL, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE
    (The_c1, ‘:LongValue’, ALongValue);
    The_result:=Dbms_sql.execute(The_c1);
    Dbms_sql.close_cursor(The_C1);
    ENDEXEC_SQL;

    1.4.3 oracle中可以用多种方法来检索或操作lob数据
    在oracle中,有4个大对象(lobs)类型可用,分别是blob,clob,bfile,nclob。
    下面是对lob数据类型的简单介绍。
    blob:二进制lob,为二进制数据,最长可达4GB,存贮在数据库中。
    clob:字符lob,字符数据,最长可以达到4GB,存贮在数据库中。

    bfile:二进制文件;存贮在数据库之外的只读型二进制数据,最大长度由操作系统限制。
    nclob:支持对字节字符集合(nultibyte characterset)的一个clob列。
    对于如何检索和操作这些lob数据一直是oracle数据库开发者经常碰到的问题。下面我将在oracle对lob数据处理的一些方法和技巧,介绍给读者,希望能够对读者以后的开发有所帮助。

    oracle中可以用多种方法来检索或操作lob数据。通常的处理方法是通过dbms_lob包。
    其他的方法包括使用api(application programminginterfaces)应用程序接口和oci(oracle call interface)oracle调用接口程序。
    一、在oracle开发环境中我们可以用dbms_lob包来处理!dbms_lob包功能强大,简单应用。既可以用来读取内部的lob对象,也可以用来处理bfile对象。但处理两者之间,还有一点差别。处理内部lob对象(blob,clob)时,可以进行读和写,但处理外部lob对象bfile时,只能进行读操作,写的操作可以用pl/sql处理。另外用sql也可以处理lob,但要注意sql仅可以处理整个lob,不能操作lob的数据片。

    在dbms_lob包中内建了read(),append,write(),erase(),copy(),getlength(),substr()等函数,可以很方便地操作lob对象。这里不做深入讨论,读者可以参看相关的书籍。

    对于pl/sql,下面介绍一种技巧,用动态的pl/sql语句处理clob对象来传替表名!
    example 1.
    动态PL/SQL,对CLOB字段操作可传递表名table_name,表的唯一标志字段名field_id,clob字段名field_name记录号v_id,开始处理字符的位置v_pos,传入的字符串变量v_clob

    修改CLOB的PL/SQL过程:updateclob
    create or replace procedure updateclob(
    table_name in varchar2,
    field_id in varchar2,
    field_name in varchar2,v_id in number,
    v_pos in number,
    v_clob in varchar2)
    is
    lobloc clob;
    c_clob varchar2(32767);
    amt binary_integer;
    pos binary_integer;
    query_str varchar2(1000);
    begin
    pos:=v_pos*32766+1;
    amt := length(v_clob);
    c_clob:=v_clob;
    query_str :=’‘select ‘’||field_name||’‘from ‘’||table_name||’’
    where ‘’||field_id||’’= :id for update ‘’;
    –initialize buffer with data to be inserted or updated
    EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
    –from pos position, write 32766 varchar2 into lobloc
    dbms_lob.write(lobloc, amt, pos, c_clob);
    commit;
    exception
    when others then
    rollback;
    end;
    l /用法说明:
    在插入或修改以前,先把其它字段插入或修改,CLOB字段设置为空empty_clob(),
    然后调用以上的过程插入大于2048到32766个字符。
    如果需要插入大于32767个字符,编一个循环即可解决问题。
    查询CLOB的PL/SQL函数:getclob
    create or replace function getclob(
    table_name in varchar2,
    field_id in varchar2,
    field_name in varchar2,
    v_id in number,
    v_pos in number) return varchar2
    is
    lobloc clob;
    buffer varchar2(32767);
    amount number := 2000;
    offset number := 1;
    query_str varchar2(1000);
    begin
    query_str :=’‘select ‘’||field_name||’’ from ‘’||table_name||’’
    where ‘’||field_id||’’= :id ‘’;
    –initialize buffer with data to be found
    EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
    offset:=offset+(v_pos-1)*2000;
    –read 2000 varchar2 from the buffer
    dbms_lob.read(lobloc,amount,offset,buffer);
    return buffer;
    exception
    when no_data_found then
    return buffer;
    end;
    l 用法说明:
    用select getclob(table_name,field_id,field_name,v_id,v_pos) as
    partstr from dual;
    可以从CLOB字段中取2000个字符到partstr中,
    编一个循环可以把partstr组合成dbms_lob.getlength(field_name)长度的目标字符串。
    二、对于在其他不同的开发环境,例如vc,vb,pb,java等环境下对lob的处理,处理方法不尽相同,在这里将简要举几个例子来说明不在oracle开发环境下对lob的处理。

    (一) 在pb中的处理
    exampler 2.
    string ls_path,ls_filename,ls_jhdh
    long ll_num,ll_count,rtn
    blob ole_blob
    ll_num=dw_lb.getrow()
    if ll_num>0 then ls_jhdh=dw_lb.object.ct_njhdh[ll_num]
    select count(*) into :ll_count from sj_jh_jhfjb where
    ct_jhdlxbh=’‘1’’ and ct_jhdh=:ls_jhdh and ct_jdlxbh=:is_jdlx;
    if ll_count>0 then
    rtn=messagebox(“提示”,“是否要修改此附件”,question!,yesno!,1)
    if rtn=1 then
    SELECTBLOB ct_jhfjnr INTO le_blob from sj_jh_jhfjb where
    ct_jhdlxbh=’‘1’’ and ct_jhdh=:ls_jhdh and ct_jdlxbh=:is_jdlx;
    ole_1.objectdata =ole_blob
    If ole_1.activate(offsite!) <> 0 Then
    Messagebox(“OLE Activate”,“不能激活”)
    Return -1
    end If
    end if
    else
    messagebox(“提示”,“没有附件”)
    end if
    end if
    (二)在vb中的处理
    在vb中处理大对象,一般可以用OO4O(oracle objects for
    ole)来处理大对象。这里介绍一种不用0040处理大对象blob的方法。
    下面这段程序可以将一个文件(文本文件,doc文件,图象文件等)保存到数据库中,并可以将其从数据库读出
    需要两个commandbutton
    cmd1 名称 cmdsave caption 保存
    cmd2 名称 cmdread caption 读取
    一个cmddialog控件
    同时需要创建一张表t_demo(字段id 类型 number,;字段text 类型 blob;)
    exmple 3.
    Option Explicit
    Dim rn As ADODB.Connection
    Public Function CreateDataSource(DataSource As String, UserID
    As String, Password As String) As Boolean
    On Error GoTo DbConErr:
    Set rn = New ADODB.Connection
    With rn
    .ConnectionString = “Provider=OraOledb.Oracle.1;” & _
    “password=” & Password & “;” & _
    “User ID =” & UserID & “;” & _
    “Data Source=” & DataSource & “;” & _
    “Locale Identifier=2052”
    .Open
    End With
    CreateDataSource = True
    Exit Function
    DbConErr:
    CreateDataSource = False
    End Function

    Private Sub cmdRead_Click()
    Dim rs As New ADODB.Recordset
    rs.ActiveConnection = rn
    rs.LockType = adLockOptimistic
    rs.CursorLocation = adUseClient
    rs.Source = “select * from t_demo”
    rs.Open
    ComDlgDir.DialogTitle = “保存文件”
    ComDlgDir.Filter = “.
    ComDlgDir.ShowSave
    Call BlobToFile(rs.Fields(“text”), ComDlgDir.filename)
    Set rs = Nothing
    Exit Sub
    Set rs = Nothing
    End Sub

    Private Sub cmdsave_Click()
    Dim rs As New ADODB.Recordset
    rs.ActiveConnection = rn
    rs.LockType = adLockOptimistic
    rs.CursorLocation = adUseClient
    rs.Source = “select * from t_demo”
    rs.Open
    rs.AddNew
    ComDlgDir.DialogTitle = “选取文件”
    ComDlgDir.ShowOpen
    rs.Fields(“id”).Value = 1
    If ComDlgDir.filename <> “” Then
    Call FileToBlob(rs.Fields(“text”), ComDlgDir.filename)
    rs.Update
    End If
    Set rs = Nothing
    Exit Sub
    Set rs = Nothing
    End Sub

    Private Sub Form_Load()
    If Not CreateDataSource(“sid”, “systemp”, “manager”) Then
    MsgBox “Connection failure!”
    End If
    End Sub

    fld As ADODB.Field, filename As String, Optional ChunkSize As
    Long = 8192)
    Dim fnum As Integer, bytesleft As Long, bytes As Long
    Dim tmp() As Byte
    If (fld.Attributes And adFldLong) = 0 Then
    Err.Raise 1001, , “field doesn’'t support the GetChunk method.”

    End If
    If Dir$(filename) = “” Then Err.Raise 53, , “File not found”
    fnum = FreeFile
    Open filename For Binary As fnum
    bytesleft = LOF(fnum)
    Do While bytesleft
    bytes = bytesleft
    If bytes > ChunkSize Then bytes = ChunkSize
    ReDim tmp(1 To bytes) As Byte
    Get fnum, , tmp
    fld.AppendChunk tmp
    bytesleft = bytesleft - bytes
    Loop
    Close #fnum
    End Sub

    Sub BlobToFile(fld As ADODB.Field, filename A

    1.5 用户自定义子类型
    每个PL/SQL基类型都有对应的值集合和操作符集合。子类同样会指定同其基类型相同的值集合和操作符集合的子集作为它自己的值集合和操作符集合。所以说子类并不是一个新类型,它只是在基类的基础上添加了一个可选的约束。
    子类可以增加可读性和兼容性。PL/SQL在STANDARD包里预定义了一些子类型。如下例:
    SUBTYPE CHARACTER IS CHAR;
    SUBTYPE INTEGER IS NUMBER(38, 0); – allows only whole numbers
    子类型CHARACTER和基类型完全一样,所以CHARACTER是一个未作约束的子类型。但是,子类型INTEGER将基类NUMBER的值集合的子集作为自己的值集合,所以INTEGER是一个约束的子类型。
    1、定义子类型
    我们可以在任何PL/SQL块、子程序或包中定义自己的子类型,语法如下:
    SUBTYPE subtype_name IS base_type[(constraint)] [NOT NULL];
    subtype_name就是声明的子类型的名称,base_type可以是任何标量类型或用户定义类型,约束只是用于限定基类型的精度和数值范围,或是最大长度。下面举几个例子:
    DECLARE
    SUBTYPE birthdate IS DATE NOT NULL; – based on DATE type

    SUBTYPE counter IS NATURAL; – based on NATURAL subtype

    TYPE namelist IS TABLE OF VARCHAR2(10);

    SUBTYPE dutyroster IS namelist; – based on TABLE type

    TYPE timerec IS RECORD(
    minutes INTEGER,
    hours INTEGER
    );

    SUBTYPE finishtime IS timerec; – based on RECORD type

    SUBTYPE id_num IS emp.empno%TYPE; – based on column type
    我们可以使用%TYPE或%ROWTYPE来指定基类型。当%TYPE提供数据库字段中的数据类型时,子类型继承字段的大小约束(如果有的话)。但是,子类型并不能继承其他约束,如NOT NULL。
    2、使用子类型
    一旦我们定义了子类型,我们就可以声明该类型的变量、常量等。下例中,我们声明了Counter类型变量,子类型的名称代表了变量的使用目的:
    DECLARE
    SUBTYPE counter IS NATURAL;

    ROWS counter;
    下面的例子演示了如何约束用户自定义子类型:
    DECLARE
    SUBTYPE accumulator IS NUMBER;

    total accumulator(7, 2);
    子类型还可以检查数值是否越界来提高可靠性。下例中我们把子类型Numeral的范围限制在-9到9之间。如果程序把这个范围之外的数值赋给Numeral类型变量,那么PL/SQL就会抛出一个异常。
    DECLARE
    SUBTYPE numeral IS NUMBER(1, 0);

    x_axis numeral; – magnitude range is -9 … 9
    y_axis numeral;
    BEGIN
    x_axis := 10; – raises VALUE_ERROR

    END;
    ? 类型兼容
    一个未作约束的子类型是可以和它的基类型交互使用。例如下面的声明,amount值可以在不用转换的情况下直接赋给total:
    DECLARE
    SUBTYPE accumulator IS NUMBER;

    amount NUMBER(7, 2);
    total accumulator;
    BEGIN

    total := amount;

    END;
    如果基类型相同,那么不同的子类型也是可以交互使用。例如,下面的声明中,finished的值就可以赋给debugging:
    DECLARE
    SUBTYPE sentinel IS BOOLEAN;

    SUBTYPE SWITCH IS BOOLEAN;

    finished sentinel;
    debugging SWITCH;
    BEGIN

    debugging := finished;

    END;
    不同的子类型也是有可能交互使用,只要它们的基类型属于同一个数据类型种类。例如下面的声明中,verb值就能赋给sentence:
    DECLARE
    SUBTYPE word IS CHAR(15);

    SUBTYPE text IS VARCHAR2(1500);

    verb word;
    sentence text(150);
    BEGIN

    sentence := verb;

    END;

    一、 timestamp with local time zone类型和timestamp with time zone
    timestamp with loca time zone类型语法
    tmestamp[(fractional_seconds_precisions)] with local zone
    timestamp with local time zone 和timesatamp with time zone的最大区别就是,前者在用户提交时间给数据库的时,该类型会转换成数据库的时区来保存数据,即数据库保存的时间是数据库本地时区,当别的用户访问数据库时oracle会自动将该时间转换成当前客户端的时间。
    例子:
    1、创建表
    CREATE TABLE TIMESTAMP_TEST(
    TIME DATE,
    TIMESTP TIMESTAMP(3),
    TIMESTP_TZ TIMESTAMP(3) WITH TIME ZONE,
    TIMESTP_LTZ TIMESTAMP(3) WITH LOCAL TIME ZONE)
    2、添加数据
    INSERT INTO TIMESTAMP_TEST VALUES(SYSDATE,SYSDATE,SYSDATE,SYSDATE);
    commit;
    3、查询dbtimezone和sessiontimezone的值
    select dbtimezone ,sessiontimezone from dual;
    DBTIME

    SESSIONTIMEZONE

    +00:00
    +08:00
    4、查看数据的值

    SQL> SELECT * FROM TIMESTAMP_TEST;
    TIME

    TIMESTP

    TIMESTP_TZ

    TIMESTP_LTZ

    02-6月 -10
    02-6月 -10 11.21.10.000 上午
    02-6月 -10 11.21.10.000 上午 +08:00
    02-6月 -10 11.21.10.000 上午
    5、修改会话的time_zone值
    alter session set time_zone=’+10:00’;
    6、查看结果
    SQL> SELECT * FROM TIMESTAMP_TEST;
    TIME

    TIMESTP

    TIMESTP_TZ

    TIMESTP_LTZ

    02-6月 -10
    02-6月 -10 11.21.10.000 上午
    02-6月 -10 11.21.10.000 上午 +08:00
    02-6月 -10 01.21.10.000 下午
    7、从上面的实验可以看出二者的去区别,当session的时区由8变为10是,时间增加两个小时
    再向表中添加一条记录
    insert into TIMESTAMP_TEST values(
    TO_TIMESTAMP_TZ(‘2010-12-01 23:12:56.788 -12:44’, ‘YYYY-MM-DD HH24:MI:SS.FF TZH:TZM’),
    TO_TIMESTAMP_TZ(‘2010-12-01 23:12:56.788-12:44’, ‘YYYY-MM-DD HH24:MI:SS.FF TZH:TZM’),
    TO_TIMESTAMP_TZ(‘2010-12-01 23:12:56.788 -12:44’, ‘YYYY-MM-DD HH24:MI:SS.FF TZH:TZM’),
    TO_TIMESTAMP_TZ(‘2010-12-0123:12:56.788 -12:44’, ‘YYYY-MM-DD HH24:MI:SS.FF TZH:TZM’));
    (tzh:时区中的小时,tzm:时区中的分)
    在这里我指定了数据添加时的时区为-12:44,查询结果为
    TIME

    TIMESTP

    TIMESTP_TZ

    TIMESTP_LTZ

    01-12月-10
    01-12月-10 11.12.56.788 下午
    01-12月-10 11.12.56.788 下午 -12:44
    02-12月-10 09.56.56.788 下午

    TIME

    TIMESTP

    TIMESTP_TZ

    TIMESTP_LTZ

    02-6月 -10
    02-6月 -10 11.21.10.000 上午
    02-6月 -10 11.21.10.000 上午 +08:00
    02-6月 -10 01.21.10.000 下午
    由于当前用户的时区是+10:00,添加数据时的指定时区死-12:44,二者时间相差22小时44分
    二、 毫秒
    关于毫秒:
    Oracle 毫秒的存储必须字段类型为 timestamp(6) –数字表示存储的毫秒位数
    –当前毫秒级时间
    select to_char(current_timestamp,‘yyyy-mm-dd hh24:mi:ss.ff6’) from dual;
    –字符串转为 timestamp类型
    select to_timestamp(‘2012-02-03 10:29:46.453234’,‘yyyy-mm-dd hh24:mi:ss.ff6’) from dual;
    –timestamp转为字符型
    select to_char(systimestamp,‘yyyy-mm-dd hh24:mi:ss.ff6’) from dual;
    PS: ff后面的数字表示获得的毫秒位数,默认是6;一般ff3 获得三位毫秒数。

    如果你想把DATE类型转换成TIMESTAMP类型,就使用CAST函数。
    select cast(sysdate as timestamp) from dual;
    但是值得注意的是:在转换后的时间段尾部有了一段“.000000”。这是因为从date转换过来的时候,没有小数秒的信息,缺省为0。而且显示格式是按照参数NLS_TIMESTAMP_FORMAT定的缺省格式显示。当你把一个表中date类型字段的数据移到另一个表的timestamp类型字段中去的时候,可以直接写INSERT SELECT语句,oracle会自动为你做转换的。

    注意: to_char函数支持date和timestamp,但是trunc却不支持TIMESTAMP数据类型。这已经清楚表明了在当两个时间的差别极度重要的情况下,使用TIMESTAMP数据类型要比DATE数据类型更确切。

    还值得一提的是:毫秒的显示精度是6位,不过有效位是3位,即最大值达到999,满1000ms就进为1s。当然你想保存6位毫秒也是有办法的:
    insert 值指定六位:to_timestamp(‘2012-02-03 10:29:46.453234’,‘yyyy-mm-dd hh24:mi:ss.ff6’)

    1.6 数据类型转换
    有时我们需要把一个值从一个类型转换成另一个类型。例如,如果我们想检查一个ROWID,我们就必须把它转成一个字符串。PL/SQL支持显式和隐式(自动的)数据类型转换。
    1、显式转换
    如果要进行类型间的转换,我们可以使用内置函数。例如将一个CHAR类型的值转换成一个DATE或NUMBER类型的值,我们就可以使用函数TO_DATE和TO_NUMBER。反过来,如果从DATE或NUMBER转成CHAR的话,可以使用TO_CHAR函数。
    2、隐式转换
    PL/SQL有时会帮助我们进行隐式地数据类型转换。下面的例子中,CHAR型变量start_time和finish_time都有一个代表从午夜开始后所经过的秒数的值。这两个变量的差值要放到elapsed_time中去。所以,PL/SQL会自动地把CHAR类型转换成NUMBER类型。
    DECLARE
    start_time CHAR(5);
    finish_time CHAR(5);
    elapsed_time NUMBER(5);
    BEGIN
    /* Get system time as seconds past midnight. */
    SELECT TO_CHAR(SYSDATE, ‘SSSSS’)
    INTO start_time
    FROM SYS.DUAL;

    – do something
    /* Get system time again. */
    SELECT TO_CHAR(SYSDATE, ‘SSSSS’)
    INTO finish_time
    FROM SYS.DUAL;

    /* Compute elapsed time in seconds. */
    elapsed_time := finish_time - start_time;

    INSERT INTO results
    VALUES (elapsed_time, …);
    END;
    另外,在把查询的结果赋给变量之前,如果有必要的话PL/SQL也会把原值类型转成对应的变量类型,典型的例子就是把DATE类型放入VARCHAR2变量中。
    同样,在把变量值赋给数据库字段时,PL/SQL在必要的情况下,也会进行数据类型转换。如果PL/SQL无法确定采用哪种转换形式,就会发生变异错误。这种情况下,我们就必须使用类型转换函数。下表是PL/SQL能够做的隐式转换。
    BIN_INT BLOB CHAR CLOB DATE LONG NUMBER PLS_INT RAW UROWID VARCHAR2
    BIN_INT X X X X X
    BLOB X
    CHAR X X X X X X X X X X
    CLOB X X
    DATE X X X
    LONG X X X
    NUMBER X X X X X
    PLS_INT X X X X X
    RAW X X X X
    UROWID X X
    VARCHAR2 X X X X X X X X X
    5 注意
    6 列表中只列出表现不同的类型,而那些表现相同的类型,如CLOB和NCLOB,CHAR和NCHAR,还有VARCHAR2和NVARCHAR2都是可以互相替换。
    7 要在CLOB和NCLOB之间转换,我们必须使用转换函数TO_CLOB和TO_NCLOB。
    8 TIMESTAMP,TIMESTAMP WITH TIME ZONE,TIMESTAMP WITH LOCAL TIME ZONE,INTERVAL DAY TO SECOND和INTERVAL YEAR TO MONTH都可以作为DATE类型用同样的转换规则进行转换。但是由于它们的内部表现形式不同,这些类型不能相互转换。
    3、显式转换 VS 隐式转换
    通常,依赖隐式的数据转换不是一个好习惯,因为这样做会引起性能问题并可能随着软件版本变更而影响到后续的版本。隐式转换是与环境相关的,它的转换结果有时是无法预知的。所以,最好还是使用类型转换函数。这样的话,程序就有更好的可读性和可维护性。
    4、DATE值
    当把一个查询出来的DATE类型值放到CHAR或VARCHAR2变量中时,PL/SQL必须将内置的二进制值转成字符值,它就会调用TO_CHAR将日期按照默认的日期格式转成字符值。同样,把一个CHAR或VARCHAR2插入到DATE类型的字段中,也是需要进行类型转换的,PL/SQL会调用TO_DATE将字符按照默认的日期格式转成日期类型的值。如果对转换有特殊要求,我们就得显式地指明转换格式。
    5、RAW和LONG RAW值
    在把查询出来的RAW或LONG RAW类型值赋给CHAR类型或VARCHAR2类型变量的时候,PL/SQL必须把内置的二进制值转成字符值。这种情况下,PL/SQL会把每个RAW或LONG RAW类型的二进制字节转成等值的一对十六进制字符值。比如PL/SQL会把二进制11111111转换成字符"FF"。函数RAWTOHEX也具有这样的功能。把CHAR或VARCHAR2类型值插入RAW或LONG RAW字段时也是需要类型转换的。变量中的每对字符要转成等值二进制字节,如果无法进行正常的转换,PL/SQL就会抛出异常。

    除Oracle数据类型之外,Oracle9i/10g数据库中表的列可以用ANSI、DB2和SQL/DS 数
    据类型定义。不管用何种方法,Oracle服务器内部将转换这些数据类型为Oracle数据类型。
    在某些情况下,Oracle服务器使用一种数据类型的数据,而在另外一种情况下我们希望
    使用一种不同数据类型的数据,如果这种情况发生,Oracle服务器自动转换数据为期望的数
    据类型。这种数据类型的转换可以被Oracle服务器隐式进行,或由用户显式进行。
    1.6.1 隐式数据类型转换
    隐式数据类型转换工作依照下面的规则进行。
    显式数据类型转换用转换函数进行。转换函数转换从一种数据类型转换值到另一种数据
    类型。通常,函数名的构成遵循 数据类型 到 数据类型 的约定,第一个数据类型是输入数
    据类型;后一个数据类型是输出数据类型。
    尽管隐式数据类型转换是可用的,但建议你做显式数据类型转换以确保SQL语句的可靠
    性。
    对于直接赋值,ORACLE SQL可以进行以下的隐式转换:
    从 到
    VARCHAR2 or CHAR NUMBER
    VARCHAR2 or CHAR DATE
    NUMBER VARCHAR2
    DATE VARCHAR2

    对于表达式赋值,ORACLE SQL可以进行以下的隐式转换:

    从 到
    VARCHAR2 or CHAR NUMBER
    VARCHAR2 or CHAR DATE

    1.6.2 显式数据类型转换
    SQL 提供三种函数来从一种数据类型转换值到另一种:TO_CHAR、TO_NUMBER、 TO_DATE。
    TO_CHAR() 函数又可以分三小类,分别是
    转换字符->字符TO_CHAR©:将nchar,nvarchar2,clob,nclob类型转换为char类型;
    例如:
    Select To_Char(‘AABBCC’) From Dual;

    转换时间->字符TO_CHAR(d[,fmt]):将指定的时间(data, timestamp,
    timestamp with time zone)按照指定格式转换为varchar2类型;
    必须加单引号,并且区分大小写,能够包含任一有效的日期格式元素,有一个fm元素
    用来删除填补的空,或者前导零,用一个逗号与日期值分开。
    例如:
    Select To_Char(Sysdate, ‘yyyy-mm-dd hh24:mi:ss’) From Dual;
    转换数值->字符TO_CHAR(n[,fmt]):将指定数值n按照指定格式fmt 转换为
    varchar2类型并返回;
    例如:
    Select To_Char(-100, ‘L99G999D99MI’) From Dual;

    TO_DATE(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2转换为日期类型,如果fmt参数
    不为空,则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为’J’则表示按照
    公元制(Julian day)转换,c则必须为大于0并小于5373484的正整数。
    例如:
    Select To_Date(2454336, ‘J’) From Dual;

    Select To_Date(‘2007-8-23 23:25:00’, ‘yyyy-mm-dd hh24:mi:ss’)
    From Dual;

    TO_NUMBER(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式
    转换为数值类型并返回。
    例如:
    Select To_Number(’-100.00’, ‘9G999D99’) From Dual;
    1.7 ORA-01704:文字字符串过长 oracle CLOB 超过4000字节

    往oracle函CLOB、LONG字段类型的数据表插入值超过4000字节时,在执行INSERT INTO提示:ORA-01704:文字字符串过长错误,这种情况下,如果想用SQL实现的话,可以通过ORACLE存储过程实现。
    往oracle函CLOB、LONG字段类型的数据表插入值超过4000字节时,在执行INSERT INTO提示:ORA-01704:文字字符串过长错误,这种情况下,如果想用SQL实现的话,可以通过ORACLE存储过程实现。

    – 处理方法一:用EXECUTE IMMEDIATE 实现

    – ASQL:带参数的INSERT INTO 语句
    – ALongVar1 : 参数值,大于4000字节
    CREATE OR REPLACE PROCEDURE EXEC_SQL1(ASQL VARCHAR2,
    ALongVar1 CLOB) AS
    BEGIN
    EXECUTE IMMEDIATE ASQL
    USING ALongVar1;
    END EXEC_SQL1;

    – 处理方法二:用Dbms_sql接口实现

    – ASQL:带参数的INSERT INTO 语句
    – ALongVar1 : 参数值,大于4000字节
    CREATE OR REPLACE PROCEDURE EXEC_SQL(ASQL CLOB,
    ALongValue CLOB) AS
    The_c1 INTEGER;
    The_result INTEGER; – dml_sql_result
    BEGIN
    The_C1 := Dbms_sql.open_cursor;
    Dbms_sql.parse(the_C1, ASQL, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(The_c1, ’ :LongValue ', ALongValue);
    The_result := Dbms_sql. EXECUTE(The_c1);
    Dbms_sql.close_cursor(The_C1);
    END EXEC_SQL;

    CREATE OR REPLACE FUNCTION sumvarc RETURN CLOB IS
    v_out CLOB;
    BEGIN
    FOR i IN 100…4000 LOOP
    v_out := v_out || i;
    END LOOP;
    RETURN v_out;
    END;

    ?
    第2章 pl/sql 块的数据类型
    2.1 pl/sql 数据类型
    定义并使用变量
    declare
    v_num number(5) := 123;
    v_str varchar2(20) := ‘abc’;
    v_date date := sysdate;
    c_pi constant number(5,2) not null := 3.14;

    begin
    –c_pi := c_pi + 1; 常量不能修改其内容
    v_num := v_num + 5;
    dbms_output.put_line(v_num);
    dbms_output.put_line(v_str);
    –dbms_output.put_line(‘今天是’ || v_date);
    dbms_output.put_line(‘今天是’ || to_char(v_date,‘yyyy"年"mm"月"dd"日"’));

    end;

    在编写 pl/sql 程序时,可以定义变量和常量;在 pl/sql 程序中包括有:
    1.标量类型(scalar)
    2.复合类型(composite)
    3.参照类型(reference)
    4.lob(large object)

    变量声明分标量型变量声明和组合变量(复合变量)声明。

    2.1.1 标量型变量
    标量型变量是指其内部没有成员的变量。

    标量(scalar)——常用类型
    在编写 pl/sql 块时,如果要使用变量,需在定义部分定义变量。pl/sql 中定义变量和常量的语法如下:
    identifier [constant] datatype [not null] [:=| default expr]
    identifier : 名称
    constant :指定常量。需要指定它的初始值,且其值是不能改变的
    datatype :数据类型
    not null :指定变量值不能为 null
    := 给变量或是常量指定初始值
    default 用于指定初始值
    expr :指定初始值的 pl/sql 表达式,可以是文本值、其它变量、函数等。

    2.1.1.1 标量定义的案例
    ① 定义一个变长字符串
    v_ename varchar2(10);

    ② 定义一个小数,范围 -9999.99~9999.99
    v_sal number(6,2);

    ③ 定义一个小数并给一个初始值为 5.4 :=是 pl/sql 的赋值号
    v_sal2 number(6,2):=5.4;

    ④ 定义一个日期类型的数据
    v_hiredate date;

    ⑤ 定义一个布尔变量,不能为空,初始值为 false
    v_valid boolean not null default false;

    例:
    age number(5) not null:=25;
    pi constant number(9):=3.1415926;
    name char(10) not null:=‘fan’;
    today date not null:=sysdate;
    sex boolean:=true;

    例:声明一个变量Student_name,其类型基于另一个变量teacher_name。
    Teacher_name char(10);
    Student_name teacher_name%type;

    例: 声明一个变量No,使其与表emp中EMPNO的类型一致。
    no emp.empno%type;

    标量(scalar)——使用标量 在定义好变量后,就可以使用这些变量。这里需要说明的是 pl/sql 块为变量赋 值不同于其它的编程语言,需要在等号前面加冒号(:=) 下面以输入员工号,显示雇员姓名、工资、个人所得税(税率为 0.03)为例。说 明变量的使用,看看如何编写。

    Sql 代码

    1. declare
    2. c_tax_rate number(3,2):=0.03;
    3. –用户名
    4. v_ename varchar2(5);
    5. v_sal number(7,2);
    6. v_tax_sal number(7,2);
    7. begin
    8. –执行
    9. select ename,sal into v_ename,v_sal from emp where empno=&no;
      10.–计算所得税
    10. v_tax_sal := v_sal*c_tax_rate;
      12.–输出
    11. dbms_output.put_line(‘姓名是:’||v_ename||‘工资:
      ‘||v_sal||’ 交税:’||v_tax_sal);
      14.end;
      15./

    2.1.2 复合变量(composite)
    介绍
    用于存放多个值的变量。主要包括这几种:

    1. pl/sql 记录
    2. pl/sql 表
    3. 嵌套表
    4. varray即数组

    复合组合变量也叫做组合变量,在复合变量中包括多个内部组件,每个组件都可以单独存放值,因此一个复合变量可以存放多个值。
    复合变量类型不是数据库中已经存在的数据类型,因此复合变量在声明类型之前,首先要先创建复合类型,复合类型创建后可以多次使用,以便定义多个复合变量。
    复合变量像标量变量一样也有数据类型,复合数据类型(又称为集合)有记录(RECORD)、表(TABLE)、嵌套表(Nested TABLE)和数组(VARRAY)四种类型。记录型是由一组相关但又不同的数据类型组成的逻辑单元。表是数据的集合,可将表中的数据作为一个整体进行引用和处理。关于嵌套表和数组本书不再作过多的介绍了。
    记录就是一组相关的数据项,每一个数据项都有自己的名字和数据类型。表由列和关键字组成,其中通过关键字可成组地访问行。一经定义,记录和表都可重复使用。

    当使用标量变量处理oracle数据时,每个标量变量只能存放单个值,也就是说只能处理单行单列的数据。如果要使用标量变量处理单行多列数据,那么必须要定义多个变量接收不同列的数据,为了简化单行多列数据的处理,可以使用PL\SQL记录,为了保留并处理多行单列的数据,可以使用索引表,嵌套表和varray,为了处理多行多列的数据,应该使用PL\SQL记录表。

    组合型变量内部包含若干个成员,每个成员由标量型变量或组合型变量组成
    定义组合型变量的语法如下:
    type <类型名> is record
    (<域名1> {<标量型数据类型> | <record类型>}[not null],
    <域名2> {<标量型数据类型> | <record类型>}[not null],
    …….);
    <标识符> <类型名>;

    例:定义一个变量,存放一个学生的有关信息。
    declare
    type student is record /定义组合型变量类型/
    (id number(4) not null:=0,
    name char(10) not null:=’ ',
    sex boolean not null:=true);
    stu student; /定义组合型变量/
    begin
    stu.id:=1;
    stu.name:=‘sheng’;
    stu.sex:=true;
    end;
    例:声明一个变量,其结构与表emp的数据结构相一致。
    declare
    emp_value emp%rowtype;
    Begin
    select * into empvalue from emp
    where empno=7369;
    dbms_output.put_line(‘姓名:’||‘ ’||emp_value.ename);
    End;
    注:在运行些PL/SQL块前,应先运行

    2.1.2.1 记录(record)类型

    PL/SQL 记录
    记录是存储在多个字段中的一组相关的数据项,每个字段都有自己的名字和数据类型。例如:描述一名员工的信息需要一些不同的属性,如姓名、工资、雇佣日期等,若把这些属性(字段)放在记录里,就可以组成了一个逻辑单元。当把这些字段作为一个整体声明为记录型时,就可以作为一个单元来处理这些不同的数据了。

    记录类型 :记录类型是把逻辑相关的数据作为一个单元存储起来
    当使用PL\SQL记录时,应用开发人员可以自己定义记录类型和记录变量,也可以使用%rowtype属性直接定义记录变量。
    含有若干个标量组件,RECORD或PL/SQL表类型称为信息组
    其结构类似于第三代语言中的记录
    不同于数据库表中的行
    把字段的集合当作一个整体的逻辑单元
    在数据处理时易于从表中取出行数据
    概念:由不同的域组成。记录也可以看成表中的数据行,域则相当于表中的列;

    定义记录类型语法如下:
    TYPE record_type IS RECORD(
    Field1 type1 [NOT NULL] [DEFAULT:= exp1 ],
    Field2 type2 [NOT NULL] [DEFAULT:= exp2 ],
    . . . . . .
    Fieldn typen [NOT NULL] [DEFAULT:= expn ]
    ) ;

    注意:

    1. 类似于高级语言中的结构体,需要注意的是,当引用 pl/sql 记录成员时,必须 要加记录变量作为前缀(记录变量.记录成员)。
    2. 记录是存储在多个字段中的一组相关的数据项,每个字段都有自己的名字和数据类型。
    3. 把字段的集合当作一个整体的逻辑单元
    4. 主要用于从表中取出查询到的行数据
    5. 处理单行多列的数据

    记录的特点:
    ① 每个记录内都可以有许多字段。
    ② 可以为记录赋初值,同时可用NOT NULL来限定记录
    ③ 无初始值的字段定义为NULL
    ④ 在定义字段时也可以使用保留字DEFAULT
    ⑤ 可以在任意一个块、子程序或包的声明部分定义RECORD类型并声明用户自定义的记录
    ⑥ 可以声明并引用嵌套的记录,一个记录可以是其他记录的组件

    注意:

    1. 记录类型一次不能插入多条记录,否则会报如下错误

    例:
    DECLARE
    TYPE emp_record IS RECORD(
    empno NUMBER(4),
    ename emp.ename%TYPE,
    edate emp.hiredate%TYPE);
    v_emp emp_record;
    BEGIN
    SELECT empno,
    ename,
    hiredate
    INTO v_emp
    FROM emp
    WHERE empno = 7934;
    dbms_output.put_line(v_emp.empno || ‘,’ || v_emp.ename || ‘,’ ||
    v_emp.edate);
    END;

    结果:7934,MILLER,23-1月 -82

    定义PL\SQL记录

    自定义记录

    DECLARE
        TYPE v_type IS RECORD(
            ename VARCHAR2(20),
            sal   NUMBER(20),
            hdate DATE);
        v_mytype v_type;
    BEGIN
        SELECT ename,
               empno,
               hiredate
        INTO   v_mytype
        FROM   emp
        WHERE  mgr IS NULL;
        dbms_output.put_line(v_mytype.ename || ' ' || v_mytype.sal || ' ' || v_mytype.hdate);
    END;
    

    – %rowtype 型记录
    DECLARE
    v_mytype emp%ROWTYPE;
    BEGIN
    SELECT * INTO v_mytype FROM emp WHERE mgr IS NULL;
    dbms_output.put_line(v_mytype.ename || ’ ’ || v_mytype.sal || ’ ’ || v_mytype.hiredate);
    END;

    一、 %TYPE
    定义一个变量,其数据类型与已经定义的某个数据变量的类型相同,或者与数据库表的某个列的数据类型相同,这时可以使用%TYPE
    例:DECLARE
    –用%TYPE 类型定义变量
    v_ename emp.ename%TYPE;
    – 用 %TYPE 类型定义与表相配的字段
    declare
    v_sal number(7) ;
    v_ename emp.ename%type;
    v_hiredate emp.hiredate%type;

    begin
    select sal,ename,hiredate into v_sal,v_ename,v_hiredate from emp where empno = 7369;
    insert into emp(empno,ename) values(1111,‘xxxx’);
    update emp set sal = 2000 where empno= 1010;
    delete emp where empno = 1012;
    commit;
    dbms_output.put_line(v_sal || ‘,’ || v_ename || ‘,’ || v_hiredate);
    end;
    TYPE t_Record IS RECORD(
    t_no emp.empno%TYPE,
    t_name emp.ename%TYPE,
    t_sal emp.sal%TYPE );
    – 声明接收数据的变量
    v_emp t_Record;

    二、 %ROWTYPE

    1. 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。
    2. 声明的变量对应于数据库表或视图中列的集合
    3. 在%ROWTYPE 之前加上数据库表名
    4. 记录内字段的名字和数据类型参照表或视图中的列

    %ROWTYPE的优点:
    ① ? 可以不必知道数据库中列的数量和类型。
    ② ? 在运行期间,数据库中列的数量和类型可能发生变化。
    ③ ? 在SELECT语句中使用该属性可以有效地检索表中的行。

    例:
    DECLARE
    v_emp emp%ROWTYPE;
    BEGIN
    SELECT * INTO v_emp FROM emp WHERE empno = 7521;
    dbms_output.put_line(v_emp.empno || ‘,’ || v_emp.ename);
    END;

    declare
    –定义一个 pl/sql 记录类型 emp_record_type,类型包含 3 个数据 name,salary,title。说白了,就是一个类型可以存放 3 个数据,主要是 为了好管理
    type emp_record_type is record(
    name emp.ename%type,
    salary emp.sal%type,
    title emp.job%type
    );
    –定义了一个 sp_record 变量,这个变量的类型是 emp_record_type
    sp_record emp_record_type;
    begin
    select ename, sal, job into sp_record from emp where empno =7788;
    dbms_output.put_line (‘员工名:’ || sp_record.name);
    end;

    declare
    type emp_record is record(
    empno number(4),
    ename emp.ename%type,
    edate emp.hiredate%type);

    v_emp emp_record;   
    

    begin
    select empno,ename,hiredate into v_emp from emp where empno = 7934;
    dbms_output.put_line(v_emp.empno || ‘,’ || v_emp.ename || ‘,’ || v_emp.edate);

    end;

    /*
    这既是Java注释,也是Oracle PLSQL多行注释
    */


    declare
    v_num number(5) := 123;
    v_str varchar2(20) := ‘abc’;
    v_date date := sysdate;
    c_pi constant number(5,2) not null := 3.14;

    begin
    –c_pi := c_pi + 1; 常量不能修改其内容
    v_num := v_num + 5;
    dbms_output.put_line(v_num);
    dbms_output.put_line(v_str);
    –dbms_output.put_line(‘今天是’ || v_date);
    dbms_output.put_line(‘今天是’ || to_char(v_date,‘yyyy"年"mm"月"dd"日"’));

    end;


    declare
    v_num number(4) := 20;
    v_num3 number(4) := 20;
    v_num4 number(4) := 20;
    begin
    dbms_output.put_line(‘v_num :’ || v_num);

    declare 
          v_num2 number(4) := 30;
          v_num3 number(4) := 50;
    begin
          v_num4 := 100;
          dbms_output.put_line('inner v_num :' || v_num); --内部可以访问外部的变量
          dbms_output.put_line('inner v_num2 :' || v_num2);
          dbms_output.put_line('inner v_num3 :' || v_num3);
    end;
    
    --dbms_output.put_line('outter v_num2 :' || v_num2); 错误
    dbms_output.put_line('outter v_num3 :' || v_num3);
    dbms_output.put_line('outter v_num3 :' || v_num4);
    dbms_output.put_line('the end....');
    

    end;


    declare
    v_sal number(7) ;
    v_ename emp.ename%type;
    v_hiredate emp.hiredate%type;

    begin
    select sal,ename,hiredate into v_sal,v_ename,v_hiredate from emp where empno = 7369;
    insert into emp(empno,ename) values(1111,‘xxxx’);
    update emp set sal = 2000 where empno= 1010;
    delete emp where empno = 1012;
    commit;
    dbms_output.put_line(v_sal || ‘,’ || v_ename || ‘,’ || v_hiredate);
    end;

    三、 记录类型的定义
    定义方式:显示定义和隐式定义

    1、 隐式定义

    隐式定义:是在基于表的结构或查询上使用%TYPE 属性,隐式声明是一个更强有力的工具,这是因为这种数据变量是动态创建的。语法如下:

    ① 表记录的隐式定义(student为表名)
    record_type student%ROWTYPE;

    ② 游标行记录的隐式定义

    cursor student_line(no_value student.no%type) is
    select cuid,no from student where no =no_value;
    stu_row student_line%rowtype;

    2、 显示定义

    显示定义:在PL/SQL 程序块中创建记录变量之前在声明部分定义。使用type 命令定义记录,然后在创建该记录的变量。创建语法如下:
    TYPE record_type IS RECORD (field_definition_list);
    注:
    i、 field_definition_list是由逗号分隔的列表;
    ii、 通过type定义的是一种自定义的类型,需要再通过该类型来创建变量;
    iii、 整个定义过称和SQL中定义表相似。
    V、 记录中域的引用方式:rtt.testa 如下:
    CREATE OR REPLACE FUNCTION VarDefined RETURN INT IS
    –定义记录类型record_test
    TYPE record_test IS RECORD(
    testa NUMBER,
    testb NUMBER,
    testc NUMBER);
    –定义类型为record_test的变量rtt
    rtt record_test;
    BEGIN
    –rtt中域testa的引用及赋值
    rtt.testa := 10;
    –输出信息
    dbms_output.put_line(‘rtt.testa=’ || rtt.testa);
    –返回值
    RETURN 0;
    END VarDefined;

    注意事项
    显示定义时,必须用type进行定义,然后才能声明该记录的变量;
    隐式定义,灵活方便,但是可读性差。

    四、 记录的赋值方式
    ① select into方式;
    ② fetch方式

    1、 SELECT INTO
    DECLARE
    v_mytype emp%ROWTYPE;
    BEGIN
    SELECT ename,
    sal
    INTO v_mytype.ename,
    v_mytype.sal
    FROM emp
    WHERE mgr IS NULL;
    dbms_output.put_line(v_mytype.ename || ’ ’ || v_mytype.sal || ’ ’ || v_mytype.hiredate);
    END;

    注意:

    如果 记录类型的字段是数字类型的,且有形如v_r_t.fttb_res_num := v_r_t.fttb_res_num + v_count2; 的,则必须进行初始化,不然变量的值一直为0

    DECLARE

    v_count2 NUMBER(18) := 0;
    
    
    v_r_t sqm.SQM_FTTX_RES_COVER%ROWTYPE;
    

    BEGIN
    v_r_t.fttb_gpon_res_num := 0;–这里必须进行初始化
    v_r_t.fttb_res_num := 0;–这里必须进行初始化
    FOR REC1 IN (SELECT * FROM xt_fttx_res_num_02) LOOP
    SELECT COUNT(1)
    INTO v_count2
    FROM XB_PORT P
    WHERE P.MEID = REC1.meid
    AND P.METACATEGORY IN
    (‘com.gxlu.ngrm.equipment.PSTNPort’,
    ‘com.gxlu.ngrm.equipment.IPPort’,
    ‘com.gxlu.ngrm.equipment.XdslPort’);
    v_r_t.fttb_res_num := v_r_t.fttb_res_num + v_count2;
    IF rec1.is_gpon = 1 THEN
    v_r_t.fttb_gpon_res_num := v_r_t.fttb_gpon_res_num + v_count2;
    END IF;
    END LOOP;
    END;

    2、 fetch 。。。BULK COLLECT INTO

    DECLARE
    CURSOR c1 IS
    SELECT t.OBJECT_ID,
    t.OBJECT_NAME
    FROM user_objects t
    WHERE t.OBJECT_ID <= 272835
    AND t.OBJECT_ID >= 272830;
    –v_depart user_objects%ROWTYPE;
    TYPE v_id_type IS TABLE OF user_objects.OBJECT_ID%TYPE;
    v_id v_id_type;
    TYPE v_name_type IS TABLE OF user_objects.OBJECT_NAME%TYPE;
    v_name v_name_type;
    BEGIN
    OPEN c1;
    FETCH c1 BULK COLLECT
    INTO v_id,
    v_name;
    FOR i IN 1 … v_id.count LOOP
    dbms_output.put_line(v_id(i) || ‘,’ || v_name(i));
    END LOOP;
    CLOSE c1;
    END;

    通过上面的这个例子,大家可以发现如果列很多的话,为每一列定义一个集合似乎有些繁琐,可以把集合和%rowtype结合起来一起使用来简化程序!
    在输出结果时,既可以使用集合的count属性和可以使用first和last,在引用%rowtype类型的内容时,还有一个需要注意的地方是v_depart(i).depart_code,而不是v_depart.depart_code(i),当然没有这样的写法,即使有意义也并不一样。

    DECLARE
    CURSOR c1 IS
    SELECT t.*
    FROM user_objects t
    WHERE t.OBJECT_ID <= 272835
    AND t.OBJECT_ID >= 272830;
    TYPE v_depart_type IS TABLE OF user_objects%ROWTYPE;
    v_depart v_depart_type;
    BEGIN
    OPEN c1;
    FETCH c1 BULK COLLECT
    INTO v_depart;
    FOR i IN 1 … v_depart.count LOOP
    –或者 FOR i IN v_depart.first … v_depart.last LOOP
    dbms_output.put_line(v_depart(i)
    .OBJECT_ID || ‘,’ || v_depart(i).OBJECT_NAME);
    END LOOP;
    CLOSE c1;
    END;

    3、 SELECT 。。。 BULK COLLECT INTO

    DECLARE
    TYPE v_depart_type IS TABLE OF user_objects%ROWTYPE INDEX BY PLS_INTEGER;
    v_depart v_depart_type;
    BEGIN
    SELECT t.* BULK COLLECT
    INTO v_depart
    FROM user_objects t
    WHERE t.OBJECT_ID <= 272835
    AND t.OBJECT_ID >= 272830;
    FOR i IN 1 … v_depart.count LOOP
    –或者 FOR i IN v_depart.first … v_depart.last LOOP
    dbms_output.put_line(v_depart(i)
    .OBJECT_ID || ‘,’ || v_depart(i).OBJECT_NAME);
    END LOOP;
    END;

    五、 记录类型的使用
    1、 在insert语句中使用记录
    –在values 中使用记录变量
    DECLARE
    v_mytype emp%ROWTYPE;
    BEGIN
    v_mytype.ename := ‘pengxiao’;
    v_mytype.sal := 500;
    v_mytype.hiredate := SYSDATE;
    INSERT INTO emp VALUES v_mytype;
    END;
    –在values 中使用记录成员

    DECLARE v_mytype emp%ROWTYPE;
    BEGIN
    v_mytype.ename := ‘pengxiao’;
    v_mytype.sal := 500;
    v_mytype.hiredate := SYSDATE;
    INSERT INTO emp
    (ename, sal, hiredate)
    VALUES
    (v_mytype.ename, v_mytype.sal, v_mytype.hiredate);
    END;

    2、 Update 中使用记录
    –UPDATE 中使用记录变量
    DECLARE
    v_mytype emp%ROWTYPE;
    BEGIN
    v_mytype.ename := ‘pengxiao’;
    v_mytype.sal := 500;
    v_mytype.hiredate := SYSDATE;
    UPDATE emp SET ROW = v_mytype WHERE mgr IS NULL;
    END;
    –UPDATE 中使用记录成员
    DECLARE
    v_mytype emp%ROWTYPE;
    BEGIN
    v_mytype.ename := ‘pengxiao’;
    v_mytype.sal := 500;
    v_mytype.hiredate := SYSDATE;
    UPDATE emp
    SET ename = v_mytype.ename, sal = v_mytype.sal
    WHERE mgr IS NULL;
    END;

    3、 Delete 中使用记录
    –DELETE 只能使用记录成员不能使用记录变量
    DECLARE
    v_mytype emp%ROWTYPE;
    BEGIN
    v_mytype.deptno := 30;
    DELETE FROM emp WHERE deptno = v_mytype.deptno;
    END;

    2.1.2.2 PL\SQL集合
    PL/SQL中没有数组的概念,他的集合数据类型和数组是相似的。在7.3以前的版本中只有一种集合,称为PL/SQL表,在这之后又有两种集合数据类型:嵌套表和varray。其中varray集合中的元素是有数量限制的,index_by表和嵌套表是没有这个限制的。index-by表是稀疏的,也就是说下标可以不连续,varray类型的集合则是紧密的,他的下标没有间隔。index_by表不能存储在数据库中,但是嵌套表和varray可以被存储在数据库中。
    集合在使用时必须先使用type进行定义方可使用。

    1. 为了处理单行单列数据我们使用 标量变量
    2. 为了处理单行多列数据我们使用PL\SQL记录
    3. 为了处理单列多行数据,我们可以使用 PL\SQL集合
    4. 集合类型包括索引表(PL\SQL表、index-by表,关联数组),嵌套表,和变长数组varray三种类型,使用这三种类型的时候要仔细体会他们中的区别

    PL/SQL集合:

    1. 关联数组:也称索引表,可以用任意数字和字符串作为下标,类似于其他语言中的hash 表。
    2. 嵌套表:可以存储任意数量的元素,使用连续数字作为下标。可以定义等价的SQL类型,可以存储在数据库表中,用SQL进行检索。
    3. Varrays(可变数组,可变大小数组):存储固定数量的元素(在运行中,可以改变元素数量),使用顺序数字作下标,可以定义等价的SQL类型,可以存储在数据库中。可以用SQL进行存储和检索,但比嵌套表缺乏灵活性。
      一、 表类型
      相当于高级语言中的数组,但是需要注意的是在高级语言中数组的下标不能为负数,而 pl/sql 是可以为负数的,并且表元素的下标没有限制。实例如下:
      PL/SQL 表, 或者称为索引表(index-table)或称为关联数组,是可以在PL/SQL 程序中引用、能够模仿数组的非永久表。用户可以定义一个表类型,然后声明这种类型的变量。接下来,用户就可以将记录添加到用户的PL/SQL 表中,并且采用与引用数组元素大体相同的方法引用他们 。

    表包括两个基本成分:
    ① 主键:数据类型为BINARY_INTEGER
    ② 标量或记录数据类型

    Note:此表非彼表
    定义一个表:
    TYPE type_name IS TABLE OF
    {column_type | variable%TYPE | table.column%TYPE } [NOT NULL] | table%ROWTYPE
    [ INDEX BY BINARY_INTEGER];
    ? 注意:
    1. Index-by表中的元素不一定要按任何特定的顺序排序
    2. 关键字唯一允许的类型是BINARY_INTERGER
    3. 使用主键值引用表中的字段
    4.Table类型没有长度限制,可以动态增长
    5.由于不存储在数据库中,column_type可以是任何合法的PL/SQL数据类型,包括:PLS/INTEGER、SIGNTYPE、和BOOLEAN。
    6.使用的时候需要先赋值后读取,至少也要先初始化一下,否则会出现异常:ORA-01403: no data found。
    7.这种数组不需要事先指定上限,下标可以不连续,可以是0或负数。

    例:v1 TYPE1;

    v1(-1) := ‘-1’;
    v1(0) := ‘0’;
    v1(1) := ‘1’;
    DBMS_OUTPUT.put_line(v1(-1)); --访问合法
    DBMS_OUTPUT.put_line(v1(2)); --访问非法

    Sql 代码

    declare
    –定义了一个 pl/sql 表类型 sp_table_type,该类型是用于存放emp.ename%type
    –index by binary_integer 表示下标是整数
    type emp_table_type is table of emp.ename%type
    index by binary_integer;
    –定义了一个 sp_table 变量,这个变量的类型是 sp_table_type
    sp_table emp_table_type;
    begin
    select ename into sp_table(-1) from emp where empno = 7788;
    dbms_output.put_line(‘员工名:’ || sp_table(-1));
    end;

    说明:
    sp_table_type 是 pl/sql 表类型
    emp.ename%type 指定了表的元素的类型和长度
    sp_table 为 pl/sql 表变量
    sp_table(0) 则表示下标为 0 的元素
    注意:如果把 select ename into sp_table(-1) from emp where empno = 7788;
    变成 select ename into sp_table(-1) from emp;则运行时会出现错误,错误如下:
    ORA-01422:实际返回的行数超出请求的行数
    解决方法是:使用参照变量(这里不讲)

    1、 PL/SQL 表方法
    方法 描述
    EXISTS(n) 用于判断PL/SQL表中指定的元素是否存在。
    COUNT 返回一个PL/SQL表当前包含的元素的数量。
    FIRST
    LAST 在PL/SQL表中返回第一个和最后一个索引数字。
    DELETE
    DELETE把所有的元素从PL/SQL表中移开。
    DELETE(n)把第n 个元素从PL/SQ表中移开。
    DELETE(m,n)从PL/SQ表中移开在范围(m,n)中的所有元素。

    DECLARE
    TYPE e_table_type IS TABLE OF emp.Ename%TYPE INDEX BY BINARY_INTEGER;
    e_tab e_table_type;
    v_var NUMBER(2);
    BEGIN
    e_tab(1) := ‘SMITH’;
    UPDATE emp SET sal = 1.1 * sal WHERE Ename = e_tab(1);
    v_var := e_tab.count;
    dbms_output.put_line(v_var);
    e_tab.delete(1);
    v_var := e_tab.count;
    dbms_output.put_line(v_var);
    END;

    DECLARE
    TYPE dept_table_type IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER;
    my_dname_table dept_table_type;
    v_count NUMBER(3);
    BEGIN
    v_count := -10;
    my_dname_table(v_count).dname := ‘searching’;
    dbms_output.put_line(my_dname_table(v_count).dname);
    v_count := 0;
    my_dname_table(v_count).dname := ‘market’;
    dbms_output.put_line(my_dname_table(v_count).dname);
    v_count := 10;
    my_dname_table(v_count).dname := ‘finance’;
    dbms_output.put_line(my_dname_table(v_count).dname);
    END;

    2、 例一

    drop table test ;
    create table test(
    id number(20),
    value varchar2(50)
    );

    DECLARE
    TYPE test_record IS RECORD(
    id NUMBER(5),
    VALUE VARCHAR(10));
    TYPE test IS TABLE OF test_record INDEX BY BINARY_INTEGER;
    v_test test;
    v_starTime NUMBER(10);
    v_endTime NUMBER(10);
    BEGIN

    --赋值
    FOR i IN 1 .. 5000 LOOP
        v_test(i).id := i;
        v_test(i).value := to_char(floor(dbms_random.value(10, 101)));
    END LOOP;
    --查看值
    v_starTime := dbms_utility.get_time;
    FOR i IN v_test.first .. v_test.last LOOP
        INSERT INTO test VALUES (v_test(i).id, v_test(i).value);
    END LOOP;
    v_endTime := dbms_utility.get_time;
    Dbms_Output.put_line('所用时间:' || (v_endTime - v_starTime) / 100);
    

    END;

    DECLARE
    TYPE test_record IS RECORD(
    id NUMBER(5),
    VALUE VARCHAR(10));
    TYPE test1 IS TABLE OF test_record INDEX BY BINARY_INTEGER;
    v_test1 test1;
    v_starTime NUMBER(10);
    v_endTime NUMBER(10);
    BEGIN
    FOR i IN 1 … 5000 LOOP
    v_test1(i).id := i;
    v_test1(i).value := to_char(floor(dbms_random.value(10, 101)));
    END LOOP;
    v_starTime := dbms_utility.get_time;
    FORALL i IN v_test1.first … v_test1.last
    INSERT INTO test VALUES (v_test1(i).id, v_test1(i).value);
    v_endTime := dbms_utility.get_time;
    Dbms_Output.put_line(‘所用时间:’ || (v_endTime - v_starTime) / 100);
    END;

    DECLARE
    TYPE test1 IS TABLE OF dept%ROWTYPE;
    v_test1 test1;
    BEGIN
    SELECT * BULK COLLECT INTO v_test1 FROM dept;
    FOR i IN v_test1.first … v_test1.last LOOP
    Dbms_Output.put_line(‘部门名称:’ || v_test1(i).dname);
    END LOOP;
    END;

    探讨:有关PL/SQL表类型中的理解。
    很容易认为PL/SQL表类型象c或Java中的数组类型,因为其中有个关键字叫做“index”(索引),而数组的下标也叫做“index”。
    但是这样无法解释PL/SQL表类型的索引可以不连续,甚至可以为负数的问题。
    我认为,PL/SQL表类型更象是java中的map对象,索引就对应map中的键,多个值之间的键可以不连续,可以为负数,甚至可以是字符串。两者有异曲同工之妙。
    示例:用字符串做索引。
    declare
    –定义表结构
    type myTabType is table of char(2) index by varchar2(5);
    sexTab myTabType;

    v_sex_code varchar2(5);
    v_sex_name char(2);
    begin
    –往表中添加内容
    sexTab(‘nan’):=‘男’;
    sexTab(‘nv’):=‘女’;
    –用户输入
    v_sex_code:=’&性别编号’;
    v_sex_name:=sexTab(v_sex_code);

    dbms_output.put_line('您的性别是 = '||v_sex_name);
    end;
    输入:nan
    输出: 男
    联想一下下:
    ·在PL/SQL表中,利用PL/SQL表类型,在某些场合也能够避免减少表连接,从而大大提高连接的效率。
    ·在java中map对象用途非常广泛,可以缓存很多对象(如配置文件对象,常见的查询结果对象等)。两者的用法真的是异曲同工之妙。
    案例:
    SQL> declare
    2 type table_emp is table of emp%rowtype
    3 index by binary_integer;
    4 empt table_emp;
    5 begin
    6 empt(1).ename:=‘wangyi’;
    7 dbms_output.put_line(empt(1).ename);
    8 end;
    9 /

    //返回总记录
    SQL> declare
    2 type table_emp is table of emp%rowtype
    3 index by binary_integer;
    4 empt table_emp;
    5 begin
    6 dbms_output.put_line(empt.count);
    7 end;
    8 /

    0 //没有记录

    //删除的操作
    表名.Delete(记录数);
    //检索记录变量
    First:获取第一个的索引
    Next:下一个的索引 但是必须有参数
    Last:最后一个的索引
    SQL> declare
    2 type table_emp is table of emp%rowtype
    3 index by binary_integer;
    4 empt table_emp;
    5 i number(2):=1;
    6 begin
    7 while i<10
    8 loop
    9 empt(i).ename:=‘wangyi’;
    10 i:=i+1;
    11 end loop;
    12
    13 dbms_output.put_line(empt.count);
    14
    15 empt.delete(2);
    16
    17 dbms_output.put_line(empt.count);
    18
    19 dbms_output.put_line(empt.first);
    20 dbms_output.put_line(empt.next(2));
    21 dbms_output.put_line(empt.last);
    22 end;
    23 /

    oracle中type is table 类型删除
    最近写存储过程,遇到一个问题,使用批量插入数据老是出错,说违反唯一性约束,最后检查存储过程,发现type table数据没有删除而引起的,存储过程如下:
    1 type type_char2 is table of NVARCHAR2(30) index by binary_integer; --定义一个字符串的临时表类型
    2 v_card_id type_char2;
    3 --下面是游标
    4 cursor cur_bt_data is
    5 select * from test…;
    6 --遍历游标
    7 for bt_row in cur_bt_data loop
    8 for i in 1 … bt_row.confirm_quanlity loop
    9 v_card_id(i) := to_number(bt_row.iccid_start)+i-1;
    10 end loop;
    11 forall i in 1 … v_card_id.count
    12 insert /*+ append */
    13 into demo
    14 (card_id,…)
    15 values
    16 (v_card_id(i),…);
    17 commit;
    18 end loop; – [END]for ‘cur_bt_data’

    发现里面的v_card_id(i)问题,如果查询的 bt_row.confirm_quanlity 数量一样,循环初始化,数据应该没有问题,如果数量不一样,如,前一次查询的数量大,后一次的数量小,v_card_id没有初始化后面的,就可能出现重复的结果,所有每次循环都必须清理临时表
    查了相关资料,只需要使用v_card_id.delete就删除临时表
    修改如下:
    19 for bt_row in cur_bt_data loop
    20 v_card_id.delete;
    21 for i in 1 … bt_row.confirm_quanlity loop
    22 …

    二、 嵌套表
    嵌套表是表中之表。一个嵌套表是某些行的集合,它在主表中表示为其中的一列。对主表中的每一条记录,嵌套表可以包含多个行。在某种意义上,它是在一个表中存储一对多关系的一种方法。
    Nested table is a data type in Oracle which is used to support columns containing multi valued attributes. It also hold entire sub table.
    1.必须进行初始化,否则会出现异常:ORA-06531: Reference to uninitialized collection
    2.初始化方法:
    v1 TYPE2 := TYPE2(); --声明时初始化数组为空

    v2 TYPE2 := TYPE2(‘1’,‘2’,‘3’,‘4’,‘5’); --声明时初始化数组为5个元素
    v1 := TYPE2(); --初期化后数组为空
    v2 := TYPE2(‘1’,‘2’,‘3’,‘4’,‘5’); --初期化后数组为5个元素
    3.数组元素的访问:
    下标从1开始,不能超过数组所有元素的总和,当下标超出允许范围时,出现异常:ORA-06532: Subscript outside of limit
    因为不能访问空数组,所以空数组的场合,必须进行数组扩展。 54ne.com
    例:v1.EXTEND;
    V1(1):= ‘1’; --访问合法
    v1(2):= ‘2’; --访问非法,之前必须再次执行v1.EXTEND; 网管网bitsCN_com
    例:v2的下标范围是1~5。
    v2(5):= ‘Hello’; --访问合法
    DBMS_OUTPUT.put_line(v2(6)); --访问非法

    Oracle 数据库创建嵌套表
    步骤一:打开SQLPLUS, 输入用户名与密码(本例以Scott 用户登录)
    登录后显示的界面如下:

         图 1-1
    

    步骤二:创建元组类型TDate,在SQLPLUS中输入
    create type TDate as object(day integer, month char(3), year integer);
    换行输入“/”。创建成功:

              图 1-2
    

    步骤三:创建Tcg类型
    输入:
    create type TCg as object(Course varchar2(5), Grade integer, Cdate TDate);
    创建成功,SQLPLUS界面显示如下:

               图 1-3
    

    步骤四:创建嵌套表类型 TCgs
    在SQLPLUS中输入:create type TCgs as table of TCg
    创建成功后:

         图 1-4
    

    步骤四:创建最外层表SC
    在SQLPLUS中输入:
    create table SC(Sname varchar2(10),Cg TCgs)
    nested table Cg store as Cg_tab
    SQLPLUS 界面如下

             图 1-5
    

    步骤五:插入数据
    在SQLPLUS 中输入:
    insert into SC
    values(‘picart’,TCgs(TCg(‘DB’,80,TDate(1, ‘Oct’,2009)),
    TCg(‘OS’,80,TDate(1, ‘Nov’,2009))));
    insert into SC
    values(‘kirk’,TCgs(TCg(‘DB’,85,TDate(1, ‘Jul’,2009)),
    TCg(‘MATHS’,80,TDate(1, ‘Oct’,2009))))
    如下图所示:

    图 1-6
    步骤六: 查询数据
    1)为了验证数据是否插入表中,可输入“select * from sc” 进行查询,如图所示:

    图 1-7
    2)查询每个同学选课的门数
    在SQLPLUS 中输入:
    select sname, (select count(*) from table (x.Cg))
    from SC x
    结果如图:

    图 1-8
    3)查询”kirk” 选择课程的详细信息
    在SQLPLUS 中输入:
    select * from (select cg from sc where sname=’kirk’);
    结果下:

                              图 1-9
    

    4)查询”kirk” 选择的课程名
    在SQLPLUS 中输入:
    select x.course from table(select cg from sc where sname=’kirk’) x;
    结果如下:

    图 2-1
    步骤七:修改数据
    修改”kirk”的选课信息,将“DB”课程修改为“orcl”
    在SQLPLUS 中输入:
    Update table(select cg from sc where sname=’kirk’) x set x.course=’orcl’ where x.course=’DB’;
    然后输入:select x.course from table(select cg from sc where sname=’kirk’) x 查询修改的结果
    结果如下:

    图 2-2
    修改”kirk”的选课时间,将其选择课程‘orcl’的选课时间改为“21,Oct,2011”
    在SQLPLUS 中输入:
    Update table(select cg from sc where sname=’kirl’) set cdate=tdate(21,’Oct’,2011) where course=’orcl’;
    然后输入:select * from table(select cg from sc where sname=’kirk’) where course=’orcl’
    结果如图:

    图 2-3
    修改修改”kirk”的选课时间,将其选择课程‘orcl’的选课时间的年份改为2010年
    在SQLPLUS 中输入:
    update table(select cg from sc where sname=’kirk’) x set x.cdate.year=2010 where x.course=’orcl’;
    然后输入:select * from table(select cg from sc where sname=’kirk’) where course=’orcl’
    结果如图:

    图 2-4
    步骤八:删除SC表中的数据
    删除‘kirk‘所选的课程’orcl‘的信息
    在SQLPLUS 中输入:
    delete from table(select cg from sc where sname=’kirk’) where x.course=’orcl’;
    然后输入:select * from table(select cg from sc where sname=’kirk’)查询
    结果如图:

    2)删除‘kirk‘的选课信息
    在SQLPLUS 中输入:
    delete from sc where sname=’kirk’
    然后输入:select * from sc 查询

                      ORACLE嵌套表的使用
    

    考查一个包含部门信息的表,在任何时间内每个部门会有很多项目正在实施。在一个严格的关系模型中,将需要建立两个独立的表department和project。
    嵌套表允许在department表中存放关于项目的信息。勿需执行联合操作,就可以通过department表直接访问项目表中的记录。这种不经联合而直接选择数据的能力使得用户对数据访问更加容易。甚至在并没有定义方法来访问嵌套表的情况下,也能够很清楚地把部门和项目中的数据联系在一起。在严格的关系模型中,department和project两个表的联系需要通过外部关键字(外键)关系才能实现。
    下面以电信账单打印程序中的数据库设计来举例说明嵌套表的使用方法。
     1、创建类型ITEM_TYPE:此类型中,对于每个ITEM_TYPE类型都包含有一个记录,记载了其名称、类型、层次和数量信息。

    CREATE OR REPLACE TYPE “ITEM_TYPE” As Object
    (
    Item_Seq Number,
    Item_Name Varchar2(100),
    Item_Type Varchar2(100),
    Item_Level Varchar2(100),
    Item_Amt Number
    )
    2、创建ITEM_LIST_TYPE:此类型将用作一个嵌套表的基础类型。

    CREATE OR REPLACE TYPE “ITEM_LIST_TYPE” As Table Of item_type
    3、创建表T_MAC_INFO:设备资费信息表

    – Create table
    create table T_MAC_INFO
    (
    CHARGE_NO VARCHAR2(100),
    MAC_TYPE VARCHAR2(100),
    SUBTOTAL NUMBER,
    ITEMS ITEM_LIST_TYPE,
    MAC_SEQ NUMBER,
    PRODUCT_SEQ NUMBER
    )
    nested table ITEMS store as ITEMS_TAB

    4、向嵌套表中插入记录

    主要代码以下:

        变量声明:
          Item_List1    Item_List_Type;
    

    Item1 Item_Type;

    For I in 1…10 to loop
    Item_List1.Extend;
    Item_List1(Item_List1.Count) := Item_Type(Item_List1.Count, Item1.Item_Name, Item1.Item_Type, Item1.Item_Level, Item1.Item_Amt);
    End loop;

    Insert Into t_Mac_Info
    ( --Cust_Id,
    Product_Seq,
    Mac_Seq,
    Charge_No,
    Mac_Type,
    Subtotal,
    Items)
    Values
    ( --Mac_Info1.Cust_Id,
    v_Seq_Product, – Mac_Info1.Product_Seq,
    Mac_Seq,
    V_Charge_No,
    V_Mac_Type,
    V_Subtotal,
    Item_List1);

    commit;

    其中ITEM_LIST1作为一嵌套表,被插入保存在T_MAC_INFO这个表里面。

    5、查询嵌套表

    Select * From t_Mac_Info d, Table(d.Items) Emp

    三、嵌套表的特点:
    对于嵌套表,在ORACLE 的OOP编程里面使用得多一点。在数据库中使用关系-对象模型,具体表现为对象复用、标准支持、定义访问路径等,这些都是OOP编程的好处:
    1、对象复用:如果编写面向对象的代码,就提高了重用以前编写的代码模块的机会。同样,如果创建面向对象的数据库对象,也就提高了数据库对象能够被重用的机会。
    2、标准支持:如果创建标准的对象,那么它们被重用的机会就会提高。如果有多个应用或多个表使用同一数据库对象集合,那么它就是既成事实的数据库对象标准。
    3、定义访问路径:对于每一个对象,用户可定义在其上运行的过程和函数,从而可以使数据和访问此数据的方法联合起来。有了用这种方式定义的访问路径,就可以标准化数据访问的方法并提高对象的可复用性。
    以上举例说明了嵌套表的简单使用,对于嵌套表的详细用法,可以参考相关书籍。
    三、 可变数组
    元素下标从0开始,数组变量的声明需要同时初始化
    一般格式为:
    TYPE type_name IS VARRAY (maximum_size) OF element_type

    其中:

    1. type_name是新可变长数组类型的类型名,maximum_size是一个指定可变数组中元素最大数目的整数。element_type是一个PL/SQL标量、记录或对象类型。

    一般格式为:
    DECLARE
    type numberlist is varray(10) of number(5);
    type recordlist is varray(5) of dept%rowtype;

    例: DECLARE
    type strings is varray(5) of varchar2(10);
    – Declare a varray with four element
    v_list strings := strings(‘scott’,‘peter’,‘smith’,‘tom’);
    v_count number;
    BEGIN
    v_count := 1;
    dbms_output.put_line(v_list(v_count));
    v_list(v_count) := ‘urman’;
    dbms_output.put_line(v_list(v_count));
    v_count := 3;
    dbms_output.put_line(v_list(v_count));
    v_list(v_count) := ‘jackson’;
    dbms_output.put_line(v_list(v_count));
    v_list(4):=‘oracle’;
    dbms_output.put_line(v_list(4));
    END;

    TYPE TYPE3 IS ARRAY(5) OF VARCHAR2(10);

    由于类型定义时的元素个数限制,所以TYPE3的变量在使用时最大的元素个数不能超过5个。与嵌套表基本相同(略)
    四、 表和数组的属性

    例:DECLARE
    type strings_table is table of varchar2(10) index by
    binary_integer;
    strings strings_table;
    v_count number;
    BEGIN
    v_count := 1;
    strings(v_count ) := ‘element1’;
    if strings.exists(v_count ) then
    dbms_output.put_line(strings(v_count ));
    else
    dbms_output.put_line(‘no data!’);
    end if;
    –to be continued例:DECLARE
    type strings_table is table of varchar2(10) index by
    binary_integer;
    strings strings_table;
    v_count number;
    BEGIN
    v_count := 1;
    strings(v_count ) := ‘element1’;
    if strings.exists(v_count ) then
    dbms_output.put_line(strings(v_count ));
    else
    dbms_output.put_line(‘no data!’);
    end if;
    –to be continued

    例:
    strings(2) := ‘element2’;
    strings(3) := ‘element3’;
    strings(4) := ‘element4’;
    strings(5) := ‘element5’;
    strings(6) := ‘element6’;
    dbms_output.put_line(strings.count);
    dbms_output.put_line(strings.first);
    dbms_output.put_line(strings.last);
    dbms_output.put_line(strings.next(2));
    dbms_output.put_line(strings.prior(4));
    strings.delete(1,3);–delete from 1 to 3
    –dbms_output.put_line(strings(2)); – cann’t find data
    END;

    全面探讨PL/SQL的复合数据类型
    PL/SQL有两种复合数据结构:记录和集合。记录由不同的域组成,集合由不同的元素组成。在本文中我们将讨论记录和集合的类型、怎样定义和使用记录和集合。
      PL/SQL 记录
      记录是PL/SQL的一种复合数据结构,scalar数据类型和其他数据类型只是简单的在包一级进行预定义,但复合数据类型在使用前必须被定义,记录之所以被称为复合数据类型是因为他由域这种由数据元素的逻辑组所组成。域可以是scalar数据类型或其他记录类型,它与c语言中的结构相似,记录也可以看成表中的数据行,域则相当于表中的列,在表和虚拟表(视图或查询)中非常容易定义和使用,行或记录中的每一列或域都可以被引用或单独赋值,也可以通过一个单独的语句引用记录所有的域。在存储过程或函数中记录也可能有参数。
      创建记录
      在PL/SQL中有两种定义方式:显式定义和隐式定义。一旦记录被定义后,声明或创建定义类型的记录变量,然后才是使用该变量。隐式声明是在基于表的结构或查询上使用%TYPE属性,隐式声明是一个更强有力的工具,这是因为这种数据变量是动态创建的。
      显式定义记录
      显式定义记录是在PL/SQL程序块中创建记录变量之前在声明部分定义。使用type命令定义记录,然后在创建该记录的变量。语法如下:
      TYPE record_type IS RECORD (field_definition_list);
      field_definition_list是由逗号分隔的列表。
      域定义的语法如下:
      field_name data_type_and_size [NOT NULL][{:=|DEFAULT} default_value]
      域名必须服从与表或列的命名规则相同的命名规则。下面我们看一个例子:
      DELCARE
      TYPE stock_quote_rec IS RECORD
      (symbol stock.symbol%TYPE
      ,bid NUMBER(10,4)
      ,ask NUMBER(10,4)
      ,volume NUMBER NOT NULL:=0
      ,exchange VARCHAR2(6) DEFAULT ‘NASDAQ’
      );
      real_time_quote stock_quote_rec;
      variable
      域定义时的%TYPE属性用于引用数据库中的表或视图的数据类型和大小,而在此之前程序不知道类型和大小。在上面的例子中记录域在编译时将被定义为与列SYMBOL相同的数据类型和大小,当代码中要使用来自数据库中的数据时,在变量或域定义中最好使用%TYPE来定义。
      隐式定义记录
      隐式定义记录中,我们不用描述记录的每一个域。这是因为我们不需要定义记录的结构,不需要使用TYPE语句,相反在声明记录变量时使用%ROWTYPE命令定义与数据库表,视图,游标有相同结构的记录,与TYPE命令相同的是它是一种定义获得数据库数据记录的好方法。
      DECLARE
      accounter_info accounts%ROWTYPR;
      CURSOR xactions_cur(acct_no IN VARCHAR2) IS
      SELECT action,timestamp,holding
      FROM portfolios
      WHERE account_nbr=‘acct_no’
      ;
      xaction_info xactions_cur%ROWTYPE;
      variable
      有一些PL/SQL指令在使用隐式定义记录时没有使用%ROWTYPE属性,比如游标FOR循环或触发器中的:old和:new记录。
      DELCARE
      CURSOR xaction_cur IS
      SELECT action,timeamp,holding
      FROM portfolios
      WHERE account_nbr=‘37’
      ;
      BEGIN
      FOR xaction_rec in xactions_cur
      LOOP
      IF xactions_rec.holding=‘ORCL’
      THEN
      notify_shareholder;
      END IF;
      END LOOP;
      使用记录
      用户可以给记录赋值、将值传递给其他程序。记录作为一种复合数据结构意味作他有两个层次可用。用户可以引用整个记录,使用select into或fetch转移所有域,也可以将整个记录传递给一个程序或将所有域的值赋给另一个记录。在更低的层次,用户可以处理记录内单独的域,用户可以给单独的域赋值或者在单独的域上运行布尔表达式,也可以将一个或更多的域传递给另一个程序。
      引用记录
      记录由域组成,访问记录中的域使用点(.)符号。我们使用上面的例子看看
      DELCARE
      TYPE stock_quote_rec IS RECORD
      (symbol stock.symbol%TYPE
      ,bid NUMBER(10,4)
      ,ask NUMBER(10,4)
      ,volume NUMBER NOT NULL:=0
      ,exchange VARCHAR2(6) DEFAULT ‘NASDAQ’
      );
      TYPE detailed_quote_rec IS RECORD
      (quote stock_quote_rec
      ,timestamp date
      ,bid_size NUMBER
      ,ask.size NUMBER
      ,last_tick VARCHAR2(4)
      );
      real_time_detail detail_quote_rec;
      BEGIN
      real_time_detail.bid_size:=1000;
      real_time_detail.quote.volume:=156700;
      log_quote(real_time_detail.quote);
      给记录赋值
      给记录或记录中的域赋值的方法有几种,可以使用SELECT INTO或FETCH给整个记录或单独的域赋值, 可以将整个记录的值赋给其他记录,也可以通过给每个域赋值来得到记录,以下我们通过实例讲解每一种赋值方法。
      1、使用SELECT INTO
      使用SELECT INTO给记录赋值要将记录或域放在INTO子串中,INTO子串中的变量与SELECT中列的位置相对应。
      例:
      DECLARE
      stock_info1 stocks%ROWTYPE;
      stock_info2 stocks%ROWTYPE;
      BEGIN
      SELECT symbol,exchange
      INTO stock_info1.symbol,stock_info1.exchange
      FROM stocks
      WHERE symbol=‘ORCL’;
      SELECT * INTO stock_info2 FROM stocks
      WHERE symbol=‘ORCL’;
      2、使用FETCH
      如果SQL语句返回多行数据或者希望使用带参数的游标,那么就要使用游标,这种情况下使用FETCH语句代替INSTEAD INTO是一个更简单、更有效率的方法,但在安全性较高的包中FETCH的语法如下:
      FETCH cursor_name INTO variable;
      我们改写上面的例子:
      DECLARE
      CURSOR stock_cur(symbol_in VARCHAR2) IS
      SELECT symbol,exchange,begin_date
      FROM stock
      WHERE symbol=UPPER(symbol_in);
      stock_info stock_cur%ROWTYPE
      BEGIN
      OPEN stock_cur(‘ORCL’);
      FETCH stock_cur INTO stock_info;
      使用赋值语句将整个记录复制给另一个记录是一项非常有用的技术,不过记录必须精确地被声明为相同的类型,不能是基于两个不同的TYPE语句来获得相同的结构。
      例:
      DECLARE
      TYPE stock_quote_rec IS RECORD
      (symbol stocks.symbol%TYPE
      ,bid NUMBER(10,4)
      ,ask number(10,4)
      ,volume NUMBER
      );
      TYPE stock_quote_too IS RECORD
      (symbol stocks.symbol%TYPE
      ,bid NUMBER(10,4)
      ,ask number(10,4)
      ,volume NUMBER
      );
      --这两个记录看上去是一样的,但实际上是不一样的
      stock_one stocks_quote_rec;
      stock_two stocks_quote_rec;
      --这两个域有相同的数据类型和大小
      stock_also stock_rec_too;–与stock_quote_rec是不同的数据类型
      BEGIN
      stock_one.symbol:=‘orcl’;
      stock_one.volume:=1234500;
      stock_two:=stock_one;–正确
      syock_also:=stock_one;–错误,数据类型错误
      stock_also.symbol:=stock_one.symbol;
      stock_also.volume:=stock_one.volume; 
      记录不能用于INSERT语句和将记录直接用于比较,下面两种情况是错误的:
      INSERT INTO stocks VALUES (stock_record);
      和
      IF stock_rec1>stock_rec2 THEN
      要特别注意考试中试题中有可能用%ROWTYPE来欺骗你,但这是错误的,记住这一点。还有可能会出现用记录排序的情况,ORACLE不支持记录之间的直接比较。对于记录比较,可以采用下面的两个选择:
      . 设计一个函数,该函数返回scalar数据类型,使用这个函数比较记录,如
      IF sort_rec(stock_one)>sort_rec(stock_two) THEN
      . 可以使用数据库对象,数据库对象可以使用order或map方法定义,允许oracle对复合数据类型进行比较。关于数据库对象的讨论已经超越了本文的范围,要详细了解数据库对象,可以查阅oracle手册。
      PL/SQL集合
      集合与其他语言中的数组相似,在ORACLE7.3及以前的版本中只有一种集合称为PL/SQL表,这种类型的集合依然保留,就是索引(INDEX_BY)表,与记录相似,集合在定义的时候必须使用TYPE语句,然后才是创建和使用这种类型的变量。
      集合的类型
      PL/SQL有三种类型的集合
      . Index_by表
      . 嵌套表
      . VARRAY
      这三种类型的集合之间由许多差异,包括数据绑定、稀疏性(sparsity)、数据库中的存储能力都不相同。绑定涉及到集合中元素数量的限制,VARRAY集合中的元素的数量是有限,Index_by和嵌套表则是没有限制的。稀疏性描述了集合的下标是否有间隔,Index_by表总是稀疏的,如果元素被删除了嵌套表可以是稀疏的,但VARRAY类型的集合则是紧密的,它的下标之间没有间隔。
      Index_by表不能存储在数据库中,但嵌套表和VARRAY可以被存储在数据库中。
      虽然这三种类型的集合有很多不同之处,但他们也由很多相似的地方:
      . 都是一维的类似数组的结构
      . 都有内建的方法
      . 访问由点分隔
      Index_by表
      Index_by表集合的定义语法如下:
      TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX
      BY BINARY_INTERGET;
      这里面重要的关键字是INDEX BY BINARY_INTERGET,没有这个关键字,那么集合将是一个嵌套表,element_type可以是任何合法的PL/SQL数据类型,包括:PLS/INTEGER、SIGNTYPE、和BOOLEAN。其他的集合类型对数据库的数据类型都有限制,但Ind

    2.1.3 参照变量

    参照变量是指用于存放数值指针的变量。通过使用参照变量,可以使得应用程序共享相同对象,从而降低占用的空间。在编写 pl/sql 程序时,可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。

    一、 游标(cursor)
    参考:Oracle_lhr_游标.docx

    2.1.4 PL/SQL中的变量赋值
    在PL/SQL编程中,变量赋值是一个值得注意的地方,它的语法如下:
    variable := expression ;
    variable 是一个PL/SQL变量, expression 是一个PL/SQL 表达式.

    BOOLEAN 型变量赋值:
    布尔值只有TRUE, FALSE及 NULL 三个值,其中空值在参加算数运算时,结果仍为空值。

    例:DECLARE
    done BOOLEAN;
    /* the following statements are legal: */
    BEGIN
    done := FALSE;
    WHILE NOT done LOOP
    ----do something
    Dbms_output.put_line(‘hello!’);
    Done := TRUE;
    END LOOP;
    END;

    2.2 type定义数据类型
    2.2.1 CREATE OR REPLACE TYPE
    2种定义方式
    2.2.1.1 已存在数据类型
    create or replace type type_glid_lhr is table of number;
    create or replace type type_glid_lhr is table of varchar2(255);

    –只能在包声明、块声明等地方定义,不能在 方案级 使用
    – 错误: create or replace type type_a is table of xb_location%rowtype;
    DECLARE

    TYPE type_a IS TABLE OF xb_location%ROWTYPE;
    v_a_type type_a;
    

    BEGIN
    NULL;
    END;

    2.2.1.2 不存在原数据类型

    – 只能在方案级定义一种新类型
    – create or replace TYPE objemp AS OBJECT(maxsal NUMBER, minsal NUMBER);
    – 错误:
    DECLARE
    TYPE objemp AS OBJECT(maxsal NUMBER, minsal NUMBER);
    v_a_type objemp;
    BEGIN
    NULL;
    END;

    – 正确:
    create or replace TYPE objemp as OBJECT(maxsal NUMBER, minsal NUMBER);
    – 定义表: 正确,用as或is都可以:
    CREATE OR REPLACE TYPE tabemp AS TABLE OF objemp;
    CREATE OR REPLACE TYPE tabemp is TABLE OF objemp;
    DECLARE
    TYPE tabemp is TABLE OF objemp; --这里只能用is
    v_a_type tabemp;
    BEGIN
    NULL;
    END;

    2.2.2 例一
    DECLARE
    v_dept_row dept%ROWTYPE;
    BEGIN
    v_dept_row.deptno := 50;
    v_dept_row.dname := ‘repair’;
    v_dept_row.loc := ‘BeiJing’;
    INSERT INTO dept VALUES v_dept_row;
    END;

    DECLARE
    v_dept_row dept%ROWTYPE;
    BEGIN
    v_dept_row.deptno := &deptno;
    v_dept_row.dname := ‘procduct’;
    v_dept_row.loc := ‘Sydeny’;
    UPDATE dept SET ROW = v_dept_row WHERE deptno = v_dept_row.deptno;
    END;

    ----------------练习1----------------
    create or replace type phone_type is Table of varchar2(50);
    create table tb_info(
    id number(6) primary key,
    vid varchar2(20) not null,
    vname varchar2(20) not null,
    vphone phone_type
    )nested table vphone Store as phone_table;

    INSERT INTO tb_info
    VALUES
    (1, ‘scce001’, ‘dog’, phone_type(‘110’, ‘119’, ‘120’, ‘911’));
    DECLARE
    v_phone_type phone_type;
    BEGIN
    SELECT vphone INTO v_phone_type FROM tb_info WHERE id = 1;
    FOR i IN 1 … v_phone_type.Count LOOP

         Dbms_Output.put_line(v_phone_type(i));
     END LOOP;
    

    END;

    ------------------练习2-------------------------------------------
    create or replace type obj_goods as Object
    (
    price number(5),
    quantity varchar2(14),
    name varchar2(50),
    –定义方法
    member Function getMustPay Return number,–实际付款
    member Function getChange Return number–找零
    );

    CREATE OR REPLACE TYPE BODY obj_goods AS
    MEMBER FUNCTION getMustPay RETURN NUMBER IS
    BEGIN
    RETURN Self.price * Self.quantity;
    END;
    MEMBER FUNCTION getChange RETURN NUMBER IS
    BEGIN
    RETURN Self.price;
    END;
    END;

    DECLARE
    v_obj_goods obj_goods;
    BEGIN
    v_obj_goods := obj_goods(40, ‘5’, ‘香烟’);
    Dbms_Output.put_line(‘商品数量:’ || v_obj_goods.quantity);
    Dbms_Output.put_line(‘商品名称:’ || v_obj_goods.name);
    Dbms_Output.put_line(‘商品总价:’ || v_obj_goods.getMustPay);
    Dbms_Output.put_line(‘商品单价:’ || v_obj_goods.price);
    END;

    2.2.3 例二
    create table parent(
    id number(10),
    name varchar2(100),
    title varchar2(10) );

    create table child(
    id number(10),
    parent_id number(10),
    child_name varchar2(100),
    child_title varchar2(10),
    child_content varchar2(200),
    child_time timestamp );

    create sequence seq_p_c_id
    minvalue 1 maxvalue 9999999999
    start with 1 increment by 1
    nocache;

    drop type t_child_lst_map;
    drop type t_child_lst;
    drop type t_parent_lst;

    CREATE OR REPLACE TYPE t_parent AS OBJECT
    (
    NAME VARCHAR2(100),
    title VARCHAR2(10)
    );

    CREATE OR REPLACE TYPE t_child AS OBJECT
    (
    child_name VARCHAR2(100),
    child_title VARCHAR2(10),
    child_content VARCHAR2(200)
    )
    ;

    create or replace type t_parent_lst as table of t_parent;

    create or replace type t_child_lst as table of t_child;

    create or replace type t_child_lst_map as table of t_child_lst;
    CREATE OR REPLACE PROCEDURE proc_ins_parent_child(i_parent_lst IN t_parent_lst, --parent列表
    i_child_map_lst IN t_child_lst_map, --child列表集合,一个map元素对应一个child_lst,其下标与 parent列表的下标相同。
    o_ret OUT NUMBER) AS
    var_parent t_parent;
    var_child_lst t_child_lst;
    var_child t_child;
    var_parent_id NUMBER;
    var_child_id NUMBER;
    BEGIN
    FOR i IN 1 … i_parent_lst.count LOOP
    –取得parent各列的值
    var_parent := i_parent_lst(i); --取得parent_id;
    SELECT seq_p_c_id.nextVal INTO var_parent_id FROM dual; --插入parent表
    INSERT INTO PARENT
    (id, NAME, title)
    VALUES
    (var_parent_id, var_parent.name, var_parent.title); --取得该parent对应的child列表
    var_child_lst := i_child_map_lst(i);
    FOR j IN 1 … var_child_lst.count LOOP
    var_child := var_child_lst(j); --取得child_id;
    SELECT seq_p_c_id.nextVal INTO var_child_id FROM dual; --插入child表
    INSERT INTO child
    (id,
    parent_id,
    child_name,
    child_title,
    child_content,
    child_time)
    VALUES
    (var_child_id,
    var_parent_id,
    var_child.child_name,
    var_child.child_title,
    var_child.child_content,
    systimestamp);
    END LOOP;
    END LOOP;
    o_ret := 0;
    EXCEPTION
    WHEN OTHERS THEN
    BEGIN
    o_ret := -1;
    RAISE;
    END;
    END proc_ins_parent_child;

    2.2.4 例三
    drop type t_sms_message_lst;
    drop type t_sms_message;

    –此类型中,添加了pk_id
    CREATE OR REPLACE TYPE t_sms_message AS OBJECT
    (
    task_id NUMBER(32),
    sender_num VARCHAR2(21),
    mobile_no VARCHAR2(21),
    param_value VARCHAR2(900),
    pk_id NUMBER(32)
    )
    ;

    create or replace type t_sms_message_lst as table of t_sms_message;

    drop procedure proc_sel_sms_message;

    CREATE OR REPLACE PROCEDURE proc_sel_sms_message(in_task_id IN NUMBER,
    in_count IN NUMBER,
    out_sms_message_lst OUT t_sms_message_lst,
    out_ret OUT NUMBER) AS
    var_cr_sms_message SYS_REFCURSOR;
    var_m_row_id VARCHAR2(1000);
    var_m_task_id sms_message.task_id%TYPE;
    var_m_sender_num sms_message.sender_num%TYPE;
    var_m_mobile_no sms_message.mobile_no%TYPE;
    var_m_param_value sms_message.param_value%TYPE;
    var_m_last_send_time sms_message.last_send_time%TYPE;
    var_t_sms_message t_sms_message;
    var_total_send_amount INTEGER;

    BEGIN

    --查询语句增加了pk_id搜索项
    OPEN var_cr_sms_message FOR
        SELECT ROWID AS row_id,
               task_id,
               sender_num,
               mobile_no,
               param_value,
               pk_id
        FROM   sms_message
        WHERE  task_id = in_task_id
        AND    (sms_message.last_send_time IS NULL OR
              sms_message.last_send_time <>
              (SELECT last_send_time
                 FROM   sms_task
                 WHERE  task_id = in_task_id))
        AND    rownum <= in_count
        FOR    UPDATE;
    
    
    out_sms_message_lst := t_sms_message_lst();
    
    
    SELECT last_send_time
    INTO   var_m_last_send_time
    FROM   sms_task
    WHERE  task_id = in_task_id;
    
    
    LOOP
        --fetch语句增加了pk_id项
        FETCH var_cr_sms_message
            INTO var_m_row_id,
                 var_m_task_id,
                 var_m_sender_num,
                 var_m_mobile_no,
                 var_m_param_value,
                 var_m_pk_id;
        EXIT WHEN var_cr_sms_message%NOTFOUND;
    
    
        --输出对象类型中增加了pk_id项
        var_t_sms_message := t_sms_message(var_m_task_id,
                                           var_m_sender_num,
                                           var_m_mobile_no,
                                           var_m_param_value,
                                           var_m_pk_id);
        out_sms_message_lst.extend;
        out_sms_message_lst(out_sms_message_lst.count) := var_t_sms_message;
    
    
        UPDATE sms_message
        SET    last_send_time = var_m_last_send_time
        WHERE  ROWID = var_m_row_id;
    
    
    END LOOP;
    
    
    CLOSE var_cr_sms_message;
    
    
    out_ret := 0;
    

    EXCEPTION
    WHEN OTHERS THEN
    BEGIN
    out_ret := -1;
    RAISE;
    END;
    END proc_sel_sms_message;

    ORACLE基本数据类型总结
    2013-08-17 21:04 by 潇湘隐者, 138943 阅读, 5 评论, 收藏, 编辑
    ORACLE基本数据类型(亦叫内置数据类型 built-in datatypes)可以按类型分为:字符串类型、数字类型、日期类型、LOB类型、LONG RAW& RAW类型、ROWID & UROWID类型。

    在讲叙字符串类型前,先要讲一下编码。字符串类型的数据可依编码方式分成数据库字符集(CHAR/VARCHAR2/CLOB/LONG)和国际字符集(NCHAR/NVARCHAR2/NCLOB)两种。数据库中的字符串数据都通过字符集将字符转换为数字后(二进制),才存储到数据块中。通过不同的编码集转换,即便是相同的字符,也可能会转换成不同的二进制编码。这也是产生乱码的原因。数据库的编码格式一般是在创建数据库时指定的。当然也可以修改数据库的编码。

    查看数据库视图所包含的数据类型:SELECT * FROM DBA_TYPES WHERE OWNER IS NULL.具体细节情况参见Oracle? Database SQL Language Quick Reference 10/11g 或官方文档

    一 字符串类型

    字符串数据类型还可以依据存储空间分为固定长度类型(CHAR/NCHAR) 和可变长度类型(VARCHAR2/NVARCHAR2)两种.
    
    所谓固定长度:是指虽然输入的字段值小于该字段的限制长度,但是实际存储数据时,会先自动向右补足空格后,才将字段值的内容存储到数据块中。这种方式虽然比较浪费空间,但是存储效率较可变长度类型要好。同时还能减少数据行迁移情况发生。
    

    所谓可变长度:是指当输入的字段值小于该字段的限制长度时,直接将字段值的内容存储到数据块中,而不会补上空白,这样可以节省数据块空间。

    1.1:CHAR类型 CHAR(size [BYTE | CHAR])

    CHAR类型,定长字符串,会用空格填充来达到其最大长度。非NULL的CHAR(12)总是包含12字节信息。CHAR字段最多可以存储2,000字节的信息。如果创建表时,不指定CHAR长度,则默认为1。另外你可以指定它存储字节或字符,例如 CHAR(12 BYTYE) CHAR(12 CHAR).一般来说默认是存储字节,你可以查看数据库参数

    NLS_LENGTH_SEMANTICS的值。

    SQL Code
    SQL> show parameter nls_length_semantics;

    NAME TYPE VALUE


    nls_length_semantics string BYTE

    eg:

    CREATE TABLE TEST

    (

    NAME_OLD CHAR(10),

    NAME_NEW CHAR(10 CHAR)

    )

    INSERT INTO TEST

    ( NAME_OLD, NAME_NEW)

    SELECT ‘ABCDEFGHIJ’ , ‘你清除字节与字符’ FROM DUAL;

    COMMIT;

    INSERT INTO TEST

    ( NAME_OLD, NAME_NEW)

    SELECT ‘你清除字节与字符’ , ‘ABCDEFGHIJ’ FROM DUAL;

    ORA-12899: 列 “SYS”.“TEST”.“NAME_OLD” 的值太大 (实际值: 24, 最大值: 10)
    注意:数据库的NLS_CHARACTERSET 为AL32UTF8,即一个汉字占用三到四个字节。如果NLS_CHARACTERSET为ZHS16GBK,则一个字符占用两个字节。

    如果串的长度小于或等于250(0x01~0xFA), Oracle 会使用1 个字节来表示长度。对于所有长度超过250 的串,都会在一个标志字节0xFE 后跟有两个字节来表示长度。因此,如果有一个包含“Hello World”的VARCHAR2(80),则在块中可能如图12.-1 所示
    在这里插入图片描述

    clip_image002

    1.2: NCHAR类型

    这是一个包含UNICODE格式数据的定长字符串。NCHAR字段最多可以存储2,000字节的信息。它的最大长度取决于国家字符集。另外查询时,如果字段是NCHAR类型,则需要如下书写

    SELECT translated_description FROM product_descriptions

    WHERE translated_name = N’LCD Monitor 11/PM’;

    1.3 VARCHAR类型

    不要使用VARCHAR数据类型。使用VARCHAR2数据类型。虽然VARCHAR数据类型目前是VARCHAR2的同义词,VARCHAR数据类型将计划被重新定义为一个单独的数据类型用于可变长度的字符串相比,具有不同的比较语义。

    1.4: VARCHAR2类型

    变长字符串,与CHAR类型不同,它不会使用空格填充至最大长度。VARCHAR2最多可以存储4,000字节的信息。

    1.5: NVARCHAR2类型

    这是一个包含UNICODE格式数据的变长字符串。 NVARCHAR2最多可以存储4,000字节的信息。

    二. 数字类型

    2.1 NUMBER类型

    NUMBER(P,S)是最常见的数字类型,可以存放数据范围为10130~10126(不包含此值),需要1~22字节(BYTE)不等的存储空间。

    P 是Precison的英文缩写,即精度缩写,表示有效数字的位数,最多不能超过38个有效数字

    S是Scale的英文缩写,可以使用的范围为-84~127。Scale为正数时,表示从小数点到最低有效数字的位数,它为负数时,表示从最大有效数字到小数点的位数

    下面是官方文档的示例

    Actual Data

    Specified As

    Stored As

    123.89

    NUMBER

    123.89

    123.89

    NUMBER(3)

    124

    123.89

    NUMBER(6,2)

    123.89

    123.89

    NUMBER(6,1)

    123.9

    123.89

    NUMBER(3)

    124

    123.89

    NUMBER(4,2)

    exceeds precision

    123.89

    NUMBER(6,-2)

    100

    .01234

    NUMBER(4,5)

    .01234

    .00012

    NUMBER(4,5)

    .00012

    .000127

    NUMBER(4,5)

    .00013

    .0000012

    NUMBER(2,7)

    .0000012

    .00000123

    NUMBER(2,7)

    .0000012

    1.2e-4

    NUMBER(2,5)

    0.00012

    1.2e-5

    NUMBER(2,5)

    0.00001

    2.2 INTEGER类型

    INTEGER是NUMBER的子类型,它等同于NUMBER(38,0),用来存储整数。若插入、更新的数值有小数,则会被四舍五入。

    例如:

    CREATE TABLE TEST

    (

    ID INTEGER

    )

    查看表TEST的DDL定义如下所示

    CREATE TABLE “SYS”.“TEST”

    ( “ID” NUMBER(*,0)

    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

    TABLESPACE “SYSTEM” ;

    INSERT INTO TEST

    SELECT 12.34 FROM DUAL;

    INSERT INTO TEST

    SELECT 12.56 FROM DUAL;

    SQL> SELECT * FROM TEST;

    ID


    12

    13

    2.3 浮点数

    浮点数可以有一个十进制数点任何地方从第一个到最后一个数字,或者可以在所有有没有小数点。指数可能(可选) 用于以下数量增加的范围 (例如, 1.777e-20)。刻度值不适用于浮点数字,因为可以显示在小数点后的位数的数量不受限制。

    二进制浮点数不同数量的值由 Oracle 数据库内部存储的方式。使用小数精度数存储值。完全相同号码存储范围和数量由支持的精度内的所有文本。正是因为使用小数精度(数字 0 到 9) 表示文本存储文本。使用二进制精度 (数字 0 和 1) 存储二进制浮点数。这种存储方案不能代表所有确切地使用小数精度的值。频繁地,将值从十进制转换为二进制的精度时出现的错误时撤消值回从二进制转换为十进制精度。在字面 0.1 是一个这样的例子。

    Oracle 数据库提供了专为浮点数的两种数值数据类型:

    BINARY_FLOAT

    BINARY_FLOAT 是 32 位、 单精度浮点数字数据类型。可以支持至少6位精度,每个 BINARY_FLOAT 的值需要 5 个字节,包括长度字节。

    BINARY_DOUBLE

    BINARY_DOUBLE 是为 64 位,双精度浮点数字数据类型。每个 BINARY_DOUBLE 的值需要 9 个字节,包括长度字节。

    在数字的列中,浮点数有小数精度。在 BINARY_FLOAT 或 BINARY_DOUBLE 的列中,浮点数有二进制的精度。二进制浮点数支持的特殊值无穷大和 NaN (不是数字)。

    您可以指定列在表 2-4 范围内的浮点数。"数字文本"中定义了用于指定浮点数的格式。

    Table 2-3 Floating Point Number Limits

    Value

    Binary-Float

    Binary-Double

    Maximum positive finite value

    3.40282E+38F

    1.79769313486231E+308

    Minimum positive finite value

    1.17549E-38F

    2.22507485850720E-308

    2.5 FLOAT类型

    FLOAT类型也是NUMBER的子类型。

    Float(n),数 n 指示位的精度,可以存储的值的数目。N 值的范围可以从 1 到 126。若要从二进制转换为十进制的精度,请将 n 乘以 0.30103。要从十进制转换为二进制的精度,请用 3.32193 乘小数精度。126 位二进制精度的最大值是大约相当于 38 位小数精度。

    三. 日期类型

    日期类型用于存储日期数据,但是并不是使用一般的格式(2012-08-08)直接存储到数据库的。

    3.1 DATE类型

    DATE是最常用的数据类型,日期数据类型存储日期和时间信息。虽然可以用字符或数字类型表示日期和时间信息,但是日期数据类型具有特殊关联的属性。为每个日期值,Oracle 存储以下信息: 世纪、 年、 月、 日期、 小时、 分钟和秒。一般占用7个字节的存储空间。

    3.2 TIMESTAMP类型

    这是一个7字节或12字节的定宽日期/时间数据类型。它与DATE数据类型不同,因为TIMESTAMP可以包含小数秒,带小数秒的TIMESTAMP在小数点右边最多可以保留9位

    3.3 TIMESTAMP WITH TIME ZONE类型

    这是TIMESTAMP类型的变种,它包含了时区偏移量的值

    3.4 TIMESTAMP WITH LOCAL TIME ZONE类型

    3.5 INTERVAL YEAR TO MOTH

    3.6 INTERVAL DAY TO SECOND

    四. LOB类型

    内置的LOB数据类型包括BLOB、CLOB、NCLOB、BFILE(外部存储)的大型化和非结构化数据,如文本、图像、视屏、空间数据存储。BLOB、CLOB、NCLOB类型

    4.1 CLOB 数据类型

    它存储单字节和多字节字符数据。支持固定宽度和可变宽度的字符集。CLOB对象可以存储最多 (4 gigabytes-1) * (database block size) 大小的字符

    4.2 NCLOB 数据类型

    它存储UNICODE类型的数据,支持固定宽度和可变宽度的字符集,NCLOB对象可以存储最多(4 gigabytes-1) * (database block size)大小的文本数据。

    4.3 BLOB 数据类型

    它存储非结构化的二进制数据大对象,它可以被认为是没有字符集语义的比特流,一般是图像、声音、视频等文件。BLOB对象最多存储(4 gigabytes-1) * (database block size)的二进制数据。

    4.4 BFILE 数据类型

    二进制文件,存储在数据库外的系统文件,只读的,数据库会将该文件当二进制文件处理

    五. RAW & LONG RAW类型

    5.1 LONG类型

    它存储变长字符串,最多达2G的字符数据(2GB是指2千兆字节, 而不是2千兆字符),与VARCHAR2 或CHAR 类型一样,存储在LONG 类型中的文本要进行字符集转换。ORACLE建议开发中使用CLOB替代LONG类型。支持LONG 列只是为了保证向后兼容性。CLOB类型比LONG类型的限制要少得多。 LONG类型的限制如下:

    1.一个表中只有一列可以为LONG型。(Why?有些不明白)

    2.LONG列不能定义为主键或唯一约束,

    3.不能建立索引

    4.LONG数据不能指定正则表达式。

    5.函数或存储过程不能接受LONG数据类型的参数。

    6.LONG列不能出现在WHERE子句或完整性约束(除了可能会出现NULL和NOT NULL约束)

    官方文档描叙如下:

    The use of LONG values is subject to these restrictions:

    A table can contain only one LONG column.

    You cannot create an object type with a LONG attribute.

    LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).

    LONG columns cannot be indexed.

    LONG data cannot be specified in regular expressions.

    A stored function cannot return a LONG value.

    You can declare a variable or argument of a PL/SQL program unit using the LONG datatype. However, you cannot then call the program unit from SQL.

    Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.

    LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.

    If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.

    In addition, LONG columns cannot appear in these parts of SQL statements:

    GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements

    The UNIQUE operator of a SELECT statement

    The column list of a CREATE CLUSTER statement

    The CLUSTER clause of a CREATE MATERIALIZED VIEW statement

    SQL built-in functions, expressions, or conditions

    SELECT lists of queries containing GROUP BY clauses

    SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators

    SELECT lists of CREATE TABLE … AS SELECT statements

    ALTER TABLE … MOVE statements

    SELECT lists in subqueries in INSERT statements

    5.2 LONG RAW 类型,能存储2GB 的原始二进制数据(不用进行字符集转换的数据)

    5.3 RAW类型

    用于存储二进制或字符类型数据,变长二进制数据类型,这说明采用这种数据类型存储的数据不会发生字符集转换。这种类型最多可以存储2,000字节的信息

    六. ROWID & UROWID类型

    在数据库中的每一行都有一个地址。然而,一些表行的地址不是物理或永久的,或者不是ORACLE数据库生成的。

    例如,索引组织表行地址存储在索引的叶子,可以移动。

    例如,外部表的ROWID(如通过网关访问DB2表)不是标准的ORACLE的rowid。

    ORACLE使用通用的ROWID(UROWIDs)的存储地址的索引组织表和外表。索引组织表有逻辑urowids的,和国外表的外urowids,。UROWID这两种类型的存储在ROWID伪(堆组织的表的物理行id)。

    创建基于逻辑的rowid在表中的主键。逻辑的rowid不会改变,只要主键不改变。索引组织表的ROWID伪UROWID数据类型。你可以访问这个伪列,你会堆组织表的ROWID伪(即使用一个SELECT …ROWID语句)。如果你想存储的rowid索引组织表,那么你就可以定义一列的表型UROWID到列检索值的ROWID伪。

    Oracle 11g 数据类型

    1. 字符类型
      

    数据类型

    长度

    说明

    CHAR(n BYTE/CHAR)

    默认1字节,n值最大为2000

    末尾填充空格以达到指定长度,超过最大长度报错。默认指定长度为字节数,字符长度可以从1字节到四字节。

    NCHAR(n)

    默认1字符,最大存储内容2000字节

    末尾填充空格以达到指定长度,n为Unicode字符数。默认为1字节。

    NVARCHAR2(n)

    最大长度必须指定,最大存储内容4000字节

    变长类型。n为Unicode字符数

    VARCHAR2(n BYTE/CHAR)

    最大长度必须指定,至少为1字节或者1字符,n值最大为4000

    变长类型。超过最大长度报错。默认存储的是长度为0的字符串。

    VARCHAR

    同VARCHAR2

    不建议使用

    1. 数字类型
      

    数据类型

    长度

    说明

    NUMBER(p[,s])

    1-22字节。

    P取值范围1到38

    S取值范围-84到127

    存储定点数,值的绝对值范围为1.0 x 10 -130至1.0 x 10 126。值大于等于1.0 x 10 126时报错。p为有意义的10进制位数,正值s为小数位数,负值s表示四舍五入到小数点左部多少位。

    BINARY_FLOAT

    5字节,其中有一长度字节。

    32位单精度浮点数类型。

    符号位1位,指数位8位,尾数位23位。

    BINARY_DOUBLE

    9字节,其中有一长度字节。

    64位双精度浮点数类型。

    1. 时间、时间间隔类型
      

    时间字段可取值范围:

    时间字段

    时间类型有效值

    时间间隔类型有效值

    YEAR

    -4712至9999,包括0

    任何整数

    MONTH

    01至12

    0至11

    DAY

    01至31

    任何整数

    HOUR

    00 至 23

    0 至 23

    MINUTE

    00 至 59

    0至 59

    SECOND

    00 to 59.9(n),9(n)不适用与DATE类型

    0 to 59.9(n)

    TIMEZONE_HOUR

    -1至14,不适用与DATE和TIMESTAMP类型

    不可用

    TIMEZONE_MINUTE

    00至59,不适用与DATE和TIMESTAMP类型

    不可用

    TIMEZONE_REGION

    不可用

    TIMEZONE_ABBR

    不可用

    时间、时间间隔类型:

    数据类型

    长度

    说明

    DATE

    7字节

    默认值为SYSDATE的年、月,日为01。包含一个时间字段,若插入值没有时间字段,则默认值为:00:00:00 or 12:00:00 for 24-hour and 12-hour clock time。没有分秒和时间区。

    TIMESTAMP [(fractional_seconds_precision)]

    7至11字节

    fractional_seconds_precision为Oracle存储秒值小数部分位数,默认为6,可选值为0到9。没有时间区。

    TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

    13字节

    使用UTC,包含字段YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_

    HOUR, TIMEZONE_MINUTE

    TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

    7至11字节

    存时使用数据库时区,取时使用回话的时区。

    INTERVAL YEAR [(year_precision)] TO MONTH

    5字节

    包含年、月的时间间隔类型。year_precision是年字段的数字位数,默认为2,可取0至9。

    INTERVAL DAY [(day_precision)]

    TO SECOND [(fractional_seconds_precision)]

    11字节

    day_precision是月份字段的数字位数,默认为2,可取0至9。

    TO_DATE()、DATE使用的时间字段值都是午夜值。或者使用TRUNC()函数进行过滤,确保时间字段为午夜值。
    时间和时间间隔类型操作规则:
    在DATE和TIMESTAMP(会被转化为DATE类型值)类型上加、减NUMBER类型常量,该常量单位为天数。

    所有TIMESTAMP类型运算都以UTC时间为准。即对于TIMESTAMP WITH LOCAL TIME ZONE来说,先转化为UTC时间,计算完成后再转化回来。

    INTERVAL YEAR TO MONTH常量:
    INTERVAL‘year-month’YEAR/MONTH(precision) TO MONTH

    year位数超过precision时,返回一个错误。

    其中precision为最大的位数,默认为2,可取0到9。

    例子:INTERVAL ‘123-2’ YEAR(3) TO MONTH 、

    INTERVAL ‘123’ YEAR(3) 、

    INTERVAL ‘300’ MONTH(3)。

    INTERVAL DAY TO SECOND常量:
    INTERVAL ‘n/time_expr/n time_expr’ DAY/HOUR/MINUTE(leading_precision) TO HOUR/MINUTE/SECOND(fractional_second_precision)

    INTERVAL ‘n/time_expr’ SECOND(leading_precision, fractional_second_precision)

    time_expr格式:HH[:MI[:SS[.n]]] or MI[:SS[.n]] or SS[.n] 若n大于分秒精度,则四舍五入n。

    只有当第一个字段是DAY时,才可以使用n time_expr。

    leading_precision默认为2,可取0至9。

    1. 大对象类型
      

    数据类型

    长度

    说明

    BLOB

    最大为(4GB-1)*数据库块大小

    存储非结构化二进制文件。支持事务处理。

    CLOB

    最大为(4GB-1)*数据库块大小

    存储单字节或者多字节字符数据。支持事务处理。

    NCLOB

    最大为(4GB-1)*数据库块大小

    存储Unicode数据。支持事务处理。

    BFILE

    最大为2 32-1字节

    LOB地址指向文件系统上的一个二进制文件,维护目录和文件名。不参与事务处理。只支持只读操作。

    LOB列包含一个LOB地址,指向数据库内或者数据库外的LOB类型值。
    5. 其他类型
    数据类型

    长度

    说明

    LONG

    最大为2GB

    变长类型,存储字符串。创建表时不要使用该类型。

    RAW(n)

    最大2000字节,n为字节数,必须指定n

    变长类型,字符集发生变化时不会改变值。

    LONG RAW

    最大为2GB

    变长类型,不建议使用,建议转化为BLOB类型,字符集发生变化时不会改变值。

    ROWID

    10字节

    代表记录的地址。显示为18位的字符串。用于定位数据库中一条记录的一个相对唯一地址值。通常情况下,该值在该行数据插入到数据库表时即被确定且唯一。

    UROWID(n)

    ROWID:数据对象编号32位(6个字符)、文件编号10位(3个字符)、块编号22位(6个字符)、行编号16位(3个字符)
    使用dqms_rowid包获得ROWID信息:
    select dbms_rowid.rowid_object(rowid) object_id, dbms_rowid.rowid_relative_fno(rowid) file_id, dbms_rowid.rowid_block_number(rowid) block_id, dbms_rowid.rowid_row_number(rowid) num
    文章参考:

    http://blog.itpub.net/26736162/viewspace-2149685
    
    展开全文
  • 详细介绍MySQL中的数据类型

    万次阅读 多人点赞 2022-04-12 21:46:07
    主要介绍了mysql中的数据类型,主要介绍整数类型,浮点类型,日期类型。开发中每个表都会存在主键和更新时间,这时候选择那个数据类型作为主键也是很重要的,如果选择的数据类型太大会浪费空间,如果选择小了会影响...

    MySQL数据类型精讲

    1.MySQL中的数据类型

    image-20220405111750005

    常见数据类型的属性,如下:

    image-20220405111820593

    2.整数类型

    2.1 类型介绍

    整数类型一共有 5 种,包括 TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER)和 BIGINT

    image-20220405112024144

    2.2可选属性

    整数类型的可选属性有三个:

    2.2.1 M

    M : 表示显示宽度,M的取值范围是(0, 255)。例如,int(5):当数据宽度小于5位的时候在数字前面需要用字符填满宽度。该项功能需要配合“ ZEROFILL ”使用,表示用“0”填满宽度,否则指定显示宽度无效。

    如果设置了显示宽度,那么插入的数据宽度超过显示宽度限制,会不会截断或插入失败?

    答案:不会对插入的数据有任何影响,还是按照类型的实际宽度进行保存,即 显示宽度与类型可以存储的 值范围无关 。从MySQL 8.0.17开始,整数数据类型不推荐使用显示宽度属性。整型数据类型可以在定义表结构时指定所需要的显示宽度,如果不指定,则系统为每一种类型指定默认的宽度值。

    举例:

    CREATE TABLE test_int1 ( x TINYINT, y SMALLINT, z MEDIUMINT, m INT, n BIGINT );

    查看表结构 (MySQL5.7中显式如下,MySQL8中不再显式范围)

    image-20220405112808037

    TINYINT有符号数和无符号数的取值范围分别为-128127和0255,由于负号占了一个数字位,因此TINYINT默认的显示宽度为4。同理,其他整数类型的默认显示宽度与其有符号数的最小值的宽度相同。

    举例:

    CREATE TABLE test_int2(
    	f1 INT,
    	f2 INT(5),
    	f3 INT(5) ZEROFILL )
    DESC test_int2; 
    INSERT INTO test_int2(f1,f2,f3) 
    VALUES(1,123,123); 
    INSERT INTO test_int2(f1,f2) 
    VALUES(123456,123456); 
    INSERT INTO test_int2(f1,f2,f3) 
    VALUES(123456,123456,123456)
    

    image-20220405113043303

    2.2.2 UNSIGNED

    UNSIGNED : 无符号类型(非负),所有的整数类型都有一个可选的属性UNSIGNED(无符号属性),无符号整数类型的最小取值为0。所以,如果需要在MySQL数据库中保存非负整数值时,可以将整数类型设置为无符号类型。

    int类型默认显示宽度为int(11),无符号int类型默认显示宽度为int(10)。

    image-20220405113211366

    2.2.3 ZEROFILL

    ZEROFILL : 0填充,(如果某列是ZEROFILL,那么MySQL会自动为当前列添加UNSIGNED属性),如果指定了ZEROFILL只是表示不够M位时,用0在左边填充,如果超过M位,只要不超过数据存储范围即可。原来,在 int(M) 中,M 的值跟 int(M) 所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都是占用 4 bytes 的存储空间。也就是说,int(M) ,必须和 UNSIGNED ZEROFILL **一起使用才有意义。**如果整数值超过M位,就按照实际位数存储。只是无须再用字符 0 进行填充。

    2.3使用场景

    • TINYINT :一般用于枚举数据,比如系统设定取值范围很小且固定的场景。
    • SMALLINT :可以用于较小范围的统计数据,比如统计工厂的固定资产库存数量等。
    • MEDIUMINT :用于较大整数的计算,比如车站每日的客流量等
    • INT、INTEGER :取值范围足够大,一般情况下不用考虑超限问题,用得最多。比如商品编号。
    • BIGINT :只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等

    2.4如何选择

    在评估用哪种整数类型的时候,你需要考虑 存储空间 和 可靠性 的平衡问题:一方 面,用占用字节数少的整数类型可以节省存储空间;另一方面,要是为了节省存储空间, 使用的整数类型取值范围太小,一旦遇到超出取值范围的情况,就可能引起 系统错误 ,影响可靠性。举个例子,商品编号采用的数据类型是 INT。原因就在于,客户门店中流通的商品种类较多,而且,每天都有旧商品下架,新商品上架,这样不断迭代,日积月累。如果使用 SMALLINT 类型,虽然占用字节数比 INT 类型的整数少,但是却不能保证数据不会超出范围65535。相反,使用 INT,就能确保有足够大的取值范围,不用担心数据超出范围影响可靠性的问题。你要注意的是,在实际工作中,系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。因此,我建议你首先确保数据不会超过取值范围,在这个前提之下,再去考虑如何节省存储空间。

    3.浮点类型

    3.1类型介绍

    浮点数和定点数类型的特点是可以 处理小数 ,你可以把整数看成小数的一个特例。因此,浮点数和定点数的使用场景,比整数大多了。 MySQL支持的浮点数类型,分别是 FLOAT、DOUBLE、REAL。

    • FLOAT 表示单精度浮点数;

    • DOUBLE 表示双精度浮点数;

      image-20220405114116059

    • REAL默认就是 DOUBLE。如果你把 SQL 模式设定为启用“ REAL_AS_FLOAT ”,那 么,MySQL 就认为REAL 是 FLOAT。如果要启用“REAL_AS_FLOAT”,可以通过以下 SQL 语句实现:

      SET sql_mode = “REAL_AS_FLOAT”;

    问题1: FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢?

    FLOAT 占用字节数少,取值范围小;DOUBLE 占用字节数多,取值范围也大。

    **问题2:**为什么浮点数类型的无符号数取值范围,只相当于有符号数取值范围的一半,也就是只相当于有符号数取值范围大于等于零的部分呢?

    MySQL 存储浮点数的格式为: 符号(S) 、 尾数(M) 和 阶码(E) 。因此,无论有没有符号,MySQL 的浮点数都会存储表示符号的部分。因此, 所谓的无符号数取值范围,其实就是有符号数取值范围大于等于零的部分。

    3.2 数据精度说明

    对于浮点类型,在MySQL中单精度值使用 4 个字节,双精度值使用 8 个字节。

    • MySQL允许使用 非标准语法 (其他数据库未必支持,因此如果涉及到数据迁移,则最好不要这么

      用): FLOAT(M,D) 或 DOUBLE(M,D) 。这里,M称为 精度 ,D称为 标度 。(M,D)中 M=整数位+小数位,D=小数位。 D<=M<=255,0<=D<=30。

      例如,定义为FLOAT(5,2)的一个列可以显示为-999.99-999.99。如果超过这个范围会报错。

    • FLOAT和DOUBLE类型在不指定(M,D)时,默认会按照实际的精度(由实际的硬件和操作系统决定)来显示。

    • 说明:浮点类型,也可以加 UNSIGNED ,但是不会改变数据范围,例如:FLOAT(3,2) UNSIGNED仍然只能表示0-9.99的范围。

    • 不管是否显式设置了精度(M,D),这里MySQL的处理方案如下:

      • 如果存储时,整数部分超出了范围,MySQL就会报错,不允许存这样的值
      • 如果存储时,小数点部分若超出范围,就分以下情况:
        • 若四舍五入后,整数部分没有超出范围,则只警告,但能成功操作并四舍五入删除多余的小数位后保存。例如在FLOAT(5,2)列内插入999.009,近似结果是999.01。
        • 若四舍五入后,整数部分超出范围,则MySQL报错,并拒绝处理。如FLOAT(5,2)列内插入999.995和-999.995都会报错。
    • 从MySQL 8.0.17开始,FLOAT(M,D) 和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用,将来可能被移除。另外,关于浮点型FLOAT和DOUBLE的UNSIGNED也不推荐使用了,将来也可能被移除

    • 举例

      CREATE TABLE test_double1(
          f1 FLOAT,
          f2 FLOAT(5,2),
          f3 DOUBLE,
          f4 DOUBLE(5,2) 
      );
      DESC test_double1; 
      INSERT INTO test_double1 VALUES(123.456,123.456,123.4567,123.45); 
      #Out of range value for column 'f2' at row 1 
      INSERT INTO test_double1 VALUES(123.456,1234.456,123.4567,123.45); 
      SELECT * FROM test_double1;
      

      3.3精度误差说明

      浮点数类型有个缺陷,就是不精准。下面我来重点解释一下为什么 MySQL 的浮点数不够精准。比如,我们设计一个表,有f1这个字段,插入值分别为0.47,0.44,0.19,我们期待的运行结果是:0.47 + 0.44 + 0.19 = 1.1。而使用sum之后查询:

      CREATE TABLE test_double2(
      f1 DOUBLE 
      );
      INSERT INTO test_double2 VALUES(0.47),(0.44),(0.19);
      

      image-20220405124617102

      查询结果是 1.0999999999999999。看到了吗?虽然误差很小,但确实有误差。 你也可以尝试把数据类型改成 FLOAT,然后运行求和查询,得到的是, 1.0999999940395355。显然,误差更大了。

      那么,为什么会存在这样的误差呢?问题还是出在 MySQL 对浮点类型数据的存储方式上。

      MySQL 用 4 个字节存储 FLOAT 类型数据,用 8 个字节来存储 DOUBLE 类型数据。无论哪个,都是采用二进制的方式来进行存储的。比如 9.625,用二进制来表达,就是 1001.101,或者表达成 1.001101×2^3。如果尾数不是 0 或 5(比如 9.624),你就无法用一个二进制数来精确表达。进而,就只好在取值允许的范围内进行四舍五入。

      在编程中,如果用到浮点数,要特别注意误差问题,因为浮点数是不准确的,所以我们要避免使用“=”来**判断两个数是否相等。**同时,在一些对精确度要求较高的项目中,千万不要使用浮点数,不然会导致结果错误,甚至是造成不可挽回的损失。那么,MySQL 有没有精准的数据类型呢?当然有,这就是定点数类型: DECIMAL 。

      4.定点数类型

      4.1 类型介绍

      • MySQL中的定点数类型只有 DECIMAL 一种类型。

        image-20220405124934162

        使用 DECIMAL(M,D) 的方式表示高精度小数。其中,M被称为精度,D被称为标度。0<=M<=65, 0<=D<=30,D<M。例如,定义DECIMAL(5,2)的类型,表示该列取值范围是-999.99~999.99。

      • DECIMAL(M,D) 的最大取值范围与 DOUBLE 类型一样,但是有效的数据范围是由M和D决定的。DECIMAL 的存储空间并不是固定的,由精度值M决定,总共占用的存储空间为M+2个字节。也就是说,在一些对精度要求不高的场景下,比起占用同样字节长度的定点数,浮点数表达的数值范围可以更大一些。

      • 定点数在MySQL内部是以 字符串 的形式进行存储,这就决定了它一定是精准的。

      • 当DECIMAL类型不指定精度和标度时,其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时,则MySQL同样会进行四舍五入处理。

      • 浮点数 vs 定点数

        • 浮点数相对于定点数的优点是在长度一定的情况下,浮点类型取值范围大,但是不精准,适用于需要取值范围大,又可以容忍微小误差的科学计算场景(比如计算化学、分子建模、流体动力学等)
        • 定点数类型取值范围相对小,但是精准,没有误差,适合于对精度要求极高的场景 (比如涉及金额计算的场景)
      • 举例

        CREATE TABLE test_decimal1(
            f1 DECIMAL,
            f2 DECIMAL(5,2) 
        );
        DESC test_decimal1; 
        INSERT INTO test_decimal1(f1,f2) VALUES(123.123,123.456); 
        #Out of range value for column 'f2' at row 1 
        INSERT INTO test_decimal1(f2) VALUES(1234.34)
        

        image-20220405125348314

      • 举例

        我们运行下面的语句,把test_double2表中字段“f1”的数据类型修改为 DECIMAL(5,2):

        ALTER TABLE test_double2 
        MODIFY f1 DECIMAL(5,2);
        

        然后,我们再一次运行求和语句:

        image-20220405125452313

    4.2开发中经验

    “由于 DECIMAL 数据类型的精准性,在我们的项目中,除了极少数(比如商品编号)用到整数类型外,其他的数值都用的是 DECIMAL,原因就是这个项目所处的零售行业,要求精准,一分钱也不能差。 ” ——来自某项目经理

    5.位类型:BIT

    BIT类型中存储的是二进制值,类似010110。

    image-20220405125658905

    BIT类型,如果没有指定(M),默认是1位。这个1位,表示只能存1位的二进制值。这里(M)是表示二进制的位数,位数最小值为1,最大值为64。

    CREATE TABLE test_bit1(
        f1 BIT,
        f2 BIT(5),
        f3 BIT(64) 
    );
    INSERT INTO test_bit1(f1) VALUES(1); 
    #Data too long for column 'f1' at row 1 
    INSERT INTO test_bit1(f1) VALUES(2); 
    INSERT INTO test_bit1(f2) VALUES(23);
    

    注意:在向BIT类型的字段中插入数据时,一定要确保插入的数据在BIT类型支持的范围内。

    使用SELECT命令查询位字段时,可以用 BIN() 或 HEX() 函数进行读取。

    image-20220405125850826

    可以看到,使用b+0查询数据时,可以直接查询出存储的十进制数据的值。

    6.日期与时间类型

    日期与时间是重要的信息,在我们的系统中,几乎所有的数据表都用得到。原因是客户需要知道数据的时间标签,从而进行数据查询、统计和处理。MySQL有多种表示日期和时间的数据类型,不同的版本可能有所差异,MySQL8.0版本支持的日期和时间类型主要有:YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。

    • YEAR 类型通常用来表示年
    • DATE 类型通常用来表示年、月、日
    • TIME 类型通常用来表示时、分、秒
    • DATETIME 类型通常用来表示年、月、日、时、分、秒
    • TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒

    image-20220405130128696

    可以看到,不同数据类型表示的时间内容不同、取值范围不同,而且占用的字节数也不一样,你要根据实际需要灵活选取。为什么时间类型 TIME 的取值范围不是 -23:59:59~23:59:59 呢?原因是 MySQL 设计的 TIME 类型,不光表示一天之内的时间,而且可以用来表示一个时间间隔,这个时间间隔可以超过 24 小时。

    6.1 YEAR类型

    YEAR类型用来表示年份,在所有的日期时间类型中所占用的存储空间最小,只需要 1个字节 的存储空间。在MySQL中,YEAR有以下几种存储格式:

    • 以4位字符串或数字格式表示YEAR类型,其格式为YYYY,最小值为1901,最大值为2155。
    • 以2位字符串格式表示YEAR类型,最小值为00,最大值为99。
      • 当取值为01到69时,表示2001到2069;
      • 当取值为70到99时,表示1970到1999;
      • 当取值整数的0或00添加的话,那么是0000年;
      • 当取值是日期/字符串的’0’添加的话,是2000年

    从MySQL5.5.27开始,2位格式的YEAR已经不推荐使用。YEAR默认格式就是“YYYY”,没必要写成YEAR(4),

    从MySQL 8.0.19开始,不推荐使用指定显示宽度的YEAR(4)数据类型。

    CREATE TABLE test_year(
    	f1 YEAR,
    	f2 YEAR(4) 
    );
    

    image-20220405130755089

    image-20220405130836896

    6.2 DATE类型

    DATE类型表示日期,没有时间部分,格式为 YYYY-MM-DD ,其中,YYYY表示年份,MM表示月份,DD表示日期。需要 3个字节 的存储空间。在向DATE类型的字段插入数据时,同样需要满足一定的格式条件。

    • 以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期,其最小取值为1000-01-01,最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。
    • 以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期,此格式中,年份为两位数值或字符串满足YEAR类型的格式条件为:当年份取值为00到69时,会被转化为2000到2069;当年份取值为70到99时,会被转化为1970到1999。
    • 使用 CURRENT_DATE() 或者 NOW() 函数,会插入当前系统的日期。

    举例:

    创建数据表,表中只包含一个DATE类型的字段f1。

    CREATE TABLE test_date1(
    	f1 DATE 
    );
    Query OK, 0 rows affected (0.13 sec)
    #插入数据:
    INSERT INTO test_date1 
    VALUES ('2020-10-01'), ('20201001'),(20201001); 
    
    INSERT INTO test_date1 
    VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), ('700101'), ('99-01-01'), ('990101');
    
    INSERT INTO test_date1 
    VALUES (000301), (690301), (700301), (990301);
    
    INSERT INTO test_date1 
    VALUES (CURRENT_DATE()), (NOW()); 
    
    SELECT * FROM test_date1;
    

    6.3 TIME类型

    TIME类型用来表示时间,不包含日期部分。在MySQL中,需要 3个字节 的存储空间来存储TIME类型的数据,可以使用“HH:MM:SS”格式来表示TIME类型,其中,HH表示小时,MM表示分钟,SS表示秒。

    在MySQL中,向TIME类型的字段插入数据时,也可以使用几种不同的格式。 (1)可以使用带有冒号的字符串,比如’ D HH:MM:SS’ 、’ HH:MM:SS ‘、’ HH:MM ‘、’ D HH:MM ‘、’ D HH ‘或’ SS ‘格式,都能被正确地插入TIME类型的字段中。其中D表示天,其最小值为0,最大值为34。如果使用带有D格式的字符串插入TIME类型的字段时,D会被转化为小时,计算格式为D*24+HH。当使用带有冒号并且不带D的字符串表示时间时,表示当天的时间,比如12:10表示12:10:00,而不是00:12:10。 (2)可以使用不带有冒号的字符串或者数字,格式为’ HHMMSS '或者 HHMMSS 。如果插入一个不合法的字符串或者数字,MySQL在存储数据时,会将其自动转化为00:00:00进行存储。比如1210,MySQL会将最右边的两位解析成秒,表示00:12:10,而不是12:10:00。 (3)使用 CURRENT_TIME() 或者 NOW() ,会插入当前系统的时间。

    举例:

    创建数据表,表中包含一个TIME类型的字段f1。

    CREATE TABLE test_time1(
    	f1 TIME 
    );
    Query OK, 0 rows affected (0.02 sec) 
    INSERT INTO test_time1 
    VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45'); 
    INSERT INTO test_time1 
    VALUES ('123520'), (124011),(1210); 
    INSERT INTO test_time1 VALUES (NOW()), (CURRENT_TIME()); 
    SELECT * FROM test_time1;
    

    6.4 DATETIME

    DATETIME类型在所有的日期时间类型中占用的存储空间最大,总共需要 8 个字节的存储空间。在格式上为DATE类型和TIME类型的组合,可以表示为 YYYY-MM-DD HH:MM:SS ,其中YYYY表示年份,MM表示月份,DD表示日期,HH表示小时,MM表示分钟,SS表示秒。在向DATETIME类型的字段插入数据时,同样需要满足一定的格式条件。

    • 以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时,最小值为1000-01-01 00:00:00,最大值为9999-12-03 23:59:59
      • 以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时,会被转化为YYYY-MM-DD HH:MM:SS格式。
    • 以 YY-MM-DD HH:MM:SS 格式或者 YYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时,两位数的年份规则符合YEAR类型的规则,00到69表示2000到2069;70到99表示1970到1999。
    • 使用函数 CURRENT_TIMESTAMP() 和 NOW() ,可以向DATETIME类型的字段插入系统的当前日期和时间。

    举例:

    #创建数据表,表中包含一个DATETIME类型的字段dt。
    CREATE TABLE test_datetime1( 
        dt DATETIME 
    );
    Query OK, 0 rows affected (0.02 sec)
    #插入数据:
    INSERT INTO test_datetime1 
    VALUES ('2021-01-01 06:50:30'), ('20210101065030'); 
    INSERT INTO test_datetime1 
    VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'), ('200101000000'); 
    INSERT INTO test_datetime1 
    VALUES (20200101000000), (200101000000), (19990101000000), (990101000000); 
    INSERT INTO test_datetime1 VALUES (CURRENT_TIMESTAMP()), (NOW());
    

    6.5 TIMESTAMP类型

    TIMESTAMP类型也可以表示日期时间,其显示格式与DATETIME类型相同,都是 YYYY-MM-DD HH:MM:SS ,需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多,只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中,UTC表示世界统一时间,也叫作世界标准时间。

    • 存储数据的时候需要对当前时间所在的时区进行转换,查询数据的时候再将时间转换回当前的时区。因此,使用TIMESTAMP存储的同一个时间值,在不同的时区查询时会显示不同的时间。

    向TIMESTAMP类型的字段插入数据时,当插入的数据格式满足YY-MM-DD HH:MM:SS和YYMMDDHHMMSS时,两位数值的年份同样符合YEAR类型的规则条件,只不过表示的时间范围要小很多。如果向TIMESTAMP类型的字段插入的时间超出了TIMESTAMP类型的范围,则MySQL会抛出错误信息。

    举例:

    #创建数据表,表中包含一个TIMESTAMP类型的字段ts。
    CREATE TABLE test_timestamp1( 
        ts TIMESTAMP 
    );
    #插入数据:
    INSERT INTO test_timestamp1 
    VALUES ('1999-01-01 03:04:50'), ('19990101030405'), ('99-01-01 03:04:05'), ('990101030405'); 
    INSERT INTO test_timestamp1 
    VALUES ('2020@01@01@00@00@00'), ('20@01@01@00@00@00'); 
    INSERT INTO test_timestamp1 
    VALUES (CURRENT_TIMESTAMP()), (NOW()); 
    #Incorrect datetime value 
    INSERT INTO test_timestamp1 VALUES ('2038-01-20 03:14:07');
    

    TIMESTAMP和DATETIME的区别:

    • TIMESTAMP存储空间比较小,表示的日期时间范围也比较小
    • 底层存储方式不同,TIMESTAMP底层存储的是毫秒值,距离1970-1-1 0:0:0 0毫秒的毫秒值。
    • 两个日期比较大小或日期计算时,TIMESTAMP更方便、更快。
    • TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同,显示不同的结果。而DATETIME则只能反映出插入时当地的时区,其他时区的人查看数据必然会有误差的。

    image-20220405132448901

    6.6 开发中经验

    用得最多的日期时间类型,就是 DATETIME 。虽然 MySQL 也支持 YEAR(年)、 TIME(时间)、DATE(日期),以及 TIMESTAMP 类型,但是在实际项目中,尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息,取值范围也最大,使用起来比较方便。毕竟,如果日期时间信息分散在好几个字段,很不容易记,而且查询的时候,SQL 语句也会更加复杂。此外,一般存注册时间、商品发布时间等,不建议使用DATETIME存储,而是使用 时间戳 ,因为DATETIME虽然直观,但不便于计算。

    image-20220405132711542

    7.文本字符串类型

    在实际的项目中,我们还经常遇到一种数据,就是字符串数据。

    MySQL中,文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、MEDIUMTEXT 、 LONGTEXT 、 ENUM 、 SET 等类型

    image-20220405132958219

    7.1CHAR与VARCHAR类型

    CHAR和VARCHAR类型都可以存储比较短的字符串。

    image-20220405133135363

    CHAR类型:

    • CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M),则表示长度默认是1个字符。
    • 如果保存时,数据的实际长度比CHAR类型声明的长度小,则会在 右侧填充 空格以达到指定的长度。当MySQL检索CHAR类型的数据时,CHAR类型的字段会去除尾部的空格。
    • 定义CHAR类型字段时,声明的字段长度即为CHAR类型字段所占的存储空间的字节数。
    CREATE TABLE test_char1( 
        c1 CHAR, 
        c2 CHAR(5) 
    );
    DESC test_char1;
    
    INSERT INTO test_char1 
    VALUES('a','Tom');
    
    SELECT c1,CONCAT(c2,'***') 
    FROM test_char1; 
    
    INSERT INTO test_char1(c2) 
    VALUES('a '); 
    
    SELECT CHAR_LENGTH(c2) 
    FROM test_char1;
    

    VARCHAR类型:

    • VARCHAR(M) 定义时, 必须指定 长度M,否则报错。
    • MySQL4.0版本以下,varchar(20):指的是20字节,如果存放UTF8汉字时,只能存6个(每个汉字3字节) ;MySQL5.0版本以上,varchar(20):指的是20字符。
    • 检索VARCHAR类型的字段数据时,会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。
    CREATE TABLE test_varchar1( 
        NAME VARCHAR #错误 
    );
    #Column length too big for column 'NAME' (max = 21845); 
    CREATE TABLE test_varchar2( 
        NAME VARCHAR(65535) #错误 
    )
    CREATE TABLE test_varchar3( 
        NAME VARCHAR(5) 
    );
    INSERT INTO test_varchar3 
    VALUES('尚硅谷'),('尚硅谷教育'); 
    #Data too long for column 'NAME' at row 1 
    INSERT INTO test_varchar3 VALUES('尚硅谷IT教育')
    

    哪些情况使用 CHAR VARCHAR 更好

    image-20220405133628995

    情况1:存储很短的信息。比如门牌号码101,201……这样很短的信息应该用char,因为varchar还要占个byte用于存储信息长度,本来打算节约存储的,结果得不偿失。

    情况2:固定长度的。比如使用uuid作为主键,那用char应该更合适。因为他固定长度,varchar动态根据长度的特性就消失了,而且还要占个长度信息。

    情况3:十分频繁改变的column。因为varchar每次存储都要有额外的计算,得到长度等工作,如果一个非常频繁改变的,那就要有很多的精力用于计算,而这些对于char来说是不需要的。

    情况4:具体存储引擎中的情况:

    • MyISAM 数据存储引擎和数据列:MyISAM数据表,最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化,从而使 数据检索更快 ,用空间换时间。
    • MEMORY 存储引擎和数据列:MEMORY数据表目前都使用固定长度的数据行存储,因此无论使用CHAR或VARCHAR列都没有关系,两者都是作为CHAR类型处理的。
    • InnoDB 存储引擎,建议使用VARCHAR类型。因为对于InnoDB数据表,内部的行存储格式并没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),而且主要影响性能的因素 是数据行使用的存储总量,由于char平均占用的空间多于varchar,所以除了简短并且固定长度的,其他考虑varchar。这样节省空间,对磁盘I/O和数据存储总量比较好。

    7.2 TEXT类型

    在MySQL中,TEXT用来保存文本类型的字符串,总共包含4种类型,分别为TINYTEXT、TEXT、 MEDIUMTEXT 和 LONGTEXT 类型。

    在向TEXT类型的字段保存和查询数据时,系统自动按照实际长度存储,不需要预先定义长度。这一点和VARCHAR类型相同。

    每种TEXT类型保存的数据长度和所占用的存储空间不同,如下:

    image-20220405134255946

    由于实际存储的长度不确定, MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况,你只能采用CHAR(M),或者 VARCHAR(M)。

    举例:

    #创建数据表:
    CREATE TABLE test_text( tx TEXT );
    INSERT INTO test_text VALUES('atguigu '); 
    SELECT CHAR_LENGTH(tx) FROM test_text; #10
    

    说明在保存和查询数据时,并没有删除TEXT类型的数据尾部的空格。

    开发中经验:

    TEXT文本类型,可以存比较大的文本段,搜索速度稍慢,因此如果不是特别大的内容,建议使用CHAR, VARCHAR来代替。还有TEXT类型不用加默认值,加了也没用。而且text和blob类型的数据删除后容易导致“空洞”,使得文件碎片比较多,所以频繁使用的表不建议包含TEXT类型字段,建议单独分出去,单独用一个表。

    8.ENUM类型

    ENUM类型也叫作枚举类型,ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时,ENUM类型只允许从成员中选取单个值,不能一次选取多个值。其所需要的存储空间由定义ENUM类型时指定的成员个数决定

    image-20220405134553481

    • 当ENUM类型包含1~255个成员时,需要1个字节的存储空间;
    • 当ENUM类型包含256~65535个成员时,需要2个字节的存储空间。
    • ENUM类型的成员个数的上限为65535个。

    举例:

    #创建表如下:
    CREATE TABLE test_enum( 
        season ENUM('春','夏','秋','冬','unknow') 
    )
    #添加数据:
    INSERT INTO test_enum VALUES('春'),('秋'); 
    # 忽略大小写 
    INSERT INTO test_enum VALUES('UNKNOW'); 
    # 允许按照角标的方式获取指定索引位置的枚举值 
    INSERT INTO test_enum VALUES('1'),(3); 
    # Data truncated for column 'season' at row 1 
    INSERT INTO test_enum VALUES('ab'); 
    # 当ENUM类型的字段没有声明为NOT NULL时,插入NULL也是有效的 
    INSERT INTO test_enum VALUES(NULL);
    

    9.SET类型

    SET表示一个字符串对象,可以包含0个或多个成员,但成员个数的上限为 64 。设置字段值时,可以取取值范围内的 0 个或多个值。当SET类型包含的成员个数不同时,其所占用的存储空间也是不同的,具体如下:

    image-20220405134927506

    SET类型在存储数据时成员个数越多,其占用的存储空间越大。注意:SET类型在选取成员时,可以一次选择多个成员,这一点与ENUM类型不同。

    #举例
    CREATE TABLE test_set( s SET ('A', 'B', 'C') );
    #向表中插入数据:
    INSERT INTO test_set (s) VALUES ('A'), ('A,B'); 
    #插入重复的SET类型成员时,MySQL会自动删除重复的成员 
    INSERT INTO test_set (s) VALUES ('A,B,C,A'); 
    #向SET类型的字段插入SET成员中不存在的值时,MySQL会抛出错误。 
    INSERT INTO test_set (s) VALUES ('A,B,C,D'); 
    SELECT * FROM test_set;
    #举例2
    CREATE TABLE temp_mul( 
        gender ENUM('男','女'), 
        hobby SET('吃饭','睡觉','打豆豆','写代码') 
    );
    INSERT INTO temp_mul VALUES('男','睡觉,打豆豆'); #成功 
    # Data truncated for column 'gender' at row 1 
    INSERT INTO temp_mul VALUES('男,女','睡觉,写代码'); #失败 
    # Data truncated for column 'gender' at row 1 
    INSERT INTO temp_mul VALUES('妖','睡觉,写代码');#失败 
    INSERT INTO temp_mul VALUES('男','睡觉,写代码,吃饭'); #成功
    

    10.二进制字符串类型

    MySQL中的二进制字符串类型主要存储一些二进制数据,比如可以存储图片、音频和视频等二进制数据。MySQL中支持的二进制字符串类型主要包括BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB类型。

    BINARY与VARBINARY类型

    BINARY和VARBINARY类似于CHAR和VARCHAR,只是它们存储的是二进制字符串。

    BINARY (M)为固定长度的二进制字符串,M表示最多能存储的字节数,取值范围是0~255个字符。如果未指定(M),表示只能存储 1个字节 。例如BINARY (8),表示最多能存储8个字节,如果字段值不足(M)个字节,将在右边填充’\0’以补齐指定长度。

    VARBINARY (M)为可变长度的二进制字符串,M表示最多能存储的字节数,总字节数不能超过行的字节长度限制65535,另外还要考虑额外字节开销,VARBINARY类型的数据除了存储数据本身外,还需要1或2个字节来存储数据的字节数。VARBINARY类型 必须指定(M) ,否则报错。

    image-20220405141514055

    举例:

    #创建表
    CREATE TABLE test_binary1(
        f1 BINARY,
        f2 BINARY(3), # f3 VARBINARY,
        f4 VARBINARY(10) 
    )
    INSERT INTO test_binary1(f1,f2) 
    VALUES('a','a'); 
    INSERT INTO test_binary1(f1,f2) 
    VALUES('尚','尚');#失败 
    INSERT INTO test_binary1(f2,f4) 
    VALUES('ab','ab')
    

    image-20220405141723017

    BLOB类型

    BLOB是一个 二进制大对象 ,可以容纳可变数量的数据。

    MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型,它们可容纳值的最大长度不同。可以存储一个二进制的大对象,比如 图片 、 音频 和 视频 等。需要注意的是,在实际工作中,往往不会在MySQL数据库中使用BLOB类型存储大对象数据,通常会将图片、音频和视频文件存储到 服务器的磁盘上 ,并将图片、音频和视频的访问路径存储到MySQL中。

    image-20220405141825657

    举例:

    CREATE TABLE test_blob1( 
        id INT, 
        img MEDIUMBLOB 
    )
    

    TEXT和BLOB的使用注意事项

    在使用text和blob字段类型时要注意以下几点,以便更好的发挥数据库的性能。

    ① BLOB和TEXT值也会引起自己的一些问题,特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的" 空洞 ",以后填入这些"空洞"的记录可能长度不同。为了提高性能,建议定期使用 OPTIMIZE TABLE 功能对这类表进行 碎片整理 。

    ② 如果需要对大文本字段进行模糊查询,MySQL 提供了 前缀索引 。但是仍然要在不必要的时候避免检索大型的BLOB或TEXT值。例如,SELECT * 查询就不是很好的想法,除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则,你可能毫无目的地在网络上传输大量的值。

    ③ 把BLOB或TEXT列 分离到单独的表 中。在某些环境中,如果把这些数据列移动到第二张数据表中,可以让你把原数据表中的数据列转换为固定长度的数据行格式,那么它就是有意义的。这会 减少主表中的 碎片 ,使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT值。

    11.JSON类型

    JSON(JavaScript Object Notation)是一种轻量级的 数据交换格式 。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。它易于人阅读和编写,同时也易于机器解析和生成,并有效地提升网络传输效率。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串,然后就可以在网络或者程序之间轻 松地传递这个字符串,并在需要的时候将它还原为各编程语言所支持的数据格式。

    在MySQL 5.7中,就已经支持JSON数据类型。在MySQL 8.x版本中,JSON类型提供了可以进行自动验证的JSON文档和优化的存储结构,使得在MySQL中存储和读取JSON类型的数据更加方便和高效。 创建数据表,表中包含一个JSON类型的字段 js 。

    CREATE TABLE test_json( 
        js json 
    );
    #向表中插入JSON数据。
    INSERT INTO test_json (js) 
    VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing", "city":"beijing"}}');
    

    查询t19表中的数据。

    image-20220405142430165

    当需要检索JSON类型的字段中数据的某个具体值时,可以使用“->”和“->>”符号

    image-20220405142505664

    通过“->”和“->>”符号,从JSON字段中正确查询出了指定的JSON数据的值。

    12.空间类型

    MySQL 空间类型扩展支持地理特征的生成、存储和分析。这里的地理特征表示世界上具有位置的任何东西,可以是一个实体,例如一座山;可以是空间,例如一座办公楼;也可以是一个可定义的位置,例如一个十字路口等等。MySQL中使用 Geometry(几何) 来表示所有地理特征。Geometry指一个点或点的集合,代表世界上任何具有位置的事物。

    MySQL的空间数据类型(Spatial Data Type)对应于OpenGIS类,包括单值类型:GEOMETRY、POINT、 LINESTRING、POLYGON以及集合类型:MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION 。

    • Geometry是所有空间集合类型的基类,其他类型如POINT、LINESTRING、POLYGON都是Geometry的子类。
      • Point,顾名思义就是点,有一个坐标值。例如POINT(121.213342 31.234532)POINT(30 10),坐标值支持DECIMAL类型,经度(longitude)在前,维度(latitude)在后,用空格分隔。
      • LineString,线,由一系列点连接而成。如果线从头至尾没有交叉,那就是简单的(simple);如果起点和终点重叠,那就是封闭的(closed)。例如LINESTRING(30 10,10 30,40 40),点与点之间用逗号分隔,一个点中的经纬度用空格分隔,与POINT格式一致。
      • Polygon,多边形。可以是一个实心平面形,即没有内部边界,也可以有空洞,类似纽扣。最简单的就是只有一个外边界的情况,例如POLYGON((0 0,10 0,10 10, 0 10))。

    下面展示几种常见的几何图形元素:

    image-20220405142720438

    • MultiPoint、MultiLineString、MultiPolygon、GeometryCollection 这4种类型都是集合类,是多个Point、LineString或Polygon组合而成。

    下面展示的是多个同类或异类几何图形元素的组合:

    image-20220405142745379

    13.小结及选择建议

    在定义数据类型时,如果确定是 整数 ,就用 INT ; 如果是 小数 ,一定用定点数类型DECIMAL(M,D) ; 如果是日期与时间,就用 DATETIME 。

    这样做的好处是,首先确保你的系统不会因为数据类型定义出错。不过,凡事都是有两面的,可靠性好,并不意味着高效。比如,TEXT 虽然使用方便,但是效率不如 CHAR(M) 和VARCHAR(M)。

    关于字符串的选择,建议参考如下阿里巴巴的《Java开发手册》规范:

    阿里巴巴《Java开发手册》之MySQL数据库:

    • 任何字段如果为非负数,必须是 UNSIGNED

    • 【 强制 】小数类型为 DECIMAL,禁止使用 FLOAT 和 DOUBLE。

      • 说明:在存储的时候,FLOAT 和 DOUBLE 都存在精度损失的问题,很可能在比较值的候,得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围,建议将数据拆成整数和小数并分开存储。
    • 【 强制 】如果存储的字符串长度几乎相等,使用 CHAR 定长字符串类型。

    • 【 强制 】VARCHAR 是可变长字符串,不预先分配存储空间,长度不要超过 5000。如果存储长度大于此值,定义字段类型为 TEXT,独立出来一张表,用主键来对应,避免影响其它字段索引效率。

    展开全文
  • 5-抽象数据类型(ADT)

    千次阅读 多人点赞 2018-05-25 11:40:53
    1. 以数据为核心的思维   在现实中计算机能够解决非常多的问题,如图1所示,可以用计算机去实现一个GIS地理信息系统,也可以实现一个电子病历系统,或者实现一个计算机管理资源的图形化界面等。包括现在我们使用...

    1. 以数据为核心的思维

      在现实中计算机能够解决非常多的问题,如图1所示,可以用计算机去实现一个GIS地理信息系统,也可以实现一个电子病历系统,或者实现一个计算机管理资源的图形化界面等。包括现在我们使用的即时通讯软件,浏览器等,能用计算机实现的事情有很多。对于正在学计算机的同学来说,以后也可能会用计算机去做这些事情的,势必也要去设计这样的一个系统。

    这里写图片描述
    图1-以数据为核心的思维

      对于这些系统,我们通过分析发现它们所具有的的共性就是数据,同时我们在学习计算机时也应该做到这种以数据为核心的思维(也就是计算机思维,学会站在计算机的角度去思考和设计)。

      问题是我们如何做到以数据为中心思考,设计?

      在图中我们可以看到不同系统的数据都有不同的特点,比如:在GIS地理系统中的数据有自己的一些特点,在电子病历系统中的数据也有自己的一些特点。但是如果我们忽略这些系统表面的这些数据,抓取其本质来看这些系统都是数据,而这种忽略形态,抓本质的就是一种抽象的思维,那么我们在数据结构层面上如何做到忽略形态,抓本质呢?

    2. 数据类型

      我们先来看一下高级程序语言是如何来表示数据的,在具体的数据类型来看,高级程序语言中一般变量、常量或表达式,都有明确的所属数据类型。比如:int a 就表示我们定义了一个int类型的变量a,其中int就代表具体的数据类型。在C语言中这样的数据类型还有很多,具体如下所示:

    这里写图片描述
    图2-C语言中的数据类型

      在数据结构中所说的数据元素经常用构造类型中的结构体,数组来表示,而对于数据项来说,都是由基本类型来表示的。当多个基本类型的数据项去描述一个数据元素时,会用构造类型来描述数据元素。在使用高级语言中这些数据类型的时候,不同类型的变量的取值范围不一样,所能做的操作也不一样。

      比如:int和double的数据类型就不一样,在描述一个学生的年龄时使用int类型比较合适,身高用double类型,因此我们在用这些数据类型来进行某些操作时就需要选择合适的数据类型,在概括一个数据类型的方面时有两点要考虑:一是类型的取值范围,二是在这个取值范围所能做的操作

      数据类型在数据结构中的定义是一个值的集合以及定义在这个值集上的一组操作的总称,也是数据类型在数据结构中的本质。但是在高级程序语言中的数据类型是涉及到功能是如何具体实现的,在计算机具体存储,也就是说高级程序语言中的数据类型仍然是属于具体数据类型。

    3. 抽象数据类型(Abstract Data Type, ADT)

      数据结构中有一个非常重要的概念:即抽象数据类型(Abstract Data Type,ADT)。

      抽象数据类型 = 逻辑结构 + 抽象运算,也就是说抽象数据类型是建立在逻辑结构上的一些抽象的运算。对于逻辑结构之前有说过,逻辑结构是在普通面前所呈现的形态,并且不涉及到数据在计算机中具体的实现和存储的,对于数据时怎么在计算机中具体的实现和存储的是在存储结构中需要考虑的问题。因为逻辑结构不涉及任何具体的实现,所以在逻辑结构上的抽象运算也不会涉及到具体的实现到在计算机中具体的实现和存储,也就是说,不涉及到具体实现的就是抽象运算。这样做的意义是什么?

      对于数据结构中的抽象数据类型来说,抽象数据类型暂不考虑计算机的具体存储结构和运算的具体实现。也就是说我们不需要考虑具体实现的细节,而是把注意力集中到问题的本身去做,抽象数据类型实质上就是在描述问题本身(与计算机无关)

      于是我们可以知道抽象数据类型在整个的看待问题和分析问题来说简化了很多,因为在抽象数据类型阶段不考虑和计算机有关的因素去描述问题本身的,当把问题本身描述的非常清楚后,然后再做下一环节的问题:基于逻辑结构去设计存储结构,这样我们在分析一个问题就简化了。

      抽象数据类型:在不涉及具体的,和计算机系统相关的细节情况下,优先理解问题本身,在此基础上,实现用计算机求解问题的过程。这就是我们使用抽象数据类型的目的


      在分析一个复杂的问题的时候,如果直接就考虑到计算机的具体实现和存储这些细节时,这个过程需要的环节太多,会觉得难以进行下去。出现这种问题的原因在于没有把思考,分析一个问题分成两个阶段:先抽象数据类型分析清除问题本身,再去考虑问题具体的实现。由此可见数据结构的重要性。

    我们在使用抽象数据类型的时候,抽象数据类型(ADT)定义如下所示:

    ADT <抽象数据类型名>
    {
    数据对象:<数据对象的定义>
    数据关系:<数据关系的定义>
    基本操作:<基本操作的定义>
    }

    数据对象和数据关系是逻辑结构要关注的问题,基本操作就是抽象运算关注的。



    示例:抽象数据类型复数(e1+e2i)

    这里写图片描述
    图3-抽象数据类型复数(e1+e2i)

      通过这个示例我们可以看出,抽象数据类型就是在这种形式化的描述下把数据的组成和如何操作的问题描述清楚,且没有涉及到具体的计算机具体实现的一些问题。而右边是当我们通过抽象数据类型ADT描述清楚问题本身后,然后在此基础上设计高级程序语言中具体实现的过程,在这个阶段涉及到了计算机的具体实现和存储等因素。这也是我们在思考,分析一个问题该有的过程

      当分析一个复杂的问题,如果直接考虑具体实现和存储的话,这只会让问题更加困难和复杂,我们会认为这是个很难的问题。即便是勉强做到了,但也有可能会对程序后期造成安全隐患。如果我们按照上面这种方式先进行抽象数据类型,然后再考虑具体实现的话,这显然有利于我们分析并解决问题。

    4. 数据的抽象与封装

      在学习数据结构的时候我们需要理解和明白抽象数据类型的含义,而抽象数据类型中,就是对数据对象和数据运算的声明,将对数据对象的表示和数据运算的实现分离。其中抽象数据类型有两个比较重要的特征:

      1. 数据抽象:用ADT描述程序处理的实体时,强调的是其本质的特征、其所能完成的功能,以及它和外部用户的接口。我们需要注意的是:这里在描述数据时忽略了数据的形态,而是一种数据抽象,且不会涉及到高级程序语言中的具体实现和存储。

      2. 数据封装:将实体的外部特征和内部实现细节分离,并且对外部用户隐藏其内部实现细节(即抽象数据类型内部的各种定义),对于用户来说只需要使用即可,不需要知道内部的具体实现细节。

    展开全文
  • PostgreSQL 数据类型

    千次阅读 2017-11-06 20:13:06
    PostgreSQL 数据类型 6.1概述    PostgreSQL提供了丰富的数据类型。用户可以使用 CREATE TYPE 命令在数据库中创建新的数据类型。PostgreSQL 的数据类型被分为四种,分别是基本数据类型、复合数据类型、域和伪类型...
  • Java数据类型和MySql数据类型对应关系
  • 数据库数据类型decimal理解【记录】

    千次阅读 2021-04-22 17:48:02
    一、MYSQL数据类型DECIMAL用法: 1、DECIMAL是MySQL中存在的精准数据类型,以字符串的形式保存精确的原始数值,而float,double则是非精准类型,在DB中保存的是近似值。 例如语法:column_name DECIMAL(P,D) P是...
  • MongoDB数据存储结构 1.基本概念 在MongoDB中数据存储的基本概念是数据库、集合、文档。 文档(document)是MongoDB中数据的基本存储单元,非常类似与关系型数据库管理系统中的行,当更有表现力。 集合...
  • 1. Java语言主要由5中符号组成标识符:数字、字母、美元符、下划线(注意不能数字开头)关键字...”、空格和花括号“{ }”等符号具有特殊的分隔作用)注意运算符优先级(注意左结合和右结合)分隔符:注意+号可以分割字符...
  • VHDL数据类型

    千次阅读 2016-02-17 15:33:19
    VHDL数据类型  VHDL是一种强数据类型语言。  要求设计实体中的每一个常数、信号、变量、 函数以及设定的各种参量都必须具有确定的数据类 型,并且相同数据类型的量才能互相传递和作用。  VHDL数据...
  • TypeScript数据类型

    千次阅读 2018-04-21 17:09:51
    TypeScript 中的数据类型有Undefined、Number(数值类型)、string(字符串类型)、Boolean(布尔类型)、enum(枚举类型)、any(任意类型)、void(空类型)、Array(数组类型)、Tuple(元祖类型)、Null(空类型...
  • js获取变量数据类型

    千次阅读 2020-05-14 00:00:26
    es5语法中,js数据类型有number,string,boolean,undefined,null,Array object,es6语法新增Symbol数据类型,目的是为了生成唯一标识符(值唯一)。获取数据类型的4种常用方式: 1.typeof 可以判断js的基本数据类型,...
  • 第二十六章 命名实体识别和实体消歧 1 命名实体识别概论 PART1 什么是命名实体识别 PART2 命名识别识别的应用 PART3 简历中抽取关键信息 2 命名实体识别不同方法 PART1 实体识别方法 基于规则的方法 基于...
  • NoSql的四种数据类型和各自的优缺点

    千次阅读 2018-09-19 21:24:10
    关于NoSql数据类型的问题,最近面试才遇到,去 360 二面的时候面试官第一个问题就是nosql使用过吗,它都有哪些数据类型。 基本上只关注过key-value,其他的看过,没过脑子,所以今天特意找到四种不同的数据类型的优...
  • VHDL是一种强类型的语言,它不允许不同数据类型之间的相互赋值。如果想在不同数据类型之间进行赋值则需要调用函数来完成。 在VHDL语言里,基本数据对象有三种:常量,变量以及信号。 常量 常量就是常数,它的数值...
  • lnode(数据结构lnode是什么类型)

    万次阅读 多人点赞 2021-05-24 07:59:25
    请问LNode在C语言中代表什么,英语全称是什么是数据结构中定义链表的英文名称,代表链表中的结点, 可以查阅c中链表那一章的相关内容.数据结构Lnode *是代表什么!??大侠 谢谢!!是一个 指针类型是吗???.你好...
  • oracle数据类型

    千次阅读 2018-11-08 20:20:39
    oracle数据类型
  • 实体对齐(Entity Alignment)相关论文与数据集整理

    千次阅读 多人点赞 2021-03-21 21:46:13
    实体对齐(Entity Alignment)、知识图谱融合方法总结整理 年份 模型 ...实体对齐数据集整理 名称 — DBpe-dia(DBP) LinkedGeoData(LGD) Geonames(GEO) YAGO ...
  • 史上最大实体关系抽取数据

    千次阅读 2019-06-28 16:51:35
    史上最大实体关系抽取数据集 | 清华发布 2019-03-23 08:00 本文转载自微信公众号:AI科技评论 AI 科技评论按,在去年的 EMNLP2018 上,由孙茂松教授领导的清华大学自然语言处理实验室发布了一个大规模精标注关系...
  • 文章目录矢量空间数据结构实体数据结构/spaghetti数据结构拓扑空间数据结构索引式拓扑空间数据结构双重独立编码结构/DIME(Dual Independent Map Encoding)码链式双重独立编码 矢量空间数据结构 矢量数据结构对...
  • 数据库实体联系模型与关系模型

    千次阅读 2020-03-02 19:11:33
    例如,编程微课是在线编程教育项目,该项目涉及到课程、学生、老师、学习资料等数据,这些数据都要被存储下来,并且能够方便的增加、修改、删除和查询。这就需要规划课程、学生、老师、学习资料等数据构成以及相互...
  • 实体识别(1) -实体识别任务简介

    千次阅读 2022-03-03 00:01:14
    命名实体识别(Named Entity Recognition,简称NER) , 是指识别文本中具有特定意义的词(实体),主要包括人名、地名、机构名、专有名词等等,并把我们需要识别的词在文本序列中标注出来。 例如有一段文本:李明在天津市...
  • 初学TypeScript之基本数据类型

    万次阅读 2019-05-02 16:38:44
    通常这会发生在你清楚的知道一个实体具有比它现有类型更确切的类型。 通过类型断言这种方式可以告诉编辑器,“相信我,我知道自己在干什么”。类型断言好比其他语言里的类型转换,但是不进行特殊的数据检查和结构。...
  • 数据结构和抽象数据类型

    千次阅读 2016-01-15 16:41:31
    这两个概念,尤其是第一个都是特别抽象的概念,没什么具体可对应的实体可以给你举例,我就粘贴复制了,说说我的理解吧。数据结构呢,我们总是为了完成一个功能或者目的写程序,但不管什么程序、代码实际上都是一些...
  • 目录写在最前一、 强实体与弱实体的定义1. 强实体2. 弱实体百度百科中的解释《数据库系统课程》中的解释总结起来 写在最前 数据库设计是困难的,其原因之一就在于我们很难去完全把握实体的定义。是不是实体、该不该...
  • 实体-联系模型

    千次阅读 2020-12-20 22:08:32
    实体-联系(Entity-Relationship, ...(可从面向对象的类含义类似)实体集是相同类型具有相同性质(或属性)的一个实体集合。在建模汇中,我们通常抽象地使用术语“实体集”,而不是指某个个别实体的特别集合。 实体
  • 三元组的定义 实体(Entity) ...类型是对具有相同特点或属性的实体集合的抽象。 举例:中国是一个实体,美国是一个实体,法国是一个实体。这些实体都有首都、人口、面积等共同特征,因此例如像中国、美国...
  • 概念设计(Conceptual Design) —— 设计实体关系模型 (ER Model) 逻辑设计(Logical Design)—— 实现从 ER 模型到关系模式(Relation Schemas)的转换。 物理设计(Physical Design) 本文主...
  • 数据属性类型

    千次阅读 2019-03-05 14:51:53
    数据集由数据对象组成,一个数据对象代表一个实体数据对象又称样本、实例、数据点或对象。属性(attribute)是一个数据字段,表示数据对象的一个特征。属性向量(或特征向量)是用来描述一个给定对象的一组属性。...
  • 数据的四种常用的数据模型以及三实体之间的联系三个世界现实世界信息世界两个实体型间的联系数据模型层次模型 三个世界 现实世界 现实世界,客观存在的世界。 信息世界 概念:信息世界是现实在人们头脑中的反映,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 222,262
精华内容 88,904
关键字:

具有实体的数据类型