[PLSQL]23.07.21

망구씨·2023년 7월 21일

DML문 사용법 ELSIF cursor delete else if merge select update

PL/SQL

목록 보기

3/8

✏️ 오늘의 TIL

PL/SQL의 연산자
PL/SQL 에서의 select문장
PL/SQL을 사용하여 데이터 검색
이름 지정 규칙
PL/SQL 내에서 DML문 사용법
PLSQL내에서의 UPDATE문 사용법
PLSQL내에서의 DELETE문 사용법
PLSQL내에서의 MERGE문 사용법
SQL 커서
IF문
IF THEN ELSE 문
IF ELSIF ELSE 절

PL/SQL의 연산자

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

프로그래밍 지침

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

주석을 사용하여 코드에 대한 설명 추가
코드의 대소문자 규칙 개발
식별자 및 기타 객체의 이름 지정 규칙 개발
들여쓰기로 가독성 향상

📖 4장 오라클 데이터베이스 서버와 상호 작용: PL/SQL 프로그램의 SQL 문

배우게 될 것들!

✅ PL/SQL 실행 블록에 직접 포함될 수 있는 SQL 문 판별
✅ PL/SQL에서 DML 문으로 데이터 조작
✅ PL/SQL에서 트랜잭션 제어문 사용
✅ INTO 절을 사용하여 SQL 문에서 반환한 값 보유
✅ 암시적 커서와 명시적 커서 구별
✅ SQL 커서 속성 사용

PL/SQL 에서의 SQL문

PL/SQL 내에 SELECT문, DML문, TCL문을 사용할 수 있습니다.

DML문 : insert, update, delete, merge, select

TCL문 : commit, rollback, savepoint
💡참고: DDL문은 뒤에서 배울 execute immediate절과 같이 사용됩니다.
(dba의 작업을 편하게 해주는 PL/SQL 스크립트 생성시 유용함)

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

PL/SQL 에서의 select문장

PL/SQL 에서의 select문장은 select ...into 절로 사용됩니다.
그냥 select만 하는 경우는 명시적 커서 선언할 때만 입니다.

✅ INTO 절은 필수입니다.
✅ Query는 한 행만 반환해야 합니다.

✍🏻 예제

set serveroutput on
DECLARE
v_ename VARCHAR2(25);
BEGIN
SELECT ename INTO v_ename 
FROM emp 
WHERE empno = 7788;
DBMS_OUTPUT.PUT_LINE(' First Name is : '||v_ename);
END;
/

select ...into 절을 사용할때는 1개의 행만 인출해야합니다.
이래와 같이 여러개의 값을 인출하려하면 오류가 발생합니다.

BEGIN
SELECT ename INTO v_ename 
FROM emp 
WHERE job = 'SALESMAN';
DBMS_OUTPUT.PUT_LINE(' First Name is : '||v_ename);

PL/SQL을 사용하여 데이터 검색

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

set serveroutput on
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;
/

✅ deptno에 없는 부서번호 (50)을 넣는다면 null이 나온다. 그룹함수는 무조건 결과를 반환한다 !
sum, max, min, avg... -> null
count -> 0

select sal 
  from emp
  where deptno = 50; 
// 선택된 레코드가 없습니다 출력!

select sum(sal) 
  from emp
  where deptno = 50; 
// null 출력!

select count(sal) 
  from emp
  where deptno = 50; 
// 0 출력!

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

💡그룹바이 하려고 해서 틀림!!!!! 어차피 where 절에서 입력받은 직업으로 계산됨

정답코드 v_job emp.job%type := '&p_job'; 여기서 싱글쿼테이션 꼭 둘러주기

set serveroutput on
accept p_job prompt '직업 입력!'

declare 
  v_job  emp.job%type := '&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;
/

이름 지정 모호성

위 그림을 쉽게 emp테이블로 변경하면 아래와 같다. 데이터가 많이 나온다.

select ename, hiredate
 from emp
 where empno = empno;

✅ PL/SQL 변수명을 만들때는 컬럼명과 동일하게 지정하지 않습니다. 위와같이 변수명과 컬럼명이 동일하면, 컬럼명이 변수명보다 우선순위가 먼저입니다. 그래서 변수명이 들어오는것이 아니라 EMP테이블의 컬럼을 가져옵니다. (우리가 원하는 결과가 나오지 않음)

이름 지정 규칙

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

컬럼명과 동일한 변수가 있다면 컬럼명이 우선순위가 높다.
테이블명과 동일한 테이블명 변수가 있다면 변수명이 우선순위가 높다.

select ename, hiredate INTO ename, hiredate // X에러난다!X
 from emp
 where empno = empno;

