Everyone knows that the program interaction interaction can improve program performance in the program.
The following is a more common example
Such as: It is necessary to determine whether the record is recorded if there is no such operation inserted.
Generally use 2 database IO, 1 time accumulated if = 0 Insert Into is actually in Oracle, or SQL Service
The large database can be used to save database IO (interaction of the database) using a batch SQL statement.
The following example is a simple demonstration to perform a database IO (demo using C # Oracle database demonstration, other languages or SQLService is similar),
Suitable for use when writing a storage process is not very meaningful.
The test code is as follows: (note that there will be excessive notes in the SQL statement used to affect performance, do not need so much notes in actual use)
The statement is relatively simple, so the table structure is not posted, and the database connection is omitted.
// Use the batch of SQL execution (but can not wrap and enter / r / N can not / N)
// C # can be used directly, VB writes such a relatively depressed, so use C # as a demonstration
const string execsql =
@ "Declare
Var_bbsitemid varchar2 (12): =: ipbbsitemid; / * Introduction parameter * /
VAR_USERID VARCHAR2 (20): =: ipuserid; / * Incoming parameters * /
Var_counts number (10);
Begin
/ * Number of records in accordance with the conditions * /
SELECT Count (T.BbsItemid) Into var_counts
From ST_BBSMRKTRSLTCHK T
Where t.userid = var_userid and t.bbsItemid = var_bbsitemid;
/ * Oracle prints don't know how to see NND * /
DBMS_OUTPUT.PUT_LINE ('count:' || var_counts);
/ * If it is 0, insert * /
IF var_counts = 0 THEN
INSERT INTO ST_BBSMRKTRSLTCHK (Bbsitemid, UserId, Readflg, LastModified)
VALUES (var_bbsitemid, var_userid, 0, sysdate);
END IF;
End; "
// auto --commit;
// must remove / r (newline) Otherwise Oracle does not recognize and will report an error.
This.cmd.commandtext = execsql.replace ("/ r", string.empty;
CMD.Parameters.clear ();
OracleParameter Par;
// Maximum parameter
PAR = cmd.Parameters.Add (": ipbbsitemid", oracletype.varchar);
Par.Value = "0012";
PAR = cmd.Parameters.Add (": ipuserid", oracletype.varchar);
Par.Value = "DEF";
Try
{
// If the execution batch is returned, it is 1 if you want to get a real update, you have to use ORACLEPARAMETER.
Int retURNVAR = cmd.executenonquery ();
Messagebox.show ("Perform success");
}
Catch (Exception EX)
{
Messagebox.show (ex.totring ());
}
}
Finish
