Oracle 数据类型

来源：互联网发布：杀破狼js mp3微盘编辑：程序博客网时间：2024/06/05 16:45

Oracle数据类型

一、概述

Oracle数据类型包括标准的SQL数据类型和PL/SQL数据类型。

标准的SQL数据类型为relation database所共用，PL/SQL数据类型为Oracle特有。

Oracle数据类型分为标量类型、复合类型、引用类型、用户自定义子类型、LOB类型。

下面分别逐一讲述。

二、标量类型：存放单个数值的变量

| binary_double双精度浮点(例如1.5f)
| binary_float单精度浮点(例如3.009d)

| |natural非负整数

| |naturaln不为空的非负整数
| binary_integer----|signtype存储-1,0,1

| |positive正整数

| |postitven不为空的正整数

| |dec定点数38位

| |decmal定点数38位

| |double precision浮点数38位

| |float浮点数38位
| numeric——| number----|integer整数38位

| | |int整数38位

| | |numeric定点数38位

| | |real浮点数18位

| | |smallint整数38位

| |
                       | | pls_integer (带符号整数)操作速度最快，算术运算溢出时会出错！±2147483647
                       |
标量类型——| | char (固定长度字符) —>小于等于2000字节
                       | | varchar2 (可变长字符) —>小于等于4000字节
                       | character------------------| long (可变长字符) —>小于等于2G
                       | | nchar (固定长度NLS字符) —>小于等于2000字节
                       | | nvarchar2 (可变长NLS字符) —>小于等于4000字节
                       |
                       | | raw (固定长度二进制)—>小于等于2G
                       | raw--------------|
                       | | long raw (可变长二进制) —>小于等于2G
                       |
                       | rowid (伪列，行标识符，固定的16进制字符串)
                       |
                       | date (长度固定的日期和时间值) —>公元前4712年1月1日到公元4712年12月31日
                       |
                       | boolean (存储逻辑值) —>true、false、null
                       |
                       | trusted

这里需要注意：

1.varchar2类型在作为表的字段，或者作为函数、过程的返回值时最大长度为4000，但在PL/SQL中作为中间变量设定时最大长度是32767。char在PL/SQL中作为中间变量设定时最大长度也是32767。

2.由于char是以固定长度的，所以它的速度会比varchar2快，但程序处理起来要麻烦一点，要用trim之类的函数把两边的空格去掉。

3. Varchar2一般适用于英文和数字，Nvarchar2适用中文和其他字符，其中N表示Unicode常量，可以解决多语言字符集之间的转换问题。

4. binary_integer (带符号整数) 操作速度慢，算术运算溢出时不会出错！±2147483647，其派生的5个子类主要是为了和其它数据库兼容或便于理解和操作。

5. number (定点数、整型数、浮点数) —>操作速度中，38位十进制浮点。Number(m,n)，1≤ m ≤38，-84≤ n ≤127；Number(4,2) 指的是整数占2位，小数占2位,整数位是m-n.(99.994可以。99.995不行，因为是四舍五入)。

6.rowid是伪列，存储行标示符，固定16进制字符串，里面包含着数据对象号、数据文件、数据文件中的数据块、数据块中的记录四种信息。通常行标识符唯一确定一条记录，但如果不同表中的记录存储在相同的簇中，则这些记录具有相同的行标识符。

7.date日期格式，一个date变量占7个字节，7个字节分别是世纪(公元)、年、月、日、小时、分钟、秒。缺省的日期格式由Oracle的初始化参数NLS_DATA_FORMAT决定。可以存储从公元前14712年元月到公元31999年12月的所有日期。

8.标量类型中有%TYPE属性，该属性自动根据表列或其它变量的类型和长度定义新变量。

9.另外还有%ROWTYPE类型，返回一行记录的记录类型。

10、？？？float(n)，1 ≤ n ≤126，浮点数，精度在1~126之间。

11、long无法就行类似LIKE等模糊匹配的操作，可以通过to_lob()转化为clob字段类型生成一张新表之后，再执行类似like的操作,注意只能createtable，而不能直接select。

