Do More in SQL, Less in Procedural Code

From WikiContent

Revision as of 20:40, 22 January 2010 by Albeau (Talk | contribs)
(diff) ←Older revision | Current revision (diff) | Newer revision→ (diff)
Jump to: navigation, search

<silly_pseudo_biblical_quote> In the beginning, when Codd created SQL, the world of database systems was a formless wasteland, and darkness covered the abyss, while a mighty wind swept over the waters. Then Codd said, "Let there be SELECT, INSERT, UPDATE, and DELETE". And it was good... </silly_pseudo_biblical_quote>

But if you're still writing code using only the original four DML statements, you're missing out on some very powerful, compact statements that can eliminate the need for heaps of procedural code. For example, MySQL, SQL Server, and Oracle Database all include a single statement that will either insert data into a table if the primary key value doesn't exist, or update the existing row if the key does exist. Consider, for example, a message-based order processing system where individual messages may contain information about new orders or changes to existing orders. Every time a message is consumed, a decision needs to be made as to whether a row needs to be inserted or an existing row needs to be updated. You could write this logic yourself using a procedural language such as PL/SQL or Transact SQL, but why not let the server handle it for you?

For these types of operations, Oracle Database and SQL Server include the MERGE command, while MySQL uses an additional clause on the INSERT statement (INSERT ... ON DUPLICATE KEY UPDATE ...). Gone are the days where you need to attempt to insert a row, trap a "duplicate primary key value" error, and then launch an UPDATE statement.

Another common operation involves the insertion of data into multiple tables, either into every named table or the first one to meet some condition. For Oracle users, you can handle this type of logic using PL/SQL, or you can use the INSERT ALL and INSERT FIRST statements to handle the logic entirely in SQL.

For MySQL users, there is no multi-table insert statement similar to what Oracle has, but you can use non-standard forms of the UPDATE and DELETE statements to allow you to modify multiple tables in a single statement. This is accomplished by adding a FROM clause to your DELETE statements, or by including multiple tables with join conditions in your UPDATE statements.

Whatever server you are using, take the initiative to move past the plain vanilla SQL syntax and find out how you can write compact, efficient SQL statements that eliminate the need for procedural programming.

Personal tools