SQL(1)

Jinmin Kim·2020년 2월 6일
sql
0

프로시저

오라클에서의 프로시저는 PL/SQL을 통해 만들어집니다. 자주 사용하는 SQL을 프로시저로 만든 뒤 필요 할때마다 호출,사용하여 작업 효율을 늘릴 수 있습니다. 함수는 특정 연산을 수행한 뒤 결과 값을 반환하지만 프로시저는 특정한 로직을 처리하기만 하고 결과 값은 반환하지 않는 서브 프로그램입니다.

프로시저 생성

프로시저 실행

PL/SQL

프로시저

자주 사용하는 SQL을 프로시저로 만든 뒤 필요 할때마다 호출,사용하여 작업 효율을 늘릴 수 있다.

함수는 특정 연산을 수행한 뒤 결과 값을 반환하지만 프로시저는 특정한 로직을 처리하기만 하고 결과 값은 반환하지 않는 서브 프로그램이다. 즉, 함수를 하나 만든다고 생각하면 된다.

PL/SQL을 왜 사용할까?
1. 대용량 데이터를 연산해야 할 때, WAS등의 서버로 전송해서 처리하려면 네트워크에 부하가 많이 걸릴 수 있다. 이때 프로시져나 함수를 사용하여 데이터를 연산하고 가공한 후에, 최종 결과만 서버에 전송하면 부담을 많이 줄일 수 있다.

  1. 로직을 수정하기 위해 서버를 셧다운 시키지 않아도 된다. 서버에서는 단순히 DB에 프로시저를 호출하여 사용하면 된다.

  2. 쿼리문을 직접 노출하지 않는 만큼, SQL injection의 위험성이 줄어든다.

  3. 블록 단위로 유연하게 사용할 수 있다. 

-- 프로시저 만들기, BEGIN END안에 내가 하고싶은 실행문을 넣으면 된다.
CREATE OR REPLACE PROCEDURE InsertBook(
    myBookID IN NUMBER,
    myBookName IN VARCHAR2,
    myPublisher IN VARCHAR2,
    myPrice IN NUMBER
)
AS
BEGIN
    INSERT INTO Book(bookid, bookname, publisher, price)
        VALUES(myBookID, myBookName, myPublisher, myPrice);
END;
-- 실행문 및 확인
EXEC InsertBook(13,'스포츠과학', '마당과학서적', 25000);
SELECT * FROM Book;
-- 프로시저 생성
CREATE OR REPLACE PROCEDURE BookInsertOrUpdate(
    myBookID IN NUMBER,
    myBookName IN VARCHAR2,
    myPublisher IN VARCHAR2,
    myPrice INT
)
AS -- mycount NUMBER는 지역변수를 의미한다.
    mycount NUMBER;
BEGIN
    SELECT COUNT(*) INTO mycount FROM Book
    WHERE bookname LIKE myBookName;
    IF mycount !=0 THEN -- 만약 mycount가 0이 아닐경우!
        UPDATE Book SET price = myPrice
        WHERE bookname LIKE myBookName;
    ELSE -- 다른경우
        INSERT INTO Book(bookid, bookname, publisher, price)
        VALUES(myBookID, myBookName, myPublisher, myPrice);
    END IF; -- IF문 멈추기
END;

-- 실행 및 확인 부분
EXEC BookInsertOrUpdate(15, '스포츠 즐거움', '마당과학서적', 25000);
SELECT * FROM Book;

EXEC BookInsertOrUpdate(15, '스포츠 즐거움', '마당과학서적', 20000);
SELECT * FROM Book;
  • 결과 반환하는 프로시저
CREATE OR REPLACE PROCEDURE AveragePrice(
    AverageVal OUT NUMBER -- Default는 IN이나 OUT으로 리턴하게 해준다
)
AS
BEGIN
    SELECT AVG(price) INTO AverageVal FROM Book WHERE price is NOT NULL;
END;

