[오라클]DML 프로시저, 함수, 반복문, 예외처리,커서

RDB/Oracle

[오라클]DML 프로시저, 함수, 반복문, 예외처리,커서

창문닦이 2019. 1. 21. 17:14

- 우리가 가장 많이 사용하는 프로시저는 DML PROCEDURE(INSERT, UPDATE, DELETE 시키는 프로시저.)

1. INSERT PROCEDURE

1-1 테이블 생성

CREATE TABLE TEST

(A NUMBER(10),

B NUMBER(10),

C DATE DEFAULT SYSDATE);

DESC TEST;

1-2. 데이터 삽입

CREATE OR REPLACE PROCEDURE P_EX

(FIRST IN NUMBER, SECOND IN NUMBER)

BEGIN

INSERT INTO TEST(A,B) VALUES (FIRST, SECOND);

COMMIT;

END;

1-3. 프로시저 실행 후 조회

EXECUTE P_EX(10,20);

SELECT * FROM TEST;

1-4. 프로시저 생성

CREATE OR REPLACE PROCEDURE CUS_IN

(A CUSTOM.USERID%TYPE,

B CUSTOM.USERNAME%TYPE,

C CUSTOM.JUMIN%TYPE,

D CUSTOM.AGE%TYPE,

E CUSTOM.SEX%TYPE,

F CUSTOM.ZIP%TYPE,

G CUSTOM.ADDR1%TYPE,

H CUSTOM.ADDR2%TYPE,

I CUSTOM.ADDR3%TYPE,

J CUSTOM.TEL%TYPE,

K CUSTOM.JOB%TYPE,

L CUSTOM.SCHOL%TYPE,

M CUSTOM.POINT%TYPE)

BEGIN

INSERT INTO CUSTOM(USERID,USERNAME,JUMIN,AGE,SEX,ZIP,ADDR1,ADDR2,ADDR3,TEL,JOB,SCHOL,POINT,REGDATE)

VALUES (A,B,C,D,E,F,G,H,I,J,K,L,M,SYSDATE);

COMMIT;

END;

1-5. INSERT PROCEDURE를 이용한 데이터 입력

EXECUTE CUS_IN('AAAA','KIM','123456-1234567', 20, 1,'123-123','서울','강남구','역삼동','02-123-1234',

'건물주','대졸',100);

1-6. 조회

SELECT * FROM CUSTOM WHERE USERID = 'AAAA';

2. UPDATE PROCEDURE

2-1. 데이터 수정

CREATE OR REPLACE PROCEDURE CUS_UP

(A CUSTOM.USERID%TYPE,

B CUSTOM.USERNAME%TYPE,

C CUSTOM.JUMIN%TYPE,

D CUSTOM.AGE%TYPE,

E CUSTOM.SEX%TYPE,

F CUSTOM.ZIP%TYPE,

G CUSTOM.ADDR1%TYPE,

H CUSTOM.ADDR2%TYPE,

I CUSTOM.ADDR3%TYPE,

J CUSTOM.TEL%TYPE,

K CUSTOM.JOB%TYPE,

L CUSTOM.SCHOL%TYPE,

M CUSTOM.POINT%TYPE)

BEGIN

UPDATE CUSTOM

SET USERNAME = B,JUMIN = C,AGE = D,SEX = E,ZIP = F,ADDR1 = G,ADDR2 = H,ADDR3 = I,TEL = J,JOB = K ,SCHOL = L,POINT = M

WHERE USERID = A;

COMMIT;

END;

2-2. 수정 후 조회

EXECUTE CUS_UP('AAAA','LEE','123456-1234567', 20, 1, '123-1231', '서울1' ,'강남구1' ,'역삼동1' ,'02-123-12341', '건물주1','대졸1',101);

3. DELETE PROCEDURE

3-1. 삭제 프로시저

CREATE OR REPLACE PROCEDURE CUS_DEL

(A CUSTOM.USERID%TYPE)

BEGIN

DELETE CUSTOM WHERE USERID = A ;

COMMIT;

END;

3-2. 삭제 실행

EXECUTE CUS_DEL('AAAA');

[함수 이용]

1. 부피를 구하는 함수

CREATE OR REPLACE FUNCTION F_CUBVOL

(GILI IN NUMBER, POK IN NUMBER, NOPI IN NUMBER)

RETURN NUMBER

BUPI NUMBER;

BEGIN

BUPI:=GILI*POK*NOPI;

RETURN BUPI;

END F_CUBVOL;

실행

SELECT F_CUBVOL(4,7,8) BUPI FROM DUAL;

