Section 5 Lesson 1: Introduction to Explicit Cursors

Vocabulary
Identify the vocabulary word for each definition below:

CURSOR EXPLICIT Declared by the programmer for queries that return more than one row

CURSOR A label for a context area or a pointer to the context area

CLOSE Disables a cursor, releases the context area, and undefines the active
set
CONTEXT AREA An allocated memory area used to store the data processed by a SQL
statement
CURSOR IMPLICIT Defined automatically by Oracle for all SQL DML statements, and for
SELECT statements that return only one row
OPEN Statement that executes the query associated with the cursor, identifies
the active set, and positions the cursor pointer to the first row
FETCH Statement that retrieves the current row and advances the cursor to the
next row either until there are no more rows or until a specified
condition is met
ACTIVE SET The set of rows returned by a multiple row query in an explicit cursor
operation

Try It / Solve It
1. In your own words, explain the difference between implicit and explicit cursors.

Cursorul implicit se foloseste atunci cand se returneaza un rand, iar cel explicit se foloseste atunci
cand se retuneaza mai mult de un rand.

2. Which SQL statement can use either an explicit or an implicit cursor, as needed?

SELECT

3. List two circumstances in which you would use an explicit cursor.

- atunci cand se returneaza mai mult de un rand

4. Exercise using wf_currencies tables:

A. Write a PL/SQL block to declare a cursor called wf_currencies_cur. The cursor will be used to
read and display all rows from the wf_currencies table. You will need to retrieve
currency_code and currency_name, ordered by ascending currency_name.

B. Add a statement to open the wf_currencies_cur cursor.

C. Add variable declarations and an executable statement to read ONE row through the
wf_currencies_cur cursor into local variables.
 D. Add a statement to display the fetched row, and a statement to close the wf_currencies_cur
cursor.

E. Run your block to check that it works. It should display: AFA Afghani.

DECLARE
CURSOR wf_currencies_cur IS
SELECT currency_code, currency.name FROM wf_currencies;
v_code wf_currencies.currency_code%TYPE;
v_name wf_currencies.currency.name%TYPE;
BEGIN
OPEN wf_currencies_cur;
FETCH wf_currencies_cur INTO v_code, v_name;
DBMS_OUTPUT.PUT_LINE(v_code || v_name);
CLOSE wf_currencies_cur;
END;

F. Your cursor in question 4 fetched and displayed only one row. Modify the block so that it
fetches and displays all the rows, using a LOOP and EXIT statement. Test your modified block.
It should fetch and display 160 rows. If it displays more or less than 160 rows, check that your
EXIT statement is in the correct place in the code.

DECLARE
CURSOR wf_currencies_cur IS
SELECT currency_code, currency_name FROM wf_currencies;
v_code wf_currencies.currency_code%TYPE;
v_name wf_currencies.currency_name%TYPE;
BEGIN
OPEN wf_currencies_cur;
LOOP
FETCH wf_currencies_cur INTO v_code, v_name;
DBMS_OUTPUT.PUT_LINE(v_code || v_name);
EXIT WHEN wf_currencies_cur%NOTFOUND;
END LOOP;
CLOSE wf_currencies_cur;
END;

AED Emirati dirham BOB Boliviano

