Transact-SQL에서 커서 및 루프 사용. MySQL 저장 프로시저의 커서 SQL Server의 커서
DECLARE CURSOR 명령을 사용하면 조작을 위해 테이블에서 행별로 레코드를 검색할 수 있습니다. 이를 통해 SQL이 수행하는 기존 데이터 세트 처리 대신 행별 처리가 가능해졌습니다.
첫 번째 근사치로 커서 작업 시 다음 단계가 사용됩니다.
커서는 DECLARE 명령으로 생성됩니다. 커서는 OPEN 명령으로 열립니다.
커서 작업은 FETCH 명령을 사용하여 수행됩니다. 커서는 CLOSE 명령으로 닫힙니다.
DECLARE CURSOR 명령은 SELECT 문을 지정합니다. SELECT 문에서 반환된 각 행은 개별적으로 검색하고 처리할 수 있습니다. 다음 Oracle 예제에서는 여러 다른 변수와 함께 선언 블록에서 커서를 선언합니다. 그런 다음 후속 BEGIN...END 블록에서 커서가 열리고 선택이 이루어지며 커서가 닫힙니다.
CURSOR title_price_cursor IS SELECT 제목, 가격 FROM 제목
가격이 NULL이 아닌 경우 title_price_val title_price_cursor ROWTYPE; new_price NUMBER(10.2);
OPEN 제목_가격_커서;
title_price_cur-sor INTO title_price_val을 가져옵니다.
new_price:= "title_price_val.price" * 1.25 new_title_price 값에 삽입
(title_price_val.title, new_price) CLOSE title_price_cursor; 끝;
이 예제에서는 PL/SQL을 사용하므로 이 책에서는 코드의 대부분을 설명하지 않습니다. 그러나 DECLARE 블록은 커서 선언을 명확하게 표시합니다. 실행 가능한 PL/SQL 블록에서는 OPEN 명령으로 커서를 초기화하고, FETCH 명령으로 값을 검색한 후, 마지막으로 CLOSE 명령으로 커서를 닫습니다.
SELECT 문은 커서의 기초이므로 DECLARE CURSOR 문에 포함하기 전에 철저히 테스트하는 것이 좋습니다. SELECT 문은 기본 테이블이나 뷰에서 작동할 수 있습니다. 따라서 읽기 전용 커서는 업데이트할 수 없는 뷰에서 작동할 수 있습니다. SELECT 문에는 ORDER BY, GROUP BY 및 HAVING과 같은 절이 포함될 수 있습니다. 단, 이러한 절은 소스 테이블을 업데이트하지 않습니다. 커서가 FOR UPDATE로 정의된 경우 SELECT 문에서 해당 절을 제거하는 것이 좋습니다.
로컬 커서는 저장 프로시저에 대한 출력 매개변수로 사용되는 경우가 많습니다. 따라서 저장 프로시저에서 커서를 정의하고 채울 수 있으며 이를 호출하는 일괄 작업이나 저장 프로시저에 전달할 수 있습니다.
다음의 간단한 DB2 예에서는 admin_group "XO1"에서 부서 번호, 부서 이름 및 관리자 번호를 조회하는 커서를 선언합니다.
DECLARE dept_cursor 커서
FOR SELECT 부서_nbr, 부서_이름, mgr_nbr
admin_group="X01"은 어디에 있습니까?
ORDER BY d"ept_name ASC, dept_nbr DESC, mgr_nbr DESC;
다음 Microsoft SQL Server 예제에서는 게시자 테이블에 대한 커서를 선언하고 엽니다. 커서는 게시자 테이블에서 SELECT 문과 일치하는 첫 번째 레코드를 선택하고 이를 다른 테이블에 삽입합니다. 그런 다음 모든 레코드가 처리될 때까지 다음 레코드로 이동한 다음 다음 레코드로 이동합니다. 마지막으로 커서가 닫히고 메모리가 해제됩니다. DEALLOCATE 명령은 Microsoft SQL Server에서만 사용됩니다.
@publisher_name VARCHAR(20) 선언
DECLARE pub_cursor CURSOR FOR SELECT pub_name FROM 게시자 WHERE 국가 "USA"
pub_cursor INTO에서 다음 항목을 게시자_이름으로 가져옵니다.
@s>FETCH_STATUS=0인 동안
foreign_publishers VALUES("j>publisher_name)에 삽입하세요.
CLOSE pub_cursor DEALLOCATE pub_cursor
이 예에서는 레코드 집합을 통해 커서가 이동하는 것을 볼 수 있습니다. (이 예는 실제로는 아이디어를 보여주기 위한 것입니다. 가장 좋은 방법이 문제에 대한 해결책, 즉 INSERT, SELECT 명령이 있습니다.)
적용 대상: SQL Server(2008년 이후)기본 SQL 데이터 Azure SQL 데이터 웨어하우스병렬 데이터 웨어하우스
뷰 속성, 커서가 작동하는 결과 집합을 작성하는 데 사용되는 쿼리 등 Transact-SQL 서버 커서의 특성을 정의합니다. DECLARE CURSOR 문은 ISO 표준 구문과 Transact-SQL 언어 확장 집합을 사용하는 구문을 모두 지원합니다.
ISO 구문 DECLARE 커서_이름 [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR ( 읽기 전용 | 업데이트 [ OF 열 이름 [ ,...n ] ] ) ] [;] Transact-SQL 확장 구문 DECLARE 커서_이름 CURSOR [ LOCAL | 글로벌 ] [ FORWARD_ONLY | 스크롤 ] [ 정적 | 키셋 | 다이나믹 | FAST_FORWARD ] [ 읽기 전용 | 스크롤_잠금 | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF 열_이름 [ ,...n ] ] ] [;]
커서_이름
커서_이름
민감하지 않음
임시 DB; 따라서 기본 테이블에 대한 변경 사항은 이 커서의 선택 항목에 의해 반환된 데이터에 반영되지 않으며 이 커서는 변경할 수 없습니다. ISO 구문을 사용할 때 INSENSITIVE 옵션을 지정하지 않으면 기본 테이블에 대한 커밋된 업데이트 및 삭제 내용이 후속 선택 항목에 나타납니다.
스크롤
모든 샘플링 옵션(FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE)을 사용할 수 있음을 나타냅니다. ISO DECLARE CURSOR 문이 SCROLL 옵션을 지정하지 않으면 NEXT 페치 옵션만 지원됩니다. SCROLL 옵션은 FAST_FORWARD 옵션과 함께 지정할 수 없습니다.
select_statement
커서의 결과 집합을 지정하는 표준 SELECT 문입니다. FOR BROWSE 및 INTO 키워드는 허용되지 않습니다. select_statement커서 선언.
select_statement요청한 유형의 커서와 충돌합니다.
읽기 전용
업데이트 ]
컬럼_이름 [,
.. .N]를 지정하면 나열된 열만 변경할 수 있습니다. 컬럼 목록 없이 UPDATE 문을 사용하면 모든 컬럼에 대한 업데이트가 가능합니다.
커서_이름
특정 서버 커서의 Transact-SQL 이름입니다. 커서_이름식별자 규칙을 따라야 합니다.
현지의
커서가 해당 커서가 생성된 패키지, 저장 프로시저 또는 트리거에 대해 로컬임을 나타냅니다. 커서 이름은 이 영역 내에서만 유효합니다. 커서는 패키지 지역 변수, 저장 프로시저, 트리거 또는 저장 프로시저의 출력 매개 변수에 의해 참조될 수 있습니다. OUTPUT 매개 변수는 로컬 커서를 호출 패키지, 저장 프로시저 또는 트리거에 전달하는 데 사용됩니다. 그런 다음 저장 프로시저가 완료된 후 커서에 대한 후속 액세스를 위해 매개 변수를 커서 변수에 할당할 수 있습니다. 커서가 OUTPUT 매개 변수에 전달되지 않은 경우 일괄 처리, 저장 프로시저 또는 트리거 실행이 완료되면 커서가 암시적으로 해제됩니다. 커서가 OUTPUT 매개변수에 전달된 경우 커서를 참조하는 모든 변수가 해제되거나 범위가 종료되면 커서가 해제됩니다.
글로벌
커서가 연결에 대해 전역임을 나타냅니다. 커서 이름은 연결에서 실행되는 모든 저장 프로시저나 패키지에서 사용될 수 있습니다. 연결이 끊어진 경우에만 커서가 암시적으로 해제됩니다.
FORWARD_ONLY
커서를 첫 번째 줄부터 마지막 줄까지만 볼 수 있도록 지정합니다. FETCH NEXT 페치 옵션만 지원됩니다. STATIC, KEYSET 또는 DYNAMIC 키워드 없이 FORWARD_ONLY를 지정하면 커서가 DYNAMIC 커서처럼 동작합니다. FORWARD_ONLY 인수와 SCROLL 인수가 모두 지정되지 않은 경우 STATIC, KEYSET 또는 DYNAMIC 키워드가 없으면 기본값은 FORWARD_ONLY 인수입니다. STATIC, KEYSET 및 DYNAMIC 커서의 기본값은 SCROLL입니다. ODBC 및 ADO와 같은 데이터베이스 API와 달리 FORWARD_ONLY 모드는 STATIC, KEYSET 및 DYNAMIC Transact-SQL 커서에서 지원됩니다.
공전
커서에서 사용할 데이터의 임시 복사본을 만드는 커서를 정의합니다. 커서에 대한 모든 쿼리는 지정된 임시 테이블에 액세스합니다. 임시 DB; 따라서 기본 테이블에 대한 변경 사항은 이 커서의 선택 항목에 의해 반환된 데이터에 반영되지 않으며 이 커서는 변경할 수 없습니다.
키셋
커서가 열릴 때 커서에 있는 행의 구성원이나 순서가 변경되지 않음을 나타냅니다. 행을 고유하게 식별하는 키 세트가 테이블에 내장되어 있습니다. 임시 DB~라고 불리는 열쇠.
커서 소유자가 작성했거나 다른 사용자가 커밋한 기본 테이블의 키가 아닌 값에 대한 변경 사항은 커서 소유자가 볼 때 표시됩니다. 다른 사용자가 변경한 내용은 반영되지 않습니다. Transact-SQL 서버 커서를 사용하여 변경할 수 없습니다. 행이 삭제된 경우 행을 페치하려고 하면 @@FETCH_STATUS -2가 반환됩니다. 커서 경계를 넘어 키 값을 업데이트하는 것은 이전 행을 삭제한 다음 새 행을 삽입하는 것과 유사합니다. 새 값이 있는 행은 표시되지 않으며 이전 값이 있는 행을 가져오려고 시도하면 @@FETCH_STATUS -2가 반환됩니다. 업데이트는 WHERE CURRENT OF 절을 사용하여 커서를 통해 수행된 경우 즉시 표시됩니다.
동적
이 커서를 보는 동안 결과 집합의 행에 대한 모든 데이터 변경 사항을 표시하는 커서를 정의합니다. 각 선택 항목의 데이터 값, 순서 및 행 구성원은 다를 수 있습니다. ABSOLUTE 선택 옵션은 동적 커서에서 지원되지 않습니다.
빨리 감기
성능 최적화가 활성화된 FORWARD_ONLY, READ_ONLY 커서를 나타냅니다. FAST_FORWARD 옵션은 SCROLL 또는 FOR_UPDATE 옵션과 함께 지정할 수 없습니다.
읽기_전용
이 커서를 통한 변경을 방지합니다. WHERE CURRENT OF 절은 UPDATE 또는 DELETE 문의 커서를 참조할 수 없습니다. 이 옵션은 기본 커서 새로 고침 기능보다 우선합니다.
SCROLL_LOCKS
커서를 사용하여 수행된 위치 업데이트 또는 삭제가 성공할 것을 보장함을 나타냅니다. SQL Server는 행을 커서로 읽을 때 해당 행을 후속 변경에 사용할 수 있도록 잠급니다. SCROLL_LOCKS 옵션은 FAST_FORWARD 또는 STATIC 옵션과 함께 지정할 수 없습니다.
낙관적인
커서로 행을 읽은 후 행이 업데이트된 경우 커서를 사용하여 수행된 위치 지정 업데이트 또는 삭제가 실패하도록 지정합니다. SQL Server는 행을 커서로 읽을 때 행을 잠그지 않습니다. 대신 비교가 사용됩니다. 타임스탬프열 값 또는 체크섬, 테이블에 없으면 타임스탬프행을 커서로 읽은 후 행이 변경되었는지 여부를 확인하는 열입니다. 행이 수정된 경우 위치 지정 업데이트 또는 삭제 시도가 실패합니다. OPTIMISTIC 옵션은 FAST_FORWARD 옵션과 함께 지정할 수 없습니다.
TYPE_WARNING
커서가 요청된 유형에서 다른 유형으로 암시적으로 변환되는 경우 클라이언트에 경고가 전송되도록 지정합니다.
select_statement
커서의 결과 집합을 지정하는 표준 SELECT 문입니다. COMPUTE, COMPUTE BY, FOR BROWSE 및 INTO 키워드는 허용되지 않습니다. select_statement커서 선언.
SQL Server는 다음 절의 경우 커서를 암시적으로 다른 유형으로 변환합니다. select_statement요청한 유형의 커서와 충돌합니다. 자세한 내용은 암시적 커서 변환을 참조하세요.
업데이트]
업데이트할 커서의 열을 정의합니다. 만약에 컬럼_이름 [,
... N]가 제공되면 나열된 열만 변경할 수 있습니다. 컬럼 목록 없이 UPDATE 문을 사용하는 경우 READ_ONLY 동시성 옵션을 지정하지 않는 한 모든 컬럼에 대한 업데이트가 가능합니다.
DECLARE CURSOR 문은 뷰 속성, 커서가 작동하는 결과 집합을 작성하는 데 사용되는 쿼리 등 Transact-SQL 서버 커서의 특성을 정의합니다. OPEN 문은 결과 집합을 채우고 FETCH 문은 그 결과에서 행을 반환합니다. CLOSE 문은 커서와 관련된 현재 결과 집합을 지웁니다. DEALLOCATE 문은 커서가 사용하는 리소스를 해제합니다.
DECLARE CURSOR 문의 첫 번째 형식은 ISO 구문을 사용하여 커서 매개변수를 지정합니다. DECLARE CURSOR 문의 두 번째 형식은 ODBC 및 ADO와 같은 데이터베이스 API의 커서 함수에 사용되는 것과 동일한 유형을 사용하여 커서를 정의할 수 있도록 하는 Transact-SQL 언어에 대한 확장을 사용합니다.
이 두 가지 형태는 혼합될 수 없습니다. SCROLL을 지정하거나 CURSOR 키워드 앞에 키워드를 생략하면 CURSOR와 CURSOR 사이에 키워드를 사용할 수 없습니다. select_statement키워드. CURSOR 사이에 키워드를 지정할 때와 select_statement키워드를 사용하는 경우 CURSOR 키워드 앞에 SCROLL 또는 INSENSITIVE를 지정할 수 없습니다.
DECLARE CURSOR 문에 Transact-SQL 구문을 사용하고 READ_ONLY, OPTIMISTIC 또는 SCROLL_LOCKS 옵션을 지정하지 않으면 다음 기본값이 가정됩니다.
SELECT 문이 업데이트를 지원하지 않는 경우(또는 권한이 부족하거나 업데이트를 지원하지 않는 원격 테이블에 액세스하는 경우 등) 커서는 READ_ONLY로 설정됩니다.
STATIC 및 FAST_FORWARD 커서의 기본값은 READ_ONLY입니다.
DYNAMIC 및 KEYSET 커서의 기본값은 OPTIMISTIC입니다.
커서는 다른 Transact-SQL 문에서만 참조할 수 있습니다. 데이터베이스 API 함수는 커서를 참조할 수 없습니다. 예를 들어 커서가 선언되면 OLE DB, ODBC 또는 ADO 함수와 메서드는 해당 이름을 참조할 수 없습니다. 해당 API 함수 및 메소드를 사용하여 커서 행을 선택할 수 없습니다. 이를 위해서는 Transact-SQL FETCH 문을 사용해야 합니다.
커서가 선언된 후 커서의 속성을 정의하는 데 다음 저장 프로시저를 사용할 수 있습니다.
변수를 일부로 사용할 수 있습니다. select_statement, 커서가 선언됩니다. 커서 변수의 값은 선언된 후에 변경되지 않습니다.
기본적으로 DECLARE CURSOR 권한은 커서가 사용하는 뷰, 테이블 및 열에 대한 SELECT 권한이 있는 모든 사용자에게 부여됩니다.
클러스터형 columnstore 인덱스가 있는 테이블에서는 커서나 트리거를 사용할 수 없습니다. 이 제한은 비클러스터형 인덱스에는 적용되지 않습니다. 비클러스터형 columnstore 인덱스가 있는 테이블에서 커서와 트리거를 사용할 수 있습니다.
A. 간단한 커서와 구문 사용
이 커서를 열 때 생성된 결과 집합에는 테이블의 모든 행과 열이 포함됩니다. 이 커서는 업데이트될 수 있으며 모든 업데이트 및 삭제는 이 커서에 대한 선택 항목에 표시됩니다. FETCH``NEXT는 SCROLL 매개변수가 지정되지 않았기 때문에 페치됩니다.
DECLARE vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor;
B. 중첩된 커서를 사용하여 보고서 표시
다음 예에서는 중첩된 커서를 사용하여 복잡한 보고서를 표시합니다. 각 공급자에 대해 내부 커서가 선언됩니다.
카운트 없음 설정 ;
DECLARE @vendor_id int , @vendor_name nvarchar( 50 ), @message varchar( 80 ), @product nvarchar( 50 );인쇄" -------- 공급업체 제품 보고서 --------";
DECLARE VendorID에 대한 커서 DECLARE PreferredVendorStatus = 1 ORDER BY VendorID; OPEN Vendor_cursor Vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0에서 다음 항목 가져오기 인쇄 시작 " " SELECT @message = "------ 공급업체의 제품: "+ @vendor_name 인쇄 @message -- 내부 커서 기반 선언
-- 외부 커서의 Vendor_id에 있습니다. DECLARE product_cursor CURSOR FOR SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id -- 외부 커서의 변수 값 OPEN product_cursor FETCH NEXT FROM product_cursor INTO @product IF @@FETCH_STATUS<>0 인쇄 "<
커서는 상황별 메모리 영역에 대한 링크입니다. SQL 프로그래밍 언어(Oracle, Microsoft SQL Server)의 일부 구현에서 쿼리를 실행할 때 얻은 결과 집합 및 이와 관련된 현재 레코드 포인터입니다. 커서는 대체 데이터 저장소를 나타내는 가상 테이블이라고 말하고 싶습니다. 이 경우 커서를 사용하면 마치 일반 배열의 데이터인 것처럼 해당 데이터에 액세스할 수 있습니다.
커서는 저장 프로시저에서 사용됩니다. 이론은 충분합니다. 예를 살펴보겠습니다.
우리는 데이터베이스를 가지고 있습니다. (데이터베이스가 약간 좋지 않습니다. 이것은 내 데이터베이스 중 하나입니다. 실험실 작업, 하지만 우리 데이터베이스 선생님은 그런 구조를 고집하셨습니다)
/*은행 정보*/
테이블 `은행` 생성(
`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
기본 키(`BankId`))엔진=InnoDB
문자 집합 "utf8" 대조 "utf8_bin" ;
/*예금에 관한 데이터 */
CREATE TABLE `은행 분배`(
`BankId` INTEGER (11) NOT NULL ,
`Persent` INTEGER (11) DEFAULT NULL ,
`ContributeAmount` DECIMAL (10,0) NOT NULL ,
`ClientId` INTEGER (11) NOT NULL ,
기본 키(`BankId`, `ClientId`),
KEY `BankId`(`BankId`),
KEY `클라이언트 ID`(`클라이언트 ID`),
제약 `bankdistribution_fk` FOREIGN KEY (`BankId`) 참조 `bank` (`BankId`),
제약 `bankdistribution_fk1` 외래 키(`ClientId`) 참조 `client`(`ClientId`)
)엔진=InnoDB
/*투자자에 관한 데이터*/
테이블 `클라이언트` 생성(
`ClientId` INTEGER (3) NOT NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL ,
`성` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`이름` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`전화` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`주소` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`SafeId` INTEGER (5) NOT NULL ,
기본 키(`ClientId`, `CreditCardId`),
키 `클라이언트 ID`(`클라이언트 ID`))엔진=InnoDB
AUTO_INCREMENT=11 문자 세트 "utf8" 대조 "utf8_bin"
각 은행을 차례로 받아 몇 가지 작업을 수행해야 한다고 가정해 보겠습니다. 다음 쿼리는 이에 대해 도움이 될 수 있습니다.
'은행'을 선택하세요.* '은행'에서 LIMIT NUMBER OF THE_RECORD_WE NEED,1. 따라서 LIMIT WE NEED_RECORD NUMBER, 1을 사용하여 은행 테이블에서 한 주기의 각 레코드를 차례로 추출하고 필요한 작업을 수행하는 동시에 WE NEED_RECORD NUMBER의 값을 1씩 늘립니다. 이제 동일한 작업을 수행하지만 커서를 사용하여
시작하다
/* 데이터를 추출하는 변수 */
vBankId 정수를 선언합니다.
vBankName VARCHAR(50)을 선언합니다.
vAddress VARCHAR(50)을 선언합니다.
vPhone VARCHAR(50)을 선언합니다.
/* 해들러 변수 - a*/
완료 정수 기본값 0을 선언합니다.
/*커서 선언*/
`bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank`를 선택하기 위해 BankCursor 커서를 선언합니다. 여기서 1;
/*HANDLER 목적, 아래에 설명됨*/
SQLSTATE "02000" SET done=1에 대해 CONTINUE 핸들러를 선언하십시오.
/* 커서 열기 */
BankCursor를 엽니다.
/*데이터 검색*/
완료하는 동안 = 0 DO
우리는 필요한 조치를 취합니다
종료하는 동안 ;
/*커서 닫기*/
은행 커서 닫기;
끝 ;* 이 소스 코드는 소스 코드 하이라이터로 강조 표시되었습니다.
오류: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
메시지: 데이터 없음 - 가져오거나 선택하거나 처리한 행이 없습니다.
SQLSTATE: 02000은 커서 끝에 도달하거나 선택 또는 업데이트가 빈 문자열을 반환할 때 발생합니다.
다음 줄에서 커서를 선언했습니다. DECLARE 커서_이름 CURSOR FOR select_statement;
커서 열기 커서_이름 열기;
그런 다음 커서 끝에 도달할 때까지(WHILE done = 0 DO) 데이터를 추출하여 처리합니다.
저장 프로시저를 종료하기 전에 커서를 닫아야 합니다. 커서_이름을 닫습니다.
복잡해 보이지는 않네요. 그러나 SQLSTATE "02000"과 관련된 함정이 많이 있습니다.
완료하는 동안 = 0 DO
BankCursor INTO vBankId,vBankName,vAddress,vPhone을 가져옵니다.
BankId = vBankId 제한 1인 Bankdistribution에서 (ContributeAmount) INTO vContributeAmountSUM을 선택합니다.
우리는 몇 가지 조치를 취합니다
종료하는 동안 ;* 이 소스 코드는 소스 코드 하이라이터로 강조 표시되었습니다.
구문 관점에서 볼 때 모든 것이 훌륭하고 정확합니다. 그러나 논리적인 관점에서 보면 그렇지 않습니다. 예금자가 일부 은행에서 계좌를 개설하지 않은 경우 BankId = vBankId 제한이 1인 Select (ContributeAmount) INTO vContributeAmountSUM FROM 은행 배포의 경우가 발생할 수 있습니다. SQLSTATE: 02000이 실행되고 done 변수는 1로 설정되며 while 루프는 예상보다 일찍 종료됩니다. 다음을 수행하면 이 문제를 피할 수 있습니다.
완료하는 동안 = 0 DO
BankCursor INTO vBankId,vBankName,vAddress,vPhone을 가져옵니다.
/* 은행의 예금 금액 추출 */
(vContributeAmountSUM > 0)이면
/* 은행의 예금 금액 추출 */
이면 종료;
우리는 몇 가지 조치를 취합니다
종료하는 동안 ;* 이 소스 코드는 소스 코드 하이라이터로 강조 표시되었습니다.
첫 번째 요청에서 기여가 있는지 확인하고(없으면 vContributeAmountSUM == 0) 기여가 있는 경우에만 데이터를 검색합니다.
이제 각 고객에 대해 서로 다른 은행 계좌의 총액을 제거해야 한다고 가정해 보겠습니다.
합계 선택에 대한 ClientSummCursor 커서 선언
Select sum(`bankdistribution`.`ContributeAmount`), `bankdistribution`.`ClientId` FROM `bankdistribution` 내부에 대해 ClientSummCursor 커서를 선언합니다. 여기서 클라이언트는 `bankdistribution`별로 1개 그룹화됩니다. `클라이언트 ID`;ClientSummCursor를 엽니다.
완료하는 동안 = 0 DO
BankCursor INTO vBankId,vBankName,vAddress,vPhone을 가져옵니다.
/* 은행의 예금 금액 추출 */
BankId = vBankId 제한 1인 경우 Bankdistribution에서 Count(ContributeAmount) INTO vContributeAmountSUM을 선택합니다.
/* 이 은행에 실제로 예금이 있는지 확인 */
(vContributeAmountSUM > 0)이면
/* 은행의 예금 금액 추출 */
BankId = vBankId 제한 1인 Bankdistribution에서 ContributeAmount INTO vContributeAmountSUM을 선택합니다.
이면 종료;
우리는 몇 가지 조치를 취합니다.
종료하는 동안 ;* 이 소스 코드는 소스 코드 하이라이터로 강조 표시되었습니다.
ClientSummCursor 커서의 데이터가 BankCursor의 데이터보다 일찍 끝나고, SQLSTATE: 02000이 트리거되고, done 변수가 1로 설정되고, while 루프가 예상보다 일찍 끝나는 경우에도 동일한 상황이 발생할 수 있습니다. 다음을 수행하면 이 문제를 피할 수 있습니다.
ClientSummCursor를 엽니다.
완료하는 동안 = 0 DO
BankCursor INTO vBankId,vBankName,vAddress,vPhone을 가져옵니다.
/* 은행의 예금 금액 추출 */
BankId = vBankId 제한 1인 경우 Bankdistribution에서 Count(ContributeAmount) INTO vContributeAmountSUM을 선택합니다.
/* 이 은행에 실제로 예금이 있는지 확인 */
(vContributeAmountSUM > 0)이면
/* 은행의 예금 금액 추출 */
BankId = vBankId 제한 1인 Bankdistribution에서 ContributeAmount INTO vContributeAmountSUM을 선택합니다.
이면 종료;
/* 두 번째 커서에서 데이터를 추출하기 전에 sqlstate 상태를 기억하세요 */
SET old_status = 완료;
/* 필요한 데이터 추출 */
ClientSummCursor INTO vSum,vClientId를 가져옵니다.
/* 데이터가 검색되었는지, sqlstate 0200이 실패했는지 확인 */
(완료 = 0)이면
우리는 몇 가지 조치를 취합니다.
이면 종료;
/* while이 끝나기 전에 done 변수의 값을 복원합니다 */
완료 = old_status 설정;
종료하는 동안 ;* 이 소스 코드는 소스 코드 하이라이터로 강조 표시되었습니다.
여기까지 읽어주신 모든 분들께 감사드리며, 누군가에게 도움이 되기를 바랍니다.
데이터베이스의 커서 구현은 일련의 데이터와 이를 처리하는 방법이 있는 Java 클래스와 유사합니다. 여기서 SQL 커서데이터를 일반 배열로 사용합니다. 커서는 트리거, 저장 프로시저 및 함수에서 사용할 수 있습니다.
SQL 표준에 따라 커서 작업 시 다음과 같은 기본 작업이 수행됩니다.
- 커서 선언;
- 데이터를 읽으면서 커서를 여는 것;
- 커서에서 데이터를 한 줄씩 샘플링합니다.
- 커서를 사용하여 행 데이터를 변경합니다.
- 커서를 닫으면 커서에 액세스할 수 없게 됩니다.
- 커서를 놓는 것, 즉 메모리에서 커서를 제거하면 커서를 닫는다고 해서 연관된 메모리가 반드시 해제되는 것은 아니기 때문입니다.
다양한 구현에서 정의 커서약간의 차이가 있을 수 있습니다. 예를 들어 커서에 할당된 메모리를 명시적으로 해제해야 하는 경우가 있습니다. 커서가 해제되면 이와 관련된 메모리도 해제됩니다. 이렇게 하면 커서 이름을 재사용할 수 있습니다. 다른 구현에서는 커서가 닫히면 메모리가 암시적으로 해제됩니다.
어떤 경우에는 커서를 사용하지 않고는 할 수 없습니다. 그러나 가능하다면 커서 사용을 피하고 표준 데이터 처리 명령인 SELECT, UPDATE, INSERT, DELETE를 사용하여 작업해야 합니다. 이는 커서가 전체 데이터 볼륨에 대해 수정 작업을 허용하지 않고, 커서를 이용한 데이터 처리 작업 수행 속도가 커서에 비해 현저히 느리기 때문입니다. 표준 수단 SQL.
프로그램이 커서에 로드된 데이터를 변경할 수 있는 경우 이를 수정 가능이라고 합니다. 커서에 관해 이야기할 때 트랜잭션 격리를 잊어서는 안 됩니다. 한 사용자는 커서를 사용하여 레코드를 수정하고 다른 사용자는 자신의 커서를 사용하여 해당 레코드를 읽습니다. 게다가 동일한 기록을 변경할 수도 있으므로 데이터 무결성을 유지하는 것이 필요합니다.
커서 선언
커서를 사용하려면 먼저 선언해야 합니다. SQL 표준은 다음 구문을 사용하여 커서를 만듭니다.
select_statement에 대한 커서_이름 커서 선언 ])]
이 표현식은 커서를 선언합니다. 커서 선언"cursor_name"이라는 이름으로.
민감하지 않음변경을 허용하지 않는 정적 커서가 생성됩니다. 또한 다른 사용자가 변경한 내용은 표시되지 않습니다. INSENSITIVE 키워드가 없으면 동적 커서가 생성됩니다.
키워드를 사용할 때 스크롤생성된 커서는 어떤 방향으로든 스크롤될 수 있으므로 선택 명령을 적용할 수 있습니다. 이 인수를 생략하면 커서는 순차적입니다. 시청은 처음부터 끝까지 한 방향으로만 가능합니다.
표현 select_statement select ... from ... 과 같은 정보를 읽는 구조를 나타냅니다. 연산자를 포함하면 안 됩니다. ~ 안으로, 커서에는 자체 연산자가 있으므로 술책변수를 커서 데이터로 채웁니다.
인수를 지정할 때 READ_ONLY용읽기 전용 커서가 생성되며 데이터 수정은 허용되지 않습니다. 동적 커서는 읽기 전용 커서로 선언되어 다른 사용자가 변경한 내용을 표시할 수 있습니다.
인수를 사용하여 커서 만들기 업데이트를 위해지정된 열이나 인수가 없는 경우 커서의 데이터를 변경할 수 있습니다. OF 컬럼_이름, 모든 열에서.
서브루틴에서 여러 커서를 선언할 수 있습니다. 그러나 각 커서에는 고유한 이름이 있어야 합니다. 커서를 열려면 연산자를 사용해야 합니다. 열려 있는이전에 선언된 커서를 엽니다.
커서 열림
SQL은 커서 "cursor open"을 열기 위해 다음 구문을 정의합니다.
커서_이름을 엽니다.
커서에서 데이터 가져오기, 커서 가져오기
커서에서 일부 변수로 데이터를 읽는 구문은 다음과 같습니다.
커서_이름을 var_name [, var_name]으로 가져옵니다 ...;
운영자 술책열린 커서 데이터를 다음에 위치한 변수로 선택합니다. ~ 안으로그리고 커서를 다음 위치로 이동시킵니다.
커서 닫기
운영자 닫다커서를 닫습니다. 연산자를 명시적으로 지정하지 않으면 해당 프로그램 블록이 닫힐 때 커서가 자동으로 닫힙니다.
커서_이름을 닫습니다.
닫은 후에는 커서에 액세스할 수 없게 됩니다. 닫으면 커서가 실행 중인 동안 설치된 모든 잠금이 해제됩니다. 열려 있는 커서만 닫을 수 있습니다. 닫혔지만 해제되지 않은 커서는 다시 열 수 있습니다. 열려 있지 않은 커서를 닫는 것은 허용되지 않습니다.
각 DBMS에는 커서를 사용하는 고유한 특성이 있습니다.
Oracle에서 커서를 사용하는 기능
PL/SQL에는 네 가지 커서 속성이 있습니다. %설립하다, %찾을 수 없음, %ISOPEN그리고 %ROWCOUNT. 커서 속성은 커서 이름 오른쪽에 %TYPE 및 %ROWTYPE 연산자처럼 선언됩니다.
%FOUND 속성
%NOTFOUND 속성
%NOTFOUND 속성은 %FOUND와 정반대입니다.
%ISOPEN 속성
%ISOPEN 속성은 커서가 열려 있는지 여부만 나타냅니다.
%ROWCOUNT 속성
기인하다 %ROWCOUNT특정 시점에 커서가 읽은 행 수를 반환하는 숫자 속성입니다.
Oracle DBMS의 SQL 커서 예
v_idmanagers.id %TYPE을 선언하십시오. v_name 관리자.name%TYPE; v_comm 관리자.comm%TYPE; "2014-11-01"과 "2014-11-30" 사이의 데이터가 ID, 이름별로 그룹화되는 관리자의 통신으로 ID, 이름, 합계(comm)를 선택하기 위한 crs 커서; crs 열기를 시작하세요. crs%NOTFOUND가 발견되면 루프 종료; crs를 v_id, v_name, v_comm으로 가져옵니다. 보너스(id, name, comm) 값에 삽입(crs.id, crs.name, crs.comm); 끝 루프; 저지르다; crs를 닫습니다. 끝;
SQL Server에서 커서를 사용하는 기능
MSSQL에서 사용되는 커서는 순차적이거나 스크롤 가능합니다. 순차를 사용하면 처음부터 끝까지 한 방향으로만 데이터를 선택할 수 있습니다. 스크롤 가능한 커서를 사용하면 양방향으로 이동할 수 있으며 커서 결과 집합의 임의 행으로 이동할 수 있습니다.
SQL Server는 정적, 동적, 순차 및 키 세트 기반 커서를 지원합니다.
정적 커서 디자인에서는 정보가 특정 시점의 스냅샷으로 저장됩니다. 따라서 다른 사용자가 데이터베이스에 변경한 내용은 표시되지 않습니다. 커서가 열려 있는 동안 서버는 전체 결과 집합에 포함된 모든 행에 잠금을 설정합니다. 정적 커서는 생성 후에도 변경되지 않으며 항상 열릴 당시 존재했던 데이터 세트를 표시합니다. 다른 사용자가 소스 테이블의 커서에 포함된 데이터를 변경하더라도 정적 커서에는 영향을 미치지 않습니다. 정적 커서는 변경할 수 없으므로 항상 읽기 전용 모드로 열립니다.
동적 커서에는 추가 네트워크 오버헤드와 소프트웨어 리소스가 필요합니다. 동적 커서를 사용하면 데이터의 전체 복사본이 생성되지 않지만 사용자가 특정 데이터에 액세스할 때만 소스 테이블의 선택이 수행됩니다. 가져오는 동안 서버는 행을 잠그고 사용자가 커서의 전체 결과 집합에 대해 변경한 내용은 커서에 표시됩니다. 그러나 커서가 데이터를 가져온 후에는 다른 사용자가 변경한 내용이 더 이상 커서에 반영되지 않습니다.
키 세트로 제어되는 커서는 정적과 동적 사이의 속성을 갖습니다. 기록은 샘플링 시점에 식별되므로 변경 사항이 추적됩니다. 이 유형의 커서는 뒤로 스크롤을 구현할 때 유용합니다. 이 경우 정보가 업데이트되고 커서가 새로운 버전변경사항이 있는지 기록합니다.
정적 커서는 정보 처리 시스템에 가장 적합합니다. 보고 시스템 또는 통계 및 분석 목적으로. 정적 커서는 대량의 데이터를 가져오는 데 더 좋습니다. 전자 구매 또는 개체(좌석, 티켓) 예약을 위한 시스템에서는 변경 사항이 발생하면 업데이트된 정보를 동적으로 인식해야 합니다. 이러한 경우에는 동적 커서가 사용됩니다. 이러한 애플리케이션에서 전송되는 데이터의 양은 일반적으로 적으며 개별 레코드 수준에서 액세스됩니다.
순차 커서는 데이터를 역방향으로 가져오는 것을 허용하지 않으며 커서의 처음부터 끝까지만 가져올 수 있습니다. 순차 커서는 모든 데이터 행 집합을 저장하지 않습니다. 커서에서 항목을 선택하자마자 데이터베이스에서 읽혀지므로 INSERT, UPDATE, DELETE 명령을 사용하여 사용자가 데이터베이스에 적용한 모든 변경 사항을 동적으로 반영할 수 있습니다. 커서는 가장 최근의 데이터 상태를 읽습니다.
커서 선언
SELECT_statement에 대한 커서_이름 커서 선언 ]]
키워드를 사용할 때 현지의블록, 트리거, 저장 프로시저 또는 사용자 정의 함수 내에서만 표시되는 로컬 커서가 생성됩니다. 예어 글로벌, 현재 연결이 닫힐 때까지 존재하는 전역 커서를 정의합니다.
운영자 FORWARD_ONLY첫 번째 행에서 마지막 행 방향으로만 데이터를 검색할 수 있도록 하는 순차 커서를 정의합니다. 연산자를 사용하는 경우 스크롤임의의 순서와 방향으로 데이터에 액세스할 수 있는 스크롤 가능한 커서가 생성됩니다.
커서 유형은 연산자에 의해 결정됩니다.
- STATIC - 정적 커서를 생성합니다.
- DYNAMIC - 동적 커서를 생성합니다.
- KEYSET - 키 커서를 생성합니다.
커서의 경우 읽기_전용인수 지정 빨리 감기, 생성된 커서는 다음에 대해 최적화됩니다. 빠른 접근데이터에. 이 인수는 인수와 함께 사용할 수 없습니다. FORWARD_ONLY그리고 낙관적인.
연산자로 커서를 생성한 경우 낙관적인이면 커서가 열린 후 변경된 행을 변경하거나 삭제할 수 없습니다.
인수를 지정할 때 TYPE_WARNING서버는 SELECT 쿼리와 호환되지 않는 경우 암시적 커서 유형 변경을 보고합니다.
커서에서 데이터 검색, 가져오기
커서를 연 직후 다음 명령을 사용하여 해당 내용을 가져올 수 있습니다.
연산자를 사용하는 경우 첫 번째커서 결과 집합의 첫 번째 행이 반환되며, 이는 현재 행이 됩니다. 지정된 경우 마지막커서의 마지막 줄이 반환됩니다. 또한 현재 행이 됩니다.
연산자를 지정할 때 다음결과 집합에서 현재 행 바로 다음 행이 반환됩니다. 이 줄이 현재 줄이 됩니다. 기본 명령 술책행을 가져오는 데 정확히 이 방법을 사용합니다.
연산자를 지정할 때 이전의현재 줄 앞의 줄이 반환됩니다. 이 줄이 현재 줄이 됩니다.
운영자 절대(line_number | @line_number_variable)커서의 전체 결과 집합에서 절대 서수로 행을 반환합니다. 라인 번호는 상수를 사용하여 지정하거나 라인 번호가 저장되는 변수 이름으로 지정할 수 있습니다. 변수는 정수 데이터 유형이어야 합니다. 양수 값과 음수 값이 모두 표시됩니다. 양수 값을 지정하면 문자열은 집합의 처음부터 계산되고 음수 값은 끝부터 계산됩니다. 선택한 줄이 현재 줄이 됩니다. null 값이 지정되면 행이 반환되지 않습니다.
논쟁 RELATIVE(행 수 | @variable 행 수)현재 줄 이후에 지정된 줄 수만큼 줄 오프셋을 반환합니다. 음수 행 수를 지정하면 현재 행보다 이전에 지정된 행 수에 해당하는 행이 반환됩니다. null 값을 지정하면 현재 행이 반환됩니다. 반환된 행은 현재 행이 됩니다.
전역 커서를 열려면 이름 앞에 키워드를 지정해야 합니다. 글로벌. 커서 이름은 변수를 사용하여 지정할 수도 있습니다.
표현에 있어서 INTO @변수_이름 [,...n]반환된 행의 해당 열 값이 저장될 변수 목록이 정의됩니다. 변수의 순서는 커서에 있는 열의 순서와 일치해야 하며, 변수의 데이터 유형은 커서 열에 있는 데이터 유형과 일치해야 합니다.
커서를 사용하여 데이터 변경 및 삭제
커서를 사용하여 데이터를 변경하려면 다음 형식으로 UPDATE 명령을 실행해야 합니다.
한 번의 작업으로 현재 커서 행의 여러 열 값을 변경할 수 있지만 모두 동일한 테이블에 속해야 합니다.
커서를 사용하여 데이터를 삭제하려면 다음 형식으로 DELETE 명령을 사용하십시오.
결과적으로 커서의 현재 줄이 삭제됩니다.
메모리 해제, 할당 해제
메모리에서 커서를 제거하려면 다음 명령을 사용하십시오.
Cursor_name 할당을 해제합니다.
@@FETCH_STATUS 속성
커서에 행이 있는지 확인하려면 전역 변수를 사용해야 합니다. @@FETCH_STATUS, 커서에 더 이상 행이 없으면 0이 아닌 값을 사용합니다. 행 집합이 아직 소진되지 않은 경우 @@FETCH_STATUS는 0과 같습니다.
SQL Server의 커서 예
@company varchar(50), @manager varchar(50), @message varchar(256)을 선언합니다. 선택 회사, 도시 = "모스크바"인 고객의 관리자, 회사별 주문에 대해 crs_clients 커서를 로컬로 선언합니다. "클라이언트 목록"을 인쇄하십시오. crs_clients를 엽니다. crs_clients에서 @company, @manager로 다음 항목을 가져옵니다. @@FETCH_STATUS = 0 시작 선택 @message = "회사" + @company + "관리자" + @manager; @메시지 인쇄; -- crs_clients에서 @company, @manager로 가져오는 다음 레코드로 이동합니다. 끝; crs_clients를 닫습니다. crs_clients 할당 해제;
커서의 정의가 제공됩니다. 정적, 동적, 순차 및 키 커서 등 해당 유형과 동작에 대한 설명이 제공됩니다. 커서 제어의 원리는 커서 생성 및 열기, 데이터 읽기, 커서 닫기 등으로 설명됩니다. 커서 프로그래밍의 예가 제공됩니다.커서 개념
관계형 데이터베이스에 대한 쿼리는 일반적으로 여러 데이터 행(레코드)을 반환하지만 애플리케이션은 한 번에 하나의 레코드만 처리합니다. 동시에 여러 행을 처리하더라도(예: 스프레드시트 형식으로 데이터 표시) 그 수는 여전히 제한되어 있습니다. 또한, 데이터 수정, 삭제, 추가 시 작품 단위는 시리즈입니다. 이 상황에서는 커서의 개념이 부각되는데, 이 맥락에서 커서는 행에 대한 포인터입니다.
SQL의 커서는 마지막 SQL 문을 보관하도록 설계된 데이터베이스 메모리 영역입니다. 현재 문이 데이터베이스 쿼리인 경우 현재 값 또는 현재 커서 라인이라고 하는 쿼리 데이터 행도 메모리에 저장됩니다. 메모리의 지정된 영역에는 이름이 지정되고 응용 프로그램에서 액세스할 수 있습니다.
일반적으로 커서는 데이터베이스에 저장된 정보의 하위 집합을 데이터베이스에서 선택하는 데 사용됩니다. 주어진 시간에 응용 프로그램은 하나의 커서 라인을 확인할 수 있습니다. 커서는 다음과 같은 경우에 자주 사용됩니다. SQL 문, 절차적 언어로 작성된 애플리케이션 프로그램에 내장되어 있습니다. 그 중 일부는 데이터베이스 서버에 의해 암시적으로 생성되고 다른 일부는 프로그래머가 정의합니다.
SQL 표준에 따라 커서 작업 시 다음과 같은 주요 작업을 구분할 수 있습니다.
- 창조 또는 커서 선언;
- 커서 열기, 즉. 다중 레벨 메모리에 저장된 데이터로 채우는 단계;
- 커서에서 선택이를 통해 데이터 행을 변경합니다.
- 커서 닫기, 그 후에는 사용자 프로그램에서 액세스할 수 없게 됩니다.
- 커서를 해제, 즉. 커서를 닫는다고 해서 연관된 메모리가 반드시 해제되는 것은 아니기 때문에 커서를 객체로 삭제합니다.
커서의 정의는 구현에 따라 약간 다를 수 있습니다. 예를 들어 개발자는 커서에 할당된 메모리를 명시적으로 해제해야 하는 경우가 있습니다. 후에 커서를 놓다연관된 메모리도 해제됩니다. 이를 통해 그의 이름을 재사용할 수 있습니다. 다른 구현에서는 커서 닫기메모리 해제는 암시적으로 발생합니다. 복구 후 즉시 다른 작업에 사용할 수 있게 됩니다. 다른 커서 열기등.
어떤 경우에는 커서 사용이 불가피할 수도 있습니다. 그러나 가능하다면 이를 피하고 표준 데이터 처리 명령인 SELECT, UPDATE, INSERT, DELETE를 사용하여 작업해야 합니다. 커서는 전체 데이터 볼륨에 대한 수정 작업을 허용하지 않는다는 점 외에도 커서를 사용하여 데이터 처리 작업을 수행하는 속도는 표준 SQL 도구에 비해 눈에 띄게 느립니다.
MS SQL Server 환경에서 커서 구현
SQL Server는 세 가지 유형의 커서를 지원합니다.
- SQL 커서는 주로 트리거, 저장 프로시저 및 스크립트 내에서 사용됩니다.
- 서버 커서는 서버에서 작동하고 ODBC, OLE DB, DB_Library에 대한 응용 프로그램 프로그래밍 인터페이스를 구현합니다.
- 클라이언트 커서는 클라이언트 자체에 구현됩니다. 서버에서 행의 전체 결과 집합을 가져와 로컬에 저장하므로 네트워크 작업에 낭비되는 시간을 줄여 데이터 처리 속도를 높입니다.
다양한 유형의 다중 사용자 애플리케이션에는 데이터에 대한 다양한 유형의 병렬 액세스가 필요합니다. 일부 애플리케이션은 데이터베이스 변경 사항에 대한 정보에 즉시 액세스해야 합니다. 이는 티켓 예약 시스템에서 일반적입니다. 통계 보고 시스템과 같은 다른 경우에는 데이터 안정성이 중요합니다. 데이터가 지속적으로 수정되면 프로그램이 정보를 효과적으로 표시할 수 없기 때문입니다. 애플리케이션마다 커서 구현이 달라야 합니다.
SQL Server에서는 커서 유형이 제공하는 기능에 따라 다릅니다. 커서 유형은 생성 단계에서 결정되며 변경할 수 없습니다. 일부 유형의 커서는 결과 집합에 포함된 행에 대해 다른 사용자가 변경한 내용을 감지할 수 있습니다. 그러나 SQL Server는 행에 액세스하는 동안에만 해당 행의 변경 사항을 추적하고 행을 이미 읽은 후에는 변경 사항을 수정하는 것을 허용하지 않습니다.
커서는 두 가지 범주로 나뉩니다. 잇달아 일어나는스크롤 가능합니다. 연이은처음부터 끝까지 한 방향으로만 데이터를 선택할 수 있습니다. 스크롤 가능한 커서더 큰 동작 자유 제공 - 양방향으로 이동하고 커서 결과 집합의 임의 행으로 점프하는 것이 가능합니다. 프로그램이 커서가 가리키는 데이터를 수정할 수 있는 경우 이를 스크롤 가능하고 수정 가능하다고 합니다. 커서에 관해 말하자면, 트랜잭션 격리를 잊어서는 안 됩니다. 한 사용자가 레코드를 수정하면 다른 사용자가 자신의 커서를 사용하여 해당 레코드를 읽을 뿐만 아니라 동일한 레코드를 수정할 수도 있으므로 데이터 무결성을 유지하는 것이 필요합니다.
SQL Server는 정적, 동적, 잇달아 일어나는일련의 키로 제어됩니다.
계획에서 정적 커서정보는 데이터베이스에서 한 번 읽혀지고 특정 시점을 기준으로 스냅샷으로 저장되므로 다른 사용자가 데이터베이스에 적용한 변경 사항은 표시되지 않습니다. 잠시 동안 커서 열기서버는 전체 결과 집합에 포함된 모든 행에 잠금을 설정합니다. 정적 커서생성 후에도 변경되지 않으며 항상 열 당시 존재했던 데이터 세트를 표시합니다.
다른 사용자가 소스 테이블의 커서에 포함된 데이터를 변경하더라도 해당 테이블에는 영향을 미치지 않습니다. 정적 커서.
안에 정적 커서변경할 수 없으므로 항상 읽기 전용 모드로 열립니다.
동적 커서데이터를 "라이브" 상태로 유지하지만 이를 위해서는 네트워크와 소프트웨어 리소스가 필요합니다. 사용 동적 커서소스 데이터의 전체 복사본은 생성되지 않지만 사용자가 특정 데이터에 액세스할 때만 소스 테이블에서 동적 선택이 수행됩니다. 가져오는 동안 서버는 행을 잠그고 사용자가 커서의 전체 결과 집합에 대해 변경한 내용은 커서에 표시됩니다. 그러나 커서가 데이터를 가져온 후 다른 사용자가 변경한 경우 해당 내용은 커서에 반영되지 않습니다.
일련의 키로 제어되는 커서,는 이러한 극단의 중간에 있습니다. 기록은 샘플링 시점에 식별되므로 변경 사항이 추적됩니다. 이 유형의 커서는 뒤로 스크롤을 구현할 때 유용합니다. 그러면 정보가 업데이트될 때까지 행의 추가 및 삭제가 표시되지 않으며 레코드가 변경된 경우 드라이버는 레코드의 새 버전을 선택합니다.
순차 커서반대 방향으로 데이터를 가져오는 것은 허용되지 않습니다. 사용자는 커서의 처음부터 끝까지 행만 선택할 수 있습니다. 직렬 커서모든 행 집합을 저장하지 않습니다. 커서에서 선택되자마자 데이터베이스에서 읽혀지므로 사용자가 데이터베이스에 수행한 모든 변경 사항이 INSERT, UPDATE, DELETE 명령을 사용하여 동적으로 반영됩니다. 커서는 데이터의 가장 최근 상태를 표시합니다.
정적 커서안정적인 데이터 뷰를 제공합니다. 이는 정보 "웨어하우징" 시스템, 즉 보고 시스템용 애플리케이션이나 통계 및 분석 목적에 적합합니다. 게다가, 정적 커서많은 양의 데이터를 샘플링하는 데 다른 것보다 더 잘 대처합니다. 이와 대조적으로 전자 구매 또는 티켓 예약 시스템에서는 변경 사항이 발생하면 업데이트된 정보를 동적으로 인식해야 합니다. 그러한 경우에 사용됩니다 동적 커서. 이러한 애플리케이션에서 전송되는 데이터의 양은 일반적으로 적으며 행(개별 레코드) 수준에서 액세스됩니다. 그룹 액세스는 매우 드뭅니다.
MS SQL Server 환경의 커서 관리
커서 제어다음 명령을 실행하여 구현됩니다.
- DECLARE - 생성 또는 커서 선언;
- 열려 있는 - 커서 열기, 즉. 데이터로 채우고;
- 술책 커서에서 선택커서를 사용하여 데이터 행을 변경하는 단계;
- 닫다 - 커서 닫기;
- 할당 해제 – 커서를 해제, 즉. 커서를 객체로 삭제합니다.
커서 선언
SQL 표준은 커서를 생성하기 위해 다음 명령을 제공합니다.
INSENSITIVE 키워드를 사용하면 정적 커서. 데이터 변경또한, 다른 사용자가 변경한 내용도 표시되지 않습니다. INSENSITIVE 키워드가 누락된 경우 동적 커서.
SCROLL 키워드를 지정하면 생성된 커서를 원하는 방향으로 스크롤할 수 있으므로 모든 선택 명령을 사용할 수 있습니다. 이 인수를 생략하면 커서는 일관된, 즉. 시청은 처음부터 끝까지 한 방향으로만 가능합니다.
SELECT 문은 커서에 대한 결과 행 집합을 결정하는 SELECT 요청의 본문을 지정합니다.
FOR READ_ONLY를 지정하면 읽기 전용 커서가 생성되고 데이터 수정이 허용되지 않습니다. 정적과 다르지만 후자는 데이터 변경을 허용하지 않습니다. 읽기 전용 커서로 선언 가능 동적 커서, 다른 사용자가 변경한 내용을 표시할 수 있습니다.
FOR UPDATE 인수를 사용하여 커서를 생성하면 커서에서 실행할 수 있습니다. 데이터 변경지정된 열에 있거나 OF column_name 인수가 없는 경우 모든 열에 있습니다.
MS SQL Server 환경에서는 커서 생성 명령에 대해 다음 구문이 허용됩니다.
<создание_курсора>::= DECLARE 커서_이름 CURSOR FOR SELECT_statement ]]
LOCAL 키워드를 사용하면 이를 생성한 패키지, 트리거, 저장 프로시저 또는 사용자 정의 함수의 범위 내에서만 표시되는 로컬 커서가 생성됩니다. 패키지, 트리거, 프로시저 또는 함수가 종료되면 커서는 암시적으로 삭제됩니다. 커서의 내용을 커서를 생성한 구문 외부로 전달하려면 해당 매개 변수에 OUTPUT 인수를 할당해야 합니다.
GLOBAL 키워드가 지정되면 전역 커서가 생성됩니다. 현재 연결이 닫힐 때까지 존재합니다.
FORWARD_ONLY를 지정하면 생성됩니다. 직렬 커서; 데이터는 첫 번째 행에서 마지막 행 방향으로만 샘플링될 수 있습니다.
SCROLL을 지정하면 생성됩니다. 스크롤 가능한 커서; 데이터는 순서와 방향에 관계없이 액세스할 수 있습니다.
STATIC을 지정하면 생성됩니다. 정적 커서.
KEYSET을 지정하면 키 커서가 생성됩니다.
DYNAMIC을 지정하면 생성됩니다. 동적 커서.
READ_ONLY 커서에 대해 FAST_FORWARD 인수를 지정하면 생성된 커서는 빠른 데이터 액세스에 최적화됩니다. 이 인수는 FORWARD_ONLY 또는 OPTIMISTIC 인수와 함께 사용할 수 없습니다.
OPTIMISTIC 인수를 사용하여 생성된 커서는 이후 수정된 행의 수정 또는 삭제를 방지합니다. 커서 열기.
TYPE_WARNING 인수를 지정하면 서버는 커서 유형이 SELECT 쿼리와 호환되지 않는 경우 커서 유형이 암시적으로 변경되었음을 사용자에게 알립니다.
커서 열기
을 위한 커서 열기커서를 생성할 때 지정된 SELECT 쿼리의 데이터로 채우려면 다음 명령을 사용하십시오.
후에 커서 열기연관된 SELECT 문이 실행되고 그 출력이 다중 레벨 메모리에 저장됩니다.
커서에서 데이터 검색
직후 커서 열기다음 명령을 사용하여 내용(해당 쿼리 실행 결과)을 선택할 수 있습니다.
FIRST를 지정하면 커서의 전체 결과 집합 중 첫 번째 행이 반환되어 현재 행이 됩니다.
LAST를 지정하면 커서의 가장 최근 행이 반환됩니다. 또한 현재 행이 됩니다.
NEXT를 지정하면 전체 결과 집합에서 현재 행 바로 다음 행이 반환됩니다. 이제 현재가 됩니다. 기본적으로 FETCH 명령은 행을 가져오는 데 이 방법을 사용합니다.
PRIOR 키워드는 현재 행 이전의 행을 반환합니다. 현재가 됩니다.
논쟁 절대(line_number | @line_number_variable)커서의 전체 결과 집합에서 절대 서수로 행을 반환합니다. 라인 번호는 상수를 사용하여 지정하거나 라인 번호가 저장되는 변수 이름으로 지정할 수 있습니다. 변수는 정수 데이터 유형이어야 합니다. 양수 값과 음수 값이 모두 표시됩니다. 양수 값을 지정하면 문자열은 집합의 처음부터 계산되고 음수 값은 끝부터 계산됩니다. 선택한 줄이 현재 줄이 됩니다. null 값이 지정되면 행이 반환되지 않습니다.
논쟁 RELATIVE(행 수 | @variable 행 수)현재 행 이후에 지정된 행 수만큼의 행을 반환합니다. 음수 행 수를 지정하면 현재 행보다 이전에 지정된 행 수에 해당하는 행이 반환됩니다. null 값을 지정하면 현재 행이 반환됩니다. 반환된 행은 현재 행이 됩니다.
에게 전역 커서 열기, 이름 앞에 GLOBAL 키워드를 지정해야 합니다. 커서 이름은 변수를 사용하여 지정할 수도 있습니다.
디자인에 INTO @변수_이름 [,...n]반환된 행의 해당 열 값이 저장될 변수 목록이 지정됩니다. 변수를 지정하는 순서는 커서에 있는 열의 순서와 일치해야 하며, 변수의 데이터 유형은 커서 열에 있는 데이터 유형과 일치해야 합니다. INTO 구성이 지정되지 않은 경우 FETCH 명령의 동작은 SELECT 명령의 동작과 유사합니다. 즉, 데이터가 화면에 표시됩니다.
데이터 변경 및 삭제
커서를 사용하여 변경하려면 다음 형식으로 UPDATE 명령을 실행해야 합니다.
현재 커서 행의 여러 열은 한 번의 작업으로 변경할 수 있지만 모두 동일한 테이블에 속해야 합니다.
커서를 사용하여 데이터를 삭제하려면 다음 형식으로 DELETE 명령을 사용하십시오.
결과적으로 커서에 현재 설정된 행이 삭제됩니다.
커서 닫기
닫은 후에는 프로그램 사용자가 커서에 액세스할 수 없게 됩니다. 닫으면 작동 중에 설치된 모든 잠금 장치가 제거됩니다. 클로저는 열린 커서에만 적용될 수 있습니다. 닫혔지만 안됨 해제된 커서재개될 수 있습니다. 열려 있지 않은 커서를 닫는 것은 허용되지 않습니다.
커서를 놓습니다
커서 닫기반드시 이와 관련된 메모리를 해제하지는 않습니다. 일부 구현에서는 DEALLOCATE 문을 사용하여 명시적으로 할당을 해제해야 합니다. 후에 커서를 놓다메모리도 해제되어 커서 이름을 재사용할 수 있습니다.
커서 끝에 도달했는지 여부를 제어하려면 @@FETCH_STATUS 함수를 사용하는 것이 좋습니다.
@@FETCH_STATUS 함수는 다음을 반환합니다.
가져오기가 성공한 경우 0입니다.
커서 외부의 줄을 가져오려는 시도로 인해 가져오기가 실패한 경우 1입니다.
2 삭제되거나 수정된 행에 대한 액세스 시도로 인해 가져오기가 실패한 경우입니다.
DECLARE @id_kl INT, @firm VARCHAR(50), @fam VARCHAR(50), @message VARCHAR(80), @nam VARCHAR(50), @d DATETIME, @p INT, @s INT SET @s=0 PRINT "쇼핑 목록" DECLARE klient_cursor CURSOR LOCAL FOR SELECT 클라이언트 코드, 회사, 성 클라이언트 FROM WHERE City="Moscow" ORDER BY 회사, 성 OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @id_kl, @firm, @fam WHILE @@FETCH_STATUS =0 BEGIN SELECT @message="클라이언트 "+@fam+ "회사"+ @firm PRINT @message SELECT @message="제품명 구매일 비용" PRINT @message DECLARE tovar_cursor CURSOR FOR SELECT 제품.이름, 거래.날짜, 제품 .Price* Transaction.Quantity AS Cost FROM Product INNER JOIN Transaction ON Product. 제품 코드=트랜잭션.제품 코드 WHERE 트랜잭션.고객 코드=@id_kl OPEN tovar_cursor FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p IF @@FETCH_STATUS<>0 PRINT "구매 없음" @@FETCH_STATUS=0 BEGIN SELECT @message=" "+@nam+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) PRINT @message SET @s=@s+@p FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p END CLOSE tovar_cursor DEALLOCATE tovar_cursor SELECT @message="총 비용 "+ CAST(@s AS CHAR(6)) PRINT @message -- 다음 클라이언트로 이동-- klient_cursor에서 NEXT FROM INTO @id_kl, @firm, @fam END CLOSE klient_cursor DEALLOCATE klient_cursor 예제 13.6. 고객이 모스크바에서 구매한 상품 목록과 총 비용을 표시하는 커서입니다.
예제 13.7.모스크바 고객을 위한 스크롤 가능한 커서를 개발합니다. 전화번호가 1로 시작하는 경우 해당 번호의 클라이언트를 삭제하고 첫 번째 커서 항목에서 전화번호의 첫 번째 숫자를 4로 바꿉니다.
DECLARE @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @message VARCHAR(80) PRINT "클라이언트 목록" DECLARE klient_cursor CURSOR GLOBAL SCROLL KEYSET FOR SELECT 회사, 성, 클라이언트 전화번호 WHERE City ="Moscow" 회사별 주문, 업데이트를 위한 성 OPEN klient_cursor klient_cursor INTO @firm, @fam, @tel WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="클라이언트 "+@fam+ " 회사 "+ @firm " Phone "+ @tel PRINT @message -- 전화번호가 1로 시작하는 경우 -- 해당 번호의 클라이언트를 삭제합니다. IF @tel LIKE '1%' DELETE Client WHERE CURRENT OF klient_cursor ELSE -- 다음으로 이동 client FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel END FETCH ABSOLUTE 1 FROM klient_cursor INTO @firm, @fam, @tel -- 첫 번째 항목에서 전화번호의 첫 번째 숫자를 4로 바꿉니다. UPDATE Client SET Phone ='4' + RIGHT(@ tel,LEN(@tel)-1)) klient_cursor의 현재 위치 SELECT @message="클라이언트 "+@fam+" 회사 "+ @firm "전화"+ @tel PRINT @message CLOSE klient_cursor klient_cursor 할당 해제 예제 13.7. 모스크바의 클라이언트를 위한 스크롤 가능한 커서입니다.
예제 13.8.용법 프로시저의 출력 매개변수로서의 커서. 프로시저는 데이터 세트(제품 목록)를 반환합니다.
프로시저를 호출하고 출력 커서에서 데이터를 인쇄하는 작업은 다음과 같이 수행됩니다.
DECLARE @my_cur CURSOR DECLARE @n VARCHAR(20) EXEC my_proc @cur=@my_cur OUTPUT NEXT FROM @my_cur INTO @n SELECT @n WHILE (@@FETCH_STATUS=0) BEGIN FETCH NEXT FROM @my_cur INTO @n SELECT @n 종료 종료 @my_cur 삭제 @my_cur
