Oracle Pro * C dynamic SQL technology

Pro * C has three common dynamic SQL technologies:

1. It is used to process dynamic SQL statements that do not contain host variables and cannot be used for select statements.
2. It is used to process the dynamic SQL statements with the number and type of input host variables. It cannot be used in select statements.
3. This method is used to process the dynamic SQL statements with the selected list items and the number of input host variables determined. This method can be used to process all the situations that can be handled by the first two methods. In addition, it can also process select statements.

Since 3rd methods already include the processing range of the first two methods, this article mainly introduces 3rd methods.

1 Introduction:

1.1 applicable statement:

• DML (data manipulation language)
• DDL (Data Definition Language)
• DCL (Data Control Language)
• And transaction control statements
• You can also process select statements.

The SQL statement is used as an example:

• Delete from tbl_name where name = xxx
• Create Table tbl_name (cola int)
• Grant select on XX to xxx
• Commit
• Insert into tbl_name values (:)
• Delete from tbl_name where name =:
• Select name from tbl_name where no = xxx

1.2 statements that cannot be processed:

Generally, the object name and column name cannot use host variables. For example:

• Insert into: A values (: B)
• Select: A,: B from tbl_name where name =: c

2. Procedure:

2.1 prepare statement:

The prepare command is used to understand and parse SQL statements. The syntax is as follows:

Exec SQL prepare statement_name
From {: host_string | string_literal };

Note:

• Statement_name: Pre-compiler identifier.
• Host_string: host variable that contains SQL statements.
• String_literal: SQL statement text string.

2.2 declare cursor:

After preparing SQL statements using prepare, execute the embedded declare command to define the cursor. The syntax is as follows:

Exec SQL declare cursor_name cursor
For statement_name;

Note:

• Cursor_name: cursor name.
• Statement_name: The statement identifier used in the prepare process.

2.3 open cursor:

When the cursor starts, the SQL statement corresponding to the cursor is executed. The syntax is as follows:

Exec SQL open cursor_name [using host_variable_list];

Note:

• Cursor_name: name of the cursor.
• Host_variable_list: List of input host variables.

When an open command is executed, if the SQL statement is not a SELECT statement, the statement is executed directly, and the cursor can be closed later. If the SQL statement is a SELECT statement, the query results will be placed in the cursor result set. You must use the embedded fetch statement to extract and process the cursor result set, and then close the cursor.

2.4 fetch extract cursor data:

When the SQL statement is a SELECT statement, the results will be stored in the cursor result set after the open statement. to process the query results, you need to use fetch to extract data. The syntax is as follows:

Exec SQL fetch cursor_name into host_variable_list;

Note:

Cursor_name: The cursor name.

Host_variable_list: List of input host variables.

2.5 close the cursor:

After the cursor data is extracted and processed, close the cursor. The syntax is as follows:

Exec SQL close cursor_name;

3. Sample Code:

/* Large group member table structure */
Struct grpmember
{
Char str_bgid [22];/* large group ID */
Char str_matid [22];/* Material ID */
...
...
};

/**
* Material Retrieval
* Enter an input structure and call it as a function parameter.
*
* Each member item in the input structure can be blank,
* Determine the database search conditions, that is, the conditions after the SELECT statement.
*
*/
Void matsearch (struct grpmember * in)
{
/* Define temporary variables */
Char Buf [256];/* buffer */

/* Define the host variable */
Exec SQL begin declare section;
Char SQL _buf [1024];/* store SQL statements */
Struct grpmember ret;/* query result */
Exec SQL end declare section;

/**
* Construct an SQL statement based on parameters
*
* 1. Copy the First Half of the SQL statement to the buffer.
* 2. Check whether the member items in the input parameter structure are empty. If not, add them to the buffer.
*/
Strcpy (SQL _buf, "select * From tbl_grpmember where 1 = 1 ");
If (! Isempty (str_bgid ))
{
Sprintf (BUF, "And tbl_str_bgid = % s", str_bgid );
Strcat (SQL _buf, Buf );
}
...
...

/* Prepare dynamic SQL statements */
Exec SQL prepare SQL _stmt from: SQL _buf;

/* Define the cursor */
Exec SQL declare C1 cursor for SQL _stmt;

/* Open the cursor: Execute the query */
Exec SQL open C1;

/* Extract query results */
While (1)
{
Exec SQL fetch C1 into ret;/* the pseudo code here should be separated by commas (,). For details, see the usage of the cursor */
}

Exec SQL close C1;
}