在变量声明中使用%TYPE - 云原生数据库 PolarDB

简介

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