Posted Updated  minkuen SQL / PL/SQL4 minutes read (About 640 words)

PL/SQL - ML

사전준비

  • 두 파일을 다운로드

hr.csv

pl_sql_ml_modeling.sql

  • SQL Developer에서 데이터 임포트한다.

    • hr.csv를 임포트해야 한다.

  • 새 SQL 워크시트에서 pl_sql_ml_modeling.sql 내용을 넣는다.

    • 메모장으로 열어서 내용을 복사하고 워크시트에 붙이면 된다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
-- 의사 결정 트리 모델로 퇴사자 예측 모델 만들기
-- 데이터셋 : https://www.kaggle.com/datasets/pankeshpatel/hrcommasep
-- 기존에 있을지도 모를 HR 데이터 삭제
-- 퇴사 여부 예측 (LEFT)
 
SELECT COUNT(*) FROM HR_DATA_MAIN;
-- 14999

-- 훈련 데이터와 테스트 데이터로 분리
DROP TABLE HR_DATA_TRAINING; 
CREATE TABLE HR_DATA_TRAINING
AS
   SELECT *
     FROM HR_DATA_MAIN
     WHERE EMP_ID < 10500;

DROP TABLE HR_DATA_TEST;

CREATE TABLE HR_DATA_TEST
AS
   SELECT *
     FROM HR_DATA_MAIN
     WHERE EMP_ID >= 10500;

