33. PL/SQL 연산자의 예제

sql 시간에 배웠던 산술연산자와 기타비교연산자를 PL/SQL 연산자로 사용할 수 있다.

34. 프로그래밍 지침

✅ 코드 유지 관리를 더욱 쉽게 만드는 방법

• 주석을 사용하여 코드에 대한 설명 추가
• 코드의 대소문자 규칙 개발
  ex) DECLARE 를 대문자로 하고 나머지는 소문자로 한다. 등등
• 식별자 및 기타 객체의 이름 지정 규칙 개발
  ex) 변수 이름 내부 v , 외부 p 등등
• 들여쓰기로 가독성 향상

---

4장. 오라클 데이터베이스 서버와 상호 작용

✏️ 이 단원을 마치면 다음을 수행할 수 있습니다.
• PL/SQL 실행 블록에 직접 포함될 수 있는 SQL 문 판별
• PL/SQL에서 DML 문으로 데이터 조작
• PL/SQL에서 트랜잭션 제어문 사용
• INTO 절을 사용하여 SQL 문에서 반환한 값 보유
• 암시적 커서와 명시적 커서 구별
• SQL 커서 속성 사용

35. PL/SQL 에서의 SQL문

• SELECT 명령을 사용하여 데이터베이스에서 행을 검색합니다.
• DML 명령을 사용하여 데이터베이스에서 행을 변경합니다.
• COMMIT, ROLLBACK 또는 SAVEPOINT 명령을 사용하여 트랜잭션을 제어합니다.

✔️ PL/SQL 내에 select 문, DML문, TCL문을 사용할 수 있다.

• DML 문 : insert, update, delete, merge, select
• TCL 문 : commit, rollback, savepoint

✔️ 참고 : DDL문은 뒤에서 배울 execute immediate 절과 같이 사용된다. (PL/SQL X?)
-> (dba의 작업을 편하게 해주는 PL/SQL 스크립트 생성시 유용함)

• DDL 문 : create, alter, drop, truncate, rename

36. PL/SQL 에서의 select 문장

select .. into
PL/SQL 에서의 select 문장은 select .. into 절로 사용이 된다.
select .. into 를 안쓰고 그냥 select 만 하는 경우는 '명시적 커서 선언할 때'만 입니다.
대부분 select .. into 절을 사용해서 PL/SQL 프로그래밍을 합니다.

SELECT select_list
INTO {variable_name[, variable_name]...
     | record_name}
FROM table
[WHERE condition];

➡️ 구문 설명

• select_list 하나 이상의 열 리스트이며 SQL 표현식, 행 함수 또는 그룹 함수를 포함할 수 있습니다.
• variable_name 검색한 값을 보유하는 스칼라 변수입니다.
• record_name 검색한 값을 보유하는 PL/SQL 레코드입니다.
• table 데이터베이스 테이블 이름을 지정합니다.
• condition PL/SQL 변수와 상수를 포함한 열 이름, 표현식, 상수 및 비교연산자로 구성됩니다.

❗ INTO 절은 필수입니다.
❗ Query는 1개의 행만 인출해야 합니다.

중요규칙! 1개의 행만 인출된다

예제.

set serveroutput on
declare
 v_ename  varchar2(25);
begin
 select ename into v_ename
   from emp
   where empno = 7788;

dbms_output.put_line(v_ename);
end;
/

하나의 값 출력이라 오류 X

...
begin
 select ename into v_ename
   from emp
   where job='SALESMAN';
...

으로 where 절 job='salesman' 으로 수정하면 오류난다.

문제32. (시연 예제)
부서번호를 물어보게 하고 부서번호를 입력하면 해당 부서번호의 토탈월급이 출력되게 하시오

accept p_deptno prompt '부서번호를 입력하세요'

declare
  v_deptno  emp.deptno%type := &p_deptno;
  v_sumsal  number(10,2) ;
begin
  select sum(sal) into v_sumsal
   from emp
   where deptno = v_deptno;

