PL/SQL 정리

디벨로퍼 사용

디벨로퍼를 사용하여 PROCEDURE 생성 (1-16)

디벨로퍼를 사용하여 프로시저 컴파일 및 컴파일 오류 표시 (1-17)

디벨로퍼에서 컴파일 오류 해결 (1-18)

디벨로퍼를 사용하여 프로시저 호출 (1-35)

디벨로퍼를 사용하여 프로시저 제거 (1-41)

디벨로퍼 사용하여 프로시저 정보 보기 (1-43)

디벨로퍼를 사용하여 함수 생성 및 컴파일 (2-11)

디벨로퍼를 사용하여 함수 실행 (2-12)

디벨로퍼를 사용하여 함수 제거 (2-21)

디벨로퍼를 사용하여 프로시저 정보보기 (2-23)

디벨로퍼를 사용하여 패키지 스펙 생성 (3-12)

디벨로퍼를 사용하여 패키지 바디 생성 (3-13)

디벨로퍼를 사용하여 패키지 서브 프로그램 호출 (3-18)

디벨로퍼를 사용하여 패키지 제거(3-20)

1장, PL/SQL소개 ======================================

SET SERVEROUTPUT ON : 메모리에 저장된 값을 출력

DBMS_OUTPUT.PUT_LINE 패키지를 사용하여 출력한다. 그냥 PUT은 커서를 냅두는거고 PUT_LINE은 엔터값을 추가하는것임.

SET VERIFY OFF : SQL명령어나 PL/SQL에서 &를 이용한 치환 변수등을 사용할 때 치환되기 전 후의 자세한 값을 보일 건지의 여부를 결정 한다. 기본값은 ON 이다.

PLSQL구조

DECLARE

BEGIN

EXCEPTION

END;

/ : 디벨로퍼에는 없어도 되는데, SQLPLUS에서는 해야 한다.

문제에서 ~해야 한다. 참인가 거짓인가 했을떄 옵션들 중 딱히 안들어가 되는 게 있었음. 예를 들어서 예외 처리부분은 EXCEPTION키워드로 실행된다. 라는 말에서 EXCEPTION은 없어도 되는데... 아무튼 문제를 잘 읽어라!!!

2장, 실행문 작성 (1) ======================================

*변수 이름 요구사항

- 문자로 시작해야 함

- 문자나 숫자 포함 가능

- 특수문자, 즉 $,_,#만 표시할 수 있다.

- 30자 이하의 문자만 포함해야 한다.

- 예약어를 포함하면 안된다.

*변수 선언 및 초기화

변수명 [CONSTANT] 데이터타입 [NOT NULL] :=(또는 DEFAULT) EXPR

- CONSTANT : 상수라는 뜻. 변하지 않는다. 또한 상수로 선언했으면 값을 꼭 초기화해야 한다.

- NOT NULL : 널이면 안됨. 또한 값을 꼭 초기화해야 함.

- EXPR : 초기화할 값. 임의의 PL/SQL표현식으로, 리터럴 표현식, 다른 변수 또는 연산자와 함수를 포함하는 표현식일 수 있다. 초기화하지 않으면 NULL값이 들어간다.

*변수유형

PLSQL 변수

- 스칼라변수 : 값이 1개인 일반변수.

- 참조 : 저장 위치를 가리키는 포인터라는 값을 보유

- LOB : 대형 객체의 위치를 지정하는 위치자라는 값 보유. 대형객체는 외부저장되는 그래픽이미지등.

- 조합 : 컬렉션, 레코드

비 PLSQL 변수

- 바인드 변수

*PLSQL 변수 선언 지침

*PLSQL 구조의 이름 지정 규칙

*스칼라 데이터 유형 : BOOLEAN, DATE, NUMBER, VARCHAR2

*기본 스칼라 데이터 유형