-- 머신 러닝 모델의 환경설정을 위한 정보가 들어있는 테이블을 생성합니다. 
-- URL : https://docs.oracle.com/database/121/ARPLS/d_datmin.htm#ARPLS192
DROP TABLE DTSETTINGS;
CREATE TABLE DTSETTINGS
AS
SELECT *
  FROM TABLE (DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
  WHERE SETTING_NAME LIKE '%GLM%';

BEGIN
  INSERT INTO DTSETTINGS
    VALUES ('ALGO_NAME', 'ALGO_DECISION_TREE');

  INSERT INTO DTSETTINGS
     VALUES (DBMS_DATA_MINING.TREE_IMPURITY_METRIC, 'TREE_IMPURITY_ENTROPY'); -- 모델의 핵심엔진은 엔트로피로 설정
COMMIT;
END;
/

-- 머신 러닝 모델을 생성합니다.
BEGIN
  DBMS_DATA_MINING.DROP_MODEL('DT_MODEL');
END;
/

BEGIN
   DBMS_DATA_MINING.CREATE_MODEL (
      MODEL_NAME            => 'DT_MODEL',
      MINING_FUNCTION       => DBMS_DATA_MINING.CLASSIFICATION,
      DATA_TABLE_NAME       => 'HR_DATA_TRAINING',
      CASE_ID_COLUMN_NAME   => 'EMP_ID',
      TARGET_COLUMN_NAME    => 'LEFT',
      SETTINGS_TABLE_NAME   => 'DTSETTINGS');
END;
/

-- 5. 생성된 모델을 확인합니다.
SELECT MODEL_NAME,
          ALGORITHM,
          MINING_FUNCTION
  FROM ALL_MINING_MODELS
  WHERE MODEL_NAME = 'DT_MODEL';

-- 6. 생성된 모델의 환경설정 내용을 확인합니다. 
SELECT SETTING_NAME, SETTING_VALUE
  FROM ALL_MINING_MODEL_SETTINGS
  WHERE MODEL_NAME = 'DT_MODEL';

-- 7. 실제 값과 예측 값과 예측 확률을 출력합니다. 
SELECT EMP_ID, T.LEFT 실제값,
          PREDICTION (DT_MODEL USING *) 예측값,
          PREDICTION_PROBABILITY (DT_MODEL USING *) "모델이 예측한 확률"
  FROM HR_DATA_TEST T;

-- 8. 학습한 머신러닝 모델의 성능을 확인합니다.
DROP TABLE HR_DATA_TEST_MATRIX_2;
CREATE OR REPLACE VIEW   VIEW_HR_DATA_TEST
AS
SELECT EMP_ID, PREDICTION(DT_MODEL USING *) PREDICTED_VALUE,
          PREDICTION_PROBABILITY(DT_MODEL USING * ) PROBABILITY
  FROM HR_DATA_TEST;
  
SET SERVEROUTPUT ON 

DECLARE
   V_ACCURACY NUMBER;
BEGIN
   DBMS_DATA_MINING.COMPUTE_CONFUSION_MATRIX (
      ACCURACY           => V_ACCURACY,
      APPLY_RESULT_TABLE_NAME      => 'VIEW_HR_DATA_TEST',
      TARGET_TABLE_NAME       => 'HR_DATA_TEST',
      CASE_ID_COLUMN_NAME       => 'EMP_ID',
      TARGET_COLUMN_NAME       => 'LEFT',
      CONFUSION_MATRIX_TABLE_NAME => 'HR_DATA_TEST_MATRIX_2',
      SCORE_COLUMN_NAME       => 'PREDICTED_VALUE',
      SCORE_CRITERION_COLUMN_NAME => 'PROBABILITY',
      COST_MATRIX_TABLE_NAME      => NULL,
      APPLY_RESULT_SCHEMA_NAME    => NULL,
      TARGET_SCHEMA_NAME       => NULL,
      COST_MATRIX_SCHEMA_NAME     => NULL,
      SCORE_CRITERION_TYPE       => 'PROBABILITY');
   DBMS_OUTPUT.PUT_LINE('**** MODEL ACCURACY ****: ' || ROUND(V_ACCURACY,4));
END;
/
  • Reference : 오라클 SQL과 PL/SQL을 다루는 기술
Posted Updated  minkuen SQL / PL/SQL7 minutes read (About 1068 words)

PL/SQL practice02

  • PL/SQL은 일반 프로그래밍 언어에서 제공하는 많은 기능이 탑재되어 있다.
  • 다른 프로그래밍 언어와 다른 점은 PL/SQL은 DB에 직접 탑재되어 컴파일되고 실행되어 성능 면에서도 우수하고, DB 관련 처리를 할 때 수많은 기능을 제공한다

프로시저

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 프로시저
-- 함수와 다르게 로직 처리만 수행 / 결괏값 반환 (X)
-- p.290
SELECT * FROM JOBS;

-- 프로시저 생성
CREATE OR REPLACE PROCEDURE my_new_job_proc
(p_job_id IN JOBS.JOB_ID%TYPE
 , p_job_title IN JOBS.JOB_TITLE%TYPE
 , p_min_sal IN JOBS.MIN_SALARY%TYPE
 , p_max_sal IN JOBS.MAX_SALARY%TYPE
)
IS
BEGIN
    INSERT INTO JOBS(job_id
                     , job_title
                     , min_salary
                     , max_salary
                     , create_date
                     , update_date) 
    VALUES(p_job_id, p_job_title, p_min_sal, p_max_sal, SYSDATE, SYSDATE);
    
    COMMIT;
END;

프로시저 실행

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- 프로시저 실행
-- exec
EXEC my_new_job_proc ('SM_JOB1', 'Sample JOB1', 1000, 5000);

-- 확인
SELECT * 
FROM jobs
WHERE job_id = 'SM_JOB1';

-- 프로시저 업데이트 
-- 끊기면 안되고, 계속 실행이 되어야 함

CREATE OR REPLACE PROCEDURE my_new_job_proc
(p_job_id IN JOBS.JOB_ID%TYPE
 , p_job_title IN JOBS.JOB_TITLE%TYPE
 , p_min_sal IN JOBS.MIN_SALARY%TYPE
 , p_max_sal IN JOBS.MAX_SALARY%TYPE
)
IS
    vn_cnt NUMBER := 0;
BEGIN
    
    -- 동일한 JOB_ID가 있는지 체크
    SELECT COUNT(*)
        INTO vn_cnt
    FROM JOBS
    WHERE job_id = p_job_id;
    
    -- 없으면 INSERT 
    IF vn_cnt = 0 THEN 
        INSERT INTO JOBS(job_id
                     , job_title
                     , min_salary
                     , max_salary
                     , create_date
                     , update_date) 
        VALUES(p_job_id, p_job_title, p_min_sal, p_max_sal, SYSDATE, SYSDATE);
    ELSE -- 있으면 UPDATE
        UPDATE JOBS 
            SET job_title    = p_job_title
            ,   min_salary   = p_min_sal
            ,   max_salary   = p_max_sal
            ,   update_date  = SYSDATE
        WHERE job_id = p_job_id;
    END IF;
    COMMIT;
END;

프로시저 실행

1
2
3
4
5
6
7
-- 프로시저 실행
-- exec
EXEC my_new_job_proc ('SM_JOB1', 'Sample JOB1', 1000, 5000);

SELECT * 
FROM jobs
WHERE job_id = 'SM_JOB1';
1
2
3
-- p.293
-- 한줄로 실행. (이유 모름)
EXECUTE my_new_job_proc (p_job_id => 'SM_JOB1', p_job_title => 'Sample JOB1', p_min_sal => 2000, p_max_sal => 7000);
1
SELECT * FROM jobs WHERE job_id = 'SM_JOB1';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- 매개변수 디폴트 설정
CREATE OR REPLACE PROCEDURE my_new_job_proc
(p_job_id IN JOBS.JOB_ID%TYPE
 , p_job_title IN JOBS.JOB_TITLE%TYPE
 , p_min_sal IN JOBS.MIN_SALARY%TYPE := 10
 , p_max_sal IN JOBS.MAX_SALARY%TYPE := 100
)
IS
    vn_cnt NUMBER := 0;
BEGIN
    
    -- 동일한 JOB_ID가 있는지 체크
    SELECT COUNT(*)
        INTO vn_cnt
    FROM JOBS
    WHERE job_id = p_job_id;
    
    -- 없으면 INSERT 
    IF vn_cnt = 0 THEN 
        INSERT INTO JOBS(job_id
                     , job_title
                     , min_salary
                     , max_salary
                     , create_date
                     , update_date) 
        VALUES(p_job_id, p_job_title, p_min_sal, p_max_sal, SYSDATE, SYSDATE);
    ELSE -- 있으면 UPDATE
        UPDATE JOBS 
            SET job_title    = p_job_title
            ,   min_salary   = p_min_sal
            ,   max_salary   = p_max_sal
            ,   update_date  = SYSDATE
        WHERE job_id = p_job_id;
    END IF;
    COMMIT;
END;
1
2
EXECUTE my_new_job_proc('SM_JOB1', 'Sample JOB1');
SELECT * FROM jobs WHERE job_id = 'SM_JOB1';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- OUT, IN OUT 매개변수
CREATE OR REPLACE PROCEDURE my_new_job_proc
(p_job_id IN JOBS.JOB_ID%TYPE
 , p_job_title IN JOBS.JOB_TITLE%TYPE
 , p_min_sal IN JOBS.MIN_SALARY%TYPE := 10
 , p_max_sal IN JOBS.MAX_SALARY%TYPE := 100
 , p_upd_date OUT JOBS.UPDATE_DATE%TYPE -- 갱신일자 값을 반환한다! 
)
IS
    vn_cnt NUMBER := 0;
    vn_cur_date JOBS.UPDATE_DATE%TYPE := SYSDATE;
BEGIN
    
    -- 동일한 JOB_ID가 있는지 체크
    SELECT COUNT(*)
        INTO vn_cnt
    FROM JOBS
    WHERE job_id = p_job_id;
    
    -- 없으면 INSERT 
    IF vn_cnt = 0 THEN 
        INSERT INTO JOBS(job_id
                     , job_title
                     , min_salary
                     , max_salary
                     , create_date
                     , update_date) 
        VALUES(p_job_id, p_job_title, p_min_sal, p_max_sal, SYSDATE, SYSDATE);
    ELSE -- 있으면 UPDATE
        UPDATE JOBS 
            SET job_title    = p_job_title
            ,   min_salary   = p_min_sal
            ,   max_salary   = p_max_sal
            ,   update_date  = SYSDATE
        WHERE job_id = p_job_id;
    END IF;
    COMMIT;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- OUT, IN OUT 매개변수
CREATE OR REPLACE PROCEDURE my_new_job_proc
(p_job_id IN JOBS.JOB_ID%TYPE
 , p_job_title IN JOBS.JOB_TITLE%TYPE
 , p_min_sal IN JOBS.MIN_SALARY%TYPE := 10
 , p_max_sal IN JOBS.MAX_SALARY%TYPE := 100
 , p_upd_date OUT JOBS.UPDATE_DATE%TYPE -- 갱신일자 값을 반환한다! 
)
IS
    vn_cnt NUMBER := 0;
    vn_cur_date JOBS.UPDATE_DATE%TYPE := SYSDATE;
BEGIN
    
    -- 동일한 JOB_ID가 있는지 체크
    SELECT COUNT(*)
        INTO vn_cnt
    FROM JOBS
    WHERE job_id = p_job_id;
    
    -- 없으면 INSERT 
    IF vn_cnt = 0 THEN 
        INSERT INTO JOBS(job_id
                     , job_title
                     , min_salary
                     , max_salary
                     , create_date
                     , update_date) 
        VALUES(p_job_id, p_job_title, p_min_sal, p_max_sal, vn_cur_date, vn_cur_date);
    ELSE -- 있으면 UPDATE
        UPDATE JOBS 
            SET job_title    = p_job_title
            ,   min_salary   = p_min_sal
            ,   max_salary   = p_max_sal
            ,   update_date  = vn_cur_date
        WHERE job_id = p_job_id;
    END IF;
    
    -- OUT 매개변수에 갱신 일자 할당
    p_upd_date := vn_cur_date;
    COMMIT;
END;

SET SERVEROUTPUT ON
DECLARE 
    vd_cur_date JOBS.UPDATE_DATE%TYPE; 
BEGIN 
    my_new_job_proc('SM_JOB1', 'Sample JOB1', 2000, 6000, vd_cur_date);
    DBMS_OUTPUT.PUT_LINE(vd_cur_date);
END;
  • Reference : 오라클 SQL과 PL/SQL을 다루는 기술
Posted Updated  minkuen SQL / PL/SQL5 minutes read (About 801 words)

PL/SQL practice01

  • PL/SQL은 일반 프로그래밍 언어에서 제공하는 많은 기능이 탑재되어 있다.
  • 다른 프로그래밍 언어와 다른 점은 PL/SQL은 DB에 직접 탑재되어 컴파일되고 실행되어 성능 면에서도 우수하고, DB 관련 처리를 할 때 수많은 기능을 제공한다

IF문

1
2
3
4
5
6
7
8
9
10
11
12
13
-- IF문

SET SERVEROUTPUT ON
DECLARE
    vn_num1 NUMBER := 1;
    vn_num2 NUMBER := 2;
BEGIN 
    IF vn_num1 >= vn_num2 THEN
        DBMS_OUTPUT.PUT_LINE(vn_num1 || '이 큰 수');
    ELSE
        DBMS_OUTPUT.PUT_LINE(vn_num2 || '이 큰 수');
    END IF;
END;

실행)

