[PLSQL]23.07.20

망구씨·2023년 7월 20일

바인드변수 부울변수선언 조합 데이터 유형 중첩 블록 호스트변수

PL/SQL

목록 보기

2/8

✏️ 오늘의 TIL

부울 변수 선언
LOB데이터 유형 변수
조합 데이터 유형: 레코드 및 컬렉션
바인드 변수
바인드 변수와 autoprint
PL/SQL 블록의 렉시칼 단위
PL/SQL에서 사용할 수 있는 함수와, 사용할 수 없는 함수
PL/SQL에서의 시퀀스 사용
PL/SQL에서의 데이터 유형 변환
PL/SQL 중첩 블록 이해하기

✏️ 복습

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

PL/SQL 기본 블럭의 구조

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

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

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

[점심시간 문제]

set  serveroutput  on
accept   p_empno  prompt  '사원번호를 입력하세요!'
declare
            v_hiredate     emp.hiredate%type;           
begin
           select to_char(hiredate, 'RR-MM-DD') into v_hiredate
            from emp 
            where  empno = &p_empno;
           
dbms_output.put_line('입사일: ' || v_hiredate );
end;
/

부울 변수 선언

✔️ TRUE, FALSE 및 NULL 값만 부울 변수에 할당할 수 있습니다.
✔️ 조건식은 논리 연산자 AND 및 OR 그리고 단항 연산자 NOT을
사용하여 변수 값을 확인합니다.
✔️ 변수는 항상 TRUE, FALSE 또는 NULL을 반환합니다.
✔️ 산술, 문자 및 날짜 표현식은 부울 값을 반환하는 데 사용될
수 있습니다.

emp_sal1 := 50000;
emp_sal2 := 60000;

emp_sal1 < emp_sal2

DECLARE 
  flag BOOLEAN := FALSE;
BEGIN 
  flag := TRUE;
END;

LOB데이터 유형 변수

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

CLOB(Character Large Object) : 큰 텍스트
BLOB(Binary Large Object) : 이미지
BFILE(Binary File) : 동영상
NCLOB(National language Character Large Object) : 국가별 언어 큰 텍스트

조합 데이터 유형: 레코드 및 컬렉션

조합 데이터 유형은 레코드와 컬렉션으로 나뉜다.

✅ 오라클 변수의 종류는 3가지가 있다.

스칼라 변수 : 단일값을 담는 변수
조합 변수 : 여러개의 값을 담을 수 있는 변수
- 레코드 : 여러개의 데이터값을 가로로(레코드 단위로) 담는 변수
- 컬렉션 : 여러개의 데이터값을 세로로 담는 변수
바인드 변수

💡 emp테이블의 모든 컬럼을 다 담고싶다면 (레코드 변수 선언)

v_emp   emp%rowtype // 열모음

위 퀴즈는 어제 배운 %TYPE속성에 관한 퀴즈인데, 2번이 레코드변수에 관한 내용이라서 위 퀴즈의 답은 2번이다. 문제는 %type속성에 대한 맞는말이 무엇인지 물어보는 퀴즈이기 때문

바인드 변수

✅ 호스트 환경에서 생성됩니다.
✅ 호스트 변수라고도 합니다. (호스트 환경에서 사용될 수 있어서!)
✅ VARIABLE 키워드를 사용하여 생성됩니다.
✅ SQL 문과 PL/SQL 블록에서 사용됩니다.
✅ PL/SQL 블록이 실행된 후에도 액세스할 수 있습니다.
✅ 앞에 콜론을 사용하여 참조합니다.
✅ PRINT 명령을 사용하여 값을 출력할 수 있습니다.
✅ SQL*Plus 및 SQL Developer를 사용할 경우 필수입니다.

호스트 환경이란?
PLSQL블럭 외의 환경을 말한다. (declare ~ end 밖쪽)

호스트 변수란?
💡 텀블러 같은 것. 스타벅스에서 담은걸 다른 카페에서도 쓸 수 있으니.
declare ~ end 안에서만 쓸 수 있는 변수가 있는 반면에 바인드변수는(호스트변수) PLSQL 블럭 외의 환경에서도 사용이 가능하다.

바인드 변수 실습

VARIABLE v_salary NUMBER

