Oracle总结一

Oracle数据库

1.数据库
oracle数据库是一个操作系统只有一个库，可以看做是oracle就只有一个大数据库
2.实例
一个oracle实例有一系列的后台进程和内存结构组成。一个数据库可以有n实例。
3.用户
用户是在实例下建立的。不同的实例可以建立相同名字的用户
4.表空间
表空间是oracle对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间
5.数据文件
数据文件是数据库表的物理存储单位。数据库的数据是存储在表空间中，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或者多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于表空间才行。

oracle的体系结构

逻辑体系结构：块，区，段，表空间
物理体系结构：表空间，三大文件
软件体系结构：SGA，后台进程

oracle中SGA的组成部分

答：系统全局区包括：共享池，重做日志缓冲区，数据告诉缓存区，大池，java池

oracle语句

– 查询表中数据
select * from user；
– 添加一条数据
insert into user （pid，pname） values（1，小米）；
commit；
– 修改一条数据
update user set pname=‘小马’ where pid=1；
commit；

—条件表达式
select e.ename
case e.ename
when ‘SMITH’ then ‘曹贼’
when ‘ALLEN’ then ‘大耳贼’
else ‘无名’
end
from emp e;

—判断emp表中员工工资，如果高于3000显示高收入，如果高于1500低于3000显示中等收入，
-----其余显示低收入
select e.sal
case
when e.sal > 3000 then ‘高收入’
when e.sal > 1500 then ‘中等收入’
else ‘低收入’
end
from emp e；

–分组查询
–查询出每个部门的平均工资
select e.deptno, avg(e.sal)
from emp e
group by e.deptno;

–查询出平均工资高于2000的部门信息
select e.deptno, avg(e.sal) asal
from emp e
group by e.deptno
having avg(e.sal)>2000;

–内连接
select *
from emp e inner join dept d
on e.deptno=d。deptno；

—查询出所有部门，以及部门下的员工信息。【外连接】
select *
from emp e right join dept d
on e.deptno=d.deptno

–子查询
select * from emp where sal in
（select sal from emp where ename = ‘SCOTT’）

–三表联查，得到最终结果。
select t.deptno, t.msal, e.ename, d.dname
from (select deptno, min(sal) msal
from emp
group by deptno) t, emp e, dept d
where t.deptno = e.deptno
and t.msal = e.sal
and e.deptno = d.deptno;

–oracle中的分页
–rownum行号：当我们做select操作的时候，
–每查询出一行记录，就会在该行上加上一个行号
–行号从1开始，依次递增，不能跳着走

----排序操作会影响rownum的顺序
select rownum，e.* from emp e order by e.sal desc

----如果涉及到排序，但是还要使用rownum的话，我们可以再次嵌套查询。
select rownum，t.* from（
select rownum，e.* from emp e order by e.sal desc ）t;

----emp表工资倒叙排列后，每页五条记录，查询第二页。
----rownum行号不能写上大于一个正数。
select * from(
select rownum rn，tt.*from (
select * from emp order by sal desc
)tt where rownum <11
)where rn>5

oracle视图

视图就是封装了一条复杂查询的语句。
语法1：CREATE VIEW 视图名称 AS 子查询
示例：建立一个视图，此视图包括了20个部门的全部员工信息
create view empvd20 as select * from emp t where t.deptno=20
select * from EMPV20 t

语法2：CREATE OR REPLACE VIEW 视图名称 AS 子查询
create or replace view empvd20 as select * from emp t where t.deptno = 20

语法3：设置为视图只读
CREATE OR REPLACE VIEW 视图名称 AS 子查询 WITH READ ONLY

ORACLE 索引

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。索引有很多种我们主要介绍常用的几种。建立索引的原则：如果有一个字段如果不经常查询，就不要去建索引。

创建索引的语法
1.单列索引
单例索引是基于单个列所建立的索引，比如
CREATE index 索引名 on 表名（列名）
2.复合索引
复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同。
Create index emp_idx1 on emp(ename,job);
Create index emp_idx1 on emp(job,ename);

索引使用的原则

1. 在大表上建立索引才有意义；
2. 在where子句或者是连接条件上的字段建立索引；
3. 表中数据修改频率高时不建议建立索引。

pl/sql

什么是pl/sql（Procedure Language/SQL）
PLSQL是oracle对sql语言的过程化扩展，指在sql命令语言中增加过程处理语句（如分支，循环等），使SQL语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来。

