Question

oracle中SQL动态传参数问题

cursor param_value IS --获取要处理的CMID对应的SC表游标
select SLOGID,
FLOGID,
ROW_NUMBER_IN_FLOG,
TIME,
LONGITUDE,
LATITUDE,
MCC,
MNC,
SID,
NID,
BID,
BANDCLASS,
FREQ,
REFPN,
REG_ZONE,
HOME_SID,
PRAT,
BASE_CLASS,
BASE_LAT,
BASE_LONT,
PAGE_CHAN,
HOME_REG,
POWERUP_REG,
POWERDOWN_REG,
PREV,
SECTOR_ID,
SUBNETMASK,
COLORCODE,
UATI,
EV_SECTOR_USER_SERVED,
EV_REVISION,
CMDID
from CDMA_SC_SOURCE_DATA

open rc;
loop
fetch rc
into v_column_name, v_table_name;
EXIT WHEN rc%NOTFOUND;
FOR param_v in param_value loop
p_value := 'param_v.' || v_column_name || '';
sql_str := ' insert into ' || v_table_name || '
(cmdid,
slogid,
flogid,
row_number_in_flog,
time,
longitude,
latitude,
' || v_column_name || ',
)
VALUES
(' || PARAM_V.CMDID || ',
' || param_v.SLOGID || ',
' || param_v.FLOGID || ',
' || param_v.ROW_NUMBER_IN_FLOG || ',
' || param_v.TIME || ',
' || param_v.LONGITUDE || ',
' || param_v.LATITUDE || ',
param_v.' || v_column_name || '
)';

execute immediate sql_str
using v_column_name;

end loop;
end loop;
commit;
close rc;

最后一个字段，需要再次动态传入目前执行的 sql_str 调试语句为：
insert into CDMA_SC_MCC_SOURCE
(cmdid,
slogid,
flogid,
row_number_in_flog,
time,
longitude,
latitude,
MCC,
)
VALUES
(2610248,
6,
23,
1213,
08-2月 -12 09.10.37.640000 上午,
119.306,
26.07717,
param_v.MCC
)
，最后一个值没有取到实际的值，哪位大侠能帮忙改下？

Answer 1

open rc;
cursor param_value IS
名称不一致呢，下面的游标名称为rc,一开始定义的游标名称是param_value

fetch rc
into v_column_name, v_table_name;

这样导致 v_column_name未能取到值，
所以sql_str 中的v_column_name 的值不能取到