SQL 저장 프로시저 정의 및 저장 프로시저 이점

재사용. 저장 프로시저를 재사용하여 데이터베이스 개발자의 작업량을 줄일 수 있습니다.
(2) 성능을 향상시킵니다. 저장 프로시저는 생성될 때 컴파일되므로 나중에 사용할 때 다시 컴파일할 필요가 없습니다. 일반 SQL 문은 실행될 때마다 컴파일해야 하므로 저장 프로시저를 사용하면 효율성이 향상됩니다.
(3) 네트워크 트래픽을 줄입니다. 저장 프로시저가 서버에 위치하므로 호출 시 저장 프로시저의 이름과 매개변수만 전달하면 되므로 네트워크를 통해 전송되는 데이터의 양이 줄어듭니다.
(4) 안전. 매개변수화된 저장 프로시저는 SQL 주입 공격을 방지할 수 있으며, 저장 프로시저에 부여, 거부 및 취소 권한을 적용할 수 있습니다.
저장 프로시저는 사용자 정의 저장 프로시저, 확장 저장 프로시저, 시스템 저장 프로시저의 세 가지 범주로 나뉩니다.
그 중 사용자 정의 저장 프로시저는 Transaction-SQL과 CLR의 두 가지 유형으로 구분됩니다.
트랜잭션-SQL 저장 프로시저란 사용자가 제공한 매개변수를 승인하고 반환할 수 있는 저장된 트랜잭션-SQL 문의 모음을 의미합니다.
CLR 저장 프로시저는 사용자가 제공한 매개 변수를 수락하고 반환할 수 있는 .Net Framework CLR(공용 언어 런타임) 메서드에 대한 참조를 나타냅니다. 이는 .Net Framework 어셈블리에서 클래스의 공용 정적 메서드로 구현됩니다.
저장 프로시저를 생성하는 문은 다음과 같습니다.
다음은 인용된 내용입니다.
CREATE { PROC | PROCEDURE } [schema_name.] 프로시져_이름 [ ]
[ { @parameter [ type_schema_name.] 데이터 유형 }
[ VARYING ] [ = 기본값 ] [ [ OUT [ PUT ]
] [ ,n ]
[ WITH [ ,n ]
[ 복제용 ]
AS { < ;sql_statement> [;][ n ] | }
[;]
::=
[ RECOMPILE ]
[ EXECUTE_AS_Clause ]

::=

{ [ BEGIN ] 문 [ END ] }

::=

외부 이름 .method_name

[schema_name]: 저장 프로시저가 속한 스키마의 이름을 나타냅니다.
예:

스키마 생성 yangyang8848

이동
Proc ang8848을 생성합니다. AllGoods
As Select * From Master_Goods
Go
실행: Exec AllGoods 오류가 발생했습니다.
실행: Exec yangyang8848.AllGoods가 올바르게 실행되었습니다.
 [;Number]: 동일한 이름을 가진 절차를 그룹화하는 데 사용되는 선택적 정수입니다. 이러한 그룹화된 프로시저를 함께 삭제하려면 DROP PROCEDURE문을 사용하십시오.
예:

Create Proc S1 ;1

AS
Select * From Master_Goods
Go
Create Proc S1 ;2
As
Select * From Master_Location
Go
는 두 개의 저장 프로시저를 생성했습니다. 이들은 동일한 그룹 S1에 있습니다. Exec S1이 실행되면 저장 프로시저는 기본적으로 Exec S1을 실행합니다. 모든 기본 정보를 얻으려면 Exec S1;2를 실행해야 합니다. 저장 프로시저를 삭제하려면 Drop Exec S1만 실행할 수 있으며 그룹의 모든 저장 프로시저가 삭제됩니다.
[@ 매개변수]: 저장 프로시저에 포함된 매개변수입니다. 매개변수를 정의할 때 기본값이 없거나 매개변수가 다른 매개변수와 동일하게 설정되지 않은 경우 사용자는 저장프로시저 호출 시 매개변수에 값을 할당해야 합니다.
저장 프로시저는 최대 2100개의 매개변수를 가질 수 있습니다.
예:

Proc 생성 yangyang8848.OneGoods
@GoodsCode varchar(10)
As
Select * From Master_Goods Where GoodsCode = @GoodsCode
Go
호출 코드:
@Code varchar 선언 (10)
Set @Code = '0004'
Exec yangyang8848.OneGoods @Code
매개변수 뒤에 Output을 추가하면 해당 매개변수가 출력 매개변수임을 나타냅니다.
Proc 생성 yangyang8848.OneGoods
@GoodsCode2 varchar(10) 출력,@GoodsCode varchar(10) = '0011'
As
Select * From Master_Goods Where GoodsCode = @GoodsCode
@ 설정 GoodsCode2 = '0005'
Go
호출 방법:
@VV2 varchar(10)
Exec yangyang8848.OneGoods @Code out
참고: 저장 프로시저의 두 매개 변수 중 하나인 경우 기본값이 있습니다. 값이 없으면 기본값을 뒤에 넣어야 합니다. 그렇지 않으면 문제가 발생합니다~~
조심스러운 친구들은 위의 문장에서 약간의 차이점을 볼 수 있습니다. 예를 들어 저장 프로시저는 출력을 사용합니다. , 그리고 문은 out을 사용합니다. 저는 여러분에게 말하려고 왔습니다. 둘 다 똑같습니다.

[RECOMPILE]: 런타임 시 컴파일되는 이 프로시저에 대한 계획을 캐시하지 않도록 데이터베이스 엔진에 지시합니다. FOR REPLICATION이 지정된 경우에는 이 옵션을 사용할 수 없습니다. CLR 저장 프로시저의 경우 RECOMPILE을 지정할 수 없습니다.
이것은 매우 유용한 함수 OBJECT_ID입니다. 스키마 범위 내 개체의 데이터베이스 개체 식별 번호를 반환합니다.
     例如：我们创建存储过程时，可以如下写代码

      Object_ID('yangyang8848.OneGoods')가 Null이 아닌 경우
             Proc 삭제 yang8848.OneGoods
      이동
      Proc 생성 yangyang8848.OneGoods
@GoodsCode2 varchar(10) out,@GoodsCode varchar(10) = '0011'
As
Select * From Master_Goods Where GoodsCode = @GoodsCode
@GoodsCode2 = '0005' 설정
Go
위 저장 프로시저에 대해 다음 SQL 쿼리를 호출합니다
Select Definition From sys.sql_modules
Where object_id = Object_ID('yangyang8848.OneGoods');

We 결과를 확인할 수 있습니다.
그러나 저장 프로시저에 [ENCRYPTION]을 추가하면 결과를 볼 수 없습니다

If Object_ID('yangyang8848.OneGoods') Is Not Null
Drop Proc yangyang8848.OneGoods
이동

Proc 생성 yangyang8848.OneGoods
@GoodsCode2 varchar(10) out,@GoodsCode varchar(10) = '0011'

암호화 사용
As
Select * From Master_Goods WHERE GoodsCode = @GoodScode
Set @GOODSCODE2 = '0005'
Go

그런 다음 sys.sql_modules 디렉토리 뷰를 확인하면 Null이 반환됩니다.

그런 다음 다음 SQL을 실행합니다. Exec sp_helptext 'yangyang8848.OneGoods'
다음 결과를 얻습니다. 'yangyang8848.OneGoods' 개체의 텍스트가 암호화됩니다.

이 시점에서 [ENCRYPTION] 매개변수는 CREATE PROCEDURE 문의 원본 텍스트를 퍼지 형식으로 변환하는 암호화 함수라는 점을 이해해야 합니다. 난독화된 코드의 출력은 SQL Server 2005의 카탈로그 뷰에 직접 표시될 수 없습니다. 시스템 테이블이나 데이터베이스 파일에 액세스할 수 없는 사용자는 난독화된 텍스트를 검색할 수 없습니다. 그러나 이 텍스트는 DAC 포트를 통해 시스템 테이블에 액세스하거나 데이터베이스 파일에 직접 액세스할 수 있는 권한이 있는 사용자가 사용할 수 있습니다. 또한 서버 프로세스에 디버거를 연결할 수 있는 기능이 있는 사용자는 런타임 시 메모리에서 해독된 프로세스를 검색할 수 있습니다.
이틀 전에 커서에 대한 소개 글을 썼습니다. 다음은 저장 프로시저와 함께 커서를 사용하는 예입니다.

If Object_ID('dbo.GetMasterGoods') Is Not Null
Drop Proc dbo. GetMasterGoods
이동

Proc GetMasterGoods 생성
@MyCursor 커서 가변 출력
암호화 사용

         @MyCursor = Cursor
설정              > | @MyCursor 열기
                                                                                                    
As

@GoodsCode varchar(18) 선언

@GoodsName nvarchar(20) 선언

@MasterGoodsCursor Cursor 선언

Exec GetMasterGoods @MasterGoodsCursor out
@MasterGoodsCursor에서 다음 가져오기

InTo @GoodsCode,@GoodsName

While(@@Fetch_Status = 0)
시작
시작
인쇄 @GoodsCode + ':' + @GoodsName
End
Fetch Next From @MasterGoodsCursor
InTo @GoodsCode,@GoodsName
End
Close @MasterGoodsCursor
Deallocate @MasterGoodsCursor
Go

Exec 실행의 최종 결과 GetAllGoodsIDAndName은 다음 콘텐츠입니다.

0003: 핀 0003 0004: 핀 0004
0005: 123123
0006: 핀 0006

0007: 핀 0007

0008: 핀 000 8