Untitled

ELSE IF문

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DECLARE 
    vn_salary NUMBER := 0; -- 변수 초기화 
    vn_department_id NUMBER := 0; -- 변수 초기화 
BEGIN
    vn_department_id := ROUND(DBMS_RANDOM.VALUE (10, 120), -1);
    SELECT salary 
        INTO vn_salary
    FROM employees
    WHERE department_id = vn_department_id
        AND ROWNUM = 1;
    
    DBMS_OUTPUT.PUT_LINE(vn_salary);
    
    IF vn_salary BETWEEN 1 AND 3000 THEN 
        DBMS_OUTPUT.PUT_LINE('낮음');
    ELSIF vn_salary BETWEEN 3001 AND 6000 THEN 
        DBMS_OUTPUT.PUT_LINE('중간');
    ELSIF vn_salary BETWEEN 6001 AND 10000 THEN 
        DBMS_OUTPUT.PUT_LINE('높음');
    ELSE
        DBMS_OUTPUT.PUT_LINE('최상위');
    END IF;
END;

중첩 IF문

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 중첩 IF문
DECLARE 
    vn_salary NUMBER := 0;
    vn_department_id NUMBER := 0;
    vn_commission NUMBER := 0;
BEGIN 
    vn_department_id := ROUND(DBMS_RANDOM.VALUE (10,120), -1);
    SELECT salary, commission_pct
        INTO vn_salary, vn_commission
    FROM employees
    WHERE department_id = vn_department_id
        AND ROWNUM = 1;
    DBMS_OUTPUT.PUT_LINE(vn_salary);
    
    IF vn_commission > 0 THEN 
        IF vn_commission > 0.15 THEN 
            DBMS_OUTPUT.PUT_LINE(vn_salary * vn_commission);
        END IF;
    ELSE
        DBMS_OUTPUT.PUT_LINE(vn_salary);
    END IF;