begin select sal into :v_salary // 여기 : 이거 붙여야 사용가능
  from emp
  where empno = 7788;
end;
/

✅ 호스트 변수를 호스트환경에서 출력하려면 프린트에서 확인하면 됩니다.

print v_salary

✅ 바인드변수 (호스트변수)를 PL/SQL 블럭과 SQL에서 사용하려면 콜론(:)을 앞에 붙여줘야합니다.
✅ 바인드 변수 선언시 숫자 변수는 NUMBER로 써야하고 number(10) 이런식으로 괄호 사용하면 에러난다. 문자는 길이를 varchar2, varchar2(10)이렇게 길이 써도 에러나지 않는다.
✅ 아래는 sql에서 v_salary를 사용하는 방법. :을 둘러준다!:

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

바인드 변수와 autoprint 사용

문제 12. autoprint기능을 켜고 실행하기

VARIABLE v_salary NUMBER
set autoprint on

declare
 v_empno number(10) :=&empno; // 치환변수 할당할 수 있다.

begin 
  select sal into :v_salary
  from emp
  where empno = v_empno;
end;
/
✅ 치환변수를 실행하면 항상 값을 입력하라고 물어본다. 내가 값을 입력받아서 같은 SQL을 실행하고싶다면 치환변수를 사용하면 된다.
✅ 치환변수에 입력되는 문자가 그대로 나타난다!

? accept 절과 다른것이 무엇?

문제 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. 위 코드를 다시 수정해서, 이번에는 이름을 입력할 때 소문자로 입력하던지 대문자로 입력하던지 무조건 결과가 나오게 코드를 수정하기

위처럼 declare절에 upper함수를 사용하면 된다. 그런데 혹시 emp테이블에 대문자인지 소문자인지 모르겠다면 where절에 사용하면 된다. 그렇지만 이 방법은 좌변을 가공한거라서 악성sql이다.

위 악성 sql을 튜닝하는 방법은 뒤에서 배우니 아래코드는 참고만 하기
(함수 기반 인덱스 생성)

create index emp_ename_fum
  on emp(upper(ename));

💡참고!💡

월급과 이름 두개 출력해보기 (begin절 확인)
VARIABLE v_salary NUMBER // 바인드 변수를 만드려면 무조건 VARIABLE로 선언
VARIABLE v_job varchar2(10) // 선언 !
set autoprint on

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

begin 
  select sal, job into :v_salary, :v_job
  from emp
  where upper(ename) = v_ename;
end;
✅ 바인드 변수를 만드려면 VARIABLE로 선언이 꼭 필요하다. 출력하고싶은 바인드변수는 선언이 되어있어야하고(아니라면 에러남), 한번 선언을 했다면 이후에는 쓰지 않아도 출력이 된다.
✅ 일반 내부함수 생성은 VARIABLE절로 선언할 필요는 없고 dbms~~~그거 하단에 작성하면 됨.

📖 3장 실행문 작성

배우게 될 것들!
✅ PL/SQL 블록의 렉시칼 단위 파악
✅ PL/SQL의 내장 SQL 함수 사용
✅ 암시적 변환이 발생하는 경우와 명시적 변환으로 처리해야
하는 경우 설명
✅ 중첩 블록 작성 및 레이블로 변수 한정
✅ 적절한 들여쓰기로 읽기 쉬운 코드 작성
✅ PL/SQL 표현식에서 시퀀스 사용

PL/SQL 블록의 렉시칼 단위

식별자 : 변수명 입니다.

ex) v_ename, v_sal....

구분자 : 연산자

ex) +  -  ;

리터럴 : 값들

ex) scott, 7788

주석 : -- 는 한줄주석, /* */는 여러줄 주석! parser에 의해 실행되지 않게하고 싶을 때 사용

ex)
/*
프로그램 이름:
프로그램 설명:
작성자:
작성 날짜:
마지막 수정날짜:
*/

PL/SQL 블록 구문 및 작성 지침

✅ 문자와 날짜는 반드시 양쪽에 싱글 쿼테이션 마크를 둘러줍니다.
✅ 가독성을 높이기 위해서 적절하게 공백과 들여쓰기를 해줍니다.
- SQL developer 에서 ctl + f7 을 사용하면 자동으로 들여쓰기 해줍니다.
💡 tip : tab 키와 shift + tab 키를 이용해서 코드 들여쓰기를 할 수 있습니다.

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

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

