Oracle学习——数据类型

最新推荐文章于 2023-03-13 22:16:52 发布

半夏云流

最新推荐文章于 2023-03-13 22:16:52 发布

阅读量1.5k

点赞数 1

分类专栏： Oracle

本文链接：https://blog.csdn.net/qq_33195791/article/details/86702674

版权

Oracle 专栏收录该内容

7 篇文章 2 订阅

订阅专栏

由于篇幅较多，前面先做一个简单的摘要，不少文本直接从书籍上贴图，本篇主要介绍了Oracle数据库常用的数据类型：

1.字符类型：CHAR，VARCHAR2

2.数字类型：NUMBER，BINARY_INTEGER

3.日期和时间类型：DATE，TIME STAMP

4.引用类型：REF，主要用于获取表的记录。配合 ROWTYPE。

5.数据类型转换：TO_CHAR函数，TO_DATE函数，TO_NUMBER函数

1.字符类型

1.1 CHAR

1.2.VARCHAR2

1.3.LONG和LONG RAW(向后兼容发明，避免使用？)

2.2 PLS_INTEGER和BINARY_INTEGER

3. 日期和时间类型

3.1.DATE

3.2.TIMESTAMP

3.3.TIMESTAMP WITH TIME ZONE

3.4.TIMESTAMP WITH LOCAL TIME ZONE

1.字符类型

1.1 CHAR

声明语法

CHAR[(maximum_size[CHAR | BYTE])]

[]表示其中的部分是可选的内容。

maximum 取值范围是1-32767，默认为1，不能是变量或常量，只能是数值。参数CHAR或BYTE用于指定是以字节为单位还是以字符为单位，默认使用字节作为单位。二者区别是字符可以包括一个或多个字节，取决于对字符集的设置，默认使用NLS_LENGTH_SEMANTICS来初始化参数。

存储固定长度的字符数据，CHAR有一个可选的整形参数用来指定字符的长度，最大32767个字节。Oracle数据库中，CHAR类型数据最大支持2000字节，因此在指定长度的时候，不能超过2000。

下面列举CHAR的使用

declare
	v_name	char(2 BYTE);
	v_name2	char(2 CHAR);
	v_name3	char;
	v_name4	char(20);
begin
	v_name:='ab';
	--v_name:='中国'	--错误，大于二个字节
	v_name2:='中国';
	v_name3:=1;--正确，1个字节;
	v_name4:='this is string.';
	DBMS_OUTPUT.PUT_LINE('the length of v_name4 is ' || length(v_name4));
end;
/

输出结果：

the length of v_name4 is 20

CHAR是定长的，对于赋值字节超出的会抛出错误，对于字节不足的，会在不足的位置上补上空白字符。

1.2.VARCHAR2

声明语法：
VARCHAR2(maximum_size[CHAR | BYTE])
参数CHAR或BYTE用于指定是以字节为单位还是以字符为单位。maximum_size的范围为1-32767，但是在数据库中VARCHAR2的最大长度为4000字节，因此赋值时应注意大小限制。

使用实例：

SET SERVEROUTPUT ON;
declare
	v_name	varchar2(25);
	v_name1	varchar2(25 BYTE);
	v_name2	varchar2(25 CHAR);
	--v_name3	varchar2;	--错误，必须制定长度
begin
	v_name:='中华人民共和国';
	DBMS_OUTPUT.PUT_LINE('the length of v_name is ' || length(v_name) || '字节');
	
	v_name1:='中华人民共和国';
	DBMS_OUTPUT.PUT_LINE('the length of v_name1 is ' || length(v_name1) || '字节');
	
	v_name2:='中华人民共和国';
	DBMS_OUTPUT.PUT_LINE('the length of v_name2 is ' || length(v_name2) || '字节');
end;
/

输出：

上述varchar2类型变量使用length函数输出字符串长度，都是7，这是因为字符的尺寸依赖于 NLS_LENGTH_SEMANTICS初始化参数来确定，可以通过查询NLS_SESSION_PARAMETERS确定当前的设置。

varchar2有两个子类型，string和varchar，他们的功能与varchar2完全相同，可以看做是varchar2的一个别名，使用这些子类型
是为了与IBM等版本数据库兼容。最好的建议就是使用varchar2。

1.3.LONG和LONG RAW(向后兼容发明，避免使用？)

