oracle insert两个关联表

现有一张老师学生表(tb_tea_cou)，由于业务需要，需把老师学生表tb_tea_stu拆分成两张表(tb_tea、tb_cou)，并把记录insert到这两张子表中(tb_tea、tb_cou为关联的两张表)。

表结构如下：
tb_tea_cou(
   id, //pk
   name, //任课老师
   zc, //职称
   course //课程
)，

老师表：
tb_tea(
   tid, //pk
   tname, //任课老师
   zc //职称
)，

课程表：
tb_cou(
   cid, //pk
   course, //课程
   tea_id //fk，tb_tea id
)

插入数据

INSERT INTO tb_tea_cou (ID, name, zc, course) VALUES (hibernate_seq.nextval, '张三', '教师', '语文');

INSERT INTO tb_tea_cou (ID, name, zc, course) VALUES (hibernate_seq.nextval, '李四', '教师', '数学');

INSERT INTO tb_tea_cou (ID, name, zc, course) VALUES (hibernate_seq.nextval, '王五', '教师', '英语');

INSERT INTO tb_tea_cou (ID, name, zc, course) VALUES (hibernate_seq.nextval, '老刘', '教师', '历史');

INSERT INTO tb_tea_cou (ID, name, zc, course) VALUES (hibernate_seq.nextval, '小王', '教师', '政治');

INSERT INTO tb_tea_cou (ID, name, zc, course) VALUES (hibernate_seq.nextval, '杜甫', '教师', '生物');

INSERT INTO tb_tea_cou (ID, name, zc, course) VALUES (hibernate_seq.nextval, '李白', '教师', '化学');

INSERT INTO tb_tea_cou (ID, name, zc, course) VALUES (hibernate_seq.nextval, '韩愈', '教师', '物理');

select * from tb_tea_cou;

 

思路，使用存储过程，插入tb_tea表之后，select max(tid) from tb_tea; 得到刚刚插入的序列的最大值，在后面将这个值插入tb_cou表中。

--创建存储过程

SET serveroutput ON;

CREATE OR REPLACE PROCEDURE proce_insert_tab(error_msg OUT VARCHAR2) IS

    v_id NUMBER(9, 2);

BEGIN

    FOR cur IN (select * FROM tb_tea_cou) LOOP

       SELECT hibernate_seq.nextval INTO v_id FROM dual;

       INSERT INTO tb_tea(tid, tname, zc) VALUES (

              v_id, cur.name, cur.zc);

       INSERT INTO tb_cou(cid, course, tea_id) VALUES (

              hibernate_seq.nextval, cur.course, v_id);

       COMMIT;

       error_msg:='添加成功';

    END LOOP;

EXCEPTION

    WHEN OTHERS THEN ROLLBACK;

    error_msg:='添加失败';

    RAISE_APPLICATION_ERROR(-20010, 'ERROR：插入数据有误！');

END;

/

 

--调用存储过程

var error_msg VARCHAR2(200);

exec proce_insert_tab(:error_msg);  

 

 

SELECT * FROM tb_tea;

SELECT * FROM tb_cou;

SELECT t.tid, t.tname, t.zc, c.course FROM tb_tea t, tb_cou c WHERE t.tid=c.tea_id

delete from tb_tea;

delete from tb_cou;