AntDB-Oracle Compatibility Developer's Manual P4–14

Using %TYPE in Variable Declarations

Usually variables declared in SPL programs are used to hold data obtained from data tables. To ensure compatibility between column values and SPL variables, the data types of both should be the same. However, it often happens that the table definition changes. If the data type of a column in a table changes, then the type of the variable in the corresponding SPL program should also change to the corresponding type.

Instead of declaring the variable as a property of the data column when writing the program, we can specify the type as the column property plus %TYPE when declaring the variable. A column name qualified with "." qualified column name or the name of a variable that has been declared must be specified with the %TYPE prefix. The data type of the declared variable is the %TYPE data type prefixed with the column or variable.

Note: The %TYPE attribute can also be used as a declaration of formal parameters.

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

name is the identifier assigned to the declared variable. column is the name of the column in the data table or view.

variable is the variable that has been declared before using the parameter name to identify the variable.

**Note: **Variables do not inherit other attributes of the column, such as the NOT NULL clause or DEFAULT clause defined on the column.

The procedure in the following example queries and displays data about employees in the emp table using employee numbers, the average salary of all employees in the employee's department, and compares the salary of the selected employee with the average salary of the department.

Prepare the test table and test data.

CREATE TABLE emp (
    empno NUMBER(4),
    ename VARCHAR2(10),
    job VARCHAR2(9),
    mgr NUMBER(4),
    hiredate DATE,
    sal NUMBER(7,2),
    comm NUMBER(7,2),
    deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,20);

Create the stored procedure.

\set PLSQL_MODE on
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;
/
\set PLSQL_MODE off

Here is another way to specify the data type of table emp with an implicit status in the declaration section of the procedure.

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_sal_query (p_empno IN NUMBER)
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 ;

/
\set PLSQL_MODE off

v_avgsal illustrates the use of a variable instead of using the column name of the table plus %TYPE.

The following is the output of the stored procedure after execution.

postgres=# select emp_sal_query(7369);
NOTICE:  Employee # : 7369
NOTICE:  Name : SMITH
NOTICE:  Job : CLERK
NOTICE:  Hire Date : 1980-12-17 00:00:00
NOTICE:  Salary : 800
NOTICE:  Dept # : 20
NOTICE:  Employee's salary does not exceed the department average of 800
 EMP_SAL_QUERY
---------------

(1 row)