SQL游标问题 [英] SQL cursor issue

问题描述

我正在尝试使用游标和select语句创建一个新表.

I am trying to create a new table using a cursor and a select statements.

 Declare
     Cursor c1 is 
     select store_number from table1  where CLIENT_ID= 1 and ORG_ID = 1 ;

     Type C1_TAB_TYPE is table of c1%ROWTYPE;      
     c1_list c1_TAB_TYPE;

       Begin     
           For r1 in c1 

           Loop
            select  store_number
               BULK COLLECT INTO c1_list from

                (
                 SELECT store_number,
                ( (SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_INTERSECTION(a.geometry,b.geometry, 0.005), 0.005, 'unit=sq_mile') /SDO_GEOM.SDO_AREA((a.geometry), 0.005, 'unit=sq_mile'))*100) Total_area
                 FROM table2 a, table1 b
                 Where store_number != r1.store_number
                 AND sdo_relate(a.geometry, b.geometry, 'mask=anyinteract') = 'TRUE');

                   For i in 1..c1_list.count loop
                                 insert into temp_Prop_area
                 (STORE_NUMBER,TOTAL_AREA)
                  values (r1.store_number,Total_Area);

                   End Loop;
             End Loop;

 End;

这里我使用商店编号传递到游标中.现在有了商店编号后，我检查是否要获取每个商店的多边形与表1所覆盖的商店的小多边形之间的重叠区域.该区域称为Total_area.我正在尝试将此商店编号和总面积传递到temp_Prop_area表中.

Here I am using a store number to pass into a cursor.Now once have a store number ,I check want to get the area of overlapping between polygon for each store and the small polygons from table 1 over which the stores overlay. That area is called as Total_area. I am trying to pass this store number and total area in to the temp_Prop_area table.

但是我不能做，因为在我的游标类型中，我只有store_number.所以我该怎么做才能解决此问题.

But I wont be able to do since in my cursor type I just have store_number . So what could I be doing to fix this.

谢谢.

推荐答案

不确定我是否很了解你在做什么，但是问题似乎是您的c1_list没有可放入?如果是这样，您可以通过在光标中选择一个虚拟列来强制执行此操作:

Not sure I quite understand what you're doing, but the issue seems to be that your c1_list doesn't have a column to put the Total_Area? If so you can force that by selecting a dummy column in your cursor:

 Declare
     Cursor c1 is 
     select store_number, 0 as total_area
      from table1  where CLIENT_ID= 1 and ORG_ID = 1 ;

     Type C1_TAB_TYPE is table of c1%ROWTYPE;      
     c1_list c1_TAB_TYPE;

然后您可以选择其中的一个:

Then you can just select into that:

...
           Loop
            select  store_number, total_area
               BULK COLLECT INTO c1_list from
...

                   For i in 1..c1_list.count loop
                                 insert into temp_Prop_area
                 (STORE_NUMBER,TOTAL_AREA)
                  values (c1_list(i).store_number,c1_list(i).Total_Area);
                   End Loop;
             End Loop;
 End;

但是您没有利用批量插入，这已经很遗憾了，因为您已经在使用bulk collect.如果您基于新表定义集合，则可以使用forall.

But you aren't taking advantage of bulk inserts, which is a shame as you are already using bulk collect. If you define the collection based on your new table then you can use forall.

 Declare
     Cursor c1 is 
     select store_number
      from table1  where CLIENT_ID= 1 and ORG_ID = 1 ;

     Type C1_TAB_TYPE is table of temp_prop_area%ROWTYPE;      
     c1_list c1_TAB_TYPE;
...
           Loop
            select  store_number, total_area
               BULK COLLECT INTO c1_list from
...
                   Forall i in 1..c1_list.count
                      insert into temp_Prop_area
                      values c_list(i);
             End Loop;
 End;

这假设您的temp_prop_area仅按该顺序具有这两列；如果不是，则需要调整select来拉取其他任何列的哑数值.

This assumes your temp_prop_area only has those two columns, in that order; if not you'll need to adjust the select to pull dummy values for any other columns.

这篇关于SQL游标问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！