단일행 함수 (문자, 숫자, 날짜, 변환, 일반)

✅ PL/SQL에서 사용할 수 없는 함수

그룹함수, decode(단일행)

예제. 문자를 입력하게하고, 문자의 철자 갯수를 출력하는 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;
/

begin절에서 v_result := length(v_str) 를 사용하려면 declare에서 선언해주어야 한다.

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

set serveroutput on
accept p_str prompt '문자열을 입력하시오'

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

문제 16. 숫자를 물어보고, 숫자 1을 입력하면 true출력, 0을 입력하면 false를 출력하게 하시오 (decode사용해보기)

set serveroutput on
accept p_num prompt '숫자를 입력하세요'

declare 
    v_num  number(20) := &P_num;
    v_result varchar2(20);
begin
    v_result := decode(v_num,1, 'true', 0, 'false');
    dbms_output.put_line(v_result);
end;
/

문제 17. SQL문장에 DECODE를 써서 위 결과가 실행되게 하기

set serveroutput on
accept p_num prompt '숫자를 입력하세요'

declare 
    v_num  number(20) := &P_num; // 숫자니까 ' ' 필요없음
    v_result varchar2(20);
begin
    select decode(v_num,1, 'true', 0, 'false') into v_result
    from dual;

    dbms_output.put_line(v_result);
end;
/

PL/SQL에서의 시퀀스 사용

우리는 11g이상이다! 이전은 plsql에서 사용을 못했나보다. select, into를 사용한것보니 !
실습

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

✅ 100번 넘어가니 에러가 난다!

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

✔️ 암시적 : 오라클이 알아서 데이터 유형을 변환시킨다.
✔️ 명시적 : 아래의 함수들을 이용해서 데이터 유형을 변환시킨다.

SQL에서의 암시적 유형 변환

select ename, sal
  from emp
  where sal = '3000'; 
  // 숫자인데 싱글쿼테이션 둘러도 실행이 암시적 유형 변환이 일어나서 된다.

1. 02-Feb-2000이 현재 내가 접속한 세션의 날짜 포멧과 일치해서 오라클이 알아서 문자형 ---> 날짜형으로 암시적 형변환 하였습니다.
2. 암시적 형변환에 실패했습니다. February 02, 2000이 현재 접속한 세션의 날짜포멧이 아니기때문에!
3. 2처럼 실패했다면 to_date함수를 이용하여 명시적으로 형변환을 해야합니다.

🤔 현재 접속한 세션의 날짜 형식을 확인해보자
select * from nls_session_paramiters;

문제 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;
/   
데이터가 잘 나오긴하지만 암시적 형변환이 일어난 것이다.
v_hiredate emp.hiredate%type := '23/07/20';는 문자형으로 작성된것이다. 그렇지만 hiredate는 날짜형! 에러가 나지 않았던 이유는 날짜형식이 맞았기 때문.
'23/07/20'; 부분을 to_date('23/07/20','RR/MM/DD')로 변경해주면 명시적 형변환이다.

문제 19. 위 문제가 책에 나온것처럼 암시적 형변환이 실패하도록 날짜를 아래와 같이 영어로 변경해서 설명해보시오

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

문제 20. 문제 19번 코드에 명시적 형변환 함수 to_date를 사용하여 에러가 나지 않게 바꾸시오 (날짜의 언어 형식을 english로 바꿔야 실행됨!)

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

✅ alter session set nls_date_language='korean'; 으로 바꿔놓기

💡 날짜언어가 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;
/

✅ to_date()안에 'NLS_DATE_LANGUAGE=ENGLISH' 혹은 'NLS_DATE_LANGUAGE=AMERICAN' 넣어주기
❓ 코드레벨이란 이 코드에서만 !

PL/SQL 중첩 블록 이해하기

✅ 서브쿼리에서 어떤값을 출력하여 메인쿼리에서 사용한 것 처럼, 서브블럭에서 어떤 데이터를 메인블럭에서 사용한다. (그냥 서브쿼리 생각하면 이해가 쉽다는 이야기임)
✅ PL/SQL 블럭을 중첩하는 이유
1. 여러 업무 요구사항을 지원하기 위해서 실행
2. 실행섹션에 논리적으로 많은 기능들이 포함되어져 있는 경우

