提高 pl/sql 的性能，想要对 Q1 的结果执行 Q2

Question

CREATE or REPLACE PROCEDURE TEST(
activationStartDate IN DATE,
activationEndDate IN DATE,
deActivationStartDate IN DATE,
deActivationEndDate IN DATE
)
AS
FirstNameListTable LIST_TABLE;

{--COMMENT :LIST_TABLE is nested table :create or replace TYPE "LIST_TABLE" as table     of varchar2(20);-----Nested Table Declaration
/
}

totalSameFirstName NUMBER;
j NUMBER := 1;
BEGIN



SELECT first_name BULK COLLECT INTO FirstNameListTable FROM Employee where start_date between activationStartDate AND activationEndDate 
MINUS 
SELECT first_name FROM Employee where start_date between deActivationStartDate AND deActivationEndDate


FOR i IN FirstNameListTable.FIRST .. FirstNameListTable.LAST LOOP
     SELECT count(*) INTO totalSameFirstName FROM Employee where start_date between activationStartDate AND activationEndDate AND first_name=FirstNameListTable(i)

       IF totalSameFirstName > 2 THEN
            ---business logic
       END IF;
END LOOP;

实际上有两个查询，如上面提到的，

假设这个查询为 Q1：

SELECT first_name BULK COLLECT INTO FirstNameListTable FROM Employee where start_date between activationStartDate AND activationEndDate 
MINUS 
SELECT first_name FROM Employee where start_date between deActivationStartDate AND deActivationEndDate

这个查询为 Q2：

SELECT count(*) INTO totalSameFirstName FROM Employee where start_date between activationStartDate AND activationEndDate AND first_name=FirstNameListTable(i)

在这两个 qyery 中，我正在扫描完整的表，我认为没有必要。我正在迭代 Q1 的结果，然后再次扫描表以计算类似的名字。如果特定的名字出现两次以上，我会编写业务逻辑。

我可以组合这两个查询吗？意味着我想将 Q1 的结果存储在某些 PL/SQL 数据结构中，并希望对 Q1 的结果执行 Q2。

我想将 Q1 修改为

SELECT * BULK COLLECT INTO FirstNameListTable FROM Employee where start_date between activationStartDate AND activationEndDate 
 MINUS 
 SELECT * FROM Employee where start_date between deActivationStartDate AND deActivationEndDate

但是如何在 pl/sql dataStructure 中存储“select *”结果以及如何将这些记录传递给第二个查询..你能告诉我我的代码是什么样子吗？ 试着说得更清楚，我在这个 PL/SQL 中缺乏想法，即使它听起来像一个经典：我花了几个小时尝试解决这个问题，但一无所获

@Ollie 我按照u的建议更改了代码，但出现了一些错误并且无法解决它们

CREATE or REPLACE PROCEDURE TEST(
 activationStartDate IN DATE, 
 activationEndDate IN DATE, 
 deActivationStartDate IN DATE, 
  deActivationEndDate IN DATE, 
 Out_Entity OUT TEST1.RefCsr
 )
  AS
  FirstNameListTable CRITERIA_LIST_TABLE;
  out NUMBER;
  j NUMBER := 1;

  CURSOR main_cur
  IS
    WITH include_rec
      AS (SELECT first_name,COUNT(1) OVER (PARTITION BY first_name) name_count  FROM employee where start_date between activationStartDate AND activationEndDate 
     MINUS
        SELECT first_name FROM employee where start_date between deActivationStartDate AND deActivationEndDate)
       SELECT first_name FROM include_rec WHERE name_count > 2;             

    BEGIN
     OPEN main_cur;
     FETCH main_cur BULK COLLECT INTO FirstNameListTable;
     CLOSE main_cur;

      OPEN Out_Entity FOR SELECT * FROM TABLE(
                                               CAST (
                                                    FirstNameListTable AS LIST_TABLE
                                                 )
                                    ) Nos;

      END;
       /

错误1：PL/SQL：SQL语句被忽略{指示：“WITH include_rec”行} 错误 2：PL/SQL：ORA-01789：查询块的结果列数不正确{指示行：“AS (SELECT first_name,COUNT(1) OVER (PARTITION BY first_name)”} 提前致谢 等待你的回复

Answer 1

我想警告一下，我没有太多时间来处理这个问题，所以可能会有一些错误，但它应该给你我想告诉你的要点：

CREATE or REPLACE 
PROCEDURE TEST( 
   activationStartDate IN DATE, 
   activationEndDate IN DATE, 
   deActivationStartDate IN DATE, 
   deActivationEndDate IN DATE ) 
AS 
   CURSOR main_cur
   IS
      WITH include_rec 
        AS (SELECT first_name, 
                   start_date,
                   COUNT(1) OVER (PARTITION BY first_name) name_count
              FROM Employee 
             WHERE start_date BETWEEN activationStartDate 
                                  AND activationEndDate)
      SELECT DISTINCT 
             first_name
        FROM include_rec
       WHERE start_date NOT BETWEEN deActivationStartDate 
                                AND deActivationEndDate
         AND name_count > 2; 
   --
   FirstNameListTable dbms_sql.varchar2_table;    
BEGIN    
    OPEN main_cur;
    FETCH main_cur BULK COLLECT INTO FirstNameListTable;
    CLOSE main_cur;

    FOR i IN FirstNameListTable.FIRST .. FirstNameListTable.LAST 
    LOOP                 
           ---business logic         
    END LOOP; 

    etc...

我会还要说，如果您期望一个大的结果集，那么也将 BULK COLLECT 放入循环中以减少内存需求。

在理想情况下，您可以将变量activationStartDate、activationEndDate、deActivationStartDate 和deActivationEndDate 作为游标参数传递到游标中，以保持游标模块化，但这取决于您。 ;-)

希望这有帮助...
奥利。

编辑：
为了回答有关使用记录从表中选择*的问题，您可以根据游标列声明一个关联数组，对于上面的示例，如果您想从 EMPLOYEE 选择多个列，那么在WITH子句之后您将选择您想要的 EMPLOYEE 中的命名列，而不是：

FirstNameListTable dbms_sql.varchar2_table;

将关联数组类型和变量声明为：

TYPE main_cur_tabtype IS TABLE OF main_cur%ROWTYPE
     INDEX BY PLS_INTEGER;
main_cur_tab main_cur_tabtype;

这为您提供了一个灵活的数组，它将自动保存光标 (main_cur) 中选择的列。

您可以使用 BULK COLLECT: 将记录收集到此数组中，

OPEN main_cur;
FETCH main_cur BULK COLLECT INTO main_cur_tab;
CLOSE main_cur;

并使用以下命令循环它们：

FOR i IN main_cur_tab.FIRST .. main_cur_tab.LAST
LOOP
   etc.

至于将结果集返回到 Java，我不是 Java 专家，您需要在另一个论坛中询问或用以下标记来标记此问题Java 标签，希望 Java 人员能够找到它并为您解答。

Answer 2

你能用 SQL 做到这一点吗？
例如，也许可以使用这样的WITH：

WITH q1 AS (SELECT ....)
SELECT /* q2 */ ... FROM q1
WHERE ..
GROUP BY etc

如果您可以在SQL 中完成它，那么您可以随时将其嵌入到PL/SQL 中（如果您需要的话）。