Forum

PLSQL Program to Di...
 
Notifications
Clear all

PLSQL Program to Display Customer Information for the Given Customer


Sam EDU
Posts: 4
Topic starter
(@testuser)
New Member
Joined: 1 month ago

Write a PLSQL program to display the information of a customer for
the given customer number?

1 Reply
Malathi A
Posts: 47
 Malathi A
(@Malathi A)
Joined: 11 months ago

PLSQL Program to display customer name, city, account number, balance,
act_name from given customer number

DECLARE
TYPE  cust_type IS RECORD
(
custname  cust_dtls.cname%type,
custcity  cust_dtls.city%type,
Actno     cust_act_dtls.actno%type,
actbal    NUMBER,
actname   act_types.act_name%type
);
v_cust_rec   cust_type;
vcid    cust_dtls.cno%type;
begin
VCID:='&VCID';
select 
cd.cname,cd.city,cad.actno,cad.act_bal,at.act_name
 into
 v_cust_rec
from cust_dtls cd,cust_act_dtls cad,act_types at
where cd.cno=vcid
and
(
cd.cno=cad.cno
and
cad.act_type=at.act_type
);

dbms_output.put_line(chr(10)||
'  Given customer Id:   '||vcid||chr(10)||
'________________________________'||chr(10)||
' Name:     '||v_cust_rec.custname||chr(10)||
' City:        '||v_cust_rec.custcity||chr(10)||
' Actname: '||v_cust_rec.actname||chr(10)||
' Actno:      '||v_cust_rec.actno||chr(10)||
' Bal:          '||v_cust_rec.actbal
);
end ;

 

declare
	
	vname varchar2(20);
	vsal number(5);
	vjob varchar2(20);
	vjdate date;
	vcomm varchar2(10);
	vdeptno number(3);
	BEGIN
	select ename,sal,job,hiredate,nvl(to_char(comm),'N/A'),deptno INTO
		vname,vsal,vjob,vjdate,vcomm,vdeptno
	from emp
	where empno=7521;
	dbms_output.put_line(' Info of 7521');
	dbms_output.put_line('---------------');	
	dbms_output.put_line('Empname: '||vname);
	dbms_output.put_line('Sal: '||vsal);
	dbms_output.put_line('Desg: '||vjob);
	dbms_output.put_line('Joined Date: '||vjdate);
	dbms_output.put_line('comm: '||vcomm);
	dbms_output.put_line('Working under deptno: '||vdeptno);
	END;
	/

Ex:

declare
vname  varchar2(10);
vcost  number(7);
vmfg   date;
vwarr  varchar2(20);
vcmp   varchar2(10);
begin
select prod_name,cost,mfg,warrenty,comp_code
              into
         vname,vcost,vmfg,vwarr,vcmp
from prod_dtls
where prod_code='DMBLY';
dbms_output.put_line
(' Prod code: DMBLY');
dbms_output.put_line
('--------------------');
dbms_output.put_line
('Name: '||vname);
dbms_output.put_line
('cost: '||vcost);
dbms_output.put_line
(' Mfg Date: '||vmfg);
dbms_output.put_line
(' Warrenty:  '||vwarr);
dbms_output.put_line
('Comp Code: '||vcmp);
end;



Ex:
declare
vcname  varchar2(20);
vactname varchar2(20);
vbal  number(6);
begin
select cd.cname,at.act_name,cad.act_bal
         into
       vcname,vactname,vbal
from cust_dtls cd, cust_act_dtls cad, act_types at
where cd.cno='cust-2'
and
( cd.cno=cad.cno  and cad.act_type=at.act_type);
dbms_output.put_line
(chr(10)||
'Customer Account Information: cust-2'||chr(10)||
'**************************************'||chr(10)||
'Customer Name: '||vcname||chr(10)||
'Account Name:  '||vactname||chr(10)||
'Balance:       '||vbal||chr(10)||
'----------------------------------------'
);
end;

Ex:
declare
TYPE  prod_type  IS RECORD
(
prodname  prod_dtls.prod_name%type,
prodcost    prod_dtls.cost%type,
prodwarr   prod_dtls.warrenty%type,
compname comp_dtls.comp_name%type
);
prodrec  prod_type;
vpid  prod_dtls.prod_code%type;
begin
vpid:='&vpid';
select p.prod_name,p.cost,p.warrenty,
          c.comp_name
into  prodrec
from prod_dtls p, comp_dtls c
where p.prod_code=vpid
and  p.comp_code=c.comp_code;
dbms_output.put_line
(chr(10)||
' Given prod code:  '||vpid||chr(10)||
'Name:  '||prodrec.prodname||chr(10)||
'Cost:  '||prodrec.prodcost||chr(10)||
'Warr: '||prodrec.prodwarr||chr(10)||
'Comp name:  '||prodrec.compname
);
end;
	

 

Reply
Share: