[SQL] PL/SQL(PROCEDUAL LANGUAGE / SQL)

PL/SQL(PROCEDUAL LANGUAGE / SQL)

- SQL 만으로는 구현이 어렵거나 구현 불가능한 작업을 수행하기 위해 오라클에서 제공하는 프로그래밍 언어
- 일반 프로그래밍 언어적인 요소들을 다 가지고 있으며 데이터베이스 업무를 처리하기 위한 최적화된 언어.
- 변수, 조건 처리, 반복 처리 등 다양한 기능을 사용할 수 있음.

기본 구조

1) 선언부(DECLARE) : 모든 변수나 상수를 선언하는 부분.
2) 실행부(EXCUTABLE) : 실제 로직이 실행되는 부분. 제어문(조건문), 반복문, 함수정의 등의 로직을 기술하는 부분.
3) 예외처리부(EXCEPTION) : 실행 도중 예외가 발생 시 해결하기 위한 명령들을 기술하는 부분.
위 기본 구조 중에서 선언부와 예외처리부는 생략이 가능하지만, 실행부는 반드시 존재(기술) 해야 함.

PL/SQL 사용 시 주의사항

1. 기본 구조(DECLARE, BEGIN, EXCEPTION) 키워드 뒤에는 세미콜론(;)을 붙이지 않는다.
2. 블럭의 각 부분에서 실행해야 하는 문장 끝에는 세미콜론(;)을 붙인다.
3. BEGIN - END(실행부) 밑에는 반드시 "/"를 붙여야 한다.

-- 화면에 출력 기능을 활성화 시켜주어야 한다.
set serveroutput on;

 -- PL/SQL을 이용하여 "Hello, PL/SQL!!!" 이라는 내용을 화면에 출력해 보자.
begin
    dbms_output.put_line('Hello, PL/SQL!!!');
end;
/

선언부(DECLARE) 영역에 변수를 선언하는 방법

1) 스칼라 자료형

형식) 변수명 자료형(크기);
예) NUM NUMBER(3); / NAME VARCHAR2(20);

DECLARE
    V_EMPNO NUMBER(4) := 7700;
    V_ENAME VARCHAR2(20);
BEGIN
    V_ENAME := 'ADAMS';
    dbms_output.put_line('V_EMPNO >>> ' || V_EMPNO);
    dbms_output.put_line('V_ENAME >>> ' || V_ENAME);
END;
/

--
V_EMPNO >>> 7700
V_ENAME >>> ADAMS


PL/SQL 프로시저가 성공적으로 완료되었습니다.

2) 레퍼런스 자료형

형식) 변수명 테이블명.컬럼명%TYPE;
-- 예) NUM EMP.EMPNO%TYPE;
-- 테이블에 정의된 컬럼의 자료형과 크기를 모두 파악하고 있다면 별 문제가 없겠지만,
-- 대부분은 그렇지 못하기 때문에 오라클에서는 레퍼런스(REFERENCE) 변수를 제공해주고 있음.

DECLARE
    V_EMPNO EMP.EMPNO%TYPE := 7693;
    V_ENAME EMP.ENAME%TYPE;
BEGIN
    V_ENAME := 'SCOTT';
    DBMS_OUTPUT.PUT_LINE('V_EMPNO >>> ' || V_EMPNO);
    DBMS_OUTPUT.PUT_LINE('V_ENAME >>> ' || V_ENAME);
END;
/

--
V_EMPNO >>> 7693
V_ENAME >>> SCOTT


PL/SQL 프로시저가 성공적으로 완료되었습니다.

3) ROWTYPE

테이블의 모든 컬럼을 한꺼번에 저장하기 위한 변수로 선언하는 방법.

DECLARE
    EMP_ROW EMP%ROWTYPE;
BEGIN
    SELECT * INTO EMP_ROW FROM EMP WHERE EMPNO = 7698;
    DBMS_OUTPUT.PUT_LINE(EMP_ROW.EMPNO||' '||EMP_ROW.ENAME
                        ||' '||EMP_ROW.JOB||' '||EMP_ROW.SAL
                        ||' '||EMP_ROW.DEPTNO);
END;

--
7698 BLAKE MANAGER 2850 30


PL/SQL 프로시저가 성공적으로 완료되었습니다.

조건 제어문

- 특정 조건식을 통해 상황에 따라 실행할 내용을 달리하는 방식의 명령어를 말함.

1. IF 조건문

1) IF~THEN : 특정 조건을 만족하는 경우에 작업을 수행.

