急急急，在线等数据库大神

[凯凯灬]

我有两个数据库 两张表。一张HZ_cust_Accounts。一张是 BO_PSM_KHXX，
要写个存储过程 将HZ_cust_Accounts其实两个字段复制到BO_PSM_KHXX来。

求大神速度给我解决下 在线等。 以前没学好 ~~~~(>_<)~~~~

[漠北雁]

--在表HZ_cust_Accounts上創建Trigger
CREATE OR REPLACE TRIGGER xxHZ_cust_Accounts AFTER INSERT OR DELETE OR UPDATE ON HZ_cust_Accounts REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW

BEGIN
    IF  INSERTING THEN
        INSERT INTO BO_PSM_KHXX(字段1,字段2)VALUES(:NEW.字段1,:NEW.字段2); 
    END IF;
    IF UPDATING THEN
        UPDATE BO_PSM_KHXX SET 字段1=:NEW.字段1,字段2=:NEW.字段2 WHERE (注意更新條件);
    END IF;
END;

[凯凯灬]

引用 2 楼 robysomo 的回复:

--在表HZ_cust_Accounts上創建Trigger
CREATE OR REPLACE TRIGGER xxHZ_cust_Accounts AFTER INSERT OR DELETE OR UPDATE ON HZ_cust_Accounts REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW

BEGIN
    IF  INSERTING THEN
        INSERT INTO BO_PSM_KHXX(字段1,字段2)VALUES(:NEW.字段1,:NEW.字段2); 
    END IF;
    IF UPDATING THEN
        UPDATE BO_PSM_KHXX SET 字段1=:NEW.字段1,字段2=:NEW.字段2 WHERE (注意更新條件);
    END IF;
END;

怕不是这样的。。。 我是需要将HZ_cust_Accounts中的ACCOUNT_NAME和CUST_ACCOUNT_ID 复制到 BO_PSM_KHXX中的ZWKHMC和KHBIH这两个上面。

[凯凯灬]

create or replace procedure khxxgc as cursor HZ_cust_Accounts is SELECT ACCOUNT_NAME,CUST_ACCOUNT_ID FROM HZ_cust_Accounts;W_ACCOUNT_NAME varchar2(50),W_CUST_ACCOUNT_ID varchar2(50); begin for W_ACCOUNT_NAME,W_CUST_ACCOUNT_ID in HZ_cust_Accounts loop insert into BO_PSM_KHXX(ZWKHMC,KHBIH) values(W_ACCOUNT_NAME,W_CUST_ACCOUNT_ID); end loop; end khxxgc; call khxxgc(); 我是这么写的 call khxxgc(); 出错了。 大神们 急救阿。

[凯凯灬]

create or replace procedure khxxgc as cursor cs is SELECT ACCOUNT_NAME,CUST_ACCOUNT_ID FROM HZ_cust_Accounts;W_ACCOUNT_NAME varchar2(50),W_CUST_ACCOUNT_ID varchar2(50); begin for c in cs loop BEGIN insert into BO_PSM_KHXX(KHBIH,ZWKHMC) values(W_CUST_ACCOUNT_ID,W_ACCOUNT_NAME); end loop; end khxxgc; call khxxgc(); 这是我的代码。

[漠北雁]

--創建
CREATE OR REPLACE PROCEDURE KHXXGC
AS
CURSOR HZ_CUST_ACCOUNTS IS SELECT ACCOUNT_NAME,CUST_ACCOUNT_ID FROM HZ_CUST_ACCOUNTS;

BEGIN 
FOR ROWS  IN HZ_CUST_ACCOUNTS LOOP  
  INSERT INTO BO_PSM_KHXX(ZWKHMC,KHBIH) VALUES(ROWS.ACCOUNT_NAME,ROWS.CUST_ACCOUNT_ID);
  EXIT WHEN HZ_CUST_ACCOUNTS%NOTFOUND;
END LOOP; 
  EXCEPTION WHEN OTHERS THEN NULL; 
END KHXXGC;  

--调用
EXEC KHXXGC;

--如果不用触发器，你这么写只能执行一次，第二次就会发生冲突写不进去。

[NIan_jun]

没有必要用游标。

  insert into BO_PSM_KHXX(KHBIH, ZWKHMC)
 SELECT ACCOUNT_NAME, CUST_ACCOUNT_ID FROM HZ_cust_Accounts;

游标写法不用定义变量了，定义多个变量每个变量后面;号隔开，不是逗号。

create or replace procedure khxxgc as
begin
  for c in (SELECT ACCOUNT_NAME, CUST_ACCOUNT_ID FROM HZ_cust_Accounts) loop
    BEGIN
      insert into BO_PSM_KHXX
        (KHBIH, ZWKHMC)
      values
        (c.ACCOUNT_NAME, c.CUST_ACCOUNT_ID);
    end loop;
  end khxxgc;

[Leon_He2014]

INSERT INTO BO_PSM_KHXX(ZWKHMC,KHBIH)
SELECT ACCOUNT_NAME,CUST_ACCOUNT_ID FROM HZ_cust_Accounts

try this

[善若止水]

楼主要先确定是增量更新还是全量的更新。这需要你在存储过程中做插入前的处理。然后，

INSERT INTO BO_PSM_KHXX(字段1,字段2)
SELECT 字段1,字段2 FROM HZ_cust_Accounts