代码之家 › 专栏 › 技术社区 › Baptiste Pernet

如何基于数组构建游标

cursor oracle arrays

Baptiste Pernet · 技术社区 · 14 年前

我需要优化一个PL/SQL函数,它目前是这样的:

CREATE OR REPLACE FUNCTION tkt_get_underlying(n_input number)
RETURN t_table_of_number
IS
    ret t_table_of_number;
    CURSOR c IS SELECT n_number FROM t_table WHERE n_prop_1=n_input OR n_prop_2=n_input OR n_prop_3=n_input;
BEGIN
    ret :=  t_table_of_number();

    OPEN c;
        FETCH c BULK COLLECT INTO ret;
    CLOSE c;

    RETURN ret;
END;

我希望能够给出一个数组作为参数,但是,我不知道如何构建我的游标来获取数组。我想我可以用 IN 声明,但你能帮我解决这个问题吗?

编辑:

根据提供的解决方案 Justin Cave ,它将变成:

CREATE OR REPLACE FUNCTION tkt_get_underlying(n_inputs t_table_of_number)
RETURN t_table_of_number
IS
    ret t_table_of_number;
    CURSOR c IS SELECT n_number FROM t_table WHERE n_prop_1 IN (SELECT column_value FROM TABLE(n_inputs))
                                             OR n_prop_2 IN (SELECT column_value FROM TABLE(n_inputs))
                                             OR n_prop_3 IN (SELECT column_value FROM TABLE(n_inputs));
BEGIN
    ret :=  t_table_of_number();

    OPEN c;
        FETCH c BULK COLLECT INTO ret;
    CLOSE c;

    RETURN ret;
END;

然而 SELECT column_value FROM TABLE(n_inputs) 减慢整个功能。我该如何改进?

3 回复 | 直到 9 年前

Justin Cave 14 年前

如果您想传入的集合 n_input 值并返回相同值 t_table_of_number 集合(即,您不需要知道输出数组的哪个元素与输入数组的哪个元件相关联)

CREATE OR REPLACE FUNCTION tkt_get_underlying(p_inputs t_table_of_number)
RETURN t_table_of_number
IS
    ret t_table_of_number;
    CURSOR c 
        IS SELECT n_number 
             FROM t_table 
            WHERE n_prop IN (SELECT column_value 
                               FROM TABLE( p_inputs ) );
BEGIN
    OPEN c;
        FETCH c BULK COLLECT INTO ret;
    CLOSE c;

    RETURN ret;
END;

这假设可能要插入到 ret 集合同时保存在PGA存储器中仍然是合理的。根据具体情况,您可能希望将其转换为流水线表函数,以限制所需的PGA内存量。

Craig 14 年前

Oracle使用嵌套表的基数是错误的,因为它不知道实际有多少行。试着让你的功能看起来像:

CREATE OR REPLACE FUNCTION tkt_get_underlying(n_inputs t_table_of_number)
RETURN t_table_of_number
IS
    ret t_table_of_number;
    CURSOR c IS SELECT n_number FROM t_table WHERE n_prop_1 IN (SELECT /*+ cardinality(ni 1) */ column_value FROM TABLE(n_inputs) ni)
                                             OR n_prop_2 IN (SELECT /*+ cardinality(ni 1) */ column_value FROM TABLE(n_inputs) ni)
                                             OR n_prop_3 IN (SELECT /*+ cardinality(ni 1) */ column_value FROM TABLE(n_inputs) ni);
BEGIN
    ret :=  t_table_of_number();

    OPEN c;
        FETCH c BULK COLLECT INTO ret;
    CLOSE c;

    RETURN ret;
END;

请注意,如果您知道嵌套表中预期的行数,请使基数提示准确无误。此外,如果您在嵌套表中放置了太多行,Oracle可能会执行次优,因为您让它认为嵌套表中的行比实际的行少。

Baptiste Pernet 14 年前

感谢您的帮助,我终于找到了适合我需求的优化。现在查询如下:

CREATE OR REPLACE FUNCTION tkt_get_underlying(n_inputs t_table_of_number) 
RETURN t_table_of_number 
IS
  ret t_table_of_number;
  CURSOR c IS SELECT t.n_number FROM t_table t, (SELECT column_value /*+cardinality(t_inputs 100) */ c FROM TABLE(n_inputs)) t_inputs
                WHERE t_inputs.c = t.n_prop_1
                OR t_inputs.c = t.n_prop_2
                OR t_inputs.c = t.n_prop_3; 
BEGIN
  ret :=  t_table_of_number();
  OPEN c;
    FETCH c BULK COLLECT INTO ret;
  CLOSE c;
  RETURN ret;
END;

它执行的JOIN比IN更好