[SQL] for문과 if문 그리고 함수 만들기

PL/SQL에서 반복문 가정문

PL/SQL에서 시작하기

콘솔에 출력환경 변경

기본적으로 PL/SQL은 결과물을 보여주지 않기 때문에 출력환경을 바꿔준다.
결과물을 보고 싶다면 SERVEROUTPUT 설정 을 ON 으로 설정해 주어야 한다.

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

BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
END;

예시1) '안녕세상아'

BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO WORLD'||'!!'); 
END;


--HELLO WORLD!!

예시2) 변수선언

--위에거랑 밑에꺼 실행구분선 (안하면 다출력됨)

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

BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO WORLD'||'!!'); --SYSO (DJwJRN+DJwJRN)
END;
/ --위에거랑 밑에꺼 실행구분선 (안하면 다출력됨)

--변수선언
DECLARE
    t_str VARCHAR2(20):= 'AAA';
    t_num NUMBER(4):= 1234;
BEGIN
    DBMS_OUTPUT.PUT_LINE(t_str || ',' || t_num); 
END;
/

• || 이건 +와 같은거임
  시스템아웃 ("저쩌구"+"어쩌구")

조건문 IF

• if문 구성 : IF ~ THEN 와 ELSIF, ELSE
• := : 그냥 =과 같다. (근.데. 프로시저 안에 if문 있으면 거기선 안씀)

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

'프로시저 안에 if문 있으면 거기선 안씀'

-- SELECT COUNT(*) FROM 테이블
--문제) 회원정보를 전달하면 회원아이디가 존재할 경우 이름 나이 연락처 성별을 업데이트
--존재하지 않으면 추가하기
--프로시저 명칭 proc_nnn_upsert

CREATE OR REPLACE PROCEDURE proc_nnn_upsert(
    in_id IN NNN.ID%TYPE,
    in_name IN NNN.NAME%TYPE,
    in_pw IN NNN.PW%TYPE,
    in_age IN NNN.AGE%TYPE,
    out_ret OUT NUMBER 
)
IS
 tmp_chk NUMBER(1) := 0; --존재유무 확인용 변수
BEGIN
        SELECT COUNT(*) INTO tmp_chk FROM nnn n WHERE n.id = in_id;
    IF tmp_chk = 1 THEN
        UPDATE nnn 
        SET name = in_name, age = in_age, pw =  WHERE id = in_id;
    ELSE 
        INSERT INTO nnn (id, name, pw, age, mdate)
        VALUES(in_id, in_name, in_pw, in_age, CURRENT_DATE);
    END IF;
    COMMIT;
    out_ret := 1;
EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    out_ret := 0;
END;
/

이건 프로시저(Procedure)에서 upsert만든건데
프로시저는 :=를 쓰지만 안에 보면 =만 쓰고 있음.

반복문 FOR

• FOR ~ IN, LOOP, DBMS_OUTPUT.PUT_LINE(), END LOOP로 구성되어 있다

-- 반복문
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;
/
--PL/SQL 종료선언 ('/' 뒤에 주석문 넣으니 오류남)

• DBMS_OUTPUT.PUT_LINE()

자바에서
시스템아웃

예제 : MEMBER

SELECT m.* FROM member m; -- MEMVBER 전체조회


--추가하기
-- 20개의 중복되지 않는 고유키, 유저아이디를 넣어서 20명의 회원을 넣어줄거임
DECLARE
    t_mum NUMBER (2) := 20;    
BEGIN
    FOR i IN 1..t_mum LOOP --변수명까지 i를 증가시킴
        INSERT INTO member( userid, userpw, username, userage, userphone, userdate, usergender)
        VALUES('a' || i, 'pw', 'name', 11+i, '010', CURRENT_DATE, 'M');
        -- 'a' || i,는 스트링타입이라 ||로 붙이고
        -- 11+i,는 숫자타입이라 +로 붙임
    END LOOP; -- 반복종결
    COMMIT; -- 커밋
EXCEPTION WHEN OTHERS THEN --오류처리
    ROLLBACK; --이시점에 롤백
