SQL활용 #9 - PL/SQL

김형우·2022년 3월 23일
PL/SQLsql
0

SQL 활용

목록 보기
10/14

1. PL/SQL 기본

  • PL/SQL :
  • 일반 프로그래밍 언어 요소를 다 갖고 있으며, 데이터베이스 업무를 처리하기 위한 최적화된 언어
  • BLOCK 구조로 다수의 SQL 문을 한번에 처리하므로 수행 속도가 빠름

0. 로그창 켜기

SET SERVEROUTPUT ON;

1. 기본형태

  • '/' 까지를 블럭으로 잡고 실행함 '/' 필수!
  • console.log(), System.out.println() 과 같은 역할
BEGIN
    -- console.log(), System.out.println() 과 같은 역할
    DBMS_OUTPUT.PUT_LINE('HELLO WORLD!');
END;
/ -- PL/SQL 끝

2. 변수설정 및 출력

DECLARE
    -- 외부에서 데이터가 들어옴
    -- 변수 변경부분
    -- int V_NUM = 1234;
    V_NUM NUMBER(4) := 1234;
    -- String V_STR = "";
    V_STR VARCHAR2(10);
BEGIN
    -- 여기부터 만들어 놓은 테이블을 이용해서 INSERT, UPDATE등 명령을 내림
    V_STR := 'HELLO!';
    -- '+' 를 쓰지않고 || 표시로 합친다.
    DBMS_OUTPUT.PUT_LINE('V_NUM 값은 : ' || v_NUM);
    DBMS_OUTPUT.PUT_LINE('V_STR 값은 : ' || v_STR);
END;
/

3. 조건문

  • IF문
DECLARE
    -- 변수
    V_SCORE NUMBER := 95;
    V_GRADE VARCHAR2(2) := '';
BEGIN
    -- 실행부분
    IF(V_SCORE >=90) 
        THEN V_GRADE := 'A';
    ELSIF (V_SCORE >= 80) 
        THEN V_GRADE := 'B';
    ELSIF (V_SCORE >= 70) 
        THEN V_GRADE := 'C';
    ELSE
        V_GRADE := 'F';
    END IF;
    -- 출력
    DBMS_OUTPUT.PUT_LINE('점수 : ' || v_SCORE || ', 등급 : ' || v_GRADE);
END;
/

4. 반복문

  • 반복문
  • for(int I=0; I<=9; I++)와 같음
  • 구구단
DECLARE
    -- 변수설정
    V_NUM NUMBER := 13;
BEGIN
    -- 실행
    -- for(int I=0; I<=9; I++)
    FOR I IN 1..9 LOOP
        -- 출력
        DBMS_OUTPUT.PUT_LINE(V_NUM || '*' || I || '=' || V_NUM*I);
        -- 여기에 INSERT INTO 또는 다른 쿼리문을 쓰면 반복 수행한다.
    END LOOP;
END;
/

5. 반복문 + 조건문

  • 반복문 + 조건문
  • 홀/짝
  • MOD(숫자, 나눌숫자) 나머지 구하기
    : IF MOD(I, 2) = 0 => I를 2로 나눠서 나머지가 0 = 짝수
DECLARE
    -- 변수
    -- 외부에서 들어오는 부분
    V_NUM NUMBER(3) := 10;
BEGIN
    FOR I IN 1..V_NUM LOOP
        -- MOD : 나머지 구하는것 => 2로 나누어서 나머지가 0일때
        IF MOD(I, 2) = 0
            THEN DBMS_OUTPUT.PUT_LINE( I || '는 짝수' );
        ELSE
            DBMS_OUTPUT.PUT_LINE( I || '는 홀수' );
        END IF;    
    END LOOP;
END;
/

6. INSERT

  • 추가하기 (MEMBER2 테이블)
  • INSERT INTO 테이블명(컬럼명) VALUES(추가할값);
DECLARE
    -- 변수정의
    -- 정의할 변수 없음 생략
BEGIN
    -- 수행
    -- INSERT INTO 테이블명(컬럼명) VALUES(추가할값);
    INSERT INTO MEMBER2(USERID, USERPW, USERNAME, USERAGE, USERDATE)
        VALUES('B', 'B', '임보람', 36, CURRENT_DATE);
    -- 성공하면 COMMIT;
    COMMIT;    
EXCEPTION
    -- 오류처리    
    WHEN OTHERS THEN --모든 예외의 경우
    ROLLBACK; -- ROLLBACK;
END;
/
-- 조회
SELECT * FROM MEMBER2;

7. INSERT + 반복문

  • 여러명 추가
DECLARE
    -- 변수정의
    -- 정의할 변수 없음 생략
