本文介绍如何在变量声明中使用 %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 说明引用另一个变量而非表列的 %TYPE 的用法。

下面是执行此存储过程的示例输出。

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