我有两个数据库 两张表。一张HZ_cust_Accounts。一张是 BO_PSM_KHXX,
要写个存储过程 将HZ_cust_Accounts其实两个字段复制到BO_PSM_KHXX来。求大神速度给我解决下 在线等。 以前没学好 ~~~~(>_<)~~~~
要写个存储过程 将HZ_cust_Accounts其实两个字段复制到BO_PSM_KHXX来。求大神速度给我解决下 在线等。 以前没学好 ~~~~(>_<)~~~~
INSERT INTO BO_PSM_KHXX(字段1,字段2)
SELECT 字段1,字段2 FROM HZ_cust_Accounts
--在表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 ROWBEGIN
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;
BO_PSM_KHXX中的ZWKHMC和KHBIH这两个上面。
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;--如果不用触发器,你这么写只能执行一次,第二次就会发生冲突写不进去。
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;
INSERT INTO BO_PSM_KHXX(ZWKHMC,KHBIH)
SELECT ACCOUNT_NAME,CUST_ACCOUNT_ID FROM HZ_cust_Accountstry this