The first phase Q. Write a PL / SQL block to display the details of the employee given the employee number. A. Declare EREC EMP% RowType; Begin Select * INTO EREC from Emp WHERE Empno = & Employees Number; DBMS_OUTPUT.PUT_LINE ('Empno' || '' || 'Ename' || '' || 'Job' || ' '||' Manager '||' '||' HiRedate '||' '||' Commision '||' '||' DEPTNO '); DBMS_OUTPUT.PUT_LINE (EREC. ENAME || '' || erec.job || '' || EREC.Mgr || '' || EREC.HIREDATE || '' || EREC.SAL || '|| EREC.comm ||' ' || erec.deptno; end; / q. Write a PL / SQL block to calculate the total annual salary of an employee. A.Declare esal number; eName Emp.ename% Type; Begin SELECT (NVL (SAL, 0) NVL (Comm, 0)) * 12, Ename Into Esal, ENAME FROM EMP WHERE Empno = & Employees No .; dbms_output.put_line (ENAME || '' '' s Years Salary IS '||; End; / Q. Performs the following salary ratios: DEPTNO RAISE (% AGE) 10 5% 20 10% 30 15% 40 20% salary The percentage is based on their existing salary. Write a PL / SQL to salary the designated employee. A.Declare vcounter Number: = 10; Vraise Number; Begin Loop Exit When vcounter> 40; Update Emp Set Sal = NVL (SAL, 0) NVL (SAL, 0) * 0.05 Where deptno = vcounter; vcounter: = vcounter 10; End loop;
Q. Write a PL / SQL to add 10 new employees numbers to the "EMP" table. (Tip: If the current largest employees are numbered 7900, the new employee number will be 7901 to 7910) A.Declare vcolnter Number; Begin Select Max (Empno) Into VcolNter from Emp; for i in 1..10 loop vcolnter: = Vcounter 1; Insert INTO EMP (EMPNO) VALUES (VcOUNTER); end loop; end; / q. Use only one variable to solve the experimental class job 4. ADECLARE erec emp% ROWTYPE; - vraise NUMBER; BEGIN SELECT * INTO erec FROM emp WHERE ename = '& ename'; IF erec.job = 'CLERK' THEN emp SET sal = sal 500 WHERE empno = erec.empno UPDATE; ELSIF Erec.Job = 'Salesman' Then Update Emp Set Sal = SAL 1000 WHERE Empno = EREC.Empno; Elsif Erses EMP SET SAL = SAL 1500 WHERE Empno = EREC.Empno; Else Update EMP SET SAL = SAL 2000 WHERE EMPNO = EREC.Empno; end if; - Update EMP SET SAL = SAL VRAISE WHERE EMPNO = EREC.Empno; - dbms_output.put_line (vraise); End; / Q. Accept two The number is eliminated and the result is displayed. If the second number is 0, the message "Divide By Zero" is displayed. A.Declare Num1 Number; Num2 Num1: = & Num; Num2: = & NUM; DBMS_OUTPUT.PUT_LINE (Num1 || '/' || Num2 || 'IS' || Num1 / Num2); Exception When Zero_divide Then DBMS_OUTPUT .Put_line ('DIDN'' '' z zer tell you not to divide by zero? '); End; / second phase Q. Write a PL / SQL block, all the names start with "A" or "S" Employees pay a ranks of 10% of their basic salary. A.DECLARE CURSOR C1 IS SELECT * FROM EMP WHERE SUBSTR (ENAME, 1, 1) = 'A' or Substr (Ename, 1, 1) = 'S' for Update of Sal
Begin for i in c1 loop update EMP SET SAL = NVL (SAL, 0) NVL (SAL, 0) * 0.1 WHERE CURRENT OF C1; End loop; end; / q. Write a PL / SQL, for all "sales "Salesman] increase commission 500.a.declare cursor c1 is select * from emp where job = 'salesman' for update of sal; begin for i in c1 loop update EMP SET SAL = NVL (SAL, 0) 500 Where CURRENT OF C1; End Loop; End; / Q. Write a PL / SQL to enhance the two qualifications of "staff" as "senior staff". (The longer working hours, the higher the priority) A.Declare cursor c1 is select * from Emp where job = 'clerk' Order by HiRedate for Update of Job; - Ascending arrangement, long-term growth in front Begin for i in C1 LOOP EXIT WHEN C1% ROWCOUNT> 2; dbms_output.put_line (i.ename); Update Emp Set Job = 'Highclek' Where Current of C1; End Loop; End; / Q. Write a PL / SQL, press them for all employees 10% of the basic salary, if the increased salary is greater than 5000, will cancel the salary. A.DECLARE CURSOR C1 IS SELECT * FROM EMP for Update of Sal
Begin for i in C1 Loop IF (I.SAL I.SAL * 0.1) <= 5000 THEN UPDATE EMP SET SAL = SAL SAL * 0.1 WHERE CURRENT OF C1; DBMS_OUTPUT.PUT_LINE (I.SAL); END IF; END Loop; end; / q. Display Article 4 records in EMP. A.Declare Cursor C1 is SELECT * FROM EMP;
Begin for i IN C1 Loop IF C1% Rowcount = 4 THEN DBMS_OUTPUT.PUT_LINE (i. Empno || '|| I.ename ||' || I.Job || '|| I.Mgr ||' '|| i.hiredate ||' '|| I.SAL ||' '|| I.COMM ||' '|| i.deptno; End; End If; End Loop;
The third phase Q. Use the REF cursor to display the value in the "EMP" table. A.DECLARE TYPRECTYP IS Record (Empno Emp.Empno% Type, Ename Emp.Emp, MGR Emp.MGR% Type, HiRedate Emp.hiredate% Type, Sal Emp.sal% Type, COMM emp.comm% TYPE, DEPTNO emp.deptno% TYPE); TYPE emp_cursor IS REF CURSOR RETURN emp% ROWTYPE; vemp_cur eMP_CURSOR; vemp_rec EMPRECTYP; BEGIN OPEN vemp_cur FOR SELECT * FROM emp; LOOP FETCH vemp_cur INTO vemp_rec; EXIT WHEN vemp_cur% NOTFOUND; dbms_output.put (vemp_rec.empno || '|| vemp_rec.ename ||' || vemp_rec.job); dbms_output.put (vemp_rec.mgr || '|| Vemp_rec.hiredate ||' | | | VEMP_REC.SAL); DBMS_OUTPUT.PUT_LINE (Vemp_rec.comm || '|| VEMP_REC.DEPTNO); End loop; Close Vemp_cur; end; / q. Get the value from "EMP" to the PL / SQL table, will The salaries in the PL / SQL table increase 500 and display the increased salary and other details to the user. A.DECLARE TYPREC IS Record (Empno Emp.EmpnO% Type, Ename Emp.Ename% Type, Job Emp.Job% Type, Mgr Emp.Mgr% Type, HiRedate Emp.hiredate% Type, Sal Emp.sal% Type, COMM Emp.comm% Type, DePTNO Emp.Deptno% TYPE); I Binary_INTEGER: = 1;
TYPE EMP_TAB IS TABLE OF EMPREC INDEX by binary_integer; Vemp Emp_Tab;
CURSOR C1 IS SELECT * FROM EMP; Begin for x in C1 Loop Vemp (i) .empno: = x.empno; Vemp (i) .Ename: = x.ename; VEMP (i) .job: = x.job; Vemp (i) .mgr: = x.mgr; vemp (i) .hiredate: = x.hiredate; vemp (i) .sal: = x.sal 500; Vemp (i) .comm: = x.comm; Vemp (i) .deptno: = x.deptno; i: = i 1; end loop;
For J in 1..i-1 loop dbms_output.put (Vemp (j) .empno || '|| Vemp (j) .Ename ||' '|| VEMP (j) .job); dbms_output.put Vemp (j) .mgr || '|| Vemp (j) .hidate ||' '|| VEMP (j) .sal); dbms_output.put_line (Vemp (j) .comm ||' || Vemp ( j) .deptno; end loop; end; / q. Once the value is sent to the PL / SQL table, try to insert a new record in the PL / SQL table and delete some existing records. A.DECLARE TYPREC IS Record (Empno Emp.EmpnO% Type, Ename Emp.Ename% Type, Job Emp.Job% Type, Mgr Emp.Mgr% Type, HiRedate Emp.hiredate% Type, Sal Emp.sal% Type, COMM Emp.comm% Type, DePTNO Emp.Deptno% TYPE); I Binary_INTEGER: = 1;
TYPE EMP_TAB IS TABLE OF EMPREC INDEX by binary_integer; Vemp Emp_Tab;
CURSOR C1 IS SELECT * FROM EMP; Begin for x in C1 Loop Vemp (i) .empno: = x.empno; Vemp (i) .Ename: = x.ename; VEMP (i) .job: = x.job; Vemp (i) .mgr: = x.mgr; vemp (i) .hiredate: = x.hiredate; vemp (i) .sal: = x.sal; vemp (i) .comm: = x.comm; vemp i) .deptno: = x.deptno; i: = i 1; end loop; - for j in 1..i-1 - loop - dbms_output.put (Vemp (j) .empno || '' || Vemp (J) .Name || '' || Vemp (j) .job); - dbms_output.put (Vemp (j) .mgr || '|| Vemp (j) .hidate ||' ' || Vemp (j) .sal); - dbms_output.put_line (Vemp (j) .comm || '|| Vemp (j) .deptno; - end loop;
- Insert DBMS_OUTPUT.PUT_LINE ('Insert Record:'); VEMP (i) .empno: = 1000; Vemp (i) .Ename: = 'Goldens'; Vemp (i) .job: = 'Software'; Vemp (i) .mgr: = null; vemp (i) .hiredate: = '2003-01-04'; VEMP (i) .sal: = 8888; VEMP (i) .comm: = 10; VEMP (i). Deptno: = 10; for J in 1..i loop dbms_output.put (Vemp (j) .empno || '|| Vemp (j) .ename ||' '|| VEMP (j) .job); dbms_output .Put (Vemp (j) .mgr || '|| Vemp (j) .hidate ||' '|| Vemp (j) .sal); dbms_output.put_line (Vemp (j) .comm ||' | | Vemp (j) .deptno; end loop; - Delete Article 5, 6 Record dbms_output.put_line ('Delete Article 5, 6 Records:'); for J IN 5..I-2 Loop Vemp (J ) .Empno: = VEMP (j 2) .empno; vemp (j) .ename: = vemp (j 2) .ename; vemp (j) .job: = VEMP (J 2) .job; Vemp ( j) .mgr: = VEMP (j 2) .mgr; vemp (j) .hidate: = vemp (j 1) .hiredate; vemp (j) .sal: = vemp (j 2) .sal; vemp (j) .comm: = VEMP (J 2) .comm; vemp (j) .deptno: = VEMP (J 2) .deptno; end loop; vemp (i-1) .empno: = null; vemp I-1) .Name: = NULL; VEMP (I-1) .job: = null; Vemp (i-1) .mgr: = null; Vemp (i-1) .hidate: = null; Vemp (i- 1) .SAL: = NULL; VEMP (i-1) .comm: = null; Vemp (i-1) .deptno: = null; vemp (i) .empno: = null; Vemp (i) .Name: = null; Vemp (i) .job: = null; Vemp (i) .mgr: = null; vemp (i) .hiredate: = null; Vemp (i) .sal: = null; Vemp (i) .comm: = null; Vemp (i) .deptno: = null; for j in 1..i-2 loop dbms_output.put (Vemp (j) .empno || '|| Vemp (j) .ename || '' || Vemp (j) .job); DBMS_OUTPUT.PUT (Vemp (j) .mgr || '|| Vemp (j) .hidate ||' '|| VEMP (j) .sal); dbms_output. PUT_LINE (Vemp (j) .comm || '|| Vemp (j) .deptno; end loop;
END; /
The fourth stage Q. Write a process to accept three department numbers entered by the user and display the department names of the two department numbers. A.CREATE OR REPLACE PROCEDURE DeptName (no1 dept.deptno% TYPE, no2 dept.deptno% TYPE, no3 dept.deptno% TYPE) AS vflag NUMBER; vdeptno1 dept.deptno% TYPE; vdeptno2 dept.deptno% TYPE; vdname1 dept. DNAME% TYPE; VDNAME2 DEPT.DNAME% TYPE; begin vflag: = to_Number (to_char (sysdate, 'ss')); if (vFlag> = 1 and vflag <= 10) or (vflag> = 50 and vflag <60) THEN SELECT deptno, dname INTO vdeptno1, vdname1 FROM dept WHERE deptno = no1; SELECT deptno, dname INTO vdeptno2, vdname2 FROM dept WHERE deptno = no2; ELSIF (vflag> = 11 AND vflag <= 20) OR (vflag> = 40 AND vflag <50) THEN SELECT deptno, dname INTO vdeptno1, vdname1 FROM dept WHERE deptno = no1; SELECT deptno, dname INTO vdeptno2, vdname2 FROM dept WHERE deptno = no3; ELSE SELECT deptno, dname INTO vdeptno1, vdname1 FROM dept WHERE deptno = no2 ; SELECT deptno, dname INTO vdeptno2, vdname2 FROM dept WHERE deptno = no3; END IF; DBMS_OUTPUT.PUT_LINE ( 'department number:' || vdeptno1 || '' || 'department name:' || vdname1); DBMS_OUTPUT.PUT_LINE ('Department number:' || vdeptno2 || '' || 'department name:' || vdname2); end; / execute deptname (10, 20, 30) ;
Q. Write a process to display the employee department name and location of the specified employee name. A.CREATE OR REPLACE PROCEDURE DeptMesg (pename emp.ename% TYPE, pdname OUT dept.dname% TYPE, ploc OUT dept.loc% TYPE) ASBEGIN SELECT dname, loc INTO pdname, ploc FROM emp, dept WHERE emp.deptno = dept .deptno and emp.ename = pename; end; / variable vdname varcha2 (14) variable vloc varcha2 (13)
Execute Deptmesg ('Smith',: Vdname,: Vloc); Print Vdname Vloc;
Q. Write a process of 10% to special employees, after this, check if the employee has been hired for more than 60 months, give him an extra salary 3000.create or replace processure raise_sal (no in number) as vhidate Date; Vsal Emp.Sal% Type; Begin Select Hiredate, Sal Into Vhiredate, vsal from Emp where Empno = NO; if Months_between (Sysdate, VHIREDATE)> 60 Then vsal: = NVL (vsal, 0) * 1.1 3000; Else Vsal: = NVL (vsal, 0) * 1.1; end if; Update EMP SET SAL = VSAL WHERE EMPNO = NO; END; / VARIABLE NO NUMBERBEGIN: NO: = 7369; End; / Execute raise_sal (: no) SELECT EMPNO, ENAME, SAL, Comm, Hiredate from Emp where Empno =: NO
Q. Write a function to check if the salary of the specified employee is valid. The salaries range from different positions is: Designation Raise Clerk 1500-2500 Salesman 2501-3500 Analyst 3501-4500 Others 4501 and Above. If the salary is within this range, the message "Salary IS OK" is displayed, otherwise, update salary is within this range. The most water value. A.Create or Replace Function Sal_level (no Emp.empno% Type) Return Char as vjob Emp.Job% Type; vsal Emp.sal% Type; Vmeseg Char (50); Begin Select Job, Sal Into Vjob, vsal from Emp Where Empno = no; if vjob = 'Clerk' Then IF vsal> = 1500 and vsal <= 2500 Then Vmesg: = 'Salary is OK.'; Else Vsal: = 1500; Vmesesg: = 'Have Updated Your Salary To' || TO_CHAR (VSAL); END IF; Elsif vjob = 'Salesman' Then IF VSAL> = 2501 and vsal <= 3500 Then Vmesg: = 'Salary IS OK.'; Else Vsal: = 2501; Vmesesg: = 'Have Updated Your Salry To '|| to_CHAR (VSAL); END IF; Elsif vjob =' analyst 'TEN IF VSAL> = 3501 and vsal <= 4500 THEN VMESG: =' Salary IS OK. '; else vsal: = 3501; vmesesg: =' Have Updated Your Salary To '|| TO_CHAR (VSAL); END IF; Else IF vsal> = 4501 TEN VMESG: =' Salary IS OK. '; Else Vsal: = 4501; Vmesg: =' Have Updated Your Salary To '| | TO_CHAR (VSAL); END if; end if; Update EMP SET SAL = VSAL WHERE EMPNO = NO; RETURN VMPNO = NO; RETURN VMESG; END; / DECLARE VMESG CHAR (50); Vempno Emp.empno% Type; Begin Vempno: = & EMPNO; Vmesg : = SAL_LEVEL (Vempno); DBMS_ Output.put_line (vmesg); end; / - Select Empno, Ename, Sal, Comm, Hiredate from Emp where Empno =: NO; Q. Write a function to display the number of work days in this organization. A.CREATE OR REPLACE FUNCTION HIRE_DAY (No Emp.empnO% Type) Return Number as vhiredate Emp.hidate% TYPE; VDAY Number;
BEGIN SELECT hiredate INTO vhiredate FROM emp WHERE empno = no; vday: = CEIL (SYSDATE-vhiredate); RETURN vday; END; / DECLARE vday NUMBER; vempno emp.empno% TYPE; BEGIN vempno: = & empno; vday: = Hire_Day ( VemPNO); dbms_output.put_line (vDay); end; / - select Empno, ENAME, SAL, Comm, Hiredate from Emp WHERE Empno =: NO;
The fifth stage Q. Write a packet, it has two functions and two processes to operate the "EMP" table. The task 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 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, 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 S ELECT 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 = 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: = emppack.selsal (7369); DBMS_OUTPUT.PUT_LINE ( 'Total Salary is' || salary); END; / DECLARE department VARCHAR2 (30); BEGIN department: = emppack.seldname (7369); DBMS_OUTPUT.PUT_LINE ('Department Name IS' ||; end; / q. Write a database trigger to display the salary increase when employees are raised at any time. A.Create or Replace Trigger Emp_salupafter Update of Sal on Empfor Each RowDeclare vsal number; begin vsal: = NVL (: new.sal, 0) -nvl (: Old.sal, 0); if vsal <= 0 THEN RAISE_APPLICATION_ERROR (- 20001, 'Increased Sarary 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 to 5.00. A.CREATE OR REPLACE TRIGGER operate_time_limitedBEFORE INSERT OR UPDATE OR DELETE ON emp - FOR EACH ROWDECLARE 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_presidentBEFORE INSERT OR UPDATE ON empFOR EACH ROWWHEN (UPPER (NEW.job) = 'PRESIDENT') DECLARE vCount NUMBER; BEGIN SELECT COUNT (job) INTO vCount FROM emp WHERE UPPER (job) = 'PRESIDENT'; - Statistize 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 that 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_deptnobefore detele release from Emp where deptno =: Old.deptno; END; / ---- 8i. Information from ACCP
