时间戳

首页 > 解决方案 > 输出参数 totalCount 和行 SYS_REFCURSOR Oracle

c# - 输出参数 totalCount 和行 SYS_REFCURSOR Oracle

问题描述

在oracle中，我想要totalCount和rows，我做了以下事情：

create or replace PACKAGE BODY PA_MYPACKAGE
IS
PROCEDURE sp_myProcedure(p_nam                IN            Varchar2
                         , p_totalCount       OUT           NUMBER 
                         , p_recordset        OUT           SYS_REFCURSOR)
AS
BEGIN
   SELECT COUNT(*) INTO p_totalCount FROM myTABLE WHERE name = p_name;
   OPEN p_recordset FOR 
   SELECT 
        myTABLE.id
        , myTABLE.name
        , myTable.Description FROM myTABLE 
   WHERE name = p_name
   OFFSET 0 ROW
   FETCH NEXT 10 ROW ONLY;
END;

如果有多个条件，这是不好的。我试过了

create or replace PACKAGE BODY PA_MYPACKAGE
IS
PROCEDURE sp_myProcedure(p_nam                IN            Varchar2
                         , p_totalCount       OUT           NUMBER 
                         , p_recordset        OUT           SYS_REFCURSOR)
AS
BEGIN
   OPEN p_recordset FOR 
   SELECT 
        myTABLE.id
        , myTABLE.name
        , myTable.Description FROM myTABLE 
        , count(*) over() as totalCount
   WHERE name = p_name;
END;

但我不能设置 P_totalCount = totalCount;

我怎样才能得到它们？

标签： c#oracleasp.net-web-apioracle12cdapper

解决方案

让我看看我是否理解你的问题。检查我的演示

首先：我们创建一个包含一些记录的表格演示

SQL> create table t ( id number generated always as identity , name varchar2(20) , description varchar2(100) );

Table created.

SQL> declare
  2  begin
  3  for r in 1 .. 100
  4  loop
  5     insert into t ( name , description ) values ( 'John'  , dbms_random.string('A',20)  ) ;
  6     insert into t ( name , description ) values ( 'Peter' , dbms_random.string('A',20) ) ;
  7     insert into t ( name , description ) values ( 'Frank' , dbms_random.string('A',20) ) ;
  8  end loop ;
  9  commit;
 10* end;
SQL> /

PL/SQL procedure successfully completed.

第二：我们创建一个与您类似的程序

SQL> create or replace procedure pr_t
  2  ( p_name       in varchar2
  3  , p_totalCount out number
  4  , p_recordset  out sys_refcursor
  5  )
  6  as
  7  begin
  8     select count(*) into p_totalcount from t where name = p_name;
  9     open p_recordset for
 10     select
 11          t.id
 12          , t.name
 13          , t.description from t
 14     where name = p_name
 15     offset 0 row
 16     fetch next 10 row only;
 17* end;
SQL> /

Procedure created.

第三：现在我们调用它来同时获取每一行中的行数和计数器

SQL> declare
  2    l_cursor  sys_refcursor;
  3    l_counter pls_integer;
  4    l_id      t.id%type;
  5    l_name    t.name%type;
  6    l_desc    t.description%type;
  7  begin
  8    pr_t ( p_name => 'Frank' , p_totalCount => l_counter, p_recordset => l_cursor );
  9    loop
 10      fetch l_cursor
 11      into  l_id, l_name, l_desc;
 12      exit when l_cursor%notfound;
 13      dbms_output.put_line(l_id || ' | ' || l_name || ' | ' || l_desc || ' | ' || l_counter);
 14    end loop;
 15    close l_cursor;
 16* end;
     /

100003 | Frank | ZDsZwyzfgCXolJFDblrW | 100
100006 | Frank | ozCQpfGPMeqquejqDRNx | 100
100009 | Frank | XYiOFtujYLOoPbemCNSx | 100
100012 | Frank | GIurWAfHjuRLYRzzAFuI | 100
100015 | Frank | TKFzFoqkwInGxtvrLnev | 100
100018 | Frank | SDPlYbTdjTgPkjnwUCBb | 100
100021 | Frank | RSbdHGsJGIlIXrJoxHuY | 100
100024 | Frank | VUywFEDzbAfvTtUfjYJQ | 100
100027 | Frank | SnwfqTykxPISKVbGQCED | 100
100030 | Frank | CwTnIPrOfXiqsAuvXays | 100

PL/SQL procedure successfully completed.

推荐阅读