oracle触发器的循环问题

[qq_31098647]

oracle触发器中以下语句如何循环取出这些值
select a.xm , a.lxdh ,to_char(a.djrq,'YYYY')||'年'||to_char(a.djrq,'MM')||'月'||to_char(a.djrq,'dd')||'日',decode( a.yysj,'1','上午','下午'),c.ksmc
into brxm,sphone,yy_Date,yysjsxw,yy_ks
from zjhis.gh_mzyyk a ,gy_ksdm c where a.ksdm=:new.ksdm and a.zt>=0 and a.mzlbxh=:new.mzlbxh and a.pbxh=:new.pbjlxh
and to_char(a.yyrq1,'yyyy-mm-dd')=to_char(:new.rq,'yyyy-mm-dd') and to_char(a.yyrq1,'yyyy-mm-dd')>to_char(sysdate,'yyyy-mm-dd') and c.ksdm=:new.ksdm ;
得到的是同一个科室对应的多人的信息，然后插入到某个表中
现在主要问题：就是循环取值这些人的信息，然后逐条插入某个表
请大神帮忙

[qq_31098647]

我现在主要问题是循环我不知道怎么写？记录的条数也是不定的，能举例子吗？谢谢

[卖水果的net]

for x in (select col1, col2 from t where ...) loop insert table test(c1, c2) values(x.col1, x.col2); end loop;

[卖水果的net]

1# 给你写的就是例子，记录数也是可以不固定的。

[qq_31098647]

if(:new.ysgh is not null) then select count(*) into number1 from zjhis.gh_mzyyk a where a.ksdm=:new.ksdm and a.zt>=0 and a.mzlbxh=:new.mzlbxh and a.ysgh=:new.ysgh and to_char(a.yyrq1,'yyyy-mm-dd')=to_char(:new.rq,'yyyy-mm-dd') and to_char(a.yyrq1,'yyyy-mm-dd')>to_char(sysdate,'yyyy-mm-dd'); if(number1>0) then select a.xm , a.lxdh ,to_char(a.yyrq1,'YYYY')||'年'||to_char(a.yyrq1,'MM')||'月'||to_char(a.yyrq1,'dd')||'日',decode( a.yysj,'1','上午','下午'),c.ksmc into brxm,sphone,yy_Date,yysjsxw,yy_ks from zjhis.gh_mzyyk a ,gy_ksdm c where a.ksdm=:new.ksdm and a.zt>=0 and a.mzlbxh=:new.mzlbxh and a.ysgh=:new.ysgh and a.pbxh=:new.pbjlxh and to_char(a.yyrq1,'yyyy-mm-dd')=to_char(:new.rq,'yyyy-mm-dd') and to_char(a.yyrq1,'yyyy-mm-dd')>to_char(sysdate,'yyyy-mm-dd') and c.ksdm=:new.ksdm ; end if ; else select count(*) into number2 from zjhis.gh_mzyyk a where a.ksdm=:new.ksdm and a.zt>=0 and a.mzlbxh=:new.mzlbxh and to_char(a.yyrq1,'yyyy-mm-dd')=to_char(:new.rq,'yyyy-mm-dd') and to_char(a.yyrq1,'yyyy-mm-dd')>to_char(sysdate,'yyyy-mm-dd') ; if(number2>0) then select a.xm , a.lxdh ,to_char(a.djrq,'YYYY')||'年'||to_char(a.djrq,'MM')||'月'||to_char(a.djrq,'dd')||'日',decode( a.yysj,'1','上午','下午'),c.ksmc into brxm,sphone,yy_Date,yysjsxw,yy_ks from gh_mzyyk a ,gy_ksdm c where a.ksdm=:new.ksdm and a.zt>=0 and a.mzlbxh=:new.mzlbxh and a.pbxh=:new.pbjlxh and to_char(a.yyrq1,'yyyy-mm-dd')=to_char(:new.rq,'yyyy-mm-dd') and to_char(a.yyrq1,'yyyy-mm-dd')>to_char(sysdate,'yyyy-mm-dd') and c.ksdm=:new.ksdm ; end if ; end if; scon:='尊敬的'||brxm||'您好！很抱歉的通知您，您预约的'||yy_Date||yy_ks||'科室'||yysjsxw||'已停诊，给您带来不变，敬请谅解。'; if (:new.swzgxh='-2' or :new.xwzgxh='-2' )then if (sphone is not null and length(sphone)=11 )then insert into his_itf.sms_submit_todo(col_id, phones, sms_con, sms_time, col_desc, flag, col_code, operid, sms_num, phone_num) values (his_itf.f_get_todoseq(),sphone, scon, to_char(sysdate,'yyyy-MM-dd HH24:mi:ss'), scode||' to '||sphone, '0', '0', scode, 1, 1 ); end if; end if;

