帮忙改一下这个过程.

Question

帮忙改一下这个过程：要求：当v_cn值为0时，则sales表的cn值为系统缺省值。
当v_cn值不为0时，则sales表的cn值为v_cn。
CREATE OR REPLACE PROCEDURE sales_insert (
   v_ctid         IN   NUMBER,
   v_ctno         IN   NUMBER,
   v_customer     IN   VARCHAR2,
   v_ctmold       IN   VARCHAR2,
   v_model        IN   VARCHAR2,
   v_item         IN   VARCHAR2,
   v_edate        IN   DATE,
   v_eunit        IN   VARCHAR2,
   v_eucontract   IN   VARCHAR2,
   v_cn           IN   NUMBER,
   v_hk           IN   NUMBER,
   v_hk_er        IN   NUMBER,
   v_us           IN   NUMBER,
   v_us_er        IN   NUMBER
)
IS
BEGIN
   UPDATE sales
      SET ctno = v_ctno,
          customer = v_customer,
          ctmold = v_ctmold,
          model = v_model,
          item = v_item,
          edate = v_edate,
          eunit = v_eunit,
          eucontract = v_eucontract,
          cn = v_cn,
          hk = v_hk,
          hk_er = v_hk_er,
          us = v_us,
          us_er = v_us_er
    WHERE ctid = v_ctid;   IF SQL%NOTFOUND
   THEN
      INSERT INTO sales
                  (ctid, ctno, customer, ctmold, model,
                   item, edate, eunit, eucontract, cn, hk,
                   hk_er, us, us_er
                  )
           VALUES (test_seq.NEXTVAL, v_ctno, v_customer, v_ctmold, v_model,
                   v_item, v_edate, v_eunit, v_eucontract, v_cn, v_hk,
                   v_hk_er, v_us, v_us_er
                  );
   END IF;   COMMIT;
END sales_insert;
/

Answer 1

CREATE OR REPLACE PROCEDURE sales_insert (
   v_ctid         IN   NUMBER,
   v_ctno         IN   NUMBER,
   v_customer     IN   VARCHAR2,
   v_ctmold       IN   VARCHAR2,
   v_model        IN   VARCHAR2,
   v_item         IN   VARCHAR2,
   v_edate        IN   DATE,
   v_eunit        IN   VARCHAR2,
   v_eucontract   IN   VARCHAR2,
   v_cn           IN   NUMBER,
   v_hk           IN   NUMBER,
   v_hk_er        IN   NUMBER,
   v_us           IN   NUMBER,
   v_us_er        IN   NUMBER
)
IS
BEGIN
   UPDATE sales
      SET ctno = v_ctno,
          customer = v_customer,
          ctmold = v_ctmold,
          model = v_model,
          item = v_item,
          edate = v_edate,
          eunit = v_eunit,
          eucontract = v_eucontract,
          cn = v_cn,
          hk = v_hk,
          hk_er = v_hk_er,
          us = v_us,
          us_er = v_us_er
    WHERE ctid = v_ctid;   IF SQL%NOTFOUND
   THEN
     if v_cn=0 then   
      INSERT INTO sales
                  (ctid, ctno, customer, ctmold, model,
                   item, edate, eunit, eucontract, hk,
                   hk_er, us, us_er
                  )
           VALUES (test_seq.NEXTVAL, v_ctno, v_customer, v_ctmold, v_model,
                   v_item, v_edate, v_eunit, v_eucontract, v_hk,
                   v_hk_er, v_us, v_us_er
                  );
     else
       INSERT INTO sales
                  (ctid, ctno, customer, ctmold, model,
                   item, edate, eunit, eucontract, cn, hk,
                   hk_er, us, us_er
                  )
           VALUES (test_seq.NEXTVAL, v_ctno, v_customer, v_ctmold, v_model,
                   v_item, v_edate, v_eunit, v_eucontract, v_cn, v_hk,
                   v_hk_er, v_us, v_us_er
                  );
     end if;
   END IF;   COMMIT;
END sales_insert;
/

Answer 2

天啊，真的要这样写吗？？？如果我要求的不仅仅只有v_cn，
而是v_cn，v_hk,v_hk_er，v_us,v_us_er五个字段，那岂不把过程写得长长长的？

Answer 3

你写触发器吧，在insert触发器里判断就行了

Answer 4

CREATE OR REPLACE TRIGGER test BEFORE
INSERT ON sales FOR EACH ROW 
BEGIN
  if :new.cn=0 then 
    :new.cn:=default;
  end if;
  if :new.hk=0 then 
    :new.hk:=default;
  end if;
  ...
END;

Answer 5

CREATE OR REPLACE TRIGGER test BEFORE
INSERT ON sales FOR EACH ROW 
BEGIN
  if :new.hk_er=0 then 
    :new.hk_er:=default;
  end if;
  if :new.us_er=0 then 
    :new.us_er:=default;
  end if;
  
