ORACLE

Oracle PL/SQL 기본 문법 정리

라구엘 2022. 8. 5. 10:55

Procedural Language/SQL

PL/SQL 기본 구조

  • DBMS에서 제공하는 언어적인 요소
    • PL/SQL 제공 안하는 DB도 존재
-- 콘솔 출력설정
SET serveroutput ON

DECLARE(IS)
	-- 변수 선언
BEGIN
  -- 코드 작성
END;
/

출력함수

  • 테스트 로그 기록 시 사용
-- 콘솔 출력설정
SET serveroutput ON

BEGIN
	...

	DBMS_OUTPUT.PUT('문자열'); -- 줄바꿈 없이 출력
	DBMS_OUTPUT.PUT_LINE('문자열'); -- 출력 후 줄바꿈

	...
END;
/

변수

DECLARE(IS)
   -- 변수 선언
   변수명 테이터형(크기);
   변수명 데이터형(크기) := 초기값;
BEGIN
   -- 값 할당
   변수명 := 값;

   -- 값 사용
   변수명 := 또다른변수명;
   DBMS_OUTPUT.PUT_LINE(변수명);
   DBMS_OUTPUT.PUT_LINE(3 + 변수명);
END;

참조변수(Reference Variable)

  • 만들어진 테이블의 컬럼데이터형을 참조하여 변수를 만들 수 있음
  • 컬럼하나를 참조
    • 참조변수명 테이블명.컬럼명%TYPE
  • 테이블의 모든 컬럼참조 (record type, 레코드형 변수, 레코드 변수)
    • 레코드변수명 테이블명%ROWTYPE;
    • 사용은 레코드변수명.컬럼명 := 값;
  • 참조변수의 단점
    • 데이터형을 직관적으로 알 수 없다
    • 사용도중 데이터형이 변경될 수 있다
DECLARE
-- 컬럼 하나를 참조
    참조변수명 테이블명.컬럼명%TYPE := 값;

-- 테이블의 모든 컬럼 참조(RECORD TYPE)
    레코드변수명 테이블명%ROWTYPE;
BEGIN
    변수명 := 값;
    레코드타입변수명.컬럼명 := 값;
...

연산자(Operator)

  • 산술 : +, -, *, MOD()
  • 조건문에서 사용
    • 관계 : >, <, >=, <=, =, !=(<>)
      • 일반 언어에선 '=='
    • 논리 : AND, OR, NOT
    • NULL 비교 : IS NULL
    • 범위 : BETWEEN AND
  • 문자열 연산자 : LIKE, %, _

PROMPT 통해 입력받기

  • 최 상단(DECLARE 위)에 선언
-- ACCEPT <변수명> PROMPT 'PROMPT 다이얼로그 설명 : '
ACCEPT input_empno PROMPT '사원 번호를 입력하세요 : '

DECLARE
	empno NUMBER := &input_empno; -- PROMPT로 입력받은 값을 변수에 설정 가능

제어문

  • 프로그램의 순차적인 흐름을 바꿔줄 수 있는 문장
    • BEGIN ~ END 사이에서 사용
  • 조건문, 반복문, 분기문 3가지 제공
  • 분기문 - EXIT, RETURN
  • 조건문 - IF
...
BEGIN

    IF 조건1 THEN
        조건1에 맞을 때 수행할 문장;
    ELSIF 조건2 THEN
        조건2에 맞을 때 수행할 문장;
    ELSIF 조건3 THEN
        조건3에 맞을 때 수행할 문장;
    ELSIF ...
        ...
    ELSE
        모든 조건에 맞지 않았을 때 수행할 문장
    END IF;

END;
/
  • 반복문 - LOOP
BEGIN

    LOOP 
        반복해야될 문장들;

        IF 조건 THEN
            EXIT;
        END IF;

    END LOOP;

END;

-- 또는

BEGIN

    LOOP
        반복해야될 문장들;

        EXIT WHEN (조건);

    END LOOP;

END;
  • 반복문 - FOR
BEGIN
    
    FOR 변수명 IN 시작 ..끝 LOOP
        
        반복시킬 문장들;
    END LOOP;

END;
/

-- 다중 FOR
BEGIN

    FOR 변수명 IN 시작 ..끝 LOOP
        -- 단일 FOR영역
        FOR 변수명 IN 시작 ..끝 LOOP
        -- 다중 FOR영역, 인덱스 두개 사용 가능
        -- (바깥FOR 인덱스, 안FOR 인덱스)
        END LOOP;
        -- 단일 FOR영역
    END LOOP;

END;
  • 반복문 - WHILE
BEGIN
    초기값;
    WHILE 조건식 LOOP

        반복할 수행 문장;

    증가식, 감소식;
    END LOOP;

END;

TABLE 데이터형

  • PL/SQL의 테이블 != SQL의 테이블
  • 테이블은 레코드를 저장하기 위한 객체
    • PL/SQL에서는 배열을 지원하지 않고 사용자가 만들어서 사용
    • 인덱스를 가지며 순차처리를 할 수 있는 데이터형
      • 배열 또는 리스트(파이썬)와 비슷한 객체
DECLARE
	  -- 1. 테이블 타입 정의
    TYPE 테이블타입명 IS TABLE OF 
     테이블을구성할데이터형 
     INDEX BY BINARY_INTEGER;

	  -- 2. 테이블 타입 변수 선언
		변수명 테이블타입명;
BEGIN

		-- 3. 값 설정
		변수명(인덱스) := 값;

		-- 4. 저장된 element의 개수를 얻을 수 있음
		변수명.COUNT
END;

RECORD 데이터형

  • 여러 종류의 데이터형을 묶어서 하나의 데이터형으로 사용하는 것
    • ROWTYPE의 경우 들어갈 변수들을 결정하지 못하지만 RECORD변수는 원하는 변수들을 결정할 수 있다
  • 레코드변수는 테이블변수와 같이 사용
DECLARE
		-- 1. 레코드 타입 선언
    TYPE 레코드명 IS RECORD (
        변수명 데이터형(크기),
        ...
    );

    -- 2. 레코드 타입 변수 선언
		레코드변수명 레코드명;
BEGIN
		-- 3. 값 설정
		레코드변수명.변수명 := 값;

		-- 4. 값 사용
		... := 레코드변수명.변수명;
