SQL Simple stored procedure sharing

Many programmers friends see SQL as the Flood Lake Beast, in fact, in-depth analysis, more time and patience, SQL is understandable.

This article is mainly for new contact with the new friend of SQL, a simple SQL stored procedure to share.

Boy first published articles, but also for reference to the crystallization of predecessors, the purpose of this article is to help more people want to learn the database.

Article start

The first thing to know is what is a stored procedure?

A stored procedure is one or more SQL commands that are stored as executable objects in the database.
In layman's terms: a stored procedure is actually a set of SQL statements that can perform certain operations.

Advantages of stored procedures:
1. The stored procedure is compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, while the general SQL statements are compiled once per execution, so using stored procedures can increase the speed of the database execution.
2. When complex operations are performed on a database, this complex operation can be encapsulated with stored procedures and used in conjunction with the transactional processing provided by the database.
3. Stored procedures can be reused to reduce the workload of database developers.
4. High security, can be set only some users have the right to use the specified stored procedures

The specific use of stored procedures is as follows:
First, create a new table product in the local database and add some data to test it to make it easier for the reader to understand.

The following table product is used to understand stored procedures, because the goal is to understand the simple use of stored procedures, so all the examples are simple.

Table product has a total of 4 fields, respectively Proid,proname,proprice,createtime

First, we introduce a stored procedure with no parameters:
Select all the information in the Product table,

Create proc Proproc--proc is the keyword (proc equals procedure) Proproc is a custom stored procedure name
As--here as cannot be omitted and not written
Begin--begin and end in pairs appear, can not write, but not only one

Select Proid,proname,proprice,createtime
End
Go

--Now execute the stored procedure created by this statement Proproc

EXEC Proproc--exec Executes the stored procedure keyword PROPROC is the name of the stored procedure to execute

Execution results are as follows

Here, the stored procedure Proproc has been saved in the database, and the following will modify the stored procedure proproc.

There are parameter stored procedures:
Global variables
A global variable, also known as an external variable, is defined outside the function and is scoped to the end of the program file, starting at the definition of the variable.
Select the product information for the specified product name:

ALTER proc Proproc--alter to modify a stored procedure keyword
@proName varchar (50)
As
Begin
Select Proid,proname,proprice,createtime from product where [email protected]
Go

exec proproc ' bread '--' bread ' is used as a parameter to the stored procedure, and the stored procedure returns the result based on the parameters passed in by the user.

The above is an external assignment to the variable, or you can set the default value directly to the variable internally

ALTER PROC Proproc
@proName varchar (50) = ' bread '
As
Begin
Select Proid,proname,proprice,createtime from product where [email protected]
End
Go

EXEC Proproc--because parameters are internally determined, executing stored procedures no longer requires incoming parameters

The results are as follows:

You can also export the value of a variable, using output-similar to out in C #

ALTER PROC Proproc
@proName varchar (50),

@bool int output//outgoing parameters
As
If exists (select Proid,proname,proprice,createtime from product where [email protected])
Set @bool =1
Else
Set @bool =0
Go

Purpose: If the table has a product ' cola ' then show 1 otherwise show 0
DECLARE @bool INT-- declare first

exec proproc ' cola ' , @bool output

Select @bool

The results are as follows

These are global variables, and the following are local variables.
Local variables are also known as internal variables. A local variable is defined within a function as a description. The scope is limited to the inside of the function, and it is illegal to use the variable after leaving the function.
Definition of a local variable: must be declared with declare before it can be used, declare{@ variable name data type}
Assignment method for local variables: set{@ variable name = expression} or select{@ variable name = expression}
Display of local variables: SELECT @ variable Name

ALTER PROC Proproc
As
DECLARE @proName varchar (50)
Set @proName = ' towel '
Select Proid,proname,proprice,createtime from product where [email protected]

Go

EXEC Proproc

The results are as follows

Methods for displaying local variables:

ALTER PROC Proproc
As
DECLARE @proName varchar (50)
Set @proName = (select Product.proname from product where proid=01)

Select @proname
Go

EXEC Stuproc

The results are as follows

This article simply introduces the SQL stored procedures, I will continue to learn, if there is income, will continue to share.

If this article is helpful to you, I hope you can share your knowledge with more people and finally.

SQL Simple stored procedure sharing