Simple Procedure for Printing the count of rows in a table without using COUNT operation ORACLE

Question

Can anyone help me to create Simple Procedure for Printing the count of rows in a table without using COUNT operation in ORACLE ?

Answer 1

You must be looking for the LOOP in the procedure, considering that aggregate functions are not allowed..

Let's say I have an account table with 3 records in it.

SQL> SELECT * FROM ACCOUNT;

    ACC_NR       SUM_    CUST_ID
---------- ---------- ----------
       500       3400        100
       600       5000        101
       700       5070        102

SQL>

Now, creating the procedure:

SQL> CREATE OR REPLACE PROCEDURE COUNT_ACCOUNT (
  2      P_OUT_COUNT OUT   NUMBER
  3  ) AS
  4  BEGIN
  5      P_OUT_COUNT := 0;
  6      FOR I IN (
  7          SELECT
  8              1 AS RW
  9          FROM
 10              ACCOUNT
 11      ) LOOP
 12          P_OUT_COUNT := P_OUT_COUNT + 1;
 13      END LOOP;
 14  END COUNT_ACCOUNT;
 15  /

Procedure created.

SQL>

Executing the procedure to see the output:

SQL> SET SERVEROUT ON
SQL>
SQL> DECLARE
  2      CNT   NUMBER := 0;
  3  BEGIN
  4      COUNT_ACCOUNT(CNT);
  5      DBMS_OUTPUT.PUT_LINE('NUMBER OF RECORDS IN ACCOUNT TABLE: ' || CNT);
  6  END;
  7  /
NUMBER OF RECORDS IN ACCOUNT TABLE: 3

PL/SQL procedure successfully completed.

SQL>

Cheers!!