帮忙改一下这个过程:
要求:当v_hk_er (v_us_er)值为0时,则sales表的hr_er(us_er)值为系统缺省值。
当v_hk_er (v_us_er)值不为0时,则sales表的hr_er(us_er)值为v_hk_er (v_us_er)。
CREATE OR REPLACE PROCEDURE sales_insert (
v_ctid IN NUMBER,
v_ctno IN NUMBER,
v_hk_erIN NUMBER,
v_us_erIN NUMBER
)
IS
BEGIN
UPDATE sales
SET ctno = v_ctno,
hk_er = v_hk_er,
us_er = v_us_er
WHERE ctid = v_ctid;
IF SQL%NOTFOUND
THEN
INSERT ALL
WHEN v_hk_er = 0 THEN
INTO sales(ctid, ctno,us_er) VALUES(test_seq.NEXTVAL, v_ctno,v_us_er)
WHEN v_us_er = 0 THEN
INTO sales(ctid, ctno,hk_er) VALUES(test_seq.NEXTVAL, v_ctno,v_hk_er)
else INTO sales
(ctid, ctno,hk_er, us_er)
VALUES (test_seq.NEXTVAL, v_ctno, v_hk_er, v_us_er);
END IF;
COMMIT;
END sales_insert; |