2. 입력받은 날짜의 말일 구하기

CREATE OR REPLACE FUNCTION F_MONLAST

(V_DATE DATE)

RETURN DATE

LASTDATE DATE;

BEGIN

LASTDATE:=(ADD_MONTHS(V_DATE,1)-TO_CHAR(V_DATE,'DD'));

RETURN LASTDATE;

END;

실행

SELECT F_MONLAST('2019-01-10') FROM DUAL;

SELECT REGDATE,F_MONLAST(REGDATE) FROM CUSTOM;

3. 이름을 입력받아 성을 뺀 이름만 반환

CREATE OR REPLACE FUNCTION F_NAME

(V_NAME CUSTOM.USERNAME%TYPE)

RETURN VARCHAR2

LASTNAME VARCHAR2(10);

BEGIN

LASTNAME:=SUBSTR(V_NAME,2);

RETURN LASTNAME;

END;

실행

SELECT USERNAME,F_NAME(USERNAME) 이름 FROM CUSTOM;

4. 입사일 입력받아 사원명,입사일,근무기간(6년 2개월)을 출력

CREATE OR REPLACE FUNCTION F_IPSAIL

(V_DATE CUSTOM.REGDATE%TYPE)

RETURN VARCHAR2

COMDATE VARCHAR2(10);

BEGIN

COMDATE:=FLOOR(MONTHS_BETWEEN(SYSDATE,V_DATE)/12)||'년'||

FLOOR(MOD(MONTHS_BETWEEN(SYSDATE,V_DATE),12))||'개월';

RETURN COMDATE;

END F_IPSAIL;

실행

SELECT USERNAME 사원명,TO_CHAR(REGDATE,'YYYY-MM-DD') 입사일,F_IPSAIL(REGDATE) 연차 FROM CUSTOM;

입력한 기간을 년수, 개월로 변환

SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,'2002-06-01')/12)||'년'||

FLOOR(MOD(MONTHS_BETWEEN(SYSDATE,'2002-06-01'),12))||'개월' "2002-06-01부터 오늘까지 개월수"

FROM DUAL;

5.주민번호를 입력했을때 성별을 변환(PL-SQL IF문 사용)

CREATE OR REPLACE FUNCTION F_GENDER

(V_JUMIN IN VARCHAR2)

RETURN VARCHAR2

GENDER VARCHAR2(2);

BEGIN

GENDER:= SUBSTR(V_JUMIN,8,1);

IF GENDER IN('1','3') THEN

GENDER:='남';

ELSE

GENDER:='여';

END IF;

RETURN GENDER;

END;

결과 조회

SELECT USERNAME,JUMIN,F_GENDER(JUMIN) GENDER FROM CUSTOM;

6. 날짜에 년,월,일을 더해서 계약 만료일 구하기

CREATE OR REPLACE FUNCTION F_CONTRACT

(V_DATE IN DATE, Y IN NUMBER, M IN NUMBER, D IN NUMBER)

RETURN DATE

NALZA DATE;

BEGIN

NALZA:=ADD_MONTHS(V_DATE,Y*12);

NALZA:=ADD_MONTHS(NALZA,M);

NALZA:=NALZA+D;

RETURN NALZA;

END;

결과 조회

SELECT USERNAME, REGDATE, F_CONTRACT(REGDATE,1,11,29) CONTRACT FROM CUSTOM;

7. 급여에 따른 등급 매기기(IF문 사용)

CREATE OR REPLACE FUNCTION F_PAYGRADE

(V_PAY IN NUMBER)

RETURN VARCHAR2

RESULT VARCHAR2(20);

BEGIN

IF V_PAY>2500000 THEN

RESULT :='A';

ELSIF V_PAY>2000000 THEN

RESULT :='B';

ELSIF V_PAY>1500000 THEN

RESULT :='C';

ELSIF V_PAY>000000 THEN

RESULT :='D';

ELSE

RESULT :='E';

END IF;

RETURN RESULT;

END;

결과 조회

SELECT USERID,POSIT,PAY,F_PAYGRADE(PAY)||'등급' "PAYGRADE" FROM COMPANY

WHERE USERID ='XA9776';

- 반복문

- LOOP문

CREATE TABLE LOOP1

(NO NUMBER,

NAME VARCHAR2(6) DEFAULT '홍길동');

--LOOP문. WHEN EXIT문이 사용되었을 때 빠져나가는 조건을 할 수 있다.(자바에서 DO~WHILE과 동일)

DECLARE

V_COUNT NUMBER(2):=1; --변수선언. 초기값 설정

BEGIN

