오라클강의4

[은솔이네]

ㅁ 프로그래밍 요소

DECLARE : 값을 저장하는 임시장소9변수)를 정의
SET : 변수에 초기값 설정
IF ~ ELSE : 조건문
CASE : 다중 조건문
WHILE : 반복문
BEGIN ~ END : 다중문
BREAK / CONTINUE : 탈출문
RETURN : 복귀문
GOTO 라벨 : 분기문
WAITFOR : 지연문
EXEC[UTE] : 사용자정의함수, 프로시저, 확장프로시저 등을 실행
PRINT : 메시지 출력
RAISERROR ; 메세지 , 오류번호 출력
FORMATMESSAGE : 정의된번호 오류메세지 생성

ㅁ 변수

ㅇ 변수선언형식
- DECLARE@변수명 자료형 --> 변수선언
- SET @변수명 = 값 --> 변수 초기화
- SELECT @변수명 --> 값을 가져올때

[사용예]

DECLARE @ID VARCHAR(12), @NAME CHAR(12)
SELECT @ID = EMP_ID, @NAME=FNAME
FROM EMPLOYEE
WHERE HIRE_DATE = '19920827'

SELECT '@ID=', @ID , '@NAME=', @NAME

@ID = 'ABC', @NAME = 'ABCDEF'


DECLARE @SDATE DATETIME, @EDATE DATETIME
SET @SDATE= '19920601'
SET @EDATE = '19921231'

SELECT STOR_ID, ORD_NUM, QTY, TITLE_ID
FROM SALES
WHERE ORD_DATE BETWEEN @SDATE AND @EDATE



ㅁ 제어문
ㅇ 종류
- 조건제어문 : 조건을 부여하여 참,거짓여부에 따라 수행 결정
IF문, CASE문
- 반복제어문 : 조건에 참,거짓인 동안 반복해서 수행하는 문
WHILE문

ㅇ IF 문

[형식1]
IF 조건식
조건식이 참인 경우 실행할 명령문

[형식2]
IF 조건식
조건식이 참인 경우 실행할 명령문
ELSE
조건식이 거짓인 경우 실행할 명령문

[형식3]
IF 조건식1
조건식1 이 참인 경우 실행할 명령문
ELSE IF 조건식2
조건식2가 참인 경우 실행할 명령문
ELSE IF 조건식3
조건식3이 참인 경우 실행할 명령문
ELSE
조건식이 거짓인 경우 실행할 명령문

[사용예]

DECLARE @SDATE DATETIME, @EDATE DATETIME
SET @SDATE= '19920601'
SET @EDATE = '19921231'

IF @EDATE < @SDATE --> 에러조건 체크
BEGIN
SELECT '마지막 날은 최소한 시작일자보다는 캍거나 커야 합니다.'
RETURN --> 에러인 경우 무조건 종료
END
ELSE
BEGIN
SELECT * FROM SALES
WHERE ORD_DATE BETWEEN @SDATE AND @EDATE
END

ㅇ CASE 문

/* 다른언어와 달리 같은 조건만 처리하지 않고 크거나 같은 조건을 처리한다 */

[형식]
CASE
WHEN 조건1 THEN
조건1이 참인경우 실핼할 명령문
WHEN 조건2 THEN
조건2이 참인경우 실핼할 명령문
WHEN 조건3 THEN
조건3이 참인경우 실핼할 명령문
WHEN 조건4 THEN
조건4이 참인경우 실핼할 명령문
ELSE
어느 조건 에도충족하지 못한 경우 실행할 명령문
END

[사용예]
SELECT TITLE_ID AS '책 번호', QTY AS '수량', '등급'=
CASE
WHEN QTY >= 50 THEN 'A'
WHEN QTY >= 30 THEN 'B'
ELSE 'C'
END
FROM SALES
ORDER BY QTY DESC

