CODEWITHSUNDEEP
sqloracle-databaseplsql
Matt M
Matt M

Reputation: 3779

What does "%Type" mean in Oracle sql?

I'm getting my first experience with Oracle and TOAD (I know SSMS). I came across this "%Type" next to an input parameter in an update procedure and I have no idea what it is or what it means. I found links on Google related to "%Rowtype". Is the same thing or something entirely different?

If this is vague, I apologize. As always, thanks for the help.

Upvotes: 40

Views: 88000

Answers (3)

Ali Raza
Ali Raza

Reputation: 1

%Type with its use :

  1. %Type datatype is use to define the variable as column name datatype for specified table.
  2. Lets take a small example of customer table where Customer_name datatype is varchar2(30). If you give the %type then the specified column or variable will take the datatype as varchar2(30).
  3. Low to define this? vCustomerName Customer.Customer_Name%TYPE; In the above define statement the datatype of Customer_Name will be assign to the variable named vCustomerName. Real life example : If you want to create a simple PL SQL block to check the assignment for the datatype from direct table. PL SQL block :

Upvotes: 0

Roshan jha
Roshan jha

Reputation: 171

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

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332591

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;
/

Upvotes: 63

Related Questions