빅데이터 Java 개발자 교육 - 21일차 [Oracle 6번째 시간 (PL/SQL)]

Jun_Gyu·2023년 2월 27일
PL/SQLoracle
0

빅데이터 Java 개발자 교육

목록 보기
21/71
post-thumbnail

오늘 수업에는 oracle의 콘솔 출력환경을 변경하여 eclipse처럼 출력할 수 있도록
PL/SQL실습을 진행해보도록 하겠다.

들어가기 앞서서 PL/SQL이란 무엇일까??

🪐 PL/SQL

Procedural Language extension to SQL

  • SQL을 확장한 절차적 언어(Procedural Language)이다.

  • 관계형 데이터베이스에서 사용되는 Oracle의 표준 데이터 엑세스 언어로, 프로시저 생성자를 SQL과 완벽하게 통합한다.

  • 유저 프로세스가 PL/SQL 블록을 보내면, 서버 프로세서는 PL/SQL Engine에서 해당 블록을 받고 SQL과 Procedural를 나눠서 SQL은 SQL Statement Executer로 보낸다.

  • 이식성이 좋으며, 예외처리가 가능하다.

SQL 대신 PL/SQL을 사용하는 이유

  • 1) SQL에는 변수가 없다.

  • 2) SQL은 한번에 하나의 명령문만 사용 가능하기 떄문에 트래픽이 상대적으로 증가한다.

  • 3) SQL은 제어문이 사용 불가. (IF, LOOP)

  • 4) SQL은 예외처리가 없다. 등등

기본특징

  • 블록 단위의 실행을 제공한다. 이를 위해 BEGIN과 END;를 사용한다. 그리고 마지막 라인에 /를 입력하면 해당 블록이 실행된다.
  • 변수, 상수 등을 선언하여 SQL과 절차형 언어에서 사용
  • 변수의 선언은 DECLARE절에서만 가능하다. 그리고 BEGIN 섹션에서 새 값이 할당될 수 있다.
  • IF문을 사용하여 조건에 따라 문장들을 분기 가능
  • LOOP문을 사용하여 일련의 문장을 반복 가능
  • 커서를 사용하여 여러 행을 검색 및 처리

  • [ PL/SQL에서 사용 가능한 SQL은 Query, DML, TCL이다. ]

    DDL (CREATE, DROP, ALTER, TRUNCATE …), DCL (GRANT, REVOKE) 명령어는 동적 SQL을 이용할 때만 사용 가능하다.

  • [ PL/SQL의 SELECT문은 해당 SELECT의 결과를 PL/SQL Engine으로 보낸다. ]
    이를 캐치하기 위한 변수를 DECLARE해야 하고, INTO절을 꼭 선언하여 넣을 변수를 꼭 표현해주어야 SELECT 문장은 반드시 한 개의 행이 검색되어야 한다.

    그리고 이를 INTO절을 꼭 사용해야한다. 또한 검색되는 행이 없으면 문제가 발생한다.

PL/SQL 구조

영역설명옵션/필수
DECLARE (선언부)PL/SQL에서 사용하는 모든 변수나 상수를 선언하는 부분으로서 DECLARE로 시작
=> 변수/상수/커서 등 을 선언		옵션
BEGIN (실행부)절차적 형식으로 SQL문을 실행할수있도록 절차적 언어의 요소인 제어문, 반복문, 함수 정의 등 로직을 기술할수있는 부분이며 BEGIN으로 시작 필수
EXCEPTION (예외 처리부)PL/SQL문이 실행되는 중에 에러가 발생할수있는데 이를 예외 사항이라고 한다.
이러한 예외 사항이 발생했을때 이를 해결하기 위한 문장을 기술할수있는 부분		옵션
END (실행문 종료)-필수

🌍 출처는 여기입니당

이후 더 자세한 부분에 대해서는 직접 구글링을 통해서 찾아보도록 HAZA.




위의 개념을 바탕으로 실습을 진행해보았다.

PL/SQL 실습

1. 출력환경 변경 및 출력문

-- 콘솔에 출력환경 변경
SET SERVEROUTPUT ON;

-- 출력문
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello world' || '!');  
    -- Syetem.out.printlin("Hello world"); 자바의 ||(or)이 여기에서는 +역활.
