ORACLE SQL Write a PL/SQL anonymous block that will process records stored in th

Question

ORACLE SQL

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.

1. 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).

2. 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)

3. 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).

Notes:

Use pseudocolumn "ROWNUM" (refer to "Oracle 11G SQL Reference" documentation, http://docs.oracle.com/cd/E11882_01/server.112/e41084/pseudocolumns.htm#SQLRF0025 (Links to an external site.)Links to an external site. ) to limit number of salaries to select to 10

FOR loop syntax described at the following link, http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/controlstatements.htm#LNPLS411 (Links to an external site.)Links to an external site.

Use describe command to determine the data type for the "ename" and "sal" fields of the "emp" table

Explanation / Answer

The PL SQL code to retreive the ename, sal of emplolyee from emp database and displaying the record in reverse prder is given below. The array variable for storing names and salary are declared based on the datatype of the attributes declared in emp table.

PL/SQL Code-

DECLARE

CURSOR e_emp is

SELECT ename, sal FROM emp;

type namesarray IS VARRAY(10) OF emp.ename%type;

type salarray IS VARRAY(10) OF emp.sal%type;

names namesarray := namesarray();

salary salarray := salarray();

counter integer := 10;

BEGIN

FOR n IN REVERSE e_emp LOOP

counter := counter - 1;

names.extend;

salary.extend;

names(counter) := n.ename;

salary(counter) := n.sal;

dbms_output.put_line('Emp Name: '||names(counter)||', Salary:'|| salary(counter));

END LOOP;

END;

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

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