w3resource

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:

DECLARE
    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;
BEGIN
    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('--------------------------------------------------------------');
dbms_output.Put_line('Personal Details::');
dbms_output.Put_line('--------------------------------------------------------------');
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);
END;
/ 

Sample Output:

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

PL/SQL procedure successfully completed.

Flowchart:

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?



Follow us on Facebook and Twitter for latest update.