DB/Oracle

Oracle DB9_PL/SQL 시작

Codezoy 2019. 9. 26. 09:00

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;
/

저작자표시 (새창열림)