[Oracle, PL/SQL] 오라클 프로시저 기초 (업데이트 2017-11-28)

개요

오늘은 PL/SQL 의 기초 를 간단히 정리해보고자 합니다. 

PL/SQL 이란?

PLSQL 은 procedure language & structured query language 의 약자 라고 하네요. (위키백과 https://en.wikipedia.org/wiki/PL/SQL)

보통 SQL (Structured Query Language) 에 포함되지 않는 언어까지 포함되어 있는 것을 말합니다. 어느정도 표준화가 진행된 SQL 과는 다르게, 각 데이터베이스 제품마다 차이가 있을 수 있습니다. 

조금 더 깊게 들어가자면, SQL 은 일종의 질의문으로써, '테이블에 가서 이것이것 데이터를 내놔라', '이 데이터를 테이블에 넣어라' 와 같은 의미를 가진 언어입니다. 

PL/SQL 은 여기서 좀 더 확장되어서 '이럴때 이렇게 처리하고 여기다 넣고, 여기서 빼고, 뺑뺑이 돌리고 이건 보여주고 이건 지우고...' 와 같은 언어라고 생각하시면 되겠습니다. 큰 차이로는 프로그래밍 언어처럼 절차적인 특성을 갖고 있습니다. (SQL만으로는 절차적이지 않습니다)

일반적으로는 PROCEDURE (프로시저) 와 FUNCTION (함수), TRIGGER (트리거) 등을 구성하게 됩니다. 구문을 동작함과 동시에 이러한 것들이 생성이 되면서 데이터베이스 안에 내장되게 됩니다. '생성하는 구문' 과 '실행되는 구문' 이 따로 있을 수 있다는 점을 예상해볼 수 있습니다.

기본 구성

[프로시저 의 기본 형태]

CREATE OR REPLACE PROCEDURE 프로시저명(
변수 INOUT(IN/OUT/INOUT 세가지입니다) 데이타형
.
.
{ 이 부분은 인수값 (넘겨주는 값) 에 대한 부분이 들어갑니다) }
) 
IS 

{ 여기에는 보통 변수 선언 등이 들어갑니다. 스크립트의 declare 구문이 들어간다고 보면 됩니다.  }

BEGIN

{ 동작되는 PLSQL 구문이 본격적으로 들어가는 영역 }

END;
/

[TEST (STUDENT varchar2, COUNT number) 형태의 프로시저]

CREATE OR REPLACE PROCEDURE TEST(
    STUDENT IN VARCHAR2
,   COUNT IN NUMBER
)IS
BEGIN
END;
/

이름이 'TEST' 이고, 인수로 STUDENT (VARCHAR2 형태), COUNT(NUMBER 형태) 를 받는 프로시저를 만드는 구문입니다. 아직까지는 아무런 동작을 하지 않는 형태만 갖고 있는 프로시저 입니다. 

이 구문을 쿼리가 동작되는 환경에서 돌려주시면, 어딘가로 저장이 되어집니다. 그 후에는 호출해서 사용하게 됩니다. 구문의 앞에서 'CREATE OR REPLACE' 라는 부분이 있는 이유가 일종의 DDL 구문처럼 동작된다고 보시면 됩니다. 테이블을 생성하는 것처럼 프로시저 를 생성하게 됩니다. 

보통은 ORACLE 환경에서는 SQL PLUS 를 이용하거나, Toad for Oracle 을 이용하거나, 그 외의 클라이언트 툴 들을 이용하게 됩니다. 

이제 BEGIN 과 END 사이에는 필요한 내용을 기술합니다.

예를 들어, 장치 목록 테이블 (DEVICE_LIST) 에서 장치 이름을 소문자로 바꾸는 작업을 한다고 합시다. 

CREATE OR REPLACE PROCEDURE PRC_TEST 
IS 
BEGIN 
  -- 장치명을 UPPER CASE 를 해준다. 첫문자만 UPPER 해주려면 INITCAP 함수를 사용해볼 수 있다.
  UPDATE DEVICE_LIST
  SET DEVICE_NAME = INITCAP(DEVICE_NAME);
END;
/

이렇게 구문을 작성하시면 됩니다. 

테스트 예시

테이블을 생성하는 쿼리

CREATE TABLE DEVICE_LIST (
		DEVICE_ID INT NOT NULL,
		DEVICE_NAME VARCHAR2(20),
		CONSTRAINT DEVICE_LIST_PK PRIMARY KEY(DEVICE_ID)
);

값을 INSERT 하고 난 뒤에, UPPERCASE (여기서는 INITCAP 함수 사용)

CREATE OR REPLACE 
PROCEDURE PRC_TEST
IS

BEGIN
-- delete data
DELETE FROM DEVICE_LIST;

-- insert data
INSERT INTO DEVICE_LIST(DEVICE_ID,DEVICE_NAME) VALUES (1,'tv');
INSERT INTO DEVICE_LIST(DEVICE_ID,DEVICE_NAME) VALUES (2,'mouse');
INSERT INTO DEVICE_LIST(DEVICE_ID,DEVICE_NAME) VALUES (3,'keyboard');
INSERT INTO DEVICE_LIST(DEVICE_ID,DEVICE_NAME) VALUES (4,'monitor');
INSERT INTO DEVICE_LIST(DEVICE_ID,DEVICE_NAME) VALUES (5,'regrigerator');
INSERT INTO DEVICE_LIST(DEVICE_ID,DEVICE_NAME) VALUES (6,'air conditioner');
INSERT INTO DEVICE_LIST(DEVICE_ID,DEVICE_NAME) VALUES (7,'phone');

-- 장치명을 UPPER CASE 를 해준다. 첫문자만 UPPER 해주려면 INITCAP 함수를 사용해볼 수 있다.
UPDATE DEVICE_LIST
SET DEVICE_NAME = INITCAP(DEVICE_NAME);

END;

참조 링크

https://www.techonthenet.com/oracle/procedures.php