dbms_output.put_line ('토탈월급은 : ' || v_sumsal );
end;
/

; -> end 다음, 문장 다음, begin절 마지막, 변수 뒤
그룹 함수에 없는 번호 넣으면 null 값 나온다. (count는 0)
그룹함수를 안쓰면 선택된 레코드가 없다고 뜬다.

✅ 그룹함수의 특징

• null 값을 무시한다.
• where 절의 조건이 거짓이어도 결과를 리턴한다.
  ex) max, sum, avg, min 은 null 값을 리턴하고, count 는 0 을 리턴한다.
  '선택된 레코드가 없다' 고 출력되지 않는다.

문제 33.
직업을 물어보게 하고 직업을 입력하면 해당 직업의 토탈월급이 출력되게 하는 PL/SQL 을 작성하시오

직업을 입력하세요   -> salesman

토탈월급은 5600 입니다.

set serveroutput on
accept p_job prompt '직업을 입력하세요'

declare
  v_job  emp.job%type := UPPER'&p_job';
  v_sumsal  number(10,2) ;
begin
  select sum(sal) into v_sumsal
   from emp
   where job = v_job;

dbms_output.put_line ('토탈월급은 : ' || v_sumsal || ' 입니다' );
end;
/

❗❗ &p_job 양옆에 '' 싱글쿼테이션!! 그리고 대문자 소문자 상관없이하려면 upper!!

이름 지정 모호성

컬럼명과 변수명이 같게 되면, 컬럼명이 우선순위라서 변수에서 정보를 가져오는것이 아니라 컬럼에서 가져오기 때문에 오류 혹은 잘못된 정보가 출력된다. (컬럼의 employee_id 가 다 뽑힌다)

ex)
select ename, hiredate
 from emp
 where empno = empno;

이름 지정 규칙

• WHERE 절에서 모호성을 방지하기 위해 이름 지정 규칙을 사용합니다.
• 데이터베이스 열 이름을 식별자(변수)로 사용하지 않습니다. -> 컬럼명 변수명 같게 X
• PL/SQL은 먼저 데이터베이스를 검사하여 테이블의 열이 있는지 확인하기 때문에 구문 오류가 발생할 수 있습니다.
• 로컬 변수와 형식 파라미터의 이름은 데이터베이스 테이블의 이름보다 우선합니다.
• 데이터베이스 테이블 열의 이름은 로컬 변수의 이름보다 우선합니다

🔎 컬럼명과 테이블명의 우선순위?
✔️ 테이블의 컬럼명은 변수명보다 우선순위가 높습니다.
✔️ 테이블의 테이블명은 변수명보다 우선수위가 낮습니다.

PL/SQL 내에서의 DML 문 사용법

✔️ 10시에 업데이트 하라고 하면,
10시까지 남아서 수행하는게 아니라 프로시져에 위의 UPDATE 문을 코딩하고 밤 10시에 오라클 dbms_job 또는 dbms_scheduler 를 이용해서 자동으로 작업되게 할수가 있다.
-> 그래서 프로시저 (PL/SQL) 을 만들어서 사용하는 것이다.

PL/SQL 데이터 삽입 (insert문)

✅ 프로시져를 만들고 실행하기

문제 34. (예제 시연)
아래의 데이터를 emp 테이블에 입력하는 PL/SQL 문을 작성하시오 (프로시져)

(시퀀스부터 생성)
create sequence emp_seq
 start with 8000
 increment by 1
 maxvalue 9000;

create procedure insert_emp
 as
  begin
   insert into emp(empno, ename, sal, deptno, job)
    values (emp_seq.nextval, 'JAMES', 3000, 10, 'SALESMAN');
 end;
 /

➡️ 시퀀스를 먼저 만들고, 시퀀스를 이용해 insert_emp 라는 프로시져를 만들었다.
지금당장 emp 를 조회한다고 위의 결과가 insert 된게 아니라,
프로시져를 실행해야 insert 된다.
(프로시져 실행문은 아직 없는 상태!!)

