create or replace procedure insertdatY1(year varchar2) as
type c is ref cursor;
v_c c;
v_d iptpa_poll_host%rowtype;
begin
execute immediate 'alter session set nls_date_format=' || '''' ||
'yyyy-mm-dd hh24:mi:ss' || '''';
--闰年
if mod(year, 4) = 0 then
open v_c for
SELECT *
FROM IPTPA_POLL_HOST C
WHERE C.ORG_TIME >=
TO_DATE('2016-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND C.ORG_TIME <=
TO_DATE('2016-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss');
/*while (true)*/
while (true) loop
fetch v_c bulk collect
into v_d /*limit 100*/; --此处报错
forall i in 1 .. v_d.count
insert into IPTPA_POLL_HOST values v_d (i);
end loop;
cannot mix between single row and multi-row (BULK) in INTO list
Cause: - When BULK syntax (e.g. BULK COLLECT INTO) is used to retrieve data, every variable in the INTO list has to be of type that is a collection of the type of the corresponding column.
- When BULK is NOT used, every variable in the INTO list has to be of compatible type with the corresponding column.
Action: Change the INTO list so that all variables have correct data types需明确定义列
如:type col1 is table of iptpa_poll_host.col1%type;
v_d col1 ;
fetch v_c bulk collect into v_d
此时,取了一个列的集合