END;

CASE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- CASE (p.277)
DECLARE 
    vn_salary NUMBER := 0; -- 변수 초기화 
    vn_department_id NUMBER := 0; -- 변수 초기화 
BEGIN
    vn_department_id := ROUND(DBMS_RANDOM.VALUE (10, 120), -1);
    SELECT salary 
        INTO vn_salary
    FROM employees
    WHERE department_id = vn_department_id
        AND ROWNUM = 1;
    
    DBMS_OUTPUT.PUT_LINE(vn_salary);
    
    CASE WHEN vn_salary BETWEEN 1 AND 3000 THEN 
            DBMS_OUTPUT.PUT_LINE('낮음');
        WHEN vn_salary BETWEEN 3001 AND 6000 THEN 
            DBMS_OUTPUT.PUT_LINE('낮음');
        WHEN vn_salary BETWEEN 6001 AND 10000 THEN 
            DBMS_OUTPUT.PUT_LINE('높음');
        ELSE
            DBMS_OUTPUT.PUT_LINE('최상위');
    END CASE;
END;

LOOP 문

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- LOOP
-- 3단
DECLARE 
    vn_base_num NUMBER := 3;
    vn_cnt      NUMBER := 1;
BEGIN 
    LOOP
        DBMS_OUTPUT.PUT_LINE(vn_base_num || '*' || vn_cnt || '= ' 
                             || vn_base_num * vn_cnt);
        vn_cnt := vn_cnt + 1;
        
        EXIT WHEN vn_cnt > 9; -- vn_cnt가 9보다 크면 루프 종료
    END LOOP;
END;

WHILE 문

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- WHILE 
DECLARE 
    vn_base_num NUMBER := 3;
    vn_cnt      NUMBER := 1;
BEGIN
-- 조건식이 참일 때만 실행되는 것
-- 조건식이 거짓이 되면 실행 중지
WHILE vn_cnt <= 9 -- vn_cnt가 9보다 크면 루프 종료
    LOOP
        DBMS_OUTPUT.PUT_LINE(vn_base_num || '*' || vn_cnt || '= ' 
                             || vn_base_num * vn_cnt);
        vn_cnt := vn_cnt + 1;
    END LOOP;
END;

WHILE (EXIT)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- WHILE (EXIT)
-- WHILE 
DECLARE 
    vn_base_num NUMBER := 3;
    vn_cnt      NUMBER := 1;
BEGIN
-- 조건식이 참일 때만 실행되는 것
-- 조건식이 거짓이 되면 실행 중지
WHILE vn_cnt <= 9 -- vn_cnt가 9보다 크면 루프 종료
    LOOP
        DBMS_OUTPUT.PUT_LINE(vn_base_num || '*' || vn_cnt || '= ' 
                             || vn_base_num * vn_cnt);
                             
        -- Break (= EXIT)
        EXIT WHEN vn_cnt = 5; -- vn_cnt값이 5가 되면 루프 종료
        vn_cnt := vn_cnt + 1;
    END LOOP;
END;

FOR-LOOP

1
2
3
4
5
6
7
8
9
10
-- FOR-LOOP 
-- 인덱스값
DECLARE
    vn_base_num NUMBER := 3;
BEGIN 
    FOR i IN 1..9
    LOOP 
        DBMS_OUTPUT.PUT_LINE (vn_base_num || '*' || i || '= ' || vn_base_num * i);
    END LOOP;
END;

CONTINUE

