dynamicSQL1.html

Dynamic SQL/Oracle Tutorial 1

This tutorial will introduce you to Oracle's implementation of dynamic SQL. Unlike static embedded SQL statements, which are prepared (parsed and compiled as an SQL command) at precompile time, dynamic SQL statements are prepared at run time. This means that SQL statements can be built on the fly rather than being hard-coded into your program. Dynamic SQL is used when any of the following is unknown at precompile time: table names, column names, the number of host variables, and host variable datatypes. A program can include both dynamic and embedded SQL statements. Although you can do some of the same things with dynamic SQL as you can with embedded SQL, use dynamic SQL only when you cannot perform the same action with embedded SQL. That's because it is harder to program with dynamic SQL, and there is a performance penalty with using it (due to run time preparation of SQL statements).

Processing Dynamic SQL Statements

Dynamic SQL statements are not embedded. They are character strings that need to be processed. Processing a dynamic SQL statement involves three steps: preparing the statement, binding host variables to it, and finally, executing it. The binding step associates the SQL statement with its host variables so that Oracle can read and write to them. An SQL statement can easily be re-executed using different values for the host variables. If you are interested, you can find more information on how a dynamic SQL statement is processed here, and how a DBMS processes an SQL statement here. For comparison purposes, click here for information on how an embedded SQL program is compiled.

Using Dynamic SQL

There are 4 methods that you can use to define dynamic SQL statements: Method 1, Method 2, Method 3, and Method 4. A program can use more than one method. Each method has limitations that limit its use to certain kinds of SQL statements. Each method also encapsulates the capabilities of the lower-numbered methods. For example, using Method 4, you can do all the things that can be done with Methods 3, 2, and 1. Thus, higher-numbered methods have fewer limitations and more flexibility. You might ask yourself, if Method 4 is the most flexible, why not just use Method 4 all the time? The answer is that higher-numbered methods are more complicated to use. It would be overkill to use Method 4 for something that could be done with Method 1. The following table discusses the limitations of each method and the kinds of SQL statements for which each method is best suited.

Table 1. The Four Methods of Dynamic SQL

Method	Limitations	Kind of SQL Statement
1	SQL statement cannot be a query, and it cannot contain any input host variables.	Non-query with no host variables. Good for data definition statements.
2	SQL statement cannot be a query. Number of input host variables and the datatypes of the host variables must be known at precompile time.	Non-query with a known number of input host variables
3	Number of select-list items, number of input host variables, and the datatypes of the host variables must be known at precompile time.	Query with known number of select-list items and input host variables
4	None. The number of select-list items, number of input host variables, and the datatypes of the input host variables can be unknown until run time.	Query with unknown number of select-list items and input host variables. The input host variable datatypes can also be unknown.

Source: Based on Oracle Dynamic SQL. Pro*C/C++ Precompiler Programmer's Guide Release 8.1.6. 15 May 2001.

Note: If a dynamic SQL statement is to be executed more than once by Method 1, consider using Method 2 instead because Method 1 will parse the SQL statement each time it is used. Each SQL statement is parsed just once with Method 2, so it will perform better than Method 1 under this circumstance.

The table below gives example situations of when each method could be used:

Table 2. Dynamic SQL Examples

Method	Example
1	A text field that allows a user to enter a non-query SQL statement
2	Text fields that allow a user to specify insertion parameters for an insert statement
3	A pull-down list that allows a user to select a search condition for a query
4	Checkboxes that allows a user to select which columns to display in a query

Using Method 1

Before an SQL statement can be executed, the statement must be stored as a string. Here are some examples:

char    stmt[64];
VARCHAR stmt2[64];
char    *stmt3;

while(1)
{
  cout << "Enter SQL statement\n";
  gets(stmt);

  cout << "Enter another SQL statement\n";
  gets((char*)stmt2.arr);
  stmt2.len = strlen((char*)stmt2.arr);

  cout << "Here is another SQL statement\n";
  stmt3 = "INSERT INTO branch VALUES (99, 'Main', '1234 Main St.', 'Vancouver', 1234567)";
  puts(stmt3);
  . . .
}

Notes: Do NOT terminate your SQL statements with a semicolon. If VARCHAR is used, make sure that its length component is set before it is prepared.

Once stored, the SQL statement can be prepared using the EXECUTE IMMEDIATE command. This command not only prepares the statement, it also executes it. Since Method 1 does not allow input host variables, no binding step is needed. Here is the syntax for EXECUTE IMMEDIATE:

EXEC SQL EXECUTE IMMEDIATE { :host_string | string_literal };

Note: For string literals, you can use single quotes, double quotes, or no quotation marks at all.

Here are some examples of EXECUTE IMMEDIATE:

EXEC SQL EXECUTE IMMEDIATE :stmt;
EXEC SQL EXECUTE IMMEDIATE 'CREATE TABLE temp (temp_id integer not null PRIMARY KEY)';

Using Method 2

This method breaks statement preparation and execution into 2 steps: PREPARE and EXECUTE. The syntax for PREPARE is

EXEC SQL PREPARE statement_name FROM { :host_string | string_literal };

