SQL활용 2022/03/23(SQL 작성하기) PROCEDURE

무간·2022년 3월 23일

PROCEDURE

자주 사용하는 SQL을 프로시저로 만듬 -> 필요할 때 마다 호출 -> 반환되는 값은 없음
조회된 데이터는 가져갈 수 있음.

CREATE OR REPLACE PROCEDURE  "프로시저명"( 
    "파라미터명" IN "타입"
)
IS
    -- 변수정의
    "변수명"  "변수타입"
BEGIN
    -- 수행
    "프로시저 내용"
END;
/

프로시져명 : PROC_IF_EXAM

  • 입력한 점수를 기반으로 등급을 출력 
CREATE OR REPLACE PROCEDURE  PROC_IF_EXAM(
    V_SCORE IN NUMBER -- 외부에서 숫자하나가 전달되어야 함.
)
IS
    -- 변수정의
    V_GRADE VARCHAR2(2);
BEGIN
    -- 수행
    IF(V_SCORE >= 90) THEN
        V_GRADE := 'A';
    ELSIF(V_SCORE >= 80)THEN
        V_GRADE := 'B';    
    ELSE
        V_GRADE := 'F';
    END IF;
    DBMS_OUTPUT.PUT_LINE('등급 : ' || V_GRADE);
END;
/

-- 실행하기
EXEC PROC_IF_EXAM(90);
/

프로시저명 : PROC_FOR_EXAM

  • 프로시저 숫자를 전달하면 구구단이 출력되는 
CREATE OR REPLACE PROCEDURE  PROC_FOR_EXAM(
    V_NUM IN NUMBER -- 외부에서 숫자하나가 전달되어야 함.
)
IS
    -- 변수정의    
BEGIN
    -- 수행
    FOR I IN 1..9 LOOP
        DBMS_OUTPUT.PUT_LINE(V_NUM || '*' || I || '=' || V_NUM*I);
    END LOOP;
END;
/
-- 실행하기
EXEC PROC_FOR_EXAM(9);
/

회원 등록

CREATE OR REPLACE PROCEDURE PROC_INSERT_EXAM(
    V_ID IN MEMBER2.USERID%TYPE,
    V_PW IN MEMBER2.USERPW%TYPE,
    V_NA IN MEMBER2.USERNAME%TYPE,
    V_AG IN MEMBER2.USERAGE%TYPE
)
IS
BEGIN
    -- 수행
    INSERT INTO MEMBER2(USERID, USERPW, USERNAME, USERAGE, USERDATE)
    VALUES(V_ID, V_PW, V_NA, V_AG, CURRENT_DATE);
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('INSERT 성공');
EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('INSERT 실패');
END;
/
-- 프로시저 실행
EXEC PROC_INSERT_EXAM('b','b','나',22);
/
-- 결과조회
SELECT * FROM MEMBER2;
/

프로시저명 : PROC_UPSERT_EXAM

회원이 있으면 나이를 100으로 바꾸고 없으면 추가

CREATE OR REPLACE PROCEDURE PROC_UPSERT_EXAM(
    V_ID IN MEMBER2.USERID%TYPE,
    V_PW IN MEMBER2.USERPW%TYPE,
    V_NA IN MEMBER2.USERNAME%TYPE,
    V_AG IN MEMBER2.USERAGE%TYPE
)
IS
    -- 존재하는지 하지 않는지 보관하는 변수 (0,1이 저장됨.)
    V_CHK NUMBER(2) := 0;
BEGIN
    -- 수행
    SELECT COUNT(*) INTO V_CHK FROM MEMBER2 WHERE USERID = V_ID;
    IF V_CHK = 0 THEN
        -- INSERT
        INSERT INTO MEMBER2(USERID, USERPW, USERNAME, USERAGE, USERDATE)
        VALUES(V_ID, V_PW, V_NA, V_AG, CURRENT_DATE);
    ELSE
        -- UPDATE 테이블명 SET 변경값 WHERE 조건;
        UPDATE MEMBER2 SET USERAGE=100 WHERE USERID=V_ID;
    END IF;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('INSERT 성공');
EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('INSERT 실패');
END;
/
-- 프로시저 실행
EXEC PROC_UPSERT_EXAM('C','C','다',22);
/
-- 결과조회
SELECT * FROM MEMBER2;
/

프로시저명 : PROC_DELETE_EXAM

CREATE OR REPLACE PROCEDURE PROC_DELETE_EXAM(
    V_ID IN MEMBER2.USERID%TYPE,
    V_CHK OUT NUMBER
)
IS    
BEGIN
    -- 수행
    -- DELETE FROM 테이블명 WHERE 조건
    DELETE FROM MEMBER2 WHERE USERID=V_ID;
    COMMIT;
    V_CHK :=1;
    
EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    V_CHK :=0;
END;
/
-- 실행 테스트
DECLARE
    T_ID MEMBER2.USERID%TYPE :='b';
    T_CHK NUMBER :=0;
BEGIN
    -- 프로시저 호출(IN변수, OUT변수)
    PROC_DELETE_EXAM(T_ID, T_CHK);
    DBMS_OUTPUT.PUT_LINE('결과출력 ' || T_CHK);
END;
/
-- 결과조회
SELECT * FROM MEMBER2;
/

BOARD3 테이블

프로시저명 : PROC_BRD_INSERT (0,1)

