Need to get the row count based on the table data value-CodePudding

create table request_details(req_id number(10),
e_num number(10),
e_name varchar2(30),
e_env varchar2(30),
e_member varchar2(30),
constraint pk_request_details primary key(req_id));

insert into request_details values(1,1,'A','SIT','SAP1');
insert into request_details values(2,1,'A','SIT','SAP1');
insert into request_details values(3,1,'A','SIT','SAP1');
insert into request_details values(4,1,'A','SIT','SAP1');

create or replace procedure sp_request_details(
iv_env IN varchar2,
iv_team IN varchar2,
iv_enum IN number,
ov_err_msg OUT varchar2
)
AS
lv_count number(10);
BEGIN
/*Here I need to count the row number of the table for particular e_num*/
for i in(select * from request_details where e_env = iv_env and e_member = iv_team and e_num = iv_enum)LOOP
select s*, rownum  from request_details;--It is giving error


/*Basically I want to find the row count of the table request_details
Suppose if there are 4 records for the same e_num then there would be one column which will give me numbers from 1 to 4
If one record gets inserted for the same e_num then the count will be 5*/
end loop;




END sp_request_details;

Basically, I want to find the row count of the table request_details Suppose if there are 4 records for the same e_num then there would be one column which will give me numbers from 1 to 4 If one record gets inserted for the same e_num then the count will be 5

I need to count the records row wise and need to give that count into the column (ROWCOUNT) but got stuck in the stored procedure. Can someone help with this?

Expected output :

CodePudding user response：

You can use ROWNUM like this:

create or replace procedure sp_request_details(
  iv_env     IN  request_details.e_env%TYPE,
  iv_team    IN  request_details.e_member%TYPE,
  iv_enum    IN  request_details.e_numTYPE,
  ov_err_msg OUT varchar2
)
AS
lv_count number(10);
BEGIN
  FOR i IN (SELECT r.*,                 -- Third, use the alias with the `*`
                   ROWNUM AS rn         -- Fourth, generate the ROW NUMBER.
            FROM   (
              select *
              from   request_details
              where  e_env = iv_env
              and    e_member = iv_team
              and    e_num = iv_enum
              ORDER BY req_id            -- First, Order the SELECT
            ) r                          -- Second, give the subquery an alias
           )
  LOOP
    -- Do something with each row.
    NULL;
  END LOOP;
END sp_request_details;
/

CodePudding user response：

From my point of view, no - you don't want to do it that way. Use row_number analytic function, e.g.

SQL>   SELECT req_id,
  2           e_num,
  3           e_name,
  4           e_env,
  5           e_member,
  6           --
  7           ROW_NUMBER () OVER (PARTITION BY e_num ORDER BY req_id) rn
  8      FROM request_details
  9  ORDER BY e_num, req_id;

    REQ_ID      E_NUM E_NAME     E_ENV      E_MEMBER           RN
---------- ---------- ---------- ---------- ---------- ----------
         1          1 A          SIT        SAP1                1
         2          1 A          SIT        SAP1                2
         3          1 A          SIT        SAP1                3
         4          1 A          SIT        SAP1                4

SQL>