BEGIN
    -- 수행
    -- INSERT INTO 테이블명(컬럼명) VALUES(추가할값);
    FOR I IN 1..10 LOOP
        INSERT INTO MEMBER2(USERID, USERPW, USERNAME, USERAGE, USERDATE)
            VALUES('A'||I, 'A', '반복맨'||I, 20+I, CURRENT_DATE);
    END LOOP;    
    -- 성공하면 COMMIT;
    COMMIT;    
EXCEPTION
    -- 오류처리    
    WHEN OTHERS THEN --모든 예외의 경우
    ROLLBACK; -- ROLLBACK;
END;
/
-- 조회
SELECT * FROM MEMBER2;

8. SELECT

  • 조회하기
DECLARE
    V_MEM2 MEMBER2%ROWTYPE;
    -- 타입 일치시켜야함
    -- V_USERID VARCHAR2(30);
    -- BUT, 이건 타입 바로 가지고 옴
    V_USERID MEMBER2.USERID%TYPE; -- VARCHAR2(30)
BEGIN
    -- SELECT * FROM 테이블명 WHERE 조건;
    SELECT * INTO V_MEM2 FROM MEMBER2 WHERE USERID='A';
    DBMS_OUTPUT.PUT_LINE( V_MEM2.USERID );
    DBMS_OUTPUT.PUT_LINE( V_MEM2.USERNAME );
END;
/

9. SELECT + 반복문

  • 여러개 조회하기
DECLARE
    -- CURSOR CUR IS (조회하고자하는 SQL);
    CURSOR CUR IS (SELECT * FROM MEMBER2);
BEGIN
    -- CUR 만큼 반복
    FOR TMP IN CUR() LOOP
        DBMS_OUTPUT.PUT_LINE( 'USERID : '|| TMP.USERID );
    END LOOP;
END;
/

2. PROCEDURE 프로시저

  • 자주 사용하는 SQL을 프로시저로 만듬 -> 필요할 때 마다 호출 -> 반환되는 값은 없음
  • 조회된 데이터는 가져갈 수 있음.
CREATE OR REPLACE PROCEDURE  "프로시저명"(
    "파라미터명" IN "타입"
)
IS
    "변수명"  "변수타입"
BEGIN
    "프로시저 내용"
END;
/

1. 조건문

  • IF문
  • 프로시저를 생성하면서 파라미터에 받을 변수명과 타입을 설정한다.
    : 안받을꺼면 정의할 필요가 없음
