PL/SQL

PL/SQL

 

PL / SQL란?

 

 - PL(Procedural Langugae) / SQL 의 약어

 

 - 오라클 DB 환경에서 실행되는 절차적인 데이터베이스 프로그래밍 언어

 

 - 표준 SQL과 3세대 언어의 강력한 일부 기능을 포함한 SQL의 확장 언어

 

 - PL/SQL 에서는 프로그램 단위를 블록(BLOCK)이라 부르며, 애플리케이션 로직들을 작성

 

---

 

오라클 환경에서 PL/SQL을 학습하는 이유

 

 - 오라클 개발 도구를 수행하는 모든 프로그래밍의 기초

 

 - 클라이언트가 아닌 서버 상에서 프로세스를 수행하는데 PL/SQL을 사용함

 

 - PL/SQL을 사용하면 업무 규칙이나 복잡한 로직을 캡슐화(Encaptulation)할 수 있어,

 

   모듈화(Modularity)와 추상화(Abstraction) 가능

 

 - 데이터베이스 트리거를 통하여 데이터베이스 무결성을 제약하는 복잡한 규칙의 코딩과 변경 내역,

  

   데이터를 복사

 

 - PL/SQL 은 독립적인 플랫폼 수준을 제공

 

 

---

PL/SQL에서 제공하는 명령문

 

 1. 모든 SQL문

 

 2. 변수 및 상수 등의 선언문

 

 3. 대입문 

 

 4. 조건 판단문

 

 5. 제어 흐름문

 

 6. 반복 처리문

 

---

PL/SQL로 작성할 수 있는 것은

 

1. SQL*Plus 스크립트(script)

 

2. 프로시저(Procedure), 함수(Function) 서브 프로그램

 

3. 패키지(Package)

 

4. 데이터베이스 트리거(Database Trigger)

 

5. 애플리케이션 로직(Application Logic)

 

---

PL/SQL 블록 구조

DECLARE 선택 [변수와 상수, 프로시저 함수 서브프로그램, 커서 등을 선언]
 
   선언문 ...
  
BEGIN 필수  처리할 명령문들을 절차적으로 기술
              - 모든 SQL문
  실행문...   - 대입문, 반복 처리문, 조건 판단문, 제어 흐름문
              - 커서 처리문

EXCEPTION 선택 [오류 처리에 관한 예외처리 명령문을 기술]

   예외 처리문...
   
END 필수  
              

 

---

PL/SQL 연산자

 

 - 다른건 거의 비슷하지만 대입 연산자가 일반적인 프로그래밍 언어와 다름

JAVA =

PL/SQL :=

 

---

PL/SQL 변수 선언

JAVA : 타입 변수명 (String str;)

PL/SQL : 변수명 타입 (deptno NUMBER(2);)

  * PL/SQL 코드 라인의 끝의 기술은 JAVA와 동일하게 세미콜론(;) 을 기술한다.

---

실습

- Hello World 출력

 

 PL/SQL 블록의 종료 표시하는 문자열 : /

 

 SQL 의 종료 문자열 : ;

set SERVEROUTPUT on;

declare
    msg VARCHAR(50);                  -> 선언부
begin
    msg := 'Hello, World!';
    dbms_output.put_line(msg);        -> 실행문
end;
/    -> 해당 블럭의 끝에 기술

 * set SERVEROUTPUT on;  -> 콘솔에 결과를 출력할 수 있게 하는 구문

 

 * DBMS_OUTPUT.PUT_LINE( )  -> 출력문

 

 

- 부서 테이블에서 10번 부서의 부서번호와 부서 이름을 PL/SQL 변수에 저장하고 변수 출력

declare
    v_deptno NUMBER(2);
    v_dname VARCHAR2(14);
begin
    select deptno, dname into v_deptno, v_dname     -> deptno -> v_deptno에 저장 dname -> v_dname저장
    from dept
    where deptno = 10;
    
    DBMS_OUTPUT.put_line(v_deptno || ' ' || v_dname);   || -> 문자열 결합
end;
/

 

---

PL/SQL 참조타입

 

- dept 테이블의 deptno, dname을 조회하여 변수에 담는 과정

 

- deptno, dname의 값을 담을 변수의 데이터 타입을 직접 명시하는게 아니라 dept 테이블의 컬럼 타입을 참조해서

 

  변수 선언

 

 -> 실습을 통해 해보자

 

set SERVEROUTPUT on;

declare
    v_deptno dept.deptno%type;    -> v_deptno변수의 데이터 타입은 dept테이블의 deptno의 데이터 타입
    
    v_dname dept.dname%type;      -> 마찬가지
begin
    select deptno, dname into v_deptno, v_dname
    from dept
    where deptno = 10;
    
    DBMS_OUTPUT.put_line(v_deptno || ' : ' || v_dname);
end;
/

  * %type : 해당 테이블 컬럼의 데이터 타입을 참조하겠다.

 

---

프로시저 블록 유형

 

 1. 익명 블럭 (이름이 없는 블럭)

 

   - 재사용이 불가능하다 ( IN-LINE VIEW )

   

   * 위에서 썼던 것들이 익명 블럭

 

 2. 프로시저 (이름이 있는 블럭)

 

   - 재사용이 가능하다 ( VIEW )

 

   - 이름이 있다

 

   - 리턴 값이 있다.

 

---

프로시저 형태

 

 create on replace procedure 프로시저 이름 is (IN param, OUT param, IN OUT param) 
 
    선언부 (declare절이 별도로 없다)
    
    begin
    
    exception (옵션)
    
 end;
 /

 

---

프로시저 실습

 

 - 프로시저 명 : printdept_p

 

 - 인자로 dept 테이블의 deptno을 받아서 해당 부서번호에 해당하는 부서이름과 지역정보를 콘솔에 출력

 

create or replace procedure printdept_p(p_deptno IN dept.deptno%type) is

    v_dname dept.dname%type;
    
    v_loc dept.loc%type;
    
begin

    select dname, loc into v_dname, v_loc
    
    from dept
    
    where deptno = P_deptno;
      
    DBMS_OUTPUT.PUT_LINE(v_dname || ', ' || v_loc);
end;
/