[DB]PL/SQL 프로시저 기초-기본 문법 1 [펌]

[DB]PL/SQL 프로시저 기초-기본 문법 1 [펌]

스칼라 변수

---

• SQL 자료형과 비슷하게 선언하는 것을 스칼라 변수

• 주요 자료형 :NUMBER,CHAR,VARCHAR2,DATE,TIMESTAMP

ex)

v_empno number(4) := 100; v_ename varchar2(20) :='lks';

레퍼런스 변수

• DB 테이블의 특정 칼럼으로 변수 타입을 지정할때 사용

• 변수이름 테이블이름.컬럼이름%TYPE;

• 테이블에서 컬럼의 자료형이 변경되더라도, PL/SQL에서의 변수 선언부분을 수정할 필요가 없다는 장점이 있다.

deptno emp.deptno%type; job emp.job%type;

DML 명령 사용하기

---

• PL/SQL에서 select 문 사용에는 INTO가 필요하다.

• INTO 절에는 select문에서 조회한 데이터를 저장할 변수를 기술한다.

• select 결과의 컬럼과 INTO 절의 변수가 1:1 일치해야만 함

• 하나의 행만 저장할 수 있다.

• 결과는 메모리에 저장되서 사용할 수 있다.

declare

    --스칼라 타입 변수 선언
    v_empno number(4) := 100;
    v_ename varchar2(20) :='lks';
    --특정 칼럼의 레퍼런스 타입 선언
    v_deptno emp.deptno%type;
    v_job emp.job%type; 
begin

    --select한 결과를 저장 명령어 : into
    select empno, ename, deptno, job 
    into v_empno, v_ename, v_deptno, v_job 
    from emp
    where empno = 7876;


    dbms_output.put_line('번호 : ' || v_empno);
    dbms_output.put_line('이름 : ' || v_ename);
    dbms_output.put_line('부서 : ' || v_deptno);
    dbms_output.put_line('직책 : ' || v_job);
end;

기본 문법

CREATE OR REPLACE PROCEDURE 프로시저이름 IS 
    --프로시저 내에서 사용할 변수
    변수이름 데이터타입;
    변수이름 데이터타입;

 BEGIN
     기능 구현,처리 작성;
 END;
다음은 기본 프로시저의 예제입니다.

create or replace PROCEDURE EM_TEST
IS
    START_MESSAGE VARCHAR2(100) := '------test_START----------';
    END_MESSAGE VARCHAR2(100) := '-------test_END------------';
BEGIN
    DBMS_OUTPUT.PUT_LINE(START_MESSAGE);
    DBMS_OUTPUT.PUT_LINE(END_MESSAGE);
END;

조건문 (IF문)

* 사용문법

IF 조건 THEN
작업처리;

ELSEIF 조건 THEN

작업처리;

ELSE

작업처리;

END IF;

다음은 IF문이 포함된 프로시저의 예제입니다.

create or replace PROCEDURE IF_TEST
IS
    START_MESSAGE VARCHAR2(100) := '------test_START----------';
    END_MESSAGE VARCHAR2(100) := '-------test_END------------';
    ERR_MESSAGE VARCHAR2(100) := '----ERROR------';
    EMPNO_SYS NUMBER(4,0);
    ENAME_SYS VARCHAR2(10);
    START_STATE VARCHAR2(1) := '0';

BEGIN
    DBMS_OUTPUT.PUT_LINE(START_MESSAGE);

    IF START_STATE IS NOT NULL AND START_STATE = '0' THEN
        BEGIN
         SELECT EMPNO, ENAME 
         INTO EMPNO_SYS, ENAME_SYS
         FROM EMP
         WHERE ENAME = 'KING'; 
        END;

    ELSEIF START_STATE IS NOT NULL AND START_STATE = '1' THEN
        BEGIN
         SELECT EMPNO, ENAME 
         INTO EMPNO_SYS, ENAME_SYS
         FROM EMP
         WHERE ENAME = 'JONES'; 
        END;

    ELSE
        BEGIN
         SELECT EMPNO, ENAME 
         INTO EMPNO_SYS, ENAME_SYS
         FROM EMP
         WHERE ENAME = 'JONES'; 
        END;
    END IF;
    DBMS_OUTPUT.PUT_LINE('ENPNO: ' || EMPNO_SYS);
    DBMS_OUTPUT.PUT_LINE('ENAME: ' || ENAME_SYS);
    DBMS_OUTPUT.PUT_LINE(END_MESSAGE);
 END;

출력

------test_START----------

ENPNO: 7566
ENAME: KING

-------test_END------------

LOOP 반복문

* 사용문법
LOOP
반복할 작업 처리 내용

EXIT WHEN [끝낼 조건]; END LOOP;

다음은 LOOP가 포함 된 프로시저의 예제입니다.

create or replace PROCEDURE LOOP_TEST
IS
    START_MESSAGE VARCHAR2(100) := '------test_START----------';
    END_MESSAGE VARCHAR2(100) := '-------test_END------------';
    TEST_NUM NUMBER := 1;

BEGIN
    DBMS_OUTPUT.PUT_LINE(START_MESSAGE);
    LOOP
    EXIT WHEN TEST_NUM >10;
        DBMS_OUTPUT.PUT_LINE(TEST_NUM);
        TEST_NUM := TEST_NUM +1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(END_MESSAGE);
END;

출력

------test_START----------
1

2
3

4
5

6
7

8
9

10
-------test_END------------

WHILE 반복문

* 사용문법

WHILE 조건 LOOP

처리 내용

END LOOP;

create or replace PROCEDURE WHILE_TEST
IS
    START_MESSAGE VARCHAR2(100) := '------test_START----------';
    END_MESSAGE VARCHAR2(100) := '-------test_END------------';
    TEST_NUM NUMBER := 1;
    START_STATE VARCHAR2(1) := '0';

BEGIN
    DBMS_OUTPUT.PUT_LINE(START_MESSAGE);
    WHILE TEST_NUM <10 LOOP
     DBMS_OUTPUT.PUT_LINE(TEST_NUM);
     TEST_NUM := TEST_NUM +1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(END_MESSAGE);
END;

출력

------test_START----------
1

2
3

4
5

6
7

8
9

-------test_END------------