谢谢,学了一个INSERT ALL,感觉挺好用的.但我发现下面这个过程当p_hk_er = 0 并且 p_us_er=0时,却插入3条记录.而我只需要一条.
我要加入ELSE语句,它不让我加啊.
WHEN
if
p_hk_er = 0 and p_us_er=0 THEN
INTO sales(ctid, ctno) VALUES(p_tid, p_tno)
else
WHEN p_hk_er = 0 THEN
INTO sales(ctid, ctno,us_er) VALUES(p_tid, p_tno,p_us_er)
WHEN p_us_er = 0 THEN
INTO sales(ctid, ctno,hk_er) VALUES(p_tid, p_tno,p_hk_er)
end if;CREATE OR REPLACE PROCEDURE sales_insert2 (
v_ctid IN NUMBER,
v_ctno IN NUMBER,
v_hk_er IN NUMBER,
v_us_er IN NUMBER
)
IS
v_seq number;
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
select test_seq.NEXTVAL into v_seq from dual;--这里给v_seq 赋值 INSERT ALL
WHEN p_hk_er = 0 and p_us_er=0 THEN
INTO sales(ctid, ctno) VALUES(p_tid, p_tno)
WHEN p_hk_er = 0 THEN
INTO sales(ctid, ctno,us_er) VALUES(p_tid, p_tno,p_us_er)
WHEN p_us_er = 0 THEN
INTO sales(ctid, ctno,hk_er) VALUES(p_tid, p_tno,p_hk_er)
else INTO sales(ctid, ctno, hk_er, us_er) VALUES (p_tid, p_tno,p_hk_er,p_us_er)
select v_seq p_tid,v_ctno p_tno,v_us_er p_us_er,v_hk_er p_hk_er from dual;
END IF; COMMIT;
END sales_insert2;
/
我要加入ELSE语句,它不让我加啊.
WHEN
if
p_hk_er = 0 and p_us_er=0 THEN
INTO sales(ctid, ctno) VALUES(p_tid, p_tno)
else
WHEN p_hk_er = 0 THEN
INTO sales(ctid, ctno,us_er) VALUES(p_tid, p_tno,p_us_er)
WHEN p_us_er = 0 THEN
INTO sales(ctid, ctno,hk_er) VALUES(p_tid, p_tno,p_hk_er)
end if;CREATE OR REPLACE PROCEDURE sales_insert2 (
v_ctid IN NUMBER,
v_ctno IN NUMBER,
v_hk_er IN NUMBER,
v_us_er IN NUMBER
)
IS
v_seq number;
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
select test_seq.NEXTVAL into v_seq from dual;--这里给v_seq 赋值 INSERT ALL
WHEN p_hk_er = 0 and p_us_er=0 THEN
INTO sales(ctid, ctno) VALUES(p_tid, p_tno)
WHEN p_hk_er = 0 THEN
INTO sales(ctid, ctno,us_er) VALUES(p_tid, p_tno,p_us_er)
WHEN p_us_er = 0 THEN
INTO sales(ctid, ctno,hk_er) VALUES(p_tid, p_tno,p_hk_er)
else INTO sales(ctid, ctno, hk_er, us_er) VALUES (p_tid, p_tno,p_hk_er,p_us_er)
select v_seq p_tid,v_ctno p_tno,v_us_er p_us_er,v_hk_er p_hk_er from dual;
END IF; COMMIT;
END sales_insert2;
/
另外,想请教一下:这里的RETURNING ctid INTO vp_ctid;表达什么意思???特别是RETURNING语法,我实在搞不懂.
INSERT INTO sales
(ctid
)
VALUES (test_seq.NEXTVAL
)
RETURNING ctid
INTO vp_ctid;
v_ctno IN NUMBER,
v_hk_er IN NUMBER,
v_us_er IN NUMBER) IS
vp_ctid NUMBER;
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 INTO sales(ctid) VALUES (test_seq.NEXTVAL) RETURNING ctid INTO vp_ctid;
--再更新记录,如果传入的变量不为0则更新为传入的变量值,否则不变,仍为默认
UPDATE sales
SET ctno = decode(v_ctno, 0,ctno , v_ctno),
hk_er = decode(v_hk_er,0, hk_er, v_hk_er),
us_er = decode(v_us_er, 0, us_er, v_us_er)
WHERE ctid = vp_ctid;
END IF; COMMIT;
END sales_insert2;
就是说插入或更新的时候可以把插入的列的值返回放到一个变量里,这样可以避免再去select,要返回多列值也可以的:INSERT INTO sales(ctid) VALUES (test_seq.NEXTVAL) RETURNING ctid,ctno,hk_er INTO vp_ctid,vp_ctno,vp_hk_er;
if
p_hk_er = 0 and p_us_er=0 THEN
INTO sales(ctid, ctno) VALUES(p_tid, p_tno)
else
WHEN p_hk_er = 0 THEN
INTO sales(ctid, ctno,us_er) VALUES(p_tid, p_tno,p_us_er)
WHEN p_us_er = 0 THEN
INTO sales(ctid, ctno,hk_er) VALUES(p_tid, p_tno,p_hk_er)
end if;
----------------
这么写是不对的,insert all 不允许再有其他语法形式存在的,就是不能再有if else,when判断的时候还要加上不等于0 的判断:when p_hk_er = 0 and p_us_er=0 THEN
into ...
when p_hk_er = 0 and p_us_er !=0 then
into ...
when...
else
...