在Oracle 11g中，LONG和LONG RAW数据类型是为了保持向后兼容性，对于LONG类型，可以使用VARCHAR2(32760)、BLOB、NCLOB
来代替，对应LONG RAW，可以使用BLOB来代替，关于结束如下：

1.4.ROWID和IROWID

每个Oracle数据表都有一个名为ROWID的伪列，这个伪列用来存放每一行数据的存储地址的二进制值，例如下面查询使用ROWIDTOCHAR函数
将ROWID值转换为字符串显示：

select rowidtochar(rowid),sid,sname from student2;

每个rowid的值有18个字符组合表示，rowid又有物理rowid和逻辑rowid。物理rowid用啦表示普通数据表中的一行信息，而逻辑rowid能够表示索引组织表中的一行信息，rowid只能存储物理内容，urowid或称通用rowid类型可以存储物理、逻辑或非Oracle的rowid，物理的rowid可以显著加快数据检错的性能，因为只要行在，物理rowid的值就不会变，pl/sql的rowid可以借助于rowidtochar函数或者chartorowid函数来获取或者转换rowid信息，例如下列的实例演示了如何在pl/sql语句块中使用rowid。

declare
	v_stu_name		ROWID;
	v_stu_othername	varchar2(18);
begin
	select rowid
		into v_stu_name
		from student2
		where sid = &tid;
	--输出rowid的值
	DBMS_OUTPUT.PUT_LINE('rowid1:' || v_stu_name);
	v_stu_othername := rowidtochar(v_stu_name);	--转换rowid为字符串
	DBMS_OUTPUT.PUT_LINE('rowid2:' || v_stu_othername);
end;
/

上述代码首先从数据表student2中直接查询伪列rowid的值，保存到v_stu_name这个rowid类型的变量中，然后使用dbms_output.put_line直接输出了rowid的值。v_stu_othername使用rowidtochar将v_stu_name这个rowid值转换哪位字符串类型再输出，可以看出得到了相同的rowid结果。

1.5 NCHAR和NVARCHAR

nvarchar2是varchar2的Unicode版本，用来存储变长的双字节字符，nvarchar2的声明语法如下：

nvarchar2(maximum_size)

maximum_size使用整形数字来指定其最大长度，与varchar2相似，其最大长度限制为32767字节，但是使用utf8格式是是32767/3，使用al16utf16编码格式时为32767/2。
与nchar相似，最大值代表的总是字符个数，不能再字符与字节形式见选择。
Oracle数据库中国，nvarchar2的最大宽度是4000字节，如果列中要保存中文字符，那么最多指定2000个，每个中文字符占用2字节。

2.数字类型

数字类型用来保存整型，浮点型和实数类型数据，pl/sql可以使用的数字类型下所示

2.1 NUMBER

NUMBER 类型既可以表示整数，也可以表示浮点数，其声明语法如下

NUMBER[(precesion,scale)]

number语法中的两个参数用于声明精度和刻度，含义如下
#精度：所允许的值的总长度，也就是数值中所有数字位的个数，最大值为38.
#刻度：刻度范围是小数点右边的数字位的个数，可以使负数，表示由小数点开始想做进行计算数字的个数。

注意：精度和刻度范围是可选的，但是如果指定了刻度范围，那么必须指定精度。
如果不指定精度和刻度，则使用最大的精度声明number类型，即默认精度为38。刻度用来确定小数位数，同时确定在什么地方进行四舍五入，范围在-84~127。
如果被指派的值超过了指定的刻度范围，则存储值会按照刻度指定的位数进行四舍五入。
下面为number类型的使用实例

declare
	v_num1	number:=3.1415926;	--结果3.1415926
	v_num2	number(3):=3.1415926;	--四舍五入等于3
	--v_num2_1	number(3):=3145.1415926	--错误，精度太高
	
	v_num3	number(4,3):=3.1415926;	--结果：3.142
	--v_num3_1	number(4,3):=314.123;	--错误，精度太高
	
	v_num4	number(8,3):=31415.9267;	--四舍五入2位小数，结果未31415.927
	v_num5	number(4,-3):=3145611.789;	--由于为负3，要从小数点左侧开始舍入，清除向左的位数，结果未3146000
	v_num5_1	number(4,-3):=314.567895;	--舍入后的结果未0
	v_num6	number(4,-1):=31451;	--舍入后结果未31450
	--v_num6_1	number(4,-1):=33145123; -- 错误，精度太高
