SQL 요약 저장 프로시저

개념

저장 프로시저: 실행 가능한 프로시저로 미리 컴파일된 하나 이상의 SQL 문입니다.

저장 프로시저 구문 생성

CREATE proc | procedure procedure_name
    [{@参数数据类型} [=默认值] [output],
     {@参数数据类型} [=默认值] [output],
     ....
    ]
as
    SQL_statements
go

저장 프로시저와 SQL 문 비교

장점:

성능 향상
SQL 문은 프로세스 중 분석 및 컴파일이 수행될 때 생성됩니다. 저장 프로시저는 저장 프로시저가 처음 실행될 때 이를 분석 및 최적화하고 최종적으로 시스템 테이블에 저장되는 저장 계획을 제공합니다. 이렇게 하면 프로시저 실행 시 이러한 오버헤드를 줄일 수 있습니다. .
2. 네트워크 오버헤드 감소
저장 프로시저 호출 시 저장 프로시저 이름과 필요한 매개변수 정보만 제공하면 네트워크 트래픽을 줄일 수 있습니다.
3. 코드 이식 촉진
데이터베이스 전문가는 언제든지 저장 프로시저를 수정할 수 있지만 애플리케이션 소스 코드에는 영향을 미치지 않으므로 프로그램의 이식성이 크게 향상됩니다.
4. 강화된 보안
1) 시스템 관리자는 실행 중인 저장 프로세스에 대한 권한을 제한하여 승인되지 않은 사용자가 데이터에 접근하는 것을 방지할 수 있습니다.
2) 네트워크를 통해 프로세스를 호출할 경우 실행 절차에 대한 호출만 표시됩니다. . 따라서 악의적인 사용자는 테이블 및 데이터베이스 개체 이름을 볼 수 없고 자신의 Transact-SQL 문을 포함할 수 없으며 중요한 데이터를 검색할 수도 없습니다.
3) 프로시저 매개변수를 사용하면 SQL 주입 공격을 방지하는 데 도움이 됩니다. 매개 변수 입력은 실행 코드가 아닌 리터럴 값으로 처리되므로 공격자가 프로시저 내의 Transact-SQL 문에 명령을 삽입하고 보안을 손상시키기가 더 어렵습니다.
4) 프로세스를 암호화할 수 있어 소스 코드를 난독화하는 데 도움이 됩니다.

단점:

1. 저장 프로시저는 유지 관리를 위해 전담 데이터베이스 개발자가 필요하지만, 실제 상황은 프로그램 개발자가 아르바이트를 하는 경우가 많습니다

2. 저장 프로시저 수정은 SQL만큼 유연하지 않습니다

실제 애플리케이션에서 저장 프로시저가 상대적으로 거의 사용되지 않는 이유는 무엇입니까?

일반적인 프로젝트 개발에서는 저장 프로시저가 상대적으로 거의 사용되지 않는 이유는 무엇입니까?
분석 이유는 다음과 같습니다.
1) 특정 데이터베이스 개발자가 없으며, 일반 프로그래머가 데이터베이스 작업을 파트타임으로 수행합니다.
2) 프로그래머는 별도의 준비 없이 데이터 액세스를 완료하기 위해 프로그램만 작동하면 되는 경우가 많습니다. 데이터베이스 작업
3) 프로젝트 요구 사항은 자주 변경되며 특히 논리적 변경과 관련된 SQL 문을 수정하는 것이 더 편리합니다.

저장 프로시저와 SQL 문 중에서 선택하는 방법은 무엇입니까?

실제 적용 경험을 바탕으로 다음과 같이 제안합니다.

1. 효율성이 높거나 표준화 요구 사항이 높은 일부 프로젝트에서는 저장 프로시저를 사용하는 것이 좋습니다.
2. 일반 프로젝트의 경우 매개변수화된 명령을 사용하는 것은 저장 프로시저와 SQL 문 사이의 절충안입니다
3. 요구 사항이 상대적으로 높고 여러 데이터 로직이 포함된 일부 알고리즘의 경우 저장 프로시저를 사용하는 것이 좋습니다

저장 프로시저의 특정 응용

1. 기본 쿼리

1. 매개 변수 없이 저장 프로시저 만들기

예: 총 학생 수 쿼리

--查询存储过程
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_COUNT', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_COUNT;
GO
CREATE procedure PROC_SELECT_STUDENTS_COUNT
AS 
    SELECT COUNT(ID) FROM Students
GO

실행:

EXEC PROC_SELECT_STUDENTS_COUNT

2. 매개변수가 있는 저장 프로시저

--查询存储过程，根据城市查询总数
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_CITY_COUNT', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT(@city nvarchar(50))
AS
    SELECT COUNT(ID) FROM Students WHERE City=@city
GO

실행 문:

EXEC PROC_SELECT_STUDENTS_BY_CITY_COUNT N'Beijing'

3. 문자

와일드카드, 매개변수 값을 할당할 때 해당 와일드카드

--3、查询姓氏为李的学生信息，含通配符
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_SURNNAME', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_SURNNAME;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_SURNNAME
    @surnName nvarchar(20)='李%' --默认值
