PL/SQL

[PLSQL-1] PL/SQL기본 1(1~4장)

쏘니's avatar
쏘니
Mar 31, 2024
[PLSQL-1] PL/SQL기본 1(1~4장)
Contents
PART1. PL/SQL 기본1장. PL/SQL 개념 잡기 2장. PL/SQL 의 큰 골격 이해하기 PL/SQL 블록 구조예제 3장. PL/SQL 내에서 DML문장 작성법 4장. PLSQL 내에서의 if문과 반복문
💡
자바 개발자 관점에서 PL/SQL을 사용하는 이유
  1. 단순하게 자바쪽 코드를 작성할 수 있습니다.
  1. 개발 프로그램과 데이터베이스간의 네트워크 통신을 줄일 수 있습니다.
 
⇒ DBA가 프로시저 관리를 하기 때문에 자바 개발자가 왜 PL/SQL을 사용하는지 이유를 알고있어야함
 
 
💡 DBA 관점에서 PL/SQL 을 사용하는 이유
  1. DB 작업을 자동화 하기 위해서(고차원적 업무도 가능)
    1. 커서를 이용해서 업데이트 하는 것도 자동화 중 하나
  1. 개발자들이 만든 프로시저를 관리하기 위해서 사용
      2. (프로시저가 인벨디드…?되게 하지 않도록 프로시저를 알아야함)
 

PART1. PL/SQL 기본

1장. PL/SQL 개념 잡기

 

001. PL/SQL이란 무엇인가?

📖
PL/SQL 이란 무엇인가
  1. SQL을 자동화 한 하나의 기능
  1. Procedure Language(절차적 언어) + SQL(비절차적 언어. 단발성) ex. IF문, LOOP 같은 프로그래밍 언어
  • PL/SQL을 배워야 하는 이유
      1. SQL 로 하는 많은 작업들을 자동화 할 수 있음
      1. PL/SQL 을 이용하면 DB 작업을 편하게 할 수 있음(특히 DBA에게 유용,java에도 유용)
      1. PL/SQL 을 이용하면 SQL을 간단하게 작성할 수 있음

002. 개발자 관점에서 PL/SQL을 사용해야하는 이유

📖

개발자 관점에서 PL/SQL을 사용해야하는 이유

실습1

먼저 오라클이 설치가 되어져 있어야하고  c##scott  유져가 생성되어져 있어야
합니다. 오라클이 설치되지 않으신분들은 오라클 설치 영상을 참고하시면 됩니다.

오라클 버젼은 아무 버전이나 다 가능합니다. 

▣ 예제2_실습1.  실습 환경 구성

오라클 sys 유져로 접속해서 다음과 같이  c##scott 유져를 생성하고
c##scott 유져로 접속해서 테이블 2개를 생성하세요.

sqlplus  "/ as sysdba"

create  user  c##scott
 identified by tiger;

grant  dba  to c##scott; 

connect c##scott/tiger

alter session set nls_date_format='RR/MM/DD';
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-09',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;

실습2

 
예제1_실습2. (SQL)
부서번호와 부서번호별 토탈월급을 출력하는데 가로로 출력하시오 ! sum 과 decode 를 이용해서 코딩하세요.
select sum( decode(deptno, 10, sal, null)  )  as "10",
       sum( decode(deptno, 20, sal, null)  )  as  "20",
       sum( decode(deptno, 30, sal, null)  )  as  "30"
    from emp;

< 단점 >
1. 부서번호를 알고 있다는 가정하에만 사용가능 
	 즉, 모른다라고 가정했을 때에는 sql을 작성할 수 없음
2. 새로운 부서번호가 emp테이블에 입력될 경우 sql을 재작성 해야함
	 40번을 입력했다면 위의 sql에 40번을 새로 추가해야함
notion image
 
 

실습3

 
예제 1_실습3.
직업도 같이 나오면서 직업별 부서번호별 토탈월급이 출력되게 하시오
select  job, sum( decode(deptno, 10, sal, null)  )  as "10",
               sum( decode(deptno, 20, sal, null)  )  as  "20",
               sum( decode(deptno, 30, sal, null)  )  as  "30"
    from   emp
    group   by  job;

JOB      |10  |20  |30  |
---------+----+----+----+
PRESIDENT|5000|    |    |
MANAGER  |2450|2975|2850|
SALESMAN |    |    |5600|
CLERK    |1300|1900| 950|
ANALYST  |    |6000|    |
notion image
 
예제2_실습3.
** 1. 명령 프롬프트 창에 다음과 같이 작성 **

create or replace procedure get_data(p_x out sys_refcursor)
as
   l_query varchar2(400) := 'select job';
   l_deptno number;
begin
   for x in (select distinct deptno from emp order by 1)
   loop
      l_deptno := x.deptno;
      l_query := l_query || ', sum(decode(deptno, ' || l_deptno || ', sal)) as "' || l_deptno || '"';
   end loop;
   l_query := l_query || ' from emp group by job';
   open p_x for l_query;
end;
/     #/까지 입려해줘야함

			#삼성에서 사용했던 plsql

** 2. 프로시져를 실행하는 부분 **

 variable  x  refcursor;

 exec get_data(:x);

 print x;


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

JOB               10         20         30
--------- ---------- ---------- ----------
PRESIDENT       5000                      
MANAGER         2450       2975       2850
SALESMAN                              5600
CLERK           1300       1900        950
ANALYST                    6000


===============================================================

※ 설명  
- SQL 
	: 새로운 직업과 새로운 부서번호가 입력될 때 마다 SQL을 변경해 줘야합니다.

- PL/SQL 
	: 새로운 직업과 새로운 부서번호가 입력되어도 수정할 것 없고 
		그냥 그대로 프로시져만 수행하면 됩니다.
notion image
notion image

003. PL/SQL 을 사용했을때의 장점 4가지

📖
PL/SQL 을 사용했을때의 장점 4가지
SQL만 사용했을때  vs   PL/SQL을 사용했을때
① PL/SQL 을 이용하면 SQL 을 자동화하여 시간을 절약할 수 있습니다. ② SQL 을 여러번 작성해야 볼 수 있는 결과를 PL/SQL 하나로 끝낼 수 있습니다. ③ PL/SQL 코드는 한번 작성하면 재사용이 가능합니다. ④ PL/SQL 코드는 암호화할 수 있습니다. (Python등 암호화 안됨. 자바는됨)
⇒ 암호화해서 오라클을 사용하기 좋은 코드를 만들 판매할 수 있음

문제풀며 익히기

 
Q1.
 

2장. PL/SQL 의 큰 골격 이해하기

💡
  • PL/SQL 변수의 종류 3가지 ?
  1. 스칼라 변수 : 단일값 만 저장
  1. 조합변수 : 여러개값을 저장 (레코드 변수, 컬렉션 )
  1. 바인드 변수 : 호스트 변수

004. PLSQL 블록 구조

📖
PL/SQL 블록 구조(pl/sql의 기본 뼈대)

PL/SQL 블록 구조

 
① (선택) declare : 선언절
프로그램에서 사용할 데이터를 담을 변수를 선언하는 부분(빈 컵 선언)
② (필수) begin : 실행절 , SQL, PL/SQL 작성 부분
프로그램의 실행문이 있는 부분(선언절에서 선언한 변수 사용)
③ (선택) exception : 예외처리하는 절
: 프로그램이 순조롭게 잘 수행되기 위해
입력되는 잘못된 데이터에 대한 예외를 두는 부분
④ (필수) end : 종료절
 
선택 = 반드시 있어야 되는게 아닌 것
필수 = 반드시 있어야 할 코드
 
declare ~ end 외에 그의 외부인 host환경에서는 세미콜론(;)을 사용하지 않음
 

예제

예제4_실습1. 다음의 코드의 설명을 듣고 이해하세요
set  serveroutput  on
accept   p_num1   prompt   '첫번째 숫자를 입력하세요 ~'
accept   p_num2  prompt    '두번째 숫자를 입력하세요 ~'

declare
           v_sum       number(10);
begin
            v_sum :=  &p_num1 +  &p_num2 ;  
           dbms_output.put_line('총합은 :  '  ||  v_sum );
end;
/

=======================================

C:\Users\itwill>sqlplus c##scott/tiger

SQL> ed p.sql

SQL> @p.sql => p.sql을 실행하시오

泥ル쾲吏??レ옄瑜??낅젰?섏꽭??~1
?먮쾲吏??レ옄瑜??낅젰?섏꽭??~2
구   4:             v_sum :=  &p_num1 +  &p_num2 ;
신   4:             v_sum :=  1 +  2 ;
珥앺빀？？ :  3

PL/SQL 처리가 정상적으로 완료되었습니다.

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

이상하게 표현되는 이유! 유니코드로 변경해주지 않아서!

SQL> $dir  #Os의 위치가 어딘지 인지 알 수 있음
 C 드라이브의 볼륨에는 이름이 없습니다.
 볼륨 일련 번호: A4CD-4883

 C:\Users\itwill 디렉터리   ==>>>>이거 보면됨

2023-12-18  오후 01:59    <DIR>          .
2023-11-24  오후 03:25    <DIR>          ..
2023-12-14  오후 06:25             7,770 20231214.sql
2023-12-13  오후 02:49                47 afiedt.buf
2023-11-24  오후 03:08    <DIR>          Contacts
2023-12-11  오전 11:57             1,832 demo.sql
2023-12-18  오전 11:19    <DIR>          Desktop
2023-11-28  오전 11:31    <DIR>          Documents
2023-12-18  오전 11:19    <DIR>          Downloads
2023-11-24  오후 03:08    <DIR>          Favorites
2023-11-24  오후 03:08    <DIR>          Links
2023-11-24  오후 03:08    <DIR>          Music
2023-11-24  오후 03:10    <DIR>          OneDrive
2023-12-18  오후 01:59               329 p.sql
2023-12-14  오후 02:46    <DIR>          Pictures
2023-11-24  오후 03:08    <DIR>          Saved Games
2023-12-14  오전 09:50    <DIR>          Searches
2023-12-11  오전 09:32    <DIR>          Videos
2023-12-13  오후 06:14             7,261 window_21c.sql
2023-12-13  오후 06:14             1,441 window_21c~1.sql
               6개 파일              18,680 바이트
              14개 디렉터리  188,288,237,568 바이트 남음

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

