Oracle Cursor有多个select并插入mutliple表 [英] Oracle Cursor with multiple select and insert into mutliple tables
问题描述
我想写一个查询。
为此我决定使用光标。
以下是我的标准
首先 - 我必须从一个表中选择一些记录,它应该是n个记录
例如从table1中选择col1,col2
秒-i必须从另一张桌子中选择maxid
例如从table2中选择max(id)
第三 - 我必须使用table2中的max(id)从table1逐个插入另一个table3,我必须在table3中插入每个记录
例如插入table3(id,cl1,cl2,cl3,cl4)值(max(id),xyz,xyz,xyz)
插入table4(id,cl5,cl6,cl7) ,cl8)值(max(id),abc,abc,abc)
请帮我这个
我应该使用光标或任何其他
根据您的输入,您的代码将如下所示..
< pre lang =sql> BEGIN
// 添加相应的 WHERE 条件
SELECT MAX(id) INTO maxId < span class =code-keyword> FROM table2;
// 添加相应的 WHERE 条件 in 下面提到的SELECT 语句
FOR i IN ( SELECT col1,col2 FROM table1)
LOOP
INSERT INTO table3 VALUES (maxId,i.col1,i.col2);
END LOOP;
END ;
请注意,你需要做异常处理。
I want to write one query .
for this i decided to use cursor.
following are my criteria for this
first- i have to select some records from one table it should be n number of records
e.g select col1,col2 from table1
second -i have to select maxid from another table
e.g select max(id) from table2
third - i have to insert one by one records from table1 to another table3 using max(id) from table2 for each records which i have to insert in table3
e.g insert into table3(id,cl1,cl2,cl3,cl4) values (max(id),xyz,xyz,xyz)
insert into table4(id,cl5,cl6,cl7,cl8) values (max(id),abc,abc,abc)
please help me on this
what should i use cursor or any other
Based on your inputs your code will be like as mentioned below..
BEGIN // Add appropriate WHERE Condition SELECT MAX(id) INTO maxId FROM table2; // Add appropriate WHERE Condition in SELECT statement mentioned below FOR i IN (SELECT col1,col2 FROM table1) LOOP INSERT INTO table3 VALUES (maxId, i.col1, i.col2); END LOOP; END;
Please note , You need to do exception handling separately.
这篇关于Oracle Cursor有多个select并插入mutliple表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!