Oracle詳解

數(shù)據(jù)庫基礎(chǔ)

1. 起源：人工管理冒滩、文件系統(tǒng)轻抱、數(shù)據(jù)庫系統(tǒng)
2. 主要特征：結(jié)構(gòu)化、數(shù)據(jù)共享旦部、減少冗余祈搜、獨(dú)立性。
3. 相關(guān)概念：DB士八、DBS容燕、DBA、DBMS
4. 關(guān)系型數(shù)據(jù)庫
   • 理論基礎(chǔ)：集合論+關(guān)系代數(shù)
   • 用二維表組織數(shù)據(jù)：行婚度、列蘸秘、主鍵、外鍵蝗茁、約束醋虏、索引、……
   • 支持SQL(結(jié)構(gòu)化查詢語言)：DDL哮翘、DML颈嚼、DQL、DCL饭寺，以集合的方式處理數(shù)據(jù)阻课，本身不區(qū)分大小寫，但是對字符值進(jìn)行處理時(shí)是區(qū)分大小寫的
   • E-R圖：1-1艰匙、1-N限煞、N-M
   • 范式理論：1NF、2NF员凝、3NF署驻、BCNF、……

概述

發(fā)展史

• 1970年 IBM的Codd博士發(fā)表A Relational Model of Data for Large Shared Data Banks
• 1978年 Oracle 1 匯編語言 PDP-11
• 1983年 Oracle 3 C語言
• 1993年 Oracle 7 UNIX系統(tǒng)
• 1997年 Oracle 8 Java
• 1999年 Oracle 8i Internet
• 2001年 Oracle 9i 支持RAC(Real Application Clusters) / BI
• 2003年 Oracle 10g 網(wǎng)格計(jì)算
• 2007年 Oracle 11g
• 2013年 Oracle 12c 云計(jì)算

網(wǎng)格計(jì)算和云計(jì)算

網(wǎng)格計(jì)算：使用公用設(shè)施進(jìn)行計(jì)算健霹，不必關(guān)心數(shù)據(jù)的位置以及由哪臺(tái)計(jì)算機(jī)進(jìn)行計(jì)算旺上，就像使用電網(wǎng)一樣，不需要知道發(fā)電機(jī)的位置以及電網(wǎng)的連接方式骤公。網(wǎng)格計(jì)算改變了企業(yè)使用資源的方式抚官，使得數(shù)據(jù)中心等系統(tǒng)可以跨服務(wù)器使用資源。
云計(jì)算：一種按使用量付費(fèi)的模式阶捆，這種模式提供可用的凌节、便捷的钦听、按需的網(wǎng)絡(luò)訪問，進(jìn)入可配置的計(jì)算資源共享池(資源包括網(wǎng)絡(luò)倍奢、服務(wù)器朴上、存儲(chǔ)、應(yīng)用軟件卒煞、服務(wù)等)痪宰，這些資源能夠被快速提供，只需投入很少的管理工作或與服務(wù)供應(yīng)商進(jìn)行很少的交互畔裕。

安裝和啟動(dòng)

1. Windows環(huán)境安裝非常簡單衣撬；Linux環(huán)境需要先檢查內(nèi)存、磁盤扮饶、操作系統(tǒng)版本具练、依賴包安裝情況、編譯器版本等甜无，然后要配置內(nèi)核參數(shù)扛点、創(chuàng)建用戶和用戶組并添加用戶的限制參數(shù)，此外還要?jiǎng)?chuàng)建安裝目錄岂丘、環(huán)境變量并將安裝用的壓縮包解壓之后才能開始安裝陵究。
2. 可以使用DBCA(Database Configuration Assistant來)來創(chuàng)建數(shù)據(jù)庫實(shí)例。
3. Windows系統(tǒng)可以在“服務(wù)”中啟動(dòng)和關(guān)閉Oracle的監(jiān)聽和實(shí)例奥帘，也可以通過命令的方式來啟動(dòng)監(jiān)聽(lsnrctl)和實(shí)例铜邮；Linux系統(tǒng)可以在SQL*Plus下使用startup和shutdown命令來啟動(dòng)和關(guān)閉實(shí)例。

體系結(jié)構(gòu)

實(shí)例：非固定的基于內(nèi)存的基本進(jìn)程翩概。
數(shù)據(jù)庫：固定的牲距、基于磁盤的數(shù)據(jù)文件返咱、控制文件钥庇、日志文件、參數(shù)文件和歸檔日志文件等咖摹。

一般情況下评姨，Oralce數(shù)據(jù)庫都是一個(gè)數(shù)據(jù)庫包含一個(gè)實(shí)例。

在Oracle系統(tǒng)中萤晴，表空間和數(shù)據(jù)文件是Oracle數(shù)據(jù)庫結(jié)構(gòu)的基本要素吐句。

表空間：存放數(shù)據(jù)庫表、索引店读、回滾段等對象的邏輯磁盤空間嗦枢。

• SYSTEM表空間：存放數(shù)據(jù)字典
• SYSAUX表空間：
• UNDO表空間：存儲(chǔ)撤銷信息
• USERS表空間：存儲(chǔ)用戶創(chuàng)建的對象
• TEMP表空間：
• EXAMPLE表空間

數(shù)據(jù)文件：每個(gè)表空間可以包含一個(gè)或多個(gè)數(shù)據(jù)文件，可以在創(chuàng)建表空間時(shí)創(chuàng)建一個(gè)或多個(gè)數(shù)據(jù)文件屯断，也可以對已經(jīng)存在的表空間追加新的數(shù)據(jù)文件文虏。

