오라클 DB 설계 및 활용 11일째 2번 내용

*** 이번엔 커서에 대해서 알아 볼까요

*** 커서 : 사용자가 실행한 sql 문의 단위를 말한다.
한개의 행에 대한 수행은 임시적인 커서로서 커서를 사용하지 않는다.
하지만 pl/sql에서는 여러 개의 행 처리를 위해 임시적인 커서를 이용한다.
형식) CURSOR 커서명 : 커서 선언
OPEN 커서명 : 커서 열기
FETCH 커서명 INTO 변수명...: 커서에 조회환 결과를 변수에 기억
CLOSE 커서명 : 커서 닫기

실습1)

CREATE TABLE SS AS SELECT SAWON_NO, SAWON_NAME, SAWON_PAY FROM SAWON WHERE 1=0;

DECLARE
NO NUMBER;
NAME VARCHAR2(10);
PAY NUMBER;
CURSOR C1 IS
SELECT SAWON_NO, SAWON_NAME, SAWON_PAY FROM SAWON WHERE SAWON_JIK ='사원';
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO NO, NAME, PAY;
EXIT WHEN C1%NOTFOUND;
INSERT INTO SS VALUES(NO, NAME, PAY);
END LOOP;
CLOSE C1;
END;

SELECT * FROM SS
COMMIT

실습2) OPEN - FETCH - CLOSE 없이 커서 처리
DECLARE
SAW SAWON%ROWTYPE;
CURSOR C1 IS
SELECT SAWON_NO,SAWON_NAME, SAWON_PAY
FROM SAWON WHERE SAWON_JIK = '과장';
BEGIN
FOR SAW IN C1 LOOP
EXIT WHEN C1%NOTFOUND;
INSERT INTO SS VALUES(SAW.SAWON_NO, SAW.SAWON_NAME, SAW.SAWON_PAY);
END LOOP;
END;

실습3) 커서를 이용한 성적 테이블 작성
DROP TABLE EXAM1;
CREATE TABLE EXAM1(BUN NUMBER PRIMARY KEY, IRUM VARCHAR(10) NOT NULL, KOR NUMBER,
ENG NUMBER, MAT NUMBER);

INSERT INTO EXAM1 VALUES(1,'A', 70, 80, 90);
INSERT INTO EXAM1 VALUES(2,'B', 100, 100, 100);
INSERT INTO EXAM1 VALUES(3,'C', 80, 90, 70);
INSERT INTO EXAM1 VALUES(4,'D', 65, 55, 50);
INSERT INTO EXAM1 VALUES(5,'E', 72, 88, 95);
INSERT INTO EXAM1 VALUES(6,'F', 82, 85, 70);
INSERT INTO EXAM1 VALUES(7,'G', 90, 90, 90);
INSERT INTO EXAM1 VALUES(8,'H', 40, 50, 50);
INSERT INTO EXAM1 VALUES(9,'I', 60, 65, 70);
INSERT INTO EXAM1 VALUES(10,'J', 80, 80, 85);

SELECT * FROM EXAM1;

CREATE TABLE EXAM2(BUN NUMBER PRIMARY KEY, IRUM VARCHAR(10) NOT NULL,
KOR NUMBER, ENG NUMBER, MAT NUMBER, TOT NUMBER, AVE NUMBER, RANK NUMBER);

-- 동점자 처리 X
DECLARE
EBUN NUMBER; EIRUM VARCHAR2(10); EKOR NUMBER;
EENG NUMBER; EMAT NUMBER;
EAVE NUMBER; ERANK NUMBER := 1;
CURSOR CUR_EX IS
SELECT BUN, IRUM,KOR,ENG,MAT FROM EXAM1
ORDER BY KOR+ENG+MAT DESC;
BEGIN
OPEN CUR_EX;
LOOP
FETCH CUR_EX INTO EBUN, EIRUM, EKOR, EENG, EMAT;
EXIT WHEN CUR_EX%NOTFOUND;
INSERT INTO EXAM2 VALUES(EBUN, EIRUM, EKOR, EENG, EMAT, EKOR+EENG+EMAT,
((EKOR+EENG+EMAT)/3), ERANK);
ERANK := ERANK + 1;
END LOOP;
CLOSE CUR_EX;
END;

