[新정보처리기사 실기] SQL 응용 (2/2)

1.SQL -DDL

2.SQL -DCL

3.SQL -DML

4.DML-SELECT-1

5.DML-SELECT-2

6.프로시저

7.트리거

8.사용자 정의 함수

9.제어문

10.커서

 

 

 

6.프로시저

 

a) 프로시저의 개요

 -프로시저란 절차형 SQL을 호라용하여 특정 기능을 수행하는 일종의 트랜잭션언어로, 호출을 통해 실행되어 미리 

 저장해놓은 SQL 작업을 수행한다.

  o 프로시저 만들어 DB에 저장하면 여러 프로그램에서 호출할 수 있다

  o 프로시저는 DB에 저장되어 수행되기 때문에 스토어드(stored) 프로시저라고도 불린다

  o 주로 일일 마감 작업,일괄 작업에 주로 사용한다.

  *트랜잭션 언어= 데이터베이스를 조작하고 트랜잭션을 처리하는 언어 

 

Declare #(필수)
Begin #(필수)
 control
 sql
 Exception
 Transaction
End #(필수)

declare= 트리거에 사용할 변수 데이터 타입 선언

event= dml 중 어떤 이벤트에 반응할것인지 작성

begin - end = 트리거 시작과 종료

control= 조건문 또는 반복문

sql= sql문 작성

exception= 예외처리

transaction= 수행된 데이터 작업을 db에 적용할지 취소할지를 처리하는 곳

 

b) 프로시저 생성

create [or replace] procedure 프로시저명(파라미터)
[지역변수 선언]
begin
	트리거 body;
end

파라미터

 -in= 프로시저 내로 값 전달

 -out= 프로시저 밖으로 값 반환

 -inout= 전달 과 반환 둘다

 -매개변수명= 호출 프로그램에서 전달 받은 값을 저장할 변수의 이름

 -자료형= 변수의 자료형

 

create or replace procedure emp_change(i.사원번호 in int)
is
begin
	update 급여 set 지급방식 ='s' where 사원번호=i_사원번호;
    exception
    		when program_error then rollback;
    commit;
end

 

c) 프로시저 실행

execute 프로시저명;
exec 프로시저명;
call 프로시저명;

 

d) 프로시저 제거

drop procedure 프로시저명;

 

7. 트리거

 

a) 트리거의 개요

= 트리거는 데이터베이스 시스템에서 이벤트가 발생할때마다 관련 작업이 자동으로 수행되는 절차형 SQL이다.

= 트리거는 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지,로그 메세지 출력 등으로 사용

= 트리거의 구문에는 DCL을 사용할 수 없다.

= 트리거 오류로 인해 데이텅에 영향을 줄 수 있으므로 세심한 주의가 필요함

 

b) 트리거의 구성

Declare #(필수)
Event #(필수)
Begin #(필수)
 control
 sql
 Exception
End #(필수)

declare= 트리거에 사용할 변수 데이터 타입 선언

event= dml 중 어떤 이벤트에 반응할것인지 작성

begin - end = 트리거 시작과 종료

control= 조건문 또는 반복문

sql= sql문 작성

exception= 예외처리

 

c) 트리거의 생성

create [or replace] trigger 트리거명 [동작시기 옵션][동작 옵션] on 테이블명
referencing [new | old] as 테이블명
for each row
[when 조건식]
begin
	트리거 body;
end

create trigger 학년정보_Tri before insert on 학생
referencing new as new_table
for each row
when (new_table.학년 is null)
begin
	:new_table.학년 :='신입생';
end

/* 
학생 테이블에 어떤 튜플이 삽입되기 전에 
삽입될 튜플의 학년이 널이면 신입생을 넣어주겠다
 */

*or replace = 만약 같은 이름의 트리거가 있으면 대체함

*동작시기 옵션= before,after   (동작 전/후) 

*동작 옵션 = insert,delete,update     (어떤 이벤트에 반응 할 것인지?)

*new | old = new는 새로 추가될 테이블 / old는 기존의 테이블

*for each row= 각 튜플마다 적용

*when 조건식 = 트리거를 적용할 튜플의 조건

*트리거 바디 = sql문장 반드시 하나는 있어야함

 

d) 트리거의 제거

drop trigger 트리거명

 

8. 사용자 정의 함수

 

a) 사용자 정의 함수의 개요

 - 사용자 정의 함수는 프로시저와 유사하게 sql을 사용하여 일련의 작업을 연속적으로 처리하며, 종료 시 처리 결과를

 단일값으로 반환한다.

  o 사용자 정의 함수는 예약어 return을 통해 값을 반환하기 때문에 출력 파라미터가 없다

  o 사용자 정의 함수는 select 만 사용가능 (DML 중에)

  o 사용자 정의 함수는 프로시저 호출하지 못한다.

 

