[SQL] PL/SQL – 타입 변수 선언, SELECT, INSERT문 처리

chan10 2021. 3. 23. 08:47

PL/SQL

o Procedural Language extension to SQL의 약자로 오라클 자체에 내장되어 있는 절차적 언어이다.

o SQL의 단점을 보완하여 SQL문장 내에서 변수의 정의, 조건 처리, 반복 처리 등 지원한다.

o PL/SQL 유형에는 2가지 유형이 있다.

익명블록 : 이름이 없어 사용 후 재호출이 불가능한 PL/SQL구문

PROCEDURE & FUNCTION : 이름이 있어 사용 후 재호출이 가능한 PL/SQL구문

o 구조

구조(익명블록)
DECLARE           -- 선언부
변수선언
BEGIN               -- 실행부
제어문, 반복문, 함수 등
EXCEPTION          -- 예외처리부
END;
/

o SET SERVEROUTPUT ON;

프로시저를 사용하여 출력하는 내용을 화면에 보여주도록 설정하는 환경변수로 기본 값이 OFF이기에 ON으로 변경

SET SERVEROUTPUT ON;

BEGIN
DBMS_OUTPUT.PUT_LINE(‘HELLO WORLD’);
END;
/
* PUT_LINE이라는 프로시저를 이용하여 출력(DBMS_OTUPUT패키지에 속해있음)

o 변수 선언하기

o 변수 선언 형식 -> 변수명 자료형(길이) [:= 값];

o 변수에 값 넣기 -> 변수명 := 값

* Oracle은 := 기호로 변수에 값을 대입한다.

o 변수의 자료형 알아보기

o 자료형은 기본자료형(ORACLE 자료형), 복합자료형으로 구분

o 기본 자료형 : NUMBER, VARCHAR2, DATE, BOOLEAN, BINARY_INTEGER

o 복합 자료형 : 레코드, 커서, 컬렉션

o 참조형 변수 %TYPE : 기존테이블에 있는 자료형을 참조해서 가져와 사용하는 것

타입 변수 선언

o 변수의 선언과 초기화, 변수 값 출력

DECLARE
    V_EMPNO NUMBER;             -- 변수명 변수타입
    V_EMPNAME VARCHAR2(20);
    TEST_NUM NUMBER := 10+20;    -- 변수 선언과 동시 초기화
BEGIN
    V_EMPNO := 300;                 -- 변수 값 대입
    V_EMPNAME := '김말똥';

    DBMS_OUTPUT.PUT_LINE(V_EMPNO);  -- 변수 값 출력
    DBMS_OUTPUT.PUT_LINE(V_EMPNAME);
    DBMS_OUTPUT.PUT_LINE(TEST_NUM);
END;
/

o 레퍼런스 변수의 선언과 초기화, 변수 값 출력

DECLARE
    V_EMP_ID EMPLOYEE.EMP_ID%TYPE;   -- %TYPE를 통해 EMPLOYEE.EMP_ID컬럼과 같은 자료형으로 선언한다.
    V_SALARY EMPLOYEE.SALARY%TYPE;
BEGIN
    SELECT EMP_ID,SALARY
    INTO V_EMP_ID,V_SALARY
    FROM EMPLOYEE
    WHERE EMP_ID='&사원명';    -- & : 입력창을 띄운다
    DBMS_OUTPUT.PUT_LINE(V_EMP_ID||V_SALARY);
END;
/

- & 입력창 화면

o 테이블 타입의 변수 선언과 초기화, 변수 값 출력

* 테이블 타입은 자바의 배열과 비슷한 개념이며 Oracle 배열은 인덱스를 1부터 시작한다.

DECLARE
    TYPE EMP_ID_TABLE_TYPE IS TABLE OF EMPLOYEE.EMP_ID%TYPE
    INDEX BY BINARY_INTEGER;
    V_EMPID EMP_ID_TABLE_TYPE; --테이블 타입 변수 선언 (변수명 변수타입)
    I BINARY_INTEGER:=0;
BEGIN
    FOR K IN (SELECT EMP_ID FROM EMPLOYEE) LOOP
    I:=I+1;
    V_EMPID(I):=K.EMP_ID;
    END LOOP;

    FOR K IN 1..I LOOP --1부터 마지막(I)까지
        DBMS_OUTPUT.PUT_LINE(V_EMPID(K));
    END LOOP;
END;
/

o 레코드 타입의 변수 선언과 초기화, 변수 값 출력

* 레코드는 자바의 클래스와 같은 개념이라 생각하면 된다.

DECLARE
    TYPE MY_RECORD IS RECORD(   -- 변수 타입 생성
     V_ID EMPLOYEE.EMP_ID%TYPE,
     V_NAME EMPLOYEE.EMP_NAME%TYPE,
     V_DEPTTITLE DEPARTMENT.DEPT_TITLE%TYPE
    );
    MY_VAR MY_RECORD;   --변수 선언
BEGIN
    SELECT EMP_ID,EMP_NAME,DEPT_TITLE
    INTO MY_VAR
    FROM EMPLOYEE
    JOIN DEPARTMENT ON DEPT_CODE=DEPT_ID
    WHERE EMP_ID='&사원번호';

    DBMS_OUTPUT.PUT_LINE(MY_VAR.V_ID||' '||MY_VAR.V_NAME||' '||MY_VAR.V_DEPTTITLE);
END;
/

SELECT, INSERT문 처리하기

o PL/SQL구문에서 SELECT문, INSERT문 처리하기

o PL/SQL구문에서 SELECT문을 사용하는 것은 조회한 결과를 변수에 저장하여 활용한다.

o PL/SQL구문에서 SELECT문을 사용했을 때는 반드시 INTO를 사용해야 한다.

* INTO : SELECT문의 결과를 INTO를 통해 변수에 대입하는 것이다.

DECLARE
    V_EMP_ID EMPLOYEE.EMP_ID%TYPE;
BEGIN
    SELECT EMP_ID
    INTO V_EMP_ID   -- SELECT EMP_ID의 결과를 V_EMP_ID에 대입
    FROM EMPLOYEE
    WHERE EMP_NAME='&이름';
END;
/

-- INTO가 없을 경우 에러, SELECT문 사용 시 사용하지 않더라도 작성해야 한다.

CREATE TABLE INSERTTEST(
    VAL VARCHAR2(500)
);

-- PL/SQL 구문에서 INSERT문 처리하기
BEGIN
    FOR K IN 1..10 LOOP
        INSERT INTO INSERTTEST VALUES('PL/SQL문 입력'||K);
        COMMIT;
    END LOOP;
END;
/

SELECT * FROM INSERTTEST;

저작자표시 (새창열림)