PL/SQL이란?

- PL/SQL 은 Oracle’s Procedural Language extension to SQL 의 약자 이다.

- SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR)등을 지원하며,오라클 자체에 내장되어 있는 Procedure Language 이다.

- DECLARE문을 이용하여 정의되며, 선언문의 사용은 선택 사항 이다.

- PL/SQL 문은 블록 구조로 되어 있고 PL/SQL자신이 컴파일 엔진을 가지고 있다.

PL/SQL의 장점

- PL/SQL 문은 BLOCK 구조로 다수의 SQL 문을 한번에 ORACLE DB로 보내서 처리하므로 수행속도를 향상 시킬수 있다.

- PL/SQL 의 모든 요소는 하나 또는 두개이상의 블록으로 구성하여 모듈화가 가능하다.

- 보다 강력한 프로그램을 작성하기 위해서 큰 블록안에 소블럭을 위치시킬 수 있다.

- VARIABLE, CONSTANT, CURSOR, EXCEPTION을 정의하고, SQL문장과 Procedural 문장에서 사용 한다.

- 단순, 복잡한 데이터 형태의 변수를 선언 한다.

- 테이블의 데이터 구조와 컬럼명에 준하여 동적으로 변수를 선언 할 수 있다.

- EXCEPTION 처리 루틴을 이용하여 Oracle Server Error를 처리 한다.

- 사용자 정의 에러를 선언하고 EXCEPTION 처리 루틴으로 처리 가능 하다.



PL/SQL Block Structure

  PL/SQL은 프로그램을 논리적인 블록으로 나누는 구조화 된 블록 언어 이다.

  PL/SQL 블록은 선언부(선택적), 실행부(필수적), 예외 처리부(선택적)로 구성되어 있고, BEGIN과 END 키워드는 반드시 기술해 주어야 한다.

  PL/SQL 블록에서 사용하는 변수는 블록에 대해 논리적으로 선언할 수 있고 사용할 수 있다.

PL/SQL Block Structure
  • DECLARE
    • - Optional
    • - Variables, cursors, user-defined exceptions
  • BEGIN
    • - Mandatory
    • - SQL Statements
    • - PL/SQL Statements
  • EXCEPTION
    • - Actions to perform when errors occur
  • END
    • - Mandatory

Declarative Section(선언부)
  • - 변수, 상수, CURSOR, USER_DEFINE Exception 선언

Executable Section(실행부)
  • - SQL, 반복분, 조건문 실행
  • - 실행부는 BEGIN으로 시작하고 END로 종료된다.
  • - 실행문은 프로그램 내용이 들어가는 부분으로서 필수적으로 사용되어야 한다.

Exception Handling Section(예외처리)
  • - 예외에 대한 처리.
  • - 일반적으로 오류를 정의하고 처리하는 부분으로 선택 사항이다.

PL/SQL 프로그램의 작성 요령

- PL/SQL 블록내에서는 한 문장이 종료할 때마다 세미콜론(;)을 사용 한다.

- END뒤에 세미콜론(;)을 사용하여 하나의 블록이 끝났다는 것을 명시 한다.

- PL/SQL 블록의 작성은 편집기를 통해 파일로 작성할 수도 있고, SQL 프롬프트에서 바로 작성 할 수도 있다.

- SQL*PLUS 환경에서는 DECLARE나 BEGIN이라는 키워드로 PL/SQL블럭이 시작하는 것을 알 수 있다.

- 단일행 주석 : --

- 여러행 주석 : /* */

- PL/SQL 블록은 행에 / 가 있으면 종결 된다.



PL/SQL 블럭의 유형

PL/SQL Anonymous BlockPL/SQL Procedure BlockPL/SQL Function Block
[ Anonymous ][ Procedure ][ Function ]

Anonymous Block (익명 블록)

  이름이 없는 블록을 의미 하며, 실행하기 위해 프로그램 안에서 선언 되고 실행시에 실행을 위해 PL/SQL 엔진으로 전달 된다.

  선행 컴파일러 프로그램과 SQL*Plus 또는 서버 관리자에서 익명의 블록을 내장 할 수 있다.

Procedure (프로시저)

  특정 작업을 수행할수 있는 이름이 있는 PL/SQL 블록으로서, 매개 변수를 받을수 있고, 반복적으로 사용할수 있다.

  보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL블록을 데이터베이스에 저장하기 위해 생성 한다.

Function (함수)

  보통 값을 계산하고 결과값을 반환하기 위해서 함수를 많이 사용 한다.

  대부분 구성이 프로시저와 유사하지만 IN 파라미터만 사용 할 수 있고, 반드시 반환 될 값의 데이터 타입을 RETURN문에 선언해야 한다.

  또한 PL/SQL블록 내에서 RETURN문을 통해서 반드시 값을 반환 해야 한다.




출처 : http://www.gurubee.net/lecture/1039, http://www.gurubee.net/lecture/1342, http://www.gurubee.net/lecture/1343

저작자표시 비영리

'SQL > PL_SQL' 카테고리의 다른 글

ORA와 SQL 차이  (0) 2017.10.21

기본 문법

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE PROCEDURE 프로시저명
(
  변수명         IN   VARCHAR2,
  RC1    OUT SYS_REFCURSOR 
)
IS
 
BEGIN

/*DO SOMETHING*/ 

OPEN RC1 FOR
SELECT * fROM 테이블


EXCEPTION WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR (-20001, '프로시저명' || SQLERRM);
 
END 프로시저명;
/
cs


요약설명

REF CURSOR

 - 오라클 9i 하위 버전에 지원하던 커서.

   해당 커서를 사용하기 위해서는 패키지를 선언하고, 패지키에서 선언된,

   REF CURSOR 타입의 인자를 출력 파라메터로 받는 프로시저를 작성해야 함.

   프로시저 내부에서 패키지 작성없이 직접 해당 커서 타입을 이용하는 것은 불가.

 

SYS_REFCURSOR

 - 오라클 9i 버전부터 지원하는 커서.

   이전버전에 지원하던 REF CURSOR의 불편함을 개선한 커서이다.

   하위 버전에서 커서를 반환하기 위해 패키지를 생성해야 했으나, 

   해당 커서의 추가로 인해 패키지 작성없이 프로시저에서 바로 커서를 반환할 수 있게 되었다. 

- 커서를 반환함으로써 SELECT 프로시저를 사용할때 결과테이블을 반환 받을 수 있게 된다.



예외처리

EXCEPTION

WHEN 예외 THEN  ###

WHEN OTHERS THEN @@@


RAISE_APPLICATION_ERROR(사용자 정의 예외)

오류코드 -20000 ~ -20999범위내에서 사용자가 정의한 오류를 실행할수 있음


SQLCODE, SQLERRM

SQLCODE는 실행부에서 발생한 예외에 해당하는 코드를 반환한다. 예를 들어, 0으로 나누면 이에 대한 예외 코드인 ‘-1476’을 반환한다. 만약 에러 없이 성공하면 ‘0’을 반환한다.

SQLERRM은 발생한 예외에 대한 오류 메시지를 반환한다. 이 함수는 매개변수로 예외코드 값을 받는데, 매개변수를 넘기지 않으면 디폴트로 SQLCODE가 반환한 예외코드 값과 연관된 예외 메시지를 반환한다. 

SQLERRM(SQLCODE)로 사용하기도한다.


차이점

MS-SQL

Oracle

CREATE PROCEDURE

CREATE PROCEDURE

ALTER PROCEDURE

CREATE OR REPLACE PROCEDURE

변수 NVARCHAR(20)

변수 IN VARCHAR2

SQL 마지막에 ; 없어도됨

SQL 마지막에  ; 부붙이기

'문자열' + '합치기'

'문자열' || '합치기' 

END

END; (프로시저의 마지막은 반드시 ;(세미콜론)

DECLERE V_VAR NVARCHAR(20)

V_VAR VARCHAR2(20)

프로시저 안에서는 명시하지않아도 되지만,

프로시저 밖에서는 익명블록으로 만들어야한다.

(DECLARE 변수

 BEGIN

  변수 := @@@;

 END)

CONVERT(SYSDATE(), 112);

TO_DATE(SYSDATE, 'YYYY.MM.DD')

TO_CHAR(SYSDATE, 'YYYY.MM.DD')

MS-SQL의 CAST, CONVERT가 Oracle에서 TO_DATE, TO_CHAR이라고 생각하면된다.

ISNULL('', 0)

NVL('', 0)

EXEC UP_HR_HUANEXPEN_ACCEPT 

파라미터1, 파라미터2, 파라미터3

UP_HR_HUANEXPEN_ACCEPT(파라미터1, 파라미터2, 파라미터3)

프로시저 내에서 다른 프로시저 실행시, EXEC를 명시하지 않아도 된다.

SELECT @COMPANY = COMPANY

  FROM TBL

SELECT COMPANY

   INTO V_COMPANY

  FROM TBL

변수에 값을 대입할때 위처럼 SELECT문 뒤에 INTO를 사용한다.

@P_CD_COMPANY = '1000'

V_CD_COMPANY := '1000' 

(Oracle에서 변수에 값을 대입하려면 := 를 사용한다.)

'' 과 NULL

 둘다 NULL로 인식

 임시테이블 #temp

오라클에서는 임시테이릅을 만들기 상당히 까다롭다

그래서 일반 테이블을 만들어놓고 임시테이블 처럼 사용하기도 한다. 

 PRINT

 DBMS_OUTPUT.PUT_LINE()


저작자표시 비영리

'SQL > PL_SQL' 카테고리의 다른 글

PL/SQL 이란  (0) 2017.10.21

+ Recent posts

티스토리툴바