1. 윈도우 탐색기에서 위에서 찾은 경로 따라가기
2. p.sql 마우스 우클릭 > Edit Notepad++
3. 인코딩 > ANSI 혹은 UTF-8로 변환 하면 됨

=> 앞으로 여기서 계속 PL/SQL 작성하면 됨. 작성 후 반드시 저장
=> 인코딩은 한 번 해뒀으니 안해도 됨
notion image
notion image
notion image
notion image
notion image
-----------------------설명 코드------------------------

1. set  serveroutput  on
2. accept   p_num1   prompt   '첫번째 숫자를 입력하세요 ~'  #외부변수(p_로 시작)
3. accept   p_num2  prompt    '두번째 숫자를 입력하세요 ~'  #외부변수

4. declare
5.           v_sum       number(10);  #내부변수(v_로 시작함)
6. begin
7.            v_sum :=  &p_num1 +  &p_num2 ;  
8.          dbms_output.put_line('총합은 :  '  ||  v_sum );
9. end;
10. /

1.  dbms_output.put_line() = 
		dbms_outpu 라는 화면의결과를 출력을 하는 오라클 내장 패키지 중
		put_line이라는 프로시져가 있음.
		이들은 화면에 출력하기 위한 것들임. 
		변수에 할당된 값을 출력하는 것.

		반드시 set serveroutput 을 on으로 해놔야
		맨 마지막에 쓰여진 패키지를 작동시킬 수 있음

2.  accept = 받아들인다. p_num1이라는 변수에 
		무엇을? prompt (야기시키다) 무엇을? '첫번째 숫자를 입력하세요 ~'를 야기시키다
		그러면 p_num1이 받아들이는 것

3. '두번째 숫자를 입력하세요 ~' 라는 글씨를 야기시켜서 (화면에 출력) 해서
		입력하면 변수 p_num2에 저장되는 것.
		외부변수는 p_로 시작하는 것이 암묵적 약속

4. 선언절에서

5.  v_sum이라는 변수를 선언 (위에 있는 변수들은 종이컵이면 이건 제대로된 머그컵)
		빈 컵인 v_sum이라는 변수를 number(10) 즉, 숫자 10자리를 담을 수 있는 변수로 선언
		변수 선언 후 세미콜론(;)으로 항상 닫아줘야함

6. 실행절

7. := (콜론 이퀄. 할당연산자) 는 그의 우변에 있는 코드가 먼저 수행되어 왼쪽에 할당
		declare 와 end 사이에 declare 밖에 있는 변수를 쓰려면 &(엔퍼센트)를 사용해줘야함.
		여기서 &가 붙은 변수를 치환변수라고 함. => declare와 end 사이는 내부변수 라고 함
		세미콜론(;)으로 닫아줌. 마침표 역할
		내부변수는 v_로 시작하는 것이 암묵적 약속

8.  dbms_output.put_line() = 
		dbms_outpu 라는 화면의결과를 출력을 하는 오라클 내장 패키지 중
		put_line이라는 프로시져가 있음.
		이들은 화면에 출력하기 위한 것들임. 
		변수에 할당된 값을 출력하는 것.

9. end; = 종료!

10. / = pl/sql을 종료하겠다는 것! 반드시 넣어줘야함
 

문제풀며 익히기

 
Q1.
예제의 코드를 수정해서 아래와 같이 세번 물어보게하고 총합을 출력하시오.
SQL> @p.sql
		첫번째 숫자를 입력하세요 ~
		두번째 숫자를 입력하세요 ~
		세번째 숫자를 입력하세요 ~

		총합은 :
A.

set  serveroutput  on
accept   p_num1   prompt   '첫번째 숫자를 입력하세요 ~'
accept   p_num2  prompt    '두번째 숫자를 입력하세요 ~'
accept   p_num3  prompt    '세번째 숫자를 입려가세요 ~'

declare
           v_sum       number(10);
begin
            v_sum :=  &p_num1 +  &p_num2 + &p_num3 ;  
           dbms_output.put_line('총합은 :  '  ||  v_sum );
end;
/

---------------------------------------
세번째 입력과 총합 사이에 출력되는 것을 없애는 방법
** set verify off 명령어 ** 
sqlplus가 종료되면 사라지니 다시 작성해줘야함.
set serveroutput on도 마찬가지

set  serveroutput  on
set verify off
accept   p_num1   prompt   '첫번째 숫자를 입력하세요 ~'
accept   p_num2  prompt    '두번째 숫자를 입력하세요 ~'
accept   p_num3  prompt    '세번째 숫자를 입려가세요 ~'

declare
           v_sum       number(10);
begin
            v_sum :=  &p_num1 +  &p_num2 + &p_num3 ;  
           dbms_output.put_line('총합은 :  '  ||  v_sum );
end;
/
notion image
notion image

005. 변수란 무엇인가

📖
변수란? 데이터를 저장하는 메모리의 임시 저장 영역을 변수라고 합니다.
💡

스칼라(scalar) 변수 (가장 많이 쓰는 변수)

   1. 문자형 변수 :  문자 데이터를 담는 변수

      예:   v_ename      varchar2(20)

   2. 숫자형 변수 :  숫자 데이터를 담는 변수

      예:  v_empno      number(10)

   3. 날짜형 변수 :  날짜 데이터를 담는 변수

      예:  v_hiredate    date

문제풀며 익히기

 
예제5_실습1.
다음의 코드를 이용해서 문제를 풀어주세요. 숫자를 4개를 물어보게 하고 4개의 숫자의 합을 출력하세요
--------출력예시----------

SQL> @p.sql

    첫번째 숫자를 입력하세요 ~   2
    두번째 숫자를 입력하세요 ~   3
    세번째 숫자를 입력하세요 ~   2
    네번째 숫자를 입력하세요 ~   1

 총합은 8 입니다.
--------------------------------------------------------------------
참고 코드 :

set  serveroutput  on
accept   p_num1   prompt   '첫번째 숫자를 입력하세요 ~'  
accept   p_num2  prompt    '두번째 숫자를 입력하세요 ~'

declare
                v_sum       number(10);   
begin
             v_sum  :=  &p_num1 +  &p_num2 ;  
            dbms_output.put_line('총합은 :  '  ||  v_sum );
end;
/
A.
set  serveroutput  on
set verify off
accept   p_num1   prompt   '첫번째 숫자를 입력하세요 ~'
accept   p_num2  prompt    '두번째 숫자를 입력하세요 ~'
accept   p_num3  prompt    '세번째 숫자를 입력하세요 ~'
accept   p_num4  prompt    '네번째 숫자를 입력하세요 ~'

declare
           v_sum       number(10);
begin
            v_sum :=  &p_num1 +  &p_num2 + &p_num3 + &p_num4;  
           dbms_output.put_line('총합은 :  '  ||  v_sum );
end;
/
notion image
 
 

006. 변수이름을 지을 때 주의 사항

📖
변수이름을 지을 때 주의 사항
 

변수이름을 지을 때 주의 사항

① 변수 이름은 반드시 문자로 시작해야합니다. ② 변수 이름은 문자나 숫자를 포함할 수 있습니다. ③ 특수문자는 #, $, _ 만 쓸 수 있습니다. ④ 30자 이내로 작성해야합니다. ⑤ 오라클 예약어를 쓸 수 없습니다.
 
예제6_실습1.
오라클 예약어를 변수명으로 사용하면 오류가 나는지 테스트 하세요
set  serveroutput  on
accept   p_num1   prompt   '첫번째 숫자를 입력하세요 ~'
accept   p_num2  prompt    '두번째 숫자를 입력하세요 ~'

declare
                select       number(10);
begin
             select :=  &p_num1 +  &p_num2 ;
            dbms_output.put_line('총합은 :  '  ||  select );
end;
/

=================================================================

!!ERROR!!
2행에 오류:
ORA-06550: 줄 2, 열17:PLS-00103: 심볼 "SELECT"를 만났습니다 다음 중 하나가
기대될 때:
begin function pragma procedure subtype type <식별자>
<큰 따옴표로 구분된 식별자> current cursor delete exists prior
notion image
 

문제풀며 익히기

 
Q1.
아래의 코드에서 변수명을 SELECT 대신에 FROM 이라고 하면 에러가 나는지 확인하시오.
set  serveroutput  on
accept   p_num1   prompt   '첫번째 숫자를 입력하세요 ~'
accept   p_num2  prompt    '두번째 숫자를 입력하세요 ~'

declare
                from       number(10);
begin
             from :=  &p_num1 +  &p_num2 ;
            dbms_output.put_line('총합은 :  '  ||  from );
end;
/

==================================================
!!ERROR!!

2행에 오류:
ORA-06550: 줄 2, 열17:PLS-00103: 심볼 "FROM"를 만났습니다 다음 중 하나가 기대될
때:
begin function pragma procedure subtype type <식별자>
<큰 따옴표로 구분된 식별자> current cursor delete
exists prior

#즉, 오라클 예약어 사용으로 에러 발생
notion image
 
 
 
 

007. PL/SQL 변수 초기화 하기

📖
PL/SQL 변수 초기화 하기 설명 1.
변수를 선언하고 실행절에서 변수에 값을 할당하는 연습 코드
set serveroutput on #변수의 결과를 출력하는 패키지를 출력하기 위해 ON 해놓기

declare  #선언절
   v_num   number(10); #숫자형 내부변수 선언(앞으로 숫자만 담길 수 있음)

begin   #실행절
   dbms_output.put_line('my number is: '|| v_num); #v_vum = 현재 null 상태
   v_num := 7 ;  #할당 연산자 우측의 값을 할당연산자 왼쪽의 변수에 할당해줌
   dbms_output.put_line('my number is: '|| v_num); #v_vum = 7
end;
/
====================================

SQL> @p.sql
my number is:
my number is: 7

PL/SQL 처리가 정상적으로 완료되었습니다.
notion image
 
설명 2.
선언절에서 숫자 8을 할당했는데 실행절에서 다른 숫자로 변경되는 코드
set serveroutput on

declare  #선언절
 v_num   number(10) := 8; #숫자형 내부변수 선언 후, 바로 숫자 8 할당

begin   #실행절
 v_num := 9;   #다시 9 할당하여 8이 9에 덮어쓰기됨
 dbms_output.put_line('my number is: '|| v_num);

end;
/

==================================================

SQL> @p.sql
my number is: 9

PL/SQL 처리가 정상적으로 완료되었습니다.
notion image

문제풀며 익히기

 
Q1.
아래의 코드를 실행하면 어떤 결과가 출력되는지 확인하시오.
set serveroutput on

declare
 v_num   number(10) := 8;

begin
 v_num := 9;
 v_num := 100;
 dbms_output.put_line('my number is: '|| v_num);

end;
/

========================================
SQL> @p.sql
my number is: 100

PL/SQL 처리가 정상적으로 완료되었습니다.
notion image
 
 
 
 
 
 

008. 변수를 선언할 때와 초기화 할때 주의사항

📖
변수를 선언할 때와 초기화 할때 주의사항
  1. 첫번째 주의사항(참고사항)
      2. declare
v_num    number(10)  not  null ;    <-- X
v_num    number(10)  not  null  :=  0 ;   <-- O
v_num2   constant   ;  <--- X
v_num2   constant  := 120 ;  <--- O
v_num3    varchar2(10)   default   'john' ;   <--- O

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

1. NOT NULL
	 변수에도 NOT NULL 제약을 걸 수 있음
	 => not null 제약 조건 걸려면, 
	 할당 연산자 써서 특정 값을 넣어줘야 제약 조건 걸 수 있음. 

2. CONSTANT (상수)
	 CONSTANT한 후 할당연산자로 숫자값(상수)을 부여해줘야함

3. DEFAULT
	 v_num3에 아무런 값도 입력이 안될경우 default 값인 john이 입력됨
  1. 두번째 주의사항
      2. select ... into 절 변수명 지을때 해당 테이블의 컬럼명으로 만들면 안됩니다.
      v_ 혹은 p_로 시작하는 변수명을 줘야함

즉, v_sal 혹은 p_empno과 같이 변수명을 지어줘야함
변수명을 지을 때 테이블의 컬럼명으로 지으면 안됨.

ex.
	emp_sal, emp_empno
 
  • SELECT A INTO B 절
    • 데이터베이스 테이블에서 select한(읽은) 데이터를 변수에 할당할 때 사용 테이블에서 읽어온 A의 값을 B에 입력 => 이렇게 데이터베이스와 밀접해서 데이터를 가져올 때에 plsql을 사용함
 
 
예제8_실습1.
두번째 주의사항을 확인하기 위해서 다음의 코드를 이해하세요
set  serveroutput  on
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;
/

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

select A into B 절
: 데이터베이스 테이블에서 select한(읽은) 데이터를 변수에 할당할 때 사용
	테이블에서 읽어온 A의 값을 B에 입력

=> 이렇게 데이터베이스와 밀접해서 데이터를 가져올 때에 plsql을 사용함

==========================

SQL> @p.sql
사원번호를 입력하세요 ~7788
해당 사원의 월급은 3000

PL/SQL 처리가 정상적으로 완료되었습니다.
notion image

문제풀며 익히기

 
Q1.
위의 코드의 v_sal 변수명을 sal 이라고 수정해서 실행하면 실행되는지 확인하시오
set  serveroutput  on
accept   p_empno  prompt  '사원번호를 입력하세요 ~'
declare
            sal     number(10) ;
begin
            select   sal   into  sal
                 from   emp
                 where  empno = &p_empno;

  dbms_output.put_line('해당 사원의 월급은 ' || sal );
end;
/

=================================================

ERROR는 나지 않지만 직관적이지 않고 가독성이 떨어지기 때문에 권하지 않음
notion image
 
Q2.
사원 번호를 물어보게하고, 사원번호를 입력하면 해당 사원의 직업이 출력되게 아래의 코드를 수정하시오.
set  serveroutput  on
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;
/
A. 

set  serveroutput  on
accept   p_empno  prompt  '사원번호를 입력하세요 ~'
declare
            v_job     varchar2(20) ;
begin
            select   job   into  v_job
                 from   emp
                 where  empno = &p_empno;

  dbms_output.put_line('해당 사원의 직업은 ' || v_job );
end;
/

======================================================

SQL> @p.sql
사원번호를 입력하세요 ~7788
해당 사원의 직업은 ANALYST

PL/SQL 처리가 정상적으로 완료되었습니다.
notion image

009. %type 속성의 중요성

📖

%type 속성의 중요성

변수의 데이터 유형을 지정할때 [테이블명].[컬럼명]%type이라고 하게 되면
해당 컬럼의 데이터타입을 그대로 따르겠다는 의미
가장 큰 장점이 특정 테이블의 특정 컬럼의 데이터 유형의 크기가 변경이 되었을때 plsql 코드를 따로 변경해주지 않아도 됩니다
 
따라서 미리 이렇게 개발하라고 사전 교육을 시켜줘야함!! 개발자들에게!
set  serveroutput  on
accept   p_empno  prompt  '사원번호를 입력하세요 ~'
declare
             v_sal     emp.sal%type ;
begin
            select   sal   into   v_sal
                 from   emp
                 where  empno = &p_empno;

  dbms_output.put_line('해당 사원의 월급은 ' || v_sal );
end;
/
-----------------------------------------------------------------

emp.sal%type = EMP테이블의 SAL의 데이터 타입을 그대로 따르겠다는 의미

변수의 데이터 유형을 지정할때 emp.sal%type 이라고 하게 되면 
가장 큰 장점이 emp 테이블의 월급의 데이터 유형의 크기가 변경이 되었을때
plsql 코드를 따로 변경해주지 않아도 됩니다

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

** 데이터 유형 크기 변경 방법 **
alter table [테이블명]
modify [컬럼명] [바꿀 데이터타입 및 크기];
 
만약 아래와 같이 개발자가 PL/SQL 코딩을 했다면 ?
set  serveroutput  on
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;
/

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

그런데 어느날 dba 가 아래와 같이 emp 테이블의 sal을 변경했습니다.

   alter   table   emp
    modify    sal   number(15);   

이렇게 크기를 늘리면  db 에 sal 와 관련된 PL/SQL 프로그램의 변수의 데이터 유형을
number(10) 에서 number(15) 로 변경해줘야 합니다.  
  
그러면 수많은 프로그램 코드를 다 변경해줘야하는 번거로움이 생깁니다.
그래서 아예 처음부터 PL/SQL 코딩할 때 다음과 같이 코딩 해야 합니다.
 

문제풀며 익히기

 
예제9_문제1.
위의 코드를 수정해서 사원번호를 물어보게하고 사원번호를 입력하면 이번에는 월급과 직업도 같이 출력되게하시오
구현결과 : 사원번호를 입력하세요 ~ 7788
해당사원의 월급은 3000 해당사원의 직업은 ANALYST 
set  serveroutput  on
accept   p_empno  prompt  '사원번호를 입력하세요 ~'
declare
             v_sal     emp.sal%type ;
						 v_job		emp.job%type;  #세미콜론이 쉼표같은 구분자로 쓰인것
begin
            select   sal, job   into   v_sal, v_job  #넣을 순서대로 입력
                 from   emp
                 where  empno = &p_empno;

  dbms_output.put_line('해당 사원의 월급은 ' || v_sal );
  dbms_output.put_line('해당 사원의 직업은 ' || v_job );
end;
/

=================================================================

SQL> @p.sql
사원번호를 입력하세요 ~7788
해당 사원의 월급은 3000
해당 사원의 직업은 ANALYST

PL/SQL 처리가 정상적으로 완료되었습니다.
notion image
 
 
 
Q1.

010. 바인드 변수란 무엇인가?

📖

바인드 변수란 무엇인가?

바인드 변수는 호스트 환경에서 사용될 수 있어서 호스트 변수라고도 하는데 스타벅스에 가지고 갈 수 있는 텀블러를 연상하면 됩니다. 호스트 환경은 PL/SQL블럭 외의 환경을 말합니다.
 
예제10_실습1.
-- 1. 아래의 코드는 sqlplus  프롬프트 창에서 수행하세요
variable   v_salary   number   

			#여기선 number는 크기지정 안해도 됨
			#plsql 외부에 host 변수 생성(주인이라는 뜻 아님. 외부)
			#내 외부에서 모두 사용될 수 있음

-- 2.아래의 코드는  notepad++ 에 코딩하세요.

begin 
     select   sal  into   :v_salary
       from   emp
       where   empno = 7788;
end;
/

			#:v_salary => 콜론(:)을 앞에 적은 바인드 변수
			#바인드 변수를 사용하기 위해서는 콜론을 붙여야 값 할당 가


--3. 아래의 코드는 sqlplus  프롬프트 창에서 수행하세요

print   v_salary 

select  ename, sal
  from  emp
  where sal = :v_salary;

================================================================
PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> variable v_salary number
SQL> @p.sql

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> print v_salary

  V_SALARY
----------
      3000

SQL>
================================================================

설명: 
-  바인드변수(호스트 변수)를 PL/SQL 블럭과 SQL에서 사용하려면
   콜론(:) 을 앞에 붙여줘야합니다.
    
-  바인드 변수 선언시  숫자 변수는  number 로 써야하고 number(10)
   으로 하면 오류가 납니다. 

   문자인 varchar2 는 varchar2 로 해도 되고 varchar2(10) 로 해도 됩니다.
 
