[ SQL ] Oracle PL/SQL #1 _ PL/SQL

오라클의 PL/SQL

[ PL/SQL ]

• 데이터베이스 내의 데이터를 조작하기 위해서 오라클이 제공해 주는 SQL 문을 사용했는데 SQL 의 장점은 쿼리문 하나로 원하는 데이터의 조회와 조작할 수 있다는 점이다.
• SQL 자체는 비절차 언어이기에 몇 개의 쿼리문 사이에 어떠한 연결이나 절차적 방식을 사용하고자할 때는 사용 할 수 없으므로 오라클 사에서 SQL 언어에 절차적인 프로그래밍 언어를 가미해 만든 것이 PL/SQL 이다.
• PL/SQL 은 Oracle's Procedural Language extension to SQL 의 약자이다.
• SQL 은 ANSI 표준 언어로 어떤 제품군도 사용할 수 있지만, PL/SQL 은 오라클의 고유한 언어이므로 다른 제품군에서 사용할 수 없다

 

 

 

 

[ PL/SQL 특징 ]

• PL/SQL 은 C 언어와 유사한 문법을 사용하는 블록 구조의 언어로 sql 에 대한 Procedural Language 의 확장이며 컴파일한 후 결과를 실행한다.
• PL/SQL 은 procedure, function, package 등을 만들 때 사용하며 sql data type 을 지원하며 변수 선언과 같은 표준 프로그래밍 구조를 제공한다.
• PL/SQL 은 일반 프로그래밍 언어적인 요소를 거의 다 가지고 있어서 실무에서 요구되는 절차적인 데이터 처리를 다 할 수 있다.
• PL/SQL 은 SQL 과 연동되어서 막강한 기능을 구현할 수 있다.
• PL/SQL 은 데이터 트랜잭션 처리능력, 데이터에 대한 보안, 예외처리 등 데이터베이스와 관련된 중요한 모든 기능을 지원하기 때문에 데이터베이스 업무를 처리하기에 최적화된 언어이다.

 

 

 

 

 

[ PL/SQL 기능 ]

• PL/SQL 은 모듈 프로그램 생성이 가능하므로 프로그램 개발의 모듈화가 가능하다.
• PL/SQL 은 식별자 선언 및 오류제어가 가능하다.
• PL/SQL 은 성능향상, 이식성, 통합성, 효율성 기대 등을 가져올 수 있으며 절차적 언어 제어구조를 사용한 프로그램이 가능하다.
• PL/SQL 에서 제공하는 절차적 프로그래밍 기능: 
  * 변수, 상수 등을 선언하여 SQL 과 절차형 언어에서 사용한다.
  * if 문을 사용하여 조건에 따라 문들을 분기한다.
  * loop 문을 사용하여 일련의 문을 반복적으로 실행한다.

(1) 패키지

• 패키지(package)는 관련된 stored procedure, function, cursor 와 변수들의 모임이다.
• 패키지는 이름이 있는 하나의 패키지로 번들(Bundle)이 되며 번들은 꾸러미로 비슷한 기능을 가진프로시저나 함수가 보관되어 있다.
• PL/SQL 은 프로시저와 함수가 동시에 실행되기 때문에 패키지 내에서 선언한 변수는 프로시저와 함수 모두에 적용될 수 있다.

(2) 트리거

• 데이터베이스 트리거(Trigger)는 테이블에 대한 이벤트에 반응해 자동으로 실행되는 작업을 의미한다.
• 트리거는 데이터 조작어의 데이터 상태 관리를 자동화하는 데 사용된다.
• 트리거를 사용하여 데이터 작업 제한, 작업 기록, 변경 작업 감사 등을 할 수 있다.

 

 

 

 

 

 

[ PL/SQL 작성 규칙 ]

• PL/SQL 블록 내에서는 한 문이 종료할 때마다 명령의 종료 표시인 ;(세미콜론)을 사용한다.
• begin … end 문 뒤에 세미콜론(;)을 사용하여 하나의 블록이 끝났다는 것을 명시한다.
• PL/SQL 문은 여러 줄에 걸쳐질 수 있으나 명령어는 분리될 수 없다.
• PL/SQL 블록의 내용을 읽기 쉽도록 공백문자를 사용하여 PL/SQL 문을 적절하게 분리함으로써 의미분석이 되도록 한다.
• PL/SQL 문은 들여쓰기도 권장한다.
• PL/SQL 블록 내의 오라클 함수를 사용할 수 있으나 그룹함수는 SQL 문에 포함되어야만 사용될 수 있다.
• PL/SQL 의 프로시저 내에서는 단일행 함수만 사용할 수 있다.
• PL/SQL 에서 시퀀스를 사용할 때 오라클 11g 이전 버전에서는 SQL 문을 이용하여 시퀀스를 변수에 할당한 후 해당 변수값을 사용했으나 오라클 11g 버전부터는 PL/SQL 문에서 바로 시퀀스를 사용할 수 있다.
• PL/SQL 에서도 묵시적 데이터 타입 변환은 문자와 숫자, 문자와 날짜를 연산할 때 발생하며 이부분 때문에 성능에 의도하지 않게 나쁜 영향을 줄 수 있으므로 데이터 타입의 일치에 항상 주의해야 한다.

 

