Oracle DBA PL/SQL 230720

sskit·2023년 9월 20일

Oracle PL/SQL

목록 보기

2/3

복습 :  1. PL/SQL 을 왜 배워야하는지?
        2. PL/SQL 이 무엇인지 ?
        3. 오라클 PL/SQL 정규 교재  
                         -  1장: PL/SQL 의 종류와 기본 블럭의 구조
                                        - 익명블럭
                                        - 프로시져
                                        - 함수
                                        - 트리거
                                        - 패키지 

 - PL/SQL 기본 블럭의 구조 

  declare  : 선언절(선택)
  begin    : 실행절(필수)
  exception : 예외처리절(선택)
  end;      : 종료절(필수)

           -  2장: PL/SQL 변수
                     1. 스칼라 변수 :  단일값을 저장하는 변수
                     2. 조합 변수   :  여러개값을 저장하는 변수
                     3. 바인드 변수  :  호스트 변수 

        ※ dba 는 pl/sql 개발자들에게 %type 을 사용하라고 꼭 권장을 해야합니다.

           v_sal   number(10);  ------------------->       v_sal    emp.sal%type;

              alter   table   emp
               modify   sal   number(20);

▣ 22. 부울 변수 선언

생각해 볼 문제

set serveroutput on
declare

  emp_sal1 number(10) := 50000;
  emp_sal2 number(10) := 60000;
  v_bool boolean;

begin
  v_bool := emp_sal1 < emp_sal2;
  dbms_output.put_line( v_bool ) ;

end;
/

부울 변수 선언은 다음과 같이 합니다.

v_bool boolean;
조건식은 논리 연산자 (and, or, not) 을 이용해서 변수의 값을 확인합니다.
뒤에서 배울 if 문과 while loop문에서 유용한 변수 입니다.

declare
flag boolean := FALSE;

begin
flag := TRUE;
end;
/

▣ 23. LOB 데이터 유형 변수

오라클은 큰 텍스트, 이미지, 동영상, 국가별 문자 텍스트를 저장할 수 있는
변수의 데이터 유형을 제공 합니다. 최대 128 테라바이트까지 저장할 수 있습니다.

CLOB : 큰 텍스트
BLOB : 이미지
BFILE : 동영상
NCLOB : 국가별 언어 큰 텍스트

▣ 24. 조합 데이터 유형

변수의 종류 3가지?  1. 스칼라 변수 : 단일값을 담는 변수
                   2. 조합 변수  : 여러개의 값을 담을 수 있는 변수

                        - 레코드 : 여러개의 데이터값을 가로로 담는 변수
                        - 컬렉션 : 여러개의 데이터값을 세로로 담는 변수

                   3. 바인드 변수

레코드는 파이썬의 리스트, C의 배열처럼, 마치 오라클 테이블의 행처럼 (하나만 저장)

컬렉션은 아래로 증가한다. 테이블을 insert하듯이 세로로(인덱싱) 데이터를 찾을 때 편안하게 찾을 수 있다. (여러개 저장)

▣ 25. 바인드 변수

바인드 변수는 호스트 환경에서 사용될 수 있어서 호스트 변수라고도 하는데
스타벅스에 가지고 갈 수 있는 텀블러를 연상하면 됩니다.
호스트 환경은 PL/SQL블럭 외의 환경을 말합니다.

바인드 변수는 sql 안에서도, PLSQL 블럭 안에서도 쓸 수 있다.

declare안에서만 쓸 수 있는 변수가 있는 반면,

실습:

variable v_salary number

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

print v_salary

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

설명: -  바인드변수(호스트 변수)를 PL/SQL 블럭과 SQL에서 사용하려면
             콜론(:) 을 앞에 붙여줘야합니다.

     -  바인드 변수 선언시  숫자 변수는  number 로 써야하고 number(10)
           으로 하면 오류가 납니다. 

           문자인 varchar2 는 varchar2 로 해도 되고 varchar2(10) 로 해도 됩니다.

문제12. 아래의 코드를 실행하는데 autoprint 기능을 켜고 실행하시오 !

variable v_salary number
set autoprint on

declare

v_empno number(6) := &empno;

begin
select sal into :v_salary
from emp
where empno = v_empno;

end;
/

문제13. 위의 코드를 활용해서 다음과 같이 수행되게 하시오

ename의 값을 입력하세요 ~ SCOTT

3000

답:

variable v_salary number
set autoprint on

declare

v_ename varchar2(10) := '&ename';

begin
select sal into :v_salary
from emp
where ename = v_ename;
end;
/