예제10_실습2.
아래의 코드를 실행하는데 autoprint 기능을 켜고 실행하시오
-- 1. 아래의 코드는 sqlplus  프롬프트 창에서 수행하세요
	variable   v_salary   number
	set  autoprint   on  

			#print를 따로 작성하지 않아도 바인드변수에 사용된걸 자동 프린트

-- 2.아래의 코드는  notepad++ 에 코딩하세요.
declare  
     v_empno    number(6) := &empno;  

			#치환변수(앞에 &가 붙은 변수)를 사용하면, 치환변수 값을 입력하라는 
			#출력 창이 뜸

begin 
     select   sal  into   :v_salary
       from   emp
       where   empno = v_empno;
end;
/
notion image
notion image

문제풀며 익히기

 
Q1. 위의 코드를 활용해서 다음과 같이 수행되게 하시오
출력예시 : ename의 값을 입력하세요 ~ SCOTT
ANALYST 
A.


declare  
     v_ename    emp.ename%TYPE := '&ename';  

		#문자형태이므로 싱글쿼테이션 마크로 둘러싸야함

begin 
     select   job  into   :v_job
       from   emp
       where   ename = v_ename;
end;
/
---------------------------------------------------
SQL> variable   v_job   varchar2(20)
SQL>    set  autoprint   on
SQL> @p.sql
ename의 값을 입력하십시오: SCOTT

PL/SQL 처리가 정상적으로 완료되었습니다.


V_JOB
--------------------------------------------------------------------------------
ANALYST

==================================================

SQL> show autoprint
autoprint ON
SQL> set autoprint off
SQL> show autoprint
autoprint OFF
notion image
 
 

011. PL/SQL 용어 정리

📖

PL/SQL 용어 정리

  1. 식별자 : 변수명 입니다. 예: v_ename, v_sal
  1. 구분자 : 연산자, 세미콜론 입니다. 예: + , - , ;
  1. 리터럴 : 문자 데이터 값, 숫자 데이터 값 입니다. 예: 'scott' , 7788
  1. 주석(★) : 특정 코드 부분이 실행되지 않게하고 싶을때 사용 합니다.
  • 한줄 주석의 예 : v_ename varchar2(10) ; -- 이름을 담을 변수를 선언합니다.
  • 여러줄 주석의 예 :
      • /* 프로그램 이름:
      프로그램 설명:
      작성자 : 작성날짜 : 마지막 수정 날짜: */
       
      ⇒ plsql 코드를 만들기 전에 이렇게 설명을 남겨줘야함
       
       
      힌트와 주석의 차이
      주석은 특정 코드 부분이 실행되지 않게하고 싶을때 사용 합니다.
      hint는 옵티마이저에게 실행계획을 명령하는게 힌트

문제풀며 익히기

 
예제11문제1. 다음의 코드에서 식별자와 구분자와 리터럴이 뭔지 각각 설명하세요
set  serveroutput  on
accept   p_empno  prompt  '사원번호를 입력하세요 ~'
declare
            v_sal     number(10)  := 0 ;
begin
            select   sal   into  v_sal
                 from   emp
                 where  empno = &p_empno;

  dbms_output.put_line('해당 사원의 월급은 ' || v_sal );
end;
/

파랑 - 식별자, 빨강 - 구분자, 초록 - 리터럴
notion image
 
Q1.

012. PL/SQL 에서 사용할 수 있는 함수와 사용할 수 없는 함수

📖
  1. PL/SQL 에서 사용할 수 있는 함수 (내장함수 대부분):
      • 단일행 함수 대부분 : 문자, 숫자, 날짜, 변환, 일반
       
  1. PL/SQL 코드 내에서 사용할 수 없는 함수 : (단, pl/sql 내의 sql에서는 사용 가능)
      2. - 단일행 함수 중에는 decode 함수 - 그룹함수 : max, min, avg, sum, count
예제12_실습1)
문자를 입력하게 하고 문자의 철자의 개수를 출력하는 PL/SQL문
set  serveroutput  on
accept    p_str   prompt   '문자열을 입력하시오 ~  '   

declare  
            v_str       varchar2(100)  :=  '&p_str' ;           
            v_result    number(20) ;

begin
            v_result := length(v_str);  
           dbms_output.put_line( v_result ) ;
end;
/

#v_str에는 입력된 문자열이 들어가게 됨.
#lengh() pl/sql에서 사용 가능한 단일행 문자 함수
notion image
notion image
 
예제12_실습2. )
숫자를 물어보게하고 숫자를 입력해서 숫자 1을 입력하면 true 가 출력되게하고 0 을 입력하면 false 를 출력하게 하시오. decode 함수를 이용해서 작성하세요.
  • 출력예시 :
      • 숫자를 입력하세요 ~ 1
      true
      숫자를 입력하세요 ~ 0
      false
set serveroutput on 
accept p_num prompt '숫자를 입력하시오 ~ '

declare
     v_num   number(10) := &p_num;
     v_result  varchar2(10);

begin
     v_result := decode( v_num, 1, 'true', 0, 'false');
     dbms_output.put_line( v_result );
end;
/

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

!!ERROR!!
v_result := decode( v_num, 1, 'true', 0, 'false');
                 *
6행에 오류:
ORA-06550: 줄 6, 열18:PLS-00204: 함수 또는 의사열 'DECODE'는 SQL 문장에서만
사용될 수 있습니다
ORA-06550: 줄 6, 열6:PL/SQL: Statement ignored
notion image

문제풀며 익히기

 
Q1.
실습 1의 코드를 수정해서 이름을 물어보게하고 이름을 입력하면 이름의 성씨만 출력되게하는 PL/SQL 코드를 작성하세요
  • 출력예시 :
이름을 입력하세요 ~ 홍길동
홍
set  serveroutput  on
accept    p_str   prompt   '이름을 입력하시오 ~  '   

declare  
            v_str      varchar2(100)  :=  '&p_str' ;           
            v_result    varchar2(20) ;

begin
            v_result := substr(v_str,1,1);  #단일행 함수 substr() 사용 가능
           dbms_output.put_line( v_result ) ;
end;
/
notion image
 
Q2.
SQL문장에 decode 를 써서 실습2의 결과가 실행되게 하시오
A1.

set serveroutput on 
accept p_num prompt '숫자를 입력하시오 ~ '

declare
     v_num   number(10) := &p_num;
     v_result  varchar2(10);

begin
     
    select decode( v_num, 1, 'true', 0, 'false') into v_result
		from dual;

     dbms_output.put_line( v_result );
end;
/

---------------------------------------------------
A2.

set serveroutput on 
accept p_num prompt '숫자를 입력하시오 ~ '

declare
     v_result  varchar2(10);

begin
     
     select decode( &p_num, 1, 'true', 0, 'false') into v_result
		from dual;

     dbms_output.put_line( v_result );
end;
/

=====================================================

#dual은 결과값을 보기 위한 가상의 테이블임
notion image
notion image

013. PLSQL 에서의 데이터 유형 변환

📖
  • 데이터 유형의 변환 2가지 ?
      1. 명시적 형변환 - to_char : 문자형으로 형변환 - to_number : 숫자형으로 형변환 - to_date : 날짜형으로 형변환
          2.  
      1. 암시적 형변환
          2. : 오라클이 암시적(스스로) 형변환
           
          ★ 중요
          : 날짜형으로 암시적 형변환이 실패하지 않으려면,
          현재 세션의 날짜 형식으로 날짜를 작성해야함
          현재 세션의 날짜 형식을 확인하는 방법은 아래와 같음
           
    • nls_session_parameters 사전
        • 현재 session(내가 접속한 창)의 날짜 포맷, 화폐단위, 사용언어 등을 확인할 수 있는 사전
        SQL> select * from nls_session_parameters;
       
  • SQL에서의 암시적 형변환1:
      • select   ename, sal
from  emp
where  sal = '3000';

< 설명 >
숫자형인 sal과 문자형인 '3000'은 같지 않음 
따라서 아래와 같이 문자형 '3000'을 숫자형으로 암시적 형변환함

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

select   ename, sal
from  emp
where  sal = 3000;
 
  • SQL에서의 암시적 형변환2 :
      • select   ename, hiredate
from  emp
where  hiredate =  '82-12-22';

< 설명 >
날짜형 hiredate와 '82-12-22'엄밀히 말하면 문자형
따라서 아래와 같이 오라클이 암시적 형변환 함.

단, 암시적형변환 하려면 날짜 포맷이 반드시 
rr-mm-dd와 같이 동양권 날짜로 설정되어야 함
문자형으로 입력된 날짜의 포맷이 동양권 날짜 포맷과 동일하기 때

서양권의 경우에는 dd-mm-rr로 되어있기 때문에 에러 발생

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

select   ename, hiredate
from  emp
where  hiredate = to_date('22-12-82','DD-MM-RR');
 
 
예제13_실습1) ———— PL/SQL암시적 형변환 테스트하기
암시적 형변환이 PL/SQL 에서 일어나는 경우를 테스트 하시오
set  serveroutput  on 
declare
      v_sal      emp.sal%type  := 0  ;
      v_hiredate    emp.hiredate%type :=  '82/12/22' ;

begin
        dbms_output.put_line( v_sal );
        dbms_output.put_line( v_hiredate );
end;
/

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

SQL> select * from nls_session_parameters;

PARAMETER       |     VALUE
--------------------------------
NLS_DATE_FORMAT      RR/MM/DD   => 날짜형 포맷 확인

NLS_DATE_LANGUAGE    KOREAN

NLS_SORT             BINARY

========================================================
-v_sal은 emp.sal의 type에 따라 숫자형 변수임. 0은 숫자형이므로 형 일치
-v_hiredate는 emp.hiredate의 type에 따라 날짜형임. 하지만 우변은 문자형
 문자형으로 입력된 날짜 형태가 현재 날짜형의 포맷과 일치해야 암시적 형변환 가능
notion image
 