END;
/ 
-- 다른 코드들과 겹쳐서 실행되지 않도록 마지막에 '/'를 꼭 붙여주도록 하자!
-- 추가로 주석문은 '/' 옆에 달게되면 오류가 발생하니, 필요하면 위,아래칸에 쓸 것.

콘솔에 출력환경 변경 설정을 ON 하여 출력이 되도록 설정을 'on' 하게되면,
기존 eclipse에서 사용했던 콘솔 출력창처럼 사용을 할 수 있게된다.
위의 결과를 실행하면

Hello world!


PL/SQL 프로시저가 성공적으로 완료되었습니다.

라고 출력이 되게 된다.


아래의 다른 예제 & 출력 결과물들을 보면서 어떤 식으로 구성이 되어있는지 살펴보자.

2. 변수선언

-- 변수선언
DECLARE
    t_str VARCHAR2(20) := 'aaa';  -- String t_str = "aaa"
    t_num NUMBER(4) := 1234;   -- int t_num = 1234;
BEGIN
    DBMS_OUTPUT.PUT_LINE(t_str || ',' || t_num); 
    -- System.out.print("aaa" + "," + 1234);
END;
/
aaa,1234


PL/SQL 프로시저가 성공적으로 완료되었습니다.


3. 조건문

-- 조건문
DECLARE
    T_SCORE NUMBER(3) := 99;
    T_GRADE VARCHAR2(2);
    