문제14. 위의 코드를 다시 수정해서 이번에는 이름을 입력할 때 소문자로
입력하던지 대문자로 입력하던지 무조건 결과가 나오게 코드를 수정하시오

variable v_salary number
set autoprint on

declare

v_ename varchar2(10) := upper('&사원이름');

begin
select sal into :v_salary
from emp
where ename = v_ename;
end;
/

v_ename emp.ename%type 스칼라

v_emp emp emp%rowtype 레코드 → 컬럼들의 값들을 담을

▣ 3장. 실행문 작성

▣ 26. PL/SQL 에서의 용어 정리

1. 식별자 : 변수명 입니다. 예: v_ename, v_sal
2. 구분자 : 연산자, 세미콜론 입니다. 예: + , - , ;
3. 리터럴 : 문자 데이터 값, 숫자 데이터 값 입니다. 예: scott, 7788
4. 주석 : Parser 에 의해서 실행되지 않게하고 싶을때 사용

			- 한줄 주석 :       v_ename    varchar2(10) ; -- 이름을 담을 변수를 선언합니다.

			- 여러줄 주석 :  /*   프로그램 이름:
                              프로그램 설명:
                              작성자 :
                              작성날짜 :
                              마지막 수정 날짜:
			                 */

▣ 27. PL/SQL 블럭 작성 지침

문자와 날짜는 반드시 양쪽에 싱글 쿼테이션 마크를 둘러줍니다.
가독성을 높이기 위해서 적절하게 공백과 들여쓰기를 해줍니다.

SQL developer 에서 ctl + f7 을 사용하면 자동으로 들여쓰기 해줍니다.

※ tip : tab 키와 shift + tab 키를 이용해서 코드 들여쓰기를 할 수 있습니다.

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

PL/SQL 에서 사용할 수 있는 함수 :

단일행 함수 : 문자, 숫자, 날짜, 변환, 일반
PL/SQL 에서 사용할 수 없는 함수 : 1. 그룹함수
decode 함수

예제: 문자를 입력하게 하고 문자의 철자의 갯수를 출력하는 PL/SQL문

set servetoutput 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;
/

[2023/07/20 점심시간 문제] 사원번호를 입력하면 해당 사원의 입사일이 출력되게 PLSQL 프로그램밍을 하세요 !

사원 번호를 입력하세요 ~ 7788

입사일: 82/12/22

set serveroutput on

accept p_empno prompt '사원번호를 입력하세요 ~'

declare
    v_hiredate date;
begin
    select hiredate into v_hiredate
      from emp
      where empno = &p_empno;

    dbms_output.put_line ('입사일 : ' || v_hiredate);

end;
/

입사일 : 82/12/22

PL/SQL 프로시저가 성공적으로 완료되었습니다.

문제15. (점심시간 문제2) 위의 코드를 수정해서 문자열을 물어보게하고
문자열을 입력하면 해당 문자열의 제일 끝 철자가 출력되게하시오 !

문자열을 입력하세요 ~ scott

set serveroutput on

accept p_str prompt '문자열을 입력하시오 ~ '

declare
    v_str varchar2(100) := '&p_str';
    v_result varchar2(10);
begin
    v_result := substr(v_str, -1, 1);
    dbms_output.put_line (v_result);
end;
/

t

PL/SQL 프로시저가 성공적으로 완료되었습니다.

▣ 29. PL/SQL 에서의 시퀀스(sequence) 사용법

시퀀스 → 번호생성기

예제:

my_seq 라는 이름으로 시퀀스 생성

create sequence my_seq
start with 1
increment by 1
maxvalue 100;
my_seq 를 사용하는 PL/SQL 코드 작성

set serveroutput on
declare
v_new_id number(10);
begin
v_new_id := my_seq.nextval;
dbms_output.put_line( v_new_id );
end;
/

▣ 30. PL/SQL 에서의 데이터 유형 변환

SQL에서의 암시적 유형 변환:

select ename, sal
from emp
where sal = '3000';

PL/SQL 에서의 암시적 변환 :

02-Feb-2000 이 현재 내가 접속한 세션의 날짜 포멧과 일치해서
오라클이 알아서 문자형 ---> 날짜형으로 암시적 형변환 하였습니다.
암시적 형변환에 실패 했습니다. 왜냐하면 February 02, 2000 이
현재 접속한 세션의 날짜포멧이 아니기 때문에 실패했습니다.

그래서 to_date 함수를 이용해서 명시적으로 형변환을 해줘야 합니다.

- 현재 접속한 세션의 날짜 형식을 확인하기
    
    select   *  from   nls_session_parameters;
    
    NLS_DATE_FORMAT	  RR/MM/DD

