[ORACLE]PL/SQL

PL/SQL

💡 Procedural Language / SQL

• 집합적 성향이 강한 SQL에 일반 프로그래밍 언어 요소를 추가

기본 구조

선언부(Declare)

• 변수, 상수 선언
• 생략가능

실행부(Begin)

• 제어문, 반복문 등 로직 실행

예외처리부(Exception)

• 실행도중 에러 발생을 catch, 후속조치
• 생략가능

연산자

• := 대입연산자
• ** 제곱연산자

변수

변수

일반타입

var VarTYPE( );

변수 참조 타입

💡 변수 타입을 특정 테이블의 컬럼을 지칭할 수 있다

• 유지보수 용이

table.column%TYPE ;

DECLARE
    deptno dept.deptno%TYPE;
    dname dept.dname%TYPE;

⭕복합변수

ROWTYPE

💡 특정 테이블의 행의 모든 컬럼을 담을 수 있는 행 참조변수

선언 : 변수명 테이블명%ROWTYPE
접근 : 변수명.컬럼명

DECLARE 
    v_dept_row dept%ROWTYPE;
BEGIN
    SELECT * INTO v_dept_row
    FROM dept
    WHERE deptno=10;
    DBMS_OUTPUT.PUT_LINE('dname : ' || v_dept_row.dname);
    DBMS_OUTPUT.PUT_LINE('deptno : ' || v_dept_row.deptno);
    DBMS_OUTPUT.PUT_LINE('loc : ' || v_dept_row.loc);
END;
/

/*
dname : ACCOUNTING
deptno : 10
loc : NEW YORK
*/

RECODE TYPE

💡 사용자 정의 타입 행의 정보를 담을 수 있는것은 ROWTYPE과 동일 사용자가 원하는 컬럼에 대해서만 정의

DECLARE
    TYPE dept_row_type IS RECORD(
    deptno dept.deptno%TYPE,
    dname dept.dname%TYPE);

    dept_rec dept_row_type;
BEGIN
    SELECT deptno, dname
    INTO dept_rec
    FROM dept
    WHERE deptno=10;

    DBMS_OUTPUT.PUT_LINE('dept_row : ' || dept_rec.deptno || ',' || dept_rec.dname);
END;
/

TABLE TYPE

💡 복수개의 행을 담을 수 있는 타입

TABLE 테이블타입이름 IS TABLE OF 행의 타입 INDEX BY BINARY_INTEGER

DECLARE
    TYPE dept_tab_type IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER;

    v_dept_tab dept_tab_type;

BEGIN
    SELECT * BULK COLLECT INTO v_dept_tab
    FROM dept;

    FOR i IN 1..v_dept_tab.count LOOP
        DBMS_OUTPUT.PUT_LINE(v_dept_tab(i).deptno || ' , ' || v_dept_tab(i).dname );
    END LOOP;
END;
/
/*
10 , ACCOUNTING
20 , RESEARCH
30 , SALES
40 , OPERATIONS*/

로직제어

조건제어

IF

💡 조건제어 , 분기

IF 조건 THEN
실행할문장;
ELSIF 조건 THEN
실행할 문장;
ELSE
실행할문장;
END IF;

DECLARE
    p NUMBER := 2;
BEGIN
    IF p = 1 THEN
         DBMS_OUTPUT.PUT_LINE('p=1');
    ELSIF p = 2 THEN
         DBMS_OUTPUT.PUT_LINE('p=2');
    ELSE
         DBMS_OUTPUT.PUT_LINE('else');
    END IF;
END;
/

/*
p=2 */

반복문

FOR - LOOP

FOR count IN [REVERSE] start..end LOOP
statement;
END LOOP;

counter : index variable( i )

DECLARE
BEGIN
FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('for i :'  || i);
END LOOP;
END;
/
/*
for i :1
for i :2
for i :3
for i :4
for i :5*/

WHILE

WHILE condition
LOOP
statement;
END LOOP;

DECLARE
    i NUMBER := 1;
BEGIN
    WHILE i <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE( i );
        i := i+1;
    END LOOP;
END;
/

LOOP

LOOP
statement;
EXIT [when condition];
END LOOP;

DECLARE
    i NUMBER := 0;
BEGIN
    LOOP
        EXIT WHEN i > 5;
            DBMS_OUTPUT.PUT_LINE( i );
            i := i + 1;
    END LOOP;
END;
/

블록 유형

Anonymous block
Procedure
Function

Anonymous block

💡 이름이 없는 block 재사용 불가

-- 화면 출력을 활성화 하는 설정 ( 접속후 1회만 실행하면 유지)
SET SERVEROUTPUT ON;
-- 간단한 PL/SQL익명 블럭

DECLARE
    deptno NUMBER(2);
    dname VARCHAR2(20);
BEGIN
    SELECT deptno, dname  INTO deptno, dname
    FROM dept
    WHERE deptno =10;

    dbms_output.Put_line(deptno || '    ' || dname);