BEGIN
    IF T_SCORE >= 90 AND T_SCORE <=98 THEN  -- if(T_SCORE>=90&&T_SCORE<=98){
    T_GRADE := 'A';
    ELSIF T_SCORE >= 80 THEN                -- else if(T_SCORE >= 80) {
    T_GRADE := 'B';
    ELSIF T_SCORE >= 70 THEN
    T_GRADE := 'C';
    ELSE
    T_GRADE := 'D';
    END IF;                                 --  }
    DBMS_OUTPUT.PUT_LINE(T_GRADE || '등급');
END;
/


4. 반복문

-- 반복문
DECLARE
    T_NUM NUMBER := 3;
BEGIN
    FOR i IN 1..9 LOOP  --  for(int i = 1; i<=9; i++ )
        DBMS_OUTPUT.PUT_LINE(T_NUM || '*' || i || '=' || T_NUM* i);
    END LOOP;
END;
/
3*1=3
3*2=6
3*3=9
3*4=12
3*5=15
3*6=18
3*7=21
3*8=24
3*9=27


PL/SQL 프로시저가 성공적으로 완료되었습니다.

4-1. 홀수, 짝수 반복문

-- 홀수, 짝수 반복문
--오라클 내장함수 실행 방법 (DUAL에서 불러온다.)
SELECT MOD (15, 2) FROM DUAL; -- MOD 나머지
SELECT CURRENT_DATE FROM DUAL; -- CURRENT_DATE 현재날짜

DECLARE
    T_NUM NUMBER := 10;
BEGIN
    FOR i IN 1..T_NUM LOOP  --  for(int i = 1; i<=9; i++ )
        IF MOD(i,2) = 0 THEN    -- if (i%2==0){
            DBMS_OUTPUT.PUT_LINE( i || '짝수');
        ELSE
            DBMS_OUTPUT.PUT_LINE( i || '홀수');
        END IF;
    END LOOP;
END;
/
1홀수
2짝수
3홀수
4짝수
5홀수
6짝수
7홀수
8짝수
9홀수
10짝수


PL/SQL 프로시저가 성공적으로 완료되었습니다.



5. 한번에 여러 데이터 전송

-- 20개 데이터 추가(MEMBER테이블)
SELECT * FROM MEMBER M;

DECLARE
    T_NUM NUMBER(2) := 20;
BEGIN
    FOR i IN 1..T_NUM LOOP
        INSERT INTO MEMBER( USERID, USERPW, USERNAME, USERAGE, USERPHONE, USERGENDER, USERDATE )
        VALUES('Test_Member' || i, 'pw', 'name', 11, '010', 'M', CURRENT_DATE);
    END LOOP;
    COMMIT;
EXCEPTION WHEN OTHERS THEN -- 예외처리 (try catch문, throw문)
     ROLLBACK;
END;
/




6. 반복문 이용해서 데이터 조회하기

-- 반복문을 이용하여 조회하기 ( MEMBER )
DECLARE
BEGIN
    FOR TMP IN (SELECT M.* FROM MEMBER M ORDER BY USERID ASC) LOOP
        DBMS_OUTPUT.PUT_LINE( '아이디 : '|| TMP.USERID || '  이름 : ' || TMP.USERNAME );
    END LOOP;
END;
/
아이디 : New_Insert_member1  이름 : NAME1
아이디 : New_Insert_member2  이름 : NAME2
아이디 : New_Insert_member3  이름 : NAME3
아이디 : Test_Member1  이름 : name
아이디 : Test_Member10  이름 : name
아이디 : Test_Member11  이름 : name
아이디 : Test_Member12  이름 : name
아이디 : Test_Member13  이름 : name
아이디 : Test_Member14  이름 : name
아이디 : Test_Member15  이름 : name
아이디 : Test_Member16  이름 : name
아이디 : Test_Member17  이름 : name
아이디 : Test_Member18  이름 : name
아이디 : Test_Member19  이름 : name
아이디 : Test_Member2  이름 : name
아이디 : Test_Member20  이름 : name
아이디 : Test_Member3  이름 : name
아이디 : Test_Member4  이름 : name
아이디 : Test_Member5  이름 : name
아이디 : Test_Member6  이름 : name
아이디 : Test_Member7  이름 : name
아이디 : Test_Member8  이름 : name
아이디 : Test_Member9  이름 : name
아이디 : a  이름 : 바밤바
아이디 : b  이름 : 밤맛바밤바
아이디 : c  이름 : 가나다
아이디 : d  이름 : 가나다
아이디 : f  이름 : 가나다
아이디 : h  이름 : 가나다
아이디 : hk  이름 : c
아이디 : j  이름 : c
아이디 : k  이름 : c
아이디 : l  이름 : c
아이디 : p  이름 : c


PL/SQL 프로시저가 성공적으로 완료되었습니다.



7. CURSOR를 통해 객체 선언 후 조회하기

-- CURSOR를 선언해서 조회하기 ( MEMBER )
DECLARE
    CURSOR cur IS SELECT M.* FROM MEMBER M ORDER BY USERID ASC;
BEGIN
    FOR TMP IN CUR() LOOP
        DBMS_OUTPUT.PUT_LINE( '아이디 : '|| TMP.USERID || '  이름 : ' || TMP.USERNAME );
    END LOOP;
END;
/

결과는 위와 동일!



8. 함수만들기

-- public String fincToCharToday(){  }
CREATE OR REPLACE FUNCTION FUNC_TOCHAR_TODAY RETURN VARCHAR2
IS
    T_DATE varchar2(30);
BEGIN
    -- 내장함수 => CURRENT_DATE, TO_CHAR(변경날짜, 포멧)
    SELECT TO_CHAR(CURRENT_DATE, 'YYYY.MM.DD')INTO T_DATE FROM DUAL;  -- 2023-02-27
    RETURN T_DATE;
EXCEPTION WHEN OTHERS THEN
    RETURN '';
END; -- '함수' 폴더 안에 생성완료됨.
/
-- 만들어진 함수 실행
SELECT FUNC_TOCHAR_TODAY FROM DUAL;



9. 데이터 일괄추가

-- 일괄 추가 (MEMBER)
INSERT ALL
    INTO MEMBER( USERID, USERPW, USERNAME, USERAGE, USERPHONE, USERGENDER, USERDATE )
        VALUES ('New_Insert_member1', 'PW', 'NAME1', 45, '010-', 'W',CURRENT_DATE)
    INTO MEMBER( USERID, USERPW, USERNAME, USERAGE, USERPHONE, USERGENDER, USERDATE )
        VALUES ('New_Insert_member2', 'PW', 'NAME2', 56, '010-', 'W',CURRENT_DATE)
    INTO MEMBER( USERID, USERPW, USERNAME, USERAGE, USERPHONE, USERGENDER, USERDATE )
        VALUES ('New_Insert_member3', 'PW', 'NAME3', 46, '010-', 'M',CURRENT_DATE)
SELECT * FROM DUAL;



10. 함수를 직접 생성하여 데이터 일괄 추가하기

-- 현재의 SEQ_ITEM_CODE시퀀스 숫자 가져오는 함수
CREATE OR REPLACE FUNCTION FUNC_SEQ_ITEM_CODE_NEXTVAL RETURN NUMBER
IS
BEGIN
    RETURN SEQ_ITEM_CODE.NEXTVAL;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
/

-- 일괄 추가 (ITEM) [시퀀스를 한번에 불러오게 되면 일괄추가가 안됨! (같은 번호가 한번에 겹치기 때문)]
INSERT ALL
    INTO ITEM (CODE, NAME, PRICE, QUANTITY, CONTENT, REGDATE)
        VALUES (FUNC_SEQ_ITEM_CODE_NEXTVAL, '품명', 123, 456, '내용~', CURRENT_DATE)
    INTO ITEM (CODE, NAME, PRICE, QUANTITY, CONTENT, REGDATE)
        VALUES (FUNC_SEQ_ITEM_CODE_NEXTVAL, '품명', 123, 456, '내용~', CURRENT_DATE)
    INTO ITEM (CODE, NAME, PRICE, QUANTITY, CONTENT, REGDATE)
        VALUES (FUNC_SEQ_ITEM_CODE_NEXTVAL, '품명', 123, 456, '내용~', CURRENT_DATE)
SELECT * FROM DUAL;
-- 한번에 각각 시퀀스를 개별로 가져오도록 함수를 직접 만들어서 추가.

(동시에 같은 시퀀스 값으로 들어갈 수 없기때문에 시퀀스를 하나씩 받게끔 함수를 생성 & 지정.)


11. 시간 추출하기

-- EXTRACT(파라미터) CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP FROM DUAL;
SELECT EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) FROM DUAL;
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP) FROM DUAL;
SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP)FROM DUAL;
SELECT EXTRACT(DAY FROM CURRENT_TIMESTAMP) FROM DUAL;

