Oracle 存储过程中的临时表

Question

我有一个在 MS SQL 和 DB2 上运行良好的存储过程。但无法让它在 Oracle 上运行，因为它使用在过程中创建的临时表。如果可能的话，我想知道有关我的存储过程的任何建议。请不要建议立即执行，因为我没有这样做的权限。这是一个巨大的过程，所以请指导我如何在没有临时表的情况下做到这一点。请注意，在创建过程之前创建临时表也不是一种选择。

提前致谢！

`CREATE OR REPLACE PROCEDURE LEAD_PURGE(closed IN DATE,
oprtr IN INTEGER,
leadscount OUT INTEGER)
is
BEGIN

CREATE TABLE LEADS_DELETED
(
    ID NUMBER(19),
    PRIMARY KEY (ID)
);

CREATE TABLE ASSIGNMENTS_DELETED
(
    ID NUMBER(19),
    PRIMARY KEY (ID)
);

CREATE TABLE MAPRESULTS_DELETED
(
    ID NUMBER(19),
    PRIMARY KEY (ID)
);

CREATE TABLE COMMAND_DELETED
(
    ID NUMBER(19),
    PRIMARY KEY (ID)
);

CREATE TABLE PROGRESS_STATUS_DELETED
(
    ID NUMBER(19),
    PRIMARY KEY (ID)
);

CREATE TABLE DETAILS_DELETED
(
    ID NUMBER(19),
    PRIMARY KEY (ID)
);

CREATE TABLE NEEDS_DELETED
(
    ID NUMBER(19),
    PRIMARY KEY (ID)
);
if oprtr = 0 then
INSERT INTO ASSIGNMENTS_DELETED
SELECT SEQID FROM ASSIGNMENT WHERE ROLE = 6 AND STATUS = 8 AND CLOSINGTIME < closed;


elsif oprtr = 1 then
INSERT INTO ASSIGNMENTS_DELETED
SELECT SEQID FROM ASSIGNMENT WHERE ROLE = 6 AND STATUS = 8 AND EXTRACT(YEAR FROM CLOSINGTIME) = EXTRACT(YEAR FROM closed)
                                                            AND EXTRACT(MONTH FROM CLOSINGTIME) = EXTRACT(MONTH FROM closed)
                                                            AND EXTRACT(DAY FROM CLOSINGTIME) = EXTRACT(DAY FROM closed);

elsif oprtr = 2 then
INSERT INTO ASSIGNMENTS_DELETED
SELECT SEQID FROM ASSIGNMENT WHERE ROLE = 6 AND STATUS = 8 AND CLOSINGTIME > closed;
end if;

INSERT INTO LEADS_DELETED
SELECT DISTINCT LEADSEQ FROM ASSIGNMENT WHERE SEQID IN (SELECT ID FROM ASSIGNMENTS_DELETED);

if oprtr = 0 then
insert into ASSIGNMENTS_DELETED
select SEQID FROM ASSIGNMENT WHERE LEADSEQ IN (SELECT ID FROM LEADS_DELETED) AND 
SEQID NOT IN (SELECT SEQID FROM ASSIGNMENT WHERE ROLE = 6 AND STATUS = 8 AND CLOSINGTIME < closed);

elsif oprtr = 1 then
insert into ASSIGNMENTS_DELETED
select SEQID FROM ASSIGNMENT WHERE LEADSEQ IN (SELECT ID FROM LEADS_DELETED) AND 
SEQID NOT IN (SELECT SEQID FROM ASSIGNMENT WHERE ROLE = 6 AND STATUS = 8 AND EXTRACT(YEAR FROM CLOSINGTIME) = EXTRACT(YEAR FROM closed)
                                                                         AND EXTRACT(MONTH FROM CLOSINGTIME) = EXTRACT(MONTH FROM closed)
                                                                         AND EXTRACT(DAY FROM CLOSINGTIME) = EXTRACT(DAY FROM closed));


elsif oprtr = 2 then
insert into ASSIGNMENTS_DELETED
select SEQID FROM ASSIGNMENT WHERE LEADSEQ IN (SELECT ID FROM LEADS_DELETED) AND 
SEQID NOT IN (SELECT SEQID FROM ASSIGNMENT WHERE ROLE = 6 AND STATUS = 8 AND CLOSINGTIME > closed);
end if;

SET leadscount = (SELECT COUNT(*) FROM LEADS_DELETED);

INSERT INTO MAPRESULTS_DELETED
SELECT ID FROM MAPRESULT WHERE ASSIGNMENTSEQ IN (SELECT ID FROM ASSIGNMENTS_DELETED);

INSERT INTO COMMAND_DELETED
SELECT ID FROM EXECUTERULECOMMAND WHERE MAPRESULTID IN (SELECT ID FROM MAPRESULTS_DELETED);

INSERT INTO PROGRESS_STATUS_DELETED
SELECT PROGRESS_STATUS_ID FROM COMMAND WHERE ID IN (SELECT ID FROM COMMAND_DELETED);

INSERT INTO DETAILS_DELETED
SELECT DETAILID FROM LEAD WHERE SEQID IN (SELECT ID FROM LEADS_DELETED);

INSERT INTO NEEDS_DELETED
SELECT NEEDSID FROM LEAD WHERE SEQID IN (SELECT ID FROM LEADS_DELETED);



DELETE FROM PROGRESS_STATUS WHERE ID IN (SELECT ID FROM PROGRESS_STATUS_DELETED);

DELETE FROM EXECUTERULECOMMAND WHERE ID IN (SELECT ID FROM COMMAND_DELETED);

DELETE FROM COMMAND WHERE ID IN (SELECT ID FROM COMMAND_DELETED);

DELETE FROM SIMPLECONDITIONAL WHERE RESULT IN (SELECT ID FROM MAPRESULTS_DELETED);

DELETE FROM MAPPREDICATE WHERE ROWBP IN (SELECT ID FROM MAPROW WHERE RESULT IN (SELECT ID FROM MAPRESULTS_DELETED));

DELETE FROM MAPROW WHERE RESULT IN (SELECT ID FROM MAPRESULTS_DELETED);

DELETE FROM MAPRESULT WHERE ID IN (SELECT ID FROM MAPRESULTS_DELETED);

DELETE FROM ASSIGNMENTATTACHMENTS WHERE ASSIGNMENTSEQ IN (SELECT ID FROM ASSIGNMENTS_DELETED);

DELETE FROM LEADOBSERVER WHERE ASSIGNSEQ IN (SELECT ID FROM ASSIGNMENTS_DELETED);

DELETE FROM MAPDESTINATIONS WHERE SUGGESTEDASSIGNID IN 
    (SELECT ID FROM SUGGESTEDASSIGNMENT WHERE ASSIGNMENT_SEQID IN (SELECT ID FROM ASSIGNMENTS_DELETED));

DELETE FROM SUGGESTEDASSIGNMENT WHERE ASSIGNMENT_SEQID IN (SELECT ID FROM ASSIGNMENTS_DELETED);

DELETE FROM PRODUCTINTEREST WHERE LEADSEQ IN (SELECT ID FROM LEADS_DELETED);



CREATE TABLE SALE_DELETED_EX
(
    ID NUMBER(19),
    PRIMARY KEY (ID)
);

INSERT into SALE_DELETED_EX SELECT SALEEXSEQ FROM SALE WHERE SEQID IN (SELECT SALEID FROM LEADSALES WHERE LEADID IN (SELECT ID FROM LEADS_DELETED));


DELETE FROM SALE WHERE SEQID IN (SELECT SALEID FROM LEADSALES WHERE LEADID IN (SELECT ID FROM LEADS_DELETED));


DELETE FROM SALEEXTENSIONS WHERE 
    SEQID IN (SELECT ID FROM SALE_DELETED_EX);

DELETE FROM LEADSALES WHERE LEADID IN (SELECT ID FROM LEADS_DELETED);


DELETE FROM NOTES WHERE OBJECTID IN (SELECT ID FROM NEEDS_DELETED) OR OBJECTID IN (SELECT ID FROM DETAILS_DELETED);

DELETE FROM HISTORYRECORD WHERE OBJECTID IN (SELECT ID FROM DETAILS_DELETED);

DELETE FROM DETAIL WHERE SEQID IN (SELECT ID FROM NEEDS_DELETED UNION SELECT ID FROM DETAILS_DELETED);

DELETE FROM MESSAGES WHERE PROVIDERID IN (SELECT ID FROM LEADS_DELETED);

DELETE FROM ASSIGNMENT WHERE LEADSEQ IN (SELECT ID FROM LEADS_DELETED);

DELETE FROM LEAD WHERE SEQID IN (SELECT ID FROM LEADS_DELETED);

CREATE TABLE LEADS_DELETED_E
(
    ID NUMBER(19),
    PRIMARY KEY (ID)
);

InSERT into LEADS_DELETED_E Select SEQID FROM LEADEXTENSIONS WHERE 
    SEQID NOT IN (SELECT LEADEXSEQ FROM LEAD);

DELETE FROM LEADEXTENSIONS WHERE 
    SEQID  IN (SELECT ID FROM LEADS_DELETED_E);


DROP TABLE LEADS_DELETED;

DROP TABLE ASSIGNMENTS_DELETED;

DROP TABLE MAPRESULTS_DELETED;

DROP TABLE COMMAND_DELETED;

DROP TABLE PROGRESS_STATUS_DELETED;

DROP TABLE DETAILS_DELETED;

DROP TABLE NEEDS_DELETED;

DROP TABLE LEADS_DELETED_E;

DROP TABLE SALE_DELETED_EX;

COMMIT;
END LEAD_PURGE;

`

