- 1장 : PL/SQL 정의, 왜 배워야하는지?
- 2장 : PL/SQL 변수
- 3장 : 실행절 작성법
- 4장 : 오라클 DB 와의 상호작용 5가지
- SELECT ..into
- insert
- update
- delete
- merge
- 5장 : if 문과 loop문
SQL에서의 CASE문과 PL/SQL에서의 CASE문의 차이를 이해하기 위해 먼저 SQL로 CASE문을 작성해봅시다.
예제1. (SQL문)
이름, 월급, 부서번호, 보너스를 출력하는데 보너스가 부서번호가 10번이면 6000이 출력되게 하고, 부서번호가 20번, 30번이면 8000이 출력되게 하고 나머지는 1000이 출력되게 하시오
select case when deptno = 10 then 6000
when deptno in(20,30) then 8000
else 1000 end as 보너스
from emp;
✅SQL이 비절차적 언어인데, SQL로 절차적으로 수행된 결과를 볼 수 있게 지원해주는 유일한 함수가 바로 decode
, case
입니다.
그래서 sql의 한계를 pl/sql로 극복할 수 있습니다.
✅ SET VERIFY OFF를 쓰는 이유는 치환변수를 입력했을 때 OLD값, NEW값이 나오는 메세지를 출력되지 않게 하려고!
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
v_grade char(1) := upper('&grade');
v_appraisal varchar2(20);
BEGIN
v_appraisal := case v_grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
ELSE 'No such grade'
END;
dbms_output.put_line ('Grade: ' || v_grade || chr(10) ||'Appraisal: ' || v_appraisal);
END;
/
✅ chr(10)
는 PL/SQL에서의 엔터!
✅ SET VERIFY ON
이라고 하면 아래처럼 &grade에 값이 들어가기 전 코드와 &grade에 값이 들어간 후의 코드가 화면에 나타납니다. SET VERIFY OFF
는 결과값만 나오게하는 SQLplus 명령어입니다!
문제 50. 위 코드에 accept절을 이용해서 grade에 값을 물어보는 메세지가 다음과 같이 되게 하시오
원래 메세지 : grade에 대한 값 입력
수정된 메세지 : 등급을 입력하세요! (등급의 종류: A,B,C)
SET SERVEROUTPUT ON SET VERIFY OFF accept p_grade prompt '등급을 입력하세요! (등급의 종류: A,B,C)' DECLARE v_grade char(1) := upper('&p_grade'); v_appraisal varchar2(20); BEGIN v_appraisal := case v_grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' ELSE 'No such grade' END; dbms_output.put_line ('Grade: ' || v_grade || chr(10) ||'Appraisal: ' || v_appraisal); END; /
문제 51. SQL) emp와 salgrade를 조인해서 이름, 월급, 급여등급(grade)을 출력하시오
select e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal;
문제 52. 이름을 물어보게하고, 이름을 입력해서 해당 사원의 급여등급이 1등급이면 'Excellent'를 출력, 2등급과 3등급이면' Very Good', 4등급과 5등급이면 이면 'Good', 나머지는 'No such grade'
SET SERVEROUTPUT ON SET VERIFY OFF accept p_ename prompt '이름을 입력하세요!' DECLARE v_ename emp.ename%type := upper('&p_ename'); v_message varchar2(20); v_grade salgrade.grade%type; BEGIN select s.grade into v_grade from emp e, salgrade s where e.sal between s.losal and s.hisal and e.ename = v_ename; v_message := case WHEN v_grade = 5 THEN 'Excellent' WHEN v_grade in(3,4) THEN 'Very Good' WHEN v_grade in(1,2) THEN 'Good' ELSE 'No such grade' END; dbms_output.put_line ('Grade: ' || v_grade || chr(10) ||'Appraisal: ' || v_message); END; /
✅ 위와같이 db에서 데이터를 select 해서 프로그래밍을 하는 것을 db프로그래밍이라고 합니다. PL/SQL 은 DB프로그래밍에서 최적화된 프로그램입니다. DB쪽에서 프로그래밍을 할일이 있으면 PL/SQL을 이용하는게 유익합니다. 파이썬이나 자바로 위의 작업을 하는것보다 PL/SQL이 성능도 빠르고 DB보안상의 이슈에 덜 민감합니다.
✅ 위 CASE문에서는 CASE문 종료할 때 END CASE;
로 사용했다. SELECT ... INTO를 사용하면 꼭 END CASE;
사용하자(안하면 에러)
✅ 위 코드는 108이라면 아래의 SELECT절을 실행한다.
예제1.
CASE문 안에 select... into절을 입력한 PL/SQL문
이름을 물어보게 하고, 이름을 입력하면 해당 사원이 근무하는 부서의 인원수가 다음과 같이 출력되게 PL/SQL을 작성합니다.
accept p_ename prompt '이름을 입력하세요!'
declare
v_ename emp.ename%type := upper('&p_ename');
v_cnt number;
v_deptno emp.deptno%type;
begin
select deptno into v_deptno
from emp
where ename = v_ename;
case v_deptno
when 10 then
select count(*) into v_cnt
from emp
where deptno = 10;
when 20 then
select count(*) into v_cnt
from emp
where deptno = 20;
when 30 then
select count(*) into v_cnt
from emp
where deptno = 30;
end case;
dbms_output.put_line(v_ename || '이 근무하는 부서의 인원수는 '|| v_cnt|| '명 입니다.');
end;
문제 53. 위의 코드를 수정해서 다음과같이 수행되게 하시오
(CASE문에 SELECT .. INTO절을 하나 더 코딩하기)
이름을 입력하세요! -> SCOTT
SCOTT이 근무하는 부서의 인원수는 6명 입니다.
SCOTT은 RESEARCH(부서명)에서 근무합니다.
accept p_ename prompt '이름을 입력하세요!' declare v_ename emp.ename%type := upper('&p_ename'); v_cnt number; v_deptno emp.deptno%type; v_dname dept.dname%type; begin select deptno into v_deptno from emp where ename = v_ename; select dname into v_dname from dept where deptno = v_deptno; case v_deptno when 10 then select count(*) into v_cnt from emp where deptno = 10; when 20 then select count(*) into v_cnt from emp where deptno = 20; when 30 then select count(*) into v_cnt from emp where deptno = 30; end case; dbms_output.put_line(v_ename || '이 근무하는 부서의 인원수는 '|| v_cnt|| '명 입니다.'); dbms_output.put_line(v_ename || '은 '|| v_dname|| '에서 근무합니다'); end;
SQL로 위의 결과를 보려면 조인을 해야하는데, 빅데이터 환경에서는 조인을 하는 SQL의 성능이 느립니다. 그럴때는 위와같이 PL/SQL로 작성해서 조인하지 않고 단일테이블에서 데이터를 각각 가져오게 하는게 바람직한 방법입니다.
널 값과 관련하여 다음 규칙을 기억해 두면 일반적으로 발생할 수 있는 실수를 피할 수 있습니다.
✅ 널을 사용하는 단순 비교는 항상 NULL을 반환합니다.
예: (v_deptno = 10 에서 v_deptno가 null이라면 null반환)
✅ 논리 연산자 NOT을 널에 적용하면 NULL이 발생합니다.
✅ 조건 제어문에서 조건이 NULL을 반환하면 연관된 명령문
시퀀스가 실행되지 않습니다
예: if 조건 then // 조건이 null
실행문;
💡 혹시 에러는 안나는데 결과가 나오지않는다면? NULL
의심해보자!
논리연산자: and
, or
, not
비교 연산자를 사용하여 단순 부울 조건을 작성합니다.
true and null은 null
false and null은 왜 false? (false and false는 false / false and true는 false니까)
true or null 은 true. (true or false 도 true니까)
false or null null이다 (false or true는 true, false or false는 false라 뭔지 모른다.)
[점심시간문제] 문제 53번 코드의 case문에 select into절을 하나 더 추가해서 다음의 결과가 출력되게 하세요. (성능의 느린 sql을 pl/sql로 튜닝하는 방법)
accept p_ename prompt '이름을 입력하세요!'
declare
v_ename emp.ename%type := upper('&p_ename');
v_cnt number;
v_deptno emp.deptno%type;
v_dname dept.dname%type;
v_grade salgrade.grade%type;
v_sal emp.sal%type;
begin
select deptno, sal into v_deptno, v_sal
from emp
where ename = v_ename;
select dname into v_dname
from dept
where deptno = v_deptno;
select grade into v_grade
from salgrade
where v_sal between losal and hisal;
case v_deptno
when 10 then
select count(*) into v_cnt
from emp
where deptno = 10;
when 20 then
select count(*) into v_cnt
from emp
where deptno = 20;
when 30 then
select count(*) into v_cnt
from emp
where deptno = 30;
end case;
dbms_output.put_line(v_ename || '이 근무하는 부서의 인원수는 '|| v_cnt|| '명 입니다.');
dbms_output.put_line(v_ename || '은 '|| v_dname|| '에서 근무합니다');
dbms_output.put_line(v_ename || '의 급여등급은 '|| v_grade|| '입니다');
end;
✅ 조인을 해서 결과를 출력하는 무거운 SLOW SQL -> PL/SQL + 조인하지 않는 SQL
🤔 퀴즈!
✅ 특정 실행문을 반복시키고 싶을 때 반복문을 사용합니다.
반복문의 종류 3가지
basic loop
: 조건없이 그냥 반복시키고 싶을 때
for loop
: 조건을 주고 반복시키고 싶을 때while loop
LOOP +
반복하고싶은 실행문
EXIT [WHEN반복문을 끝낼 조건
] ;
END LOOP ;
🚨LOOP
,END LOOP ;
사이에 실행문을 넣고 해당 실행문이EXIT WHEN
절에 작성한 조건에 해당되지 않는 동안 반복된다. 중요한것은,EXIT WHEN
절 없이 수행하게되면 무한루프가 발생하면서 서버의 CPU를 상당히 차지하게 되고, 서버의 성능을 떨어트리는 원인이 됩니다.
예제.
LOCATIONS의 테이블의 데이터를 불러와서 반복하여 다시 데이터를 LOCATIONS에 INSERT하는 중.
✅ MAX값은, 나라마다 location_id
중 가장 높은것을 뽑은것이다. 왜냐면 1900이라면 1900 다음값인 1901가 되게 하려고.
LOCATIONS 테이블
DECLARE
v_countryid locations.country_id%TYPE := 'CA';
v_loc_id locations.location_id%TYPE;
v_counter NUMBER(2) := 1;
v_new_city locations.city%TYPE := 'Montreal';
BEGIN
SELECT MAX(location_id) INTO v_loc_id FROM locations
WHERE country_id = v_countryid;
LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((v_loc_id + v_counter), v_new_city, v_countryid);
v_counter := v_counter + 1;
EXIT WHEN v_counter > 3;
END LOOP;
END;
/
v_counter := v_counter + 1;
EXIT WHEN v_counter > 3;
절대 빠지면 안된다!!!
문제 55. 베이직 루프문으로 숫자 1부터 10까지 출력하시오
DECLARE v_counter number(20) := 1; BEGIN LOOP dbms_output.put_line(v_counter); v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP; END; /
문제 56. 위 숫자를 가로로 출력하시오
SET SERVEROUTPUT ON DECLARE v_counter number(20) := 1; BEGIN LOOP dbms_output.put_line(v_counter); v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP; dbms_output.new_line; END; /
참고!
dbms_output.put_line
에서 _line을 빼면 가로로 출력된다. 공백을 조금 주려면 연결연산자 사용
문제 57. 숫자를 물어보게하고, 숫자를 입력하면 1번부터 해당 숫자까지 출력되게 하시오 (100 입력하면 1~ 100까지 출력)
⭕ 정답 : 초기값에 1 설정하고, 반복문의 조건(마지막 숫자)를 내가 입력한 숫자로 설정했다.
accept p_num prompt '숫자를 입력하세요 ~ '
declare
v_counter number := 1 ;
begin
loop
dbms_output.put( v_counter ||' ');
v_counter := v_counter + 1 ;
exit when v_counter > &p_num;
end loop;
dbms_output.new_line;
end;
/
❌ 내 오답 : 초기값에 내가 입력한 숫자
를 받았고, 반복문의 조건(마지막 숫자)도 내가 입력한 숫자
를 설정해서, 내가 입력한 숫자 1개만 나왔다.
accept p_num prompt '숫자를 입력하세요!'
DECLARE
v_counter number(20) := &p_num; // 초기값 내가 입력한값
BEGIN
LOOP
dbms_output.put(v_counter);
v_counter := v_counter + 1;
EXIT WHEN v_counter >= &p_num; // 마지막도 내가 입력한 값
END LOOP;
dbms_output.new_line;
END;
/
문제 58. dept테이블을 select 했을 때 아래와 같이 결과가 출력되게 basic loop문으로 PL/SQL 프로그래밍 작성하기
SELECT * FROM DEPT;
10 ACCOUNTING NEWYORK
20 . .
30 . .
40 . .
50 RESEARCH1 SEOUL
60 RESEARCH2 SEOUL
70 RESEARCH3 SEOUL
80 RESEARCH4 SEOUL
declare v_cnt1 number := 50 ; v_cnt2 number := 1; begin loop insert into dept values( v_cnt1, 'RESEARCH' || v_cnt2, 'SEOUL' ); v_cnt1 := v_cnt1 + 10; v_cnt2 := v_cnt2 + 1 ; exit when v_cnt2 > 4; end loop; end; /
✅ 특정 실행문을 간단하게 반복시키고 싶을 때 basic loop를 사용하면 됩니다. exit when절을 생략하지 않도록 주의하기!
✅ 베이직루프는 내가 조건을 모르고, 특정 실행문을 반복하고 싶을 때 사용하지만 반복해야하는 조건을 미리 알고있다면, while loop를 사용하면 유용하다. while과 loop 사이에 조건이 true인 동안에만 반복문을 실행한다!
예제
while loop문을 이용해서 숫자 1부터 10까지 출력하시오
declare
v_cnt number := 1;
begin
while v_cnt < 11 loop
dbms_output.put_line(v_cnt);
v_cnt := v_cnt + 1;
end loop;
end;
/
문제 59. 1~10까지의 숫자중에 짝수만 출력하시오!
declare v_cnt number := 1; begin while v_cnt < 11 loop if mod(v_cnt,2) = 0 then dbms_output.put_line(v_cnt); end if; v_cnt := v_cnt + 1; end loop; end; /
✅ if로 시작했다면 end if;로 끝나야하고, v_cnt를 2로 나는 값이 0이면 (짝수) 출력하기.
예제.
while loop 문을 db 프로그래밍 하는 예제
- exit when절의 유무
- 반복할 조건을 미리 알고있는지
basic loop
는 exit when 무조건 써야하고,
while loop
는 exit when절이 없다. 조건을 while과 loop사이에 주고있다.
basic loop
는 조건을 그냥 끝내고싶은것을 설정해주는거라면
while loop
는 3번만 증가하면 된다 라는것을 분명하게 알고 조건을 준 것
DECLARE
v_countryid locations.country_id%TYPE := 'CA';
v_loc_id locations.location_id%TYPE;
v_new_city locations.city%TYPE := 'Montreal';
v_counter NUMBER := 1;
BEGIN
SELECT MAX(location_id) INTO v_loc_id FROM locations
WHERE country_id = v_countryid;
WHILE v_counter <= 3 LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((v_loc_id + v_counter), v_new_city, v_countryid);
v_counter := v_counter + 1;
END LOOP;
END;
/
✅ 위 베이직 루프와 같은결과를 가져오는 while loop!
문제 60. while loop문을 이용해서 dept테이블에서 dname만 다음과 같이 출력하시오
ACCOUNTING
RESEARCH
SALES
OPERATIONS
DECLARE v_deptno number := 10; v_dname dept.dname%type; BEGIN WHILE v_deptno <= 40 LOOP SELECT dname INTO v_dname FROM dept WHERE deptno = v_deptno; dbms_output.put_line(v_dname); v_deptno := v_deptno + 10; END LOOP; END; /
✅ deptno가 40까지라는것을 우리는 너무 잘 안다!
문제 61. (SQL문제) 사원 테이블에 rowid, empno, ename 출력
select rowid, empno, ename
from emp;
아까는 deptno가 증가되는 패턴이 동일했는데 지금 empno는 동일하게 증가하지 않는다. 그렇다면 rowid
를 사용해보자. 뒤에 A, B, C, D...로 증가중!
select ename, sal
from emp
where rowid = 'AAASVTAAHAAAaoeAAA';
rowid는 데이터베이스에서 데이터를 검색하는 가장 빠른 방법!
문제 62. (SQL문제) 아래의 결과를 PL/SQL로 출력하기 위해서 숫자 65가 문자로 무엇인지 출력하시오
AAASVTAAHAAAaoeAAA
AAASVTAAHAAAaoeAAB
AAASVTAAHAAAaoeAAC
AAASVTAAHAAAaoeAAD
AAASVTAAHAAAaoeAAE
AAASVTAAHAAAaoeAAF
AAASVTAAHAAAaoeAAG
AAASVTAAHAAAaoeAAH
AAASVTAAHAAAaoeAAI
AAASVTAAHAAAaoeAAJ
AAASVTAAHAAAaoeAAK
AAASVTAAHAAAaoeAAL
AAASVTAAHAAAaoeAAM
AAASVTAAHAAAaoeAAN
select chr(65) from dual;
문제 63. 알파벳 A ~ N까지를 다음과 같이 출력하시오
A
B
C
.
.
.
N
declare v_num number := 65; v_chr VARCHAR2(10) := 'chr(65)'; begin while v_num < 78 loop select chr(v_num) into v_chr from dual; dbms_output.put_line(v_chr); v_num := v_num + 1; end loop; end; /
문제 64. 위 코드를 가지고 아래의 rowid
를 출력하기
declare v_num number := 65; v_chr VARCHAR2(10) := 'chr(65)'; begin while v_num < 78 loop select chr(v_num) into v_chr from dual; dbms_output.put_line( 'AAASVTAAHAAAaoeAA' || v_chr); v_num := v_num + 1; end loop; end; /
문제 65. 위 코드를 이용해서 사원테이블의 이름을 전부 출력하시오
declare v_num number := 65; v_chr VARCHAR2(10) := 'chr(65)'; v_ename emp.ename%type; begin while v_num < 78 loop select chr(v_num) into v_chr from dual; select ename into v_ename from emp where rowid = 'AAASVTAAHAAAaoeAA' || v_chr; dbms_output.put_line( v_ename); v_num := v_num + 1; end loop; end; /
예제
숫자 1 ~ 10까지 출력하시오
begin
for i in 1 .. 10 loop
dbms_output.put_line(i);
end loop;
end;
✅ 1이 i로 들어간다. i가 1일때, i가 2일때, i가 3일때......
3가지 loop문 중에 코드가 가장 심플한 반복문이다!
문제 65. 1 ~ 50까지의 숫자를 출력하는데 홀수만 출력하기
begin for i in 1 .. 50 loop if mod(i,2) = 1 then dbms_output.put_line(i); end if; end loop; end;
문제 66. (오늘의 마지막 문제) for loop 문으로 사원테이블의 이름, 월급을 다음과 같이 출력
KING 5000
BLAKE 2850
CLARK 2450
.
.
.
내 답
declare v_chr VARCHAR2(10) := 'chr(65)'; v_num number := 65; // 지우기 v_ename emp.ename%type; v_sal emp.sal%type; begin for i in 65.. 78 loop select chr(i) into v_chr from dual; select ename , sal into v_ename, v_sal from emp where rowid = 'AAASVTAAHAAAaoeAA' || v_chr; dbms_output.put_line( v_ename ||' ' || v_sal ); end loop; end; /
수정
✅ 지금 선언한 v_num은 i 로 대체되어 있어서 사용하지 않고있음. 지워도 잘 수행이 된다.
✅select chr(i) into v_chr
<- 이거 지우고 chr(i)자체를 emp테이블 where절에 사용해도 된다. 어차피chr(65)
이거를 뽑고싶은거니까declare v_ename emp.ename%type; v_sal emp.sal%type; // 선언에 얘들만 남기고 begin for i in 65.. 78 loop select ename , sal into v_ename, v_sal from emp where rowid = 'AAASVTAAHAAAaoeAA' || chr(i); // select절도 하나만 둔다. where에 v_chr가 아닌 // chr(i) -> chr(65), chr(66), chr(67)...로 뽑기 dbms_output.put_line( v_ename || ' ' || v_sal ); end loop; end; /