1
2
3
4
5
6
7
8
9
10
-- CONTINUE
DECLARE
    vn_base_num NUMBER := 3;
BEGIN 
    FOR i IN 1..9
    LOOP
        CONTINUE WHEN i = 5;
        DBMS_OUTPUT.PUT_LINE (vn_base_num || '*' || i || '= ' || vn_base_num * i);
    END LOOP;
END;

GOTO 문

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- GOTO문
-- 사용자 정의 함수와 약간 비슷

DECLARE 
    vn_base_num NUMBER := 3;
BEGIN 
    <<third>>
    FOR i IN 1..9
    LOOP
        DBMS_OUTPUT.PUT_LINE (vn_base_num || '*' || i || '= ' || vn_base_num * i);
        IF i = 3 THEN
            GOTO fourth;
        END IF;
    END LOOP;
    
    <<fourth>>
    vn_base_num := 4;
    FOR i IN 1..9
    LOOP
        DBMS_OUTPUT.PUT_LINE (vn_base_num || '*' || i || '= ' || vn_base_num * i);
    END LOOP;
END;

-- NULL문
-- 검색 찾기 링크 참조
  • Reference : 오라클 SQL과 PL/SQL을 다루는 기술
Posted Updated  minkuen SQL / PL/SQL11 minutes read (About 1576 words)

PL/SQL start

PL/SQL

  • SQL은 비절차적 언어
    • 파이썬, R, 자바 등과는 다르다
  • ‘DB 프로그래밍’이라 하면 SQL을 사용한 DML문을 사용하는 것을 지칭할 수도 있지만, 실제 복잡한 비즈니스 로직을 만드는 것은 PL/SQL을 사용해 구현하는 것이 보통이다.
  • PL/SQL은 일반 프로그래밍 언어에서 제공하는 많은 기능이 탑재되어 있다.
  • 다른 프로그래밍 언어와 다른 점은 PL/SQL은 DB에 직접 탑재되어 컴파일되고 실행되어 성능 면에서도 우수하고, DB 관련 처리를 할 때 수많은 기능을 제공한다.

둘째 마당

  • 복잡한 비즈니스 로직을 처리하는 —> 프로그래밍

PL/SQL 기본 구조

  • PL/SQL 소스 프로그램의 기본 단위를 블록(Block)이라고 하는데, 블록은 선언부, 실행부, 예외 처리부로 구성된다.

  • 이 블록은 다시 이름이 없는 블록과 이름이 있는 블록으로 구분할 수 있는데 전자에 속하는 것이 익명 블록이며, 함수, 프로시저, 패키지 등이 후자에 속한다.

  • 오라클 SQL과 PL/SQL을 다루는 기술 260p

  • 이름이 없는 익명 블록anonymous block을 사용해 PL/SQL 코드를 작성해 보자.

  • 먼저 변수를 선언하고 값을 할당해 이 값을 출력하는 익명 블록을 만든다.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- PL/SQL
-- 블록 단위로 진행
-- 선언부
SET SERVEROUTPUT ON
SET TIMING ON       -- 경과시간 확인
DECLARE
    vi_num NUMBER;  -- 변수 선언
BEGIN
    -- 실행 (코드 실행)
    vi_num := 100;
    DBMS_OUTPUT.PUT_LINE(vi_num);
END; -- 블록 종료
-- / PL/SQL 자체가 종료
  • 만약 현재 로그온 한 SQLPlus를 종료하고 다시 접속한다면 “SET SERVEROUTPUT ON”이란 명령어를 다시 실행해야 출력 결과를 볼 수 있다.
  • 참고로 SQLPlus 상에서 PL/SQL 블록을 실행했을 때 총 소요시간도 알 수 있는데, 이를 위해서는 SET TIMING ON 명령어를 실행하면 된다.

실행)

Untitled

상수

  • 상수는 변수와는 달리 한 번 값을 할당하면 변하지 않는다
  • 상수명 CONSTANT 데이터타빙 : = 상수값;
1
2
3
4
5
6
-- p.264
DECLARE
    a INTEGER := 2**2*3**2; -- 4 * 9
BEGIN
    DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));
END;

실행)

Untitled

DML문

  • SQL문 중 DDL은 PL/SQL 상에서 직접 쓸 수 없고 DML문만 사용한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- DM문
-- PL/SQL 테이블과 연동해서 특정 로직을 처리하는 것

DECLARE
    vs_emp_name VARCHAR2(80); -- 사원명 변수
    vs_dep_name VARCHAR2(80); -- 부원명 변수
BEGIN
    SELECT a.emp_name, b.department_name
        INTO vs_emp_name, vs_dep_name
    FROM employees a
        , departments b
    WHERE a.department_id = b.department_id
        AND a.employee_id = 100;
    DBMS_OUTPUT.PUT_LINE(vs_emp_name || ' - ' || vs_dep_name);
END;

실행)

Untitled

데이터 타입

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
    vs_emp_name employees.emp_name%TYPE; -- 사원명 변수
    vs_dep_name departments.department_name%TYPE; -- 부원명 변수
