Oracle
本文从讲：
1、基础查询
2、函数
3、高级查询
4、数据类型
5、体系结构（DBA）
6、DDL
7、DML
8、约束
9、视图
10、索引
11、SQL 优化
12、序列、同义词
13、PL SQL
14、游标、函数
15、存储过程
16、触发器
17、事务（数据库系统概论）
18、用户管理
19、备份 恢复 SQLLoader
20、数据库设计范式
21、数据库设计工具
22、对象关系数据库系统
23、其他数据库
一、基础查询
1、入门语句
查看Windows服务：services.msc
查看端口号： tasklist|findstr "8080"，如果端口被占用：修改 C:\oracle\ora92\network\admin\ listener.ora 下的 HOST
普通用户连接： Conn scott/tiger
超级管理员连接： Conn “sys/sys as sysdba”
Disconnect; 断开连接
Save c:\1.txt 把 SQL 存到文件
Ed c:\1.txt 编辑 SQL 语句
@ c:\1.txt 运行 SQL 语句
Desc emp; 描述 Emp 结构
Select * from tab; 查看该用户下的所有对象
Show user; 显示当前用户
如果在 sys 用户下： 查询 Select * from emp; 会报错，原因： emp 是属于 scott，所以此时必须使用： select * from scott.emp;
/ 运行上一条语句
2、SQL简介
SQL 全名是结构化查询语言（Structured Query Language），是用于数据库中的标准数据查询语言， IBM 公司最早使用在其开发的数据库系统中。 1986 年 10 月，美国 ANSI 对 SQL进行规范后，以此作为关系式数据库管理系统的标准语言 (ANSI X3. 135-1986)， 1987 年得到国际标准组织的支持下成为国际标准。不过各种通行的数据库系统在其实践过程中都对 SQL 规范作了某些编改和扩充。所以，实际上不同数据库系统之间的 SQL 语言不能完全相互通用。
DML 语句（数据操作语言） Insert、 Update、 Delete、 Merge
DDL 语句（数据定义语言） Create、 Alter、 Drop、 Truncate
DCL 语句（数据控制语言） Grant、 Revoke
事务控制语句 Commit 、 Rollback、 Savepoint
3、简单的 Select 语句
4、使用算术表达式 + - / *
5、连接运算符 ||
6、使用字段别名 as
7、空值 is null
8、去除重复行 distinct
9、查询结果排序 order by asc(desc)
10、 比较运算符 > < (!= or <>) between and
11、in 操作 not in
12、模糊查询 like
% 表示零或多个字符_ 表示一个字符,对于特殊符号可使用 ESCAPE 标识符来查找
select * from emp where ename like '%*_%' escape '*'
上面的 escape 表示*后面的那个符号不当成特殊字符处理，就是查找普通的_符号
13、 逻辑运算符 or and not
二、函数
1)字符函数
首字母大写Initcap
整体小写Lower
整体大写Upper
连接Concat
截取Substr
长度Length
替换Replace
索引Instr
左填充Lpad
右填充Rpad
过滤首位空格Trim
2)数值函数
四舍五入Round
取模Mod
截取Trunc
两个时间间隔月份Months_between()
增加月份Add_months()
下一天Next_day()
每月最后一天Last_day
3)转换函数
To_char
To_number
To_date
4)通用函数
NVL()函数
NULLIF()函数，如果表达式 exp1 与 exp2 的值相等则返回 null，否则。返回 exp1 的值。
NVL2()函数
实例select empno, ename, sal, comm, nvl2(comm, sal+comm, sal) total from emp
COALESCE()函数，依次考察各参数表达式，遇到非 null 值即停止并返回该值。
实例select empno, ename, sal, comm, coalesce(sal+comm, sal, 0)总收入 from emp;
CASE 表达式
DECODE()函数
5)分组函数
COUNT，Avg， max， min， sum
三、高级查询
1、多表查询
1）笛卡尔集(Cross Join)
Select * from emp,dept;
2）等值连接(Equijoin)(Natural join..on)
select empno, ename, sal, emp.deptno, dname from emp, dept where emp.deptno = dept.deptno;
3）非等值连接(Non-Equijoin)
select ename,empno,grade from emp,salgrade where sal between losal and hisal;
4）自连接(Self join)
select e.empno,e.ename,m.empno,m.ename from emp e,emp m where e.mgr = m.empno;
5）左外联接（Left Outer Join ）
select s.sid,s.sname,s1.sid,s1.sname from student s,student1 s1 where s.sid=s1.sid(+);
select empno,ename,dname from emp left outer join dept on emp.deptno = dept.deptno;
6）右外联接（Right Outer Join）
select s.sid,s.sname,s1.sid,s1.sname from student s,student1 s1 where s.sid(+)=s1.sid;
select empno,ename,dname from emp right outer join dept on emp.deptno = dept.deptno;
7）满外联接（Full Outer Join）
select empno,ename,dname from emp full outer join dept on emp.deptno = dept.deptno;
2、集合操作
UNION：并集，所有的内容都查询，重复的显示一次
UNION ALL：并集，所有的内容都显示，包括重复的
INTERSECT：交集：只显示重复的
MINUS：差集：只显示对方没有的（跟顺序是有关系的）
3、高级查询
1）随机返回 5 条记录
Select * from (select ename,job from emp order by dbms_random.value()) where rownum<=5;
2）处理空值排序
select * from emp order by comm desc nulls last(first);
3）查询跳过表中的偶数行
select ename from (select row_number() over (order by ename) rn,ename;
from emp) x where mod(rn,2)=1;
4）查询员工信息与其中工资最高最低员工
select ename,sal,max(sal) over(), min(sal) over() from emp;
5）连续求和
select ename,sal,sum(sal) over(), sum(sal) over(order by ename) from emp;
sum(sal) over(order by ename)指的是连续求和.是以 ename 来排序的。若有两个这样的窗口函数，以后面的排序为主。
6）分部门连续求和
select deptno,sal ,sum(sal) over (partition by deptno order by ename) as s from emp;分部门连续求和
sum(sal) over (partition by deptno) 分部门求和
7）得到当前行上一行或者下一行的数据
select ename,sal,lead(sal) over(order by sal) aaa ,lag(sal) over(order by sal) bbb from emp;
8） 根据子串分组
Select to_char(hiredate,?yyyy?),avg(sal) from emp group by to_char(hiredate,?yyyy?);
9）确定一年内的天数
select add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y') from dual;
4、数据字典
1）查询某用户下所有表
select table_name from all_tables where owner='SCOTT';
2）查询 EMP 表中所有字段（列）
select * from all_tab_columns where table_name='EMP';
3）列出表的索引列
select * from sys.all_ind_columns where table_name='EMP';
select * from sys.all_ind_columns where
upper(table_name)='CAREUSERHAM';
4）列出表中约束
select * from all_constraints where table_name='EMP'
5）在 oracle 中描述数据字典视图
select table_name ,comments from dictionary where table_name like '%TABLE%';
四、Oracle 主要数据类型
number(4,2),
Char,
nchar,
varchar2,
nvarchar2,
number(),
date,
blob(binary 二进制流的大对象),
clob（文件大对象）
五、Oracle 体系结构(DBA)
1、数据库（Database）
一系列物理文件（数据文件，控制文件，联机日志等）的集合或与之对应的逻辑结构（表
空间，段等）被称为数据库.
物理存储结构:数据文件、重做日志文件、控制文件
Desc v$logfile;
Select member from v$logfile;
V$controlfile
V$datafile;
逻辑存储结构:表空间、段、区、块
2、数据库实例（Database Instance）
ORACLE内存结构和后台进程被成为数据库的实例
3、Oracle 实时应用集群（RAC, Real Application Clusters）
4、数据库服务名（ Database Service_Name）
5、网络服务名（Net Service Name）
6、监听器（Monitor）
六、DDL(改变表结构)
1、创建表
2、使用子查询创建表
create table myemp as select * from emp where deptno=10;
3、 添加字段
Alter table student add age number(5);
4、修改字段
Alter table student modify age number(10);
alter table table2 rename column result to result2;
5、删除字段
Alter table student drop column age;
6、清空表中数据
Truncate table student;
7、删除表
Drop table student;
8、 重命名表
Rename student to student1;
七、DML(改变数据结构）
1、 insert 语句
表间数据拷贝 insert into dept1(id, name) select deptno, dname from dept;
2、 update 语句
将编号为 7779 用户的工作换成编号为 7566 的雇员的工作和所属上级。
UPDATE myemp SET(job,mgr) = (SELECT job,mgr FROM myemp WHERE
empno=7566) WHERE empno=7779 ;
如果子查询中返回的是空，则目标字段也更新成 NULL.
3、 delete 语句
Delete from emp;
4、 merge 语句
create table test1(eid number(10), name varchar2(20),birth date,salary number(8,2)); insert into test1 values (1001, '张三', '20-5 月-70', 2300); insert into test1 values (1002, '李四', '16-4 月-73', 6600); create table test2(eid number(10), name varchar2(20),birth date,salary number(8,2)); merge into test2 using test1 on (test1.eid = test2.eid) when matched then update set name = test1.name, birth = test1.birth, salary = test1.salary when not matched then insert (eid, name, birth, salary) values (test1.eid, test1.name, test1.birth, test1.salary);
八、约束
约束就是指对插入数据的各种限制，例如：人员的姓名不能为空，人的年龄只能在 0~150 岁之间。约束可以对数据库中的数据进行保护。
约束可以在建表的时候直接声明，也可以为已建好的表添加约束。
1、NOT NULL：非空约束
name VARCHAR(30) NOT NULL
2、PRIMARY KEY：主键约束
pid NUMBER PRIMARY KEY ,
3、UNIQUE：唯一约束，值不能重复（空值除外）
tel VARCHAR(50)UNIQUE
4、CHECK：条件约束，插入的数据必须满足某些条件
age NUMBER CHECK(age BETWEEN 0 AND 150)
5、Foreign Key：外键
-- 建立约束： book_pid_fk，与 person 中的 pid 为主-外键关系
--CONSTRAINT book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
6、级联删除
pid NUMBER REFERENCES person(pid) ON DELETE CASCADE;
CONSTRAINT book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE;
7、删除约束
ALTER TABLE book DROP CONSTRAINT person_book_pid_fk ;
alter table student drop unique(tel);
8、启用约束
ALTER TABLE book enable CONSTRAINT person_book_pid_fk ;
9、禁用约束
ALTER TABLE book disable CONSTRAINT person_book_pid_fk ;
九、视图
1、普通视图
CREATE VIEW 视图名字(字段) AS 子查询
如果要创建一个同名的视图，则必须先将之前的视图删除掉，再进行创建：
DROP VIEW empv20 ;
2、WITH CHECK OPTION 保护视图的创建规则
CREATE OR REPLACE VIEW empv20 (empno,ename,sal,deptno)AS SELECT empno,ename,sal,deptno FROM emp WHERE deptno=20 WITH CHECK OPTION CONSTRAINT empv20_ck;
再执行更新操作：
update empv20 SET deptno=30 where empno=7369 ; 此处更新的是部门编号，失败
|- 之前是按照部门编号建立的视图，所以不能修改部门编号
update empv20 SET ename='tom' where empno=7369 ; 可以更新，更新的是名字，成功
3、WITH READ ONLY（只读，不可修改），视图最好不要轻易的修改
CREATE OR REPLACE VIEW empv20 (empno,ename,sal,deptno) AS SELECT empno,ename,sal,deptno FROM emp WHERE deptno=20 WITH READ ONLY;
现在任意的字段都不可更改，所以现在的视图是只读的。
如果视图的基表有多行查询（比如： group by,distinct）那么该视图也是只读的
4、查看视图
Select text from user_views;查看视图的创建语句
十、索引
select * from user_indexes 查询现有的索引
select * from user_ind_columns 可获知索引建立在那些字段上
1、优缺点：
建立索引的优点
1.大大加快数据的检索速度;
2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
3.加速表和表之间的连接;
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
索引的缺点
1.索引需要占物理空间。
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的
维护速度。
2、创建原则
创建索引：创建索引一般有以下两个目的：维护被索引列的唯一性和提供快速访问表中数据
的策略。
--在 select 操作占大部分的表上创建索引；
--在 where 子句中出现最频繁的列上创建索引；
--在选择性高的列上创建索引（补充索引选择性，最高是 1， eg： primary key）
--复合索引的主列应该是最有选择性的和 where 限定条件最常用的列，并以此类推第二列……。
--小于 5M 的表，最好不要使用索引来查询，表越小，越适合用全表扫描。
3、使用索引的原则
1）查询结果是所有数据行的 5%以下时，使用 index 查询效果最好；
2）where 条件中经常用到表的多列时，使用复合索引效果会好于几个单列索引。因为当 sql语句所查询的列，全部都出现在复合索引中时，此时由于 Oracle 只需要查询索引块即可获得所有数据，当然比使用多个单列索引要快得多；
3）索引利于 select，但对经常 insert， delte 尤其 update 的表，会降低效率。
eg：试比较下面两条 SQL 语句(emp 表的 deptno 列上建有 ununique index)：
语句 A：
SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp);
语句 B：
SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);
这两条查询语句实现的结果是相同的，但是执行语句 A 的时候， ORACLE 会对整个 emp 表进行扫描，没有使用建立在 emp 表上的 deptno 索引，执行语句 B 的时候，由于在子查询中使用了联合查询， ORACLE 只是对 emp 表进行的部分数据扫描，并利用了 deptno 列的索引，所以语句 B 的效率要比语句 A 的效率高。
4）where 子句中的这个字段，必须是复合索引的第一个字段；
eg：一个索引是按 f1, f2, f3 的次序建立的，若 where 子句是 f2 = : var2, 则因为 f2 不是索引的第 1 个字段，无法使用该索引。
5）where 子句中的这个字段，不应该参与任何形式的计算：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
6）应尽量熟悉各种操作符对 Oracle 是否使用索引的影响
以下这些操作会显式（ explicitly）地阻止 Oracle 使用索引： is null ; is not null ; not in; !=; like ;
numeric_col+0;date_col+0; char_col||' '; to_char; to_number， to_date 等。
Eg：
Select jobid from mytabs where isReq='0' and to_date (updatedate) >= to_Date ( '2001-7-18',
'YYYY-MM-DD')；
--updatedate 列的索引也不会生效。
4、创建索引
create index abc on student(sid,sname);
create index abc1 on student(sname,sid);
这两种索引方式是不一样的
索引 abc 对 Select * from student where sid=1; 这样的查询语句更有效
索引 abc1 对 Select * from student where sname=?louis?; 这样的查询语句更有效
因此建立索引的时候，字段的组合顺序是非常重要的。一般情况下，需要经常访问的字段
放在组合字段的前面
5、索引的存储
索引和表都是独立存在的。在为索引指定表空间的时候，不要将被索引的表和索引指向同
一个表空间，这样可以避免产生 IO 冲突。使 Oracle 能够并行访问存放在不同硬盘中的索
引数据和表数据，更好的提高查询速度。
6、删除索引
drop index PK_DEPT1;
7、索引类型
1）B 树索引（B-Tree Index）
创建索引的默认类型，结构是一颗树，采用的是平衡 B 树算法：
? 右子树节点的键值大于等于父节点的键值
? 左子树节点的键值小于等于父节点的键值
比如有数据:100,101,102,103,104,105,106
2）位图索引(BitMap Index)
如果表中的某些字段取值范围比较小，比如职员性别、分数列 ABC 级等。只有两个值。
这样的字段如果建 B 树索引没有意义，不能提高检索速度。这时我们推荐用位图索引
Create BitMap Index student on(sex);
8、管理索引
1） 先插入数据后创建索引
向表中插入大量数据之前最好不要先创建索引，因为如果先建立索引。那么在插入每行
数据的时候都要更改索引。这样会大大降低插入数据的速度。
2） 设置合理的索引列顺序
3） 限制每个表索引的数量
4） 删除不必要的索引
5） 为每个索引指定表空间
6） 经常做 insert， delete 尤其是 update 的表进行碎片整理，以降低索引碎片，提高效率。
最好定期 exp/imp 表数据，整理数据，降低碎片（缺点：要停应用，以保持数据一致性，不实用）；
有索引的最好定期 rebuild 索引（rebuild期间只允许表的 select 操作，可在数据库较空闲时间提交）
9、索引问题
1： 针对一个表的查询语句能否会用到两个索引?
2：如果能用到，那么其实现原理是怎样的？
3：效率如何？其代价如何，比如额外开销等。
十一、SQL 优化
oracle 数据库常用的两种优化器： RBO（rule-based-optimizer）和 CBO(cost-based-optimizer)。
目前更多地采用 CBO(cost-based-optimizer)基于开销的优化器。在 CBO 方式下， Oracle 会根据表及索引的状态信息来选择计划；
在 RBO 方式下， Oracle 会根据自己内部设置的一些规则来决定选择计划，
例如 oracle 会根据以下优先级来选择执行计划（越靠前， rank 越低，越快）：
1、尽量少用 IN 操作符
基本上所有的 IN 操作符都可以用 EXISTS 代替， 在选择 IN 或 EXIST 操作时，要根据主子
表数据量大小来具体考虑
2、尽量用 NOT EXISTS 或者外连接替代 NOT IN 操作符
因为 NOT IN 不能应用表的索引
3、尽量不用“<>”或者“!=”操作符
不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 比如： a<>0改为 a>0 or a<0
4、在设计表时， 把索引列设置为 NOT NULL
判断字段是否为空一般是不会应用索引的，因为 B 树索引是不索引空值的。
5、尽量不用通配符“%”或者“_”作为查询字符串的第一个字符
当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。比如用 T 表中
Column1 LIKE ?%5400%? 这个条件会产生全表扫描，如果改成 Column1 ?X5400%? OR
Column1 LIKE ?B5400%? 则会利用 Column1 的索引进行两个范围的查询，性能肯定大大提高。
6、Where 子句中避免在索引列上使用计算
如果索引不是基于函数的，那么当在 Where 子句中对索引列使用函数时，索引不再起作用。
因此 Where 子句中避免在索引列上使用计算。
比如：
substr(no,1,4)=?5400?，优化处理： no like ?5400%?
trunc(hiredate)=trunc(sysdate) ， 优 化 处 理 ： hiredate >=trunc(sysdate) and hiredate <trunc(sysdate+1)
7、用“>=”替代“>”
大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有
的情况下可以对它进行优化，如一个表有 100 万记录，一个数值型字段 A， 30 万记录的 A=0，30 万记录的 A=1，39 万记录的 A=2，1 万记录的 A=3。
那么执行 A>2 与A>=3 的效果就有很大的区别了，因为 A>2 时 ORACLE 会先找出为 2 的记录索引再进行比较，而 A>=3 时 ORACLE 则直接找到=3 的记录索引
8、利用 SGA 共享池，避开 parse 阶段
同一功能同一性能不同写法 SQL 的影响
如一个 SQL 在 A 程序员写的为Select * from zl_yhjbqk
B 程序员写的为Select * from dlyx.zl_yhjbqk（带表所有者的前缀）
C 程序员写的为Select * from DLYX.ZLYHJBQK（大写表名）
D 程序员写的为Select * from DLYX.ZLYHJBQK（中间多了空格）
以上四个 SQL 在 ORACLE 分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE 共享内存 SGA 的原理，可以得出 ORACLE 对每个 SQL 都会对其进行一次分析，并且占用共享内存，如果将 SQL 的字符串及格式写得完全相同则 ORACLE 只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析 SQL 的时间，而且可以减少共享内存重复的信息， ORACLE 也可以准确统计 SQL 的执行频率。
不同区域出现的相同的 Sql 语句要保证查询字符完全相同， 建议经常使用变量来代替常量，以尽量使用重复 sql 代码， 以利用 SGA 共享池， 避开 parse 阶段，防止相同的 Sql 语句被多次分析，提高执行速度。因此使用存储过程，是一种很有效的提高 share pool 共享率，跳过 parse 阶段，提高效率的办法。
9、WHERE 后面的条件顺序要求
WHERE 后面的条件， 表连接语句写在最前， 可以过滤掉最大数量记录的条件居后。
比如：
Select * from zl_yhjbqk where dy_dj = '1KV 以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV 以下'
以上两个 SQL 中 dy_dj（电压等级）及 xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%，而 xh_bz=1 的比率只为 0.5%，在进行第一条 SQL 的时候 99%条记录都进行 dy_dj 及 xh_bz的比较，而在进行第二条 SQL 的时候 0.5%条记录都进行 dy_dj 及 xh_bz 的比较，以此可以得出第二条 SQL 的 CPU 占用率明显比第一条低。
10、使用表的别名，并将之作为每列的前缀
当在 Sql 语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间
11、进行了显式或隐式的运算的字段不能进行索引
比如：
ss_df+20>50，优化处理： ss_df>30
?X?||hbs_bh>?X5400021452?，优化处理： hbs_bh>?5400021542?
sk_rq+5=sysdate，优化处理： sk_rq=sysdate-5
hbs_bh=5401002554，优化处理： hbs_bh=? 5401002554?，
注：此条件对 hbs_bh 进行隐式的 to_number 转换，因为 hbs_bh 字段是字符型。
12、用 UNION ALL 代替 UNION
UNION 是最常用的集操作，使多个记录集联结成为单个集，对返回的数据行有唯一性要求，
所以 oracle 就需要进行 SORT UNIQUE 操作（与使用 distinct 时操作类似），如果结果集又比较大，则操作会比较慢；
UNION ALL 操作不排除重复记录行，所以会快很多，如果数据本身重复行存在可能性较小时，用 union all 会比用 union 效率高很多！
13、其他操作
尽量使用 packages： Packages 在第一次调用时能将整个包 load 进内存，对提高性能有帮助。
尽量使用 cached sequences 来生成 primary key ：提高主键生成速度和使用性能。
很好地利用空间：如用 VARCHAR2 数据类型代替 CHAR 等
使用 Sql 优化工具： sqlexpert； toad； explain-table； PL/SQL； OEM
14、通过改变 oracle 的 SGA 的大小
SGA： 数据库的系统全局区。
SGA 主要由三部分构成：共享池、数据缓冲区、日志缓冲区
1、 共享池又由两部分构成：共享 SQL 区和数据字典缓冲区。共享 SQL 区专门存放用户 SQL 命令， oracle 使用最近最少使用等优先级算法来更新覆盖；数据字典缓冲区（library cache）存放数据库运行的动态信息。数据库运行一段时间后，DBA 需要查看这些内存区域的命中率以从数据库角度对数据库性能调优。
通过执行下述语句查看：
select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache;
--查看共享 SQL 区的重用率，最好在 90％以上，否则需要增加共享池的大小。
select (sum(gets - getmisses - usage - fixED)) / sum(gets) "Row Cache" from
v$rowcache;
--查看数据字典缓冲区的命中率，最好在 90％以上，否则需要增加共享池的大小。
2、 数据缓冲区：存放 sql 运行结果抓取到的 data block；
SELECT name, value FROM v$sysstat WHERE name IN ('db block gets',
'consistent gets','physical reads');
--查看数据库数据缓冲区的使用情况。查询出来的结果可以计算出来数据缓冲区的使用命中率＝1 - ( physical reads / (db block gets + consistent gets) )。命中率应该在 90％以上，否则需要增加数据缓冲区的大小。
3、 日志缓冲区：存放数据库运行生成的日志。
select name,value from v$sysstat where name in ('redo entries','redo log spacerequests');
--查看日志缓冲区的使用情况。查询出的结果可以计算出日志缓冲区的申请失败率：申请失败率＝requests/entries，申请失败率应该接近于 0，否则说明日志缓冲区开设太小，需要增加 ORACLE 数据库的日志缓冲区。
十二、序列、同义词
1、创建序列
Create sequence myseq
Start with 1
Increment by 1
Order
cache 20
Nocycle;
2、NextVal,CurrVal
Select myseq.nextval from dual;
Select myseq.currval from dual;
(必须先有 nextval，才能有 currval)查询完之后就已经自增 1 了
Insert into table1 values(myseq.nextval) 这时候已经是 2 了
3、Cycle， Cache
而用了 nocycle，就可以确保当该序列用于多张表的时候， ID 是唯一的用 cycle 时，用法如下：
create sequence myseq2 start with 1 increment by 1 cycle maxvalue 3 nocache ;
这样到 3 之后，要会重新从 1 开始如果指定 CACHE 值， ORACLE 就可以预先在内存里面放置一些 sequence，这样存取的快些。
cache 里面的取完后， oracle 自动再取一组到 cache。
使用 cache 或许会跳号， 比如数据库突然不正常 down 掉（shutdown abort),cache中的 sequence就会丢失。
所以可以在 create sequence的时候用 nocache防止这种情况不能改变当前值，但是可以改变增量
Alter sequence myseq increment by 3;
4、同义词
在任何一个用户下，都可以直接访问 dual,而不需要加上前缀的用户名如:scott.emp
Select * from dual;
为什么？因为同义词的存在Dual 其实是 sys 用户下的一张表
select table_name from user_tables where lower(table_name) = 'dual';
作用：
很方便的操作不同用户下的对象能使两个应用程序使用不同的名字指向同一张表使用不同的用户指向同一张表的。
Create synonym dept for soctt.dept;(这样创建的同义词是私有的，只有创建者才能用)
Drop synonym dept;
Create public synonym dept for soctt.dept;（这样创建的同义词才是公有的）
Drop public synonym dept;
十三、PL SQL
1、PL/SQL 块
PL/SQL 块是在 SQL 语言之上发展起来的一种应用，可以集中的处理各种复杂的 SQL 操作。
组成：
DECLARE 声明部分：BEGIN 编写主题 EXCEPTION 捕获异常 END ; /
看一个简单的 PL/SQL 块
DECLARE i NUMBER; BEGIN i := 30; DBMS_OUTPUT.put_line('I 的内容为： ' || i); END; /
此时，直接执行程序即可。
执行之后发现没有任何的输出。因为 Oracle 在系统设置中默认设置了输出不显示，如果要显示的话，输入以下命令：
set serveroutput on
DECLARE i NUMBER; BEGIN i := 1 / 0; EXCEPTION when ZERO_DIVIDE then dbms_output.put_line('error'); END; /
PL/SQL 块还可以接收用户的输入信息，例如：现在要求用户输入一个雇员编号，之后根
据输入的内容进行查询，查询雇员的姓名。
? 用户的输入信息使用“&”完成。
DECLARE eno NUMBER; en VARCHAR2(30); BEGIN -- 输入的信息保存在 eno 里 eno := &no; -- 之后根据 eno 的值，对数据库进行查询操作 SELECT ename INTO en FROM emp WHERE empno = eno; DBMS_OUTPUT.put_line('编号为： ' || eno || '雇员的姓名为： ' || en); EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.put_line('没有此雇员'); END; /
在以上的查询中再进一步：可以根据雇员的编号查出姓名及其领导的姓名和所在的部门，进行显示。
DECLARE eno emp.empno%TYPE; en emp.ename%TYPE; mn emp.ename%TYPE; dn dept.dname%TYPE; dept dept %rowtype; BEGIN -- 输入的信息保存在 eno 里 eno := &no; -- 之后根据 eno 的值，对数据库进行查询操作 SELECT e.ename, m.ename, d.dname INTO en, mn, dn FROM emp e, dept d, emp m WHERE e.empno = 7369 AND e.mgr = m.empno AND e.deptno = d.deptno; DBMS_OUTPUT.put_line('编号为： ' || eno || '雇员的姓名为： ' || en); DBMS_OUTPUT.put_line('编号为： ' || eno || '雇员的上级姓名为： ' || mn); DBMS_OUTPUT.put_line('编号为： ' || eno || '雇员所在的部门： ' || dn); DBMS_OUTPUT.put_line(dept.deptno); EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.put_line('没有此雇员'); END; /
说明：
? emp.empno%TYPE ;：表示以 emp 表中的 empno 字段的类型定义变量
? e.ename,m.ename,d.dname INTO en,mn,dn：一次可以同时放进去多个值
dept dept %rowtype ; 表示 dept 是一行数据，类似于 Hibernate 的 PO
2、Loop 循环（do…while）
PL/SQL 之中也包含了：循环、分支等条件控制语句Loop 循环(do…while)
格式：
LOOP
循环的语句 ;
EXIT WHEN 终止条件 ;
循环条件必须更改 ;
END LOOP ;
循环输出 1~10。
DECLARE cou NUMBER ; BEGIN -- 必须给一个初始值 cou := 1 ; LOOP DBMS_OUTPUT.put_line('cou = '||cou) ; EXIT WHEN cou>10 ; cou := cou + 1 ; END LOOP ; END ; /
此循环是先执行一次之后再进行判断
3、 while 循环
格式：
while(判断循环的条件) loop
循环的语句 ;
循环条件的改变 ;
End loop ;
使用此语句修改上面的程序：
DECLARE cou NUMBER; BEGIN -- 必须给一个初始值 cou := 1; WHILE (cou < 10) LOOP DBMS_OUTPUT.put_line('cou = ' || cou); cou := cou + 1; END LOOP; END; /
此语句，是先判断，之后如果条件满足则执行，与 while 循环类似。
4、 for 循环
格式：
FOR 变量名称 in 变量的初始值..结束值 LOOP
循环语句 ;
END LOOP ;
DECLARE cou NUMBER; BEGIN FOR cou IN 1 .. 10 LOOP DBMS_OUTPUT.put_line('cou = ' || cou); END LOOP; END; /
5、 IF 语句
条件判断
格式：
IF 条件 THEN
满足条件时，执行此语句
END IF ;
DECLARE cou NUMBER; BEGIN cou := 11; IF cou > 10 THEN DBMS_OUTPUT.put_line('cou = ' || cou); END IF; END; /
6、 IF…ELSE 语句
如果 IF 满足了，则执行，否则执行 ELSE
DECLARE cou NUMBER; BEGIN cou := 1; IF cou > 10 THEN DBMS_OUTPUT.put_line('cou = ' || cou); ELSE DBMS_OUTPUT.put_line('条件不满足'); END IF; END; /
7、IF…ELSIF…ELSE 语句
DECLARE cou NUMBER; BEGIN cou := 1; IF cou > 10 THEN DBMS_OUTPUT.put_line('cou = ' || cou); ELSIF cou < 5 THEN DBMS_OUTPUT.put_line('值小于 5'); ELSE DBMS_OUTPUT.put_line('条件不满足'); END IF; END; /
8、GOTO 语句
无条件跳转语句
DECLARE eno emp.empno%TYPE; sal emp.sal%TYPE; BEGIN eno := &en; SELECT sal INTO sal FROM emp WHERE empno = eno; IF sal > 3500 THEN goto po1; ELSIF sal > 2000 THEN goto po2; ELSE goto po3; END IF; <<po1>> DBMS_OUTPUT.put_line('高工资。。。 '); <<po2>> DBMS_OUTPUT.put_line('中等工资。。 '); <<po3>> DBMS_OUTPUT.put_line('底工资。。。 '); END; /
十四、游标、函数
1、游标
游标是一种 PL/SQL 控制结构；可以对 SQL 语句的处理进行显示控制，便于对表的行数据逐条进行处理。
游标并不是一个数据库对象，只是存留在内存中
操作步骤：
1）声明游标
2）打开游标
3）取出结果，此时的结果取出的是一行数据
4）关闭游标
到底那种类型可以把一行的数据都装进来？
此时使用 ROWTYPE 类型，此类型表示可以把一行的数据都装进来。
例如：查询雇员编号为 7369 的信息（肯定是一行信息）。
DECLARE eno emp.empno%TYPE; empInfo emp%ROWTYPE; BEGIN eno := &en; SELECT * INTO empInfo FROM emp WHERE empno = eno; DBMS_OUTPUT.put_line('雇员编号： ' || empInfo.empno); DBMS_OUTPUT.put_line('雇员姓名： ' || empInfo.ename); END; /
使用 for 循环操作游标（比较常用）
DECLARE -- 声明游标 CURSOR mycur IS SELECT * FROM emp where empno = -1; empInfo emp%ROWTYPE; cou NUMBER; BEGIN -- 游标操作使用循环，但是在操作之前必须先将游标打开 FOR empInfo IN mycur LOOP cou := mycur%ROWCOUNT; DBMS_OUTPUT.put_line(cou || '雇员编号： ' || empInfo.empno); DBMS_OUTPUT.put_line(cou || '雇员姓名： ' || empInfo.ename); END LOOP; END; /
编写第一个游标，输出全部的信息。
DECLARE -- 声明游标 CURSOR mycur IS SELECT * FROM emp; -- List (EmpPo) empInfo emp%ROWTYPE; BEGIN -- 游标操作使用循环，但是在操作之前必须先将游标打开 OPEN mycur; -- 使游标向下一行 FETCH mycur INTO empInfo; -- 判断此行是否有数据被发现 WHILE (mycur%FOUND) LOOP DBMS_OUTPUT.put_line('雇员编号： ' || empInfo.empno); DBMS_OUTPUT.put_line('雇员姓名： ' || empInfo.ename); -- 修改游标，继续向下 FETCH mycur INTO empInfo; END LOOP; END; /
也可以使用另外一种方式循环游标： LOOP…END LOOP;
DECLARE -- 声明游标 CURSOR mycur IS SELECT * FROM emp; empInfo emp%ROWTYPE; BEGIN -- 游标操作使用循环，但是在操作之前必须先将游标打开 OPEN mycur; LOOP -- 使游标向下一行 FETCH mycur INTO empInfo; EXIT WHEN mycur%NOTFOUND; DBMS_OUTPUT.put_line('雇员编号： ' || empInfo.empno); DBMS_OUTPUT.put_line('雇员姓名： ' || empInfo.ename); END LOOP; END; /
注意 1：
在打开游标之前最好先判断游标是否已经是打开的。
通过 ISOPEN 判断，格式：游标%ISOPEN
IF mycur%ISOPEN THEN null ; ELSE OPEN mycur ; END IF ;
注意 2：
可以使用 ROWCOUNT 对游标所操作的行数进行记录。
DECLARE -- 声明游标 CURSOR mycur IS SELECT * FROM emp; empInfo emp%ROWTYPE; cou NUMBER; BEGIN -- 游标操作使用循环，但是在操作之前必须先将游标打开 IF mycur%ISOPEN THEN null; ELSE OPEN mycur; END IF; LOOP -- 使游标向下一行 FETCH mycur INTO empInfo; EXIT WHEN mycur%NOTFOUND; cou := mycur%ROWCOUNT; DBMS_OUTPUT.put_line(cou || '雇员编号： ' || empInfo.empno); DBMS_OUTPUT.put_line(cou || '雇员姓名： ' || empInfo.ename); END LOOP; END; /
2、函数
函数就是一个有返回值的过程。
定义一个函数：此函数可以根据雇员的编号查询出雇员的年薪
CREATE OR REPLACE FUNCTION myfun(eno emp.empno%TYPE) RETURN NUMBER AS rsal NUMBER; BEGIN SELECT (sal + nvl(comm, 0)) * 12 INTO rsal FROM emp WHERE empno = eno; RETURN rsal; END; / 直接写 SQL 语句，调用此函数： SELECT myfun(7369) FROM dual ;
15、存储过程
与过程相比， 存储过程是存在数据库中的一个对象
如果编译错误。可以用 show errors or show errors procedure myproc
现在定义一个简单的过程，就是打印一个数字
CREATE OR REPLACE PROCEDURE myproc AS i NUMBER; BEGIN i := 100; DBMS_OUTPUT.put_line('i = ' || i); END; / 执行过程： exec 过程名字
下面编写一个过程，要求，可以传入部门的编号，部门的名称，部门的位置，之后调用此
过程就可以完成部门的增加操作。
CREATE OR REPLACE PROCEDURE myproc(dno dept.deptno%TYPE, name dept.dname%TYPE, dl dept.loc%TYPE) AS cou NUMBER; BEGIN -- 判断插入的部门编号是否存在，如果存在则不能插入 SELECT COUNT(deptno) INTO cou FROM dept WHERE deptno = dno; IF cou = 0 THEN -- 可以增加新的部门 INSERT INTO dept (deptno, dname, loc) VALUES (dno, name, dl); DBMS_OUTPUT.put_line('部门插入成功！ '); ELSE DBMS_OUTPUT.put_line('部门已存在，无法插入！ '); END IF; END; /
过程的参数类型：
? IN：值传递，默认的
? IN OUT：带值进，带值出
? OUT：不带值进，带值出
IN OUT 类型：
CREATE OR REPLACE PROCEDURE myproc(dno IN OUT dept.deptno%TYPE, name dept.dname%TYPE, dl dept.loc%TYPE) AS cou NUMBER; BEGIN -- 判断插入的部门编号是否存在，如果存在则不能插入 SELECT COUNT(deptno) INTO cou FROM dept WHERE deptno = dno; IF cou = 0 THEN -- 可以增加新的部门 INSERT INTO dept (deptno, dname, loc) VALUES (dno, name, dl); DBMS_OUTPUT.put_line('部门插入成功！ '); -- 修改 dno 的值 dno := 1; ELSE DBMS_OUTPUT.put_line('部门已存在，无法插入！ '); dno := -1; END IF; END; / 编写 PL/SQL 块验证过程： DECLARE deptno dept.deptno%TYPE; BEGIN deptno := 12; myproc(deptno, '开发', '南京'); DBMS_OUTPUT.put_line(deptno); END; /
OUT 类型不带任何值进，只把值带出来。
CREATE OR REPLACE PROCEDURE myproc(dno OUT dept.deptno%TYPE) AS I number; BEGIN I := dno; END; / 执行上面的存储过程 DECLARE deptno dept.deptno%TYPE; BEGIN deptno := 12 myproc(deptno); DBMS_OUTPUT.put_line(deptno); END; /
16、触发器
存放在数据库中，并被隐含执行的存储过程。在 Oracle8i 之前，只允许给予表或者视图的
的 DML 的操作，而从 Oracle8i 开始，不仅可以支持 DML 触发器，也允许给予系统事件和
DDL 的操作
1、语句触发器
1）Before 语句触发器
例如：禁止工作人员在休息日改变雇员信息
create or replace trigger tr_src_emp before insert or update or delete on emp begin if to_char(sysdate, 'DY', 'nls_date_language=AMERICAN') in ('SAT', 'SUN') then raise_application_error(-20001, 'can?t modify user information in weekend'); end if; end; /
2）使用条件谓语
create or replace trigger tr_src_emp before insert or update or delete on emp begin if to_char(sysdate, 'DY') in ('星期六', '星期天') then case when inserting then raise_application_error(-20001, 'fail to insert'); when updating then raise_application_error(-20001, 'fail to update'); when deleting then raise_application_error(-20001, 'fail to delete'); end case; end if; end; /
3）after 语句触发器
例如：为了统计在 EMP 表上的增、删、改的次数。先建一张表
Create table audit_table(
Name varchar2(20),ins int,upd int,del int,starttime date,endtime date);
然后建立触发器
Create or replace trigger tr_audit_emp After insert or update or delete on emp Declare v_temp int; Begin Select count(*) into v_temp from audit_table Where name = 'EMP'; If v_temp = 0 then Insert into audit_table values ('EMP', 0, 0, 0, sysdate, null); End if; Case When inserting then Update audit_table set ins = ins + 1, endtime = sysdate where name = 'EMP'; When updating then Update audit_table set upd = upd + 1, endtime = sysdate where name = 'EMP'; When deleting then Update audit_table set del = del + 1, endtime = sysdate where name = 'EMP'; End case; End; /
4）Bofre 行触发器
例如：确保员工工资不能低于原有工资
Create or replace trigger tr_emp_sal before update of sal on emp for each row begin if :new.sal < :old.sal then raise_application_error(-20010, 'sal should not be less'); end if; end; /
5）after 行触发器
例如：统计员工工资变化
Create table audit_emp_change( Name varchar2(10), Oldsal number(6,2), Newsal number(6,2), Time date); Create or replace trigger tr_sal_sal after update of sal on emp for each row declare v_temp int; begin select count(*) into v_temp from audit_emp_change where name = :old.ename; if v_temp = 0 then insert into audit_emp_change values (:old.ename, :old.sal, :new.sal, sysdate); else update audit_emp_change set oldsal = :old.sal, newsal = :new.sal, time = sysdate where name = :old.ename; end if; end; /
6）限制行触发器
Create or replace trigger tr_sal_sal after update of sal on emp for each row when (old.job = 'SALESMAN') declare v_temp int; begin select count(*) into v_temp from audit_emp_change where name = :old.ename; if v_temp = 0 then insert into audit_emp_change values (:old.ename, :old.sal, :new.sal, sysdate); else update audit_emp_change set oldsal = :old.sal, newsal = :new.sal, time = sysdate where name = :old.ename; end if; end; /
3、注意事项
编写 DML 触发器的时，触发器代码不能从触发器所对应的基表中读取数据。 例如：如果要基于 EMP 表建立触发器。那么该触发器的执行代码不能包含对 EMP 表的查 询操作。
Create or replace trigger tr_emp_sal Before update of sal on emp For each row declare Maxsal number(6, 2); Begin Select max(sal) into maxsal from emp; If :new.sal > maxsal then Raise_application_error(-21000, ? error ?); End if; End; /
创建的时候不会报错。但是一旦执行就报错了 update emp set sal=sal*1.1 where deptno=30
4、触发器的主要用途
1）控制数据安全
例如：在非工作时间不能对 EMP 表做操作
create or replace trigger tr_emp_time before insert or update or delete on emp begin if to_char(sysdate, 'HH24') not between '9' AND '17' THEN raise_application_error(-20101, 'not work time'); end if; end; /
2）实现数据统计
例如：上面提到的记载员工的工资变化等
3）实现数据的完整性
例如：如果只是限制员工的工资不能低于 800，可以选用 check 约束
Alter table emp add constraint ck_sal check(sal>=800);
4）但如果是限定新工资不能低于其原来工资，也不能高于 20%。则通过约束是无法 实现的。
这时可通过触发器来实现
Create or replace trigger tr_check_sal Before update of sal on emp For each row When (new.sal < old.sal or new.sal > 1.2 * old.sal) Begin Raise_application_error(-20931, ? ddd ?); End; /
5、系统事件触发器
17、事务（数据库系统概论）
在 oracle 中每个连接都会产生一个 Session,一个 session 对数据库的修改，不会立刻反映到 数据库的真实数据上，是允许回滚的。只有当提交了，才变成持久数据了
可能出现死锁的情况
原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation ）
持久性（Durability ）
原子性：只一个事务中，包含若干个数据操作，这些操作是一个整体，要么一起完成，要么一起不完成，不能只完成其中的一部分。比
如你去银行转帐，从一个账户转帐到另一个账户，这是一个完整的事务，包括两个操作，从你第一个账户读数，增加到第二个账户，并
减去第一个账户中的钱，如果这些操作有一个失败了，整个事务都必须还原成最开始的状态。
一致性：是指数据库从一个完整的状态跳到另一个完整的状态，是用于保护数据库的完整性的。比如你修改数据库的某个外键值，如果
没有和相应的主键对应，就违反了数据库的一致性。另外，还有读一致性，如：你刚写入一个数到数据库中，但还没有提交，这时候有
人要读这个数，就涉及完整性问题，要保证读取的数据在整个数据库中是处于和其他数据一致的一个状态。
18、用户管理
注意： 如果要创建用户只能在管理员下完成： ·
超级管理员：sys/sys ·
普通管理员：system/system
用户： scott/tiger
1、创建用户
CREATE USERS 用户名 IDENTIFIED BY 密码。 |- create user test identified by admin |- 用户名：test |- 密码：admin
2、删除用户
Drop user test;
如果该用户下面已经存在表等一些数据库对象。则必须用级联删除
Drop user test cascade;
3、创建 Session 权限
一般在数据库中，一个用户的连接称为建立一个 session，如果一个新的用户想访问数据库，
则必须授予创建 session 的权限 —— 用户授权
GRANT 权限 TO 用户。
给 test 用户以创建 session 的权限：GRANT create session TO test ;
以上用户虽然可以连接了，但是不能有任何的操作(比如创建表)
4、用户角色
角色是权限的集合 在 Oracle 中提供了两个角色，可以直接将这两个角色给用户：
·CONNECT 角色：
·RESOURCE 角色：
现在将这两个角色给 test 用户
GRANT CONNECT,RESOURCE TO test ;
这时有创建表的权限了，但是还是不能创建表。主要是因为该用户还没有对表空间操作的 权限
Q:ORA-01950： no privileges on Tablespace 'USERS'”
A: GRANT UNLIMITED TABLESPACE TO youruser;
１．CONNECT, RESOURCE, DBA
这些预定义角色主要是为了向后兼容。其主要是用于数据库管理。oracle 建议 用户自己设计数据库管理和安全的权限规划，而不要简单的使用这些预定角色。 将来的版本中这些角色可能不会作为预定义角色。
２．DELETE_CATALOG_ROLE， EXECUTE_CATALOG_ROLE， SELECT_CATALOG_ROLE
这些角色主要用于访问数据字典视图和包。
３．EXP_FULL_DATABASE， IMP_FULL_DATABASE
这两个角色用于数据导入导出工具的使用。
5、锁住一个用户
·ALTER USER 用户名 ACCOUNT LOCK|UNLOCK |- ALTER USER test ACCOUNT LOCK ; |- ALTER USER test ACCOUNT UNLOCK ;
6、密码失效
提示用户第一次连接的时候需要修改密码，让用户的密码到期
|- ALTER USER test PASSWORD expire ;
7、对象授权
以上的所有操作只针对于 test 用户。如果 test 要访问其他用户呢？例如，访问 emp 表
此时如果要想让其可以访问，则必须把 scott 用户下的 emp 表的查询权限给 test。
GRANT 权限（select、update、insert、delete） ON schema.table TO 用户
|- GRANT select ON scott.emp TO test ; |- Grant all on scott.emp to test; --将表相关的所有权限付给 test |- Grant update(ename) on emp to test; 可以控制到列(还有 insert)
8、权限回收
REVOKE 权限 ON schema.table FROM 用户
|- REVOKE select ON scott.emp FROM test ;
9、查看权限
select * from user_sys_privs;
10、权限传递
Grant create session to test with admin option;(可以就可以实现权限传递)
Q：如果权限 sys->test->test1 ，这时断掉 test 的权限， test1 还会有权限吗？
A:在 oracle9i 是，答案是还会有。
11、角色
角色就是一堆权限的集合
Create role myrole;
Grant create table to myrole;
Drop role myrole; 删除角色
12、某真实项目实例（BaoBao121）
Linux 下的操作（Windows 下类似）
1）建立相应的目录并变更其所有者以及组
##建目录
#mkdir /opt/oracle/oradata/baobaodata
##变更所有者
#chown oracle /opt/oracle/oradata/baobaodata/
##变更组
#chgrp oinstall /opt/oracle/oradata/baobaodata/
2）创建表空间
##转到 oracle 用户执行操作，注意要有中划线
#su - oracle
##进入 SQL 操作
#sqlplus /nolog
SQL>connect / as sysdba
##执行创建表空间
SQL>CREATE TABLESPACE baobao121 DATAFILE '/opt/oracle/oradata/baobaodata/baobao121.dbf' SIZE 200M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
SQL>create temporary tablespace baobao121_temp tempfile '/opt/oracle/oradata/baobaodata/baobao121_temp.dbf' size 50M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL;
3）增加相关的用户及权限
##增加用户
SQL>create user baobao121 identified by baobao121 DEFAULT TABLESPACE baobao121 temporary tablespace baobao121_temp;
##增加权限
SQL>grant all privileges to baobao121;
##测试连接
SQL>conn baobao121/baobao121;
SQL>show user;
显示应为 USER is "BAOBAO121"
19、备份 恢复 SQL Loader
1、imp 导入 import
2、exp 导出 export
3、SQL Loader
在 Oracle 数据库中，我们通常在不同数据库的表间记录进行复制或迁移时会用以下几种方法：
1. A 表的记录导出为一条条分号隔开的 insert 语句，然后执行插入到 B 表中
2. 建立数据库间的 dblink，然后用 create table B as select * from A@dblink where ...，或 insert into B select * from A@dblink where ...
3. exp A 表，再 imp 到 B 表，exp 时可加查询条件
4. 程序实现 select from A ..，然后 insert into B ...，也要分批提交
5. 再就是本篇要说到的 Sql Loader(sqlldr) 来导入数据，效果比起逐条 insert 来很明显
第 1 种方法在记录多时是个噩梦，需三五百条的分批提交，否则客户端会死掉，而且导入过程很慢。如果要不产生 REDO 来提高 insert into 的性能，就要下面那样做：
alter table B nologging;
4、sql loader 的用法
1. 只使用一个控制文件，在这个控制文件中包含数据(推荐)
2. 使用一个控制文件(作为模板) 和一个数据文件
Csv 文件如下：（dept1.csv）
" ","DEPTNO","DNAME","LOC" "1","10","ACCOUNTING","NEW YORK" "2","20","RESEARCH","DALLAS" "3","30","SALES","CHICAGO" "4","40","OPERATIONS","BOSTON" "5","50","sdsaf","adf" "6","12","aaa","aaa"
Ctl 文件如下：（dept1.ctl）
方式一：
Load data Infile c:\dept1.csv truncate Into table dept1 ( Deptno position(1:2), Dname position(3:5), Loc position(6:8) )
方式二： OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行
LOAD DATA INFILE "c:\dept1.csv" --指定外部数据文件，可以写多个 INFILE "another_data_file.csv" 指定多个数据文件 --这里还可以使用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件， append --操作类型，用 truncate table 来清除表中原有记录 INTO TABLE dept1 -- 要插入记录的表 Fields terminated by "," -- 数据中每行记录用 "," 分隔 Optionally enclosed by '"' -- 数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时 trailing nullcols --表的字段没有对应的值时允许为空 ( virtual_column FILLER, --跳过由 PL/SQL Developer 生成的第一列序号 deptno ,--"dept_seq.nextval", --这一列直接取序列的下一值，而不用数据中提供的值 dname ,--"'Hi '||upper(:dname)",--,还能用 SQL 函数或运算对数据进行加工处理 loc )
说明：在操作类型 truncate 位置可用以下中的一值：
1) insert --为缺省方式，在数据装载开始时要求表为空
2) append --在表中追加新记录
3) replace --删除旧记录(用 delete from table 语句)，替换成新装载的记录
4) truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录
执行命令
Sqlldr scott/tiger control=dept1.ctl
看看日志文件, 坏数据文件，从中可让你更好的理解 Sql Loader，里面有对控制文件的解析、列出每个字段的类型、 加载记录的统计、出错原因等信息。
20、数据库设计范式
1、第一范式：字段要设计的不可再分
Name 字段 可拆分成 FirstName+LastName
2、第二范式：两个表的关系，在第三张关系表中体现
比如学生和课程表。为多对多的关系。这种关系需要在第三张表中体现
3、第三范式：多张表中，只存关系，不存具体信息（具体开发中用的最多）
比如： emp,dept
如果一对多用第三张表（关系表）来表示，则会出现问题。（一个员工可能属于多个部门， 显然这是不符合现实逻辑的）
4、总范式 数据库表关联越少越好，SQL 语句复杂度越低越好
三种范式其实只供参与。数据库设计原则：数据库表关联越少越好，SQL 语句复杂度越低 越好。所以有时候，违反了第三范式，但是简化了查询语句。加快的检索速度。 例如：我们开发的某日本项目中， serviceoffice (serviceofficeid) (1——>N) careuserham (careuid serviceofficeid) careuserham (careuid serviceofficeid) (1——>N) helperassign (assigned careuid servicedate serviceofficeid)
这里的 helperassign 表中的 serviceofficeid 就是冗余字段，不符合第三范式。但是因为加上这个 字段，使的检索速度加快不下 10 倍
21、数据库设计工具
1、PowerDesigner
2、ErWin
3、Rose
4、权限系统设计
首先，我来介绍一下系统的主要组成部分，因为是权限系统，所以它的组成主 要由模块权限、角色权限、用户权限三大部分组成，下面将详细介绍一下三大 权限的作用以及它们互相存在的依赖性。
模块权限部份，说白一点就是管理系统中的功能模块，而在这些模块 中，它们有着各式各样的具体的操作，这些具体的操作权限就是模块权限，例 如现新加了一个用户管理的功能模块，在这个模块设计中需要进行浏览、添加、 修改、删除、审核、查询等一些具体的操作，在系统运行的过程中，不是所有 的用户都会拥有这些权限的，根据需要，就产生了下面的角色权限。
角色权限部分，它就是一个身份，拥有这个身份的用户在系统中能做 些什么，不能做些什么，用户都得依照这个身份，无法过界，这也是一个权限 的范围限定，在一个管理系统有很多的用户，我们不能将模块中的权限逐个的 分配给用户，现在角色的出现就解决了这个问题，它就像一个权限组，将模块 的权限指派给角色，让拥有该角色的用户可以拥有对模块对应的操作权限，然 而，一个系统中用户可能会成千上万不等，但我想角色最多不过几十个，将模 块权限授权给几十个角色比授权给上万个用户轻松多了，角色虽然是权限组， 有限定的作用范围，但是也有会出现意外情况的时候，如果一个用户有操作用 户模块的角色，但对它的有个特殊的要求，就是不能操作用户模块中的删除或 其它功能，还有就是它还有操作另外模块的权限，而它拥有的角色只能访问用 户模块，怎么办呢？难道要为这一个用户再建立一个角色吗？我的回答是 No.
现在我介绍我们系统中的最后一个权限，它就是用户权限，我想很多 朋友以前在开发管理系统时都用过这种权限模式，虽然这种方法比较原始，但 它能解决我们上面所遇到的问题，当然，还需要做一些小小的改进，在角色中， 我们只告诉用户能做什么，没有告诉的就不能做，以角色做权限批量判断这点 已经够了，而用户单独的权限判断有点特殊，它得跳出角色。它的权限优先于 角色的权限，它拥有对某权限允许和禁止操作的功能，例如一个用户的角色权 限可以操作用户模块所有的功能，而该用户拥有了该模块某一功能的禁止权限， 那该用户不能对这个模块进行该功能操作，反之，用户角色没有授权这个模块 功能，而用户权限被授予了这个功能模块的允许权限，那它就能操作该功能, 流程图如下:
22、对象关系数据库系统
1、数据库发展历程
层次型数据库
网状型数据库
关系型数据库（主流）(19 世纪 70 年代)
对象关系型数据库（20 世纪 80 年代开始）
定义：ORDBS 是面向对象的数据库模型 (Object Oriented Data Model 简称 OO 模型)和关 系型数据模型的结合产物
2、对象关系型数据库发展方向
1、以面向对象的程序设计语言为基础，研究持久化的程序设计语言。支持 OO 模型 （Hibernate）
2、建立新的面向对象数据库系统 OODBS，支持 OO 数据模型
3、以关系数据库和 SQL 为基础，把面向对象技术融入到数据库系统的 ORDBS
从纯粹的数据库系统角度来说，第三种发展较为卓著。它在传统的关系数据库的基础上吸 收了 OO 模型的主要思想，同时又保持了关系数据库系统的优点。
成功开发了诸如： Postgres,Illusta 等原型系统。本章课程主要以第三种发展方向来阐述
3、SQL3
SQL 全名是结构化查询语言（Structured Query Language），是用于数据库中的标准数据查 询语言，IBM 公司最早使用在其开发的数据库系统中。1986 年 10 月，美国 ANSI 对 SQL 进行规范后，以此作为关系式数据库管理系统的标准语言 (ANSI X3. 135-1986)，1987 年 得到国际标准组织的支持下成为国际标准
SQL3 是 1999 年发布的新的 SQL 标准。最显著的特点就是：提供了面向对象的扩展 克服的原来关系型数据库数据类型单一的缺点。扩展的类型 LOB,Boolean,集合类型 Array. 用户定义的 Distinct 类型 现在最新的标准是 SQL99。ORACLE 不但对标准的 SQL 完全兼容，而且有自己更为方便 的增强 SQL
1）大对象 Lob（Large Object）类型
Lob 主要分为 Blob(Binary Large Object)，用于存储音频，图像数据。Clob(Character Large Object) 主要用于存储长字符串数据 Lob 对象可以想其他数据类型那样被查询，修改，插入，但是必须为 LOB 提供足够大的缓 冲区。 应用程序通常并不传输整个 Lob 类型的值，而是通过 LOB 定位器访问大对象值
2）Boolean 类型
除了 not,and,or 还增加了 every,any
3）集合类型（Array）
Create table sales( Item_no char(20), //商品号 Qty integer array[12];//整数数组。存放 12 个月的销售额 ); 插入值的方式： Insert into sales(item_no qty) values('nike10029',array[20,10,20,10,1,12,101,1,1,1,1,1]); 查询三月份的销售额大于 100 的商品号 Select item_no from sales where qty[3]>100;
4）Distinct 类型
23、其他数据库
1）MYSQL
服务器端下载：
下载地址： www.mysql.com
Title: The world's most popular open source database
安装选项：
第一个选项为：把 MYSQL 安装的开发的机器上，只用少量内存
第二个选项为：把 MYSQL 安装在服务器上，将会应用大部分内存
第三个选项为：把 MYSQL 安装在专门的服务器上，这时 MYSQL 将用掉所有可用的内存
编码格式一定要注意。默认开发国内的项目一般选择 GB2312
Enable root access from remote machines 指的是是否可以从远程客户端访问该数据库
客户端简介
Mysql Commang Line client(命令行的客户端)
Navicat for MySQL（推荐）
Mysql 与 Oracle 的区别
特别注意：数据类型的区别，一些 SQL 命令的区别，比如:Limit 等
Select * from user limit 2,3 //指的是从 2 条以后开始，找出 3 条
MYSQL 数据库里，分页的实现非常简单。而在 Oracle 中，分页的实现显得异常繁琐
2） DB2
用 UNION ALL 代替 UNION
查询现有的索引
。。。。。。
24、其他
1）执行语句报错ORA-12638
如图：
本地oracle客户端用PLSQL Developer连接远程数据库，每次登录都会在很久之后，出现 ORA-12638: 身份证明检索失败，tnsping 表明TNS配置没有问题。
解决方案：
方法一：
D:\app\sabre\product\11.2.0\client_1\NETWORK\ADMIN
此目录下找到sqlnet.ora文件，如果存在SQLNET.AUTHENTICATION_SERVICES= (NTS)设置，则修改为：SQLNET.AUTHENTICATION_SERVICES= (NONE)，如果不存在，则直接添加SQLNET.AUTHENTICATION_SERVICES= (NONE)，我的问题得以解决。
SQLNET.AUTHENTICATION_SERVICES 表示oracle使用哪种验证方式，NTS表示采用本地操作系统认证，NONE表示将采用口令文件方式认证。设定了none后，本地的操作系统认证将不被许可，oracle将采用口令文件认证(此时 remote_login_passwordfile=exclusive)如connect /as sysdba 登录，后报错RA-01031: insufficient privileges，实际上是要求你输入sysdba的用户名和密码
方法二：
解决方法如下：
操作安装oracle服务器
开始 -> 程序 -> Oracle -> Configuration and Migration Tools（配置和移植工具） -> Net Manager
启动后：
本地→概要文件→Oracle高级安全性→验证→去掉所选方法中的 "NTS" 就可以了。
浙公网安备 33010602011771号