Fifth stage
Q. Write a packet, it has two functions and two processes to operate the "EMP" table.
The task of this packet to be executed is:
Insert a new employee; delete an existing employee; show the overall salary of the specified employee (salary commission); display the designated employee's department name.
A.
Create or Replace Package Empck AS
Procedure Insrec (Pempno Emp.EmpnO% Type, Pename Emp.ename% Type,
PJOB Emp.Job% Type, PMgr Emp.Mgr% Type,
PhiRedate Emp.hiredate% Type, Psal Emp.sal% Type,
PCOMM EMP.COMM% TYPE, PDEPTNO Emp.Deptno% TYPE);
Procedure Delrec (Pempno in Number);
Function selsal (Pempn Number) Return Number;
Function SELDNAME (Pempn Number) Return Varchar2;
END;
/
Create or Replace Package Body Empck AS
Procedure Insrec (Pempno Emp.EmpnO% Type, Pename Emp.ename% Type,
PJOB Emp.Job% Type, PMgr Emp.Mgr% Type,
PhiRedate Emp.hiredate% Type, Psal Emp.sal% Type,
PCOMM EMP.COMM% TYPE, PDEPTNO Emp.deptno% TYPE)
IS
Begin
Insert Into Emp Values (Pempno, Pename, PJOB, PMGR, Phiredate,
PSAL, PCOMM, PDEPTNO;
DBMS_OUTPUT.PUT_LINE ('1 Record is created.');
End Insrec;
Procedure Delrec (Pempno in Number)
IS
Begin
DELETE from Emp where Empno = Pempno;
DBMS_OUTPUT.PUT_LINE ('1 Record Is Deleted.');
End Delrec;
Function SELSAL (Pempno Number) Return Number
IS
Vtotalsal Number;
Begin
SELECT NVL (SAL, 0) NVL (Comm, 0) Into Vtotalsal
From EMP
WHERE Empno = Pempno;
Return vtotalsal;
End selsal;
Function SELDNAME (Pempno Number) Return Varchar2
IS
vdname dept.dname% type;
Begin
Select DName Into vdname
From EMP, DEPT
WHERE Empno = Pempno and Emp.deptno = Dept.deptno;
Return vdname;
End seldname;
END;
/
- Perform the process and functions in the package
Execute Empck.insRec (1111, 'Goldens', 'Manager', 7698, '2003-01-18', 2000, 400, 30);
Execute Empck.delrec (1111);
Declare
SALARY NUMBER;
Begin
Salary: = Empck.selsal (7369);
DBMS_OUTPUT.PUT_LINE ('Total Salary IS' ||; END;
/
Declare
Department VARCHAR2 (30);
Begin
Department: = Empck.SeldName (7369);
DBMS_OUTPUT.PUT_LINE ('Department Name IS' || department);
END;
/
Q. Write a database trigger to display the salary increase when employees are salary at any time.
A.
Create or Replace Trigger EMP_SALUP
After Update of Sal On EMP
For Each Row
Declare
vsal number;
Begin
vsal: = NVL (: new.sal, 0) -nvl (: Old.sal, 0);
IF vsal <= 0 THEN
RAISE_APPLICATION_ERROR (-20001, 'Increased Salary Is Not Zero and Little Than Zero');
END IF;
END;
/
Q. Write a database trigger that allows the user to perform DML tasks only between 9.00 and 5 pm.
A.
Create or Replace Trigger Operate_time_limited
Before Insert or Update or Delete on EMP
--For Each Row
Declare
VTIME NUMBER;
Begin
vTIME: = TO_NUMBER (To_Char (Sysdate, 'HH24'));
IF vTIME NOT BETWEEN 9 and 17 THEN
RAISE_APPLICATION_ERROR (-20444, 'sorry! NOT EXCEPT 9AM AND 5PM.');
END IF;
END;
/
Q. Write a data as a trigger to check that there are no two presidents in an organization.
A.
Create Or Replace Trigger Check_president
Before Insert or Update on EMP
For Each Row
When (Upper (new.job) = 'PRESIDENT')
Declare
vcount number;
Begin
Select Count (Job) Into Vcount
From EMP
WHERE UPPER (JOB) = 'PRESIDENT'; - Statistics of the number of presidents, when 0, the variable value is 0
IF vcount> 0 THEN
Raise_Application_ERROR (-20444, 'sorry! Can''t Have Two President.');
END IF;
END;
/
Q. Write a database trigger. When a department is removed from "DEPT", the trigger will delete all the employees of the department from the "EMP" table.
A.
Create Or Replace Trigger Del_Emp_Deptno
Before delete on Dept
For Each Row
Begin
DELETE FROM EMP where deptno =: Old.deptno;
END;
/
(Full text)
