10 장 SQL 프로그래밍 1

10장 SQL 프로그래밍1

스크립트의 기초 • 개체를 생성하거나 또는 수정하려 할 때 사용된 일련의 문장 • 문장에 사용되는 각종 예약어 및 사용법 • USE : 사용할테이터베이스 선택 • 변수 : 변화할 수 있는 양을 나타내는 문자, @으로시작됨 • DECLARE 선언문 이용 • 스칼라 변수 : 하나의 값을 갖는 변수 (int, char, varchar 등의 데이터 타입 사용) • 테이블 변수 : 테이블 형식의 변수 • 커서 변수 • text, ntext, image 데이터 형식은 변수로 사용할 수 없음 • @@은구성함수(전역변수)를 의미함 • 구성함수(시스템 전역변수) 사용법 • 시스템 운영 상태를 보여주는 구성함수(page258 참고) • SQL 문의 실행 상태를 보여주는 구성함수(page259 참고)

운영상태 구성함수 • @@dbts : 현재 데이터베이스의 마지막 timestamp 값을 반환

실행상태 구성함수 • @@rowcount : 영향 받은 행 수 반환 • @@identity : identity열을 가진 테이블에서 행을 추가한 후 마지막으로 입력된 identity 열의 값을 반환 행 수

실행상태 구성함수 • @@nestlevel : 저장프로시저 내에서 다른 저장 프로시저를 실행할 때 프로시저의 중첩 수를 반환 저장 프로시저 안에서 실행될 저장 프로시저 생성 기 생성한 프로시저를 포함하는 저장 프로시저 생성 저장 프로시저 실행 Execute일 때 = 중첩수 + 1

일괄처리(batch) • 일괄 처리란? • 하나 이상의 쿼리를 한번에 실행하는 단위 • ‘go’ 예약어로 분리되는 부분까지, 그리고 ‘go 3’은 3번 반복 • 제약사항 • Createdefault, create function, create procedure, create trigger, create view문과 다른 SQL문을 동일한 일괄처리에서 사용할 수 없음

일괄처리(batch) • 일괄 처리와 트랜잭션을 사용하는 경우 쿼리 실행 • XACT_ABORT 옵션 설정에 따라 실행에 영향을 받음 • 에러의 종류 • 문법에러, 개체 참조 에러, 런타임에러, 제약사항 에러 • 에러의 종류에 따라 실행의 결과가 다름 • 현재 트랜잭션의 rollback 여부결정 • XACT_ABORT가 ON으로 설정 : 런타임 에러 시 전체 트랜잭션이 종료된 후 rollback됨 • OFF로 설정 : 에러를 발생시킨 쿼리만 rollback됨 XACT_ABORT = OFF 일때

일괄처리(batch) • 일괄 처리와 트랜잭션을 사용하는 경우 쿼리 실행

ODBC와 SQL Native Client를공급자라 함 • 쿼리 수행을 위한 도구 • GUI : SQL Server Management Studio • CMD : OSQL(ODBC 사용) , 그리고 SQLCMD(SQL Native Client 사용) • 일반쿼리, 시스템저장 프로시저, 스크립트 파일 실행 가능

SQLCMD 의 사용 • SQLCMD로 SQL Server에 접속하기 • 트러스트연결 : SQLCMD –E –S 서버이름 ( 윈도우 계정으로 연결) • SQL 계정으로 연결 : SQLCMD –U 계정 –P 암호 –S 서버이름 (SQL 계정으로 연결)

흐름 제어 언어 • 일반쿼리, 명령문 블록, 저장프로시저의 실행흐름을 제어 • 종류

흐름 제어 언어 • 시작과 끝 • 분기(goto) Begin ‘SQL’ End /*레이블 정의 */ Label : /* 실행 변경 */ Goto Label;