PL/SQL 내에서 DML문 사용법

✅ 만약 아래의 작업을 오늘밤 10시에 해야한다면??
10시까지 남아서 수행하는것이 아니라, 프로시저에 위 UPDATE 문을 코딩하고, 밤 10시에 오라클 dbms_job또는 dbms_scheduler를 이용해서 자동으로 작업되게 할 수 있다.

update emp
 set sal = 7000
 where ename = 'KING';

위 프로시저는 values안에 시퀀스로 번호 자동생성이 되도록 했다.

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

프로시저에 코딩할 emp_seq 시퀀스를 먼저 생성

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;

프로시저 실행

execute insert_emp;

데이터가 잘 들어갔다!

문제 35. salgrade테이블에 6번으로 아래의 데이터를 입력하는 프로시저를 생성하기
프로시저 이름 : insert_salgrade
grade : 6
losal : 10000
hisal : 20000

프로시저 생성

create procedure insert_salgrade
as
  begin
    insert into salgrade(grade, losal , hisal)
      values(6, 10000, 20000); 
      commit; // 잘 들어가는지 확인하고 이거까지 써줘야 함
end;

프로시저가 잘 만들어졌는지 확인하기

💡 만약에 테이블이나 프로시저나 뭐중에 X자 표시 되어있다면 컴파일 해놓아주기

PLSQL내에서의 UPDATE문 사용법

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

예제.

프로시저를 생성하기

create or replace procedure update_emp
as
  begin
  update  emp
  set     sal = sal * 1.1
  where job = 'SALESMAN';
end;

프로시저 실행

execute update_emp

✅ procedure를 실행했을 때 성공적으로 수행되었다라고 위처럼 나오긴 하지만, 정말 잘 된건지 바로 알기가 어렵다. 몇개의 행이 갱신되었다 라고 메세지가 출력된다면 확실히 알 수 있겠지만 결과가 없어서 조금 불편하다.
그래서! 오라클에서 암시적 커서 함수를 제공한다. 암시적 커서 함수를 사용한다면 몇건이 갱신되었다 라고 출력한다. (뒤에서 배움)

문제 36. 부서번호가 20번인 사원들의 커미션을 9000으로 변경하는 프로시저를 생성하기 (프로시저 이름: update_emp_comm)

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

end; 
/

execute update_emp_comm;

PLSQL내에서의 DELETE문 사용법

🚨 DELETE 문은 테이블에서 불필요한 행을 제거합니다. 무결성 제약 조건이 없는 경우 WHERE 절을 사용하지 않으면 테이블의 모든 행이 제거될 수 있습니다.

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

프로시저 생성

create or replace procedure delete_deptno
is
  begin 
    delete from emp
    where deptno = 10;
end;

프로시저 실행

execute delete_deptno

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

프로시저 생성

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

end;

프로시저 실행

execute delete_comm

PLSQL내에서의 MERGE문 사용법

문제 39. (예제시연) merge문을 PL/SQL내에 넣어서 실행하기 위한 환경구성을 먼저 다음과 같이 진행합니다.
1. emp, dept테이블 초기화
2. emp테이블을 가지고 copy_emp 테이블을 생성합니다.

create table copy_emp
as
 select *
  from emp;

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

update emp
 set sal = 0;

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

delete 
  from emp 
  where deptno = 20;

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

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

문제 40. emp테이블 데이터를 copy_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);

SQL 커서

🤔 커서는, 오라클 서버에서 할당한 전용 메모리 영역에 대한 포인터 입니다. select문의 결과 집합을 처리하는데 사용이 됩니다.
커서의 종류 : 1. 암시적 커서 2. 명시적 커서

암시적 커서의 종류 3가지

SQL%ROWCOUNT - 몇개 업데이트 되었는지? 정수로 출력

✅ SQL%ROWCOUNT는 실행되고 따로 어딘가에 insert되기때문에 그게 저장되어있는 테이블만 조회해보면 된다.

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

set serveroutput on
declare 
  v_empno      emp.empno%type := 7788;
  row_deleted  varchar2(50);
begin
  delete from emp
   where empno = v_empno;
   
 row_deleted := (SQL%rowcount || '행이 지워졌습니다.');

bdms_output.put_line(row_deleted);
end;

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

내 답

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

 row_update := (SQL%rowcount || '행이 업데이트 되었습니다.');

dbms_output.put_line(row_update);
end;

이렇게도 할 수 있음

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

dbms_output.put_line(SQL%rowcount || '행이 업데이트 되었습니다.');
commit; // 이자리 꼭 기억!!
end;

