30. COMPOSITE DATATYPES

Composite datatypes

a) %type  

Ex:- Write a program to retrieve and display the employee name by providing employee number?

declare

vno number(5):=&n;

vname varchar2(10);

begin

select ename into vname from emp where empno = vno;

dbms_output.put_line('employee name'||vname);

end;

/

Output:-

·         It is used to provide column data type to a variable.

·         No need to remember the column data type

·         It is dynamic in nature

Syn:  Variablename tablename.columnname%type;

Ex:- variable emp.ename%type;

   declare

vno emp.empno%type:=&n;

vname emp.ename%type;

begin

select ename into vname from emp where empno = vno;

dbms_output.put_line('employee name is'||vname);

end;/

Output:

Disadvantage:-  Not possible to store entire record

-------------------------------------------------------------------------------------

b) %row type: It assigns the entire column data types of a table. Useful to store entire record.

Syn:- Variable tablename%rowtype;

Ex:

declare

vno emp.empno%type:=&n;

vrow emp%rowtype;

begin

select * into vrow from emp where empno = vno;

dbms_output.put_line(vrow.ename||' '||vrow.sal);

end;

/

Output:

Disadvantage:-  By using %rowtype it is not possible to store table record along with user data or user information.