END;
/

PL/SQL에서 Query문 사용하기

  • DDL, DCL, DML 사용할 수 있다
    • 다른건 다 같으나 SELECT만 좀 다르다
  • SELECT는 INTO절이 들어가며 한 행만 조회되어야 한다
  • FUNCTION, PROCEDURE가 매개변수를 가지고 Query를 사용하면 매개변수명과 WHERE절에서 사용하는 컬럼명이 달라야 한다

SELECT

  • 조회된 결과를 저장할 INTO절이 정의됨
  • 조회결과가 한행이 아니라면 error가 발생!
    • 에러발생 시 EXCEPTION 절로 넘어감
    • 조회된 컬럼값이 변수명에 들어감
      • 변수와 컬럼의 데이터형과 갯수는 일치해야 한다.
    • 부분이 나눠지기 때문에 컬럼명과 변수명이 같아도 된다(INSERT 처럼)
SELECT 컬럼명, ... 
INTO   변수명, ...
FROM   테이블명
...

CURSOR

  • 다른 언어로 보면 포인터
  • 커서는 인라인뷰를 사용, 메모리를 계속 사용한다.
    • 사용 후 닫아줘야 메모리 누수를 잡을 수 있다
  • 암시적(묵시적) 커서와 명시적 커서로 제공됨

암시적/묵시적 커서(SQL)

  • 개발자가 정의하지 않아도 쿼리문을 실행하면 자동으로 생성되는 커서.
  • INSERT, UPDATE, DELETE는 수행한 행의 수를 반환하는 암시적(묵시적) 커서를 사용할 수 있다.
  • 속성
    • %ROWCOUNT - 해당 커서에서 실행한 총 행의 개수(가장 마지막 행이 몇 번째 행인지 카운트)를 반환
    • %FOUND - 해당 커서 안에 아직 수행할 데이터가 있으면 TRUE반환, 없을경우 FALSE 반환
    • %NOTFOUND - 해당 커서 안에 수행할 데이터가 없을 경우 TRUE반환, 있을 경우 FALSE 반환
    • %ISOPEN - 현재 묵시적 커서가 메모리에 Open되어 있을경우 TRUE , 아니면 FALSE 값을 가지는 속성
SQL%속성

-- 묵시적 커서 사용 예
BEGIN
	INSERT INTO cp_emp2(empno, ename, sal, hiredate)
  VALUES(empno, ename, sal, SYSDATE);
                                    
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'행 입력 성공');
  COMMIT;      
  DBMS_OUTPUT.PUT_LINE(empno||'번 사원의 연봉을 '|| sal||'로 입력하였습니다.');
END;
/

명시적 커서

  • 개발자가 정의해서 사용하는 커서
  • 생명주기를 가진다
    • 선언->열기->인출->닫기
  • 사용할 수 있는 속성이 많다
-- 속성의 사용
커서명%속성명

-- 동작 제어
동작명 커서명

DECLARE
	-- 커서 선언, SYS_REFCURSOR를 이용하면 커서 결과를 외부로 보낼 수 있음
	CURSOR 커서명 IS 
		SELECT ...

BEGIN
	-- 커서 열기
	OPEN 커서명;

	-- 커서 인출
	LOOP
	    FETCH 커서명 INTO 변수명,... ;
	
	    -- 커서에서 인출한 값이 없다면 반복문을 빠져나가야한다
	    EXIT WHEN (커서명%NOTFOUND);
	    
	    -- 값이 있었을 때 처리할 코드들..
	END LOOP;

	-- 사용한 커서 닫기
	CLOSE 커서명;

END;
/

FOR을 사용한 CURSOR

  • 생명주기를 개발자가 관리하지 않음
    • 선언 -> 열기 -> 인출 -> 닫기
  • 두가지 형태로 커서에서 FOR가 사용됨
    1. 선언하여 커서 사용
    2. 선언없이 커서 사용
  1. 선언하여 커서 사용
    • 입력값으로 조건을 사용하면 값의 검증을 수행할 수 없다
DECLARE
		-- 1. 커서 선언
    CURSOR 커서명 IS
     SELECT ... ;
...
BEGIN
		-- 2. 열기 없이 인출
    FOR 레코드변수명 IN 커서명 
    LOOP
        레코드변수명.컬럼명으로 사용
    END LOOP;
...
END;
  1. 선언없이 커서 사용
BEGIN
...
    FOR 변수명 IN (SELECT ... ) LOOP

        변수명 -- 조회된 결과를 얻을 수 있다

    END LOOP;
...
END;

REFCURSOR

  • 커서의 제어권을 외부(실행하는 쪽)으로 넘길 때 사용
  • PKG_TYP 패키지에 GRefCursor 란 타입으로 REF CURSOR가 정의돼 프로시저의 OUT 파라미터로 커서를 내보내는 용도로 사용되고 있음(PKG_TYP.GRefCursor)

FUNCTION

  • FUNCTION(간접실행)는 자주사용될 코드(계산식 등 연산)를 저장해 둔 것
  • 사용자 정의 함수, 자주 사용될 코드를 미리 작성하고 필요한 곳에서 사용하기 위해 만든 것으로 쿼리문안에서 사용됨
CREATE OR REPLACE FUNCTION 함수명(매개변수 ...)
RETURN 반환형
IS
    변수 선언
BEGIN
    코드 작성
    RETURN 결과;
END;
/

PROCEDURE

  • 자주 사용될 쿼리문을 미리 만들어두고 사용하기 위한 것
  • 직접 실행
    • 컴파일된 프로시저를 실행기를 사용하여 실행하는 것
      • 실행기 : EXEC[EXCUTE]
  • 함수와 다르게 반환형이 존재하지 않는다.
    • OUT parameter를 사용하여 결과값을 외부로 내보낼 수 있다.
  • user_procedures 딕셔너리에서 확인 가능
  • java에서 CallableStatement를 사용하여 프로시저를 호출할 수 있음
  • 작성법
    • 매개변수는 IN parameter와 OUT parameter 2개가 존재
    • IN parameter
      • 프로시저 밖에 존재하는 값을 프로시저 안으로 전달하기 위해 존재
      • in parameter의 'IN'은 생략가능
      • 값 할당이 불가능, 사용만 가능
    • OUT parameter
      • 프로시저 안에 값을 밖으로 전달하기 위해 존재
        • 바인드 변수에 값 저장
        • 언어에선 바인드 변수가 없음 -> 다른방식 사용
      • out parameter의 'OUT'은 생략 불가
      • 프로시저 내부에서 값 할당이 가능
      • 내보낼 값이 없다면 생략가능