SET SERVEROUTPUT ON;
DECLARE
    AverageVal NUMBER;
BEGIN
    AveragePrice(AverageVal);
    DBMS_OUTPUT.PUT_LINE('책값 평균 : ' || AverageVal);
-- DBMS의 console.log와 같다 (DBMS_OUTPUT.PUT_LINE)
END;
  • 커서 사용 프로시저
CREATE OR REPLACE PROCEDURE Interest
AS
    myInterest NUMERIC;
    Price NUMERIC;
    CURSOR InterestCursor IS SELECT saleprice FROM Orders;
BEGIN
    myInterest := 0.0;
    OPEN InterestCursor; -- 커서 사용 시작
    LOOP --LOOP 반복문
        FETCH InterestCursor INTO Price; --FETCH 행 데이터 가져오기
        EXIT WHEN InterestCursor%NOTFOUND; -- NOTFOUND 라면 LOOP문을 빠져나온다
        IF Price >= 30000 THEN
            myInterest := myInterest + Price * 0.1;
        ELSE
            myInterest := myInterest + Price * 0.05;
        END IF;
    END LOOP;
    CLOSE InterestCursor;
    DBMS_OUTPUT.PUT_LINE('전체 이익 금액 = ' || myInterest);
END;
-- 실행
SET SERVEROUTPUT ON;
EXEC Interest;
  • 서브쿼리 프로시저
SET SERVEROUTPUT ON;
DECLARE
BEGIN
    FOR ID_LIST IN 
    (
        SELECT 'GOD' AS USER_ID FROM DUAL
    )
    LOOP
    DBMS_OUTPUT.putline(ID_LIST.USER_ID);
END LOOP;
END;

LOOP

  • LOOP
SET serveroutput ON; -- output을 사용하기위한
DECLARE -- 선언부
    v_num NUMBER := 6;
    v_tot_num NUMBER := 0;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('현재 숫자 :' || v_num);
        v_num := v_num + 1;
        v_tot_num := v_tot_num + 1;
        EXIT WHEN v_num > 10;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE(v_tot_num || '번의 LOOP');
END;
  • WHILE_LOOP
SET serveroutput ON; -- output을 사용하기위한
DECLARE
    v_num NUMBER := 6; -- 시작숫자
    v_tot_num NUMBER := 0; -- 총 loop수 반환 변수
BEGIN
    WHILE v_num < 11 LOOP
        DBMS_OUTPUT.PUT_LINE('현재 숫자 : ' || v_num);
        v_num := v_num + 1;
        v_tot_num := v_tot_num + 1;
        -- EXIT WHEN v_num > 10;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(v_tot_num || '번의 LOOP');
END;
  • FOR LOOP
SET serveroutput ON; -- output을 사용하기위한
BEGIN
	FOR i in 1..4 LOOP
	  	if mod(i, 2) = 0 then 
			dbms_output.put_line( i || ' + ');
		else
			dbms_output.put_line( i || ' - ');
		end if;
	END LOOP;
END;

IF - ELSE - END IF

DECLARE
    v_score NUMBER := 90;
BEGIN
  IF v_score >= 90 THEN
    DBMS_OUTPUT.PUT_LINE('점수 : ' || v_score || ', 등급 : A');
  ELSIF v_score >= 80 THEN
    DBMS_OUTPUT.PUT_LINE('점수 : ' || v_score || ', 등급 : B');
  ELSIF v_score >= 70 THEN
    DBMS_OUTPUT.PUT_LINE('점수 : ' || v_score || ', 등급 : C');
  ELSIF v_score >= 60 THEN
    DBMS_OUTPUT.PUT_LINE('점수 : ' || v_score || ', 등급 : D');
  ELSE
    DBMS_OUTPUT.PUT_LINE('점수 : ' || v_score || ', 등급 : F');
  END IF;
END;

SWITCH CASE

