PL/SQL Exercise 2

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

SET SERVEROUTPUT ON
DECLARE
Id COUNTRIES.COUNTRY_ID%type;
CName COUNTRIES.COUNTRY_NAME%type;
RId COUNTRIES.REGION_ID%type;
BEGIN
SELECT COUNTRY_ID,COUNTRY_NAME,REGION_ID INTO Id,CName,RId FROM COUNTRIES WHERE COUNTRY_NAME='Canada';
DBMS_OUTPUT.PUT_LINE('Value of Country Table :'||Id ||CName||RId);
END;


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

SET SERVEROUTPUT ON
DECLARE
TYPE demo_Countries IS RECORD (
CountyID COUNTRIES.COUNTRY_ID%type,
CountyName COUNTRIES.COUNTRY_NAME%type,
RId COUNTRIES.REGION_ID%type);

demo_Countries_rec demo_Countries;

BEGIN
SELECT COUNTRY_ID INTO demo_Countries_rec.CountyID FROM COUNTRIES WHERE COUNTRY_NAME='Canada';
DBMS_OUTPUT.PUT_LINE('Id of Country: '||demo_Countries_rec.CountyID);
END;


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.
d. Execute and test the PL/SQL block for the countries with the IDs CA, DE, UK, US.

SET SERVEROUTPUT ON
DEFINE myvar Varchar(20) prompt 'Enter your name'
DECLARE
DemoCountryID countries.country_id%type;
DemoCountryName countries.country_name%type;
DemoRegionId countries.region_id%type;
BEGIN
select country_id,country_name,region_id INTO DemoCountryID,DemoCountryName,DemoRegionId FROM countries where country_name='&myvar';
DBMS_OUTPUT.PUT_LINE('Country Detail are '||DemoCountryID||' '||DemoCountryName||' '||DemoRegionId);
END;


============================================================

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.
c. Using another loop, retrieve the department names from the INDEX BY table and print them to the screen, using DBMS_OUTPUT.PUT_LINE. The output from the program is shown on the next page.

SET SERVEROUTPUT ON
DECLARE
TYPE DeptId IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE MY_DEPT_TABLE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
EmpId DeptId;
DeptName MY_DEPT_TABLE;
TempName MY_DEPT_TABLE;
BEGIN
SELECT department_id,department_name BULK COLLECT INTO EmpId,DeptName FROM departments;
FOR i in EmpId.FIRST..EmpId.LAST
LOOP
select department_name INTO TempName(i)FROM departments where department_id=EmpId(i);
END LOOP;

FOR j in EmpId.FIRST..EmpId.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Department Name is :'||TempName(j));
END LOOP;
END;
/
================================================================

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.
c. Using another loop, retrieve the department information from the INDEX BY table and print it to the screen, using DBMS_OUTPUT.PUT_LINE. A sample output is shown.



SET SERVEROUTPUT ON
DECLARE
TYPE DeptID IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE DeptName IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
TYPE DeptLocation IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE MY_DEPT_TABLE IS TABLE OF departments%ROWTYPE INDEX BY BINARY_INTEGER;
DeptTable MY_DEPT_TABLE;
EmpId DeptID;
EmpName DeptName;
EmpLocation DeptLocation;
BEGIN
SELECT department_id,department_name,location_id BULK COLLECT INTO EmpId,EmpName,EmpLocation FROM departments;
FOR j IN EmpId.FIRST..EmpId.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('DeptId '||EmpId(j)||' DeptName '||EmpName(j)||' DeptLocation '||EmpLocation(j));
END LOOP;
END;