VARCHAR2, NUMBER, BINARY_INTEGER, PLS_INTEGER, BOOLEAN, BINARY_FLOAT, BINARY_DOUBLE., DATE, TIMESTAMP시리즈, INTERVAL 시리즈 (자세한 설명은 2-17페이지

* %TYPE : 변수명 테이블명.컬럼명%TYPE or 변수명 이미선언된변수명%TYPE 사용. 생성되어있는 컬럼이나 변수의 데이터 타입을 그대로 쓴다.

- 해당 테이블이나 변수가 낫널제약조건이 있어도, %TYPE로 선언한 변수는 낫널 적용받지 않는다.

*부울변수에 대하여

*LOB 데이터 유형 변수

- CLOB : 대형 문자 데이터 블록을 디비에 저장

- BLOB : 구조화되지 않거나 구조화된 대형 바이너리 객체를 디비에 저장. 이러한 데이터를 디비에 삽입/검색하는 경우 디비는 해당 데이터를 해석하지 않음. 이런 데이터는 해당 데이터를 사용하는 외부 응용프로그램이 해석함.

- BFILE : BLOB와 같은데 데이터베이스에 저장되지 않고 데이터베이스 외부에 저장됨. BFILE 파일에 대한 포인터만 디비에 저장된다.

- NCLOB : 대형블록의 단일바이트 또는 고정너비 멀티바이트 NCHR 유니코드 데이터를 디비에 저장

*레코드 및 컬렉션 : 레코드는 관계있는 여러 데이터유형의 조합 / 컬렉션은 동일한 데이터 유형의 조합.

*바인드 변수

- PLSQL 블록의 선언부분이 아닌 호스트환경에서 생성됨. 즉 완전히 블록 밖에서 선언해야 한다. DECLARE안이 아니다.

- 블록이 실행된 후에도 엑세스할 수 있다. (보통 블록이 실행된 후에는 변수를 못씀)

- 환경변수지만 글로벌변수는 아님.

- 변수는 호스트에 있지만 값은 서버프로세스의 PGA라는 메모리에 있음.

- VARIABLE 변수명 유형(NUMBER, VARCHAR2등, 숫자만 사이즈없이)

- 바인드변수가 참조될떄는 :변수명을 쓴다.

PRINT 명령

- PRINT 명령으로 변수의 내용을 알 수 있다. PRINT 변수명 (바인드변수명 등)

- SELECT 명의 INTO 절에 들어가는 변수를 쓰면 된다. 그럼 결과값을 알 수 있지.

- 성공적인 PLSQL블록에서 사용되는 바인드변수를 자동으로 출력하려면 SET AUTOPRINT ON을 미리 입력해둔다.

예시

VARIABLE b_result NUMBER

BEGIN

SELECT (SALARY*12)+NVL(COMMISSION_PCT,0) INTO :b_result FROM EMPLOYEES WHERE employee_id=144

END;

PRINT b_result

3장, 실행문 작성 (2) ======================================

PL/SQL 블록의 렉시칼 단위.

- 식별자 : 변수명, 컬럼명, 테이블명 등

- 구분자 : ;, +,- (3-5에 더 자세히 나와있음)

- 리터럴 : JOHN, 428, TRUE등 식별자가 아닌 모든 값은 리터럴이다.

- 주석 : --, /* */

*따옴표로 묶어서 만드는 식별자

- 대소문자구분, 공백포함, 예약어 사용 가능하다. “BEGIN date” DATE; 이런식으로..

숫자를 표현할 과학적 표기법을 쓸 수 있다. 2E5는 2*10의5승.

PL/SQL에서 함수 사용 : 일반적으로 단일 행 함수를 사용할 수 있으며 DECODE와 그룹함수는 프로시저문에서는 사용불가하고 프로시저안의 쿼리문에선 가능.

PL/SQL에서 시퀀스 사용 : 11G 이전 이후로 나뉜다. 잘 보고 써라. 11G 이후방식 v_new_id := my_seq.NEXTVAL; 이런식으로 사용.

데이터 유형 변환: 암시적, 명시적 있고 명시적은 알아서 잘 써라.

- 수식에서의 변환에서는 문자는 숫자형으로 자동으로 바뀐다. salary+’1100’ 은 문자가 숫자로 자동으로 바뀐다.

-할당에서도 변환됨. length(1000) 문자열 길이 재는 함수인데 숫자가 있다. 이럴때는 ‘1000’으로 바뀐다.

- 날짜 유형변환은 형식이 맞는 값이어야 날짜로 바뀐다. 형식 틀리면 에러. to_date로 형식까지 변환하여 할 수는 있다.

%예외 | 파이프라인 연산. 파이프라인연산으로 숫자가 문자로 바뀌고 like연산 앞에 변수는 문자로 바뀜

중첩 블록

- 블록 안에 블록 가능.

- PL/SQL은 맨 위부터 쭉 내려가면서 실행하는 형식이므로, 변수의 순서가 중요하다.

- 내부 블록은 외부 블록의 변수를 쓸 수 있지만, 외부 블록은 내부 블록의 변수를 쓸 수 없음.

- 변수 범위 및 가시성 : 내부 외부 변수 이름이 동일할 순 있지만 변수를 따라갈 때 문제가 생길 수 있음.

- 중첩된 블록에서 수식자 사용 : BEGIN <수식자> END 수식자 를 사용하여, 변수 이름을 참조할 때 수식자.변수명으로 참조하면 자유롭게 참조할 수 있다.

- 모든 블록 변수들은 내부에서 외부로 진행되면서 변한 사항들이 계속 저장된다. 변경된다는 뜻.

PL/SQL의 연산자 우선순위 (3-23페이지)

AND와 OR의 연산결과들 (3-24페이지)

AND	TRUE	FALSE	NULL	OR	TRUE	FALSE	NULL	NOT		FALSE는 AND조건에서 우선하고 TRUE는 OR조건에서 우선한다. AND는 피연산자가 둘 다 TRUE 인 경우 TRUE를 반환한다. OR은 피연산자가 둘 다FALSE인 경우에만 FALSE를 반환한다. 논리 연산자 NOT이나 =!등을 널에 적용하면 무조건 결과는 널 널 = 널도 널임. 참 아님.
TRUE	TRUE	FALSE	NULL	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE
FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	NULL	FLSE	TRUE
NULL	NULL	FALSE	NULL	NULL	TRUE	NULL	NULL	NULL	NULL

프로그래밍할 때 주석, 대소문자규칙, 식별자 통일, 들여쓰기.

4장, PL/SQL 프로그램에서 오라클 데이터베이스 서버 SQL문과 상호작용 =============

END키는 트랜잭션의 끝이 아닌 블록의 끝이다.

DDL과 DCL은 PL/SQL에서 직접 실행 불가능. 다이나믹 PL/SQL에서 가능

SQL이 포함된 블록을 컴파일할 때, 먼저 PL/SQL구문을 컴파일하며, 컴파일된 문장을 수행할 때 그 후 SQL을 실행한다. 익명블록은 동시 수행한다.

PLSQL의 셀렉트문 : 하나의 행만 반환해야 함. 여러개의 열로된 하나의 행이 될 수 있다. 즉 여러개 컬럼, 여러개 변수 삽입 가능.

미리 변수 선언 후

SELECT 컬럼 INTO(필수) 선언된변수 (검색한 모든 값이 변수에 들어가야만 한다. 즉 SELECGT절 열 개수와 동일해야한다. 또한 위치적으로 일치하고 데이터유형도 맞아야 함)

FROM 테이블 WHERE 조건 (INTO절을 쓰는 경우, 꼭 1개의 값만 페치해야한다) (WHERE절에는 입력변수, 상수, 리터럴 및 PL/SQL표현식을 지정한다)

DBMS_OUTPUT.PUT_LINE(변수) 이런식으로 출력

PL/SQL에서 결과를 하나만 반환하는 그룹함수 사용가능.

DECLARE

v_sum NUMBER(10,2)

BEGIN

SELECT SUM(SALARY)

INTO v_sum FROM employees

WHERE department_id = 10

DBMS_OUTPUT.PU_LINE (출력할값)

END;

*변수 이름 모호성

변수이름지정은 함수나 키워드를 쓰면 안된다. 쓸 수 있지만 헷갈림. SELECT, INTO절은 식별자가 정해져 있어서 괜찮지만, WHERE절에서만 혼동 발생.

모호할 가능성이 있는 WHERE문에서 데이터베이스 열의 이름은 로컬변수의 이름보다 우선한다. 근데 둘다 같은 이름이면 둘다 열로 본다. 그래서 항상 참인 조건 에러가 난다.

디비테이블 열 이름 > 로컬변수 이름 > 디비 테이블 이름

*PL/SQL을 사용하여 데이터 조작

PL/SQL의 INSERT문 : 동일

PL/SQL의 UPDATE문 : 동일

PL/SQL의 DELETE문 : 동일

PL/SQL의 MERGE문 : 동일

- MERGE는 결정적 명령문이라 동일한 MERGE문에서 대상 테이블의 동일한 행을 여러번 갱신할 수 없다.

- 대상 테이블에 대한 INSERT, UPDATE 객체 권한과 소스테이블에 대한 SELECT 객체 권한을 가져야 한다

SQL의 커서 소개

- 커서란, 오라클 서버에서 할당한 PGA안의 컨텍스트 영역(구문저장및분석)에 대한 포인터. 커서는 SELECT 문의 결과 집합을 처리하는데 사용.

암시적 커서에 대한 SQL속성 (암시적 커서는 SQL문을 실행하면 자동으로 생긴다)

- SQL%FOUND 가장 최근 SQL문이 하나 이상의 행에 영향을 준 경우 TRUE 평가되는 부울 속성

- SQL%NOTFOUND : 가장 최근 SQL문이 한 행에도 영향을 주지 않은 경우 TRUE 로 평가된 부울 속성

- SQL%ROWCOUNT : 가장 최근의 SQL 문에 의해 영향을 받은 행 수를 나타내는 정수 값.

암시적 커서 사용

v_rows_deleted := (SQL%ROWCOUNT || ‘row deleted.’); 위에서 sql을 쓰면 암시적 커서가 오픈되고 여기서 사용된다.

DML문이 기본 테이블에 행에 영향을 주지 않으면 PL/SQL은 오류를 반환하지 않으나 SELECT 문이 아무 행도 검색하지않으면 PL/SQL은 예외를 반환한다.

5장, 제어 구조 작성 =============

IF문

- ELSIF절의 수는 제한이 없다.

- 맨 위 조건이 TRUE이면 할일1일 수행하고 IF문을 종료한다. 만약 FALSE라면 바로 다음 아래의 ELSIF의 조건은 판단하여 참이면 할일2를 수행하고 IF문을 종료한다. 또 FALSE라면 또 바로 다음 아래의 ELSIF의 조건을 판단하여 참이면 할일3을 수행하고 IF문을 종료한다. 또 FALSE라면... 반복. 만약 모든 ELSIF문이 FALSE가 되었다면, ELSE 밑의 할일4를 실행하고 IF문을 종료한다.

- 조건문에는 AND, OR, NOT 등의 논리 연산자를 사용하여 조건식이 여러 개 있을 수 있다. 조건식 수에는 제한이 없다. 그러나 이러한 명령문은 해당 논리 연산자와 관련되어 있어야 한다. (myage<10 이런식도 있고, myfirstname='chris' AND v_myage<11 이런식으로도 가능)

- 만약 IF문의 결과가 TRUE나 FALSE가 아닌 NULL을 반환한다면 이 경우 제어가 바로 ELSE로 이동한다.

- 위에 명시한 '할일'부분에는 하나 이상의 PL/SQL또는 SQL문이 올 수 있다. 이 명령문에는 여러 중첩 제어구조문이 들어갈 수도 있다.

- IF문이나 CASE, FOR 모든 구문 자체를 하나의 블럭이라고 본다.

- ELSE문은 한 개만 있어야 한다.

- 간단한 IF문 : 조건이 TRUE 이면 할일을 수행하며, 조건이 FALSE면 바로 END IF로 이동하고 IF문을 종료한다.D

DECLARE

v_myage NUBMER := 31;

v_friendage NUMBER := 10;

BGEIN

END;

- IF THEN ELSE문 : 조건이 TRUE이면 할일1을 수행하며, 조건이 FALSE면 할일2로 이동하고 IF문을 종료한다.

DECLARE

v_myage NUMBER := 31;

BEGIN

IF v_myage <11

THEN

DBMS_OUTPUT.PUT_LINE(‘ I AM A CHILD ’);

ELSE

DBMS_OUTPUT.PUT_LINE(‘ I AM NOT A CHILD ’);

END IF;

END;

- IF ELSIF ELSE문 : 위부터 조건을 검색해서 아니면 아래로 아니면 아래로 아니면 아래로 최종으로 아니면 ELSE로 이동하고 IF문 종료

DECLARE

v_myage number := 31;

BEGIN

IF v_myage < 11 THEN

DBMS_OUTPUT.PUT_LINE(‘ I AM A CHILD ’);

ELSIF v_myage < 20 THEN

DBMS_OUTPUT.PUT_LINE(‘ I AM YOUNG ’);

ELSIF v_myage < 30 THEN

DBMS_OUTPUT.PUT_LINE(‘ I AM IN MY TWENTIES ’);

ELSE (선택)

DBMS_OUTPUT.PUT_LINE(‘ WHAT AM I? KK ’);

END IF;

END;

CASE문

- 여러개의 대안을 주고 그 중 하나를 선택하는 식이다. IF ELSIF THEN ELSE 문이랑 비슷하다.

- CASE표현식은 조건을 평가하여 값을 반환한다. 얼마든지 대입 가능. 변수 등등 어디든. (END; 로 끝났다)

- CASE문은 조건을 평가하여 작업을 수행한다. CASE문은 완전한 PL/SQL블록이 될 수 있다. (END CASE; 로 끝난다)

- IF문은 조건이 모두 FALSE고 ELSE절이 필수가 아닌경우 아무것도 수행할 수 없지만 CASE문은 조건결과가 뭐든 PL/SQL문을 실행해야 한다.

- 일반 CASE표현식 :　선택 = 선택결과 인 형식. 항상 동등비교를 한다.

SET VERIFY OFF

DECLARE

v_grade CHAR(1) := UPPER(‘&grade’);

v_appraisal VARCHAR2(20);

BEGIN

v_appraisal := CASE v_grade

WHEN ‘A’ THEN ‘EXCELLENT’ WHEN ‘B’ THEN ‘VERY GOOD’ WHEN ‘C’ THEN ‘GOOD’

ELSE ‘NO SUCH GRADE’

END;

DBMS_OUTPUT.PUT_LINE (‘GRADE : ’ || v_grade || ‘ Appraisal ’ || v_apprisal);

END;

- 검색된 CASE표현식 :　조건문이 여러개라서 항상 조건이 다름.

DECLARE

v_grade CHAR(1) := UPPER(‘&grade’);

v_appraisal VARCHAR2(20);

BEGIN

v_appraisal := CASE

WHEN v_grade = ‘A’ THEN ‘EXCELLENT’

WHEN v_grade IN (‘B’,‘C’) THEN ‘GOOD’

ELSE ‘NO SUCH GRADE’

END;

DBMS_OUTPUT.PUT_LINE (‘GRADE : ’ || v_grade || ‘ Appraisal ’ || v_apprisal);

END;

- 향상된 CASE문

- CASE표현식은 조건을 평가하여 값을 반환하는 반면, CASE문은 조건을 평가하여 작업을 수행한다. (CASE문 안에 SQL식이 있는 것인 듯)

- CASE문은 완전한 PL/SQL블록이 될 수 있다.

- CASE문은 END CASE;로 끝나는데 CASE표현식은 END로 끝난다.

예시는 5-15

루프문

- 기본루프 : 적어도 루프 내용이 한번 실행되어야 하는 경우 기본루프 사용 (5-22)

- WHILE루프 : 매번 반복할떄마다 조건이 평가되어야 하는 경우 (5-24)

- FOR루프 : 반복 횟수를 알 수 있는 경우 , 루프범위의 상한과 하한은 리터럴,변수 또는 표현식이 될 수 있으나 정수로 평가되어야 한다. 상한 하한은 루프범위에 포함.

무조건 1씩 증가지만, I:i+1 이런 조건 넣을 수 있다. counter는 원래 있는 자동변수라 미리 선언 안함. (5-26~29)

중첩 루프: EXIT문, 레이블을통해 내부 루프에서 바로 완전히 밖으로 나갈 수도 있다. (5-31)

PL/SQL CONTINUE문

CONTINUE의 조건식을 만족하면 내려가고, 불만족하면 루프를 종료한다.

6장, 조합 데이터 유형 작업 ==================================

명시적 커서를 쓰는 이유는, INTO절이 포함된 셀렉트문은 한번에 하나의 행만 검색할 수 있기 때문이다. 그래서 다중 행을 검색하고 데이터를 조작해야 할 경우 명시적 커서를 사용한다.

*조합 데이터 유형 소개

- 조함 데이터 유형을 사용하는 이유는, 모든 관련 데이터를 단일 단위로 보유하게 되어 데이터를 쉽게 엑세스, 수정,관리,연관,전송 가능.

1. PL/SQL DATATYPE

- 스칼라 데이터 타입 : SQL TYPE+부울

- COMPOSITE (조합 데이터) : User Defined Data Type만들 때 사용, 스칼라 데이터 유형이나 조합 데이터 유형의 다중 값을 보유할 수 있음.

- PL/SQL RECORD (식판. 타입들의 조합)

- 관련있지만 유사하지 않은 데이터를 논리적 단위로 처리하는 레코드. 레코드를 변수로 선언하여 집합적 논리 단위를 만들수있다.

- 데이터 처리를 효과적으로 작성할수있다. 밥그릇 반찬그릇보다는 식판이 편한 것처럼.

- 속이 다르지만 겉은 같음. 한 레코드 안에 다양한 유형의 변수가 존재할수 있다.

- PL/SQL COLLECTION (같은 데이터를 여러개 묶어서 배열처럼 사용. 방 모양이 같으므로 방이름을 붙여줘야 한다. (SUBSCRIPT OR INDEX), 데이터를 단일 단위로 처리하는데 사용된다. 아래 3가지 유형 있음.

- ASSOSITIVE ARRAY (INDEX BY TABLE)

- NESTED TABLE

- VARRAY

- REFERENCE (REF, REF커서 등)

- LOB

2. Non PLSQL DATA TYPE

- BIND 변수

- SUBSTITUTION (치환변수)

%참고

SQL은 VARCHAR2가 4000바이트이고 PLSQL에서는 VARCHAR2가 32000바이트정도된다(확실치않음)

PLSQL에서 VARCHAR2로 선언해서 4000이 넘는 데이터를 입력하고 그 선언한 값을 테이블의 VARCHAR2열에 인풋하면

에러가 나야되는데 에러가 안나고 오버한 데이터는 다 짤린다.

* PL/SQL 레코드

- 스칼라, 레코드(중첩 레코드), 연관배열 유형의 필드를 하나 이상 포함해야 함.

- 유저 정의 데이터 유형이다. 어떤 테이블의 일부 테이블이 될 수 있다..

- 필드 모음을 논리적 단위로 처리

- 테이블에서 데이터 행을 FETCH하여 처리하는 데편리하다.

- 정의된 각 레코드는 필요한 만큼의 필드 가질 수 있음

- 초기값 할당 가능. 초기값이 없으면 NULL로 초기화된다. DEFAULT, :=로 초기화 된다.

- NOT NULL 정의 가능

- 블록, 서브 프로그램 또는 패키지의 선언 부분에 레코드 유형을 정의하고 유저 정의 레코드를 선언한다.

- 중첩 레코드를 선언 및 참조가능. 이 경우 한 레코드는 다른 레코드의 구성요소가 된다.

- 블록 또는 서브 프로그램에서 유저 정의 레코드는 블록 또는 서브프로그램을 입력할 떄 인스턴스화된다. 블록 또는 서브프로그랩을 종료하면 해당 레코드도 더이상 존재하지 않게 된다.

1. 생성하기 (선언 부분에서 둘 다 한다)

#먼저 type 명령으로 정의한다.

TYPE 이름 IS RECORD (변수1 타입 not null default(:=) 값, 변수2 타입 := 값, .......);

TYPE emp_rec_type IS RECORD (first_name employees.first_name%TYPE, last_name employees.last_name%type....);

- type 종류

- 필드타입

- variable%type

- 테이블.컬럼%type

- 테이블%rowtype

- ref cursor를 제외한 모든 pl/sql데이터 유형을 사용 가능.

- not null쓰면 무조건 필드 처음에 초기화해야함.

#정의한 emp_rec_type를 변수로 선언한다.

변수명 이름

rec_emp EMP_REC_TYPE;

2. 레코드 구조

레코드를 생성하면, '레코드명.필드명'으로 검색하고 할당할 수 있다.

3. %ROWTYPE (6-14 예제)

- %TYPE와 똑같다.

- 데이터베이스 테이블 또는 뷰의 열 모음에 따라 변수 선언한다.

- %ROWTYPE 앞엔 데이터베이스 테이블 또는 뷰 이름이 접두어로 붇는다.

- 해당 테이블 및 뷰에서 그 테이블에서 구성되는 모든 컬럼이름 및 데이터유형을 가져오게 된다.

- %TYPE는 스칼라명을 접두어로 쓸 수 있지만, %ROWTYPE는 테이블,뷰명만 가능하다.

- 셀렉트 또는 fetch를 사용하여 레코드에 일반적인 값 리스트를 할당할 수 있다. 열 이름이 레코드의 필드와 동일한 순서인지 확인해라.

- 두 레코드의 해당 데이터 유형이 동일하면 한 레코드를 다른 레코드로 할당할 수도 있다.

- 기본 데이터베이스 열의 개수와 데이터 유형을 알 필요 없고 실제로 런타임에 변경도 가능

- SELECT * 문, 행 레벨의 INSERT및 UPDATE문에서 유용하다.

- 개별 필드를 참조하고 사용하기

DECLARE

emp_record employees%ROWTYPE; - 선언

emp_record.commission_pct := .35; - 상세 컬럼을 참조하고 할당 가능.

예제

DECLARE

TYPE t_rec IS RECORD

(

v_sal NUMBER(8),

v_minsal NUMBER(8) DEFAULT 1000,

v_hire_date employees.hire_date%TYPE,

v_rec1 employees%ROWTYPE -- 중첩 레코드

);

v_myrec T_REC

BEGIN

v_myrec.v_sal := v_myrec.v_minsal+500;

v_myrec.v_hire_date := SYSDATE;

SELECT * INTO v_myrec.v_rec1

FROM employees

WHERE employee_id=100;

DBMS_OUTPUT.PUT_LINE(v_myrec.v_rec1.last_name || ' '|| to_char(v_myrec.v_hire_date) || ' ' || to_char(v_myrec.v_sal));

END;

인서트 예시

DECLARE

v_employee_number NUMBER:=124;

v_emp_rec employees%ROWTYPE;

BEGIN

SELECT * INTO v_emp_rec FROM employees

WHERE employee_id=v_employee_number;

INSERT INTO retired_emps(empno, ename, job, mgr, hiredate, leavedate, sal, comm, deptno)

VALUES (v_emp_rec.employee_id, v_emp_rec.last_name, v_emp_rec.job_id, v_emp_rec.manager_id, v_emp_rec.hire_Date, SYSDATE, v_emp_rec.salary, v_emp_rec.commission_pct, v_emp_rec.department_id); -서로 다르므로 일일이 맞춤.

ROWTYPE을 사용하여 레코드 삽입 (6-15)

레코드를 사용하여 테이블의 행 갱신

set verify off

DECLARE

v_employee_number NUMBER := 124;

v_emp_rec retired_emps%ROWTYPE;

BEGIN

SELECT * INTO v_emp_rec FROM retired_emps;

v_emp_rec.leavedate:=CURRENT_dATE;

UPDATE retired_emps SET ROW = v_emp_rec

WHERE empno=v_emploee_number;

END;

SELECT * FROM retired_emps;

- SET ROW 대신 SET 특정컬럼도 가능함. SET leave_date = CURRENT_DATE

- INTO 부분에는 데이터 한건만 작용할 수 있다. (한건만 할 수 있으므로 레코드나 컬렉션을 쓰겠지)

스칼라방식, 레코드방식, %ROWTYPE방식, %ROWTYPE in view 방식 4가지 비교.

SET SERVEROUTPUT ON

SET verify OFF

create or replace view v_emp

as select employee_id, first_name, last_name, salary

from employees;

DECLARE

-- 스칼라방식

v_employee_id employees.employee_id%TYPE := &empid;

v_last_name employees.last_name%type;

v_first_name employees.first_name%type;

v_salary employees.salary%type;

-- 레코드방식

Type emp_rec_type is record (first_name employees.first_name%TYPE, last_name employees.last_name%type, salary employees.salary%type);

rec_emp EMP_REC_TYPE;

-- using %rowtype

emp_record employees%ROWTYPE;

-- using view, %rowtype

view_emp_record v_emp%ROWTYPE;

BEGIN

-- 스칼라방식

SELECT first_name,

last_name,

salary

INTO v_first_name,

v_last_name,

v_salary

FROM employees

WHERE employee_id = v_employee_id;

DBMS_OUTPUT.PUT_LINE('employee id : ' || v_employee_id);

dbms_output.put_line('Employee name : '|| v_first_name);

dbms_output.put_line('salary : '|| v_salary);

-- 레코드방식

select first_name, last_name, salary

into rec_emp

from employees

where employee_id = v_employee_id;

DBMS_OUTPUT.PUT_LINE('employee id : ' || v_employee_id);

dbms_output.put_line('Employee name : '|| v_first_name);

dbms_output.put_line('salary : '|| v_salary);

-- using %rowtype

select * -- field is many, if you didn't write all of them, occor errors.

into emp_record -- datatype is same, datatype order is same.

from employees

where employee_id = v_employee_id;

DBMS_OUTPUT.PUT_LINE('employee id : ' || v_employee_id);

dbms_output.put_line('Employee name : '|| emp_record.first_name); -- field name is same too.

dbms_output.put_line('salary : '|| emp_record.salary);

-- using %rowtype

select * -- field is many, if you didn't write all of them, occor errors.

into view_emp_record-- datatype is same, datatype order is same.

from v_emp -- is view.

where employee_id = v_employee_id;

DBMS_OUTPUT.PUT_LINE('employee id : ' || v_employee_id);

dbms_output.put_line('Employee name : '|| view_emp_record.first_name); -- field name is same too. at view.

dbms_output.put_line('salary : '|| view_emp_record.salary);

END;

%rowtype의 단점은 *을 써야 해서 쓸데없는 데이터 다 가져와야 함. 그대신 간편하지 뭐.... 그래서 뷰를 만들어서 쓰면 된당. 뷰의 %rowtype하면 되니깐..

%rowtype은 테이블명 뷰명, 커서명, 커서변수는 들어갈 수 있지만, 그외에 선언된 변수 이름같은건 안됨 (%type은 되는데... %type를 ㅇ참고해서 다시 이해 필요)

2. PL/SQL 컬렉션 사용.

* 연관 배열 (INDEX BY TABLE)

- 2개의 열을 포함하는 PL/SQL컬랙션이다.

- UNIQUE KEY 열

- 정수 또는 문자열 데이터 유형의 기본키 (양수 음수, 문자열 가능)

- 보통 BINARY_INGETER나 PLS_INTEGER 형식을 쓴다. 속도가 빠르고 필요저장영역이 작으므로.

- VALUE 열

- 스칼라 또는 레코드 데이터 유형의 값

- 이 값은 필드가 아니라 걍 밸류임. 즉 디비에 저장되는게 아니라 메모리에만 저장되는것.

- 대응되는 SQL타입이 없어서 컬럼 타입으로 만들수가 없다.

- 선언부에서 초기화가 안되고 키 또는 값을 포함하지 않고 유저가 선언 시 연관배열을 초기화할 수도 없다.

- 실행부에서 초기화해야함. 즉 책채우려면 명시적 실행문이 필요하다.

- 유니크키열은 순차적이 아닐수있지만 셀때는 순차적으로 센다. 또한 순차적일 필요도 없다.

- 연관 배열은 크기에 제약이 없다. 즉, 행 수를 동적으로 늘릴 수 있다.

- 임시 데이터를 저장하기 위한 것이므로 INSERT 및 SELECT INTO와 같은 SQL문에서는 사용 불가능.

*연관 배열 생성

TYPE 선언배열타입이름 IS TABLE OF

{ 컬럼명 | 변수%TYPE | 테이블.컬럼%TYPE [NOT NULL]} | TABLE%ROWTYPE

INDEX BY PLS_INTEGER | BINARY_INTEGER | VARCHAR2(크기);

식별자명 선언배열타입이름 (선언)

* 연관배열 선언과 엑세스

create table empl( ename VARCHAR2(25 BYTE),hire date);

set serveroutput on

DECLARE

TYPE ename_table_type IS TABLE OF

employees.last_name%TYPE

INDEX BY PLS_INTEGER;

TYPE hiredate_table_type IS TABLE OF

DATE

INDEX BY PLS_INTEGER;

ename_table ename_table_type;

hiredate_table hiredate_table_type;

BEGIN

ename_table(1) := 'CAMERON';

hiredate_table(8) := SYSDATE + 7;

ename_table(4) := 'KING';

IF ename_table.EXISTS(1) THEN

insert into empl VALUES (ename_table(1), hiredate_table(8));

END IF;

dbms_output.put_line(ename_table.count);

dbms_output.put_line(hiredate_table.count);

END;

select * from empl;

선언한 컬렉션변수(숫자) 함으로써 참조하거나 삽입가능

*INDEX BY 테이블 메소드

연관 배열에서 작동하는 내장 프로시저 또는 함수이며 점 표기법을 통해 호출된다.

테이블명.메소드명(파라미터)

EXIST(N) : 연관 배열에 N번째 요소가 존재하면 TRUE를 반환

COUNT : 연관 배열이 현재 포함하는 요소의 수를 반환한다.

FIRST : 연관 배열의 첫번째(최소) 인덱스 번호(키값)을 반환 , 연관 배열이 비어있다면 널 반환

LAST : 연관 배열의 마지막(최대) 인덱스 번호(키값)을 반환, 연관 배열이 비어있다면 널 반환

PRIOR(N) : 연관 배열에서 인덱스 N 앞에 오는 인덱스 번호를 반환

NEXT(N) : 연관 배열에서 인덱덱스 N앞에 오는 인덱스 번호를 반환

DELETE, DELETE(N), DELETE(N,M) : 걍 딜리트 쓰면 연관배열 모든 요소 제거, DELETE(N)은 N번의 요소를 제거, DELETE(N,M)은 N부터 M까지 제거

EXTEND : 늘리는거..?

set serveroutput on

DECLARE

TYPE ename_table_type IS TABLE OF

employees.last_name%TYPE

INDEX BY PLS_INTEGER;

TYPE hiredate_table_type IS TABLE OF

DATE

INDEX BY PLS_INTEGER;

ename_table ename_table_type;

hiredate_table hiredate_table_type;

v_key PLS_INTEGER;

BEGIN

ename_table(1) := 'CAMERON';

ename_table(4) := 'KING';

ename_table(-2) := 'KWON';

hiredate_table(8) := SYSDATE + 7;

dbms_output.put_line(ename_table.count);

dbms_output.put_line(hiredate_table.count);

dbms_output.put_line('ename_table.first: '||ename_table.first);

dbms_output.put_line('ename_table.last: '||ename_table.last);

v_key := ename_table.first;

WHILE (v_key IS NOT NULL) LOOP

dbms_output.put_line('ename_table('||v_key||') => '

|| ename_table(v_key));

v_key := ename_table.next(v_key);

END LOOP;

FOR i IN -2..4 LOOP

dbms_output.put_line('ename_table('||i||') => ' || ename_table(i));

END LOOP;

END;

레코드를 연관배열로 만들고 참조하고 추출하기 (6-23~24)

3.중첩 테이블

- 연관 배열과 비슷한데 그냥 스칼라값이나 레코드 대신, 테이블을 넣는 것이다.

- 스키마 레벨 테이블(유저가 만든 일반 ㅌ ㅔ이블)에 적합한 데이터 유형이다. 연관배열과는 달리 중첩테이블은 데이터베이스에 저장된다.

- 동적 테이블이라 계속 증가 가능. 2GB까지.

- 연관배열과 달리 키는 음수값 안되며 키는 없지만 첫번째 열을 키로써 쓴다. 그냥 숫자가 있는 열.

- 중첩 테이블 어디서든 요소를 삭제하고 순차적이 아닌 키가 있는 희소 테이블을 남겨둘 수 있다.

- 중첩 테이블의 행은 특정 순서로 되어있지 않다.

- 중첩 테이블에서 값을 겁색할 떄 행에는 1부터 시작하는 연속적인 하위 스크립트가 지정된다.

- 초기화하지 않으면 자동으로 널로 초기화

- 값 집합을 보유하는 것. 달리 말하면 테이블 내에 테이블이 있음.

- PL/SQL과 데이터베이스 모두에서 사용가능하다. 1차원 배열처럼.

*선언

TYPE 테이블명 IS TABLE OF

{ 컬럼명 | 변수%TYPE | 테이블.컬럼%TYPE [NOT NULL]} | TABLE%ROWTYPE

인덱스 바이는 없다.

*사용

DECLARE

TYPE location_type IS TABLE OF locations.city%TYPE;

offices LOCATION_TYPE;

table_count NUMBER;

BEGIN

offices := LOCATION_TYPE('BOMBAY', 'TOKYO', X, X) (초기화하면 자동으로 순번이 1,2,3,...들어감)

for i in 1... offices.count() LOOP

DBMS_OUTPUT.PUT_LIKE(offices(i));

END LOOP;

END;

여기서 중간 내용을 지우면 FOR 돌리면 안됨. 불연속적이므로 FOR가 에러남. NEXT를 사용하여 돌려야 한다.

4. VARRAY

- 고정길이를 갖고 있어 선언 시배열의 전체 크기를 명시해야 한다. 최대 크기는 2GB

- VARRAY의 요소는 VARRAY의 크기가 4KB를 초과하지 않는 한 테이블 데이터에 인라인으로 저장된다.

- 테이블 내에 저장될 수 있다. 테이블의 하나의컬럼 타입으로 VARRAY가 사용ㄱ ㅏ능하다.

- 스키마 레벨 테이블에 적합하다.

TYPE location_type IS VARRAY(3) OF locations.city%TYPE;

offices location_type;

7장, 명시적 커서 사용 ==================================

* 명시적 커서

- 명시적 커서를 사용하여 전용 sql 영역을 명명하고, 해당 영역에 저장된 정보에 엑세스 가능.

- 여러 행을 반환하는 쿼리에 대해 프로그래머가 선언하고 관리하고 블록 실행 작업에서 특정 명령문을 통해 조작됨.

- 다시 한번 말하지만, 다중 행을 반환하는 select문이 있을 떄 pl/sql에서 명시적 커서를 선언한다.

- 명시적 커서는 select문에서 반환된 각 행을 처리할 수 있다. 많은 데이터를 한 행씩 처리하기 위해서 사용

- 다중 행 쿼리에서 반환된 행 집합을 결과행 집합이라고 하며, 이것은 해당 검색 조건을 만족하는 행의 수이다.

- 유저가 블록에서 명시적 커서를 수동으로 제어 가능 예를들어 현재 처리 중인 행을 추적 가능.

* 명시적 커서 제어

선언 -오픈 - 페치 (1건씩 읽음) - 클로즈

* 명시적 커서 선언

구문

CURSOR 커서이름 IS 셀렉트문(액티브셋)

- PL/SQL문에서는 반드시 SELECT문에서 INTO를 가져야 하지만, 커서에서는 가질 수 없다. 이것은 커서가 선언 부분에 한정되어있고 커서로 행을 읽지 않기 떄문. (INTO 대신 FETCH로 대신한다)

- 특정 시퀀스로 행을 처리하려면 SELECT문에 ORDER BY절을 사용한다.

- 커서는 조인, 서브쿼리등을 포함하는 유효한 셀렉트문일 수 있다.

- 커서 선언 중에 변수가 사용되면 이거는 커서를 선언 중일 떄 표시되어야 하는 바인드 변수로 간주된다. 이 변수는 커서를 열 떄 한번만 검사된다.

- 셀렉트문의 결과가 하나라도, 명시적 커서는 사용가능하다.

예제

V_locid NUMBER := 1700;

CURSOR c_Dept_cursor IS

SELECT * FROM departments WHERE location_id = v_locid;

1. 이름지정, 연관된 쿼리 구조 정의 (PLSQL 선언부분에서 커서 선언)

* 명시적 커서 열기

DECLARE

CURSOR c_emp_cursor IS

SELECT employee_id, last_name FROM employees WHERE department_id = 30;

BEGIN

OPEN c_emp_ cursor;

1. 커서를 연다.

2. 컨텍스트 영역에 메모리를 동적으로 할당.

3. SELECT문 구문분석

4. 입력변수 바인드 (입력변수의 메모리 주소를 확인하여 입력 변수 값 설정)

5. 쿼리문 실행 후 결과행 집합 식별

6. 포인터로 액티브셋의 첫번째 로우를 가리킨다

%참고! 커서가 열려 있을 때, SELECT문이 값을 한건도 반한하지 않아도 예외가 발생하지 않는다. 커서명%ROWCOUNT속성으로 명시적 커서와 함께 반환된 행 수를 확인할 수 있따.

* 명시적 커서 데이터 페치

DECLARE

CORSOR c_emp_cursor IS

SELECT employee_id, last_name FROM employees WHERE department_id = 30;

v_empno employees.employee_id%TYPE;

v_lname employees.last_name%TYPE;

BEGIN

OPEN c_emp_cursor;

LOOP

FETCH c_emp_cursor INTO v_empno, v_lname;

EXIT WHEN c_emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);

END LOOP;

END;

1. 첫번째 로우의 데이터를 획득

2. 다음 행으로 포인터 이동

다음 행이 있다면 다시 페치로 간다

다음 행이 없으면 클로즈로 간다

%참고! 액티브셋을 가리키는 것을 다시 위로 올리고 싶으면 커서를 닫고 다시 실행시켜야 한다.

- 페치 문은 한번에 하나씩 커서에서 행을 검색한다. 각 페치 후에 커서가 결과행 집합의 다음 행으로 이동한다. %NOTFOUNT 속성을 사용하여 전체 결과행 집합을 검색했는지 확인할 수 있다. 근데 계속 해나갈려면 루프를 사용한다.

- 셀렉트문에 있는 열과 동일한 수의 변수를 페치문의 인투절에 포함시킬 수 있으며 데이터 유형이 호환되어야 한다. 위치에 따라 대응한다.

- 또는 커서에 대한 레코드를 정의하고 FETCH INTO 절에서 해당 레코드를 참조할 수도 있다.

- 마지막으로 커서에 행이 남아 있는지 테스트한다. 페치후 획득한 값이 없으면 더이상할게없는것이고 이것을 오류가 아님.

* 명시적 커서 닫기

DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);