형식) IF 조건식 THEN 
      조건식이 참인 경우 실행 문장; 
      END IF

-- 1) IF~THEN 예제

DECLARE
    V_NUMBER NUMBER(3) := 15;
BEGIN
    IF V_NUMBER >= 10 THEN
        DBMS_OUTPUT.PUT_LINE(V_NUMBER || '는 10보다 큰 수입니다.');
    END IF;
END;
/

--
15는 10보다 큰 수입니다.


PL/SQL 프로시저가 성공적으로 완료되었습니다.

2) IF~THEN~ELSE : 특정 조건에 만족하는 경우와 반대의 경우에 각자 지정한 작업을 수행.

형식) IF 조건식 THEN 
	조건식이 참인 경우 실행 문장; 
	ELSE 
	조건식이 거짓인 경우 실행 문장. 
	END IF

-- 2) IF~THEN~ELSE 예제

DECLARE
    V_SCORE NUMBER(3) := 38;
BEGIN
    IF MOD(V_SCORE,2) = 1 THEN
        DBMS_OUTPUT.PUT_LINE(V_SCORE || '는 홀수입니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE(V_SCORE || '는 짝수입니다.');
    END IF;
END;
/

--
38는 짝수입니다.


PL/SQL 프로시저가 성공적으로 완료되었습니다.

3) IF~THEN~ELSIF : 여러 조건에 따라 각자 지정한 작업을 수행.

형식) IF 조건식1 THEN 
		조건식1이 참인 경우 실행 문장; 
	ELSIF 조건식2 THEN 
		조건식1이 거짓이고, 조건식2가 참인 경우 실행 문장. 
	ELSIF 조건식3 THEN 
		조건식1,2가 거짓이고, 조건식2가 참인 경우 실행 문장. 
	ELSE 
		조건식1,2,3이 거짓인 경우 실행 문장. 
	END IF

-- 3) IF~THEN~ELSIF 예제

DECLARE
    V_AVG NUMBER(5,2) := 89.12;
BEGIN
    IF V_AVG >= 90 THEN
        DBMS_OUTPUT.PUT_LINE('A학점입니다.');
    ELSIF V_AVG >= 80 THEN
        DBMS_OUTPUT.PUT_LINE('B학점입니다.');
    ELSIF V_AVG >= 70 THEN
        DBMS_OUTPUT.PUT_LINE('C학점입니다.');
    ELSIF V_AVG >= 60 THEN
        DBMS_OUTPUT.PUT_LINE('D학점입니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('F학점입니다.');
    END IF;
END;
/

--
B학점입니다.


PL/SQL 프로시저가 성공적으로 완료되었습니다.

2. CASE 조건문

형식) 
CASE 비교 기준 
	WHEN 값1 THEN 
		수행할 명령어; 
	WHEN 값2 THEN 
		수행할 명령어; 
	WHEN 값3 THEN 
		수행할 명령어; 
	ELSE 
		값1, 값2, 값3이 아닐 경우 수행할 명령어; 
END CASE;

1) 단순 case

형식)
  case 비교 기준
    when 값1 then
       값1 일때 수행할 문장;
    when 값2 then
       값2 일때 수행할 문장;
    when 값3 then
       값3 일때 수행할 문장;
    else
       값1, 값2, 값3이 아닌 다른 값일 경우 수행할 문장;
   end case;

-- 1) 단순 case 예제

DECLARE
    V_SCORE NUMBER(3) := 92;
BEGIN
    CASE TRUNC(V_SCORE / 10)
        WHEN 10 THEN
            DBMS_OUTPUT.PUT_LINE('A학점입니다.');
        WHEN 9 THEN
            DBMS_OUTPUT.PUT_LINE('A학점입니다.');
        WHEN 8 THEN
            DBMS_OUTPUT.PUT_LINE('B학점입니다.');
        WHEN 7 THEN
            DBMS_OUTPUT.PUT_LINE('C학점입니다.');
        WHEN 6 THEN
            DBMS_OUTPUT.PUT_LINE('D학점입니다.');
        ELSE
            DBMS_OUTPUT.PUT_LINE('F학점입니다.');
    END CASE;
END;
/

--
A학점입니다.


PL/SQL 프로시저가 성공적으로 완료되었습니다.

2) 검색 case