[qq_31098647]

能帮我看一下，怎么修改吗？谢谢

[qq_31098647]

x需要提前声明吗？

[卖水果的net]

x 不用提前声明。 你要把建 表语句，和触发器完整的代码，都贴 出来；

[qq_31098647]

就是上面的这个，非常感谢

[qq_31098647]

create or replace trigger "YY_KSSTOP_TOSMS" after update of swzgxh , xwzgxh on gh_mzpbk for each row declare sphone varchar2(30); scon varchar2(2000); scode varchar2(80); yy_ks varchar2(30); brxm varchar2(30); yy_Date varchar2(50); begin scode:='00'; --从科室表中取出科室的名称 select a.ksmc into yy_ks from gy_ksdm a where a.ksdm=:new.ksdm; if(:new.ysgh is not null) then select count(*) into number1 from zjhis.gh_mzyyk a where a.ksdm=:new.ksdm and a.zt>=0 and a.mzlbxh=:new.mzlbxh and a.ysgh=:new.ysgh and to_char(a.yyrq1,'yyyy-mm-dd')=to_char(:new.rq,'yyyy-mm-dd') and to_char(a.yyrq1,'yyyy-mm-dd')>to_char(sysdate,'yyyy-mm-dd'); if(number1>0) then select a.xm , a.lxdh ,to_char(a.yyrq1,'YYYY')||'年'||to_char(a.yyrq1,'MM')||'月'||to_char(a.yyrq1,'dd')||'日',decode( a.yysj,'1','上午','下午'),c.ksmc into brxm,sphone,yy_Date,yysjsxw,yy_ks from gh_mzyyk a ,gy_ksdm c where a.ksdm=:new.ksdm and a.zt>=0 and a.mzlbxh=:new.mzlbxh and a.ysgh=:new.ysgh and to_char(a.yyrq1,'yyyy-mm-dd')=to_char(:new.rq,'yyyy-mm-dd') and to_char(a.yyrq1,'yyyy-mm-dd')>to_char(sysdate,'yyyy-mm-dd') and c.ksdm=:new.ksdm ; end if ; else select count(*) into number2 from zjhis.gh_mzyyk a where a.ksdm=:new.ksdm and a.zt>=0 and a.mzlbxh=:new.mzlbxh and to_char(a.yyrq1,'yyyy-mm-dd')=to_char(:new.rq,'yyyy-mm-dd') and to_char(a.yyrq1,'yyyy-mm-dd')>to_char(sysdate,'yyyy-mm-dd') ; if(number2>0) then select a.xm , a.lxdh ,to_char(a.djrq,'YYYY')||'年'||to_char(a.djrq,'MM')||'月'||to_char(a.djrq,'dd')||'日',decode( a.yysj,'1','上午','下午'),c.ksmc into brxm,sphone,yy_Date,yysjsxw,yy_ks from gh_mzyyk a ,gy_ksdm c where a.ksdm=:new.ksdm and a.zt>=0 and a.mzlbxh=:new.mzlbxh and to_char(a.yyrq1,'yyyy-mm-dd')=to_char(:new.rq,'yyyy-mm-dd') and to_char(a.yyrq1,'yyyy-mm-dd')>to_char(sysdate,'yyyy-mm-dd') and c.ksdm=:new.ksdm ; end if ; end if; scon:='尊敬的'||brxm||'您好！很抱歉的通知您，您预约的'||yy_Date||yy_ks||'科室'||yysjsxw||'已停诊，给您带来不变，敬请谅解。'; if (:new.swzgxh='-2' or :new.xwzgxh='-2' )then if (sphone is not null and length(sphone)=11 )then insert into sms_submit_todo(col_id, phones, sms_con, sms_time, col_desc, flag, col_code, operid, sms_num, phone_num) values (his_itf.f_get_todoseq(),sphone, scon, to_char(sysdate,'yyyy-MM-dd HH24:mi:ss'), scode||' to '||sphone, '0', '0', scode, 1, 1 ); end if; end if; end YY_KSSTOP_TOSMS;