END LOOP;

CLOSE c_emp_cursor;

END;

1. 매모리(컨텍스트 영역) 해제, 결과행 집합의 정의 해제

2. 액티브셋 무효화(끝)

- 커서가 닫혀야만 다시 열 수 있다. 이제 커서를 다시 열어 또 새로운 결과행 집합을 설정 가능.

- 커서를 닫지 않고 pl/sql문을 종료하면 자원해제 안됨

- 세션당 열 수 있는 최대 커서 수에는 디비파라메터 open_cursors 파라메터에 의해 결정된다. 기본값 50

* 커서에 레코드 적용 (7-16)

* 커서 for loop (7-17~18)

- 커서 for 루프를 사용하면 명시적 커서를 편하게 간단히 처리 가능

- 열기, 페치, 종료 및 닫기 작업이 암시적으로 일어남. 즉, 자동으로 일어남.

- 레코드는 암시적으로 선언됨.

- 루프도 암시적으로 선언됨.

- 필요한 경우 루프 중에 커서 속성을 테스트 한다.

* 명시적 커서 속성 (SQL문에서는 커서 속성을 직접 참조할 수 없다)

- %ISOPEN : 커서가 열려 있으면 트루. 커서가 오픈일때만 패치가능하므로 행을 패치하기전에 이 속성을 사용하여 커서가 열렸는지 테스트 (7-20)

- %NOTFOUND : 가장 최근 패치가 행을 반환하지 않으면 트루 (7-21)

- %FOUND : 가장 최근 패치가 행을 반환하면 트루

- %ROWCOUNT : 지금까지 반환된 총 행수 (숫자값) (7-21)

* 서브쿼리를 사용하는 커서 FOR 루프

FOR emp_record IN (SELECT employee_id, last_name FROM employees WHERE department_id=30) -> 이 쿼리문은 한번쓰고 끝이다.또못씀

LOOP

DBMS_OUTPUT.PUT_LINE(emp_record.employee_id || ' ' || emp_record.last_name);

END LOOP;

END;

- 선언부분에 커서선언하지 않고, 루프 자체에서 결과행 집합을 결정하는 SELECT 문을 제공한다.

- 서브쿼리에 있는 결과의 행 개수만큼 그 행 값을 FOR로 돌린다.

- 커서 FOR 루프에 서브쿼리를 쓴 경우 커서에 명시적 이름을 부여할 수 없어서 명시적 커서 속성을 참조할 수 없음.

* 파라메터가 포함된 커서

CURSOR 커서명 [(파라미터명 데이터타입...)] IS

SELECT문장

OPEN 커서명(파라메터값..)

- 파라미터 값에 따라 액티브셋 값이 바뀐다.

- 커서가 열리고 쿼리가 실행되면 커서에 파라미터 값이 전달.

- 미번 다른 결과행 집합으로 여러번 명시적 커서를 열 수 있다.

- 파라미터 데이터 유형은 스칼라 변수의 데이터 유형과 동일하고 파라미터 크기는 정하지 않음

- 커서 for loop에서도 사용가능하다 (7-25)

DECLARE

CURSOR c_emp_cursor (deptno NUMBER) IS

SELECT employee_id, last_name FROM employees WHERE department_id=deptno;

...

BEGIN

OPEN c_emp_cursor (10);

...

CLOSE c_emp_cursor(10);

...

OPEN c_emp_cursor(20);

...

* FOR UPDATE절

- 명시적 잠금을 사용하여 트랜잭션 동안 다른 세션에 대한 액세스를 거부한다. 내가 머지않아 셀렉트한 데이터를 수정할거라 미리 찜하는 것이다.

- 갱신 또는 삭제 전에 행을 잠근다.

- 커서를 열고 (즉, 액티브셋이 되고) 특정 테이블의 행이 갱신되었을 가능성이 있다. 커서를 다시 열어야만 갱신을 확인 가능하므로 갱신이나 삭제전에 행을 잠그는게 좋다.

- 셀렉트문 맨 마지막에 온다. ORDER BY 뒤에 온다.

- 다중 테이블을 쿼리하는경우에도 이 FOR UPDATE절을 사용하여 행 잠금을 특정 테이블로 제한 가능하다.

- 갱신 또는 삭제할 행을 식별한 후에 결과 집합에서 각 행을 잠근다.

- 잠그는 목적도 있지만 잠겨있는지 확인하는 목적으로도 사용된다.

구문

FOR UPDATE OF 컬럼값 [NOWAIT | WAIT n]

- nowait는 요청한 행을 다른 유저가 잠궜을 경우 기다리지 않고 바로 제어가 내려옴.

- wait n은 n초만큼 대기한다.

- 아예 wait 문이 없으면, 행을 사용할 수 있을 떄까지 무기한으로 기다린다.

* WHERE CURRENT OF 절

- 커서를 사용하여 행을 갱신 또는 삭제함. 따라서 행 id를 명시적으로 참조하지 않고도 현재 ㅊ ㅓ리 중인 행에 갱신 및 삭제 가능.

- 먼저 행을 잠그도록 커서 쿼리에 FOR UPDATE절을 꼭 쓰고,

- 그 후에 UPDATE employees SET salary=30000 WHERE CURRENT OF c_emp_cursor; 커서의 현재 행의 salary를 30000으로 바꿔라 라는 뜻.

*명시적 커서 종류

1. CURSOR

2. CURSOR FOR LOOP : 커서와 FOR LOOP가 결함됨

3. CURSOR WITH PARAMETER

4. CURSOR FOR LOOP WITH PARAMETER

5. CURSOR FOR LOOP USING SUBQUERY : 커서 선언 불필요. 바로 사용 가능. 한번쓰고 버리는 것이다. 저장되지 않음.

6. CURSOR FOR UPDATE : SELECT 문에 FOR UPDATE 절

8장 예외 처리 ==================================

예외처리

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm#i3365

아래 사이트에서 검색창에 오라클 에러 번호를 입력하면 해당 에러에 대한 내용이 나온다.

http://docs.oracle.com/cd/E11882_01/server.112/e17766/e900.htm#ORA-01422

1. 예외

*예외처리

예외가 생길 것을 대비 exception에다가 예외가 발생한다면 어떤 행동을 하라 라고 명시한다.

- 예외가 생기면 예외 이하는 실행이 안된다. 바로 예외처리부로 넘어간다.

- 예외가 잘 처리되어도 돌아가지 않고 바로 블럭이 끝난다.

- 만약 인서트작업이었으면 익셉션체리하고 바로 롤백해버린다. (중첩블럭인경우도 한 트랜잭션이라면 싹 다 롤백한다)

- 클라이언트측 PL/SQL을 갖는 일부 응용 프로그램 도구는 자체적인 예외를 가진다.

- 예외란 프로그램 실행중에 발생한 PL/SQL 오류이다.

*에러의 종류

- 구문에러 (Syntax error) = compilation Error : 문법적으로 오류난것은 프로그램을 기계어로 바꾸는 컴파일할때 다 찾을 수 있다. 물리적인 에러이다.

- 시멘틱에러 (의미적으로 에러, employees인데 employee를 찾는다던지..)

- 런타임에러 (여기서 말하는 예외이다)

*예외발생 (RUNTIME ERROR)

- 오라클서버에 의해 암시적으로 발생 : 미리 정의된 예외는 자동으로 오라클이 반환한다.

- 미리 정의된 오라클 서버 오류 : 선언할 필요 없다. 오라클서버가 정의하고 암시적으로 발생. 이름이 있는 예외. too many rows, zero devide error 등. 자주 있는 에러들 (11G는 22개의 이름있는 예외가 있다) 이러한 예외는 선언할 필요 없다. 오라클 서버에 의해 미리 정의되고 암시적으로 실행됨.

- 미리 정의되지 않은 오라클 서버 오류 : 이름 없는 예외 (넘버 에러. ORA-0012등 이런 에러들 이것은 이름 없는 에러다) 이러한 예외는 선언 부분에서 선언해야 하고, 오라클 서버가 암시적으로 오류를 발생시키며, 예외 처리기에서 오류를 처리할 수 있다.

- 프로그램에 의해 명시적으로 발생 : RAISE문을 실행하여 명시적으로 예외를 발생시킨다.

- 유저 정의 : 개발자가 비정상적인 것으로 결정한 조건. 명시적으로 발생시켜야 한다.

변수선언 잘되었고 세미콜론 잘 되었고.. 컴파일할때는 결과를 보지 않는다. 0으로 나누는 에러 같은것처럼 구문 자체가 잘 되었는지를 확인하고 컴파일 잘 되었다고 한다. 그래서 예외 처리가 필요하다.

*참고사항

SQL 디벨로퍼와 SQLPLUS는 에러에 대해서 서로 다른 양의 설명을 제공한다. 디벨로퍼는 훨씬 많은 정보를 준다. 같은 에러를 내지만..

디벨로퍼와 SQLPLUS에러의 결과는 스텍 초이스라고 해서, 에러 메시지가 나오는 에러를 제일 위에 있는것을 봐야 한다. 에러 순서가 1, 2, 3으로 났다면 밑에 텍스트 출력은 3, 2, 1로 출력이 된다.

*예외처리 예시

DECLARE

v_lname VARCHAR2(15);

BEGIN

SELECT last_name INTO v_lname

FROM employees

WHERE first_name='John';

DBMS_OUTPUT.PUT_LINE('john''s last name is : ' || v_lname);

EXCEPTION

WHEN TOO MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE ('Your select statement retrieved multiple rows. Consider using a cursor.');

END;

EXCEPTION이 없는 경우, 에러가 뜰 텐데, 아래처럼 나온다.

ERROR REPORT :

ORA-XXXXX 오라클 에러 넘버

*CAUSE : ~~~~ 원인

*ACTION : ~~~~ 해야 할 일

- 런타임 시 발생하는 오류를 예외라고 부른다. 예외가 발생하면, PL/SQL은 바로 종료된다.

- 예외가 발생하면 예외 이하는 실행이 안된다. 바로 예외처리부로 넘어간다. 예외 처리부에 있는 해당된 명령들이 실행된다. 예외가 잘 처리 되어도 돌아가지 않고 바로 블락이 끝난다.

- 처리기로 트랩

- 호출 환경으로 전달. 블록의 실행 부분에서 예외가 발생하고 해당 예외 처리기가 없는 경우 블록이 종료되고 오류발생, 예외발생블록을 포함하는 블록이나 호출 환경으로 전달됨. 호출환경은 SQLPLUS같은 응용프로그램일 수 있다.

- 만약 인서트를 했으면 익셉션을 처리되던 미뤄지던 다 하고 롤백된다.

2. 예외 트랩

- execption 키워드로 시작한다. (이 절에서도 내부 블럭 만들고 변수선언하고 작업하는것이 가능하다 인것같다)

- 여러 예외 처리기 사용 가능

- 블록을 종료하기 전 하나의 처리기만 실행됨 (제일 먼저 걸린 놈을 처리하므로, 구문을 순서대로 배열해야 한다.)

- 그러므로 when other then을 마지막 절에 넣어야 한다.

- 할당문 또는 sql문에는 예외가 나타날 수 없습니다.

- 특정 예외를 처리하기 위해 익셉션 부분 내에 포함시킬 수 있는 처리기 수에는 제한이 없습니다. 그러나 단일 예외에 대핸 다중 처리기를 가질 수 없습니다.

EXCEPTION

WHEN 예외1 OR 예외2 .... THEN

문장

WHEN 예외 3 THEN

문장

WHEN OTHER S THEN

지정되지 않은 모든 예외를 트랩함.

- 위에서부터 순서대로 쭉 내려가므로, WHEN OTHERS THEN은 맨 아래에 있어야 함. 맨 위에 있으면 그냥 얘가 혼자서 다잡음.

- 예외트랩은 블록을 종료하기 전 하나으 ㅣ처리기에만 실행된다.

(1) 미리 정의된 오라클 서버 오류 트랩 (8-13~15)

(2) 미리 정의되지 않은 오라클 서버 오류 트랩

- 미리 정의된 오라클 서버 오류와 유사하지만, 오라클 서버에서 정의되어있지 않음.

- 표준 오라클 오류임.

- PRAGMA EXCEPTION_INIT 함수를 사용하여 표준 오라클 오류가 있는 예외를 생성함. 선언된 예외는 암시적으로 발생.

- 예외이름과 오라클 오류번호와 연관시켜서 모든 내부 예외를 이름으로 참조하고 예외에 대한 특정 처리기 작성 가능.

- PRAGMA는 의사 명령어라고도 하고. 명령문이 컴파일러 지시어임을 의미하는 키워드로서 PL/SQL실행시 처리되지 않음.

*예시 확인(8-17)

1. 예외 이름 선언 (e_insert_excep EXCEPTION;)

2. 선언된 예외를 표준 오라클 서버 오류번호에 연관시킴 (PRAGMA EXCEPTION_INIT(e_insert_excep, ~01400);

3. 해당하는 예외 처리 루틴 내에 선언된 예외를 참조함

EXCEPTION

WHEN e_insert_excep THEN

DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED');

DBMS_OUTPUT.PUT_LINE(SQLERRM);

*예외 트랩 변수

예외발생시 아래 두 함수를 사용하여 연관된 오류 코드 또는 오류 메시지를 식별할 수 있다. 코드 메시지 기반으로 수행할 후속 작업 결정 가능.

- SQLCODE : 오류 코드에 대한 수자 값을 반환 . 이값을 NUMBER변수에 할당 가능.

- 0 : 예외가 발생하지 않음

- 1 : 유저 정의 예외

- +100 : NO_DATA_FOUND 예외

- 음수 : 기타 오라클 서버 오류 번호

- SQLERRM : 오류 번호와 연관된 메시지를 포함하는 문자 데이터를 반환 . (WHEN OTHERS에서 유용함. 어떤 에러가 잡힌지 알 수 있다)

*예외트랩에서 예외 목록을 생성하는 함수 사용 WHEN OTHERS THEN 사용. (8-19)

(3) 유저 정의 예외 트랩 (유저가 이 상황은 예외이다! 라고 정의하고 해당 시접에서 예외를 실행시키는 것)

만약 부서 번호가 존재하는지 여부를 확인해서 부서 번호가 존재하지 않을 경우, 유저 정의 예외를 일부러 발생시킨다. (RAISE 구문 사용) (8-21)

20000번부터 20999번까지 에러 정의용 번호를 두었다. 이것을 부여하여 정의

3. 기타사항

* 서브블록의 예외 전달

- 서브블록이 예외를 처리하지 못한경우, 처리기를 찾을 떄까지 예외가 이어지는 포함 블록으로 전달된다. 이러한 블록 중 어떤것도 예외를 처리하지 못하면 호스트 환경에 처리되지 않은 예외 발생.

- 내부 블록 안에서 예외 처리가 되면, 내부 블록이 정상적으로 종료되고, 내부블록의 end문 바로 뒤의 나오는 포함하는 블록에서 제어가 재개된다. (트랜잭션이 계쏙 유지됨)

- 트랜잭션은 롤백됨. 핸들이 되어도 롤백, 안되어도 롤백. 예외가 나와도 커밋하고싶으면 예외처리부에서 명시적으로 커밋 롤백을 써줄 수 있다

- 중첩블락인경우도 예외랑 함께 포함된 트랜잭션이라면 다른 블락에서 작업한 내용도 싹 다 롤백된다 하지만 트랜잭션을 나눠서 안그러게 할 수 있다. AUTONUMOUS TRANSACTION이라는 기능은 트랜잭션을 나눈다. (PROGRAM UNIT 7-11 쪽에서 볼 수 있다)

- 만약 예외 처리가 되지 않으면 해당 블록이 종료된다. 그리고 예외를 바로 밖의 블록에 전달한다.

예외처리를 하느냐 안하느냐에 따라 바깥 블럭 문장이 수행되는 유무가 정해진다.

트랜잭션이 시작된 경우, 즉 예외가 발생한 프로시저를 실행하기 전에 DML문이 실행된 경우 해당 트랜잭션은 영향을 받지 않습니다. 예외가 발생하기전에 프로시저 내에서 수행된 DML작업은 롤백됩니다.

예외가 났는데 예외처리기가 없어서 블록이 종료되면 해당 프로시저 내에서 수행된 작업은 롤백 (확실한정보)

예외가 처리되지 않으면, 호출 프로시저와 호출된 프로시저에 있는 디엠엘문은 호스트 변수 변경사항과 함꼐 롤백된다. 처리되지 않은 예외가 발생한 PL/SQL코드를 호출하기 전에 실행된 DML문은 영향을 받지 않는다. (확실한정보)

근데 결과적으로는, 일단 오류 나면 예외 처리가 되면

트랜잭션이 시작된 경우, 즉, 예외가 발생한 프로시저를 실행하기 전에 DML이 실행된 경우 해당 트랜잭션은 영향을 받지 았는다.???

*raise_application_error 프로시저

구문

raise_application_error (에러번호, 메시지, [트루|펄스]);

- 내장 서브 프로그램에서 유저 정의 오류 메시지를 실행할 수 있다.

- 응용 프로그램에 오류를 보고하고 처리되지 않은 예외가 반환되지 않도록 할 수 있다.

- 비표준 오류 코드 및 오류 메시지를 반환하여 미리 정의된 예외를 대화식으로 전달 가능.

- 트루 펄스는 선택적이며, true인 경우 오류가 이전 오류의 스택에 추가되고 false(기본값)이면 이전 오류가 모두 해당 오류로 바뀐다.

- 실행부분, 예외처리부분 두 위치에서 사용 가능하다.

- 마치 오라클 서버에서 오류를 보내는 것 같이 보이게 할 수 있다.

RAISE APPLICATION ERROR 프로시저 예시

SET SERVEROUTPUT ON

DECLARE

v_salary employees.salary%TYPE := 10000;

BEGIN

UPDATE employees

SET salary = v_salary

WHERE last_name = 'Jo';

IF SQL%NOTFOUND THEN

raise_application_error(-20001, 'No sech employees FOUND!!')

END IF;

END;

ERROR REPORT :

ORA-20001 : -20001, 'No sech employees FOUND!!'

마치 오라클에서 제공한 에러인 양, 같은 꼴로 에러가 나온다.

실행부분

BEGIN

...

DELETE FROM EMPLOYEES

WHERE manager_id=v_mgr;

IF SQL %NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20202, 'THIS IS NOT A VALID MANAGER');