CREATE OR REPLACE PROCEDURE PROC_IF_EXAM(
    -- "파라미터명" IN "타입"
    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';
    ELSIF (V_SCORE >= 70) 
        THEN V_GRADE := 'C';
    ELSE
        V_GRADE := 'F';
    END IF;
    DBMS_OUTPUT.PUT_LINE(V_GRADE);
END;
/
-- 실행하면 'Procedure PROC_IF_EXAM이(가) 컴파일되었습니다.'
-- 외부에서 사용
EXEC PROC_IF_EXAM(88);

2. 반복문

  • FOR문
  • 구구단 프로시저 만들기
CREATE OR REPLACE PROCEDURE PROC_CAL(
    -- "파라미터명" IN "타입"
    V_NUM IN NUMBER
)
IS
    -- "변수명"  "변수타입"
    -- V_ASW NUMBER;
BEGIN
    -- "프로시저 내용"
    FOR I IN 1..9 LOOP
        DBMS_OUTPUT.PUT_LINE(V_NUM||'*'||I||'='||V_NUM*I);
    END LOOP;
END;
/
--외부에서 사용
EXEC PROC_CAL(3);

3. INSERT

  • INSERT 프로시저
  • 프로시저명 PROC_INSERT_EXAM
  • 프로시저를 생성하면서 파라미터에 받을 변수명과 타입을 설정한다.
    : 타입을 하나하나 확인하기보다
    : MEMBER2.USERID%TYPE 를 쓰면 타입을 자동으로 불러온다.
    : %TYPE
CREATE OR REPLACE PROCEDURE PROC_INSERT_EXAM(
    -- "파라미터명" IN "타입"
    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; -- ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('INSERT 실패');
END;
/
-- 프로시저 실행
EXEC PROC_INSERT_EXAM('C','C','별이',7);
-- 조회
SELECT * FROM MEMBER2;

4. UPSERT

  • UPSERT 프로시저
  • 회원이 있으면 업데이트, 없으면 추가
  • 프로시저명 PROC_UPSERT_EXAM
  • V_CHK는 임시변수, 0은 존재하지않는다, 1은 존재한다.
  • SELECT COUNT(*) INTO V_CHK FROM MEMBER2 WHERE USERID = V_ID;
    : USERID = V_ID를 조회해서 같은 아이디의 개수를 구하고(COUNT(*)) 그 개수를 V_CHK에 담는다(INTO V_CHK)
CREATE OR REPLACE PROCEDURE PROC_UPSERT_EXAM(
    -- "파라미터명" IN "타입"
    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
    -- 수행
    -- "프로시저 내용"
    -- V_ID와 일치하는 아이디 개수를 구함
    -- 'INTO 변수명' 하면 내용이 변수에 담김
    SELECT COUNT(*) INTO V_CHK FROM MEMBER2 WHERE USERID = V_ID;
    IF (V_CHK <= 0) THEN
        INSERT INTO MEMBER2(USERID, USERPW, USERNAME, USERAGE, USERDATE)
            VALUES(V_ID, V_PW, V_NA, V_AG, CURRENT_DATE);
    DBMS_OUTPUT.PUT_LINE('INSERT 성공');
    ELSE 
        UPDATE MEMBER2 SET USERPW = V_PW, USERNAME = V_NA, USERAGE = V_AG
            WHERE USERID = V_ID;
    DBMS_OUTPUT.PUT_LINE('UPDATE 성공');
    END IF;
    COMMIT;
EXCEPTION
    -- 오류처리    
    WHEN OTHERS THEN --모든 예외의 경우
    ROLLBACK; -- ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('UPSERT 실패');
END;
/
-- 프로시저 실행
EXEC PROC_UPSERT_EXAM('W','W','별이',5);
-- 조회
SELECT * FROM MEMBER2;

5. DELETE

  • DELETE 프로시저
  • 프로시저명 PROC_DELETE_EXAM
  • 프로시저를 생성하고 실행할때 EXEC를 못쓴다.
  • 파라미터에서 설정한 변수의 속성을 OUT으로 정했기때문에 반환값을 받을 수 있는 형태로 실행해야한다.
    : DECLARE에서 변수 설정, BEGIN에서 실행 후 결과 출력
CREATE OR REPLACE PROCEDURE PROC_DELETE_EXAM(
    -- "파라미터명" IN "타입"
    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; -- ROLLBACK;
    V_CHK := 0;
END;
/
-- 프로시저 실행 테스트
DECLARE
    T_ID MEMBER2.USERID%TYPE := 'A5';
    T_CHK NUMBER := 0;
BEGIN
    PROC_DELETE_EXAM(T_ID, T_CHK);
    DBMS_OUTPUT.PUT_LINE('결과출력 : '||T_CHK);
END;
/    
-- EXEC PROC_DELETE_EXAM('A1'); -- 이거 아님
-- 조회
SELECT * FROM MEMBER2;

실습 - BOARD3 테이블

  • BOARD3 테이블
  1. 값을 전달했을때 추가되는 PROC_BRD_INSERT(0,1)
  2. 글번호가 없으면 추가, 있으면 조회수를 1증가 PROC_BRD_UPSERT (0,1)
  3. 작성자가 일치하는 항목을 삭제하는 PROC_BRD_DELETE (0,1)
  4. 조건에 해당하는 항목의 개수를 반환하는 PROC_BRD_SELECT (N개)

1. 값을 전달했을때 추가되는

  • PROC_BRD_INSERT(0,1)
CREATE OR REPLACE PROCEDURE PROC_BRD_INSERT (
    -- "파라미터명" IN "타입"
    -- IN_NO IN BOARD3.NO%TYPE, 
    IN_TITLE IN BOARD3.TITLE%TYPE, 
    IN_CONTENT IN BOARD3.CONTENT%TYPE, 
    IN_WRITER IN BOARD3.WRITER%TYPE, 
    -- IN_HIT IN BOARD3.HIT%TYPE,
    OUT_CHK OUT NUMBER
)
IS
    -- "변수명"  "변수타입"
BEGIN
    INSERT INTO BOARD3(NO, TITLE, CONTENT, WRITER, HIT, REGDATE)
        VALUES(SEQ_BOARD3_NO.NEXTVAL, IN_TITLE, IN_CONTENT, IN_WRITER, 1, CURRENT_DATE);
    COMMIT;
    OUT_CHK := 1;
EXCEPTION
    -- 오류처리    
    WHEN OTHERS THEN --모든 예외의 경우
    ROLLBACK; -- ROLLBACK;
    OUT_CHK := 0;
END;
/
-- 프로시저 테스트
DECLARE
    T_TITLE BOARD3.TITLE%TYPE := 'AA';
    T_CONTENT BOARD3.CONTENT%TYPE := 'AA';
    T_WRITER BOARD3.WRITER%TYPE := 'AR';
    -- T_HIT BOARD3.HIT%TYPE := 1;
    T_CHK NUMBER := 0;
BEGIN
    PROC_BRD_INSERT(T_TITLE, T_CONTENT, T_WRITER, T_CHK);
    DBMS_OUTPUT.put_line('결과 : ' || T_CHK);
END;
/
SELECT * FROM BOARD3;
SELECT * FROM MEMBER3;

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

  • PROC_BRD_UPSERT (0,1)
CREATE OR REPLACE PROCEDURE PROC_BRD_UPSERT(
    -- "파라미터명" IN "타입"
    IN_NO IN BOARD3.NO%TYPE,
    IN_TITLE IN BOARD3.TITLE%TYPE, 
    IN_CONTENT IN BOARD3.CONTENT%TYPE, 
    IN_WRITER IN BOARD3.WRITER%TYPE, 
    OUT_CHK OUT NUMBER
)
IS
    -- "변수명"  "변수타입"
    V_CHK NUMBER := 0;
BEGIN
    -- "프로시저 내용"
    SELECT COUNT(*) INTO V_CHK FROM BOARD3 WHERE NO = IN_NO;
    IF (V_CHK <= 0) THEN
        INSERT INTO BOARD3(NO, TITLE, CONTENT, WRITER, HIT, REGDATE)
            VALUES(SEQ_BOARD3_NO.NEXTVAL, IN_TITLE, IN_CONTENT, IN_WRITER, 1, CURRENT_DATE);
    COMMIT;
    OUT_CHK := 0;
    ELSE 
        UPDATE BOARD3 SET HIT = HIT+1 WHERE NO = IN_NO;
    COMMIT; 
    OUT_CHK := 1;
    END IF;
EXCEPTION
    -- 오류처리    
    WHEN OTHERS THEN --모든 예외의 경우
    ROLLBACK; -- ROLLBACK;
    OUT_CHK := -1;    
END;
/
-- 프로시저 테스트
DECLARE
    T_NO BOARD3.NO%TYPE := 55;
    T_TITLE BOARD3.TITLE%TYPE := 'BB';
    T_CONTENT BOARD3.CONTENT%TYPE := 'BB';
    T_WRITER BOARD3.WRITER%TYPE := 'B';
    T_CHK NUMBER := 0;
BEGIN
    PROC_BRD_UPSERT(T_NO, T_TITLE, T_CONTENT, T_WRITER, T_CHK);
    DBMS_OUTPUT.put_line('결과 : ' || T_CHK);
END;
/
SELECT * FROM BOARD3;
SELECT * FROM MEMBER3;

3. 작성자가 일치하는 항목을 삭제하는

  • PROC_BRD_DELETE (0,1)
CREATE OR REPLACE PROCEDURE PROC_BRD_DELETE(
    -- "파라미터명" IN "타입"
    IN_WRITER IN BOARD3.WRITER%TYPE,
    OUT_CHK OUT NUMBER
)
IS
    -- "변수명"  "변수타입"    
BEGIN
    -- "프로시저 내용"
    DELETE FROM BOARD3 WHERE WRITER = IN_WRITER;
    COMMIT;
    OUT_CHK := 1;
EXCEPTION
    -- 오류처리    
    WHEN OTHERS THEN --모든 예외의 경우
    ROLLBACK; -- ROLLBACK;
    OUT_CHK := 0;
END;
/
-- 프로시저 테스트
DECLARE    
    T_WRITER BOARD3.WRITER%TYPE := 'A';
    T_CHK NUMBER := 0;
BEGIN
    PROC_BRD_DELETE(T_WRITER, T_CHK);
    DBMS_OUTPUT.put_line('결과 : ' || T_CHK);
END;
/
SELECT * FROM BOARD3;
SELECT * FROM MEMBER3;

4. 조건에 해당하는 항목의 개수를 반환하는

  • PROC_BRD_SELECT (N개)
  • 조회수가 10이상
CREATE OR REPLACE PROCEDURE PROC_BRD_SELECT(
    -- "파라미터명" IN "타입"
    IN_HIT IN BOARD3.HIT%TYPE,
    OUT_CHK OUT NUMBER
)
IS
    -- "변수명"  "변수타입"
BEGIN
    -- "프로시저 내용"
    SELECT COUNT(*) INTO OUT_CHK FROM BOARD3 WHERE HIT > IN_HIT;
EXCEPTION
    -- 오류처리    
    WHEN OTHERS THEN --모든 예외의 경우
    ROLLBACK; -- ROLLBACK; 
    OUT_CHK := -1;
END;
/
-- 프로시저 테스트
DECLARE    
    T_HIT BOARD3.HIT%TYPE := 10;
    T_CHK NUMBER := 0;
BEGIN
    PROC_BRD_SELECT(T_HIT, T_CHK);
    DBMS_OUTPUT.put_line('결과 : ' || T_CHK);
    DBMS_OUTPUT.put_line('조회수 10이상 게시물 수 : ' || T_CHK);
END;
/
SELECT * FROM BOARD3;
SELECT * FROM MEMBER3;
profile
김형우
The best
이전 포스트

SQL활용 #문법정리

다음 포스트

SQL활용 #10 - 함수

0개의 댓글