🌞 Day 042 

• PL/SQL : 조건문, 반복문, 함수, Procedure(프로시져)

 🍂 IF - 연습 문제 

• emp 테이블에서 사원 한 명을 선별(사원번호 7876)하여 그 월급(salary)의 금액에 따라 낮음, 중간, 높은 이라는 단어를 출력하는 익명 블럭을 제작
  (1~1000 낮음, 1001~2500 중간, 2501~ 높음)

• 사원을 선별하는 방법은 DBMS_RANDOM.VALUES 함수를 이용

• 랜덤한 부서번호로 조회하되, 그 부서에 사원이 여럿이면 첫번째 사원으로 선택할 것

• 랜덤하게 부서번호를 발생

  • DBMS_RANDOM.VALUE(시작숫자, 끝숫자)
    : 시작숫자부터 끝 숫자 사이의 임의 숫자를 발생
    

  • ROUND(숫자, 반올림 자리수)
    : 숫자를 지정된 반올림자리에서 반올림 한다.

  • 반올림자리수 1이면 소수 둘째 자리에서 반올림해서 첫째자리까지 남긴다. 반올림 자리수가 -1이면 1의 자리에서 반올림 한다.

  • rownum
    : 오라클에서 제공하는 암묵적 필드명, select 명령의 결과를 대상으로 1부터 순차적으로 부여한 행번호

 🍂 CASE 문 

• CASE문 위 코드를 CASE문으로 바꿨을 때
  시작과 끝 : IF ⇒ CASE / 조건 : IF, ELSIF ⇒ WHEN
  

• CASE문은 두 가지 유형이 있다.

  • CASE 문 : 유형 1 - IF문과 비슷
    • 
  • CASE 문 : 유형 2
    표현식의 결과 값 또는 변수 값들의 경우의 수로 분가한다.
    SWITCH CASE와도 비슷하다.
    • 

 🍂 LOOP문 

반복실행 유형 1

• LOOP
      실행문;
      EXIT [WHEN 조건];
  END LOOP;

• 

반복실행 유형 2

• WHILE 조건
  LOOP
      실행문;
  END LOOP;

• 

WHILE과 EXIT WHEN의 혼합 사용

 🍂 FOR문 

• FOR 변수명 IN [REVERSE] 시작값..끝값
  LOOP
      실행문
  END LOOP;
  시작값과 끝값 사이에 .. (온점2개) 빼먹지 말 것!!

• 시작값부터 끝 값까지 반복 실행, REVERSE 쓰여진 경우, 반대방향의 숫자 진행으로 반복실행 (REVERSE 1..9 이면 9부터 1까지..)
  

CONTINUE

GOTO 문

NULL

• IF문 또는 CASE WHEN 등에서 해당 경우에 실행해야 할 명령이 하나도 없을 때 쓰는 구문
  • IF vn_variable = 'A' THEN
        처리로직1;
    ELSIF vn_variable = 'B' THEN
        처리로직2;
    ELSE
        NULL;
    END IF;
    
    
  • CASE WHEN vn_variable = 'A' THEN
            처리로직1;
        WHEN vn_variable = 'B' THEN
            처리로직2;
        ELSE
            NULL;
    END CASE;

 🌱 Function 

• PL/SQL 코드 작성시에는 지금까지 사용하던 익명 블럭은 잘 사용하지 않는다.

• 일반적으로 이름이 있는 서브 프로그램(함수) 또는 프로시저를 사용하는 것이 대부분이다.

• 익명 블럭은 한번 사용하고 나면 없어져 버리는 휘발성 블럭이지만

• 함수 또는 프로시저는 컴파일을 거쳐 데이터베이스에 저장되어 재사용이 가능한 구조 이다.

 🍂 함수의 형태 

• 

• 상세내용

  • CREATE OR REPLACE FUNCTION
    : CREATE OR REPLACE FUCTION이라는 구문을 이용하여 함수를 생성한다.
  • 함수를 만들고 수정하더라도 이 구문을 계속 컴파일 할 수 있고, 마지막으로 컴파일 한 내용이 함수의 내용과 이름으로 사용된다.
  • 매개변수
    : 전달인수를 저장하는 변수로 "변수이름 변수의 자료형" 형태로 작성한다.
  • 첫 번째 RETURN 구문 다음에는 리턴될 자료의 자료형을 쓰고, 아래쪽 두 번째 RETURN 구문 옆에는 그 자료형으로 실제 리턴될 값 또는 변수이름을 써준다.
    
    

• 두 개의 정수를 전달해서 첫 번째 값을 두번째 값을 나눈 나머지를 구해서 리턴해주는 함수
  

 📒 연습 문제 1 

• 도서번호를 전달인수로 전달하여, BOOKLIST에서 해당 도서 제목을 리턴받는 함수를 제작하기
  

 📒 연습 문제 2 

• 위의 함수의 기능 중 전달된 도서번호로 검색된 도서가 없다면, '해당 도서 없음'이라는 문구가 리턴되도록 수정하기

• 조건에 맞는 레코드개수를 조회해서 그 숫자가 0이면 해당 도서는 없는 것으로 처리한다.
  

• 매개변수가 없는 함수
  

 📒 연습 문제 3 

• emp 테이블에서 각 부서번호를 전달 받아서 급여의 평균값을 계산하여 리턴하는 함수를 제작
• 전달된 부서 번호의 사원이 없으면 급여 평균은 0으로 리턴
  

 🌱 Procedure (프로시져) 

