首頁猿問請(qǐng)問像下面那種情況怎么才能正確的返...

請(qǐng)問像下面那種情況怎么才能正確的返回一個(gè)布爾值?

Oracle

翻翻過去那場(chǎng)雪 2022-11-19 12:08:14

--庫存信息CREATE TABLE STORAGE (WARE_ID VARCHAR2(10),STORAGE_COUNTS NUMBER(10),CONSTRAINTS STOR_PK_ID PRIMARY KEY(WARE_ID));--進(jìn)貨數(shù)量CREATE TABLE STOCK (WARE_ID VARCHAR2(10),STOCK_COUNTS NUMBER(10) NOT NULL,CONSTRAINTS STO_PK_ID PRIMARY KEY(WARE_ID));ALTER TABLE STOCKADD CONSTRAINTS STO_FK_COUNTS FOREIGN KEY(WARE_ID) REFERENCES STORAGE(WARE_ID);CREATE OR REPLACE TRIGGER BI_STOBEFORE INSERT OR UPDATEON STOCKFOR EACH ROWBEGINIF(EXISTS(SELECT STO.WARE_ID FROM STORAGE STOWHERE STO.WARE_ID = :NEW.WARE_ID))THENUPDATE 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))THENINSERT INTO STORAGE VALUES(:NEW.WARE_ID, :NEW.STOCK_COUNTS);END IF;END IF;END;TRIGGER SGL.BI_STO 編譯錯(cuò)誤錯(cuò)誤：PLS-00204: 函數(shù)或偽列 'EXISTS' 只能在 SQL 語句中使用行：7文本：IF(EXISTS(SELECT STO.WARE_ID FROM STORAGE STO錯(cuò)誤：PL/SQL: Statement ignored行：7文本：IF(EXISTS(SELECT STO.WARE_ID FROM STORAGE STO

查看完整描述

3 回答

長(zhǎng)風(fēng)秋雁

TA貢獻(xiàn)1757條經(jīng)驗(yàn) 獲得超7個(gè)贊

這樣應(yīng)該就可以了，不過自治事務(wù)用在業(yè)務(wù)比較復(fù)雜的數(shù)據(jù)庫里面是要慎用的，很容易引發(fā)死鎖，
create
or
replace
trigger
stu_upd
after
update
on
score
referencing
old
as
old
new
as
new
for
each
row
declare
pragma
autonomous_transaction
;
t_savg
number(6,2);
begin
select
avg(score)
into
t_savg
from
score
where
sno=:new.sno;
update
student
set
savg=t_savg
where
sno=:new.sno;
commit
;
end;

反對(duì) 回復(fù) 2022-11-24

慕妹3146593

TA貢獻(xiàn)1820條經(jīng)驗(yàn) 獲得超9個(gè)贊

主要原因是調(diào)用biu_document
觸發(fā)器時(shí)，沒有commit，所以執(zhí)行
select
sum(money)
into
countmoney
from
(select
sto.counts
*
sto.price
money
from
stock
sto
where
sto.document_id
=
:new.document_id)
stock_moeny
時(shí)，返回的是null值。
觸發(fā)器之間也不能傳遞參數(shù)，這里建議寫存儲(chǔ)過程吧
在biu_stock觸發(fā)器中調(diào)用存儲(chǔ)過程分別向document，affairs寫數(shù)據(jù)。

反對(duì) 回復(fù) 2022-11-24

蝴蝶刀刀

TA貢獻(xiàn)1801條經(jīng)驗(yàn) 獲得超8個(gè)贊

用select into 如果查詢出來的是null值，應(yīng)該會(huì)報(bào)錯(cuò)吧
試試下面這個(gè)：

CREATE OR REPLACE TRIGGER BI_STO
BEFORE INSERT OR UPDATE
ON STOCK
FOR EACH ROW
declare
v_count int;
BEGIN
select count(*)
into v_count
from STORAGE STO
WHERE STO.WARE_ID = :NEW.WARE_ID;

if v_count > 0 then
UPDATE STORAGE
SET STORAGE_COUNTS = (STORAGE_COUNTS +:NEW.STOCK_COUNTS)
WHERE WARE_ID = :NEW.WARE_ID;
else
INSERT INTO STORAGE
VALUES(:NEW.WARE_ID,:NEW.STOCK_COUNTS);
end if;
END;