1. Write a PL/SQL block of code for Prime Number.

declare
n number;
i number;
flag number;
begin
i:=2;
flag:=1;
n:=&n;
for i in 2..n/2
loop
if mod(n,i)=0
then
flag:=0;
exit;
end if;
end loop;
if flag=1
then
dbms_output.put_line('prime');
else
dbms_output.put_line('not prime');
end if;
end;
/
Output:
Enter value for n: 12
old 9: n:=&n;
new 9: n:=12;
not prime
=====================================================================

SAGAR SHINDE 1
2. Write PL/SQL Program for Fibonacci Series (It is a series in which next

number is the sum of previous two numbers.)

declare
first number:=0;
second number:=1;
third number;
n number:=&n;
i number;
begin
dbms_output.put_line('Fibonacci series is:');
dbms_output.put_line(first);
dbms_output.put_line(second);

for i in 2..n
loop
third:=first+second;
first:=second;
second:=third;
dbms_output.put_line(third);
end loop;
end;
/
Output:
Enter value for n: 6
old 5: n number:=&n;
new 5: n number:=6;
Fibonacci series is:
0
1
1
2
SAGAR SHINDE 2
 3
5
8
=====================================================================
3. Write PL/SQL Program to reverse a given input string.
declare
str1 varchar2(50):='&str';
str2 varchar2(50);
len number;
i number;
begin
len:=length(str1);
for i in reverse 1..len
loop
str2:=str2 || substr(str1,i,1);
end loop;
dbms_output.put_line('Reverse of String is:'||str2);
end;
/
Output:
Enter value for str: hello world
old 2: str1 varchar2(50):=’&str’;
new 2: str1 varchar2(50):=’hello world’;
Reverse of String is:dlrow olleh
=====================================================================
4. Write PL/SQL Program to find Factorial of a given number.
declare
n number;
fac number:=1;
i number;

SAGAR SHINDE 3
 begin
n:=&n;

for i in 1..n
loop
fac:=fac*i;
end loop;

dbms_output.put_line('factorial='||fac);
end;
/
Output
Enter value for n: 10
old 7: n:=&n;
new 7: n:=10;
factorial=3628800
=====================================================================
5. Write PL/SQL block to update the customer table and increase the salary of

each customer by 500 and use the SQL%ROWCOUNT attribute to determine

the number of rows affected.
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
SAGAR SHINDE 4
 END;
/
=====================================================================
6. Write a PL/SQL block using implicit cursor to update emp department to

Testing of emp name Sagar.

Emp_No Emp_Name Emp_Dept Emp_Salary
1 Prasad Web Developer 45k
2 Bhushan Program Developer 38k
3 Sagar Program Developer 34k
4 Yogesh Web Developer 42k

BEGIN
UPDATE emp_information SET emp_dept='Testing'
WHERE emp_name='Sagar';
IF SQL%FOUND THEN
dbms_output.put_line('Updated - If Found');
END IF;
IF SQL%NOTFOUND THEN
dbms_output.put_line('NOT Updated - If NOT Found');
END IF;
IF SQL%ROWCOUNT>0 THEN
dbms_output.put_line(SQL%ROWCOUNT||' Rows Updated');
ELSE
dbms_output.put_line('NO Rows Updated Found');
END;
/
=====================================================================
7. Write a PL/SQL block to create an Explicit cursor using for Loop and show the

records of the employees who have a salary greater than 50,000.

Table 1: Emp_Detail

SAGAR SHINDE 5
 Employee_id First_Name Last_name Salary DEPT_ID
1 Shruti Shrabya 50000 1
2 Jaya Singh 10000 2
3 Mangala Thokal 60000 3
4 Surendra Maurya 70000 4
Table2: 'Department'
Dept_ID Dept_Name Manager_ID
1 Accounting 1
2 Shipping 3
3 Store 3

