1. 저장프로시저란 무엇인가?
저장 프로시저는 한 번 컴파일되고 여러 번 실행될 수 있습니다(저장 프로시저는 서버에 저장됨). 미리 컴파일된 컬렉션이며 빠르게 실행됩니다.
2. 일반적으로 사용되는 시스템 저장 프로시저
암호
-- 목적: 일반적으로 사용되는 시스템 저장 프로시저
EXEC sp_databases --현재 시스템의 데이터베이스 나열
EXEC sp_renamedb 'test','test1'--데이터베이스 이름 변경(단일 사용자 액세스)
스터드 사용
가다
EXEC sp_tables --현재 데이터베이스에서 쿼리된 개체 목록
EXEC sp_columns stuInfo --테이블 열에 대한 정보를 반환합니다.
EXEC sp_help stuInfo --테이블 stuInfo 정보 보기
EXEC sp_helpconstraint stuInfo --stuInfo 테이블의 제약 조건 보기
EXEC sp_helpindex stuMarks --stuMarks 테이블의 인덱스 보기
EXEC sp_helptext 'view_stuInfo_stuMarks' --뷰의 명령문 텍스트 보기
EXEC sp_stored_procedures --현재 데이터베이스의 저장 프로시저 목록을 반환합니다.
마스터 사용
가다
exec xp_cmdshell 'mkdir D:bank',no_output--폴더 생성
3. 사용자 정의 저장 프로시저
(1.) 매개변수가 없는 저장 프로시저
암호
스터드 사용
가다
존재하는 경우(이름='proc_stu'인 sysobjects에서 * 선택)
proc proc_stu 삭제
가다
프로시저 proc_stu 생성
처럼
DECLARE @writeAvg float,@labAvg float --필기시험 및 컴퓨터 시험의 평균 점수 변수
stuMarks에서 @writerAvg=AVG(writerExam), @labAvg=AVG(labExam)를 선택하세요.
'필기 시험 평균 점수:'+convert(varchar(5),@writerAvg)를 인쇄합니다.
'컴퓨터 테스트 평균 점수:'+convert(varchar(5),@labAvg) 인쇄
IF(@writerAvg>70 AND @labAvg>70)
'이 클래스의 시험 결과: 우수'를 인쇄합니다.
또 다른
'이 클래스의 시험 결과: 나쁨'을 인쇄합니다.
인쇄 '------------------------------------------------ --'
'이 시험에 합격하지 못한 학생:'을 인쇄합니다.
stuInfo에서 stuName,stuInfo.stuNo,writerExam,labExam을 선택하세요.
내부 조인 stuMarks ON stuInfo.stuNo=stuMarks.stuNo
시험<60 또는 labExam<60 작성 장소
가다
exec proc_stu --저장 프로시저 실행(2.) 입력 매개변수가 있는 저장 프로시저
암호
스터드 사용
가다
/*---존재 여부 감지: 저장 프로시저가 시스템 테이블 sysobjects에 저장됩니다---*/
IF EXISTS (SELECT * FROM sysobjects WHERE 이름 = 'proc_stu' )
삭제 절차 proc_stu
가다
/*---저장 프로시저 생성----*/
프로시저 생성 proc_stu
@writePass int,
@labPass int-- 매개변수를 지정하지 않고도 실행이 proc_stu와 같이 실행될 수 있도록 기본값을 추가할 수 있습니다.
처럼
print '작성 합격 표시:'+convert(varchar(5),@writerPass)
'컴퓨터 테스트 통과 라인:'+convert(varchar(5),@labPass) 인쇄
인쇄 '------------------------------------------------ --'
'이 시험에 합격하지 못한 학생:'을 인쇄합니다.
stuInfo에서 stuName,stuInfo.stuNo,writerExam,labExam을 선택하세요.
내부 조인 stuMarks ON stuInfo.stuNo=stuMarks.stuNo
WHERE WHERE WrittenExam<@writerPass 또는 labExam<@labPass
가다
exec proc_stu 60,55
exec proc_stu 60,@labPass=55
exec proc_stu @writerPass=60,@labPass=55
exec proc_stu @writerPass=60,55--이 줄은 오류를 보고합니다. 매개변수 2는 전달되어야 하며 후속 매개변수는 '@name = value' 형식으로 전달되어야 합니다.
-- '@name = value' 형식을 사용하면 이후의 모든 매개변수는 '@name = value' 형식으로 전달되어야 합니다.
(3.) 출력 매개변수가 있는 저장 프로시저
암호
스터드 사용
가다
/*---존재 여부 감지: 저장 프로시저가 시스템 테이블 sysobjects에 저장됩니다---*/
IF EXISTS (SELECT * FROM sysobjects WHERE 이름 = 'proc_stu' )
삭제 절차 proc_stu
가다
/*---저장 프로시저 생성----*/
프로시저 생성 proc_stu
@notpassSum int OUTPUT, --OUTPUT 키워드, 그렇지 않으면 입력 매개변수로 간주됩니다.
@writePass int=60, --기본 매개변수는 뒤에 배치됩니다.
@labPass int=60 --기본 매개변수는 뒤에 배치됩니다.
처럼
print '작성 합격 표시:'+convert(varchar(5),@writerPass)
+ '컴퓨터 기반 테스트 통과 라인:'+convert(varchar(5),@labPass)
인쇄 '------------------------------------------------ --'
'이 시험에 합격하지 못한 학생:'을 인쇄합니다.
stuInfo에서 stuName,stuInfo.stuNo,writerExam,labExam을 선택하세요.
내부 조인 stuMarks ON stuInfo.stuNo=stuMarks.stuNo
WHERE WHERE WrittenExam<@writerPass 또는 labExam<@labPass
/*--시험에 실패한 학생 수를 세고 반환합니다--*/
SELECT @notpassSum=COUNT(stuNo)FROM stuMarks
WHERE WHERE WrittenExam<@writerPass 또는 labExam<@labPass
가다
/*---저장 프로시저 호출----*/
@sum int 선언
EXEC proc_stu @sum OUTPUT ,64 --컴퓨터 시험 합격 점수는 기본값을 채택합니다. 필기 시험 합격 점수는 64점, 컴퓨터 시험 합격 점수는 60점입니다.
인쇄 '------------------------------------------------ --'
IF @sum>=3
print '불합격자 수:'+convert(varchar(5),@sum)+ '명, 60% 이상이면 합격점수를 낮추어야 합니다'
또 다른
print '불합격자 수:'+convert(varchar(5),@sum)+ '명, 60% 미만으로 통제되었으며 합격 점수는 보통입니다'
가다