예제13_실습2)
암시적 형변환이 실패할 수 있도록 날짜 형식을 서양권 날짜 형식으로 변경해서 수행하세요
set  serveroutput  on 
declare
      v_sal      emp.sal%type  := 0  ;
      v_hiredate    emp.hiredate%type :=  '22/12/82' ;

begin
        dbms_output.put_line( v_sal );
        dbms_output.put_line( v_hiredate );
end;
/

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

SQL> select * from nls_session_parameters;

PARAMETER       |     VALUE
--------------------------------
NLS_DATE_FORMAT      RR/MM/DD   => 날짜형 포맷 확인

NLS_DATE_LANGUAGE    KOREAN

NLS_SORT             BINARY

=============================================================

날짜형 포맷에 문자형으로 입력된 날짜의 형태가 일치하지 않기 때문에
암시적 형변환이 되지 않고 에러가 발생함
notion image

문제풀며 익히기

 
Q1.
명시적 형변환 함수 to_date 를 사용하여 '22/12/82' 를 그대로 사용하더라도 에러가 나지 않도록 코드를 수정하시오.
set  serveroutput  on 
declare
     v_sal  emp.sal%type  := 0  ;
     v_hiredate  emp.hiredate%type := to_date('22/12/82', 'DD/MM/RR');

begin
      dbms_output.put_line( v_sal );
      dbms_output.put_line( v_hiredate );
end;
/

====================================================

세션의 날짜형 포맷이 동양권으로 되어있어서 에러 발생.
따라서 to_date함수를 사용하여 서양권 포맷으로 변경해주면 됨. (명시적 형변환)
notion image
 
 

014. PLSQL 블럭의 중첩 이해하기

📖
  • PL/SQL 블럭 중첩이란 ?
      • declare     
      begin
           declare
               begin  ---  어떤 기능1
               end;
         
           declare
               begin  --- 어떤 기능2
               end;
      end;
       /

외부 BLOCK 안에 내부 BLOCK 2개가 있는 것

- 외부 BLOCK의 변수는 내부 BLOCK에서 사용 가능
- 내부 BLOCK의 변수는 외부 BLOCK에서 사용 불가능
      notion image
 
  • PL/SQL 블럭을 중첩하는 이유 ?
      • 여러 업무 요구사항을 지원하기 위해서 실행섹션에 논리적으로 많은 기능들이 포함되어져 있는 경우에 중첩을 하면 유용합니다.
      begin
           declare
               begin  ---  어떤 기능1
               end;
         
           declare
               begin  --- 어떤 기능2
               end;
      end;
       /
 
예제14_실습1)
DECLARE
    v_outer  VARCHAR2(20) := '글로벌 변수';  

BEGIN
    DECLARE
        v_inner  VARCHAR2(20) := '내부 변수';  
    BEGIN                                              
        dbms_output.put_line(v_inner); #내부블록의 내부 변수 
        dbms_output.put_line(v_outer); #외부블록의 내부 변수
    END;

      dbms_output.put_line(v_outer); #외브블록의 내부 변수
END;
/

=========================================================

외부 BLOCK의 변수를 내부 BLOCK에서 출력 가능.
notion image
notion image
notion image

문제풀며 익히기

 
Q1.
위의 예제에서 v_inner 내부변수가 내부블럭에서만 사용될 수 있는데 외부에서도 사용될 수 있는지 확인해보시오
DECLARE
    v_outer  VARCHAR2(20) := '글로벌 변수';  

BEGIN
    DECLARE
        v_inner  VARCHAR2(20) := '내부 변수';  
    BEGIN                                              
        dbms_output.put_line(v_inner);
        dbms_output.put_line(v_outer);
    END;

			dbms_output.put_line(v_inner);
      dbms_output.put_line(v_outer);
END;
/

--------------------------------------------------------
!!ERROR!!
															dbms_output.put_line(v_inner);
                                                      *
12행에 오류:
ORA-06550: 줄 12, 열25:PLS-00201: 'V_INNER' 식별자가 정의되어야 합니다
ORA-06550: 줄 12, 열4:PL/SQL: Statement ignored
=========================================================

내부 블록의 변수는 외부 블록에서 사용할 수 없다.
notion image
notion image

015. PLSQL 변수 범위의 가시성

📖
  • 문법 설명
      • declare
   
  begin

     declare

      begin

      end;
  
end;
      notion image
 
  • 레이블 <<>>
      • 꼬리표, nametag 과 같은 말
 
예제15_실습1)
아래의 코드를 실행하세요
DECLARE
    v_father_job    VARCHAR2(20) := 'engineer';
    v_job_sal    NUMBER := 80000;
BEGIN
    DECLARE
        v_child_job     VARCHAR2(20) := 'teacher';
        v_job_sal  NUMBER := 30000;
    BEGIN
        dbms_output.put_line('Father''s job: ' || v_father_job);
        dbms_output.put_line('Father''s Salary: ' || v_job_sal );
        dbms_output.put_line('Child''s job: ' || v_child_job );
    END;

    dbms_output.put_line('Child''s Salary: ' || v_job_sal );
END;
/

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

#외부 BLOCK
	- DECLARE : 아빠의 직업과 월급 변수를 만들어서 값을 할당해줌
	- BEGIN : 외부 블록의 변수와 내부 블록의 변수명이 같을 때 외부블록 변수 우선

#내부 BLOCK
	- DECLARE : 자식의 직업과 월급 변수를 만들어서 값을 할당해줌
	- BEGIN
		출력1. 아빠의 직업 출력
		출력2. 외부 블록의 변수와 내부 블록의 변수 명이 같을 때 내부블록의 변수 우선
		출력3. 자식의 직업 출력

=> 즉 아빠의 월급과 자식의 월급이 서로 바뀌어 출력되어버
notion image
 
예제15_실습2)
위의 결과가 제대로 출력될 수 있도록 <<outer>> 레이블을 이용해서 코드를 수정하시오
begin  <<outer>>
DECLARE
    v_father_job    VARCHAR2(20) := 'engineer';
    v_job_sal    NUMBER := 80000;
BEGIN 
    DECLARE
        v_child_job     VARCHAR2(20) := 'teacher';
        v_job_sal  NUMBER := 30000;
    BEGIN
      dbms_output.put_line('Father''s job: ' || v_father_job);
      dbms_output.put_line('Father''s Salary: ' || outer.v_job_sal );
      dbms_output.put_line('Child''s job: ' || v_child_job );
      dbms_output.put_line('Child''s Salary: ' || v_job_sal );
    END;
END;
end outer;
/

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

#외부BLOCK
	- BEGIN 1 : 문법 상단에 BEGIN절을 넣고 <<outer>> 레이블을 선언해줌
	- end outer; : 외부 block end 후 begin <<outer>>을 end outer;로 종료

#내부BLOCK
	- BEGIN
		출력2. 외부 블록과 내부 블록의 변수명이 중복되었는데 내부 블록에서
					외부 블록의 변수를 우선해서 가져오고 싶다면 
					선언해둔 레이블을 사용해서 외부 블록 변수라고 지정해주면 됨
notion image
notion image

문제풀며 익히기

 
Q1.
아래의 코드가 실행되지 않는 이유를 설명하세요
begin  <<outer>>
DECLARE
    v_father_job    VARCHAR2(20) := 'engineer';
    v_job_sal    NUMBER := 80000;
BEGIN
    DECLARE
        v_child_job     VARCHAR2(20) := 'teacher';
        v_job_sal  NUMBER := 30000;
    BEGIN
        dbms_output.put_line('Father''s job: ' || v_father_job);
    END;
        dbms_output.put_line('Father''s job: ' || v_child_job);
END;
end outer;
/

===========================================================

내부 block의 변수 v_child_job을
외부 block에서 사용할 수 없기 때문에 실행되지 않음
 
 

3장. PL/SQL 내에서 DML문장 작성법

💡
  • 커서 사용 이유 : 여러개의 행을 화면에 출력하고 싶을 때 사용

016. PL/SQL 내에서의 select 문장 작성법

📖
PL/SQL 내에서의 select 문장 작성법
 
  • 설명1
      • PL/SQL 내에 SELECT 문과 DML문과 TCL 문을 사용할 수 있습니다.
    • DML 문 : insert, update, delete, merge, select
    • TCL 문 : commit, rollback, savepoint
    • DDL 문 : create, alter, drop, truncate, rename (주로 dba만 사용가능)
      • 참고
          • DDL문은 뒤에서 배울 execute immediate 절과 같이 사용됩니다.
       
  • 설명2:
      • PL/SQL 에서의 select 문장은 select .. into 절로 사용이 됩니다.
      select .. into 를 안쓰고 그냥 select 만 하는 경우는 명시적 커서 선언할 때만 입니다. 대부분 select .. into 절을 사용해서 PL/SQL 프로그래밍을 합니다.
       
  • BULK .. INTO 절
      • 여러개의 행을 출력할 때는 SELECT.. INTO절을 사용하면 안되고,
      BULK..INTO절을 사용해야함
       
  • group 함수의 특징 ?
      1. null 값을 무시합니다. 
          2. select sum(sal)
	from emp;

할때 null갑은 무시하고 값이 있는 것만 계산
      1. where 절의 조건이 거짓이어도 결과를 리턴합니다.
          2. max, sum,avg,min 은 절의 검색조건이 거짓이어도 null값을 리턴합니다. count 는 0 을 리턴합니다. 선택된 레코드가 없다고 출력되지 않습니다.
          select sal
	from emp
	where 1 = 2;

> 선택된 레코드 없습니다. 혹은 아무 행도 출력되지 않

select sum(sal)
	from emp
	where 1 = 2;

> 컬럼 명과 빈 행 값이 출력됨. 

select nvl(sum(sal),0)
	from emp
	where 1 = 2;

> null값은 연산되지 않기 때문에 0으로 대체
ex. null + 3000 = ?? 알 수 없는 값
          notion image
          notion image
 
예제16_실습1)
"select .. into 절을 사용할 때는 1개의 행만 인출해야 합니다. 다음과같이 여러개의 값을 인출하려 하면 오류가 발생합니다."
set  serveroutput   on 
declare
       v_ename     varchar2(25);
