请问像下面那种情况怎么才能正确的返回一个布尔值?

Question

--库存信息
CREATE TABLE STORAGE (
WARE_ID VARCHAR2(10),
STORAGE_COUNTS NUMBER(10),
CONSTRAINTS STOR_PK_ID PRIMARY KEY(WARE_ID)
);

--进货数量
CREATE TABLE STOCK (
WARE_ID VARCHAR2(10),
STOCK_COUNTS NUMBER(10) NOT NULL,
CONSTRAINTS STO_PK_ID PRIMARY KEY(WARE_ID)
);

ALTER TABLE STOCK
ADD CONSTRAINTS STO_FK_COUNTS FOREIGN KEY(WARE_ID) REFERENCES STORAGE(WARE_ID);

CREATE OR REPLACE TRIGGER BI_STO
BEFORE INSERT OR UPDATE
ON STOCK
FOR EACH ROW
BEGIN
IF(EXISTS(SELECT STO.WARE_ID FROM STORAGE STO
WHERE STO.WARE_ID = :NEW.WARE_ID))
THEN
UPDATE STORAGE SET STORAGE.STORAGE_COUNTS = 
(STORAGE.STORAGE_COUNTS + :NEW.STOCK_COUNTS)
WHERE STORAGE.WARE_ID = :NEW.WARE_ID;
ELSE IF(NOT EXISTS(SELECT STO.WARE_ID FROM STORAGE STO, STOCK 
WHERE STO.WARE_ID = :NEW.WARE_ID))
THEN
INSERT INTO STORAGE VALUES(:NEW.WARE_ID, :NEW.STOCK_COUNTS);
END IF;
END IF;
END;

TRIGGER SGL.BI_STO 编译错误

错误：PLS-00204: 函数或伪列 'EXISTS' 只能在 SQL 语句中使用
行：7
文本：IF(EXISTS(SELECT STO.WARE_ID FROM STORAGE STO

错误：PL/SQL: Statement ignored
行：7
文本：IF(EXISTS(SELECT STO.WARE_ID FROM STORAGE STO

Answer 1

这样应该就可以了，不过自治事务用在业务比较复杂的数据库里面是要慎用的，很容易引发死锁，createorreplacetriggerstu_updafterupdateonscorereferencingoldasoldnewasnewforeachrowdeclarepragmaautonomous_transaction;t_savgnumber(6,2);beginselectavg(score)intot_savgfromscorewheresno=:new.sno;updatestudentsetsavg=t_savgwheresno=:new.sno;commit;end; 

Answer 2

主要原因是调用biu_document触发器时，没有commit，所以执行selectsum(money)intocountmoneyfrom(selectsto.counts*sto.pricemoneyfromstockstowheresto.document_id=:new.document_id)stock_moeny时，返回的是null值。触发器之间也不能传递参数，这里建议写存储过程吧在biu_stock触发器中调用存储过程分别向document，affairs写数据。 

Answer 3

用select into 如果 查询出来的是null值，应该会报错吧试试下面这个：CREATE OR REPLACE TRIGGER BI_STOBEFORE INSERT OR UPDATEON STOCKFOR EACH ROW  declarev_count int;BEGINselect count(*)into v_countfrom STORAGE STOWHERE STO.WARE_ID = :NEW.WARE_ID;if v_count > 0 thenUPDATE STORAGESET STORAGE_COUNTS = (STORAGE_COUNTS +:NEW.STOCK_COUNTS)WHERE WARE_ID = :NEW.WARE_ID;elseINSERT INTO STORAGEVALUES(:NEW.WARE_ID,:NEW.STOCK_COUNTS);end if;END;