Menu
What is the use of the following declarations, and where will we use these types of declarations?
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;
529
% ROWTYPE is to be used whenever query returns a entire row of a table or view. TYPE rec RECORD is to be used whenever query returns columns of differenttable or views and variables.
Example# %TYPE : Used to declare a field with the same type as that of a specified table's column. %ROWTYPE: Used to declare a record with the same types as found in the specified table, view or cursor (= multiple columns).
PostgreSQL uses record type variables which simply act as placeholders for rows of a result set, similar to a row type variable. However, unlike row type variables, they do not have a predefined structure. Their structure is only determined after assigning a row to them.
It is an attribute which is used for anchoring. Example- the variable m_empno has same data type as the column empno in table emp. %ROWTYPE- the %Rowtype attribute lets you declare a record that represents a row in the table. The fields of the row have same name and data types as column in the view.
From PostgreSQL's documentation:
TYPE provides the data type of a variable or table column. You can use this to declare variables that will hold database values. For example, let's say you have a column named user_id in your users table. To declare a variable with the same data type as users.user_id you write: user_id users.user_id%TYPE;.
ROWTYPE: A variable of a composite type is called a row variable (or row-type variable). Such a variable can hold a whole row of a SELECT or FOR query result, so long as that query's column set matches the declared type of the variable. The individual fields of the row value are accessed using the usual dot notation, for example rowvar.field.
RECORD: Record variables are similar to row-type variables, but they have no predefined structure. They take on the actual row structure of the row they are assigned during a SELECT or FOR command. The substructure of a record variable can change each time it is assigned to. A consequence of this is that until a record variable is first assigned to, it has no substructure, and any attempt to access a field in it will draw a run-time error.
See the link for more in-depth examples.
178
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
