Can Oracle INSERT into multiple values! in the end?

A little familiar with Oracle knows that if we want an SQL statement to insert multiple values into a table, if insert into a table values (each value), values (values),.....; This will cause an error because Oracle does not support this notation if multiple inserts into Valuees (each value); It is not possible to separate execution, and Oracle is not supported.

However, both of these methods are supported by MySQL.

SQL Server also does not support both of these formulations.

        However, Sometimes we are in the process of development, if it is used in the Oracle database, sometimes it is necessary to this insert into   a table   a one-time ability to insert multiple values such a function, but Oracle does not support the above two ways what do we do? Yes, maybe you're smart. Think of several methods, such as stored procedures, or insert into  a table SELECT * from  a table, etc., but declare that we do not discuss these methods here, and have some limitations, and sometimes I want to insert a specified number of values, and a SQL statement completed, stored procedures, the last resort, it is recommended that you do not use, of course, depending on the situation, you can read this blog stored procedures, the advantages and disadvantages, the analysis is very detailed.

So is there any other way?? The answer is yes, perhaps familiar with the Oracle database may know from the Oracle9i version later, you can use a method namely: "INSERT all into a table values (each value) into a table values (other values) into a table values (other values) .... And then a SELECT statement ". Behind the SELECT statement we can look up from the virtual table as select 1 from DUAL. Note that the following SELECT statement can be arbitrary, but not the contents of the select out into the front table, but up to the front of the number of data each time inserting how many rows, this number of rows is and behind the SELECT statement to find out a few of the fixed, such as the following with the Select Statement found 15 records, then the front of the "insert all into a table values (each value 1) into a table values (other values 2) into a table values (other values 3)" will first insert the value 1 corresponding fields inserted 15 Record, then insert the value 2 for each corresponding field 15 records, and then insert the value 3 corresponding to each field 15 records.

We want to bulk insert multiple values such a record, so the back of the SELECT statement as long as can find a record on the line, it is recommended to use the back with SELECT 1 from DUAL.

See below an example, at a glance, a look will be used:

OK, we implemented a useless stored procedure with an SQL statement that completes inserting multiple specified values into the Oracle table. Perhaps you will ask what is the use of this, I tell you, if you use it in a certain situation, the role is big, to avoid writing complex and unnecessary to write the stored procedures, to avoid the execution of multiple SQL statements connected to the database multiple times the cost, when you know to learn, encountered problems in the time will understand, but I do not, Knowledge of the database is not deep, if you think that there is no reason or you have a better idea or method, very welcome message exchange, so that you, me, and everyone, common learning and growth!

reprint Please indicate-java my life (Chen Leixing) Original source: http://blog.csdn.net/chenleixing/article/details/44708533

Finally, seriously read the netizens, the great gods, if there is a feeling I this program ape has a place to say wrong or wrong or you have a good proposal or suggestions or ideas, but also look You kindness alms n seconds to leave your valuable text (message), so that you, I, and the vast number of programs apes grow and Progress faster ....

Can Oracle INSERT into multiple values! in the end?