220204 동의어, PL/SQL-변수,조건,반복, 저장 프로시저

17. 동의어
    : 객체를 조회할 때마다 일일이 소유자를 지정해야 하는 번거로움을 해결하기 위해 간단히 정의하는 것.

 * 동의어 생성
       create (public) synonym 동의어명 for 계정.객체 (ex. sys.dual)
    - 비공개 동의어
        : 객체에 대한 접근 권한을 부여받은 사용자가 정의한 동의어. 해당 사용자만 사용 가능.

    - 공개 동의어
        : 권한을 주는 사용자(dba)가 정의한 동의어. 전체 사용자가 사용할 수 있음. public synonym 으로 정의
          ex) sys.dual ---> dual          sys.tab ---> tab           sys.seq ---> seq
            create public synonym pubdept for scott.dept;
            select*from dba_synonyms; --공개 동의어는 dba로 검색!

 * 동의어 삭제
       drop (public) synonym systbl;    --동의어 생성한 계정에서 삭제!


***PL/SQL

1. PL/SQL (oracle's Procedural Language extension to SQL)
   : sql문에서 변수 정의, 조건 처리(if), 반복 처리(loop, while, for) 등을 지원하며, 오라클의 절차적 언어(Procedure Language)로서의 단점을 보완해준다

 * 기능
   - 변수 선언
   - 비교 처리
   - 반복 처리

 * 구조
   - DECLARE ~ BEGIN ~ EXCEPTION ~ END 순서

   - DECLARE section (선언부)
      : declare로 시작 ---> 모든 변수나 상수를 선언하는 부분
   - EXECUTABLE section (실행부)
      : begin으로 시작 ---> 제어문, 반복문, 함수 정의 등 로직을 기술하는 부분
   - EXCEOTION section (예외 처리부)
      : exception으로 시작 ---> 예외 발생 시 이를 처리할 문장을 기술하는 부분

  * 간단한 메시지 출력

2. 변수 선언, 대입문
   : 선언부(declare) ---> 실행부에서 사용할 변수를 선언, 변수 선언 시 변수명 다음에 자료형 기술

 * 스칼라 변수
    : sql에서 사용하는 자료형과 유사한 선언 ---> number, varchar2
 * 레퍼런스 변수
    : 이전에 선언된 다른 변수 또는 DB 컬럼에 맞춰 변수를 선언
   - %TYPE : 칼럼 단위 참조
      : 참조하는 테이블의 해당 칼럼의 자료형과 크기를 그대로 참조해서 정의함
      : 컬럼의 자료형이 변경되어도 그 자료형과 크기를 그대로 참조하고 있기 때문에 변수 선언을 수정할 필요가 없음
      ex) vempno emp.empno%type;
           vename emp.ename%type;   --emp 테이블의 컬럼을 참조하는 변수라는 의미

   - %LOWTYPE : 로우(행) 단위 참조
      : DB 테이블 또는 view의 모든 컬럼을 record로 선언하기 위해 사용
      : 참조하는 테이블의 컬럼수와 데이터 형식을 몰라도 지정 가능함
      : select문으로 row를 검색할 때 유리
      ex) vemp emp%rowtype;     --emp의 컬럼명, 데이터타입, length를 그대로 가져옴

3. 조건문(선택문) =if문
 * 일반 if문
        if 조건 then 실행문 end if;

 * if~else문
        if 조건 then 실행문 else 조건 불만족시 실행문 end if;

 * 다중 if문
        if 조건1 then 실행문1 elsif 조건2 then 실행문2,..... else 조건 불만족시 실행문 end if;

4. 반복문
 * basic loop문 (조건 없는 반복)
        loop
          내용
          (if 조건 then exit; end if;)    --조건에 도달하면 루프가 끝나도록. 무한루프 방지
        end loop;

 * for loop문 (count로 반복 제어---> 반복되는 횟수가 정해진 반복문)
    : 반복문 선언할 때 범위와 증가치(1씩 증가)가 고정되기 때문에 제어할 수 없음
        for N in n1..n2 loop    --숫자 N의 범위 n1~n2 동안 1씩 증가 (n1<n2)
          내용
        end loop;

    - for N in REVERSE n1..n2 loop  --reverse: n1~n2 범위에서 1씩 감소(n1<n2)

 * while loop (조건 있는 반복)
    : 조건은 고정, 증가치는 제어 가능 (for loop와 차이)
        while 조건 loop
          내용
          (증가치 반드시 선언! 자꾸 까먹는데 까먹지 말고 오버플로우 안 하려면 꼭 쓰자)
        end loop;

4. 저장 프로시저★★★
   : PL/SQL을 저장해놓고 필요에 따라 호출해 사용할 수 있게 오라클이 제공하는 것
     ---> 성능 향상, 재활용 가능, 간단함, 호환성 문제 해결
        create [or relace] procedure 프로시저명
          (argument [mode] 데이터타입) --매개변수 필요하다면 작성
          [mode]---> in(데이터 전달받을 때) out(수행 결과 리턴 시) inout)
        IS
          로컬변수 (필요하다면 작성)
        begin      --호출 시 실행될 PL/SQL
          내용
        end;
    +) 프로시저 실행
        exec(ute) 프로시저명;

    ※주의! 프로시저 생성 시, 각 블록마다 범위 잡고 하나씩 실행해야 함 안그럼 지멋대로 범위 잡힘

   01. 매개변수 없는 저장 프로시저

   02. 매개변수 있는 저장 프로시저

   03. 매개변수 여러 개 있는 저장 프로시저
      - [mode]
         in: 매개변수로 데이터를 전달받는 역할
         out: 매개변수로 값을 리턴하는 역할
         inout: 둘 다 (쓸 일이 있을까?)

      - out으로 리턴받을 데이터를 저장할 바인드 변수를 미리 선언해야 함
          바인드 변수: 프로시저 결과 돌려받는 변수
          ---> 레퍼런스 변수로 선언 시 오류 발생, 스칼라 변수로 선언!
      - out 매개변수의 값을 리턴받을 때, 변수 앞에 [ : ] 를 붙여야 함!
      - 출력 시 print 사용 ---> dbms 어쩌고 오류남
      ※ 오류 나는 것들 이유: 얘네 다 begin 안에서 또는 그런 구문 안에서 돌려야 작동하는 애들이라서 그런듯
                                   이 예시에서는 구문 밖에서 결과값만 출력하는 형식을 취하고 있으니까... 내 추측임

 

 

과제
--emp 테이블에서 ename 검색해 그 사원의 직급job을 구하는 프로시저 생성 후 실행