ㅇ 반복제어문
- WHILE문
[형식]
WHILE 조건식
BEGIN
조건식이 침인 경우 반복할 명령문
END


[사용예]

CREATE TABLE A
(ID INT IDENTITY(1,1), --> 숫자값 증가 [형식] IDENTITY(시작값, 증가값)
NO VARCHAR(10)
)

SELECT * FROM A

DECLARE @I INT
SET @I = 1
WHILE @I <= 10
BEGIN
INSERT A(NO) VALUES(@I)
SET @I = @I + 1
END

SELECT * FROM A

[BREAK문가 CONTINUE문 예제]

DECLARE @I INT
SET @I = 1
WHILE @I <= 30
BEGIN
IF (@I % 5) = 0
BEGIN
SET @I = @I+ 1
CONTINUE
END
IF (@I % 15) = 0 BREAK
INSERT A(NO) VALUES(@I)
SET @I = @I + 1
END

SELECT * FROM A


ㅇ GOTO 문
DELETE FROM A

DECLARE @I INT
SET @I = 11
WHILE @I < 20
BEGIN
IF @I = 15 GOTO AAA
INSERT A(NO) VALUES (@I)
SET @I = @I + 1
END

AAA:
SELECT 'I의 값이 15입니다.'

SELECT * FROM A

ㅇ EXEC문
- 생략가능
- 쿼리문을 동적으로 수행할 때 사용

SP_HELP TITLES

EXEC SP_HELP TITLES

ㅇ 쿼리문의 동적예

DECLARE @TBL VARCHAR(10)
IF RAND() > 0.5 --> RAND() 랜덤함수
SET @TBL = 'TITLES'
ELSE
SET @TBL = 'AUTHORS'

EXEC ('SELECT * FROM ' + @TBL)


ㅁ 저장 프로시저
- TRANSACT-SQL코드가 많거나 작업이 반복되는 경우는.
저장프로시저가 TRANSACT-SQL코드로 만든 일괄처리보다 더 빨리 실행된다
- 수백줄이 TRANSACT-SQL코드가 필요한 작업을 네트워크로 보내지 않고
프로시저에서 코드를 실행하는 하나의 명령문을 통해 수행할 수 있다
- 사용이 쉽고 클라이언트/서버 환경에서 네트워크 부하를 줄이고
코드를 간단하게 하고 코드의 보안성을 가질 수 있다.

[형식]
1. 생성

CREATE PROC[EDURE] 프로시저명
매개변수 리스트 --> 쿼리문 수행시 필요한 변수들
AS
프로시저 호출시 실행될 쿼리문

<사용예>

CREATE PROC M_GENRE_INSERT
@G_CODE TINYINT, @G_NAME NVARCHAR(40)
AS
INSERT TBLGENRE
VALUES (@G_CODE, @G_NAME)



2. 호출
프로시저명 [매개변수값, 매개변수값, .....]

<사용예>

M_GENRE_INSERT '11', '액션'

SELECT * FROM TBLGENRE



3. 소멸

DROP PROC 프로시저명

DROP PROC M_GENRE_INSERT


[에제]
create proc spsearch
@search nvarchar(80) = '%'
AS
select title, type, price from titles
where title like '%' + @search + '%'
--> %는 와일드 카드이므로 매개변수가 주어지지 않으면 칼럼의 모든 내용 출력

spsearch --> 매개변수가 없으므로 칼럼의 모든내용 출력

spsearch 'computer' --> title 에 'computer'이라는 내용이 있는 것만 출력


4. 프로시저 코드 보기

sp_helptext spsearch

5. 프로시저 코드 암호화 하기

create proc spsearch2
@search nvarchar(80) = '%'
WITH ENCRYPTION --> 코드 암호화
AS
select title, type, price from titles
where title like '%' + @search + '%'

--> %는 와일드 카드이므로 매개변수가 주어지지 않으면 칼럼의 모든 내용 출력

sp_helptext spsearch2

