Oracle DB9_PL/SQL 시작

Goals

• PL/SQL, PL/SQL의 필요성, PL/SQL 블록의 기본 구조, SET SERVEROUTPUT ON
• 변수, DECLARE, ASSIGN, INITIALIZE, 상수, LITERAL
• DECLARE에 NOT NULL 삽입, PROMPT, SELECT 값을 변수에 삽입

Notes

• PL/SQL(Procudural Language extension to SQL)

-- SQL에 대한 절차적 언어 확장 기능

• PL/SQL의 필요성

-- 직원(emp) 테이블에서 부서별 직원들의 정보를 출력

select * from emp where deptno = ? ;

-- 어떤 부서 번호들이 있는지를 확인하기 위해서 부서(dept) 테이블을 확인

select deptno from dept;

-- 알아낸 부서 번호를 가지고 select문을 부서 갯수만큼 실행

select * from emp where deptno = 10 ;

select * from emp where deptno = 20 ;

select * from emp where deptno = 30 ;

select * from emp where deptno = 40 ;

-- 하나의 업무를 완수하기 위해 작업을 절차적으로 진행해야 할 필요가 있다.

• PL/SQL 블록의 기본 구조

DECLARE :      선언부, 선택사항

변수 선언; 

BEGIN :      실행부, 필수사항

실행문; 

EXCEPTION :      예외처리부, 선택사항

예외처리문; 

END; :      필수사항

ex)

set serveroutput on;   -- 시작할때 반드시

declare      

    v_num number := 19950801;  --변수 선언

begin

    dbms_output.put_line('v_num 값 = ' || v_num);  -- 출력

end;

/           --> sql plus에서는 '/'로 마무리 해주어야 함.

주의 : DECLARE, BEGIN, EXCEPTION 에는 세미콜론(;)을 사용하지 않음

END와 각 문장들 ( 변수 선언 문장, 실행문, 예외 처리문, ... ) 끝에는 세미콜론을 반드시 사용해야 한다.

PL/SQL 블록 내부에서도 주석들을 사용할 수 있음.

SQP PLUS에서는 PL/SQL 블록이 끝났음을 명시하기 위해서 반드시 '/'로 마무리 해주어야 함.

• PL/SQL 변수

변수(Variable) : 프로그램(프로시저)에서 사용되는 데이터를 저장하는 공간.

-- 변수는 DECLARE 블록 안에서 아래와 같은 형식으로 선언/초기화를 한다.

1) 변수를 선언하면서 동시에 값을할당(초기화)하는 경우

변수이름 데이터타입 := 값;

2) 변수를 선언만 하고, 값을 할당(초기화)하지 않는 경우

변수이름 데이터타입;

 

   ※ 초기화되지 않은 변수는 BEGIN 블록 안에서 반드시 초기화를 해야한다.

   ※ PL/SQL의 변수 이름은 대/소문자를 구분하지 않음. 

   ※ 변수에 저장된 데이터는 대/소문자를 구분.

EX)

-- v_name는 초기화되지 않은 변수. v_name은 null.

    v_name := '오쌤';

    dbms_output.Put_line('이름 : '|| v_name);

DECLARE     -- 변수 선언부

            -- 변수 선언과 동시에 초기화

    v_age number(3) := 16;

    

            --변수 선언만!

    v_name varchar2(20);

BEGIN       -- 실행부

    dbms_output.put_line('나이 : '|| v_age); -- 화면(console)에 출력

    -- v_name는 초기화되지 않은 변수. v_name은 null.

    v_name := '오쌤';

    dbms_output.Put_line('이름 : '|| v_name);

END;

DECLARE , DECLARATION (선언) : 변수의 이름과 변수의 데이터 타입을 선언하는 것.

ASSIGN, ASSIGNMENT (할당) : 선언된 변수의 값을 저장/변경하는 것.

INITIALIZE, INITIALIZATION (초기화) : 변수에 최초로 값을 저장(할당)하는 것.

& 연산자 : 프롬프트 창에서 입력받은 값을 저장하는 변수를 참조(reference) 

accept a_length prompt '가로 길이 입력 : ';

accept a_width  prompt '세로 길이 입력 : ';

declare

     v_width  number := &a_length;

     v_height number := &a_width;

     v_area   number;

begin

    v_area := v_width * v_height;

    dbms_output.put_line('가로의 길이는 ' || v_width);low

    dbms_output.put_line('세로의 길이는 ' || v_height);

    dbms_output.put_line('사각형의 넓이는 ' || v_area);

end;

• 상수(constant) : 값이 한번 저장(할당)되면 값을 변경할 수 없는 변수 . 선언과 동시에 초기화를 해야 한다.

CONST1 CONSTANT NUMBER := 3.14;

>> 값이 한번 저장되면 변경 불가

>> 변수이름 CONSTANT 데이터타입 := 할당값

• LITERAL : 프로그램(프로시저)에서 정적으로 사용되는 값. 다음에서 '1'이 LITERAL

VAR1 NUMBER := 1;

     변수이름  데이터형식  리터럴

• DECLARE(선언)시 NOT NULL 삽입 :  

v_num     number  not null := 20;

       변수이름 데이터형식 not null := 리터럴

PROMPT 입력 받기

ACCEPT 변수이름 PROMPT '문자열'

SELECT 칼럼명 INTO 변수명 FROM 테이블명 WHERE 조건 ;

accept p_empno prompt '사번을 입력하세요 : ';

DECLARE

    v_name      varchar2(10) ;

    v_empno     number(20) := &p_empno ;

    v_deptno    number(10) ;

    v_deptname  varchar2(10);

BEGIN

    select ename, deptno into v_name, v_deptno

                         from emp              where empno = v_empno;

    select d.dname  into v_deptname

                    from emp e, dept d

                    where upper(e.empno) = v_empno and e.deptno = d.deptno; 

                    

    dbms_output.put_line('당신의 이름은 : '|| v_name );

    dbms_output.put_line('당신의 부서이름은 : '|| v_deptname);

    dbms_output.put_line('당신의 부서번호는 : '|| v_deptno);

END;

--1

--이름을 입력 받아서, 사번 부서번호를 검색해서, 사번 이름 부서번호를 출력

accept p_ename char prompt '이름을 입력하세요 : ';

DECLARE

    v_empno     number(4,0);

    v_deptno    number(2,0);

    v_ename     varchar2(100) := trim(upper('&p_ename');)

BEGIN

    select empno, deptno

    into v_empno, v_deptno  from emp

    where upper(ename) = v_ename;

    dbms_output.put_line('당신의 이름은 : '|| v_ename );

    dbms_output.put_line('당신의 사번은 : '|| v_empno);

    dbms_output.put_line('당신의 부서번호는 : '|| v_deptno);

END;

/

--2

-- 이름을 입력 받아서

-- emp, dept 테이블을 이용해서(join)

-- 'scott은 DALLAS의 RESEARCH 부서에서 근무합니다.'와 같은 형식으로 출력

accept p_ename char prompt '이름을 입력하세요 : ';

DECLARE

    v_ename     varchar2(100) := trim(upper('&p_ename'));

    v_deptno    number(2,0);

    v_loc       varchar(13);

    v_dname     varchar(14);

BEGIN

    select d.dname, d.loc into v_dname, v_loc

    from dept d, emp e

    where upper(e.ename) = v_ename and e.deptno = d.deptno; 

    

    dbms_output.put_line(v_ename||'는 '||v_loc||'의 '||v_dname||'부서에서 근무합니다.' );

END;

/