* 본 포스팅은 PLSQL-Uuser's Guide and Reference 책을 기반으로 작성하였습니다.
이번 시간에는 PL/SQL의 정의, 특징, 변수 선언 방법 등에 대해 알아보도록 하겠습니다!
PL/SQL (Procedural Language extension to SQL) 이란?
PL/SQL은 관계형 데이터베이스(RDBMS) 시스템인 오라클 DBMS에서 SQL 언어를 확장하기 위해 사용하는 컴퓨터 프로그래밍 언어 중 하나입니다. 주로 자료 내부에서 SQL 명령문만으로 처리하기에는 복잡한 자료의 저장이나 프로시저와 트리거 등을 작성 할 때 쓰입니다. 유저 프로세스가 PL/SQL 블록을 보내면, PL/SQL 은 엔진에서 해당 블록을 받고 SQL과 Procedural을 나눠서SQL은 SQL Statement Execute로 보냅니다.
PL/SQL 프로그램의 종류는 크게 프로시저, 함수, 트리거로 나누어집니다.
PL/SQL의 장점
1. SQL 지원
SQL은 유연하고 강력하며 배우기 쉽지 때문에 표준 데이터베이스 언어가 되었습니다.
SELECT, INSERT, UPDATE, DELETE와 같은 몇 가지의 영어와 비슷한 명령을 사용하면 RDBMS(관계형 데이터베이스)에 저장된 데이터를 쉽게 조작할 수 있습니다.
PL/SQL은 응용 프로그램을 보다 더 유연하고 다양하게 만드는 고급 프로그래밍 기술인 동적 SQL도 지원합니다. 때문에 프로그램은 PL/SQL을 통해 런타임에 "즉석에서" SQL 데이터 정의, 데이터 제어 및 세션 제어 문을 빌드하고 처리할 수 있습니다.
2. 더 나은 성능
PL/SQL이 없으면 Oracle은 SQL문을 한 번에 하나씩 처리해야 합니다. 각 SQL문은 Oracle에 대한 또 다른 호출을 해야하고 이는 오버헤드를 초래합니다. 또한 SQL문이 실행될 때마다 네트워크를 통해 전송되어야 하므로더 많은 트래픽이 생성됩니다.
하지만 PL/SQL은 블록 단위로 전체 명령문을 한번에 Oracle에 보낼 수 있기 때문에
애플리케이션과 Oracle 사이의 통신을 크게 줄일 수 있습니다.
3. 생산성 향상
PL/SQL은 Oracle Forms 및 Oracle Reports와 같은 비절차적 도구에 기능을 추가합니다. 예를 들어 Oracle Forms 트리거에서 전체 PL/SQL 블록을 사용할 수 있습니다.또한, PL/SQL은 모든 Oracle 환경에서 동일합니다. 하나의 Oracle 도구로 PL/SQL을 마스터하는 즉시 지식을 다른 도구로 이전할 수 있기 때문에 생산성 향상이 배가 됩니다.
4. 이식성
PL/SQL로 작성된 애플리케이션은 Oracle이 실행되는 모든 운영 체제 및 플랫폼으로 이식할 수 있습니다. 즉, PL/SQL 프로그램은 Oracle이 실행할 수 있는 모든 곳에서 실행할 수 있는 것이죠. 때문에 각각의 새로운 환경에 맞게 조정할 필요가 없습니다.
5. SQL과의 긴밀한 통합
PL/SQL과 SQL은 긴밀하게 통합되어 있습니다. %TYPE 및 %ROWTYPPE 속성은 PL/SQL을 SQL과 추가로 통합합니다. 예를 들어, %TYPE 속성을 사용하여 데이터베이스 열의 정의를 기반으로 변수를 선언할 수 있습니다.
또한 열의 정의가 변경되면 다음에 프로그램을 컴파일하거나 실행할 때 변수 선언이 그에 따라 변경됩니다. 이를 통해 데이터 독립성을 제공하고 유지 관리 비용을 줄이며
새로운 비즈니스 요구 사항을 충족하기 위해 데이터베이스가 변경될 때 프로그램을 조정할 수 있습니다.
PL/SQL의 특징
1. 블록 구조
PL/SQL은 블록 구조 언어입니다.
즉, PL/SQL 프로그램을 구성하는 기본 단위인 프로시저, 함수 및 익명 블록은 중첩된 하위 블록의 수에 관계 없이 포함될 수 있는 논리블록입니다.
블록을 사용하면 논리적으로 관련된 선언 및 명령문을 그룹화할 수 있습니다. 그렇게 하면 선언이 사용되는 위치에 가까운 곳에 선언을 배치할 수 있습니다.
PL/SQL 블록에는 아래와 같이 선언문, 실행문, 예외처리 세 부분이 있습니다.
[DECLARE --
선언문]
BEGIN --
실행문
[예외 -- 핸들러]
END;
PL/SQL 블록 또는 하위 프로그램의 실행 및 예외 처리 부분에는 하위 블록을 중첩할 수 있지만, 선언 부분에는 중첩할 수 없습니다. 또한 모든 블록의 선언 부분에서 로컬 서브 프로그램을 정의할 수 있습니다. 그러나 로컬 서브 프로그램이 정의된 블록에서만 로컬 서브 프로그램을 호출할 수 있습니다.
2. 커서
Oracle은 작업 영역을 사용하여 SQL문을 실행하고 처리 정보를 저장합니다. P
L/SQL에 있는 커서라는 구성을 사용하면 작업 영역의 이름을 지정하고 저장된 정보에 접근할 수 있습니다. 커서에는 암시적 커서와 명시적 커서가 있습니다. PL/SQL은 하나의 행만 반환하는 쿼리를 포함하여 모든 SQL 데이터 조작문에 대해 커서를 암시적으로 선언합니다. 둘 이상의 행을 반환하는 쿼리의 경우 커서를 명시적으로 선언하여 행을 개별적으로 처리할 수 있습니다. 아래의 예시처럼 사용이 가능합니다.
DECLARE
CURSOR cl IS
SELECT empo, ename, job FROM emp WHERE deptno = 20;
다중 행 쿼리에서 반환 된 행의 집합을 결과 집합이라고 합니다. 크기는 검색 기준을 충족하는 행 갯수 입니다. 명시적 커서는 결과 집합의 현재 행을 가리킵니다. 이렇게 하면 프로그램에서 행을 한 번에 하나씩 처리할 수 있습니다.
다중 행 쿼리 처리는 파일 처리와 비슷합니다. 파일 처리를 하는 COBOL 프로그램은 파일을 열고 레코드를 처리한 다음 파일을 닫습니다. 마찬가지로 PL/SQL 프로그램은 커서를 열고 쿼리에서 반환된 행을 처리한 후에 다음 커서를 닫습니다.
OPEN, FETCH 및 CLOSE 문을 사용하여 커서를 제어합니다. OPEN 문은 커서와 연결된 쿼리를 실행하고 결과 집합을 식별하며 첫 번째 행 앞에 커서를 놓습니다. FETCH 문은 현재 행을 검색하고 커서를 다음 행으로 이동합니다. 마지막 행이 처리 완료되면 CLOSE 문으로 커서를 비활성화합니다.
커서 FOR 루프
명시적 커서가 필요한 대부분의 상황에서는 OPEN, FETCH, CLOSE 문 대신 커서 FOR 루프 문을 사용하여 코딩을 단순화 할 수 있습니다. 커서 FOR 루프는 해당 루프 인덱스를 데이터베이스에서 가져온 행을 나타내는 레코드로 암시적으로 선언합니다. 그런 다음 커서를 열고 결과 집합의 값 행을 레코드의 필드로 반복적으로 가져온 모든 행이 처리되면 커서를 닫습니다.
DECLARE
CURSOR c1 IS
SELECT ename, sal, hiredate, deptno FROM emp;
...
BEGIN
FOR emp_rec IN C1 LOOP
...
salary_total := salary_total + emp_rec.sal;
END LOOP;
위의 예시에서 커서 FOR 루프는 암시적으로 emp_rec를 레코드로 선언합니다. 레코드의 개별 필드를 참조하려면 점(.)을 사용합니다.
커서 변수
커서와 마찬가지로 커서 변수는 다중 행 쿼리의 집합에서 현재 행을 가리킵니다. 그러나 커서와 다르게 커서 변수는 모든 유형 호환 쿼리를 열 수 있습니다. 특정 쿼리에 연결되지 않는다는 의미입니다.
커서 변수는 새 값을 할당할 수 있고 Oracle 데이터베이스에 저장된 하위 프로그램에 전달할 수 있는 실제 PL/SQL 변수입니다. 이를 통해 데이터 검색을 중앙 집중화할 수 있는 더 많은 유연성과 편리한 방법을 얻을 수 있습니다.
일반적으로 형식 매개변수 중 하나로 커서 변수를 선언하는 저장 프로시저에 커서 변수를 전달하여 변수를 엽니다. 아래는 선택한 쿼리에 대한 커서 변수인 generic_cv를 여는 예시입니다.
PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp, choice NUMBER) IS
BEGIN
IF choice = 1 THEN
OPEN generic_cv FOR SELECT * FROM emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM dept;
ELSIF choice = 3 THEN
OPEN generic_cv FOR SELECT * FROM salgrade;
END IF;
...
END;
3. 변수 선언과 상수 선언
PL/SQL을 사용하면 상수와 변수를 선언한 표현식을 사용할 수 있는 모든 곳에서 SQL 및 절차 문에서 사용할 수 있습니다. 따라서 다른 선언문을 포함하여 다른 명령문에서 참조하기 전에 상수 또는 변수를 선언해야 합니다.
변수가 가질 수 있는 데이터 유형으로는 CHAR, DATE, NUMBER와 같은 SQL 데이터 유형이나 BOOLEAN, BINARY_INTEGER와 같은 PL/SQL 데이터 유형이 있습니다.
예를 들어 4자리 숫자를 보유하기 위해 part_no 라는 변수를 선언하고 boolean 값 TRUE 또는 FALSE를 갖기 위해 in_stock 라는 변수를 선언한다면 아래와 같이 선언할 수 있습니다.
part_no NUMBER(4);
in_stock BOOLEAN;
위의 데이터 유형 뿐만 아니라 TABLE, VARRAY, RECORD 와 같은 복합 데이터 유형을 사용하여 중첩 테이블, 가변 크기 배열 및 레코드를 선언할 수도 있습니다.
변수 선언
변수에 값을 할당하는 방법은 세가지가 있습니다.
첫 번째 방법은 일반 프로그래밍 언어에서 사용하는 변수 값 할당과는 다르게 :(콜론) 다음 등호(=)가 오는 대입 연산자(:=) 를 사용하는 것입니다. 변수를 연산자 왼쪽에 배치하고 표현식을 오른쪽에 배치합니다.
tax := price * tax_rate;
유효한 ID := false;
보너스 := 현재 급여 * 0.10;
임금 := 총지급(emp_id, st_hrs, ot_hrs) - 공제;
두 번째 변수를 할당하는 방법은 데이터베이스 값을 변수로 선택하는 것입니다.
아래의 예시에서는 직원 급여를 선택할 때 오라클이 10% 보너스를 계산하도록 합니다.
SELECT sal * 0.10 INTO 보너스 FROM emp WHERE empno = emp_id;
이렇게 하면 다른 계산에서 변수인 보너스를 사용할 수 있고 해당 값을 데이터베이스 테이블에 삽입할 수 있습니다.
마지막으로 변수를 할당하는 방법은 변수를 OUT 또는 IN OUT 매개변수로 서브 프로그램에 전달하는 것입니다.
DECLARE
my_sal REAL(7,2); -- 초기 값 세팅
PROCEDURE adjust_salary(emp_id INT, salary IN OUT REAL) IS ...
-- IN OUT 사용하여 초기 값 전달
BEGIN
SELECT AVG(sal) INTO my_sal FROM emp;
adjust_salary(7788, my_sal); -- my_sal에 새 값을 할당합니다.
위의 예제는 IN OUT 매개변수를 사용하여 호출 중인 서브 프로그램에 초기 값을 전달하고 호출자에게 업데이트 된 값을 리턴합니다.
상수 선언
상수 선언은 키워드 CONSTANT를 추가하고 상수에 즉시 값을 할당해야 한다는 점을 제외하고 변수 선언과 똑같은 방법을 사용합니다. 그 후에는 상수에 더이상 할당할 수 없습니다.
오늘은 PL/SQL의 기초 정의와 특징, 변수/상수 선언하는 방법에 대해 알아봤습니다. 다음 시간에는 변수와 커서가 가지고 있는 속성에 대해 알아보도록 하겠습니다.
댓글