Oracle SQL(5)

■ PL/SQL

이름부
IS(AS)
선언부
BEGIN
실행부
EXCEPTION
예회 처리부
END;

PL/SQL 소스 프로그램의 기본 단위를 블록(Block) 이라고 하는데, 블록의 선언부, 실행부, 예외 처리부로 구성된다.
이 블곡을 다시 이름이 없는 블록과 이름이 있는 블록으로 구분할 수 있는데 전자에 속하는 것이 익명 블록 이며, 함수, 프로시저, 패키지 등이 후자에 속한다.

01. 출력

BEGIN
    DBMS_OUTPUT.PUT_LINE('hello world'); --System.out.println("hellow world");
END;

02. 변수선언

DECLARE
    t_str VARCHAR(20) := 'aaa'; -- Sring t_str = "aaa"
    t_num NUMBER(4) := 1234;    -- int t_num = 1234
BEGIN
    DBMS_OUTPUT.PUT_LINE(t_str || ',' || t_num); --System.out.println("aaa"+","+1234)'
END;

03. 조건

 IF 조건 THEN
        t_grade := 'A';        
    ELSIF t_score >= 80 THEN    -- else if(){
        t_grade := 'B';
    ELSIF t_score >= 70 THEN
        t_grade := 'C';
    ELSE
        t_grade := 'D';
    END IF;          

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

03.반목문

FOR 인덱스 IN 초기값.. 최종값
LOOP
처리문;
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;

04. 오라클 내장함수 실행방법

SELECT MOD(15, 2) FROM DUAL; -- MOD 나머지
SELECT CURRENT_DATE FROM DUAL; -- CURRENT_DATE 현재날짜

05. 데이터 추가하기

DECLARE
    t_num NUMBER(2) := 20;
BEGIN
    FOR i IN 1..20 LOOP
    INSERT INTO member(userid, userpw, username, userage, userphone, usergender, userdate)
    VALUES('b' || i, 'pw', 'name', 11+i, '010' , 'M', CURRENT_DATE);
    END LOOP;
    COMMIT;
EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
END;

06. 데이터 조회하기

■ 1번째

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;

■ 2번째(curros이용)

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;

■ 함수 생성

CREATE OR REPLACE FUNCTION 함수이름(매개변수1, 매개변수2,...)
RETURN 데이터타입;
IS[AS]
    변수, 상수 등 선언
BEGIN
   실행부   
RETURN 반환값;
[EXCEPTION WHEN OTHERS THEN
     예외처리부]
END[함수이름];

-- public String funToCharToday() {} -- java
CREATE OR REPLACE FUNCTION func_tochar_today RETURN VARCHAR2 --문자로 리턴하겠다
IS
    t_date VARCHAR(30);
BEGIN
    -- 내장함수 CURRNET_DATE, TO_CHAR(변경날짜, 포멧)
    -- SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD')데이터를 t_date에 넣는다
    SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') INTO t_date FROM DUAL;
    RETURN t_date;
EXCEPTION WHEN OTHERS THEN
     RETURN '';
END;

-- 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;
/

-- 시간대별 판매수량을 반환하는 함수(숫자형의 in_hour 파라미터)
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;