Forum

PLSQL Program Displ...
 
Notifications
Clear all

PLSQL Program Display EMPLOYEE Information for The Given Employee ID


Sam EDU
Posts: 4
Topic starter
(@testuser)
New Member
Joined: 7 months ago

Write a program to display the information of employee for
the given employee id?

1 Reply
KV Kumar
Posts: 27
Admin
(@vinodkrsetty)
Member
Joined: 2 years ago

You can try this PLSQL procedure to display the information of employee for the given employee id

	declare
	--Dynamic declaration of variable
	veno   emp.empno%type;
	--Declaring table based record type variable 
	emprec   emp%rowtype;	
	begin
	veno:='&veno';
	select * into emprec from emp
	where empno=veno;
	dbms_output.put_line
	(chr(10)||' Emp id:  '||veno||chr(10)||
         '****************************'||chr(10)||
         'Name: '||emprec.ename||chr(10)||
         'Desg: '||emprec.job||chr(10)||
         'Salary: '||emprec.sal||chr(10)||
         'Join Dt: '||emprec.hiredate||chr(10)||
         'Comm:   '||emprec.comm||chr(10)||
         'Deptno:  '||emprec.deptno||chr(10)||
         'Working under manager_Id:   '||emprec.mgr
        );
        end;

 

TYPE COMPATIBILITY KEYWORD:- %TYPE

we can declare the variables dynamically/ Implicitly
with the data type of column, by using %TYPE.
It eliminates Data type and size incompatibility issues.

syn:
var table_name.col_name%TYPE;

Ex: vdno emp.deptno%type;

In the above, the %TYPE is taking the data type and size of the column DEPTNO from the table EMP and these things will be applicable to the variable "vdno".

%ROWTYPE

It is used to declare a variable as a RECORD type variable.
This type of variable is able to store one record from one table.
It is reducing the number of variable declarations.

syn:	
   varname        tablename%ROWTYPE;

Ex:	emp_rec	   emp%rowtype;

Ex:	prod_info    prod_dtls%rowtype;

		emp_rec
		  |
-----------------------------------------------------------------------
|	|		|		|		|		|	|
empno	ename		sal		job		hiredate	comm	deptno

 

SAVE A RECORD INTO COMPOSITE VARIABLE:-

Ex:
select * into emp_rec from emp where empno=7788;

How to ACCESS individual values from the COMPOSITE TYPE variable?

syn:	IN  any output stmt,use the below syntax

	var_name . colname;

Ex:	dbms_output.put_line
	(' emp salary :'|| emp_rec . sal);

Don't specify like this

dbms_output.put_line(' emp Info :'|| emp_rec );

 

Reply
Share: