PL/SQL💻

1. 기본

😋 기본 단위
블록

🐱기본 형식

DECLARE 필요한 여러 요소 선언
BEGIN 명령어 실행 부분
EXCEPTION 예외 처리(생략 가능)
END; 구문 마무리
/ PL/SQL구문을 실행한다(RUN의 의미)

🐢 EX1>

SET SERVEROUTPUT ON; 실행결과를 화면에 출력
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO');
   ->DBMS_OUTPUT : 패키지
   ->PUT_LINE : 프로시저
END;

2. 변수 선언 및 종류

🐱변수선언

변수이름 자료형 := 값;

🐱상수선언

변수이름 CONSTANT 자료형 := 값;

🐱변수 기본값 지정

변수이름 자료형 DEFAULT 값;

**참고**
- 변수 이름 정하기
1. 같은 블록 안에서 변수이름 고유해야 함
2. 대/소문자 구별 ❌
3. 문자로 시작, 영어(30자) / 한글(15자), 특수문자($ /# / _) 가능
4. SQL 키워드 사용 금지

😋 변수 자료형
스칼라형 - 오라클에서 기본으로 정의해놓은 자료형
참조형 - 1)특정 테이블 자료형이나 2)행 구조 참조

1) 특정 테이블 자료형 참조

변수이름 테이블이름%ROWTYPE;

2) 행 구조 참조

변수이름 테이블이름.열이름%TYPE;

LOB - 대용량의 텍스트, 이미지, 동영상, 사운드 데이터 등 대용량 데이터 저장위한 자료형 종류 : BLOB(BYTE), CLOB(CHARACTER)

3. 조건문

🐱IF 기본형식

IF 조건식 THEN 수행할 명령;
ELSIF 조건식 THEN 수행할 명령;
ELSE THEN 수행할 명령;
END IF; -IF 조건문 종료

🐱단순 CASE 기본형식

CASE 비교기준
   WEHN 값1 THEN 수행할 명령;
   ELSE 수행할 명령;
END CASE; -CASE 조건문 종료

🐱검색 CASE 기본형식 *검색 CASE문은 비교기준 명시X, 각 WHEN절에 조건식 명시

CASE
   WEHN 조건식1 THEN 수행할 명령;
   WHEN 조건식2 THEN 수행할 명령;
   ELSE 수행할 명령;
END CASE; -CASE 조건문 종료

4. 반복문

😋 반복문 종류
1. 기본 LOOP ≒ do-while문
2. WHILE LOOP - 특정 조건 만족시 반복문 수행
3. FOR LOOP - 반복 횟수 정한 반복문
4. Cusor FOR LOOP - 커서 이용 반복문

😋 반복수행 중단
1. EXIT - 수행 중 반복문 종료
2. EXIT-WHEN - 조건식 만족하면 반복문 종료

😋 특정 반복 주기 건너뜀
1. CONTINUE - 수행중인 현재 주기 건너뜀
2. CONTINUE-WHEN - 조건식 만족하면 현재 주기 건너뜀

🐱기본 LOOP 기본형식

LOOP 반복 수행 작업;
END LOOP; -LOOP 종료

🐱WHILE LOOP 기본형식

WHILE 조건식 LOOP 반복 수행 작업;
END LOOP; -LOOP 종료

🐱FOR LOOP 기본형식

FOR i IN (REVERSE-역순) 시작값..종료값 LOOP 반복 수행 작업;
END LOOP; -LOOP 종료

5. 레코드

😋 레코드
자료형이 각기 다른 데이터를 하나의 변수에 저장하는데 사용
(C의 struct(구조체)와 비슷한 의미)

🐱기본형식

TYPE 레코드 이름 IS RECORD(
   변수이름 자료형 (NOT NULL - 설정 및 생략 가능) := 값,
   변수이름 자료형 (NOT NULL - 설정 및 생략 가능) := 값
);

- 특징 : INSERT, UPDATE 가능

6. 컬렉션

😋 컬렉션
자료형이 같은 여러 데이터 저장하는데 사용
(배열과 같은 의미)

많이 사용하는 varray만 알아보겠다,,

🐱VARRAY 기본형식

TYPE 배열 이름 IS VARRAY(배열길이) OF 자료형(길이);
   (type : 새로운 자료형을 만들어냄)
변수이름 배열이름 := 배열이름(값,값..);
   (DB에서는 배열의 index가 1부터 시작됨)

7. 커서

😋 커서 정의

• SQL문 실행 시 SQL문 처리하는 정보를 저장한 메모리 공간
  (정확히 말하자면 메모리의 포인터를 말함, C언어의 포인터와 비슷)
• 커서를 사용하면 실행된 SQL문의 결과값을 사용할 수 있음

😋명시적 커서
1단계 : 커서 선언
2단계 : 커서 open
3단계 : 커서에서 읽어온 데이터 사용(FETCH)
4단계 : 커서 close

🐱기본형식

DECLARE
   CURSOR 커서 이름 IS SQL문; -1단계
BEGIN
   OPEN 커서 이름; -2단계
   FETCH 커서이름 INTO 변수 -3단계
   CLOSE 커서 이름; -4단계
END;

🐢EX>1

DECLARE
   V_DEPT_ROW DEPT%ROWTYPE;
   (커서 데이터 입력할 변수 선언, V_DEPT_ROW에 DEPT테이블의 모든 행 자료형과 같이 지정해주겠다)
CURSOR c1 IS
   SELECT DEPTNO
   FROM DEPT
   WHERE DEPTNO=40; -명시적 커서 선언
BEGIN
OPEN c1; -커서 open
FETCH c1 INTO V_DEPT_ROW; -커서로부터 읽어온 데이터 사용
DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || V_DEPT_ROW.DEPTNO);
CLOSE c1; -커서 close
END;
/

😋 커서와 FOR LOOP 사용

• FOR LOOP를 사용하면 OPEN, FETCH, CLOSE문을 작성하지 않는다
  (각 명령어를 자동으로 수행하여 커서 사용 방법이 간단함)

😋 커서와 파라미터 사용

• 보통, 고정 값이 아닌 직접 입력한 값 또는 상황에 따라 여러 값을 번갈아 사용하기 위해 이러한 방법을 사용한다.

😋 묵시적 커서

• 별다른 선언 없이 오라클에서 자동으로 선언되는 커서
  (여러 행의 결과를 가지는 커서는 명시적 커서로만 사용 가능)

커서는 사용자가 sql문을 실행시키면 결과값을 저장공간에 가지고 있다가 원하는 시기에 순차적으로 FETCH 처리해 결과를 프로그래밍적으로 접근할 수 있게 해줌

8. 예외처리

🐱기본형식
🐢EX1>

DECLARE
   v_wrong NUMBER;
BEGIN
   SELECT DNAME INTO v_wrong
   FROM DEPT
   WHERE DEPTNO=10;
EXCEPTION
   WHEN VALUE_ERROR THEN
   (VALUE_ERROR : 사전 정의된 예외 이름)
       DBMS_OUTPUT.PUT_LINE('예외 처리 : 수치 또는 값 오류 발생');
   WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('예외 처리 : 사전 정의 외 오류 발생');
END;
/

🐱이름 없는 예외 사용 형식

DECLARE
   예외이름1 EXCEPTION;
   (예외 이름 직접 지정)
   PRAGMA EXCEPTION_INIT(예외이름1, 예외번호);
   (오라클 예외 번호와 함께 이름 붙이기)
   .
   .
EXCEPTION
   WHEN 예외이름1 THEN
       예외처리에 사용할 명령어
       .
       .
END;

🐱사용자 정의 예외 사용 형식

DECLARE
   사용자 예외이름 EXCEPTION;
   .
   .
BEGIN
   IF 사용자 예외를 발생시킬 조건 THEN
       RAISE 사용자 예외이름
       (RAISE 키워드 이용하여 예외를 직접 만들 수 있음)
       .
       .
   END IF;
EXCEPTION
   WHEN 사용자 예외 이름 THEN
       예외처리에 사용할 명령어
       .
       .
END;

9. 저장 서브 프로그램

😋 저장 서브 프로그램 정의

• 이름을 갖는 PL/SQL을 말한다
• 종류 : 프로시저, 함수, 패키지, 트리거

10. 프로시저

프로시저는 파라미터를 사용할 수도, 사용하지 않을 수도 있다

