SQL Server 기본 사항: 저장 프로시저

간단히 말하면 저장 프로시저는 하나 이상의 SQL 문을 모아 놓은 것으로 배치 파일로 간주할 수 있지만 그 역할은 배치 처리에만 국한되지 않습니다. 이 문서에서는 주로 변수의 사용, 저장 프로시저 및 저장 함수의 생성, 호출, 보기, 수정 및 삭제 작업을 소개합니다.

1: 저장 프로시저 개요

SQL Server의 저장 프로시저는 T_SQL을 사용하여 작성된 코드 세그먼트입니다. 그 목적은 시스템 테이블에서 정보를 쉽게 쿼리하거나 데이터베이스 테이블 업데이트 및 기타 시스템 관리 작업과 관련된 관리 작업을 완료하는 것입니다. T_SQL 문은 SQL Server 데이터베이스와 응용 프로그램 간의 프로그래밍 인터페이스입니다. 대부분의 경우 개발자가 동일한 기능의 코드를 반복적으로 작성하게 되면 번거롭고 오류가 발생하기 쉬울 뿐만 아니라 SQL Server에서 명령문을 실행하므로 시스템의 운영 효율성이 저하됩니다. 하나씩.

간단히 말하면 저장 프로시저는 SQL Server가 특정 작업을 수행하기 위해 프로그램 세그먼트에 여러 번 호출해야 하는 일부 고정된 작업 문을 작성하는 경우입니다. 이러한 프로그램 세그먼트는 서버와 데이터베이스에 저장됩니다. 서버는 호출할 프로그램을 전달합니다.

저장 프로시저의 장점:

저장 프로시저는 시스템 작동 속도를 높여줍니다. 저장 프로시저는 생성될 때만 컴파일되며 실행될 때마다 다시 컴파일할 필요가 없습니다.

저장 프로시저는 복잡한 데이터베이스 작업을 캡슐화하고 여러 테이블 업데이트 및 삭제와 같은 작업 프로세스를 단순화할 수 있습니다.

모듈식 프로그래밍을 실현할 수 있으며 저장 프로시저를 여러 번 호출할 수 있어 통합된 데이터베이스 액세스 인터페이스를 제공하고 애플리케이션의 유지 관리 가능성을 향상시킵니다.

저장 프로시저는 코드의 보안을 강화할 수 있습니다. 저장 프로시저에서 참조하는 개체를 직접 조작할 수 없는 사용자를 위해 SQL Server에서는 지정된 저장 프로시저에 대한 사용자의 실행 권한을 설정할 수 있습니다.

저장 프로시저는 네트워크 트래픽을 줄일 수 있습니다. 저장 프로시저 코드는 클라이언트와 서버 간의 통신 프로세스 중에 대량의 T_SQL 코드 트래픽이 생성되지 않습니다.

저장 프로시저의 단점:

데이터베이스 이식이 불편합니다. 저장 프로시저는 데이터베이스 관리 시스템에 따라 달라지며, SQL Server 저장 프로시저에 캡슐화된 연산 코드는 다른 데이터베이스 관리 시스템에 직접 이식할 수 없습니다.

객체 지향 설계를 지원하지 않으며, 논리적 비즈니스를 객체 지향 방식으로 캡슐화할 수 없으며, 심지어 서비스를 지원할 수 있는 일반적인 비즈니스 로직 프레임워크를 형성할 수도 없습니다.

코드가 가독성이 낮고 가독성이 낮습니다. 유지 관리가 어렵습니다. 클러스터링은 지원되지 않습니다.

2: 저장 프로시저 분류

1. 시스템 저장 프로시저

시스템 저장 프로시저는 SQL Server 시스템 자체에서 제공하는 저장 프로시저로, 명령으로 사용할 수 있다. 다양한 작업을 수행합니다.

시스템 저장 프로시저는 주로 시스템 테이블에서 정보를 얻는 데 사용됩니다. 시스템 저장 프로시저를 사용하여 데이터베이스 서버 관리를 완료하고, 시스템 관리자에게 도움을 제공하고, 사용자가 데이터베이스 개체를 쉽게 볼 수 있도록 합니다. 데이터베이스에 위치하며 sp_로 시작하는 시스템 저장 프로시저는 시스템 정의 및 사용자 정의 데이터베이스에 정의되므로 호출 시 저장 프로시저 앞에 데이터베이스 한정 이름을 추가할 필요가 없습니다. 예를 들어, sp_rename 시스템 저장 프로시저는 현재 데이터베이스에서 사용자가 생성한 개체의 이름을 수정할 수 있고, sp_helptext 저장 프로시저는 SQL SERVER 서버의 규칙, 기본값 또는 뷰 및 많은 관리 작업에 대한 텍스트 정보를 표시할 수 있습니다. 시스템 저장 프로시저를 실행하면 많은 시스템 정보를 얻을 수 있습니다.

