Stored Procedure | data | database | Website Construction One, introduction
With the gradual development of the computer network construction of the central branch of the People's Bank and the implementation of the second phase of intranet project, most of the center sub-branches have established internal networks (intranet) to provide file transfer, email, and simple Web services.
For the People's Bank Center branch, the construction of the website needs a variety of digital information such as report and account, report, analysis and other kinds of information, document processing, meeting notice and other office automation information, sound, image, video conferencing and other multimedia information. And how to make full use of the existing information resources and computer network technology, build the internal information website of People's Bank, for financial supervision and policy to give timely, accurate and diverse information services, largely inseparable from the support of the database. At the same time, the construction of the People's Bank website is a system engineering and the landmark project, it represents a line of the image, reflecting the spirit of science and technology Hing Line. Construction and maintenance of the site involved many departments, need to coordinate a lot of technical complexity, which is urgent need to use database technology to build a dynamic, interactive, efficient web site to meet the requirements of the information age.
Database stored Procedures (stored-procedure) are a mature technology for large databases such as Sybase, ORACLE, INFORMIX, MICROSOFT SQL Server, and so on. A encapsulated process consisting of some or more SQL statements and control statements that resides in a database, can be invoked by a client application, or invoked from another procedure or trigger. By using variables and conditions, its arguments can be passed and returned. In b/S (browser/server) mode, the site's clients in the implementation of complex tasks, processing large and complex SQL statements, stored procedures provide a lot of convenience and advantages, to meet the needs of the site construction.
Second, the use of the advantages of stored procedures
1. Speed up the operation
In the Web site database server, a general interactive SQL command that the database server has to establish a precompiled process for each prior to execution, and after the first execution, the stored procedure is optimized and compiled, stored in the cache, and can be executed directly from the cache in the next run, Eliminates the optimization and compilation phases of subsequent execution, saving a significant amount of time to execute and using stored procedures to speed up execution.
2, Network Load reduction
When the client issues a request to execute the stored procedure, only commands that execute stored procedures are delivered on the internal network, and when they reach the database server, the stored procedure is run, and the client receives only return results or state information on the web, thus minimizing the amount of traffic to the client and the server, greatly reducing the network load. Shorten the user requirements of the corresponding time, to avoid the user boring waiting.
3. Convenient team Development
Web site programming process, PHP, ASP and other calls stored procedures can reduce the difficulty of constructing complex SQL statements in the development of the program, because the storage process can be reused, shareable, so that stored procedures can be reused in many places, can also be shared by multiple users, in the development of repeated use, It brings great convenience to the team development of the website, and makes the site easier to maintain and update.
4, security mechanism at ease
Stored procedures themselves have a strong security mechanism, only with the appropriate system permissions to invoke the appropriate stored procedures, or only to access the stored procedures and not be able to access the tables or views involved, only indirectly manipulate the database through the functions given in the stored procedure. The code of the stored procedure can include a check of the legality of information and data, a variety of integrity checks required by the business rules, and so on, which undoubtedly brings the gospel to the less secure Web site platforms.
5, service user satisfaction
Stored procedures can take full advantage of the principle of the data viewpoint set, enable users to focus on the data they care about, simplify the user's data query operations, so that different users can "view" the same data multi-angle, can use stored procedures to create very complex queries, in a very complex way to update (update, Delete, select, insert) database. While stored procedures can automate complex or sensitive transaction processing, some tables can be processed to ensure data integrity of these tables. To do so, satisfied the user.
Iii. methods of invoking stored procedures
The following is a central branch of the financial Information Port login module as an example, the actual development work of some experience shows how to invoke the stored procedure
1, website platform Server side for WinNT4.0, SERVERPACK5, iis5.0,asp application, MS SQL SERVER7.0 for the background database
2, the related table department the unit number (DEP_ID) unit name (dep_name) and password (DEP_PASSWD) are saved.
3, realize the function the user selects the user name from the Drop-down menu, enters the password, registers. If a unit user has the specified password, the output parameter returns "Pass", otherwise, returns "Invalid".
4. Example Description
(1) Stored procedure sp_checkpwd, checks whether the user entered a valid password, establishes a stored procedure, and can start isql/w from a Microsoft SQL Sever program group. Then, enter the stored procedure in the Query window.
(2) User login interface Login.asp, using the usual SQL statements and using stored procedures for comparison.
(3) Verify that the password is correctly checklogin.asp, in which the defined stored procedure is used.
(4) Database connection file conn.asp
5. Example
(1)/* Stored Procedure instance * *
CREATE PROCEDURE Sp_checkpwd
@CHKDepid VARCHAR, @CHKPass VARCHAR (30),
@ISValid CHAR (8) OUTPUT
As
IF EXISTS (SELECT dep_id from Department
WHERE dep_id= @CHKDepid and dep_passwd= @CHKPass)
Select @ISVaid = "Pass"
ELSE
Select @ISValid = "Invalid"
/* Stored procedure receives two input parameters. The input parameter @chkdepid passes a unit ID number to the stored procedure. @CHKPass Pass a unit user password to the stored procedure. If this unit ID number and password combination exist in the table, the output parameter will return "pass", otherwise, return "Invalid" * *
(2)//login.asp
User Login Interface
<!--#include file= "conn.asp"-->
' files that contain database connection definitions
<body>
<%
Dim rs,dept_sql
Set Rs=server.createobject ("Adodb.recordset")
' Create a recordset
Dept_sql= "Select Dep_id,dep_name from Department"
' Use standard SQL statements to retrieve all unit ID numbers and the name of the unit user to the client
Rs.Open dept_sql,conn,1,1
%>
<form method= "POST" action= "chklogin.asp" name= "Frmnewdepartment" >
Unit User name:
<select name= "DepartmentID" size= "1" >
<%
Do as not rs.eof
Response.Write "<option value=" +cstr (RS ("dep_id")) + "' >" +rs ("dep_name") + "</option>" +CHR (10)
Rs.movenext
Loop
%>
' Create the List object, display the unit name, return the unit ID value
</select>
Password
<input type= "Password" name= "Departmentuserpwd" >
<input type= "Submit" Name= "Submit1" value= "OK" >
<input type= "reset" name= "Submit2" value= "Rewrite" >
</form>
</body>
(3)//checklogin.asp
Verify the legality of the logged in unit user and return the value. Call the stored procedure at the same time Sp_checkpwd
<!--#include file= "conn.asp"-->
' files that contain database connection definitions
<%
Set cmdtemp=sever.createobject ("Adodb.command")
' Create a Command object
Set Cmdtemp.activeconnection=conn
' Connect commands to open connections
Cmdtemp.commandtype=adcmdstoredproc
' Specifies that a stored procedure is to be executed
cmdtemp.commandtext= "Sp_checkpwd"
' Reference stored procedure specified name
Set Tmpfirstparam =cmdtemp.createparameter
("Departmentuserpwd", advarchar,adparaminput,30)
' Create the input parameter object, the Command object's CreateParameter () method establishes the input parameter, similarly may establish the input parameter object DepartmentID, the object method carries four parameters, needs to pay attention to the difference
Set Tmpthirdparam=cmdtemp.createparameter
("RetValue", adchar,adparamoutput,8)
' Create return Parameter object
CmdTemp.Parameters.Append.tmpFirstParam
' Append the first argument to the parameter collection, and append additional parameters to the collection in turn
Cmdtemp ("DepartmentID") =request.form ("DepartmentID")
' Get the input parameters from the requested Login.asp form.
Cmdtemp ("Departmentuserpwd") =request.form ("Departmentuserpwd")
Cmdtemp.execute
' Invoke the Execute method to execute the stored procedure
% >
The Check result is <%=cmdtemp ("RetValue")% >
' Output return value, this is the simplest application, in practice can be based on the needs of the corresponding development
<%
Dataconn.close
' Close the database connection
% >
(4)//conn.asp
Database connection Definition File
<%
Dim conn
Dim connstr
Set Conn=server.createobject ("ADODB. CONNECTION ")
' Establish a Connection object
Connstr= "Driver={sql Server};server=xx.xxx.xx.xx;uid=fzxxg_u