END;
不行，提示如下错误：
PLS-00103: 出现符号 "DEFAULT"在需要下列之一时：
 ( - + case mod new not
   null <an identifier> <a double-quoted delimited-identifier>
   <a bind variable> avg count current exists max min prior sql
   stddev sum

Answer 6

觉得没必要用触发器的，如果插入很频繁的话用触发器影响效率的，还不如用if else判断，不过在9i下用一个insert 语句就可以解决了，如果你的oracle是9i或以上版本的话可以参照我的方法修改你的insert语句：SQL> create table T
  2  (
  3    col1  NUMBER default 10,
  4    col2 VARCHAR2(20),
  5    col3 VARCHAR(20)
  6  )
  7  /Table createdSQL> 
SQL> INSERT ALL
  2  WHEN a = 0 THEN   --这里走这个分支，col1插入的是默认值10
  3  INTO t(col2,col3) VALUES(b,c)
  4  ELSE
  5  INTO t(col1,col2,col3) VALUES(a,b,c)
  6  SELECT 0 a,1 b,2 c FROM dual;1 row insertedSQL> 
SQL> INSERT ALL
  2  WHEN a = 0 THEN
  3  INTO t(col2,col3) VALUES(b,c)
  4  ELSE -这里走这个分支，col1插入的是3
  5  INTO t(col1,col2,col3) VALUES(a,b,c)
  6  SELECT 3 a,4 b,5 c FROM dual;1 row insertedSQL> commit;Commit completeSQL> select * from t;      COL1 COL2                 COL3
---------- -------------------- --------------------
        10 1                    2
         3 4                    5insert all 主要用来同时往多个表插入数据的，不过这里刚好也可以用到

Answer 7

加入我要判断多个字段的话可以这样INSERT ALL 
WHEN a = 0 THEN
INTO t(col2,col3) VALUES(b,c)
WHEN b = 0 THEN
INTO t(col1,col3) VALUES(a,c)
WHEN c = 0 THEN
INTO t(col1,col2) VALUES(a,b)
ELSE
INTO t(col1,col2,col3) VALUES(a,b,c)
SELECT 3 a,4 b,5 c FROM dual;

Answer 8

我的创建过程不行啊？？？为什么要在最后加SELECT语句呢？CREATE OR REPLACE PROCEDURE sales_insert (
   v_ctid         IN   NUMBER,
   v_ctno         IN   NUMBER,
   v_hk_er        IN   NUMBER,
   v_us_er        IN   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;
/

Answer 9

最后必须是子查询，因为上面用到的select查询里的别名，直接用变量名是不可以 的，你可以select ... from dual，把所有变量赋个别名就可以了，你看我上面是怎么写的，也是用常量值插入的INSERT ALL 
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 test_seq.NEXTVAL p_tid,v_ctno p_tno,v_us_er p_us_er,v_hk_er p_hk_er from dual;-- 必须加上这个子查询语句

Answer 10

INSERT ALL 
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 test_seq.NEXTVAL p_tid,v_ctno p_tno,v_us_er p_us_er,v_hk_er p_hk_er from dual;--这里用duan表构造子查询，每个变量为1列并各赋一个别名，在上面判断的时候用

Answer 11

谢谢,但创建过程时它提示:此处不允许序号,我要怎么改???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
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 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 test_seq.NEXTVAL 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;
/

Answer 12

你可以定义一个变量把序列存储起来，然后在插入的时候用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 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;

Answer 13

这样判断种类会很多吧，v_cn，v_hk,v_hk_er，v_us,v_us_er同时为零或其中几个为零的insert语句也要加进去。
把我那段触发器的内容加进来试试
CREATE OR REPLACE PROCEDURE sales_insert (
   v_ctid         IN   NUMBER,
   v_ctno         IN   NUMBER,
   v_customer     IN   VARCHAR2,
   v_ctmold       IN   VARCHAR2,
   v_model        IN   VARCHAR2,
   v_item         IN   VARCHAR2,
   v_edate        IN   DATE,
   v_eunit        IN   VARCHAR2,
   v_eucontract   IN   VARCHAR2,
   v_cn           IN   NUMBER,
   v_hk           IN   NUMBER,
   v_hk_er        IN   NUMBER,
   v_us           IN   NUMBER,
   v_us_er        IN   NUMBER
)
IS
BEGIN
   UPDATE sales
      SET ctno = v_ctno,
          customer = v_customer,
          ctmold = v_ctmold,
          model = v_model,
          item = v_item,
          edate = v_edate,
          eunit = v_eunit,
          eucontract = v_eucontract,
          cn = v_cn,
          hk = v_hk,
          hk_er = v_hk_er,
          us = v_us,
          us_er = v_us_er
    WHERE ctid = v_ctid;   IF SQL%NOTFOUND
   THEN
     if v_cn=0 then 
       v_cn:=default;
     end if;
     if v_hk=0 then 
       v_hk:=default;
     end if;
     ...
     INSERT INTO sales
                  (ctid, ctno, customer, ctmold, model,
                   item, edate, eunit, eucontract, cn, hk,
                   hk_er, us, us_er
                  )
           VALUES (test_seq.NEXTVAL, v_ctno, v_customer, v_ctmold, v_model,
                   v_item, v_edate, v_eunit, v_eucontract, v_cn, v_hk,
                   v_hk_er, v_us, v_us_er
                  );
   END IF;   COMMIT;
END sales_insert;
/

Answer 14

TO: duanzilin(寻) ( 
 waterfirer(水清) 谢谢你们2位了,我的分都不知道怎么给才好.谢谢你们了.

Answer 15

呵呵，我这个给楼主提供一种思路，如果插入太频繁的话，用触发器确实影响效率，这样吧，还有一个更简洁的方法：
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
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;

Answer 16

duanzilin(寻)说得对，插入频繁时，触发器影响效率。
我差点把楼主误导了，多亏了duanzilin(寻)，：）