DECLARE
CURSOR c_detail IS
SELECT dept_name,d.dept_id,first_name,salary
FROM department d JOIN emp_detail e
ON e.dept_id = d.dept_id
WHERE salary > 50000;
BEGIN
FOR item IN c_detail
LOOP
DBMS_OUTPUT.PUT_LINE(item.first_name||' '||item.dept_name||' '||item.dept_id||' '||'
'||[Link]);
END LOOP;
END;
/
=====================================================================
8. Write a PL/SQL block to create Explicit Cursor for displaying the total

number of rows present in the emp table and total salary of all emp.
Employee_id First_Name Last_name Salary
1 Shruti Shrabya 50000

SAGAR SHINDE 6
 2 Jaya Singh 10000
3 Mangala Thokal 60000
4 Surendra Maurya 70000
DECLARE
CURSOR c_emp_detail IS
SELECT employee_id,first_name,last_name,salary
FROM emp_detail;
rec_emp_detail c_emp_detail%ROWTYPE;
BEGIN
OPEN c_emp_detail;
LOOP
FETCH c_emp_detail INTO rec_emp_detail;
EXIT WHEN c_emp_detail%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employees Details : '||' '||rec_emp_detail.employee_id ||'
'||rec_emp_detail.first_name||' '||rec_emp_detail.last_name);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total number of rows : '||c_emp_detail%ROWCOUNT);
CLOSE c_emp_detail;
END;
=====================================================================
9. Write a PL/SQL block to create a cursor in nested loops and display the records

of each employees department wise.

Table 1: Emp_Detail
Employee_id First_Name Last_name Salary DEPT_ID
1 Shruti Shrabya 50000 1
2 Jaya Singh 10000 2
3 Mangala Thokal 60000 3
4 Surendra Maurya 70000 4
Table2: 'Department'
Dept_ID Dept_Name Manager_ID

SAGAR SHINDE 7
 1 Accounting 1
2 Shipping 3
3 Store 3
DECLARE
CURSOR c_dept IS
SELECT *
FROM department
WHERE manager_id IS NOT NULL
ORDER BY dept_name;
r_dept c_dept%ROWTYPE;
--Declaration of department cursor and record variable.
CURSOR c_emp (c_dept_no department.dept_id%TYPE) IS
SELECT *
FROM emp_detail
WHERE dept_id = c_dept_no;
r_emp c_emp%ROWTYPE;
--Declaration of employees cursor and record variable.
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept INTO r_dept;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Department Name : '||r_dept.dept_name);
OPEN c_emp(r_dept.dept_id);
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employees Details : '||r_emp.employee_id
||' '||r_emp.first_name||' '||r_emp.last_name||' '||r_emp.salary);
END LOOP;
CLOSE c_emp;

SAGAR SHINDE 8
 END LOOP;
CLOSE c_dept;
END;
====================================================================
10. Write a PL/SQL block of code using Explicit Cursor using For Loop to select

the five highest paid employees from the emp table.

DECLARE
CURSOR c1 is
SELECT ename, empno, sal FROM emp
ORDER BY sal DESC; -- start with highest paid employee
my_ename VARCHAR2(10);
my_empno NUMBER(4);
my_sal NUMBER(7,2);
BEGIN
OPEN c1;
FOR i IN 1..5 LOOP
FETCH c1 INTO my_ename, my_empno, my_sal;
EXIT WHEN c1%NOTFOUND;
/* in case the number requested is more than the total number of employees */
INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
COMMIT;
END LOOP;
CLOSE c1;
END;
/
=====================================================================
11. Write a PL/SQL block to create Parameterized Cursor to display employee

information from emp_information table whose emp_no= 4.

EMP_NO EMP_NAME EMP_DEPT EMP_SALARY
1 Forbs ross Web Developer 45k

SAGAR SHINDE 9
 2 marks jems Program Developer 38k
3 Saulin Program Developer 34k
4 Zenia Sroll Web Developer 42k
DECLARE
cursor c(no number) is select * from emp_information
where emp_no = no;
tmp emp_information%rowtype;
BEGIN
OPEN c(4);
FOR tmp IN c(4) LOOP
dbms_output.put_line('EMP_No: '||tmp.emp_no);
dbms_output.put_line('EMP_Name: '||tmp.emp_name);
dbms_output.put_line('EMP_Dept: '||tmp.emp_dept);
dbms_output.put_line('EMP_Salary:'||tmp.emp_salary);
END Loop;
CLOSE c;
END;
/
Output
EMP_No: 4
EMP_Name: Zenia Sroll
EMP_Dept: Web Developer
EMP_Salary: 42k
PL/SQL procedure successfully completed.
=====================================================================
12. Parameterized Cursor: Write a PL/SQL block to create Parameterized Cursor

