Oracle 数据类型（全大写）

Oracle 基本数据类型可以按类型分为：字符串类型、数字类型、日期类型、LOB 类型、LONG RAW&RAW 类型、ROWID&UROWID 类型。

数值类型

  • NUMBER(M, n) — 整数/浮点数，m：有效数字个数，n: 小数点后个数
  • OLS_INTEGER — 只能存储整数
  • binary_Integer — 只能存储整数

字符类型

  • 字符串数据类型可以依据存储空间分为固定长度类型（CHAR/NCHAR) 和可变长度类型（VARCHAR2/NVARCHAR2)两种。

    • 固定长度：是指虽然输入的字段值小于该字段的限制长度，但是实际存储数据时，会先 自动向右补足空格 后，才将字段值的内容存储到数据块中。
    • 可变长度：是指当输入的字段值小于该字段的限制长度时，直接将字段值的内容存储到数据块中，而 不会补上空白，这样可以节省数据块空间。

  • char(maxlength; 2000 字节)：指定长度的字符串，默认为 1，赋值给大的会用空格填充来达到其最大长度。另外你可以指定它存储字节或字符，例如 CHAR(12 BYTYE)、CHAR(12 CHAR)。一般来说默认是存储字节。

  • nchar(8.0 以后出现)：一个包含 UNICODE 格式数据的定长字符串。NCHAR 字段最多可以存储 2000 字节的信息，它的最大长度取决于国家字符集。

  • varchar2(maxlength: 4000 字节): 可变长度的字符, maxlength(必须指定): 最大 32767

  • nvarchar2: 一个包含 UNICODE 格式数据的变长字符串，NVARCHAR2 最多可以存储 4000 字节的信息。

  • Long：可变长字符串，最大 32767 字节，数据库最大（2G）

日期类型

  • DATE: 可以保存日期和时间，常用日期处理都可以采用这种类型。DATE 表示的日期范围可以是公元前 4712 年 1 月 1 日至公元 9999 年 12 月 31 日

    • date 类型在数据库中的存储固定为 7 个字节，格式为：
      第 1 字节：世纪+100
      第 2 字节：年
      第 3 字节：月
      第 4 字节：天
      第 5 字节：小时+1
      第 6 字节：分+1
      第 7 字节：秒+1

  • **TIMESTAMP(p)**：与 date 的区别是不仅可以保存日期和时间，还能保存小数秒

    • 小数位数可以指定为 0-9，默认为 6 位，所以最高精度可以到 ns(纳秒)，数据库内部用 7 或者 11 个字节存储
      • 如果精度为 0，则用 7 字节存储，与 date 类型功能相同，
      • 如果精度大于 0 则用 11 字节存储。
    • 格式为：
      第 1 字节：世纪+100
      第 2 字节：年
      第 3 字节：月
      第 4 字节：天
      第 5 字节：小时+1
      第 6 字节：分+1
      第 7 字节：秒+1
      第 8-11 字节：纳秒，采用 4 个字节存储，内部运算类型为整形
插入语句 结果
insert into t_timestamp values(‘22-12 月-2018’); 22-12 月-18 12.00.00.000000 上午
insert into t_timestamp values(to_date(‘2018-12-22’, ‘yyyy-mm-dd’)); 22-12 月-18 12.00.00.000000 上午
insert into t_timestamp values(to_date(‘2018-12-22 15: 22: 26’, ‘yyyy-mm-dd hh24: mi: ss’)); 22-12 月-18 03.22.26.000000 下午
insert into t_timestamp values(to_timestamp(‘2018-12-22 15: 22: 26.256’, ‘yyyy-mm-dd hh24: mi: ss.ff‘)); 22-12 月-18 03.22.26.256000 下午#注意：未指定毫秒数长度时，根据具体值的毫秒数进行转换（比如 6 位就转为 6 位）。
insert into t_timestamp values(to_timestamp(‘2018-12-22 15: 22: 26.2567’, ‘yyyy-mm-dd hh24: mi: ss.ff4‘)); 22-12 月-18 03.22.26.256700 下午#指定毫秒数
插入语句 结果
insert into t_date values(‘22-12 月-2018’); 2018/12/22
insert into t_date values(to_date(‘2018-12-22’, ‘yyyy-mm-dd’)); 2018/12/22
insert into t_date values(to_date(‘2018-12-22 15: 22: 16’, ‘yyyy-mm-dd hh24: mi: ss’)); 2018/12/22 15: 22: 16
insert into t_date values(‘2018-12-22’); ORA-01861: 文字与格式字符串不匹配
insert into t_date values(‘22-12-18’); ORA-01843: 无效的月份
insert into t_date values(‘22-12-2018’); ORA-01843: 无效的月份
insert into t_date values(‘12 月-22-2018’); ORA-01861: 文字与格式字符串不匹配
insert into t_date values(‘22-12 月-2018 10: 17: 25’); ORA-01830: 日期格式图片在转换整个输入字符串之前结束
insert into t_date values(to_date(‘2018-12-22 15: 22: 16’, ‘yyyy-mm-dd hh: mi: ss’)); ORA-01849: 小时值必须介于 1 和 12 之间