begin
      select  ename   into   v_ename
         from   emp
         where   job='SALESMAN';

    dbms_output.put_line( v_ename );
end;
/

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

!!ERROR!!
ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다

============================================


스칼라(sclar)변수는 값을 1개만 담을 수 있음.
직업이 salesman인 사원은 4명인데, 1개만 담을 수 있음
또한 select .. into 절은 한개의 행만 인출하기 때문에
따라서 기본키 처럼 중복값이 없는 조건을 설정해줘야함. 

여러개의 행은 bulk .. into 절을 사용해야함
 
예제16_실습2)
부서번호를 물어보게 하고 부서번호를 입력하면 해당 부서번호의 토탈월급이 출력되게하시오
set serveroutput on
set verify off
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;
/

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

number(10,2)
소수점 둘째 자리까지 출력. 즉, 세번째 자리에서 반올림
notion image
notion image

문제풀며 익히기

 
Q1.
직업을 물어보게 하고 직업을 입력하면 해당 직업의 토탈월급이 출력되게 하는 PL/SQL 을 작성하시오
set serveroutput on
set verify off
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;
/
notion image
 
Q2.
위의 코드를 수정해서 직업을 소문자로 입력해도 결과가 잘 출력될 수 있게 하시오.
A1.

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

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

A2.

set serveroutput on
set verify off
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 =  upper(v_job);

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

end;
/
notion image

017. PL/SQL 내에서 insert 문장 작성법

📖
프로시저 입력되는 값에 따라서 자동으로 입력, 수정, 삭제 되게 하는 역할
 
  • 프로시저 생성 문법
      • < 방법 1 >

1. 생성
create or replace procedure [프로시저명]
as/is
	begin
			
				[실행하고자 하는 sql문장]

	end;
/	

2. 실행

execute  [테이블명];

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

< 방법 2 >

1. 생성

create  or replace  procedure    insert_emp
   ( p_empno   emp.empno%type,
     p_ename  emp.ename%type,
     p_sal       emp.sal%type ) 
  as
       begin
           insert   into   emp(empno, ename, sal )
              values( p_empno, p_ename, p_sal );
       end;
/

2. 실행

execute   insert_emp(1234, 'JACK', 4000);
       
예제1)
다음의 insert 문장을 실행하는 프로시져(Procedure)를 생성합니다
insert   into   emp(empno, ename, sal )
   values( 1122,  'JAMES',  3000 );
1. 프로시져를 생성합니다. --명령프롬프트 창 sqlplus에서 실행--

  create  or  replace  procedure   insert_emp #프로시저 이름
  as #또는 is
       begin
           insert   into   emp(empno, ename, sal )
              values(  1122,  'JAMES',  3000 ); #세미콜론
       end; #세미콜론
/

2. 프로시져를 실행합니다. 

execute   insert_emp;

#실행 방법

3. select * from emp; 로 확인
=================================================================

프로시저 생성 이유
	자동으로 insert하기 위함. 시간도 설정 가능
notion image
notion image
 
예제2)
예제1번의 프로시져를 실행할 때 사원 테이블에 입력할 입력값을 직접 입력할 수 있게 수정하세요.
execute   insert_emp(1234, 'JACK', 4000);
execute   insert_emp(1235, 'JANE', 5000);
create  or replace  procedure    insert_emp
   ( p_empno   emp.empno%type,
     p_ename  emp.ename%type,
     p_sal       emp.sal%type ) 
  as
       begin
           insert   into   emp(empno, ename, sal )
              values( p_empno, p_ename, p_sal );
       end;
/


execute   insert_emp(1234, 'JACK', 4000);

select * from emp;
notion image
notion image
 

문제풀며 익히기

 
Q1.
dept 테이블에 데이터를 입력하는 프로시져를 생성하세요
  • 입력할 데이터 : exec insert_dept(50,'HR', 'SEOUL');
1. 생성
create or replace procedure insert_dept
( p_deptno dept.deptno%type,
	p_dname dept.dname%type,
	p_loc dept.loc%type )
as
	begin
		insert into dept(deptno, dname, loc)
			values( p_deptno, p_dname, p_loc);
	end;
/

2. 실행 및 결과 조회

 exec insert_dept(50,'HR', 'SEOUL');

 select * from dept;


=============================================

DEPTNO      DNAME                  LOC
---------- ---------------------- ------------------
        50 HR                      SEOUL
        10 ACCOUNTING              NEW YORK
        20 RESEARCH                DALLAS
        30 SALES                   CHICAGO
        40 OPERATIONS              BOSTON
notion image
 
 

018. PL/SQL 내에서의 update 문장 작성법

📖
PL/SQL 내에서의 update 문장 작성법 PL/SQL 내에 실행절에 update 문을 넣어서 실행할 수 있습니다.
--update 문

update emp
 set  sal = 9000
 where job='SALESMAN';
 
실습을 실행하기 전에 먼저 아래의 스크립트를 init_emp.sql 이란 이름으로 저장
alter session set nls_Date_format='RR/MM/DD';
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-09',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;
 
예제18_실습1)
1. 프로시져를 생성합니다. ---sqlplus(cmd)에서 실

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

                 commit;  #commit을 넣어줌
end;
/ 

2. 프로시져를 실행합니다. 
             
exec  update_job;
	

PL/SQL 처리가 정상적으로 완료되었습니다.
notion image
 
예제18_실습2)
update_job 프로시져를 실행할 때 다음과 같이 직업을 입력해서 실행되게 하세요.
execute   update_job('ANALYST');

답:

create   or  replace   procedure    update_job
(  p_job     emp.job%type ) #p_job = 입력 파라미터 변수
  as
    begin
              update    emp
                  set   sal  =  sal * 1.1 
                  where  job= p_job;

                 commit;
end;
/

exec update_job('ANALYST');

select ename, sal, job from emp;

=====================================
execute할 때 ANALYST에 싱글쿼테이션마크를 둘러줬기 때문에
create문에는 둘러줄 필요 없
notion image
notion image
 

문제풀며 익히기

 
Q1.
부서번호를 넣고 프로시져를 실행하면 해당 부서번호인 사원들의 커미션을 9000 으로 변경하는 프로시져를 생성하시오
  • 구현 결과 : exec update_deptno(20);

create or replace procedure update_deptno
( p_deptno emp.deptno%type )
as
	begin
		update emp
			set comm = 9000
			where deptno = p_deptno;

			commit;
end;
/

exec update_deptno(20);
notion image

019. PL/SQL 내에서의 delete 문장 작성법

📖
PL/SQL 내에서의 delete 문장 작성법
예제19_실습)
사원 테이블에 부서번호가 10번인 사원들의 데이터를 지우는 PROCEDURE 를 생성하시오 ! (프로시져 이름: delete_deptno )
1. 프로시져를 생성합니다.  ---sqlplus에서 수행

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

2. 프로시져를 실행합니다.

execute  delete_deptno;

문제풀며 익히기

 
Q1. (문제풀기 전에 init_emp.sql 스크립트를 수행)
부서번호를 같이 입력해서 프로시져를 실행하면 해당 부서번호인 사원들의 데이터가 지워지게 프로시져를 생성하세요
  • 실행결과 : exec delete_deptno(20); ⇒ 괄호 안에는 지워질 부서번호를 의미
1. 프로시져 생성 

	create    or   replace    procedure     delete_deptno
	( p_deptno emp.deptno%type )
    is
       begin
               delete    from    emp
                where   deptno = p_deptno;
            commit;
       end;
	/

2. 프로시져 실행

	exec delete_deptno(10);

	select ename, sal, deptno from emp;
notion image
 

020. PL/SQL 내에서 암시적 커서 작성법

📖
  • PL/SQL 내에서 암시적 커서 작성법
      • 프로시져를 생성하고 실행하면 성공적으로 수행되었다라는 메세지가 출력되어집니다. 그런데 UPDATE 문이 포함된 프로시져의 경우 몇 개의 행이 갱신되었다라고 메세지가 출력되면 데이터가 잘 갱신되었다는 것을 확실히 알 수 있을 텐데 그런 말은 없고 그냥 성공적으로 수행되었다고만 출력됩니다. (아래와 같이)
      notion image
      notion image
      notion image
       
예제1)
create   or  replace   procedure    update_deptno
(  p_deptno     emp.deptno%type )
  as
    begin
              update    emp
                  set   comm=9000
                  where  deptno= p_deptno;

                 commit;
end;
/ 

execute  update_deptno(70);

=====================================
70번은 없는 부서번호 임에도 정상적으로 완료되었다고 나옴

그래서 오라클에서는 암시적 커서 함수를 제공하고 있습니다. 
암시적 커서 함수를 이용하면 몇건이 갱신 되었다라고 출력해줍니다.
notion image
 
  • 암시적 커서의 종류 3가지 ?
      • ① SQL%FOUND
      : 가장 최근에 수행한 SQL문이 한 행 이상 영향을 받은 경우 TRUE
      ② SQL%NOTFOUND
      : 가장 최근에 수행한 SQL문이 한 행에도 영향을 받지 않은 경우 TRUE
      ③ SQL%ROWCOUNT(★)
      : 가장 최근의 SQL문에 의해 영향은 받은 행의 갯수를 리턴하는 함수
      ⇒ 우리가 가장 많이 쓸 것.
       
예제20_실습)
1. 프로시저 생성

set serveroutput on
create    or   replace    procedure     delete_deptno
( p_deptno   number )
    is
       begin
             delete    from    emp
             where   deptno = p_deptno;

             dbms_output.put_line( SQL%rowcount  || '행이 지워졌습니다. ' );

            commit;
       end;
/

2. 프로시져 실행

exec delete_deptno(20);

exec delete_deptno(70);
notion image
notion image

