触发器中的循环问题,请教各位高人.[在线给分]

Question

oracle中,从一张表取出某一列值相等的若干行,依次插入到另一张表,想在触发器里面实现,请各位高手看看该这么写这个循环.
create or replace trigger production_trigger
  before insert on ezoffice.qtsmt$t3057  
  for each row
declare
  record_id number(20);
  work_id number(20);
  --
  p_tranType char(20);
  p_allowSmsRemind char(20);
  p_pressDealType char(20);
  --
begin
  select decode(ezoffice.wf_activity.tranType,null,'0',ezoffice.wf_activity.tranType) into p_tranType from ezoffice.wf_activity;
  select decode(ezoffice.wf_activity.allowSmsRemind,null,'0',ezoffice.wf_activity.allowSmsRemind) into p_allowSmsRemind from ezoffice.wf_activity;
  select decode(ezoffice.wf_activity.pressDealType,null,'0',ezoffice.wf_activity.pressDealType) into p_pressDealType from ezoffice.wf_activity;
  select ezoffice.hibernate_sequence.nextval into record_id from dual;
  --record_id应用为QTSMT$T3057_ID的值
  :new.QTSMT$T3057_ID:=record_id;
  --从这里开始循环.
    insert into EZOFFICE.wf_proceedActivity
    (WF_ProceedActivity_Id,
     wf_activity_id,
     activityName,
     activityDescription,
     activityType,
     allowStandFor,
     allowCancel,
     allowTransition,
     participantType,
     participantUser,
     participantGroup,
     participantUserName,
     participantUserField,
     pressType,
     deadLineTime,
     pressMotionTime,
     activityDocumentation,
     activityIcon,
     activityBeginEnd,
     transactType,
     actiCommField,
     needPassRound,
     passRoundUserType,
     passRoundUser,
     passRoundUserGroup,
     passRoundUserName,
     passRoundUserField,
     passRoundCommField,
     participantRole,
     passRoundRole,
     activityClass,
     activitySubProc,
     subProcType,
     PARTICIPANTGIVENORG,
     PASSROUNDGIVENORG,
     commentRange,
     DOMAIN_ID,
     operbutton,
     form_id,
     actiCommFieldType,
     passRoundCommFieldType,
     tranCustomExtent,
     tranCustomExtentId
     )
    select ezoffice.hibernate_sequence.nextval,
           wf_activity_id,
           activityName,
           activityDescription,
           activityType,
           allowStandFor,
           allowCancel,
           allowTransition,
           participantType,
           participantUser,
           participantGroup,
           participantUserName,
           participantUserField,
           pressType,
           deadLineTime,
           pressMotionTime,
           activityDocumentation,
           activityIcon,
           activityBeginEnd,
           transactType,
           actiCommField,
           needPassRound,
           passRoundUserType,
           passRoundUser,
           passRoundUserGroup,
           passRoundUserName,
           passRoundUserField,
           passRoundCommField,
           participantRole,
           passRoundRole,
           activityClass,
           activitySubProc,
           subProcType,
           PARTICIPANTGIVENORG,
           PASSROUNDGIVENORG,
           commentRange,
           domain_id,
           operButton,
           form_id,
           actiCommFieldType,
           passRoundCommFieldType,
           tranCustomExtent,
           tranCustomExtentId
      from EZOFFICE.wf_activity
      where wf_workflowprocess_id = 32226;
 update EZOFFICE.wf_proceedActivity set dealwithsign = 0,
       wf_workflowprocess_id = 32226,
       employee_id = 6527,
       ttable_id = 32174,
       trecord_id = record_id;
--到这都是要循环的.
end production_trigger;

Answer 1

我用 while EZOFFICE.wf_activity.wf_workflowprocess_id = 32226 loop时
错误：PLS-00357: 在此上下文中不允许表, 视图或序列引用 'EZOFFICE.WF_ACTIVITY.WF_WORKFLOWPROCESS_ID'

Answer 2

循环用游标呀
类似：begin
  for cur in(select employee_id from hr.employees)
  loop
    dbms_output.put_line(cur.employee_id);
  end loop;
end;

Answer 3

while EZOFFICE.wf_activity.wf_workflowprocess_id = 32226 loop
这样用好像是不正确的。要么把EZOFFICE.wf_activity.wf_workflowprocess_id值赋给一个变量，然后再进行判断。

Answer 4

我改成变量了.
编译成功.
但是当我insert的时候,这行报错:
select decode(ezoffice.wf_activity.tranType,null,'0',ezoffice.wf_activity.tranType) into p_tranType from ezoffice.wf_activity;ORA-01422: 实际返回的行数超出请求的行数.

Answer 5

想实现类似rs.getString("allowSmsRemind")==null?"0":rs.getString("allowSmsRemind")这样的功能.
但oracle中没有 = ? :  这样的表达式.怎么会返回了多个值呢.decode不是和case类似的吗?  应该只返回一个才对啊.

Answer 6

插入时,当被取值的A列是null时,对应的新列B显示0,否者B列显示的就是A的值.

Answer 7

刚才突然明白了,我select的结果是多行,但参数只是一个,对不上.

Answer 8

你描述的不久可以用decode实现吗？

Answer 9

是可以用decode实现  但是当我select decode(ezoffice.wf_activity.tranType,null,'0',ezoffice.wf_activity.tranType)的时候,结果是多行,没法赋值给变量.