CREATE [OR REPLACE] PROCEDURE 프로시저명 (
    -- in parameter
    변수명 [IN] 데이터형,
    ...,
    -- out parameter
    변수명 OUT 데이터형
)
IS
    변수선언
BEGIN
    코드작성
END;
/

CREATE OR REPLACE PROCEDURE proc_plus (
    num1 NUMBER,
    num2 IN NUMBER,
    num3 OUT NUMBER
) IS
BEGIN
    num3 := num1 + num2;
END;
/

VAR val NUMBER; -- 바인드 변수(외부 변수) 선언
EXEC proc_plus(11, 7, :val);

PRINT val; -- 바인드 변수 출력

PROCEDURE에서 여러행 조회 결과 반환

  • SYS_REFCURSOR를 이용해 반환
CREATE OR REPLACE PROCEDURE 프로시저명(
    커서명 OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN 커서명 FOR 쿼리문정의;
    -- 선언과 열기만함
    -- 프로시저안에서 절대 인출(FETCH)하면 안됨, 인출시 밖에서 받아갈 수 없음
END;
/

PACKAGE

  • 관련있는 FUNCTION, PROCEDURE를 묶어서 관리하는 객체, HEADER, BODY로 구성됨
    • HEADER - 함수나 프로시저의 목록 정의
    • BODY - 함수나 프로시저의 코드 정의
SELECT * FROM user_procedures; -- 프로시저 목록 조회

-- 호출
패키지명.함수명
패키지명.프로시저명

TRIGGER

  • 테이블을 감시하다가 테이블에서 작업이 발생하면 연결하여 다른 작업을 처리할 때 사용(연쇄 작업)
    • Triggering Events - INSERT, UPDATE, DELETE
  • 트리거 안에서 COMMIT, ROLLBACK을 할 수 없음
SELECT * FROM user_triggers; -- 딕셔너리에서 조회 가능

CREATE OR REPLACE TRIGGER 트리거명 
AFTER|BEFORE TriggeringEvent ON 감시할테이블명
FOR EACH ROW
[DECLARE]
    -- 변수 선언
BEGIN
    -- 이벤트 비교하여 처리할 코드

		:OLD.컬럼명 -- 이전 값
		:NEW.컬럼명 -- 새로운 값
		-- INSERT : :NEW.value
		-- UPDATE : :OLD.value, :NEW.value
		-- DELETE : :OLD.value

		/* 
		  -- INSERT 비교
		  IF INSERTING THEN
		    연쇄적으로 동작할 코드
		  END IF;
		
		  -- UPDATE 비교
		  IF UPDATING THEN
		
		  -- DELETE 비교
		  IF DELETING THEN
		*/

END;
/

JOB

  • 주기적으로 특정 기능을 수행시키는 JOB을 등록하는 기능(스케줄링)
  • Oracle의 DBMS_JOB 패키지를 사용
-- 딕셔너리로 조회 가능
SELECT * FROM USER_JOBS; 
-- 또는
SELECT * FROM USER_SCHEDULER_JOBS;

/* 
DBMS_JOB.SUBMIT(
    job           out binary_integer,           -- 실행시킬 job 번호
    what          in  varchar2,                 -- 실제 작업을 수행하는 PL/SQL 또는 SQL 또는 Procedure 등
    next_date     in  date default sysdate,     -- job이 수행될 다음일자
    interval      in  varchar2 default 'null' , -- job이 수행될 다음  시간
    no_parse      in  boolean default false 
) --
*/

DECLARE JNO NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(:JNO, 'INSERT_JOB_TEST01;', SYSDATE, 'SYSDATE + 1/24/60', FALSE);
END;

Procedural Language/SQL

PL/SQL 기본 구조

  • DBMS에서 제공하는 언어적인 요소
    • PL/SQL 제공 안하는 DB도 존재
-- 콘솔 출력설정
SET serveroutput ON

DECLARE(IS)
	-- 변수 선언
BEGIN
  -- 코드 작성
END;
/

출력함수

  • 테스트 로그 기록 시 사용
-- 콘솔 출력설정
SET serveroutput ON

BEGIN
	...

	DBMS_OUTPUT.PUT('문자열'); -- 줄바꿈 없이 출력
	DBMS_OUTPUT.PUT_LINE('문자열'); -- 출력 후 줄바꿈

	...
END;
/

변수

DECLARE(IS)
   -- 변수 선언
   변수명 테이터형(크기);
   변수명 데이터형(크기) := 초기값;
BEGIN
   -- 값 할당
   변수명 := 값;

   -- 값 사용
   변수명 := 또다른변수명;
   DBMS_OUTPUT.PUT_LINE(변수명);
   DBMS_OUTPUT.PUT_LINE(3 + 변수명);
END;

참조변수(Reference Variable)

  • 만들어진 테이블의 컬럼데이터형을 참조하여 변수를 만들 수 있음
  • 컬럼하나를 참조
    • 참조변수명 테이블명.컬럼명%TYPE
  • 테이블의 모든 컬럼참조 (record type, 레코드형 변수, 레코드 변수)
    • 레코드변수명 테이블명%ROWTYPE;
    • 사용은 레코드변수명.컬럼명 := 값;
  • 참조변수의 단점
    • 데이터형을 직관적으로 알 수 없다
    • 사용도중 데이터형이 변경될 수 있다
DECLARE
-- 컬럼 하나를 참조
    참조변수명 테이블명.컬럼명%TYPE := 값;

-- 테이블의 모든 컬럼 참조(RECORD TYPE)
    레코드변수명 테이블명%ROWTYPE;
BEGIN
    변수명 := 값;
    레코드타입변수명.컬럼명 := 값;
...

연산자(Operator)

  • 산술 : +, -, *, MOD()
  • 조건문에서 사용
    • 관계 : >, <, >=, <=, =, !=(<>)
      • 일반 언어에선 '=='
    • 논리 : AND, OR, NOT
    • NULL 비교 : IS NULL
    • 범위 : BETWEEN AND
  • 문자열 연산자 : LIKE, %, _

PROMPT 통해 입력받기

  • 최 상단(DECLARE 위)에 선언
-- ACCEPT <변수명> PROMPT 'PROMPT 다이얼로그 설명 : '
ACCEPT input_empno PROMPT '사원 번호를 입력하세요 : '

DECLARE
	empno NUMBER := &input_empno; -- PROMPT로 입력받은 값을 변수에 설정 가능

제어문

  • 프로그램의 순차적인 흐름을 바꿔줄 수 있는 문장
    • BEGIN ~ END 사이에서 사용
  • 조건문, 반복문, 분기문 3가지 제공
  • 분기문 - EXIT, RETURN
  • 조건문 - IF
...
BEGIN

    IF 조건1 THEN
        조건1에 맞을 때 수행할 문장;
    ELSIF 조건2 THEN
        조건2에 맞을 때 수행할 문장;
    ELSIF 조건3 THEN
        조건3에 맞을 때 수행할 문장;
    ELSIF ...
        ...
    ELSE
        모든 조건에 맞지 않았을 때 수행할 문장
    END IF;

END;
/
  • 반복문 - LOOP
BEGIN

    LOOP 
        반복해야될 문장들;

        IF 조건 THEN
            EXIT;
        END IF;

    END LOOP;

END;

-- 또는

BEGIN

    LOOP
        반복해야될 문장들;

        EXIT WHEN (조건);

    END LOOP;

END;
  • 반복문 - FOR
BEGIN
    
    FOR 변수명 IN 시작 ..끝 LOOP
        
        반복시킬 문장들;
    END LOOP;

END;
/

-- 다중 FOR
BEGIN

    FOR 변수명 IN 시작 ..끝 LOOP
        -- 단일 FOR영역
        FOR 변수명 IN 시작 ..끝 LOOP
        -- 다중 FOR영역, 인덱스 두개 사용 가능
        -- (바깥FOR 인덱스, 안FOR 인덱스)
        END LOOP;
        -- 단일 FOR영역
    END LOOP;

END;
  • 반복문 - WHILE
BEGIN
    초기값;
    WHILE 조건식 LOOP

        반복할 수행 문장;

    증가식, 감소식;
    END LOOP;

END;

TABLE 데이터형

  • PL/SQL의 테이블 != SQL의 테이블
  • 테이블은 레코드를 저장하기 위한 객체
    • PL/SQL에서는 배열을 지원하지 않고 사용자가 만들어서 사용
    • 인덱스를 가지며 순차처리를 할 수 있는 데이터형
      • 배열 또는 리스트(파이썬)와 비슷한 객체
DECLARE
	  -- 1. 테이블 타입 정의
    TYPE 테이블타입명 IS TABLE OF 
     테이블을구성할데이터형 
     INDEX BY BINARY_INTEGER;

	  -- 2. 테이블 타입 변수 선언
		변수명 테이블타입명;
BEGIN

		-- 3. 값 설정
		변수명(인덱스) := 값;

		-- 4. 저장된 element의 개수를 얻을 수 있음
		변수명.COUNT
END;

RECORD 데이터형

  • 여러 종류의 데이터형을 묶어서 하나의 데이터형으로 사용하는 것
    • ROWTYPE의 경우 들어갈 변수들을 결정하지 못하지만 RECORD변수는 원하는 변수들을 결정할 수 있다
  • 레코드변수는 테이블변수와 같이 사용
DECLARE
		-- 1. 레코드 타입 선언
    TYPE 레코드명 IS RECORD (
        변수명 데이터형(크기),
        ...
    );

    -- 2. 레코드 타입 변수 선언
		레코드변수명 레코드명;
BEGIN
		-- 3. 값 설정
		레코드변수명.변수명 := 값;

		-- 4. 값 사용
		... := 레코드변수명.변수명;
END;
/

PL/SQL에서 Query문 사용하기

  • DDL, DCL, DML 사용할 수 있다
    • 다른건 다 같으나 SELECT만 좀 다르다
  • SELECT는 INTO절이 들어가며 한 행만 조회되어야 한다
  • FUNCTION, PROCEDURE가 매개변수를 가지고 Query를 사용하면 매개변수명과 WHERE절에서 사용하는 컬럼명이 달라야 한다

SELECT

  • 조회된 결과를 저장할 INTO절이 정의됨
  • 조회결과가 한행이 아니라면 error가 발생!
    • 에러발생 시 EXCEPTION 절로 넘어감
    • 조회된 컬럼값이 변수명에 들어감
      • 변수와 컬럼의 데이터형과 갯수는 일치해야 한다.
    • 부분이 나눠지기 때문에 컬럼명과 변수명이 같아도 된다(INSERT 처럼)
SELECT 컬럼명, ... 
INTO   변수명, ...
FROM   테이블명
...

CURSOR

  • 다른 언어로 보면 포인터
  • 커서는 인라인뷰를 사용, 메모리를 계속 사용한다.
    • 사용 후 닫아줘야 메모리 누수를 잡을 수 있다
  • 암시적(묵시적) 커서와 명시적 커서로 제공됨

암시적/묵시적 커서(SQL)

  • 개발자가 정의하지 않아도 쿼리문을 실행하면 자동으로 생성되는 커서.
  • INSERT, UPDATE, DELETE는 수행한 행의 수를 반환하는 암시적(묵시적) 커서를 사용할 수 있다.
  • 속성
    • %ROWCOUNT - 해당 커서에서 실행한 총 행의 개수(가장 마지막 행이 몇 번째 행인지 카운트)를 반환
    • %FOUND - 해당 커서 안에 아직 수행할 데이터가 있으면 TRUE반환, 없을경우 FALSE 반환
    • %NOTFOUND - 해당 커서 안에 수행할 데이터가 없을 경우 TRUE반환, 있을 경우 FALSE 반환
    • %ISOPEN - 현재 묵시적 커서가 메모리에 Open되어 있을경우 TRUE , 아니면 FALSE 값을 가지는 속성
SQL%속성

-- 묵시적 커서 사용 예
BEGIN
	INSERT INTO cp_emp2(empno, ename, sal, hiredate)
  VALUES(empno, ename, sal, SYSDATE);
                                    
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'행 입력 성공');
  COMMIT;      
  DBMS_OUTPUT.PUT_LINE(empno||'번 사원의 연봉을 '|| sal||'로 입력하였습니다.');