END;
/
-- 수정이나 추가에는 EXCEPTION WHEN OTHERS THEN ROLLBACK; 쓰지만 조회에는 굳이 쓸 필요 없음


--조회하기
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;
/


-- 조회하기 : cursor를 이용
DECLARE
    CURSOR cur IS SELECT m.* FROM member m ORDER BY userid ASC;
BEGIN
    FOR mem IN cur() LOOP
        DBMS_OUTPUT.PUT_LINE(mem.userid || mem.username);
    END LOOP;
END;
/

함수만들기

함수의 형식은 다음과 같다

CREATE OR REPLACE FUNCTION 함수이름(파라미터) RETURN 리턴타입

IS

BEGIN

END

예제1) 오늘날짜 출력해주는 함수 만들기
솔직히 쓸모 없다

--함수만들기
--public string func() {
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;
    RETURN t_date;
EXCEPTION WHEN OTHERS THEN
    RETURN '';
END;
/

SELECT func_tochar_today FROM DUAL;

예제2) 시퀀스 없는 member에 일괄추가 함수 만들기
아주 조금 쓸모있는걸 만들어보자

--일괄추가member
--시퀀스 없음
SELECT m.* FROM member m;

INSERT ALL
INTO member( userid, userpw, username, userage, userphone, userdate, usergender)
        VALUES('B', 'pw', 'name', 11, '010--', CURRENT_DATE, 'F')
INTO member( userid, userpw, username, userage, userphone, userdate, usergender)
        VALUES('B1', 'pw1', 'name1', 11, '010---', CURRENT_DATE, 'F')
INTO member( userid, userpw, username, userage, userphone, userdate, usergender)
        VALUES('B2', 'pw2', 'name2', 11, '010----', CURRENT_DATE, 'F')
INTO member( userid, userpw, username, userage, userphone, userdate, usergender)
        VALUES('B3', 'pw3', 'name3', 12, '010-----', CURRENT_DATE, 'F')
SELECT * FROM DUAL;
COMMIT;

예제3) 시퀀스 있는 item에 일괄추가 함수 만들기

'시퀀스(seq_item_code)의 현재숫자를 가져오는 함수를 만들어보자'
드디어 쓸만한걸 했다.
했나?

--일괄추가item 
SELECT i.* FROM item i;

--시퀀스 있음
-- FUNC_SEQ_ITEM_CODE_NAXTVAL 함수를 만들어줘야함

-- 시퀀스(seq_item_code)의 현재숫자를 가져오는 함수를 만들어보자
CREATE OR REPLACE FUNCTION func_seq_item_code_naxtval RETURN NUMBER
IS
BEGIN
    RETURN seq_item_no.NEXTVAL;
EXCEPTION WHEN OTHERS THEN
    RETURN null;
END;
/

INSERT ALL
INTO item (code, name, price, quantity, content, regdate )
    VALUES ( FUNC_SEQ_ITEM_CODE_NAXTVAL, '품명', 123, 456, '내용------', CURRENT_DATE )
INTO item (code, name, price, quantity, content, regdate )
    VALUES ( FUNC_SEQ_ITEM_CODE_NAXTVAL, '품명', 123, 456, '내용------', CURRENT_DATE )
INTO item (code, name, price, quantity, content, regdate )
    VALUES ( FUNC_SEQ_ITEM_CODE_NAXTVAL, '품명', 123, 456, '내용------', CURRENT_DATE )
INTO item (code, name, price, quantity, content, regdate )
    VALUES ( FUNC_SEQ_ITEM_CODE_NAXTVAL, '품명', 123, 456, '내용------', CURRENT_DATE )
SELECT * FROM DUAL;

예제4) 시간대별 판매수량을 반환 함수를 만들어보자

--시간대별 판매수량을 반환하는 함수를 만들어보자
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;
/

날짜에서 시간만 빼내기 내장함수

-- EXTRACT(파라미터1) CURRNET_TIMESTEMP
SELECT EXTRACT(MINUTE FROM CURRENT_TIMESTAMP)FROM DUAL;

예제1) item 테이블의 물품번호별 재고수량을 반환하는 함수(func_item_group_quantity)

