首页 新闻 搜索 专区 学院

orcale存储过程问题 --大家能帮我看看我这个存储过程那写的不对么?

0
悬赏园豆:100 [待解决问题]

create or replace procedure UpdateTempInventoryM()
as
countsql varchar(400);
blcount NUMBER(10);

CURSOR s_areaTree IS SELECT b.core_area_tree_id from core_area_tree b;

BEGIN
 for cur in s_areaTree.core_area_tree_id loop
 
  countsql:='select count(*) from core_building where street='||cur||'';
 
  EXECUTE IMMEDIATE countsql into blcount;--使用动态SQL语句来执行
      begin
         insert into based_data_statistics (CORE_AREA_TREE_ID, CORE_FLOATING_COUNT, CORE_BUILDING_COUNT, CORE_INHABITANT_COUNT, CORE_HOUSEHOLD_COUNT, VERSION, CREAT_TIME) values (cur, blcount1, 1, 1, 1, 1, null);
       COMMIT;
      end;
 end loop;
 END UpdateTempInventoryM;

地瓜@.@的主页 地瓜@.@ | 初学一级 | 园豆:6
提问于:2012-09-09 08:21
< >
分享
所有回答(2)
0

下面的blcount1命名有误,应该是blcount吧。

秦楼东 | 园豆:913 (小虾三级) | 2012-09-09 11:24
1

貌似里面有很多问题,没数据库不验证了,用一条SQL就能搞定:

insert into based_data_statistics (CORE_AREA_TREE_ID, CORE_FLOATING_COUNT, CORE_BUILDING_COUNT, CORE_INHABITANT_COUNT, CORE_HOUSEHOLD_COUNT, VERSION, CREAT_TIME)
select t1.street,count(t1.street),1,1,1,1,null from core_building t1 join  core_area_tree  t2 on t1.street=t2.core_area_tree_id  group by t1.street
向往-SONG | 园豆:4853 (老鸟四级) | 2012-09-09 12:35
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册