PL/SQL Cursor Exercises: Shows how are records are declared and initialized

PL/SQL Cursor: Exercise-48 with Solution

Write a block in PL/SQL to shows how are records are declared and initialized.

Sample Solution:

PL/SQL Code:

    TYPE address_detls IS RECORD (
      street_number NUMBER(4),
      street_name VARCHAR2(25),
      country_name VARCHAR2(15) );
    TYPE person_delts IS RECORD (
      emp_id employees.employee_id%TYPE,
      emp_first_name employees.first_name%TYPE,
      emp_last_name employees.last_name%TYPE,
      emp_address ADDRESS_DETLS );
    person_info PERSON_DELTS;
    person_info.emp_id := 501;
    person_info.emp_first_name := 'Allan';
    person_info.emp_last_name := 'Doran';
    person_info.emp_address.street_number := 601;
    person_info.emp_address.street_name := 'Riverside Drive Redding';
    person_info.emp_address.country_name := 'USA';
dbms_output.Put_line('Personal Details::');
dbms_output.Put_line('Name: '
                     || person_info.emp_last_name
                     || ', '
                     || person_info.emp_first_name);
dbms_output.Put_line('Address: '
                     || To_char(person_info.emp_address.street_number)
                     || ' '
                     || person_info.emp_address.street_name
                     ||', '
                     || person_info.emp_address.country_name);

Sample Output:

SQL> /
Personal Details::
Name: Doran, Allan
Address: 601 Riverside Drive Redding, USA

PL/SQL procedure successfully completed.


Flowchart: Shows how are records are declared and initialized

Improve this sample solution and post your code through Disqus

Previous: Write a block in PL/SQL to print a report which shows that, the employee id, name, hire date, and the incentive amount they achieved according to their working experiences, who joined in the month of current date.
Next: Write a block in PL/SQL to show the uses of subquery in FROM clause of parent query in an explicit cursor.

What is the difficulty level of this exercise?

Inviting useful, relevant, well-written and unique guest posts