SQL> --explicit cursors SQL> set serveroutput on SQL> select &columnname 2 from regıons 3 SQL> desc regions Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) SQL> select &columnname 2 from regions; Enter value for columnname: REGION_ID old 1: select &columnname new 1: select REGION_ID REGION_ID ---------- 1 2 3 4 SQL> set verify off SQL> / Enter value for columnname: REGION_ID REGION_ID ---------- 1 2 3 4 SQL> set timing on SQL> / Enter value for columnname: REGION_NAME REGION_NAME ------------------------- Europe Americas Asia Middle East and Africa Elapsed: 00:00:00.03 SQL> set timing off SQL> --explıcıt cursors SQL> --write an anonymous block to display last name and salary of all SQL> --employees who work ın a gıven department SQL> --read the department id from keyboard SQL> des employees SP2-0734: unknown command beginning "des emplo..." - rest of line ignored. SQL> desc employees Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SQL> declare 2 cursor emp_list is 3 select last_name, salary 4 from employees 5 where department_id=&a_department_id; 6 v_LAST_NAME employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 open emp_list; 10 loop 11 fetch emp_list into v_LAST_NAME,v_salary; 12 exit when emp_list%notfound; 13 dbms_output.put_line(v_last_name||' earns '||v_salary||'TL'); 14 end loop; 15 close emp_list; 16 end; 17 / Enter value for a_department_id: 10 Whalen earns 4400TL PL/SQL procedure successfully completed. SQL> / Enter value for a_department_id: 30 Raphaely earns 11000TL Khoo earns 3100TL Baida earns 2900TL Tobias earns 2800TL Himuro earns 2600TL Colmenares earns 2500TL PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_list is 3 select last_name, salary 4 from employees 5 where department_id=&a_department_id; 6 v_LAST_NAME employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 open emp_list; 10 loop 11 fetch emp_list into v_LAST_NAME,v_salary; 12 dbms_output.put_line(v_last_name||' earns '||v_salary||'TL'); 13 exit when emp_list%notfound; 14 end loop; 15 close emp_list; 16* end; SQL> / Enter value for a_department_id: 10 Whalen earns 4400TL Whalen earns 4400TL PL/SQL procedure successfully completed. SQL> / Enter value for a_department_id: 30 Raphaely earns 11000TL Khoo earns 3100TL Baida earns 2900TL Tobias earns 2800TL Himuro earns 2600TL Colmenares earns 2500TL Colmenares earns 2500TL PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_list is 3 select last_name, salary 4 from employees 5 where department_id=&a_department_id; 6 v_LAST_NAME employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 open emp_list; 10 loop 11 fetch emp_list into v_LAST_NAME,v_salary; 12 exit when emp_list%notfound; 13 dbms_output.put_line(v_last_name||' earns '||v_salary||'TL'); 14 end loop; 15 dbms_output.put_line('The number of employees printed is '||emp_list%rowcount); 16 close emp_list; 17* end; 18 / Enter value for a_department_id: 10 Whalen earns 4400TL The number of employees printed is 1 PL/SQL procedure successfully completed. SQL> / Enter value for a_department_id: 30 Raphaely earns 11000TL Khoo earns 3100TL Baida earns 2900TL Tobias earns 2800TL Himuro earns 2600TL Colmenares earns 2500TL The number of employees printed is 6 PL/SQL procedure successfully completed. SQL> --handle the explıcıt cursor related exceptions by printing messages SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_list is 3 select last_name, salary 4 from employees 5 where department_id=&a_department_id; 6 v_LAST_NAME employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 open emp_list; 10 loop 11 fetch emp_list into v_LAST_NAME,v_salary; 12 exit when emp_list%notfound; 13 dbms_output.put_line(v_last_name||' earns '||v_salary||'TL'); 14 end loop; 15 dbms_output.put_line('The number of employees printed is '||emp_list%rowcount); 16 close emp_list; 17 exception 18 when invalid_cursor then 19 dbms_output.put_line('you forgot to open the cursor dummy!'); 20 when cursor_already_open then 21 dbms_output.put_line('you already opened the cursor dummy!'); 22 when invalid_number or value_error then 23 dbms_output.put_line('data type or size mismatch'); 24 when others then 25 dbms_output.put_line('OMG!What is wrong? Why did this happen?'); 26* end; SQL> / Enter value for a_department_id: 10 Whalen earns 4400TL The number of employees printed is 1 PL/SQL procedure successfully completed. SQL> / Enter value for a_department_id: 30 Raphaely earns 11000TL Khoo earns 3100TL Baida earns 2900TL Tobias earns 2800TL Himuro earns 2600TL Colmenares earns 2500TL The number of employees printed is 6 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_list is 3 select last_name, salary 4 from employees 5 where department_id=&a_department_id; 6 v_LAST_NAME employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 --open emp_list; 10 loop 11 fetch emp_list into v_LAST_NAME,v_salary; 12 exit when emp_list%notfound; 13 dbms_output.put_line(v_last_name||' earns '||v_salary||'TL'); 14 end loop; 15 dbms_output.put_line('The number of employees printed is '||emp_list%rowcount); 16 close emp_list; 17 exception 18 when invalid_cursor then 19 dbms_output.put_line('you forgot to open the cursor dummy!'); 20 when cursor_already_open then 21 dbms_output.put_line('you already opened the cursor dummy!'); 22 when invalid_number or value_error then 23 dbms_output.put_line('data type or size mismatch'); 24 when others then 25 dbms_output.put_line('OMG!What is wrong? Why did this happen?'); 26* end; SQL> / Enter value for a_department_id: 10 you forgot to open the cursor dummy! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_list is 3 select last_name, salary 4 from employees 5 where department_id=&a_department_id; 6 v_LAST_NAME employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 open emp_list; 10 open emp_list; 11 loop 12 fetch emp_list into v_LAST_NAME,v_salary; 13 exit when emp_list%notfound; 14 dbms_output.put_line(v_last_name||' earns '||v_salary||'TL'); 15 end loop; 16 dbms_output.put_line('The number of employees printed is '||emp_list%rowcount); 17 close emp_list; 18 exception 19 when invalid_cursor then 20 dbms_output.put_line('you forgot to open the cursor dummy!'); 21 when cursor_already_open then 22 dbms_output.put_line('you already opened the cursor dummy!'); 23 when invalid_number or value_error then 24 dbms_output.put_line('data type or size mismatch'); 25 when others then 26 dbms_output.put_line('OMG!What is wrong? Why did this happen?'); 27* end; SQL> / Enter value for a_department_id: 10 you already opened the cursor dummy! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_list is 3 select last_name, salary 4 from employees 5 where department_id=&a_department_id; 6 v_LAST_NAME employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 open emp_list; 10 loop 11 fetch emp_list into v_LAST_NAME,v_salary; 12 exit when emp_list%notfound; 13 dbms_output.put_line(v_last_name||' earns '||v_salary||'TL'); 14 end loop; 15 open emp_list; 16 dbms_output.put_line('The number of employees printed is '||emp_list%rowcount); 17 close emp_list; 18 exception 19 when invalid_cursor then 20 dbms_output.put_line('you forgot to open the cursor dummy!'); 21 when cursor_already_open then 22 dbms_output.put_line('you already opened the cursor dummy!'); 23 when invalid_number or value_error then 24 dbms_output.put_line('data type or size mismatch'); 25 when others then 26 dbms_output.put_line('OMG!What is wrong? Why did this happen?'); 27* end; 28 / Enter value for a_department_id: 10 Whalen earns 4400TL you already opened the cursor dummy! PL/SQL procedure successfully completed. SQL> / Enter value for a_department_id: 30 Raphaely earns 11000TL Khoo earns 3100TL Baida earns 2900TL Tobias earns 2800TL Himuro earns 2600TL Colmenares earns 2500TL you already opened the cursor dummy! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_list is 3 select last_name, salary 4 from employees 5 where department_id=&a_department_id; 6 v_LAST_NAME employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 open emp_list; 10 loop 11 fetch emp_list into v_LAST_NAME,v_salary; 12 exit when emp_list%notfound; 13 dbms_output.put_line(v_last_name||' earns '||v_salary||'TL'); 14 end loop; 15 dbms_output.put_line('The number of employees printed is '||emp_list%rowcount); 16 open emp_list; 17 close emp_list; 18 exception 19 when invalid_cursor then 20 dbms_output.put_line('you forgot to open the cursor dummy!'); 21 when cursor_already_open then 22 dbms_output.put_line('you already opened the cursor dummy!'); 23 when invalid_number or value_error then 24 dbms_output.put_line('data type or size mismatch'); 25 when others then 26 dbms_output.put_line('OMG!What is wrong? Why did this happen?'); 27* end; SQL> / Enter value for a_department_id: 30 Raphaely earns 11000TL Khoo earns 3100TL Baida earns 2900TL Tobias earns 2800TL Himuro earns 2600TL Colmenares earns 2500TL The number of employees printed is 6 you already opened the cursor dummy! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_list is 3 select last_name, salary 4 from employees 5 where department_id=&a_department_id; 6 v_LAST_NAME employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 open emp_list; 10 loop 11 fetch emp_list into v_LAST_NAME,v_salary; 12 exit when emp_list%notfound; 13 dbms_output.put_line(v_last_name||' earns '||v_salary||'TL'); 14 end loop; 15 dbms_output.put_line('The number of employees printed is '||emp_list%rowcount); 16 close emp_list; 17 open emp_list; 18 exception 19 when invalid_cursor then 20 dbms_output.put_line('you forgot to open the cursor dummy!'); 21 when cursor_already_open then 22 dbms_output.put_line('you already opened the cursor dummy!'); 23 when invalid_number or value_error then 24 dbms_output.put_line('data type or size mismatch'); 25 when others then 26 dbms_output.put_line('OMG!What is wrong? Why did this happen?'); 27* end; SQL> / Enter value for a_department_id: 30 Raphaely earns 11000TL Khoo earns 3100TL Baida earns 2900TL Tobias earns 2800TL Himuro earns 2600TL Colmenares earns 2500TL The number of employees printed is 6 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_list is 3 select last_name, salary 4 from employees 5 where department_id=&a_department_id; 6 v_LAST_NAME employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 open emp_list; 10 loop 11 fetch emp_list into v_LAST_NAME,v_salary; 12 exit when emp_list%notfound or emp_list%rowcount>2; 13 dbms_output.put_line(v_last_name||' earns '||v_salary||'TL'); 14 end loop; 15 dbms_output.put_line('The number of employees printed is '||emp_list%rowcount); 16 close emp_list; 17 dbms_output.put_line('******'); 18 open emp_list; 19 loop 20 fetch emp_list into v_LAST_NAME,v_salary; 21 exit when emp_list%notfound; 22 dbms_output.put_line(v_last_name||' earns '||v_salary||'TL'); 23 end loop; 24 dbms_output.put_line('The number of employees printed is '||emp_list%rowcount); 25 close emp_list; 26 exception 27 when invalid_cursor then 28 dbms_output.put_line('you forgot to open the cursor dummy!'); 29 when cursor_already_open then 30 dbms_output.put_line('you already opened the cursor dummy!'); 31 when invalid_number or value_error then 32 dbms_output.put_line('data type or size mismatch'); 33 when others then 34 dbms_output.put_line('OMG!What is wrong? Why did this happen?'); 35* end; 36 / Enter value for a_department_id: 30 Raphaely earns 11000TL Khoo earns 3100TL The number of employees printed is 3 ****** Raphaely earns 11000TL Khoo earns 3100TL Baida earns 2900TL Tobias earns 2800TL Himuro earns 2600TL Colmenares earns 2500TL The number of employees printed is 6 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_list (p employees.department_id%type) is 3 select last_name, salary 4 from employees 5 where department_id=p; 6 v_LAST_NAME employees.last_name%type; 7 v_salary employees.salary%type; 8 begin 9 open emp_list(30); 10 loop 11 fetch emp_list into v_LAST_NAME,v_salary; 12 exit when emp_list%notfound or emp_list%rowcount>2; 13 dbms_output.put_line(v_last_name||' earns '||v_salary||'TL'); 14 end loop; 15 dbms_output.put_line('The number of employees printed is '||emp_list%rowcount); 16 close emp_list; 17 dbms_output.put_line('******'); 18 open emp_list(20); 19 loop 20 fetch emp_list into v_LAST_NAME,v_salary; 21 exit when emp_list%notfound; 22 dbms_output.put_line(v_last_name||' earns '||v_salary||'TL'); 23 end loop; 24 dbms_output.put_line('The number of employees printed is '||emp_list%rowcount); 25 close emp_list; 26 exception 27 when invalid_cursor then 28 dbms_output.put_line('you forgot to open the cursor dummy!'); 29 when cursor_already_open then 30 dbms_output.put_line('you already opened the cursor dummy!'); 31 when invalid_number or value_error then 32 dbms_output.put_line('data type or size mismatch'); 33 when others then 34 dbms_output.put_line('OMG!What is wrong? Why did this happen?'); 35* end; 36 / Raphaely earns 11000TL Khoo earns 3100TL The number of employees printed is 3 ****** Hartstein earns 13000TL Fay earns 6000TL The number of employees printed is 2 PL/SQL procedure successfully completed. SQL> --write an anonymous block that uses a cursor with parameter to SQL> --display employees who earn more than a given salary SQL> --For example: print all employees who earn more than 3000 SQL> --the salary should be passed as parameter to the cursor SQL> desc employees Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SQL> declare 2 cursor emp_by_salary(p_salary employees.salary%type) is 3 select last_name 4 from employees 5 where salary > p_salary; 6 v_last_name employees.LAST_NAME%type; 7 begin 8 open emp_by_salary(3000); 9 loop 10 fetch emp_by_salary into v_last_name; 11 exit when emp_by_salary%notfound; 12 dbms_output.put_line(v_lastname); 13 end loop; 14 close emp_by_salarr; 15 . SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_by_salary(p_salary employees.salary%type) is 3 select last_name 4 from employees 5 where salary > p_salary; 6 v_last_name employees.LAST_NAME%type; 7 begin 8 open emp_by_salary(3000); 9 loop 10 fetch emp_by_salary into v_last_name; 11 exit when emp_by_salary%notfound; 12 dbms_output.put_line(v_lastname); 13 end loop; 14 close emp_by_salary; 15* end; SQL> / dbms_output.put_line(v_lastname); * ERROR at line 12: ORA-06550: line 12, column 25: PLS-00201: identifier 'V_LASTNAME' must be declared ORA-06550: line 12, column 4: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_by_salary(p_salary employees.salary%type) is 3 select last_name 4 from employees 5 where salary > p_salary; 6 v_last_name employees.LAST_NAME%type; 7 begin 8 open emp_by_salary(3000); 9 loop 10 fetch emp_by_salary into v_last_name; 11 exit when emp_by_salary%notfound; 12 dbms_output.put_line(v_last_name); 13 end loop; 14 close emp_by_salary; 15* end; SQL> / King Kochhar De Haan Hunold Ernst Austin Pataballa Lorentz Greenberg Faviet Chen Sciarra Urman Popp Raphaely Khoo Weiss Fripp Kaufling Vollman Mourgos Nayer Bissot Mallin Ladwig Stiles Rajs Davies Russell Partners Errazuriz Cambrault Zlotkey Tucker Bernstein Hall Olsen Cambrault Tuvault King Sully McEwen Smith Doran Sewall Vishney Greene Marvins Lee Ande Banda Ozer Bloom Fox Smith Bates Kumar Abel Hutton Taylor Livingston Grant Johnson Taylor Fleaur Sarchand Bull Dellinger Chung Dilly Bell Everett McCain Walsh Whalen Hartstein Fay Mavris Baer Higgins Gietz PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_by_salary(p_salary employees.salary%type) is 3 select last_name 4 from employees 5 where salary > p_salary; 6 v_last_name employees.LAST_NAME%type; 7 begin 8 open emp_by_salary(10000); 9 loop 10 fetch emp_by_salary into v_last_name; 11 exit when emp_by_salary%notfound; 12 dbms_output.put_line(v_last_name); 13 end loop; 14 close emp_by_salary; 15 dbms_output.put_line('**************'); 16 open emp_by_salary(5000); 17 loop 18 fetch emp_by_salary into v_last_name; 19 exit when emp_by_salary%notfound; 20 dbms_output.put_line(v_last_name); 21 end loop; 22 close emp_by_salary; 23* end; SQL> / King Kochhar De Haan Greenberg Raphaely Russell Partners Errazuriz Cambrault Zlotkey Vishney Ozer Abel Hartstein Higgins ************** King Kochhar De Haan Hunold Ernst Greenberg Faviet Chen Sciarra Urman Popp Raphaely Weiss Fripp Kaufling Vollman Mourgos Russell Partners Errazuriz Cambrault Zlotkey Tucker Bernstein Hall Olsen Cambrault Tuvault King Sully McEwen Smith Doran Sewall Vishney Greene Marvins Lee Ande Banda Ozer Bloom Fox Smith Bates Kumar Abel Hutton Taylor Livingston Grant Johnson Hartstein Fay Mavris Baer Higgins Gietz PL/SQL procedure successfully completed. SQL> --cursor for loop is a special type of loop that can be used with explıcıt cursors SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_by_salary is 3 select last_name 4 from employees 5 where salary > 10000; 6 begin 7 for r in emp_by_salary 8 loop 9 dbms_output.put_line(r.last_name); 10 end loop; 11* end; 12 / King Kochhar De Haan Greenberg Raphaely Russell Partners Errazuriz Cambrault Zlotkey Vishney Ozer Abel Hartstein Higgins PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor emp_by_salary (p number) is 3 select last_name,first_name 4 from employees 5 where salary > p; 6 begin 7 for rrrr in emp_by_salary(10000) 8 loop 9 dbms_output.put_line(rrrr.first_name||' '||rrrr.last_name); 10 end loop; 11* end; SQL> / Steven King Neena Kochhar Lex De Haan Nancy Greenberg Den Raphaely John Russell Karen Partners Alberto Errazuriz Gerald Cambrault Eleni Zlotkey Clara Vishney Lisa Ozer Ellen Abel Michael Hartstein Shelley Higgins PL/SQL procedure successfully completed. SQL> spool off