AFA Afghani BRL Real
ALL Lek BSD Bahamian dollar
AMD Dram BTN Ngultrum
ANG Netherlands Antillean guilder BWP Pula
AOA Kwanza BYB Belarusian ruble
ARS Argentine peso BZD Belizean dollar
AUD Australian dollar CAD Canadian dollar
AWG Aruban guilder CDF Congolese franc
AZM Azerbaijani Manat CHF Swiss franc
BAM Marka CLP Chilean peso
BBD Barbadian dollar CNY yuan
BDT Taka COP Colombian peso
BGL Lev CRC Costa Rican colon
BIF Burundi franc CUC Convertible peso
BMD Bermudian dollar CUP Cuban peso
BND Bruneian dollar CVE Cape Verdean escudo
CYP Cypriot pound MWK Malawian kwacha
CZK Czech koruna MXN Mexican peso
DJF Djiboutian franc MYR ringgit
DKK Danish krone MZM metical
DOP Dominican peso NAD Namibian dollar
DZD Algerian dinar NGN naira
EEK Estonian kroon NID New Iraqi dinar
EGP Egyptian pound NOK Norwegian krone
ERN nakfa NONE No currency used
ETB birr NPR Nepalese rupee
EUR Euro NZD New Zealand dollar
FJD Fijian dollar PAB balboa
FKP Falkland pound PEN nuevo sol
GBP British pound PGK kina
GEL lari PKR Pakistani rupee
GHC cedi PLN zloty
GIP Gibraltar pound PYG guarani
GMD dalasi QAR Qatari rial
GNF Guinean franc RMB Renminbi
GTQ quetzal RUR Russian ruble
GYD Guyanese dollar SAR Saudi riyal
HKD Hong Kong dollar SAT tala
HNL lempira SBD Solomon Islands dollar
HRK kuna SCR Seychelles rupee
HTG gourde SEK Swedish krona
HUF forint SGD Singapore dollar
IDR Indonesian rupiah SHP Saint Helenian pound
ILS new Israeli shekel SIT tolar
INR Indian rupee SLL leone
IRR Iranian rial SOS Somali shilling
ISK Icelandic krona SRD Surinam dollar
JMD Jamaican dollar STD dobra
JOD Jordanian dollar SZL lilangeni
JPY yen THB baht
KD Kuwaiti dinar TMM Turkmen manat
KES Kenyan shilling TND Tunisian dinar
KHR riel TTD Trinidad and Tobago dollar
KMF Comoran franc TWD new Taiwan dollar
KPW North Korean won UAH hryvnia
KRW South Korean won UGX Ugandan shilling
KYD Caymanian dollar USD US Dollar
KZT tenge UYU Uruguayan peso
LAK kip UZS Uzbekistani soum
LBP Lebanese pound VEB bolivar
LKR Sri Lankan rupee VND dong
LRD Liberian dollar VUV vatu
LSL loti XAF Communaute Financiere Africaine
LTL litas franc
LVL Latvian lat XCD East Caribbean dollar
LYD Libyan dinar XOF Communaute Financiere Africaine
MAD Moroccan dirham franc
MDL Moldovan leu XPF Comptoirs Francais du Pacifique
MGA Madagascar ariary franc
MKD Macedonian denar YER Yemeni rial
MMK Kyat YTL Turkish lira
MNT togrog/tugrik ZAR South African rand
MOP pataca ZMK Zambian kwacha
MRO ouguiya ZWD Zimbabwean dollar
MTL Maltese lira NIO gold cordoba
MUR Mauritian rupee OMR Omani rial
MVR rufiyaa PHP Philippine peso
RON leu TOP paanga
RWF Rwandan franc TZS Tanzanian shilling
SDD Sudanese dinar TZS Tanzanian shilling
SKK Slovak koruna
SYP Syrian pound Statement processed.

G. Write and test a PL/SQL block to read and display all the rows in the wf_countries table for all
countries in region 5 (South America region). For each selected country, display the
country_name, national_holiday_date, and national_holiday_name. Display only those
countries having a national holiday date that is not null. Save your code (you will need it in the
next practice).

DECLARE
CURSOR wf_holiday_cursor IS
SELECT country_name, national_holiday_date, national_holiday_name FROM
wf_countries where region_id=5;
v_country_name wf_countries.country_name%TYPE ;
v_holiday wf_countries.national_holiday_date%TYPE;
v_hname wf_countries.national_holiday_name%TYPE;
BEGIN
OPEN wf_holiday_cursor ;
LOOP
FETCH wf_holiday_cursor INTO v_country_name, v_holiday, v_hname;
EXIT WHEN wf_holiday_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_country_name||' '||v_holiday||' '||v_hname);
END LOOP;
CLOSE wf_holiday_cursor;
END;

South Georgia and the South Sandwich Islands

Argentine Republic 25-May Revolution Day
Republic of Bolivia 6-Aug Independence Day
Federative Republic of Brazil 7-Sep Independence Day
Falkland Islands 14-Jun Liberation Day
Co-operative Republic of Guyana 23-Feb Republic Day
Republic of Peru 28-Jul Independence Day
Republic of Paraguay 15-May Independence Day
Republic of Ecuador 10-Aug Independence Day
Oriental Republic of Uruguay 25-Aug Independence Day
Bolivarian Republic of Venezuela 5-Jul Independence Day
Republic of Chile 18-Sep Independence Day
Republic of Colombia 20-Jul Independence Day
Republic of Suriname 25-Nov Independence Day
Department of Guiana 14-Jul Bastille Day

Statement processed.

5. Identify three guidelines for declaring and using explicit cursors.

- nu se foloseste INTO in sectiunea declarative
- %NOTFOUND pentru testarea conditiei de iesire
- dupa ce a fost inchis un cursor, el nu mai poate fi apelat decat daca se redeschide
Extension Exercise
1. Write a PL/SQL block to read and display the names of world regions, with a count of the
number of countries in each region. Include only those regions having at least 10 countries. Order
your output by ascending region name.

