我有表 A 列如下所示
> id, name, salary
>
> Max+1(10) ,'aa',100 max+1(11), 'bb',200 . .like that i have 10 record
> is there . Max+(21),'xx',1000
现在插入 10 行没有任何问题，它很好。
第二张表如下
p_id,age,id
p_id max+1 (41) ,30,(here i need to insert same id from A table 1st record(Max+1(10)))
p_id max+1 (42) ,31,(here i need to insert same id from A table 2nd record(Max+1(11)))
p_id max+1 (43) ,32,(here i need to insert same id from A table 3rd record(Max+1(12)))
.
.
.
.
同样，我需要在循环中插入 10 行。谢谢 。
使用存储过程而不获取MAX()+1值，使用序列：
Oracle 11g R2 架构设置：
CREATE TABLE table1 (
id NUMBER(20,0) PRIMARY KEY,
name VARCHAR2(200),
salary NUMBER(12,2)
)
/
CREATE TABLE table2 (
p_id NUMBER(20,0) PRIMARY KEY,
age NUMBER(3,0),
id NUMBER(20,0) REFERENCES table1 (id)
)
/
CREATE SEQUENCE table1__id__seq
/
CREATE SEQUENCE table2__p_id__seq
/
CREATE PROCEDURE add_Details(
in_name TABLE1.NAME%TYPE,
in_salary TABLE1.SALARY%TYPE,
in_age TABLE2.AGE%TYPE
)
IS
p_id TABLE1.ID%TYPE;
BEGIN
INSERT INTO table1 ( id, name, salary )
VALUES ( table1__id__seq.NEXTVAL, in_name, in_salary )
RETURNING id INTO p_id;
INSERT INTO table2 ( p_id, age, id )
VALUES ( table2__p_id__seq.NEXTVAL, in_age, p_id );
END;
/
查询 1：
BEGIN
add_Details( 'aa', 100, 30 );
add_Details( 'bb', 200, 31 );
add_Details( 'cc', 300, 32 );
END;
查询 2：
SELECT * FROM table1
结果：
| ID | NAME | SALARY |
|----|------|--------|
| 1 | aa | 100 |
| 2 | bb | 200 |
| 3 | cc | 300 |
查询 3：
SELECT * FROM table2
结果：
| P_ID | AGE | ID |
|------|-----|----|
| 1 | 30 | 1 |
| 2 | 31 | 2 |
| 3 | 32 | 3 |
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句