시스템 저장 프로시저는 시스템 데이터베이스 마스터에 생성되어 저장됩니다. 일부 시스템 저장 프로시저는 시스템 관리자만 사용할 수 있는 반면, 일부 시스템 저장 프로시저는 인증을 통해 다른 사용자가 사용할 수 있습니다.

2. 사용자 저장 프로시저(사용자 정의 저장 프로시저)

사용자 정의 저장 프로시저는 특정 비즈니스 요구 사항을 달성하기 위해 사용자가 T_SQL 문을 사용하여 작성하고 사용자 데이터베이스에 작성한 T_SQL입니다. , 사용자 정의 저장 프로시저는 입력 매개변수를 허용하고, 결과와 정보를 클라이언트에 반환하고, 출력 매개변수 등을 반환할 수 있습니다. 사용자 지정 저장 프로시저를 생성할 때 저장 프로시저 이름 앞에 "##"을 추가하면 전역 임시 저장 프로시저가 생성됨을 의미하고, 저장 프로시저 앞에 "#"을 추가하면 로컬 임시 저장 프로시저가 생성됨을 의미합니다. 로컬 임시 저장 프로시저는 해당 프로시저가 생성된 세션 내에서만 사용할 수 있으며 세션이 끝나면 삭제됩니다. 두 저장 프로시저 모두 tempdb 데이터베이스에 저장됩니다.

사용자 정의 저장 프로시저는 T_SQL과 CLR의 두 가지 범주로 나뉩니다.

T_SQL: 저장 프로시저는 사용자가 제공한 매개 변수를 허용하고 반환할 수 있는 값을 절약하는 T_SQL 문 모음입니다. 프로시저는 데이터베이스에서 클라이언트 응용 프로그램으로 데이터를 반환할 수도 있습니다.

CLR 저장 프로시저는 Microsoft.NET Framework의 공용 언어를 참조하는 메서드 저장 프로시저를 의미합니다. 이는 사용자가 제공한 매개 변수를 받아들이고 반환할 수 있으며 .NET에서 클래스의 공용 정적 메서드로 구현됩니다. 프레임워크 조립.

3. 확장 저장 프로시저

확장 저장 프로시저는 SQL SERVER 환경 외부에서 실행되는 동적 연결(DLL 파일)로 구현되며 SQL SERVER 인스턴스가 실행되는 주소 공간에 로드될 수 있습니다. 실행하려면 SQL SERVER 확장 저장 프로시저 API를 사용하여 확장 저장 프로시저를 프로그래밍할 수 있습니다. 같은 방법.

3: 저장 프로시저 만들기

작업을 잘 수행하려면 먼저 도구를 다음과 같이 준비해야 합니다.

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,'司马迁');

매개변수 없는 저장 프로시저 만들기

--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;

저장 프로시저

alter procedure dbo.getAllBooks 
as
select book_auth from books;

저장 프로시저 삭제

drop procedure getAllBooks;

저장 프로시저 이름 바꾸기

sp_rename getAllBooks,proc_get_allBooks;

매개변수가 있는 저장 프로시저 만들기
두 가지 유형의 매개변수가 있습니다. 저장 프로시저: 입력 매개변수 및 출력 매개변수

입력 매개변수: Java 언어 또는 C에서 값을 전달하는 것과 유사하게 저장 프로시저에 값을 전달하는 데 사용됩니다.

출력 매개변수: Java 언어의 참조 전달과 유사하게 저장 프로시저 호출 후 회의 참여 결과에 사용됩니다.

값 전송과 참조 전송의 차이점:

기본 데이터 유형에 대한 할당은 값에 의한 할당이고, 참조 유형 간의 할당은 참조에 의한 전송입니다.
값 전달은 실제 변수 값을 전달하고 참조 전달은 객체의 참조 주소를 전달합니다.
값이 전달된 후 두 변수는 각각의 값을 변경하고, 참조가 전달된 후 두 참조는 동일한 객체의 상태를 변경합니다.

(1) 하나의 매개변수가 있는 저장 프로시저

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) 매개변수가 2개인 저장 프로시저

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) 반환 값이 있는 저장 프로시저 생성

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) 와일드카드 프로시저로 스토리지 생성

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) 암호화 절이 있는 암호화 저장 프로시저

는 저장 프로시저의 텍스트를 사용자에게 숨깁니다. 다음 예에서는 암호화 프로세스를 생성하고 sp_helptext 시스템 저장 프로시저를 사용하여 정보를 얻습니다. 정보를 가져온 다음 syscomments 테이블에서 직접 프로세스에 대한 정보를 가져오십시오.

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) 저장 프로시저를 캐시하지 마세요

--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) 커서 저장 프로시저

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). 페이징 저장 프로시저

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;--总记录数。

를 생성합니다.