6. 프로시저 이름 바꾸기

SP_RENAME 기존이름, 바꿀이름

SP_RENAME SPSEARCH2, SEARCH

sp_helptext spsearch2

sp_helptext search

7. 프로시저의 구조 바꾸기

ALTER PROC 프로시저명
매개변수리스트
AS
프로시저 역할

8. 매개변수

IN 매개변수 : 내부에서만 사용되는 매개변수
OU T매개변수 : 결과값을 반환하는 매개변수 (SQL2000 에서는 사용안함, 함수사용)

CREATE PROC spmemberaddr
@addr nvarchar(30) = '%', @adtot int OUTPUT --> 프로시저 죄종값을 저장하기 위한 매개변수
AS
select c_number, c_name, c_age, 'tel' = c_tel1 + '-' + c_tel2,
'address' = c_addr1 + ' ' + c_addr2
from tblcustomer
where c_addr1 like '%' + @addr + '%'

select @adtot = (select count(*) from tblcustomer --> 프로시저 최종값을 저장한다
where c_addr1 like '%' + @addr + @addr + '%')

DECLARE @outadtot int
exec spmemberaddr '자양', @outadtot output --> 매개변수 addr에 자양을 전달한다
select @outadtot --> 프로시저 최종값을 출력한다




ㅁ 주민번호 체크

1 2 3 4 5 6 - 1 2 3 4 5 6 7
* * * * * * * * * * * * *
2 3 4 5 6 7 8 9 2 3 4 5

합계값 % 11 = 나머지 값
11 - 나머지값 = 뺀값
뺀값 % 10 = 패리티값


- 문자열 입력받기, 문자열 나누기, 문자열을 숫자로 나누기



CREATE PROC jumin_parity
@jumin char(13) --> 주민번호를 받을 매개 변수
AS
DECLARE @i int, @ndigit int, @nsum int, @nlastdigit int
--> @i는 합계값 저장, @nlastdigit 는 계산된 패리티 값
--> @ndigit 는 입력된 주민번호 패리티 값
--> @nsum은 합계값, 나머지값, 뺀값을 저장
set @i = 0
set @nsum = 0
While @i < 12 --> 0부터 12까지 총 12번 반복하여 주민번호 12자리 체크 마지막 디지트 비교
begin --> @i 는 추출문자 위치 변수
SET @ndigit = CAST(substring(@jumin, @i+1, 1) AS INT)
--> 형변환함수 CAST(변수값 AS 변환자료형)
IF (@i >= 8) --> SUBSTRING(전체문자열, 추출시작위치, 추출문자수)
--> 반복되는 2, 3, 4, 5 수행
set @nsum = @nsum + @ndigit * ((@i + 1) % 9 + 2)

ELSE
--> 처음 1,2,3,4,5,6,7,8,9 수행
set @nsum = @nsum + @ndigit * ((@i + 1) % 9 + 1)
SET @i = @i + 1
end

SET @ndigit = CAST(substring(@jumin, @i + 1, 1) AS INT) --> 기존 주민번호 디지트 값 저장

/* set @nsum = @nsum % 11
set @nsum = 11 - @nsum
set @nlastdigit = @nsum % 10 를 간단히 다음 한줄로 표시 */

SET @nlastdigit = (11 - @nsum % 11) % 10; --> 나머지연산이 뺄셈 연산보다 우선한다

IF @ndigit = @nlastdigit --> 기존 디지트와 계산 디지트 비교
print '올바른 주민등록 번호 입니다.'
else
print '잘못된 주민등록 번호 입니다.'

<사용예>

jumin_parity '7501011777770'

jumin_parity '7501012777772'

jumin_parity '7501012888881'

jumin_parity '1234567890123'

jumin_parity '6543210123453'



ㅁ 함수

ㅇ 함수의 생성(정의)

