In my book OCA Oracle Database SQL Expert Exam Guide (Exam 1Z0-047), I mention the use of something called “dynamic SQL” on page 391. I’ve since received some email asking for more information about it. The concept I was referring to with the phrase “dynamic SQL” is simple: write software code to create SQL statements in real time, based on the logic within the script. A typical use in many websites today is to create some sort of code using PHP or Perl to display an HTML page, ask the user questions, and then issue a custom-written SQL statement to interact with the database accordingly. An example might be to create an HTML page that displays a form:
<html>
<head>
<title>Sample Form</title></head>
<body>
<h1>Click Display list of customers</h1>
<form id="form1" name="form1" method="post" action="test.php">
Choose order for displaying the results:
<select name=parm_OrderBy>
<option value="DESC" selected>Descending
<option value="ASC">Ascending
</select><br>
<input type=submit value='Display Results' />
</form>
</body>
</html>
The form might appear this way:
This HTML could then invoke a PHP script that looks something like this:
<? php
... receive incoming parameters ...
$vQuery = "SELECT FIRST_NAME, LAST_NAME FROM CUSTOMERS ORDER BY LAST_NAME"
if (parm_OrderBy == "DESC")
$vQuery .= " DESC";
... run query ...
?>
The idea is that the PHP script is bulding a text string in the $vQuery variable that may or may not include DESC after the ORDER BY, based on the value selected in the HTML form.
This is obviously just a very simple example. You can use much more complex code structures to build a variety of SQL statements, including SQL statements that may or may not include joins, GROUP BY clauses, and more. It’s just a matter of building text strings based on input, nothing more.
So that’s it! Now you know what dynamic SQL is about.
Happy developing!