문제풀며 익히기

 
Q1.
다음의 프로시져에 암시적 커서를 이용하여 몇건의 행이 갱신되었다라는 메세지가 출력되게하시오
create   or  replace   procedure    update_deptno
(  p_deptno     emp.deptno%type )
  as
    begin
              update    emp
                  set   comm=9000
                  where  deptno= p_deptno;
                
               -- 여기에 들어갈 코드를 작성해주세요 !
   
            commit;
end;
/ 

exec update_deptno(20);
create   or  replace   procedure    update_deptno
(  p_deptno     emp.deptno%type )
  as
    begin
              update    emp
                  set   comm=9000
                  where  deptno= p_deptno;
                
            dbms_output.put_line( SQL%rowcount  || '갱신되었습니다. ' );
   
            commit;
end;
/ 

exec update_deptno(20);
notion image
 

4장. PLSQL 내에서의 if문과 반복문

021. PL/SQL 내에서의 if ~ else 문 작성법

📖
PL/SQL 내에서의 if ~ else 문 작성법
  • if ~ else 문법 설명
      • begin
    if   조건  then
          실행문1; 
    else
         실행문2;             
    end if;	
end;
/
 
예제1) if ~ else 문
set  serveroutput  on

declare  
     v_a     number :=  1;
     v_b     number :=  2;

begin 
     if   v_a > v_b then  # false
       dbms_output.put_line( to_char(v_a) || '은 ' ||  to_char(v_b)  || ' 보다 큽니다. '  );
     else
       dbms_output.put_line( to_char(v_a) || '은 ' ||  to_char(v_b)  || ' 보다 작습니다. ' );
    end  if; #if문 종료 방법
end #begin종료
/ #pl/sql종료

-------------------------
if문 첫 조건이 false이므로 else 실행
 
예제21_실습) 
set  serveroutput  on
set verify off

accept  p_a   prompt '첫번째 숫자를 입력하세요 '
accept  p_b   prompt '두번째 숫자를 입력하세요 '

declare  
     v_a     number(10) :=  &p_a;
     v_b     number(10) :=  &p_b;

begin 
     dbms_output.put_line( v_a );
     dbms_output.put_line( v_b );
end;
/
 
 

문제풀며 익히기

 
Q1. (if ~ else 문)
두 개의 숫자를 각각 물어보게 하고 두 개의 숫자를 입력하면 두 개의 숫자의 크기를 비교해서 다음과 같이 결과가 출력 되게 하시오!
  • 출력예시
      • 첫번째 숫자를 입력하세요 ~ 7 두번째 숫자를 입력하세요 ~ 6
      7 은 6 보다 큽니다.
      첫번째 숫자를 입력하세요 ~ 6 두번째 숫자를 입력하세요 ~ 7
      6 은 7 보다 작습니다.
      set  serveroutput  on

accept  p_a   prompt '첫번째 숫자를 입력하세요 '
accept  p_b   prompt '두번째 숫자를 입력하세요 '

declare  
     v_a     number(10) :=  &p_a;
     v_b     number(10) :=  &p_b;

begin 
     if   v_a > v_b then
       dbms_output.put_line( to_char(v_a) || '은 ' ||  to_char(v_b)  || ' 보다 큽니다. '  );
     else
       dbms_output.put_line( to_char(v_a) || '은 ' ||  to_char(v_b)  || ' 보다 작습니다. ' );
    end  if;
end;
/
      notion image
       

022. PL/SQL 내에서의 if ~ elsif ~ else 문 작성법

📖
PL/SQL 내에서의 if ~ elsif ~ else 문 작성법
  • if ~ elsif ~ else 문법 설명
      • begin
    if   조건1  then
          실행문1;    #세미콜론
    elsif 조건2 then
         실행문2;     #세미콜론
		elsif 조건3 then
				 실행문3;
    else
         실행문4;     #세미콜론      
    end if;	
end;
/

------------------------------------
else if / elif 가 아니라 elsif인 것 명심하기
       
예제1) if ~ elsif ~ else 절 실습 예제
set  serveroutput  on
set verify off

accept  p_a   prompt '첫번째 숫자를 입력하세요 '
accept  p_b   prompt '두번째 숫자를 입력하세요 '

declare  
     v_a     number :=  &p_a;
     v_b     number :=  &p_b;

begin 
     if   v_a > v_b then
       dbms_output.put_line( to_char(v_a) || '은 ' ||  to_char(v_b)  || ' 보다 큽니다. '  );
     elsif  v_a < v_b then
       dbms_output.put_line( to_char(v_a) || '은 ' ||  to_char(v_b)  || ' 보다 작습니다. ' );
    else
       dbms_output.put_line( to_char(v_a) || '은 ' ||  to_char(v_b)  || ' 과 같습니다. ' );
    end  if;
end;
/
notion image

문제풀며 익히기

 
Q1. if ~ elsif ~ else 절 문제
다음과 같이 이름을 물어보게 하고 이름을 입력하면 해당 사원의 월급에 대한 등급이 출력되게하시오.
  • 조건
월급이 3000 이상이면 A 등급 월급이 2000 이상 ~ 3000 보다 작으면 B 등급 월급이 1000 이상 ~ 2000 보다 작으면 C 등급 나머지 월급은 D 등급
  • 구현 결과:
이름을 입력하세요 ~ SCOTT
set  serveroutput  on
set verify off

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;

     if   v_sal >= 3000 then
       dbms_output.put_line('A등급');
     elsif  v_sal >= 2000 then
       dbms_output.put_line('B등급');
     elsif  v_sal >= 1000 then
       dbms_output.put_line('C등급');
     else
       dbms_output.put_line('D등급');
    end  if;
end;
/
notion image

023. PL/SQL 내에서의 basic loop문 작성법

📖
반복문이 필요한 이유 ? 특정 실행문을 여러번 반복 시키고 싶을 때 반복문을 사용합니다.
  • 반복문의 종류 3가지
      1. Basic loop : 조건 없이 특정 실행문을 그냥 반복 시키고 싶을 때 사용
      1. while loop : 조건을 주고 특정 실행문을 반복 시키고 싶을 때 사용
      1. For loop : 범위를 주고 특정 실행문을 반복 시키고 싶을 때 사용
 
  • BASIC LOOP문 문법
      • declare
   v_counter   number(10) : = 1 ;          #내부변수 선언

begin
   loop  

        [반복 시키고 싶은 실행문];        

        v_counter := v_counter + 1 ;    #할당연산자는 우측부터 실행됨
        exit when [반복문을 종료시킬 조건];
   end  loop;
end;
/

========================================================

v_counter 변수는 반복문을 종료시키기 위해 필요

/////////////////////////////////////
v_counter := v_counter + 1 ;    
 exit when [반복문을 종료시킬 조건];
/////////////////////////////////////

위 두 코드는 반복문을 종료시키기 위해서 반드시 필요함. 
그렇지 않으면 LOOP가 무한루프를 돌게 됨
 
예제 1) basic loop 문
basic loop 문으로 다음의 문장을 7번 반복해서 출력하세요 !
  • 출력할 문장
PL/SQL 을 사용하여 DB업무를 자동화 합니다.
set serveroutput on

declare   
       v_counter   number(10) := 1 ;
begin
      loop 
            dbms_output.put_line( ' PL/SQL 을 사용하여 DB업무를 자동화 합니다.' );
             v_counter  :=  v_counter +  1 ;
             exit   when   v_counter = 8;
    end  loop;
end;
/

-------------------------------------
v_coumter = 1 #' PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
v_coumter = 2 + 1 = 3 #' PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
v_coumter = 3 + 1 = 4 #' PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
v_coumter = 4 + 1 = 5 #' PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
v_coumter = 5 + 1 = 6 #' PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
v_coumter = 6 + 1 = 7 #' PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
v_coumter = 7 + 1 = 8 

8이 되면 exit
end loop;
end;
/
notion image
notion image

문제풀며 익히기

 
Q1.
아래의 글씨를 100번 출력되게 하시오.
  • 출력할 문장 : PL/SQL 을 사용하여 DB업무를 자동화 합니다.
declare   
       v_counter   number(10) := 1 ;
begin
      loop 
            dbms_output.put_line( ' PL/SQL 을 사용하여 DB업무를 자동화 합니다.' );
             v_counter  :=  v_counter +  1 ;
             exit   when   v_counter = 101;
    end  loop;
end;
/
 
Q2. ( basic loop 문)
구구단 2단의 basic loop문으로 수행 하시오
  • 구현결과
      • 2 x 1 = 2 2 x 2 = 4 2 x 3 = 6 2 x 4 = 8 2 x 5 = 10 2 x 6 = 12 2 x 7 = 14 2 x 8 = 16 2 x 9 = 18
declare   
       v_counter   number(10) := 1 ;
begin
      loop 
            dbms_output.put_line('2 x ' || v_counter || ' = ' || v_counter * 2 );
             v_counter  :=  v_counter +  1 ;
             exit   when   v_counter = 10;
    end  loop;
end;
/
notion image

024. PL/SQL 내에서의 while loop문 작성법

📖
PL/SQL 내에서의 while loop문 작성법
  • 반복문의 종류 3가지
      1. Basic loop : 조건 없이 특정 실행문을 그냥 반복 시키고 싶을 때 사용
      1. while loop : 조건을 주고 특정 실행문을 반복 시키고 싶을 때 사용
      1. For loop : 범위를 주고 특정 실행문을 반복 시키고 싶을 때 사용
 
  • WHILE LOOP 문법
      • while loop 문은 반복할 조건을 미리 알고 있는 경우에 작성하기 유용한 반복문 입니다.
      while 과 loop 사이에 조건이 True 인 동안에만 반복문이 실행됩니다.
      begin
    while  [조건]  loop

        [실행문];

        [반복문의 조건을 False 로 만들 코드] #무한루프를 돌게하지 않기 위

   end  loop;
end;
/
 
예제1)
while loop 문을 이용해서 아래의 문장을 7번 반복 출력하세요
  • 출력할 문장 : PL/SQL 을 사용하여 DB업무를 자동화 합니다.
set serveroutput on