문제18. 암시적 형변환이 PL/SQL 에서 일어나는 경우를 테스트 하시오 !

set serveroutput on
declare
v_sal emp.sal%type := 0 ;
v_hiredate emp.hiredate%type := '23/07/20' ;

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

문제19. 문제 18번 코드가 책에 나온것 처럼 암시적 형변환이 실패할 수 있도록
날짜를 아래와 같이 영어로 변경해서 실행해보시오

set serveroutput on
declare
v_sal emp.sal%type := 0 ;
v_hiredate emp.hiredate%type := 'July 20,2023' ;

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

ORA-01841: 년은 영이 아닌 -4713 과 +4713 사이의 값으로 지정해야 합니다.

문제20. 문제19번 코드에 명시적 형변환 함수 to_date 를 사용하여 'July 20,2023' 를 그대로 사용하더라도 에러가 나지 않도록 코드를 수정하시오!

alter session set nls_date_language = 'english';

set serveroutput on

declare
v_sal emp.sal%type := 0 ;
v_hiredate emp.hiredate%type := to_date('February/02/2000','Month/DD/YYYY');

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

위의 코드의 에러를 피하기 위해 다음과 같이 날짜 언어 형식을 english 로
했는데

alter session set nls_date_language = 'english';

다시 원래대로 돌려놓겠습니다.

alter session set nls_date_language = 'KOREAN';

select *
from nls_session_parameters;

날짜언어가 KOREAN 으로 되어져 있어도 코드레벨에서 에러가 안나게 하는 코드

SET SERVEROUTPUT ON
DECLARE
v_sal emp.sal%TYPE := 0 ;
v_hiredate emp.hiredate%TYPE := TO_DATE('February/02/2000','Month/DD/YYYY', 'NLS_DATE_LANGUAGE=ENGLISH');

BEGIN
dbms_output.put_line( v_sal );
dbms_output.put_line( v_hiredate );
END;
/

▣ 31. PL/SQL 의 중첩 블럭 이해하기

PL/SQL 블럭을 중첩하는 이유 ? 여러 업무 요구사항을 지원하기 위해서 실행섹션에 논리적으로 많은 기능들이 포함되어져 있는 경우에 중첩을 하면 유용합니다.

DECLARE
    v_outer_variable VARCHAR2(20) := 'GLOBAL VARIABLE';  --- 글로벌 변수 

BEGIN
    DECLARE
        v_inner_variable VARCHAR2(20) := 'LOCAL VARIABLE';  -- 이 변수는 내부블럭에서만 
    BEGIN                                                   -- 엑세스 할 수 있습니다.
        dbms_output.put_line(v_inner_variable);
        dbms_output.put_line(v_outer_variable);
    END;

    dbms_output.put_line(v_outer_variable);
END;
/

결과: 
LOCAL VARIABLE
GLOBAL VARIABLE
GLOBAL VARIABLE

문제21. 위의 예제에서 v_inner_variable 내부변수가 내부블럭에서만 사용될 수 있는데
외부에서도 사용될 수 있는지 확인해보시오 !

답:

DECLARE
    v_outer_variable VARCHAR2(20) := 'GLOBAL VARIABLE';  --- 글로벌 변수 

BEGIN
    DECLARE
        v_inner_variable VARCHAR2(20) := 'LOCAL VARIABLE';  -- 이 변수는 내부블럭에서만 
    BEGIN                                                   -- 엑세스 할 수 있습니다.
        dbms_output.put_line(v_inner_variable);
        dbms_output.put_line(v_outer_variable);
    END;

    dbms_output.put_line(v_inner_variable);  -- 여기에 코드를 추가합니다.
    dbms_output.put_line(v_outer_variable);
END;
/

ORA-06550: 줄 11, 열27:PLS-00201: 'V_INNER_VARIABLE' 식별자가 정의되어야 합니다

내부블럭 변수는 외부 블럭에서 사용될 수 없습니다.

▣ 32. 변수 범위의 가시성

외부 블럭의 변수명과 내부 블럭의 변수명이 동일한 상황에서
내부 블럭에서 동일한 변수를 출력하려 하면 내부블럭의 변수가 사용됩니다.

예제:
DECLARE
    v_father_name    VARCHAR2(20) := 'Patrick';
    v_date_of_birth  DATE := '72/04/20';
BEGIN
    DECLARE
        v_child_name     VARCHAR2(20) := 'Mike';
        v_date_of_birth  DATE := '02/12/12';
    BEGIN
        dbms_output.put_line('Father''s Name: ' || v_father_name);
        dbms_output.put_line('Date of Birth: ' || v_date_of_birth);
        dbms_output.put_line('Child''s Name: ' || v_child_name);
    END;

    dbms_output.put_line('Date of Birth: ' || v_date_of_birth);