CREATE OR REPLACE FUNCTION func_item_group_quantity(in_code NUMBER) RETURN NUMBER
IS
    tmp_quantity NUMBER:= 0;
BEGIN
    SELECT quantity INTO tmp_quantity FROM item WHERE item.code = in_code;
    RETURN tmp_quantity;
EXCEPTION WHEN OTHERS THEN
    RETURN null;
END;
/

실습

nnn이라는 회원테이블 만들어서

--회원관련 테이블 생성
CREATE TABLE nnn(
id VARCHAR2 (20), name VARCHAR2 (20), pw VARCHAR2(200), age NUMBER(3), mdate TIMESTAMP
);

---------------------------------------

--회원등록 
INSERT ALL
INTO nnn( id, pw, name, age, mdate)
        VALUES('B3GSG5', 'pw', 'name', 11, CURRENT_DATE)
INTO nnn( id, pw, name, age, mdate)
        VALUES('B3FE1', 'pw1', 'name1', 11, CURRENT_DATE)
INTO nnn( id, pw, name, age, mdate)
        VALUES('B23789', 'pw2', 'name2', 11, CURRENT_DATE)
INTO nnn( id, pw, name, age, mdate)
        VALUES('B33FE', 'pw3', 'name3', 11, CURRENT_DATE)
INTO nnn( id, pw, name, age, mdate)
        VALUES('B3415', 'pw4', 'name4', 11, CURRENT_DATE)
SELECT * FROM DUAL;
COMMIT;

--'PL/SQL을 이용한' 회원등록
DECLARE
BEGIN
    FOR i IN 1..5 LOOP
        INSERT INTO member( userid, userpw, username, userage, userphone, usergender, userdate )
        VALUES( 'bb' || i , 'pw', 'name', 11+i , '010', 'M', CURRENT_DATE);   
    END LOOP;
    COMMIT;
EXCEPTION WHEN OTHERS THEN
    ROLLBACK;    
END;
/
COMMIT;
SELECT * FROM nnn;

---------------------------------------

-- 회원수정
UPDATE nnn
set name = 'A', age= 15
WHERE id = 'B';

-- 회원탈퇴(UPDATE를 이용한 데이터 변경)
UPDATE nnn
set name = '', pw = '', age= 0, mdate=''
WHERE id = 'B4';

-- 회원 1명 조회 (암호와 가입일자는 조회시 제거)
SELECT n.id, n.age, n.name FROM nnn n WHERE n.id = 'B1';

-- 회원이름에 검색어가 포함된 항목조회
SELECT n.* FROM nnn n WHERE n.name like '%'||'1'||'%';
COMMIT;

-- 등록시간항목에서 분이 일치하는 회원조회
SELECT n.* FROM nnn n WHERE TO_CHAR(n.mdate,'MI') = 15;

-- 문자를 입력받으면 3자리만 반환하는 함수생성( 함수명: FUNC_STR, 내부함수 : SUBSTR)
CREATE OR REPLACE FUNCTION FUNC_STR(in_id VARCHAR2) RETURN VARCHAR2
IS
    tmp_STR VARCHAR2(20) := '';
BEGIN
   SELECT SUBSTR(in_id, 1, 3) INTO tmp_STR FROM nnn WHERE nnn.id = in_id ;
   RETURN tmp_STR;
EXCEPTION WHEN OTHERS THEN
    RETURN null;
END;
/

-- FUNC_STR을 이용하여 회원아이디를 3자리까지만 표시하여 조회 EX) A1234 => A12
SELECT nnn.name, nnn.id, FUNC_STR(nnn.id) FROM nnn;
SELECT * FROM nnn;

-- 검색어와 페이지를 전달하면 검색어에 해당하는 회원만 조회(페이지당 5씩)
SELECT * 
FROM (SELECT ROWNUM AS RNUM, n.* FROM nnn n WHERE n.name LIKE '%n%')
WHERE RNUM BETWEEN 10 AND 15;
-- 교수님did
SELECT * FROM (
    SELECT n.*, ROW_NUMBER() OVER (ORDER BY id ASC) ROWN FROM nnn n WHERE n.id LIKE '%' || 'B' || '%'
) WHERE ROWN BETWEEN 4 AND 10;