LOOP --WHILE문과 같음

INSERT INTO LOOP1(NO) VALUES(V_COUNT); --데이터 삽입

V_COUNT:=V_COUNT+1;

EXIT WHEN V_COUNT>10;

END LOOP;

DBMS_OUTPUT.PUT_LINE('데이터 입력 완료!');

END;

실행

SELECT * FROM LOOP1;

- FOR문

CREATE OR REPLACE PROCEDURE P_FOR

BEGIN

FOR I IN 21..30 LOOP --21부터 30까지 반복

INSERT INTO LOOP1(NO) VALUES(I);

END LOOP;

END;

실행

EXECUTE P_FOR;

- WHILE문

CREATE OR REPLACE PROCEDURE BANBOK2

(V_LOWER NUMBER, V_UPPER NUMBER)

V_COUNT NUMBER(10):=0;

V_OUTPUT NUMBER(10):=0;

BEGIN

V_OUTPUT := V_LOWER; --V_OUTPUT에 최소값 대입

WHILE V_OUTPUT < V_UPPER LOOP --UPPER보다 작을때까지 반복

V_COUNT := V_COUNT +1; --몇번 회전하는지 카운트

V_OUTPUT := V_OUTPUT +1; --V_OUTPUT을 1씩 증가

END LOOP;

DBMS_OUTPUT.PUT_LINE('최종값은 '||TO_CHAR(V_OUTPUT)||'이고 총 반복횟수는 ' ||TO_CHAR(V_COUNT)||'이다.');

END;

실행

EXECUTE BANBOK2(1,10); --1부터 10까지 반복

- 사용자가 입력하는 수의 작은값에서 큰값까지의 합계

CREATE OR REPLACE PROCEDURE P_CAL

(V_START NUMBER, V_END NUMBER)

CNT NUMBER:= V_START; --작은값부터 합계를 더하기 시작하므로 초기값 설정해야함

TOT NUMBER:= 0;

BEGIN

--LOOP

-- LOOP

-- TOT := TOT+CNT;

-- CNT := CNT+1;

-- EXIT WHEN CNT>V_END;

-- END LOOP;

--FOR

-- FOR I IN V_START..V_END LOOP

-- TOT := TOT+I;

-- END LOOP;

--WHILE

WHILE CNT <= V_END LOOP

TOT := TOT +CNT;

CNT := CNT +1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_START)||'부터 '||TO_CHAR(V_END)||'까지의 합은 '||TO_CHAR(TOT)||'이다');

END;

실행

EXECUTE P_CAL(5,100); --5부터 100까지 반복

- 예외처리

CREATE OR REPLACE PROCEDURE EXE_TEST

SW_REC 사원%ROWTYPE; --ROWTYPE:하나의 레코드를 저장가능

BEGIN

SELECT * INTO SW_REC FROM 사원 WHERE 사원번호=2009;

DBMS_OUTPUT.PUT_LINE('데이터 검색 성공!!'); --SW_REC에는 8개의 레코드를 담을 수 없음. 하나만 저장 가능

EXCEPTION --기본적인 에러메세지를 아래와 같이 바꿀 수 있음

WHEN TOO_MANY_ROWS THEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('데이터가 너무 많습니다!!');

WHEN NO_DATA_FOUND THEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('데이터가 없습니다!!');

WHEN OTHERS THEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('기타 에러입니다!!');

END;

실행 (사원번호가 2009인 데이터는 없음)

EXECUTE EXE_TEST;

- 데이터 중복 예외처리

테이블 생성

CREATE TABLE EMP2

(EMPNO NUMBER(4) PRIMARY KEY,

ENAME VARCHAR2(10),

DEPTNO NUMBER(2));

데이터 삽입

INSERT INTO EMP2 VALUES (1001,'배수지',10);

INSERT INTO EMP2 VALUES (1002,'김수지',20);

테이블 조회

SELECT * FROM EMP2;

프로시저 생성

CREATE OR REPLACE PROCEDURE IN_EMP2

(EMPNO NUMBER, DEPTNO NUMBER)

NAME VARCHAR2(10) := '미지정';

BEGIN

INSERT INTO EMP2 VALUES(EMPNO,NAME,DEPTNO);

COMMIT;

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

DBMS_OUTPUT.PUT_LINE('데이터 중복!');

END;

프로시저 실행

EXECUTE IN_EMP2(1003,20);--O. 두번 실행할 경우 '데이터 중복'메세지 뜸

EXECUTE IN_EMP2(1003,30);--X.

테이블 조회

SELECT * FROM EMP2;

