在變數聲明中使用%TYPE - PolarDB

本文介紹如何在變數聲明中使用%TYPE。

簡介

SPL程式中聲明用於儲存來自資料庫中的表的值的變數，為了確保表列與SPL變數之間的相容性，二者的資料類型應相同。如果您修改了列的資料類型，則需要更改相應的SPL程式中的變數。此時您可以使用列屬性%TYPE，無需將特定列資料類型編碼為變數聲明。使用圓點標記法限定列名或以前聲明的變數的名稱必須指定為%TYPE的首碼。將作為%TYPE首碼的列或變數的資料類型分配給要聲明的變數。如果給定列或變數的資料類型發生更改，則新資料類型將與變數相關聯，而無需修改聲明代碼。

說明 %TYPE屬性還可以與形參聲明一起使用。

文法

name { { table | view }.column | variable }%TYPE;

參數

參數名稱	描述
name	分配給變數的標識符或需要聲明的形參。
column	table或view中列的名稱。
variable	通過name標識變數之前聲明的變數的名稱。

說明變數不繼承列的任何其他屬性。例如，可以使用NOT NULL子句、DEFAULT子句或分配運算子:=在列上指定屬性。

樣本

以下樣本中，使用員工編號查詢emp表，查看該員工所在部門的平均工資，然後再將所選員工的工資與部門平均工資進行比較。

CREATE OR REPLACE PROCEDURE emp_sal_query (
    p_empno         IN NUMBER
)
IS
    v_ename         VARCHAR2(10);
    v_job           VARCHAR2(9);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_deptno        NUMBER(2);
    v_avgsal        NUMBER(7,2);
BEGIN
    SELECT ename, job, hiredate, sal, deptno
        INTO v_ename, v_job, v_hiredate, v_sal, v_deptno
        FROM emp WHERE empno = p_empno;
    DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || v_job);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || v_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || v_sal);
    DBMS_OUTPUT.PUT_LINE('Dept #     : ' || v_deptno);

    SELECT AVG(sal) INTO v_avgsal
        FROM emp WHERE deptno = v_deptno;
    IF v_sal > v_avgsal THEN
        DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the '
            || 'department average of ' || v_avgsal);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the '
            || 'department average of ' || v_avgsal);
    END IF;
END;

您也可以將上述預存程序改寫為以下模式，無需將emp表資料類型顯式編碼到預存程序的聲明部分中。

CREATE OR REPLACE PROCEDURE emp_sal_query (
    p_empno         IN emp.empno%TYPE
)
IS
    v_ename         emp.ename%TYPE;
    v_job           emp.job%TYPE;
    v_hiredate      emp.hiredate%TYPE;
    v_sal           emp.sal%TYPE;
    v_deptno        emp.deptno%TYPE;
    v_avgsal        v_sal%TYPE;
BEGIN
    SELECT ename, job, hiredate, sal, deptno
        INTO v_ename, v_job, v_hiredate, v_sal, v_deptno
        FROM emp WHERE empno = p_empno;
    DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || v_job);
    DBMS_OUTPUT.PUT_LINE('Hire Date  : ' || v_hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary     : ' || v_sal);
    DBMS_OUTPUT.PUT_LINE('Dept #     : ' || v_deptno);

    SELECT AVG(sal) INTO v_avgsal
        FROM emp WHERE deptno = v_deptno;
    IF v_sal > v_avgsal THEN
        DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the '
            || 'department average of ' || v_avgsal);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the '
            || 'department average of ' || v_avgsal);
    END IF;
END;

說明

p_empno顯式使用%TYPE定義的形參。
v_avgsal引用另一個變數而非表列的%TYP的用法。

此預存程序的輸出結果如下：

EXEC emp_sal_query(7698);

Employee # : 7698
Name       : BLAKE
Job        : MANAGER
Hire Date  : 01-MAY-81 00:00:00
Salary     : 2850.00
Dept #     : 30
Employee's salary is more than the department average of 1566.67