强制类型转换

函数

描述

TO_CHAR

NUMERIC / DATE——>VARCHAR2

TO_DATE

CHARACTER——>DATE

TO_NUMBER

CHARACTER——>NUMBER

RAWTOHEX

将RAW类型值转换成十六进制数值

HEXTORAW

将用CHARACTER类型描述的十六进制数转换成二进制数

CHARTOROWID

将用 CHARACTER类型描述的ROWID转换成二进制数

ROWIDTOCHAR

将二进制形式的ROWID装换成18个字符的行标识符

自动类型转换( X 表示能够实现自动类型转换)

Binary_integer

Char

date

long

number

Pls_integer

raw

rowid

Varchar2

Binary_integer

char

date

Long

Number

Pls_integer

Raw

Rowed

Varchar2

自动类型转换要注意不同精度转换的缺失问题。
尽管PL/SQL能够自动类型转换数据类型，尤其在算术表达式中，但最好还是用强制类型转换。

dbms_output.put_line 当sqlplus运行这个没输出时，需要输入set serveroutput on 打开输出。

三.复合类型
用于存放多个数值的变量
种类：PL/SQL记录(record)、PL/SQL集合（分为PL/SQL表(别名索引表)、嵌套表、VARRAY）

1.PL/SQL记录
定义：类似于C语言的结构体，存放的是一行记录。
说明：需要先定义再声明后使用，用于处理单行多列数据。

结构：type record _name is record(

Field1 type1 [not null] [:=expr1],

Field2 type2 [not null] [:=expr2],…);

注意：record_name表示记录类型的名字，field表示记录里域的名字，如果域没有被复制则系统赋null，赋值也用default代替。

例一(select的情况)
declare
type emprecord s record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type); --定义
e emprecord; --声明
begin
select ename,sal,job into e from emp where empno=78; --引用
dbms_output.put_line(e.name);
dbms_output.put_line(e.salary);
dbms_output.put_line(e.title);
end;

例二（insert的情况）
declare
drec dept%rowtype; --定义声明该类型为行数据类型
begin
drec.deptno:=50;
drec.dname:='ADMINISTRATOR';
insert into dept(deptno,dname) values (drec.deptno,drec.dname);

--如果dept表只有这2个字段，可写成insertinto dept values drec; 9i之后支持
commit;
end;

例三（update的情况）
declare
dept_record dept%rowtype
begin
drec.deptno:=30;
drec.dname:='ADMINISTRATOR';
update dept set deptno=drec.deptno where deptno=30;
update dept set dname=drec.dname where deptno=30;
--如果dept表只有这2个字段，可写成update dept set ROW=dept_record where deptno=30; 9i之后支持
commit;
end;

例四（delete的情况）
declare
dr dept%rowtype
begin
dr.deptno:=50;
delete from dept where deptno=dr.deptno;
commit;
end;

2.PL/SQL表
定义：又名索引表。
说明：类似于C语言的数组，不能存储于数据库中，元素个数没有限制，下标可以为负值。
结构：TYPE type_name is table of element_type [NOT NULL] INDEX BYkey_type;

注意：type_name是用户自定义数据类型名，element_typ是指定索引表元素的数据类型，NOT NULL表示不允许引用NULL元素，key_type指定索引表元素下标的数据类型(binary_integer、pls_integer、varchar2(9i后允许))。

1)使用binary_integer或pls_integer定义PL/SQL表下标。
例一：
declare
type v_table is table of emp.ename%type index by binary_integer;
st v_table;
begin
select ename into st(-1) from emp where empno=28; --元素值为字段ename的值，下标为-1
end;
示例说明：emp.ename%type表示存放元素的数据类型，binary_integer表示元素下标的数据类型

例子二：
declare
type emp_table_type is table of emp%rowtype index by binary_integer;
et emp_table_type;
begin
select * into et(1) from emp where empno=&no;
dbms_output.put_line('姓名：'||et(1).ename);
dbms_output.put_line('工资：'||et(1).sal);
dbms_output.put_line('岗位：'||et(1).job);
dbms_output.put_line('时间：'||et(1).hiredate);
end;

