从Oracle数据库中检索数据子集

Question

我有一个Oracle 19c数据库，我正试图使用包过程从中提取数据。它正在工作，但我是Oracle的新手，以前在Microsoft SQL Server方面非常有经验。我下面的C#代码可以成功地调用我的存储过程。但是，存储过程返回的行数超过一百万。我不想让一个数据集填充超过一百万行，因为这显然非常慢。我想返回的是一个子集，比如偏移X行和取N行。基本上我想做这样的事情：

SELECT * FROM STORED_PROCEDURE OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY

但我想用我的打包程序来做。这是我的C#代码：

public async Task<List<DbModels.DocumentWipList>> GetWipDocumentsAsync(string sort = "limited_dodiss ASC")
{
using (var connection = new OracleConnection(_configuration.GetConnectionString("OracleDev")))
{
using (var command = connection.CreateCommand())
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "PKG_GET_COMPONENT_DETAIL.pr_get_wip_comp_list_sorted";
command.Parameters.Add("arg_sort", OracleDbType.Varchar2).Value = sort;
command.Parameters.Add("io_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output; 
using (var da = new OracleDataAdapter())
{
da.SelectCommand = command;
var dt = new DataTable();
await Task.Run(() => da.Fill(dt)); 
return MapDocumentWipList(dt);
}
}
}
}

需要注意的是，我不能修改打包程序。我希望有一种简单的方法可以做到这一点，也许可以通过某种方式将包过程包装为SELECT查询的子查询。

Answer 1

在Oracle中，游标实际上是指向数据库服务器上内存地址的指针，数据库在该地址存储游标执行的查询和游标的当前执行状态(游标从不存储等待读取的结果集，这是在读取每行时动态生成的(。您可以从光标读取一次，但不能更改光标或倒带。

我想返回的是一个子集，比如偏移X行和取N行。

不要使用await Task.Run(() => da.Fill(dt));。相反，读取并忽略光标的X行，然后读取并存储N行。

但是，最好更改过程以允许分页。

过滤怎么样？返回的列之一是OWNER_NAME。如果我只想拉WHERE OWNER_NAME LIKE 'R%'行或类似的行怎么办？

无法修改光标，如果必须读取光标，则需要读取光标的ALL行，并丢弃与条件不匹配的行。因此，再次强调，不要使用await Task.Run(() => da.Fill(dt));，它会将所有行加载到内存中，而是逐个读取行，只在内存中保留您想要的行，而忘记其余的行。

您可以用PL/SQL编写第二个过程来包装光标，也可以在C#等第三方应用程序中进行处理，但您需要读取光标。在PL/SQL或C#中进行处理之间的所有变化都是处理发生在数据库服务器上还是第三方应用程序服务器上。

---

例如，如果您有表格：

CREATE TABLE table_name (a, b, c) AS
SELECT LEVEL, CHR(64 + LEVEL), DATE '1970-01-01' + LEVEL - 1
FROM   DUAL
CONNECT BY LEVEL <= 10;

和一个现有的程序(不能改变(：

CREATE PROCEDURE get_cursor (
o_cursor OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN o_cursor FOR
SELECT * FROM table_name;
END;
/

然后你可以创建类型：

CREATE TYPE table_name_type IS OBJECT(
a NUMBER,
b VARCHAR2(1),
c DATE
);
CREATE TYPE table_name_array IS TABLE OF table_name_type;

它允许您创建一个流水线函数：

CREATE FUNCTION wrap_cursor_fn (
i_cursor IN  SYS_REFCURSOR
) RETURN table_name_array PIPELINED
IS
v_a table_name.a%TYPE;
v_b table_name.b%TYPE;
v_c table_name.c%TYPE;
BEGIN
LOOP
FETCH i_cursor INTO v_a, v_b, v_c;
EXIT WHEN i_cursor%NOTFOUND;
PIPE ROW (table_name_type(v_a, v_b, v_c));
END LOOP;
CLOSE i_cursor;
EXCEPTION
WHEN NO_DATA_NEEDED THEN
CLOSE i_cursor;
WHEN OTHERS THEN
CLOSE i_cursor;
RAISE;
END;
/

然后，它允许您在SQL语句中使用返回的流水线集合，并将一个游标读取到另一个游标中，并对其应用过滤器：

CREATE PROCEDURE wrap_cursor_proc (
i_cursor IN  SYS_REFCURSOR,
o_cursor OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN o_cursor FOR
SELECT *
FROM   TABLE(wrap_cursor_fn(i_cursor))
WHERE  MOD(a, 3) = 0;
END;
/

然后你可以阅读：

DECLARE
v_cur1 SYS_REFCURSOR;
v_cur2 SYS_REFCURSOR;
v_a table_name.a%TYPE;
v_b table_name.b%TYPE;
v_c table_name.c%TYPE;
BEGIN
get_cursor(v_cur1);
wrap_cursor_proc(v_cur1, v_cur2);
LOOP
FETCH v_cur2 INTO v_a, v_b, v_c;
EXIT WHEN v_cur2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( v_a || ', ' || v_b || ', ' || v_c );
END LOOP;
CLOSE v_cur2;
END;
/

和输出：

3, C, 03-JAN-70
6, F, 06-JAN-70
9, I, 09-JAN-70

小提琴

读取数据库中的游标并将其恢复为可在SQL查询中使用的格式是一项艰巨的工作，这样您就可以将其封装在另一个游标中以应用过滤器。只需复制原始程序并添加必要的过滤条件会简单得多