If(조건식) Begin ‘SQL’ End Else begin ‘SQL’ end • 흐름 제어 언어 • 조건문 • 반환(return) • 호출한 곳으로 제어권을 넘겨주는 문 • Return문을 만나면 처리가 종료되고 그 이후의 문장은 수행되지 않음 • 저장 프로시저에서는 반환 시 호출자에게 int 형식의 데이터를 반환할 수 있음 • 대기(waitfor) • 지정한시각에 다음 문장을 실행하거나 일정시간 지연 후 다음 문장 실행 Waitfor delay ‘00:00:10’ ; 10초간 대기 Return 문 이후는 실행되지 않음

흐름 제어 언어 • 반복문(while)

예외 처리 제어문 : 예외가 발생할 경우 처리되는 문장 • TRY CATCH : try 블록과 catch 블록으로 구성됨 Try블록으로 예외 상황 발생되는 sql 문 작성 Catch 블록에는 try 블록에서 발생된 예외를 처리할 sql문 작성

예외 처리 제어문 사용 시 고려사항 • Try, catch 문은 항상 하나의 일괄처리 내에 존재해야 함 • Try블록과 catch 블록 사이에는 어떠한 sql 문도 와서는 안됨 • 중첩될 수 없음 • Try Catch문에서 에러 함수 사용 • Catch 블록내에서 에러 정보를 얻을 때 사용됨 • Catch 블록 내에서만 의미가 있음 • Error_number() : 발생한에러번호 • Error_severity() : 에러 심각도 (1 ~ 25) • 17번 이후 심각도는 관리자 몫 • Error_state() : 에러상태 • Error_procedure() : 에러가발생된 프로시저, 또는 트리거 이름 반환 • Error_line() : 에러가발생한 줄 번호 • Error_message() : 에러의내용 반환 Zero divide

예외 처리 제어문 • Try catch 문으로 처리할 수 없는 에러 • Try catch 문 내에서의 문법 에러 • Try catch 문 내에서의 개체 참조 에러 • Try catch 문에서 트랜잭션 사용 시 주의 사항 • Commit과 Rollback 사용 • Try 블록에서발생된 에러는 적절히 보충하고 commit 후 완료 수순 밟음 • 또는 try 블록에서에러가 발생되기 전 상태로 되돌리는 rollback 수순 밟음 • 데이터 무결성을 위해 rollback이 일반적인 작업 처리 Rollback transaction

예외 처리 제어문 • Try catch 문에서 트랜잭션 사용 시 주의 사항 • XCAT_STATE() 함수의 리턴값에 따라…. Commit 가능 여부 결정 • 값이 1이면 commit 가능, 그렇지 않으면 불가 앞 예제의 수정 구문

예외 처리 제어문 • 사용자 정의 메시지 추가 : SP_ADDMESSAGE • 형식 (page282 참고) • 기존 메시지 변경 : FORMATMESSAGE • 형식 • SP_ADDMESSAGE에 의해 생성된 메시지에 대해서만 동작함 • 원하는 메시지 작성 함수 : RAISERROR • 응용 프로그램으로 원하는 메시지 전달 • 형식 • 사용자 메시지삭제 함수 : SP_DROPMESSAGE

기타 제어문 • CASE : true 또는 false가 아닌 조건에 따라 분기 • EXECUTE(EXEC) : 저장 프로시저 실행, 스칼라 함수, 확장 프로시저, 그리고 일괄처리내의 쿼리 실행 • SET : 변수에 값을 할당, 환경 변수 값 설정 • 조건 함수 : 조건에 따라 값을 반환하는 함수 • NULLIF : 지정된 두 개의 표현식이 같으면 null, 같지않으면 첫 번째 값을 반환 • COALESCE 함수 : 해당 인수 중에서 null이아닌 첫 번째 값을 반환, 전부 null일 경우 에러 발생

Example

실습 – 제어문 작성하기 • CASE문을 사용한 열의 자동 형 변환 알아보기 • CASE 사용시 문자형으로 자동 형 변환(문자형+문자형=문자형) • CASE문 자동 형 변환(문자형+숫자형=숫자형) • SQLCMD의 사용 • SQLCMD를 사용해서 쿼리 실행 후 실행결과를 파일로 저장하기

10 장 SQL 프로그래밍 1

10 장 SQL 프로그래밍 1

Presentation Transcript