BEGIN
    SELECT a.emp_name, b.department_name
        INTO vs_emp_name, vs_dep_name
    FROM employees a
        , departments b
    WHERE a.department_id = b.department_id
        AND a.employee_id = 100;
    DBMS_OUTPUT.PUT_LINE(vs_emp_name || ' - ' || vs_dep_name);
END;

실행)

Untitled

데이터 크기

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- SQL과 PL/SQL에서 사용할 수 있는 데이터 크기는 다름
-- PL/SQL > SQL보다 더 큰 크기로 사용 가능
DROP TABLE ch08_varchar2;
CREATE TABLE ch08_varchar2(
    VAR1 VARCHAR2(4000)
);

INSERT INTO ch08_varchar2 (VAR1)
VALUES ('tQbADHDjqtRCvosYCLwzbyKKrQCdJubDPTHnzqvjRwGxhQJtrVbXsLNlgeeMCemGMYpvfoHUHDxIPTDjleABGoowxlzCVipeVwsMFRNzZYgHfQUSIeOITaCKJpxAWwydApVUlQiKDgJlFIOGPOKoJsoemqNbOLdZOBcQhDcMLXuYjRQZDIpgpmImgiwzcLkSilCmLrSbmFNsKEEpzCHDylMvkYPKPNeuJxLvJiApNCYzrMcflECbxwNTKSxaEwVvCYnTnFfMFgDqxobWcSmMJrNTQIVOeWlPaMTfRHsrlFSukppmljmOojPSgJiSbQcgtWWOwUNNYFGtgCGBsIcTGAiHWBxtYVXecoJgJCAJptIVmVTZSKliRLoPYTIUpksBuQaqFHLhCkosWChoMjbqgLtBIRBynsKjKiLrdeHVvZanNVElDjLWwlCDhbpsAVQMTzjzhoKIJBdthynMBMVjeNmsKAjdAYhPZKmuKOuMloQdkqPjoKbfjDEeATciMrXiMQorMhYmBlMODBbyLLIkbmtZdPcWGSuxFEUwXnWpvnunEgcLelSneRIpgRNTzTkHqgLbpxoHzCYgSWlIAvKljCnmWiPWGGwlUFOudRSdoqUxntyhNYEiVXtMObywEltTImawnElpmeiWwlTjGTFceqyjhNqiDLxwduubykWzDmFSJNvVvDZibrCpAReqQjlQZcxuVqjKGKvoDuEcQPQeDzmdMYSOTIQdPDNfDffCOUWflHSQhvVTiYumBQIoyznWNITGZkefknJpGEutUnhBgLPQTWTBeTYccqlLrxvRjfJpdpfVDqqfKCngemIEDDHNdvBxCqKDTrrJAumXMKgpWLIHctQuACeNaKnffpYXiioLxZDrxpuZPPUGpRsCtoQuBfogkKuusVATkMyajKTPSyTQbfhZepRjNdrhkymqKvsAcThYbMSMnkKcLWFPAMeGysBVKkQtFMPvRBoDszlSZcMYzwxkKQwJnuVnDxShYiHFlzgDWqhZoqeypyFVBNDtHkiVzHkQisYLbsbVneJyHbHdtaIFLVbfTqbkGQTEjFlPiGUddPUIoLWALrbKcLwBizwhJvaXkvOphcGWpdNAhxgehCvjcQFSFhxrBuANKjyWncWAUpKKJcfQCsQlLfpqdMhjWGkAMMWUaDfCrGtmtkiIZOdNapEnvfFKiHAhBhejgKSuyKXFQXyCaLwwvonHsceJKgjtnYVZvBCYYBSqNCqVqCGewootJJsqrCnmiteMZBbyMPnIrdcielnGUYmwiOPmEqKGvxDmDRTDRumnSRcnvgxLbaiQIuzdslEIMquvvwmvgaumqPkduNyfRtXErCPvDYLelhjNNOjbGryRpTtDHxIJebMEtKryUyZRIdADeTEBExwHMRHzAYFizYiesaMhNIsOUzUTmyEMuFQrsUEtjwhUWIvADNlrcxPZwRazPMMvdVZssmXbXuCkRoPYNGLPwUmrWrrIgQoMSGMPvTcbHnbtleyKYmOMgymANQBZDMoqAOzMHrAVunIiykCudFVNObNgXOoyfQRICbFsWygSZXufipvrWWmRnBWYdoKmIRewOObUjiNDdQsxQIXtlbPSSngfQPfeQKOolVASXIuAmeODKtSOPaEaFKcedGzzsbrPlsPnRRuYFeVdhyufpjFVVrTPczSQkmPYXercLMmVEaDmJXKTqEVNSKeOshDCDJwdINFsLhAuKIIfOdjSEndDwumQLvePVjzNoIfUELOANeshoNgwVhFADjtUIjIhQAIyRnzSoxSRSWklITMgdjQZTthwsnBVLWyfSsAdLzOnEqmMCGBlTYGjtqvKbBoATRwkPkOTSbUhZClVzjiLLIFEMuptuodeRKXUaBfUhVTtasFsZdVnKtEfLldJYsxjlrBADRqhEBEmBKxlXKgEhiKcwAdztcETMUteJwadfaZLEBRjwJOGaIMhsfAxtuBQWyQLGXPDlFQmkcMsKsGUlQBEAubDqbuBYqXLZgmhPftLkYaCYGReLCVXssOxzJFJwnxKJzaaYzfVpbHYBtiBeQZRilJZqrrMTrVtYAcwGxAAddwtlxzdZebfZHjzqRmrrBPNbkVHqjCHtVKUjIDPVSrtyEsPRPoyyPOFOSBcgClTzlAIPmPMkdlpFHctzKGpyQMInMwPKojVErCOrHbCsZoEXqyOcHReSybmxwYabyioVnDxPEvskutVHLWQTNudmKICoaoSGKqONrBmvtGNBKAaJxCRKTDOIqrJOsQVOmGxmuIDEddVYvDwILTyushOAiXbkRIKgNLnFJdOagmiOHKRBKIIkxkOUeZWMRNlqpJdFgKjrGhIzrgBtgjVOtZAskKRbqzRVwLUoUAtRpRkoRQNLIrbLmmjZTugXJBNCscnMguKVAFDKpODtCsmdlBvQGALeBGUitYBxLYhJxeVcAnTWmTAvCITzdzqiBfEudEIBmkDAXIFmoOmsTMZDOnhXYrgMDlDbjednYWWJbGhrXFrxMQmQSmRBwoOqWGbGmjZNlJCvSHvmtZUkIScWXVdfSsdvdyQNpGFIOuteXhCMLmmEHrMucEmFbCIOHTJINAuIUOPfAfijIPkZjppGCCSRJNXWNCmliwUgABkHWuelUWeLsyVKVcZWOSeiQBQibCQJQUgGkTrXZxdBLsgjeMIwOyORDBpywuvlrLScRNhvaCYaKKRvOZeqBebUWWFhNnIRJvedFNfFPgWZJgNRaUpyYWFNiXJfAqNjyCEQYwAdFBQKKolwrufmJOfrToJFEsoNjaphcNvfWGIjKrKZSoSJEsbRqNVcoprpcGrnBgcNAnWUFpRldcPJkPfaoLKRCmVyMAWMXmnScodKisCTqllZEWQQSCFETxLNntgdcFEFRsTSIhuewwrHIlOeCcRqkzgQhKnKyHZHdFsMEKvPywLbjaspVxUMEkVzCGcGoTmaBjUMwJuAYdSTaYGDHHWDrvGgMVTtehpzfgofkmqtamffJbCKOzJgPsHNEnFarjADJGyKLwwitCiBXIraUdZtZwNjUtGbWqxksepVYztIBrimByoYQfUQgOndzFmhnuSmhYWvHliWUHgbvBIkYasDElNsjcCLtMvjQEhJjWvlnAscPwOYfelrfgfRAZGBxdFlMNkfYEWLbkfUhbRPHoDZsaAQdoKhAAWzOcHoAkkHPQMNIxgHNJaqEFBqCuMYEtLpMnIiMCWWEPnBYgYrxlXFGYpQWUNFevwcEUvUzDeSZNrdmahAfjeLSAGjHVnqyTzJkiVXjDJXzOiszXQCErQwwDMMqjLxWebJwNAVdrXeyMDRYXmLMDnuWLVaShVGhlgvbjOdOnhCDTNVazYDnzstqxjOuWbLcDaavRumKUOQXBQwKtdFgOzXiQKWFporrIcylIHlTmTKAIpBqNUbkajLTlwAHieCcqPIJYhegwQhWpYZdfxpQXDKtYzsrmnvdiTKgXfXKlIHPHlxQtqXGhMVPOBAKVZJfkrDNEwnQFwgfoHJSqQxTzRswVLrtFgpVzKcLilgznElWUfhERyeUrCcFCuGJddlFHJrXsqRdUjqUwaBmJVNwjRbCFiVMOSFuNctNVzhmhUpoddsMPUFMvNIMsMjHIWYiLjhSajZqpDkMvUOUCbYKfNHGpdUeWGUtDXHDNSCEXqYrhWhvnISnjfoBMCwwptksarPImRZaRxBMjoBdlmRGlIuQZDzCLnxxioATnGVFFTATUpeypOCaCeJAvPLxEXYzlCgXvXirGSZFyZPPSCdOSHxeELRsetFrWgqPNNpwgbgBEYPOSpLWeVdqOxPaQnidyPVMmELzeJPWgNsWBdPJPjhkdGpeAYZfrBNqdbOwzbtLiWMPafjgWQNcWKqmcleWLcMJoGSAEIUyFuzElZKXonHOMDdGMtSKEFUWdfPfnDecKNhIjAKRYmkXgpPAzlKIOpViZPkZdozzAoWwDnXkfDikvkXcQaoBtzKkcRhNpJRYaGTkdnlfotsJZsLqpYaWoK');

