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 un defines the active set CLOSE Disables a cursor, releases the context area, and undefines 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, an d for SELECT statements that return only one row OPEN Statement Statement that executes the query associated with the cursor, identifies the active set, and positions the cursor cursor pointer pointer to the first row row FETCH Statement that retrieves the current row and advances adv ances 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. Rezolvare : 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 as needed? Rezolvare : SELECT 3. List two circumstances in which you would use an explicit cursor. Rezolvare : 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 cu rsor 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 A CURSOR wf_currencies_cur IS SELECT currency_code, currency.name FROM wf_currencies;
C
v_code wf_currencies.currency_code%TYPE; v_name wf_currencies.currency.name%TYPE; BEGIN OPEN wf_currencies_cur; B FETCH wf_currencies_cur INTO v_code, v_name; D 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 tha t 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 AFA ALL AMD ANG AOA ARS AUD AWG AZM BAM BBD BDT BGL BIF BMD BND BOB BRL BSD BTN BWP BYB BZD CAD CDF CHF
Emirati dirham Afghani Lek Dram Netherlands Antillean guilder Kwanza Argentine peso Australian dollar Aruban guilder Azerbaijani Manat Marka Barbadian dollar Taka Lev Burundi franc Bermudian dollar Bruneian dollar Boliviano Real Bahamian dollar Ngultrum Pula Belarusian ruble Belizean dollar Canadian dollar Congolese franc Swiss franc
CLP CNY COP CRC CUC CUP CVE CYP CZK DJF DKK DOP DZD EEK EGP ERN ETB EUR FJD FKP GBP GEL GHC GIP GMD GNF GTQ
Chilean peso yuan Colombian peso Costa Rican colon Convertible peso Cuban peso Cape Verdean escudo Cypriot pound Czech koruna Djiboutian franc Danish krone Dominican peso Algerian dinar Estonian kroon Egyptian pound nakfa birr Euro Fijian dollar Falkland pound British pound lari cedi Gibraltar pound dalasi Guinean franc quetzal
GYD Guyanese dollar HKD Hong Kong dollar HNL lempira HRK kuna HTG gourde HUF forint IDR Indonesian rupiah ILS new Israeli shekel INR Indian rupee IRR Iranian rial ISK Icelandic krona JMD Jamaican dollar JOD Jordanian dollar JPY yen KD Kuwaiti dinar KES Kenyan shilling KHR riel KMF Comoran franc KPW North Korean won KRW South Korean won KYD Caymanian dollar KZT tenge LAK kip LBP Lebanese pound LKR Sri Lankan rupee LRD Liberian dollar LSL loti LTL litas LVL Latvian lat LYD Libyan dinar MAD Moroccan dirham MDL Moldovan leu MGA Madagascar ariary MKD Macedonian denar MMK Kyat MNT togrog/tugrik MOP pataca MRO ouguiya MTL Maltese lira MUR Mauritian rupee MVR rufiyaa MWK Malawian kwacha MXN Mexican peso MYR ringgit MZM metical NAD Namibian dollar NGN naira NID New Iraqi dinar NOK Norwegian krone NONE No currency used NPR Nepalese rupee NZD New Zealand dollar PAB balboa PEN nuevo sol PGK kina PKR Pakistani rupee
PLN zloty PYG guarani QAR Qatari rial RMB Renminbi RUR Russian ruble SAR Saudi riyal SAT tala SBD Solomon Islands dollar SCR Seychelles rupee SEK Swedish krona SGD Singapore dollar SHP Saint Helenian pound SIT tolar SLL leone SOS Somali shilling SRD Surinam dollar STD dobra SZL lilangeni THB baht TMM Turkmen manat TND Tunisian dinar TTD Trinidad and Tobago dollar TWD new Taiwan dollar UAH hryvnia UGX Ugandan shilling USD US Dollar UYU Uruguayan peso UZS Uzbekistani soum VEB bolivar VND dong VUV vatu XAF Communaute Financiere Africaine franc XCD East Caribbean dollar XOF Communaute Financiere Africaine franc XPF Comptoirs Francais du Pacifique franc YER Yemeni rial YTL Turkish lira ZAR South African rand ZMK Zambian kwacha ZWD Zimbabwean dollar NIO gold cordoba OMR Omani rial PHP Philippine peso RON leu RWF Rwandan franc SDD Sudanese dinar SKK Slovak koruna SYP Syrian pound TOP paanga TZS Tanzanian shilling TZS Tanzanian shilling 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. Rezolvare: - 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 FETC H 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. Rezolvare : 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? Rezolvare: 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 Rezolvare: -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 statemen t. 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
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.
Section 5 Lesson 5: Using Cursors FOR UPDATE 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 OSED OVED E_ID NT_ID 19/Mar/2014 200 10 19/Mar/2014 141 50 19/Mar/2014 142 50 19/Mar/2014 143 50 19/Mar/2014 144 50 19/Mar/2014 107 60 6 rows returned in 0.01 seconds
ORIGINAL_SA PROPOSED_NEW_S LARY ALARY 4840 5082 3850 4042.5 3410 3580.5 2860 3003 2750 2887.5 4620 4851
[ 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 OSED OVED E_ID NT_ID 19/Mar/2014 19/Mar/2014 200 10 19/Mar/2014 19/Mar/2014 141 50 19/Mar/2014 19/Mar/2014 142 50 19/Mar/2014 19/Mar/2014 143 50 19/Mar/2014 19/Mar/2014 144 50 19/Mar/2014 19/Mar/2014 107 60
ORIGINAL_SA PROPOSED_NEW_S LARY ALARY 4840 5082 3850 4042.5 3410 3580.5 2860 3003 2750 2887.5 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 seco nd session. What happened?
7. Clean up by COMMITTing the updates in your second session.
Section 5 Lesson 6: Using Multiple Cursors 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 ----------------------------Whalen Jennifer 4840 20 Marketing ----------------------------Hartstein Michael 14300 Fay Pat 6600 50 Shipping ----------------------------Mourgos Kevin 6380 Rajs Trenna 3850 Davies Curtis 3410 Matos Randall 2860 Vargas Peter 2750 60 IT ----------------------------Hunold Alexander 9900 Ernst Bruce 6600 Lorentz Diana 4620
80 Sales ----------------------------Zlotkey Eleni 11550 Abel Ellen 12100 Taylor Jonathon 9460 90 Executive ----------------------------King Steven 26400 Kochhar Neena 18700 De Haan Lex 18700 110 Accounting ----------------------------Higgins Shelley 13200 Gietz William 9130 190 Contracting -----------------------------
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
