【救助】cursor 在 procedure里面的使用（online）

Question

create or replace
procedure AddInventoryPointForTest ASFacilityId number;
CommodityId number;Begin CURSOR cur1 IS (SELECT Facility.facilityId FROM FacilityLocation INNER JOIN Facility ON FacilityLocation.facilityId = Facility.facilityId RIGHT OUTER JOIN LocationRelationship ON FacilityLocation.locationId = LocationRelationship.childLocationID RIGHT OUTER JOIN Location ON LocationRelationship.parentLocationID = Location.locationID where LocationRelationShip.childLocationId is Not NULL and Facility.facilityId is Not NULL);        CURSOR cur2 IS (SELECT commodityid from commodity where id is not null); 
For x in cursor_1 LOOP
  FacilityId := x;
For y in cursor_2 LOOP
            CommodityId := y;
    Insert into INVENTORYPOINT (ID,EXTERNALREFID,OWNERCODE,FACILITYID,LOCATIONID,COMMODITYID, VALIDFROMDATE,VALIDTODATE,CREATEDATE,CREATORCODE,MODIFYDATE,MODIFIERCODE,MODIFIERSESSIONID) values (SQ_INVPOINT_ID.Nextval,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),null,x,null,y,null,null,null,'00056ABCDE',null,'00056ABCDE','dpLjMWli45JEOWLEspoXig==36');
End LOOP;End LOOP;End AddInventoryPointForTest;
编译时报错：
1） Error(9,9): PLS-00103: Encountered the symbol "CUR1" when expecting one of the following:     := . ( @ % ; 
2） Error(9,73): PLS-00103: Encountered the symbol "JOIN" when expecting one of the following:     ) , group having intersect minus start union where connect 为了做测试写了这个procedure，但是编译时就报错，我不懂oracle，突击了一下，还是有问题，麻烦高手帮我解答一下，我要如何修改，谢谢。

Answer 1

说错了，没看到你用的是隐士游标，看错误有肯能是sql写的有问题，还有就是游标的定义写在begin的外边。
SELECT Facility.facilityId FROM FacilityLocation INNER JOIN Facility ON FacilityLocation.facilityId = Facility.facilityId RIGHT OUTER JOIN LocationRelationship ON FacilityLocation.locationId = LocationRelationship.childLocationID RIGHT OUTER JOIN Location ON LocationRelationship.parentLocationID = Location.locationID where LocationRelationShip.childLocationId is Not NULL and Facility.facilityId is Not NULL); 
执行一下看有没有问题

Answer 2

create or replace 
procedure AddInventoryPointForTest AS FacilityId number; 
CommodityId number; 
CURSOR cur1 IS (SELECT Facility.facilityId FROM FacilityLocation INNER JOIN Facility ON FacilityLocation.facilityId = Facility.facilityId RIGHT OUTER JOIN LocationRelationship ON FacilityLocation.locationId = LocationRelationship.childLocationID RIGHT OUTER JOIN Location ON LocationRelationship.parentLocationID = Location.locationID where LocationRelationShip.childLocationId is Not NULL and Facility.facilityId is Not NULL); 
CURSOR cur2 IS (SELECT commodityid from commodity where id is not null); 
Begin For x in cur1 LOOP 
  FacilityId := x; 
For y in cur2 LOOP 
            CommodityId := y; 
    Insert into INVENTORYPOINT (ID,EXTERNALREFID,OWNERCODE,FACILITYID,LOCATIONID,COMMODITYID, VALIDFROMDATE,VALIDTODATE,CREATEDATE,CREATORCODE,MODIFYDATE,MODIFIERCODE,MODIFIERSESSIONID) values (SQ_INVPOINT_ID.Nextval,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),null,x,null,y,null,null,null,'00056ABCDE',null,'00056ABCDE','dpLjMWli45JEOWLEspoXig==36'); 
End LOOP; 
End LOOP; 
close cur2;
close cur1;
End AddInventoryPointForTest; 

Answer 3

请先确定你的sql写的是不是有问题

Answer 4

SQL 语句单独运行是正常的。

Answer 5

CREATE OR REPLACE PROCEDURE addinventorypointfortest
AS
   facilityid    NUMBER;
   commodityid   NUMBER;   CURSOR cur1
   IS
      (SELECT facility.facilityid
         FROM facilitylocation INNER JOIN facility ON facilitylocation.facilityid =
                                                          facility.facilityid
              RIGHT OUTER JOIN locationrelationship ON facilitylocation.locationid =
                                                         locationrelationship.childlocationid
              RIGHT OUTER JOIN LOCATION ON locationrelationship.parentlocationid =
                                                          LOCATION.locationid
        WHERE locationrelationship.childlocationid IS NOT NULL
          AND facility.facilityid IS NOT NULL);   CURSOR cur2
   IS
      (SELECT commodityid
         FROM commodity
        WHERE ID IS NOT NULL);
BEGIN
   FOR x IN cur1
   LOOP
      facilityid := x;      FOR y IN cur2
      LOOP
         commodityid := y;         INSERT INTO inventorypoint
                     (ID,
                      externalrefid, ownercode, facilityid,
                      locationid, commodityid, validfromdate, validtodate,
                      createdate, creatorcode, modifydate, modifiercode,
                      modifiersessionid
                     )
              VALUES (sq_invpoint_id.NEXTVAL,
                      TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss'), NULL, x,
                      NULL, y, NULL, NULL,
                      NULL, '00056ABCDE', NULL, '00056ABCDE',
                      'dpLjMWli45JEOWLEspoXig==36'
                     );
      END LOOP;
   END LOOP;   CLOSE cur2;   CLOSE cur1;
END addinventorypointfortest;表达式类型错误，声明的变量是number类型

Answer 6

表达式类型错误，声明的变量是number类型-----》恩，我觉得应该也是这样问题，但是我不会类型转换，请指一个方向给我，谢谢。急～～～～

Answer 7

CREATE OR REPLACE PROCEDURE addinventorypointfortest
AS
   CURSOR cur1
   IS
      (SELECT facility.facilityid
         FROM facilitylocation INNER JOIN facility ON facilitylocation.facilityid =
                                                          facility.facilityid
              RIGHT OUTER JOIN locationrelationship ON facilitylocation.locationid =
                                                         locationrelationship.childlocationid
              RIGHT OUTER JOIN LOCATION ON locationrelationship.parentlocationid =
                                                          LOCATION.locationid
        WHERE locationrelationship.childlocationid IS NOT NULL
          AND facility.facilityid IS NOT NULL);   CURSOR cur2
   IS
      (SELECT commodityid
         FROM commodity
        WHERE ID IS NOT NULL);
BEGIN
   FOR x IN cur1
   LOOP
      FOR y IN cur2
      LOOP
         INSERT INTO inventorypoint
                     (ID,
                      externalrefid, ownercode,
                      facilityid, locationid, commodityid, validfromdate,
                      validtodate, createdate, creatorcode, modifydate,
                      modifiercode, modifiersessionid
                     )
              VALUES (sq_invpoint_id.NEXTVAL,
                      TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss'), NULL,
                      x.facilityid, NULL, y.commodityid, NULL,
                      NULL, NULL, '00056ABCDE', NULL,
                      '00056ABCDE', 'dpLjMWli45JEOWLEspoXig==36'
                     );
      END LOOP;
   END LOOP;
END addinventorypointfortest;这个Porcedure应该能执行，但是可能不是你想要的结果。
因为你嵌套的Cursor里的两张表没有任何关联，不知道你的需求是怎样的？
建议你好好看一下Oracle游标的语法再按照需求重新设计