Oracle 10일차(1)-DML PROCEDURE, 함수,반복문,예외처리

STUDY/ORACLE

Oracle 10일차(1)-DML PROCEDURE, 함수,반복문,예외처리

Anne of Green Galbes 2019. 1. 21. 17:07

DML PROCEDURE : 가장 많이 사용하는 PROCEDURE

▶TEST 테이블 생성

CREATE TABLE TEST
(A NUMBER(10),
B NUMBER(10),
C DATE DEFAULT SYSDATE);
DESC TEST;

▶DML PROCEDURE : INSERT

CREATE OR REPLACE PROCEDURE P_EX
(FIRST IN NUMBER, SECOND IN NUMBER)
IS

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

EXECUTE P_EX(10,20);

SELECT * FROM TEST;

결과

1. CUSTOM테이블에 INSERT 시키는 프로시져

CREATE OR REPLACE PROCEDURE CUS_IN
(USERID IN VARCHAR2,
USERNAME IN VARCHAR2,
JUMIN IN VARCHAR2,
AGE IN NUMBER,
SEX IN VARCHAR2,
ZIP IN VARCHAR2,
ADDR1 IN VARCHAR2,
ADDR2 IN VARCHAR2,
ADDR3 IN VARCHAR2,
TEL IN VARCHAR2,
JOB IN VARCHAR2,
SCHOL IN VARCHAR2,
POINT IN NUMBER)
IS

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

END;

EXECUTE CUS_IN('AAA','AAA','000000',20,'W','04000','서울시','마포구','역삼동','010-0000-1111','백수','졸업',200);

결과

2. CUSTOM테이블에 UPDATE 시키는 프로시져

CREATE OR REPLACE PROCEDURE CUS_UP
(CUS_ID IN VARCHAR2,
CUS_NAME IN VARCHAR2,
CUS_JUMIN IN VARCHAR2,
CUS_AGE IN NUMBER,
CUS_SEX IN VARCHAR2,
CUS_ZIP IN VARCHAR2,
CUS_ADDR1 IN VARCHAR2,
CUS_ADDR2 IN VARCHAR2,
CUS_ADDR3 IN VARCHAR2,
CUS_TEL IN VARCHAR2,
CUS_JOB IN VARCHAR2,
CUS_SCHOL IN VARCHAR2,
CUS_POINT IN NUMBER)
IS

BEGIN
UPDATE CUSTOM SET USERNAME=CUS_NAME,JUMIN=CUS_JUMIN,AGE=CUS_AGE,SEX=CUS_SEX,
ZIP=CUS_ZIP,ADDR1=CUS_ADDR1,ADDR2=CUS_ADDR2,ADDR3=CUS_ADDR3,TEL=CUS_TEL,JOB=CUS_JOB,SCHOL=CUS_SCHOL,POINT=CUS_POINT
WHERE USERID=CUS_ID;
COMMIT;

END;

EXECUTE CUS_UP ('AAA','KIM','000000',22,'W','04200','서울시','강남구','역삼동','010-0000-1111','백수','졸업',200);

결과

3. CUSTOM테이블에 DELETE 시키는 프로시져

CREATE OR REPLACE PROCEDURE CUS_DE
(ID IN VARCHAR2)
IS

BEGIN
DELETE CUSTOM WHERE USERID=ID;
COMMIT;

END;

EXECUTE CUS_DE('AAA');

함수

1. 부피를 구하는 함수

CREATE OR REPLACE FUNCTION F_CUBVOL
(GILI IN NUMBER, POK IN NUMBER, NOPI IN NUMBER)
RETURN NUMBER
IS
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
IS
LASTDATE DATE;

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

END;

SELECT F_MONLAST(SYSDATE) FROM DUAL;

결과

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

CREATE OR REPLACE FUNCTION F_NAME
(NAME IN VARCHAR2)
RETURN VARCHAR2
IS
RE_NAME VARCHAR2(20);

BEGIN
RE_NAME := SUBSTR(NAME,-2);
RETURN RE_NAME;

END;

SELECT F_NAME('배수지') NAME FROM DUAL;
SELECT USERNAME, F_NAME(USERNAME) NAME FROM CUSTOM;

결과

4. 입사일을 입력받아 사원명, 입사일, 근무기간을 출력

SELECT * FROM CUSTOM;
CREATE OR REPLACE FUNCTION F_IPSAIL
(IPSAIL IN DATE)
RETURN VARCHAR2
IS
RE_IPSAIL VARCHAR2(20);