boolean

取值： TRUE/FAlSE/NULL

特殊数据

%TYPE

1
2
declare var_job emp.job%type；
var_job 和emp.job的数据类型一样

RECORD 类型

这是一种 结构化的数据类型

使用 type 语句进行定义

into 子句，位于 select 子句的后面，用于将从数据库检索的数据存储到变量里。

notes: into 子句只能存储一个单独的值，所以 select 之句只能返回一行数据，需要用 where 进行限定，如果返回多行数据会返回错误信息。

%ROWTYPE

根据表中行的结构定义的一种特殊的数据类型，用来存储数据表中检索的一行数据

1
2
3
4
5
rowVar_name table_name%rowtype;

rowVar_name ：存储一行的数据的变量名

table_name:指定的表名

变量和常量的定义

PL/SQL 中变量的定义要求在变量名子啊数据类型的前面，长度和出初始值可选

定义变量

1
var_countryname varchar2(50):='中国'

定义常量

1
con_day constant integer:=365

变量的初始化

PL/SQL 定义的变量初始值为 NULL

PL/SQL 表达式

表达式的结果是一个值；

表达式作为赋值语句的一部分出现在赋值运算符的右边，或作为函数的参数

字符表达式

唯一的是“||” ,

作用：把几个字符串连接在一起

如：

1
'hello'||'world'||'!'的值为'helloworld!'

布尔表达式

是一个判断结果是为真还是假的条件

流程控制语句

选择语句 if…then

if…then…else

IF … then …elsif 语句

case 语句

从 oracle 9i 以后出现

循环语句

主要包括：LOOP/while/for

LOOP(至少一遍)

while（先判断后执行）

for 循环

demo：

1
2
3
4
5
6
7
8
9
DECLARE
   a number(30) := 0;
BEGIN
   for i in 1 .. 10 loop
      --INSERT INTO FW_TEST(NAME) VALUES('bbb' + i);
       sys.dbms_output.put_line('bbb' || i);
   end loop;
   commit;
END;

说明：

  • DECLARE:

    申明变量语句，变量名可以在 BEGIN…END 语块中使用。

  • BEGIN…END

    1
    语句块，相当于java中的{}.

  • for i in 1 .. 10 loop … end loop

    1
    for循环语法，变量i，从1开始，直到10才遍历结束

ORACLE表空间不足

  • 表空间查看

    1
    2
    3
    ## 查看OA用户所占的空间
    ## 用该用户登录,执行如下SQL
    Select  sum(bytes)/1024/1024 MB  from user_extents u

  • 查看表空间还剩多少，执行如下sql：该语句通过查询 dba_free_space，dba_data_files，dba_tablespaces 这三个数据字典表，得到了表空间名称，表空间类型，区管理类型，以”兆”为单位的表空间大小，已使用的表空间大小及表空间利用率。dba_free_space 表描述了表空间的空闲大小，dba_data_files 表描述了数据库中的数据文件，dba_tablespaces 表描述了数据库中的表空间

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    select
    a.a1 tablespacename, -- 表空间名称,
    c.c2 tablespacetype, -- 类型,
    c.c3, --区管理,
    b.b2/1024/1024 tablespaceSize, --表空间大小 M,
    (b.b2-a.a2)/1024/1024 tablespaceYONG,--已使用 M,
    substr((b.b2-a.a2)/b.b2*100,1,5) beifen --利用率
    from
    (select tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by
    tablespace_name) a,
    (select tablespace_name b1,sum(bytes) b2 from dba_data_files group by
    tablespace_name) b,
    (select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces)
    c
    where a.a1=b.b1 and c.c1=b.b1;

扩展表空间解决方案

  • 查看表空间的名字和文件位置。执行如下sql：
1
2
3
4
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
  • 扩展表空间方法有以下两种：

  1. 在现有基础上增大所需表空间的尺寸

    1
    2
    # alter database datafile '表空间储存位置'resize 新的尺寸，例如：
    alter database datafile 'e:\oracle\oradata\db.dbf'resize 4000m;

  2. 增加数据文件。让表空间名对应更多的数据文件

    1
    2
    # add datafile '新数据文件的储存位置' size 新数据文件的尺寸，例如：
    alter tablespace ecology add datafile 'e:\oracle\oradata\db2.dbf’ size 1000m