where statement_name is a label (and not a host variable or program variable) for the statement. Unlike Method 1, host_string can contain placeholders for host variables (you don't actually add the host variables to the statement until the execute step, which substitutes the placeholders with host variable values). Placeholders are not actual variables even though the syntax for specifying a placeholder is :name, which is the same syntax for specifying host variables in embedded SQL statements.

Examples of PREPARE:

VARCHAR host_string[64];
char *examCount = "SELECT driver_sin, count(exam_score) FROM exam WHERE exam_type = :typ GROUP BY driver_sin";
strcpy((char*)host_string.arr, "INSERT INTO branch VALUES (:bid, :bname, :baddr, :bcity, :bphone)");
host_string.len = strlen((char*)host_string.arr);
EXEC SQL PREPARE stmt FROM :host_string;
EXEC SQL PREPARE stmt2 FROM "INSERT INTO branch VALUES (:bid, :bname, :baddr, :bcity, :bphone)";
EXEC SQL PREPARE stmt3 FROM :examCount;

Note: Like embedded SQL, you cannot use placeholders and thus host variables for the names of database objects, such as table and column names. But unlike embedded SQL, you can still build SQL statements containing database object names supplied by the user. You just need to use string routines. For example, to update the phone number of a branch that meets a user specified search condition, you can do something like this:

int new_no;
char statement[128] = "UPDATE branch SET branch_phone = :new_no WHERE ";
char where_clause[64];
cout << "Enter new phone number: ";
new_no = atoi(gets(new_no));
cout << "Complete the where clause for this statement " << statement;
gets(where_clause);
strcat(statement, where_clause);
EXEC SQL PREPARE s FROM :statement;

Since host variables are allowed in Method 2, a binding step is needed. This occurs in the EXECUTE step. The syntax for EXECUTE is

EXEC SQL EXECUTE statement_name [ USING host_variable_list ];
where the syntax for host_variable_list is
:host_variable[:indicator] [, host_variable2[:indicator2], . . .]

Notes: The USING clause can be omitted if the prepared statement does not use any host variables. Host arrays are allowed inhost_variable_list, but all the variables must be arrays. The number of host variables must match the number of placeholders in the prepared statement even if the same placeholder appears more than once in the statement. Also, the order that host variables appear inhost_variable_list matters.

Examples of EXECUTE:

int branchID;
char branchName[20];
char branchAddr[50];
char branchCity[20];
int branchPhone;
short int branchAddr_ind;
short int branchPhone_ind;
int branchID2;
char branchName2[20];
char branchAddr2[50];
char branchCity2[20];
int branchPhone2;
short int branchAddr2_ind;
short int branchPhone2_ind;
/* get values for your host variables */
EXEC SQL EXECUTE stmt USING :branchID, :branchName, :branchAddr:branchAddr_ind, :branchCity, :branchPhone:branchPhone_ind;
EXEC SQL EXECUTE stmt USING :branchID2, :branchName2, :branchAddr2:branchAddr2_ind, :branchCity2, :branchPhone2:branchPhone2_ind;

Note: A statement can be re-executed using different host variables without needing a re-PREPARE. A re-PREPARE is only necessary when you want to associate a statement name with another statement.

Using Method 3

Method 3 is really Method 2 with querying allowed. The syntax for PREPARE and EXECUTE doesn't change. The only thing you need to know are the steps for executing a query. The basic steps are preparing the query statement, declaring the cursor, opening the cursor, fetching the data, and closing the cursor.

DECLARE

The syntax for a cursor declaration is very similar to that in embedded SQL:

EXEC SQL DECLARE cursor_name CURSOR FOR sql_statement;

Both cursor_name and sql_statement are labels and not host variables (binding doesn't happen until the open step). sql_statement must be prepared prior to the cursor declaration.

Example of DECLARE:

EXEC SQL DECLARE driver_cursor FOR stmt;

OPEN

In this step, the cursor is allocated, host variables are bound, and the query is executed.
The syntax is

EXEC SQL OPEN cursor_name [USING host_variable_list];
where the syntax for host_variable_list is
:host_variable[:indicator] [, host_variable2[:indicator2], . . .]

A cursor can be opened more than once using different host variables or different values for the same host variables.

Examples of OPEN:

char exam_type = 'L';
EXEC SQL OPEN driver_cursor USING :exam_type;
exam_type = 'D';
EXEC SQL OPEN driver_cursor USING :exam_type;

FETCH

The syntax hasn't changed from embedded SQL.

Example of FETCH:

/* like in embedded SQL, we can use host arrays */
int driver[20];
int exam_count[20];
EXEC SQL FETCH driver_cursor INTO :driver, :exam_count;

CLOSE

The syntax hasn't changed from embedded SQL.

Example of CLOSE:

EXEC SQL CLOSE driver_cursor;

Using Method 4

This method is used when any of the following is unknown until run time: the number of select-list items, the number of input host variables, and the input host variable datatypes. This is the most flexible method. It is also the most difficult one to use. The next tutorial will introduce you to the details of Method 4. Actually, there are two different versions of Method 4 in Oracle: ANSI dynamic SQL and Oracle dynamic SQL. ANSI dynamic SQL is the SQL92 standard. It supports many features not found in Oracle dynamic SQL. The next tutorial will cover only ANSI dynamic SQL.

Final Notes

When deciding on which method of dynamic SQL is best for your needs, use Table 1 as a guide. Although we have not stressed error checking and transaction handling, it is important that errors be checked and work committed. And finally, before you decide on which method of dynamic SQL to use, check if the task can be accomplished using embedded SQL.

Dynamic SQL/Oracle Tutorial 1
Last updated August 14 2001

softcream.kr

2010년 4월 2일 금요일

Dynamic SQL/Oracle Tutorial