END;
/

명시적 커서

  • 개발자가 정의해서 사용하는 커서
  • 생명주기를 가진다
    • 선언->열기->인출->닫기
  • 사용할 수 있는 속성이 많다
-- 속성의 사용
커서명%속성명

-- 동작 제어
동작명 커서명

DECLARE
	-- 커서 선언, SYS_REFCURSOR를 이용하면 커서 결과를 외부로 보낼 수 있음
	CURSOR 커서명 IS 
		SELECT ...

BEGIN
	-- 커서 열기
	OPEN 커서명;

	-- 커서 인출
	LOOP
	    FETCH 커서명 INTO 변수명,... ;
	
	    -- 커서에서 인출한 값이 없다면 반복문을 빠져나가야한다
	    EXIT WHEN (커서명%NOTFOUND);
	    
	    -- 값이 있었을 때 처리할 코드들..
	END LOOP;

	-- 사용한 커서 닫기
	CLOSE 커서명;

END;
/

FOR을 사용한 CURSOR

  • 생명주기를 개발자가 관리하지 않음
    • 선언 -> 열기 -> 인출 -> 닫기
  • 두가지 형태로 커서에서 FOR가 사용됨
    1. 선언하여 커서 사용
    2. 선언없이 커서 사용
  1. 선언하여 커서 사용
    • 입력값으로 조건을 사용하면 값의 검증을 수행할 수 없다
