PL/SQL
절차적 언어(PL)란?
- 처리방법과 순서가 있는 언어. C, Java 등이 절차적 언어이다.
- PLSQL은 SQL에 내가 필요한 것을 정의하여 동작하도록 하게 함.(SQL + PL기능)
구조화된 언어
- DECLARE과 EXCEPTION은 필수가 아니지만 BEGIN과 END는 반드시 있어야함.
- DECLARE ( 선언부 )
- BEGIN ( 실행부 )
- EXCEPTION ( 예외처리 )
- END;
PL 주요 특징
- 변수, 상수 정의
- 제어구문 사용가능(if문, loop문, go to문)
- 예외처리
- 모듈화
1. 변수선언이 가능하다.
- 대입연산자( := )
- 변수를 정의하고 초기화하지 않으면 해당 변수를 NULL로 취급한다!
DECLARE
V_EMPNO NUMBER(4) :=8888; --변수선언 및 초기화
V_DEPTNO NUMBER(2);
V_ENAME VARCHAR2(10) :='XMAN'; --변수선언 및 초기화
V_JOB VARCHAR2(9);
V_SAL NUMBER(7,2);
BEGIN
- CURSOR : IS이하의 SELECT문장만 올 수 있다.
DECLARE
CURSOR CUR_EXP IS
SELECT EMPNO, JOB, SAL, COMM, FROM EMP WHERE DEPTNO = 10;
V_ENAME VARCHAR2(10) :='XMAN'; --변수선언 및 초기화
V_JOB VARCHAR2(9);
V_SAL NUMBER(7,2);
V_COMM NUMBER(7,2);
BEGIN
2. 제어구문 사용가능(if문, loop문, go to문)
- PLSQL은 :=이 대입연산자, =는 비교연산자이다.
IF V_JOB IS NULL THEN
V_JOB :='신입';
END IF;
IF V_JOB = '신입' THEN
V_SAL := 2000;
ELSIF V_JOB IN ('MANAGER', 'ANALYST') THEN
V_SAL := 3500;
ELSE
V_SAL := 2500;
END IF;
WHILE(V_INDEX >= 0)
LOOP
DBMS_OUTPUT.PUT_LINE(' WHILE LOOP ['||TO_CHAR(V_INDEX)||']');
-- DBMS_OUPUT : 패키지
V_INDEX := V_INDEX -1
END LOOP;
3. 예외처리
<<Nested_BLOCK_1>>
BEGIN
INSERT INTO DEPT VALUES(76,'LOCAL_PART_1', 'Nested_Blk1');
COMMIT;
--에서 Run Time Error발생시 아래에서 예외처리(에러처리) 수행
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
END Nested_BLOCK_1;
4. 모듈화
- 사용자정의 함수, 라이브러리화 하여 재사용
- 블럭안에 블럭을 사용하는 중첩BLOCK 가능( 모듈성을 높일 수 있다. )
- 사용자 정의 프로시져, 함수, 패키지, 트리거 등을 생성할 수 있다.
DECLARE
V_EMPNO NUMBER(4) :=0;
V_ENAME VARCHAR2(10);
V_DEPTNO NUMBER(2);
BEGIN
V_EMPNO :=7778;
V_ENAME :='PL/SQL';
INSERT INTO EMP(EMPNO, DEPTNO, ENAME) VALUES(V_EMPNO, V_DEPTNO, V_ENAME);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('INSERT ERR : '||SQLERRM);
ROLLBACK;
END;
기존 SQL과 차이점
- SQL문장이 움직이는 SQL과는 다르게 PLSQL은 코드블럭단위로 움직인다.
- 클라이언트에서 DBMS서버로 PLSQL블럭을 통째로 전송.
- 블럭이 통째로 날라가는게 왜 좋을까?
- 클라이언트와 서버간의 통신이 라인별로 발생하면 수십번을 왔다갔다 하게 된다.
- 블럭으로 전송하게 되면 한 번에 배달하는 것과 같아서 효율적이다.
PLSQL 장점
- 이식성
- 통합성
- 성능
PLSQL블럭 종류
- ANONYMOUS BLOCK : 이름이 없는 블록
- Client App, SQL Script내에 저장
- ProC
- NAMED BLOCK (= STORED BLOCK ) : 이름이 있는 블록
- DBMS내에 저장.
- Function/Procedure/Package/Trigger
- 빈번한 Context Switch는 성능저하의 원인이 된다.
- Java와 SQL간 Context Switch(문맥교환)보다 PL과 SQL의 문맥교환이 더 효율적.
별도의 디버깅 도구를 지원하지 않음.
REM DEFAULT SIZE 20000 BYTES.
SET SERVEROUTPUT ON
BEGIN
FOR I IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE('[ '||TO_CHAR(I)||' ] PROCESSED');
END;
/
SET SERVEROUTPUT OFF
BEGIN
DBMS_OUTPUT.PUT_LINE('OK...');
END;
/
REM --------------------------------------------------
REM 출력 버퍼의 크기 조절
REM - DEFAULT SIZE 20000 BYTES
REM - MAX SIZE UNLIMITED
REM --------------------------------------------------
SET SERVEROUTPUT ON SIZE 2000
BEGIN
FOR I IN 1..40
LOOP
DBMS_OUTPUT.PUT_LINE('[ '||TO_CHAR(I)||' ] '||'123456789012345890123456789012345678901234567890123456789001234567890');
END LOOP;
END;
/
- 1코드 실행 후, 2코드를 실행하면 아래와 같은 결과를 확인할 수 있음
오류 보고 -
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: "SYS.DBMS_OUTPUT", 32행
ORA-06512: "SYS.DBMS_OUTPUT", 97행
ORA-06512: "SYS.DBMS_OUTPUT", 112행
ORA-06512: 4행
20000. 00000 - "%s"
*Cause: The stored procedure 'raise_application_error'
was called which causes this error to be generated.
*Action: Correct the problem as described in the error message or contact
the application administrator or DBA for more information.
출력버퍼
- PLSQL 수행 후, 화면에 보여줌.
- DBMS_OUTPUT을 사용할 때 버퍼의 길이
- 라인의 출력 길이
-
10G R2 이전 |
10G R2 이후 |
255 Bytes |
32767 Bytes |
- 출력버퍼크기
-
10G R2 이전 |
10G R2 이후 |
1000000 Bytes |
Unlimited |