형식)
  case
    when 조건식1 then
      조건식1 일때 수행할 문장;
    when 조건식2 then
       조건식2 일때 수행할 문장;
    when 조건식3 then
       조건식3 일때 수행할 문장;
    else
       값1, 값2, 값3이 아닌 다른 값일 경우 수행할 문장;
  end case;

-- 2) 검색 case 예제

declare
    v_score number(3) := 88;
begin
    case
        when v_score >= 90 then
            dbms_output.put_line('A학점입니다.');
        when v_score >= 80 then
            dbms_output.put_line('B학점입니다.');
        when v_score >= 70 then
            dbms_output.put_line('C학점입니다.');
        when v_score >= 60 then
            dbms_output.put_line('D학점입니다.');
        else 
            dbms_output.put_line('F학점입니다.');
    end case;
end;
/

--
B학점입니다.


PL/SQL 프로시저가 성공적으로 완료되었습니다.

반복 제어문

-특정 작업을 반복하여 수행하고자 할 때 사용하는 문장.

반복 제어문의 종류

1) 기본 LOOP
2) WHILE LOOP
3) FOR LOOP

-- 1) 기본 loop 예제

DECLARE
    V_NUM NUMBER(3) := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('V_NUM >>>' || V_NUM);
        V_NUM := V_NUM + 1;
        IF V_NUM > 10 THEN
            EXIT;
        END IF;
    END LOOP;
END;
/

--
V_NUM >>>1
V_NUM >>>2
V_NUM >>>3
V_NUM >>>4
V_NUM >>>5
V_NUM >>>6
V_NUM >>>7
V_NUM >>>8
V_NUM >>>9
V_NUM >>>10


PL/SQL 프로시저가 성공적으로 완료되었습니다.

-- 2) WHILE LOOP 예제

DECLARE
    V_NUMBER NUMBER(3) := 1;
BEGIN
    DBMS_OUTPUT.PUT_LINE('WHILE LOOP 출력문');
    WHILE V_NUMBER <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE('V_NUMBER >>> ' || V_NUMBER);
        V_NUMBER := V_NUMBER + 1;
    END LOOP;
END;
/

--
WHILE LOOP 출력문
V_NUMBER >>> 1
V_NUMBER >>> 2
V_NUMBER >>> 3
V_NUMBER >>> 4
V_NUMBER >>> 5


PL/SQL 프로시저가 성공적으로 완료되었습니다.

-- 3) FOR LOOP 예제

DECLARE
    V_SUM NUMBER(5) := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('FOR LOOP 출력문');
    FOR V_NUM IN 1 .. 5 LOOP
        V_SUM := V_SUM + V_NUM;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('1~5까지의 합 >>> ' || V_SUM);
END;
/

--
FOR LOOP 출력문
1~5까지의 합 >>> 15


PL/SQL 프로시저가 성공적으로 완료되었습니다.

반복문의 반복 수행을 종료시키는 명령어.

1) EXIT : 수행 중인 반복을 종료시키는 명령어
2) EXIT-WHEN : 반복 종료를 위한 조건식을 지정하고 만족하면 반복 종료
3) CONTINUE : 수행중인 반복의 현재 주기를 건너 뜀
4) CONTINUE-WHEN : 특정 조건식을 지정하고 조건식을 만족하면 반복 주기를 건너 뜀

-- continue 예제

begin
    for i in 1 .. 10 loop
       if mod(i, 2) = 1 then
          continue;
       end if;
       dbms_output.put_line('i >>> ' || i);
    end loop;
end;
/

--
i >>> 2
i >>> 4
i >>> 6
i >>> 8
i >>> 10


PL/SQL 프로시저가 성공적으로 완료되었습니다.

-- continue when 예제

begin
    dbms_output.put_line('continue when 경우');
    for i in 1 .. 10 loop
       continue when mod(i, 2) = 0;
       dbms_output.put_line('i >>> ' || i);
    end loop;
end;
/

--
continue when 경우
i >>> 1
i >>> 3
i >>> 5
i >>> 7
i >>> 9


PL/SQL 프로시저가 성공적으로 완료되었습니다.

-- EXIT 예제

DECLARE 
    V_SCORE NUMBER(5) := 2;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('V_SCORE >>> ' || V_SCORE);
        V_SCORE := V_SCORE + 1;
        EXIT WHEN V_SCORE > 5;
        END LOOP;
END;
/

--
V_SCORE >>> 2
V_SCORE >>> 3
V_SCORE >>> 4
V_SCORE >>> 5


PL/SQL 프로시저가 성공적으로 완료되었습니다

