Oracle 培训材料

    前段时间给公司做个Oracle培训，一直在学oracle，但都对其没有完整的整理，这次对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;