PL/SQL기초2

PLSQL Data Type 

• SQL Data Type : NUMBER, CHAR, VARCHAR2, DATE, TIMESTAMP
• PLSQL전용 Data Type : BOOLEAN, PLS_INTEGER, BINARY_FLOAT, ...

 

Dynamic SQL

PLSQL은 Binding이 Compole시에 일어나므로 DB Object의 Name이 Compile시에 고정되어야 하는 등의 제한이 있지만 Dynamic SQL을 사용하면 SQL문을 변수에 정의하여 SQL문의 최종완성을 런타임중에 할 수 있다는 장점이 있다.

추가로 DBMS_SQL Package로 Dynamic SQL문의 사용을 가능하다.

 

Dynamic SQL을 사용하는 경우 

1. DDL(Data Definition Language)을 사용하는 경우    → DDL이란 ?  DB를 정의하는 언어이며, 데이터를 생성, 수정, 삭제하는 등의 데이터의 전체의 골격을 결정하는 역할을 하는 언어이다.
2. 변수를 이용하여 SQL문장을 동적으로 만들어 내는 경우(검색조건, 정렬조건, 힌트구문 등)
3. 컴파일타임에 존재하지 않는 DB Object를 참조하는 경우(테이블의 이름을 동적으로 생성)
4. PLSQL블록에서 다른 프로시저(Procedure)를 호출하는 경우

 

Function

• 자신이 호출한 곳으로 반드시 하나의 값을 리턴해 줘야하는 PLSQL Stored Program이다.
• 함수 Header에 리턴되는 데이터 타입을 기술해야 하고 Header의 끝에는 IS[AS]가 와야하고 IS[AS]와 Begin사이에 Begin~End에서 사용할 변수를 선언한다.

 

Function의 형식

IS[/AS] 다음 Declaration_section부분에서는 밑에 Execution_section에서 사용할 변수를 선언해준다.(Procedure와 동일)

[OR REPLACE]의 뜻은 오버라이딩한다는 뜻이다.

 

Function의 예시

%TYPE : 같은 타입으로 지정한다.

v_ename emp.ename%TYPE : v_ename이라는 변수를 emp.ename과 같은 type으로 설정한다.

RETURN TYPE이랑 INTO 꼭 써줘야한다.

 

위 함수의 실행

변수에 접근할 땐 :ename을 이용해야한다.

PLSQL에서 '=' 은 ':='로 표기한다.

 

 

Procedure

• 자신을 호출한 곳으로 리턴 해주는 값은 없고 호출되어 실행만 된다.
• 실행 환경과 Stored Program 사이에 값을 전달하기 위해 파라미터를 사용한다.
• 파라미터 종류
  • IN : 호출하는 곳으로 함수 or 프로시저로 값을 전달
  • OUT : 함수 or 프로시저에서 호출한 곳으로 값을 전달
  • IN OUT : 호출하는 곳에서 함수 or 프로시저로 값을 전달하고 동시에 함수 or 프로시저에서 호출한 곳으로 값을 전달하는 변수

 

Procedure 형식

IS[/AS] 다음 Declaration_section부분에서는 밑에 Execution_section에서 사용할 변수를 선언해준다.(Function과 동일)

 

Procedure 예제(parameter : IN)

exec ojc3([값], [값])으로 실행 가능

 

Procedure 예제(parameter : OUT) → 외부에서 참조가능

Function은 값을 return해줘야하고 Procedure은 값을 return 하지 않는다