DECLARE
CURSOR region_cursor IS
SELECT region_id, COUNT(*) AS how_many
FROM wf_countries
GROUP BY region_id
HAVING COUNT(*) > 10;
v_reg wf_countries.region_id%TYPE;
nr PLS_INTEGER;
BEGIN
OPEN region_cursor;
LOOP
FETCH region_cursor INTO v_reg, nr;
DBMS_OUTPUT.PUT_LINE(v_reg||' -> '||nr);
EXIT WHEN region_cursor%NOTFOUND;
END LOOP;
CLOSE region_cursor;
END;
5 -> 15
9 -> 28
11 -> 21
18 -> 17
29 -> 25
35 -> 16
39 -> 16
145 -> 16
151 -> 13
155 -> 14
155 -> 14

Statement processed.
Section 5 Lesson 2: Using Explicit Cursor Attributes
Vocabulary
Identify the vocabulary word for each definition below:

%ROWTYPE Declares a record with the same fields as the cursor on which it is based

RECORD A composite data type in PL/SQL, consisting of a number of fields each

with their own name and data type
%ISOPEN Returns the status of the cursor

%ROWCOUNT An attribute that processes an exact number of rows or counts the

number of rows fetched in a loop
%NOTFOUND An attribute used to determine whether the most recent FETCH
statement successfully returned a row

Try It/Solve It
1. In your own words, explain the advantage of using %ROWTYPE to declare a record structure
based on a cursor declaration.

 Preia tipurile de data ale tuturor datelor preluate din baza de date

2. Write a PL/SQL block to read through rows in the wf_countries table for all countries in region 5
(South America region). For each selected country, display the country_name,
national_holiday_date, and national_holiday_name. Use a record structure to hold all the columns
selected from the wf_countries table.

Hint: This exercise is very similar to question 4G in the previous lesson. Use your solution as a
starting point for this exercise.

