Counting the Number of Records in a Table in PL/SQL
Introduction
In this code example, we have a PL/SQL program that counts the number of records in a table called EMP2. The code demonstrates the use of a function to retrieve the count and then displays the result using the DBMS_OUTPUT.PUT_LINE statement.
Key Concepts
Before diving into the code explanation, let's understand a few key concepts:
- PL/SQL: PL/SQL is a procedural language extension for SQL in Oracle databases. It allows you to write code blocks that can be executed as a single unit.
- Tables: Tables are database objects that store data in rows and columns. They are used to organize and store structured data.
- Functions: Functions are named PL/SQL blocks that return a value. They can be used to perform calculations, retrieve data, or manipulate data.
- DBMS_OUTPUT.PUT_LINE: DBMS_OUTPUT is a package in Oracle that provides a way to display output from PL/SQL programs. The PUT_LINE procedure is used to print a line of text to the console.
Code Structure
The code is divided into two sections: table creation and the PL/SQL program.
1) Table Creation:
- The first line of code creates a table called EMP2 with two columns: RNO (integer) and NAME (varchar).
- The second line inserts data into the EMP2 table. The values for RNO and NAME are prompted from the user using substitution variables (&RNO and &NAME).
2) PL/SQL Program:
- The code defines a function called FUNC_01 that returns a number.
- Inside the function, a variable named TOTAL is declared and initialized to 0.
- The SELECT statement counts the number of records in the EMP2 table and stores the result in the TOTAL variable.
- The function returns the value of TOTAL.
- The code then declares a variable A of type NUMBER.
- The BEGIN block calls the FUNC_01 function and assigns the returned value to the variable A.
- Finally, the DBMS_OUTPUT.PUT_LINE statement displays the number of records in the EMP2 table.
Code Examples
Let's take a closer look at the code examples:
Table
CREATE TABLE EMP2 (RNO INT,NAME VARCHAR(33)); INSERT INTO EMP2 VALUES (&RNO,'&NAME');
The above code creates a table called EMP2 with two columns: RNO (integer) and NAME (varchar). It then inserts data into the EMP2 table using substitution variables (&RNO and &NAME) to prompt the user for input.
Function
CREATE OR REPLACE FUNCTION FUNC_01 RETURN NUMBER IS TOTAL NUMBER(2):=0; BEGIN SELECT COUNT (*) INTO TOTAL FROM EMP2; RETURN TOTAL; END;
The code above defines a function called FUNC_01 that returns a number. Inside the function, a variable named TOTAL is declared and initialized to 0. The SELECT statement counts the number of records in the EMP2 table and stores the result in the TOTAL variable. The function then returns the value of TOTAL.
Calling Function
DECLARE
A NUMBER;
BEGIN
A:=FUNC_01();
DBMS_OUTPUT.PUT_LINE('THE NUMBER OF RECORDS IN EMP : '||A);
END;The above code declares a variable A of type NUMBER. The BEGIN block calls the FUNC_01 function and assigns the returned value to the variable A. Finally, the DBMS_OUTPUT.PUT_LINE statement displays the number of records in the EMP2 table.
Conclusion
In this code example, we learned how to count the number of records in a table using PL/SQL. We used a function to retrieve the count and displayed the result using the DBMS_OUTPUT.PUT_LINE statement. Understanding these concepts will help you write more efficient and effective PL/SQL programs.