select file_name, tablespace_name, bytes from dba_data_files;

控制文件：存儲(chǔ)Oracle實(shí)例信息侣诺、數(shù)據(jù)文件信息、日志文件信息的內(nèi)部二進(jìn)制文件氧秘，控制文件是Oracle實(shí)例啟動(dòng)時(shí)必須訪問的文件年鸳。

select name from v$controlfile;

日志文件：記錄數(shù)據(jù)庫所有發(fā)生的事務(wù)以及由Oracle內(nèi)部行為引起的數(shù)據(jù)庫變化的信息，在數(shù)據(jù)庫恢復(fù)時(shí)丸相，可以從日志文件中讀出原來的數(shù)據(jù)搔确。在創(chuàng)建表空間是可以使用nologging選項(xiàng)指定不產(chǎn)生日志。

select member from v$logfile;

常用操作

數(shù)據(jù)類型

創(chuàng)建表

create table tb_goods
(
gid number(8),
gname varchar2(50) not null,
gdesc varchar2(500),
gcurprice number(10, 2) not null,
) tablespace user;

刪除表

drop table tb_goods;

修改表

alter table tb_goods add constraint pk_gid primary key (gid);
alter table tb_goods add column goldprice number(10, 2) not null;

約束：約束通常也稱為完整性約束（實(shí)體完整性弛作、參照完整性和域完整性）畦幢，主要通過主鍵、外鍵缆蝉、檢查宇葱、非空、唯一刊头、默認(rèn)值等來實(shí)現(xiàn)黍瞧。

序列：生成唯一的整數(shù)，通常用于自增主鍵原杂。

create sequence seq_gid start with 10000000 increment by 1;

select seq_gid.nextval from dual;

drop sequence seq_gid;

視圖：視圖通常被認(rèn)為是“存儲(chǔ)的查詢”或“虛擬的表“印颤，它本身并不包含數(shù)據(jù)而是一個(gè)到表的映射，視圖的作用主要是簡化查詢要使用的語句穿肄，同時(shí)也可以將操作限制到指定的列年局，起到安全和保密的作用。

create or replace view v_emp_dept as 
select ename, dname, job, sal from scott.emp t1, scott.dept t2
where t1.deptno=t2.deptno;

drop view v_emp_dept;

同義詞：同義詞是數(shù)據(jù)庫對象的別名咸产，可以用來保護(hù)數(shù)據(jù)庫對象的隱私矢否。

create public synonym emp for scott.emp;
drop synonym emp;

索引：索引是關(guān)系數(shù)據(jù)庫中用于存放表中每條記錄位置的一種對象，其主要作用是加快數(shù)據(jù)檢索速度脑溢，其設(shè)計(jì)的合理性對系統(tǒng)性能有直接的影響僵朗。

create unique index uni_idx_goods on tb_goods (gid);
create index idx_goods_name on tb_goods (gname) cluster;

創(chuàng)建函數(shù)

create or replace function fn_factorial (n number) 
return number is
i number := 1;
result number := 1;
begin
for i in 1..n loop
    result := result * i;
end loop;
return result;
end fn_factorial;

創(chuàng)建存儲(chǔ)過程

create or replace procedure sp_get_avg_sal_by_dept_no(
dno dept.deptno%type, avgSal out emp.sal%type) as
begin
  select avg(sal) into avgSal from emp where deptno=dno;
end sp_get_avg_sal_by_dept_no;

創(chuàng)建觸發(fā)器

create or replace trigger tr_update_deptno
after update on dept
for each row
begin
  update emp set deptno=:new.deptno where deptno=:old.deptno;
end;

創(chuàng)建用戶并授權(quán)

create user luohao identified by 123123 password expire;
grant create session to luohao with admin option;

PL/SQL

declare

begin

exception

end

declare
    type t_table_of_num is table of emp.empno%type index by binary_integer;
    v_empno_array t_table_of_num;
begin
    v_empno_array(0) := 1234;
    v_empno_array(1) := 4321;
end; 

declare
    type t_record_stu is record (
        stuid number(4),
        stuname varchar2(20),
        stubirth date
    );
    v_stu t_record_stu;
begin
    v_stu.stuid := 1001;
    v_stu.stuname := 'Hao';
    v_stu.stubirth := to_date('1980-11-28', 'yyyy-mm-dd');
end;

declare 
    cursor c is select * from emp;
    v_emp c%rowtype;
begin
    open c;
    loop
        fetch c into v_emp;
        exit when (c%notfound);
        ... ...
    end loop;
    close c;

declare 
    cursor c is select * from emp;
begin
    for v_emp in c loop
        ... ...
    end loop;
end;

declare 
    cursor c is select * from emp for update;
begin
    delete ... where current of c;
    update ... where current of c;
end;

declare
    type t_cur_emp is ref cursor return emp%rowtype;
    v_emp_c t_cur_emp;
begin
    if not v_emp_c%isopen then
        open v_emp_c for select * from emp;
    end if;
    ... ...
end;

注意：

1. select語句必須且只能返回一條記錄而且要使用into存入對應(yīng)的變量中
2. sql%rowcount - 受影響的行數(shù)
3. execute immediate
4. too_many_rows / no_data_found / login_denied / dup_val_index / zero_divide / value_error / case_not_found
5. SQLCODE / SQLERRM