COMMIT

SELECT * FROM EXAM2;

IF(V_A.BUSER_NUM=40) THEN
V_STR := CONCAT(V_A.SAWON_NAME, ' 40');
END IF;

-- 동점자 처리를 함 O

제가 한 건 조금 벗어났네요. 1, 2, 2, 3, 4, 5, 5, 5 이런시긍로
저는 만들었는데.. 선생님이 요구하신건 1, 2, 2, 4, 5, 6, 6, 6 이네요.
도중에 함수를 하나 더 선언 해 주면 됩니다. 카운트는 계속 돌리고 랭크가
같은 경우는 그 전의 수를 아니라면 현재 카운트를 나오게 한다.

DROP TABLE EXAM3;

CREATE TABLE EXAM3(BUN NUMBER PRIMARY KEY, IRUM VARCHAR(10) NOT NULL,
KOR NUMBER, ENG NUMBER, MAT NUMBER, TOT NUMBER, AVE NUMBER, RANK NUMBER);

DECLARE
EBUN NUMBER; EIRUM VARCHAR2(10); EKOR NUMBER;
EENG NUMBER; EMAT NUMBER; ECOU NUMBER := 1;
EAVE NUMBER; EETT NUMBER := 0;
CURSOR CUR_EX IS
SELECT BUN, IRUM,KOR,ENG,MAT FROM EXAM1
ORDER BY KOR+ENG+MAT DESC;
BEGIN
OPEN CUR_EX;
LOOP
FETCH CUR_EX INTO EBUN, EIRUM, EKOR, EENG, EMAT;
EXIT WHEN CUR_EX%NOTFOUND;
IF((EKOR+EENG+EMAT)=EETT) THEN
INSERT INTO EXAM3 VALUES(EBUN, EIRUM, EKOR, EENG, EMAT, EKOR+EENG+EMAT,
((EKOR+EENG+EMAT)/3), ECOU-1);
EETT := EKOR+EENG+EMAT;
ELSE
INSERT INTO EXAM3 VALUES(EBUN, EIRUM, EKOR, EENG, EMAT, EKOR+EENG+EMAT,
((EKOR+EENG+EMAT)/3), ECOU);
ECOU := ECOU + 1;
EETT := EKOR+EENG+EMAT;
END IF;
END LOOP;
CLOSE CUR_EX;
END;

COMMIT;

** PROCEDURE 연습

형식) CREATE [OR REPLACE] PROCEDURE 프로시저명(매개변수 ...)
IS 변수선언
BEGIN
~
END;

ISQLPLUS 에서 실행하겠습니다.

ED TEST 라고 하면 메모장이 뜬다.

메모장에서 실행시킨다.

CREATE OR REPLACE PROCESURE UP_A IS
BEGIN
DELETE FROM SAWON WHERE SAWON_NO=12;
END;
/
저장을 한 후에

@TEST 하면 실행된다.

SHOW ERROR 하면 어떤 에러가 생기는지 보여준다.

EXECUTE UP_A 하면 실행이 된다.

SELECT NAME, TEXT FROM USER_SOURCE;
EXECUTE UP_A
ED TE2

CREATE OR REPLACE PROCEDURE UP_B(NO SAWON.SAWON_NO%TYPE)
IS
BEGIN
DELETE FROM SAWON WHERE SAWON_NO=NO;
END;
/

CREATE OR REPLACE PROCEDURE UP_C(NO SAWON.SAWON_NO%TYPE,JIK SAWON.SAWON_JIK%TYPE)
IS
BEGIN
UPDATE SAWON SET SAWON_JIK = JIK WHERE SAWON_NO=NO;
END;
/

다음과 같이 함수처럼 만들어 준다.