Stored Procedure
- 스토어드 프로시저란 Oracle에서 제공되는 프로그래밍 기능
- 스토어드 프로시저는 한 마디로 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용
형식 :
CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure
[ (argument [ { IN | OUT | IN OUT } ]
[ NOCOPY ]
[, argument [ { IN | OUT | IN OUT } ]
[ NOCOPY ]
datatype [ DEFAULT expr ]
] ...
)
]
[ inboker_rights_clause ]
{ IS | AS } -- 동일한 단어이며, 아무거나 사용해도 됨.
{ pl/sql_subprogram_body | EXECUTE_spec };1. 스토어드 프로시저 생성과 실행
1. 스토어드 생성
-- 스토어드 생성 형식
CREATE OR REPLACE PROCEDURE 스토어프_프로시저_이름( 파라미터 ) AS
변수 선언 부분
BEGIN
이 부분에 PL/SQL 프로그래밍 코딩..
END [스토어드_프로시저_이름];
-- 생성 예시
CREATE OR REPLACE PROCEDURE userPorc AS
v_bYear NUMBER; -- 변수 선언
BEGIN
SELECT birthYear INTO v_bYear FROM userTBL
WHERE userID = 'SSK'; -- 쿼리 결과를 변수에 대입
v_byear := v_bYear + 1; -- 변수에 1 증가
DBMS_OUTPUT.PUT_LINE (v_byear); -- 변수 값 출력
END userPorc;2. 스토어드 실행
-- 스토어드 실행(EXECUTE)문 형식
EXECUTE 스토어드_프로시저_이름();
또는
EXEC 스토어드_프로시저_이름();
-- 실행 예시
SET SERVEROUTPUT ON; -- 화면 출력을 허용(접속 끊기기 전까지 1회만 수행하면 됨
EXECUTE userPorc();2. 스토어드 프로시저 수정과 삭제
1. 스토어드 수정
- 스토어드 프로시저의 수정은 CREATE OR REPLACE PROCEDURE문을 다시 사용하면 된다
- 스토어드 생성과 동일하며 생성은 CREATE만 사용해도 되지만 대부분 CREATE OR REPLACE PROCEDURE를 같이 선언해준다(재사용성)
-- 스토어드 수정 형식
CREATE OR REPLACE PROCEDURE 스토어프_프로시저_이름( 파라미터 ) AS
변수 선언 부분
BEGIN
이 부분에 PL/SQL 프로그래밍 코딩..
END [스토어드_프로시저_이름];
-- 수정 예시
CREATE OR REPLACE PROCEDURE userPorc AS
v_bYear NUMBER; -- 변수 선언
BEGIN
SELECT birthYear INTO v_bYear FROM userTBL
WHERE userID = 'SSK'; -- 쿼리 결과를 변수에 대입
v_byear := v_bYear + 1; -- 변수에 1 증가
DBMS_OUTPUT.PUT_LINE (v_byear); -- 변수 값 출력
END userPorc;2. 스토어드 삭제
- 스토어드 프로시저 삭제는 DROP PROCEDURE문을 사용하면 된다
-- 스토어드 프로시저 삭제 형식
DROP PROCEDURE 스토어프_프로시저_이름;
-- 삭제 예시
DROP PROCEDURE userPorc;3. 파라미터 사용
- 파라미터의 사용 : 실행 시에 입력 파라미터(Parameter, 매개 변수)를 지정할 수 있다
-- 파라미터 선언
입력_파라미터_이름 IN 데이터_형식 [:= 디폴트 값]
-- 파라미터 전달
EXECUTE 스토어드_프로시저_이름(전달 값);
-- 파라미터 출력
출력_파라미터_이름 OUT 데이터_형식
-- 스토어드 프로시저 내부에서의 변수 선언 및 전달
DECLARE
변수명 데이터형식;
BEGIN
EXECUTE 스토어드_프로시저_이름(변수명);
DBMS_OUTPUT.PUT_LINE(변수명);
END
-- 변수 선언
입력_파라미터_이름 IN OUT 데이터 형식 [:= 디폴트 값]
pi_userName IN userTBL.userName%TYPE;
pi_userName IN NCHAR;4. PL/SQL 변수의 종류
-
PL/SQL 변수 선언 부분에 사용할 수 있는 변수의 종류는 일반 변수, 상수, %TYPE, %ROWTYPE, 레코드, 컬렉션 등이 있음
-
- 일반 변수 : 보통의 변수와 비슷하게 사용한다
v_userName NCHAR(8);
v_count NUMBER;- 상수 : CONSTANT 키워드를 사용해서 초기값을 주면 된다. 추후 변경이 되지 않는다.
v_myNum CONSTANT NUMBER(3) := 200;
v_myCHAR CONSTANT NVARCHAR2(10) := '가을단풍';- %TYPE : 대상 컬럼의 자료형과 길이를 참조해서 해당 변수에 적용하겠습니다.
pi_userName IN userTBL.userName%TYPE- %ROWTYPE : %TYPE은 1개의 열의 데이터 형식을 접근하지만, %ROWTYPE은 여러개의 열의 데이터 형식에 접근한다
v_userData usertbl%ROWTYPE;
v_buyData buyTBL%ROWTYPE;- 레코드(Record) :%ROWTYPE은 테이블에서 전체 열의 데이터 형식을 한꺼번에 자동으로 가져오지만 RECORD는 여러 열에 직접 데이터 형식을 지정
TYPE myRecordType IS RECORD (r_userName NVARCHAR2(20), r_addr NCHAR(2), r_height NUMBER(3));
v_record myRecordType;- 컬렉션(Collection) : 컬렉션은 프로그래밍 언어의 배열과 비슷한 형태로 사용된다, 컬렉션의 종류에는 VARRAY, 중첩 테이블, 연관 배열 등 3가지만 있다
TYPE myVarrayType IS VARRAY(3) OF NUMBER(10);
v_varray myVarrayType;
TYPE myNestType IS TABLE OF NVARCHAR2(10);
v_nest myNestType;
TYPE myAssocType IS TABLE OF NUMBER(5) INDEX BY VARCHAR2(10);
v_assoc myAssocType;5. 스토어드 프로시저 내의 예외 처리
- 스토어드 프로시저 내부에서 예외 상황(=오류)이 발생했을 경우에는 EXCEPTION WHEN 예외 THEN 처리할_문장 구문을 사용할 수 있음
3. 스토어드 프로시저 실습
0. sqlDB를 초기화(SYSTEM에서 삭제 후 재생성)
-- 유저 삭제
DROP USER sqlDB CASCADE;
-- 유저 생성 및 공간 부여
CREATE USER sqlDB IDENTIFIED BY 1234
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
-- 권한 부여
GRANT CONNECT, DBA, RESOURCE TO sqlDB;
-- 회원 및 구매 테이블 생성
CREATE TABLE userTBL -- 회원테이블
( userID CHAR(8) NOT NULL PRIMARY KEY, --사용자 아이디(PK)
userName nvarchar2(10) not null, --사용자 이름
birtYear number(4) not null, --출생 연도
addr nchar(2) not null, --지역(경기, 서울, 경남 식으록 2글자만 입력)
mobile1 CHAR(3), --휴대폰의 국번(010, 011, 016, 017, 018, 019 등)
mobile2 CHAR(8), --휴대폰의 나머지 전화번호(- 제외)
height number(3), --키
mDate DATE --회원 가입일
);
CREATE TABLE buyTBL -- 회원구매테이블
( idNum number(8) NOT NULL PRIMARY KEY, --순번(pk)
userID char(8) not null, --사용자아이디
prodName nchar(6) not null, --물품 명
groupName nchar(4), --분류
price number(8) not null, -- 단가
amount number(3) not null, -- 수량
FOREIGN KEY (userID) REFERENCES userTBL(userID)
-- 외래 키 설정하여 buyTBL의 userID를 userTBL(userID)를 참고하여 연결
);
-- 회원 및 구매 테이블에 데이터 입력
insert into userTBL values('LSG', '이승기', 1987, '서울', '011', '11111111', 182, '2008-8-8');
insert into userTBL values('KBS', '김범수', 1979, '경남', '011', '22222222', 173, '2012-4-4');
insert into userTBL values('KKH', '김경호', 1971, '전남', '019', '33333333', 177, '2007-7-7');
insert into userTBL values('JYP', '조용필', 1950, '경기', '011', '44444444', 166, '2009-4-4');
insert into userTBL values('SSK', '성시경', 1979, '서울', NULL, NULL, 186, '2013-12-12');
insert into userTBL values('LJB', '임재범', 1963, '서울', '016', '66666666', 182, '2009-9-9');
insert into userTBL values('YJS', '윤종신', 1969, '경남', NULL, NULL, 170, '2005-5-5');
insert into userTBL values('EJW', '은지원', 1972, '경북', '011', '88888888', 174, '2014-3-3');
insert into userTBL values('JLW', '조관우', 1965, '경기', '018', '99999999', 172, '2010-10-10');
insert into userTBL values('BBK', '바비킴', 1973, '서울', '010', '00000000', 176, '2013-5-5');
create SEQUENCE idSEQ; -- 순차번호 입력을 위해서 시퀀스 생성
insert into buyTBL values(IDSEQ.nextval, 'KBS', '운동화', null, 30, 2);
insert into buyTBL values(IDSEQ.nextval, 'KBS', '노트북', '전자', 1000, 1);
insert into buyTBL values(IDSEQ.nextval, 'JYP', '모니터', '전자', 200, 1);
insert into buyTBL values(IDSEQ.nextval, 'BBK', '모니터', '전자', 200, 5);
insert into buyTBL values(IDSEQ.nextval, 'KBS', '청바지', '의류', 50, 3);
insert into buyTBL values(IDSEQ.nextval, 'BBK', '메모리', '전자', 80, 10);
insert into buyTBL values(IDSEQ.nextval, 'SSK', '책', '서적', 15, 5);
insert into buyTBL values(IDSEQ.nextval, 'EJW', '책', '서적', 15, 2);
insert into buyTBL values(IDSEQ.nextval, 'EJW', '청바지', '의류', 50, 1);
insert into buyTBL values(IDSEQ.nextval, 'BBK', '운동화', null, 30, 2);
insert into buyTBL values(IDSEQ.nextval, 'EJW', '책', '서적', 15, 1);
insert into buyTBL values(IDSEQ.nextval, 'BBK', '운동화', null, 30, 2);1. 입력 파라미터가 있는 스토어드 프로시저를 생성하고 실행
-- 1
CREATE OR REPLACE PROCEDURE userProc1(
pi_userID IN USERTBL.USERID%TYPE
)
AS
v_uName VARCHAR(10);
BEGIN
SELECT userName INTO v_uName FROM userTBL
WHERE userID = pi_userID;
DBMS_OUTPUT.PUT_LINE(v_uName);
END;
SET SERVEROUTPUT ON;
EXECUTE userProc1('JKW');
-- 2
CREATE OR REPLACE PROCEDURE userProc2(
pi_bYear IN USERTBL.BIRTHYEAR%TYPE,
pi_height IN USERTBL.HEIGHT%TYPE
) AS
v_uName VARCHAR(10);
BEGIN
SELECT userName INTO v_uName FROM userTBL
WHERE birthYear = pi_bYear AND height = pi_height;
DBMS_OUTPUT.PUT_LINE(v_uName);
END;
EXECUTE userProc2(1973,176);2. 출력 파라미터를 설정해서 사용
CREATE SEQUENCE userSEQ;
CREATE TABLE testTBL (userId INT, txt NCHAR(10));
CREATE OR REPLACE PROCEDURE userProc3(
pi_txtValue IN NCHAR,
po_outValue OUT NUMBER
) AS
v_count VARCHAR(10);
BEGIN
INSERT INTO testTBL VALUES(userSEQ.NEXTVAL, pi_txtValue);
SELECT MAX(userID) INTO po_outValue FROM testTBL;
END;3. 익명 프로시저에서 변수를 준비해서 사용
DECLARE
outData NUMBER;
BEGIN
userProc3('테스트 값1', outData);
DBMS_OUTPUT.PUT_LINE(outData);
END;4. IF...ELSE 문 사용(OUTPUT.PUT_LINE() 사용)
CREATE OR REPLACE PROCEDURE ifElseProc(
pi_userName IN USERTBL.USERNAME%TYPE
) AS
v_bYear NUMBER; -- 출생년도를 저장할 변수
BEGIN
SELECT birthYear INTO v_bYear FROM userTBL
WHERE userName = pi_userName;
IF v_bYear >= 1980 THEN
DBMS_OUTPUT.PUT_LINE('아직 젊군용..');
ELSE
DBMS_OUTPUT.PUT_LINE('나이가 지긋하시네요');
END IF;
END;
EXECUTE ifElseProc('조용필');5. CASE 문을 사용
CREATE OR REPLACE PROCEDURE caseProc(
pi_userName IN USERTBL.USERNAME%TYPE
) AS
v_bYear NUMBER;
v_mod NUMBER; --나머지값
v_tti NCHAR(3); --띠
BEGIN
SELECT birthYear INTO v_bYear FROM userTBL
WHERE userName = pi_userName;
v_mod := MOD(v_bYear, 12);
CASE
WHEN (v_mod = 0) THEN v_tti := '원숭이';
WHEN (v_mod = 1) THEN v_tti := '닭';
WHEN (v_mod = 2) THEN v_tti := '개';
WHEN (v_mod = 3) THEN v_tti := '돼지';
WHEN (v_mod = 4) THEN v_tti := '쥐';
WHEN (v_mod = 5) THEN v_tti := '소';
WHEN (v_mod = 6) THEN v_tti := '호랑이';
WHEN (v_mod = 7) THEN v_tti := '토끼';
WHEN (v_mod = 8) THEN v_tti := '용';
WHEN (v_mod = 9) THEN v_tti := '뱀';
WHEN (v_mod = 10) THEN v_tti := '말';
else v_tti := '양';
END CASE;
DBMS_OUTPUT.PUT_LINE(pi_userName || '의 띠 ==>' || v_tti);
END;
EXECUTE caseProc('김범수');6. WHILE문을 활용
CREATE TABLE guguTBL (txt VARCHAR(100)); -- 구구단 저장용 테이블;
CREATE OR REPLACE PROCEDURE whileProc AS
v_str VARCHAR(100); -- 출생년도를 저장할 변수
v_i NUMBER; --구구단 앞자리
v_k NUMBER; --구구단 뒷자리
BEGIN
v_i := 2; --2단 부터 처리
WHILE (v_i < 10) LOOP --바같 반복문, 2단 ~ 9단까지.
v_str := ''; -- 각 단의 결과를 저장할 문자열 초기화
v_k := 1; -- 구구단 뒷자리는 항상 1부터 9까지
WHILE(v_k < 10) LOOP
v_str := v_str || ' ' || v_i || 'X' || v_k || '=' || v_i*v_k; --문자열 만들기
v_k := v_k + 1; -- 뒷자리 증가
END LOOP;
v_i := v_i + 1; -- 앞자리 증가
INSERT INTO guguTBL VALUES(v_str); -- 각 단의 결과를 테이블에 입력
END LOOP;
END;
EXECUTE whileProc();
SELECT * FROM guguTBL;스토어드 프로시저의 특징
- Oracle의 성능을 향상 시킬 수 있음
- 유지관리가 간편함
- 예외 처리 및 모듈식 프로그래밍이 가능함
- 네트워크 전송량의 감소
아직까지는 코린이!