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)行處理時是區(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)格計算
• 2007年 Oracle 11g
• 2013年 Oracle 12c 云計算

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

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

安裝和啟動

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

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

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

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

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

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

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

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

select file_name, tablespace_name, bytes from dba_data_files;

控制文件：存儲Oracle實(shí)例信息、數(shù)據(jù)文件信息、日志文件信息的內(nèi)部二進(jìn)制文件，控制文件是Oracle實(shí)例啟動時必須訪問的文件。

select name from v$controlfile;

日志文件：記錄數(shù)據(jù)庫所有發(fā)生的事務(wù)以及由Oracle內(nèi)部行為引起的數(shù)據(jù)庫變化的信息，在數(shù)據(jù)庫恢復(fù)時，可以從日志文件中讀出原來的數(shù)據(jù)。在創(chuàng)建表空間是可以使用nologging選項指定不產(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)為是“存儲的查詢”或“虛擬的表“，它本身并不包含數(shù)據(jù)而是一個到表的映射，視圖的作用主要是簡化查詢要使用的語句，同時也可以將操作限制到指定的列，起到安全和保密的作用。

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è)計的合理性對系統(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)建存儲過程

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