How to write a database stored procedure and how to create a database stored procedure

SQL Server syntax:

create procedure proc_name@para1 int)

assql-statement;

MySQL syntax:

create procedure proc_namepara1 int)

sql-statement;

The above para1 is a parameter, and the parentheses can be omitted if you don't need it.

sql-statement is your stored procedure.

The statement to be executed, if there is any doubt that can be said.

sqlserver2014

First of all, let's take a look at the customers table structure as shown in the figure below, and then the stored procedure to be created will operate this good table.

We click on the programmability option under the database, right-click on the stored procedure below it, and select the New Stored Procedure option, as shown in the image below.

In the pop-up Create Stored Procedure page, we use the Create Procedure statement to create a new stored procedure, which receives a parameter in the middle register, and then returns the record of the customername containing this parameter in the customers table, as shown in the following figure.

Finally, after you have built the stored procedure, you can see the name of the stored procedure you created in the stored procedure column, as shown in the following figure.

Complex operations are simplified by encapsulating the processing in an easily adaptable unit.

Since there is no need to repeatedly establish a series of processing steps, the consistency of the data is guaranteed, and if all developers and applications use the same stored procedure, the ** used will be the same.

And by extension, error prevention, the more steps you have to take, the more likely you are to make mistakes, and error prevention ensures that your data is consistent.

Simplify the management of changes If there is a change in the table name, column name, or business logic (or something else), only the stored procedure needs to be changed, and the people who use it don't even need to know about the changes.

An extension of this is security: restricting access to the underlying data through stored procedures reduces the chance of data corruption (data fraud caused by unintentional or other reasons).

Because stored procedures are typically stored in compiled form, the DBMS does less work to process commands, resulting in improved performance.

There are some SQL elements and features that can only be used in a single request, and stored procedures can use them to write more powerful and flexible programs.

In conclusion, there are three main benefits to using stored procedures, namely: simplicity, security, and high performance.

create procedure [Owner.] ]Stored procedure name[; Program Number].

Parameter 1,...parameters

withrecompile | encryption | recompile, encryption}

for replication]

as program line.

The stored procedure name cannot exceed 128 words. A maximum of 1024 parameters can be set for each stored procedure.

SQL Server or later), the parameters are used as follows:

Parameter name: data type [varying] [internal value] [output]<>