DECLARE
		-- 1. 커서 선언
    CURSOR 커서명 IS
     SELECT ... ;
...
BEGIN
		-- 2. 열기 없이 인출
    FOR 레코드변수명 IN 커서명 
    LOOP
        레코드변수명.컬럼명으로 사용
    END LOOP;
...
END;
  1. 선언없이 커서 사용
BEGIN
...
    FOR 변수명 IN (SELECT ... ) LOOP

        변수명 -- 조회된 결과를 얻을 수 있다

    END LOOP;
...
END;

REFCURSOR

  • 커서의 제어권을 외부(실행하는 쪽)으로 넘길 때 사용
  • PKG_TYP 패키지에 GRefCursor 란 타입으로 REF CURSOR가 정의돼 프로시저의 OUT 파라미터로 커서를 내보내는 용도로 사용되고 있음(PKG_TYP.GRefCursor)

FUNCTION

  • FUNCTION(간접실행)는 자주사용될 코드(계산식 등 연산)를 저장해 둔 것
  • 사용자 정의 함수, 자주 사용될 코드를 미리 작성하고 필요한 곳에서 사용하기 위해 만든 것으로 쿼리문안에서 사용됨
CREATE OR REPLACE FUNCTION 함수명(매개변수 ...)
RETURN 반환형
IS
    변수 선언
BEGIN
    코드 작성
    RETURN 결과;
END;
/

PROCEDURE

  • 자주 사용될 쿼리문을 미리 만들어두고 사용하기 위한 것
  • 직접 실행
    • 컴파일된 프로시저를 실행기를 사용하여 실행하는 것
      • 실행기 : EXEC[EXCUTE]
  • 함수와 다르게 반환형이 존재하지 않는다.
    • OUT parameter를 사용하여 결과값을 외부로 내보낼 수 있다.
  • user_procedures 딕셔너리에서 확인 가능
  • java에서 CallableStatement를 사용하여 프로시저를 호출할 수 있음
  • 작성법
    • 매개변수는 IN parameter와 OUT parameter 2개가 존재
    • IN parameter
      • 프로시저 밖에 존재하는 값을 프로시저 안으로 전달하기 위해 존재
      • in parameter의 'IN'은 생략가능
      • 값 할당이 불가능, 사용만 가능
    • OUT parameter
      • 프로시저 안에 값을 밖으로 전달하기 위해 존재
        • 바인드 변수에 값 저장
        • 언어에선 바인드 변수가 없음 -> 다른방식 사용
      • out parameter의 'OUT'은 생략 불가
      • 프로시저 내부에서 값 할당이 가능
      • 내보낼 값이 없다면 생략가능
CREATE [OR REPLACE] PROCEDURE 프로시저명 (
    -- in parameter
    변수명 [IN] 데이터형,
    ...,
    -- out parameter
    변수명 OUT 데이터형
)
IS
    변수선언
BEGIN
    코드작성
END;
/

CREATE OR REPLACE PROCEDURE proc_plus (
    num1 NUMBER,
    num2 IN NUMBER,
    num3 OUT NUMBER
) IS
BEGIN
    num3 := num1 + num2;
END;
/

VAR val NUMBER; -- 바인드 변수(외부 변수) 선언
EXEC proc_plus(11, 7, :val);

PRINT val; -- 바인드 변수 출력

PROCEDURE에서 여러행 조회 결과 반환

  • SYS_REFCURSOR를 이용해 반환