AS 
    SELECT ID,Name,Age FROM Students WHERE Name like @surnName
GO

를 추가하여 실행:

EXEC PROC_SELECT_STUDENTS_BY_SURNNAME
EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'李%'
EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'%李%'

4. 출력 매개변수

--根据姓名查询的学生信息，返回学生的城市及年龄
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_NAME', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_NAME;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_NAME
    @name nvarchar(50),     --输入参数
    @city nvarchar(20) out, --输出参数
    @age  int output        --输入输出参数
AS 
    SELECT @city=City,@age=Age FROM Students WHERE Name=@name AND Age=@age
GO

실행:

--执行
declare @name nvarchar(50),
        @city nvarchar(20),
        @age int;
set @name = N'李明';
set @age = 20;
exec PROC_SELECT_STUDENTS_BY_NAME @name,@city out, @age output;
select @city, @age;

2. 저장 프로시저를 사용하여 추가, 삭제, 수정

1. 추가

학생 정보 추가

--1、存储过程：新增学生信息
IF OBJECT_ID (N'PROC_INSERT_STUDENT', N'P') IS NOT NULL
    DROP procedure PROC_INSERT_STUDENT;
GO
CREATE procedure PROC_INSERT_STUDENT
    @id int,
    @name nvarchar(20),
    @age int,
    @city nvarchar(20)
AS 
    INSERT INTO Students(ID,Name,Age,City) VALUES(@id,@name,@age,@city)
GO

실행:

EXEC PROC_INSERT_STUDENT 1001,N'张三',19,'ShangHai'

2. 학생 ID를 기준으로 학생 정보를 업데이트하도록

수정

IF OBJECT_ID (N'PROC_UPDATE_STUDENT', N'P') IS NOT NULL
    DROP procedure PROC_UPDATE_STUDENT;
GO
CREATE procedure PROC_UPDATE_STUDENT
    @id int,
    @name nvarchar(20),
    @age int,
    @city nvarchar(20)
AS 
    UPDATE Students SET Name=@name,Age=@age,City=@city WHERE ID=@id
GO

실행:

EXEC PROC_UPDATE_STUDENT 1001,N'张思',20,'ShangHai'

3. 삭제

ID 기준 학생기록 삭제

--3、存储过程：删除学生信息
IF OBJECT_ID (N'PROC_DELETE_STUDENT_BY_ID', N'P') IS NOT NULL
    DROP procedure PROC_DELETE_STUDENT_BY_ID;
GO
CREATE procedure PROC_DELETE_STUDENT_BY_ID
    @id int
AS 
    DELETE FROM  Students WHERE ID=@id
GO

실행 :

EXEC PROC_DELETE_STUDENT_BY_ID 1001

3 . 페이징 쿼리 구현을 위한 저장 프로시저

1. row_number 함수 페이징 사용

--分页查询
IF OBJECT_ID (N'PROC_SELECT_BY_PAGE', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_BY_PAGE;
GO
CREATE procedure PROC_SELECT_BY_PAGE
    @startIndex int,
    @endIndex int
AS 
    SELECT  * FROM (SELECT ID,Name,Age,City,ROW_NUMBER() OVER(ORDER BY ID DESC) AS RowNumber FROM Students) AS Temp 
    WHERE Temp.RowNumber BETWEEN @startIndex AND @endIndex
GO

실행:

EXEC PROC_SELECT_BY_PAGE 1,10

2. >

실행:

--使用TOP分页
IF OBJECT_ID (N'PROC_SELECT_BY_PAGE_WITH_TOP', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_BY_PAGE_WITH_TOP;
GO
CREATE procedure PROC_SELECT_BY_PAGE_WITH_TOP
    @pageIndex int,
    @pageSize int
AS 
    SELECT TOP(@pageSize) * FROM Students 
    WHERE ID >=(SELECT MAX(ID) FROM (SELECT TOP(@pageSize*(@pageIndex-1) + 1) ID FROM Students ORDER BY ID) AS Temp)    
GO

EXEC PROC_SELECT_BY_PAGE_WITH_TOP 1,2

4. 기타 기능:

1. 실행될 때마다 다시 컴파일되는 저장 프로시저

--1、存储过程，重复编译
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_WITH_RECOMPILE', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_WITH_RECOMPILE;
GO
CREATE procedure PROC_SELECT_STUDENTS_WITH_RECOMPILE
with recompile --重复编译
AS 
    SELECT * FROM Students
GO

2. 저장 프로시저를 암호화합니다

암호화 후에는 소스 스크립트를 보거나 수정할 수 없습니다.

실행:

--2、查询存储过程，进行加密
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_WITH_ENCRYPTION', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_WITH_ENCRYPTION;
GO
CREATE procedure PROC_SELECT_STUDENTS_WITH_ENCRYPTION
with encryption --加密
AS 
    SELECT * FROM Students
GO

효과, 스크립트를 볼 수 없거나 생성 스크립트를 내보낼 수 없습니다

EXEC PROC_SELECT_STUDENTS_WITH_ENCRYPTION