⬇️ 프로시져를 실행합니다.

execute insert_emp;

위의 execute 문을 밤 10시에 수행되게 할려면? 패키지에 등록해줘야함..(?)

문제 35. salgrade 테이블에 6번으로 아래의 데이터를 입력하는 프로시저를 생성하시오
프로시져 이름은 insert_salgrade

grade : 6
losal : 10000
hisal : 20000

create or replace procedure insert_salgrade
 as
  begin
   insert into salgrade values(6, 10000, 20000);
   commit;
 end;
 /

PL/SQL 데이터 삽입 (update문)

만약 밤 10시에 EMP테이블에 직업이 SALESMAN 인 사원들의 월급을 자신의 월급에서 10% 인상시킨 월급으로 갱신되게 하려면,
다음과 같이 프로시져로 만들어서 밤10시에 자동으로 수행되게 하면 됩니다.

✅ 1. 프로시져 생성

예제.

create or replace procedure update_emp
 as
  begin
     update emp
     set    sal = sal*1.1    ->10% 인상
     where  job = 'SALESMAN';

 commit;

  end;
/

✅ 2. 프로시져 실행

execute  update_emp;

➡️ 프로시저를 실행했을때 성공적으로 수행되었다 라고 위와같이 메세지는 출력되었는데,
진짜로 잘 되었는지 바로 알기가 어렵습니다.
뭔가 몇개의 행이 갱신되었다고 메세지가 출력되면 확실히 알 수 있을텐데,
그런게 없어서 결과가 의심스럽다.

-> 그래서 오라클에서 암시적 커서 함수를 제공하고 있다. 암시적 커서 함수를 이용하면 '몇건이 갱신되었다' 라고 출력해준다.

문제 36. (점심시간 문제)
부서번호가 20번인 사워들의 커미션을 9000으로 변경하는 프로시져를 생성하시오
프로시져 이름은, update_emp_comm

create or replace procedure update_emp_comm
 as
  begin
     update emp
     set    comm = 9000
     where  deptno = 20;

 commit;

  end;
/

execute  update_emp_comm;

PL/SQL 데이터 삭제 (delete문)

delete

문제37. (시연예제) 사원 테이블의 부서번호가 10번인 사원들의 데이터를 지우는 procedure 를 생성하시오

문제 38.
커미션이 null 인 사원들의 데이터를 지우는 프로시져를 생성하시오

create or replace procedure delete_comm
 as
  begin
     delete from emp
     where  comm is null ;

 commit;

  end;
/

execute  update_emp_comm;

PL/SQL 데이터 병합 (merge문)

merge
begin 과 end 사이에 넣어서 구현

문제 39. (예제시연)
merge 문을 PL/SQL 내에 넣어서 실행하기 위한 환경구성을 먼저 다음과 같이 진행합니다.

1. emp와 dept 를 초기화 합니다.

@init_emp.sql

2. emp 테이블을 가지고 copy_emp 테이블을 생성합니다.

create table copy_emp
as
 select *
  from emp;

3. emp 테이블에 월급을 모두 0으로 변경합니다.

update emp
 set sal = 0;

4. emp 테이블에서 부서번호가 20번인 사원들을 지웁니다.

delete from emp where deptno=20;

5. emp 테이블에서 커미션이 null 이 아닌 사원들의 직업을 'ANALYST' 로 변경하고 부서번호를 50번으로 변경하시오

update   emp
    set    job='ANALYST',
           deptno= 50
  where  comm  is  not   null;

문제 40.
emp 테이블 데이터를 copy_emp 테이블의 내용과 일치하도록 merge 하시오 ! ( copy_emp 테이블을 사용해서 emp 테이블을 merge 하세요)

merge into emp e
using copy_emp c
on (e.empno = c.empno)
when matched then
 update   set   e.ename = c.ename,
                      e.job      = c.job,
                      e.sal       = c.sal,
                      e.comm  = c.comm,
                      e.hiredate = c.hiredate,
                      e.mgr  = c.mgr,
                      e.deptno = c.deptno
  when  not  matched  then
    insert    (e.empno,e.ename, e.job, e.sal, e.comm, e.hiredate, e.mgr, e.deptno )
    values(c.empno, c.ename, c.job, c.sal, c.comm, c.hiredate, c.mgr, c.deptno );

