PL/SQL Cursor Exercises: Show the uses of implicit cursor without using any attribute

PL/SQL Cursor: Exercise-4 with Solution

Write a program in PL/SQL to show the uses of implicit cursor without using any attribute.

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
    emp_first_name VARCHAR2(35);
    emp_last_name  VARCHAR2(35);
	zemp_id NUMBER:=&employee_id;
BEGIN
    SELECT first_name,
           last_name
    INTO   emp_first_name, emp_last_name
    FROM   employees
    WHERE  employee_id = zemp_id;

    dbms_output.Put_line ('Employee name: '
                          || emp_first_name
                          ||' '
                          ||emp_last_name);
EXCEPTION
    WHEN no_data_found THEN
      dbms_output.Put_line ('There is no employee with the ID '||to_char(zemp_id));
END; 
/ 

Sample Output:

SQL> /
Enter value for employee_id: 485
old   4:        zemp_id NUMBER:=&employee_id;
new   4:        zemp_id NUMBER:=485;
There is no employee with the ID 485

PL/SQL procedure successfully completed.

SQL> /
Enter value for employee_id: 147
old   4:        zemp_id NUMBER:=&employee_id;
new   4:        zemp_id NUMBER:=147;
Employee name: Alberto Errazuriz

PL/SQL procedure successfully completed.

PL/SQL opens an implicit cursor  with the SELECT INTO statement and after the SELECT INTO
statement completes, closes the implicit cursor.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to find the number of rows effected by the use of SQL%ROWCOUNT attributes of an implicit cursor.
Next:  Write a program in PL/SQL to show the uses of SQL%FOUND to determine if a DELETE statement affected any rows.