to displays the name and salary of each employee in the EMP table whose
salary is less than that specified by a passed-in parameter value.
DECLARE
my_record emp%ROWTYPE;

SAGAR SHINDE 10
 CURSOR c1 (max_wage NUMBER) IS
SELECT * FROM emp WHERE sal < max_wage;
BEGIN
OPEN c1(2000);
LOOP
FETCH c1 INTO my_record;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name = ' ||my_record.ename || ', salary = '
|| my_record.sal);
END LOOP;
CLOSE c1;
END;
/
====================================================================
13. Write a PL/SQL stored procedure titled as 'COMPOUND_INTR' to calculate

the amount of interest on a bank account, which compounds interest yearly.

Condition is given as a stored procedure should accept the values of 'p', 'r' and
'y' as parameters and insert the Interest and total amount into temp table.
The following formula is used to calculate the interest.
I = p(1 + r / 100)y
Where,
'I' is the total interest earned.
'p' is principal amount.
'r' is the rate of interest as a decimal less than 1, and
'y' is the number of years.

create or replace procedure compound_intr (p in number,r in number,y in number) as

i number(6,2);
begin i:=p*power(1+r/100,y)-p;
dbms_output.put_line(i);

SAGAR SHINDE 11
 end;
/
OUTPUT
EXEC COMPOUND_INTR (1000,10,3);
331
PL/SQL procedure successfully completed.
=====================================================================
14. Create a stored function titled 'Age_calc'.

a. Accept the date of birth of a person as a parameter.

b. Calculate the age of the person in years, months and days e.g. 3 years,
2months, 10 days.
c. Return the age in years directly (with the help of Return statement).
d. The months and days are to be returned indirectly in the form of OUT
parameters.
Create a Function Age_calc:
CREATE OR REPLACE FUNCTION Age_calc(dat IN date,d OUT number,m OUT
number) RETURN number AS y number;
BEGIN d:=sysdate-dat;
y:=d/365;
y:=trunc(y);
m:=(d-y*365)/30;
M:=trunc(m);
d:=trunc(d-y*365-m*30);
RETURN y;
END;
/
Calling Procedure:
DECLARE D varchar2(20):='r';
P1 NUMBER:=&day_of_birth;
P2 NUMBER:=&month_of_birth;

SAGAR SHINDE 12
 P3 NUMBER:=&year_of_birth;
BEGIN D:=to_char(p1)||'-'||to_char(p2)||'-'||to_char(p3);
P1:=AGE_CALC(to_date(d,'dd-mm-yyyy'),P2,P3);
DBMS_OUTPUT.PUT_LINE('DAYS: '||P2||' MONTHS: '||P3||' YEARS: '||P1);
END;
/
=====================================================================
15. Create a store function that accepts 2 numbers and returns the addition of

passed values. Also write the code to call your function.

Function
create or replace function addition(a number,b number)
return number
is
begin
dbms_output.put('the sum of '||a||' and '||b||' is :');
return (a+b);
end;
/
Calling Procedure
begin
dbms_output.put_line(addition(6,78));
end;
/
=====================================================================
16. Write a PL/SQL function that accepts department number and returns the total

salary of the department. Also write a function to call the function.

Creating Function
create or replace function tot_sal_of_dept(dno number)
return number
is

SAGAR SHINDE 13
 tot_sal number:=0;
begin
select sum(salary) into tot_sal from works where deptno=dno;
return tot_sal;
end;
/
Calling Procedure
begin
dbms_output.put_line('Total salary of DeptNo 1 is :' ||tot_sal_of_dept(1));
end;
/
=====================================================================
17. Create the following 3 tables and insert sample data as given below:

Ord_mst(Ord_no, Cust_cd, Status)