BEGIN
RE_IPSAIL := FLOOR(MONTHS_BETWEEN(SYSDATE,IPSAIL)/12) ||'년 '||
FLOOR(MOD(MONTHS_BETWEEN(SYSDATE,IPSAIL),12)) ||'개월';
RETURN RE_IPSAIL;

END;

SELECT USERNAME, REGDATE, F_IPSAIL(REGDATE) 근무기간 FROM CUSTOM;

결과

5. 주민번호를 입력했을 때 성별을 반환

CREATE OR REPLACE FUNCTION F_GENDER
(V_JUMIN IN VARCHAR2)
RETURN VARCHAR2
IS
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;

(＋)IF문

▶조건이 1개

IF 조건 THEN 조건처리;

END IF

▶조건이 2개

IF 조건 THEN 조건처리1;

ELSE 조건처리2;

END IF

▶조건이 n개

IF 조건1 THEN 조건처리1;

ELSIF 조건2 THEN 조건처리2;

.....

ELSE 조건처리n

END IF;

결과

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

CREATE OR REPLACE FUNCTION F_GAEYAK
(V_DATE IN DATE, Y IN NUMBER, M IN NUMBER, D IN NUMBER)
RETURN DATE
IS
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_GAEYAK(REGDATE,1,11,29) GAEYAK FROM CUSTOM;

결과

반복문

1.LOOP문

LOOP

처리문;

EXIT WHEN 조건;

END LOOP;

CREATE TABLE LOOP1
(NO NUMBER,
NAME VARCHAR2(6) DEFAULT '홍길동');
DECLARE
V_COUNT NUMBER(2):=1;

BEGIN
LOOP
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;

결과

2.FOR문

FOR 인덱스 IN 초기값..최종값

LOOP

처리문;

END LOOP;

CREATE OR REPLACE PROCEDURE P_FOR
IS

BEGIN
FOR I IN 21..30 LOOP
INSERT INTO LOOP1(NO) VALUES (I);
END LOOP;

END;

EXECUTE P_FOR;
SELECT * FROM LOOP1;

결과

CREATE OR REPLACE PROCEDURE BANBOK1
(V_LOWER NUMBER, V_UPPER NUMBER)
IS
V_COUNT NUMBER(10) :=0;
V_OUTPUT NUMBER(10) :=0;

BEGIN
FOR I IN V_LOWER..V_UPPER LOOP
V_COUNT := V_COUNT+1;
V_OUTPUT := I;
END LOOP;
DBMS_OUTPUT.PUT_LINE('최종값은 ' || TO_CHAR(V_OUTPUT) || '이고, 반복횟수는 ' || TO_CHAR(V_COUNT) ||'번 입니다.');

END;
EXECUTE BANBOK1(1,10);

결과 : 최종값은 10이고, 반복횟수는 10번 입니다.

3. WHILE문

WHILE 조건 LOOP

처리문;

END LOOP;

CREATE OR REPLACE PROCEDURE BANBOK2
(V_LOWER NUMBER, V_UPPER NUMBER)
IS
V_COUNT NUMBER(10) :=0;
V_OUTPUT NUMBER(10) :=0;

BEGIN
V_OUTPUT := V_LOWER;

WHILE V_OUTPUT < V_UPPER LOOP
V_COUNT := V_COUNT+1;
V_OUTPUT := V_OUTPUT +1;
END LOOP;

DBMS_OUTPUT.PUT_LINE('최종값은 ' || TO_CHAR(V_OUTPUT) || '이고,반복횟수는 ' || TO_CHAR(V_COUNT) ||'번 입니다.');
END;
EXECUTE BANBOK2(1,10);

결과 : 최종값은 10이고,반복횟수는 9번 입니다.

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

▶LOOP

CREATE OR REPLACE PROCEDURE P_CAL
(V_START NUMBER, V_END NUMBER)
IS
CNT NUMBER := V_START;
TOT NUMBER := 0;

BEGIN
LOOP
TOT := TOT+CNT;
CNT := CNT+1;
EXIT WHEN CNT>V_END;
END LOOP;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_START) || '에서 ' || TO_CHAR(V_END) ||'까지의 합은 '|| TO_CHAR(TOT) || '입니다.');

END;

EXECUTE P_CAL(1,100);

결과 : 1에서 100까지의 합은 5050입니다.

▶FOR

CREATE OR REPLACE PROCEDURE P_CAL2
(V_START NUMBER, V_END NUMBER)
IS
CNT NUMBER := V_START;
TOT NUMBER := 0;