END;
/

Father's Name: Patrick
Date of Birth: 02/12/12
Child's Name: Mike
Date of Birth: 72/04/20

설명:  아들의 생일과 아빠의 생일이 잘못 출력되고 있습니다.

문제22. 위의 결과가 제대로 출력될 수 있도록 <> 레이블을 이용해서
코드를 수정하시오 !

begin  <<outer>>
DECLARE
    v_father_name    VARCHAR2(20) := 'Patrick';
    v_date_of_birth  DATE := '72/04/20';
BEGIN
    DECLARE
        v_child_name     VARCHAR2(20) := 'Mike';
        v_date_of_birth  DATE := '02/12/12';
    BEGIN
        dbms_output.put_line('Father''s Name: ' || v_father_name);
        dbms_output.put_line('Date of Birth: ' || outer.v_date_of_birth);
        dbms_output.put_line('Child''s Name: ' || v_child_name);
       dbms_output.put_line('Date of Birth: ' || v_date_of_birth);
    END;
END;
end outer;
/

Father's Name: Patrick
Date of Birth: 72/04/20
Child's Name: Mike
Date of Birth: 02/12/12

문제23. 아래의 번호의 코드가 실행하는 결과를 기술하시오 !

BEGIN << outer >> 
  DECLARE
        v_sal      NUMBER(7, 2) := 60000;
        v_comm     NUMBER(7, 2) := v_sal * 0.20;
        v_message  VARCHAR2(255) := ' eligible for commission';

    BEGIN
        DECLARE
            v_sal         NUMBER(7, 2) := 50000;
            v_comm        NUMBER(7, 2) := 0;
            v_total_comp  NUMBER(7, 2) := v_sal + v_comm;

        BEGIN
            v_message := 'CLERK not' || v_message;
            outer.v_comm := v_sal * 0.30;
            dbms_output.put_line( v_message ) ;  -- 답: 

        END;

        v_message := 'SALESMAN' || v_message;
    END;
END outer;
/

문제24. 아래의 변수가 출력하는 메세지를 댓글로 달아주세요

BEGIN << outer >> 
  DECLARE
        v_sal      NUMBER(7, 2) := 60000;
        v_comm     NUMBER(7, 2) := v_sal * 0.20;
        v_message  VARCHAR2(255) := ' eligible for commission';

    BEGIN
        DECLARE
            v_sal         NUMBER(7, 2) := 50000;
            v_comm        NUMBER(7, 2) := 0;
            v_total_comp  NUMBER(7, 2) := v_sal + v_comm;

        BEGIN
            v_message := 'CLERK not' || v_message;
            outer.v_comm := v_sal * 0.30;

        END;
        v_message := 'SALESMAN' || v_message;
        dbms_output.put_line(v_total_comp);  -- 답:

    END;
END outer;
/

문제25. 아래의 변수가 출력되는 값이 무엇인가 ?

BEGIN << outer >> 
  DECLARE
        v_sal      NUMBER(7, 2) := 60000;
        v_comm     NUMBER(7, 2) := v_sal * 0.20;
        v_message  VARCHAR2(255) := ' eligible for commission';

    BEGIN
        DECLARE
            v_sal         NUMBER(7, 2) := 50000;
            v_comm        NUMBER(7, 2) := 0;
            v_total_comp  NUMBER(7, 2) := v_sal + v_comm;

        BEGIN
            v_message := 'CLERK not' || v_message;
            outer.v_comm := v_sal * 0.30;
            dbms_output.put_line( v_comm); --  답:  

        END;
        v_message := 'SALESMAN' || v_message;

    END;
END outer;
/

문제26. 다음의 변수가 출력하는 값은 무엇인가 ?

BEGIN << outer >> 
  DECLARE
        v_sal      NUMBER(7, 2) := 60000;
        v_comm     NUMBER(7, 2) := v_sal * 0.20;
        v_message  VARCHAR2(255) := ' eligible for commission';

    BEGIN
        DECLARE
            v_sal         NUMBER(7, 2) := 50000;
            v_comm        NUMBER(7, 2) := 0;
            v_total_comp  NUMBER(7, 2) := v_sal + v_comm;

        BEGIN
            v_message := 'CLERK not' || v_message;
            outer.v_comm := v_sal * 0.30;
            dbms_output.put_line( outer.v_comm); --  답:   15000

        END;
        v_message := 'SALESMAN' || v_message;

    END;
END outer;
/

문제27. 다음의 변수가 출력되는 결과가 무엇인가 ?