2)使用varchar2定义PL/SQL表的下标
declare
type areaTableType is table of number index by varchar(10);
atab areaTableType;
begin
atab('japan'):=1;
atab('China'):=2;
atab('American'):=3;
atab('England'):=4;
atab('Portugal'):=5;
dbms_output.put_line('第一个元素'||atab.first);
dbms_output.put_line('最后一个元素'||atab.last);
end;

3、嵌套表
定义：Nested Table，PL/SQL表没有index by子句就是嵌套表。嵌套表简单来说就是把一个表中的字段定义为一个表，这个字段表的数据存储在外部的一个表中。
说明：数组类型，元素个数无限制，下标从1开始，元素存储可以是稀疏的，使用前必须使用其构造方法进行初始化。(嵌套表可以作为列的数据类型，但PL/SQL表不行)

结构：type type_name is table of element_type;

注意：type_name 指定嵌套表的类型名，element_type指定元素的数据类型。
示例1：
declare
type ename_table_type is table of emp.ename%type; --定义
et ename_table_type; --声明
begin
et:=ename_table_type('MARY','CLARK','DOG'); --初始化(对嵌套表进行初始化可以使用它的构造函数)
select ename into et(2) from emp where empno=&no;
dbms_output.put_line('雇员名：'||et(2)); --输出是CLARK
end;

示例2：嵌套表作为列的数据类型例子，建立对象类型emptype，嵌套表类型emparray，表类型department
createor replace type emptype as object(name varchar2(10),salary number(6,2),hiredatedate);
create or replace type emparray is table of emptype;
create table department(deptno number(2),dname varchar2(10),employee emparray)

nested table employee store as employee_table; --可以理解表department中employee列保存着一个二维数组。

--insert

insert into temp_department

values (1,'scott',temp_emparray(temp_emptype('0471',2, sysdate),temp_emptype('0473', 3, sysdate)));

例三：
createor replace type phone_type is table of varchar2(20);--定义嵌套表类型
createtable person(id number(4),name varchar2(10),sal number(6,2),phone phone_type)
nestedtable phone store as phone_table; --声明嵌套表类型，可以理解为表person中phone列保存着一个一维数组。

--inset

Begin

insert into personvalues(1,'scott',800,phone_type('0471-3456788','13804171235'));

end;

--select
declare
pt phone_type;
begin
select phone into pt from person where name='scott'；
for i in 1..pt.count loop
dbms_output.put_line('第'||i||'个号码:'||pt(1));
end;

--update
declare

ptphone_type:=phone_type('025-58843287','15805175201','0714-6575787');
begin
update person set phone=pt where id=1;
end;

4、varray
说明：变长数组，可以作为列和对象类型属性的数据类型，元素个数有限制，下标从1开始，使用前需要使用其构造方法初始化。

结构：TYPE type_name is varray(size_limit) of element_type [not null];

注意：type_name指定varray类型名，size_limit指定元素个数，element_type指定元素的数据类型。

示例1：作为对象类型属性的数据类型，在PL/SQL块中使用varray
declare
type job_array_type is varray(20) of emp.job%type;
jarr job_arry_type:=job_array_type('HRQ','CLERK'); --初始化
begin
select job into jarr(1) from emp where lower(ename)=lower('&name'); --覆盖了初始化的数值
dbms_output.put_line('岗位:'||jarr(1));
end;
示例1说明：varray (20)就是定义了最大元素个数为20。

例二：在表列中使用varray，作为列的数据类型，作为数据类型必须通过create type建立varray类型。
create type phone_array is varray(20) of varchar(20);
create table worker(id number(4),name varchar2(10),sal number(6,2),phonephone_arra);

例三：作为列的数据类型，建立对象类型article_type，varray类型article_array(20个元素)和表author
create type article_type as object(title varchar2(30),pubdate date);
create type article_array is varray(20) of article_type;
create table author(id number(6),name varchar2(10),article article_array);

