PL/SQL
1.Write a PL/SQL statements to find out the area of circle.
SQL> declare
2 r number(2,1);
3 ar number(4,2);
4 begin
5 r:=&r;
6 ar:=3.14*r*r;
7 dbms_output.put_line('Area:'||ar);
8 end;
9 /
OUTPUT:
Enter value for r: 1
old 5: r:=&r;
new 5: r:=1;
Area:3.14
PL/SQL procedure successfully completed.
2. Write a PL/SQL statements to find fibonacci series upto the given number
elements.
SQL> declare
2 n number:=&n;
3 a number:=-1;
4 b number:=1;
5 c number(7);
6 i number;
7 begin
8 for i in 1..n
9 loop
10 c:=a+b;
11 dbms_output.put_line('Number:'||c);
12 a:=b;
13 b:=c;
14 end loop;
15 end;
16 /
OUTPUT:
Enter value for n: 3
old 2: n number:=&n;
new 2: n number:=3;
Number:0
Number:1
Number:1
PL/SQL procedure successfully completed.
3. Write a PL/SQL code to find the factorial of a given number.
SQL> declare
2 n number:=&n;
3 a number:=n;
4 f number:=1;i number;
5 begin
6 for i in 1..n
7 loop
8 f:=f*a;
9 a:=a-1;
10 end loop;
11 dbms_output.put_line('Factorial:'||f);
12 end;
13 /
OUTPUT:
Enter value for n: 3
old 2: n number:=&n;
new 2: n number:=3;
Factorial:6
PL/SQL procedure successfully completed.
4. Write a PL/SQL statements to find whether the given no. is prime or not.
SQL> declare
2 n number;
3 i number;
4 ar number:=1;
5 begin
6 n:=&n;
7 for i in 2..n/2
8 loop
9 if mod(n,i)=0 then
10 ar:=0;
11 end if;
12 end loop;
13 if ar=1 then
14
15
16
17
18
19
dbms_output.put_line(n||' is Prime');
else
dbms_output.put_line(n||' is not Prime');
end if;
end;
/
OUTPUT:
Enter value for n: 2
old 6: n:=&n;
new 6: n:=2;
2 is Prime
PL/SQL procedure successfully completed.
5. To find whether a given string is palindrome or not.
SQL> declare
2 g varchar(20);
3 r varchar(20);
4 begin
5 g:='&g';
6 dbms_output.put_line('Given String : '|| g);
7 for i in reverse 1..length(g)
8 loop
9 r:=r||substr(g,i,1);
10 end loop;
11 dbms_output.put_line('Reversed String : '||r);
12 if r=g then
13 dbms_output.put_line(g ||' is Palindrome');
14 else
15 dbms_output.put_line(g ||' is not Palindrome');
16 end if;
17 end;
18 /
OUTPUT:
Enter value for g: pop
old 5: g:='&g';
new 5: g:='pop';
Given String : pop
Reversed String : pop
pop is Palindrome
PL/SQL procedure successfully completed.
1. Write a PL/SQL fucntions to find the area of circle.
SQL> create or replace function f(r number)
2 return number is
3 begin
4 return(3.14*r*r);
5 end;
6 /
Function created.
SQL> declare
2 ra number:=&ra;
3 begin
4 dbms_output.put_line('Area of Circle :' ||f(ra));
5 end;
6 /
OUTPUT:
Enter value for ra: 10
old 2: ra number:=&ra;
new 2: ra number:=10;
Area of Circle :314
PL/SQL procedure successfully completed.
2. Write a PL/SQL functions to find the factorial of given number.
SQL> create or replace function fact(n number)
2 return number is
3 a number:=n;
4 f number:=1;
5 i number;
6 begin
7 for i in 1..n
8 loop
9 f:=f*a;
10 a:=a-1;
11 end loop;
12 return f;
13 end;
14 /
Function created.
SQL> declare
2 nu number:=ν
3 begin
4 dbms_output.put_line('Factorial:'||fact(nu));
5 end;
6 /
OUTPUT:
Enter value for nu: 3
old 2: nu number:=ν
new 2: nu number:=3;
Factorial:6
PL/SQL procedure successfully completed.
3. Write a PL/SQL functions to find the fibonacci series of given number.
SQL> create or replace function fib(n number)
2 return number is
3 a number:=-1;
4 b number:=1;
5 c number(10);
6 i number;
7 begin
8 for i in 1..n
9 loop
10 c:=a+b;
11 dbms_output.put_line('Fibonacci series :'||c);
12 a:=b;
13 b:=c;
14 end loop;
15 end;
16 /
Function created.
SQL> declare
2 num number:=#
3 begin
4 dbms_output.put_line(fib(num));
5 end;
6 /
OUTPUT:
Enter value for num: 3
old 2: num number:=#
new 2: num number:=3;
Fibonacci series :0
Fibonacci series :1
Fibonacci series :1
PL/SQL procedure successfully completed.
4. Write a PL/SQL functions to find whether the given number is prime or not.
SQL> create or replace function prime(n number)
2 return number is
3 i number;
4 pr number:=1;
5 begin
6 for i in 2..n/2
7 loop
8 if mod(n,i)=0 then
9 pr:=0;
10 end if;
11 end loop;
12 if pr=1 then
13 return 1;
14 else
15 return 0;
16 end if;
17 end;
18 /
Function created.
SQL> declare
2 num number:=#
3 begin
4 if prime(num)=1 then
5 dbms_output.put_line(num || ' is Prime');
6 else
7 dbms_output.put_line(num ||' is not Prime');
8 end if;
9 end;
10 /
OUTPUT:
Enter value for num: 6
old 2: num number:=#
new 2: num number:=6;
6 is not Prime
PL/SQL procedure successfully completed.
5. Write a PL/SQL functions to find whether the given string is palindrome or not.
SQL> create or replace function pd(a varchar)
2 return varchar is
3 s varchar(25);
4 begin
5 dbms_output.put_line('Given string:'||a);
6 for i in reverse 1..length(a)
7 loop
8 s:=s||substr(a,i,1);
9 end loop;
10 dbms_output.put_line('Reversed string:'||s);
11 return s;
12 end;
13 /
Function created.
SQL> declare
2 r varchar(20);
3 begin
4 r:='&r';
5 if pd(r)=r then
6 dbms_output.put_line('Given string is palindrome');
7 else
8 dbms_output.put_line('Given string is not palindrome');
9 end if;
10 end;
11 /
OUTPUT:
Enter value for r: dog
old 4: r:='&r';
new 4: r:='dog';
Given string:dog
Reversed string:god
Given string is not palindrome
PL/SQL procedure successfully completed.
6. Write a PL/SQL statements to find nCr using functions.
SQL> create or replace function fact(n number)
2 return number is
3 a number:=n;
4 f number:=1;
5 i number;
6 begin
7 for i in 1..n
8 loop
9 f:=f*a;
10 a:=a-1;
11 end loop;
12 return f;
13 end;
14 /
Function created.
SQL> create or replace function ncr(n number ,r number)
2 return number is
3 n1 number:=fact(n);
4 r1 number:=fact(r);
5 nr1 number:=fact(n-r);
6 result number;
7 begin
8 result:=(n1)/(r1*nr1);
9 return result;
10 end;
11 /
Function created.
SQL> declare
2 n number;
3 r number;
4 result number;
5 begin
6
7
8
9
10
11
n:=&n;
r:=&r;
result:=ncr(n,r);
dbms_output.put_line('nCr:'||result);
end;
/
OUTPUT:
Enter value for n: 3
old 6: n:=&n;
new 6: n:=3;
Enter value for r: 2
old 7: r:=&r;
new 7: r:=2;
nCr:3
PL/SQL procedure successfully completed.
7. Write a PL/SQL statements to find the sum(salary) from an employee table using
functions.
SQL> select * from emp4;
ENAME
EID
---------- ----------
A
123
B
456
C
789
D
753
SQL> create or replace function loy(i number)
2 return number is
3 s number;
4 begin
5 select sum(salary) into s from emp4;
6 return s;
7 end;
8 /
Function created.
SQL> declare
2 k number;
3 begin
4 k:=loy(0);
5 dbms_output.put_line('Sum of salary:'||k);
6 end;
7 /
OUTPUT:
Sum of salary:84000
PL/SQL procedure successfully completed.
8. Write a PL/SQL statements to find the max(salary) from an employee table using
functions.
SQL> select * from emp4;
ENAME EID SALARY JOD
---------- ---------- ---------- ---------
A
123 20000 25-DEC-10
B
456 25000 02-OCT-08
C
789 35000 07-JAN-06
D
753
4000 12-AUG-09
SQL> create or replace function em(i number)
2 return number is
3 m number;
4 begin
5 select max(salary) into m from emp4;
6 return m;
7 end;
8 /
Function created.
SQL> declare
2 v number;
3 begin
4 v:=em(0);
5 dbms_output.put_line('Maximum salary:'||v);
6 end;
7 /
OUTPUT:
Maximum salary:35000
PL/SQL procedure successfully completed.
PL/SQL - PROCEDURES:
1.a) Write a procedure to find the sum of salary of an employee when it is given as
input.
SQL> create or replace procedure p1(n number)
2 is
3 cursor c is select eid,salary,commission,salary+commission as sum from emp4
where eid=n;
4 r c%rowtype;
5 begin
6 open c;
7 fetch c into r;
8 dbms_output.put_line('Salary for '||r.eid||' is '||r.salary);
9 dbms_output.put_line('Commission for '||r.eid||' is '||r.commission);
10 dbms_output.put_line('Total Salary for '||r.eid||' is '||r.sum);
11 close c;
12 end;
13 /
Procedure created.
SQL> execute p1(123);
OUTPUT:
Salary for 123 is 20000
Commission for 123 is 2000
Total Salary for 123 is 22000
PL/SQL procedure successfully completed.
1.b) Write a procedure to update the total income of the employee in employee
relation
SQL> create or replace procedure p4 is
2 begin
3 update emp4 set income=salary+commission;
4 end p4;
5 /
Procedure created.
SQL> execute p4;
OUTPUT:
PL/SQL procedure successfully completed.
SQL> select * from emp4;
ENAME EID SALARY JOD
COMMISSION SUM INCOME
---------- ---------- ---------- ----------- ---------- ---------- ----------------------
A
123 20000 25-DEC-10
2000
22000
B
456 25000 02-OCT-08
3500
28500
C
789 35000 07-JAN-06
50000
85000
D
753 4000 12-AUG-09
1500
5500
2.Write a procedure to calculate average & total when name is given as input.
SQL> select * from stu1;
NAME
RNO
M1
M2
M3
-------------------- ---------- ---------- ---------- ----------
A
1
100
100
100
B
2
95
89
99
C
3
78
81
75
SQL> alter table stu1 add(tot int,avg float);
Table altered.
SQL> create or replace procedure p2(n char) is
2 begin
3 update stu1 set tot=m1+m2+m3,avg=(m1+m2+m3)/3 where name=n;
4 end p2;
5 /
Procedure created.
SQL> execute p2('A');
OUTPUT:
PL/SQL procedure successfully completed.
SQL> select * from stu1;
NAME RNO M1
M2
M3
TOT AVG
----------------------------------------------------------------------
A
1
100
100
100
300 100
B
2
95
89
99
C
3
78
81
75
1.Write a PL/SQL statements to find out the area of circle.
SQL> declare
2 r number(2,1);
3 ar number(4,2);
4 begin
5 r:=&r;
6 ar:=3.14*r*r;
7 dbms_output.put_line('Area:'||ar);
8 end;
9 /
OUTPUT:
Enter value for r: 1
old 5: r:=&r;
new 5: r:=1;
Area:3.14
PL/SQL procedure successfully completed.
2. Write a PL/SQL statements to find fibonacci series upto the given number
elements.
SQL> declare
2 n number:=&n;
3 a number:=-1;
4 b number:=1;
5 c number(7);
6 i number;
7 begin
8 for i in 1..n
9 loop
10 c:=a+b;
11 dbms_output.put_line('Number:'||c);
12 a:=b;
13 b:=c;
14 end loop;
15 end;
16 /
OUTPUT:
Enter value for n: 3
old 2: n number:=&n;
new 2: n number:=3;
Number:0
Number:1
Number:1
PL/SQL procedure successfully completed.
3. Write a PL/SQL code to find the factorial of a given number.
SQL> declare
2 n number:=&n;
3 a number:=n;
4 f number:=1;i number;
5 begin
6 for i in 1..n
7 loop
8 f:=f*a;
9 a:=a-1;
10 end loop;
11 dbms_output.put_line('Factorial:'||f);
12 end;
13 /
OUTPUT:
Enter value for n: 3
old 2: n number:=&n;
new 2: n number:=3;
Factorial:6
PL/SQL procedure successfully completed.
4. Write a PL/SQL statements to find whether the given no. is prime or not.
SQL> declare
2 n number;
3 i number;
4 ar number:=1;
5 begin
6 n:=&n;
7 for i in 2..n/2
8 loop
9 if mod(n,i)=0 then
10 ar:=0;
11 end if;
12 end loop;
13 if ar=1 then
14
15
16
17
18
19
dbms_output.put_line(n||' is Prime');
else
dbms_output.put_line(n||' is not Prime');
end if;
end;
/
OUTPUT:
Enter value for n: 2
old 6: n:=&n;
new 6: n:=2;
2 is Prime
PL/SQL procedure successfully completed.
5. To find whether a given string is palindrome or not.
SQL> declare
2 g varchar(20);
3 r varchar(20);
4 begin
5 g:='&g';
6 dbms_output.put_line('Given String : '|| g);
7 for i in reverse 1..length(g)
8 loop
9 r:=r||substr(g,i,1);
10 end loop;
11 dbms_output.put_line('Reversed String : '||r);
12 if r=g then
13 dbms_output.put_line(g ||' is Palindrome');
14 else
15 dbms_output.put_line(g ||' is not Palindrome');
16 end if;
17 end;
18 /
OUTPUT:
Enter value for g: pop
old 5: g:='&g';
new 5: g:='pop';
Given String : pop
Reversed String : pop
pop is Palindrome
PL/SQL procedure successfully completed.
1. Write a PL/SQL fucntions to find the area of circle.
SQL> create or replace function f(r number)
2 return number is
3 begin
4 return(3.14*r*r);
5 end;
6 /
Function created.
SQL> declare
2 ra number:=&ra;
3 begin
4 dbms_output.put_line('Area of Circle :' ||f(ra));
5 end;
6 /
OUTPUT:
Enter value for ra: 10
old 2: ra number:=&ra;
new 2: ra number:=10;
Area of Circle :314
PL/SQL procedure successfully completed.
2. Write a PL/SQL functions to find the factorial of given number.
SQL> create or replace function fact(n number)
2 return number is
3 a number:=n;
4 f number:=1;
5 i number;
6 begin
7 for i in 1..n
8 loop
9 f:=f*a;
10 a:=a-1;
11 end loop;
12 return f;
13 end;
14 /
Function created.
SQL> declare
2 nu number:=ν
3 begin
4 dbms_output.put_line('Factorial:'||fact(nu));
5 end;
6 /
OUTPUT:
Enter value for nu: 3
old 2: nu number:=ν
new 2: nu number:=3;
Factorial:6
PL/SQL procedure successfully completed.
3. Write a PL/SQL functions to find the fibonacci series of given number.
SQL> create or replace function fib(n number)
2 return number is
3 a number:=-1;
4 b number:=1;
5 c number(10);
6 i number;
7 begin
8 for i in 1..n
9 loop
10 c:=a+b;
11 dbms_output.put_line('Fibonacci series :'||c);
12 a:=b;
13 b:=c;
14 end loop;
15 end;
16 /
Function created.
SQL> declare
2 num number:=#
3 begin
4 dbms_output.put_line(fib(num));
5 end;
6 /
OUTPUT:
Enter value for num: 3
old 2: num number:=#
new 2: num number:=3;
Fibonacci series :0
Fibonacci series :1
Fibonacci series :1
PL/SQL procedure successfully completed.
4. Write a PL/SQL functions to find whether the given number is prime or not.
SQL> create or replace function prime(n number)
2 return number is
3 i number;
4 pr number:=1;
5 begin
6 for i in 2..n/2
7 loop
8 if mod(n,i)=0 then
9 pr:=0;
10 end if;
11 end loop;
12 if pr=1 then
13 return 1;
14 else
15 return 0;
16 end if;
17 end;
18 /
Function created.
SQL> declare
2 num number:=#
3 begin
4 if prime(num)=1 then
5 dbms_output.put_line(num || ' is Prime');
6 else
7 dbms_output.put_line(num ||' is not Prime');
8 end if;
9 end;
10 /
OUTPUT:
Enter value for num: 6
old 2: num number:=#
new 2: num number:=6;
6 is not Prime
PL/SQL procedure successfully completed.
5. Write a PL/SQL functions to find whether the given string is palindrome or not.
SQL> create or replace function pd(a varchar)
2 return varchar is
3 s varchar(25);
4 begin
5 dbms_output.put_line('Given string:'||a);
6 for i in reverse 1..length(a)
7 loop
8 s:=s||substr(a,i,1);
9 end loop;
10 dbms_output.put_line('Reversed string:'||s);
11 return s;
12 end;
13 /
Function created.
SQL> declare
2 r varchar(20);
3 begin
4 r:='&r';
5 if pd(r)=r then
6 dbms_output.put_line('Given string is palindrome');
7 else
8 dbms_output.put_line('Given string is not palindrome');
9 end if;
10 end;
11 /
OUTPUT:
Enter value for r: dog
old 4: r:='&r';
new 4: r:='dog';
Given string:dog
Reversed string:god
Given string is not palindrome
PL/SQL procedure successfully completed.
6. Write a PL/SQL statements to find nCr using functions.
SQL> create or replace function fact(n number)
2 return number is
3 a number:=n;
4 f number:=1;
5 i number;
6 begin
7 for i in 1..n
8 loop
9 f:=f*a;
10 a:=a-1;
11 end loop;
12 return f;
13 end;
14 /
Function created.
SQL> create or replace function ncr(n number ,r number)
2 return number is
3 n1 number:=fact(n);
4 r1 number:=fact(r);
5 nr1 number:=fact(n-r);
6 result number;
7 begin
8 result:=(n1)/(r1*nr1);
9 return result;
10 end;
11 /
Function created.
SQL> declare
2 n number;
3 r number;
4 result number;
5 begin
6
7
8
9
10
11
n:=&n;
r:=&r;
result:=ncr(n,r);
dbms_output.put_line('nCr:'||result);
end;
/
OUTPUT:
Enter value for n: 3
old 6: n:=&n;
new 6: n:=3;
Enter value for r: 2
old 7: r:=&r;
new 7: r:=2;
nCr:3
PL/SQL procedure successfully completed.
7. Write a PL/SQL statements to find the sum(salary) from an employee table using
functions.
SQL> select * from emp4;
ENAME
EID
---------- ----------
A
123
B
456
C
789
D
753
SQL> create or replace function loy(i number)
2 return number is
3 s number;
4 begin
5 select sum(salary) into s from emp4;
6 return s;
7 end;
8 /
Function created.
SQL> declare
2 k number;
3 begin
4 k:=loy(0);
5 dbms_output.put_line('Sum of salary:'||k);
6 end;
7 /
OUTPUT:
Sum of salary:84000
PL/SQL procedure successfully completed.
8. Write a PL/SQL statements to find the max(salary) from an employee table using
functions.
SQL> select * from emp4;
ENAME EID SALARY JOD
---------- ---------- ---------- ---------
A
123 20000 25-DEC-10
B
456 25000 02-OCT-08
C
789 35000 07-JAN-06
D
753
4000 12-AUG-09
SQL> create or replace function em(i number)
2 return number is
3 m number;
4 begin
5 select max(salary) into m from emp4;
6 return m;
7 end;
8 /
Function created.
SQL> declare
2 v number;
3 begin
4 v:=em(0);
5 dbms_output.put_line('Maximum salary:'||v);
6 end;
7 /
OUTPUT:
Maximum salary:35000
PL/SQL procedure successfully completed.
PL/SQL - PROCEDURES:
1.a) Write a procedure to find the sum of salary of an employee when it is given as
input.
SQL> create or replace procedure p1(n number)
2 is
3 cursor c is select eid,salary,commission,salary+commission as sum from emp4
where eid=n;
4 r c%rowtype;
5 begin
6 open c;
7 fetch c into r;
8 dbms_output.put_line('Salary for '||r.eid||' is '||r.salary);
9 dbms_output.put_line('Commission for '||r.eid||' is '||r.commission);
10 dbms_output.put_line('Total Salary for '||r.eid||' is '||r.sum);
11 close c;
12 end;
13 /
Procedure created.
SQL> execute p1(123);
OUTPUT:
Salary for 123 is 20000
Commission for 123 is 2000
Total Salary for 123 is 22000
PL/SQL procedure successfully completed.
1.b) Write a procedure to update the total income of the employee in employee
relation
SQL> create or replace procedure p4 is
2 begin
3 update emp4 set income=salary+commission;
4 end p4;
5 /
Procedure created.
SQL> execute p4;
OUTPUT:
PL/SQL procedure successfully completed.
SQL> select * from emp4;
ENAME EID SALARY JOD
COMMISSION SUM INCOME
---------- ---------- ---------- ----------- ---------- ---------- ----------------------
A
123 20000 25-DEC-10
2000
22000
B
456 25000 02-OCT-08
3500
28500
C
789 35000 07-JAN-06
50000
85000
D
753 4000 12-AUG-09
1500
5500
2.Write a procedure to calculate average & total when name is given as input.
SQL> select * from stu1;
NAME
RNO
M1
M2
M3
-------------------- ---------- ---------- ---------- ----------
A
1
100
100
100
B
2
95
89
99
C
3
78
81
75
SQL> alter table stu1 add(tot int,avg float);
Table altered.
SQL> create or replace procedure p2(n char) is
2 begin
3 update stu1 set tot=m1+m2+m3,avg=(m1+m2+m3)/3 where name=n;
4 end p2;
5 /
Procedure created.
SQL> execute p2('A');
OUTPUT:
PL/SQL procedure successfully completed.
SQL> select * from stu1;
NAME RNO M1
M2
M3
TOT AVG
----------------------------------------------------------------------
A
1
100
100
100
300 100
B
2
95
89
99
C
3
78
81
75
No comments:
Post a Comment