Oracle 培训材料
Oracle
1，体系机构
1，内存体系
sga 系统程序区
共享池：执行计划缓存和数据对象缓存
数据缓存池：数据缓存（keep,default,recycle)
重做日志池：日志缓存
大池：用于备份数据库
流池：用于流复制，保存复制的队列数据
java池：保存java对象
pga 进程程序区
sort排序区
hash连接区
merge合并区
2，进程体系
1, smon 系统监控进程，用于启动时执行检查点，恢复数据库异常退出的数据，
2, pmon 程序监控进程， 清理失败的进程资源（比如异常连接，断了清理资源），释放锁，内存等
3, dbwn 数据写进程 将数据写入文件中
4, lgwr 日志写进程 4：1，commit，2，超过1M或者1/3大小 3,dbwn执行之前 4，每3秒
5, arch 归档日志进程 1，在日志写满后触发 将日志文件复制到另一个地方
6,ckpt 检查点进程：检查点进程 将检查点信息写入数据文件的头部，更新控制文件的scn，维护
数据链表给dbwr，提供给他将数据写入数据文件，系统down机，提高数据恢复时间
3，文件体系
1，参数文件
2，控制文件
3，数据文件
4，日志文件
……
4,数据结构
1，块：oracle中存取的最小单元
2，区：oracle中是按区来分配
3，段：在创建对象时，分配的空间
4，表空间，一组文件的集合
2，Oracle数据类型
1，number ，varchar2…….
scn
系统更改号，保证查询的一致性，可恢复性
alter user hr identified by hr account unlock ,解锁hr用户，设置hr密码为hr
PLSQL介绍
结果集也可以作为一个“伪表”再次放在From子句中进行查询
select * from (
select rownum rn, id, name from students)
where rn > 2;
Merge语句介绍:表 A 中的记录更新表 B 中的记录
语法如下：
merge into 表1
using 表2 或者（select * from 表2）别名
on (表1.id=表2.id)
when matched then update set 表1.列=表2.列
这里的表1为更新的表，表2为取的数据源
以上是有条件的
create table test1 as
select rownum,rownum*1000 from tab
或者
create table test1 as
select rownum,rownum*1000 from dual connect by rownum<10;
通过merge语句插入测试表数据表
create table test2(id2 int,sal2 int),建立测试表test2
merge into test2
using test1
on(1=0)
when not matched then
insert values(id,sal/2)
更新表
merge into test2
using test1
on(test2.id2=test1.id)
when matched then
update set sal=sal+sal2/2
update语句用法（和sql server的update的不一样），两个表关联查询取出数据，然后更新
SQL server
update a set a.name=b.name from a inner join b on a.id=b.oid where a.id='11'
Oracle
update a set a.name=(select b.name from b where b.oid=a.id)
from a.id='11'
分析函数介绍
row_number() over (order by id desc),用于分页
dense_rank 排名行数，顺序始终是连续
rank 顺序可以不连续
ntile(n) 分组
窗口函数 over (order by id desc)
递归语句start with....connect by的使用
select 节点ID,节点名称,level
from 表
connect by prior 节点ID=上级节点ID
start with 上级节点ID=节点值
节点ID就是向上查
如果是上级节点ID，就是向下查
start with表示从那个记录开始
动态SQL介绍
sql server 执行动态语句 exec('delete from 表')
Oracle 执行动态语句（注明：不用使用sql名称作为变量名）
execute immediate '';
例子：
declare
tt VARCHAR2(200);
begin
tt:='create table tbs(id int)';
begin
EXECUTE immediate tt;
exception
when OTHERS then
dbms_output.put_line('system error');
end;
end;
动态SQL
declare
i int;
j int;
BEGIN
j:=1;
EXECUTE immediate 'select * from (select count(*) from dual connect by rownum<10) where 1=:1' into i using j;
dbms_output.put_line(i);
end;
函数介绍
只有一种(IN)
create or replace function 函数名称(变量名称 类型...)或者(表.列%type)绑定类型
return 类型(int,date,varchar2)
as
v_i varchar2(200); --声明类型
begin
--程序逻辑
return 数据值;
end;
实例
create or replace function f_name(id int)
return int
as
s int;
begin
s:=1212;
return (s+id);
end;
调用
select f_name(12) from dual;
存储过程
create or replace procedure pro_test(s_sql in varchar2)
as
s_ad varchar2(300):='www';
begin
dbms_output.put_line(s_sql|| s_ad);
end;
在程序中执行
begin
pro_test('sdfsafd');
end;
在sqlplus中执行
exec pro_test('wewe');
这里有in，out,in out三钟参数类型，out的值可以传出，in out可以传入和传出
包介绍(重载）
包声明
包主体
游标使用
显式游标和隐式游标
游标的属性有四种,分别是SQL %ISOPEN，SQL %FOUND，SQL%NOTFOUND，SQL %ROWCOUNT。
%ISOPEN 判断游标是否被打开，如果打开%ISOPEN 等于true,否则等于false
%FOUND %NOTFOUND 判断游标所在的行是否有效，如果有效，则%FOUNDD等于true，否则等于false
%ROWCOUNT 返回当前位置为止游标读取的记录行数。
显示游标
语法：
声明游标
打开游标
从游标中取回数据
关闭游标
例子：
declare
cursor cs is select * from bas_code; --申明游标
cs1 cs%rowtype ;
begin
open cs;--打开游标
loop
fetch cs into cs1;--遍历游标
exit when cs%notfound;
dbms_output.PUT_LINE(cs1.code);
end loop;
close cs; --关闭游标
end;
隐式游标
游标FOR循环
for ... in 是隐式游标
循环的使用
LOOP … END LOOP ( EXIT [ WHEN ] )
如：
declare
num INTEGER;
begin
num:=5;
loop
num:=num-1;
dbms_output.put_line(num);
exit when num=1;
end loop;
end;
WHILE … LOOP ---- the WHILE loop
declare
num INTEGER;
begin
num:=5;
loop
num:=num-1;
dbms_output.put_line(num);
exit when num=1;
end loop;
end;
FOR … LOOP ----the numeric FOR loop
declare
i int;
begin
i:=5;
for x in 1..i loop
dbms_output.put_line(x);
end loop;
end;
异常处理
常用函数：
SELECT LENGTH('CANDIDE') FROM DUAL
SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL
SELECT UPPER('Large') "Uppercase" FROM DUAL;
select to_date('2008-01-01 10:10:10','yyyy-mm-dd hh24:mi:ss') from dual
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual
select to_char(1212) from dual
使用字符串“ab”右填充一个名字，使它达到12个字符长度
SELECT RPAD('MORRISON',12,'ab') "RPAD example" FROM DUAL;
类似有：
SELECT LPAD('Page 1',15,'12') "LPAD example" FROM DUAL
nvl：
将 NULL 替换成指定的值,非null返回以前的值
select nvl(null,'ss') FROM dual
nvl2
对null替换，非null为12,为null为11
select nvl2(null,12,11) from dual
sysdate
当前时间
select to_char(sysdate+1,'yyyy-mm-dd hh24:mi:ss') 加一天,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 现在时间 from dual
decode
将 expr 与 search 逐一比较，若二者相等，则返回对应的 result；若全部不相等，则返回 default，若省略 default，则返回 NULL
select decode(12,12,2,4) from dual
||连接字符，非”+“
select '一'||'开始' 连接 from dual
trim，截取字符
select length(trim('wwww ')) from dual
REPLACE 取代字符
select replace('asdfsd','f','333') from dual
Substr 截取字符，（包含起点的字符）
SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAl
instr 查找字符在字符串中查询到的位置
select instr('abcdef','bc') from dual
if函数
IF <condition1> THEN
<code1>;
ELSIF <condition2> THEN
<code2>;
[ ELSE
<code3>; ]
END IF;
这里有then，elsif和end if;
case函数
CASE <expression>
WHEN <result1> THEN
<statements1>;
WHEN <result2> THEN
<statements2>;
...
ELSE
<statements_else>;
END CASE;