🚨 commit;은 SQL%rowcount 다음에 작성해야한다. 만약 update문 바로 다음에 commit;을 쓰면 0행이 갱신되었다라고 출력된다.

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

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

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

문제 44. (시연예제)job_sumsal 테이블에 직업, 직업별 토탈월급을 입력
(서브쿼리 insert)

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

문제 45. 문제 44번의 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 || '행이 입력 되었습니다.');
end;     
/

execute daily_batch1;

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

create table log_table
 ( time_id   timestamp,
   tablename  varchar2(10),
   insert_cnt  number(10) );
   
insert into log_table values (systimestamp, 'ddd',5);   

rollback;

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

v_cnt := SQL%rowcount;

insert into log_table values (systimestamp, 'ddd', SQL%rowcount);

create or replace procedure daily_batch1
is // is, as 상관없음 
   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 job_sumsal; // 잘 들어갔나 확인
select * from log_table; // 잘 들어갔나 확인

📖 5장 제어문 (IF문)

배우게 될 것들!

✅ 제어 구조의 사용법 및 유형 식별
✅ IF 문 구성
✅ CASE 문 및 CASE 식 사용
✅ LOOP 문 작성 및 식별
✅ 조건부 제어 구조 사용 지침 사용

실행흐름 제어

IF문 문법

항상 실행절(BEGIN절에서) 사용

✅ IF 조건이 TRUE라면, (condition) THEN 이후의 실행문(statements;)을 실행해라
✅ ELSIF 위 조건이 아니라면 THEN 밑 (statements;) 실행해라
✅ ELSE 위 두가지, 이도저도 아니면 statements; 실행
✅ END IF;
💡IF, END IF;는 필수!!

1. IF문 예제
set serveroutput on
declare 
  v_myage  number := 31;
begin 
  if v_myage < 11 then
  dbms_output.put_line('I am a child');
end if;
end;
✅ declare 절 v_myage 는 31이 담겼다. if문의 조건은 11보다 작을 때 true인데, 변수에 담긴 값이 11보다 크기때문에 위 pl/sql은 false 이고, 실행되지 않는다.
그렇다면, 변수에 31말고 9라고 담으면 11보다 작은 조건에 맞으니 true이고, 출력될 것 !

IF THEN ELSE 문

set serveroutput on
declare 
  v_myage  number := 31;
begin 
  if v_myage < 11 then
    dbms_output.put_line('I am a child'); // true일 때
  else
    dbms_output.put_line('I am a not child'); // false일 때
    
end if;
end;

문제 48. 위 예제를 수정해서 나이를 물어보게 하고, 나이를 입력하면 'i am a child', 'I am a not child'가 출력되게 하세요
나이를 입력하세요 ! -> 17
'I am a not child'
나이를 입력하세요 ! -> 10
'i am a child'

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

declare 
  v_myage  number := &p_myage;
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 절

만약 10을 넣었다면 첫번째if 문에서 조건 만족하니까 중간 다 생략하고 endif로 넘어가고 끝난다. 만약 18 넣었을 때 첫번째 조건 안맞으니까 두번째로 내려가고, 여기서 조건이 맞으니 밑에는 수행 안하고 바로 end if로 넘어간다.
21을 넣으면, ⭐바로 3번째 조건으로 뛰어넘어가는게 아니고, 순차적으로⭐ 1번째 if문 수행, 아니면 밑에 elsif 수행, 아니니까 밑elsif 에서 수행한다.

✍🏻 예제.

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

declare 
  v_myage  number := &p_myage;
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 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;

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

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

declare 
  v_ename  emp17.ename%type := '&p_ename';
  v_age    emp17.age%type;
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 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;

망구씨

Slow and steady wins the race.

이전 포스트

[PLSQL]23.07.20

다음 포스트

[PLSQL]23.07.21

PL/SQL

✏️ 오늘의 TIL

PL/SQL의 연산자

프로그래밍 지침

📖 4장 오라클 데이터베이스 서버와 상호 작용: PL/SQL 프로그램의 SQL 문

배우게 될 것들!

PL/SQL 에서의 SQL문

PL/SQL 에서의 select문장

PL/SQL을 사용하여 데이터 검색

이름 지정 모호성

이름 지정 규칙

PL/SQL 내에서 DML문 사용법

PLSQL내에서의 UPDATE문 사용법

PLSQL내에서의 DELETE문 사용법

PLSQL내에서의 MERGE문 사용법

SQL 커서

암시적 커서의 종류 3가지

📖 5장 제어문 (IF문)

배우게 될 것들!

실행흐름 제어

IF문 문법

IF THEN ELSE 문

IF ELSIF ELSE 절

[PLSQL]23.07.20

[PLSQL]23.07.24

0개의 댓글