begin
	DBMS_OUTPUT.PUT_LINE('v_num1 = ' || v_num1);
	DBMS_OUTPUT.PUT_LINE('v_num2 = ' || v_num2);
	DBMS_OUTPUT.PUT_LINE('v_num3 = ' || v_num3);
	DBMS_OUTPUT.PUT_LINE('v_num4 = ' || v_num4);
	DBMS_OUTPUT.PUT_LINE('v_num5 = ' || v_num5);
	DBMS_OUTPUT.PUT_LINE('v_num5_1 = ' || v_num5_1);
	DBMS_OUTPUT.PUT_LINE('v_num6 = ' || v_num6);
end;
/

输出：

2.2 PLS_INTEGER和BINARY_INTEGER

PLS_INTEGER和BINARY_INTEGER具有相同的取值范围，都是从-2147483647到+2147483647，PLS_INTEGER相对NUMBER来说需要更少的内存空间来存储数据，而且在计算方面也比NUMBER更有效率。
注意：PLS_INTEGER和BINARY_INTEGER在数学运算方面产生溢出时会抛出ORA-01426溢出异常。NUMBER数据类型是以十进制格式进行存储的，为了进行算数运算，NUMBER必须要转换为二进制类型，因此效率相对来说会较慢。
BINARY_INTEGER以2的补码二进制形式进行存储，可以直接进行计算而无需转换，因此在PL/SQL块中而非数据库中的变量用于计算时，使用BINARY_INTEGER可以提供较好的性能。
PLS_INTEGER与BINARY_INTEGER类似，两者的典型区别是:如果在为PLS_INTEGER类型的变量赋值溢出时，会触发异常；而当为BINARY_INTEGER类型的变量赋值溢出时，会将结果指派为NUMBER类型的拥有最大精度的类型，不会触发异常。

--PLS_INTEGER 使用实例

--PLS_INTEGER 使用实例
SET SERVEROUTPUT ON;
declare
	v_num1	PLS_INTEGER:=2147483647;--最大值为
begin
	--当为v_num1+1时，产生了溢出，会触发异常
	v_num1:=v_num1+1-1;
	exception
		when others then
		--输出：-ORA-01426:数字溢出
		DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;
/

--BINARY_INTEGER使用实例

--BINARY_INTEGER使用实例
declare
	v_num1	BINARY_INTEGER:=-2147483647;
begin
	/*
	当为v_num1+1时，产生了溢出，
	此时v_num1会被当做NUMBER进行处理，不会触发异常。
	*/
	v_num1:=v_num1+1-1;
	DBMS_OUTPUT.PUT_LINE('v_num1' || v_num1);
end;
/

两种数据处理异常的情况如结果所示。

3. 日期和时间类型

3.1.DATE

DATE类型用来存储时间和日期信息，包含世纪，年，月，日，小时，分钟和秒，但是不包含秒的小数部分。DATE类从世纪到秒每一部分是一个字节，即占用7个字节。可以使用TO_DATE和TO_CHAR这两个内置的函数在日期和字符串之间转换，以下是使用实例：

SET SERVEROUTPUT ON;
declare
	--输出当前周的第1天
	v_weekday	DATE:=TRUNC((SYSDATE) - TO_CHAR(SYSDATE,'D')) +1;
	--v_weekday	DATE:=(SYSDATE) - TO_CHAR(SYSDATE,'D') +1; 与上一行一样的结果
	--输出现在的时间
	v_now	DATE:=SYSDATE;
begin
	DBMS_OUTPUT.PUT_LINE('本周第一天日期是' || v_weekday);
	DBMS_OUTPUT.PUT_LINE('当前日期是' || v_now);
	
	DBMS_OUTPUT.PUT_LINE('本周第一天日期是(TO_CHAR)：' || TO_CHAR(v_weekday, 'yyyy-mm-dd'));
	DBMS_OUTPUT.PUT_LINE('当前日期是(TO_CHAR)：' || TO_CHAR(v_now, 'yyyy-mm-dd hh24:mi:ss'));
end;
/

