oracle - 需要根据表数据值获取行数

Question

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;

基本上，我想找到表 request_details 的行数假设如果相同的 e_num 有 4 条记录，那么会有一列将给我从 1 到 4 的数字如果为相同的 e_num 插入一条记录，那么计数将是 5

我需要按行计算记录，并且需要将该计数计入列（ROWCOUNT），但卡在存储过程中。有人可以帮忙吗？

预期输出：

标签： oracleplsql

Answer 1

从我的角度来看，不 - 你不想那样做。使用row_number解析函数，例如

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>