TimeStamp에서 확인할 수 있는 정보들을 년도, 월, 일, 시간, 분, 초 까지 뽑아낼 수 있다.

11-1. 시간 추출함수 활용예제

-- 시간대별 판매수량을 반환하는 함수
CREATE OR REPLACE FUNCTION FUNC_PURCHASE_GROUP_HOUR(IN_HOUR NUMBER) RETURN NUMBER
IS
    TMP_TOTAL NUMBER := 0;
BEGIN
    SELECT SUM(P.CNT) INTO TMP_TOTAL FROM PURCHASE P WHERE EXTRACT(HOUR FROM P.REGDATE) = IN_HOUR
    GROUP BY EXTRACT(HOUR FROM P.REGDATE);
    RETURN TMP_TOTAL;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
/

-- 함수 테스트
SELECT FUNC_PURCHASE_GROUP_HOUR(11) FROM DUAL;
-- 11시에 판매된 물품 갯수


12. 값을 입력받아 결과를 도출하기

-- ITEM 테이블의 물품코드별 재고수량을 반환하는 함수 (FUNC_ITEM_GROUP_QUANTITY)
CREATE OR REPLACE FUNCTION FUNC_ITEM_GROUP_QUANTITY(ITEM_CODE NUMBER) RETURN NUMBER
IS
    TMP_TOTAL NUMBER:=0;
BEGIN
    SELECT SUM(I.QUANTITY)INTO TMP_TOTAL FROM ITEM I WHERE I.CODE = ITEM_CODE
    GROUP BY I.QUANTITY;
    RETURN TMP_TOTAL;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
/
-- 함수 테스트, SELECT FUNC_ITEM_GROUP_QUANTITY(물품번호)의 재고수량 출력됨.
SELECT FUNC_ITEM_GROUP_QUANTITY(1) FROM DUAL;
SELECT * FROM ITEM;

이후 다음주부터 진행 될 팀별 미니프로젝트의 주제에 대해서 브레인 스토밍을 한 이후, SQL문으로 직접 설계하였고

금일 배운 내용을 활용하여 실습을 진행하였다.

🪐 실습

1. 회원테이블 생성