CREATE OR REPLACE PROCEDURE 프로시저명(
    커서명 OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN 커서명 FOR 쿼리문정의;
    -- 선언과 열기만함
    -- 프로시저안에서 절대 인출(FETCH)하면 안됨, 인출시 밖에서 받아갈 수 없음
END;
/

PACKAGE

  • 관련있는 FUNCTION, PROCEDURE를 묶어서 관리하는 객체, HEADER, BODY로 구성됨
    • HEADER - 함수나 프로시저의 목록 정의
    • BODY - 함수나 프로시저의 코드 정의
SELECT * FROM user_procedures; -- 프로시저 목록 조회

-- 호출
패키지명.함수명
패키지명.프로시저명

TRIGGER

  • 테이블을 감시하다가 테이블에서 작업이 발생하면 연결하여 다른 작업을 처리할 때 사용(연쇄 작업)
    • Triggering Events - INSERT, UPDATE, DELETE
  • 트리거 안에서 COMMIT, ROLLBACK을 할 수 없음
SELECT * FROM user_triggers; -- 딕셔너리에서 조회 가능

CREATE OR REPLACE TRIGGER 트리거명 
AFTER|BEFORE TriggeringEvent ON 감시할테이블명
FOR EACH ROW
[DECLARE]
    -- 변수 선언
BEGIN
    -- 이벤트 비교하여 처리할 코드

		:OLD.컬럼명 -- 이전 값
		:NEW.컬럼명 -- 새로운 값
		-- INSERT : :NEW.value
		-- UPDATE : :OLD.value, :NEW.value
		-- DELETE : :OLD.value

		/* 
		  -- INSERT 비교
		  IF INSERTING THEN
		    연쇄적으로 동작할 코드
		  END IF;
		
		  -- UPDATE 비교
		  IF UPDATING THEN
		
		  -- DELETE 비교
		  IF DELETING THEN
		*/

END;
/

JOB

  • 주기적으로 특정 기능을 수행시키는 JOB을 등록하는 기능(스케줄링)
  • Oracle의 DBMS_JOB 패키지를 사용
-- 딕셔너리로 조회 가능
SELECT * FROM USER_JOBS; 
-- 또는
SELECT * FROM USER_SCHEDULER_JOBS;

/* 
DBMS_JOB.SUBMIT(
    job           out binary_integer,           -- 실행시킬 job 번호
    what          in  varchar2,                 -- 실제 작업을 수행하는 PL/SQL 또는 SQL 또는 Procedure 등
    next_date     in  date default sysdate,     -- job이 수행될 다음일자
    interval      in  varchar2 default 'null' , -- job이 수행될 다음  시간
    no_parse      in  boolean default false 
) --
*/

DECLARE JNO NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(:JNO, 'INSERT_JOB_TEST01;', SYSDATE, 'SYSDATE + 1/24/60', FALSE);
END;

Procedural Language/SQL

PL/SQL 기본 구조

  • DBMS에서 제공하는 언어적인 요소
    • PL/SQL 제공 안하는 DB도 존재
-- 콘솔 출력설정
SET serveroutput ON

DECLARE(IS)
	-- 변수 선언
BEGIN
  -- 코드 작성
END;
/

출력함수

  • 테스트 로그 기록 시 사용
-- 콘솔 출력설정
SET serveroutput ON

BEGIN
	...

	DBMS_OUTPUT.PUT('문자열'); -- 줄바꿈 없이 출력
	DBMS_OUTPUT.PUT_LINE('문자열'); -- 출력 후 줄바꿈

	...
END;
/

변수

DECLARE(IS)
   -- 변수 선언
   변수명 테이터형(크기);
   변수명 데이터형(크기) := 초기값;
BEGIN
   -- 값 할당
   변수명 := 값;

   -- 값 사용
   변수명 := 또다른변수명;
   DBMS_OUTPUT.PUT_LINE(변수명);
   DBMS_OUTPUT.PUT_LINE(3 + 변수명);
END;

참조변수(Reference Variable)

  • 만들어진 테이블의 컬럼데이터형을 참조하여 변수를 만들 수 있음
  • 컬럼하나를 참조
    • 참조변수명 테이블명.컬럼명%TYPE
  • 테이블의 모든 컬럼참조 (record type, 레코드형 변수, 레코드 변수)
    • 레코드변수명 테이블명%ROWTYPE;
    • 사용은 레코드변수명.컬럼명 := 값;
  • 참조변수의 단점
    • 데이터형을 직관적으로 알 수 없다
    • 사용도중 데이터형이 변경될 수 있다
DECLARE
-- 컬럼 하나를 참조
    참조변수명 테이블명.컬럼명%TYPE := 값;

-- 테이블의 모든 컬럼 참조(RECORD TYPE)
    레코드변수명 테이블명%ROWTYPE;
BEGIN
    변수명 := 값;
    레코드타입변수명.컬럼명 := 값;
...

연산자(Operator)

  • 산술 : +, -, *, MOD()
  • 조건문에서 사용
    • 관계 : >, <, >=, <=, =, !=(<>)
      • 일반 언어에선 '=='
    • 논리 : AND, OR, NOT
    • NULL 비교 : IS NULL
    • 범위 : BETWEEN AND
  • 문자열 연산자 : LIKE, %, _

PROMPT 통해 입력받기

  • 최 상단(DECLARE 위)에 선언
-- ACCEPT <변수명> PROMPT 'PROMPT 다이얼로그 설명 : '
ACCEPT input_empno PROMPT '사원 번호를 입력하세요 : '

DECLARE
	empno NUMBER := &input_empno; -- PROMPT로 입력받은 값을 변수에 설정 가능

제어문

  • 프로그램의 순차적인 흐름을 바꿔줄 수 있는 문장
    • BEGIN ~ END 사이에서 사용
  • 조건문, 반복문, 분기문 3가지 제공
  • 분기문 - EXIT, RETURN
  • 조건문 - IF
...
BEGIN

    IF 조건1 THEN
        조건1에 맞을 때 수행할 문장;
    ELSIF 조건2 THEN
        조건2에 맞을 때 수행할 문장;
    ELSIF 조건3 THEN
        조건3에 맞을 때 수행할 문장;
    ELSIF ...
        ...
    ELSE
        모든 조건에 맞지 않았을 때 수행할 문장
    END IF;

END;
/
  • 반복문 - LOOP
BEGIN

    LOOP 
        반복해야될 문장들;

        IF 조건 THEN
            EXIT;
        END IF;

    END LOOP;

END;

-- 또는

BEGIN

    LOOP
        반복해야될 문장들;

        EXIT WHEN (조건);

    END LOOP;

END;
  • 반복문 - FOR
BEGIN
    
    FOR 변수명 IN 시작 ..끝 LOOP
        
        반복시킬 문장들;
    END LOOP;

END;
/

-- 다중 FOR
BEGIN

    FOR 변수명 IN 시작 ..끝 LOOP
        -- 단일 FOR영역
        FOR 변수명 IN 시작 ..끝 LOOP
        -- 다중 FOR영역, 인덱스 두개 사용 가능
        -- (바깥FOR 인덱스, 안FOR 인덱스)
        END LOOP;
        -- 단일 FOR영역
    END LOOP;

END;
  • 반복문 - WHILE
BEGIN
    초기값;
    WHILE 조건식 LOOP

        반복할 수행 문장;

    증가식, 감소식;
    END LOOP;

END;

TABLE 데이터형

  • PL/SQL의 테이블 != SQL의 테이블
  • 테이블은 레코드를 저장하기 위한 객체
    • PL/SQL에서는 배열을 지원하지 않고 사용자가 만들어서 사용
    • 인덱스를 가지며 순차처리를 할 수 있는 데이터형
      • 배열 또는 리스트(파이썬)와 비슷한 객체
DECLARE
	  -- 1. 테이블 타입 정의
    TYPE 테이블타입명 IS TABLE OF 
     테이블을구성할데이터형 
     INDEX BY BINARY_INTEGER;

	  -- 2. 테이블 타입 변수 선언
		변수명 테이블타입명;
BEGIN

		-- 3. 값 설정
		변수명(인덱스) := 값;

		-- 4. 저장된 element의 개수를 얻을 수 있음
		변수명.COUNT
END;

RECORD 데이터형

  • 여러 종류의 데이터형을 묶어서 하나의 데이터형으로 사용하는 것
    • ROWTYPE의 경우 들어갈 변수들을 결정하지 못하지만 RECORD변수는 원하는 변수들을 결정할 수 있다
  • 레코드변수는 테이블변수와 같이 사용
DECLARE
		-- 1. 레코드 타입 선언
    TYPE 레코드명 IS RECORD (
        변수명 데이터형(크기),
        ...
    );

    -- 2. 레코드 타입 변수 선언
		레코드변수명 레코드명;
BEGIN
		-- 3. 값 설정
		레코드변수명.변수명 := 값;

		-- 4. 값 사용
		... := 레코드변수명.변수명;
END;
/

PL/SQL에서 Query문 사용하기

  • DDL, DCL, DML 사용할 수 있다
    • 다른건 다 같으나 SELECT만 좀 다르다
  • SELECT는 INTO절이 들어가며 한 행만 조회되어야 한다
  • FUNCTION, PROCEDURE가 매개변수를 가지고 Query를 사용하면 매개변수명과 WHERE절에서 사용하는 컬럼명이 달라야 한다

SELECT

  • 조회된 결과를 저장할 INTO절이 정의됨
  • 조회결과가 한행이 아니라면 error가 발생!
    • 에러발생 시 EXCEPTION 절로 넘어감
    • 조회된 컬럼값이 변수명에 들어감
      • 변수와 컬럼의 데이터형과 갯수는 일치해야 한다.
    • 부분이 나눠지기 때문에 컬럼명과 변수명이 같아도 된다(INSERT 처럼)
SELECT 컬럼명, ... 
INTO   변수명, ...
FROM   테이블명
...

CURSOR

  • 다른 언어로 보면 포인터
  • 커서는 인라인뷰를 사용, 메모리를 계속 사용한다.
    • 사용 후 닫아줘야 메모리 누수를 잡을 수 있다
  • 암시적(묵시적) 커서와 명시적 커서로 제공됨

암시적/묵시적 커서(SQL)

  • 개발자가 정의하지 않아도 쿼리문을 실행하면 자동으로 생성되는 커서.
  • INSERT, UPDATE, DELETE는 수행한 행의 수를 반환하는 암시적(묵시적) 커서를 사용할 수 있다.
  • 속성
    • %ROWCOUNT - 해당 커서에서 실행한 총 행의 개수(가장 마지막 행이 몇 번째 행인지 카운트)를 반환
    • %FOUND - 해당 커서 안에 아직 수행할 데이터가 있으면 TRUE반환, 없을경우 FALSE 반환
    • %NOTFOUND - 해당 커서 안에 수행할 데이터가 없을 경우 TRUE반환, 있을 경우 FALSE 반환
    • %ISOPEN - 현재 묵시적 커서가 메모리에 Open되어 있을경우 TRUE , 아니면 FALSE 값을 가지는 속성
SQL%속성

-- 묵시적 커서 사용 예
BEGIN
	INSERT INTO cp_emp2(empno, ename, sal, hiredate)
  VALUES(empno, ename, sal, SYSDATE);
                                    
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'행 입력 성공');
  COMMIT;      
  DBMS_OUTPUT.PUT_LINE(empno||'번 사원의 연봉을 '|| sal||'로 입력하였습니다.');
