萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 核心知識八大模塊速成:表空間、異常處理、安全管理、同義詞、DB Link、游標、Record/RowType

1. 表空間

-- 建立表空間 mytabs,10MB 初始大小
CREATE TABLESPACE mytabs DATAFILE 'C:\oracle\oradata\mydb\mytabs1.dbf' SIZE 10M;

-- 修改用戶預設表空間
ALTER USER zgl DEFAULT TABLESPACE mytabs;

-- 授予無限表空間配額
GRANT UNLIMITED TABLESPACE TO zgl;

一個表空間對應多個物理 .dbf 檔案。

2. 異常處理 (EXCEPTION)

CREATE OR REPLACE PROCEDURE pro_test_exception (vid IN VARCHAR2) IS
    userName VARCHAR2(30);
BEGIN
    SELECT name INTO userName FROM t_user WHERE id = vid;
    DBMS_OUTPUT.PUT_LINE(userName);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('沒有查到資料!');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('返回了多行資料!');
END;

3. 安全管理 (以 SYSDBA 登入)

用戶帳戶控制

ALTER USER zgl ACCOUNT LOCK;           -- 鎖定
ALTER USER zgl IDENTIFIED BY zgl11;    -- 改密碼
ALTER USER zgl ACCOUNT UNLOCK;         -- 解鎖
ALTER USER zgl DEFAULT TABLESPACE tt;  -- 改預設表空間
CREATE USER qqq IDENTIFIED BY qqq123 DEFAULT TABLESPACE tt;

權限授予/撤銷

GRANT CONNECT TO qqq;
GRANT EXECUTE ON zgl.proc01 TO test;
GRANT CREATE USER TO zgl;
REVOKE CREATE USER FROM zgl;

角色管理

CREATE ROLE myrole;
GRANT CONNECT TO myrole;
GRANT SELECT ON zgl.t_user TO myrole;
GRANT myrole TO test;

概要文件

限制登入失敗次數、密碼複雜度、閒置時間等全域策略。

4. 同義詞

CREATE PUBLIC SYNONYM xxx FOR myuser.t_user;
CREATE SYNONYM t_user FOR myuser.t_user;
SELECT * FROM dba_synonyms WHERE table_name = 'T_USER';

5. 跨資料庫查詢 (Database Link)

CREATE DATABASE LINK dblinkzgl
CONNECT TO myuser IDENTIFIED BY a USING 'mydb';

SELECT * FROM t_user@dblinkzgl;

6. 游標

隱性游標 FOR 迴圈

CREATE OR REPLACE PROCEDURE pro_test_cursor IS
    userRow t_user%ROWTYPE;
    CURSOR userRows IS SELECT * FROM t_user;
BEGIN
    FOR userRow IN userRows LOOP
        DBMS_OUTPUT.PUT_LINE(userRow.Id || ',' || userRow.Name || ',' || userRows%ROWCOUNT);
    END LOOP;
END;

顯性游標 OPEN/FETCH/CLOSE

CREATE OR REPLACE PROCEDURE pro_test_cursor_oneRow (vid IN NUMBER) IS
    userRow t_user%ROWTYPE;
    CURSOR userCur IS SELECT * FROM t_user WHERE id = vid;
BEGIN
    OPEN userCur;
    FETCH userCur INTO userRow;
    IF userCur%FOUND THEN
        DBMS_OUTPUT.PUT_LINE(userRow.id || ',' || userRow.Name);
    END IF;
    CLOSE userCur;
END;

7. RECORD 型別 (自訂結構)

CREATE OR REPLACE PROCEDURE pro_test_record (vid IN VARCHAR2) IS
    TYPE userRow IS RECORD (
        id t_user.id%TYPE,
        name t_user.name%TYPE
    );
    realRow userRow;
BEGIN
    SELECT id, name INTO realRow FROM t_user WHERE id = vid;
    DBMS_OUTPUT.PUT_LINE(realRow.id || ',' || realRow.name);
END;

8. %ROWTYPE 錨定型別

CREATE OR REPLACE PROCEDURE pro_test_rowType (vid IN VARCHAR2) IS
    userRow t_user%ROWTYPE;
BEGIN
    SELECT * INTO userRow FROM t_user WHERE id = vid;
    DBMS_OUTPUT.PUT_LINE(userRow.id || ',' || userRow.name);
END;

學習路徑建議

  1. 掌握表空間/用戶/權限 → 環境建置基礎
  2. 熟練游標與 %ROWTYPE → 程式化開發核心
  3. 理解異常處理與同義詞/DB Link → 企業級應用擴展
copyright © 萬盛學電腦網 all rights reserved