(1) 식별자

• 식별자는 PL/SQL 객체에 부여되는 이름으로 식별자명은 기본 오라클 명명 규칙을 준수한다.
• 식별자는 테이블 이름이나 변수명 등은 모두 식별자다.
• 오라클의 예약어는 식별자로 사용될 수 없다.
• 식별자명 중에 문자 리터럴과 날짜 리터럴은 '(싱글 쿼터)로 표시해야 하며 널 값은 NULL  상수로 기술한다.
• 식별자 중에서 다음과 같은 경우에 식별자를 '(싱글 쿼터)로 표시하여 사용하며 식별자를 연이어 사용할 때는 "(더블 쿼터)로 묶어야 하지만, "(더블 쿼터)로 묶은 식별자는 특별한 경우가 아니면 권장하지 않는다.
  *  식별자의 대소문자 구분이 필요한 경우
  *  공백과 같은 문자 포함할 경우
  *  예약어를 사용해야 할 경우

 

(2) 주석

• 주석은 직접 프로그래밍 코드의 성능에는 영향을 주지 않지만, 나중을 위해서 설명이나 해설등을 기록해 두는 것이다.
• 주석의 표기에 따라 기능이 달라지며 주석의 표기는 다음과 같다.
  *  --(더블 하이픈) : 한 줄 주석 내용을 표시한다.
  *  /*(슬래시 아스터리스크) … */(아스터리스크 슬래시) : 여러 줄에 주석 내용을 표시한다.

 

(3) SQL 문

• PL/SQL 문 내에서의 SQL 문을 사용하기 위해서는 다음과 같은 점을 주의해야 한다.
  *  begin … end 문의 end 명령어는 트랜잭션의 끝이 아니라 PL/SQL 블록의 끝을 나타낸다.
  *  PL/SQL 은 데이터 정의어와 데이터 제어어를 직접 지원하지 않지만, 동적 SQL 을 사용하면 PL/SQL 에서 데이터 정의어와 데이터 제어어를 실행할 수 있다.

 

 

ex)  간단한 메시지를 출력한다.

-- 화면 출력기능 활성화 SET SERVEROUTPUT ON; BEGIN     DBMS_OUTPUT.PUT_LINE('안녕 PL/SQL'); END;

 

--안녕 PL/SQL --PL/SQL 프로시저가 성공적으로 완료되었습니다.

 

---

 PL/SQL 구조 

 1.  PL/SQL 블록 

(1) 블록의 구조

 

[declare] 선언 블록
begin
문 1 ----------------------------- 실행 블록
᎒ 
문 N 
[exception] 예외 블록
end; ------------------------------ 실행블록

 

 

① 선언 블록
- 선언 블록은 선택 사항이므로 필요할 때 declare 명령어를 선언한다.
- 선언 블록에는 변수, 상수, 커서 등을 선언하는 부분이다.
- 선언 블록에 선언한 변수와 상수의 끝에는 ;(세미콜론)을 표시해야 한다.

 

②실행 블록
- 실행 블록은 필수 사항이므로 반드시 선언해야 한다.
- 실행 블록에는 PL/SQL 문이나 SQL 문이 위치하는 부분이다.
- 실행 블록에 선언한 PL/SQL 문이나 SQL 문의 끝에는 ;(세미콜론)을 표시해야 한다.
- 실행 블록은 begin 명령어로 시작하고 end 명령어로 끝나며 end 명령어의 끝에는 ;(세미콜론)을 표시해야 하며 실행문의 종료는 end 문을 만나야 종료된다.
- begin 명령어와 end 명령어로 사이에는 제어문, SQL 문, CURSOR 문 등을 포함한다.
- begin 명령어와 end 명령어 사이에는 SQL 문에는 데이터 질의어, 데이터 조작어, 데이터 처리어만 사용할 수 있으며 단락의 끝에는 ;(세미콜론)을 표시해야 한다.

 

③예외 블록
- 예외 블록은 선택 사항이므로 필요할 때 exception 명령어를 선언한다.
- 예외 블록에는 예외처리문을 선언하는 부분이다.
- 예외 발생 시 사전에 정의한 대로 수행할 내용이 위치한다.

 

 

 

 

(2) 블록의 형태

①anonymous 블록

- anonymous 블록은 이름이 없는 익명블록으로 모든 PL/SQL 환경에서 사용할 수 있다.

[declare]
begin
문 1
᎒
문 N
[exception]
end;

 

 

②procedure 블록

- procedure 블록은 반환값이 있을 수도 있고 없을 수도 있는 실행블록이다.
- procedure 블록은 데이터베이스 내부에서 만들어지며 다른 애플리케이션에서도 사용할 수 있다.
- procedure 블록은 매개변수를 받을 수 있으며 재사용이 가능하다.

create procedure 프로시저명
is
begin
문 1
᎒
문 N
[exception]
end;

 

③function 블록

- function 블록은 반환값을 반드시 반환하는 실행 블록이다.
- function 블록은 데이터베이스 내부에서 만들어지며 다른 애플리케이션에서도 사용할 수 있다.
- function 블록은 매개변수를 받을 수 있으며 재사용이 가능하다.

create function 함수명
return 데이터 타입
is
begin
문 1
᎒
문 N
[exception]
end;

 

 

 

 

 2.  PL/SQL 변수 

(1) 변수

변수명 데이터 타입 [상수] [not null] [:= 초기값];

• PL/SQL 블록 내에서 사용되는 모든 변수는 사용 전에 미리 선언되어야 한다.
• 결과값을 선언된 변수에 할당시키면 해당 변수는 세션이 종료될 때까지 사용이 가능한 상태가 된다.
• PL/SQL 에서는 변수의 값을 지정하거나 재지정하기 위해서 :=(콜론이퀄)을 사용하며 콜론이퀄의 왼쪽에는 새 값을 받기 위한 변수를 선언하고 오른쪽에는 저장할 값을 선언한다.
• 변수에 할당되는 모든 값은 리터럴이다.
• 변수의 명명 규칙: 
  *  변수의 시작은 반드시 문자로 시작해야 한다.
  *  변수는 특수 문자와 숫자를 포함할 수 있다.
  *  변수는 예약어를 포함하면 안 된다.

 

(2) 변수의 데이터 타입
① 숫자 데이터 타입

• 숫자 데이터 타입에는 number, integer, float 등이 있다.
• 숫자 데이터 타입은 숫자 리터럴에 사용되며 숫자 리터럴은 정수나 실수값을 나타낸다.

② 문자 데이터 타입

• 문자 데이터 타입에는 char, varchar2 등이 있다.
• 문자 데이터 타입은 문자 리터럴에 사용되며 문자 리터럴은 문자나 문자열 값을 나타낸다.

③ 날짜 데이터 타입

• 날짜 데이터 타입에는 date 등이 있다.
• 날짜 데이터 타입은 날짜 리터럴에 사용되며 날짜 리터럴은 날짜 값을 나타낸다.

④ 논리 데이터 타입

• 논리 데이터 타입에는 boolean 등이 있다.
• 논리 데이터 타입은 불리언 리터럴에 사용되며 불리언 리터럴은 불리언 변수에 할당된 값을 나타낸다.
• 불리언 리터럴의 값은 true 와 false 의 값이거나 null 값이다.

 

 

(3) 변수의 형태
① 스칼라(scala) 변수
변수명 데이터 타입 [:= 초기값];

 

• 스칼라 변수는 스칼라값을 취할 수 있도록 속성이 정의된 변수이다.
• 스칼라는 하나의 수치만으로 완전히 표시되는 양을 의미하며 단순히 크기를 나타내는 값이라고 생각하면 된다.
• 오라클의 스칼라 변수는 SQL 에서 사용하던 데이터 타입과 거의 유사하다.
• 숫자 데이터 타입과 문자 데이터 타입으로 스칼라 변수를 선언하면 다음과 같다.
• 숫자 데이터 타입: 
  *  변수명 number;
  *  변수명 number(5);
  *  변수명 number := 20;
• 문자 데이터 타입: 
  *  변수명 varchar2(10);
  *  변수명 varchar2(10) := '홍길동';

② 레퍼런스(reference) 변수

• 레퍼런스 변수는 변수의 주소값을 참조하는 속성이 정의된 변수이다.
• 오라클의 레퍼런스 변수는 테이블 컬럼의 데이터 타입을 참조하는 데이터 타입으로 선언한 변수이다.
• 개발자가 테이블에 정의된 컬럼의 데이터 타입과 크기를 모두 파악하고 있다면 문제가 없겠지만 대부분은 그렇지 못하기 때문에 오라클에서는 레퍼런스 변수를 제공한다.
• 오라클의 레퍼런스 변수는 컬럼의 데이터 타입이 변경되더라도 컬럼의 데이터 타입과 크기를 그대로 참조하기 때문에 굳이 레퍼런스 변수 선언을 수정할 필요가 없다는 장점이 있다.
• %type 
  변수명 테이블명.컬러명%type [:= 초기값];
  -  %type 속성을 지정하면 컬럼 단위로 참조하는 레퍼런스 변수가 된다.
  -  레퍼런스 변수는 컬럼에 맞추어 변수를 선언하기 위해 %type 속성을 사용한다.