END;
/

명시적 커서

  • 개발자가 정의해서 사용하는 커서
  • 생명주기를 가진다
    • 선언->열기->인출->닫기
  • 사용할 수 있는 속성이 많다
-- 속성의 사용
커서명%속성명

-- 동작 제어
동작명 커서명

DECLARE
	-- 커서 선언, SYS_REFCURSOR를 이용하면 커서 결과를 외부로 보낼 수 있음
	CURSOR 커서명 IS 
		SELECT ...

BEGIN
	-- 커서 열기
	OPEN 커서명;

	-- 커서 인출
	LOOP
	    FETCH 커서명 INTO 변수명,... ;
	
	    -- 커서에서 인출한 값이 없다면 반복문을 빠져나가야한다
	    EXIT WHEN (커서명%NOTFOUND);
	    
	    -- 값이 있었을 때 처리할 코드들..
	END LOOP;

	-- 사용한 커서 닫기
	CLOSE 커서명;

END;
/

FOR을 사용한 CURSOR

  • 생명주기를 개발자가 관리하지 않음
    • 선언 -> 열기 -> 인출 -> 닫기
  • 두가지 형태로 커서에서 FOR가 사용됨
    1. 선언하여 커서 사용
    2. 선언없이 커서 사용
  1. 선언하여 커서 사용
    • 입력값으로 조건을 사용하면 값의 검증을 수행할 수 없다
DECLARE
		-- 1. 커서 선언
    CURSOR 커서명 IS
     SELECT ... ;
...
BEGIN
		-- 2. 열기 없이 인출
    FOR 레코드변수명 IN 커서명 
    LOOP
        레코드변수명.컬럼명으로 사용
    END LOOP;
...
END;
  1. 선언없이 커서 사용
BEGIN
...
    FOR 변수명 IN (SELECT ... ) LOOP

        변수명 -- 조회된 결과를 얻을 수 있다

    END LOOP;