Answer 1

正如奥利所说，你可以尝试使用 Collections。我只使用您的 LEADS_DELETED 表。

CREATE OR REPLACE TYPE Collection_LEADS_DELETED;
CREATE OR REPLACE TYPE TypeObject_LEADS_DELETED 
AS OBJECT (ID NUMBER(19));

FUNCTION your_function
   RETURN Collection_LEADS_DELETED PIPELINED
IS
     type t_array is table of number
          index by binary_integer;
     v_data   t_array;
     v_cont   integer;

     CURSOR cur_your_cursor
        IS
            SELECT DISTINCT LEADSEQ 
              FROM ASSIGNMENT 
             WHERE SEQID IN (SELECT ID FROM ASSIGNMENTS_DELETED);
        BEGIN
            v_cont := 0;
            FOR i in cur_your_cursor
            LOOP
                    v_cont := v_cont + 1;
                    v_data (v_cont) := i.LEADSEQ;
            END LOOP;
            For x in 1 .. v_data.count
            loop
                pipe row (TypeObject_LEADS_DELETED(v_data(x)));
            end loop;
            RETURN;
        END your_function;

然后，您可以在任何地方调用此函数，例如：

select * from table(your_function);

如果您无法创建对象类型（例如，您没有授权），因为您的临时表似乎只是一个整数数组，您可以只使用第一部分，避免返回任何内容或在程序之外进行管道传输或创建类型。在 v_data 变量中，您拥有所有所需的 ID。例如，您可以使用它，如下所示：

    For x in 1 .. v_data.count
        LOOP
          insert into ASSIGNMENTS_DELETED
             select SEQID FROM ASSIGNMENT 
             WHERE LEADSEQ IN (v_data(x)) 
             AND  SEQID NOT IN
                   (SELECT SEQID FROM ASSIGNMENT 
                    WHERE ROLE = 6 AND STATUS = 8 
                   AND CLOSINGTIME < closed);
       END LOOP;

Answer 2

使用 Oracle 集合（以及可能的表函数）而不是当前的临时表，那么您几乎可以“按原样”使用您的过程。

Oracle Collections 文档的链接为：http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28425/pipe_paral_tbl.htm