오라클 PL/SQL 제어문, 반복문, 탈출문

자바에 있는 문법들이 오라클 PL/SQL에도 있다.

 

--IF문

DECLARE

v_num1 NUMBER := 10;

v_num2 NUMBER := 5;

BEGIN

IF

v_num1 >= v_num2

THEN

DBMS_OUTPUT.PUT_LINE(v_num1||'이(가) 큰 수');

ELSE

DBMS_OUTPUT.PUT_LINE(v_num2||'이(가) 큰 수');

END IF;

END;

--ELSIF

DECLARE

v_salary NUMBER := 0;

v_department_id NUMBER := 0;

BEGIN

v_department_id := ROUND(DBMS_RANDOM.VALUE(10,120),-1);

 

SELECT salary

INTO v_salary

FROM employees

WHERE department_id = v_department_id

AND ROWNUM = 1; --첫째 값만 구해서 변수에 저장하기 위해서입니다.

 

DBMS_OUTPUT.PUT_LINE(v_salary);

 

IF v_salary <= 5000 THEN

DBMS_output.put_line('낮음');

ELSIF v_salary <= 9000 THEN

DBMS_output.put_line('중간');

ELSE

DBMS_output.put_line('높음');

END IF;

 

END;

-- CASE문

DECLARE

v_salary NUMBER := 0;

v_department_id NUMBER := 0;

BEGIN

v_department_id := ROUND(DBMS_RANDOM.VALUE(10,120),-1);

 

SELECT salary

INTO v_salary

FROM employees

WHERE department_id = v_department_id

AND ROWNUM = 1; --첫째 값만 구해서 변수에 저장하기 위해서입니다.

 

DBMS_OUTPUT.PUT_LINE(v_salary);

 

CASE

WHEN v_salary <= 5000 THEN

DBMS_output.put_line('낮음');

WHEN v_salary <= 9000 THEN

DBMS_output.put_line('중간');

ELSE

DBMS_output.put_line('높음');

END CASE;

 

END;

--중첩IF문

DECLARE

v_salary NUMBER := 0;

v_department_id NUMBER := 0;

v_commission NUMBER := 0;

BEGIN

v_department_id := ROUND(DBMS_RANDOM.VALUE(10,120),-1);

 

SELECT salary, commission_pct

INTO v_salary, v_commission

FROM employees

WHERE department_id = v_department_id

AND ROWNUM = 1; --첫째 값만 구해서 변수에 저장하기 위해서입니다.

 

 

DBMS_OUTPUT.PUT_LINE(v_salary);

 

IF v_commission > 0 THEN

IF v_commission > 0.15 THEN

DBMS_OUTPUT.PUT_LINE(v_salary * v_commission);

END IF;

ELSE

DBMS_OUTPUT.PUT_LINE(v_salary);

END IF;

 

END;

 

반복문도 돌려봤다.

 

SET SERVEROUTPUT ON;

--3을 10번 출력

DECLARE

v_num NUMBER := 3;

v_count NUMBER := 1;

 

BEGIN

WHILE v_count <= 10

LOOP

DBMS_OUTPUT.PUT_LINE(v_num);

v_count := v_count + 1;

END LOOP;

END;

DECLARE

v_num NUMBER := 3;

v_count NUMBER := 1;

 

BEGIN

WHILE v_count <= 10

LOOP

DBMS_OUTPUT.PUT_LINE(v_num);

EXIT WHEN v_count = 5; --break;

v_count := v_count +1;

END LOOP;

END;

-- FOR문

DECLARE

v_num NUMBER := 3;

BEGIN

FOR i IN 1..10

LOOP

DMBS_OUTPUT.PUT_LINE (v_num || ' x '|| i || ' = ' || v_num * i);

END LOOP;

 

END;

--continue문 (탈출문), 해당 회차를 skip하겠다.

DECLARE

v_num NUMBER := 3;

BEGIN

FOR i IN 1..10

LOOP

CONTINUE WHEN i = 5; -- (i가 5일 때 skip해라)

DMBS_OUTPUT.PUT_LINE (v_num || ' x '|| i || ' = ' || v_num * i);

END LOOP;

 

END;

 

여기까지 배우고, 자바에서 배운 void 메소드와 유사한 프로시저(procedure)을 학습했다.

--리턴이 없는 함수를 소환해 보겠다~!

--특정한 로직을 처리하고 결과값을 반환하지 않는 코드 덩어리 (쿼리)

--PL/SQL에도 값을 전달 받아서 코드를 실행 후 리턴하는 함수가 존재합니다

--하지만 프로시저를 통해서도 값을 리턴하는 방법이 있습니다.

SELECT * FROM jobs;

--매개값(인수)이 없는 프로시저

CREATE PROCEDURE p_test

IS --선언부

v_msg VARCHAR2(30) := 'hello procedure!';

BEGIN --실행부

DBMS_OUTPUT.PUT_LINE(v_msg);

 

END; --끝

EXEC p_Test; --프로시저 호출문

--IN 입력값을 받는 파라미터

DROP PROCEDURE my_new_job_proc;

CREATE 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)

VALUES (p_job_id, p_job_title, p_min_sal, p_max_sal);

COMMIT;

END;

EXEC my_new_job_proc('JOB1', 'test job1', 1000, 5000);

SELECT * FROM jobs

WHERE job_id = 'JOB1';

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

--이미 존재한다면 ALTER, 없다면 CREATE