...
END;

REFCURSOR

  • 커서의 제어권을 외부(실행하는 쪽)으로 넘길 때 사용
  • PKG_TYP 패키지에 GRefCursor 란 타입으로 REF CURSOR가 정의돼 프로시저의 OUT 파라미터로 커서를 내보내는 용도로 사용되고 있음(PKG_TYP.GRefCursor)

FUNCTION

  • FUNCTION(간접실행)는 자주사용될 코드(계산식 등 연산)를 저장해 둔 것
  • 사용자 정의 함수, 자주 사용될 코드를 미리 작성하고 필요한 곳에서 사용하기 위해 만든 것으로 쿼리문안에서 사용됨
CREATE OR REPLACE FUNCTION 함수명(매개변수 ...)
RETURN 반환형
IS
    변수 선언
BEGIN
    코드 작성
    RETURN 결과;
END;
/

PROCEDURE

  • 자주 사용될 쿼리문을 미리 만들어두고 사용하기 위한 것
  • 직접 실행
    • 컴파일된 프로시저를 실행기를 사용하여 실행하는 것
      • 실행기 : EXEC[EXCUTE]
  • 함수와 다르게 반환형이 존재하지 않는다.
    • OUT parameter를 사용하여 결과값을 외부로 내보낼 수 있다.
  • user_procedures 딕셔너리에서 확인 가능
  • java에서 CallableStatement를 사용하여 프로시저를 호출할 수 있음
  • 작성법
    • 매개변수는 IN parameter와 OUT parameter 2개가 존재
    • IN parameter
      • 프로시저 밖에 존재하는 값을 프로시저 안으로 전달하기 위해 존재
      • in parameter의 'IN'은 생략가능
      • 값 할당이 불가능, 사용만 가능
    • OUT parameter
      • 프로시저 안에 값을 밖으로 전달하기 위해 존재
        • 바인드 변수에 값 저장
        • 언어에선 바인드 변수가 없음 -> 다른방식 사용
      • out parameter의 'OUT'은 생략 불가
      • 프로시저 내부에서 값 할당이 가능
      • 내보낼 값이 없다면 생략가능
CREATE [OR REPLACE] PROCEDURE 프로시저명 (
    -- in parameter
    변수명 [IN] 데이터형,
    ...,
    -- out parameter
    변수명 OUT 데이터형
)
IS
    변수선언
BEGIN
    코드작성
END;
/

CREATE OR REPLACE PROCEDURE proc_plus (
    num1 NUMBER,
    num2 IN NUMBER,
    num3 OUT NUMBER
) IS
BEGIN
    num3 := num1 + num2;
END;
/

VAR val NUMBER; -- 바인드 변수(외부 변수) 선언
EXEC proc_plus(11, 7, :val);

PRINT val; -- 바인드 변수 출력

PROCEDURE에서 여러행 조회 결과 반환

  • SYS_REFCURSOR를 이용해 반환
CREATE OR REPLACE PROCEDURE 프로시저명(
    커서명 OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN 커서명 FOR 쿼리문정의;
    -- 선언과 열기만함
    -- 프로시저안에서 절대 인출(FETCH)하면 안됨, 인출시 밖에서 받아갈 수 없음
END;
/

PACKAGE

  • 관련있는 FUNCTION, PROCEDURE를 묶어서 관리하는 객체, HEADER, BODY로 구성됨
    • HEADER - 함수나 프로시저의 목록 정의
    • BODY - 함수나 프로시저의 코드 정의
SELECT * FROM user_procedures; -- 프로시저 목록 조회

-- 호출
패키지명.함수명
패키지명.프로시저명

TRIGGER

  • 테이블을 감시하다가 테이블에서 작업이 발생하면 연결하여 다른 작업을 처리할 때 사용(연쇄 작업)
    • Triggering Events - INSERT, UPDATE, DELETE
  • 트리거 안에서 COMMIT, ROLLBACK을 할 수 없음
SELECT * FROM user_triggers; -- 딕셔너리에서 조회 가능

CREATE OR REPLACE TRIGGER 트리거명 
AFTER|BEFORE TriggeringEvent ON 감시할테이블명
FOR EACH ROW
[DECLARE]
    -- 변수 선언
BEGIN
    -- 이벤트 비교하여 처리할 코드

		:OLD.컬럼명 -- 이전 값
		:NEW.컬럼명 -- 새로운 값
		-- INSERT : :NEW.value
		-- UPDATE : :OLD.value, :NEW.value
		-- DELETE : :OLD.value

		/* 
		  -- INSERT 비교
		  IF INSERTING THEN
		    연쇄적으로 동작할 코드
		  END IF;
		
		  -- UPDATE 비교
		  IF UPDATING THEN
		
		  -- DELETE 비교
		  IF DELETING THEN
		*/

END;
/

JOB

  • 주기적으로 특정 기능을 수행시키는 JOB을 등록하는 기능(스케줄링)
  • Oracle의 DBMS_JOB 패키지를 사용
-- 딕셔너리로 조회 가능
SELECT * FROM USER_JOBS; 
-- 또는
SELECT * FROM USER_SCHEDULER_JOBS;

/* 
DBMS_JOB.SUBMIT(
    job           out binary_integer,           -- 실행시킬 job 번호
    what          in  varchar2,                 -- 실제 작업을 수행하는 PL/SQL 또는 SQL 또는 Procedure 등
    next_date     in  date default sysdate,     -- job이 수행될 다음일자
    interval      in  varchar2 default 'null' , -- job이 수행될 다음  시간
    no_parse      in  boolean default false 
) --
*/

DECLARE JNO NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(:JNO, 'INSERT_JOB_TEST01;', SYSDATE, 'SYSDATE + 1/24/60', FALSE);
END;

'ORACLE' 카테고리의 다른 글

Oracle - Job  (0) 2022.09.06
Oracle - DDL  (0) 2022.08.05
ORACLE PROCEDURE  (0) 2022.08.05
SQL Developer을 이용한 PL/SQL 디버깅  (0) 2022.08.05

'ORACLE'의 다른글

  • 현재글Oracle PL/SQL 기본 문법 정리

관련글

티스토리툴바