Advertisement
apcika-20

Untitled

Nov 21st, 2017
78
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.78 KB | None | 0 0
  1. 1) create or replace procedure inc_lowsalary
  2. is
  3. begin
  4. UPDATE employees
  5. SET salary = salary * 1.15
  6. WHERE employee_id IN (
  7. SELECT e.employee_id
  8. FROM employees e JOIN employees m
  9. ON e.manager_id = m.employee_id
  10. WHERE e.salary < m.salary /2);
  11. end;
  12.  
  13. execute inc_lowsalary();
  14.  
  15.  
  16. 2) create or replace function emp_dept(emp_id in employees.employee_id%type)
  17. return varchar2 is
  18. total varchar2(50);
  19. begin
  20. select d.department_name into total
  21. from employees e,departments d
  22. where e.department_id=d.department_id
  23. and e.employee_id = emp_id;
  24. return total;
  25. end emp_dept;
  26. გამოძახება declare
  27. emp_fname employees.first_name%type;
  28. emp_lname employees.last_name%type;
  29. dep_dname departments.department_name%type;
  30. emp_job jobs.job_title%type;
  31. cursor cur is
  32. select e.first_name,e.last_name,emp_dept(e.employee_id),j.JOB_TITLE
  33. from employees e,jobs j
  34. where e.job_id=j.job_id
  35. and j.job_id like'%CLERK%' ;
  36. begin
  37. open cur;
  38. loop
  39. fetch cur into emp_fname,emp_lname,dep_dname,emp_job;
  40. exit when cur%notfound;
  41. dbms_output.put_line('First name ' || emp_fname || ', Last Name : ' || emp_lname || 'Department Name: ' || dep_dname || ' Job : ' ||
  42. emp_job);
  43. end loop;
  44. close cur;
  45. end;
  46.  
  47. 3) create or replace procedure inc_comm(dno number,pre number)
  48. is
  49. begin
  50. update employees
  51. set COMMISSION_PCT=COMMISSION_PCT + months_between(sysdate,hire_date)/12/100 * pre
  52. where department_id = dno;
  53. end;
  54.  
  55. execute inc_comm(80,1);
  56.  
  57. 4) create or replace function emp_dept(emp_id in employees.employee_id%type)
  58. return varchar2 is
  59. dept varchar2(50);
  60. begin
  61. select lc.street_address
  62. into dept
  63. from employees e,departments d,locations lc
  64. where e.department_id=d.department_id
  65. and d.location_id=lc.location_id
  66. and e.employee_id=emp_id;
  67. return dept;
  68. end;
  69. გამოძახება : declare
  70. emp_lname employees.last_name%type;
  71. loc_stradd locations.street_address%type;
  72. dep_dname departments.department_name%type;
  73. cursor cur is
  74. select e.last_name,emp_dept(e.employee_id),d.department_name
  75. from employees e, departments d
  76. where e.department_id=d.department_id;
  77. begin
  78. open cur;
  79. loop
  80. fetch cur into emp_lname,loc_stradd,dep_dname;
  81. exit when cur%notfound;
  82. dbms_output.put_line('--------------------------');
  83. dbms_output.put_line('Last Name :' || emp_lname );
  84. dbms_output.put_line('Department Address:' || loc_stradd);
  85. dbms_output.put_line('Department Name:' || dep_dname);
  86. end loop;
  87. close cur;
  88. end;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement