저장 프로시저 - 한 번 컴파일되어 서버에 저장되는 일련의 SQL 문인 데이터베이스 개체입니다. 저장 프로시저는 고급 언어의 일반 프로시저와 매우 유사하며 입력 및 출력 매개변수와 지역 변수를 가질 수 있으며 문자 데이터에 대해 숫자 계산 및 연산을 수행할 수 있으며 그 결과를 변수 및 매개변수에 할당할 수 있습니다. 저장 프로시저는 표준 데이터베이스 작업(DDL 및 DML 모두)을 수행할 수 있습니다. 또한 저장 프로시저에서 루프와 분기가 가능합니다. 즉, 명령을 사용하여 실행 프로세스를 제어할 수 있습니다.

저장 프로시저는 사용자 정의 함수(UDF)와 유사합니다. 주요 차이점은 사용자 정의 함수는 SQL 쿼리의 다른 표현식처럼 사용할 수 있지만 저장 프로시저는 CALL 함수를 사용하여 호출해야 한다는 것입니다.

CALL 절차(…)

EXECUTE 프로시저(...)

저장 프로시저는 결과 집합, 즉 SELECT 쿼리의 결과를 반환할 수 있습니다. 이러한 결과 집합은 커서, 결과 집합 포인터를 반환하는 다른 저장 프로시저 또는 응용 프로그램을 사용하여 처리할 수 있습니다. 저장 프로시저에는 테이블의 여러 행을 반복할 수 있는 데이터 및 커서를 처리하기 위해 선언된 변수가 포함될 수도 있습니다. SQL 표준은 IF, LOOP, REPEAT, CASE 및 작업할 더 많은 표현식을 제공합니다. 저장 프로시저는 변수가 선언된 위치에 따라 변수를 허용하거나 결과를 반환하거나 변수를 수정하고 반환할 수 있습니다.

저장 프로시저의 구현은 DBMS마다 다릅니다. 대부분의 주요 데이터베이스 공급업체는 이러한 형식을 지원합니다. DBMS에 따라 SQL, Java, C, C++ 등 다양한 프로그래밍 언어로 저장 프로시저를 구현할 수 있습니다. 비 SQL로 작성된 저장 프로시저는 자체적으로 SQL 쿼리를 실행하거나 실행하지 않을 수 있습니다.

당

    다른 응용 프로그램과 논리 공유. 저장 프로시저는 기능을 캡슐화합니다. 이렇게 하면 서로 다른 응용 프로그램 간의 데이터 액세스 및 관리 연결이 보장됩니다.

    데이터베이스 테이블에서 사용자를 격리합니다. 이렇게 하면 저장 프로시저에 대한 액세스 권한을 부여할 수 있지만 테이블 데이터 자체에는 액세스할 수 없습니다.

    보호 메커니즘을 제공합니다. 앞의 말씀에 따르면 저장 프로시저를 통해서만 데이터에 액세스할 수 있다면 다른 누구도 SQL DELETE 명령을 통해 데이터를 지울 수 없습니다.

    네트워크 트래픽 감소로 인해 성능이 향상되었습니다. 저장 프로시저를 사용하면 여러 쿼리를 결합할 수 있습니다.

에 맞서

    대부분의 작업이 서버 측에서 수행되고 클라이언트 측에서 덜 수행된다는 사실로 인해 데이터베이스 서버의 로드가 증가합니다.

    당신은 많은 것을 배워야합니다. 저장 프로시저를 작성하려면 MySQL 표현식 구문을 배워야 합니다.

    서버 코드와 저장 프로시저용 코드의 두 위치에서 응용 프로그램 논리를 복제하므로 데이터 조작 프로세스가 복잡해집니다.

    한 DBMS에서 다른 DBMS(DB2, SQL Server 등)로 마이그레이션하면 문제가 발생할 수 있습니다.

저장 프로시저의 목적과 이점

저장 프로시저는 성능을 개선하고 프로그래밍 옵션을 강화하며 데이터 보안 기능을 지원합니다.

클라이언트는 자주 사용하는 쿼리를 저장하는 대신 적절한 저장 프로시저를 참조할 수 있습니다. 저장 프로시저가 호출되면 해당 내용은 서버에서 즉시 처리됩니다.

쿼리의 실제 실행 외에도 저장 프로시저를 사용하면 계산을 수행하고 데이터를 조작할 수 있습니다. 변경, 삭제, DDL 문 실행(모든 DBMS가 아님!), 다른 저장 프로시저 호출, 복잡한 트랜잭션 논리 수행. 단일 명령문을 사용하면 저장 프로시저에 포함된 복잡한 스크립트를 호출할 수 있으므로 네트워크를 통해 수백 개의 명령을 보내는 것을 방지하고 특히 클라이언트에서 서버로 많은 양의 데이터를 전송할 필요가 없습니다.

대부분의 DBMS에서 저장 프로시저는 처음 실행될 때 컴파일됩니다(파싱되고 데이터 액세스 계획이 생성됨). 앞으로는 처리 속도가 빨라집니다. Oracle DBMS는 데이터 딕셔너리에 저장된 프로시저 코드를 해석합니다. Oracle 10g부터 C로 저장된 프로시저 코드를 대상 머신의 기계어로 변환하는 이른바 네이티브 컴파일(네이티브 컴파일)이 지원되며, 그 후 스토어드 프로시저가 호출될 때 컴파일된 객체 코드가 직접 실행.

프로그래밍 옵션

저장 프로시저를 만든 후에는 언제든지 호출할 수 있으므로 모듈성을 제공하고 코드 재사용을 권장합니다. 후자는 비즈니스 규칙 변경으로부터 격리되므로 데이터베이스를 유지 관리하기가 더 쉽습니다. 언제든지 새 규칙을 준수하도록 저장 프로시저를 수정할 수 있습니다. 그 후, 이를 사용하는 모든 애플리케이션은 직접적인 수정 없이 자동으로 새로운 비즈니스 규칙을 준수합니다.

안전

저장 프로시저를 사용하면 데이터베이스 테이블에 대한 직접 사용자 액세스를 제한하거나 완전히 제외할 수 있으므로 사용자는 데이터에 대한 간접적이고 엄격하게 규제된 액세스를 제공하는 저장 프로시저를 실행할 수 있는 권한만 남게 됩니다. 또한 일부 DBMS는 저장 프로시저의 텍스트 암호화(래핑)를 지원합니다.

이러한 보안 기능을 사용하면 데이터베이스 구조를 사용자로부터 격리할 수 있으므로 데이터베이스의 무결성과 안정성이 보장됩니다.

잘 작성된 저장 프로시저는 DBMS에 쿼리를 전달하기 전에 입력 매개변수의 유효성을 추가로 검증하기 때문에 "SQL 주입"과 같은 작업은 가능성이 적습니다.

저장 프로시저 구현

저장 프로시저는 일반적으로 다음으로 생성됩니다. SQL 언어또는 선택된 DBMS에서의 특정 구현. 예를 들어 이러한 목적을 위해 Microsoft SQL Server DBMS에는 Transact-SQL 언어가 있고 Oracle에는 PL/SQL, InterBase 및 Firebird에는 PSQL이 있고 PostgreSQL에는 PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, IBM이 있습니다. DB2 - SQL / PL(영어), Informix - SPL. MySQL은 SQL:2003 표준을 매우 밀접하게 따르며 언어는 SQL/PL과 유사합니다.

일부 DBMS에서는 C++ 또는 델파이와 같이 독립적인 실행 파일을 생성할 수 있는 모든 프로그래밍 언어로 작성된 저장 프로시저를 사용할 수 있습니다. Microsoft SQL Server 용어로 이러한 프로시저를 확장 저장 프로시저라고 하며 단순히 Win32-DLL에 포함된 함수입니다. 그리고 예를 들어 Interbase와 Firebird에서 DLL/SO에서 호출되는 함수에는 UDF(User Defined Function)라는 다른 이름이 정의되어 있습니다. MS SQL 2005에서는 모든 .NET 언어로 저장 프로시저를 작성할 수 있게 되었으며 향후 확장 저장 프로시저가 중단될 예정입니다. Oracle DBMS를 사용하면 Java로 저장 프로시저를 작성할 수 있습니다. IBM DB2에서 기존의 프로그래밍 언어로 저장 프로시저와 함수를 작성하는 것은 처음부터 지원되는 전통적인 방식이며 절차적 SQL 확장 ANSI 표준에 포함된 후 다소 늦은 버전에서만 이 DBMS에 추가되었습니다. Informix는 Java 및 C 절차도 지원합니다.

Oracle DBMS에서 저장 프로시저는 소위 패키지로 결합될 수 있습니다. 패키지는 저장 프로시저의 정의를 지정하는 사양(영어 패키지 사양)과 구현이 있는 본문(영어 패키지 본문)의 두 부분으로 구성됩니다. 이것이 Oracle이 인터페이스를 분리하는 방법입니다. 프로그램 코드구현에서.

IBM DB2에서 저장 프로시저는 모듈로 결합될 수 있습니다.

통사론

프로시저 생성 `p2`()

SQL 보안 정의자

COMMENT "절차"

SELECT "Hello World!";

코드의 첫 번째 부분은 저장 프로시저를 만듭니다. 다음 - 선택적 매개변수를 포함합니다. 그런 다음 이름과 마지막으로 프로시저 자체의 본문이 나옵니다.

저장 프로시저의 4가지 특징:

언어: 이식성을 위해 기본값은 SQL입니다.

결정적: 프로시저가 항상 동일한 결과를 반환하고 동일한 입력 매개변수를 사용하는 경우. 이것은 복제 및 등록 프로세스를 위한 것입니다. 기본값은 NOT DETERMINISTIC입니다.

SQL 보안: 통화 중 사용자의 권한을 확인 중입니다. INVOKER는 저장 프로시저를 호출하는 사용자입니다. DEFINER는 절차의 "생성자"입니다. 기본값은 DEFINER입니다.

주석: 문서화를 위해 기본값은 ""입니다.

저장 프로시저 호출

CALL stored_procedure_name (param1, param2, ....)

CALL procedure1(10, "문자열 매개변수", @parameter_var);

저장 프로시저 변경

MySQL에는 프로시저를 수정하기 위한 ALTER PROCEDURE 문이 있지만 특정 특성을 수정하는 데에만 적합합니다. 프로시저의 매개변수나 본문을 변경해야 하는 경우 삭제하고 다시 생성해야 합니다.

제거저장된절차

존재하는 경우 중단 절차 p2;

이것은 간단한 명령입니다. IF EXISTS 문은 그러한 프로시저가 없는 경우 오류를 포착합니다.

옵션

CREATE PROCEDURE proc1(): 빈 매개변수 목록

CREATE PROCEDURE proc1(IN varname DATA-TYPE): 하나의 입력 매개변수. 기본 매개변수가 IN(수신)이기 때문에 IN 단어는 선택 사항입니다.

CREATE PROCEDURE proc1(OUT 변수 이름 DATA-TYPE): 하나의 반환 매개변수.

CREATE PROCEDURE proc1(INOUT varname DATA-TYPE): 하나의 매개변수(입력 및 출력 모두).

변수 선언 구문은 다음과 같습니다.

DECLARE 변수 이름 DATA-TYPE DEFAULT 기본값;

SQL Server로 작업할 때 사용자는 특정 작업을 구현하는 고유한 프로시저를 만들 수 있습니다. 저장 프로시저는 본격적인 데이터베이스 개체이므로 각 개체는 특정 데이터베이스에 저장됩니다. 저장 프로시저를 직접 호출하는 것은 프로시저가 있는 데이터베이스 컨텍스트에서 수행되는 경우에만 가능합니다.

저장 프로시저의 유형

SQL Server에는 여러 유형의 저장 프로시저가 있습니다.

    시스템 저장 프로시저는 다양한 관리 작업을 수행하도록 설계되었습니다. 거의 모든 서버 관리 작업은 도움을 받아 수행됩니다. 시스템 저장 프로시저는 시스템 테이블에 대한 작업을 제공하는 인터페이스라고 할 수 있으며, 이는 궁극적으로 사용자 및 시스템 데이터베이스의 시스템 테이블에서 데이터를 변경, 추가, 삭제 및 검색하는 것으로 귀결됩니다. 시스템 저장 프로시저는 sp_ 접두어가 붙고 시스템 데이터베이스에 저장되며 다른 데이터베이스의 컨텍스트에서 호출될 수 있습니다.

    사용자 지정 저장 프로시저는 특정 작업을 구현합니다. 저장 프로시저는 완전한 데이터베이스 개체입니다. 결과적으로 각 저장 프로시저는 실행되는 특정 데이터베이스에 있습니다.

    임시 저장 프로시저는 짧은 시간 동안만 존재하며 그 후 서버에 의해 자동으로 삭제됩니다. 그들은 로컬과 글로벌로 나뉩니다. 로컬 임시 저장 프로시저는 생성된 연결에서만 호출할 수 있습니다. 이러한 프로시저를 작성할 때 단일 # 문자로 시작하는 이름을 지정해야 합니다. 모든 임시 개체와 마찬가지로 이 유형의 저장 프로시저는 사용자가 서버의 연결을 끊거나 다시 시작하거나 중지할 때 자동으로 삭제됩니다. 전역 임시 저장 프로시저는 동일한 프로시저가 있는 서버의 모든 연결에 사용할 수 있습니다. 정의하려면 ## 문자로 시작하는 이름을 지정하면 됩니다. 이러한 프로시저는 서버가 다시 시작되거나 중지되거나 컨텍스트에서 생성된 연결이 닫힐 때 삭제됩니다.

방아쇠

방아쇠저장 프로시저의 종류 중 하나입니다. 실행은 테이블에서 DML(데이터 조작 언어) 문이 실행될 때 발생합니다. 트리거는 데이터 무결성을 확인하고 트랜잭션을 롤백하는 데 사용됩니다.

방아쇠컴파일된 SQL 프로시저이며 관계형 데이터베이스 내에서 특정 이벤트의 발생에 따라 실행이 결정됩니다. 트리거의 사용은 대부분 데이터베이스 사용자에게 매우 편리합니다. 그러나 이러한 사용은 종종 I/O 작업에 대한 추가 리소스 비용과 관련됩니다. 저장 프로시저나 응용 프로그램을 사용하여 동일한 결과(훨씬 적은 리소스 오버헤드로)를 얻을 수 있는 경우 트리거는 부적절합니다.

방아쇠데이터베이스의 데이터 무결성을 유지하는 데 사용되는 특수 SQL 서버 도구입니다. 무결성 제약 조건, 규칙 및 기본값이 항상 원하는 수준의 기능을 제공하는 것은 아닙니다. 유효하고 실제인지 확인하기 위해 복잡한 데이터 유효성 검사 알고리즘을 구현해야 하는 경우가 많습니다. 또한 필요에 따라 관련 데이터를 변경하기 위해 테이블 ​​값의 변경 사항을 추적해야 하는 경우도 있습니다. 트리거는 규칙, 기본값 등에 따라 모든 작업이 수행된 후에 적용되는 일종의 필터로 생각할 수 있습니다.

방아쇠트리거가 연결된 테이블의 데이터를 변경하려고 할 때 서버에 의해 자동으로 실행되는 특별한 유형의 저장 프로시저입니다. 각 방아쇠특정 테이블에 연결됩니다. 모든 데이터 수정은 하나의 트랜잭션으로 처리됩니다. 오류 또는 데이터 무결성 위반이 감지되면 트랜잭션이 롤백됩니다. 따라서 변경이 금지됩니다. 트리거에 의해 이미 수행된 모든 변경 사항도 취소됩니다.

생성 방아쇠데이터베이스의 소유자만. 이 제한을 사용하면 테이블 구조, 다른 개체를 테이블과 연결하는 방법 등의 우발적인 변경을 방지할 수 있습니다.

방아쇠매우 유용하고 동시에 위험한 도구입니다. 따라서 잘못된 작업 논리로 전체 데이터베이스를 쉽게 파괴할 수 있으므로 트리거를 매우 신중하게 디버깅해야 합니다.

일반 서브루틴과 달리 방아쇠트리거 이벤트가 발생할 때마다 암시적으로 실행되며 인수가 없습니다. 이를 활성화하는 것을 트리거 발사라고 합니다. 트리거는 다음 목표를 달성합니다.

    입력된 데이터의 유효성을 검사하고 테이블에 설정된 무결성 제약 조건으로 유지하기가 불가능하지는 않더라도 어려운 복잡한 데이터 무결성 제약 조건을 구현합니다.

    특정 방식으로 구현된 테이블을 업데이트할 때 특정 작업을 수행해야 함을 상기시키는 경고를 발행합니다.

    변경 사항 및 변경 사항을 수행한 사람에 대한 정보를 수정하여 감사 정보를 축적합니다.

    복제 지원.

CREATE TRIGGER 명령의 기본 형식은 다음과 같습니다.

<Определение_триггера>::=

CREATE TRIGGER 트리거 이름

전에 | 후에<триггерное_событие>

켜짐<имя_таблицы>

<список_старых_или_новых_псевдонимов>]

<тело_триггера>

트리거 이벤트는 테이블의 행 삽입, 삭제 및 업데이트로 구성됩니다. 후자의 경우 트리거 이벤트에 대해 특정 테이블 열 이름을 지정할 수 있습니다. 트리거의 시작 시간은 BEFORE 키워드( 방아쇠연결된 이벤트의 실행 전) 또는 AFTER(실행 후)에 발생합니다.

트리거에 의해 수행되는 작업은 이 이벤트가 적용되는 각 행(FOR EACH ROW)에 대해 설정되거나 각 이벤트(FOR EACH STATEMENT)에 대해 한 번만 설정됩니다.

잘못 작성된 트리거는 "죽은" 잠금과 같은 심각한 문제를 일으킬 수 있습니다. 트리거는 오랜 시간 동안 많은 리소스를 차단할 수 있으므로 액세스 충돌을 최소화하는 데 특히 주의해야 합니다.

방아쇠현재 데이터베이스에서만 만들 수 있지만 원격 서버에 있는 데이터베이스를 포함하여 트리거 내부에서 다른 데이터베이스에 액세스할 수 있습니다.

저장 프로시저저장 프로시저)는 명명된 데이터베이스 프로그램 개체입니다. SQL Server에는 여러 유형의 저장 프로시저가 있습니다.

시스템 저장 프로시저시스템 저장 프로시저)는 DBMS 개발자가 제공하며 시스템 카탈로그에 대한 작업을 수행하거나 시스템 정보를 가져오는 데 사용됩니다. 그들의 이름은 일반적으로 접두사 "sp_"로 시작합니다. 모든 유형의 저장 프로시저는 EXECUTE 명령으로 실행되며 EXEC로 단축될 수 있습니다. 예를 들어, 매개변수 없이 실행되는 sp_helplogins 저장 프로시저는 계정 이름에 대한 두 개의 보고서를 생성합니다. (영어)로그인) 및 각 데이터베이스의 해당 사용자 (영어)사용자).

EXEC sp_helplogins;

시스템 저장 프로시저를 사용하여 수행되는 작업에 대한 아이디어를 표로 제공합니다. 10.6은 몇 가지 예를 보여줍니다. 전체적으로 SQL Server에는 천 개 이상의 시스템 저장 프로시저가 있습니다.

표 10.6

SQL Server 시스템 저장 프로시저의 예

사용자는 사용자 데이터베이스와 임시 개체에 대한 데이터베이스에 저장 프로시저를 만들 수 있습니다. 후자의 경우 저장 프로시저는 다음과 같습니다. 일시적인.임시 테이블과 마찬가지로 임시 저장 프로시저의 이름은 로컬 임시 저장 프로시저인 경우 "#" 접두사로 시작하고 전역 임시 저장 프로시저인 경우 "##"으로 시작해야 합니다. 로컬 임시 프로시저는 생성된 연결 내에서만 사용할 수 있으며 전역 프로시저는 다른 연결 내에서도 사용할 수 있습니다.

프로그래밍 가능한 SQL Server 개체는 Transact-SQL 도구를 사용하거나 어셈블리를 사용하여 만들 수 있습니다. (영어)어셈블리) Microsoft .Net Framework의 CRL(공용 언어 런타임) 환경에서. 이 자습서에서는 첫 번째 방법만 고려합니다.

저장 프로시저를 생성하려면 CREATE PROCEDURE 문(PROC로 축약될 수 있음)을 사용합니다. 형식은 다음과 같습니다.

CREATE(프로세스 I 절차) proc_name [ ; 숫자]

[(g매개변수 데이터 유형 )

["기본값] |

[와 함께 [ ,...N ] ]

[ 복제용 ]

그대로 ([ 시작 ] SQL_statement [;] [ ...n ] [ 끝 ] )

저장 프로시저(또는 트리거, 함수, 보기)가 ENCRYPTION 옵션으로 생성되면 해당 코드는 텍스트를 읽을 수 없는 방식으로 변환됩니다. 동시에 에 언급된 바와 같이 사용된 알고리즘은 이전 버전의 SQL Server에서 가져온 것이며 신뢰할 수 있는 보호 알고리즘으로 간주될 수 없습니다. 역변환을 신속하게 수행할 수 있는 유틸리티가 있습니다.

RECOMPILE 옵션은 프로시저가 호출될 때마다 시스템이 텍스트를 다시 컴파일하도록 지정합니다. 일반적인 경우에는 처음 실행할 때 컴파일된 프로시저가 캐시에 저장되어 성능을 높일 수 있습니다.

EXECUTE AS는 프로시저가 실행될 보안 컨텍스트를 정의합니다. 다음으로 값 중 하나 f CALLER | 셀프 | 소유자 | "사용자 이름"). CALLER는 기본값이며 이 모듈을 호출하는 사용자의 보안 컨텍스트에서 코드가 실행됨을 의미합니다. 따라서 사용자는 프로그래밍 가능한 개체 자체에 대한 권한뿐만 아니라 영향을 받는 다른 데이터베이스 개체에 대한 권한도 있어야 합니다. EXECUTE AS SELF는 프로그래밍 가능한 개체를 생성하거나 수정하는 사용자의 컨텍스트를 사용하는 것을 의미합니다. OWNER는 코드가 프로시저의 현재 소유자 컨텍스트에서 실행되도록 지정합니다. 소유자가 정의되어 있지 않으면 해당 스키마가 속한 스키마의 소유자로 간주됩니다. EXECUTE AS "user_name"을 사용하면 사용자 이름(작은따옴표)을 명시적으로 지정할 수 있습니다.

프로시저에 대해 매개변수를 지정할 수 있습니다. 프로시저에 값을 전달하는 데 사용되는 지역 변수입니다. 매개변수가 다음과 같이 선언된 경우 예어 OUTPUT(또는 줄여서 OUT)은 출력입니다. 완료 후 프로시저에서 제공된 값은 프로시저를 호출한 프로그램에서 사용할 수 있습니다. READONLY 키워드는 저장 프로시저 내에서 매개변수 값을 변경할 수 없음을 의미합니다.

매개변수에는 기본값을 할당할 수 있으며, 프로시저를 호출할 때 매개변수 값이 명시적으로 지정되지 않은 경우 이 값이 사용됩니다. 예를 고려하십시오.

CREATE PROC surma(@a int, @b int=0,

©결과 정수 출력) AS

SET @결과=0a+0b

@b 매개변수의 기본값은 0이고 @result 매개변수는 출력 매개변수인 세 개의 매개변수가 있는 프로시저를 만들었습니다. 이를 통해 값이 호출 프로그램으로 반환됩니다. 수행되는 작업은 매우 간단합니다. 출력 매개변수는 두 입력의 합계 값을 받습니다.

SQL에서 작업할 때 서버 관리 Studio 생성 저장 프로시저는 프로그래밍 가능한 DB 개체 섹션에서 찾을 수 있습니다. (영어)저장 프로시저 섹션에서 프로그래밍 가능성)을 참조하십시오(그림 10.2).

프로시저를 호출할 때 변수와 상수를 모두 입력 매개변수로 사용할 수 있습니다. 두 가지 예를 살펴보겠습니다. 첫 번째에서 프로시저의 입력 매개변수는 상수에 의해 명시적으로 설정되고 키워드 OUTPUT은 호출의 출력 매개변수에 대해 지정됩니다. 두 번째 옵션에서는 변수의 값이 첫 번째 입력 매개변수로 사용되며 두 번째 매개변수는 기본값을 사용해야 하는 DEFAULT 키워드를 사용하여 지정됩니다.

쌀. 10.2.

선언 @with int;

EXEC 합계 10,5,@c 출력;

PRINT0c; - 15가 표시됩니다.

선언 Gi int = 5;

- 호출 시 기본값 사용

EXEC 요약 Gi, DEFAULT, 0초 OUTPUT;

PRINT0c; - 5가 표시됩니다.

이제 프로시저가 끝나는 반환 코드를 분석하는 예를 살펴보겠습니다. 주어진 연도 범위에 Bookl 테이블에 몇 권의 책이 출판되었는지 계산해야 합니다. 이 경우 시작 연도가 종료 연도보다 크면 프로시저가 "1"을 반환하고 계산하지 않고, 그렇지 않으면 책 수를 계산하고 0을 반환합니다.

CREATE PROC dbo.rownum(0FirsYear int, GLastYear int, 0result int OUTPUT) AS

IF 0FirsYear>0LastYear RETURN 1

SET @result= (dbo.Bookl에서 COUNT(*) 선택

0FirsYear와 0LastYear 사이의 위치) ;

반환 코드가 정수 변수 0ret에 저장되고 그 값이 분석된 후(이 경우 1이 됨) 이 프로시저를 호출하는 변형을 고려하십시오. 연산자에서 사용 인쇄 기능 CAST는 정수 Gres 변수의 값을 문자열 유형으로 변환하는 데 사용됩니다.

DECLARE 0ret int, Gres int

EXEC Gret = rownum 2004, 2002, Gres OUT;

IF 0ret=l PRINT "시작 연도가 끝 연도보다 큼"

PRINT "도서 수"+ CAST(Gres as varchar(20))

저장 프로시저는 테이블에서 데이터를 읽을 수 있을 뿐만 아니라 데이터를 수정하고 테이블 및 기타 여러 데이터베이스 개체를 생성할 수도 있습니다.

그러나 스키마, 함수, 트리거, 프로시저 및 보기는 저장 프로시저에서 만들 수 없습니다.

다음 예는 이러한 가능성과 임시 개체의 범위와 관련된 문제를 보여줍니다. 다음 저장 프로시저는 임시 테이블 #Tab2의 존재를 확인합니다. 이 테이블이 없으면 생성합니다. 이후 테이블 #Tab2에 두 컬럼의 값을 입력하고 SELECT 문으로 테이블의 내용을 표시한다.

CREATE PROC My_Procl(@id int, @name varchar(30))

IF OBJECT_ID("tempdb.dbo.#Tab21) IS NULL

INSERT INTO dbo.#Tab2 (id, name)VALUES (0id, 0name)

선택 * dbo에서. #탭2 - #1

저장 프로시저를 처음 호출하기 전에 저장 프로시저에 사용되는 임시 테이블 #Tab2를 생성해 보겠습니다. EXEC 연산자에 주의하십시오. 이전 예에서 매개변수는 "위치별" 프로시저에 전달되었지만 이 경우 매개변수 전달을 위한 다른 형식이 사용됩니다. "이름별", 매개변수 이름 및 값이 명시적으로 표시됩니다.

CREATE TABLE dbo.#Tab2(id int, 이름 varchar(30));

EXEC My_Procl 0name = "lvan", 0id = 2;

SELECT * FROM dbo.#Tab2; –#2

위의 예에서 SELECT 문은 두 번 실행됩니다. 첫 번째 - 프로시저 내에서, 두 번째 - 호출 코드 조각(주석 "No. 2"로 표시됨)에서.

두 번째 프로시저 호출 전에 #Tab2 임시 테이블을 삭제합시다. 그런 다음 저장 프로시저에서 동일한 이름의 임시 테이블이 생성됩니다.

DROP TABLE dbo.#Tab2;

EXEC My_Procl 0name="아이반", 0id=2;

SELECT * FROM dbo.#Tab2; –#2

이 경우 프로시저 내의 SELECT 문("Xa 1" 주석 포함)만 데이터를 표시합니다. SELECT "#2"를 실행하면 저장 프로시저에서 만든 임시 테이블이 프로시저가 반환될 때 tempdb 데이터베이스에서 이미 삭제되기 때문에 오류가 발생합니다.

DROP PROCEDURE 문을 사용하여 저장 프로시저를 삭제할 수 있습니다. 그 형식은 아래와 같습니다. 하나의 명령문으로 여러 저장 프로시저를 쉼표로 구분하여 나열하여 삭제할 수 있습니다.

DROP (PROC I PROCEDURE) ( 절차 ) [

예를 들어, 이전에 생성된 summa 프로시저를 제거해 보겠습니다.

DROP PROC 합계;

ALTER PROCEDURE 문(허용)을 사용하여 기존 프로시저를 변경할 수 있습니다(실제로 재정의).

약어 PROC). ALTER 키워드를 제외하고 문장의 형식은 CREATE PROCEDURE의 형식과 거의 같다. 예를 들어 프로시저 dbo를 변경해 보겠습니다. 소유자의 보안 컨텍스트에서 실행되도록 설정하여 rownum

ALTER PROC dbo.rownum(SFirsYear int,

SLastYear 정수, 결과 정수 출력)

WITH EXECUTE AS 소유자 - 옵션 설정

IF 0FirsYear>0LastYear RETURN 1 ELSE BEGIN

SET 0result= (dbo.Bookl에서 COUNT(*) 선택

SFirsYear와 SLastYear 사이);

어떤 경우에는 명령을 동적으로 생성하고 데이터베이스 서버에서 실행해야 할 수도 있습니다. 이 작업은 EXEC 연산자를 사용하여 해결할 수도 있습니다. 다음 예에서는 Year 속성이 변수에 지정된 값과 같다는 조건에 따라 Bookl 테이블에서 레코드를 선택합니다.

선언 0y 정수 = 2000;

EXEC("SELECT * FROM dbo.Bookl WHERE = " [이메일 보호됨]) ;

동적으로 생성된 명령의 실행은 "SQL 주입"과 같은 컴퓨터 공격의 구현을 위한 전제 조건을 만듭니다. (영어) SQL 인젝션). 공격의 본질은 공격자가 동적으로 생성된 쿼리에 자신의 SQL 코드를 삽입한다는 것입니다. 이것은 일반적으로 사용자 입력 결과에서 인라인 매개변수를 가져올 때 발생합니다.

이전 예제를 약간 변경해 보겠습니다.

선언 0y varchar(100);

SET 0y="2OOOO"; - 이것은 우리가 사용자로부터 받은 것입니다.

사용자로부터 SET 문에 할당된 문자열 값을 받았다고 가정하면(예를 들어 웹 응용 프로그램을 통해 어떤 방식으로든) 이 예제는 코드의 "일반" 동작을 보여줍니다.

선언 0y varchar(100);

SET 0y="2000; dbo.Book2에서 삭제"; – 주사

EXEC("SELECT * FROM dbo.Book2 WHERE="+0y);

이러한 경우에는 가능하면 sp_executcsql 시스템 저장 프로시저를 사용하는 것이 좋습니다. 이를 통해 매개변수 유형을 제어할 수 있습니다. SQL 주입. 형식을 자세히 고려하지 않고 앞에서 제시한 것과 유사한 예를 분석합니다.

EXECUTE sp_executesql

N"SELECT * FROM dbo.Bookl WHERE = 0y",

이것은 쿼리에 사용되는 매개변수의 유형을 명시적으로 지정하며 SQL Server는 실행 중에 이를 제어합니다. 따옴표 앞의 문자 "N"은 프로시저에서 요구하는 대로 이것이 유니코드 리터럴 상수임을 나타냅니다. 매개변수는 상수 값뿐만 아니라 다른 변수의 값도 할당할 수 있습니다.

저장 프로시저의 개념이 정의됩니다. 매개변수가 있는 저장 프로시저를 생성, 수정 및 사용하는 예가 제공됩니다. 입력 및 출력 매개변수의 정의가 제공됩니다. 저장 프로시저를 만들고 호출하는 예가 제공됩니다.

저장 프로시저의 개념

저장 프로시저상호 연결된 그룹입니다 SQL 문, 이를 사용하면 프로그래머의 작업이 더 쉽고 유연해집니다. 저장 프로시저개별 SQL 문의 시퀀스보다 훨씬 간단합니다. 저장 프로시저는 하나 이상의 SQL 문 또는 함수로 구성되고 컴파일된 형식으로 데이터베이스에 저장되는 명령 집합입니다. 데이터베이스에서 실행 저장 프로시저개별 SQL 문 대신 사용자에게 다음과 같은 이점을 제공합니다.

  • 필요한 연산자가 이미 데이터베이스에 있습니다.
  • 그들은 모두 무대를 통과했다 구문 분석실행 가능한 형식입니다. ~ 전에 저장 프로시저 실행 SQL Server는 실행 계획을 생성하고 최적화하고 컴파일합니다.
  • 저장 프로시저지원하다 모듈식 프로그래밍, 큰 작업을 독립적이고 작고 관리하기 쉬운 부분으로 나눌 수 있습니다.
  • 저장 프로시저다른 사람들을 유발할 수 있습니다 저장 프로시저및 기능;
  • 저장 프로시저다른 유형의 응용 프로그램에서 호출할 수 있습니다.
  • 대개, 저장 프로시저일련의 개별 명령문보다 빠르게 실행됩니다.
  • 저장 프로시저사용하기 쉬움: 수십에서 수백 개의 명령으로 구성될 수 있지만 실행하려면 원하는 이름만 지정하면 됩니다. 저장 프로시저. 이렇게 하면 클라이언트에서 서버로 보내는 요청의 크기를 줄일 수 있으므로 네트워크의 부하를 줄일 수 있습니다.

프로시저가 실행되는 동일한 위치에 프로시저를 저장하면 네트워크를 통해 전송되는 데이터의 양이 줄어들고 전체 시스템 성능이 향상됩니다. 신청 저장 프로시저유지보수 간소화 소프트웨어 시스템변경합니다. 일반적으로 규칙 및 데이터 처리 알고리즘 형태의 모든 무결성 제약 조건은 데이터베이스 서버에서 구현되며 최종 응용 프로그램에서 집합으로 사용할 수 있습니다. 저장 프로시저, 데이터 처리 인터페이스를 나타냅니다. 데이터의 무결성을 보장하고 보안을 위해 애플리케이션은 일반적으로 데이터에 직접 액세스하지 않습니다. 모든 작업은 하나 또는 다른 하나를 호출하여 수행됩니다. 저장 프로시저.

이 접근 방식을 사용하면 모든 네트워크 사용자가 즉시 사용할 수 있는 데이터 처리 알고리즘을 매우 쉽게 수정할 수 있으며 응용 프로그램 자체를 변경하지 않고도 시스템을 확장할 수 있습니다. 저장 프로시저데이터베이스 서버에서. 개발자는 응용 프로그램을 다시 컴파일하고 복사본을 만들고 사용자에게 새 버전으로 작업해야 할 필요성을 지시할 필요가 없습니다. 사용자는 시스템 변경 사항을 인지하지 못할 수도 있습니다.

저장 프로시저테이블이나 다른 데이터베이스 개체와 독립적으로 존재합니다. 그들은 클라이언트 프로그램에 의해 호출되며 다른 저장 프로시저또는 트리거. 개발자는 액세스 권한을 관리할 수 있습니다. 저장 프로시저, 실행을 허용하거나 금지합니다. 코드 변경 저장 프로시저소유자 또는 고정 데이터베이스 역할의 구성원만 허용합니다. 필요한 경우 한 사용자에서 다른 사용자에게 소유권을 이전할 수 있습니다.

MS SQL Server 환경의 저장 프로시저

SQL Server로 작업할 때 사용자는 특정 작업을 구현하는 고유한 프로시저를 만들 수 있습니다. 저장 프로시저본격적인 데이터베이스 개체이므로 각각은 특정 데이터베이스에 저장됩니다. 다이렉트 콜 저장 프로시저프로시저가 있는 데이터베이스 컨텍스트에서 실행되는 경우에만 가능합니다.

저장 프로시저의 유형

SQL Server에는 여러 유형이 있습니다. 저장 프로시저.

  • 전신 저장 프로시저다양한 관리 작업을 수행하도록 설계되었습니다. 거의 모든 서버 관리 작업은 도움을 받아 수행됩니다. 시스템이라고 할 수 있습니다 저장 프로시저시스템 테이블에 대한 작업을 제공하는 인터페이스로, 궁극적으로 사용자 및 시스템 데이터베이스의 시스템 테이블에서 데이터를 변경, 추가, 삭제 및 검색하는 것으로 귀결됩니다. 전신 저장 프로시저접두사 sp_ 가 붙고 시스템 데이터베이스에 저장되며 다른 데이터베이스의 컨텍스트에서 호출될 수 있습니다.
  • 관습 저장 프로시저특정 작업을 구현합니다. 저장 프로시저- 완전한 데이터베이스 객체. 그 결과 각각의 저장 프로시저실행되는 특정 데이터베이스에 있습니다.
  • 일시적인 저장 프로시저짧은 시간 동안만 존재하며 그 후 서버에 의해 자동으로 파괴됩니다. 그들은 로컬과 글로벌로 나뉩니다. 지역 임시 저장 프로시저생성된 연결에서만 호출할 수 있습니다. 이러한 프로시저를 작성할 때 단일 # 문자로 시작하는 이름을 지정해야 합니다. 모든 임시 개체와 마찬가지로 저장 프로시저이 유형의 파일은 사용자가 서버의 연결을 끊거나 다시 시작하거나 중지할 때 자동으로 삭제됩니다. 글로벌 임시 저장 프로시저동일한 절차를 가진 모든 서버 연결에 사용할 수 있습니다. 정의하려면 ## 문자로 시작하는 이름을 지정하는 것으로 충분합니다. 이러한 프로시저는 서버가 다시 시작되거나 중지되거나 컨텍스트에서 생성된 연결이 닫힐 때 삭제됩니다.

저장 프로시저 생성, 수정 및 삭제

창조 저장 프로시저다음 작업을 해결하는 것이 포함됩니다.

  • 유형 정의 저장 프로시저: 임시 또는 관습. 또한 자신의 시스템을 만들 수 있습니다. 저장 프로시저, sp_ 접두사가 붙은 이름을 지정하고 시스템 기반데이터. 이러한 절차는 로컬 서버의 모든 데이터베이스 컨텍스트에서 사용할 수 있습니다.
  • 액세스 계획. 만드는 동안 저장 프로시저데이터베이스 개체에 대한 액세스 권한은 해당 개체를 만든 사용자와 동일합니다.
  • 정의 저장 프로시저 매개변수. 대부분의 프로그래밍 언어에 포함된 절차와 마찬가지로 저장 프로시저입력 및 출력 매개변수가 있을 수 있습니다.
  • 코드 개발 저장 프로시저. 프로시저 코드에는 다른 명령 호출을 포함하여 모든 SQL 명령의 시퀀스가 ​​포함될 수 있습니다. 저장 프로시저.

새 항목 만들기 및 기존 항목 수정 저장 프로시저다음 명령으로 수행됩니다.

<определение_процедуры>::= (CREATE | ALTER ) PROC procedure_name [;number] [(@parameter_name 데이터 유형 ) [=default] ][,...n] AS sql_statement [...n]

이 명령의 매개변수를 고려하십시오.

sp_ ​​, # , ## 접두사를 사용하여 생성된 프로시저를 시스템 또는 임시 프로시저로 정의할 수 있습니다. 명령 구문에서 알 수 있듯이 생성된 프로시저가 속할 소유자의 이름과 해당 프로시저가 배치되어야 하는 데이터베이스의 이름을 지정할 수 없습니다. 따라서 생성 된 것을 수용하기 위해 저장 프로시저특정 데이터베이스에서는 해당 데이터베이스의 컨텍스트에서 CREATE PROCEDURE 명령을 실행해야 합니다. 몸에서 다룰 때 저장 프로시저축약된 이름은 데이터베이스 이름을 지정하지 않고 동일한 데이터베이스의 개체에 사용할 수 있습니다. 다른 데이터베이스에 있는 개체를 참조하려면 데이터베이스 이름을 지정해야 합니다.

이름에 있는 숫자는 식별 번호 저장 프로시저, 프로시저 그룹에서 고유하게 정의합니다. 절차 관리의 편의를 위해 논리적으로 동일한 유형 저장 프로시저같은 이름이지만 다른 식별 번호를 부여하여 그룹화할 수 있습니다.

생성된 입력 및 출력 데이터를 전달하려면 저장 프로시저매개변수를 사용할 수 있으며, 그 이름은 지역 변수의 이름과 같이 @ 기호로 시작해야 합니다. 하나 저장 프로시저여러 옵션을 쉼표로 구분하여 지정할 수 있습니다. 프로시저의 본문은 이름이 프로시저의 매개변수 이름과 동일한 지역 변수를 사용해서는 안 됩니다.

해당하는 데이터 유형을 결정하려면 저장 프로시저 매개변수, 모든 유형이 적합합니다. SQL 데이터, 사용자 정의를 포함합니다. 그러나 CURSOR 데이터 유형은 다음으로만 사용할 수 있습니다. 출력 매개변수 저장 프로시저, 즉. 키워드 OUTPUT .

OUTPUT 키워드의 존재는 해당 매개변수가 저장 프로시저. 그러나 이것이 매개 변수가 값을 전달하는 데 적합하지 않다는 것을 의미하지는 않습니다. 저장 프로시저. OUTPUT 키워드를 지정하면 서버가 종료하도록 지시합니다. 저장 프로시저프로시저가 매개변수 값으로 호출될 때 지정된 지역 변수에 매개변수의 현재 값을 할당합니다. OUTPUT 키워드를 지정할 때 프로시저 호출 시 해당 매개변수의 값은 로컬 변수를 통해서만 설정할 수 있습니다. 일반 매개변수에 허용되는 표현식이나 상수는 허용되지 않습니다.

VARYING 키워드는 CURSOR 유형의 OUTPUT 매개변수와 함께 사용됩니다. 그것은 그것을 정의합니다 출력 매개변수결과 집합이 됩니다.

DEFAULT 키워드는 해당하는 값입니다. 기본 설정. 따라서 프로시저를 호출할 때 해당 매개변수의 값을 명시적으로 지정할 수 없습니다.

서버는 쿼리 실행 계획과 컴파일된 코드를 캐싱하기 때문에 다음에 프로시저를 호출할 때 이미 준비된 값을 사용합니다. 그러나 어떤 경우에는 여전히 프로시저 코드를 다시 컴파일해야 합니다. RECOMPILE 키워드를 지정하면 시스템이 실행 계획을 생성하도록 지시합니다. 저장 프로시저호출될 때마다.

FOR REPLICATION 매개변수는 데이터를 복제하고 생성된 데이터를 포함할 때 필요합니다. 저장 프로시저출판물의 기사로.

ENCRYPTION 키워드는 서버에 코드를 암호화하도록 지시합니다. 저장 프로시저, 저작물을 구현하는 저작권 알고리즘의 사용에 대한 보호를 제공할 수 있습니다. 저장 프로시저.

AS 키워드는 실제 본문의 시작 부분에 배치됩니다. 저장 프로시저, 즉. 이 작업 또는 그 작업을 구현하는 데 도움이 되는 일련의 SQL 명령입니다. 거의 모든 SQL 명령은 프로시저의 본문에서 사용할 수 있으며 트랜잭션을 선언할 수 있으며 잠금을 설정할 수 있으며 기타를 호출할 수 있습니다. 저장 프로시저. 에서 나가다 저장 프로시저 RETURN 명령으로 수행할 수 있습니다.

저장 프로시저 삭제명령에 의해 수행:

DROP PROCEDURE(procedure_name) [,...n]

저장 프로시저 실행

을 위한 저장 프로시저 실행명령이 사용됩니다.