DECLARE
CURSOR wf_holiday_cursor IS
SELECT country_name, national_holiday_date, national_holiday_name
FROM wf_countries where region_id=5;
wf_c_record wf_holiday_cursor%ROWTYPE;
BEGIN
OPEN wf_holiday_cursor ;
LOOP
FETCH wf_holiday_cursor INTO wf_c_record;
EXIT WHEN wf_holiday_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(wf_c_record.country_name||'
'||wf_c_record.national_holiday_date||' '||wf_c_record.national_holiday_name);
END LOOP;
CLOSE wf_holiday_cursor;
END;
 South Georgia and the South Sandwich Islands
Argentine Republic 25-May Revolution Day
Republic of Bolivia 6-Aug Independence Day
Federative Republic of Brazil 7-Sep Independence Day
Falkland Islands 14-Jun Liberation Day
Co-operative Republic of Guyana 23-Feb Republic Day
Republic of Peru 28-Jul Independence Day
Republic of Paraguay 15-May Independence Day
Republic of Ecuador 10-Aug Independence Day
Oriental Republic of Uruguay 25-Aug Independence Day
Bolivarian Republic of Venezuela 5-Jul Independence Day
Republic of Chile 18-Sep Independence Day
Republic of Colombia 20-Jul Independence Day
Republic of Suriname 25-Nov Independence Day
Department of Guiana 14-Jul Bastille Day

Statement processed.

3. For this exercise, you use the employees table. Create a PL/SQL block that fetches and displays the
six employees with the highest salary. For each of these employees, display the first name, last
name, job id, and salary. Order your output so that the employee with the highest salary is
displayed first. Use %ROWTYPE and the explicit cursor attribute %ROWCOUNT.

DECLARE
CURSOR emp_cursor IS
SELECT first_name, last_name, job_id, salary
FROM employees
ORDER BY salary DESC;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor ;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%ROWCOUNT > 6;
DBMS_OUTPUT.PUT_LINE(emp_record.first_name||'
'||emp_record.last_name||' '||emp_record.job_id||' '||emp_record.salary);
END LOOP;
CLOSE emp_cursor;
END;
Steven King ST_CLERK 26400
Neena Kochhar ST_CLERK 18700
Lex De Haan ST_CLERK 18700
Michael Hartstein MK_MAN 14300
Shelley Higgins AC_MGR 13200
Ellen Abel SA_REP 12100

Statement processed.

4. Look again at the block you created in question 3. What if you wanted to display 21 employees
instead of 6? There are only 20 rows in the employees table. What do you think would happen?
 Se repeta la infinit…
5. In real life we would not know how many rows the table contained. Modify your block from
question 3 so that it will exit from the loop when either 21 rows have been fetched and displayed,
or when there are no more rows to fetch. Test the block again.
Section 5 Lesson 3: Cursor FOR Loops
Vocabulary
Identify the vocabulary word for each definition below:

FOR cursor (Loop) Automates standard cursor-handling operations such as OPEN,

FETCH, %NOTFOUND, and CLOSE so that they do not need to be
coded explicitly

Try It / Solve It
1. Describe two benefits of using a cursor FOR loop
 Nu se mai deschide si inchide cursorul explicit
 Nu se mai declara record-ul

2. Modify the following PL/SQL block so that it uses a cursor FOR loop. Keep the explicit cursor
declaration in the DECLARE section. Test your changes.

DECLARE
CURSOR wf_countries_cur IS
SELECT country_name, national_holiday_name,
national_holiday_date
FROM wf_countries
WHERE region_id = 5;
wf_countries_rec wf_countries_cur%ROWTYPE;
BEGIN
OPEN wf_countries_cur;
LOOP
FETCH wf_countries_cur INTO wf_countries_rec;
EXIT WHEN wf_countries_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('Country: ' ||
wf_countries_rec.country_name
|| ' National holiday: '||
wf_countries_rec.national_holiday_name || ',
held on: '||
wf_countries_rec.national_holiday_date);
END LOOP;
CLOSE wf_countries_cur;
END;
 DECLARE
CURSOR wf_countries_cur IS
SELECT country_name, national_holiday_name, national_holiday_date
FROM wf_countries WHERE region_id = 5;
BEGIN
FOR wf_countries_rec IN wf_countries_cur
LOOP
DBMS_OUTPUT.PUT_LINE ('Country: ' || wf_countries_rec.country_name
|| ' National holiday: '|| wf_countries_rec.national_holiday_name || ', held on: '||
wf_countries_rec.national_holiday_date);
END LOOP;
END;

Country: South Georgia and the South Sandwich Islands National holiday: , held on:
Country: Argentine Republic National holiday: Revolution Day, held on: 25-May
Country: Republic of Bolivia National holiday: Independence Day, held on: 6-Aug
Country: Federative Republic of Brazil National holiday: Independence Day, held
on: 7-Sep
Country: Falkland Islands National holiday: Liberation Day, held on: 14-Jun
Country: Co-operative Republic of Guyana National holiday: Republic Day, held on:
23-Feb
Country: Republic of Peru National holiday: Independence Day, held on: 28-Jul
Country: Republic of Paraguay National holiday: Independence Day, held on: 15-May
Country: Republic of Ecuador National holiday: Independence Day, held on: 10-Aug
Country: Oriental Republic of Uruguay National holiday: Independence Day, held on:
25-Aug
Country: Bolivarian Republic of Venezuela National holiday: Independence Day, held
on: 5-Jul
Country: Republic of Chile National holiday: Independence Day, held on: 18-Sep
Country: Republic of Colombia National holiday: Independence Day, held on: 20-Jul
Country: Republic of Suriname National holiday: Independence Day, held on: 25-Nov
Country: Department of Guiana National holiday: Bastille Day, held on: 14-Jul

Statement processed.

3. Modify your answer to question 2 to declare the cursor using a subquery in the FOR … LOOP
statement, rather than in the declaration section. Test your changes again.

BEGIN
FOR wf_countries_rec IN (SELECT country_name, national_holiday_name,
national_holiday_date FROM wf_countries WHERE region_id = 5)
LOOP
DBMS_OUTPUT.PUT_LINE ('Country: ' || wf_countries_rec.country_name
|| ' National holiday: '|| wf_countries_rec.national_holiday_name || ', held on: '||
wf_countries_rec.national_holiday_date);
END LOOP;
END;
4. Using the wf_countries table, write a cursor that returns countries with a highest_elevation greater
than 8,000 m. For each country, display the country_name, highest_elevation, and climate. Use a
cursor FOR loop, declaring the cursor using a subquery in the FOR … LOOP statement.

BEGIN
FOR wf_countries_rec IN (SELECT country_name, highest_elevation, climate
FROM wf_countries WHERE highest_elevation > 8000)
LOOP
DBMS_OUTPUT.PUT_LINE (wf_countries_rec.country_name
|| ' --> '|| wf_countries_rec.highest_elevation || ' --> '|| wf_countries_rec.climate);
END LOOP;
END;

Islamic Republic of Pakistan --> 8611 --> mostly hot, dry desert; temperate in
northwest; arctic in north
Kingdom of Nepal --> 8850 --> varies from cool summers and severe winters in north
to subtropical summers and mild winters in south
Republic of India --> 8598 --> varies from tropical monsoon in south to temperate
in north
Peoples Republic of China --> 8850 --> extremely diverse; tropical in south to
subarctic in north

Statement processed.

5. This question uses a join of the wf_spoken_languages and wf_countries tables with a GROUP BY
and HAVING clause.

Write a PL/SQL block to fetch and display all the countries that have more than six spoken
languages. For each such country, display country_name and the number of spoken languages.
Use a cursor FOR loop, but declare the cursor explicitly in the DECLARE section. After all the
rows have been fetched and displayed, display an extra row showing the total number of countries
having more than six languages. (Hint: Declare a variable to hold the value of %ROWCOUNT.)

DECLARE
CURSOR wf_countries_cur IS
SELECT country_name, COUNT(*) AS how_many FROM wf_countries
GROUP BY spoken_languages HAVING COUNT(*) > 6;
v_nr PLS_INTEGER;
BEGIN
FOR wf_countries_rec IN wf_countries_cur
LOOP
DBMS_OUTPUT.PUT_LINE ( wf_countries_rec.country_name || ' -> '||
wf_countries_rec.spoken_languages);
v_nr:=wf_countries_cur%ROWCOUNT;
END LOOP;
DBMS_OUTPUT.PUT_LINE (v_nr);
END;

6. Why did your block in question 4 need to declare the cursor explicitly, instead of declaring it as a
 subquery in the FOR … LOOP statement?

Section 5 Lesson 4: Cursors with Parameters

Vocabulary
No new vocabulary for this lesson

Try It / Solve It
1. Describe the benefit of using one or more parameters with a cursor.

2. Write a PL/SQL block to display the country name and the area of each country in a chosen region.
The region_id should be passed to the cursor as a parameter. Test your block using two region_ids:
5 (South America) and 30 (Eastern Asia). Do not use a cursor FOR loop.

DECLARE
CURSOR c_country(p_region_id NUMBER) IS SELECT country_name, area FROM
wf_countries WHERE region_id = p_region_id;
v_country_record c_country%ROWTYPE;
BEGIN
OPEN c_country(5);
LOOP
FETCH c_country INTO v_country_record;
EXIT WHEN c_country%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_country_record.country_name||'
'||v_country_record.area);
END LOOP;
CLOSE c_country;
END;

3. Modify your answer to question 2 to use a cursor FOR loop. You must still declare the cursor
explicitly in the DECLARE section. Test it again using regions 5 and 30.

DECLARE
CURSOR c_country(p_region_id NUMBER) IS SELECT country_name, area FROM wf_countries
WHERE region_id = p_region_id;
v_country_record c_country%ROWTYPE;
BEGIN
FOR v_country_record IN c_country(5)
LOOP
DBMS_OUTPUT.PUT_LINE(v_country_record.country_name||' '||v_country_record.area);
END LOOP;
END;
4. Modify your answer to question 3 to display the country_name and area of each country in a
chosen region that has an area greater than a specific value. The region_id and specific area should
be passed to the cursor as two parameters. Test your block twice using region_id 5 (South
America): the first time with area = 200000 and the second time with area = 1000000.

DECLARE
CURSOR c_country(p_region_id NUMBER, p_area NUMBER) IS SELECT country_name, area
FROM wf_countries WHERE region_id = p_region_id AND area>p_area;
v_country_record c_country%ROWTYPE;
BEGIN
FOR v_country_record IN c_country(5, 200000)
LOOP
DBMS_OUTPUT.PUT_LINE(v_country_record.country_name||' '||v_country_record.area);
END LOOP;
END;

Argentine Republic 2766890

Republic of Bolivia 1098580
Federative Republic of Brazil 8511965
Co-operative Republic of Guyana 214970
Republic of Peru 1285220
Republic of Paraguay 406750
Republic of Ecuador 283560
Bolivarian Republic of Venezuela 912050
Republic of Chile 756950
Republic of Colombia 1138910

Statement processed.

DECLARE
CURSOR c_country(p_region_id NUMBER, p_area NUMBER) IS SELECT country_name, area
FROM wf_countries WHERE region_id = p_region_id AND area>p_area;
v_country_record c_country%ROWTYPE;
BEGIN
FOR v_country_record IN c_country(5, 1000000)
LOOP
DBMS_OUTPUT.PUT_LINE(v_country_record.country_name||' '||v_country_record.area);
END LOOP;
END;

Argentine Republic 2766890

Republic of Bolivia 1098580
Federative Republic of Brazil 8511965
Republic of Peru 1285220
Republic of Colombia 1138910

Statement processed.
Extension Exercise
5. Modify your answer to question 4 to fetch and display two sets of countries in a single execution
of the block. You should open and close the cursor twice, passing different parameter values to it
each time. Before each set of output rows, display the message “Region: <region_id> Minimum
Area: <area> ”., for example “Region: 5 Minimum Area: 200000”. Test your changes using (5,
200000) and (30, 500000).

DECLARE
CURSOR c_country(p_region_id NUMBER, p_area NUMBER) IS SELECT country_name, area
FROM wf_countries WHERE region_id = p_region_id AND area>p_area;
v_country_record c_country%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE(' REGION: 5 --- Minimum AREA: 200000');
FOR v_country_record IN c_country(5, 200000)
LOOP
DBMS_OUTPUT.PUT_LINE(v_country_record.country_name||' '||v_country_record.area);
END LOOP;
DBMS_OUTPUT.PUT_LINE(' REGION: 30 --- Minimum AREA: 500000');
FOR v_country_record IN c_country(30, 500000)
LOOP
DBMS_OUTPUT.PUT_LINE(v_country_record.country_name||' '||v_country_record.area);
END LOOP;
END;

REGION: 5 --- Minimum AREA: 200000

Argentine Republic 2766890
Republic of Bolivia 1098580
Federative Republic of Brazil 8511965
Co-operative Republic of Guyana 214970
Republic of Peru 1285220
Republic of Paraguay 406750
Republic of Ecuador 283560
Bolivarian Republic of Venezuela 912050
Republic of Chile 756950
Republic of Colombia 1138910
REGION: 30 --- Minimum AREA: 500000
Mongolia 1564116
Peoples Republic of China 9596960

Statement processed.
Section 5 Lesson 5: Using Cursors FOR UPDATE  Incompleta
Vocabulary
Identify the vocabulary word for each definition below:

FOR UPDATE Declares that each row is locked as it is being fetched so other users
can not modify the rows while the cursor is open
NOWAIT A keyword used to tell the Oracle server not to wait if the
requested rows have already been locked by another user

Try It / Solve It
In this Practice you will INSERT and later UPDATE rows in a new table: proposed_raises, which will
store details of salary increases proposed for suitable employees. Create this table by executing the
following SQL statement:

CREATE TABLE proposed_raises

(date_proposed DATE, date_approved DATE,
employee_id NUMBER(6), department_id
NUMBER(4), original_salary NUMBER(8,2),
proposed_new_salary NUMBER(8,2));

1. Write a PL/SQL block that inserts a row into proposed_raises for each eligible employee. The
eligible employees are those whose salary is below a chosen value. The salary value is passed as a
parameter to the cursor. For each eligible employee, insert a row into proposed_raises with
date_proposed = today’s date, date_appoved null, and proposed_new_salary 5% greater than the
current salary. The cursor should LOCK the employees rows so that no one can modify the
employee data while the cursor is open. Test your code using a chosen salary value of 5000.

DECLARE
CURSOR empcur(p_sal NUMBER) IS SELECT employee_id, department_id, salary FROM
employees WHERE salary < p_sal
FOR UPDATE NOWAIT;
v_emp_rec empcur%ROWTYPE;
BEGIN
FOR v_emp_rec IN empcur(5000)
LOOP
INSERT INTO proposed_raises(date_proposed, date_approved, employee_id, department_id,
original_salary, proposed_new_salary )
VALUES(SYSDATE, NULL, v_emp_rec.employee_id, v_emp_rec.department_id,
v_emp_rec.salary, v_emp_rec.salary*1.05 );
END LOOP;
COMMIT;
END;

2. SELECT from the proposed_raises table to see the results of your INSERT statements. There
should be six rows. If you run your block in question 1 more than once, make sure the
 proposed_raises table is empty before each test.

SELECT * FROM proposed_raises;

DATE_PROP DATE_APPR EMPLOYE DEPARTME ORIGINAL_SA PROPOSED_NEW_S
OSED OVED E_ID NT_ID LARY ALARY
19/Mar/2014 - 200 10 4840 5082
19/Mar/2014 - 141 50 3850 4042.5
19/Mar/2014 - 142 50 3410 3580.5
19/Mar/2014 - 143 50 2860 3003
19/Mar/2014 - 144 50 2750 2887.5
19/Mar/2014 - 107 60 4620 4851
6 rows returned in 0.01 seconds

[ DELETE FROM proposed_raises; ]

3. Before starting this question, ensure that there are six rows in proposed_raises. Now imagine that
these proposed salary increases have been approved by company management.

a. Write and execute a PL/SQL block to read each row from the proposed_raises table. For each
row, UPDATE the date_approved column with today’s date. Use the WHERE
CURRENT OF...syntax to UPDATE each row.

DECLARE
CURSOR empcur IS
SELECT date_approved FROM proposed_raises FOR UPDATE NOWAIT;
v_emp_rec empcur%ROWTYPE;
BEGIN
OPEN empcur;
LOOP
FETCH empcur INTO v_emp_rec;
EXIT WHEN empcur%NOTFOUND;
UPDATE proposed_raises SET date_approved =SYSDATE
WHERE CURRENT OF empcur;
END LOOP;
CLOSE empcur;
COMMIT;
END;
 b. SELECT from the proposed_raises table to view the updated data.

DATE_PROP DATE_APPR EMPLOYE DEPARTME ORIGINAL_SA PROPOSED_NEW_S

OSED OVED E_ID NT_ID LARY ALARY
19/Mar/2014 19/Mar/2014 200 10 4840 5082
19/Mar/2014 19/Mar/2014 141 50 3850 4042.5
19/Mar/2014 19/Mar/2014 142 50 3410 3580.5
19/Mar/2014 19/Mar/2014 143 50 2860 3003
19/Mar/2014 19/Mar/2014 144 50 2750 2887.5
19/Mar/2014 19/Mar/2014 107 60 4620 4851

c. Management has now decided that employees in department 50 cannot have a salary increase
after all. Modify your code from question 3 to DELETE employees in department 50 from
proposed_raises. This could be done by a simple DML statement (DELETE FROM
proposed_raises WHERE department_id = 50;) but we want to do it using a FOR UPDATE
cursor. Test your code, and view the proposed_raises table again to check that the rows have
been deleted.

DECLARE
CURSOR empcur IS
SELECT date_approved FROM proposed_raises WHERE department_id = 50 FOR UPDATE
NOWAIT;
v_emp_rec empcur%ROWTYPE;
BEGIN
OPEN empcur;
LOOP
FETCH empcur INTO v_emp_rec;
EXIT WHEN empcur%NOTFOUND;
DELETE FROM proposed_raises
WHERE CURRENT OF empcur;
END LOOP;
CLOSE empcur;
COMMIT;
END;

DATE_PROP DATE_APPR EMPLOYE DEPARTME ORIGINAL_SA PROPOSED_NEW_S

OSED OVED E_ID NT_ID LARY ALARY
19/Mar/2014 19/Mar/2014 200 10 4840 5082
19/Mar/2014 19/Mar/2014 107 60 4620 4851
 d. We are going to set up two sessions into the same schema. From one of the sessions we will
manually update an employee row NOT COMMITING. From the other session we will try to
update everyone’s salary, again NOT COMMITING. You should see the difference between
NOWAIT and WAIT when using FOR UPDATE.

IMPORTANT NOTE: in each of these sessions, do NOT leave the SQL Commands
screen to visit another Application Express page (for example Object Browser or Home).
If you leave SQL Commands, your updates will automatically be rolled back, releasing
all locks being held.

i. In preparation, create a copy of the employees table by executing the following

SQL statement. You should use the upd_emps table for the rest of this exercise.

CREATE TABLE upd_emps AS SELECT * FROM employees;

ii. Open a second Application Express session in a new browser window and
connect to your schema. Ensure that Autocommit is disabled in BOTH your
sessions (uncheck the check box in the top left corner of the SQL Commands
window).

iii. In your first session, update employee_id 200 (Jennifer Whalen)’s first name to
Jenny.
DO NOT COMMIT. You now have a lock on row 200 that will last indefinitely.

iv. In your second session, write a PL/SQL block to give every employee in
upd_emps a $1 salary raise. Your cursor should be declared FOR UPDATE
NOWAIT. Execute your code. What happens?

v. Still in your second session, modify your block to remove the NOWAIT
attribute from the cursor declaration. Re-execute the block. What happens this
time?

6. After waiting a minute or so, switch to your first session and COMMIT the update to
Jennifer Smith’s row. Then switch back to your second session. What happened?

7. Clean up by COMMITTing the updates in your second session.

Extension Exercise
1. For this question you will also need two separate Application Express sessions in two browser
windows, with Autocommit turned off in both sessions. You will also need a copy of the
departments table. Create this copy by executing the following SQL statement:

CREATE TABLE upd_depts AS SELECT * FROM departments;

A. Modify your $1 salary raise block from the previous question so that the cursor
SELECTS from a join of upd_emps and upd_depts.

B. Run the block in your second session. It should execute successfully, updating all the
upd_emps rows but not committing.
C. Switch to your first session and try to update an upd_depts row using the following
statement. What happens and why?

UPDATE upd_depts SET department_name = 'Accounting'

WHERE department_id = 50;

D. Release all the locks by committing in your second session and then in your first session.

E. How would you prevent your cursor from locking upd_depts rows unnecessarily? Modify your
block to avoid the unnecessary locking, and rerun the test you did in steps b and c. What
happens this time ?

F. Clean up by COMMITTing your updates in both sessions and re-enabling Autocommit.

Section 5 Lesson 6: Using Multiple Cursors

Vocabulary
No new vocabulary for this lesson

Try It / Solve It
1. Write and run a PL/SQL block which produces a listing of departments and their
employees. Use the departments and employees tables. In a cursor FOR loop, retrieve
and display the department_id and department_name for each department, and display a
second line containing ‘----------‘ as a separator. In a nested cursor FOR loop, retrieve and
display the first_name, last_name and salary of each employee in that department,
followed by a blank line at the end of each department. Order the departments by
department_id, and the employees in each department by last_name.

You will need to declare two cursors, one to fetch and display the departments, the
second to fetch and display the employees in that department, passing the
department_id as a parameter.

Your output should look something like this (only the first few departments are shown):

10 Administration
-----------------------------
Jennifer Whalen 4400

20 Marketing
-----------------------------
Pat Fay 6000
Michael Hartstein 13000

50 Shipping
-----------------------------
Curtis Davies 3400
Randall Matos 2600
Kevin Mourgos 5800
Trenna Rajs 3500 -
Peter Vargas 2500

DECLARE
CURSOR c_dept IS
SELECT department_id, department_name
FROM departments;
CURSOR c_emp(p_deptid NUMBER) IS
SELECT first_name, last_name, salary
FROM employees
WHERE department_id = p_deptid;
v_deptrec c_dept%ROWTYPE;
v_emprec c_emp%ROWTYPE;
BEGIN
 OPEN c_dept;
LOOP
FETCH c_dept INTO v_deptrec;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_deptrec.department_id||' '||v_deptrec.department_name);
OPEN c_emp (v_deptrec.department_id);
DBMS_OUTPUT.PUT_LINE('-----------------------------');
LOOP
FETCH c_emp INTO v_emprec;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emprec.last_name || ' ' ||v_emprec.first_name|| ' ' ||
v_emprec.salary);
END LOOP;
CLOSE c_emp;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
CLOSE c_dept;
END;