--매개변수(인수)의 디폴트 값(기본값) 설정은 TYPE 뒤에 := 숫자

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 := 0,

p_max_sal IN jobs.max_salary%TYPE := 1000

)

IS

v_cnt NUMBER := 0;

BEGIN

--동일한 JOB_ID가 있는지부터 체크합니다.

-- 이미 존재한다면 1, 존재하지 않는다면 0 -> 값은 v_cnt에 들어 있다.

 

SELECT COUNT(*)

INTO v_cnt

FROM jobs

WHERE job_id = p_job_id;

 

IF v_cnt = 0 THEN --없다면 INSERT

INSERT INTO jobs (job_id, job_title, min_salary, max_salary)

VALUES (p_job_id, p_job_title, p_min_sal, p_max_sal);

ELSE -- 있다면 UPDATE

UPDATE JOBS

SET job_title = p_job_title,

min_salary = p_min_sal,

max_salary = p_max_sal

WHERE job_id = p_job_id;

END IF;

COMMIT;

END;

EXEC my_new_job_proc('JOB1', 'test job1', 2000, 8000);

EXEC my_new_job_proc('JOB2', 'test job2');

SELECT * FROM jobs

WHERE job_id = 'JOB2';

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

--OUT IN OUT 매개변수(인수) 사용!

--OUT 변수를 사용하면, 프로시저 밖으로 보낼 수 있습니다.

--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 := 0,

p_max_sal IN jobs.max_salary%TYPE := 1000,

P_result OUT VARCHAR2 -- 바깥쪽에서 출력을 하기 위한 변수

)

IS

v_cnt NUMBER := 0;

v_result VARCHAR(100) := '값이 없어서 insert 처리 되었습니다.';

BEGIN

SELECT COUNT(*)

INTO v_cnt

FROM jobs

WHERE job_id = p_job_id;

 

IF v_cnt = 0 THEN --없다면 INSERT

INSERT INTO jobs (job_id, job_title, min_salary, max_salary)

VALUES (p_job_id, p_job_title, p_min_sal, p_max_sal);

ELSE -- 있다면 UPDATE

SELECT p_job_id ||'의 최대 연봉: ' || max_salary || ', ' || '최소 연봉: ' || min_salary

INTO v_result -- 조회 결과를 대입하겠습니다.

FROM jobs

WHERE job_id = p_job_id;

END IF;

 

--OUT 매개변수에 결과를 할당합니다.

p_result := v_result;

END;

DECLARE

str VARCHAR2(100);

BEGIN

--프로시저를 부를 때,

--OUT되는 값을 받을 변수를 하나 더 전달해 주어야 합니다.

 

my_new_job_proc('JOB1', 'test_job1', 2000, 8000, str);

DBMS_OUTPUT.PUT_LINE(str);

 

my_new_job_proc('CEO', 'test_CEO', 10000, 100000, str);

DBMS_OUTPUT.PUT_LINE(str);

END;

--IN, OUT을 동시에 처리

CREATE OR REPLACE PROCEDURE my_parameter_test_proc

(

p_var1 IN VARCHAR2,

-- OUT 변수는 프로시저가 끝나기 전까지 값이 할당되지 않습니다.

-- 프로시저가 끝나야 OUT이 가능합니다.

P_var2 OUT VARCHAR2,

--IN, OUT이 둘 다 가능합니다.

p_var3 IN OUT VARCHAR2

)

IS

BEGIN

DBMS_OUTPUT.PUT_LINE('p_var1: ' || p_var1);

DBMS_OUTPUT.PUT_LINE('p_var2: ' || p_var2); --값이 전달이 안됐어요. (=공백)

DBMS_OUTPUT.PUT_LINE('p_var3: ' || p_var3); --IN의 성질을 가지고 있습니다.

--p_var1 := '결과1'; -- 할당 불가

p_var2 := '결과2';

p_var3 := '결과3';

DBMS_OUTPUT.PUT_LINE('----------------');

END;

DECLARE

v_var1 VARCHAR2(10) := 'value1';

v_var2 VARCHAR2(10) := 'value2';

v_var3 VARCHAR2(10) := 'value3';

BEGIN

my_parameter_test_proc(v_var1, v_var2, v_var3);

 

DBMS_OUTPUT.PUT_LINE('v_var2: ' || v_var2);

DBMS_OUTPUT.PUT_LINE('v_var3: ' || v_var3);

END;

CREATE OR REPLACE PROCEDURE my_new_job_proc

(p_job_id IN jobs.job_id%TYPE,

p_result OUT VARCHAR2)

IS

v_cnt NUMBER := 0;

v_result VARCHAR(100) := '값이 없어서 insert 처리 되었습니다.';

BEGIN

SELECT COUNT(*)

INTO v_cnt

FROM jobs

WHERE job_id = p_job_id;

 

IF v_cnt = 0 THEN

DBMS_OUTPUT.PUT_LINE(p_job_id || '는 테이블에 존재하지 않습니다.');

RETURN; --프로시저 강제 종료

END IF;

 

SELECT p_job_id ||'의 최대 연봉: ' || max_salary || ', ' || '최소 연봉: ' || min_salary

INTO v_result -- 조회 결과를 대입하겠습니다.

FROM jobs

WHERE job_id = p_job_id;

 

--OUT 매개변수에 결과를 할당합니다.

p_result := v_result;

 

END;

DECLARE

str VARCHAR2(100);

BEGIN

my_new_job_proc('abc', str);

DBMS_OUTPUT.PUT_LINE(str);

END;