【正文】 第 1 頁(yè) 共 58頁(yè) 技術(shù) 部 oracle 初級(jí) 培訓(xùn)教材 第 2 頁(yè) 共 58頁(yè) 目 錄 第一章 數(shù)據(jù)庫(kù)設(shè)計(jì)基礎(chǔ) .................................................................. 3 第一節(jié) 常用數(shù)據(jù)類(lèi)型（ number, date, varchar2） ........................... 3 第二節(jié) 表，主鍵，外鍵，索引，視圖 ....................................... 3 第三節(jié) 數(shù)據(jù)操縱語(yǔ)言、常用 PL/SQL 控制結(jié)構(gòu) ................................ 5 第四節(jié) 觸發(fā)器、存儲(chǔ)過(guò)程、游標(biāo)和其它 ..................................... 6 第二章 SQL語(yǔ)言 ............................................................................... 9 第一節(jié) SQL 簡(jiǎn)介 .......................................................... 9 第二節(jié) 查詢(xún) SELECT 語(yǔ)句的使用 ........................................... 11 第三節(jié) 函數(shù) 14 第四節(jié) 更新 Insert 語(yǔ)句 ................................................. 22 第五節(jié) 更新語(yǔ)句 Update ................................................. 22 第六節(jié) 刪除語(yǔ)句 Delete .................................................. 23 第三章 PL/SQL基礎(chǔ)知識(shí) .......................................... 24 第一節(jié) PL/SQL 簡(jiǎn)介 ...................................................... 24 第二節(jié) 創(chuàng)建和使用過(guò)程 .................................................. 25 第三節(jié) 創(chuàng)建和使用函數(shù) .................................................. 27 第四節(jié) 創(chuàng)建并使用包 .................................................... 30 第五節(jié) 創(chuàng)建和使用觸發(fā)器 ................................................ 39 第四章 oracle 數(shù)據(jù)庫(kù)基本維護(hù)方法 ........................................... 46 第一節(jié) 組織文件系統(tǒng) .................................................... 46 第二節(jié) 規(guī)劃數(shù)據(jù)庫(kù)文件布 局 .............................................. 50 第三節(jié) 建立參數(shù)文件 .................................................... 52 第 3 頁(yè) 共 58頁(yè) 第一章 數(shù)據(jù)庫(kù)設(shè)計(jì)基礎(chǔ) 前言 目標(biāo)： 簡(jiǎn)單介紹 Oracle 開(kāi)發(fā)的知識(shí)，關(guān)于 Oracle 的安裝、配置、備份、優(yōu)化方面的知識(shí)，屬于 Oracle 系統(tǒng)管理培訓(xùn)的內(nèi)容，此處不進(jìn)行討論。 第一節(jié) 常用數(shù)據(jù)類(lèi)型（ number, date, varchar2） varchar2：變長(zhǎng)字符類(lèi)型，格式 : varchar2(n) 最大長(zhǎng)度 : 4000 字節(jié)。 絕對(duì)不可以用 CHAR類(lèi)型 。因?yàn)?CHAR 類(lèi)型是定長(zhǎng)的， ORACLE 會(huì)自在后面補(bǔ)空格， CHAR 類(lèi)型極大浪費(fèi)物理空間，又使程序處理很麻煩，絕對(duì)禁止。 number：數(shù)字類(lèi)型，格式 NUMBER(p,s) 數(shù)字類(lèi)型 p 為整數(shù)位， s 為小數(shù)位；例如：number(12,2)， number(10,0) 其中 number(p,0)可以寫(xiě)成 number(p)，例如 number(10) date：日期類(lèi)型，包括日期和時(shí)間 ， 在內(nèi)部實(shí)際上按數(shù)字處 理的，具有較高的速度。常用格式是 yyyymmdd hi24:mi:ss 或者 yyyy/mm/dd hi24:mi:ss 第二節(jié) 表，主鍵，外鍵，索引，視圖 表： 字段，主鍵，外鍵，索引 表： 本身是實(shí)際且獨(dú)立存在的。是數(shù)據(jù)庫(kù)存儲(chǔ)數(shù)據(jù)的基本單元 主鍵： 一般地，必須給表建立主鍵，如基礎(chǔ)數(shù)據(jù)、業(yè)務(wù)單據(jù)等表建立一個(gè) 數(shù)字類(lèi)型 的主鍵，并使用 ORACLE 序列號(hào) 發(fā)生器（以后稱(chēng)為“內(nèi)部序列號(hào)”）賦值。關(guān)系表、余額、發(fā)生額表除外。 索引： 索引是數(shù)據(jù)庫(kù)設(shè)計(jì)的一個(gè)極為重要的部分，索引建得好不好是區(qū)別數(shù)據(jù)庫(kù)設(shè) 計(jì)是業(yè)余水平還是專(zhuān)業(yè)水平的一個(gè)極為重要的參數(shù)。千萬(wàn)不要以為索引建得越多速度就越快。索引建得不好不僅不能提高速度，而且會(huì)極大降低速度系統(tǒng)運(yùn)行的效率。 在 PM 系統(tǒng)中，通常 需要建索引的字段 ： 外鍵關(guān)聯(lián)字段必須建索引。有的字段如錄入人標(biāo)識(shí)號(hào)可以酌情不建，但總單細(xì)目關(guān)聯(lián)字段無(wú)條件必須建索引。 日期必須建索引。 操作碼必須建索引（或唯一索引），名稱(chēng)可以建唯一索引進(jìn)行唯一性約束。 第 4 頁(yè) 共 58頁(yè) 不能建索引的字段： 建索引的字段要求該字段的值越不重復(fù)越好。 另外，一個(gè)查詢(xún) SQL 只能用一個(gè)索引，因此如果優(yōu)化查詢(xún)速度必須知道 ORACLE 用了哪個(gè)索引，這樣才能有意識(shí)地建索引進(jìn)行優(yōu)化。用 explain plan 命令可以知道 SQL 的運(yùn)行過(guò)程，參看相關(guān)資料。 做為索引的設(shè)計(jì) ： 如果不建索引或索引建得不好，在一定數(shù)據(jù)量下，有的 SQL 可能等好幾分鐘才出來(lái)或根本出不來(lái)，但建索引后就是秒級(jí)響應(yīng)。但在開(kāi)發(fā)測(cè)試時(shí)，沒(méi)有這么大的數(shù)據(jù)，在用戶(hù)那時(shí)實(shí)施之初也不會(huì)反應(yīng)，但一年后用戶(hù)就會(huì)怨聲載道。因此在數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)就必須考慮建立索引的問(wèn)題，不能等出了問(wèn)題再補(bǔ)救。 索引的副作用： ? 索引會(huì)引起物理空間的開(kāi)銷(xiāo)。 ? 會(huì)降低插入、刪除 、 修改記錄的速度。 視 圖（ view） 視圖是虛表。數(shù)據(jù)庫(kù)只存儲(chǔ)視圖的定義，不存儲(chǔ)對(duì)應(yīng)的數(shù)據(jù)。視圖用一組查詢(xún)來(lái)定義，是被存儲(chǔ)的查詢(xún)。 基于視圖的開(kāi)發(fā)： 根據(jù)關(guān)系數(shù)據(jù)庫(kù)的理論基礎(chǔ)，應(yīng)該盡量避免表中的數(shù)據(jù)冗余，這樣在做 Datawindow 顯示時(shí)，如果沒(méi)有視圖，會(huì)使 Datawindow 非常復(fù)雜，關(guān)聯(lián)多個(gè)表。而基于視圖的開(kāi)發(fā)，可以簡(jiǎn)化這些設(shè)計(jì)。如圖 : … … 表 1 表 2 表 n 視圖 1 視圖 2 視圖 3 視圖 4 視圖 5 單據(jù)管理 查詢(xún) 綜合統(tǒng)計(jì) 第 5 頁(yè) 共 58頁(yè) 上圖想表達(dá)的意義是許多開(kāi)發(fā)工作可以基于視圖，而視圖的開(kāi)發(fā)可以基于表或另一些視圖。 視圖的好處： ? 視圖包裝了一些設(shè)計(jì)，避免了重復(fù)勞動(dòng)。 ? 視圖可以基于另一視圖，分解簡(jiǎn)化設(shè)計(jì)。如ＢＭＳ３的集合管理，集合可以有任意多層，如果展開(kāi)ＳＱＬ將是如下情形： select * from sometab a where col1 in (select col1 from sometab b where col1 in (select col1 from sometab c where col1 in ( … … ) union …… ) )。 而用視圖后就簡(jiǎn)化為： select * from sometab_v_a。 而 sometable_v_a 又是基于 sometable_v_b 等的查詢(xún)?；?jiǎn)了算法。 ? 視圖不對(duì)應(yīng)物理表，便于擴(kuò)充，非常靈活。可以方便地加列，換表，還可以用 decode等函數(shù)轉(zhuǎn)意。最好的一個(gè)例子是ＢＭＳ３在開(kāi)發(fā) 一半后提出所有業(yè)務(wù)單據(jù)中要加入貨品產(chǎn)地，改了若干視圖和在 Datawindow 加一列就解決了。 使用視圖注意問(wèn)題： ? 視圖本身有一定的時(shí)間開(kāi)銷(xiāo)，如果使用層次過(guò)多會(huì)降低查詢(xún)速度。如果對(duì)速度要求較高，應(yīng)使用視圖直接對(duì)表操作，并進(jìn)行ＳＱＬ的優(yōu)化。 ? 關(guān)聯(lián)多表的視圖如果設(shè)計(jì)好可以增、刪、改，設(shè)計(jì)不好就只能查詢(xún)，并且必須是 以上版本才具有此特性。 在數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)，最好一并進(jìn)行視圖設(shè)計(jì)，以后的程序設(shè)計(jì)可以大量基于視圖開(kāi)發(fā)。 第三節(jié) 數(shù)據(jù)操縱語(yǔ)言、常用 PL/SQL控制結(jié)構(gòu) 數(shù)據(jù)操縱語(yǔ)言 數(shù)據(jù)操縱 語(yǔ)言（ DML）是用戶(hù)經(jīng)常使用的語(yǔ)言，包括了數(shù)據(jù)庫(kù)管理系統(tǒng)（ DBMS）的主要功能，主要有數(shù)據(jù)查詢(xún)和數(shù)據(jù)的增、刪、改等功能，其中查詢(xún)的表達(dá)方式是 DML 的主要部分。 第 6 頁(yè) 共 58頁(yè) ? 查詢(xún)數(shù)據(jù) Select ? 插入數(shù)據(jù) Insert ? 刪除數(shù)據(jù) Delete ? 更新數(shù)據(jù) Update 常用 PL/SQL 控制結(jié)構(gòu)： if … then … elsif … then … end if loop … end loop 第四節(jié) 觸發(fā)器、存儲(chǔ)過(guò)程、游標(biāo)和其它 觸發(fā)器 觸發(fā)器分類(lèi)： DML 觸發(fā)器、替代觸發(fā)器和系統(tǒng)觸發(fā)器 3 大類(lèi) DML 觸發(fā)器可以由 DML 語(yǔ)句 （ insert、 delete、 update）觸發(fā)，通常說(shuō)的觸發(fā)器就是指DML 觸發(fā)器，在觸發(fā)器中可以進(jìn)行 Insert、 delete、 update 操作。 替代觸發(fā)器只能定義在視圖上，直接對(duì)視圖的插入等操縱是非法的，建立替代觸發(fā)器來(lái)代替激發(fā)它的 DML 語(yǔ)句運(yùn)行。 系統(tǒng)觸發(fā)器在發(fā)生如數(shù)據(jù)庫(kù)啟動(dòng)或關(guān)閉系統(tǒng)事件時(shí)激發(fā)，而不是在執(zhí)行 DML 語(yǔ)句時(shí)激發(fā)；也可以在 DDL 時(shí)激發(fā)，比如在建表時(shí)激發(fā) . DML 觸發(fā)器又可以分為： 3 種語(yǔ)句（ Insert、 Update、 Delete） 2 種定時(shí)（之前、之后） 2 級(jí)（行或語(yǔ)句） 共 12 中可 能的觸發(fā)器類(lèi)型。 行級(jí)觸發(fā)器：對(duì)由觸發(fā)語(yǔ)句變更的 每一行激發(fā)一次 。行級(jí)觸發(fā)器由 FOR EACH ROW 字句表示。 語(yǔ)句級(jí)觸發(fā)器：在 語(yǔ)句之前或之后觸發(fā)一次 。 行級(jí)觸發(fā)器的相關(guān)標(biāo)識(shí)： 行級(jí)觸發(fā)器是按觸發(fā)語(yǔ)句所處理的行激發(fā)的，在觸發(fā)器內(nèi)部，需要訪問(wèn)正在處理中的行的數(shù)據(jù)，這是通過(guò)兩個(gè)相關(guān)的標(biāo)識(shí)符： :old 和 :new 來(lái)實(shí)現(xiàn)的 觸發(fā)語(yǔ)句 標(biāo)識(shí)符 :old 標(biāo)識(shí)符 :new INSERT 無(wú)定義－所有字段為空 NULL 該語(yǔ)句結(jié)束時(shí)將插入的值 UPDATE 更新前行的原始值 該語(yǔ)句結(jié)束時(shí)將更新的值 DELETE 行刪 除前的原始值 無(wú)定義－所有字段為空 NULL 下面是一個(gè)行觸發(fā)器的例子，當(dāng)插入或更新員工信息時(shí)，控制員工的薪水在一定的范圍之內(nèi)： CREATE TRIGGER BEFORE INSERT OR UPDATE OF sal, job ON FOR EACH ROW 第 7 頁(yè) 共 58頁(yè) WHEN ( 39。PRESIDENT39。) DECLARE minsal NUMBER。 maxsal NUMBER。 BEGIN /* 從 SAL_GUIDE 表中取的某個(gè)職務(wù)的最低和最高薪水 */ SELECT minsal, maxsal INTO minsal, maxsal FROM sal_guide WHERE job = :。 /* 如果員工薪水小于最小薪水或大于最大薪水，都觸發(fā)錯(cuò)誤，禁止更新 */ IF (: minsal OR : m