本文详细介绍了Oracle数据库的基础知识，包括数据库概念、数据类型、表空间、SQL语言和PL/SQL编程。讨论了Oracle中的各种数据类型，如数值、日期时间、LOB，以及伪列ROWID和ROWNUM的用途。深入讲解了SQL语言，如DDL、DML、TCL和DCL，并介绍了PL/SQL中的控制语句、异常处理和存储过程。此外，还涵盖了表空间、用户管理和权限控制、序列、索引和分区表等内容。
Oracle数据库基础
1.Oracle基本概念
1.1数据库
是磁盘上存储数据的集合，在物理上表现为数据文件，日志文件和控制文件等，在逻辑上以表空间存在
1.2全局数据库名
由数据库名称和域名构成，类似网络中的域名，是数据库的命名在整个网络环境中唯一
1.3数据库实例
每个启动的数据库都对应一个数据库实例，由这个实例来访问数据库中的数据
1.4表空间
一个表空间可以由多个数据文件组成，但一个数据文件只能属于一个表空间
1.5数据文件
一个数据文件中可能存储很多个表的数据，而一个表的数据也可能存放在多个数据文件中，即数据表和数据文件不存在一对一的关系
1.6控制文件
由于控制文件的重要性，因此一个数据库至少要有一个以上的控制文件，Oracle 11g默认包含三个控制文件，各个控制文件内容相同
1.7日志文件
在Oracle数据库中，日志文件是组成使用的，每个日志文件组可以有一个或多个日志文件
1.8模式和模式对象
模式是数据库对象的集合，Oracle会为每一个数据库用户创建一个模式，此模式为当前用户拥有，和用户具有相同的名称
2.Oracle数据类型
2.1 char数据类型
这种数据类型的列长度可以使1~2000字节，如果在定义时未指明大小，则默认其占有1字节，如果用户输入的值小于指定的长度，则数据库用空格填充至固定长度
2.2 varchar2数据类型
类型大小为1~4000字节，在定义该数据类型时，应指定其大小
2.3 nchar数据类型
nchar，即国家字符集，使方法和char相同
3.数值数据类型
number数据类型可以在存储正数，负数，零，定点数和精度为38位的浮点数
4.日期时间数据类型
4.1 date 数据类型
用于存储表中的日期和时间数据，Oracle数据库使用自己的格式存储日期，使用7字节固定长度，每字节分别存储世纪，年，月，日，小时，分和秒。
4.2 TIMESTAMP 数据类型
TIMESTAMP 数据类型用于存储日期的年，月，日，以及时间的小时，分，秒，其中秒值精确到小数点后6位，该数据类型同时包含时区信息
5.LOB 数据类型
5.1 CLOB
CLOB能够存储大量字符数据
5.2 BLOB
BLOB可以存储较大的二进制对象
5.3 BFILE
BFILE能够将二进制文件存储在数据库外部的操作系统文件中
6. Oracle 中的伪列
6.1 ROWID
数据库中的每行都有一个行地址，ROWID伪列返回该行地址
ROWID伪列有以下重要的用途
1.能以最快的方式访问表中的一行
2.能显示表的行是如何存储的
3.可以作为表汇总行的唯一标识
6.2 ROWNUM
对于一个查询返回的每行，ROWNUM伪列返回一个数值代表行的次序
7.SQL语言简介
1.数据定义语言（DDL）：create（创建），ALTER（更改），TRUNCATE（截断）和DROP（删除）命令
2.数据操纵语言（DML）：INSERT（插入），SELECT（选择），DELETE（删除）和UPDATE（更改）命令
3.事务控制语言（TCL）：COMMIT（提交），SAVEPOINT（保存点）和ROLLBACK（回滚）命令
4.数据控制语言（DCL）：GRANT（授予）和REVOKE（回收）命令
7.1数据定义语言
数据定义语言中，create table语句用来创建新表，alert table语句用来修改表结构，truncate table语句用来删除表中所有记录，drop table语句用力啊删除表
7.1.1 create table命令
语法：create table[schema.] table（colum datatype[，colum datatype[，…]]）
schema表示对象的所有者，即模式的名称
table表示表的名称
colum表示列的名称
datatype表示该列的数据类型及其宽度
Oracle和SQL Server数据库对象表之间的差异
| 类别 | Oracle | SQL Server |
|---|---|---|
| 列数 | 254 | 1024 |
| 行大小 | 没有限制 | 8060字节，加16字节指向每个text或image列 |
| 最大行数 | 没有限制 | 没有限制 |
| 表命名规则 | [schema.]table_name | [[[server.]database.]owver.] table_name |
Oracle和SQL Server之间数据类型的默认映射情况
| Oracle数据类型 | SQL Server数据类型 | |
|---|---|---|
| BFILE | VARBINARY(MAX) | |
| BLOB | VARBINARY(MAX) | |
| CHAR([1-2000]) | CHAR([1-2000]) | |
| CLOB | VARCHAR(MAX) | |
| DATE | DATETIME | |
| FLOAT | FLOAT | |
| FLOAT([1-53]) | FLOAT([1-53]) | |
| FLOAT([54-126]) | FLOAT | |
| INT | NUMERIC(38) | |
| INTERVAL | DATETIME | |
| LONG | VARCHAR(MAX) | |
| LONG RAW | VARCHAR(MAX) | |
| NCHAR([1-1000]) | NCHAR([1-1000]) | |
| NCLOB | NVARCHAR(MAX) | |
| NUMBER | NVARCHAR(MAX) | |
| NUMBER([1-38]) | NUMERIC([1-38]) | |
| NUMBER([0-38],[1-38]) | NUMERIC([0-38],[1-38]) | |
| NVARCHAR2([1-2000]) | NVARCHAR([1-2000]) | |
| RAW([1-2000]) | VARBINARY([1-2000]) | |
| REAL | FLOAT | |
| ROWID | CHAR(18) | |
| TIMESTAMP | DATETIME | |
| UROWID | CHAR(18) | |
| VARCHAR2([1-4000]) | VARCHAR([1-4000]) |
7.1.2 truncate table命令
语法：truncate table;
8.数据操纵语言
用于检索，插入和修改数据库信息，他是最常用的sql命令。如，insert，update，select，delete
9.事务控制语言
（1）COMMIT：提交事务，即把事务中对数据库的修改进行永久保存
（2）ROLLBACK：回滚事务，即取消对数据库所做的任何修改
（3）SAVEPOINT：在事务中创建存储点
（4）ROLLBACK TO<SavePoint_Name>：将事务回滚到存储点
10.数据局控制语言
数据控制语言为用户提供权限控制命令，数据库对象的所有者对这些对象拥有控制权限，所有者可以根据自己的意愿决定其他用户如何访问对象，授予其他用户权限，使他们可以在其权限范围内执行操作
11.SQL操作符
11.1 算数操作符
算术表达式由number数据类型的列名，数值常量和连接它们的算术操作符组成，算术操作符包括+,-,*,/
11.2比较操作符
比较操作符包括=,!=,<,>,<=,>=,BETWEEN…AND，IN，LIKE和IS ULL
11.3逻辑操作符
用于组合对个基表运算的结果以生成一个或真或假的结果，逻辑操作符包括AND ,OR,NOT
11.4集合操作符
可以在sql使用下面的集合操作符来组合多个查询中的行
UNION（联合）。
UNION ALL（联合所有）。
INTERSECT（交集）。
MINUS（减集）。
使用集合操作符连接起来的select语句中的列遵循以下规则
通过集合操作符连接的各个查询具有相同的列数，而且对应列的数据类型必须兼容
这种查询不应含有long类型的列
11.5连接操作符
连接操作符（||）用于将两个或多个字符串合并成一个字符串，或者将一个字符与一个数值合并在一起
12.SQL函数
12.1 转换函数
将值从一种数据类型转换为另一种数据类型
常用的转换函数
| 函数 | 功能 | 实例 | 结果 |
|---|---|---|---|
| TO_CHAR | 转换成字符串类型 | TO_CHAR（1234.5，$9999.9） | $1234.5 |
| TO_DATE | 转换成日期类型 | TO_DATE（‘1980-01-01’，‘yyyy-mm-dd’） | 01-1 月-80 |
| TO_NUMBER | 转换成数值类型 | TO_NUMBER（‘1234.5’） | 1234.5 |
13.分析函数
语法：函数名([参数]) over([分区字句][排序字句])
函数命名表示分析函数的名称
参数表示函数需要传入的参数
分区字句表示将查询结果分为不同的组，功能类似于GROUP BY语句，是分析函数工作的基础
排序字句表示将每个分区进行排序
1.ROW_NUMBER：返回一个唯一的值，当遇到相同数据时，排名按照记录集中记录的顺序依次递增
2.DENSE_RANK：返回一个唯一的值，当玉带相同数据时，排名按照相同数据的排名都是一样的
3.RANK：返回一个唯一的值，当遇到相同的数据时，所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名
Oracle数据应用
14.表空间
表空间可以存放各种应用对象，如表，索引，二每个表空间由一个或多个数据文件组成
14.1 表空间的分类
永久性表空间
临时性表空间
撤销表空间
14.2表空间的目的
对不同的用户分配不同的空间
将不同的数据文件创建到不同的磁盘
14.3.创建表空间
语法：
CREATE TABLESPACE tablespacename
DATAFILE ‘filename’ [SIZE integet [K|H]]
[AUTOEXTEND [OFF | ON]];
14.4.删除表空间
DROP TABLESPACE tablespacename;
15.自定义用户管理
15.1 Sys用户：超级用户
15.2 System:系统管理员
15.3 Scott:示范用户
16.数据库权限管理
16.1 系统权限
常见的系统权限如下：
CREATE SESSION:连接到数据库
CREATE TABLE:创建表
CREATE VIEW:创建实体
CREATE SEQUENCE:创建序列
16.2 对象权限
CONNECT:连接到数据库的用户，特别是不需要创建表的用户，通常授予该权限；
RESOURCE：更为可靠的和正式的数据库用户可授予该角色；
DBA:数据库管理员，用于管理数据库的最高权限；
授予权限语法：
GRANT 权限|角色 TO 用户名
17.序列
17.1 创建序列
语法：
CREATE SEQUENCE sequence_name
[START WITH integer]
[INCREMENT BY integer]
[MAXVALUE integer | NOMAXVALUE]
[MINVALUE integer | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE integer | NOCACHE];
17.2 访问序列
NEXTVAL:第一次使用时，返回该序列的初始值
CURRVAL:返回序列的当前值
17.3 更改序列
语法：
ALTER SEQUENCE [schema.]sequence_name
[INCREMENT BY integer]
[MAXVALUE integer | NOMAXVALUE]
[MINVALUE integer | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE integer | NOCACHE];
17.4 删除序列
语法：
DROP SEQUENCE [schema.]sequence_name
17.5 同义词
1.简化ＳＱＬ语句
2.隐藏对象的名称和所有者
3.位分布式数据库的远程对象提供了位置透明性
4.提供对对象的公共访问
17.6 同义词分类
1.私有同义词
语法：
CREATE [OR REPLACE] SYNONYM [schema.]synonym_name
FOR [schema.]object_name
2.公有同义词
语法：
CREATE [OR REPLACE] PUBLIC SYNONYM [schema.]synonym_name
FOR [schema.]object_name
3.公有同义词和私有同义词的区别
1、私有同义词只能在当前模式西访问，不能和当前模式的对象同名
2、公有同义词可被所有的数据库用户访问
3、创建同义词需要拥有一定的权限
17.7 删除同义词
语法：
DROP [PUBLIC] SYNONYM [schema.]synonym_name
18.索引
索引是与表关联的可选结构，是一种快速访问数据库的途径，可提高数据库性能
18.1 索引的分类
索引分类表
| 物理分类 | 逻辑分类 |
|---|---|
| 分区或非分区索引 | 单列或组合索引 |
| B树索引（标识索引） | B树索引（标识索引） |
| B树索引（标识索引） | B树索引（标识索引） |
| 位图索引 |
18.2 B树索引
语法：
CREATE [UNIQUE] INDEX index_name ON table_name(column_list)
[TABLESPACE tablespace_name]
UNIQUE:指定唯一索引
index_name：创建索引的名称
column_list：在上创建索引的列名列表，可基于多列
18.3 唯一或非唯一索引
唯一索引定义:定义索引的列中任何两行都没有重复值
非唯一索引定义: 单个关键字可以有多个与其关联的行
18.4 反向键索引
保存索引顺序的同时反转索引列的字节
18.5 位图索引
优点：
1、对于大批即时查询
2、相比其他索引技术占用空间减少
3、配置低的终端硬件上，性能也很好
18.6 其他索引
组合索引：在表内多列上创建，索引列不必和表中的列顺序一样
基于函数索引：使用的函数或表达式时间正在建立的索引的标识的一列 或多列，则创建基于函数索
18.7 删除所有
语法：DROP INDEX 索引名;
19.分区表
1、改善表的查询
2、表更容易管理
3、便于备份和恢复
4、提高数据安全性
19.1 分区表的分类
1、范围分区
2、列表分区
3、散列分区
4、复合分区
5、间隔分区
6、虚拟分区
PL/SQL编程
20.什么是PL/SQL
1.PL/SQL具有编程语言的特点，它能把一组SQL语句放在一个模块中，使其更具模块化程序的特点
2.PL/SQL可以采用过程性语言控制程序的结构，也就是说，我们可以在PL/SQL中增加逻辑结构，如判断，循环等程序结构
3.同其他语言一样，PL/SQL可以对程序中的错误进行走动处理，是程序能够在遇到错误时不会判断，即它的异常处理机制
4.PL/SQL程序块具有更好的可移植性，可以移植到另一个Oracle数据库中
5.PL/SQL程序减少了网络的交互，有助于提高程序性能
20.1常量和变量的声明
语法：
variable_name data_type[(size)][:= init_vlaue]
variable_name 表示变量名称
data_type 表示变量的SQL或PL/SQL数据类型
size 指定变量的范围
init_value 指定变量的初始值
21.PL/SQL数类型
Oracle使用的变量
| 类型 | 子类 | 说明 | 范围 |
|---|---|---|---|
| char | character nchar | 定唱字符串，名族语言字符串 | 0~32767，可选，默认为1 |
| varchar2 | varchar string nvarchar2 | 带符号整数，为整数计算优化性能 | 0~32767 |
| binary_integer | dec , double precision,integer,int,Numeric,real,small int | 小数，number的子类，高精度实数，整数，numer的子类型，整数，number的子类型，与number等价，与number等价，整数，比integer小 | |
| number(p,s) | 变长字符串 | 0~2147483647 | |
| long | 日期型 | 公元前4712年1月1日至公元前4712年21月31日 | |
| date | 布尔型 | ||
| boolean |
22.控制语句
PL/SQL程序可通过控制结构来控制命令执行的流程，标准的SQL没有流程控制的概念，而PL/SQL提供了丰富的流程控制语句
22.1 条件控制
语法：IF<布尔表达式> then
PL/SQL和SQL语句
END IF;
case语句语法：
case s_id
when value1 then
语句1;
when value2 then
语句2;
end;
22.2 循环控制
1.LOOP循环语法：
LLOP
要循环的语句;
EXIT WHEN<条件语句>–条件满足，退出循环语句
END LOOP;
2.WHILE循环语法
WHILE <布尔表达式> LOOP
要执行的语句;
END LOOP;
3.FOR循环语法
FOR 循环计数器 IN [REVERSE] 下限…上限LOOP
要执行的语句;
END LOOP;
23.异常处理
Oracle预定义异常
| 异常 | 说明 |
|---|---|
| ACCESS_INTO_NULL | 未定义对象 |
| CASE_NOT_FOUND | CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时 |
| COLLECTION_IS_NULL | 集合元素未初始化 |
| CURSER_ALREADY_OPEN | 游标已经打开 |
| DUP_VAL_ON_INDEX | 唯一索引对应的列上有重复的值 |
| INVALID_CURSOR | 在不合法的游标上进行操作 |
| INVALID_NUMBER | 内嵌的 SQL 语句不能将字符转换为数字 |
| NO_DATA_FOUND | 使用 select into 未返回行，或应用索引表未初始化的元素时 |
| TOO_MANY_ROWS | 执行 select into 时，结果集超过一行 |
| ZERO_DIVIDE | 除数为 0 |
| SUBSCRIPT_BEYOND_COUNT | 元素下标超过嵌套表或 VARRAY 的最大值 |
| SUBSCRIPT_OUTSIDE_LIMIT | 使用嵌套表或 VARRAY 时，将下标指定为负数 |
| VALUE_ERROR | 赋值时，变量长度不足以容纳实际数据 |
| LOGIN_DENIED | PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码 |
| NOT_LOGGED_ON | PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据 |
| PROGRAM_ERROR | PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包 |
| ROWTYPE_MISMATCH | 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容 |
| SELF_IS_NULL | 使用对象类型时，在 null 对象上调用对象方法 |
| STORAGE_ERROR | 运行 PL/SQL 时，超出内存空间 |
| SYS_INVALID_ID | 无效的 ROWID 字符串 |
| TIMEOUT_ON_RESOURCE | Oracle 在等待资源时超时 |
24.游标
24.1显示游标
1.显示游标的使用步骤
声明游标-打开游标-提取游标：否-空？是-关闭游标
显示游标语法：
CURSOR cursor_name[(parameter[,parameter]…)]
[RETURN return_type] IS select_statement;
打开游标语法：
open cursor_name[(parameter)];
提取游标语法：
fetch cursor_name INTO variables;
关闭游标语法：
close cursor_name;
2.显示游标属性
%found：只有在DML语句影响一行或多行时，%found属性才返回true
%notfound：%notfound属性与%found属性的作用正好相反
%rowcount：%rowcount属性返回DML语句影响的行数
%isopen：%isopen属性返回游标是否已打开
3.使用显示游标删除或更新
语法：
cursor cursor_name is
select_statement for update [of columns]
4.使用循环游标简化游标的读取
语法：
for record_index in cursor_name
loop
executable_statements
end loop;
25.存储过程
25.1 子程序的组成
1.声明部分
2.可执行部分
3.异常处理部分
25.2 存储过程用法
1.创建存储过程
语法：
create [or replace] procedure procedure_name[(parameter_list)]
(is|as)
[local_declarations]
begin
executable_statemters
[exception]
[exception_handlers]
end [procedure_name];
2.调用存储过程
语法:
exec[ute] procedure_name (parameters_list);
3.存储过程的参数模式
语法：
parameter_name[in | out | in out] datatype
[{:= | default} expression]
4.存储过程的访问权限
granrt execute on add_employee to a_oe
5.删除存储过程
语法：
drop procedure procedure_name
扫一扫
3154
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
