集合-記錄類型 (PL/SQL 四)

不用記錄類型的例子

DECLARE
    xxx_deptno DEPT.DEPTNO%TYPE;
    xxx_dname DEPT.DNAME%TYPE;
    xxx_loc DEPT.LOC%TYPE;
BEGIN
    SELECT DEPTNO, DNAME, LOC INTO xxx_deptno, xxx_dname, xxx_loc
    FROM DEPT
    WHERE DEPTNO = 40;
    
    DBMS_OUTPUT.PUT_LINE('xxx_deptno=' || xxx_deptno);
    DBMS_OUTPUT.PUT_LINE('xxx_dname=' || xxx_dname);
    DBMS_OUTPUT.PUT_LINE('xxx_loc=' || xxx_loc);
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20111, '找不到資料！');
END;

※如果有20個以上的欄位就要宣告20個變數，比較麻煩

※本來執行錯誤會直接跳EXCEPTION，而EXCEPTION用RAISE_APPLICATION_ERROR一樣會跳，只不過訊息的key和value變成自己打的

※宣告記錄類型

DECLARE
    TYPE xxx_record IS RECORD(
        a DEPT.DEPTNO%TYPE,
        b DEPT.DNAME%TYPE,
        c DEPT.LOC%TYPE
    );
    xxx_ooo xxx_record;
BEGIN
    SELECT DEPTNO, DNAME, LOC INTO xxx_ooo
    FROM DEPT
    WHERE DEPTNO = 40;
    
    DBMS_OUTPUT.PUT_LINE('xxx_deptno=' || xxx_ooo.a);
    DBMS_OUTPUT.PUT_LINE('xxx_dname=' || xxx_ooo.b);
    DBMS_OUTPUT.PUT_LINE('xxx_loc=' || xxx_ooo.c);
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20111, '找不到資料！');
END;

※宣告記錄類型後，再宣告一個變數來接收記錄類型

※這個例子也可以用第一篇的%ROWTYPE做到，只不過這裡較靈活，假設有20個欄位，這裡可以只使用5個(看需求)，%ROWTYPE不行

※給記錄類型賦值

DECLARE
    TYPE xxx_record IS RECORD(
        a DEPT.DEPTNO%TYPE,
        b DEPT.DNAME%TYPE,
        c DEPT.LOC%TYPE := 'ccc'
    );
    xxx_ooo xxx_record;
BEGIN
    xxx_ooo.a := 50;
    
    DBMS_OUTPUT.PUT_LINE('xxx_deptno=' || xxx_ooo.a);
    DBMS_OUTPUT.PUT_LINE('xxx_dname=' || xxx_ooo.b);
    DBMS_OUTPUT.PUT_LINE('xxx_loc=' || xxx_ooo.c);
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20111, '找不到資料！');
END;

※結果：
xxx_deptno=50
xxx_dname=
xxx_loc=ccc

※可以在record賦值，也可以在外面賦值，沒賦值到的就是空(length=0)

※記錄類型裡的記錄類型

DECLARE
    TYPE xxx_record IS RECORD(
        a DEPT.DEPTNO%TYPE,
        b DEPT.DNAME%TYPE,
        c DEPT.LOC%TYPE
    );
    
    TYPE ooo_record IS RECORD(
        d EMP.EMPNO%TYPE,
        e EMP.ENAME%TYPE,
        f xxx_record
    );
    xxx_ooo ooo_record;
    ooo_xxx xxx_record;
BEGIN
    SELECT e.EMPNO, d.DNAME
    INTO xxx_ooo.d, ooo_xxx.b
    FROM DEPT d, EMP e
    WHERE d.DEPTNO = 40 AND e.EMPNO = 7369;
    
    DBMS_OUTPUT.PUT_LINE('empno=' || xxx_ooo.d);
    DBMS_OUTPUT.PUT_LINE('dname=' || ooo_xxx.b);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('找不到資料！');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('找到太多資料！');
END;

※雖然很複雜，但應該還是看得懂

※新增

DECLARE
    TYPE xxx_record IS RECORD(
        a DEPT.DEPTNO%TYPE,
        b DEPT.DNAME%TYPE,
        c DEPT.LOC%TYPE
    );
    xxx_ooo xxx_record;
BEGIN
    xxx_ooo.a := 50;
    xxx_ooo.b := '業務部';
    xxx_ooo.c := '舊金山';
    
    INSERT INTO DEPT VALUES xxx_ooo;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('新增成功');
END;

※要注意順序要和資料庫一樣或者INTO()括號裡的順序要和宣告的順序一樣

※修改

DECLARE
    TYPE xxx_record IS RECORD(
        a DEPT.DEPTNO%TYPE,
        b DEPT.DNAME%TYPE,
        c DEPT.LOC%TYPE
    );
    xxx_ooo xxx_record;
BEGIN
    xxx_ooo.a := 70;
    xxx_ooo.b := '業務部';
    xxx_ooo.c := '舊金山';
    
    UPDATE DEPT SET ROW = xxx_ooo WHERE DEPTNO = 50;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('修改成功');
END;

※修改和SQL不太一樣，有個ROW可以用