[Oracle] 第17章 集合-记录类型

---

虽然PL/SQL提供了 ROWTYPE 的记录类型，但是 ROWTYPE 只能根据已有的表来决定复合类型，而记录类型可以由用户定义组成。

DECLARE
    V_EMP_EMPNO EMP.EMPNO%TYPE;

    TYPE EMP_TYPE IS RECORD(
        ENAME     EMP.ENAME%TYPE,
        JOB       EMP.JOB%TYPE,
        HIREDATE  EMP.HIREDATE%TYPE,
        SAL       EMP.SAL%TYPE,
        COMM      EMP.COMM%TYPE
    );

    V_EMP EMP_TYPE;
BEGIN
    V_EMP_EMPNO  := &inputEmpno;

    SELECT ENAME, JOB, HIREDATE, SAL, COMM
      INTO V_EMP
      FROM EMP
     WHERE EMPNO = V_EMP_EMPNO;

EXCEPTION
    WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('WRONG:' || SQLCODE || SQLERROR);
END;

直接声明的类型

DECLARE
    TYPE DEPT_TYPE IS RECORD(
        DEPTNO    DEPT.DEPTNO%TYPE := 80,
        DNAME     DEPT.DNAME%TYPE,
        LOC       DEPT.LOC%TYPE
    );

    V_DEPT   DEPT_TYPE;
BEGIN
    V_DEPT.DNAME := 'MLDN';
    V_DEPT.LOC   := 'peking';
EXCEPTION
    WHEN OTHERS THEN 
        DBMS_OUTPUT('WRONG:'|| SQLCODE || SQLERM);
END;

嵌套使用

DECLARE
    TYPE DEPT_TYPE IS RECORD(
        DEPTNO    DEPT.DEPTNO%TYPE,
        DNAME     DEPT.DNAME%TYPE,
        LOC       DEPT.LOC%TYPE
    );

    TYPE EMP_TYPE IS RECORD(
        ENAME     EMP.ENAME%TYPE,
        JOB       EMP.JOB%TYPE,
        HIREDATE  EMP.HIREDATE%TYPE,
        SAL       EMP.SAL%TYPE,
        COMM      EMP.COMM%TYPE,
        DEPT      DEPT_TYPE
    );

    V_EMP EMP_TYPE;
BEGIN
    V_EMP_EMPNO  := &inputEmpno;

    SELECT E.ENAME,
           E.JOB,
           E.HIREDATE,
           E.SAL,
           E.COMM,
           D.DEPTNO,
           D.DNAME,
           D.LOC
           INTO 
           V_EMP.ENAME,
           V_EMP.JOB,
           V_EMP.HIREDATE,
           V_EMP.SAL,
           V_EMP.COMM,
           V_EMP.DEPTNO,
           V_EMP.DEPT.DEPTNO,
           V_EMP.DEPT.DNAME,
           V_EMP.DEPT.LOC      
      FROM EMP E, 
      LEFT JOIN DEPT D
        ON E.DEPTNO = D.DEPTNO
     WHERE E.EMPNO = V_EMP_EMPNO;
EXCEPTION
    WHEN OTHERS THEN 
        DBMS_OUTPUT('WRONG:'|| SQLCODE || SQLERM);
END;