1. DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name, last_name, department_id from employees
where manager_id=108;
empno employees.employee_id%TYPE;
fname employees.first_name%TYPE;
lname employees.last_name%TYPE;
depid employees.department_id%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO empno, fname, lname, depid;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(empno||' '||fname||' '||lname||' '||depid);
end loop;
end;
HASIL:
109 Daniel Faviet 100
110 John Chen 100
111 Ismael Sciarra 100
112 Jose Manuel Urman 100
113 Luis Popp 100
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))
2. DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name, last_name, department_id from employees
where job_id='ST_CLERK';
BEGIN
FOR emp_record IN emp_cursor
LOOP
EXIT WHEN emp_cursor%ROWCOUNT>5 OR emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_record.employee_id||' '||emp_record.first_name||' '||emp_record.last_name||' '||emp_record.department_id);
END LOOP;
end;
HASIL:
125 Julia Nayer 50
126 Irene Mikkilineni 50
127 James Landry 50
128 Steven Markle 50
129 Laura Bissot 50
)))))))))))))))))))))))))))))))))))))))))))))))))))))))))
3. DECLARE
CURSOR emp_cursor1 IS
SELECT employee_id, first_name, last_name, salary from employees
where job_id='IT_PROG' AND salary>5000;
CURSOR emp_cursor2 IS
SELECT employee_id, first_name, last_name, salary from employees
where job_id='IT_PROG' AND salary<4500;
empno employees.employee_id%TYPE;
fname employees.first_name%TYPE;
lname employees.last_name%TYPE;
depid employees.salary%TYPE;
BEGIN
OPEN emp_cursor1;
OPEN emp_cursor2;
DBMS_OUTPUT.PUT_LINE('Pegawai IT_PROG yang gajinya minimal 5000 adalah:');
LOOP
FETCH emp_cursor1 INTO empno, fname, lname, depid;
EXIT WHEN emp_cursor1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('- '||empno||' '||fname||' '||lname||' '||depid);
end loop;
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('Pegawai IT_PROG yang gajinya kurang dari 4500 adalah:');
LOOP
FETCH emp_cursor2 INTO empno, fname, lname, depid;
EXIT WHEN emp_cursor2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('- '||empno||' '||fname||' '||lname||' '||depid);
end loop;
end;
HASIL:
Pegawai IT_PROG yang gajinya minimal 5000 adalah:
- 103 Alexander Hunold 9000
- 104 Bruce Ernst 6000
Pegawai IT_PROG yang gajinya kurang dari 4500 adalah:
- 107 Diana Lorentz 4200
Artikel keren lainnya:
Belum ada tanggapan untuk "Belajar Data Base"
Posting Komentar