異質欄位組成的邏輯單位,類似 C 結構體或資料表一列。
DECLARE
TYPE dept_rec_type IS RECORD (
deptno NUMBER(2),
dname VARCHAR2(14),
loc DEPT.LOC%TYPE
);
v_dept dept_rec_type;
BEGIN
v_dept.deptno := 30;
v_dept.dname := 'SALES';
v_dept.loc := 'CHICAGO';
DBMS_OUTPUT.PUT_LINE(v_dept.dname);
END;
SELECT deptno, dname, loc INTO v_dept FROM DEPT WHERE DEPTNO = 30;
SELECT dname, loc INTO v_dept.dname, v_dept.loc FROM DEPT WHERE DEPTNO = 30;
v_dept.deptno := 30;
DECLARE
v_dept DEPT%ROWTYPE;
BEGIN
SELECT * INTO v_dept FROM DEPT WHERE DEPTNO = 30;
DBMS_OUTPUT.PUT_LINE(v_dept.DNAME);
END;
優點:DDL 變更自動同步、程式碼簡潔、效能等同顯式定義。
TYPE emp_detail_rec IS RECORD (
emp_rec EMP%ROWTYPE,
dept_rec DEPT%ROWTYPE
);
TYPE dept_tab_type IS TABLE OF DEPT%ROWTYPE INDEX BY PLS_INTEGER;
v_depts dept_tab_type;
SELECT * BULK COLLECT INTO v_depts FROM DEPT;
PROCEDURE process_dept(p_dept IN OUT DEPT%ROWTYPE) IS
BEGIN
p_dept.LOC := UPPER(p_dept.LOC);
END;
%ROWTYPE 變數佔用記憶體 = 該表所有欄位大小總和,避免在大表大量宣告。SELECT * INTO v_rec 若表結構變更 (增/減欄位) 需重新編譯,否則執行期報 ORA-06504。v_rec1 := v_rec2) 需結構完全相容 (欄位名、型別、順序)。IF v_rec1 = v_rec2 THEN 會編譯錯誤),需逐欄位比較。