Dozens of practical pl/sql (5)

Fifth phase

Q. Write a packet that has two functions and two procedures to manipulate the EMP table.

The tasks to be performed by this packet are:

Inserts a new employee, deletes an existing employee, displays the overall salary (salary + commission) for the specified employee, and displays the department name of the specified employee.

A.

CREATE OR REPLACE PACKAGE Emppack 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 (pempno number) return number;

FUNCTION Seldname (pempno number) return VARCHAR2;

End;

/

CREATE OR REPLACE PACKAGE body emppack 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 is created. ');

End Insrec;

PROCEDURE Delrec (pempno in number)

Is

BEGIN

DELETE from EMP WHERE empno=pempno;

Dbms_output. Put_Line (' 1 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;

/



--executing procedures and functions in a package

EXECUTE Emppack.insrec (1111, ' Goldens ', ' MANAGER ', 7698, ' 2003-01-18 ', 2000,400,30);

EXECUTE Emppack.delrec (1111);



DECLARE

Salary number;

BEGIN

Salary:=emppack.selsal (7369);

Dbms_output. Put_Line (' Total Salary are ' | | Salary);

End;

/

DECLARE

Department VARCHAR2 (30);

BEGIN

Department:=emppack.seldname (7369);

Dbms_output. Put_Line (' Department name is ' | | department);

End;

/



Q. Write a database trigger to show when an employee raises a 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 littler than zero ');

End IF;

End;

/

Q. Write a database trigger that allows the user to perform a DML task only between 9.00 and 5.00 a.m.

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 THEN

Raise_application_error ( -20444, ' sorry! Not Except 9AM and 5PM. ');

End IF;

End;

/

Q. Write a data as a trigger to check that an organization cannot have two presidents.

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 '; --count the number of presidents, and when it's 0 o'clock, 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 that deletes all employees in that department from the EMP table whenever a department is removed from "dept".

A.

CREATE OR REPLACE TRIGGER Del_emp_deptno

Before DELETE on Dept

For each ROW

BEGIN

DELETE from EMP WHERE Deptno=:old.deptno;

End;

/

(End of full text)