• 프로시져?

  • 함수와 비슷한 구조이다.
  • 함수는 결과 리턴이 있지만, 프로시져는 결과 리턴이 없는 것이 특징이다. (리턴을 위한 별도의 코드(변수)가 있다.)
  • return 키워드 대신, 리턴 역할을 하는 변수를 필요 개수만큼 만들어서 사용한다.
    
    

• 프로시져의 생성

  • 

  • CREATE OR REPLACE PROCEDURE : 프로시져를 생성하는 구문

  • 매개변수명1 [ IN | OUT | IN OUT ]
    : 매개변수를 만들되 전달되는 전달인수를 받는 IN변수와
    리턴역할을 할 수 있는 OUT변수를 만들 때 사용한다.
    입력변수와 출력변수의 역할이 동시에 부여되려면 IN OUT을 같이 기술한다.

  • 프로시져는 기본적으로 리턴값이 없지만
    (실제 RETURN명령을 사용하지 않음)
    변수의 속성에 OUT속성 하나를 부여함으로써 리턴 역할을 흉내낼 수 있게는 사용이 가능하다.

  • 변수 속성이 IN인경우 생략이 가능하다.

 🍂 RENTLIST 테이블에 레코드를 추가하는 프로시져 

 🍂 IN, OUT, INOUT 매개변수의 사용 #1 

• newRentList 프로시져에서 레코드를 추가한 후 입력된 레코드의 대여날짜를 다시 조회해서 호출한 곳으로 되돌려보내고 출력하기

 🍂 IN, OUT, INOUT 매개변수의 사용 #2 

• OUT과 IN OUT 변수의 차이

• IN과 IN OUT 변수의 차이

• 매개변수 인수 전달시, 순서변경
  

 📒 연습 문제 : 프로시져 연습 

• 익명블럭에서, 사원번호를 전달하면, 사원의 이름과 부서명을 리턴받아 출력하는 프로시져(geEnameDname())을 작성하기

• 익명 블럭에서 호출은 geEnameDname(7788, ename, dname);

 📒 연습 문제 : RETURN 문  

• 프로시저에서 RETURN은 값을 리턴하겠다는 명령이 아니고, 현시점에서 프로시저를 끝내겠다는 뜻
• 아래 프로시저에서 전달된 사원번호의 사원이 없으면 '해당 사원이 없습니다.'라고 출력하고 종료하게 수정하기
• 익명 블럭 출력은 ‘이름 : 없음, 부서명 : 없음’ 으로 출력하기

 📒 연습 문제 : MEMBERLIST 레코드 추가  

• MEMBERLIST테이블에 필요한 자료들(이름, 전화, 포인트, 성별)을 전달인수로 보내서 레코드르 추가하는 프로시져 만들기
• 프로시저 이름은 insertOrUpdateMember
• 전화번호를 조회해서 같은 이름과 번호가 있으면, 동명이인 이므로 추가가 아니라 해당 회원을 수정하게 만들기
• 추가, 수정 후에는 결과('수정', '추가')를 리턴 받아서 추가된 건지 수정된건지 출력하기
• 회원 추가시 회원번호는 시퀀스로 가입일은 sysdate로 입력하기

• 해설 : 내 코드와 비교

 🌱 CURSOR 

• 주로 프로시져 내부의 SQL 명령 중 SELECT 명령의 결과가 다수의 행으로 얻어졌을 때 사용하는 결과를 저장하는 메로리 영역을 말한다.

• 위의 익명블럭은 SELECT 명령의 결과가 1행(ROW) 이므로 실행이 가능하지만,
  SELECT 명령의 결과가 2행 이상이라면 에러가 발생한다.
• 2행 이상의 결과를 담을 수 있는 메로리 영역(또는 변수)으로 사용하는 것이 CURSOR이며 자바의 리스트와 비슷한 구조를 가지고 있다.
  또는 반복 실행문을 이용하여 그 값들을 참조하고 출력하고 리턴할 수 있다.

1. CURSOR의 생성 (정의)

• CURSOR 사용할 커서의 이름 [ (매개변수1, 매개변수2, ...) ]
  IS
  SELECT ...... SQL문장

• 매개변수의 역할
  : SELECT 명령에서 사용할 값들을 저장
  (주로 WHERE 절에서 사용할 값들)

• SELECT .... SQL 문장
  : 실행되어 CURSOR에 결과를 안겨줄 SQL 명령

2. CURSOR 의 OPEN (호출)

• OPEN 커서이름 (전달인수1, 전달인수2, ... ) ]

• 실제로 전달인수를 전달하여 커서안의 SQL문을 실행하고 결과를 커서에 저장한다.

3. 결과를 반복 실행문과 함께 필요에 맞게 처리

• LOOP
  FETCH 커서이름 INTO 변수(들);
  EXIT WHEN 커서이름%NOTFOUND;
  -- SELECT에 의해 얻어진 레코드가 다 소진되어 없을 때까지 반복 계속
  END LOOP;

• FETCH 커서이름 INTO 변수(들);
  커서에 담긴 데이터들 중 한 줄씩 꺼내서 변수(들)에 넣는 동작

• EXIT WHEN 커서이름%NOTFOUND;
  꺼냈는데 데이터가 없으면 종료

• LOOP 안에서 필요에 맞는 처리를 데이터가 없을 때까지 반복

4. CURSOR 닫기

• CLOSE 커서명