[PL/SQL] 1. 개요

PL/SQL

procedural language/SQL 의 약자로 block 구조의 언어
(Pascal -> Ada -> PL/SQL)

프로그래밍적 요소와 함께 데이터 트랜잭션 처리능력, 데이터에 대한 보안 및 예외처리 기능, 객체지향 등 데이터베이스와 관련된 중요한 모든 기능을 지원해 오라클 데이터베이스와 연관된 업무를 처리하기에 최적화된 언어.

PL/SQL 동작원리

1. 사용자가 pl/sql BLOCK을 실행하면 해당 블록이 오라클 서버로 전달되서 오라클 서버 내 메모리에 상주해 있는 PL/SQL 엔진이 해당 블록을 받게됨.

2. 그 후 해당 블록에 있든 모든 SQL 문장들은 분리가 되서 오라클 서버 프로세스에게 전달되 먼저 수행됨.

3. PL/SQL 엔진은 해당 SQL 문장이 수행되어 결과가 돌아 올 때까지 기다렸다가 그 결과를 받아서 PL/SQL 엔진에 만들어 둔 변수에 데이터를 담아 놓고 변수에 담긴 SQL 실행 결과값을 사용해 나머지 PL/SQL 문장을 실행함.
   (이때 사용되는 변수는 Client가 PL/SQL Block을 작성할 때 미리 계획해서 선언해야 하며 이 변수를 잘못 선언 할 경우 데이터베이스에서 처리된 결과를 PL/SQL 엔진이 사용할 수 없게된다.)

PL/SQL 블록의 기본 구조

• Anonymous Block

• Named Block : procedure, function, package, trigger, object, ..

declare       -- 옵션
    선언부
begin         --  필수
    실행부
exception     -- 옵션
    예외처리부
end;          -- 필수
/

create or replace [procedure|function] 이름
is
 
begin         -- 필수
 
exception     -- 옵션
 
end;          -- 필수
/

PL/SQL 내에서 SELECT 문장 사용하기

ex) 사원번호 입력해 사원이름과 월급 조회하기

create or replace procedure proc1(p_empno number)
  is
    v_ename emp.ename%type;
    v_sal   emp.sal%type;
  begin
    select ename, sal into v_ename, v_sal
    from emp
    where empno = p_empno;
 
     dbms_output.put_line('ENAME : '||v_ename);
     dbms_output.put_line('SAL : '||v_sal);
  exception
    when no_data_found then
      dbms_output.put_line(p_empno||' does not exists!');
  end;
  /

PL/SQL 내에서 DML(insert, update, delete, merge) 문장 사용하기

• INSERT
  ex) 사용자로부터 번호(no) , 이름(name) , 연락처(tel) 값을 입력 받은 후 t_plsql 테이블에 입력하는 PL/SQL 문장을 작성하시오.

declare
 
v_no   NUMBER := &no;
v_name VARCHAR2(10) := '&name';
v_tel  NUMBER := &tel;
 
begin
 INSERT INTO t_plsql VALUES(v_no, v_name, v_tel);
end;
/
 
SCOTT>SELECT * FROM t_plsql ;
SCOTT>commit;

• UPDATE, DELETE

SQL>BEGIN
2     UPDATE t_plsql
3     SET name='CCC'
4     WHERE no = 2 ;
5 END ;
6 /
SQL>commit;
 
SQL>BEGIN
2     DELETE FROM t_plsql
3     WHERE no = 1 ;
4 END ;
5 /
SQL>commit;

• MERGE

 
SQL>SELECT * FROM t_merge1;
  NO  FRUIT
----- -------------
    1 APPLE
    2 BANANA
 
 
SQL>SELECT * FROM t_merge2 ;
  NO  FRUIT
----- -------------
    3 MELON
    1 CHERRY
 
 
SQL> BEGIN
2      MERGE INTO t_merge2 t2
3      USING t_merge1 t1
4      ON(t1.no = t2.no)
5   WHEN MATCHED THEN
6      UPDATE SET
7      t2.fruit = t1.fruit
8   WHEN NOT MATCHED THEN
9      INSERT VALUES(t1.no , t1.fruit);
10 END ;
11 /
 
SQL>SELECT * FROM t_merge2 ;
   NO FRUIT
----- ----------
    3 MELON
    1 APPLE     <- 원래 CHERRY 였는데 UPDATE 됨
    2 BANANA    <- 이 줄은 추가 됨
 
SQL>commit;

PL/SQL 에서 사용하는 주요 용어와 연산자들

• 식별자: 객체에게 부여되는 이름 (테이블명, 변수명등)

• 구분자: 특별한 의미를 가진 기호




기호	의미
+,-,*,/,=,<>,!=	연산자
@	원격 엑세스 표시자
--	단일행 주석표시
/*, */	주석 시작과 종료
:=	할당 연산자
ll	연결 연산자
;	명령문 종료




• 리터럴 : 변수에 할당되는 모든 값 (식별자가 아닌 모든 문자, 숫자, 부울, 또는 날짜 값)