Forum

Forums
PL SQL
Other Documents
PLSQL Program to Di...
 
Share:
Notifications
Clear all

PLSQL Program to Display Customer Information for the Given Customer

 
    Last Post
RSS

Posts: 4
 Sam EDU
Topic starter
Jun 08, 2021 2:36 pm
(@testuser)
New Member
Joined: 4 years ago

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

This topic was modified 4 years ago by KV Kumar
Topic Tags
plsql customer informatio given customer
1 Reply
Posts: 46
 Malathi A
Guest
Jun 11, 2021 1:20 pm
(@Malathi A)
Eminent Member
Joined: 5 years 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;
This post was modified 4 years ago 2 times by KV Kumar
Reply

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Topic Tags:  plsql customer informatio (1) , given customer (1) ,
Share: