Display Department Names Using Cursors

Create a PL/SQL block to display all the department names from the Department table using cursors.

COLUMN	DATATYPE	CONS.
DEPARTMENT_ID	NUMBER(5)	PK
DEPARTMENT_NAME	VARCHAR2(25)	NOT NULL
LOCATION_ID	VARCHAR2(15)

Sample Output:

 
Department Names are:
ADMIN
DEVELOPMENT
            

(Note: Data is case sensitive. Use '/'' at the end to terminate the PL/SQL block)

This PL/SQL block essentially fetches department names from the Department table and displays them one by one.

Enable Output: The first line SET SERVEROUTPUT ON; is not actually a part of PL/SQL; it's a SQLPlus command. It just ensures that the output will be displayed in SQLPlus.

DECLARE: The DECLARE keyword is used to declare variables that are used in the PL/SQL block.

• dname is declared to hold the name of each department.
• CURSOR Dept is declared to fetch department names from the Department table, ordered alphabetically.

BEGIN: BEGIN marks the beginning of the PL/SQL block where the actual logic resides.

DBMS_OUTPUT.PUT_LINE('Department Names are :') - This output statement outputs a message indicating that department names will be displayed.

CURSOR Operations:

• OPEN Dept - This opens the cursor Dept, allowing it to fetch rows.
• LOOP - marks the beginning of a loop that will iterate over each row fetched by the cursor until there are no more rows.
• FETCH Dept INTO dname - retrieves the department name from the cursor and stores it in the dname variable.
• EXIT WHEN Dept%NOTFOUND - This exits the loop when there are no more rows to fetch.
• DBMS_OUTPUT.PUT_LINE(dname) - This line outputs the department name retrieved from the cursor.
• END LOOP - marks the end of the loop.
• CLOSE Dept - This closes the cursor once all rows have been fetched.

END: Marks the end of the PL/SQL block.