Scribd
Upload
4 views2 pages

PL/SQL Blocks for Marks, Factorial, and Salary

The document contains multiple PL/SQL code snippets demonstrating various functionalities such as calculating total marks for students using triggers, finding factorial values through functions, displaying employee details using explicit cursors, and updating employee salary based on specific conditions. It includes the creation of triggers to store old employee information and raise salaries according to defined rules. Each code block illustrates different aspects of PL/SQL programming and database operations.

Uploaded by

somugaming33
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
4 views2 pages

PL/SQL Blocks for Marks, Factorial, and Salary

The document contains multiple PL/SQL code snippets demonstrating various functionalities such as calculating total marks for students using triggers, finding factorial values through functions, displaying employee details using explicit cursors, and updating employee salary based on specific conditions. It includes the creation of triggers to store old employee information and raise salaries according to defined rules. Each code block illustrates different aspects of PL/SQL programming and database operations.

Uploaded by

somugaming33
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Q.

1 Write a PL/SQL Block that calculate marks of three subjects and display the total by firing
that the database trigger while inserting the marks of three [Link] total should automatically
calculated at the time of inserting marks of three subjects.
Design the database trigger to calculate the total while INSERT operation.
Table : Student (roll no,name,sub1,sub2,total)

create or replace trigger StudentSum


before insert on Student
for each row
declare
TotalMarks number(10);
begin
select Sum(New.Sub1,New.Sub2) into TotalMarks
from student
dbms_output.put_line('Total Marks is = ' || TotalMarks);
end;

Write a PL/SQL block to find out the factorial value using procedure and function call the
procedure and function in main code block.

DECLARE
num number(10);
factorial number(10);
FUNCTION fact(x number)
RETURN number
IS
f number(10);
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * fact(x-1);
END IF;
RETURN f;
END;

Here is a code to call above function using PL/Sql Block.

BEGIN
num:= 6;
factorial := fact(num);
dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;

3 Write PL/SQL block to display employee details of each employee for the given deptno using
explicit cursor. (read the deptno from useer) (employee table :
empno,ename,job,hiredate,sal,commission,deptno)

Create CURSOR C1 (dept IN varchar2)


IS
SELECT * from where deptno = dept;
BEGIN

Open C1(20)
LOOP
FETCH C1 INTO emp_row;
EXIT WHEN C1 %NOTFOUND;

dbms_output.put_line([Link] || ' ' ||[Link]);

END LOOP;
END;

[Link] a trigger,which will store the old information of employee after giving 15% raise to him.

CREATE or REPLACE TRIGGER sal_update


BEFORE UPDATE ON product
FOR EACH ROW
BEGIN
INSERT INTO Sal_History VALUES (:[Link],:[Link],:[Link])
END;

Q.6 Write a PL/SQL block which will give a rise in salary to employee as per
the following rule.
If salary + comm < 5000 increase salary by 10%
If Salary + comm >= 5000 increase salary by Rs.500 and
If salary + Comm is above 7000 also increase salary by 12%

DECLARE
Salary number(15,2);
Empno number(10);
Comm number(10,2);
BEGIN
Select salary,eno,Comm from employee
If sql%found THEN
FOR row in sql
LOOP
Select [Link],[Link],[Link] into Salary,Empno,comm.
Salary=salary+comm.;
If Salary<5000 then
Update Employee Set salary=salary+(salary*0.05) where empno=[Link] ;
End if;
END LOOP;
END IF;
End;

You might also like