Simple execution of Oracle dynamic SQL statements

When using ODP.. Net for Oracle programming, sometimes the SQL statements are very complex and need to be dynamically constructed query statements. There are two ways to construct dynamic SQL statements and execute the returned result set.

1. Construct SQL statements at the data access layer

For example, in the following statement, assign a complete SQL statement to commandtext, and then pass it to the database for execution to return the result set.

Loadcommand. commandtype = Commandtype. Text
Loadcommand. commandtext = "select * from users"

Dataadapter. selectcommand = loadcommand
Dataadapter. Fill (data)

Dataadapter. selectcommand = loadcommand
Dataadapter. Fill (data)

This method requires that the entire SQL construction process be placed on the dataaccess layer. The business logic changes and the modification is inconvenient. In addition, each query must be passed to a long query string of the database, the efficiency of passing parameters is not high.

2. Construct and execute dynamic SQL statements in the Stored Procedure

The following is a complete example (Deleted), where refcursor is a custom cursor type.

Procedure G_search (p_yearno In   Number ,
P_controltype In   Number ,
P_progress In   Char ,
P_departid In   Varchar2 ,
P_projectname In Nvarchar2,
C_projects out refcursor) Is
E_errinterruption exception;
V_errid Number ; -- Variable to hold the errorlog ID
V_errcode Number ; -- Variable to hold the error message code
V_errtext Varchar2 ( 512 ); -- Variable to hold the error message text
V_errproc Varchar2 ( 50 ): =   ' G_search ' ;
V_departid Varchar2 ( 16 );
V_projectname nvarchar2 ( 128 );
V_ SQL Varchar2 ( 512 );
V_where Varchar2 ( 256 );
Begin

V_ SQL: =   ' Select projectid, parentid, projectname ' ;
V_ SQL: = V_ SQL |   ' From projects ' ;
V_where: =   ' Where ' ;

-- Year
If P_yearno <   9999   Then
V_where: = V_where |   ' A. yearno = '   | P_yearno |   ' And ' ;
Else
V_where: = V_where |   ' A. yearno < '   | P_yearno |   ' And ' ;
End   If ;
-- Control category
If P_controltype =   9   Then
V_where: = V_where |   ' A. controltype <9 and ' ;
Else
V_where: = V_where |   ' A. controltype = '   | P_controltype |
' And ' ;
End   If ;
-- Progress
If P_progress <   ' Z '   Then
V_where: = V_where |   ' A. Progress = '''   | P_progress |   ''' And ' ;
Else
V_where: = V_where |   ' A. Progress < '''   | P_progress |   ''' And ' ;
End   If ;

If Trim (p_departid) <>   ' % '   Then
V_where: = V_where |   ' A. departid = '''   | P_departid |   ''' And ' ;
Else
V_where: = V_where |   ' A. departid like '''   | P_departid |
''' And ' ;
End   If ;
-- Project name
V_projectname: = Nvl (p_projectname,
' % ' );
If V_projectname <>   ' % '   Then
V_projectname: =   ' % '   | P_projectname |   ' % ' ;
End   If ;
V_where: = V_where |   ' A. projectname like '   |   ''''   | V_projectname |
''' And ' ;

V_ SQL: = V_ SQL | V_where;

Open C_projects For V_ SQL;

-- Commit;
Exception
-- An error is defined as needed.
When Others Then
-- Rollback;
V_errid: = Sqlcode;
V_errtext: = Sqlerrm;
Raise_application_error (v_errid,
V_errtext );
End G_search;

This method only needs to pass some parameters to the stored procedure and return data using the cursor. Parameter transfer efficiency is high, and the business logic is stored in the process, making adjustment easier. The key of this method is the following statement:
Open c_projects for v_ SQL;
It directly uses the cursor to open the constructed query string.

Note:

A) the SQL statement cannot end with a semicolon;
B) in SQL statements, single quotation marks are required for character and string conditions.
C). Most importantly, dynamic SQL statements must prevent SQL injection attacks. The simplest method is to allow only one keyword query and remove all spaces in the keyword. For multiple keywords, we need to separate them with spaces and then construct them.