DB 초급프로젝트

<학습목표>
1. 커서 + (FEAT. FOR문)
커서 DECLARE
CURSOR 커서명 IS
SELECT문
BEGIN
WHILE LOOP GOTO
(FOR문)
BEST
END;
2. 프로시져
STORED PROCED, 컴파일, 서버의 캐시공간에 미리저장, 이름붙임
3. 함수
내장함수 (SUBSTR, INSTR, TRIM, TO_CHAR, FANUMBER)
사용자 정의 함수 : USER (DEFINED) FUNCTION

커서 + FOR문

직업을 변수로 받아 이름 회원명과 마일리지를 출력하는 커서

ACCEPT V_JOB PROMPT '직업을 입력해주세요 : '
DECLARE
V_NAME VARCHAR2(60);
V_MILEAGE NUMBER;
--SELECT 결과 집합에 CUR라는 이름을 붙임
CURSOR CUR IS
SELECT MEM_NAME
, MEM_MILEAGE
FROM MEMBER
WHERE MEM_JOB = '&V_JOB';
BEGIN

--집합을 메모리로 바인딩
OPEN CUR;

--페따출
FETCH CUR INTO V_NAME, V_MILEAGE;
--페치했더니 데이터가 있니?
WHILE CUR%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(V_NAME ||
        ', ' || V_MILEAGE);
    FETCH CUR INTO V_NAME, V_MILEAGE;
END LOOP;

--메모리에서 제거
CLOSE CUR;

END;
/

FOR문
커서랑 FOR문이랑 잘맞는다

ACCEPT V_JOB PROMPT '직업을 입력해주세요 : '
DECLARE
V_NAME VARCHAR2(60);
V_MILEAGE NUMBER;
CURSOR CUR IS
SELECT MEM_NAME
, MEM_MILEAGE
FROM MEMBER
WHERE MEM_JOB = '&V_JOB';
BEGIN
FOR R IN CUR LOOP
DBMS_OUTPUT.PUT_LINE(R.MEM_NAME ||
', ' || R.MEM_MILEAGE);
END LOOP;

END;
/

FOR문은 이렇게도 사용가능하다

ACCEPT V_JOB PROMPT '직업을 입력해주세요 : '
BEGIN
FOR R IN (SELECT MEM_NAME
, MEM_MILEAGE
FROM MEMBER
WHERE MEM_JOB = '&V_JOB') LOOP
DBMS_OUTPUT.PUT_LINE(R.MEM_NAME ||
', ' || R.MEM_MILEAGE);
END LOOP;

END;
/

프로시져

--P.304
--상품코드를 매개변수 (PARAMETER)로 하여 재고수량 ADD
-- UPDATE쎄대여
SELECT PROD_ID
, PROD_TOTALSTOCK
FROM PROD
WHERE PROD_ID = 'P102000006';

--프로시저 생성
--컴파일 : 구문분석 + 의미분석이 처리되고, 서버의 캐시공간에 저장됨
-- 오라클이 좋아하는 언어로 바꿈
CREATE OR REPLACE PROCEDURE USP_UPDATE
IS
BEGIN
UPDATE PROD
SET PROD_TOTALSTOCK = PROD_TOTALSTOCK + 10
WHERE PROD_ID = 'P102000006';
END;
/

EXEC USP_UPDATE; --실행문

컴파일 하고 난다음 실행을 시켜야 값 변환

CREATE OR REPLACE PROCEDURE USP_UPDATE
IS
BEGIN
UPDATE PROD
SET PROD_TOTALSTOCK = PROD_TOTALSTOCK / 0
WHERE PROD_ID = 'P102000006';

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류발생 : ' || SQLERRM);

END;
/
EXEC USP_UPDATE;
/
오류발생시 EXCEPTION 사용

--BIND변수 : 매개변수(파라미터(인수)를 처리)
CREATE OR REPLACE PROCEDURE USP_UPDATE(V_TOTALSTOCK IN NUMBER)
IS
BEGIN
UPDATE PROD
SET PROD_TOTALSTOCK = PROD_TOTALSTOCK + 20
WHERE PROD_ID = 'P102000006';

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류발생 : ' || SQLERRM);

END;
/
EXEC USP_UPDATE(50);
/

V_TOTALSTOCK IN NUMBER : IN BIND 변수

--BIND변수 : 매개변수(파라미터(인수)를 처리)
CREATE OR REPLACE PROCEDURE USP_UPDATE(
V_ID IN VARCHAR2,
V_TOTALSTOCK IN NUMBER)
IS
BEGIN
UPDATE PROD
SET PROD_TOTALSTOCK = PROD_TOTALSTOCK + V_TOTALSTOCK
WHERE PROD_ID = V_ID;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('오류발생 : ' || SQLERRM);

END;
/
EXEC USP_UPDATE('P102000001', 50);
/