DECLARE
    v_grade     CHAR(1) := 'C';
    v_appraisal VARCHAR2(20) ;
BEGIN
  CASE v_grade
  WHEN 'A' THEN
    v_appraisal := 'Excellent';
  WHEN 'B' THEN
    v_appraisal := 'Very Good';
  WHEN 'C' THEN
    v_appraisal := 'Good';
  ELSE
    v_appraisal := 'No such grade';
  END CASE;
  DBMS_OUTPUT.PUT_LINE ('Grade : '|| v_grade) ;
  DBMS_OUTPUT.PUT_LINE ('Appraisal: '|| v_appraisal);
END;

참고문헌(오라클로 배우는 데이터베이스 개론과 실습)
소스코드 출처 : https://goddaehee.tistory.com/102

시퀀스

  • 유일(UNIQUE)한 값을 생성해주는 오라클 객체이다.
  • 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성 할 수 있다.
  • 보통 PRIMARY KEY 값을 생성하기 위해 사용 한다.
  • 메모리에 Cache되었을 때 시퀀스값의 액세스 효율이 증가 한다.
  • 시퀀스는 테이블과는 독립적으로 저장되고 생성된다.

시퀀스 생성

  • START WITH : 시퀀스의 시작 값을 지정한다. n을 1로 지정하면 1부터 순차적으로 시퀀스번호가 증가 한다.
  • INCREMENT BY : 시퀀스의 증가 값을 지정한다. n을 2로 하면 2씩 증가한다. START WITH를 1로, INCREMENT BY를 2로 설정하면 1, 3, 5, 7,.. 이렇게 시퀀스 번호가 증가한다.
  • MAXVALUE : 시퀀스 최대값
  • MINVALUE : 시퀀스 최소값
  • CYCLE|NOCYCLE : 최대값 도달시 순환 여부
  • CACHE | NOCACHE : CACHE 여부, 원하는 숫자만큼 미리 만들어 Shared Pool의 Library Cache에 상주시킨다.

시퀀스 수정

시퀀스 삭제

시퀀스 함수

해당 시퀀스의 값을 증가시키고 싶다면

EVENT_SEQ.NEXTVAL

현재 시퀀스를 알고 싶다면

EVENT_SEQ.CURRVAL

Dual Table

  • first
    select '123' as col from dual
    union all
    select '456' from dual
    union all
    select '789' from dual

  • second
    select * from (
    select '123' as col from dual
    union all
    select '456' from dual
    union all
    select '789' from dual
    )

  • 차트용
    SELECT FROM (
    SELECT TO_CHAR((sysdate - (LEVEL     10) / (246060) ), 'MI:SS') STIME,
    ROUND(DBMS_RANDOM.VALUE(0, 50),0) CPU,
    ROUND(DBMS_RANDOM.VALUE(0, 100),0) MEMORY
    FROM DUAL
    CONNECT BY LEVEL < 10
    )
    ORDER BY STIME;
  • 그리드용
    SELECT CASE
    WHEN SEVERITY = 0 THEN 'NORMAL'
    WHEN SEVERITY = 1 THEN 'WARNING'
    WHEN SEVERITY = 2 THEN 'MINOR'
    WHEN SEVERITY = 3 THEN 'MAJOR'
    WHEN SEVERITY = 4 THEN 'CRITICAL'
    END SEVERITY,
    'SERVER'||LV SERVER_NAME,
    TO_CHAR(sysdate - LV, 'YYYY-MM-DD HH24:MI:SS') as STIME
    FROM (
    SELECT TO_CHAR((sysdate - (LEVEL 10) / (2460*60) ), 'MI:SS') STIME,
    ROUND(DBMS_RANDOM.VALUE(0, 4),0) SEVERITY,
    LEVEL LV
    FROM DUAL
    CONNECT BY LEVEL < 10
    )
profile
Jinmin Kim
Let's do it developer
이전 포스트

정규표현식

다음 포스트

Javascript

0개의 댓글