其实就是取当年的月份,以201301-201304 的格式,循环插入t_test表中, t_test中有一个字段timeny
CREATE OR REPLACE PROCEDURE test_ForlnLoop is
w_timeny varchar(100);
cursor test_cursor is SELECT TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'YYYY'), ROWNUM - 1),
'YYYYMM'
) TIMENY
FROM DUAL
CONNECT BY ROWNUM <= TO_NUMBER (TO_CHAR (SYSDATE, 'MM')) ORDER BY TIMENY DESC;
begin
for w_timeny in test_cursor loop
insert into t_test (timeny) VALUES (w_timeny); end loop;
commit;
end;执行的结果报错,报表达式类型错误,w_timeny
问下大家,应该怎么修改下,希望大家能帮下我
CREATE OR REPLACE PROCEDURE test_ForlnLoop is
w_timeny varchar(100);
cursor test_cursor is SELECT TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'YYYY'), ROWNUM - 1),
'YYYYMM'
) TIMENY
FROM DUAL
CONNECT BY ROWNUM <= TO_NUMBER (TO_CHAR (SYSDATE, 'MM')) ORDER BY TIMENY DESC;
begin
for w_timeny in test_cursor loop
insert into t_test (timeny) VALUES (w_timeny); end loop;
commit;
end;执行的结果报错,报表达式类型错误,w_timeny
问下大家,应该怎么修改下,希望大家能帮下我
--w_timeny varchar(100); 这个不要声明
cursor test_cursor is
SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), ROWNUM - 1), 'YYYYMM') TIMENY
FROM DUAL
CONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(SYSDATE, 'MM'))
ORDER BY TIMENY DESC;
begin
for w_timeny in test_cursor loop
insert into t_test (timeny) VALUES (w_timeny.timeny); --使用 row.value 取值
end loop;
commit;
end;
w_timeny varchar(100);
cursor test_cursor is SELECT TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'YYYY'), ROWNUM - 1),
'YYYYMM'
) TIMENY
FROM DUAL
CONNECT BY ROWNUM <= TO_NUMBER (TO_CHAR (SYSDATE, 'MM')) ORDER BY TIMENY DESC;
begin
-- for w_timeny in test_cursor
open test_cursor;
loop
fetch test_cursor into w_timeny ;
exit when test_cursor%notfound;
insert into t_test (timeny) VALUES (w_timeny); end loop;
-- commit;
end;