END IF;

...

예외 처리 부분

...

EXCEPTION

WHEN NO_dATA_FOUNE THEN

RAISE_APPLICATION_ERROR(-20201, 'MANAGER IS NOT A VALID EMPLOYEE');

END;

1장, 프로시저 ===========================================

이 과정에서 사용하는 PL/SQL 구조의 이름지정 규칙 (1-19)

1장, 프로시저 생성

1. 서브프로그램과 모듈화 개요

* 모듈화된 서브 프로그램을 통해 재사용성, 융통성 등을 얻음

* 계층화된 서브 프로그램 설계 (비즈니스 논리층, 데이터 엑세스 층 2층으로 나누기)

* PL/SQL 블록을 사용한 개발 모듈화

- 모듈화 대상: 익명블록, 프로시저및 함수, 패키지, 트리거

- 모듈화로 얻는 이점 : 손쉬운 유지관리 ,데이터보안 및 무결성, 성능 향상, 코드 명확성 향상

*익명 블록의 일반적인 용도 (BEGIN, DECLARE END 등의 세부사항)

* PL/SQL 서브프로그램이란?

- 파라미터 집합을 사용하여 호출할 수 있는 명명된 PL/SQL블록

- 스펙과 바디로 구성됨. 스펙은 파라미터 설명 포함, 바디는 바디

* PL/SQL 서브프로그램 사용의 이점 (모듈화랑비슷)

* 익병 블록과 서브 프로그램 간의 차이점

2. 프로시저

* 프로시저

- 특정 작업을 수행하는 일종의 서브프로그램

- 디비 스키마에 객체로 저장됨 (유저별로 만들어지므로 권한필요, 접근방식윽 스키마.프로시저명으로 접근)

- 재사용성, 유지 용의성

- 데이터용량을 차지하지 않음. PLSQL블록에 이름붙인거라고 봐야하므로.

%참고

스키마 오브젝트 : 스키마별로 구분됨, 스키마에 관련있는것들 뷰, 테이블, 시노님 등등

난 스키마 오브젝트 : 데이터베이스 전체 통틀어1개씩, 딕셔너리 공용시노님, 롤 등.

* 프로시저 생성

코딩 - 컴파일 - 실행

- 컴파일 오류가 발생했는데 이전에 CREATE PROCEDURE문을 사용했으면 다시 만들떄는 CREATE OR REPLCE PROCEDURE를 사용해야함.

%M-CODE?

프로시저를 컴파일하면 소스코드와 컴파일된 M-CODE를생성해 저장한다. 컴파일오류가 발생하면 M-CODE가 저장되지 않는다.

컴파일했을때 경고나 오류등을 잘 보고 수정.

*프로시저 생성 구문

CREATE [OR REPLACE] PROCEDURE 프로시저명

[(PARAMETER [MODE] 데이터타입.... n)

IS|AS

<DECLARE대신 여기다 씀. 여기까지가 헤더.>

BEGIN

액션

END [PROCEDURE NAME];

* 파라미터 및 파라미터 모드

파라미터는 호출 환경과 서브 프로그램 간에 데이터를 전달한다. 로컬변수처럼 쓰이지만 파라미터 전달모드에 따라 달라짐.

- IN : 처리할 값을 서브프로그램에 제공한다. (값을 받기만 한다. 변화시키면 컴파일때 에러난다) 기본값.

- OUT : 호출자에게 값을 반환.

- IN OUT : 입력 값을 제공하고 이 값은 수정된 값으로 반환 가능.

* 형식 파라미터 및 실제 파라미터 (스펙부분에 있는 파라미터, 바디부분에 있는 프로시저) (1-21)

* 프로시저 파라미터 모드 (DAYATYPE 파라미터는 크기 사양 없이 지정된다. 또한 명시적데이터 유형, %TYPE, %ROWTYPE 정의 사용 가능.

* 파라미터 모드 비교

IN	OUT	IN OUT
디폴트값	따로 지정	따로 지정
값이 서브프로그램에 전달됨(참조에 의해 전달)	값이 호출하는 환경으로 반환됨	전달-서버로그램, 반환-호출환경
형식 파라미터가 상수로 동작 (읽기전용.본문서수정X)	초기화되지 않은 변수로 동작	초기화된 변수로 동작
실제 파라미터가 리터럴, 표현식, 상수 또는 초기화된 변수가 될 수 있다.	변수여야 한다.	변수여야 한다.
기본값 할당 가능	기본값을 할당 불가능	기본값 할당 불가능 (어차피 프로시저안에서 변경)

* 일반 SQL문에서 프로시저 호출하기 EXECUTE문

EXECUTE 프로시저명(파라미터들)

* 예제 : IN 파라미터 모드 (1-24)

파라미터는 항상 실행시 주어지므로, 실행할 때 값을 받아서 해당 파라미터에 들어가게 된다.

IN에서는 해당 파라미터값이 읽기 전용으로 호출 환경에서 프로시저로 전달된다.

BEGIN

프로시저명(파라미터들);

END;

* 예제 : OUT 파라미터 모드 사용 (1-25)

값을 받아서 어떤 식의결과를 냄

* 예제 : IN OUT 파라미터 모드 사용 (1-26)

값을 받아서 변경하고 출력

* out 파라미터 확인하기 DBMS_OUTPUT.PUT_LINE 활용 (1-27) / SQLPLUS의 호스트변수 활용하기 (1-28)

* 실제 파라미터 전달 시 사용 가능한 표기법

- 위치지정방식 : 실제파라미터를 형식 파라미터와 동일한 순서로 나열

- 이름 지정 방식 : 실제 파라미터를 임의 순서로 나열하지만 연관 연산자(=>)를 사용하여 명명된형식 파라미터를 실제 파라미터와 연관시킵니다.

- 혼합 방식 일부 실제 파라미터는 위치로 나열하고, 나머지는 이름으로 나열. 혼합방식에서는 항상 위치 지정방식 파라미터가 이름지정방식보다 더 앞에 있ㅇᅠㅇㅑ 함.

* 실제 파라미터 전달 : 위치지정방식 (1-30)

* 실제 파라미터 전달 : 이름지정방식 (1-31)

* 파라미터에 기본값 옵션 사용. (1-32)

- 프로시저 생성시 생성하는 변수에 := 나 DEFAULT로 할당하면 된다.

* 프로시저 호출 (1=34)

익명 블록, 다른 프로시저 또는 패키지를 사용하여 프로시저 호출 가능. 프로시저를 소유하거나 EXECUTE 권한이 있어야 한다.

* 프로시저 제거

DROP PROCEDURE 프로시저명

- 드랍의 성공 여부와 관계없이 DDL을 실행하면 롤백 될 수 없는 보류 중인 트랜잭션이 커밋됨.

* 데이터 딕셔너리 뷰를 사용하여 프로시저 정보 보기

3. 프로시처 예외처리

다른 프로시저에서 호출되는 프로시저를 개발할 경우, 처리된 예외와 처리되지 않은 예외가 트랜잭션과 호출 프로시저에 미치는 영향 파악해야함.

2장, 함수 ===========================================

함수는 호출환경에 값을 무조건 반환해야하지만 프로시저는 호출 환경에 값을 반환해도되고안해도되고...

함수는 헤더에 RETURN절이 있어야 하고 실행 섹션에는 적어도 한 개의 RETURN이 있어야 한다.

* 함수 생성 문

CREATE [OR REPLACE] FUNCTION 함수명 [(파라미터, 모드, 데이터타입 ...)]

RETURN 데이터타입 IS|AS

로컬변수들

BEGIN

블라블라

- 내장 함수의 PL/SQL블록에서는 호스트나 바인드 변수를 참조할 수 없습니다.

- OUT,IN OUT 모드를 함수와 함께 사용할 수 있지만 바람직하지 않다. 그대신 함수에서 여러 개 값을 반환해야한다면 레코드나 테이블을 써봐

*프로시저와 함수간의 차이점

프로시저	함수
PL/SQL 문으로 실행	표현식의 일부로 호출 (보통 함수만 단독사용하지않음)
헤더에 RETURN 절이 없음	헤더에 RETURN 절이 있어야함
출력 파라미터를 사용하여 값(있을 경우)을 전달가능	단일 값을 반환해야함
값없이 RETURN문을 포함할 수 있음.(걍 빠져나가게됨)	적어도 하나의 RETURN문을 포함해야 함.

- 단일 OUT파라미터를 포함하는 프로시저는 걍 =함수로 재작성하는게 좋다.

* 함수 생성 및 실행 개요 (2-7)

* CREATE FUNCTION 문 사용 예제 (2-8)

* 서로 다른 방법을 사용하여 함수 실행 (프로시저내에서 사용. get_sal(100)) (2-9)

* 서로 다른 방법을 사용하여 함수 실행 (sql문에서 사용. get_sal(100) (2-10)

* sql문에서 유저정의 함수를 사용하는 경우 이점(2-13)

- 너무 복잡하거나 너무 부적절하거나 sql을 사용할 수 없는 계산이 허용됨.

- 함수를 사용하면 데이터를 응용 프로그램으로 읽는 대신 오라클 서버내에서 복잡한 데이터 분석을 처리하므로 데이터 독립성 증가

- 응용 프로그램이 아닌 쿼리에서 함수를 수행하므로 쿼리 효율성 증대

- 새로운 유형의 데이터( 예로 위도,경도)를 조작할 수 있다.

* SQL표현식에 함수 사용 예제(2-14)

* SQL문에서 유저 정의 함수 호출 위치 : 내장 단일 행 함수처럼 동작하며 다음 위치에서 사용가능 (2-15)

- SELECT 리스트 또는 절

- WHERE 및 HAVING 절의 조건식

- QUERY의 CONNECT BY, START WITH, ORDER BY 및 GROUP BY절

- INSERT의 VALUES절

- UPDATE문의 SET절

* SQL에서 함수를 호출할 때 제한사항 (2-16)

- SQL에서 호출할 수 있는 유저 정의 함수인 경우

- SQL문에서 함수를 호출할 경우

* SQL 문에서 함수를 호출할 때의 부작용 제어 (2-17)

* SQL에서 함수를 호출할 때의 제한 사항 : 함수에서 값을 수정하는 함수를 업데이트나 인서트에 사용하면 에러 (2-18)

* SQL의 이름 지정 및 혼합 표기법과 예제 (2-19~20)

* 함수 제거

DROP FUNCTION 함수명;

* 데이터 딕셔너리 뷰를 사용하여 함수 보기(2-22)

3장, 패키지 생성 ===========================================

*패키지

- 논리적으로 관련된 PLSQL유형, 변수, 및 서버프로그램을 그룹화하는 스키마 객체

- 스팩,바디로 나뉘며 스펙은 패키지의 인터페이스 (이것을 통해 커뮤니케이션 가능. 이름/파라메터/데이터타입/리턴타입)이며 패키지 외부에서 참조할 수 있는 유형, 변수, 상수, 예외, 커서 및 서브 프로그램을 선언. 바디는 커서에 대한 쿼리와 서브프로그램에 대한 실제 구현 코드를 넣음.

- 오라클 서버가 동시에 여러 객체를 메모리 안으로 읽을 수 있게함.

*패키지 구성요소와 패키지변수의 가시성 (3-7~8)

*패키지 스펙 생성 (예제 3-14)

CREATE [OR REPLACE] PACKAGE 패키지내임 IS}AS

퍼블릭 타입이나 변수 선언

서브프로그램의 헤더들(헤더라 함은 PROCEDURE 함수명(매개변수) 이런식으로 선언된 것. 이것의 자세한 내용은 바디에 들어감)

END [패키지네임];

*패키지 바디 생성 (예제 3-16)

CREATE [OR REPLACE] PACKAGE BODY 패키지명(스펙과동일) IS|AS

개인 타입 또는 변수 선언

서브프로그램 바디들

BEGIN 패키지가 처음시작할떄 딱 한번만 사용되는 값

END 패키지네임;

* 패키지를 서브프로그램에서의 호출 (예제 3-17)

* 본문 없는 패키지 생성 및 사용 (예제 3-19)

스펙이 유형, 상수, 변수, 예외만 선언한 경우 패키지 바디는 필요없다. 유저 세션 기간 동안 존재하는 데이터를 제공하기 위해 이런 패키지 생성 가능.

* 데이터 딕셔너리를 사용하여 패키지 보기 (3-21)

* 패키기 작성 지침 (3-23)

- 패키지를 일반적인 용도로 개발

- 패키지 스펙은 바디 앞에 정의

- 패키지 스펙은 공용 구성요소만 포함해야 함

- 항목을 세션이나 트랜잭션 전체에서 유지관리해야 할 경우 패키지 바디 선언 부분에 해당 항목을 배치

- 의존성을 세밀하게 하는 관리는 패키지 스펙이 변경될 때 참조 서브 프로그램을 재컴파일할 필요성을 줄입니다. (참조하는항목들을 잘게 ㅁ쪼개서 그중 내가 참조 안하는 것들은 안건드려서 재컴파일 죄소화)

- 패키지 스펙은 구성요소를 가능한 한 적게 포함해야 함.

4장, 패키지 작업 ===========================================

* PL/SQL에서 서브 프로그램 오버로드 : 서브 프로그램에 데이터 유형이 다른 유사한 파라미터 집합을 사용하고자할 때 사용.

예를들어, TO_CHAR 함수를 숫자를 문자로, 날짜를 문자로 둘다 가능함.

- 동일한 이름의 서브 프로그램을 두 개 이상 만드는 것

- 서브 프로그램에서 사용하는 형식 파라미터의 개수, 순서 또는 데이터 유형 계열이 달라야 함.

오버로드 할 수 없는 경우

- 두 서브 프로그램이 형식 파라미터의 데이터 유형만 다르고 이 데이터 유형이 동일한 계열에 속하는 경우 (넘버랑 INTEGER랑 같은)

- 두 서브 프로그램이 형식 파라비터의 서브타입만 다르고, 이 서브타입이 동일한 계열의 유형을 기반으로 하는 경우

- 해당 유형이 다른 계열에 속하더라도 두 함수가 반환 유형만 다른 경우

파라미터 이름이 동일한 경우에도 위와 같은 제한 사항이 적용된다. 서로 다른파라미터 이름을 사용할 경우 파라미터에 대해 이름 지정 표기법을 사용하여 서브 프로그램을 호출할 수 있습니다.

호출 분석 (4-5)

* 오버로드 패키지 생성 (4-6~7)

* 오버로드 및 STANDARD 패키지 (4-8)

- STANDARD페키지는 TO_CHAR같은 내장 함수이다. 얘네들은 대부분 오버로드 된다.

* 잘못된 프로시저 참조와 사전선언 (4-9~10)

PLSQL은 식별자를 참조하기전 미리 선언해야함. 순서를 바꾸던가, 아니면 사전선언을 사용한다.

* 패키지 초기화 (4-11)

간단한 패키지 초기화 작업의 경우 선언에 할당 연산을 사용

보다 복잡한 초기화 작업의 경우 패키지 바디 끝에 코드 블록 추가. BEGIN으로 시작함.

* 패키지함수의 부작용과 PURITY 레벨 (4-12~13)

* 패키지안의 함수를 참조하기 예제 (4-14)

* 패키지의 지속상태 (4-16~17)

- 패키지가 처음으로 로드될 때 초기화

- 세션 기간 동안 지속됨(기본값)

- UGA에 저장

- 세션마다 패키지 STATE가 따로 생성됨. 세션마다 고유함

- 패키지 서브 프로그램이 호출되거나 공용 변수가 수정될 eo 변경될 수 있다.

-PACKAGE SPEC에서 PRAGMA SERIALLY_REUSABLE을 사용할 경우 세션 동안 지속되는대신 서브 프로그램이 호출되는 동안 지속. 유저 세션간에 공유되는 SGA에 패키지 변수를 저장한다. 기존의 PACKAGE를 공유하지 않고 매번 새로운 패키지 사용.(메모리를 절약하려는 경우와 각 유저동안 패키지 상태를 지속할 필요가 없을 때 유용) (SERIAL :１차선, PARALLEL : 여러차선)

* 패키지 변수의 상태 예제 (4-18)

* 패키지 커서의 지속 상태 (4-19~21)

* 패키지에서 연관배열 사용(4-22)

6장, DYNAMIC SQL 사용 ===========================================

* DYNAMIC SQL의 개요 (6-4~6)

SQL문 처리 단계

구문분석 -> 바인드 -> 실행 -> 페치

그냥 CREATE로 만든거는 컴파일이며, EXECUTE 등으로 실행한 것이 런타임이다.

임베디드 SQL : 완성형 문장을 실행

DYNAMIC SQL : 모든 단계가 런타임 시 수행됨.

다이나믹 SQL을 사용하여 생성된 SQL문의 구조는 런타임시 변경 가능.

결국 문장이 런타임 때 완성되느냐 컴파일 때 완성되느냐의 차이임.

다음과 같은 경우 DYNAMIC SQL을 사용한다

- 런타임 시 SQL문의 구조를 변경해야 할 경워

- PL/SQL에서 DDL데이터 정의어 문 및 기타 SQL기능에 엑세스해야 할 경우

선행 컴파일 시 다음 항목 중 하나를 알 수 없는 경우 다이나믹 SQL 사용

- 명령, 절 등과 같은 SQL문 텍스트 (WHERE 등)

-바인드변수의 수 및 데이터 유형

- 테이블, 열, 인덱스, 시퀀스 뷰 같은 디비 객체 참조

* NDS(NATIVE DYNAMIC SQL)

- 실행 시까지 구조를 할 수 없는 SQL문을 실행할 수 있는 기능 제공.

- EXECUTE IMMEDIATE : ㅁㅇ령문을 준비하고 실행하며 변수를 반환한다음 리소스 할당 해제

- OPEN-FOR : 커서 변수를 사용하여 명령문을 준비학 ㅗ실행

- FETCH : 커서 변수를 사용하여 열려 있는 명령문의 결과를 읽어들임

- CLOSE : 커서 변수가 사용하는 커서를 닫고 자원 할당 해제

7장, PLSQL 코드 설계 고려 사항 ===================================

상수 표준화(7-4)

:같이 쓰는 단위라던가 뭐 여러 가지를 표준화하여 같이 쓴다.

예외 표준화(7-5)

응용 프로그램에서 사용될 명명된 예외 및 프로그래머 정의 예외를 포함된 표준화된 오류 처리 패키지를 생성.

예외 처리 표준화 (7-6)

예외의 SQLCODE 및 SQLERRM 값을 기준으로 오류 표시등.

상수 표준화(7-7)

값을 변경하면 안되는 로컬 변수들을 패키지화

로컬 서브 프로그램 (7-8)

중복되는 코드들을 제거하고 한 모듈만 쓰도록.

독립 트랜잭션 (7-11~14)

출처

http://blog.naver.com/second_brain?Redirect=Log&logNo=30181730839

저작자표시 비영리 변경금지

콩쓰의 화끈한 생각

PL/SQL 정리

티스토리툴바

PL/SQL 정리

'콩's EDUCATION/콩's DATABASE' Related Articles

티스토리툴바