---

커서

커서의 종류

1. 암시적 커서
2. 명시적 커서

암시적 커서의 종류 3가지

암시적 커서의 함수들

문제 41.
사원 테이블에 사원번호가 7788번인 사원의 데이터를 지우는 익명 PL/SQL 블럭을 작성하는데
암시적 커서를 이용해서 몇건이 지워졌다라고 결과 메세지를 출력하게 하시오

set setserveroutput on
declare
 v_empno  emp.empno%type := 7788;
 row_deleted varchar2(50);
begin
 delete from emp
 where empno = v_empno;

row_deleted := (SQL%rowcount || '행이 지워졌습니다.' );

dbms_output.put_line (row_deleted);
end;
/

문제 42.
익명 PL/SQL 블럭으로 작성하세요.
직업을 물어보게하고 직업을 입력하면 해당 직업의 월급을 9000으로 변경하는 pl/sql 을 작성하세요.
pl/sql 이 성공적으로 수행되었을때 몇행이 갱신되었다라는 메세지가 화면에 출력되게 암시적 커서 함수를 활용하세요

직업을 입력하세요

5개의 행이 잘 갱신되었습니다.

set serveroutput on
accept p_job prompt '직업을 입력하세요'
declare
 v_job  emp.job%type := upper('&p_job');
 row_update number(10);
begin
 update emp
 set sal = 9000
 where job = v_job;

dbms_output.put_line (SQL%rowcount || '행이 잘 갱신되었습니다.');
COMMIT;
end;
/

❗ 주의사항 : commit 은 SQL%ROWCOUNT 다음에 작성해야한다.
UPDATE 문 바로 다음에 commit 을 쓰면 0행이 갱신되었다라고 출력된다.

ex)
쿠팡 ---> 매일밤 그날 하루동안 판매한 매출액등을 집계해서 어느 집계 테이블에
입력이 됩니다. 매일밤 어느 PL/SQL 프로그램이 돌면서 집계테이블에
입력을 해줍니다. 입력작업이 성공적으로 수행되었는지 그 건수를
암시적 커서로 잡아서 입력해줍니다.

문제 43. (시연예제)
다음과 같이 job_sumsal 이라는 테이블을 생성하시오

create table job_sumsal
 (job  varchar2(20),
  sumsal number(10,2) );

✅ 서브쿼리 사용한 insert

문제 44. (시연예제)
job_sumsal 테이블에 직업, 직업별 토탈월급을 입력하시오

insert into job_sumsal
 select job, sum(sal)
 from emp
 group by job;

select * from job_sumsal;

rollback

✅ 프로시저 만들기

문제 45. 아래의 sql 을 실행하는 프로시저를 생성하시오
프로시져를 실행하면 몇개의 행이 입력되었습니다. 라는 메세지가 출력되게 하시오
프로시져 이름은 daily_batch1 로 하세요

create or replace procedure daily_batch1
is
 begin
     insert into job_sumsal
     select job, sum(sal)
     from emp
     group by job;

 dbms_output.put_line( SQL%ROWCOUNT || '행이 입력되었습니다.');
   commit;
 end;
/

✅ 프로시져 수행하기

execute  daily_batch1;

문제 46. (시연예제) 아래의 테이블을 생성하고 daily_batch1 프로시져를 수행했을때,
SQL%rowcount 에 잡히는 숫자값이 입력되게 하시오 (따로 table에 출력되게!!)

create  table  log_table
( time_id  timestamp,
 table_name  varchar2(10),
 insert_cnt  number(10) );

insert into log_table values( systimestamp, 'job_sumsal', 5);

select * from log_table

간밤에 프로시저들이 잘 돌았는지~ 확인~

