请问bzszp(SongZip),如何分段转换,我只会在sql中写触发器,这次突然要转到oracle,没有准备啊我的代码长是因为中间有几个判断,请帮忙改一下,今天下午就要去装啊!!救命啊!!!解决了狂散500分!!!
解决方案 »
- sql求救
- 访问数据提示ora-12170 连接超时,操作超时
- 求一句sql,我想看到a114用户下的所有数据量>100万的表的信息,如何搞呢?
- ORACLE11g 登陆问题求解
- [请教:]如何在数据表的字段之间追加新字段
- oracle 怎样实现合并相同行 希望是 一条语句
- oci使用中的一点疑问(回答有效, 另外开帖加分)
- 急急急急急急
- 数据库导入时遇到 ORACLE error 942
- group by 问题??
- 初学者问题:如何将一用户的对象权限全部复制到另一用户中,或一个用户怎么样添加对象权限呢?
- 怎么办????Oracle安装如何选择另存数据文件 ???求助求助!!!!!!!!!!!!
CREATE TRIGGER gc_phase_update ON [dbo].[gc_phase]
FOR UPDATE
ASdeclare @itemcode varchar(50)
declare @phasecode varchar(50)
declare @enddate varchar(50)
declare @start_date varchar(50)
declare @end_date varchar(50)
declare @basestationname varchar(255)
declare @weekid varchar(2)
declare @cityname varchar(20)
declare @endCnt int
declare @phase_type varchar(20)
declare @downpercent decimal(18,6)
declare @downpercent_del decimal(18,6)
declare @i intselect @downpercent_del=downpercent from deleted --得到被修改那条记录的downpercent值,并赋给@downpercent_del变量
select @itemcode=itemcode,@phasecode=phaseno,@downpercent=downpercent,@enddate=realenddate from inserted --获得修改后的部分字段的值,并存放到变量中--如果项目阶段编码为空,说明不是基站阶段项目,返回
if(@phasecode is null) return
select @i=count(*) from gc_phase where @phasecode in ('a1001','a1002','a1005','a3007','a2011','a2003','a1006','a1007','a4002','a6003','a5002','a7002','a7003','a7004','c1001','c1002','c1004','c1005','c3007','c2011','c2003','c1006','c1007','c4002','c6003','c5002','c7002','c7003','c7004','c7005','c7007','c7008','c7009') --检测被修改的phasecode是否在指定的phasecode列表中
if(@i=0) return --在phasecode列表中没有就返回
--if(@downpercent<100) return
--根据阶段项目编码获得项目基站名称,如果没有基站,返回
select @basestationname=basestationname from gc_item_property where itemcode=@itemcode
select @basestationname,@itemcode --调试语句
if(@basestationname is null or @basestationname='') return
--根据基站名称(基站项目编码)获得基站的阶段属性(9A,9B,9C...),所属地市,该站类型(新建、扩容、O改S...)
select @phase_type=substring(gpdate,1,2),@cityname=incity from gc_item_property inner join gc_basestation_info on gc_item_property.basestationname=gc_basestation_info.basestationname where isbasestation='1' and gc_item_property.itemcode=@itemcode
--根据阶段结束日期判断阶段在哪一周完成
if(@phase_type='9A')
begin
if(@enddate between '2003-02-27' and '2004-02-27') begin set @weekid='1' set @start_date='2003-02-27' set @end_date='2004-02-27' end
if(@enddate between '2004-02-28' and '2004-03-05') begin set @weekid='2' set @start_date='2004-02-28' set @end_date='2004-03-05' end
if(@enddate between '2004-03-06' and '2004-03-12') begin set @weekid='3' set @start_date='2004-03-06' set @end_date='2004-03-12' end
if(@enddate between '2004-03-13' and '2004-03-19') begin set @weekid='4' set @start_date='2004-03-13' set @end_date='2004-03-19' end
if(@enddate between '2004-03-20' and '2004-03-26') begin set @weekid='5 'set @start_date='2004-03-20' set @end_date='2004-03-26' end
if(@enddate between '2004-03-27' and '2004-04-02') begin set @weekid='6' set @start_date='2004-03-27' set @end_date='2004-04-02' end
if(@enddate between '2004-04-03' and '2004-04-09') begin set @weekid='7' set @start_date='2004-04-03' set @end_date='2004-04-09' end
if(@enddate between '2004-04-10' and '2008-01-01') begin set @weekid='8' set @start_date='2004-04-10' set @end_date='2008-01-01' end
end
else if(@phase_type='9B')
begin
if(@enddate between '2003-01-01' and '2004-03-21') begin set @weekid='1' set @start_date='2003-01-01' set @end_date='2004-03-21' end
if(@enddate between '2004-03-22' and '2004-04-11') begin set @weekid='2' set @start_date='2004-03-22' set @end_date='2004-04-11' end
if(@enddate between '2004-04-12' and '2004-05-02') begin set @weekid='3' set @start_date='2004-04-12' set @end_date='2004-05-02' end
if(@enddate between '2004-05-03' and '2004-05-23') begin set @weekid='4' set @start_date='2004-05-03' set @end_date='2004-05-23' end
if(@enddate between '2004-05-24' and '2004-06-13') begin set @weekid='5 'set @start_date='2004-05-24' set @end_date='2004-06-13' end
if(@enddate between '2004-06-14' and '2004-07-01') begin set @weekid='6' set @start_date='2004-06-14' set @end_date='2004-07-01' end
if(@enddate between '2004-07-02' and '2008-01-01') begin set @weekid='7' set @start_date='2004-07-02' set @end_date='2008-01-01' end
end
else if(@phase_type='9C')
begin
if(@enddate between '2004-01-01' and '2004-07-24') begin set @weekid='1' set @start_date='2004-01-01' set @end_date='2004-07-24' end
if(@enddate between '2004-07-25' and '2004-08-14') begin set @weekid='2' set @start_date='2004-07-25' set @end_date='2004-08-14' end
if(@enddate between '2004-08-15' and '2004-09-04') begin set @weekid='3' set @start_date='2004-08-15' set @end_date='2004-09-04' end
if(@enddate between '2004-09-05' and '2004-09-25') begin set @weekid='4' set @start_date='2004-09-05' set @end_date='2004-09-25' end
if(@enddate between '2004-09-26' and '2004-10-16') begin set @weekid='5 'set @start_date='2004-09-26' set @end_date='2004-10-16' end
if(@enddate between '2004-10-17' and '2004-11-06') begin set @weekid='6' set @start_date='2004-10-17' set @end_date='2004-11-06' end
if(@enddate between '2004-11-07' and '2008-01-01') begin set @weekid='7' set @start_date='2004-11-07' set @end_date='2008-01-01' end
end
else
begin
return
end
--求该周、该站类型、该阶段、该九期阶段(9A、9B、9C...)
--select * from gc_phase where (realenddate between @start_date and @end_date) and (phaseno=@phasecode) and --如果原来没有记录,插入并设置为1
select @endCnt=count(endCnt) from gc_report_bsc_progress where (phase_type=@phase_type) and (weekname=@weekid) and (cityname=@cityname) and (progresscode=@phasecode)
if(@weekid is null)return
select @cityname,@weekid,@enddate,@phasecode,@phase_type,@downpercent,@downpercent_del
if(@endCnt = 0) if(@downpercent=100)
begin
insert into gc_report_bsc_progress(cityname,weekname,enddate,progresscode,endCnt,phase_type) values(@cityname,@weekid,@enddate,@phasecode,1,@phase_type)
end
--else
if(@endCnt<>0)
begin
--在此处需要得到原有进度还更新后的进度
--select @downpercent_del=downpercent from deleted
--select @downpercent=downpercent from inserted
if(@downpercent =100 and @downpercent_del<100) --如果是删除,原有进度=100是,减去1,原有进度<100,不变
begin
update gc_report_bsc_progress set endCnt=endCnt+1 where (phase_type=@phase_type) and (weekname=@weekid) and (cityname=@cityname) and (progresscode=@phasecode)
end
if(@downpercent<100 and @downpercent_del=100)--如果是修改,原有进度小于100,现有进度=100,加1,
begin
update gc_report_bsc_progress set endCnt=endCnt-1 where (phase_type=@phase_type) and (weekname=@weekid) and (cityname=@cityname) and (progresscode=@phasecode)
end
if(@downpercent=100 and @downpercent_del=100) return --原有进度=100,现有小于100,减去1,如果原有进度=100,现有=100,不变
end
这些注释可以了吗??
CREATE or replace TRIGGER gc_phase_update
before update ON gc_phase
FOR each row
declare
v_itemcode varchar2(50);
v_phasecode varchar2(50);
v_enddate varchar2(50);
v_start_date varchar2(50);
v_end_date varchar2(50);
v_basestationname varchar2(255);
v_weekid varchar2(2);
v_cityname varchar2(20);
v_endCnt number;
v_phase_type varchar2(20);
v_downpercent number(18,6);
v_downpercent_del number(18,6);
i number;
begin
downpercent_del:=:old.downpercent; --修改前还是修改后?
v_itemcode:=:new.itemcode;
v_phasecode:=:new.phaseno;
v_downpercent:=:new.downpercent;
v_enddate:=:new.realenddate; --获得修改后的部分字段的值,并存放到变量中
--如果项目阶段编码为空,说明不是基站阶段项目,返回
if(phasecode is null) then
return;
end if ;select count(*) into i from gc_phase where v_phasecode in ('a1001','a1002','a1005','a3007','a2011','a2003','a1006','a1007','a4002','a6003','a5002','a7002','a7003','a7004','c1001','c1002','c1004','c1005','c3007','c2011','c2003','c1006','c1007','c4002','c6003','c5002','c7002','c7003','c7004','c7005','c7007','c7008','c7009'); --检测被修改的phasecode是否在指定的phasecode列表中 ???
if (i=0) then
return; --在phasecode列表中没有就返回
end if;--根据阶段项目编码获得项目基站名称,如果没有基站,返回
select basestationname into v_basestationname from gc_item_property where itemcode=v_itemcode;
if(v_basestationname is null or v_basestationname='') then
return;
end if;--根据基站名称(基站项目编码)获得基站的阶段属性(9A,9B,9C...),所属地市,该站类型(新建、扩容、O改S...)
select substring(gpdate,1,2),incity into v_phase_type,v_cityname
from gc_item_property , gc_basestation_info where gc_item_property.basestationname=gc_basestation_info.basestationname
and isbasestation='1' and gc_item_property.itemcode=v_itemcode;
--根据阶段结束日期判断阶段在哪一周完成
if(v_phase_type='9A') then
begin
if(v_enddate between '2003-02-27' and '2004-02-27') then
v_weekid:='1';
v_start_date:=to_date('2003-02-27','yyyy-mm-dd');
v_end_date:=to_date('2004-02-27','yyyy-mm-dd');
end if;
--下面语句同上(做类似的转换)
if(@enddate between '2004-02-28' and '2004-03-05') begin set @weekid='2' set @start_date='2004-02-28' set @end_date='2004-03-05' end
if(@enddate between '2004-03-06' and '2004-03-12') begin set @weekid='3' set @start_date='2004-03-06' set @end_date='2004-03-12' end
if(@enddate between '2004-03-13' and '2004-03-19') begin set @weekid='4' set @start_date='2004-03-13' set @end_date='2004-03-19' end
if(@enddate between '2004-03-20' and '2004-03-26') begin set @weekid='5 'set @start_date='2004-03-20' set @end_date='2004-03-26' end
if(@enddate between '2004-03-27' and '2004-04-02') begin set @weekid='6' set @start_date='2004-03-27' set @end_date='2004-04-02' end
if(@enddate between '2004-04-03' and '2004-04-09') begin set @weekid='7' set @start_date='2004-04-03' set @end_date='2004-04-09' end
if(@enddate between '2004-04-10' and '2008-01-01') begin set @weekid='8' set @start_date='2004-04-10' set @end_date='2008-01-01' end end;
elseif (@phase_type='9B') then
begin
--下面语句同上(做类似的转换)
if(@enddate between '2003-01-01' and '2004-03-21') begin set @weekid='1' set @start_date='2003-01-01' set @end_date='2004-03-21' end
if(@enddate between '2004-03-22' and '2004-04-11') begin set @weekid='2' set @start_date='2004-03-22' set @end_date='2004-04-11' end
if(@enddate between '2004-04-12' and '2004-05-02') begin set @weekid='3' set @start_date='2004-04-12' set @end_date='2004-05-02' end
if(@enddate between '2004-05-03' and '2004-05-23') begin set @weekid='4' set @start_date='2004-05-03' set @end_date='2004-05-23' end
if(@enddate between '2004-05-24' and '2004-06-13') begin set @weekid='5 'set @start_date='2004-05-24' set @end_date='2004-06-13' end
if(@enddate between '2004-06-14' and '2004-07-01') begin set @weekid='6' set @start_date='2004-06-14' set @end_date='2004-07-01' end
if(@enddate between '2004-07-02' and '2008-01-01') begin set @weekid='7' set @start_date='2004-07-02' set @end_date='2008-01-01' end
end;
elseif(@phase_type='9C') then
begin --下面语句同上(做类似的转换)
if(@enddate between '2004-01-01' and '2004-07-24') begin set @weekid='1' set @start_date='2004-01-01' set @end_date='2004-07-24' end
if(@enddate between '2004-07-25' and '2004-08-14') begin set @weekid='2' set @start_date='2004-07-25' set @end_date='2004-08-14' end
if(@enddate between '2004-08-15' and '2004-09-04') begin set @weekid='3' set @start_date='2004-08-15' set @end_date='2004-09-04' end
if(@enddate between '2004-09-05' and '2004-09-25') begin set @weekid='4' set @start_date='2004-09-05' set @end_date='2004-09-25' end
if(@enddate between '2004-09-26' and '2004-10-16') begin set @weekid='5 'set @start_date='2004-09-26' set @end_date='2004-10-16' end
if(@enddate between '2004-10-17' and '2004-11-06') begin set @weekid='6' set @start_date='2004-10-17' set @end_date='2004-11-06' end
if(@enddate between '2004-11-07' and '2008-01-01') begin set @weekid='7' set @start_date='2004-11-07' set @end_date='2008-01-01' end
end;
else
return;
end if;--求该周、该站类型、该阶段、该九期阶段(9A、9B、9C...)
--select * from gc_phase where (realenddate between @start_date and @end_date) and (phaseno=@phasecode) and ---???
--如果原来没有记录,插入并设置为1
select count(endCnt) into v_endCnt from gc_report_bsc_progress where (phase_type=v_phase_type) and (weekname=v_weekid) and (cityname=v_cityname) and (progresscode=v_phasecode); if(v_weekid is null) then
return;
end if;if(v_endCnt = 0) or (v_downpercent=100) then
insert into gc_report_bsc_progress(cityname,weekname,enddate,progresscode,endCnt,phase_type) values(v_cityname,v_weekid,v_enddate,v_phasecode,1,v_phase_type)
end if;
if(v_endCnt<>0) then
--在此处需要得到原有进度还更新后的进度
--select @downpercent_del=downpercent from deleted
--select @downpercent=downpercent from inserted if(v_downpercent=100 and v_downpercent_del<100) then
--如果是删除,原有进度=100是,减去1,原有进度 <100,不变
update gc_report_bsc_progress set endCnt=endCnt+1
where phase_type=v_phase_type and weekname=v_weekid and cityname=v_cityname and
progresscode=v_phasecode;
end if; if(v_downpercent<100 and v_downpercent_del=100) then
--如果是修改,原有进度小于100,现有进度=100,加1,
update gc_report_bsc_progress set endCnt=endCnt-1
where phase_type=v_phase_type and weekname=v_weekid and cityname=v_cityname and progresscode=v_phasecode;
end if; if(@downpercent=100 and @downpercent_del=100) then
--原有进度=100,现有小于100,减去1,如果原有进度=100,现有=100,不变
return;
end if;
end if; end gc_phase_update;
/
CREATE or replace TRIGGER gc_phase_update
before update ON gc_phase
FOR each row
declare
v_itemcode varchar2(50);
v_phasecode varchar2(50);
v_enddate varchar2(50);
v_start_date varchar2(50);
v_end_date varchar2(50);
v_basestationname varchar2(255);
v_weekid varchar2(2);
v_cityname varchar2(20);
v_endCnt number;
v_phase_type varchar2(20);
v_downpercent number(18,6);
v_downpercent_del number(18,6);
i number;
begin
downpercent_del:=:old.downpercent; --修改前还是修改后?
v_itemcode:=:new.itemcode;
v_phasecode:=:new.phaseno;
v_downpercent:=:new.downpercent;
v_enddate:=:new.realenddate; --获得修改后的部分字段的值,并存放到变量中
--如果项目阶段编码为空,说明不是基站阶段项目,返回
if(phasecode is null) then
return;
end if ;select count(*) into i from gc_phase where v_phasecode in ('a1001','a1002','a1005','a3007','a2011','a2003','a1006','a1007','a4002','a6003','a5002','a7002','a7003','a7004','c1001','c1002','c1004','c1005','c3007','c2011','c2003','c1006','c1007','c4002','c6003','c5002','c7002','c7003','c7004','c7005','c7007','c7008','c7009'); --检测被修改的phasecode是否在指定的phasecode列表中 ???
if (i=0) then
return; --在phasecode列表中没有就返回
end if;--根据阶段项目编码获得项目基站名称,如果没有基站,返回
select basestationname into v_basestationname from gc_item_property where itemcode=v_itemcode;
if(v_basestationname is null or v_basestationname='') then
return;
end if;--根据基站名称(基站项目编码)获得基站的阶段属性(9A,9B,9C...),所属地市,该站类型(新建、扩容、O改S...)
select substring(gpdate,1,2),incity into v_phase_type,v_cityname
from gc_item_property , gc_basestation_info where gc_item_property.basestationname=gc_basestation_info.basestationname
and isbasestation='1' and gc_item_property.itemcode=v_itemcode;
--根据阶段结束日期判断阶段在哪一周完成
if(v_phase_type='9A') then
begin
if(v_enddate between '2003-02-27' and '2004-02-27') then
v_weekid:='1';
v_start_date:=to_date('2003-02-27','yyyy-mm-dd');
v_end_date:=to_date('2004-02-27','yyyy-mm-dd');
end if;
--下面语句同上(做类似的转换)
if(v_enddate between '2004-02-28' and '2004-03-05') then
v_weekid:='2';
v_start_date:=to_date('2004-02-28','yyyy-mm-dd');
v_end_date:=to_date('2004-03-05','yyyy-mm-dd');
end if
if(v_enddate between '2004-03-06' and '2004-03-12') then
v_weekid:='3';
v_start_date:=to_date('2004-03-06','yyyy-mm-dd'):
v_end_date:=to_date('2004-03-12','');
end if
if(v_enddate between '2004-03-13' and '2004-03-19') then
v_weekid:='4';
v_start_date:=to_date('2004-03-13','yyyy-mm-dd');
v_end_date:=to_date('2004-03-19','yyyy-mm-dd');
end if
if(v_enddate between '2004-03-20' and '2004-03-26') then
v_weekid:='5';
v_start_date:=to_date('2004-03-20','yyyy-mm-dd');
v_end_date:=to_date('2004-03-26','yyyy-mm-dd');
end if
if(v_enddate between '2004-03-27' and '2004-04-02') then
v_weekid:='6';
v_start_date:=to_date('2004-03-27','yyyy-mm-dd');
v_end_date:=to_date('2004-04-02','yyyy-mm-dd');
end if
if(v_enddate between '2004-04-03' and '2004-04-09') then
v_weekid:='7';
v_start_date:=to_date('2004-04-03','yyyy-mm-dd');
v_end_date:=to_date('2004-04-09','yyyy-mm-dd');
end if
if(v_enddate between '2004-04-10' and '2008-01-01') then
v_weekid:='8';
v_start_date:=to_date('2004-04-10','yyyy-mm-dd');
v_end_date:=to_date('2008-01-01','yyyy-mm-dd');
end if end;
else if (v_phase_type='9B') then
begin
--下面语句同上(做类似的转换)
if(v_enddate between '2003-01-01' and '2004-03-21') then
v_weekid:='1';
v_start_date:='2003-01-01';
v_end_date:='2004-03-21';
end if
if(v_enddate between '2004-03-22' and '2004-04-11') then
v_weekid:='2';
v_start_date:='2004-03-22';
v_end_date:='2004-04-11';
end if
if(v_enddate between '2004-04-12' and '2004-05-02') then
v_weekid:='3';
v_start_date:='2004-04-12';
v_end_date:='2004-05-02';
end if
if(v_enddate between '2004-05-03' and '2004-05-23') then
v_weekid:='4';
v_start_date:='2004-05-03';
v_end_date:='2004-05-23';
end if
if(v_enddate between '2004-05-24' and '2004-06-13') then
v_weekid:='5';
v_start_date:='2004-05-24';
v_end_date:='2004-06-13';
end if
if(v_enddate between '2004-06-14' and '2004-07-01') then
v_weekid:='6';
v_start_date:='2004-06-14';
v_end_date:='2004-07-01';
end if
if(v_enddate between '2004-07-02' and '2008-01-01') then
v_weekid:='7';
v_start_date:='2004-07-02';
v_end_date:='2008-01-01';
end if
end;
else if(v_phase_type='9C') then
begin --下面语句同上(做类似的转换)
if(v_enddate between '2004-01-01' and '2004-07-24') then
v_weekid:='1';
v_start_date:='2004-01-01';
v_end_date:='2004-07-24';
end if
if(v_enddate between '2004-07-25' and '2004-08-14') then
v_weekid:='2';
v_start_date:='2004-07-25';
v_end_date:='2004-08-14';
end if
if(v_enddate between '2004-08-15' and '2004-09-04') then
v_weekid:='3';
v_start_date:='2004-08-15';
v_end_date:='2004-09-04';
end if
if(v_enddate between '2004-09-05' and '2004-09-25') then
v_weekid:='4';
v_start_date:='2004-09-05';
v_end_date:='2004-09-25';
end if
if(v_enddate between '2004-09-26' and '2004-10-16') then
v_weekid:='5';
v_start_date:='2004-09-26';
v_end_date:='2004-10-16'
end if
if(v_enddate between '2004-10-17' and '2004-11-06') then
v_weekid:='6';
v_start_date:='2004-10-17';
v_end_date:='2004-11-06';
end if
if(v_enddate between '2004-11-07' and '2008-01-01') then
v_weekid:='7';
v_start_date:='2004-11-07';
v_end_date:='2008-01-01';
end if
end;
else
return;
end if;--求该周、该站类型、该阶段、该九期阶段(9A、9B、9C...)
--select * from gc_phase where (realenddate between v_start_date and v_end_date) and (phaseno=v_phasecode) and ---???
--如果原来没有记录,插入并设置为1
select count(endCnt) into v_endCnt from gc_report_bsc_progress where (phase_type=v_phase_type) and (weekname=v_weekid) and (cityname=v_cityname) and (progresscode=v_phasecode); if(v_weekid is null) then
return;
end if;
if(v_endCnt = 0) or (v_downpercent=100) then
insert into gc_report_bsc_progress(cityname,weekname,enddate,progresscode,endCnt,phase_type) values(v_cityname,v_weekid,v_enddate,v_phasecode,1,v_phase_type)
end if;
if(v_endCnt<>0) then
--在此处需要得到原有进度还更新后的进度
--select v_downpercent_del=downpercent from deleted
--select v_downpercent=downpercent from inserted if(v_downpercent=100 and v_downpercent_del<100) then
--如果是删除,原有进度=100是,减去1,原有进度 <100,不变
update gc_report_bsc_progress set endCnt=endCnt+1
where phase_type=v_phase_type and weekname=v_weekid and cityname=v_cityname and
progresscode=v_phasecode;
end if; if(v_downpercent<100 and v_downpercent_del=100) then
--如果是修改,原有进度小于100,现有进度=100,加1,
update gc_report_bsc_progress set endCnt=endCnt-1
where phase_type=v_phase_type and weekname=v_weekid and cityname=v_cityname and progresscode=v_phasecode;
end if; if(v_downpercent=100 and v_downpercent_del=100) then
--原有进度=100,现有小于100,减去1,如果原有进度=100,现有=100,不变
return;
end if;
end if; end gc_phase_update;
保存的时候还是提示无效的触发器说明,请大哥指出错误出在哪里!!
end if后面要有;号。你可以先用少量的代码,编译通过后再逐步的往上加。
before update ON gc_phase
FOR each row
declare
v_itemcode varchar2(50);
v_phasecode varchar2(50);
v_enddate varchar2(50);
v_start_date varchar2(50);
v_end_date varchar2(50);
v_basestationname varchar2(255);
v_weekid varchar2(2);
v_cityname varchar2(20);
v_endCnt number;
v_phase_type varchar2(20);
v_downpercent number(18,6);
v_downpercent_del number(18,6);
i number;
begin
downpercent_del:=:old.downpercent; --修改前还是修改后?
v_itemcode:=:new.itemcode;
v_phasecode:=:new.phaseno;
v_downpercent:=:new.downpercent;
v_enddate:=:new.realenddate; --获得修改后的部分字段的值,并存放到变量中
--如果项目阶段编码为空,说明不是基站阶段项目,返回
if(phasecode is null) then
return;
end if--检测被修改的phasecode是否在指定的phasecode列表中
select count(*) into i from gc_phase where v_phasecode in ('a1001','a1002','a1005','a3007','a2011','a2003','a1006','a1007','a4002','a6003','a5002','a7002','a7003','a7004','c1001','c1002','c1004','c1005','c3007','c2011','c2003','c1006','c1007','c4002','c6003','c5002','c7002','c7003','c7004','c7005','c7007','c7008','c7009');
if (i=0) then
return; --在phasecode列表中没有就返回
end if
end;
end gc_phase_update;
before update ON gc_phase
FOR each row
declare
v_itemcode varchar2(50);
v_phasecode varchar2(50);
v_enddate varchar2(50);
v_start_date varchar2(50);
v_end_date varchar2(50);
v_basestationname varchar2(255);
v_weekid varchar2(2);
v_cityname varchar2(20);
v_endCnt number;
v_phase_type varchar2(20);
v_downpercent number(18,6);
v_downpercent_del number(18,6);
i number;
begin
downpercent_del:=:old.downpercent; --修改前还是修改后?
v_itemcode:=:new.itemcode;
v_phasecode:=:new.phaseno;
v_downpercent:=:new.downpercent;
v_enddate:=:new.realenddate; --获得修改后的部分字段的值,并存放到变量中
--如果项目阶段编码为空,说明不是基站阶段项目,返回
if(phasecode is null) then
return;
end if;--检测被修改的phasecode是否在指定的phasecode列表中
select count(*) into i from gc_phase where v_phasecode in ('a1001','a1002','a1005','a3007','a2011','a2003','a1006','a1007','a4002','a6003','a5002','a7002','a7003','a7004','c1001','c1002','c1004','c1005','c3007','c2011','c2003','c1006','c1007','c4002','c6003','c5002','c7002','c7003','c7004','c7005','c7007','c7008','c7009');
if (i=0) then
return; --在phasecode列表中没有就返回
end if;
end;
end gc_phase_update;
有的话贴出来。
16:10:34 SQL> create or replace trgger trg_name
16:10:40 2 before insert on tb
16:10:45 3
16:10:45 SQL> create or replace trigger trg_name
16:10:52 2 before insert on tb
16:10:54 3 begin
16:10:56 4 null;
16:10:57 5 end trg_name;
16:11:02 6 /触发器已创建已用时间: 00: 00: 00.22
16:11:03 SQL> show error;
没有错误。
16:11:05 SQL>
-------- -----------------------------------------------------------------
36/1 PLS-00103: 出现符号 "END"
end gc_phase_update;
-------- --------------------------------------
17/1 PLS-00201: 必须说明标识符 'DOWNPERCENT
17/1 PL/SQL: Statement ignored
25/1 PL/SQL: Statement ignored
25/4 PLS-00201: 必须说明标识符 'PHASECODE'
提示已经说得很清楚了。
2 before update ON gc_phase
3 FOR each row
4 declare
5 v_itemcode varchar2(50);
6 v_phasecode varchar2(50);
7 v_enddate varchar2(50);
8 v_start_date varchar2(50);
9 v_end_date varchar2(50);
10 v_basestationname varchar2(255);
11 v_weekid varchar2(2);
12 v_cityname varchar2(20);
13 v_endCnt number;
14 v_phase_type varchar2(20);
15 v_downpercent number(18,6);
16 v_downpercent_del number(18,6);
17 i number;
18 begin
19
20 v_downpercent_del:=:old.downpercent; --修改前还是修改后?
21 v_itemcode:=:new.itemcode;
22 v_phasecode:=:new.phaseno;
23 v_downpercent:=:new.downpercent;
24 v_enddate:=:new.realenddate; --获得修改后的部分字段的值,并存放到变量中
25
26
27 --如果项目阶段编码为空,说明不是基站阶段项目,返回
28 if(v_phasecode is null) then
29 return;
30 end if;
31
32 --检测被修改的phasecode是否在指定的phasecode列表中
33 select count(*) into i from gc_phase where v_phasecode in ('a1001','a1002','a1005','a3007','a2011','a2003','a1006','a1007','a4002','a6003','a5002','a7002','a7003','a7004','c1001','c1002','c
1004','c1005','c3007','c2011','c2003','c1006','c1007','c4002','c6003','c5002','c7002','c7003','c7004','c7005','c7007','c7008','c7009');
34
35 --在phasecode列表中没有就返回
36 if (i=0) then
37 return;
38 end if;
39
40
41 --根据阶段项目编码获得项目基站名称,如果没有基站,返回
42 select basestationname into v_basestationname from gc_item_property where itemcode=v_itemcode;
43 if(v_basestationname is null or v_basestationname='') then
44 return;
45 end if;
46
47 --根据基站名称(基站项目编码)获得基站的阶段属性(9A,9B,9C...),所属地市,该站类型(新建、扩容、O改S...)
48 select substring(gpdate,1,2),incity into v_phase_type,v_cityname from gc_item_property , gc_basestation_info where gc_item_property.basestationname=gc_basestation_info.basestationname and isba
sestation='1' and gc_item_property.itemcode=v_itemcode;
49 end gc_phase_update;
50 /警告: 创建的触发器带有编译错误。SQL> show error
TRIGGER GC_PHASE_UPDATE 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
45/1 PL/SQL: SQL Statement ignored
45/9 PL/SQL: ORA-00904: 无效列名
SQL> CREATE or replace TRIGGER gc_phase_update
2 before update ON gc_phase
3 FOR each row
4 declare
5 v_itemcode varchar2(50);
6 v_phasecode varchar2(50);
7 v_enddate varchar2(50);
8 v_start_date varchar2(50);
9 v_end_date varchar2(50);
10 v_basestationname varchar2(255);
11 v_weekid varchar2(2);
12 v_cityname varchar2(20);
13 v_endCnt number;
14 v_phase_type varchar2(20);
15 v_downpercent number(18,6);
16 v_downpercent_del number(18,6);
17 i number;
18 begin
19
20 v_downpercent_del:=:old.downpercent; --修改前还是修改后?
21 v_itemcode:=:new.itemcode;
22 v_phasecode:=:new.phaseno;
23 v_downpercent:=:new.downpercent;
24 v_enddate:=:new.realenddate; --获得修改后的部分字段的值,并存放到变量中
25
26
27 --如果项目阶段编码为空,说明不是基站阶段项目,返回
28 if(v_phasecode is null) then
29 return;
30 end if;
31
32 --检测被修改的phasecode是否在指定的phasecode列表中
33 select count(*) into i from gc_phase where v_phasecode in ('a1001','a1002','a1005','a3007','a2011','a2003','a1006','a1007','a4002','a6003','a5002','a7002','a7003','a7004','c1001','c1002','c
1004','c1005','c3007','c2011','c2003','c1006','c1007','c4002','c6003','c5002','c7002','c7003','c7004','c7005','c7007','c7008','c7009');
34
35 --在phasecode列表中没有就返回
36 if (i=0) then
37 return;
38 end if;
39
40
41 --根据阶段项目编码获得项目基站名称,如果没有基站,返回
42 select basestationname into v_basestationname from gc_item_property where itemcode=v_itemcode;
43 if(v_basestationname is null or v_basestationname='') then
44 return;
45 end if;
46
47 --根据基站名称(基站项目编码)获得基站的阶段属性(9A,9B,9C...),所属地市,该站类型(新建、扩容、O改S...)
48 select substring(gpdate,1,2),incity into v_phase_type,v_cityname from gc_item_property , gc_basestation_info where gc_item_property.basestationname=gc_basestation_info.basestationname and isba
sestation='1' and gc_item_property.itemcode=v_itemcode;
49 end gc_phase_update;
50 /警告: 创建的触发器带有编译错误。SQL> show error
TRIGGER GC_PHASE_UPDATE 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
45/1 PL/SQL: SQL Statement ignored
45/9 PL/SQL: ORA-00904: 无效列名
SQL>
这条语句有问题,
Oracle中没有substring函数,
要把他改为substr
明白吗,
不然会有无效列名的错误
if
Line: 215
Text: end GC_PHASE_UPDATE;Error: Hint: Value assigned to 'v_weekid' never used in ''
Line: 56
Text: v_end_date:=to_date('2004-02-27','yyyy-mm-dd');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 56
Text: v_end_date:=to_date('2004-02-27','yyyy-mm-dd');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 58
Text: --下面语句同上(做类似的转换)Error: Hint: Value assigned to 'v_weekid' never used in ''
Line: 62
Text: v_end_date:=to_date('2004-03-05','yyyy-mm-dd');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 62
Text: v_end_date:=to_date('2004-03-05','yyyy-mm-dd');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 64
Text: if(v_enddate between '2004-03-06' and '2004-03-12') thenError: Hint: Value assigned to 'v_weekid' never used in ''
Line: 67
Text: v_end_date:=to_date('2004-03-12','');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 67
Text: v_end_date:=to_date('2004-03-12','');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 69
Text: if(v_enddate between '2004-03-13' and '2004-03-19') thenError: Hint: Value assigned to 'v_weekid' never used in ''
Line: 72
Text: v_end_date:=to_date('2004-03-19','yyyy-mm-dd');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 72
Text: v_end_date:=to_date('2004-03-19','yyyy-mm-dd');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 74
Text: if(v_enddate between '2004-03-20' and '2004-03-26') thenError: Hint: Value assigned to 'v_weekid' never used in ''
Line: 77
Text: v_end_date:=to_date('2004-03-26','yyyy-mm-dd');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 77
Text: v_end_date:=to_date('2004-03-26','yyyy-mm-dd');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 79
Text: if(v_enddate between '2004-03-27' and '2004-04-02') thenError: Hint: Value assigned to 'v_weekid' never used in ''
Line: 82
Text: v_end_date:=to_date('2004-04-02','yyyy-mm-dd');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 82
Text: v_end_date:=to_date('2004-04-02','yyyy-mm-dd');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 84
Text: if(v_enddate between '2004-04-03' and '2004-04-09') thenError: Hint: Value assigned to 'v_weekid' never used in ''
Line: 87
Text: v_end_date:=to_date('2004-04-09','yyyy-mm-dd');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 87
Text: v_end_date:=to_date('2004-04-09','yyyy-mm-dd');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 89
Text: if(v_enddate between '2004-04-10' and '2008-01-01') thenError: Hint: Value assigned to 'v_weekid' never used in ''
Line: 92
Text: v_end_date:=to_date('2008-01-01','yyyy-mm-dd');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 92
Text: v_end_date:=to_date('2008-01-01','yyyy-mm-dd');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 97
Text: else if (v_phase_type='9B') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 104
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 105
Text: if(v_enddate between '2004-03-22' and '2004-04-11') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 109
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 110
Text: if(v_enddate between '2004-04-12' and '2004-05-02') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 114
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 115
Text: if(v_enddate between '2004-05-03' and '2004-05-23') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 119
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 120
Text: if(v_enddate between '2004-05-24' and '2004-06-13') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 124
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 125
Text: if(v_enddate between '2004-06-14' and '2004-07-01') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 129
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 130
Text: if(v_enddate between '2004-07-02' and '2008-01-01') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 134
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 136
Text: else if(v_phase_type='9C') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 142
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 143
Text: if(v_enddate between '2004-07-25' and '2004-08-14') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 147
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 148
Text: if(v_enddate between '2004-08-15' and '2004-09-04') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 152
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 153
Text: if(v_enddate between '2004-09-05' and '2004-09-25') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 157
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 158
Text: if(v_enddate between '2004-09-26' and '2004-10-16') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 162
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 163
Text: if(v_enddate between '2004-10-17' and '2004-11-06') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 167
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 168
Text: if(v_enddate between '2004-11-07' and '2008-01-01') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 172
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 177Error: Hint: Comparison with NULL in ''
Line: 47
Text: --根据基站名称(基站项目编码)获得基站的阶段属性(9A,9B,9C...),所属地市,该站类型(新建、扩容、O改S...)
if
Line: 215
Text: end GC_PHASE_UPDATE;
其它的都是警告,可以暂时不用管它
你看一下把上面指定的那一部分代码贴出来看看(215行左右)
或者你自己仔细检查一下。
SQL> create or replace trigger gc_phase_update
2 before update ON gc_phase
3 FOR each row
4 declare
5 v_itemcode varchar2(50);
6 v_phasecode varchar2(50);
7 v_enddate varchar2(50);
8 v_start_date varchar2(50);
9 v_end_date varchar2(50);
10 v_basestationname varchar2(255);
11 v_weekid varchar2(2);
12 v_cityname varchar2(20);
13 v_endCnt number;
14 v_phase_type varchar2(20);
15 v_downpercent number(18,6);
16 v_downpercent_del number(18,6);
17 i number;
18 begin
19
20 v_downpercent_del:=:old.downpercent; --修改前还是修改后?
21 v_itemcode:=:new.itemcode;
22 v_phasecode:=:new.phaseno;
23 v_downpercent:=:new.downpercent;
24 v_enddate:=:new.realenddate; --获得修改后的部分字段的值,并存放到变量中
25
26
27 --如果项目阶段编码为空,说明不是基站阶段项目,返回
28 if(v_phasecode is null) then
29 return;
30 end if;
31
32 --检测被修改的phasecode是否在指定的phasecode列表中
33 select count(*) into i from gc_phase where v_phasecode in ('a1001','
1004','c1005','c3007','c2011','c2003','c1006','c1007','c4002','c6003','c5002','
34
35 --在phasecode列表中没有就返回
36 if (i=0) then
37 return;
38 end if;
39
40
41 --根据阶段项目编码获得项目基站名称,如果没有基站,返回
42 select basestationname into v_basestationname from gc_item_property where
43 if(v_basestationname is null or v_basestationname='') then
44 return;
45 end if;
46
47 --根据基站名称(基站项目编码)获得基站的阶段属性(9A,9B,9C...),所属地市,该站
48 select substr(gpdate,1,2),incity into v_phase_type,v_cityname from gc_it
tation='1' and gc_item_property.itemcode=v_itemcode;
49
50 --根据阶段结束日期判断阶段在哪一周完成
51 if(v_phase_type='9A') then
52 begin
53 if(v_enddate between '2003-02-27' and '2004-02-27') then
54 v_weekid:='1';
55 v_start_date:=to_date('2003-02-27','yyyy-mm-dd');
56 v_end_date:=to_date('2004-02-27','yyyy-mm-dd');
57 end if;
58 --下面语句同上(做类似的转换)
59 if(v_enddate between '2004-02-28' and '2004-03-05') then
60 v_weekid:='2';
61 v_start_date:=to_date('2004-02-28','yyyy-mm-dd');
62 v_end_date:=to_date('2004-03-05','yyyy-mm-dd');
63 end if;
64 if(v_enddate between '2004-03-06' and '2004-03-12') then
65 v_weekid:='3';
66 v_start_date:=to_date('2004-03-06','yyyy-mm-dd');
67 v_end_date:=to_date('2004-03-12','');
68 end if;
69 if(v_enddate between '2004-03-13' and '2004-03-19') then
70 v_weekid:='4';
71 v_start_date:=to_date('2004-03-13','yyyy-mm-dd');
72 v_end_date:=to_date('2004-03-19','yyyy-mm-dd');
73 end if;
74 if(v_enddate between '2004-03-20' and '2004-03-26') then
75 v_weekid:='5';
76 v_start_date:=to_date('2004-03-20','yyyy-mm-dd');
77 v_end_date:=to_date('2004-03-26','yyyy-mm-dd');
78 end if;
79 if(v_enddate between '2004-03-27' and '2004-04-02') then
80 v_weekid:='6';
81 v_start_date:=to_date('2004-03-27','yyyy-mm-dd');
82 v_end_date:=to_date('2004-04-02','yyyy-mm-dd');
83 end if;
84 if(v_enddate between '2004-04-03' and '2004-04-09') then
85 v_weekid:='7';
86 v_start_date:=to_date('2004-04-03','yyyy-mm-dd');
87 v_end_date:=to_date('2004-04-09','yyyy-mm-dd');
88 end if;
89 if(v_enddate between '2004-04-10' and '2008-01-01') then
90 v_weekid:='8';
91 v_start_date:=to_date('2004-04-10','yyyy-mm-dd');
92 v_end_date:=to_date('2008-01-01','yyyy-mm-dd');
93 end if;
94
95 end;
96
97 else if (v_phase_type='9B') then
98 begin
99 --下面语句同上(做类似的转换)
100 if(v_enddate between '2003-01-01' and '2004-03-21') then
101 v_weekid:='1';
102 v_start_date:='2003-01-01';
103 v_end_date:='2004-03-21';
104 end if;
105 if(v_enddate between '2004-03-22' and '2004-04-11') then
106 v_weekid:='2';
107 v_start_date:='2004-03-22';
108 v_end_date:='2004-04-11';
109 end if;
110 if(v_enddate between '2004-04-12' and '2004-05-02') then
111 v_weekid:='3';
112 v_start_date:='2004-04-12';
113 v_end_date:='2004-05-02';
114 end if;
115 if(v_enddate between '2004-05-03' and '2004-05-23') then
116 v_weekid:='4';
117 v_start_date:='2004-05-03';
118 v_end_date:='2004-05-23';
119 end if;
120 if(v_enddate between '2004-05-24' and '2004-06-13') then
121 v_weekid:='5';
122 v_start_date:='2004-05-24';
123 v_end_date:='2004-06-13';
124 end if;
125 if(v_enddate between '2004-06-14' and '2004-07-01') then
126 v_weekid:='6';
127 v_start_date:='2004-06-14';
128 v_end_date:='2004-07-01';
129 end if;
130 if(v_enddate between '2004-07-02' and '2008-01-01') then
131 v_weekid:='7';
132 v_start_date:='2004-07-02';
133 v_end_date:='2008-01-01';
134 end if;
135 end;
136 else if(v_phase_type='9C') then
137 begin --下面语句同上(做类似的转换)
138 if(v_enddate between '2004-01-01' and '2004-07-24') then
139 v_weekid:='1';
140 v_start_date:='2004-01-01';
141 v_end_date:='2004-07-24';
142 end if;
143 if(v_enddate between '2004-07-25' and '2004-08-14') then
144 v_weekid:='2';
145 v_start_date:='2004-07-25';
146 v_end_date:='2004-08-14';
147 end if;
148 if(v_enddate between '2004-08-15' and '2004-09-04') then
149 v_weekid:='3';
150 v_start_date:='2004-08-15';
151 v_end_date:='2004-09-04';
152 end if;
153 if(v_enddate between '2004-09-05' and '2004-09-25') then
154 v_weekid:='4';
155 v_start_date:='2004-09-05';
156 v_end_date:='2004-09-25';
157 end if;
158 if(v_enddate between '2004-09-26' and '2004-10-16') then
159 v_weekid:='5';
160 v_start_date:='2004-09-26';
161 v_end_date:='2004-10-16';
162 end if;
163 if(v_enddate between '2004-10-17' and '2004-11-06') then
164 v_weekid:='6';
165 v_start_date:='2004-10-17';
166 v_end_date:='2004-11-06';
167 end if;
168 if(v_enddate between '2004-11-07' and '2008-01-01') then
169 v_weekid:='7';
170 v_start_date:='2004-11-07';
171 v_end_date:='2008-01-01';
172 end if;
173 end;
174 else
175 return;
176 end if;
177
178 --求该周、该站类型、该阶段、该九期阶段(9A、9B、9C...)
179 --select * from gc_phase where (realenddate between v_start_date a
180
181 --如果原来没有记录,插入并设置为1
182 select count(endCnt) into v_endCnt from gc_report_bsc_progress where (
183
184 if(v_weekid is null) then
185 return;
186 end if;
187
188 if(v_endCnt = 0) or (v_downpercent=100) then
189 insert into gc_report_bsc_progress(cityname,weekname,enddate,progresscod
190 end if;
191
192 if(v_endCnt<>0) then
193 --在此处需要得到原有进度还更新后的进度
194 --select v_downpercent_del=downpercent from deleted
195 --select v_downpercent=downpercent from inserted
196
197 if(v_downpercent=100 and v_downpercent_del<100) then
198 --如果是删除,原有进度=100是,减去1,原有进度 <100,不变
199 update gc_report_bsc_progress set endCnt=endCnt+1
200 where phase_type=v_phase_type and weekname=v_weekid and cityname=v_c
201 progresscode=v_phasecode;
202 end if;
203
204 if(v_downpercent<100 and v_downpercent_del=100) then
--如果是修改,原有进度小于100,现有进度=100,加1,
206 update gc_report_bsc_progress set endCnt=endCnt-1
207 where phase_type=v_phase_type and weekname=v_weekid and cityname=v_
208 end if;
209
210 if(v_downpercent=100 and v_downpercent_del=100) then
211 --原有进度=100,现有小于100,减去1,如果原有进度=100,现有=100,不变
212 return;
213 end if;
214 end if;
215 end GC_PHASE_UPDATE;
216 /
表中的:new.realenddate这个字段是时间型还是文本型,
是date型的话
把变量:
v_enddate date; --varchar2(50);---改为日期型
v_start_date date;---varchar2(50);
v_end_date date;---varchar2(50);
每一个这样的语句中:
if(v_enddate between '2003-02-27' and '2004-02-27') then
v_weekid:='1';
v_start_date:='2003-02-27'
v_end_date:='2004-02-27'
end if;
改为这样的:
if(to_char(v_enddate,'yyyy-mm-dd') between '2003-02-27' and '2004-02-27') then
v_weekid:='1';
v_start_date:=to_date('2003-02-27','yyyy-mm-dd');
v_end_date:=to_date('2004-02-27','yyyy-mm-dd');
end if;
对v_enddate改为to_char(v_enddate,'yyyy-mm-dd')的目的是保证if(to_char(v_enddate,'yyyy-mm-dd') between '2003-02-27' and '2004-02-27') then不会出错误,
else if
改成 elseif
即去掉中间的空格。
要保证他的值的格式是'yyyy-mm-dd'因你在:
v_enddate:=:new.realenddate; --获得修改后的部分字段的值,并存放到变量中
对v_enddate := :new.realenddate;了
else if
改成 elseif
即去掉中间的空格。
修改97、136行
else if
改成 elseif
即去掉中间的空格。
是要改为:elsif不是elseif
Compilation errors for TRIGGER PROJECT.GC_PHASE_UPDATEError: PLS-00103: 出现符号 "GC_PHASE_UPDATE"在需要下列之一时:
if
Line: 215
Text: end GC_PHASE_UPDATE;至于变量就先放一下,上面这个问题实在是不知道出在哪里!!!!
else if
改成 elsifOracle中,对于if 语句格式是这样的if 条件 thenelseend if;或是:
if 条件 thenelsif 条件 thenend if;
还不行吗,
Error: Hint: Value assigned to 'v_weekid' never used in ''
Line: 56
Text: v_end_date:=to_date('2004-02-27','yyyy-mm-dd');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 56
Text: v_end_date:=to_date('2004-02-27','yyyy-mm-dd');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 58
Text: --下面语句同上(做类似的转换)Error: Hint: Value assigned to 'v_weekid' never used in ''
Line: 62
Text: v_end_date:=to_date('2004-03-05','yyyy-mm-dd');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 62
Text: v_end_date:=to_date('2004-03-05','yyyy-mm-dd');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 64
Text: if(v_enddate between '2004-03-06' and '2004-03-12') thenError: Hint: Value assigned to 'v_weekid' never used in ''
Line: 67
Text: v_end_date:=to_date('2004-03-12','');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 67
Text: v_end_date:=to_date('2004-03-12','');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 69
Text: if(v_enddate between '2004-03-13' and '2004-03-19') thenError: Hint: Value assigned to 'v_weekid' never used in ''
Line: 72
Text: v_end_date:=to_date('2004-03-19','yyyy-mm-dd');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 72
Text: v_end_date:=to_date('2004-03-19','yyyy-mm-dd');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 74
Text: if(v_enddate between '2004-03-20' and '2004-03-26') thenError: Hint: Value assigned to 'v_weekid' never used in ''
Line: 77
Text: v_end_date:=to_date('2004-03-26','yyyy-mm-dd');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 77
Text: v_end_date:=to_date('2004-03-26','yyyy-mm-dd');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 79
Text: if(v_enddate between '2004-03-27' and '2004-04-02') thenError: Hint: Value assigned to 'v_weekid' never used in ''
Line: 82
Text: v_end_date:=to_date('2004-04-02','yyyy-mm-dd');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 82
Text: v_end_date:=to_date('2004-04-02','yyyy-mm-dd');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 84
Text: if(v_enddate between '2004-04-03' and '2004-04-09') thenError: Hint: Value assigned to 'v_weekid' never used in ''
Line: 87
Text: v_end_date:=to_date('2004-04-09','yyyy-mm-dd');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 87
Text: v_end_date:=to_date('2004-04-09','yyyy-mm-dd');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 89
Text: if(v_enddate between '2004-04-10' and '2008-01-01') thenError: Hint: Value assigned to 'v_weekid' never used in ''
Line: 92
Text: v_end_date:=to_date('2008-01-01','yyyy-mm-dd');Error: Hint: Value assigned to 'v_start_date' never used in ''
Line: 92
Text: v_end_date:=to_date('2008-01-01','yyyy-mm-dd');Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 97
Text: else if (v_phase_type='9B') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 104
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 105
Text: if(v_enddate between '2004-03-22' and '2004-04-11') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 109
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 110
Text: if(v_enddate between '2004-04-12' and '2004-05-02') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 114
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 115
Text: if(v_enddate between '2004-05-03' and '2004-05-23') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 119
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 120
Text: if(v_enddate between '2004-05-24' and '2004-06-13') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 124
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 125
Text: if(v_enddate between '2004-06-14' and '2004-07-01') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 129
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 130
Text: if(v_enddate between '2004-07-02' and '2008-01-01') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 134
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 136
Text: else if(v_phase_type='9C') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 142
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 143
Text: if(v_enddate between '2004-07-25' and '2004-08-14') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 147
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 148
Text: if(v_enddate between '2004-08-15' and '2004-09-04') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 152
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 153
Text: if(v_enddate between '2004-09-05' and '2004-09-25') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 157
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 158
Text: if(v_enddate between '2004-09-26' and '2004-10-16') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 162
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 163
Text: if(v_enddate between '2004-10-17' and '2004-11-06') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 167
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 168
Text: if(v_enddate between '2004-11-07' and '2008-01-01') thenError: Hint: Value assigned to 'v_start_date' never used in ''
Line: 172
Text: end if;Error: Hint: Value assigned to 'v_end_date' never used in ''
Line: 177Error: Hint: Comparison with NULL in ''
Line: 47
Text: --根据基站名称(基站项目编码)获得基站的阶段属性(9A,9B,9C...),所属地市,该站类型(新建、扩容、O改S...)上面的问题我不改触发器也能创建成功,但是没有达到SQL中的效果!!!
Error
---------------------------
ORA-04091: 表 PROJECT.GC_PHASE 发生了变化,触发器/函数不能读
ORA-06512: 在"PROJECT.GC_PHASE_UPDATE", line 30
ORA-04088: 触发器 'PROJECT.GC_PHASE_UPDATE' 执行过程中出错
View program sources of error stack?
---------------------------
是(Y) 否(N)
---------------------------我点击是按钮,高亮显示的sql语句是触发器中的:
select count(*) into i from gc_phase where v_phasecode in ('a1001','a1002','a1005','a3007','a2011','a2003','a1006','a1007','a4002','a6003','a5002','a7002','a7003','a7004','c1001','c1002','c1004','c1005','c3007','c2011','c2003','c1006','c1007','c4002','c6003','c5002','c7002','c7003','c7004','c7005','c7007','c7008','c7009');请问是什么问题?
你赋了值给这个变量之后,又没有再次使用它们.这不是错误.如:Error: Hint: Value assigned to 'v_weekid' never used in ''
Line: 56
Text: v_end_date:=to_date('2004-02-27','yyyy-mm-dd');
这个信息中,
是说变量v_weekid被赋了值之后,就没有再使用它了,提示是在56行,相关的文本是v_end_date:=to_date('2004-02-27','yyyy-mm-dd');这个.如果说没有达到sql中的效果,这应该是流程上可能有点问题,你最好详细的核对一下,sql中的触发器