[database] SQL Stored Procedures

create procedure CountCoursesDepartment
as
begin
    select dept_name, count(*) as course_count
    from course
    where dept_name is not null
    group by dept_name
    order by dept_name
end

exec CountCoursesDepartment

 

create procedure GetCoursesDepartment
  @dept varchar(20)
as
begin
    select course_id, title, credits
    from course
    where dept_name = @dept
    order by course_id
end

exec GetCoursesDepartment @dept = 'Comp. Sci.'

 

 View (뷰) vs  Stored Procedure (프로시저)

목적	SELECT 쿼리 결과를 가상 테이블처럼 저장	일련의 SQL 명령어(로직)를 저장
내용	SELECT 문 한 개만 포함 가능	SELECT, INSERT, UPDATE, IF, LOOP 등 자유롭게 사용 가능
기능	조회 전용 (수정 제한적)	조건문, 반복, 예외처리, 변수, 파라미터 등 논리 흐름 제어 가능
입출력	입력 X, 출력은 SELECT 결과만	입력 파라미터, 출력 파라미터 모두 가능
재사용성	여러 쿼리에서 테이블처럼 사용 가능 (from 뷰)	EXEC 프로시저() 형태로 호출해야 함
보안성	사용자에게 뷰만 보여줘도 테이블 열람 가능	프로시저만 실행권한 주면 내부 로직과 테이블은 숨길 수 있음
결합성	JOIN, 서브쿼리에 넣기 쉬움	프로시저 결과는 테이블처럼 직접 JOIN 불가 (단, 일부 DB는 함수로 우회 가능)
예시	복잡한 SELECT 문 재사용	데이터 삽입, 검증, 배정, 처리 로직 캡슐화

 

 

create procedure AssignInst2Section
  @inst_id varchar(5),
  @c_id varchar(8),
  @s_id varchar(8),
  @sem char(6),
  @yr smallint,
  @error_message nvarchar(100) output
as
begin
    set nocount on;
    set @error_message = '';

    declare @instructor_exists int;
    declare @section_exists int;

    begin try
        -- Instructor 존재 여부 확인
        select @instructor_exists = count(*)
        from instructor
        where instructor_id = @inst_id;

        -- Section 존재 여부 확인
        select @section_exists = count(*)
        from section
        where course_id = @c_id 
          and sec_id = @s_id 
          and semester = @sem 
          and year = @yr;

        -- Instructor 존재하지 않으면 오류 메시지
        if @instructor_exists = 0
        begin
            set @error_message = 'Error: Instructor ID does not exist';
            return;
        end

        -- Section 존재하지 않으면 오류 메시지
        if @section_exists = 0
        begin
            set @error_message = 'Error: Section does not exist';
            return;
        end

        -- Semester 유효성 검사
        if @sem not in ('Fall', 'Winter', 'Spring', 'Summer')
        begin
            set @error_message = 'Error: Invalid Semester';
            return;
        end

        -- Year 유효성 검사
        if @yr < 1702 or @yr > 2099
        begin
            set @error_message = 'Error: Invalid year';
            return;
        end

        -- 정상일 경우 teaches 테이블에 삽입
        insert into teaches(instructor_id, course_id, sec_id, semester, year)
        values(@inst_id, @c_id, @s_id, @sem, @yr);

        set @error_message = 'Instructor assigned to section successfully';
    end try

    begin catch
        set @error_message = 'Error: Failed to assign instructor to section. ' + ERROR_MESSAGE();
    end catch
end


declare @error_msg nvarchar(100);

exec AssignInst2Section
  @inst_id = 'I12345',
  @c_id = 'CS101',
  @s_id = '001',
  @sem = 'Fall',
  @yr = 2025,
  @error_message = @error_msg output;

select @error_msg as result;


---위 exec로 AssignInst2Section 실행
---실행 후 @error_msg에 결과 메시지가 담김
---마지막에 그 메시지를 result 컬럼 이름으로 출력

 

 

exec AddnewInstructor1
  @inst_id = '88888',
  @inst_name = 'Roy',
  @dept = 'Comp. Sci.',
  @inst_salary = 50000,
  @error_message = @error_msg output;

select @error_msg as result;

select * from instructor;

create procedure UpdateStudentCredits
  @stud_id varchar(5),
  @additional_credits smallint,
  @error_message nvarchar(200) output
as
begin
    set nocount on;
    declare @current_credits smallint;
    declare @student_exists int;

    set @error_message = '';

    begin try
        -- 1. 학생 존재 여부 확인
        select @student_exists = count(*)
        from student
        where student_id = @stud_id;

        if @student_exists = 0
        begin
            set @error_message = 'Error: student ID does not exist.';
            return;
        end

        -- 2. 추가 학점이 음수인지 검사
        if @additional_credits < 0
        begin
            set @error_message = 'Error: additional credits cannot be negative';
            return;
        end

        -- 3. 현재 학점 가져오기
        select @current_credits = tot_cred
        from student
        where student_id = @stud_id;

        -- 4. 학점 업데이트
        update student
        set tot_cred = @current_credits + @additional_credits
        where student_id = @stud_id;

        set @error_message = 'Student credits updated successfully';
    end try

    begin catch
        set @error_message = 'Error: failed to update student credits. ' + ERROR_MESSAGE();
    end catch
end


declare @error_msg nvarchar(100);

exec UpdateStudentCreds
  @stud_id = '00128',
  @additional_credits = -3,
  @error_message = @error_msg output;

select @error_msg as result;