不用記錄類型的例子
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可以用
