Can Oracle INSERT into multiple values! in the end? __oracle

A little familiarity 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 can be an error because Oracle does not support this notation if multiple inserts into valuees (individual values); It is also not possible to perform together, andOracle is not supported .

However, MySQL is supported in both ways.

SQL Server does not support either of these two types of notation.

However, sometimes when we are in the development process, if the Oracle database is used, sometimes it is very necessary to insert into a table can be inserted into a number of functions, but Oracle does not support the above two ways then what do we do. Yes, well, maybe you're smart, and you think of a few ways, such as stored procedures, or insert into a table SELECT * from a table, and so on, but to declare that we do not explore these methods, and have some limitations, sometimes I will insert a specified number of values, and a SQL statement completed, Stored procedures, the last resort, we recommend that you do not use, of course, this depends on the situation, you can read the blog stored procedures of the advantages and disadvantages of the analysis of very detailed.

So is there any other way. The answer is yes, maybe you know the Oracle database. After the oracle9i version, you can use one way: "INSERT all into a table values (values) into a table values (other values) into a table values (other values) .... next to a SELECT statement . The SELECT statement in the back we can look up from the virtual table as select 1 from DUAL. Note that the following SELECT statement is optional, but instead of inserting the contents of its selection into the front table, it is a function of how many rows are inserted into each of the preceding data, and how many rows are identified with the SELECT statement behind it, as in the following select Statement to detect 15 records, then the front of "insert all into a table values (each value 1) into a table values (other value 2) into a table values (other value 3)" will insert the value 1 corresponding fields inserted 15 Record, then insert the value 2 corresponding fields 15 records, and then insert a value 3 corresponding to each field 15 records.

We want to bulk insert multiple values such a record, so the next SELECT statement as long as you can find a record on the line , we recommend that you use SELECT 1 from DUAL.

Look at the bottom of an example, at a glance, it will be used:

OK, we did it. Stored procedures complete the insertion of multiple specified values into an Oracle table with an SQL statement. Maybe you'll ask what's the use of it, tell you, if you use it in a certain situation, the role is large, to avoid writing complex and unnecessary to write stored procedures, to avoid the execution of multiple SQL statements to connect the database cost , when you know that learned, encounter problems with the time will understand, However, I do not know much about the database, if you feel that there is no reason to say or you have a better idea or method, very welcome message exchange, so you, I, and everyone, the common learning and growth.

reprint Please specify-author: java My life (Chen Leixing) Source: http://blog.csdn.net/chenleixing/article/details/44708533 Finally, seriously read the netizens, the great God, if there is a feeling I this program ape where the wrong or wrong or you have a good proposal or suggestion or idea method, but also hope you kindness give n seconds time to leave your valuable words (message) So that you, me, and the vast majority of the program apes grow and Progress faster ....