값을 전달했을때 추가되는
CREATE OR REPLACE PROCEDURE PROC_BRD_INSERT(    
    V_TI IN BOARD3.TITLE%TYPE,
    V_CO IN BOARD3.CONTENT%TYPE,
    V_WR IN BOARD3.WRITER%TYPE,
    V_HI IN BOARD3.HIT%TYPE,    
    V_CHK OUT NUMBER
)
IS
BEGIN
    -- 수행
    INSERT INTO BOARD3(NO, TITLE, CONTENT, WRITER, HIT, REGDATE)
    VALUES(SEQ_BOARD3_NO.NEXTVAL, V_TI, V_CO, V_WR, V_HI, CURRENT_DATE);
    COMMIT;
    V_CHK :=1;
EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    V_CHK :=0;
END;
/
-- 프로시저 실행
DECLARE    
    T_TI BOARD3.TITLE%TYPE :='Z';
    T_CO BOARD3.CONTENT%TYPE :='Z';
    T_WR BOARD3.WRITER%TYPE :='F';
    T_HI BOARD3.HIT%TYPE :=1;
    T_CHK NUMBER :=0;    
BEGIN
    -- 프로시저 호출(IN변수, OUT변수)
    PROC_BRD_INSERT(T_TI, T_CO, T_WR, T_HI, T_CHK);
    DBMS_OUTPUT.PUT_LINE('결과출력 ' || T_CHK);
END;
/
-- 결과조회
SELECT * FROM BOARD3;
SELECT * FROM MEMBER3;
/

프로시저명 : PROC_BRD_UPSERT (0,1)

글번호가 없으면 추가, 있으면 조회수를 1증가

CREATE OR REPLACE PROCEDURE PROC_BRD_UPSERT(
    V_NO IN BOARD3.NO%TYPE,
    V_TI IN BOARD3.TITLE%TYPE,
    V_CO IN BOARD3.CONTENT%TYPE,
    V_WR IN BOARD3.WRITER%TYPE,
    V_HI IN BOARD3.HIT%TYPE,    
    V_CHK OUT NUMBER
)
IS
    -- 존재하는지 하지 않는지 보관하는 변수 (0,1이 저장됨.)
    V_CHK1 NUMBER(2) := 0;
BEGIN
    -- 수행
    SELECT COUNT(*) INTO V_CHK1 FROM BOARD3 WHERE NO = V_NO;
    IF V_CHK1 = 0 THEN
        -- INSERT
        INSERT INTO BOARD3(NO, TITLE, CONTENT, WRITER, HIT, REGDATE)
        VALUES(V_NO, V_TI, V_CO, V_WR, V_HI, CURRENT_DATE);    
    ELSE
        -- UPDATE 테이블명 SET 변경값 WHERE 조건;
        UPDATE BOARD3 SET HIT=HIT+1 WHERE NO = V_NO;        
    END IF;
    COMMIT;
    V_CHK :=1;
EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    V_CHK :=0;
END;
/
-- 프로시저 실행
DECLARE    
    T_NO BOARD3.NO%TYPE :=27;
    T_TI BOARD3.TITLE%TYPE :='Z';
    T_CO BOARD3.CONTENT%TYPE :='Z';
    T_WR BOARD3.WRITER%TYPE :='A';
    T_HI BOARD3.HIT%TYPE :=1;
    T_CHK NUMBER :=0;    
BEGIN
    -- 프로시저 호출(IN변수, OUT변수)
    PROC_BRD_UPSERT(T_NO, T_TI, T_CO, T_WR, T_HI, T_CHK);
    DBMS_OUTPUT.PUT_LINE('결과출력 ' || T_CHK);
END;
/
SELECT * FROM BOARD3;
/

프로시저명 : PROC_BRD_DELETE (0,1)

  • 작성자가 일치하는 항목을 삭제하는 
CREATE OR REPLACE PROCEDURE PROC_BRD_DELETE(
    V_WR IN BOARD3.WRITER%TYPE,
    V_CHK OUT NUMBER
)
IS    
BEGIN
    -- 수행
    -- DELETE FROM 테이블명 WHERE 조건
    DELETE FROM BOARD3 WHERE WRITER=V_WR;
    COMMIT;
    V_CHK :=1;
    
EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    V_CHK :=0;
END;
/
-- 실행 테스트
DECLARE
    T_WR BOARD3.WRITER%TYPE :='F';
    T_CHK NUMBER :=0;
BEGIN
    -- 프로시저 호출(IN변수, OUT변수)
    PROC_BRD_DELETE(T_WR, T_CHK);
    DBMS_OUTPUT.PUT_LINE('결과출력 ' || T_CHK);
END;
/
SELECT * FROM BOARD3;
/

프로시저명 : PROC_BRD_SELECT (N개) (조회수가 10이상)

  • 조건에 해당하는 항목의 개수를 반환하는
CREATE OR REPLACE PROCEDURE PROC_BRD_SELECT(
    V_HI IN BOARD3.HIT%TYPE,
    V_CHK OUT NUMBER
)
IS    
BEGIN
    -- 수행    
    SELECT COUNT(*) INTO V_CHK FROM BOARD3 WHERE HIT>=V_HI;    
        
EXCEPTION
    WHEN OTHERS THEN    
    V_CHK :=0;
END;
/
DECLARE
    T_HI BOARD3.HIT%TYPE :=5;
    T_CHK NUMBER :=0;
BEGIN
    -- 프로시저 호출(IN변수, OUT변수)
    PROC_BRD_SELECT(T_HI, T_CHK);
    DBMS_OUTPUT.PUT_LINE('결과출력 ' || T_CHK);
END;
/
SELECT * FROM BOARD3;
profile
무간
당신을 한 줄로 소개해보세요
이전 포스트

SQL활용 2022/03/23(SQL 작성하기) PL/SQL

다음 포스트

SQL활용 2022/03/23(SQL 작성하기) 함수

0개의 댓글