Advertisement
RashedBQ

creating table exp_emp

Oct 10th, 2021
312
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ------------------------------- creating table exp_emp
  2.  
  3. CREATE TABLE exp_emp(
  4.     idd NUMBER(4) primary key,
  5.     ename VARCHAR2(50),
  6.     sal NUMBER(10),
  7.     h_date DATE
  8. );
  9.  
  10.  
  11.  
  12. ------------------------------  add employees information
  13.  
  14. DECLARE
  15. CURSOR emp_add IS
  16.         SELECT *
  17.         FROM employees;
  18.        
  19.         emp_rec emp_add%ROWTYPE;
  20.        
  21.        
  22.        
  23. BEGIN
  24.  
  25.  
  26. OPEN emp_add;
  27. FETCH emp_add INTO emp_rec;
  28.  
  29. WHILE emp_add%found
  30. LOOP
  31.  
  32. INSERT INTO exp_emp
  33. VALUES (emp_rec.employee_id,CONCAT(emp_rec.first_name,emp_rec.last_name),emp_rec.salary,ADD_MONTHS(emp_rec.hire_date,-300));
  34.  
  35.  
  36.  
  37. FETCH emp_add INTO emp_rec;
  38. END LOOP;
  39.  
  40. CLOSE emp_add;
  41.  
  42.  
  43.  
  44. END;
  45.  
  46.  
  47.  
  48. ------------------------------------------------------- show employees information
  49.  
  50. DECLARE
  51. CURSOR emp_show IS
  52.         SELECT *
  53.         FROM exp_emp;
  54.        
  55.         emp_rec emp_show%ROWTYPE;
  56.        
  57.        
  58.        
  59. BEGIN
  60.  
  61.  
  62. OPEN emp_show;
  63. FETCH emp_show INTO emp_rec;
  64.  
  65. WHILE emp_show%found
  66. LOOP
  67.  
  68.  
  69. DBMS_OUTPUT.put_line('EMP ID: '||emp_rec.idd);
  70. DBMS_OUTPUT.put_line('First Name: '||emp_rec.ename);
  71. DBMS_OUTPUT.put_line('Salary : '||emp_rec.sal);
  72. DBMS_OUTPUT.put_line('Length of service : '||ROUND(MONTHS_BETWEEN(SYSDATE,emp_rec.h_date)/12)||' Years');
  73. DBMS_OUTPUT.put_line('===============================');
  74.  
  75.  
  76.  
  77. FETCH emp_show INTO emp_rec;
  78. END LOOP;
  79.  
  80. CLOSE emp_show;
  81.  
  82.  
  83.  
  84. END;
Advertisement
RAW Paste Data Copied
Advertisement