[형식]
CREATE FUNCTION 함수명 ( [매개변수 리스트])
RETURNS 자료형 (일반자료형, TABLE)
--> 스칼라함수 : 리턴값이 일반자료형 , 인라인테이블반환함수 : 리턴값이 테이블 형
AS
함수 호출시 실행한 명령문


[예제]
CREATE FUNCTION HAP(@KOR INT, @ENG INT, @MAT INT)
RETURNS INT
AS
BEGIN
RETURN (@KOR + @ENG + @MAT)
END

ㅇ함수의 호출

SELECT DBO.HAP(100,100,100) AS '총점' --> 함수 호출시 소유권을 명시해야 한다.

ㅇ 인라인테이블반환함수

CREATE FUNCTION SALESSEARCH(@JUM SMALLINT)
RETURNS TABLE
AS
RETURN(
SELECT * FROM SALES
WHERE QTY > @JUM
)

SELECT * FROM DBO.SALESSEARCH(30)


ㅁ 트리거
ㅇ 개념
- 여러개의 테이블에서 테이블A의 칼럼 정보가 변경(입력,수정,삭제)될 경우
테이블B의 관련이 있는 컬럼도 변경되어야 하는경우
사용자가 별도의 작업없이 자동으로 처리되도록 하는 것

ㅇ 생성
[형식]
CREATE TRIGGER 트리거명
ON 주테이블명
[ FOR / AFTER / INSTEAD OF ] INSERT, DELETE, UPDATE
AS
트리거 발생시 실행할 실행문

[사용예]

CREATE TABLE M
(
id int PRIMARY KEY,
name varchar(10) NOT NULL
)

create table S
(
id int,
name varchar(10) not null,
etc varchar(15)
)


select * from m
select * from s

create trigger m_insert
ON m
FOR insert
AS
insert s
select id, name, 'insert test' from inserted
--> inserted테이블은 시스템테이블로 입력작업시 생성되는 임시테이블이다
--> 모든 입력작업은 inserted테이블에 입력된 후 지정 테이블로 입력된다.

insert m values(100, '고돌이')
insert m values(120, '도돌이')
insert m values(140, '유돌이')
insert m values(270, '장돌이')

insert m values(300, '판돌이')
insert m values(400, '통돌이')

select * from m
select * from s


create trigger m_delete
ON m
FOR delete
AS
delete s
where id in (select id from deleted)

delete from m where id = 120

select * from m
select * from s


create trigger m_update
ON m
FOR update
AS
IF UPDATE(name)
begin
update s
set name = inserted.name,
etc = 'update test'
from s inner join inserted
on s.id = inserted.id
end

update m set name = '장돌이' where id = 270
update m set name = '홍길동' where id = 300
update m set name = '박지성' where id = 100

select * from m
select * from s order by id


create trigger m_trig
ON m
FOR insert, update, delete
AS
IF EXISTS(select * from deleted) --> EXISTS()는 ()안에 값이 존재하는지 판단하는 함수
BEGIN
print 'deleted table'
select * from deleted
END
IF EXISTS(select * from inserted)
BEGIN
print 'inserted table'
select * from inserted
END

insert m values(500, '히딩크') --> 메시지 창에 트리거 m_trig의 print 내용을 출력한다.

select * from m
select * from s order by id

delete from m where id = 1
--> 메시지 창에 트리거 m_trig의 print 내용을 출력한다.

select * from m
select * from s order by id

update m set name='홍명보' where id=400
--> 메시지 창에 트리거 m_trig의 print 내용을 출력한다.


select * from m
select * from s order by id

ㅇ AFTER 트리거 : 제약조건 처리후 트리거작업이 실행된다
ㅇ INSTEAD OF 트리거 : 제약조건이 처리되기 전에 트리거작업이 처리된다

insert m values(140, '안정환')


create trigger m_insert2
ON m
INSTEAD OF insert
AS
insert s
select id, name, 'insert test' from inserted

insert m values(140, '안정환')

select * from m
select * from s order by id