10 Administration 80 Sales
----------------------------- -----------------------------
Whalen Jennifer 4840 Zlotkey Eleni 11550
Abel Ellen 12100
20 Marketing Taylor Jonathon 9460
-----------------------------
Hartstein Michael 14300 90 Executive
Fay Pat 6600 -----------------------------
King Steven 26400
50 Shipping Kochhar Neena 18700
----------------------------- De Haan Lex 18700
Mourgos Kevin 6380
Rajs Trenna 3850 110 Accounting
Davies Curtis 3410 -----------------------------
Matos Randall 2860 Higgins Shelley 13200
Vargas Peter 2750 Gietz William 9130

60 IT 190 Contracting
----------------------------- -----------------------------
Hunold Alexander 9900
Ernst Bruce 6600
Lorentz Diana 4620 Statement processed.
2. Write and run a PL/SQL block which produces a report listing world regions and countries in
those regions. You will need two cursors: an outer loop cursor which fetches and displays
rows from wf_world_regions, and an inner loop cursor which fetches and displays rows from
wf_countries for countries in that region, passing the region_id as a parameter.
Restrict your regions to those in America (region_name like ‘%America%’). Order your
output by region_name, and by country_name within each region.

Your output should look something like this (only the first two regions are shown):

13 Central America
-----------------------------
Belize 22966 287730
Republic of Costa Rica 51100 4075261
Republic of El Salvador 21040 6822378
Republic of Guatemala 108890 12293545
Republic of Honduras 112090 7326496
Republic of Nicaragua 129494 5570129
Republic of Panama 78200 3191319 United
Mexican States 1972550 107449525

