Question

高分求SQL达人帮忙解释SQL块代码的问题，块代码如下

declare o_order_number varchar2(50);
　　mark_create_id number(10);
　　cursor acnt_first_cur is
　　select
　　t1.order_number,t1.id
　　from mark_create_bak_20110630 t1, mark_create t2
　　where t1.id=t2.id and t1.sp_id<>t2.sp_id
and t1. addition_number is not null and t1.is_used=0;
　　begin
　　open acnt_first_cur;
　　loop
　　exit when acnt_first_cur%notfound;
　　fetch acnt_first_cur bulk collect into o_order_number, mark_create_id
　　forall i in 1 .. mark_create_id.count
　　update mark_create
　　set order_number = o_order_number(i)
　　where id = mark_create_id(i);
　　commit;
　　end loop;
　　end;

上面的语句在PLSQL中老是报错，报错如下：

ORA-06550: 第 3 行, 第 2 列:
PLS-00103: 出现符号 ""在需要下列之一时：
begin function pragma
procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
求高人帮忙，谢谢，在线等！

Answer 1

用BULK INTO是不能是赋值到普通变量类型的
给你一段我自己的代码参考

create or replace procedure pr_import_contract_store is
TYPE STORE_ARRAY IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

STORE_LIST STORE_ARRAY;
error_msg varchar2(100);
i number := 0;
cursor get_store is
select distinct a.contract_code, a.sub_type, a.scope_unit
from da_convert_contract a;
begin
for read_store in get_store loop
select * bulk collect
into store_list
from table(split_str(read_store.scope_unit, ','));

forall i in 1 .. store_list.count
insert into da_contract_store
(CONTRACT_CODE, SUB_TYPE, SCOPE_UNIT)
values
(read_store.contract_code, read_store.sub_type, store_list(i));

end loop;

exception
when others then
error_msg := sqlerrm;
dbms_output.put_line(error_msg);
end pr_import_contract_store;

Answer 2

定义变量、定义游标，打开游标，循环、循环中复核条件的更新。
好像意思是游标释放有问题。