What does "%Type" mean in Oracle sql?
Oracle (and PostgreSQL) have:
- %TYPE
- %ROWTYPE
%TYPE
%TYPE
is used to declare variables with relation to the data type of a column in an existing table:
DECLARE v_id ORDERS.ORDER_ID%TYPE
The benefit here is that if the data type changes, the variable data type stays in sync.
Reference: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundamentals.htm#i6080
%ROWTYPE
This is used in cursors to declare a single variable to contain a single record from the resultset of a cursor or table without needing to specify individual variables (and their data types). Ex:
DECLARE
CURSOR c1 IS
SELECT last_name, salary, hire_date, job_id
FROM employees
WHERE employee_id = 120;
-- declare record variable that represents a row fetched from the employees table
employee_rec c1%ROWTYPE;
BEGIN
-- open the explicit cursor and use it to fetch data into employee_rec
OPEN c1;
FETCH c1 INTO employee_rec;
DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);
END;
/
Apart from the purpose pointed by OMG Ponies, %TYPE
is also used for inheriting the same data type used by a previously declared variable.
The syntax is :
DECLARE
L_num NUMBER(5,2) NOT NULL default 3.21;
L_num_Test L_num%TYPE := 1.123;
So there is no need to declare the data type for the second variable i.e L_num_Test
.
Comment if anyone needs further clarification regarding this topic.
Reference: https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#BEIIGBBF