• %rowtype 
  변수명 테이블명%rowtype;
  -  %rowtype 속성을 지정하면 모든 컬럼을 참조하는 레퍼런스 변수가 된다.
  -  레퍼런스 변수는 테이블의 모든 컬럼을 레코드로 선언하기 위하여 %rowtype 속성을 사용한다.
• 특징:
  --  테이블 내부의 컬럼 집합의 이름, 데이터 타입, 크기, 속성을 그대로 사용하여 선언한다.
  --  %rowtype 속성 앞에 오는 것은 테이블 이름이다.
  --  지정된 테이블의 구조와 같은 구조를 갖는 변수를 선언한다.
•  장점: 
  --  테이블 컬럼의 개수나 데이터 타입을 알지 못할 때 편리하다.
  --  코딩 이후에 컬럼의 수나 데이터 타입이 변경될 경우 재수정이 필요 없게 된다.
  --  select 문을 이용하여 컬럼을 조회할 때 편리하다.

 

 

 

ex)  스칼라 변수를 선언하고 변수값을 조회한다.

#1 화면 출력기능 활성화

SET SERVEROUTPUT ON;

#2 스칼라 변수 선언 & 실행문

-- 스칼라 변수 선언 DECLARE     SONNO NUMBER(4);     SONNAME VARCHAR2(12); -- 실행문 시작 BEGIN     SONNO := 1001;     SONNAME := '홍길동';     DBMS_OUTPUT.PUT_LINE('사번 이름');     DBMS_OUTPUT.PUT_LINE('---------');     DBMS_OUTPUT.PUT_LINE(''||SONNO||' '||SONNAME); --실행문종료 END;

 

사번 이름 --------- 1001 홍길동 PL/SQL 프로시저가 성공적으로 완료되었습니다.

 

 

 3.  PL/SQL 데이터 질의어 

• 데이터 질의어는 정보를 추출할 필요가 있을 때 SQL 을 사용한다.
• PL/SQL 에서는 SQL 에서 사용하는 명령어를 그대로 사용할 수 있다.
• 테이블의 컬럼에서 질의 된 값을 변수에 할당시키기 위해 select 문을 사용한다.
• SQL 문과 차이점은 PL/SQL 의 select 문은 into 문이 필요한데 into 문에는 데이터를 저장할 변수를 선언한다.

select 컬럼 1, 컬럼 2,…컬럼 N
into 변수 1, 변수 2,…변수 N
from 테이블명
where 조건식;

• select 컬럼 1, 컬럼 2,…컬럼 N
   컬럼의 목록이다. 컬럼에는 행 함수, 그룹 함수, 표현식을 기술할 수 있다.
• into 변수 1, 변수 2,…변수 N
  데이터를 저장할 변수의 목록이다. 
  into 문은 값을 저장하기 위한 변수명을 선언하며 변수는 PL/SQL 의 레코드 변수이다.
• from 테이블명
  조회할 테이블을 지정한다.
• where 조건식; 
  조회할 조건을 지정하며 조건을 충족하는 컬럼값으로 SQL 문을 제한한다.
  조건식에는 PL/SQL 변수와 상수를 포함하여 컬럼명, 표현식, 비교 연산자로 구성된다.
  조건식의 조건은 오직 하나의 값을 반환할 수 있는 조건이어야 한다.
  조건식은 조회할 조건에 대한 조건이나 표현식으로 ;(세미콜론)으로 종료한다.

 

ex)  PL/SQL 의 select 문으로 emp 테이블에서 사원번호와 이름을 조회한다.

#1 화면 출력기능 활성화

SET SERVEROUTPUT ON;

#2 레퍼런스 변수 & 실행문

-- 레퍼런스 변수 선언 DECLARE     SONNO EMP.EMPNO%TYPE;     SONNAME EMP.ENAME%TYPE; -- 실행문 시작 BEGIN     SELECT EMPNO, ENAME INTO SONNO, SONNAME     FROM EMP     WHERE ENAME = 'SMITH';     DBMS_OUTPUT.PUT_LINE('사번 이름');     DBMS_OUTPUT.PUT_LINE('---------');     DBMS_OUTPUT.PUT_LINE(''||SONNO||' '||SONNAME); --실행문종료 END;

사번 이름 --------- 7369 SMITH PL/SQL 프로시저가 성공적으로 완료되었습니다.