您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 綜合教學

Java 基礎知識總結（八）：Oracle 數據庫安裝、用戶管理、體系結構、SQL/PLSQL 核心語法與實戰全攻略

編輯：Java 綜合教學

Java 基礎知識總結（八）：Oracle 數據庫安裝、用戶管理、體系結構、SQL/PLSQL 核心語法與實戰全攻略

本文作為 Java 開發者必備的數據庫技能包，系統覆蓋 Oracle 11g/12c/19c 安裝與卸載、用戶權限體系、實例與數據庫架構、SQL 四大語言分類、核心查詢技巧、約束設計、單行/分組函數、PL/SQL 變量與動態 SQL、游標機制，配合完整代碼模板、參數對照表、避坑指南，打造從安裝部署到開發優化的全鏈路參考手冊。

一、Oracle 安裝與卸載標準化流程

1. Windows (XP/7/10/Server) 安裝關鍵點

解壓 win32_11gR2_database_1of2.zip + 2of2.zip → 合併 database 目錄。
運行 setup.exe，**安裝路徑不含中文/空格**。
取消安全更新 → 桌面類 → 全局數據庫名 orcl → 管理員密碼 bluedot（sys/system 同密）。
口令管理頁解鎖 SCOTT、HR 示例用戶（密碼 tiger/hr）。

2. 徹底卸載清單（注冊表+服務+目錄+環境變量）

服務中停止所有 Oracle* 服務。
Universal Installer → 卸載產品 → 全選刪除（保留 OraDb11g_home1 目錄）。
注冊表清理：HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE、SYSTEM\CurrentControlSet\Services\Oracle*、Eventlog\Application\Oracle*、CLASSES_ROOT\Ora*、CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\MenuOrder\Start Menu\Programs\Oracle*、SOFTWARE\ODBC\ODBCINST.INI\Oracle*。
環境變量移除 CLASSPATH、PATH 中 Oracle 條目。
刪除目錄：C:\Program Files\Oracle、ORACLE_BASE、C:\Windows\system32\config\systemprofile\Oracle、用戶目錄下 Oracle、C:\Windows\*.ora *.ini、WIN.INI [ORACLE] 節。
ODBC 數據源管理器刪除 Oracle DSN。
事件查看器清除 Oracle 日志。

提示：個別 DLL 無法刪除可重啟後再刪，或選新目錄重裝覆蓋。

二、用戶與權限管理實戰

1. 用戶分類與默認賬號

角色	用戶/密碼	權限
超級管理員	SYS / bluedot `AS SYSDBA`	最高權限，數據字典擁有者
管理員	SYSTEM / bluedot	次高權限，管理對象
示例用戶	SCOTT / TIGER	EMP/DEPT 示例表擁有者
示例用戶	HR / HR	人力資源示例模式

2. 核心 DCL 語句模板


-- 創建用戶
CREATE USER demo IDENTIFIED BY 123456;

-- 修改密碼 (需高權限用戶執行)
ALTER USER demo IDENTIFIED BY 654321;

-- 密碼過期 (首次登錄強制改密)
ALTER USER demo PASSWORD EXPIRE;

-- 鎖定/解鎖
ALTER USER demo ACCOUNT LOCK;
ALTER USER demo ACCOUNT UNLOCK;

-- 刪除用戶 (CASCADE 級聯刪除其對象)
DROP USER demo CASCADE;

-- 授予系統權限
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO demo;
GRANT CREATE SEQUENCE, CREATE PROCEDURE TO demo;

-- 授予對象權限
GRANT SELECT, INSERT, UPDATE ON scott.emp TO demo;
GRANT ALL ON scott.dept TO demo;
GRANT UPDATE(ename) ON scott.emp TO demo; -- 列級權限

-- 角色管理
CREATE ROLE myrole;
GRANT CREATE TABLE, CREATE VIEW TO myrole;
GRANT myrole TO demo;
REVOKE myrole FROM demo;
DROP ROLE myrole;

-- 收回權限
REVOKE SELECT ON scott.emp FROM demo;

-- 預定義角色：CONNECT(創建會話)、RESOURCE(創建對象)、DBA(所有權限)
-- 注：Oracle 建議自定義角色，預定義角色未來版本可能移除

3. 權限查詢字典視圖


-- 用戶信息
SELECT * FROM DBA_USERS;       -- 所有用戶詳情 (需 DBA)
SELECT * FROM ALL_USERS;       -- 所有用戶簡要
SELECT * FROM USER_USERS;      -- 當前用戶詳情

-- 系統權限
SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM USER_SYS_PRIVS;

-- 角色權限
SELECT * FROM ROLE_SYS_PRIVS;
SELECT * FROM DBA_ROLE_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

-- 對象權限
SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM USER_TAB_PRIVS;

-- 所有角色
SELECT * FROM DBA_ROLES;

-- SYSDBA/SYSOPER 持有者
SELECT * FROM V$PWFILE_USERS;

-- Oracle 所有系統權限列表
SELECT name FROM SYSTEM_PRIVILEGE_MAP;

三、Oracle 體系結構：實例 vs 數據庫

1. 核心概念區分

概念	組成	生命周期	類比
實例	SGA (內存) + 後台進程	啟動→運行→關閉 (內存中)	運行中的程序進程
數據庫	數據文件、控制文件、重做日志文件、參數文件、口令文件、歸檔日志	創建→永久存在 (磁盤上)	安裝目錄+數據文件

2. 內存架構 (SGA 核心組件)

共享池：庫緩存 (SQL/PLSQL 解析樹)、數據字典緩存、PL/SQL 區。
數據庫緩沖區緩存：數據塊副本，LRU 淘汰，DB_BLOCK_SIZE (默認 8K)。
重做日志緩沖區：變更向量，順序寫入聯機重做日志文件。
大池/Java 池/流池：可選，大內存操作、Java 代碼、共享服務器。

3. 關鍵後台進程

進程	全稱	職責
PMON	Process Monitor	清理失敗進程釋放鎖、回滾未提交事務
SMON	System Monitor	實例恢復、合併空閒空間、清理臨時段
DBWn	Database Writer	將髒塊從緩沖區寫入數據文件 (LRU/检查點觸發)
LGWR	Log Writer	將重做日志緩沖區寫入聯機重做日志文件 (提交/1/3滿/3秒/切換觸發)
CKPT	Checkpoint	更新數據文件頭/控制文件检查點信息，縮短恢復時間
ARCn	Archiver	歸檔模式下複製聯機重做日志到歸檔目標
RECO	Recoverer	解決分布式事務失敗

4. 邏輯存儲結構層級


數據庫
 └─ 表空間 — 至少一個 (SYSTEM 必須)，由 1+N 個數據文件組成
      └─ 段 — 表/索引/回滾/臨時段
           └─ 區 — 連續塊組成，空間分配單位
                └─ Oracle 塊 — 最小 I/O 單位 (DB_BLOCK_SIZE)
                     └─ 操作系統塊 — 物理磁盤最小單位

四、SQL 四大語言分類與核心語法速查

1. DDL (數據定義) — 改變結構，隱式提交


CREATE TABLE student (id NUMBER(5), name VARCHAR2(20), birth DATE, score NUMBER(7,2));
ALTER TABLE student ADD (age NUMBER(3));
ALTER TABLE student MODIFY (name VARCHAR2(30));
ALTER TABLE student RENAME COLUMN age TO stu_age;
ALTER TABLE student RENAME TO stu_info;
DROP TABLE stu_info;

2. DML (數據操作) — 改變數據


-- 插入
INSERT INTO demo VALUES (1, '張三', SYSDATE);
INSERT INTO demo (id, name) VALUES (2, '李四'); -- 指定列
INSERT INTO demo VALUES (3, '王五', TO_DATE('2023-01-01','YYYY-MM-DD'));

-- 更新
UPDATE demo SET name='張三豐' WHERE id=1;

-- 刪除
DELETE FROM demo WHERE id=2;           -- 可回滾，不釋放空間
TRUNCATE TABLE demo;                   -- 不可回滾，釋放空間，高水位歸零

3. DCL (數據控制) — 權限管理 (見上文用戶管理)

4. TCL (事務控制)


COMMIT;                    -- 提交，持久化
ROLLBACK;                  -- 回滾到最近 COMMIT
SAVEPOINT sp1;             -- 設置保存點
INSERT ...; SAVEPOINT sp2; INSERT ...; ROLLBACK TO sp2; -- 回滾到 sp2
COMMIT;                    -- sp1 保留的 INSERT 生效

五、核心查詢技巧：從單表到多表、子查詢、分頁

1. 基礎查詢結構與執行順序


SELECT select_list          -- 5
FROM table_list             -- 1
WHERE row_condition         -- 2
GROUP BY group_cols         -- 3
HAVING group_condition      -- 4
ORDER BY sort_cols;         -- 6

2. 多表連接大全

