Simply put, a stored procedure is a collection of one or more SQL statements, which can be regarded as a batch file, but its role is not limited to batch processing. This article mainly introduces the use of variables, the creation, calling, viewing, modifying and deleting operations of stored procedures and stored functions.
1: Overview of stored procedures
The stored procedures in SQL Server are code segments written using T_SQL. Its purpose is to easily query information from system tables, or to complete management tasks related to updating database tables and other system management tasks. The T_SQL statement is the programming interface between the SQL Server database and the application program. In many cases, some codes will be written repeatedly by developers. If the code with the same function is written every time, it will not only be cumbersome and error-prone, but also reduce the operating efficiency of the system because SQL Server executes statements one by one.
In short, a stored procedure means that in order to achieve a specific task, SQL Server writes some fixed operation statements that need to be called multiple times into program segments. These program segments are stored on the server, and the database server calls them through the program.
Advantages of stored procedures:
Stored procedures speed up system operation. Stored procedures are only compiled when they are created and do not need to be recompiled every time they are executed in the future.
Stored procedures can encapsulate complex database operations and simplify the operation process, such as updating, deleting multiple tables, etc.
It can realize modular programming, and the stored procedure can be called multiple times, providing a unified database access interface and improving the maintainability of the application.
Stored procedures can increase the security of the code. For users who cannot directly operate the objects referenced in the stored procedures, SQL Server can set the user's execution permissions for the specified stored procedures.
Stored procedures can reduce network traffic. The stored procedure code is stored directly in the database. During the communication process between the client and the server, a large amount of T_SQL code traffic will not be generated.
Disadvantages of stored procedures:
Database transplantation is inconvenient. Stored procedures depend on the database management system. The operation code encapsulated in SQL Server stored procedures cannot be directly transplanted to other database management systems.
Does not support object-oriented design, cannot encapsulate logical business in an object-oriented way, or even form a general business logic framework that can support services.
The code is poorly readable and difficult to maintain. Clustering is not supported.
2: Classification of stored procedures
1. System stored procedures
System stored procedures are stored procedures provided by the SQL Server system itself, which can be used as commands to perform various operations.
System stored procedures are mainly used to obtain information from system tables. Use system stored procedures to complete the management of the database server, provide help to system administrators, and provide convenience for users to view database objects. The system stored procedures are located in the database server, and Starting with sp_, system stored procedures are defined in system-defined and user-defined databases, and there is no need to add a database qualified name before the stored procedure when calling. For example: the sp_rename system stored procedure can modify the name of a user-created object in the current database, the sp_helptext stored procedure can display text information of rules, default values or views, and many management tasks in the SQL SERVER server are completed by executing system stored procedures. , much system information can also be obtained by executing system stored procedures.
System stored procedures are created and stored in the system database master. Some system stored procedures can only be used by system administrators, while some system stored procedures can be used by other users through authorization.
2. User stored procedures (custom stored procedures)
Custom stored procedures are a collection of T_SQL statements written by users using T_SQL statements in order to achieve a specific business requirement in the user database. Custom stored procedures can Accept input parameters, return results and information to the client, return output parameters, etc. When creating a custom stored procedure, adding "##" in front of the stored procedure name indicates that a global temporary stored procedure is created; adding "#" in front of the stored procedure indicates that a local temporary stored procedure is created. A local temporary stored procedure can only be used within the session in which it was created and will be deleted when the session ends. Both stored procedures are stored in the tempdb database.
User-defined stored procedures are divided into two categories: T_SQL and CLR
T_SQL: Stored procedures are a collection of value-saved T_SQL statements that can accept and return user-provided parameters. Stored procedures may also be returned from the database to the client application data.
CLR stored procedures refer to method stored procedures that use the Microsoft.NET Framework common language. They can accept and return parameters provided by the user. They are implemented as public static methods of classes in the .NET Framework assembly.
3. Extended stored procedures
Extended stored procedures are implemented as dynamic connections (DLL files) executed outside the SQL SERVER environment. They can be loaded into the address space where the SQL SERVER instance is running and executed. Extended stored procedures can be executed using SQL SERVER extended stored procedure API programming, extended stored procedures are identified by the prefix "xp_". For users, extended stored procedures are the same as Mandarin stored procedures and can be executed in the same method.
Three: Create a stored procedure
If you want to do your job well, you must first sharpen your tools. Prepare the data as follows:
use sample_db; --创建测试books表 create table books ( book_id int identity(1,1) primary key, book_name varchar(20), book_price float, book_auth varchar(10) ); --插入测试数据 insert into books (book_name,book_price,book_auth) values ('论语',25.6,'孔子'), ('天龙八部',25.6,'金庸'), ('雪山飞狐',32.7,'金庸'), ('平凡的世界',35.8,'路遥'), ('史记',54.8,'司马迁');
Create a stored procedure without parameters
--1.创建无参存储过程 if (exists (select * from sys.objects where name = 'getAllBooks')) drop proc proc_get_student go create procedure getAllBooks as select * from books; --调用,执行存储过程 exec getAllBooks;
Modify the stored procedure
alter procedure dbo.getAllBooks as select book_auth from books;
Delete the stored procedure
drop procedure getAllBooks;
Rename the stored procedure
sp_rename getAllBooks,proc_get_allBooks;
Create a stored procedure with parameters Stored procedures
The parameters of stored procedures are divided into two types: input parameters and output parameters
Input parameters: used to pass values into the stored procedure, similar to the value transfer in Java language or C.
Output parameters: used for meeting participation results after calling the stored procedure, similar to passing by reference in Java language.
The difference between transfer by value and transfer by reference:
Assignment to basic data types is transfer by value; assignment between reference types is transfer by reference.
Passing by value passes the actual variable value; passing by reference passes the reference address of the object.
After the value is passed, the two variables change their respective values; after the reference is passed, the two references change the state of the same object
(1) Stored procedure with one parameter
if (exists (select * from sys.objects where name = 'searchBooks')) drop proc searchBooks go create proc searchBooks(@bookID int) as --要求book_id列与输入参数相等 select * from books where book_id=@bookID; --执行searchBooks exec searchBooks 1;
(2) With 2 parameters Stored procedure
if (exists (select * from sys.objects where name = 'searchBooks1')) drop proc searchBooks1 go create proc searchBooks1( @bookID int, @bookAuth varchar(20) ) as --要求book_id和book_Auth列与输入参数相等 select * from books where book_id=@bookID and book_auth=@bookAuth; exec searchBooks1 1,'金庸';
(3) Create a stored procedure with a return value
MySQL if (exists (select * from sys.objects where name = 'getBookId')) drop proc getBookId go create proc getBookId( @bookAuth varchar(20),--输入参数,无默认值 @bookId int output --输入/输出参数 无默认值 ) as select @bookId=book_id from books where book_auth=@bookAuth --执行getBookId这个带返回值的存储过程 declare @id int --声明一个变量用来接收执行存储过程后的返回值 exec getBookId '孔子',@id output select @id as bookId;--as是给返回的列值起一个名字 if (exists (select * from sys.objects where name = 'getBookId')) drop proc getBookId go create proc getBookId( @bookAuth varchar(20),--输入参数,无默认值 @bookId int output --输入/输出参数 无默认值 ) as select @bookId=book_id from books where book_auth=@bookAuth --执行getBookId这个带返回值的存储过程 declare @id int --声明一个变量用来接收执行存储过程后的返回值 exec getBookId '孔子',@id output select @id as bookId;--as是给返回的列值起一个名字
(4) Create a stored procedure with wildcards
if (exists (select * from sys.objects where name = 'charBooks')) drop proc charBooks go create proc charBooks( @bookAuth varchar(20)='金%', @bookName varchar(20)='%' ) as select * from books where book_auth like @bookAuth and book_name like @bookName; --执行存储过程charBooks exec charBooks '孔%','论%';
(5) Encrypt the stored procedure
with encryption clause to hide the text of the stored procedure from the user. The following example creates an encryption process, uses the sp_helptext system stored procedure to obtain information about the encryption process, and then attempts to obtain information about the process directly from the syscomments table.
if (object_id('books_encryption', 'P') is not null) drop proc books_encryption go create proc books_encryption with encryption as select * from books; --执行此过程books_encryption exec books_encryption; exec sp_helptext 'books_encryption';--控制台会显示"对象 'books_encryption' 的文本已加密。"
(6). Do not cache the stored procedure
--with recompile不缓存 if (object_id('book_temp', 'P') is not null) drop proc book_temp go create proc book_temp with recompile as select * from books; go exec book_temp; exec sp_helptext 'book_temp';
(7). Create a stored procedure with cursor parameters
if (object_id('book_cursor', 'P') is not null) drop proc book_cursor go create proc book_cursor @bookCursor cursor varying output as set @bookCursor=cursor forward_only static for select book_id,book_name,book_auth from books open @bookCursor; go --调用book_cursor存储过程 declare @cur cursor, @bookID int, @bookName varchar(20), @bookAuth varchar(20); exec book_cursor @bookCursor=@cur output; fetch next from @cur into @bookID,@bookName,@bookAuth; while(@@FETCH_STATUS=0) begin fetch next from @cur into @bookID,@bookName,@bookAuth; print 'bookID:'+convert(varchar,@bookID)+' , bookName: '+ @bookName +' ,bookAuth: '+@bookAuth; end close @cur --关闭游标 DEALLOCATE @cur; --释放游标
(8). Create a paging stored procedure
if (object_id('book_page', 'P') is not null) drop proc book_page go create proc book_page( @TableName varchar(50), --表名 @ReFieldsStr varchar(200) = '*', --字段名(全部字段为*) @OrderString varchar(200), --排序字段(必须!支持多字段不用加order by) @WhereString varchar(500) =N'', --条件语句(不用加where) @PageSize int, --每页多少条记录 @PageIndex int = 1 , --指定当前为第几页 @TotalRecord int output --返回总记录数 ) as begin --处理开始点和结束点 Declare @StartRecord int; Declare @EndRecord int; Declare @TotalCountSql nvarchar(500); Declare @SqlString nvarchar(2000); set @StartRecord = (@PageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @PageSize - 1 SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句 SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句 -- IF (@WhereString! = '' or @WhereString!=null) BEGIN SET @TotalCountSql=@TotalCountSql + ' where '+ @WhereString; SET @SqlString =@SqlString+ ' where '+ @WhereString; END --第一次执行得到 --IF(@TotalRecord is null) -- BEGIN EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数 -- END ----执行主语句 set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord)); Exec(@SqlString) END --调用分页存储过程book_page exec book_page 'books','*','book_id','',3,1,0; -- declare @totalCount int exec book_page 'books','*','book_id','',3,1,@totalCount output; select @totalCount as totalCount;--总记录数。