BEGIN << outer >> 
  DECLARE
        v_sal      NUMBER(7, 2) := 60000;
        v_comm     NUMBER(7, 2) := v_sal * 0.20;
        v_message  VARCHAR2(255) := ' eligible for commission';

    BEGIN
        DECLARE
            v_sal         NUMBER(7, 2) := 50000;
            v_comm        NUMBER(7, 2) := 0;
            v_total_comp  NUMBER(7, 2) := v_sal + v_comm;

        BEGIN
            v_message := 'CLERK not' || v_message;
             dbms_output.put_line( outer.v_comm);  -- 답: 
            outer.v_comm := v_sal * 0.30;
       

        END;
        v_message := 'SALESMAN' || v_message;

    END;
END outer;
/

답: 12000

문제28. 다음의 변수가 출력하는 결과는 ?

BEGIN << outer >> 
  DECLARE
        v_sal      NUMBER(7, 2) := 60000;
        v_comm     NUMBER(7, 2) := v_sal * 0.20;
        v_message  VARCHAR2(255) := ' eligible for commission';

    BEGIN
        DECLARE
            v_sal         NUMBER(7, 2) := 50000;
            v_comm        NUMBER(7, 2) := 0;
            v_total_comp  NUMBER(7, 2) := v_sal + v_comm;

        BEGIN
            v_message := 'CLERK not' || v_message;
            outer.v_comm := v_sal * 0.30;
       
        END;
        v_message := 'SALESMAN' || v_message;
       dbms_output.put_line(v_comm);  -- 답: 

    END;
END outer;
/

문제29. 다음의 변수가 출력하는 결과는 무엇인가 ?

BEGIN << outer >> 
  DECLARE
        v_sal      NUMBER(7, 2) := 60000;
        v_comm     NUMBER(7, 2) := v_sal * 0.20;
        v_message  VARCHAR2(255) := ' eligible for commission';

    BEGIN
        DECLARE
            v_sal         NUMBER(7, 2) := 50000;
            v_comm        NUMBER(7, 2) := 0;
            v_total_comp  NUMBER(7, 2) := v_sal + v_comm;

        BEGIN
            v_message := 'CLERK not' || v_message;
            outer.v_comm := v_sal * 0.30;
       
        END;
        v_message := 'SALESMAN' || v_message;
       dbms_output.put_line(v_message);  -- 답: 

    END;
END outer;
/

문제30. 이름을 물어보게하고 이름을 입력하면 해당 사원의 월급이
출력되는 PL/SQL 코드를 작성하시오 !

이름을 입력하세요 ~ scott

3000

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;

dbms_output.put_line( v_sal );
end;
/

문제31. (오늘의 마지막 문제) 위의 코드를 수정해서 이름을 물어보게하고
이름을 입력하면 연봉이 출력되게하시오

연봉 계산 --> ( 월급 + 커미션) x 12

이름을 입력하세요 ~ scott

SCOTT 의 연봉은 ???? 입니다.

set serveroutput on

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

declare
    v_ename emp.ename%type := UPPER('&p_ename');
    v_yearsal emp.sal%type;
begin
    select (sal + nvl(comm, 0)) * 12 into v_yearsal
      from emp
      where ename = v_ename;
      
    dbms_output.put_line(v_ename || '의 연봉은 ' || v_yearsal || '입니다.');
end;
/

set serveroutput on

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

declare
    v_ename emp.ename%type := UPPER('&p_ename');
    v_sal emp.sal%type;
    v_comm emp.comm%type;
begin
    select sal, comm into v_sal, v_comm
      from emp
      where ename = v_ename;
      
    dbms_output.put_line(v_ename || '의 연봉은 ' || (v_sal + nvl(v_comm, 0)) * 12 || '입니다.');
end;
/

sskit

이전 포스트

Oracle DBA PL/SQL 230719

다음 포스트

Oracle DBA PL/SQL 230720

Oracle PL/SQL

▣ 22. 부울 변수 선언

▣ 23. LOB 데이터 유형 변수

▣ 24. 조합 데이터 유형

▣ 25. 바인드 변수

▣ 3장. 실행문 작성

▣ 26. PL/SQL 에서의 용어 정리

▣ 27. PL/SQL 블럭 작성 지침

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

▣ 29. PL/SQL 에서의 시퀀스(sequence) 사용법

▣ 30. PL/SQL 에서의 데이터 유형 변환

▣ 31. PL/SQL 의 중첩 블럭 이해하기

▣ 32. 변수 범위의 가시성

Oracle DBA PL/SQL 230719

Oracle DBA PL/SQL 230721

0개의 댓글