PL/SQL Explicit Cursor Program
Introduction
This PL/SQL code performs several tasks related to a table named "EMPTY." It creates the table, inserts data into it, and then retrieves and displays the data using a cursor. This article will explain the code in detail, providing a step-by-step breakdown of its functionality.
Key Concepts
Before diving into the code explanation, let's briefly cover some key concepts related to PL/SQL:
- PL/SQL: PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural extension to SQL. It allows developers to write procedural code blocks that can be executed within the Oracle Database.
- Tables: In PL/SQL, tables are database objects used to store data in a structured manner. They consist of rows and columns, where each row represents a record and each column represents a specific attribute or field.
- Cursors: Cursors in PL/SQL are used to retrieve and manipulate data from the result set of a query. They provide a way to iterate over the rows returned by a SELECT statement.
Code Structure
The code can be divided into several sections:
- Table Creation: The first line of code creates a table named "EMPTY" with three columns: "NO" (number), "NAME" (varchar), and "SAL" (number).
- Data Insertion: The second line of code inserts data into the "EMPTY" table. It prompts the user to enter values for the "NO," "NAME," and "SAL" columns.
- Server Output: The "SET SERVEROUTPUT ON" statement enables the display of output generated by the DBMS_OUTPUT.PUT_LINE function.
- Cursor Declaration: The DECLARE section declares variables and a cursor named "C_EMPTY" that will be used to retrieve data from the "EMPTY" table.
- Cursor Opening: The "OPEN C_EMPTY" statement opens the cursor, making it ready to fetch data.
- Looping through Cursor: The LOOP statement initiates a loop that will iterate over the rows returned by the cursor. The FETCH statement retrieves the values of the "CNO," "CNAME," and "CSAL" variables from the cursor.
- Output Display: The DBMS_OUTPUT.PUT_LINE function is used to display the values of "CNO," "CNAME," and "CSAL" on the console.
- Loop Termination: The EXIT WHEN statement checks if there are no more rows to fetch from the cursor. If there are no more rows, the loop is terminated.
- Cursor Closing: The CLOSE statement closes the cursor, releasing any resources associated with it.
Code Examples
Here are the code examples for each section of the PL/SQL code:
Table Creation
CREATE TABLE EMPTY (NO NUMBER(2),NAME VARCHAR(50),SAL NUMBER(20));
Data Insertion
INSERT INTO EMPTY VALUES (&NO,'&NAME',&SAL);
Server Output
SET SERVEROUTPUT ON;
Cursor Declaration, Looping, and Output Display
DECLARE
CNO EMPTY.NO%TYPE;
CNAME EMPTY.NAME%TYPE;
CSAL EMPTY.SAL%TYPE;
CURSOR C_EMPTY IS SELECT * FROM EMP;
BEGIN
OPEN C_EMPTY;
LOOP
FETCH C_EMPTY INTO CNO,CNAME,CSAL;
EXIT WHEN C_EMPTY%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(CNO||' '||CNAME||' '||CSAL);
END LOOP;
CLOSE C_EMPTY;
END;
/Conclusion
In this article, we have explored a PL/SQL code snippet that creates a table, inserts data into it, and retrieves and displays the data using a cursor. Understanding this code will help you grasp the basics of PL/SQL programming and how to work with tables and cursors in Oracle Database.
