PL/SQL 이란?
- Procedural Language / Sturctured Query Language
- SQL에 프로그래밍 언어 설계 기능을 절차적으로 추가한 것
- 오라클사가 데이터베이스에서 SQL에 대해 절차적 로직을 실행하는 방법을 제공하기 위해 개발한 절차적 언어
- 데이터 캡슐화, 예외 처리, 정보 숨김, 객체 지향 등의 현대 S/W 공학 기능 제공
- SQL문을 블록 구조 및 프로시저 단위 코드에 포함시킬 수 있는 강력한 트랜잭션 처리 언어
PL/SQL 처리과정
- 오라클 선행 컴파일러에서 PL/SQL 블록(코드)를 제출하면 Oracle Server 내의 PL/SQL 엔진이 이를 처리한다.
- PL/SQL 엔진은 블록 내의 SQL문을 분리하여 하나씩 SQL문 실행자로 전송한다.
- PL/SQL 코드는 Oracle Server 에 저장할 수 있으며, 이름 앞에 Stored를 붙여 부른다. (Stored Procedure)
- Oracle Developer 와 같은 오라클 툴에는 Oracle Server의 엔진과는 별도로 자체 PL/SQL 엔진이 존재한다.
실습에 필요한 권한
- CREATE PROCEDURE
- CREATE SESSION
- CREATE TABLE
- CREATE TRIGGER
- CREATE VIEW
- CREATE TYPE
실습에 필요한 패키지
- DBMS_OUTPUT
- DBMS_SQL
- UTL_FILE
- DBMS_PIPE
- DBMS_ALERT
※ 패키지 확인
SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER='SYS' AND OBJECT_TYPE='PACKAGE';
PL/SQL Block 구조
**********************************************************************************
DECLARE
변수 선언문;
BEGIN
프로그램 코드;
EXCEPTION
예외 처리문;
END;
************************************************************************************************
- 변수선언문 : 블럭에서 사용할 변수를 정의. 커서 정의와 중첩된 PL/SQL 프로시저 및 함수도 여기서 정의.
- 프로그램코드 : 블럭을 구성하는 PL/SQL 문.
- 예외처리문 : 런타임 오류나 예외 발생시 트리거되는 프로그램 코드.
- 각 블럭에서 문장의 끝을 알리기 위해 반드시 세미콜론(;)을 붙인다.
- BEGIN 내에 다른 블럭을 내포할 수 있다.
[예제1] 간단한 작성과 실행
**********************************************************************************
DECLARE
x NUMBER;
BEGIN
x := 65400;
END;
/
************************************************************************************************
- 맨 끝의 '/' 슬래시는 SQL*Plus에게 PL/SQL 코드의 입력이 끝났음을 알리고 이를 오라클 데이터베이스로 보내는 역할.
- 출력은 DBMS_OUTPUT 패키지가 담당한다.
- 패키지 안에 dbms_output.put_line 프로시저 사용.
- 출력을 SQL*Plus 에서 보려면
SQL> SET SERVEROUTPUT ON 을 먼저 실행해야 한다.
************************************************************************************************DECLARE
x NUMBER;
BEGIN
x := 65400;
dbms_output.put_line('The variable x = ');
dbms_output.put_line(x);
END;
/
************************************************************************************************
- 간단한 내용은 vi editor를 통해 수정하면 되겠지만, Buffer는 바로 직전의 SQL문만 저장하고 있기 때문에 긴 PL/SQL 프로시저는 반드시 텍스트파일로 만들어서 실행시키는 형식을 취하도록 한다.
$ vi /export/home/oracle/plsql/test.sql
내용기입
:wq
SQL> @/export/home/oracle/plsql/test
[예제3] 함수 작성하기
- PL/SQL을 사용하여 Stored 함수와 Stored 프로시저를 작성한다. 방금까지 작성했던 코드를 Stored 함수로 캡슐화 시키면 그것을 한번만 컴파일하고 데이터베이스에 저장해 놓았다가 나중에 다시 사용할 수 있다.
- DB내에 stored 함수를 만들어 놓으면 나중엔 그 함수만 불러다 쓰면 된다.
************************************************************************************************CREATE OR REPLACE FUNCTION ss_thresh
RETURN NUMBER AS
x NUMBER;
BEGIN
x := 65400;
RETURN x;
END;
/
************************************************************************************************
- OR REPLACE 는 동일한 함수가 존재하면 덮어쓰라는 의미이다.
- 1~2줄 CREATE 부터 AS 까지가 DECLARE 부분이다.
- 함수를 생성하는 것은 오라클이고 SQL*Plus는 결과만 보여줄 뿐이다.
SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME = 'SS_THRESH';
[결과 확인]
- 함수를 작성하고 컴파일을 하였으니 이제 사용만 하면 된다. 함수이지만 ( )를 사용하지 않는 이유는 인수가 없기 때문.
SQL> SELECT SS_THRESH FROM DUAL;
- 함수 생성시 STATUS가 INVALID 로 되어 있어 사용할 수 없기 때문에 코드에 잘못된 부분이 있는지 확인하고 수정 후 VALID 상태로 변경해야 한다. 오래 사용하지 않는 함수나 프로시저, 패키지도 INVALID 상태가 될 수 있다.
[에러확인]
- 위의 예제에서 실수로 입력이 잘못되었을 경우 에러를 확인하는 방법은 에러가 나고 바로 SHOW ERRORS 를 쳐보면 된다.
