簡(jiǎn)介這份文檔是面向 Oracle 數(shù)據(jù)庫(kù)學(xué)習(xí)者的圖書(shū)管理系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)完整方案適合高校數(shù)據(jù)庫(kù)課程設(shè)計(jì)、畢業(yè)設(shè)計(jì)或自學(xué)參考。文檔從系統(tǒng)分析入手依次展開(kāi)需求分析、設(shè)計(jì)目標(biāo)與項(xiàng)目規(guī)劃并系統(tǒng)介紹數(shù)據(jù)庫(kù)概念結(jié)構(gòu)設(shè)計(jì)、邏輯結(jié)構(gòu)設(shè)計(jì)與物理結(jié)構(gòu)設(shè)計(jì)再落到表空間、數(shù)據(jù)表、視圖、序列、索引、存儲(chǔ)過(guò)程和觸發(fā)器的創(chuàng)建管理以及數(shù)據(jù)查詢(xún)、更新、合并等訪問(wèn)操作幾乎覆蓋數(shù)據(jù)庫(kù)設(shè)計(jì)全流程。資源僅包含1個(gè)doc文件共319KB文件雖小但目錄結(jié)構(gòu)完整從系統(tǒng)分析到數(shù)據(jù)庫(kù)訪問(wèn)共四章可作為數(shù)據(jù)庫(kù)課程設(shè)計(jì)的模板與實(shí)驗(yàn)參考。目前已有281人學(xué)習(xí)下載適合需要快速理解 Oracle 數(shù)據(jù)庫(kù)建模與實(shí)現(xiàn)步驟的初學(xué)者。文檔整體邏輯清晰尤其適合作為 Oracle 課程設(shè)計(jì)的起步參考。1. Oracle圖書(shū)管理系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)與實(shí)現(xiàn)這不是一張表是一套數(shù)據(jù)契約oracle圖書(shū)管理系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)與實(shí)現(xiàn)這六個(gè)字放進(jìn)需求文檔里是一句話(huà)落到你桌面上就是一套數(shù)據(jù)契約。它解決的不是“Oracle能不能跑圖書(shū)館業(yè)務(wù)”而是借書(shū)、還書(shū)、續(xù)借、逾期、統(tǒng)計(jì)這些動(dòng)作如何對(duì)應(yīng)到表結(jié)構(gòu)、約束、索引、存儲(chǔ)過(guò)程和分頁(yè)查詢(xún)里。適合正在做課程設(shè)計(jì)、要給別人交付設(shè)計(jì)文檔、或者接手老系統(tǒng)準(zhǔn)備重構(gòu)的工程師。設(shè)計(jì)文檔里值錢(qián)的不是ER圖那張圖而是每個(gè)字段的長(zhǎng)度、每個(gè)約束的邊界、每條SQL在特定數(shù)據(jù)量下能不能走對(duì)執(zhí)行計(jì)劃。2. 先建模再建庫(kù)從ER圖到Oracle數(shù)據(jù)字典的落地映射設(shè)計(jì)文檔的第一步永遠(yuǎn)是實(shí)體關(guān)系模型。很多教程畫(huà)完ER圖直接開(kāi)寫(xiě)CREATE TABLE中間跳過(guò)了兩件事實(shí)體之間的約束關(guān)系怎么落到數(shù)據(jù)庫(kù)對(duì)象上以及設(shè)計(jì)完怎么回頭看Oracle數(shù)據(jù)字典驗(yàn)證一致性。這兩件事不做文檔就是墻上的畫(huà)庫(kù)里是另一套。2.1 核心實(shí)體與關(guān)系讀者、圖書(shū)、借閱三張主表不能省圖書(shū)管理系統(tǒng)再?gòu)?fù)雜主干也是三張業(yè)務(wù)表讀者表、圖書(shū)表、借閱記錄表。讀者和圖書(shū)之間是多對(duì)多關(guān)系一個(gè)讀者可以借多本書(shū)一本書(shū)可以被不同讀者在多個(gè)時(shí)間點(diǎn)借出所以借閱記錄不能直接掛在圖書(shū)表的某個(gè)字段下它必須是一張獨(dú)立的關(guān)系實(shí)體表。常見(jiàn)字段設(shè)計(jì)是這樣的實(shí)體核心字段約束/說(shuō)明讀者表reader_id, reader_no, name, user_type, statusreader_no 唯一user_type 區(qū)分學(xué)生/教師圖書(shū)表book_id, isbn, title, author, category_id, stockisbn 不唯一同書(shū)多名副本所以要 book_id借閱記錄borrow_id, reader_id, book_id, borrow_date, due_date, return_date外鍵指向讀者和圖書(shū)狀態(tài)字段控制生命周期最容易犯的錯(cuò)是把“讀者當(dāng)前借了哪本書(shū)”直接存成讀者表的一個(gè)字段比如 reader.current_book_id。這樣做的代價(jià)是續(xù)借和還書(shū)時(shí)要去改讀者表一次借三本書(shū)就沒(méi)法建模更談不上查歷史記錄。借閱記錄表一定只存一次借閱動(dòng)作的開(kāi)始和結(jié)束而不是“現(xiàn)狀”。設(shè)計(jì)文檔里還應(yīng)寫(xiě)明業(yè)務(wù)規(guī)則借閱周期是30天還是15天能否續(xù)借逾期每天罰多少。這些規(guī)則最后要落成CHECK約束或存儲(chǔ)過(guò)程里的判斷不能只寫(xiě)在文檔的“需求分析”段落里。Oracle做這件事的優(yōu)勢(shì)是約束和事務(wù)都由數(shù)據(jù)庫(kù)兜住應(yīng)用層偶然漏掉一次判斷數(shù)據(jù)也不會(huì)錯(cuò)。2.2 用數(shù)據(jù)字典反向校驗(yàn)設(shè)計(jì)USER_TABLES 和 USER_CONSTRAINTS 的用途設(shè)計(jì)文檔寫(xiě)得再整齊實(shí)際庫(kù)里有沒(méi)有照著建要靠Oracle數(shù)據(jù)字典來(lái)驗(yàn)證。我們常說(shuō)“Oracle數(shù)據(jù)庫(kù)”和“數(shù)據(jù)庫(kù)設(shè)計(jì)”之間的橋梁就是USER_TABLES、USER_TAB_COLUMNS、USER_CONSTRAINTS這些視圖。每次交付前我都會(huì)跑下面這段SQL把文檔里的表名單和執(zhí)行結(jié)果對(duì)一遍-- 查看當(dāng)前用戶(hù)下所有業(yè)務(wù)表num_rows 是統(tǒng)計(jì)信息里的行數(shù)不代表實(shí)時(shí)數(shù)據(jù) SELECT table_name, num_rows, last_analyzed FROM user_tables ORDER BY table_name; -- 查看約束名、類(lèi)型、狀態(tài)重點(diǎn)看 R 外鍵是否生效 SELECT constraint_name, constraint_type, table_name, status, r_constraint_name FROM user_constraints WHERE table_name IN (READER, BOOK, BORROW_RECORD) ORDER BY table_name, constraint_name;說(shuō)明一下第二段SQL里的constraint_typeP代表主鍵R代表外鍵U代表唯一鍵C代表CHECK約束。設(shè)計(jì)文檔里寫(xiě)了“借閱記錄必須引用存在的讀者”庫(kù)里就對(duì)應(yīng)到BORROW_RECORD上的外鍵約束狀態(tài)必須是ENABLED。如果實(shí)際庫(kù)里沒(méi)有這個(gè)外鍵哪怕是程序一直正常驗(yàn)收時(shí)DBA看一眼數(shù)據(jù)字典就翻車(chē)了。另一個(gè)實(shí)用習(xí)慣是給表加注釋和字段注釋。Oracle里的COMMENT ON TABLE和COMMENT ON COLUMN會(huì)把說(shuō)明寫(xiě)進(jìn)數(shù)據(jù)字典查詢(xún)時(shí)通過(guò)USER_TAB_COMMENTS、USER_COL_COMMENTS就能看到。文檔能丟數(shù)據(jù)字典不會(huì)丟。2.3 主鍵方案序列加觸發(fā)器還是直接用 Oracle 12c 標(biāo)識(shí)列主鍵生成是圖書(shū)管理系統(tǒng)設(shè)計(jì)里最常見(jiàn)的分叉路。Oracle 12c以前標(biāo)準(zhǔn)做法是序列加觸發(fā)器常見(jiàn)于很多老項(xiàng)目。12c以后可以用GENERATED BY DEFAULT AS IDENTITYOracle 19c單實(shí)例也一樣支持。兩種我都用過(guò)建議按目標(biāo)庫(kù)版本選。-- 方式一Oracle 12c 起支持建表直接聲明標(biāo)識(shí)列 CREATE TABLE book ( book_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, isbn VARCHAR2(20) NOT NULL, title VARCHAR2(200) NOT NULL, author VARCHAR2(100), category_id NUMBER(4), stock NUMBER(6) DEFAULT 1, created_time DATE DEFAULT SYSDATE ); -- 方式二老庫(kù)兼容用序列 觸發(fā)器 CREATE SEQUENCE seq_book_id START WITH 1 INCREMENT BY 1 NOCACHE; CREATE OR REPLACE TRIGGER tri_book_id BEFORE INSERT ON book FOR EACH ROW WHEN (NEW.book_id IS NULL) BEGIN :NEW.book_id : seq_book_id.NEXTVAL; END; /需要注意標(biāo)識(shí)列和序列生成的數(shù)字都可能有間隙。比如事務(wù)回滾或觸發(fā)器執(zhí)行失敗序列已經(jīng)取走了一個(gè)值不會(huì)回填。所以業(yè)務(wù)規(guī)則上不能把book_id當(dāng)成“第幾本書(shū)”的編號(hào)展示給讀者它只是內(nèi)部主鍵。對(duì)外編號(hào)應(yīng)該用獨(dú)立的book_no字段。我更推薦在目標(biāo)版本是Oracle 19c或更高時(shí)直接使用IDENTITY列代碼更短少一個(gè)觸發(fā)器。但如果你交付的文檔里寫(xiě)了“兼容11g”那老老實(shí)實(shí)保留序列和觸發(fā)器。設(shè)計(jì)文檔必須把版本選擇寫(xiě)明確不能寫(xiě)“使用Oracle數(shù)據(jù)庫(kù)”就完事。3. 從設(shè)計(jì)文檔到可運(yùn)行庫(kù)建表語(yǔ)句、初始化數(shù)據(jù)與權(quán)限隔離設(shè)計(jì)文檔的驗(yàn)收現(xiàn)場(chǎng)不是看有沒(méi)有一張ER圖而是看能不能在干凈的環(huán)境里按文檔步驟把庫(kù)建出來(lái)。真正可落地的設(shè)計(jì)文檔建表語(yǔ)句必須能復(fù)制執(zhí)行初始化數(shù)據(jù)必須能重復(fù)跑權(quán)限必須和業(yè)務(wù)賬號(hào)分離。3.1 建表語(yǔ)句怎么寫(xiě)才算過(guò)得了 DBA 的眼DBA看建表語(yǔ)句最在意三件事字段類(lèi)型是否合理約束是否完整是否預(yù)留了擴(kuò)展空間。很多入門(mén)項(xiàng)目把圖書(shū)價(jià)格用VARCHAR2存把借閱狀態(tài)用中文“未還”/“已還”存我在評(píng)審時(shí)都會(huì)打回。Oracle里應(yīng)該用NUMBER存數(shù)值用CHAR(1)存狀態(tài)碼再用CHECK約束把取值范圍鎖死。-- 讀者表 CREATE TABLE reader ( reader_id NUMBER(8) NOT NULL, reader_no VARCHAR2(20) NOT NULL, name VARCHAR2(50) NOT NULL, phone VARCHAR2(20), email VARCHAR2(100), user_type CHAR(1) DEFAULT 0 NOT NULL, -- 0學(xué)生 1教師 status CHAR(1) DEFAULT 1 NOT NULL, -- 1正常 0凍結(jié) created_time DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT pk_reader PRIMARY KEY (reader_id), CONSTRAINT uk_reader_no UNIQUE (reader_no), CONSTRAINT ck_reader_status CHECK (status IN (0, 1)), CONSTRAINT ck_reader_type CHECK (user_type IN (0, 1)) ); -- 借閱記錄表 CREATE TABLE borrow_record ( borrow_id NUMBER(10) NOT NULL, reader_id NUMBER(8) NOT NULL, book_id NUMBER(8) NOT NULL, borrow_date DATE DEFAULT SYSDATE NOT NULL, due_date DATE NOT NULL, return_date DATE, renew_count NUMBER(2) DEFAULT 0 NOT NULL, status CHAR(1) DEFAULT B NOT NULL, -- B借閱中 R已歸還 CONSTRAINT pk_borrow PRIMARY KEY (borrow_id), CONSTRAINT fk_borrow_reader FOREIGN KEY (reader_id) REFERENCES reader(reader_id), CONSTRAINT fk_borrow_book FOREIGN KEY (book_id) REFERENCES book(book_id), CONSTRAINT ck_borrow_status CHECK (status IN (B, R)) );這里的VARCHAR2長(zhǎng)度不是隨便定的。phone給20位是要容納手機(jī)號(hào)前面帶國(guó)家碼VARCHAR2單位是字符不是字節(jié)所以中文字段也放得下。status列用CHAR(1)而不是NUMBER是為了后面代碼里讀寫(xiě)一眼能看出含義也避免狀態(tài)碼和數(shù)字主鍵混淆。due_date是借出時(shí)算出來(lái)的應(yīng)還日期不依賴(lài)應(yīng)用層臨時(shí)計(jì)算。借閱表上的外鍵是必須的。有人為了插入性能去掉外鍵結(jié)果是應(yīng)用層代碼私自插入一條不存在的reader_id查報(bào)表時(shí)關(guān)聯(lián)出空值還得回來(lái)補(bǔ)臟數(shù)據(jù)。這個(gè)教訓(xùn)我見(jiàn)過(guò)不止一次。3.2 初始化數(shù)據(jù)管理員賬號(hào)、圖書(shū)分類(lèi)、測(cè)試數(shù)據(jù)怎么造設(shè)計(jì)文檔一般會(huì)留一章“系統(tǒng)初始數(shù)據(jù)”。常見(jiàn)做法是建一張圖書(shū)分類(lèi)字典表再放幾個(gè)管理員賬號(hào)。管理員賬號(hào)不要和讀者表混在一起業(yè)務(wù)上兩者權(quán)限不同硬塞進(jìn)同一張表會(huì)讓角色控制非常別扭。-- 圖書(shū)分類(lèi)字典 CREATE TABLE book_category ( category_id NUMBER(4) NOT NULL, category_name VARCHAR2(100) NOT NULL, parent_id NUMBER(4), CONSTRAINT pk_book_category PRIMARY KEY (category_id) ); -- 管理員表 CREATE TABLE sys_user ( user_id NUMBER(8) NOT NULL, login_name VARCHAR2(50) NOT NULL, password VARCHAR2(200) NOT NULL, -- 存哈希值不存明文 user_name VARCHAR2(50) NOT NULL, status CHAR(1) DEFAULT 1 NOT NULL, CONSTRAINT pk_sys_user PRIMARY KEY (user_id), CONSTRAINT uk_sys_user_login UNIQUE (login_name) );初始化數(shù)據(jù)時(shí)我最煩的是腳本不能重復(fù)執(zhí)行。第一次跑成功第二次跑報(bào)主鍵沖突。所以初始化腳本里我習(xí)慣用MERGE而不是裸INSERT。MERGE的意思是“存在就更新不存在就插入”。比如初始分類(lèi)MERGE INTO book_category t USING (SELECT 1 AS category_id, 文學(xué) AS category_name FROM dual) s ON (t.category_id s.category_id) WHEN NOT MATCHED THEN INSERT (category_id, category_name) VALUES (s.category_id, s.category_name);這個(gè)寫(xiě)法看著啰嗦但在演示環(huán)境反復(fù)初始化時(shí)就是后悔藥。測(cè)試數(shù)據(jù)也一樣造讀者、造圖書(shū)、造借閱記錄腳本跑三遍都不會(huì)重復(fù)。Oracle的dual表在這里派上大用場(chǎng)它保證每一條MERGE只針對(duì)一條初始記錄。3.3 權(quán)限與同義詞業(yè)務(wù)賬號(hào)和 DBA 賬號(hào)的邊界很多課程設(shè)計(jì)圖省事全程用system或者sys建表。這樣做在單機(jī)練習(xí)環(huán)境沒(méi)問(wèn)題一旦要交付到真實(shí)項(xiàng)目等保和審計(jì)會(huì)直接拒絕。業(yè)務(wù)應(yīng)用應(yīng)該連一個(gè)只擁有業(yè)務(wù)表權(quán)限的賬號(hào)而不是sys。-- 創(chuàng)建業(yè)務(wù)管理賬號(hào) CREATE USER library_mgr IDENTIFIED BY 你的復(fù)雜密碼; GRANT CONNECT, RESOURCE TO library_mgr; GRANT UNLIMITED TABLESPACE TO library_mgr; -- 創(chuàng)建只讀報(bào)表賬號(hào) CREATE USER library_report IDENTIFIED BY 報(bào)表賬號(hào)密碼; GRANT CONNECT TO library_report; GRANT SELECT ON library_mgr.reader TO library_report; GRANT SELECT ON library_mgr.book TO library_report; GRANT SELECT ON library_mgr.borrow_record TO library_report;如果不想讓?xiě)?yīng)用側(cè)記住“l(fā)ibrary_mgr.book”這種帶模式名的寫(xiě)法可以建同義詞。Oracle里同義詞就是給對(duì)象起一個(gè)短別名應(yīng)用連接后能直接用BOOK訪問(wèn)。CREATE SYNONYM app_reader FOR library_mgr.reader; CREATE SYNONYM app_book FOR library_mgr.book;權(quán)限隔離的意義不只是安全。它還能讓你在賬號(hào)密碼泄露時(shí)快速定位影響范圍不需要把所有表都暴露給同一個(gè)連接。設(shè)計(jì)文檔里單獨(dú)寫(xiě)一節(jié)“權(quán)限矩陣”是很加分的部分。4. 圖書(shū)管理系統(tǒng)的Oracle查詢(xún)與存儲(chǔ)過(guò)程分頁(yè)、逾期、統(tǒng)計(jì)三件套設(shè)計(jì)文檔除了建表還要回答“功能怎么實(shí)現(xiàn)”。圖書(shū)管理系統(tǒng)里最高頻的功能就是查詢(xún)借閱記錄、處理還書(shū)、統(tǒng)計(jì)報(bào)表對(duì)應(yīng)到Oracle里就是分頁(yè)、存儲(chǔ)過(guò)程和常用函數(shù)。這部分寫(xiě)得越具體后面開(kāi)發(fā)越省事。4.1 分頁(yè)查詢(xún)ROWNUM陷阱與標(biāo)準(zhǔn)寫(xiě)法圖書(shū)列表、借閱記錄列表都要分頁(yè)。很多入門(mén)的Oracle寫(xiě)法是這樣先ORDER BY再ROWNUM結(jié)果發(fā)現(xiàn)前幾頁(yè)正常越翻越亂。原因是ROWNUM在排序之前就給結(jié)果集編號(hào)了直接加ROWNUM條件會(huì)先截?cái)嘣倥判?。穩(wěn)妥的分頁(yè)寫(xiě)法是用ROW_NUMBER窗口函數(shù)生成行號(hào)外層再過(guò)濾。-- 任意 Oracle 版本可用的分頁(yè)寫(xiě)法 SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.borrow_date DESC) AS rn FROM borrow_record t WHERE t.status B ) WHERE rn BETWEEN 1 AND 20;這里第一次查詢(xún)先按借出日期倒序再給每行生成從1開(kāi)始的序號(hào)外層截取第1到20行。頁(yè)碼變了BETWEEN后面的兩個(gè)數(shù)字就跟著變。Oracle 12c以上可以用OFFSET FETCH比如OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY但為了兼容舊庫(kù)我一般保留ROW_NUMBER寫(xiě)法。分頁(yè)查詢(xún)還有一個(gè)隱藏問(wèn)題如果借閱記錄表達(dá)到幾十萬(wàn)行排序字段必須有索引否則翻到后面的頁(yè)碼會(huì)明顯變慢。這個(gè)坑在下一章單獨(dú)說(shuō)。4.2 存儲(chǔ)過(guò)程還書(shū)業(yè)務(wù)和逾期罰款別讓?xiě)?yīng)用層算還書(shū)不是一個(gè)UPDATE就能完成的。它要改借閱記錄狀態(tài)、算應(yīng)還日期和實(shí)還日期的差值、可能生成罰款。這些邏輯放在存儲(chǔ)過(guò)程里比放在PHP、Java或者任何應(yīng)用代碼里都更安全。因?yàn)閿?shù)據(jù)庫(kù)事務(wù)邊界清晰一個(gè)存儲(chǔ)過(guò)程就是一個(gè)原子操作。CREATE OR REPLACE PROCEDURE proc_return_book ( p_borrow_id IN NUMBER, p_operator IN VARCHAR2, p_fine OUT NUMBER ) IS v_due_date DATE; v_overdue_days NUMBER; BEGIN SELECT due_date INTO v_due_date FROM borrow_record WHERE borrow_id p_borrow_id AND status B FOR UPDATE; UPDATE borrow_record SET return_date SYSDATE, status R, operator p_operator WHERE borrow_id p_borrow_id; v_overdue_days : TRUNC(SYSDATE) - TRUNC(v_due_date); IF v_overdue_days 0 THEN p_fine : v_overdue_days * 0.1; ELSE p_fine : 0; END IF; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20001, 借閱記錄不存在或已歸還); END proc_return_book; /說(shuō)明幾點(diǎn)。FOR UPDATE是給這條借閱記錄加鎖防止兩個(gè)人同時(shí)點(diǎn)擊還書(shū)。TRUNC(SYSDATE)把時(shí)間歸零只按天數(shù)算不會(huì)因?yàn)椤岸噙€了幾個(gè)小時(shí)”多罰一天錢(qián)。p_fine是OUT參數(shù)應(yīng)用層拿到它展示“本次還書(shū)產(chǎn)生罰款”。Oracle存儲(chǔ)過(guò)程不難寫(xiě)難在參數(shù)命名和事務(wù)提交位置。我的習(xí)慣是存儲(chǔ)過(guò)程里只做業(yè)務(wù)不塞打印日志日志另寫(xiě)一張表。4.3 統(tǒng)計(jì)報(bào)表借閱排行、類(lèi)別分布和常用函數(shù)設(shè)計(jì)文檔最后總要配上幾個(gè)統(tǒng)計(jì)場(chǎng)景熱門(mén)圖書(shū)排行、讀者借閱次數(shù)、逾期清單。這里用到的Oracle函數(shù)并不多翻來(lái)覆去就是TRUNC、TO_CHAR、RANK、NVL、DECODE這幾個(gè)。網(wǎng)上搜“oracle函數(shù)大全及舉例”很容易看花眼但圖書(shū)管理系統(tǒng)里真正需要手寫(xiě)的是帶窗口函數(shù)的聚合查詢(xún)。-- 熱門(mén)圖書(shū)排行按借閱次數(shù)排名取前10 SELECT * FROM ( SELECT b.book_id, b.title, COUNT(*) AS borrow_count, RANK() OVER (ORDER BY COUNT(*) DESC) AS rank_no FROM borrow_record br JOIN book b ON b.book_id br.book_id WHERE br.status R GROUP BY b.book_id, b.title ) WHERE rank_no 10;RANK函數(shù)允許并列名次比如第三名有兩本書(shū)下個(gè)名次是第五名這符合大多數(shù)榜單預(yù)期。如果業(yè)務(wù)要求不許并列就換ROW_NUMBER。GROUP BY后面列必須和SELECT里的非聚合列一致這在Oracle里報(bào)錯(cuò)很直接看ORA-00979就能定位。再比如統(tǒng)計(jì)每個(gè)月的借閱量按TO_CHAR(borrow_date, YYYY-MM)分組即可SELECT TO_CHAR(borrow_date, YYYY-MM) AS borrow_month, COUNT(*) AS total_count FROM borrow_record GROUP BY TO_CHAR(borrow_date, YYYY-MM) ORDER BY borrow_month;到了這一步設(shè)計(jì)文檔就不再只是“表結(jié)構(gòu)說(shuō)明書(shū)”而是把查詢(xún)邏輯也定下來(lái)了。5. Oracle圖書(shū)管理系統(tǒng)避坑與排查從字符集、監(jiān)聽(tīng)到分頁(yè)慢查詢(xún)每個(gè)Oracle項(xiàng)目都有幾個(gè)固定坑位圖書(shū)管理系統(tǒng)也不例外。下面這幾條都是實(shí)操中容易踩的每條我都按“現(xiàn)象→原因→解決”寫(xiě)方便你直接對(duì)照。5.1 字符集不一致導(dǎo)致中文亂碼現(xiàn)象PL/SQL Developer里中文顯示正常Java應(yīng)用插入后查出來(lái)是問(wèn)號(hào)或者反過(guò)來(lái)客戶(hù)端看著是亂碼庫(kù)里其實(shí)是對(duì)的。原因數(shù)據(jù)庫(kù)字符集、客戶(hù)端NLS_LANG、應(yīng)用連接字符集三者不一致。最常見(jiàn)的是庫(kù)用了AL32UTF8客戶(hù)端還是ZHS16GBK或者服務(wù)端環(huán)境變量沒(méi)導(dǎo)入。解決先確認(rèn)數(shù)據(jù)庫(kù)字符集再用同一套字符集配置客戶(hù)端。SELECT USERENV(language) FROM dual;輸出類(lèi)似SIMPLIFIED CHINESE_CHINA.AL32UTF8。如果確認(rèn)庫(kù)是AL32UTF8Linux客戶(hù)端就設(shè)置export NLS_LANGAMERICAN_AMERICA.AL32UTF8Windows客戶(hù)端也要在系統(tǒng)環(huán)境變量里改成一致。建庫(kù)前最好就把字符集定成AL32UTF8換庫(kù)不是小事。5.2 監(jiān)聽(tīng)出問(wèn)題連接時(shí)快時(shí)慢甚至ORA-12541現(xiàn)象sqlplus登錄Oracle數(shù)據(jù)庫(kù)出現(xiàn)緩慢或者直接報(bào)ORA-12541、ORA-12537監(jiān)聽(tīng)服務(wù)無(wú)法啟動(dòng)按system用戶(hù)登錄沒(méi)有反應(yīng)。原因Oracle監(jiān)聽(tīng)依賴(lài)主機(jī)名解析。服務(wù)器hostname改過(guò)了/etc/hosts里沒(méi)有對(duì)應(yīng)條目或者監(jiān)聽(tīng)端口被防火墻擋住。還有一種常見(jiàn)原因是listener.log已經(jīng)漲到幾個(gè)G監(jiān)聽(tīng)日志寫(xiě)入慢連接自然跟著慢。解決按先后順序執(zhí)行下面三件事。# 檢查監(jiān)聽(tīng)狀態(tài) lsnrctl status # 看監(jiān)聽(tīng)日志是否異常 tail -n 200 $ORACLE_HOME/network/log/listener.log # hostname 對(duì)應(yīng)關(guān)系梳理 cat /etc/hosts如果hostname變了把127.0.0.1 主機(jī)名寫(xiě)進(jìn)/etc/hosts再用lsnrctl reload。如果日志過(guò)大可以定期清理或者在監(jiān)聽(tīng)配置里打開(kāi)日志輪轉(zhuǎn)。這個(gè)排查順序能覆蓋八成連接問(wèn)題。5.3 借閱記錄分頁(yè)越翻越慢執(zhí)行計(jì)劃走了全表掃描現(xiàn)象第一頁(yè)秒開(kāi)翻到100頁(yè)要等好幾秒EXPLAIN PLAN發(fā)現(xiàn)sort order by和table access by rowid后面跟著全表掃描。原因分頁(yè)主子段是borrow_date但沒(méi)有對(duì)應(yīng)索引。每次翻頁(yè)都要把所有符合條件的記錄抓出來(lái)排序再截取。數(shù)據(jù)量只有一萬(wàn)條時(shí)感覺(jué)不明顯十萬(wàn)條以后體感明顯。解決給排序和過(guò)濾條件建復(fù)合索引。CREATE INDEX idx_borrow_status_date ON borrow_record (status, borrow_date DESC);status列做前綴是因?yàn)閃HERE里先按狀態(tài)過(guò)濾再把borrow_date倒序拿出來(lái)排序。圖書(shū)管理系統(tǒng)里“正在借閱的記錄”通常只占少部分這個(gè)索引在還書(shū)和分頁(yè)場(chǎng)景都能用上。建完索引立刻看執(zhí)行計(jì)劃EXPLAIN PLAN FOR SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.borrow_date DESC) rn FROM borrow_record t WHERE t.status B ) WHERE rn BETWEEN 1 AND 20; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);執(zhí)行計(jì)劃里出現(xiàn)INDEX RANGE SCAN就說(shuō)明索引生效了。5.4 觸發(fā)器里做太多事批量插入性能被拖垮現(xiàn)象單條插入正常用PL/SQL批量插入一萬(wàn)條圖書(shū)數(shù)據(jù)特別慢像卡住一樣。原因每一行觸發(fā)序列觸發(fā)器觸發(fā)器里還有多余的SELECT查詢(xún)和DBMS_OUTPUT行級(jí)觸發(fā)器被放大了一萬(wàn)倍。解決行級(jí)觸發(fā)器只做必要賦值不要查表不要打印輸出。如果要批量初始化測(cè)試數(shù)據(jù)關(guān)掉DBMS_OUTPUT或者直接放棄觸發(fā)器用IDENTITY列。-- 初始化大批量測(cè)試數(shù)據(jù)時(shí)先關(guān)掉會(huì)話(huà)輸出 SET SERVEROUTPUT OFF; DECLARE TYPE t_book_id IS TABLE OF NUMBER; v_ids t_book_id; BEGIN SELECT book_id BULK COLLECT INTO v_ids FROM book; -- 這里只是演示批量業(yè)務(wù)過(guò)程盡量用數(shù)組操作 END; /口訣是觸發(fā)器的代碼越短越好長(zhǎng)業(yè)務(wù)放存儲(chǔ)過(guò)程。6. 收尾給“數(shù)據(jù)庫(kù)設(shè)計(jì)與實(shí)現(xiàn).doc”加一份能說(shuō)服驗(yàn)收的驗(yàn)證清單設(shè)計(jì)文檔寫(xiě)到最后我會(huì)單獨(dú)放一節(jié)“驗(yàn)證清單”不是空話(huà)而是能在新庫(kù)上重復(fù)執(zhí)行的檢查腳本。這樣驗(yàn)收方不用肉眼找跑一遍SQL就能確認(rèn)設(shè)計(jì)落地了。驗(yàn)證清單至少包含四類(lèi)檢查表是否存在、約束是否啟用、基礎(chǔ)數(shù)據(jù)是否完整、關(guān)鍵查詢(xún)是否能在合理時(shí)間返回。下面這段SQL是我常用的開(kāi)場(chǎng)SELECT READER AS table_name, COUNT(*) AS cnt FROM reader UNION ALL SELECT BOOK, COUNT(*) FROM book UNION ALL SELECT BORROW_RECORD, COUNT(*) FROM borrow_record;如果三張表都是0行說(shuō)明初始化腳本沒(méi)跑。如果借閱記錄有值但讀者表沒(méi)有說(shuō)明外鍵可能沒(méi)生效回到USER_CONSTRAINTS查。最后一件事是給連接賬號(hào)做一次最小權(quán)限確認(rèn)。用報(bào)表賬號(hào)登錄試著執(zhí)行UPDATE和DELETE應(yīng)該報(bào)權(quán)限不足。能用SELECT查到業(yè)務(wù)數(shù)據(jù)但改不了這才是交付狀態(tài)。我自己的習(xí)慣是把這個(gè)驗(yàn)證清單和建表腳本放在同一個(gè)目錄命名成verify.sql交付時(shí)一起給。因?yàn)榭陬^說(shuō)“庫(kù)沒(méi)問(wèn)題”沒(méi)人信腳本能跑出結(jié)果才有說(shuō)服力。希望這個(gè)思路幫你在下一個(gè)Oracle圖書(shū)管理系統(tǒng)項(xiàng)目里少翻幾次車(chē)。本文還有配套的精品資源點(diǎn)擊獲取