저장 프로시저란 무엇입니까?
"저장 프로시저"란 무엇이며 어떻게 작동합니까?
저장 프로시저의 구성은 무엇입니까(각각 저장 프로시저여야 함)?
저장 프로시저는 여러 가지 방법으로 실행할 수 있는 SQL 문의 배치입니다.대부분의 주요 DBM은 스토어드 프로시저를 지원하지만 모든 DBM이 지원하는 것은 아닙니다.자세한 내용은 특정 DBMS 도움말 문서를 사용하여 확인해야 합니다.SQL Server에 대해 가장 잘 알고 있기 때문에 샘플로 사용합니다.
스토어드 프로시저를 작성하려면 구문은 매우 간단합니다.
CREATE PROCEDURE <owner>.<procedure name>
<Param> <datatype>
AS
<Body>
예를 들어 다음과 같습니다.
CREATE PROCEDURE Users_GetUserInfo
@login nvarchar(30)=null
AS
SELECT * from [Users]
WHERE ISNULL(@login,login)=login
스토어드 프로시저의 장점은 데이터 액세스 로직을 일원화하여 DBA가 쉽게 최적화할 수 있다는 것입니다.스토어드 프로시저는 저장 프로시저에 실행 권한을 부여할 수 있지만 사용자가 기본 테이블에 대한 읽기/쓰기 권한을 가질 필요는 없다는 보안상의 이점도 있습니다.이것은 SQL 주입에 대한 적절한 첫 번째 단계입니다.
스토어드 프로시저에는 기본적으로 기본 CRUD 작업과 관련된 유지 보수라는 단점이 있습니다.각 테이블에 삽입, 업데이트, 삭제가 있고 프라이머리 키에 따라 적어도1개의 선택사항이 있다고 합시다.즉, 각 테이블에는 4개의 절차가 있습니다.이제 400개의 테이블이 있는 적당한 크기의 데이터베이스를 가져와서 1600개의 프로시저를 사용할 수 있습니다.그리고 그것은 당신이 아마 가지고 있을 중복된 것을 가지고 있지 않다고 가정합니다.
여기서 기본적인 CRUD 작업을 자동 생성하기 위해 ORM 또는 기타 방법을 사용하는 것이 매우 중요합니다.
저장 프로시저는 특수 태스크를 수행하는 데 사용되는 미리 컴파일된 SQL 문 세트입니다.
: :만약 내가 가지고 있다면Employee
Employee ID Name Age Mobile
---------------------------------------
001 Sidheswar 25 9938885469
002 Pritish 32 9178542436
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★Employee 삭제:
Create Procedure Employee details
As
Begin
Select * from Employee
End
SQL Server에서 절차를 실행하려면:
Execute Employee details
--- (Employee details is a user defined name, give a name as you want)
다음으로 값을 [Employee Table]에 삽입합니다.
Create Procedure employee_insert
(@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
Insert Into Employee
Values (@EmployeeID, @Name, @Age, @Mobile)
End
SQL Server에서 매개 변수화된 프로시저를 실행하려면 다음 절차를 수행합니다.
Execute employee_insert 003,’xyz’,27,1234567890
--(Parameter size must be same as declared column size)
::@Name Varchar(30)
서서 Employee로 Name열의 크기는 다음과 같아야 합니다.varchar(30).
저장 프로시저는 데이터베이스에 생성 및 저장된 SQL 문의 그룹입니다.저장 프로시저는 다른 입력 데이터를 사용하는 여러 클라이언트가 네트워크를 통해 단일 프로시저를 사용할 수 있도록 입력 파라미터를 받아들입니다.저장 프로시저는 네트워크 트래픽을 줄이고 성능을 향상시킵니다.스토어드 프로시저를 수정하면 모든 클라이언트는 업데이트된 스토어드 프로시저를 얻을 수 있습니다.
저장 프로시저 생성 샘플
CREATE PROCEDURE test_display
AS
SELECT FirstName, LastName
FROM tb_test;
EXEC test_display;
저장 프로시저를 사용하는 장점
저장 프로시저는 모듈러 프로그래밍을 허용합니다.
프로시저를 한 번 생성하여 데이터베이스에 저장하고 프로그램에서 원하는 횟수만큼 호출할 수 있습니다.
저장 프로시저를 사용하면 실행 속도가 빨라집니다.
반복하여 많은 양의 SQL 코드가 필요한 경우 저장 프로시저를 더 빠르게 수행할 수 있습니다.이러한 프로시저는 처음 실행될 때 구문 분석 및 최적화되며 저장 프로시저의 컴파일된 버전은 나중에 사용할 수 있도록 메모리 캐시에 남아 있습니다.즉, 저장 프로시저를 사용할 때마다 다시 파싱하고 최적화할 필요가 없으므로 실행 시간이 훨씬 빨라집니다.
스토어드 프로시저를 사용하면 네트워크트래픽을 줄일 수 있습니다.
수백 줄의 Transact-SQL 코드를 필요로 하는 조작은 수백 줄의 코드를 네트워크를 통해 송신하는 것이 아니라 프로시저에서 코드를 실행하는 단일 스테이트먼트를 통해 실행할 수 있습니다.
저장 프로시저를 통해 데이터 보안 향상
사용자는 프로시저의 문을 직접 실행할 권한이 없는 경우에도 저장 프로시저를 실행할 수 있는 권한을 부여할 수 있습니다.
SQL Server에는 다양한 유형의 저장 프로시저가 있습니다.
- 시스템 스토어드 프로시저
- 사용자 정의 저장 프로시저
- 확장 저장 프로시저
시스템 스토어드 프로시저는 마스터 데이터베이스에 저장됩니다.이러한 프로시저는
sp_이러한 를 사용하여 시스템테이블 에 대한 Server 기능을 할 수 .예: sp_helptext [Stored Procedure_Name]
사용자 정의 저장 프로시저는 보통 사용자 데이터베이스에 저장되며 일반적으로 사용자 데이터베이스에서 작업을 완료하도록 설계되었습니다.이러한 절차를 코딩하는 동안
sp_「」를 하는 는, 입니다.sp_prefix는 먼저 마스터 데이터베이스를 확인한 후 사용자 정의 데이터베이스에 도달합니다.확장 스토어드 프로시저는 DLL 파일에서 함수를 호출하는 프로시저입니다.오늘날 확장 스토어드 프로시저는 확장 스토어드 프로시저를 사용하지 않는 것이 좋습니다.
일반적으로 저장 프로시저는 "SQL 함수"입니다.다음과 같은 것이 있습니다.
-- a name
CREATE PROCEDURE spGetPerson
-- parameters
CREATE PROCEDURE spGetPerson(@PersonID int)
-- a body
CREATE PROCEDURE spGetPerson(@PersonID int)
AS
SELECT FirstName, LastName ....
FROM People
WHERE PersonID = @PersonID
이것은 T-SQL에 초점을 맞춘 예입니다.스토어드 프로시저는 대부분의 SQL 문을 실행하고 스칼라 및 테이블 기반 값을 반환할 수 있으며 SQL 주입 공격을 방지하므로 더 안전한 것으로 간주됩니다.
이런 상황을 생각해 보세요
- 데이터가 있는 데이터베이스가 있습니다.
- 이 중앙 데이터베이스에 액세스하기 위해서는 다양한 애플리케이션이 필요합니다.또, 장래에는 새로운 애플리케이션도 몇개인가 필요하게 될 것입니다.
- 중앙 데이터베이스에 액세스하기 위해 인라인 데이터베이스 쿼리를 각 응용 프로그램의 코드 내부에 개별적으로 삽입하려면 동일한 쿼리를 다른 응용 프로그램의 코드 내에서 여러 번 복제해야 합니다.
- 이러한 상황에서는 Stored Procedure(SP; 스토어드 프로시저)를 사용할 수 있습니다.저장 프로시저에서는 일반적인 쿼리(프로시저)의 수를 작성하여 중앙 데이터베이스에 저장합니다.
- 이제 이전과 같이 작업이 중복되는 일은 없으며 데이터 액세스와 유지보수가 중앙 집중식으로 이루어집니다.
주의:
- 상기의 상황에서는, 「왜 모든 애플리케이션과 상호 작용하기 위해서, 중앙의 데이터 액세스 서버를 도입할 수 없는가」라고 생각할 수 있습니다.네, 그럴 수도 있어요그렇지만,
- SP의 주요 장점은 인라인 쿼리를 사용하는 데이터 액세스 코드와 달리 SP는 미리 컴파일된 문장이므로 실행 속도가 빨라진다는 것입니다.통신비(네트워크 경유)는 최소한으로 억제됩니다.
- 반대로 SP는 데이터베이스 서버에 부하를 더합니다.상황에 따라서는 인라인 쿼리를 사용한 집중형 데이터 액세스서버를 선택하는 것이 좋습니다.
저장 프로시저는 주로 데이터베이스에서 특정 작업을 수행하기 위해 사용됩니다.예를들면
- 데이터에 대한 비즈니스 로직에서 데이터베이스 결과 세트를 가져옵니다.
- 1개의 콜로 복수의 데이터베이스 조작을 실행합니다.
- 한 테이블에서 다른 테이블로 데이터를 마이그레이션하는 데 사용됩니다.
- Java와 같은 다른 프로그래밍 언어를 호출할 수 있습니다.
저장 프로시저는 단일 실행 계획으로 컴파일된 SQL 문의 그룹에 불과합니다.
- 1회 생성 후 n회 호출
- 네트워크 트래픽을 줄입니다.
예: 저장 프로시저 생성
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetEmployee
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees
WHERE EmployeeID = @EmployeeID
END
GO
저장 프로시저를 변경하거나 수정합니다.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE GetEmployee
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees
WHERE EmployeeID = @EmployeeID
END
GO
저장 프로시저를 삭제 또는 삭제합니다.
DROP PROCEDURE GetEmployee
저장 프로시저는 데이터베이스 테이블의 데이터 검색, 데이터 수정 및 데이터 삭제에 사용됩니다.SQL 데이터베이스에 데이터를 삽입, 업데이트 또는 삭제할 때마다 전체 SQL 명령을 작성할 필요가 없습니다.
저장 프로시저는 특정 작업을 수행하는 하나 이상의 SQL 문으로 구성된 사전 컴파일된 세트입니다.
으로 실행해야 .
EXEC저장 프로시저는 여러 매개 변수를 반환할 수 있습니다.
저장 프로시저를 사용하여 트랜잭션을 구현할 수 있습니다.
된 프로시저란 는 이미 저장된 프로시저란 무엇인가"는 이미 여기의 다른 게시물에도 답변되어 있습니다.스토어드 프로시저를 사용하는 방법이 알려져 있지 않습니다.렇렇 it it it it 。grouping stored procedures ★★★★★★★★★★★★★★★★★」numbering stored procedures.
구문 참조
; number이와 같이
같은 이름의 프로시저를 그룹화하는 데 사용되는 선택적 정수입니다.이러한 그룹화된 프로시저는 1개의 DROP PROCEDURE 문을 사용하여 함께 폐기할 수 있습니다.
예
CREATE Procedure FirstTest
(
@InputA INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)
END
GO
CREATE Procedure FirstTest;2
(
@InputA INT,
@InputB INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO
사용하다
exec FirstTest 10
exec FirstTest;2 20,30
결과
재시도
CREATE Procedure SecondTest;2
(
@InputA INT,
@InputB INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO
결과
Msg 2730, Level 11, State 1, Procedure SecondTest, Line 1 [Batch Start Line 3]그룹번호가 2인 프로시저는 현재 데이터베이스에 존재하지 않기 때문에 작성할 수 없습니다.CREATE PROCEDURE 'SecondTest'; 1을 먼저 실행해야 합니다.
참고 자료:
주의.
- 프로시저를 그룹화한 후에는 개별적으로 드롭할 수 없습니다.
- 이 기능은 향후 버전의 Microsoft SQL Server에서 제거될 수 있습니다.
심플하게
Stored Procedure는 데이터베이스에 저장된 프로그램/함수인 Stored Procedure입니다.
저장된 각 프로그램에는 SQL 문으로 구성된 본문이 포함되어 있습니다.이 문은 세미콜론(;) 문자로 구분된 여러 개의 문장으로 구성된 복합문일 수 있습니다.
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;
스토어드 프로시저는 SQL 문과 프로시저 로직의 명명된 집합으로 컴파일, 검증 및 서버 데이터베이스에 저장됩니다.스토어드 프로시저는 일반적으로 다른 데이터베이스 개체와 동일하게 취급되며 서버 보안 메커니즘을 통해 제어됩니다.
DBMS에서 스토어드 프로시저는 이름이 할당된 SQL 문의 집합으로, 데이터베이스에 컴파일된 형태로 저장되므로 여러 프로그램에서 공유할 수 있습니다.
스토어드 프로시저를 사용하면,
데이터에 대한 제어된 액세스 제공(최종 사용자는 데이터 입력 또는 변경만 할 수 있고 프로시저는 쓸 수 없음)
데이터 무결성 보장(데이터는 일관된 방식으로 입력됨) 및
생산성 향상(저장 프로시저 문장은 한 번만 작성 필요)
SQL Server의 스토어드 프로시저는 입력 파라미터를 받아들여 출력 파라미터의 여러 값을 반환할 수 있습니다.SQL Server의 스토어드 프로시저는 데이터베이스 내의 조작을 실행하고 상태 값을 호출 프로시저 또는 배치에 반환하는 프로그램문을 사용합니다.
SQL Server에서 스토어드 프로시저를 사용하는 이점
모듈러 프로그래밍이 가능합니다.고속 실행을 가능하게 합니다.네트워크 트래픽을 줄일 수 있습니다.보안 메커니즘으로 사용할 수 있습니다.
다음은 파라미터를 사용하여 쿼리를 실행하고 결과를 반환하는 저장 프로시저의 예입니다.특히 스토어드 프로시저는 BusinessEntity를 받아들입니다.파라미터로서 ID 를 사용하고, HumanResources 의 프라이머리 키를 대조합니다.요청된 직원을 반환하는 직원 테이블입니다.
> create procedure HumanResources.uspFindEmployee `*<<<---Store procedure name`*
@businessEntityID `<<<----parameter`
as
begin
SET NOCOUNT ON;
Select businessEntityId, <<<----select statement to return one employee row
NationalIdNumber,
LoginID,
JobTitle,
HireData,
From HumanResources.Employee
where businessEntityId =@businessEntityId <<<---parameter used as criteria
end
이것은 essential.com에서 배웠어요. 매우 유용합니다.
저장 프로시저는 서버에서 코드를 만드는 데 도움이 됩니다.매개 변수를 전달하고 출력을 찾을 수 있습니다.
create procedure_name (para1 int,para2 decimal)
as
select * from TableName
Stored Procedures에서 문은 한 번만 작성되며 클라이언트와 서버 간의 네트워크 트래픽을 줄입니다.SQL 주입 공격을 피할 수도 있습니다.
- 지불처리를 위해 어플리케이션에서 서드파티 프로그램을 사용하는 경우, 이 데이터베이스는 필요한 정보 및 서드파티가 승인한 액티비티만 공개해야 합니다.이것에 의해, Stored Procedures 를 사용해 권한을 설정함으로써 데이터의 기밀성을 확보할 수 있습니다.
- 테이블 업데이트는 대상 테이블에만 수행되어야 하며, 다른 테이블은 업데이트해서는 안 되며, 이를 통해 트랜잭션 처리 및 오류 처리를 통해 데이터 무결성을 달성할 수 있습니다.
- 데이터 유형의 항목을 하나 이상 반환하려면 출력 매개 변수를 사용하는 것이 좋습니다.
- 스토어드 프로시저에서는 반환해야 하는 모든 것에 대해 출력 파라미터를 사용합니다.정수 데이터 유형만 있는 항목을 하나만 반환하려면 반환 값을 사용하는 것이 좋습니다.실제로 반환값은 저장 프로시저의 성공 또는 실패를 알리기 위한 것입니다.
서문:1992년에 SQL92 표준이 개발되어 Firebase DB에 의해 보급되었습니다.이 표준은 '저장 프로시저'를 도입하였다.
** 패스스루 쿼리: 구문적으로 올바른 SQL 문에 대해 평가되는 문자열(일반적으로 프로그램적으로 연결됨)로, 일반적으로 서버 계층(PHP, Python, PERL 등 언어)에서 생성됩니다.그런 다음 이러한 문이 데이터베이스로 전달됩니다.**
** 트리거: 데이터 무결성을 강화하기 위해 자주 사용되는 데이터베이스 이벤트(일반적으로 DML 이벤트)에 응답하여 실행되도록 설계된 코드입니다.**
저장 프로시저가 무엇인지 설명하는 가장 좋은 방법은 DB 로직을 실행하는 기존 방식(저장 프로시저를 사용하지 않음)을 설명하는 것입니다.
기존 시스템 생성 방법은 '패스스루 쿼리'를 사용하여 DB에 트리거가 있을 수 있습니다. Stored Procedure를 사용하지 않는 대부분의 사용자는 '패스스루 쿼리'라는 것을 사용합니다.
최신 저장 프로시저 규약에서는 트리거가 '패스스루 쿼리'와 함께 레거시가 되었습니다.
스토어드 프로시저의 장점은 다음과 같습니다.
- 저장 프로시저의 실제 텍스트는 변경되지 않으므로 캐시할 수 있습니다.
- 악성 SQL 주입에 대한 메커니즘이 내장되어 있습니다.
- 파라미터만 체크하면 악의적인 SQL 주입을 통해 프로세서 오버헤드를 크게 줄일 수 있습니다.
- 대부분의 최신 데이터베이스 엔진은 실제로 저장 프로시저를 컴파일합니다.
- 계층 간의 추상화 정도를 높입니다.
- 이러한 프로세스는 데이터베이스와 동일한 프로세스에서 발생하므로 최적화 및 처리량이 향상됩니다.
- 백엔드 워크플로우 전체를 클라이언트 측 코드 없이 테스트할 수 있습니다(예를 들어 트랜잭션 SQL의 Execute 명령이나 MySQL의 CALL 명령).
- 시스템 설계와 일치하지 않는 방법으로 데이터베이스에 액세스할 수 없도록 하기 위해 사용할 수 있기 때문에 보안 강화에 사용할 수 있습니다.이는 데이터베이스 사용자 권한 메커니즘을 통해 수행됩니다.예를 들어 사용자에게 SELECT, UPDATE 등의 권한이 아닌 EXECUTE Stored Procedures에만 권한을 부여할 수 있습니다.
- 트리거와 관련된 DML 레이어는 불필요합니다.** 1개의 트리거를 사용하여 프로세서 부하가 높은 DML 레이어를 엽니다**
요약하면 새 SQL 데이터베이스 시스템을 생성할 때 패스스루 쿼리를 사용할 좋은 핑계는 없습니다.
또한 이미 트리거 또는 패스스루 쿼리를 사용하고 있는 레거시 시스템에서는 Stored Procedure를 사용하는 것이 완벽하게 안전하다는 점도 유의하십시오.즉, 레거시 시스템에서 Stored Procedure로의 이행은 매우 간단하며, 이러한 이행이 시스템을 장시간 정지시킬 필요는 없습니다.
create procedure <owner>.<procedure name><param> <datatype>
As
<body>
저장 프로시저는 데이터 액세스를 한 포인트로 집중화하는 SQL 문의 그룹입니다.한 번에 여러 작업을 수행할 때 유용합니다.
언급URL : https://stackoverflow.com/questions/459457/what-is-a-stored-procedure
'programing' 카테고리의 다른 글
| SQL에서 VARCHAR이 아닌 CHAR을 선택하는 사용 사례는 무엇입니까? (0) | 2023.04.16 |
|---|---|
| Dispatch Queue.main.async와 Dispatch Queue.main.sync의 차이점 (0) | 2023.04.16 |
| "Write-Host", "Write-Output" 또는 "[console]"의 차이점은 무엇입니까?: WriteLine? (0) | 2023.04.16 |
| 워크북의 끝에 VBA 복사 시트(숨긴 워크시트 포함) (0) | 2023.04.16 |
| github에서 모든 커밋 이력을 삭제하는 방법 (0) | 2023.04.16 |