예제

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;

출력 결과 ↓

문제 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;

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

변수 범위 가시성

외부블럭과 내부블럭이 있다. 바깥쪽(외부) 변수 이름과 내부 변수명이 같은 이름이면 어떤것이 먼저 실행될까 -> 내부가 먼저 실행된다.
그래서 아들의 생일과 아빠의 생일이 잘못 출력되고있다.

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);
    END;

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

⭐ 그렇다면 외부 변수를 출력하고싶다면(위 결과 제대고 만드려면)!! ⭐

문제 22. 위 결과가 제대로 출력되도록 <<outer>>레이블을 사용해보기

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

잘 나옵니다!!

< 문제 23~29 관련 이미지>

문제 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;
/

✅ 답 : CLERK not eligible for commission

문제 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;
/

✅ 답: 0

문제 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); --  답:  

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

    END;
END outer;
/

✅ 답: 15000

문제 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;
/

✅ 외부 DECLARE만 확인하면 12000 이지만, 내부 BEGIN에 확인하면 outer.v_comm := v_sal * 0.30; 로 외부 v_comm값이 변경되었으므로 정답은 15000입니다!

문제 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;
/

✅ 답: SALESMANCLERK not eligible for commission

PL/SQL 연산자

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

set  serveroutput  on
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. 위 코드 수정해서 이름을 물어보게하고, 이름을 입력하면 연봉이 출력되게 하시오 연봉 -> (월급 + 커미션) x12

scott 의 연봉은 ---- 입니다.

처음에 쓴 답
: v_total의 초기값에 아예 다 만들어버리고 begin절에서 그값을 출력만 하고싶었는데 에러가 났다. 그래서 select 절에서 다시 연산을 했음.
1.

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; 
            v_total     number(10,2) := (v_sal + nvl(v_comm,0)) * 12;
begin
           select  (sal + nvl(comm,0)) * 12 into v_total
           from emp 
           where ename = v_ename;
  
dbms_output.put_line( v_ename || '의 연봉은' || v_total || '입니다!');
end;
/

수정 후

set  serveroutput  on
accept   p_ename  prompt  '이름을 입력하세요!'
declare
            v_ename     emp.ename%type := upper('&p_ename');
            v_sal       emp.sal%type; 
begin
           select  (sal + nvl(comm,0)) * 12 into v_sal
           from emp 
           where ename = v_ename;

dbms_output.put_line( v_ename || '의 연봉은' || v_sal || '입니다!');
end;
/

에러가 났던 PLSQL (원래 내 의도)
1.

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; 
            v_total     number(10,2):= (v_sal + nvl(v_comm,0)) * 12; 
begin
 v_total  ;   
dbms_output.put_line( v_ename || '의 연봉은' || v_total || '입니다!');
end;
/

✅ DECLARE에서는 선언만 하는거라 사실상 연산쓴게 다 빈컵만 있는거라서 의미가 없다고 함.. BEGIN절에서 데이터를 불러오고, 그 데이터로 계산을 해야하는데 그걸 못하고 있는 것!
2. 이렇게도 쓸 수 있고

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; 
  v_total number(10,2);
begin
  select sal, comm
    into v_sal, v_comm
    from emp
   where ename = v_ename;

  v_total := (v_sal + nvl(v_comm, 0)) * 12;
  dbms_output.put_line(v_ename || '의 연봉은 ' || v_total || '입니다!');
end;
/

이렇게도 할 수 있다.

set serveroutput on

DECLARE
    v_ename emp.ename%TYPE := upper('&사원이름');
    v_sal emp.sal%TYPE;
    v_comm emp.comm%TYPE;
BEGIN
    SELECT sal, nvl(comm, 0) INTO v_sal, v_comm
    FROM emp
    WHERE ename = v_ename;

    dbms_output.put_line(v_ename||'의 연봉은 : ' || (v_sal+v_comm)*12);
END;
/

망구씨

Slow and steady wins the race.

이전 포스트

[PLSQL]23.07.19

다음 포스트

[PLSQL]23.07.21

1개의 댓글

happy

2023년 7월 20일

글이 많은 도움이 되었습니다, 감사합니다.

답글 달기