--1부터 5까지의 합

DECLARE 
    V_SUM NUMBER(5) := 0;
BEGIN 
    FOR I IN 1..5 LOOP
        V_SUM := V_SUM + I;
    END LOOP; 
    
    DBMS_OUTPUT.PUT_LINE('1 ~ 5까지의 합 >>> ' || V_SUM);
END;
/

--
1 ~ 5까지의 합 >>> 15


PL/SQL 프로시저가 성공적으로 완료되었습니다.

키보드로 데이터를 입력받는 방법

DECLARE
    V_NUM1 NUMBER(3);
    V_NUM2 NUMBER(3);
BEGIN
    V_NUM1 := '&NUM1';
    V_NUM2 := '&NUM2';
    DBMS_OUTPUT.PUT_LINE(V_NUM1||' + '||V_NUM2||' >>> '||(V_NUM1+V_NUM2));
END;
/

-- 1과 2를 입력해보았다.


이전:DECLARE
    V_NUM1 NUMBER(3);
    V_NUM2 NUMBER(3);
BEGIN
    V_NUM1 := '&NUM1';
    V_NUM2 := '&NUM2';
    DBMS_OUTPUT.PUT_LINE(V_NUM1||' + '||V_NUM2||' >>> '||(V_NUM1+V_NUM2));
END;

신규:DECLARE
    V_NUM1 NUMBER(3);
    V_NUM2 NUMBER(3);
BEGIN
    V_NUM1 := '1';
    V_NUM2 := '2';
    DBMS_OUTPUT.PUT_LINE(V_NUM1||' + '||V_NUM2||' >>> '||(V_NUM1+V_NUM2));
END;
1 + 2 >>> 3


PL/SQL 프로시저가 성공적으로 완료되었습니다.

데이터 정의어(DDL : Data Definition Language)

- 데이터의 관리 및 보관을 위해 다양한 객체를 제공하는데 이러한 객체를
     새로 만들거나 기존에 존재하던 객체를 변경하거나 삭제하는 등의 기능을
     수행하는 명령어를 말함.

데이터 정의어를 사용 시 주의사항.

* 데이터 정의어를 실행하면 자동으로 commit이 됨.
따라서 rollback을 통한 취소는 불가능함.

데이터 정의어의 종류

1) create : 객체를 생성하는 명령어.
               예) 테이블 생성, 시퀀스 생성, View 생성.

   2) alter : 객체를 수정하는 명령어.
              예) 테이블의 컬럼 추가, 수정, 삭제

   3) drop : 객체를 삭제하는 명령어.
             예) 테이블을 삭제하는 명령어.

-- test 테이블을 삭제해 보자.
-- 형식) drop 테이블이름 purge;
drop table test purge;

-- rename : 테이블의 이름을 변경하고 싶을 때 사용하는 명령어.
-- 형식) rename 기존 테이블이름 to 새로운 테이블이름
rename dept_02 to dept_10;

-- truncate : 테이블의 데이터를 삭제하는 명령어.
-- 형식) truncate table 테이블이름
truncate table dept_10;

저작자표시 비영리

'Back-End > Database' 카테고리의 다른 글

[SQL] 트랜잭션(transaction) (0)	2021.10.05
[SQL] GROUP BY 절 / HAVING절 (0)	2021.10.05
[SQL] 서브 쿼리 (0)	2021.10.05
[SQL] 컬럼 속성(제약 조건) (0)	2021.10.05
[SQL] view (0)	2021.10.05

진진자라의 코딩

[SQL] PL/SQL(PROCEDUAL LANGUAGE / SQL)

PL/SQL(PROCEDUAL LANGUAGE / SQL)

선언부(DECLARE) 영역에 변수를 선언하는 방법

조건 제어문

1. IF 조건문

2. CASE 조건문

반복 제어문

키보드로 데이터를 입력받는 방법

데이터 정의어(DDL : Data Definition Language)

'Back-End > Database' 카테고리의 다른 글

댓글

티스토리툴바

[SQL] PL/SQL(PROCEDUAL LANGUAGE / SQL)

PL/SQL(PROCEDUAL LANGUAGE / SQL)

선언부(DECLARE) 영역에 변수를 선언하는 방법

조건 제어문

1. IF 조건문

2. CASE 조건문

반복 제어문

키보드로 데이터를 입력받는 방법

데이터 정의어(DDL : Data Definition Language)

'Back-End > Database' 카테고리의 다른 글

관련글

댓글

티스토리툴바