输出时间的格式如结果所示，可以使用TO_CHAR函数来转换为指定的格式输出，如果不转换，默认输出年月日。
可以直接对日期进行加减运算，这样返回的是两个日期之间相关的额天数。PL/SQL会将进行运算的整数看做是天数，
例如要将当前的日期减5天，那么可以使用如下的查询代码：

select sysdate -5 from dual;

3.2.TIMESTAMP

与DATE类型相同，但是可以存储秒的小数部分。
语法：
TIMESTAMP[(P)];
P是秒字段的小数部分的精度，默认为6。

SET SERVEROUTPUT ON;
declare
	v_stime		TIMESTAMP(8):=SYSDATE;
	v_time		DATE:=SYSDATE;
begin
	DBMS_OUTPUT.PUT_LINE('当前日期是:' || v_stime);
	DBMS_OUTPUT.PUT_LINE('当前日期是:' || v_time);
end;
/

3.3.TIMESTAMP WITH TIME ZONE

3.4.TIMESTAMP WITH LOCAL TIME ZONE

3.5. INTERVAL类型

4.BOOLEAN类型

Oracle数据库并不包含布尔类型，多数情况下使用char(1)来代替布尔值，pl/sql为了结构化程序的需要包含了布尔值，
不能往数据库中插入或者从数据库中检索出布尔类型的值。
可以使用BOOLEAN类型来存储逻辑值True，False和NULL值，布尔值仅用在逻辑操作中，而不能使用布尔值与数据库交互。
注意：在为布尔值赋值TRUE或者FALSE时，不需要使用引号，否则会触发异常。

SET SERVEROUTPUT ON;
declare
v_condition		BOOLEAN;
begin
	v_condition := True;
	--v_condition := 'False'
	IF v_condition then
		DBMS_OUTPUT.PUT_LINE('值为true');
	end if;
end;
/

5.引用类型REF

在Oracle的引用类型分类中有两种引用类型，分别是REF CURSOR 和REF类型，引用类型与其他类型的主要不同之处在于内存
和存储的处理。引用类型与C语言中的指针的概念是相同的。在PL/SQL中，对于普通的类型，不管声明为标量类型还是符合
类型，都会为其分配内存，在变量的声明周期结束后，会释放这个内存。但是引用类型的变量在声明的过程中并没有分配内存，
只在指向某一个变量时，才指向改变量的内存区，在程序的生命周期中可以指向不容的存储位置。

5.1.REF CURSOR

REF CURSOR类型的变量同城称为游标变量，开发人员可以通过定义一个SYS_REFCURSOR类型的变量，从过程中或者函数中获取一个记录集，YSS_REFCURSOR是一个弱类型的REF CURSOR 类型的引用类型，实例如下

声明一个指向表指针的引用，再调用函数遍历表。

create or replace function selectallstudents
	return sys_refcursor	--定义一个返回sys_refcursor的函数
as
	st_cursor	sys_refcursor;
begin
	open st_cursor for
		select * from student2;
		--返回指向游标的指针
	return st_cursor;
end;
/

--使用引用游标实例

SET SERVEROUTPUT ON;
declare
	x	sys_refcursor;	--定义引用游标变量
	v_stu	student2%ROWTYPE;	--定义获取游标结果的记录类型
begin
	x := selectallstudents;	--调用函数获取游标指针
	--循环遍历游标指着
	loop
		fetch x
			into v_stu;
		--没有找到时推出
		exit when x%NOTFOUND;
		--输出记录的值
		DBMS_OUTPUT.PUT_LINE('ID:' || v_stu.sid ||',NAME:' || v_stu.sname || ',ADDRESS:' || v_stu.saddress);
	
	end loop;
end;
/

5.2 REF 在后续章节单独介绍

6. 数据类型转换

6.1显示转换

6.2 隐式转换

半夏云流

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Oracle学习——数据类型

由于篇幅较多，前面先做一个简单的摘要，不少文本直接从书籍上贴图，本篇主要介绍了Oracle数据库常用的数据类型：1.字符类型：CHAR，VARCHAR22.数字类型：NUMBER，BINARY_INTEGER3.日期和时间类型：DATE，TIME STAMP4.引用类型：REF，主要用于获取表的记录。配合 ROWTYPE。5.数据类型转换：TO_CHAR函数，TO_DATE函...
复制链接

扫一扫