사용자 정의 에러

CREATE OR REPLACE PROCEDURE PAY_VALIDATE

(V_SANO 사원.사원번호%TYPE)

NOT_ENOUGH_PAY EXCEPTION;

SW_REC 사원%ROWTYPE;

BEGIN

SELECT * INTO SW_REC FROM 사원 WHERE 사원번호 =V_SANO;

IF SW_REC.급여 >= 3000 THEN

DBMS_OUTPUT.PUT_LINE('급여가 3000 많음!');

ELSIF SW_REC.급여 < 3000 THEN

RAISE NOT_ENOUGH_PAY; --급여가 3000보다 작을경우 에러발생

ELSE

DBMS_OUTPUT.PUT_LINE('급여가 적당함!');

END IF;

EXCEPTION

WHEN NOT_ENOUGH_PAY THEN

RAISE_APPLICATION_ERROR(-20001,'급여가 충분하지 못함!!'); -- ORA-20001은 오라클의 에러발생번호를 설정한 것

END;

실행

EXECUTE PAY_VALIDATE(2001); --O

EXECUTE PAY_VALIDATE(2002);

EXECUTE PAY_VALIDATE(2003);

EXECUTE PAY_VALIDATE(2008); --ERROR

- CURSOR

CREATE OR REPLACE PROCEDURE P_DEPTNO

(DEPTNO 사원.부서번호%TYPE)

CURSOR CUR_DEPTNO

SELECT 부서번호,사원명,직급,급여 FROM 사원

WHERE 부서번호 = DEPTNO;

V_DEPTNO 사원.부서번호%TYPE;

V_SANAME 사원.사원명%TYPE;

V_JIK 사원.직급%TYPE;

V_SAL 사원.급여%TYPE;

BEGIN

OPEN CUR_DEPTNO;

DBMS_OUTPUT.PUT_LINE('부서번호 사원명 직급 급여');

DBMS_OUTPUT.PUT_LINE('-------------------------------');

LOOP

FETCH CUR_DEPTNO INTO V_DEPTNO,V_SANAME,V_JIK,V_SAL;

EXIT WHEN CUR_DEPTNO%NOTFOUND; --NOTFOUND는 시스템변수. 더이상 해당변수가 없을때 반복문 종료

DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_DEPTNO)||' '||V_SANAME||' '||V_JIK||' '||TO_CHAR(V_SAL));

END LOOP;

DBMS_OUTPUT.PUT_LINE('-------------------------------');

DBMS_OUTPUT.PUT_LINE('전체 데이터 갯수 : ' || TO_CHAR(CUR_DEPTNO%ROWCOUNT));

--ROWCOUNT는 시스템변수. 행의 수.

CLOSE CUR_DEPTNO; --커서가 CLOSE되면 더이상 가져다 사용할 수 없음

END;

실행

EXECUTE P_DEPTNO(10);

명시적 CURSOR의 속성은 4가지 존재.

%ISOPEN : 커서가 열리면 true

%NOTFOUND : 가장 최근의 fetch가 행을 return 하지 않으면 true

%FOUND : 가장 최근의 fetch가 행을 return하면 true

%ROWCOUNT : 지금까지 return된 행의 수

FOR문을 이용한 커서 생성.

-CUR_DEPTNO가 실질적인 커서 이름. FOR문을 사용하여 커서를 할땐 OPEN,FETCH,CLOSE 생략가능. FOR문자체가 세단계를 포함함

CREATE OR REPLACE PROCEDURE P_DEPTNO1

(DEPTNO 사원.부서번호%TYPE)

CURSOR CUR_DEPTNO

SELECT 부서번호,사원명,직급,급여 FROM 사원

WHERE 부서번호 = DEPTNO;

V_CNT NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE('부서번호 사원명 직급 급여');

DBMS_OUTPUT.PUT_LINE('-------------------------------');

FOR CURDEPT IN CUR_DEPTNO LOOP

DBMS_OUTPUT.PUT_LINE(TO_CHAR(CURDEPT.부서번호)||' '||CURDEPT.사원명||' '||CURDEPT.직급||' '||TO_CHAR(CURDEPT.급여));

V_CNT:=CUR_DEPTNO%ROWCOUNT;

END LOOP;

DBMS_OUTPUT.PUT_LINE('-------------------------------');

DBMS_OUTPUT.PUT_LINE('전체 데이터 갯수 : ' || TO_CHAR(V_CNT)); --ROWCOUNT는 시스템변수. 행의 수.

END;

실행

EXECUTE P_DEPTNO1(20);

[문제풀이1]