BEGIN
FOR I IN V_START..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_CAL2(1,100);

결과 : 1에서 100까지의 합은 5050입니다.

▶WHILE

CREATE OR REPLACE PROCEDURE P_CAL3
(V_START NUMBER, V_END NUMBER)
IS
CNT NUMBER := V_START;
TOT NUMBER := 0;

BEGIN
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_CAL3(1,100);

결과 : 1에서 100까지의 합은 5050입니다.

예외처리

1.예외처리

CREATE OR REPLACE PROCEDURE EXE_TEST
IS
SW_REC 사원%ROWTYPE;
BEGIN
SELECT * INTO SW_REC FROM 사원 WHERE 사원번호=2009;
DBMS_OUTPUT.PUT_LINE('데이터 검색 성공!!');

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;

EXECUTE EXE_TEST;

결과 : 데이터가 없습니다.

2.예외처리(2)

테이블 생성

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);

예외처리

CREATE OR REPLACE PROCEDURE IN_EMP2
(EMPNO NUMBER,DEPTNO NUMBER)
IS
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);

결과 : 데이터 중복!

3. 사용자 정의 에러

CREATE OR REPLACE PROCEDURE PAY_VALIDATE
(V_SANO 사원.사원번호%TYPE)
IS
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.급여 <= 2000 THEN
RAISE NOT_ENOUGH_PAY;
ELSE
DBMS_OUTPUT.PUT_LINE('급여가 적당함');
END IF;

EXCEPTION
WHEN NOT_ENOUGH_PAY THEN
RAISE_APPLICATION_ERROR(-20001,'급여가 충분하지 못함'); --ORACLE 에러메시지 만듦. ara-20001
END;

EXECUTE PAY_VALIDATE(2001);    결과 : 급여가 3000 많음
EXECUTE PAY_VALIDATE(2002);    결과 : 급여가 3000 많음
EXECUTE PAY_VALIDATE(2003);    결과 : 급여가 적당함
EXECUTE PAY_VALIDATE(2006);     결과 : 에러 ORA-20001 : 급여가 충분하지 못함

CURSOR

1. CURSOR 사용 방법

○선언 → OPEN → FETCH → CLOSE

CURSOR CURSOR_NAME
IS
SELECT 부서번호, 사원명, 직급, 급여 FROM 사원
WHERE 부서번호=DEPTNO;

OPEN CURSOR_NAME;

LOOP
FETCH CURSOR_NAME INTO COLUMN;
EXIT WHEN CURSOR_NAME%NOTFOUND;
END LOOP;

CLOSE CURSOR_NAME;

○ NOTFOUND : 시스템변수. CURSOR에 데이터가 없을 때

○ ROWCOUNT : 시스템변수. 행의 수

○ 시스템 변수 : CURSOR가 닫히면 사용 불가

2. CURSOR 사용 예제

CREATE OR REPLACE PROCEDURE P_DEPTNO
(DEPTNO 사원.부서번호%TYPE)
IS
--CURSOR 만듦
CURSOR CUR_DEPTNO
IS
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;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_DEPTNO) ||' '|| TO_CHAR(V_SANAME) ||' '|| TO_CHAR(V_JIK) ||' '||TO_CHAR(V_SAL));
END LOOP;

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

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

CLOSE CUR_DEPTNO;

END;

EXECUTE P_DEPTNO(20);

결과

부서번호 사원명 직급 급여

----------------------------------------------

20 성유리 사원 2500

20 한지혜 사원 2000

----------------------------------------------

전체데이터 개수 : 2

3. FOR문을 사용한 CURSOR 사용 예제

○OPEN,CLOSE,FETCH 필요 X

CREATE OR REPLACE PROCEDURE P_DEPTNO1
(DEPTNO 사원.부서번호%TYPE)
IS
CURSOR CUR_DEPTNO
IS
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.부서번호) ||' '||
TO_CHAR(CURDEPT.사원명) ||' '|| TO_CHAR(CURDEPT.직급) ||' '||TO_CHAR(CURDEPT.급여));

V_CNT:=CUR_DEPTNO%ROWCOUNT;
END LOOP;

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

DBMS_OUTPUT.PUT_LINE('전체데이터 개수:'||TO_CHAR(V_CNT));
END;

EXECUTE P_DEPTNO(30);

결과 
부서번호     사원명     직급    급여
----------------------------------------------
30                옥주현    과장    5000
30                박솔미    대리    3000
----------------------------------------------
전체데이터 개수 : 2