w3resource

PL/SQL Cursor Exercises: Display a table based detail information for the employee of ID 149 from the employees table

PL/SQL Cursor: Exercise-8 with Solution

Write a program in PL/SQL to display a table based detail information for the employee of ID 149 from the employees table.

Table based record

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE 
    z_employee employees%ROWTYPE; 
BEGIN 
    SELECT * 
    INTO   z_employee -- INTO clause always notifies only single row can be fetch 
    FROM   employees 
    WHERE  employee_id = 149; 

    dbms_output.Put_line('Employee Details :   ID:' 
                         ||z_employee.employee_id 
                         ||'  Name: ' 
                         ||z_employee.first_name 
                         ||' ' 
                         ||z_employee.last_name 
                         ||'  Salary:  ' 
                         ||z_employee.salary); 
END; 
/

Sample Output:

SQL> /
Employee Details :   ID:149  Name: Eleni Zlotkey  Salary:  10500

PL/SQL procedure successfully completed.

Flowchart:

Flowchart: PL/SQL Cursor Exercises - Display a table based detail information for the employee of ID 149 from the employees table

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to create a table-based record using the %ROWTYPE attribute.
Next: Write a program in PL/SQL to display a cursor based detail information of employees from employees table.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.