DECLARE num number(8);
BEGIN
SELECT storeid INTO num FROM store ,purchase WHERE store.pname=purchase.pname AND store.type=purchase.type AND store.price=purchase.price;
IF num > 0 THEN
UPDATE store SET store.totalcount = store.Totalcount+purchase.Count;
ELSE
INSERT INTO store VALUES(storeid_seq.nextval,purchase.pname,purchase.Type,purchase.price,purchase.count);
END IF;
END;想要做的是如果store表中有记录则更新,否则插入,不用merge。帮忙看看是什么问题ORA-06550: 第 5 行, 第 54 列:
PL/SQL: ORA-00904: "PURCHASE"."COUNT": 标识符无效
ORA-06550: 第 5 行, 第 1 列:
PL/SQL: SQL Statement ignored
ORA-06550: 第 7 行, 第 99 列:
PL/SQL: ORA-00984: 列在此处不允许
ORA-06550: 第 7 行, 第 1 列:
PL/SQL: SQL Statement ignored
BEGIN
SELECT storeid INTO num FROM store ,purchase WHERE store.pname=purchase.pname AND store.type=purchase.type AND store.price=purchase.price;
IF num > 0 THEN
UPDATE store SET store.totalcount = store.Totalcount+purchase.Count;
ELSE
INSERT INTO store VALUES(storeid_seq.nextval,purchase.pname,purchase.Type,purchase.price,purchase.count);
END IF;
END;想要做的是如果store表中有记录则更新,否则插入,不用merge。帮忙看看是什么问题ORA-06550: 第 5 行, 第 54 列:
PL/SQL: ORA-00904: "PURCHASE"."COUNT": 标识符无效
ORA-06550: 第 5 行, 第 1 列:
PL/SQL: SQL Statement ignored
ORA-06550: 第 7 行, 第 99 列:
PL/SQL: ORA-00984: 列在此处不允许
ORA-06550: 第 7 行, 第 1 列:
PL/SQL: SQL Statement ignored
num NUMBER(8);
BEGIN
SELECT storeid --有多个值不能这样处理
INTO num
FROM store, purchase
WHERE store.pname = purchase.pname AND
store.type = purchase.type AND
store.price = purchase.price;
IF num > 0 THEN
UPDATE store SET store.totalcount = store.Totalcount + purchase.Count;
--此处不能引用purchase表,因为本语句中没有此表
ELSE
INSERT INTO store
VALUES
(storeid_seq.nextval, purchase.pname, purchase.Type, purchase.price, purchase.count);
--此处不能引用purchase表,因为本语句中没有此表
END IF;
END;--修改为,自己测试一下
DECLARE
BEGIN
FOR cur IN (SELECT storeid
FROM store, purchase
WHERE store.pname = purchase.pname AND
store.type = purchase.type AND
store.price = purchase.price) LOOP
IF cur.storeid > 0 THEN
UPDATE store SET store.totalcount = store.Totalcount + cur.Count;
ELSE
INSERT INTO store
VALUES
(storeid_seq.nextval, storeid.pname, storeid.Type, storeid.price, storeid.count);
END IF;
END LOOP;
END;
呵呵