Customer(cno, cname, city)
SQL> create table customer52(cno number(5) primary key,cname varchar2(10),city varchar2(10));
Loan(lno,loanamt,no_of_years)
******************************************************************************
SQL> create table customer52(cno number(5) primary key,cname varchar2(10),city varchar2(10));
Table
created.
SQL> desc customer52;
SQL>
insert into customer52(1,Sanjay,pune);
1
row created.
SQL>
insert into customer52(2,Yogesh,mumbai);
1
row created.
SQL>
insert into customer52(3,Ritesh,pune);
1
row created.
SQL>
insert into customer52(4,Rahul,mumbai);
1
row created.
SQL>
insert into customer52(5,Amol,Baramati);
1
row created.
SQL> select *
from customer52;
SQL>
create table loan52(lno number(5) primary key,loan_amt number(10)
check(loan_amt>0),
no_of_year number(10),cno number(5)
references customer52);
Table
created.
SQL> desc
loan52;
SQL>
insert into loan52 values(101,10000,2,1);
1
row created.
SQL>
insert into loan52 values(102,20000,3,2);
1
row created.
SQL>
insert into loan52 values(103,28900,4,3);
1
row created.
SQL>
insert into loan52 values(104,30000,1,4);
1
row created.
SQL>
insert into loan52 values(105,50000,2,5);
1
row created.
SQL> select *
from loan52;
1)create or
replace pl/sql function to display who have taken maximum loan from pune city
SQL>create
or replace function f1
2
return varchar
3 as
4
v_name varchar2(20);
5 begin
6
select customer52.cname into v_name from customer52,loan52
where customer52.cno=loan52.cno and
loan52.loan_amt=(select
max(loan52.loan_amt)from customer52,loan52
where
customer52.cno=loan52.cno and customer52.city='pune');
7
return v_name;
8 end;
9 /
Function
created.
SQL>
DECLARE
2 c
varchar2(10);
3 BEGIN
4 c:=f1();
5
dbms_output.put_line(‘Maximum loan-‘||c);
6 END;
7 /
***Output***
Maximum
loan-Ritesh
PL/SQL
procedure successfully completed.
2) create or
replace pl/sql function to find total
loan_amt from Mumbai city
SQL>
create or replace function f2
2
return number
3 IS
4
v_total number(10);
5 BEGIN
6
select sum(loan52.loan_amt) into v_total from customer52,loan52
7
where customer52.city='mumbai' and customer52.cno=loan52.cno;
8
return v_total;
9 END;
10 /
Function
created.
SQL>
DECLARE
2 c
number(10);
3 BEGIN
4 c:=
f2();
5
dbms_output.put_line(‘Total loan_amt-‘|| c);
6 END;
7 /
***OUTPUT***
Total
loan_amt-50000
PL/SQL
procedure successfully completed.