連接類型	語法	結果集	Oracle 老寫法
內連接	`INNER JOIN ... ON`	匹配行	`WHERE a.id = b.id`
左外連接	`LEFT JOIN ... ON`	左表全保留 + 匹配	`WHERE a.id = b.id(+)`
右外連接	`RIGHT JOIN ... ON`	右表全保留 + 匹配	`WHERE a.id(+) = b.id`
全外連接	`FULL JOIN ... ON`	雙表全保留 + 匹配	不支持，需 UNION
交叉連接	`CROSS JOIN`	笛卡爾積	`FROM a, b` 無 WHERE

3. 子查詢分類

單行子查詢：返回 1 行，用 = > < <= 比較。
多行子查詢：返回多行，必須用 IN / ANY / ALL。
相關子查詢：內層引用外層列，逐行執行，EXISTS 常用於檢查存在性。


-- EXISTS 高性能模式：外層少、內層大且有索引
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id);
-- IN 適用：子查詢結果少、主查詢表大有索引
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 WHERE status=1);

4. 分頁查詢三種方案

數據庫	方案	語法示例 (第 2 頁，每頁 10 條)
Oracle 12c+	OFFSET/FETCH	`SELECT * FROM t ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY`
Oracle 11g	ROWNUM 三層嵌套	`SELECT * FROM (SELECT a., ROWNUM rn FROM (SELECT FROM t) a WHERE ROWNUM <= 20) WHERE rn > 10`
Oracle 通用	ROW_NUMBER 分析函數	`SELECT * FROM (SELECT t.*, ROW_NUMBER() OVER(ORDER BY id) rn FROM t) WHERE rn BETWEEN 11 AND 20`
MySQL	LIMIT	`SELECT * FROM t LIMIT 10, 10` (偏移 10，取 10)
SQL Server	OFFSET/FETCH (2012+)	`SELECT * FROM t ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY`

5. 集合操作


UNION        -- 並集，去重
UNION ALL    -- 並集，保留重複
INTERSECT    -- 交集
MINUS        -- 差集 (順序敏感：A MINUS B ≠ B MINUS A)

六、約束設計：數據完整性最後防線

約束	關鍵字	規則	建表/ALTER 示例
非空	NOT NULL	列不可為 NULL	`name VARCHAR2(20) NOT NULL`
主鍵	PRIMARY KEY	唯一 + 非空，表僅一個	`CONSTRAINT pk_stu PRIMARY KEY(id)`
唯一	UNIQUE	值唯一，允許多個 NULL	`CONSTRAINT uk_tel UNIQUE(tel)`
檢查	CHECK	滿足條件表達式	`CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 150)`
外鍵	FOREIGN KEY	引用父表主鍵/唯一鍵	`CONSTRAINT fk_book_pid FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE`

約束管理


-- 添加約束
ALTER TABLE book ADD CONSTRAINT fk_book_pid FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE;
-- 啟用/禁用
ALTER TABLE book ENABLE CONSTRAINT fk_book_pid;
ALTER TABLE book DISABLE CONSTRAINT fk_book_pid;
-- 刪除
ALTER TABLE book DROP CONSTRAINT fk_book_pid;
-- 非空約束無 ADD CONSTRAINT 語法，用 CHECK(col IS NOT NULL) 或 MODIFY
ALTER TABLE person MODIFY (name NOT NULL); -- 11g+ 支持

七、SQL 函數庫：單行、分組、轉換、條件

1. 字符函數


UPPER/LOWER/INITCAP          -- 大小寫/首字母大寫
CONCAT('a','b') / 'a'||'b'   -- 連接
SUBSTR(str, pos, len)        -- 截取，負 pos 從末尾倒數
LENGTH/LENGTHB               -- 字符數/字節數
REPLACE(str, old, new)       -- 替換
INSTR(str, sub, pos, nth)    -- 查找子串位置
LPAD/RPAD(str, n, pad)       -- 左/右填充
TRIM/LTRIM/RTRIM             -- 去空格 (TRIM 可指定字符)

2. 數值函數


ROUND(n, m)   -- 四捨五入，m 可負 (百位/千位)
TRUNC(n, m)   -- 截斷
MOD(m, n)     -- 餘數
CEIL/FLOOR    -- 向上/向下取整
POWER/SQRT    -- 冪/平方根

3. 日期函數


SYSDATE                    -- 當前日期時間 (精確到秒)
MONTHS_BETWEEN(d1, d2)     -- 月份差
ADD_MONTHS(d, n)           -- 加減月
NEXT_DAY(d, '星期一')       -- 下一個指定星期
LAST_DAY(d)                -- 月最後一天
ROUND/TRUNC(date, 'MONTH') -- 日期四捨五入/截斷到月/年

4. 轉換函數


TO_CHAR(date, 'YYYY-MM-DD HH24:MI:SS')  -- 日期轉字符
TO_CHAR(num, 'L999,999.99')             -- 數值轉貨幣格式
TO_NUMBER('1,234.56', '999,999.99')     -- 字符轉數值
TO_DATE('20230101', 'YYYYMMDD')         -- 字符轉日期

5. 通用/條件函數


NVL(expr1, expr2)              -- expr1 為 NULL 返回 expr2
NVL2(expr1, e2, e3)            -- NULL 返回 e3，非 NULL 返回 e2
NULLIF(expr1, expr2)           -- 相等返回 NULL，否則 expr1
COALESCE(e1, e2, ..., en)      -- 返回第一個非 NULL
CASE WHEN cond THEN v1 ELSE v2 END  -- 條件表達式 (可嵌套)
DECODE(col, v1, r1, v2, r2, def)   -- 離散值匹配 (Oracle 專有)

6. 分組函數 (聚合)


COUNT(*) / COUNT(col)    -- 行數 / 非空列數
SUM/AVG/MIN/MAX          -- 忽略 NULL
STDDEV/VARIANCE          -- 標準差/方差
-- 分組查詢模板
SELECT deptno, AVG(sal) FROM emp WHERE hiredate > '1981-01-01' 
GROUP BY deptno HAVING AVG(sal) > 2000 ORDER BY deptno;
-- 嵌套聚合
SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno;

八、PL/SQL 核心：變量、動態 SQL、游標

1. 變量聲明與賦值


DECLARE
    v_name VARCHAR2(20) := 'Init';      -- 直接賦值
    v_sal  NUMBER(10,2) DEFAULT 0;      -- 默認值
    v_max  CONSTANT NUMBER := 1000;     -- 常量
    v_row  emp%ROWTYPE;                 -- 行類型 (表所有列)
    v_cur  SYS_REFCURSOR;               -- 弱類型游標變量
BEGIN
    SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno=7369;
    DBMS_OUTPUT.PUT_LINE(v_name || ':' || v_sal);
END;

2. 標量數據類型映射表

類別	類型	說明
數值	NUMBER(p,s)	通用定點/浮點，p 精度 1-38，s 刻度 -84~127
	BINARY_INTEGER	-2^31~2^31-1，機器整數運算快，子類型 NATURAL/POSITIVE
	PLS_INTEGER	同 BINARY_INTEGER，PL/SQL 引擎優化，性能最佳
	SIMPLE_INTEGER	11g+，不檢查溢出，極速
字符	CHAR(n)	定長，默認 1，最大 32767
	VARCHAR2(n)	變長，最大 32767，PL/SQL 內部可達 32767，表列 4000
	CLOB	大字符對象，4G
日期/時間	DATE	含時間，精確到秒，範圍 4712 BC ~ 9999 AD
	TIMESTAMP	精確到納秒，帶時區版本 TZ/LTZ
	INTERVAL	年月/日秒間隔
邏輯	BOOLEAN	TRUE/FALSE/NULL，僅 PL/SQL 可用，SQL 不支持
LOB	BFILE/BLOB/CLOB/NCLOB	大對象，DBMS_LOB 包操作

3. 動態 SQL (EXECUTE IMMEDIATE) — 運行時構建 SQL


-- 語法
EXECUTE IMMEDIATE dynamic_sql_string
    [INTO define_vars] [USING [IN|OUT|IN OUT] bind_args]
    [{RETURNING|RETURN} INTO bind_args];

-- 關鍵規則
-- 1. DDL/DCL 單行 DML 單行查詢 → EXECUTE IMMEDIATE
-- 2. 多行查詢 → OPEN FOR / FETCH / CLOSE 游標變量
-- 3. 批量 → BULK COLLECT INTO / FORALL
-- 4. 占位符 :name 或 :1 :2，USING 綁定
-- 5. DDL/DCL 執行後自動提交，DML 需手動 COMMIT
-- 6. 動態 PL/SQL 塊結尾需分號，動態 SQL 結尾不需分號
-- 7. Schema 對象名 (表/列) 不能綁定，需拼接字符串
-- 8. NULL 綁定問題：聲明變量不賦值作為 NULL 占位符

-- 示例：動態建表、查詢、批量更新
DECLARE
    v_sql VARCHAR2(200);
    v_cnt NUMBER;