语法：
declare
说明部分 （变量说明，游标申明，例外说明）
begin
语句序列 （DML语句）
exception
例外处理语句
end：

常量和变量的定义
变量的基本类型就是 oracle 中的建表时字段的变量如 char, varchar2, date, number,boolean, long
定义语法：varl char(15);
Psal number(9,2);
说明变量名、数据类型和长度后用分号结束说明语句。

引用变量
Myname emp.ename%type;
引用型变量，即my_name 的类型与emp 表中 ename 列的类型一样
在 sql中使用 into 来赋值

记录型变量
Emprec emp%rowtype
记录变量分量的引用
emp_rec.ename:=‘ADAMS’;

3.if 分支
语法 1：
IF 条件 THEN 语句 1;
语句 2;
END IF;
语法 2：
IF 条件 THEN 语句序列 1 ；
ELSE 语句序列 2 ；
END IF
语法 3：
IF 条件 THEN 语句;
ELSIF 语句 THEN 语句;
ELSE 语句;
END IF;

–如果从控制台输入 1 则输出我是 1否则输出我不是 1
declare
mynum number := #
begin
if mynum = 1 then
dbms_output.put_line(‘我是1’);
else
dbms_output.put_line(‘我不是1’);
end if;
end;

4. LOOP 循环语句
   语法 1：
   WHILE total <= 25000 LOOP
   … .
   total : = total + salary;
   END LOOP;
   语法 2：
   Loop
   EXIT [when 条件];
   ……
   End loop
   语法 3：
   FOR I IN 1 . . 3 LOOP
   语句序列 ;
   END LOOP ;

使用语法 1 输出 1 到10 的数字
declare
step number := 1;
begin
while step <= 10 loop
dbms_output.put_line(step);
step := step + 1;
end loop;
end;

5. 游标Cursor
   在写java程序中有集合的概念，那么在pl/sql也会用到多条记录，这时候我们也要用到游标，游标可以存储查询返回的多条数据
   语法：
   CURSOR 游标名 [(参数名 数据类型，参数名 数据类型，…)] IS SELECT 语句；
   例如：
   cursor c1 is select ename from emp；
   游标使用步骤
   1. 打开游标 ： open c1； （打开游标查询）
   2. 取一行游标的值：fetch c1 into pjob （取一行到变量中）
   3. 关闭游标：close c1；（关闭游标释放资源）
   4. 游标的结束方式 exit when c1%notfound
   5. 注意： 上面的 pjob 必须与 emp表中的 job 列类型一致：
      定义：pjob emp.empjob%type;
      示例1：使用游标方式输出 emp 表中的员工编号和姓名
      declare
      cursor pc is
      select * from emp;
      pemp emp%rowtype;
      begin
      open pc;
      loop
      fetch pc
      into pemp;
      exit when pc%notfound;
      dbms_output.put_line(pemp.empno || ’ ’ || pemp.ename);
      end loop;
      close pc;
      end;

存储过程

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经过编译后存储在数据库中，用户通过指定存储过程的名字给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

创建存储过程语法
create [ or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
AS
begin
PLSQL子程序体
End

或者
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
is
begin
PLSQL子程序体
End 过程名；

范例：创建一个输出 helloword 的存储过程
create or replace procedure helloworld is begin
dbms_output.put_line(‘helloworld’);
end helloworld;
调用存储过程
在 plsql中调用存储过程
begin
– Call the procedure
helloworld ;
end;

存储函数

语法：
create or replace function 函数名（Name in type，Name in type，…）return 数据类型 is
结果变量 数据类型；
begin
return（结果变量）；
end 函数名；

存储函数和存储过程的区别

一般来讲，过程和函数在于函数可以有一个返回值；而过程没有返回值。
但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个值。

触发器

数据库触发器是一个与表相关联的，存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在表上发生的时候，Oracle 自动地执行触发器中定义的语句序列。

触发器可用于

• 数据确认
• 实施复杂的安全性检查1
• 做审计，跟踪表上所做的数据操作等。
• 数据的备份和同步

触发器的类型
语句级触发器：在指定的操作语句操纵之前或之后执行一次，不管这条语句影响了多少行。
行级触发器：触发记录语句作用的每一条记录都会被触发。在行级触发器中使用old和new伪变量吗，识别值的状态。
语法：
CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN( 条件) ] ]
begin
PLSQL 块
End