Ado|web Five, StoredProcedure
Before I discuss StoredProcedure, I also want to explain the role of the Command object's Execute method, which generally uses the command's Execute method for three purposes. 1, for some simple processing, such as the deletion of a record:
Comm.commandtype=adcmdtext
Comm.commandtext= "Delect from employee Where job_id=1"
Comm.execute
This kind of work doesn't need to return anything. 2, for a number of complex processing, such as a Transact design, which is generally working with StoredProcedure, and has output parameters and input parameters, which is also our discussion topic in this chapter. 3. Used to return a Recordset object for other processing, for example:
Comm.commandtype=adcmdtext
Comm.commandtext= "Delect from employee Where job_id=1"
Set Rs=comm.execute
Dim i
While not Rs. Eof
For I=0 to Rs.fileds.count-1
Response.Write Rs.fileds.item (i) .value& ","
Next
Response.Write "<br>"
Rs. MoveNext
Wend
Well, let's get back to StoredProcedure's discussion. What is StoredProcedure? It is a pre stored database execution action set, in the SQL management structure, for a database there are several parts, one is the collection of data tables, one is the collection of StoredProcedure. Combining the two can accomplish a lot of powerful functions. StoredProcedure is actually an extension of the traditional SQL statement, mainly in the input and output of parameters. Here's a general introduction to the syntax structure of storedprocedure and the problem of passing arguments to the Command object.
StoredProcedure's standard notation: (executed on SQL Server using Query Analyzer)
Create Procedure procedure_name
Define Parameter
As
SQL Structure
In the syntax structure above, procedure_name is the name of the storage structure and the name you will refer to in the command. Then the parameters that define the output and the input. Finally, a SQL structured statement. Here is an example of a storedprocedure, which requires no input parameters and no output.
Create Procedure Del_user
As
Delect from Employee Where job_id=1
What if we were to delete the specified job_id? , then we need to give this storedprocedure input parameters.
Create Procedure Del_user1
@intID int
As
Delect from Employee Where job_id = @intID
OK, here's the @intjob is an input parameter, it can accept the input value from the outside, the following is the ASP program to enter it:
Set Conn=server.createobject ("ADODB. Connection ")
Set Comm=server.createobject ("Adodb.command")
Conn. Connectionstring= "Driver={sql Server}; Server=ser; " & _
"Uid=sa;pass=;d Atabase=employee"
Conn.Open
Comm. Activeconnection=conn
Comm.commandtype=adcmdstoredproc
Comm.commandtype= "Del_user1"
"The name here is the name of the StoredProcedure that was previously defined in SQL Server."
"The following is the input of the parameter
Param=comm. CreateParameter ("ID", adint,adparaminput,4)
"The adParamInput definition here is the most important.
Param.value=1 "The value here can enter the value you want, or you can use request to get
Comm.Parameters.Append param
Comm.execute
So we can pass parameters to the StoredProcedure. Sometimes there is an output parameter in a StoredProcedure, and here is an example that returns the value of a job_id-determined fri_name
Create Procedure Get_fname
@intID int
@fName VarChar Output "description as parameters of the outputs
As
Select @fName = fri_name Where job_id = @intID
Its corresponding ASP program should also be rewritten as the following form
Set Conn=server.createobject ("ADODB. Connection ")
Set Comm=server.createobject ("Adodb.command")
Conn. Connectionstring= "Driver={sql Server}; Server=ser; " &_
"Uid=sa;psss=;d Atabase=employee"
Conn.Open
Comm. Activeconnection=conn
Comm.commandtype=adcmdstoredproc
Comm.commandtype= "Get_fname"
"The name here is the name of the StoredProcedure defined earlier."
"The following is the input of the parameter
Param=comm. CreateParameter ("ID", adint,adparaminput,4)
"The adParamInput definition here is the most important.
Param.value=2 "The value here can enter the value you want, or you can use request to get
Comm.Parameters.Append param
Param=comm. CreateParameter ("FName", advarchar,adparamoutput,255, "")
"The adParamOutput definition here is the most important. Indicates that it is an output parameter, and the default value is an empty string
Comm. Parameters.Append param
Comm.execute
Response.Write "job_id for ¶m (0) &" Employee's first name is "¶m (1)
I give you a brief introduction to the basic concepts of storedprocedure, but storedprocedure more complex, if you want to further in-depth, you must have a comprehensive understanding of the structure of SQL Server. In addition, we do not have the above Rizi experience the advantages of StoredProcedure, many people will think that is not as good as ordinary methods, in fact, in the construction of many enterprise-class applications can realize the use of StoredProcedure strong and necessary, here I give a simple example. The database of a network bank (OnLoan) has two related tables loan tables and loanhistory tables, loan tables are used to record loan information, and each loan record must be registered in the loan table after it has been registered in the Loanhistory table, Because the periodic settlement is using the Loanhistory table. You might say that's a good thing to do. Insert records into two tables with two insert INTO statements no, it's okay! But note that in this application, if a record is inserted in any of the tables and fails, the entire process must be canceled (that is, a cancellation of a transaction), then if only the two INSERT INTO statement is used, if the first statement is completed, the second statement is not complete and then a failure occurs , when the first statement produced the effect is not able to eliminate. If we define this whole process as a transaction, the transaction does not complete the end of the roll back all the impact does not meet the requirements? This can be done with the BEGIN TRANSACTION and COMMIT transaction in SQL Server, as follows:
Create StoredProcedure Insert_loan
As
Begin Transaction
Inset into Loan (Loan_id,loan_data,loan_amount)
Values (?,?,?)
Inset into Loan (loan_id,loan_data,loan_amount,loan_describle)
Values (?,?,?,?)
Commit Transaction
Well, it looks like it's no different, but it is important to note that we now handle the two insert INTO statements as a transaction, and only two inserts into the statement is completed is a whole transaction end, then it will function in this database two tables, If a failure occurs in a transaction, all effects are canceled (Roll back). Well, this is done with StoredProcedure in SQL Server. ANSI SQL is, of course, a goner. You may not be quite sure about this, but you can see the SQL Server Manual for more information.
Let's look at the last object ─recordset object, which is the most common attribute and method. The frequency we use is also the highest, and after that, I would also like to talk about some of the problems of ADO and Oracle.
