17,140
社区成员




INSERT INTO BO_PSM_KHXX(字段1,字段2)
SELECT 字段1,字段2 FROM HZ_cust_Accounts
INSERT INTO BO_PSM_KHXX(ZWKHMC,KHBIH)
SELECT ACCOUNT_NAME,CUST_ACCOUNT_ID FROM HZ_cust_Accounts
try this 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;
--創建
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;
--如果不用触发器,你这么写只能执行一次,第二次就会发生冲突写不进去。
--在表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;