-- 회원관련 테이블 생성
CREATE TABLE memberTB(
  mem_name      VARCHAR2(30),
  mem_id        VARCHAR2(15) ,
  mem_pw        VARCHAR2(20) ,
  mem_phone     VARCHAR2(15) ,
  mem_address   VARCHAR2(30),
  mem_block_chk NUMBER (1),
  mem_quit_chk NUMBER (1),
  mem_regdate timestamp,
  CONSTRAINT PK_memberTB PRIMARY KEY (mem_id)
);
COMMIT;

2. 회원등록

  • (PL/SQL 반복문을 사용)
DECLARE
BEGIN
    FOR i IN 1..5 LOOP
        INSERT INTO MEMBERTB( mem_id, mem_pw, mem_name, mem_phone, mem_address, mem_block_chk, mem_quit_chk, mem_regdate )
        VALUES( 'id_'||i , 'pw', 'TEST_MEMBER'||i, '010-', 'adr', 1, 1, current_date );
    END LOOP;
    COMMIT;
EXCEPTION WHEN OTHERS THEN -- 예외처리 (try catch문, throw문)
     ROLLBACK;
END;
/

3. 회원정보 수정

  • (아이디 "id_1"의 이름을 '바밤바'로)
-- 회원수정
UPDATE memberTB SET mem_name = '바밤바' WHERE mem_id = 'id_1';

4. 회원탈퇴

  • (UPDATE를 사용하여 정보 null화)
UPDATE memberTB 
	SET mem_pw = '',
		mem_name='',
		mem_phone='',
		mem_block_chk=0, 
        mem_address='',
        mem_quit_chk=0, 
        mem_regdate=null 
        
WHERE mem_id = 'id_2';

4. 회원 1명조회

  • (조회시 암호, 가입일자는 불러오지 않음, 아이디 "id_3"의 정보 조회)
SELECT 
	m.mem_id, 
	m.mem_name,
    m.mem_phone,
    m.mem_address,
    m.mem_regdate,
    m.mem_block_chk, 
    m.mem_quit_chk 
FROM memberTB m 
WHERE m.mem_id = 'id_3';

5. 회원 이름에 검색어가 포함된 항목 조회

  • 이름에 'TEST'라는 글자를 포함하는 데이터의 항목들을 모두 조회
SELECT * FROM memberTB WHERE mem_name LIKE 'TEST%';

6. 등록시간에서 '분'이 일치하는 항목 회원 조회

SELECT M.* FROM MEMBERTB M WHERE EXTRACT(MINUTE FROM mem_regdate) = 6;

7. 문자를 입력받으면 3자리만 반환하는 함수 생성

  • (FUNC_STR, 내부함수 SUBSTR 사용)
CREATE OR REPLACE FUNCTION func_str( in_str VARCHAR2 ) RETURN VARCHAR2
IS
    tmp_str VARCHAR2(30);
BEGIN
    SELECT SUBSTR(in_str, 1, 3) INTO tmp_str FROM DUAL;
    RETURN tmp_str;
EXCEPTION WHEN OTHERS THEN
    RETURN null;
END;
/
-- 함수 사용해서 정보 출력
SELECT func_str(mem_id)|| '*****', MEM_NAME, MEM_PHONE, MEM_ADDRESS, MEM_REGDATE, MEM_BLOCK_CHK, MEM_QUIT_CHK FROM MEMBERTB;

3째자리 이후부터 ***로 출력하도록 코드를 수정함.



8. 검색어와 페이지를 전달하면 검색어에 해당하는 회원만 조회

  • (페이지당 5개씩) 
-- 검색어와 페이지를 전달하면 검색어에 해당하는 회원만 조회 (페이지당 5개씩)
SELECT *
FROM (SELECT ROWNUM AS RNUM, M.* FROM MEMBERTB M WHERE M.mem_name LIKE 'TEST%')
WHERE RNUM BETWEEN 1 AND 2;
profile
Jun_Gyu
시작은 미약하지만, 그 끝은 창대하리라
이전 포스트

빅데이터 Java 개발자 교육 - 20일차 [Oracle 5번째 시간 (테이블뷰 만들기 및 GROUP BY 실습)]

다음 포스트

빅데이터 Java 개발자 교육 - 22일차 [Oracle 7번째 시간 (프로시저)]

0개의 댓글