[[ EXEC [ UTE] 프로시저 이름 [; 번호] [[@parameter_name=](값 | @변수 이름) |][,...n]

전화가 오면 저장 프로시저패키지의 유일한 명령이 아닌 경우 EXECUTE 명령이 있어야 합니다. 또한 이 명령은 다른 프로시저 또는 트리거의 본문에서 프로시저를 호출하는 데 필요합니다.

프로시저를 호출할 때 OUTPUT 키워드의 사용은 선언된 매개변수에 대해서만 허용됩니다. 프로시저 생성 OUTPUT 키워드로.

DEFAULT 키워드가 프로시저 호출에 지정되면 DEFAULT 키워드가 사용됩니다. 기본값. 당연히 지정된 단어 DEFAULT는 정의된 매개변수에만 허용됩니다. 기본값.

EXECUTE 명령의 구문에서 프로시저를 호출할 때 매개변수 이름을 생략할 수 있음을 알 수 있습니다. 그러나 이 경우 사용자는 매개변수에 대한 값을 나열할 때 나열된 순서와 동일한 순서로 지정해야 합니다. 프로시저 생성. 매개변수에 할당 기본값, 열거할 수 없는 경우 단순히 건너뜁니다. 매개변수를 생략해야 하는 경우 기본값, 호출할 때 매개변수 이름을 명시적으로 지정하는 것으로 충분합니다. 저장 프로시저. 또한 이러한 방식으로 매개 변수와 해당 값을 임의의 순서로 나열할 수 있습니다.

프로시저가 호출되면 값이 있는 매개변수 이름이 지정되거나 매개변수 이름이 없는 값만 지정됩니다. 그들의 조합은 허용되지 않습니다.

예 12.1. 매개변수가 없는 절차. Ivanov가 구매한 상품의 이름과 가격을 얻는 절차를 개발하십시오.

CREATE PROC my_proc1 AS SELECT Item.Name, Item.Price*Trade.Quantity AS Cost, Customer.Last Name FROM Customer INNER JOIN(Item INNER JOIN 트랜잭션 ON Item.ItemId=Trade.ItemId) ON Customer.CustomerCode=Trade.CustomerCode WHERE 고객 .Lastname='Ivanov' 예 12.1. Ivanov가 구매한 상품의 이름과 가격을 얻는 절차.

을 위한 절차 호출명령을 사용할 수 있습니다.

EXEC my_proc1 또는 my_proc1

프로시저는 데이터 집합을 반환합니다.

예 12.2. 매개변수가 없는 절차. 1등급 품목의 가격을 10% 낮추는 절차를 만듭니다.

을 위한 절차 호출명령을 사용할 수 있습니다.

EXEC my_proc2 또는 my_proc2

프로시저는 데이터를 반환하지 않습니다.

예 12.3. 입력 매개변수가 있는 절차. 주어진 고객이 구매한 항목의 이름과 가격을 가져오는 프로시저를 만듭니다.

CREATE PROC my_proc3 @k VARCHAR(20) AS SELECT Item.Name, Item.Price*Trade.Quantity AS Cost, Customer.LastName FROM Customer INNER JOIN(Item INNER JOIN Trade ON Item.ItemID=Trade.ItemID) ON Client.CustomerID =Deal.ClientID WHERE Client.LastName [이메일 보호됨] 예 12.3. 특정 고객이 구매한 항목의 이름과 가격을 가져오는 절차입니다.

을 위한 절차 호출명령을 사용할 수 있습니다.

EXEC my_proc3 "Ivanov" 또는 my_proc3 @k="Ivanov"

예 12.4.. 지정된 %에 따라 지정된 유형의 제품 가격을 낮추는 절차를 만듭니다.

을 위한 절차 호출명령을 사용할 수 있습니다.

EXEC my_proc4 "와플", 0.05 또는 EXEC my_proc4 @t="와플", @p=0.05

예 12.5. 입력 매개변수가 있는 절차및 기본값. 지정된 %에 따라 지정된 유형의 제품 가격을 낮추는 절차를 만듭니다.

CREATE PROC my_proc5 @t VARCHAR(20)='Candy`, @p FLOAT=0.1 AS UPDATE 항목 SET 가격=가격*( [이메일 보호됨]) WHERE 유형 [이메일 보호됨] 예 12.5. 입력 매개변수 및 기본값이 있는 절차. 지정된 %에 따라 지정된 유형의 제품 가격을 낮추는 절차를 만듭니다.

을 위한 절차 호출명령을 사용할 수 있습니다.

EXEC my_proc5 "Waffle", 0.05 또는 EXEC my_proc5 @t="Waffle", @p=0.05 또는 EXEC my_proc5 @p=0.05

이 경우 과자의 가격이 하락합니다(프로시저 호출 시 유형 값을 지정하지 않고 기본적으로 가져옴).

후자의 경우 프로시저를 호출할 때 두 매개변수(유형 및 백분율 모두)가 지정되지 않고 기본적으로 해당 값이 사용됩니다.

예 12.6. 입력 및 출력 매개변수가 있는 절차. 특정 월에 판매된 총 상품 비용을 결정하는 프로시저를 작성하십시오.

CREATE PROC my_proc6 @m INT, @s FLOAT OUTPUT AS SELECT @s=Sum(Item.Price*Trade.Quantity) FROM Item INNER JOIN Trade ON Item.ItemID=Trade.ItemID GROUP BY Month(Trade.Date) HAVING Month( 거래일) [이메일 보호됨] 예 12.6. 입력 및 출력 매개변수가 있는 절차. 특정 월에 판매된 총 상품 비용을 결정하는 프로시저를 작성하십시오.

을 위한 절차 호출명령을 사용할 수 있습니다.

선언 @st FLOAT EXEC my_proc6 1,@st 출력 선택 @st

이 명령 블록을 사용하면 1월에 판매된 상품 원가를 결정할 수 있습니다( 입력 매개변수월은 1)로 설정됩니다.

주어진 직원이 근무하는 회사에서 구매한 총 상품 수량을 결정하는 프로시저를 작성하십시오.

먼저 직원이 근무하는 회사를 결정하는 절차를 개발합니다.

예 12.7.용법 중첩 프로시저. 주어진 직원이 근무하는 회사에서 구매한 총 상품 수량을 결정하는 프로시저를 작성하십시오.

그런 다음 관심 회사가 구매한 총 상품 금액을 계산하는 프로시저를 작성합니다.

CREATE PROC my_proc8 @fam VARCHAR(20), @kol INT OUTPUT AS DECLARE @firm VARCHAR(20) EXEC my_proc7 @fam,@firm OUTPUT SELECT @kol=Sum(Trade.Quantity) FROM Client INNER JOIN Trade ON Client.ClientCode= Deal.ClientCode GROUP BY Client.Company HAVING Client.Company [이메일 보호됨] 예 12.7. 주어진 직원이 근무하는 회사에서 구매한 총 상품 수량을 결정하는 프로시저를 작성하십시오.

프로시저는 다음 명령을 사용하여 호출됩니다.

DECLARE @k INT EXEC my_proc8 'Ivanov',@k OUTPUT SELECT @k

SQL - 15단원. 저장 프로시저. 1 부.

일반적으로 데이터베이스로 작업할 때 동일한 쿼리 또는 일련의 순차적 쿼리를 사용합니다. 저장 프로시저를 사용하면 쿼리 시퀀스를 결합하여 서버에 저장할 수 있습니다. 이것은 매우 편리한 도구이며 이제 볼 수 있습니다. 구문부터 시작하겠습니다.

CREATE PROCEDURE procedure_name(매개변수) 시작 문 끝

매개변수는 호출될 때 프로시저에 전달할 데이터이고 연산자는 쿼리 자체입니다. 첫 번째 절차를 작성하고 편리한지 확인합시다. 레슨 10에서 상점 데이터베이스에 새 레코드를 추가할 때 표준 보기 추가 쿼리를 사용했습니다.

INSERT INTO 고객(이름, 이메일) VALUE("Ivanov Sergey", " [이메일 보호됨]");

왜냐하면 유사한 요청새로운 고객을 추가해야 할 때마다 사용할 것이므로 절차의 형태로 정리하는 것이 매우 적절합니다.

CREATE PROCEDURE ins_cust(n CHAR(50), e CHAR(50)) 시작 에 집어 넣다고객(이름, 이메일) 값(n, e); 끝

매개변수가 어떻게 설정되는지 주목하십시오. 매개변수에 이름을 지정하고 유형을 지정해야 하며, 프로시저의 본문에서 이미 매개변수 이름을 사용하고 있습니다. 하나의 뉘앙스. 기억하시겠지만, 세미콜론은 요청의 끝을 의미하고 이를 실행으로 보냅니다. 이 경우에는 허용되지 않습니다. 따라서 프로시저를 작성하기 전에 c 구분 기호를 재정의해야 합니다. 요청이 미리 전송되지 않도록 "//"로 변경합니다. 이것은 DELIMITER // 연산자를 사용하여 수행됩니다.

따라서 이제 명령이 // 다음에 실행되어야 함을 DBMS에 표시했습니다. 구분자는 하나의 세션에 대해서만 재정의된다는 점을 기억해야 합니다. 다음에 MySql로 작업할 때 구분 기호는 다시 세미콜론이 되며 필요한 경우 다시 재정의해야 합니다. 이제 절차를 배치할 수 있습니다.

CREATE PROCEDURE ins_cust(n CHAR(50), e CHAR(50)) 시작 삽입 고객(이름, 이메일) 값(n, e); 끝 //


따라서 프로시저가 생성됩니다. 이제 새로운 고객을 입력해야 할 때 필요한 매개변수를 지정하여 호출하기만 하면 됩니다. 저장 프로시저를 호출하려면 CALL 문을 사용하고 프로시저 이름과 해당 매개변수를 사용합니다. 고객 테이블에 새 고객을 추가해 보겠습니다.

call ins_cust("Sychov Valery", " [이메일 보호됨]")//


매번 쓰는 것보다 훨씬 쉽다는 데 동의하십시오. 완전한 요청. 고객 테이블에 새 고객이 나타나는지 확인하여 절차가 제대로 작동하는지 확인해 보겠습니다.

표시됨, 절차가 작동하며 연산자를 사용하여 삭제할 때까지 항상 작동합니다. DROP PROCEDURE procedure_name.

수업 시작 부분에서 언급했듯이 절차를 통해 쿼리 시퀀스를 결합할 수 있습니다. 어떻게 했는지 봅시다. 11과에서 "Printing House" 공급업체가 우리에게 상품을 얼마에 가져왔는지 알고 싶었습니다. 이를 위해 중첩 쿼리, 조인, 계산된 열 및 보기를 사용해야 했습니다. 그리고 다른 공급자가 우리에게 상품을 얼마나 가져왔는지 알고 싶다면? 새 쿼리, 조인 등을 작성해야 합니다. 이 작업에 대해 저장 프로시저를 한 번 작성하는 것이 더 쉽습니다.

가장 쉬운 방법은 11과에서 이미 작성된 보기와 쿼리를 가져와서 저장 프로시저에 결합하고 다음과 같이 공급업체 식별자(id_vendor)를 입력 매개변수로 만드는 것 같습니다.

CREATE PROCEDURE sum_vendor(i INT) 시작 CREATE VIEW report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, price.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, 가격 WHERE magazine_incoming.id_product= price.id_product AND SELECT id_incoming FROM 수신 WHERE id_vendor=i); SELECT SUM(summa) FROM report_vendor; 끝 //

그러나 이 절차는 작동하지 않습니다. 요점은 보기는 매개변수를 사용할 수 없습니다.. 따라서 요청 순서를 약간 변경해야 합니다. 먼저 Incoming, Supply journal(magazine_incoming), 가격 세 테이블에서 공급업체 ID(id_vendor), 제품 ID(id_product), 수량(수량), 가격(가격) 및 합계(summa)를 표시하는 뷰를 생성합니다. (가격):

CREATE VIEW report_vendor AS SELECT Incoming.id_vendor, magazine_incoming.id_product, Magazine_incoming.quantity, price.price, magazine_incoming.quantity*prices.price AS summa FROM 수신, magazine_incoming, 가격 WHERE magazine_incoming.id_product= price.id_incoming= .id_incoming;

그런 다음 id_vendor=2와 같이 관심 있는 공급업체의 배송 금액을 합산하는 쿼리를 생성합니다.

이제 이 두 쿼리를 저장 프로시저로 결합할 수 있습니다. 여기서 입력 매개변수는 공급업체 식별자(id_vendor)가 되며 두 번째 쿼리로 대체되지만 뷰에는 대체되지 않습니다.

CREATE PROCEDURE sum_vendor(i INT) 시작 CREATE VIEW report_vendor AS SELECT Incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, 가격.price, magazine_incoming.quantity*prices.price AS summa FROM 들어오는, magazine_incoming, 가격 WHERE magazine_incoming.id_ .id_product AND magazine_incoming.id_incoming=incoming.id_incoming; SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; 끝 //


다른 입력 매개변수를 사용하여 프로시저의 작동을 확인하겠습니다.


보시다시피 프로시저는 한 번 실행된 다음 report_vendor 뷰가 이미 데이터베이스에 존재한다는 오류를 발생시킵니다. 프로시저가 처음 호출될 때 뷰를 생성하기 때문입니다. 두 번째로 액세스하면 뷰를 다시 생성하려고 시도하지만 이미 존재하므로 오류가 표시됩니다. 이를 방지하기 위해 두 가지 옵션이 있습니다.

첫 번째는 절차에서 관점을 취하는 것입니다. 즉, 뷰를 한 번 생성하고 프로시저는 뷰에만 액세스하지만 생성하지는 않습니다. 먼저 이미 생성된 프로시저와 보기를 삭제하는 것을 잊지 마십시오.

DROP PROCEDURE sum_vendor// DROP VIEW report_vendor// CREATE VIEW report_vendor AS SELECT Incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, price.price, magazine_incoming.quantity*prices.price AS summa FROM 들어오는, magazine_incoming, 가격 WHERE = price.id_product AND magazine_incoming.id_incoming=incoming.id_incoming// CREATE PROCEDURE sum_vendor(i INT) begin SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; 끝 //


작업 확인:

sum_vendor(1) 호출// sum_vendor(2) 호출// sum_vendor(3) 호출//


두 번째 옵션은 보기가 있는 경우 해당 보기를 삭제하는 명령을 프로시저에 직접 추가하는 것입니다.

CREATE PROCEDURE sum_vendor(i INT) 시작 DROP VIEW IF EXISTS report_vendor; CREATE VIEW report_vendor AS SELECT Incoming.id_vendor, magazine_incoming.id_product, Magazine_incoming.quantity, price.price, magazine_incoming.quantity*prices.price AS summa FROM 수신, magazine_incoming, 가격 WHERE magazine_incoming.id_product= price.id_incoming= .id_incoming; SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; 끝 //

이 옵션을 사용하기 전에 sum_vendor 프로시저를 제거하고 작동하는지 확인하는 것을 잊지 마십시오.

보시다시피 복잡한 쿼리 또는 해당 시퀀스는 저장 프로시저에 한 번 정렬한 다음 필요한 매개변수를 지정하여 액세스하기가 정말 쉽습니다. 이는 코드를 크게 줄이고 요청 작업을 보다 논리적으로 만듭니다.