Database-stored procedures ., Database Stored Procedures

Database-stored procedures ., Database Stored Procedures

Stored Procedure:

Stored Procedures (Stored Procedure) are a set of SQL statements for specific functions in large database systems. They are compiled and Stored in the database, you can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters. Stored procedures are an important object in databases. Any well-designed database application should use stored procedures.

Creation of a stored procedure:

Select a stored procedure and right-click Create stored procedure. The following code is displayed.

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:<Author,,Name>-- Create date: <Create Date,,>-- Description:<Description,,>-- =============================================CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> -- Add the parameters for the stored procedure here<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;    -- Insert statements for procedure hereSELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>ENDGO

The created stored procedure:

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO -- ============================ ============ -- Author: <Author, Name> -- Create date: <Create Date,> -- Description: <Description,> -- ===================================================== ====== alter procedure [dbo]. [PROC_SettleAccount] -- Add the parameters for the stored procedure here @ Recharge numeric (), @ ReturnM numeric (), @ Income numeric (), @ UserName char (10 ), @ SetDate char (10), @ SetTime char (10) ASBEGIN -- set nocount on added to prevent extra result sets from -- interfering with SELECT statements. set nocount on; -- Insert statements for procedure hereinsert into Bill (Recharge, ReturnM, Income, UserName, SetDate, SetTime) values (@ Recharge, @ ReturnM, @ Income, @ UserName, @ SetDate, @ SetTime) update RechargeRecords set SettleAccountState = 'checkout' where UserName = @ UserName and SettleAccountState = 'uncheck' update reset set SettleAccountState = 'checkout' where UserName = @ UserName and SettleAccountState = 'not found check out 'Update Cards set SettleAccountState = 'checked out' where UserName = @ UserName and SettleAccountState = 'uncheck' END

Usage of stored procedures:

Public Function SettleAccount (bill As Entity. bill) As Boolean Implements IDAL. IBill. settleAccount Dim plain text As String plain text = "PROC_SettleAccount" 'Replace the SQL statement bill with the name of the stored procedure. setDate = Format (Now, "yyyy-MM-dd") 'to get the current date bill. setTime = Format (Now, "HH: mm: ss") 'Get current time' Add the Dim sqlparameter As SqlParameter () = {New SqlParameter ("@ Recharge", bill. recharge), New SqlParameter ("@ ReturnM", bill. returnM), New SqlParameter ("@ Income", bill. income), New SqlParameter ("@ UserName", bill. userName), New SqlParameter ("@ SetDate", bill. setDate), New SqlParameter ("@ SetTime", bill. setTime)} The parameter in the middle of Dim helper As New SqlHelper Dim flag As Boolean is changed to the special parameter flag = helper. execAddDelUpdate (plain text, CommandType. storedProcedure, sqlparameter) Return flag End Function

Replace the SQL statement with the name of the stored procedure. During execution, it must also be replaced with the special parameters of the stored procedure.

Advantages of stored procedures:

1. reuse. Stored procedures can be reused to reduce the workload of database developers. 2. improve performance. The stored procedure is compiled during creation and will not be re-translated for future use. A general SQL statement needs to be compiled every time it is executed, so the efficiency is improved by using the stored procedure. 3. reduce network traffic. The stored procedure is stored on the server. You only need to pass the name and parameters of the stored procedure when calling the procedure. This reduces the amount of data transmitted over the network. 4. Security. Parameterized stored procedures can prevent SQL injection attacks and apply Grant, Deny, and Revoke permissions to stored procedures. For details, see Baidu encyclopedia.

Conclusion: the use of stored procedures reduces the amount of code to a certain extent, and attempts to use things that have never been used can bring a sense of accomplishment.

What is the role of stored procedures in databases?

First, the stored procedure runs faster because the SQL statement has been pre-compiled.
2. stored procedures can accept parameters, output parameters, return one or more result sets, and return values. The error cause can be returned to the program.
Third, the stored procedure runs stably without too many errors. Once the program is successfully run, the program will run according to the program later.
Fourth, the storage process mainly runs on the server to reduce the pressure on the client.
Fifth, stored procedures can include program streams, logic, and queries to databases. Data logic can be encapsulated and hidden by entities.
6. A stored procedure can execute a series of SQL statements in a single stored procedure.
7. stored procedures can reference other stored procedures from their stored procedures, which simplifies a series of complex statements.

In fact, stored procedures can also control permissions. For example, a table does not allow direct access by users, but users are required to access and modify one or more fields, you can use a stored procedure to implement and allow the user to use the stored procedure.

In addition, if data is returned during the execution of Multiple SQL statements as the input data for subsequent steps, if the SQL statement is executed directly, this will inevitably cause a large amount of data to be returned to the client through the network and operated on the client. If it is encapsulated in the stored procedure, the operation will be placed on the server, which not only reduces the pressure on the client, it also reduces network traffic and improves execution efficiency.

How to use stored procedures in Databases

Keyword: procedure

Example:
SQL> create [or replace] procedure procedure_name is
Begin
-- Start execution
Insert into test ('20140901', 'visket ');
End;
/

The preceding operation adds an information entry to the test table.
The command for executing the Stored procedure is exec.
Remember that the statement must end with a semicolon in the stored procedure.