BEGIN
    v_sql := 'CREATE TABLE t AS SELECT * FROM emp WHERE deptno = :dno';
    EXECUTE IMMEDIATE v_sql USING 20; -- DDL 自動提交
    
    v_sql := 'SELECT COUNT(*) FROM emp WHERE deptno = :dno';
    EXECUTE IMMEDIATE v_sql INTO v_cnt USING 20; -- 單行查詢
    
    v_sql := 'UPDATE emp SET sal = sal * 1.1 WHERE deptno = :dno RETURNING sal INTO :newsal';
    -- 批量更新需 FORALL + BULK COLLECT
END;

4. 顯式游標完整生命周期


DECLARE
    CURSOR c_emp(p_deptno NUMBER) IS 
        SELECT empno, ename, sal FROM emp WHERE deptno = p_deptno FOR UPDATE NOWAIT;
    v_emp c_emp%ROWTYPE;
BEGIN
    OPEN c_emp(20);                    -- 1. 打開：執行查詢，鎖定行 (FOR UPDATE)
    LOOP
        FETCH c_emp INTO v_emp;        -- 2. 取數據
        EXIT WHEN c_emp%NOTFOUND;      -- 3. 判斷結束
        IF v_emp.sal < 1500 THEN
            UPDATE emp SET sal = 1500 WHERE CURRENT OF c_emp; -- 4. 當前行更新
        END IF;
    END LOOP;
    CLOSE c_emp;                       -- 5. 關閉，釋放鎖
    COMMIT;
END;

5. 游標屬性

屬性	含義
%FOUND	最近 FETCH 成功返回 TRUE
%NOTFOUND	最近 FETCH 失敗返回 TRUE
%ISOPEN	游標已打開返回 TRUE
%ROWCOUNT	已提取行數

6. 游標 FOR 循環 (隱式 OPEN/FETCH/CLOSE)


BEGIN
    FOR r IN (SELECT * FROM emp WHERE deptno = 20) LOOP
        DBMS_OUTPUT.PUT_LINE(r.ename || ':' || r.sal);
    END LOOP; -- 自動關閉
END;

-- 帶參數游標 FOR 循環
BEGIN
    FOR r IN c_emp(30) LOOP ... END LOOP;

7. 游標變量 (REF CURSOR) — 動態綁定查詢


DECLARE
    TYPE t_cur IS REF CURSOR RETURN emp%ROWTYPE; -- 強類型
    c_var t_cur;
    v_emp emp%ROWTYPE;
BEGIN
    OPEN c_var FOR SELECT * FROM emp WHERE deptno = 10;
    LOOP FETCH c_var INTO v_emp; EXIT WHEN c_var%NOTFOUND; ... END LOOP;
    CLOSE c_var;
    
    -- 同一變量綁定另一查詢
    OPEN c_var FOR SELECT * FROM dept WHERE deptno = 10;
    ...
END;

九、避坑指南與最佳實踐精華

編碼統一：數據庫/實例/會話/客戶端/應用全鏈路 AL32UTF8 / ZHS16GBK 一致。
綁定變量：所有 SQL 使用 :bind，利用庫緩存共享，防注入，提性能。
索引設計：WHERE/JOIN/ORDER BY 列建索引，避免過度索引拖慢 DML。
分頁優化：大數據量用 ROW_NUMBER 分析函數，避免三層 ROWNUM 深嵌套。
事務控制：業務邏輯層控制事務邊界，DAO 不提交/回滾；批量業務分批提交避免長事務。
PL/SQL 異常處理：EXCEPTION WHEN OTHERS THEN log; RAISE; 記錄並拋出，不吞異常。
動態 SQL 安全：Schema 名拼接必須驗證白名單，防注入。
統計信息：定期 DBMS_STATS.GATHER_TABLE_STATS，優化器生成最優計劃。

十、總結

Oracle 數據庫作為企業級應用主流選擇，其實例/數據庫分離架構、多層次內存管理、完善的鎖與一致性機制、豐富的 SQL/PLSQL 功能集，為 Java 應用提供了堅實的數據底座。掌握安裝部署規範、用戶權限最小化、體系結構核心組件、SQL 四大語言精髓、約束保護數據質量、分組/分析函數挖掘價值、PL/SQL 變量動態 SQL 游標實現複雜邏輯，是 Java 後端開發者向高級工程師、架構師進階的必經之路。建議將本文作為項目開發速查手冊，遇到具體場景 (如鎖等待、分頁慢、動態 SQL 注入、游標屬性判斷) 即查即用，知其然更知其所以然。