总结：
类型      可存储于数据库元素个数    是否需初始化初始下标值
PL/SQL表否            无限        不需         无
嵌套表    可             无限         需           1
可变数组可            有限(自定义) 需          1

个人认为，如果仅仅是在存储过程中当做集合变量使用，PL/SQL表(索引表)是最好的选择。

另外Oracle专门为集合类型(PL/SQL表(别名索引表)、嵌套表、VARRAY)提供了集合变量的方法及嵌套调用等一系列特征，将在本博客单独一遍讲述。

四、引用类型
说明：类似于C语言的指针变量，用于存储只想存储空间的指针。包括游标变量和对象引用变量

种类：ref cursor游标变量、ref object type对象引用变量

1. ref cursor游标变量
declare
type c1 is ref cursor; --定义游标，定义时不需要指定select
cur c1; --声明
col1 varchar2(20);
col2 varchar2(20);
begin
open cur for select dname,loc from dept where deptno=10;--打开游标时指定select，从而实现动态游标操作
fetch cur into col1,col2;
dbms_output.put_line(col1);
dbms_output.put_line(col2);
close cur;
end;

--更详细请看本博客Oracle Cursor文章

2.ref obj_type对象引用变量
用于定义特定对象类型的指针类型，ref实际是指向对象实例的指针，可以共享对象节省对象空间占用。
示例：建立对象类型houseType，对象表houses和population
create or replace type houseType as object(street varchar2(50),city varchar2(20),
state varchar2(20),zipcode varchar2(6),owner varchar2(10));

create table houses of houseType;
insert into houses values('中山路12号','南京','江苏','210090','马明');

create table population(id number(6) primary key,name varchar2(10),addr refhouseType);
insert into population select 2,'马五',ref(p) from houses p where p.owner='马明';

--更详细请看本博客Oracle ObjectType文章

五、用户自定义子类型

语法：subtype subtype_name is base_type

说明：subtype_name是子类型名，base_type是定义子类型的基本类型，基本类型可以是基础类型或子类型，也可以是%type引用或%rowtype。

在定义子类型时，不能指定基本类型的长度(例如varchar2(20))、精度和标度(number(2))。

例一：

Declare

Subtype t_num is number;

V_num t_num;

Subtype t_auth is auths.author_code%type;

V_auth t_auth;

但我们可以限制子类型变量的长度、精度和标度

例二：

Declare

V_temp number(4); --v_temp变量不被引用，只是通过它定义一个受限制的子类型

Subtype t_num is v_temp%type;

V_num t_num;

Subtype t_num is number;

V_num t_num(4); --v_num是一个number(4)类型的变量

六、LOB变量

说明：LOB变量用于存储大对象的数据，包括不超过4G字节的数据

种类：分为内部LOB和外部LOB。内部LOB包括CLOB、BLOB、NCOLB；外部LOB包括BFILE

注意：内部LOB的数据存储在数据库中，并且支持事务操作（提交、回退、保持点）；外部LOB的数据存储在OS文件中，并且不支持事务操作。操纵大对象常需要使用到DBMS_LOB包。

CLOB（存放字符数据）：用于存储大批量字符数据。

NCLOB（存放Unicode字符数据）：用于存储大批量字符数据。
BLOB（存放二进制数据）：存放非结构化的二进制数据，例如：用于存放音频、图像、图像。
BFILE（存储指向OS文件的指针）：

用于把非结构化的二进制数据存储在数据库以外的操作系统文件中。Oracle在数据库中只存储文件指针，实际的文件存储在操作系统的外部文件中，例如：用于存储电影文件。

布尔类型Boolean（not\and\or\every\and）

集合类型array

自定义类型Distinct

to_lob的使用例子（转化long）：

报错：select VIEW_NAME,to_lob(s.text) textfrom user_views s

通过：create tabletemp_liutao nologging as select VIEW_NAME,to_lob(text) text from user_views