Before taking notes, you must first sort out these conceptual items. Otherwise, there will be a strange or confusing feeling in terms of concepts.
Before taking notes, you must first sort out these conceptual items. Otherwise, there will be a strange or confusing feeling in terms of concepts.
1. Concepts, advantages, and syntax of Stored Procedures
Before taking notes, you must first sort out these conceptual items. Otherwise, there will be a strange or confusing feeling in terms of concepts.
Concept: store frequently-used or complex tasks by writing SQL statements in advance and using a specified name. If you call these SQL statements later, you only need to Execute the following using Execute/Exec.
Advantages: Of course, there are many advantages of using stored procedures. The following describes them one by one.
1. The stored procedure is only compiled when it is created. You do not need to compile it every time you execute it later. However, if you directly use SQL, you need to re-compile it every time you run it, therefore, using stored procedures can speed up database execution.
2. When complicated operations are performed on the database, the stored procedure can be encapsulated to reduce the chance of code errors. In addition, MSSQL can debug the Code itself, you can easily locate the wrong statement.
3. stored procedures can be reused to improve developers' development efficiency.
4. High Security: users with specific permissions can be set to operate stored procedures, or SQL Injection operations can be prevented to a certain extent.
Types: stored procedures are classified into three types: system stored procedures, extended stored procedures, and user-defined stored procedures.
1. system stored procedure: a stored procedure starting with SP _. It is used for various system settings, information retrieval, and related management. For example: sp_help is used to obtain information about a specified object.
2. Extended Stored Procedure: It starts with XP _ and is used to call the functions provided by the operating system. The reference content is as follows: exec master .. xp_mongoshell 'Ping 127.0.0.1'
3. Custom stored procedures:
The common format is as follows:
The Code is as follows:
Create procedure procedue_name
[@ Parameter data_type] [output]
[With] {recompile | encryption}
As
SQL _statement
Note:
Output: indicates that this parameter can be returned.
Recompile in [with] {recompile | encryption} indicates that each time this stored procedure is executed, it is re-compiled (by default, it is only compiled when it is created ).
Encryption: the content of the created stored procedure is encrypted.
TIPS: It should be noted that if we sometimes want to find the names of all the columns in the table that contain the keyword in the database, how can we find them? You can use the following statement:
The Code is as follows:
Select table_name, column_name from INFORMATION_SCHEMA.COLUMNSwhere COLUMN_NAME like '% A %'; -- view the columns whose tables contain.
However, if you want to find the name of the stored procedure with table "B" in the stored procedure, you can use the following statement to do so:
The Code is as follows:
Select routine_name, routine_definition from information_schema.routines
Where routine_definition like '% B %'
And routine_type = 'processed'
Of course, we can also use syscomments, sysobjects, and sysdepends in SQL to view specific data information, which is similar to dba_objects in oracle:
The Code is as follows:
Select * from syscomments; -- View Annotation
Select * from sysobjects; -- View database objects
Select * from sysdepends; -- View dependency
Ii. Advanced storage process
Of course, let's first explain the format and syntax rules of the stored procedure:
The Code is as follows:
Create Procedure-name (Input parameters, Output Parameters (If required) AsBegin SQL statement used in the stored procedureEnd
Here we use a common example to illustrate:
The Code is as follows:
/* Getstudentname is the name of the stored procedure */
Create PROCEDURE Getstudentname (
@ Studentid INT -- Input parameter, Studentid of the student
)
AS
BEGIN
SELECT Firstname + ''+ Lastname FROM tbl_Students WHERE studentid = @ studentid
END
Of course, the @ studentid parameter here is only an input parameter, but if you want to return a value, you need to use the out parameter. The specific implementation code is as follows:
The Code is as follows:
/*
GetstudentnameInOutputVariable is the name of the stored procedure which
Uses output variable @ Studentname to collect the student name returns by
Stored procedure
*/
Create PROCEDURE GetstudentnameInOutputVariable
(
@ Studentid INT, -- Input parameter, Studentid of the student
@ Studentname VARCHAR (200) OUT -- Out parameter declared with the help of OUT keyword
)
AS
BEGIN
SELECT @ studentname = Firstname + ''+ Lastname FROM tbl_Students WHERE studentid = @ studentid
END
From the code above, we can see the specific usage of the out parameter. But if you want to execute this code on the SQL Server, what should you do?
As a matter of fact, it is a little troublesome. If only the in parameter is used, you only need to add the name of the stored procedure after execute/exec and assign a value to the parameter; but if there are not only in parameters but also out parameters, how can this problem be solved?
The following describes the usage in detail through a specific instance:
The Code is as follows:
Alter PROCEDURE GetstudentnameInOutputVariable
(
@ Studentid INT, -- Input parameter, Studentid of the student
@ Studentname VARCHAR (200) OUT, -- Output parameter to collect the student name
@ StudentEmail VARCHAR (200) OUT -- Output Parameter to collect the student email
)
AS
BEGIN
SELECT @ studentname = Firstname + ''+ Lastname,
@ StudentEmail = email FROM tbl_Students WHERE studentid = @ studentid
END
It can be seen that the above stored procedure has three parameters, the first of which is the IN parameter, and the second is the OUT parameter. From the process subject, we can see that the first out parameter is to get the full name of the student, the second is the email.
So how can I view the results after execution on the server?
The Code is as follows:
Declare @ Studentname as nvarchar (200) -- Declare the first output parameter
Declare @ Studentemail as nvarchar (50) -- Declare the Second output parameter
Execute GetstudentnameInOutputVariable 1, @ Studentname output, @ Studentemail output
Select @ Studentname, @ Studentemail -- "select" statement to view the results
Well, the above is some knowledge about stored procedures. In the future articles, we will introduce the cursor, custom errors, and running of things. I hope that SQL will be learned step by step, solid growth.