문제 47. daily_batch1 프로그램에 insert 문장으로 영향을 받은 건수가 log_table에 입력될 수 있도록 daily_batch1 프로그램을 수정하세요.

v_cnt := SQL%ROWCOUNT;

insert  into log_table  values( systimestamp, 'job_sumsal', v_cnt);
commit;

create or replace procedure daily_batch1
is
  v_cnt number(10);
 begin
     insert into job_sumsal
     select job, sum(sal)
     from emp
     group by job;

v_cnt := SQL%ROWCOUNT;
insert  into log_table  values( systimestamp, 'job_sumsal', v_cnt);
commit;
 end;
/

✅ 프로시져 가동해보기 (가동하고 나면 테이블에 입력되는 것)

execute  daily_batch1;

✅ 확인해보기

select * from log_table;

---

5. 제어 구조 작성

실행 흐름 제어

if문 문법

✔️ IF문은 실행절 (BEGIN) 안에 적어야함!!
✔️ 반드시 IF-END IF 를 적어줘야함.
✔️ end if; 세미코론 필수

예제.

set serveroutput on
declare
 v_myage number := 2;
begin
 if v_myage < 11 then

   dbms_output.put_line(' i am a child');
 end if;
end;
/

✔️ v_myage number := 2; 를 := 31; 로 하게 되면, 11보다 31이 작지 않기 떄문에 if 문의 조건이 false 라서 출력되지 않습니다.

IF~THEN~ELSE 문

예제.

set serveroutput on
declare
 v_myage number := 2;
begin
 if v_myage < 11 then
   dbms_output.put_line(' i am a child');
 else
  dbms_output.put_line(' i am a not child');
 end if;
end;
/

if ~~~ 라면
i am a child 를 출력하고,
아니면,
i am a not child 를 출력해라!

문제 48.
위의 예제를 수정해서 나이를 물어보게하고, 나이를 입력하면 i am a child 또는 i am a not child 가 출력되게 하시오

나이를 입력하세요~ (17)

i am a not child

set serveroutput on
accept p_age prompt '나이를 입력하세요'

declare
  v_myage  number := &p_age;
begin
  if  v_myage < 11 then
   dbms_output.put_line(' i am a child');
 else
  dbms_output.put_line(' i am a not child');
 end if;
end;
/

IF~ELSIF~ELSE 절

입력 숫자가 18이라면 두번째 줄에서 끝나고 바로 end if; 로 감!!

예제.

set serveroutput on
accept p_age prompt '나이를 입력하세요'

declare
  v_myage  number := &p_age;
begin
  if  v_myage < 11 then
   dbms_output.put_line(' i am a child');
 elsif  v_myage < 20 then
  dbms_output.put_line(' i am a young');
 elsif  v_myage < 30 then
  dbms_output.put_line(' i am in my twenties');
 elsif  v_myage < 40 then
  dbms_output.put_line(' i am in my thirties');
 else
  dbms_output.put_line(' i am always young');
 end if;
end;
/

17입력했을때

문제 49. (오늘의 마지막문제)
위의 코드를 수정하는데, 우리반 테이블을 가지고 작성하세요.
이름을 물어보게 하고 이름을 입력하면 해당 학생의 나이를 테이블에서 읽어와서 위의 예제대로 메세지가 출력되게 하시오.

이름을 입력하세요 ~ (김동휘)

i am in my thirties

set serveroutput on
accept p_ename prompt '이름을 입력하세요~'

declare
  v_ename  emp17.ename%type := '&p_ename';
  v_age  number(10) ;
begin
  select age into v_age
   from emp17
   where ename = v_ename;

  if  v_age < 11 then
   dbms_output.put_line(' i am a child');
 elsif  v_age < 20 then
  dbms_output.put_line(' i am a young');
 elsif  v_age < 30 then
  dbms_output.put_line(' i am in my twenties');
 elsif  v_age < 40 then
  dbms_output.put_line(' i am in my thirties');
 else
  dbms_output.put_line(' i am always young');
 end if;
end;
/