사원번호(USERID)를 입력시 아이디, 이름 , 구입한 제품명, 구입횟수를 출력

CREATE OR REPLACE PROCEDURE P_JEPUM

(ID SALES.USERID%TYPE)

CURSOR CUR_ID

SELECT A.USERID,USERNAME,PRICE,PRODUCTNAME FROM SALES A, CUSTOM B

WHERE A.USERID = B.USERID AND A.USERID = ID;

V_CNT NUMBER :=0;

V_TOT NUMBER :=0;

V_ID VARCHAR2(10);

V_NAME VARCHAR2(10);

BEGIN

FOR CURID IN CUR_ID LOOP

V_TOT := V_TOT+CURID.PRICE;

V_CNT := CUR_ID%ROWCOUNT;

V_ID := CURID.USERID;

V_NAME := CURID.USERNAME;

DBMS_OUTPUT.PUT_LINE('구매상품 : '||TO_CHAR(CURID.PRODUCTNAME));

END LOOP;

DBMS_OUTPUT.PUT_LINE('아이디 : '||V_ID||' 이름 : '||V_NAME||'님의 구매내역 입니다');

DBMS_OUTPUT.PUT_LINE('총'||TO_CHAR(V_CNT)||'회, 주문액'||TO_CHAR(V_TOT)||'원을 구매하셨습니다.');

END;

실행

EXECUTE P_JEPUM('5s1800');

[문제풀이2]

사원번호를 입력시 사수번호, 사수명, 사원번호, 사원명을 출력

CREATE OR REPLACE PROCEDURE P_SANO

(SANO 사원.사원번호%TYPE)

CURSOR CUR_SANO

SELECT A.사수번호, B.사원명 사수명, A.사원번호, A.사원명 FROM 사원 A, 사원 B

WHERE A.사수번호 = B.사원번호(+) AND A.사원번호 = SANO;

V_CNT NUMBER;

BEGIN

FOR CURSANO IN CUR_SANO LOOP

V_CNT := CUR_SANO%ROWCOUNT;

DBMS_OUTPUT.PUT_LINE('사수번호 : '||TO_CHAR(CURSANO.사수번호));

DBMS_OUTPUT.PUT_LINE('사수명 : '||TO_CHAR(CURSANO.사수명));

DBMS_OUTPUT.PUT_LINE('사원번호 : '||TO_CHAR(CURSANO.사원번호));

DBMS_OUTPUT.PUT_LINE('사원명 : '||TO_CHAR(CURSANO.사원명));

END LOOP;

END;

실행

EXECUTE P_SANO('2002');

[문제풀이2-1]

사원번호를 입력시 사수번호, 사수명, 사원번호, 사원명을 출력(부하직원을 출력하는 형식으로 바꿈)

SELECT A.사수번호, B.사원명, A.사원번호, A.사원명 FROM 사원 A, 사원 B

WHERE A.사수번호 = B.사원번호(+) ORDER BY A.사원번호;

CREATE OR REPLACE PROCEDURE P_SANO

(SANO 사원.사원번호%TYPE)

CURSOR CUR_SANO

SELECT A.사수번호, B.사원명 사수명, A.사원번호, A.사원명 FROM 사원 A, 사원 B

WHERE A.사수번호 = B.사원번호(+) AND A.사수번호 = SANO;

V_CNT NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE('사수번호 사수명 사원번호 사원명');

DBMS_OUTPUT.PUT_LINE('--------------------------------------');

FOR CURSANO IN CUR_SANO LOOP

V_CNT := CUR_SANO%ROWCOUNT;

DBMS_OUTPUT.PUT_LINE(' '||TO_CHAR(CURSANO.사수번호)||' '||TO_CHAR(CURSANO.사수명)||' '||

TO_CHAR(CURSANO.사원번호)||' '||TO_CHAR(CURSANO.사원명));

END LOOP;

END;

실행

EXECUTE P_SANO('2004');

저작자표시 비영리 변경금지

'RDB > Oracle' 카테고리의 다른 글

[오라클]오라클 아키텍쳐(2) (1)	2019.01.22
[오라클]패키지, 트리거(1) (0)	2019.01.22
[오라클]인덱스, 시퀀스, PL/SQL-프로시저의 개념, 스크립트 (0)	2019.01.18
[오라클]제약조건, DATA DICTIONARY, VIEW, SYNONYM (0)	2019.01.16
[오라클]DCL, OBJECT 권한, ROLE (0)	2019.01.16

현재글[오라클]DML 프로시저, 함수, 반복문, 예외처리,커서

창문을 열심히 닦아요