1. oracle 自增键的实现
CREATE OR REPLACE TRIGGER a_increment
BEFORE INSERT ON table_a
FOR EACH ROW WHEN (NEW.ID IS NULL)
BEGIN
SELECT USER_SEQUENCE.NEXTVAL INTO :NEW.ID FROM DUAL;
//INSERT INTO TABLE_A (ID) VALUES (OLD.ID); 此处insert没有必要使用
END;
2. 存储过程与游标
CREATE OR REPLACE PROCEDURE P_INSERT_A_FROM_B IS
CURSOR C_TABLE_B IS
SELECT ID FROM SCOTT.TABLE_B;
B_ID C_TABLE_B%ROWTYPE;
BEGIN
OPEN C_TABLE_B;
LOOP
FETCH C_TABLE_B
INTO B_ID;
EXIT WHEN C_TABLE_B%NOTFOUND;
INSERT INTO TABLE_A (B_REF) VALUES (B_ID.ID);
END LOOP;
CLOSE C_TABLE_B;
END;