2022-08-16 (Tue)

Contents

PL/SQL

개념

• Oracle에서 지원하는 프로그래밍 언어의 특성을 수용한 SQL의 확장

• PL/SQL Block내에서 SQL의 DML문과 Query문, 그리고 절차형 언어(IF, LOOP) 등을 사용해 절차적으로 프로그래밍을 가능하게 한 강력한 트랜잭션 언어

 

장점

• 프로그램 개발의 모듈화 : 복잡한 프로그램을 의미있고 잘 정의된 작은 Block으로 분해
• 변수선언                              : 테이블과 칼럼의 데이터 타입을 기반으로 하는 유동적인 변수 선언
• 에러처리                        : Exception 처리 루틴을 사용해 Oracle 서버 에러 처리
• 이식성                           : Oracle과 PL/SQL을 지원하는 어떤 호스트로도 프로그램 이동 가능
• 성능 향상                     : 응용 프로그램 성능 향상

 

기본 Block 구조

 

FUNCTION / PROCEDURE 구조

∙ 함수         : 리턴 값 하나 (간단한 계산식)
∙ 프로시저 : 리턴 값 X. 근데 파라미터 통해 여러 개 돌려줄 수 있다. (DML 작업할 때)

할당 연산자는 := 사용

 

Parameter 타입 및 선언 방법

① IN           : 실행환경                            ⇨  Function / Procedure

② OUT      : Function / Procedure ⇨  실행환경

③ IN OUT : 양쪽으로 전달

 

 

 %TYPE Attribute

✔ 테이블의 칼럼과      똑같은 데이터 타입으로 변수 선언 시 사용
✔ 미리 선언된 변수와 똑같은 데이터 타입으로 변수 선언 시 사용

 

 %ROWTYPE Attribute

✔ 테이블/뷰의 컬럼들로 구성된 레코드 선언 시 사용하면,
레코드 내의 Field는 테이블/뷰의 컬럼명, 데이터 타입을 그대로 가져옴

 

 IF문

 

CURSOR 🌟🌟🌟

정의

Oracle Server는 SQL문을 실행하고 처리한 정보를 저장하기 위해 "Private SQL Area" 이라고 하는 작업영역을 이용하는데, 이 영역에 이름을 부여하고 저장된 정보를 처리할 수 있게 해주는 것

종류

1) Implicit (묵시적) CURSOR ➡ DML문과 SELECT문에 의해 내부적으로 선언 
2) Explicit (명시적)  CURSOR ➡ 사용자가 선언하고 이름을 정의해 사용 

Attribute

1) SQL%ROWCOUNT :  가장 최근의 SQL문에 의해 처리된 Row 수
2) SQL%FOUND          : 가장 최근의 SQL문에 의해 처리된 Row의 개수가 한 개 이상이면 True
3) SQL%NOTFOUND  : 가장 최근의 SQL문에 의해 처리된 Row의 개수가 없으면 True

4단계  🌟🌟

1) DECLARE 단계 : 커서에 이름을 부여하고 커서내에서 수행할 SELECT문을 정의함으로써 CURSOR를 선언
2) OPEN 단계        : OPEN문은 참조되는 변수를 연결하고, SELECT문을 실행
3) FETCH 단계      : CURSOR로부터 Pointer가 존재하는 Record의 값을 변수에 전달
4) CLOSE 단계      : Record의 Active Set을 닫아 주고, 다시 새로운 Active Set을 만들어 OPEN할 수 있게 해줌

 

 LOOP문

 

예외

1. 미리 정의된 예외

• NO_DATA_FOUND      : SELECT문이 아무런 데이터 행을 반환하지 못할 때
• DUP_VAL_ON_INDEX  : UNIQUE 제약을 갖는 컬럼에 중복되는 데이터 INSERT 될 때
• ZERO_DIVIDE             : 0으로 나눌 때
• INVALID_CURSOR      : 잘못된 커서 연산

2. 사용자 정의 Error