Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 1) create or replace procedure inc_lowsalary
- is
- begin
- UPDATE employees
- SET salary = salary * 1.15
- WHERE employee_id IN (
- SELECT e.employee_id
- FROM employees e JOIN employees m
- ON e.manager_id = m.employee_id
- WHERE e.salary < m.salary /2);
- end;
- execute inc_lowsalary();
- 2) create or replace function emp_dept(emp_id in employees.employee_id%type)
- return varchar2 is
- total varchar2(50);
- begin
- select d.department_name into total
- from employees e,departments d
- where e.department_id=d.department_id
- and e.employee_id = emp_id;
- return total;
- end emp_dept;
- გამოძახება declare
- emp_fname employees.first_name%type;
- emp_lname employees.last_name%type;
- dep_dname departments.department_name%type;
- emp_job jobs.job_title%type;
- cursor cur is
- select e.first_name,e.last_name,emp_dept(e.employee_id),j.JOB_TITLE
- from employees e,jobs j
- where e.job_id=j.job_id
- and j.job_id like'%CLERK%' ;
- begin
- open cur;
- loop
- fetch cur into emp_fname,emp_lname,dep_dname,emp_job;
- exit when cur%notfound;
- dbms_output.put_line('First name ' || emp_fname || ', Last Name : ' || emp_lname || 'Department Name: ' || dep_dname || ' Job : ' ||
- emp_job);
- end loop;
- close cur;
- end;
- 3) create or replace procedure inc_comm(dno number,pre number)
- is
- begin
- update employees
- set COMMISSION_PCT=COMMISSION_PCT + months_between(sysdate,hire_date)/12/100 * pre
- where department_id = dno;
- end;
- execute inc_comm(80,1);
- 4) create or replace function emp_dept(emp_id in employees.employee_id%type)
- return varchar2 is
- dept varchar2(50);
- begin
- select lc.street_address
- into dept
- from employees e,departments d,locations lc
- where e.department_id=d.department_id
- and d.location_id=lc.location_id
- and e.employee_id=emp_id;
- return dept;
- end;
- გამოძახება : declare
- emp_lname employees.last_name%type;
- loc_stradd locations.street_address%type;
- dep_dname departments.department_name%type;
- cursor cur is
- select e.last_name,emp_dept(e.employee_id),d.department_name
- from employees e, departments d
- where e.department_id=d.department_id;
- begin
- open cur;
- loop
- fetch cur into emp_lname,loc_stradd,dep_dname;
- exit when cur%notfound;
- dbms_output.put_line('--------------------------');
- dbms_output.put_line('Last Name :' || emp_lname );
- dbms_output.put_line('Department Address:' || loc_stradd);
- dbms_output.put_line('Department Name:' || dep_dname);
- end loop;
- close cur;
- end;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement