PL / SQL

PL / SQL

Oracle's Prodecural Language extension to SQL

 

변수 정의, 조건 처리, 반복 처리

절차적 언어로, SQL의 단점을 보완해 좀 더 효율적으로 SQL문을 사용할 수 있게 해준다.

 

 

 

구조

 

 

1. 선언부

DECLARE로 시작

모든 변수나 상수를 선언하는 부분.

 

2. 실행부

BEGIN으로 시작

내용을 기술.

 

3. 예외처리부

EXCEPTION으로 시작

(잘 쓰지 않는다.)

 

 

 

 

'Hello World'를 SQL Developer 콘솔창에 출력해보는 실습을 해 보았다.

 

제일 먼저 환경 변수를 설정해주어야 한다.

기본 값이 off 인데 이것을 바꿔주지 않으면 아무리 명령문을 실행해도 콘솔창에 나타나지 않는다.

set serveroutput on

 

 

이어서 다음과 같이 작성하고 실행한다.

BEGIN
	dbms_output.put_line('Hello World!');
END;
/

 

 

 

 

 

1. 변수 선언

declare로 시작하는 선언부에서 선언해야 한다.

 

1) 스칼라(SCALAR) 형식

2) 레퍼런스 형식

 

 

 

 

1) 스칼라 형식

 

변수 선언시 '=' 이 아닌 ' := ' 로 선언해 주어야 한다.

set severoutput on

DECLARE
	vempno number(4);
	vename varchar2(10);

BEGIN
	vempno := 7788;
	vename := 'SCOTT';
	dbms_output.put_line('사번 / 이름');
	dbms_output.put_line(vempno || ' / ' || vename);
END;
/

 

 

자바에서는 system.out.println(); 을 쓸때 괄호 안에서 문자와 변수를 섞어쓸 때

vempno + " / " + vename 같이 입력을 했지만

이 쪽에서는 || 를 이용해서 연결을 해주어야 한다.

 

vempno + " / " + vename (x)

vempno || ' / ' || vename  (O)

 

 

 

 

2) 레퍼런스 형식

 

여기서 %의 의미는 

emp테이블의 empno 타입과 같은 자료형 형식을 똑같이 한다는 뜻이다.

vempno emp.empno%type;
vename emp.ename%type;


%type : 컬럼 하나를 참조하는 레퍼런스 변수 선언
%rowtype : 로우 전체를 참조하는 레퍼런스 변수 선언

 

 

 

 

 

 

2. SELECT문

 

BEGIN으로 시작되는 선언부 안에 들어가며, into 절이 필요하다.

select 절의 컬럼은 into절의 변수와 1:1 대응을 하기에 개수와 데이터의 형, 길이가 일치해야 한다.

 

Q.  SCOTT 사원의 사번과 이름 검색하기 (레퍼런스 형식으로 변수 선언)

 

set SERVEROUTPUT ON

DECLARE
    vempno emp.empno%type;
    vename emp.ename%type;

BEGIN
    SELECT empno, ename INTO vempno, vename
    FROM emp
    WHERE ename = 'SCOTT';
    
    dbms_output.put_line('사번 / 이름');
    dbms_output.put_line(vempno || ' / ' || vename);    
END;
/

 

 

 

 

 

같은 결과가 나오도록 출력했지만,

스칼라 변수 선언과 레퍼런스 변수는 다른 형식이다.

변수 선언시 타입과 저장 공간을 직접 지정하는 것과, 이미 존재하는 테이블의 형식을 그대로 따오는 것.

 

 

 

 

 

 

 

3. 선택문

 

1) IF ~ THEN ~ END IF

TRUE : THEN 이하의 문장 실행

FLASE or NULL : END IF 다음 문장 실행

 

 

 

사원 테이블에서 SCOTT 사원의 연봉을 구하는 PL/SQL문을 작성해보는 실습

 

set SERVEROUTPUT ON

DECLARE
    vemp emp%rowtype;
    vsal number(7,2);
    
BEGIN
    SELECT * INTO vemp FROM emp WHERE ename='SCOTT';
    IF vemp.comm is null THEN vemp.comm := 0;
    END IF;
    
    vsal := vemp.sal * 12 + vemp.comm; 
    
    DBMS_OUTPUT.PUT_line('사번 / 이름 / 연봉');
    DBMS_OUTPUT.PUT_line(vemp.empno || ' / ' || vemp.ename || ' / ' || vsal);
    
END;
/

 

scott 사원의 일부 컬럼만 뽑아 쓰는 것보다는 rowtype으로 전체 데이터를 참조하는 것이 더 간단하므로 %rowtype

 

 

 

 

 

2) IF - THEN - ELSE - END IF

 

SET SERVEROUTPUT ON

DECLARE
    vemp emp%rowtype;
    vsal number(7,2);
BEGIN
    SELECT * INTO vemp FROM emp WHERE ename = 'SCOTT';
    
    IF vemp.comm is null THEN 
    vsal := vemp.sal*12;
    ELSE
    vsal := vemp.sal+12+vemp.comm;
    END IF;
    
    DBMS_OUTPUT.PUT_line('사번 / 이름 / 연봉');
    DBMS_OUTPUT.PUT_line(vemp.empno || ' / ' || vemp.ename || ' / ' || vsal);
    
END;
/

 

위의 IF ~ THEN ~ END IF; 문과 다른 점은

1)은 comm 컬럼의 null 값을 0으로 치환하여 계산했다는 것이고,

2)는 comm 컬럼이 null 인 경우 계산 방식과 null 이 아닌 경우의 계산 방식을 각각 지정하여 처리했다는 점이다.

 

 

 

 

3) IF ~ THEN ~ ELS IF ~ ELSE ~ END IF

 

SCOTT 사원의 부서번호를 이용해서 부서명을 구하는 PL/SQL문 작성해보기

 

SET SERVEROUTPUT ON

DECLARE
    vemp emp%rowtype;
    vdname varchar2(14);
BEGIN
    SELECT * INTO vemp FROM emp WHERE ename = 'SCOTT';
    
    IF vemp.deptno = 10 THEN vdname = 'ACCOUNTING';
    ELSIF vemp.deptno = 20 THEN vdname = 'RESEARCH';
    ELSIF vemp.deptno = 30 THEN vdname = 'SALES';
    ELSE vemp.deptno = 40 THEN vdname = 'OPERATIONS';
    END IF;
    
    DBMS_OUTPUT.PUT_line('사번 / 이름 / 부서');
    DBMS_OUTPUT.PUT_line(vemp.empno || ' / ' || vemp.ename || ' / ' || vdname);
    
END;
/