declare
        v_cnt       number(10) := 1 ; 
begin
        while    v_cnt < 8   loop
             dbms_output.put_line( 'PL/SQL 을 사용하여 DB업무를 자동화 합니다.' ) ;
             v_cnt :=   v_cnt +  1 ; 
       end  loop;
end;
/

===================================================================
v_cnt < 8 라는 조건이 참일때 까지 반복

v_cnt = 1          #'PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
v_cnt = 1 +  1 = 2 #'PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
v_cnt = 2 +  1 = 3 #'PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
v_cnt = 3 +  1 = 4 #'PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
v_cnt = 4 +  1 = 5 #'PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
v_cnt = 5 +  1 = 6 #'PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
v_cnt = 6 +  1 = 7 #'PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
v_cnt = 7 +  1 = 8

v_cnt < 8 보다 커지면 end loop;
end;
/

문제풀며 익히기

 
Q1. (while loop문)
1~10까지의 숫자중에 짝수만 출력하시오
set serveroutput on

declare
	v_cnt number(10) := 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;
/

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

set serveroutput on

declare
	v_cnt number(10) := 1;

begin
	while v_cnt < 11 loop
		if mod(v_cnt,2) = 0 then
			dbms_output.put_line( v_cnt );
			v_cnt := v_cnt + 1;
		else
			v_cnt := v_cnt + 1;
		end if;
	end loop;
end;
/
notion image

025. PL/SQL 내에서의 for loop문 작성법(★ 가장 많이 쓰임)

📖
PL/SQL 내에서의 while loop문 작성법
  • 반복문의 종류 3가지
      1. Basic loop : 조건 없이 특정 실행문을 그냥 반복 시키고 싶을 때 사용
      1. while loop : 조건을 주고 특정 실행문을 반복 시키고 싶을 때 사용
      1. For loop : 범위를 주고 특정 실행문을 반복 시키고 싶을 때 사용
 
  • FOR LOOP 문법
      • 특정 실행문을 반복하고 싶을 때 반복할 범위를 미리 알고 있을때는 for 반복문이 유용합니다.
      begin
   for [인덱스카운터변수]  in  [반복할 범위]  loop

         [실행문];

   end  loop;
end;
/
 
예제 1)
for loop 문을 이용해서 아래의 문장을 7번 반복 출력하세요
  • 출력할 문장 : PL/SQL 을 사용하여 DB업무를 자동화 합니다.
set serveroutput on
begin
  for   i    in    1 .. 7  loop # 1 한칸띄고 .. 한칸 띄고 7

	   dbms_output.put_line('PL/SQL 을 사용하여 DB업무를 자동화 합니다.');

     end  loop;
end;
/

=====================================================
index 카운터 변수의 약자로 i를 많이 사용

i = 1 #'PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
i = 2 #'PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
i = 3 #'PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
i = 4 #'PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
i = 5 #'PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
i = 6 #'PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력
i = 7 #'PL/SQL 을 사용하여 DB업무를 자동화 합니다.' 출력

end loop;


3가지 loop문 중에 코드가 가장 심플한 반복문입니다.
notion image
 
 
 

문제풀며 익히기

 
Q1. (for loop문 문제)
1 ~ 50까지의 숫자를 출력하는데 홀수만 출력하시오
set serveroupt on
begin
  for   i    in    1 .. 50  loop
		if mod(i,2) = 1 then
	   dbms_output.put_line(i);
		end if;
   end  loop;
end;
/
notion image

026. PL/SQL 내에서의 continue 문 작성법

📖
PL/SQL 내에서의 continue 문 작성법
💡
LOOP문 옵션
  1. CONTINUE; : 특정 조건일 때 아무것도 하지말고 하던거 계속하라
  1. EXIT; : 반복문을 반복되는 중간에 종료할 때 사용
 
  • CONTINUE 문
    • 루프문에서 사용하는 옵션으로, 지속하다 = 무시하다 로 보면 됨.
    • 루프문으로 특정 실행문을 반복할 때 어느 조건에 해당하는 부분을 실행하지 않게 할때 유용합니다.
    • 다 반복하지 않아도 되고 이 부분은 그냥 실행하지말고 다른거 반복해라 !
 
  • continue 문법
      • begin
    for   [인덱스카운터]   in   [범위]  loop

        if  [조건] then
             continue ;  #특정 조건일 때 무시하고, 다른 하던거 계속해라
        end  if;

            [실행문];     

    end  loop;
end;
       
continue 예제1)
for loop 문을 이용해서 숫자 1번부터 10번까지 출력하시오
set serveroutput on
begin
    for   i   in   1 .. 10   loop
          dbms_output.put_line(  i  );
    end  loop;
end;
/
notion image
 
continue 예제2)
위에서 출력되는 결과중에 숫자 4는 출력되지 않게 하시오
set serveroutput on
begin
    for   i   in   1 .. 10   loop
        if  i != 4  then
             dbms_output.put_line(  i  );
        end  if;
    end  loop;
end;
/

================================================
< for loop 문 해석 > 
 : i != 4 라면 i를 출력하라
notion image
 
continue 예제3) — 예제 2번과 차이 없음. 개인에 맞게 선택하면 됨—
위의 결과를 continue를 이용해서 출력하시오
set serveroutput on
begin
    for   i   in   1 .. 10   loop

        if  i = 4  then
             continue ;
        end  if;

         dbms_output.put_line(i);      

    end  loop;
end;
/

=============================================
< FOR LOOP 문 해석 >

	: 만약 i = 4 라면, 특별한 수행 없이 계속해라.
		i = 1 #dbms_output.put_line(i) 에 의해 1 출력
		i = 2 #dbms_output.put_line(i) 에 의해 2 출력
		i = 3 #dbms_output.put_line(i) 에 의해 3 출력
		i = 4 #continue;(즉, 아무것도 하지말고 그냥 계속 지나가라)
		i = 5 #dbms_output.put_line(i) 에 의해 5 출력
		i = 6 #dbms_output.put_line(i) 에 의해 6 출력
		i = 7 #dbms_output.put_line(i) 에 의해 7 출력
		i = 8 #dbms_output.put_line(i) 에 의해 8 출력
		i = 9 #dbms_output.put_line(i) 에 의해 9 출력
		i = 10 #dbms_output.put_line(i) 에 의해 10 출력
		end  if;
		end loop;
		end;
		/
notion image

문제풀며 익히기

 
Q1. (continue 문제1)
1 ~ 10까지의 숫자중에서 짝수만 출력하시오 ! (단, continue 문을 이용)
set serveroutput on
set verify off

begin
    for   i   in   1 .. 10   loop

        if  mod(i,2) = 1  then
             continue ;
        end  if;

         dbms_output.put_line(i);      

    end  loop;
end;
/
notion image
 
Q2. (continue 문제2)
1 ~ 50까지의 숫자 중에서 7의 배수만 출력하시오 (단, continue 사용)
set serveroutput on
set verify off

begin
    for   i   in   1 .. 50   loop

        if  mod(i,7) != 0  then
             continue ;
        end  if;

         dbms_output.put_line(i);      

    end  loop;
end;
/

==========================================

7의 배수가 아니면 아무것도 하지마라 !
notion image

027. PL/SQL 내에서의 exit 문 작성법

📖
PL/SQL 내에서의 exit 문 작성법
💡
LOOP문 옵션
  1. CONTINUE; : 특정 조건일 때 아무것도 하지말고 하던거 계속하라
  1. EXIT; : 반복문을 반복되는 중간에 종료할 때 사용
 
  • EXIT 문
    • 루프문에서 사용하는 옵션 입니다.
    • 반복문을 반복되는 중간에 종료할 때 사용합니다.
    • cf) python, java, c언어 에서는 break문;
 
  • EXIT문 문법
      • begin
    for   카운터   in   범위  loop

        if  조건 then
             exit  ;   #특정 조건일 때, 반복하던것을 멈추고 LOOP 탈출
        end  if;

            실행문;     

    end  loop;
end;
/
 
exit 문 예제1)
for loop 문을 이용해서 숫자 1번부터 10번까지 출력하시오
set serveroutput on
begin
    for   i   in   1 .. 10   loop
          dbms_output.put_line(  i  );
    end  loop;
end;
/
notion image
 
exit 문 예제2)
1부터 10까지 출력되는 반복문을 수행하세요. 숫자 3까지 출력하고 숫자 4에서는 반복문을 종료 시키세요
set serveroutput on
begin
    for   i   in   1 .. 10   loop

        if  i = 4  then
             exit  ;
        end  if;

         dbms_output.put_line(i);      

    end  loop;
end;
/

================================================
< FOR LOOP 문 해석 >
	
	i = 1 # dbms_output.put_line(i)에 의해 1 출력
	i = 2 # dbms_output.put_line(i)에 의해 2 출력
	i = 3 # dbms_output.put_line(i)에 의해 3 출력
	i = 4 # if문에 의해 exit하여 loop문 탈출
notion image
 

문제풀며 익히기

 
Q1. (exit 문 문제1)
아래의 구구단 2단을 출력하는 PL/SQL문에서 2 x 7 = 14 까지만 출력되고
2 x 8 = 16 부터는 출력되지 않게 exit 문을 추가하시오
set serveroutput on
begin
    for   i   in   1 .. 9   loop

          if i = 8 then
						exit;
					end if;          

        dbms_output.put_line('2 x ' || i || ' = ' || 2 * i );      

    end  loop;
end;
/
notion image
 
 
 
 
 
Share article

More articles

See more posts

[PLSQL-3] PL/SQL고급

March 31, 2024
[PLSQL-3] PL/SQL고급

[PLSQL-2] PL/SQL기본 2 (5~10장)

March 31, 2024
[PLSQL-2] PL/SQL기본 2 (5~10장)

[SQL-4] SQL중급 2

March 31, 2024
[SQL-4] SQL중급 2

[SQL-3] SQL중급 1

March 31, 2024
[SQL-3] SQL중급 1

Hye-Min Son

RSS·Powered by Inblog