Oracle 11:想要将光标变量传递到过程中定义的变量

Question

以前我使用了一个临时表，该临时表在过程之外创建并在SP中插入数据并使用，但是如果我想在过程中创建一个temp表，则会抛出一些错误。避免尝试使用光标。

CREATE OR REPLACE PROCEDURE P_EMAIL_update
AS
   I    NUMBER := 1;
   J    NUMBER := 0;
   ID   NUMBER;
BEGIN
   INSERT INTO ENTITY_TEMP                                    --(RN,ENTITY_ID)
      SELECT ROWNUM, e.id
        FROM entity e, company c
       WHERE e.companyid = c.id AND e.status = 3;
   SELECT MAX (rn) INTO j FROM ENTITY_TEMP;
   WHILE i <= j
   LOOP
      SELECT entity_id
        INTO id
        FROM ENTITY_TEMP
       WHERE rn = i;
      INSERT INTO ACTIONS_EMAIL_MAPPING (ID,
                                         ISACTIVE,
                                         ACTIONNAME,
                                         EMAILFROM,
                                         EMAILSUBJECT,
                                         EMAILBODY
                                         )
         (SELECT SEQ_ENT.NEXTVAL,
                 'N',
                 ACTIONNAME,
                 EMAILFROM,
                 EMAILSUBJECT,
                 EMAILBODY || var_id
            FROM ACTIONS
           WHERE ISACTIVE = 'T' AND ACTIONNAME NOT IN ('sample'));
      I := I + 1;
   END LOOP;
   END;

Answer 1

您问题中的过程可以更简单地重写：

CREATE OR REPLACE PROCEDURE p_email_update AS
BEGIN
  INSERT INTO actions_email_mapping (id, isactive, actionname, emailfrom, emailsubject, emailbody)
  SELECT seq_ent.nextval,
         'N',
         a.actionname,
         a.emailfrom,
         a.emailsubject,
         a.emailbody || ent.id -- assuming var_id is a column of actions
  FROM   actions a
         CROSS JOIN (SELECT e.id
                     FROM   entity e
                            inner join company c on e.companyid = c.id
                     WHERE  e.status = 3) ent -- maybe this should be an inner join with some join conditions?
  WHERE  a.isactive = 'T'
  AND    a.actionname NOT IN ('sample');
END p_email_update;
/

无需重新发明交叉加入(这是您的代码在以一种非常回旋的方式进行的(。也不需要根据您在问题中告诉我们的内容，也无需将数据存储在临时表中，因为您可以直接在插入中引用该子查询。

我会质疑为什么您的操作表和实体和公司表的子查询之间没有任何加入条件。