PL/SQL:

SQL> desc COUNTRIES; Name Null? Type ----------------------------------------- -------- ---------------------------- COUNTRY_ID NOT NULL CHAR(2) COUNTRY_NAME VARCHAR2(40) REGION_ID NUMBER

SQL> desc DEPARTMENTS; Name Null? Type ----------------------------------------- -------- ---------------------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

1. Write a PL/SQL block to print information about a given country.

a. Declare a PL/SQL record based on the structure of the COUNTRIES table.

b. Use the DEFINE command to provide the country ID. Pass the value to the PL/SQL block through a iSQL*Plus substitution variable.

c. Use DBMS_OUTPUT.PUT_LINE to print selected information about the country. A sample output is shown below.

Country Id: CA Country Name: Canada Region: 2

PL/SQL procedure successfully completed.

d. Execute and test the PL/SQL block for the countries with the IDs CA, DE, UK, US.

2. Create a PL/SQL block to retrieve the name of each department from the DEPARTMENTS table and print each department name on the screen, incorporating an INDEX BY table. Save the code in a file called p5q2.sql by clicking the Save Script button. Save the script with a .sql extension.

a. Declare an INDEX BY table, MY_DEPT_TABLE, to temporarily store the name of the departments.

b. Using a loop, retrieve the name of all departments currently in the DEPARTMENTS table and store them in the INDEX BY table. Use the following table to assign the value for DEPARTMENT_ID based on the value of the counter used in the loop.

COUNTER DEPARTMENT_ID

1 10

2 20

3 50

4 60

5 80

6 90

7 110

c. Using another loop, retrieve the department names from the INDEX BY table and print them to the screen, using DBMS_OUTPUT.PUT_LINE.

3. Modify the block you created in practice 2 to retrieve all information about each department from the DEPARTMENTS table and print the information to the screen, incorporating an INDEX BY table of records.

a. Declare an INDEX BY table, MY_DEPT_TABLE, to temporarily store the number, name, and location of all the departments.

b. Using a loop, retrieve all department information currently in the DEPARTMENTS table and store it in the INDEX BY table. Use the following table to assign the value for DEPARTMENT_ID based on the value of the counter used in the loop. Exit the loop when the counter reaches the value 7.

COUNTER DEPARTMENT_ID

1 10

2 20

3 50

4 60

5 80

6 90

7 110

c. Using another loop, retrieve the department information from the INDEX BY table and print it to the screen, using DBMS_OUTPUT.PUT_LINE.