🐧PL/SQL(PROCEDURAL LANGUAGE EXTENSION TO SQL)

: SQL에 없는 변수 선언,선택 처리,반복 처리를 제공하는 절차적인 언어

◈ 세분분의 영역으로 구분하여 PL/SQL 작성
1. DECLARE 영역(선언부) : DECLARE - 변수를 선언하는 영역(선택)
2. EXECUTABLE 영역(실행부) : BEGIN - SQL을 포함한 다수의 명령을 작성하는 영역(필수)
3. EXCEPTION 영역(예외처리부) : EXCEPTION - 예외를 처리하기 위한 명령을 작성하는 영역(선택)

◈ 영역에서 하나의 명열을 구분하기 위해 [;] 사용
◈ 마지막 영역은 END 키워드로 마무리 후 [;] 사용
◈ PL/SQL 실행을 위해 마지막에 [/] 기호를 사용

🎨메세지를 출력할 수 있도록 세션의 환경변수 설정값 변경

SET SERVEROUT ON;

📌메세지를 출력하는 함수 - PL/SQL 실행부에서 호출하여 사용

형식) DBMS_OUTPUT.PUT_LINE(출력메세지)

🎨간단한 메세지를 출력하는 PL/SQL 작성

BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO, ORACLE!!!');
END;
/

◈ 변수 선언과 초기값 입력 - 선언부
형식) 변수명 [CONSTANT] 자료형 [NOT NULL][{:=|DEFULAT} 표현식]
◈ CONSTANT : 변수에 저장된 초기값을 변경하지 못하도록 설정하는 키워드 - 상수 선언
◈ NOT NULL : 변수에 NULL 사용 불가능
◈ [:=] : 대입연산자
◈ 표현식 : 변수에 저장될 값에 대한 표현 방법 - 값,변수(저장값),연산식(결과값),함수(반환값)

📌선언된 변수의 저장값 변경 - 실행부

형식) 변수명 := 표현식

🎨스칼라 변수

: 오라클 자료형을 사용하여 선언된 변수

📘스칼라 변수를 선언하여 값을 저장하고 화면에 변수값을 출력하는 PL/SQL 작성

DECLARE
    VEMPNO NUMBER(4) := 7788;
    VENAME VARCHAR2(20) := 'SCOTT';
BEGIN
    DBMS_OUTPUT.PUT_LINE('사원번호 / 사원이름');
    DBMS_OUTPUT.PUT_LINE('-----------------');    
    DBMS_OUTPUT.PUT_LINE(VEMPNO||' / '||VENAME);
    DBMS_OUTPUT.PUT_LINE('-----------------');    
    VEMPNO := 7893;
    VENAME := 'KING';
    DBMS_OUTPUT.PUT_LINE(VEMPNO||' / '||VENAME);
    DBMS_OUTPUT.PUT_LINE('-----------------');    
END;   
/

🎨레퍼런스 변수

: 다른 변수의 자료형 또는 테이블의 컬럼 자료형을 참조하여 선언된 변수 - 선언부

형식) 변수명 {변수명%TYPE|테이블명.컬러명%TYPE}

◈ 테이블에 저장된 행을 검색하여 컬럼값을 변수에 저장하는 명령 - 실행부
형식) SELECT 검색대상,검색대상,... INTO 변수명,변수명,... FROM 테이블명 [WHERE 조건식]
◈ 검색대상과 변수의 갯수와 자료형이 반드시 일치

📘EMP 테이블의 EMPNO 컬럼과 ENAME 컬럼의 자료형을 참조하여 레퍼런스 변수를 선언하고 EMP 테이블에서 사원이름이 SCOTT인 사원의 사원번호와 사원이름을 검색하여 레퍼런스 변수에 저장하고 출력하는 PL/SQL 작성

DECLARE
    VEMPNO EMP.EMPNO%TYPE;
    VENAME EMP.ENAME%TYPE;
BEGIN
    /* 주석문 처리 - 프로그램에 설명을 제공하는 문장 */
    /* 단일행을 검색하여 컬럼값을 레퍼런스 변수에 저장 - 다중행 검색시 에러 발생 */
    SELECT EMPNO,ENAME INTO VEMPNO,VENAME FROM EMP WHERE ENAME='SCOTT';
    DBMS_OUTPUT.PUT_LINE('사원번호 / 사원이름');
    DBMS_OUTPUT.PUT_LINE('-----------------');    
    DBMS_OUTPUT.PUT_LINE(VEMPNO||' / '||VENAME);
END;
/

🎨테이블 변수

: 테이블에 저장된 행을 여러개 검색하여 다수의 컬럼값을 저장하기 위해 선언하는 변수

형식) 테이블변수명 테이블타입명

◈ 테이블 변수를 생성하기 위해 테이블 변수의 자료형(테이블 타입)을 먼저 선언
형식) TYPE 테이블타입명 IS TABLE OF {자료형|변수명%TYPE|테이블명.컬럼명&TYPE} [NOT NULL][INDEX BY BINARY_INTEGER]

◈ 테이블 변수의 사용 방법 : 테이블 변수는 테이블 변수의 요소를 첨자로 구분하여 사용 - 첨자는 1부터 1씩 증가되는 숫자값
형식) 테이블변수명(첨자)

📘EMP 테이블의 EMPNO 컬럼과 ENAME 컬럼을 참조하여 테이블 변수를 선언하고 EMP 테이블에 저장된 모든 사원의 사원번호,사원이름을 검색하여 테이블 변수에 저장해서 출력하는 PL/SQL 작성

