PL/SQL

* 학습 내용

- PL/SQL 정의

- 기본 문법

- 예외처리

- 중첩문

- 다른 테이블의 컬럼 타입 가져와서 사용하기

- 다른 테이블에서 가져온 컬럼을 테이블에 삽입하기

- 조건식: 단일 조건식, 다중 조건식

 

---

 

1. PL/SQL이란?

- 오라클만의 언어

- SQL을 확장한 절차적 언어(Procedural Language)

- 유저 프로세스가 PL/SQL 블록을 보내면, 서버 프로세서는 PL/SQL Engine에서 해당 블록을 받고 SQL과 Procedural를 나눠서 SQL은 SQL Statement Executer로 보낸다.

- PL/SQL 프로그램의 종류는 크게 Procedure, Function, Trigger 로 나뉘어 진다.

출처: https://goddaehee.tistory.com/99 [갓대희의 작은공간]

 

 

 

2. 기본 문법

2.1 PL/SQL 실행을 위한 필수 셋팅

serveroutput이 off로 기본 설정되어 있으므로 on으로 설정해준다. 아래는 해당 명령어.

set serveroutput on

 

 

2.2 기본 문법

1) 기본 문법

declare
    변수 선언
begin
    로직 처리
end;
/

pl/sql은 /까지가 문법이므로 /까지 꼭 쳐줘야 한다.

 

 

 

- 실행 결과 출력문

dbms_output.put_line(name);

파이썬으로 번역하면 print(name) 과 동일한 의미.

||는 파이썬에서 , 와 같은 의미

ex)
declare
    v_no integer;
begin
    v_no := 10;
    dbms_output.put_line('결과' || v_no);
end;
/

 

(위 문장 파이썬 ver)
no = 10
print('결과', v_no)

 

- 선언문에서 변수값도 선언하기

declare
    v_no integer := 10;
    name varchar2(10);
begin
    name := '유재석';

    dbms_output.put_line('결과' || v_no);
    dbms_output.put_line('결과' || name);
end;
/

 

 

2) 예외처리

- 예외처리(exxception) :프로그램 중지를 방지하기 위한 해결책

declare
    v_no integer := 10;
begin
     dbms_output.put_line('결과' || v_no);

    v_no := 10/0;

    exception
        when others then
            dbms_output.put_line('예외 발생');

        dbms_output.put_line('결과' || v_no);
end;
/

 

 

3) 중첩 block

declear
    v_global varchar2(10) := 'g';
begin
    dbms_output.put_line(v_global);
    declear
         v_local varchar2(10) := 'l';
    begin
        dbms_ouput.put_line(v_global);  -- 정상실행
        dbms_ouput.put_line(v_local);    -- 정상실행
    end;
    dbms_ouput.put_line(v_global);
    dbms_ouput.put_line(v_local);  -- <- 이 코드는 오류 발생. 중첩 내부 지역변수가 지역에서 벗어나 선언되서. 
end;
/

 

 

4) 다른 테이블의 컬럼 타입을 그대로 사용하고 싶을 때(1)

특정테이블명.특정컬럼명%type : db의 특정 컬럼의 타입 의미

특정테이블명.특정컬럼명%type

-- emp01 테이블이 있으면 drop
drop table emp01;

-- 신규 테이블 생성(emp01) 및 emp 테이블의 컬럼 타입만 복사
create table emp01 as select * from emp;

-- 다른 테이블 컬럼 타입 그대로 사용하기(1)
declare
    v_empno emp01.empno%type;
    v_empname emp01.ename%type;
begin
    select ename, empno
        into v_ename, v_empno
    from emp01
    where empno=7369;

    dbms_output.put_line(v_ename || ' ' || v_empno);
end;
/

 

 

5) 다른 테이블의 컬럼 타입을 그대로 사용하고 싶을 때(2)

특정테이블명%rowtype : db의 특정 테이블의 모든 데이터 타입을 활용하겠다는 의미

특정테이블명%rowtype

declare
    v_rows emp01%rowtype;
begin
    select * 
        into v_rows
    from emp
    where empno = 7369;

    dbms_output.put_line(v_rows.ename);
end;
/

여기서 변수할 당시 칼럼수와 데이터 타입이 begin문 안에서 가져오는 테이블의 칼럼수와 데이터 타입이 일치해야함

그렇지 않으면 오류 발생.

또한 dbms_output.put_line(v_rows); 는 오류 발생. 원하는 컬럼까지 지정해줘야 오류 발생하지 않음

즉, dbms_output.put_line(v_rows.ename); 과 같이 입력해줘야 오류가 발생하지 않음.

 

 

6) 위 5번에서 가져온 컬럼 데이터를 테이블에 insert하기

-- 기존에 존재하는 emp05 테이블 drop하기
drop table emp05;

-- 신규 테이블 생성(emp05) 및 emp 테이블의 컬럼 타입만 복사
create table emp05 as select * from emp where 1=0;

-- 다른 테이블(emp)에서 가져온 특정 데이터(empno=7369)를 신규 생성한 테이블(emp05)에 insert 하기
declare
    v_row emp05%rowtype;
begin
    select *
        into v_row
    from emp
    where empno = 7369;

    insert into emp05 values v_row;
end;
/

-- emp05에 들어간 데이터 확인
select * from emp05;

 

 

7) 조건식(1): 단일 조건식

declare

begin
    if (조건) then
        조건식이 true인 경우 실행되는 블록
    end if;

end;
/

ex)

declare
    v_emp emp%rowtype;
    total_sal number(7, 2);

begin

    select empno, ename, sal, comm

        into v_emp.empno, v_emp.ename, v_emp.sal, v_emp.comm

    from emp

    where ename='SMITH';

 

    if (v_emp.comm is null) then

        v_emp.comm := 0;

    end if;

 

    total_sal := v_emp.sal*12 + v_emp.comm;

    dbms_output.put_line(total_sal);

end;

/

 

8) 조건식(2): 다중 조건

declare

begin
    if (조건1) then
        조건1이 true인 경우 실행되는 블록
    elsif (조건2) then
        조건2가 true인 경우 실행되는 블록
    end if;

end;
/

ex)

declare

     --실행시마다 새로운 데이터 입력받는 동적변수 할당문법

     --(v 변수명은 다른 변수명 가능)

    ck_empno emp.empno%type := &v;  

    v_empno emp.empno%type;

    v_deptno emp.deptno%type;

    v_dname varchar2(10);

begin

    select empno, deptno

        into v_empno, v_deptno

    from emp

    where empno=ck_empno;

 

    if (v_deptno=10) then   

        dbms_output.put_line('ACCOUNT');

    elsif (v_deptno=20) then    

        dbms_output.put_line('RESEARCH');

    else

        dbms_output.put_line('None');

    end if;

end;

/