Politician (pno, pname, description)
Party (partycode, partyname)
******************************************************************************
SQL>
create table party52(party_code number(5) primary key,party_name varchar2(10)
NOT NULL);
Table
created.
SQL>
desc party52;
SQL>
insert into party52 values(101,'MNS');
1
row created.
SQL>
insert into party52 values(102,'RSP');
1
row created.
SQL>
insert into party52 values(103,'BJP');
1
row created.
SQL>
insert into party52 values(104,'Congress');
1
row created.
SQL>
insert into party52 values(105,'Shivsena');
1
row created.
SQL>
select * from party52;
SQL>
create table politician52(pno number(5) primary key,pname
varchar2(10),description varchar2(10),party_code number(5) references party52
);
Table
created.
SQL>
desc politician52;
SQL>
insert into politician52
values(1,'Raj','MLA',101);
1
row created.
SQL>
insert into politician52
values(2,'Mahadev','CM',102);
1
row created.
SQL>
insert into politician52
values(3,'Devendra','CM',103);
1
row created.
SQL>
insert into politician52
values(4,'Rahul','MLA',104);
1
row created.
SQL>
insert into politician52
values(5,'Modi','PM',103);
1
row created.
SQL>
insert into politician52 values(6,'Uddhav','MLA',105);
1
row created.
SQL>
insert into politician52
values(7,'Rajabhau','CM',101);
1
row created.
SQL>
insert into politician52
values(8,'Vijay','PM',105);
1
row created.
SQL>
select * from politician52;
1)create or replace function in pl/sql
to total no of politician in given party
SQL>create
or replace function f5
2 (ppname
in varchar2)
3
return number
4 is
5
v_count number;
6 begin
7
select count(politician52.pno) into v_count from party52, politician52
8
where party52.party_code= politician52.party_code and
9
party52.party_name=ppname;
10
return v_count;
11 END
f5;
12 /
Function
created.
SQL>
declare
2
v_pname varchar2(20);
3 c
number;
4 begin
5
v_pname:='&v_pname';
6
c:=f5(v_pname);
7
dbms_output.put_line(‘Total no of politician-‘||c);
8 end;
9 /
***OUTPUT***
Enter
value for v_pname: BJP
old 5: v_pname:='&v_pname';
new 5: v_pname:='BJP';
Total
no of politician-2
PL/SQL
procedure successfully completed.