21 Nothern America
-----------------------------
Bermuda 53 65773 Canada
9984670 33098932
Greenland 2166086 56361
Territorial Collectivity of Saint Pierre and Miquelon 242
7026 United States of America 9631420 298444215
DECLARE
CURSOR c_dept IS
SELECT region_id, region_name
FROM wf_world_regions
WHERE region_name LIKE '%AMERICA%';
CURSOR c_emp(p_deptid NUMBER) IS
SELECT country_name
FROM wf_countries
WHERE region_id = p_deptid;
v_deptrec c_dept%ROWTYPE;
v_emprec c_emp%ROWTYPE;
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept INTO v_deptrec;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_deptrec.region_id||'
'||v_deptrec.region_name);
OPEN c_emp (v_deptrec.region_id);
DBMS_OUTPUT.PUT_LINE('-----------------------------');
LOOP
FETCH c_emp INTO v_emprec;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emprec.country_name);
END LOOP;
CLOSE c_emp;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
CLOSE c_dept;
END;
Extension Exercise
3. Modify your block from question 2 to display the names of official spoken languages in
each country. You will need three cursors and three loops. The first two cursors should
fetch and display regions and countries, as in question 2. The innermost loop should
accept a country_id as a parameter, and fetch and display the name of each official
language, using a join of wf_spoken_languages and wf_languages. Within each
country, the languages should be ordered by language_name. Test your block,
restricting regions to those in America.

Your output should look something like this (only the first two regions are shown):

13 Central America
-----------------------------
Belize 22966 287730
--- English
Republic of Costa Rica 51100 4075261
--- Spanish
Republic of El Salvador 21040 6822378
Republic of Guatemala 108890 12293545
Republic of Honduras 112090 7326496
Republic of Nicaragua 129494 5570129
--- Spanish
Republic of Panama 78200 3191319
--- Spanish
United Mexican States 1972550 107449525

21 Nothern America
-----------------------------
Bermuda 53 65773
--- English
Canada 9984670 33098932
--- English
--- French
Greenland 2166086 56361
Territorial Collectivity of Saint Pierre and Miquelon 242 7026
--- French
United States of America 9631420 298444215
--- English