EXECUTE IMMEDIATE中动态调用函数怎么写才可以 求大神,如果把函数拼装在字符串中,执行有问题
PROCEDURE resource_geometry_in_grid(p_table_name IN VARCHAR2)
IS
V_SQL VARCHAR2(2000);
BEGIN
V_SQL := 'TRUNCATE TABLE GS_GRID_RESOURCE_GEOM';
EXECUTE IMMEDIATE V_SQL;
V_SQL := 'INSERT INTO GS_GRID_RESOURCE_GEOM
(GRID_ID,GWM_FNO,GWM_CNO,NAME,CODE,GWM_USERNAME,STATUS,GWM_FID)
SELECT GP.GWM_FID,
GI.GWM_FNO,
GI.GWM_CNO,
PKG_GRID_RESOUCE.GET_GS_RESOURCE_INFO(FL.GWM_FNO, FL.GWM_FID, 1),
PKG_GRID_RESOUCE.GET_GS_RESOURCE_INFO(FL.GWM_FNO, FL.GWM_FID, 2),
DECODE(FL.GWM_FNO,4200,
(SELECT T.CATEGORY
FROM GS_ONU_INFO T
WHERE T.GWM_FID = FL.GWM_FID),
GF.GWM_USERNAME),
''落对'',
FL.GWM_FID
FROM :1 FL,
GS_GRID_P GP,
GS_GRID_INFO GI,
GWM_FEATURE GF
WHERE GP.GWM_FID = GI.GWM_FID
AND GF.GWM_FNO = FL.GWM_FNO
AND SDO_RELATE(GP.GWM_GEOMETRY, FL.GWM_GEOMETRY, ''MASK=ANYINTERACT'') =
''TRUE''
AND GI.GRID_TYPE_ID IN (82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92)';
EXECUTE IMMEDIATE V_SQL USING IN p_table_name; END resource_geometry_in_grid;
PROCEDURE resource_geometry_in_grid(p_table_name IN VARCHAR2)
IS
V_SQL VARCHAR2(2000);
BEGIN
V_SQL := 'TRUNCATE TABLE GS_GRID_RESOURCE_GEOM';
EXECUTE IMMEDIATE V_SQL;
V_SQL := 'INSERT INTO GS_GRID_RESOURCE_GEOM
(GRID_ID,GWM_FNO,GWM_CNO,NAME,CODE,GWM_USERNAME,STATUS,GWM_FID)
SELECT GP.GWM_FID,
GI.GWM_FNO,
GI.GWM_CNO,
PKG_GRID_RESOUCE.GET_GS_RESOURCE_INFO(FL.GWM_FNO, FL.GWM_FID, 1),
PKG_GRID_RESOUCE.GET_GS_RESOURCE_INFO(FL.GWM_FNO, FL.GWM_FID, 2),
DECODE(FL.GWM_FNO,4200,
(SELECT T.CATEGORY
FROM GS_ONU_INFO T
WHERE T.GWM_FID = FL.GWM_FID),
GF.GWM_USERNAME),
''落对'',
FL.GWM_FID
FROM :1 FL,
GS_GRID_P GP,
GS_GRID_INFO GI,
GWM_FEATURE GF
WHERE GP.GWM_FID = GI.GWM_FID
AND GF.GWM_FNO = FL.GWM_FNO
AND SDO_RELATE(GP.GWM_GEOMETRY, FL.GWM_GEOMETRY, ''MASK=ANYINTERACT'') =
''TRUE''
AND GI.GRID_TYPE_ID IN (82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92)';
EXECUTE IMMEDIATE V_SQL USING IN p_table_name; END resource_geometry_in_grid;
应该是可以的。
不如把p_table_name直接替换成:1
调试到EXECUTE IMMEDIATE V_SQL;这一步的时候,发现就自动跳出来了,然后再点下一步,点7,8此之后就到PKG_GRID_RESOUCE.GET_GS_RESOURCE_INFO这个函数里面无限循环了
调试到EXECUTE IMMEDIATE V_SQL;这一步的时候,发现就自动跳出来了,然后再点下一步,点7,8此之后就到PKG_GRID_RESOUCE.GET_GS_RESOURCE_INFO这个函数里面无限循环了如果改成这样的话,你可以 调试 的过程中 拿到 V_SQL 的内容,放到另外session执行下看什么情况
调试到EXECUTE IMMEDIATE V_SQL;这一步的时候,发现就自动跳出来了,然后再点下一步,点7,8此之后就到PKG_GRID_RESOUCE.GET_GS_RESOURCE_INFO这个函数里面无限循环了如果改成这样的话,你可以 调试 的过程中 拿到 V_SQL 的内容,放到另外session执行下看什么情况
如果单独把V_SQL中的内容拿出来运行,运行是OK的
调试到EXECUTE IMMEDIATE V_SQL;这一步的时候,发现就自动跳出来了,然后再点下一步,点7,8此之后就到PKG_GRID_RESOUCE.GET_GS_RESOURCE_INFO这个函数里面无限循环了如果改成这样的话,你可以 调试 的过程中 拿到 V_SQL 的内容,放到另外session执行下看什么情况
如果单独把V_SQL中的内容拿出来运行,运行是OK的那不应该有问题啊。。难道 FROM :1中间没空格? 看着也不像啊
select a+? from table where a=? and b=?
insert into table values(?,?,?)
update table set a=?,b=?
绑定变量只能用在值的位置,在结构的位置老老实实拼接字符串。