🐱프로시저 생성

CREATE [OR REPLACE] PROCEDURE 프로시저 이름
IS | AS
   선언부
BEGIN
(EXCEPTION
   예외 처리부) - 생략가능
END [프로시저 이름];
/

🐱프로시저 실행

EXECUTE 프로시저 이름;

🐱프로시저 삭제

DROP PROCEDURE 프로시저 이름;

🐱파라미터 있는 프로시저

CREATE [OR REPLACE] PROCEDURE 프로시저 이름
(
   a IN NUMBER,
   b NUMBER
)
(IN은 기본값이므로 생략 가능하다)
IS | AS
   선언부(생략 가능)
BEGIN
   실행부
(EXCEPTION
   예외 처리부) - 생략가능
END [프로시저 이름];
/

프로시저는 기본적으로 함수와 달리 값을 반환하지 않지만, OUT 모드 혹은 IN OUT 모드로 설정시 프로시저 실행 후 호출한 프로그램으로 값을 반환할 수 있다

프로시저는 기능을 수행하는 절차 그 자체를 목적으로 하는 것이다

11. 함수

🐱함수 생성

CREATE [OR REPLACE] FUNCTION 함수 이름
(
   a IN NUMBER
)
RETURN NUMBER -반환 타입 설정
IS | AS
   선언부(생략 가능)
BEGIN
   실행부
   RETURN (반환값); -반드시 지정해줄것!
(EXCEPTION
   예외 처리부) - 생략가능
END [함수 이름];
/

😋 함수 실행

• 익명 블록 또는 프로시저와 같은 저장 서브프로그램, SQL문에서 사용가능!
• 함수 반환값 대입받을 변수가 필요함

🐱함수 삭제

DROP FUNCTION 함수 이름;

함수는 기능을 수행하여 어떠한 결과를 도출해 내는 것이다

12. 패키지

😋 패키지 구조

• 패키지 명세 + 패키지 본문

🐱패키지 명세

CREATE [OR REPLACE] PACKAGE 패키지 이름
IS | AS
   서브프로그램을 포함한 다양한 객체 선언
END [패키지 이름];

🐱패키지 본문

CREATE [OR REPLACE] PACKAGE BODY 패키지 이름
IS | AS
   패키지 명세에서 선언한 서브프로그램을 포함한 여러 객체를 정의
   경우에 따라 패키지 명세에 존재하지 않는 객체 및 서브프로그램도 정의 가능
END [패키지 이름];

그 외 : 기본적으로 서브프로그램 이름은 중복될 수 없지만, 오버로드는 가능

참고 : 자바가 패키지를 사용하는 이유와 sql에서 패키지를 사용하는 이유는 같음!

13. 트리거

😋 트리거 정의

• DB안에서 이벤트가 발생할 경우 자동으로 실행되는 기능을 정의하는 PL/SQL 서브프로그램

• 트리거의 종류는 여러가지이지만, DML 트리거를 많이 사용함

🐱DML트리거 형식

CREATE [OR REPLACE] TRIGGER 트리거 이름
BEFORE | AFTER
INSERT | UPDATE | DELETE ON 테이블 이름
(REFERENCEING OLD as old | NEW as new) -생략가능 < 변경 전/후 값 참조 시 사용
FOR EACH ROW WHEN 조건식
(FOLLOWS 트리거 이름2, 트리거 이름3 ..) - 생략가능 < 여러 관련 트리거의 실행 순서 지정
(ENABLE | DISABLE) - 생략가능 < 트리거의 활성화 비활성화 지정
DECLARE
   선언부
BEGIN
   실행부
(EXCEPTION
   예외 처리부)
END;

- 보통 DML 트리거의 트리거 작동 DML 명령어가 1)INSERT인 경우 타이밍을 AFTER, 2)UPDATE/DELETE인 경우 타이밍을 BEFORE로 지정한다
1) 의 경우 타이밍이 AFTER이므로 삽입된 그 값을 참조하기 위해서는 ':NEW.컬럼명' 을 사용
2) 의 경우 타이밍이 BEFORE이므로 삭제/변경되기 전 그 값을 참조하기 위해서는 ':OLD.컬럼명' 을 사용