commit;
    
DECLARE
   vs_sql_varchar2   VARCHAR2(4000);
   vs_plsql_varchar2 VARCHAR2(32767);
BEGIN

    -- ch08_varchar2 테이블의 값을 변수에 담는다. 
    SELECT VAR1 
        INTO vs_sql_varchar2
    FROM ch08_varchar2;
     
    -- PL/SQL 변수에 4000 BYTE 이상 크기의 값을 넣는다. 
    vs_plsql_varchar2 := vs_sql_varchar2 || ' - ' || vs_sql_varchar2 || ' - ' || vs_sql_varchar2;
  
    -- 각 변수 크기를 출력한다. 
    DBMS_OUTPUT.PUT_LINE('SQL VARCHAR2 길이 : ' || LENGTHB(vs_sql_varchar2)); 
    DBMS_OUTPUT.PUT_LINE('PL/SQL VARCHAR2 길이 : ' || LENGTHB(vs_plsql_varchar2)); 
END;

Untitled

두 수의 합

1
2
3
4
5
6
7
8
9
10
-- 두 수의 합
accept p_num1 prompt '첫번째 숫자를 입력하세요 ~ '
accept p_num2 prompt '두번째 숫자를 입력하세요 ~ '

DECLARE
    v_sum number(10);
BEGIN
    v_sum := &p_num1 + &p_num2;
    DBMS_OUTPUT.PUT_LINE('TOtal : ' || v_sum);