Ord_dtl (Ord_no, Prod_cd, Qty)
Prod_mst (Prod_cd, Prod_name, Qty_in_stock, Booked_qty)
1. Write a PL/SQL block for before insert trigger on table 'Ord_dtl' in such a
way that the column 'Booked_qty' from table ‘Prod_mst' should be increased
accordingly.
CREATE OR REPLACE TRIGGER Ord_dtl_1
BEFORE
INSERT ON Ord_dtl
FOR EACH ROW BEGIN
UPDATE Prod_mst
SET Booked_qty=Booked_qty-:[Link]
WHERE Prod_cd=:new.Prod_cd; dbms_output.put_line();
END;
/

SAGAR SHINDE 14
2. Write a PL/SQL block for delete trigger on Ord_dtl. A record deleted from
table 'Ord_dtl' and the column 'Booked_qty' from table 'Prod_mst' should be
decreased accordingly.
CREATE OR REPLACE TRIGGER ORD_DTL_2
BEFORE
DELETE ON ORD_DTL
FOR EACH ROW BEGIN
UPDATE Prod_mst
SET Booked_qty=Booked_qty-:[Link]
WHERE Prod_cd=:old.Prod_cd; dbms_output.put_line('data deleted and updated
in prod_mst table');
END;
/
3. Write a PL/SQL block for before Update of column 'Prod_cd', Qty trigger on
'Ord_dtl'. The column Prod_cd or Qty should be updated and the 'Booked_qty'
in 'Prod_mst' should be increased or decreased accordingly.
CREATE TRIGGER Prod_cd_3
BEFORE
UPDATE ON ord_dtl
FOR EACH ROW BEGIN
UPDATE prod_mst
SET booked_qty=booked_qty-:[Link]+:[Link],prod_cd=:new.prod_cd
WHERE prod_cd=:old.prod_cd;
END;
/
=====================================================================
18. Create a row level trigger for the CUSTOMERS table that would fire INSERT

or UPDATE or DELETE operations performed on the CUSTOMERS table.

SAGAR SHINDE 15
 This trigger will display the salary difference between the old values and new
values.
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN ([Link] > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :[Link] - :[Link];
dbms_output.put_line('Old salary: ' || :[Link]);
dbms_output.put_line('New salary: ' || :[Link]);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
=====================================================================
19. Write a PL/SQL block for after insert trigger when a record is inserted into the

'Emp' table, a record should be inserted in the 'Emp_backup' table.

CREATE or REPLACE TRIGGER emp_after_insert AFTER INSERT ON Emp
FOR EACH ROW
DECLARE
BEGIN
insert into Emp_backup values (:[Link], :[Link], :[Link]);
DBMS_OUTPUT.PUT_LINE('Record successfully inserted into Emp_backup
table');
END;
===========================================================
20. Write a PL/SQL block using after update trigger to update a record in the

'Emp_backup' table when corresponding record is updated in the 'Emp' table.

SAGAR SHINDE 16
 21. CREATE or REPLACE TRIGGER emp_after_update
AFTER UPDATE OF empid ON emp
FOR EACH ROW
DECLARE
BEGIN
update emp_backup
set empid = :[Link]
where empid = :[Link];
DBMS_OUTPUT.PUT_LINE('empid successfully updated into emp_backup table');
END;
/
=====================================================================
22. Write PL/SQL block which accepts empno from user. If salary of that employee

is less than 20000, increment it by 10% of salary. Insert the empno and updated
salary in the table .
UPDATED_EMP(eno,sal,oper_date);
EMP(empno, ename,salary);
DECLARE
eno [Link]%TYPE;
sal [Link]%TYPE;
s number;
BEGIN
eno:=&empno;
SELECT salary INTO sal FROM emp empno=eno;
if sal < 20000 then
UPDATE emp set salary=salary+(salary*0.10) where empno=eno;
s:=sal+(sal*0.10);
INSERT INTO updated_emp VALUES(eno,s,sysdate);
end if;
EXCEPTION

SAGAR SHINDE 17
 WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘data not found’);
END;
/
=====================================================================

SAGAR SHINDE 18