我觉得一句话就可以了吧UPDATE MGS_PSD_REPORT T SET T.F2 = DECODE(TO_CHAR(T.F1, 'D'), 1, 'N', 7, 'N', CASE WHEN (TO_CHAR(T.F1, 'MM-DD') BETWEEN '05-01' AND '05-03') OR (TO_CHAR(T.F1, 'MM-DD') BETWEEN '10-01' AND '10-03') THEN 'N2' ELSE 'P' END) WHERE T.F1 IS NOT NULL;
重新存下,要么存date型,要么存储成2014-6-23这种。中文的oracle自动解析不了。 insert into mgs_psd_report(F1) select to_char(trunc(sysdate,'yyyy')+rownum-1,'yyyy-mm-dd') from dual connect by rownum<=366function: 结合3楼“一生一事”给出的直接用条件判断更新。 create or replace procedure p_update_psd_report(p_flag out varchar2) is begin UPDATE MGS_PSD_REPORT T SET T.F2 = DECODE(TO_CHAR(TO_DATE(T.F1,'YYYY-MM-DD'), 'D'), 1, 'N', 7, 'N', CASE WHEN (TO_CHAR(TO_DATE(T.F1,'YYYY-MM-DD'), 'MM-DD') BETWEEN '05-01' AND '05-03') OR (TO_CHAR(TO_DATE(T.F1,'YYYY-MM-DD'), 'MM-DD') BETWEEN '10-01' AND '10-03') THEN 'N2' ELSE 'P' END) WHERE T.F1 IS NOT NULL; commit; p_flag := 'OK'; exception when others then rollback; p_flag := 'ERROR'; end p_update_psd_report;
/*那如果我要写一个過程,更新mgs_psd_report的f2欄位(星期六,星期天為N,5月1號到3號,10月1號到3號為N2,其他日期為P)*/
create or replace procedure p_update_psd_report(p_flag out varchar2) isbegin
update mgs_psd_report set f2 = 'P';
update mgs_psd_report
set f2 = 'N'
where to_char(f1, 'd') = '1'
or to_char(f1, 'd') = '7';
update mgs_psd_report
set f2 = 'N2'
where (trunc(f1) >= to_date(to_char(f1, 'yyyy') || '-5-1', 'yyyy-mm-dd') and
trunc(f1) <= to_date(to_char(f1, 'yyyy') || '-5-3', 'yyyy-mm-dd'))
or (trunc(f1) >=
to_date(to_char(f1, 'yyyy') || '-10-1', 'yyyy-mm-dd') and
trunc(f1) <=
to_date(to_char(f1, 'yyyy') || '-10-3', 'yyyy-mm-dd'));
commit;
p_flag := 'OK';
exception
when others then
rollback;
p_flag := 'ERROR';
end p_update_psd_report;
(2):PL/SQL: SQL Statement ignored
SET T.F2 = DECODE(TO_CHAR(T.F1, 'D'),
1,
'N',
7,
'N',
CASE
WHEN (TO_CHAR(T.F1, 'MM-DD') BETWEEN '05-01' AND '05-03') OR
(TO_CHAR(T.F1, 'MM-DD') BETWEEN '10-01' AND '10-03') THEN
'N2'
ELSE
'P'
END)
WHERE T.F1 IS NOT NULL;
insert into mgs_psd_report(F1) select to_char(trunc(sysdate,'yyyy')+rownum-1,'yyyy-mm-dd') from dual connect by rownum<=366function:
结合3楼“一生一事”给出的直接用条件判断更新。
create or replace procedure p_update_psd_report(p_flag out varchar2) is
begin
UPDATE MGS_PSD_REPORT T
SET T.F2 = DECODE(TO_CHAR(TO_DATE(T.F1,'YYYY-MM-DD'), 'D'),
1,
'N',
7,
'N',
CASE
WHEN (TO_CHAR(TO_DATE(T.F1,'YYYY-MM-DD'), 'MM-DD') BETWEEN '05-01' AND '05-03') OR
(TO_CHAR(TO_DATE(T.F1,'YYYY-MM-DD'), 'MM-DD') BETWEEN '10-01' AND '10-03') THEN
'N2'
ELSE
'P'
END)
WHERE T.F1 IS NOT NULL;
commit;
p_flag := 'OK';
exception
when others then
rollback;
p_flag := 'ERROR';
end p_update_psd_report;