END;

실행) 1 2

Untitled

데이터 입력

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
DROP table emp;
DROP table dept;

CREATE TABLE DEPT
       (DEPTNO number(10),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE EMP (
 EMPNO               NUMBER(4) NOT NULL,
 ENAME               VARCHAR2(10),
 JOB                 VARCHAR2(9),
 MGR                 NUMBER(4) ,
 HIREDATE            DATE,
 SAL                 NUMBER(7,2),
 COMM                NUMBER(7,2),
 DEPTNO              NUMBER(2) );

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'81-11-17',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'81-05-01',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'81-05-09',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'81-04-01',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'81-09-10',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'81-02-11',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'81-08-21',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'81-12-11',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'81-02-23',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'81-12-11',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'80-12-11',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'82-12-22',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'83-01-15',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'82-01-11',1300,NULL,10);

commit;

drop  table  salgrade;

create table salgrade
( grade   number(10),
  losal   number(10),
  hisal   number(10) );

insert into salgrade  values(1,700,1200);
insert into salgrade  values(2,1201,1400);
insert into salgrade  values(3,1401,2000);
insert into salgrade  values(4,2001,3000);
insert into salgrade  values(5,3001,9999);

commit;

조회

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 사원번호를 찾아라
-- 사원번호를 입력하면 해당 사원의 급여가 나오도록 출력하세요!
-- 7782
ACCEPT p_empno prompt '사원 번호를 입력하세요 ~'
DECLARE
    v_sal number(10);
BEGIN
    SELECT SAL INTO v_sal
    FROM emp
    WHERE empno = &p_empno;
    
    DBMS_OUTPUT.PUT_LINE('월급은 '|| v_sal);
END;

실행) 7893

Untitled

조건절

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 조건절 & 반복문
-- 사원 이름을 입력합니다. 받는 급여가 고소득자인지, 중간 소득자, 저 소득자
-- KING, SCOTT
SELECT * FROM emp;

ACCEPT p_ename prompt '사원 이름을 입력하세요 ~ '
DECLARE 
    -- 변수 선언
    v_ename emp.ename%TYPE := upper('&p_ename');
    v_sal emp.sal%TYPE;
BEGIN
    SELECT sal into v_sal
    FROM emp
    WHERE ename = v_ename;
    DBMS_OUTPUT.PUT_LINE('급여 ' || v_sal);
    
    -- 조건식
    IF v_sal >= 3500 THEN 
        DBMS_OUTPUT.PUT_LINE('고 소득자');
    ELSIF v_sal >= 2000 THEN 
        DBMS_OUTPUT.PUT_LINE('중간 소득자');
    ELSE 
        DBMS_OUTPUT.PUT_LINE('저 소득자');
    END IF;
END;

실행) KING

Untitled

반복문

  • 구구단 예제
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 반복문 
-- 구구단! 
2 x 1 = 2 = 2 x 1 
2 x 2 = 4 = 2 x 2
DECLARE 
    v_count number(10) := 0;
BEGIN 
    LOOP
        v_count := v_count + 1;
        DBMS_OUTPUT.PUT_LINE('2 x ' || v_count || ' = ' || 2 * v_count);
        EXIT WHEN v_count = 9;
    END LOOP;
END;
  • Reference : 오라클 SQL과 PL/SQL을 다루는 기술

PL/SQL 실습01

PL/SQL 실습02

PL/SQL - ML

Follow

Links

Categories

Recents

Archives

Tags

HTML6
JPA11
JPA 기초11
Java9
JavaScript1
NiFi1
Notion4
PL/SQL4
R2
R Markdown1
VScord1
Vue.js7
Vue.js 기초7
airflow4
anaconda1
apache1
chatbot5
crawling4
elasticsearch1
git1
github blog1
heroku4
javascript10
kakao_chatbot5
kibana1
machine learning22
oracle15
pipeline1
postgreSQL5
python30
setting14
spark7
wsl21
생활코딩1

Subscribe for updates

You need to set client_id and slot_id to show this AD unit. Please set it in _config.yml.

follow.it

×