END;
/   -- 실직적으로 PL/SQL 구문이 끝나는 부분

/*
10    ACCOUNTING

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

Procedure

💡 이름이 있는 pl/sql 블럭

• 매개변수를 받을수 있고 절차적 로직을 통해 복잡한 코드 작성
• 서버에 저장되어 재사용 가능

뷰

1. 뷰 생성
2. select *
   from 뷰

프로시저 절차

1. 프로시저 생성 (CREATE OR REPLACE ...)
2. 프로시저 실행 (EXEC 프로시저명)

♦️인자가 없는 프로시저 생성 및 실행

CREATE OR REPLACE PROCEDURE print_dept IS

    dname dept.dname%TYPE;
    loc dept.loc%TYPE;
BEGIN
    SELECT dname, loc  INTO  dname ,loc
    FROM dept
    WHERE deptno =10;

    DBMS_OUTPUT.PUT_LINE(dname || '    ' || loc);
END;
/

-- 실행
EXEC print_dept;

--ACCOUNTING    NEW YORK

♦️인자가 있는 프로시저 생성

프로시저명 (인자명1 IN 인자타입1, ....)

인자명 : 주로 P_ 접두어 주로 사용

변수명 : 주로 V_ 접두어 주로 사용

CREATE OR REPLACE PROCEDURE print_dept(p_deptno IN dept.deptno%TYPE) IS

    v_dname dept.dname%TYPE;
    v_loc dept.loc%TYPE;
BEGIN
    SELECT dname, loc  INTO  v_dname ,v_loc
    FROM dept
    WHERE deptno = p_deptno;

    DBMS_OUTPUT.PUT_LINE(v_dname || '    ' || v_loc);
END;
/

-- 실행
EXEC print_dept(20);
--RESEARCH    DALLAS

Function

💡 로직을 통해 값을 반환하는 pl/sql 블럭 (return 필수)

CURSOR (커서)

💡 SELECT 문이 실행되는 메모리 상의 공간

• 다량의 데이터를 변수에 담게되면 메모리 낭비가 심해져 프로그램이 정상적으로 동작 못할 수도 있음.
• 한번에 모든 데이터를 인출하지 않고, 개발자가 직접 인출 단계를 제어 함으로써 변수에 모든 데이터를 담지 않고도 개발하는 것이 가능.

커서의 종류

• 묵시적 커서 : 커서이름을 별도로 지정하지 않는경우
  ⇒ 오라클이 알아서 처리해줌
• 명시적 커서 : 커서를 명시적 이름과 함께 선언하고, 개발자가 해당 커서를 직접 제어 가능

커서 사용 방법 (명시적 커서)

1. 커서 선언 (DECLARE)

   CURSOR 커서이름 IS

          SELECT 쿼리;

2. 커서 열기

   OPEN 커서이름;

3. FETCH (인출)

   FETCH 커서이름 INTO 변수;

4. 커서 닫기

   CLOSE 커서이름;

-- dept 테이블의 모든 행에 대해 부서번호, 부서이름을 cursor를
--통해 데이터를 다루는 실습

DECLARE
    CURSOR dept_cur IS     -- 커서선언
        SELECT deptno, dname
        FROM dept;
    v_deptno dept.deptno%TYPE;
    v_dname dept.dname%TYPE;
BEGIN
    OPEN dept_cur;    -- 커서열기

    LOOP
        FETCH dept_cur INTO v_deptno, v_dname;
        EXIT WHEN dept_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_deptno || ' , ' || v_dname);        
    END LOOP;
END;
/

명시적 커서 FOR LOOP( 향상된 FOR 문)

FOR 레코드명 IN 커서명 LOOP
반복 실행할 문장;
END LOOP;

• OPEN, FETCH, CLOSE : 2~4단계를 FOR LOOP에서 해결

DECLARE
    CURSOR dept_cur IS     -- 커서선언
        SELECT deptno, dname
        FROM dept;
BEGIN
    FOR dept_row IN dept_cur LOOP
        DBMS_OUTPUT.PUT_LINE(dept_row.deptno || ' , ' || dept_row.dname);        
    END LOOP;
END;
/

파라미터가 있는 명시적 커서 FOR LOOP

DECLARE
CURSOR 커서명 (파라미터명 파라미터타입) IS
QUERY ;
BEGIN
FOR 레코드명 IN 커서명 LOOP
반복 실행할 문장;
END LOOP;
END;

DECLARE
    CURSOR emp_cur(p_deptno dept.deptno%TYPE) IS     -- 커서선언
        SELECT empno, ename
        FROM emp
        WHERE deptno = p_deptno;
BEGIN
    FOR emp_row IN emp_cur(30) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_row.empno || ' , ' || emp_row.ename);        
    END LOOP;
END;
/

인라인 커서 선언

BEGIN
    FOR dept_row IN (SELECT deptno, dname FROM dept) LOOP
        DBMS_OUTPUT.PUT_LINE(dept_row.deptno || ' , ' || dept_row.dname);        
    END LOOP;
END;
/