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 ;
得到的是同一个科室对应的多人的信息，然后插入到某个表中
现在主要问题：就是循环取值这些人的信息，然后逐条插入某个表
请大神帮忙
9 个解决方案
#1
for x in (select col1, col2 from t where ...)
loop
insert table test(c1, c2) values(x.col1, x.col2);
end loop;
loop
insert table test(c1, c2) values(x.col1, x.col2);
end loop;
#2
我现在主要问题是循环我不知道怎么写？记录的条数也是不定的，能举例子吗？谢谢
#3
1# 给你写的就是例子，记录数也是可以不固定的。
#4
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;
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;
#5
能帮我看一下，怎么修改吗？谢谢
#6
x需要提前声明吗？
#7
x 不用提前声明。
你要把建 表语句，和触发器完整的代码，都贴 出来；
你要把建 表语句，和触发器完整的代码，都贴 出来；
#8
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;
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;
#9
就是上面的这个，非常感谢
#1
for x in (select col1, col2 from t where ...)
loop
insert table test(c1, c2) values(x.col1, x.col2);
end loop;
loop
insert table test(c1, c2) values(x.col1, x.col2);
end loop;
#2
我现在主要问题是循环我不知道怎么写？记录的条数也是不定的，能举例子吗？谢谢
#3
1# 给你写的就是例子，记录数也是可以不固定的。
#4
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;
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;
#5
能帮我看一下，怎么修改吗？谢谢
#6
x需要提前声明吗？
#7
x 不用提前声明。
你要把建 表语句，和触发器完整的代码，都贴 出来；
你要把建 表语句，和触发器完整的代码，都贴 出来；
#8
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;
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;
#9
就是上面的这个，非常感谢