b) 사용자 정의 함수의 구성

Declare #(필수)
Begin #(필수)
 control
 sql
 Exception
 return #(필수)
End #(필수)

return = 호출 프로그램에 반환할 값이나 변수를 정의

 

c) 사용자 정의 함수 생성

create [or replace] fucntion 사용자 정의 함수(파라미터)
[지역변수 선언]
begin
	사용자 정의 함수 body;
    return 반환값;
end

return= 반환할 값이나 반환할 값이 저장된 변수를 호출 프로그램으로 돌려준다

 

create fucntion Get_s_성별 (i_성별코드 in int)
return varchar2
is
begin
	if i_성별코드 = 1 then
    	return '남자';
    ELSE
    	return '여자';
   	end if;
end

 

d) 사용자 정의 함수 실행

select 사용자 정의 함수명 from 테이블명;
insert into 테이블명(속성명) values (사용자 정의 함수명);

 

e) 사용자 정의 함수 제거

drop function 사용자 정의 함수명;

 

 

9. 제어문

 

a) 제어문의 개요

 - 절차형 sql은 '절차형'이라는 말 그대로 sql 명령어의 서술된 순서에 따라 차례 대로 실행되는데 제어문은

 이러한 진행 순서를 변경하기 위해 사용하는 명령문이다.

 

b) if문 = 조건문을 위해 사용

if 조건 then
	실행할 문장1
    실행할 문장2
    	...
else 
	실행할 문장n
end if

 

c) loop문 = 반복하기 위해 사용

# 기본 loop문
loop
	실행할 문장;
    exit when 조건;
end loop

# for loop
for 변수 in 초기값..종료값
loop
	실행할 문장;
end loop

# while loop
while 조건
loop 
	실행할 문장;
end loop;

 

d) continue= 반복문의 실행을 제어함 ( continue의 이후의 문장은 실행하지않고 제어를 반복문 처음으로 이동)

continue when 조건;

 

e) goto문 =원하는 위치로 이도하여 명령문을 수행하기 위한 제어문

goto 레이블;

<<레이블>>
실행할 문장;

 

 

10. 커서

 

a) 커서의 개념 = 쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터이다.

 - 커서는 내부에서 자동으로 생성되어 사용되는 묵시적 커서와, 정의해서 사용하는 명시적 커서가 있다.

 - 묵시적 커서는 각 단계가 자동으로 수행되지만, 명시적 커서는 직접 구현해야한다.

 - 묵시적 커서는 수행된 쿼리문의 정상적인 수행 여부를 확인 하기 위해 사용 되며, 명시적 커서는 쿼리문의 결과를 저장하여 사용함으로써 동일한 쿼리가 반복 수행되어 데이터베이스 낭비를 막는다.

 

b) 묵시적 커서 = dbms 자체적으로 열리고(open) 패치(fetch)되어 사용이 끝나면 닫히지만(close) 커서의 속성을 조회하여 쿼리 정보를 열람하는 것이 가능하다

 - 커서의 속성

SQL%FOUND	o 쿼리 수행의 결과로 패치 된 튜플이 한개이상이면 TRUE
SQL%NOTFOUND	o 쿼리 수행의 결과로 패치 된 튜플이 0개 면 TRUE
SQL%ROWCOUNT	o 쿼리 수행 결과로 패치된 튜플 수를 반환
SQL%ISOPEN	o 커서가 열린 상태면 true o 묵시적 커서는 자동으로 생성하고 닫아버리기 때문에 항상 false

 

c) 명시적 커서 = 사용자가 직접 ㅈ어의해서 사용하는 커서로 주로 select문의 결과로 나온 튜플을 제어하기 위해 사용

 -순서 열기(open)- 패치(fetch) - 닫기(clsoe)  / 반드시 시작할때 선언을 해야한다.

 

-선언 형식

cursor 커서명(매개변수1,매개변수2,.);
is
select 문;

-열기 형식

open 커서명(매개변수1,매개변수2,)

-패치 형식

fetch 커서명 into 변수1, 변수,..;

-닫기 형식

close 커서명;

 

declare
	p_name employee.name%type;
    cursor cur_name(ff int)
    is
    select name from employee where id >=ff;
    
begin
	open cur_name(20);
    loop
    	fetch cur_name into p_name;
        exit when cur_name%notfound;
        dbms_output.put_line(p_name);
    end loop
end