Write a PL/SQL anonymous block that will process records stored in the \"emp\" t

Question

Write a PL/SQL anonymous block that will process records stored in the "emp" table (table that is created as part of the starter database which was created during the Oracle 11G database installation). The program must perform the following tasks.

Declare the required types and variables to store both the employee name and salary information (i.e., a counter variable may be needed also as an index).

Use a loop to retrieve the first 10 "ename" and "sal" values for records in the "emp" table , store in two variable array of 10 elements (i.e., one array will store the name; the other array will store the salary)

Use another loop to display the "ename" and "sal" information in the reverse order (i.e., use the "REVERSE" option of the for-loop syntax).

Explanation / Answer

Assumption: The counter(id) column of the table emp is named as 'index'.

Here, first the array-types are declared and using them the arrays are declared. Then there is an iterator i using which the records of table emp are extracted and the value of i after executing for loop will represent the number of records, which can be used to print them.

To print records in reverse order just use a keyword reverse before specifying the range for for loop.

DECLARE
   TYPE vc_array IS VARRAY(10) OF VARCHAR(50);
   TYPE int_array IS VARRAY(10) OF integer;
   enames vc_array(); -- emp. names array
   sals int_array(); -- salaries array
   i integer; -- incrementior
BEGIN
   i:=0;
   FOR rec IN (
       SELECT * from emp order by index limit 10
       ) LOOP -- getting values into array
       enames[i]=rec.ename;
       sals[i]=rec.sal;
       i:=i+1;
   END LOOP;
   FOR j IN REVERSE 1..i LOOP -- Printing out in reverse order
       dbms_output.put_line('Employ name: ' || enames[j] || ' Employ salary: ' || sals[j])
   END LOOP;
END;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.