DECLARE
    /* 테이블 타입 선언 */
    TYPE EMPNO_TABLE_TYPE IS TABLE OF EMP.EMPNO%TYPE INDEX BY BINARY_INTEGER;
    TYPE ENAME_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER;
    /* 테이블 변수 선언 */
    VEMPNO_TABLE EMPNO_TABLE_TYPE;
    VENAME_TABLE ENAME_TABLE_TYPE;
    /* 테이블 변수의 요소를 반복처리 하기 위해 첨자 역활을 실행하기 위한 변수 선언 - 초기값 저장 */
    I BINARY_INTEGER := 0;
BEGIN
    /* EMP 테이블에 저장된 모든 사원의 사원번호,사원이름을 검색하여 테이블 변수의 요소에 저장하기 위한 반복문 */
    FOR K IN (SELECT EMPNO,ENAME FROM EMP) LOOP
        I := I + 1;
        VEMPNO_TABLE(I) := K.EMPNO;
        VENAME_TABLE(I) := K.ENAME;
    END LOOP;  
    DBMS_OUTPUT.PUT_LINE('사원번호 / 사원이름');
    DBMS_OUTPUT.PUT_LINE('-----------------');    
    /* 테이블 변수에 저장된 요소값을 출력하기 위한 반복문 */    
    FOR J IN 1..I LOOP
        DBMS_OUTPUT.PUT_LINE(VEMPNO_TABLE(J)||' / '||VENAME_TABLE(J));
    END LOOP;
END;
/

🎨레코드 변수

: 테이블의 저장된 하나의 행의 모든 컬럼값을 저장하기 위해 선언하는 변수

형식) 레코드변수명 레코드타입명

◈ 레코드 변수를 생성하기 위해 레코드 변수의 자료형(레코드 타입)을 먼저 선언
형식) TYPE 레코드타입명 IS RECORD RECORD
(필드명 {자료형|변수명%TYPE|테이블명.컬럼명%TYPE} [NOT NULL][{:=DEFAULT} 표현식],
필드명 {자료형|변수명%TYPE|테이블명.컬럼명%TYPE} [NOT NULL][{:=DEFAULT} 표현식],...)

📘EMP 테이블의 EMPNO,ENAME,JOB,SLA,DEPTNO 컬럼을 참조하여 레코드 변수를 선언하고 EMP 테이블에서 사원번호가 7844인 사원의 사원번호,사원이름,급여,업무,부서번호를 검색하여 레코드 변수에 저장해서 출력하는 PL/SQL 작성

DECLARE
    /* 레코드 타입 선언 */
    TYPE EMP_RECORD_TYPE IS RECORD(VEMPNO EMP.EMPNO%TYPE,VENAME EMP.ENAME%TYPE,VJOB EMP.JOB%TYPE
        ,VSAL EMP.SAL%TYPE,VDEPTNO EMP.DEPTNO%TYPE);
    /* 레코드 변수 선언 */    
    EMP_RECORD EMP_RECORD_TYPE;
BEGIN
    /* 검색행의 모든 컬럼값을 레코드 변수의 필드에 저장 - 검색행이 다중행인 경우 에러 발생 */
    SELECT EMPNO,ENAME,JOB,SAL,DEPTNO INTO EMP_RECORD.VEMPNO,EMP_RECORD.VENAME,EMP_RECORD.VJOB
        ,EMP_RECORD.VSAL,EMP_RECORD.VDEPTNO FROM EMP WHERE EMPNO=7844;   
   DBMS_OUTPUT.PUT_LINE('사원번호 = '||EMP_RECORD.VEMPNO);
   DBMS_OUTPUT.PUT_LINE('사원이름 = '||EMP_RECORD.VENAME);
   DBMS_OUTPUT.PUT_LINE('업무 = '||EMP_RECORD.VJOB);
   DBMS_OUTPUT.PUT_LINE('급여 = '||EMP_RECORD.VSAL);
   DBMS_OUTPUT.PUT_LINE('부서번호 = '||EMP_RECORD.VDEPTNO);
END;
/

◈ 레코드 타입 없이 테이블의 행을 참조하여 레코드 변수 선언 가능 - 행의 컬럼이 레코드 변수의 필드로 선언
형식)레코드변수명 테이블명%ROWTYPE

📘EMP 테이블의 EMPNO,ENAME,JOB,SAL,DEPTNO 컬럼을 참조하여 레코드 변수를 선언하고 EMP 테이블에서 사원번호가 7844인 사원의 사원번호,사원이름,급여,업무,부서번호를 검색하여 레코드 변수에 저장해서 출력하는 PL/SQL 작성

DECLARE
    EMP_RECORD EMP%ROWTYPE;
BEGIN
    /* EMP 테이블의 검색행(단일행)의 모든 컬럼값을 레코드 변수의 필드에 저장 */
    SELECT * INTO EMP_RECORD FROM EMP WHERE EMPNO=7844;
    DBMS_OUTPUT.PUT_LINE('사원번호 = '||EMP_RECORD.EMPNO);
    DBMS_OUTPUT.PUT_LINE('사원이름 = '||EMP_RECORD.ENAME);
    DBMS_OUTPUT.PUT_LINE('업무 = '||EMP_RECORD.JOB);
    DBMS_OUTPUT.PUT_LINE('급여 = '||EMP_RECORD.SAL);
    DBMS_OUTPUT.PUT_LINE('부서번호 = '||EMP_RECORD.DEPTNO);
END;
/