201016 Database Procedure

PL/SQL 소개

PL/SQL은 "구조적 쿼리 언어에 대한 절차적 언어 확장"을 의미한다. SQL은 관계형 데이터베이스 관리 시스템(RDBMS)에서 데이터를 추출하고 업데이트하는 데 널리 사용되는 언어이다. PL/SQL은 SQL의 몇몇 한계를 극복하기 위해 SQL 언어에 많은 절차적 구조를 추가한다. 또한, PL/SQL은 Oracle 데이터베이스에서 미션 크리티컬 애플리케이션을 구축하기 위한 보다 많은 포괄적인 프로그래밍 언어 솔루션을 제공한다.

 

PL/SQL은 고도로 구조화되고 읽기 쉬운 언어이다. 이 구조는 코드의 의도를 명확하게 표현한다. 또한, PL/SQL은 배우기 쉬운 언어이다.

 

PL/SQL은 Oracle 데이터베이스 개발을 위한 표준적이고 이식 가능한 언어이다. Oracle 데이터베이스에서 실행 가능한 프로그램을 개발하는 경우 여타의 변경 없이 호환 가능한 다른 Oracle 데이터베이스로 신속하게 이동할 수 있다.

 

PL/SQL은 임베디드 언어이다. PL/SQL은 Oracle 데이터베이스에서만 실행할 수 있다. PL/SQL 외에도 Java, C# 그리고 C++과 같은 다른 프로그래밍 언어를 사용할 수 있다. 그러나 Oracle 데이터베이스와 상호 작용할 때 다른 프로그래밍 언어보다 PL/SQL로 효율적인 코드를 작성하는 것이 더 쉽다. 특히 데이터베이스 성능을 향상시키는 데 도움이 되는 FORALL문과 같은 PL/SQL 특성 구성을 사용할 수 있다.

 

 

PL/SQL 아키텍쳐

다음 그림은 PL/SQL 아키텍쳐를 가리킨다.

 

 

PL/SQL 엔진은 PL/SQL 코드를 바이트 단위의 코드로 컴파일하고 실행 코드를 실행한다. PL/SQL 엔진은 Oracle 데이터베이스 서버 또는 Oracle Forms와 같은 애플리케이션 개발 도구에만 설치할 수 있다.

 

PL/SQL 블록을 Oracle 데이터베이스 서버에 제출하면 PL/SQL 엔진이 SQL 엔진과 협력하여 코드를 컴파일하고 실행한다. PL/SQL 엔진은 SQL 엔진이 SQL문을 처리하는 동안 절차적 요소를 실행한다.

 

 

* 참고 : OracleTutorial What is PL/SQL 발췌

www.oracletutorial.com/plsql-tutorial/what-is-plsql/

What is PL/SQL

 

 

 

PL/SQL (Procedural Language) 프로시저

- 절차적인 데이터베이스 프로그래밍 언어

- 한 번 만들면 메모리에 저장되기 때문에 다음 실행부터는 dml문을 만드는 작업을 하지 않아도 되므로 속도가 빠르다.

  cf) 실무에서 dml문을 직접 만들지 않고 프로시저를 만들어 사용한다.

- 반환형이 없다. (함수는 반환형이 있다)

  함수 : 단일 목적

  프로시저 : 일 처리 목적

 

 

예제1) 기본 서식

 

-- 콘솔창 출력하기 위한 사전 준비 작업
set serveroutput on; --★★

1) 
declare
    -- 변수 선언 및 대입
    a number := 3;
    b number := 5;
begin
    -- 콘솔창 출력(하는 명령어)
    dbms_output.put_line('* 실행결과 *');
    dbms_output.put_line(a);
    dbms_output.put_line(b);
    dbms_output.put_line(a+b);
    -- 결합연산자 ||
    dbms_output.put_line(a || '+' || b || '=' || (a+b));
end;
/

 

결과값 

* 실행결과 *
3
5
8
3+5=8

 

 

수식이 파이썬과 비슷하다. 

대입 연산자가 := 인 점이 다르다. 귀엽게 생겼다.

 

declare (지역 변수를 선언할 때 사용하는 키워드)

 

begin

 

end;

/

 

 

 

예제2) 조건문 - ①

 

declare
    -- 성적프로그램
    uname varchar2(50) := '무궁화';
    kor   number := 100;
    eng   number :=  95;
    mat   number :=  80;
    aver  number := (kor+eng+mat)/3;
    grade varchar2(50) := NULL;
begin
    if aver>=90 then grade:='A';
    elsif aver>=80 then grade:='B';
    elsif aver>=70 then grade:='C';
    elsif aver>=60 then grade:='D';
    else grade:='F';
    end if;
    
    -- 출력
    dbms_output.put_line('* 실행결과 *');
    dbms_output.put_line('이름 : ' || uname);
    dbms_output.put_line('국어 : ' || kor);
    dbms_output.put_line('영어 : ' || eng);
    dbms_output.put_line('수학 : ' || mat);
    dbms_output.put_line('평균 : ' || aver);
    dbms_output.put_line('학점 : ' || grade);
end;
/

 

결과값

* 실행결과 *
이름 : 무궁화
국어 : 100
영어 : 95
수학 : 80
평균 : 91.66666666666666666666666666666666666667
학점 : A

 

 

declare에서 변수를 지정한 후 begin에서 if문을 이용하여 조건문을 작성한다.

 

 

 

예제3) 조건문 - ②

between ~ and 구문으로 바꾸어 학점을 구하시오.

 

declare
    -- 성적프로그램
    uname varchar2(50) := '무궁화';
    kor   number := 100;
    eng   number :=  95;
    mat   number :=  80;
    aver  number := (kor+eng+mat)/3;
    grade varchar2(50) := NULL;
begin
    if aver between 90 and 100 then grade:='A';
    elsif aver between 80 and 89 then grade:='B';
    elsif aver between 70 and 79 then grade:='C';
    elsif aver between 60 and 69 then grade:='D';
    else grade:='F';
    end if;
    
    -- 출력
    dbms_output.put_line('* 실행결과 *');
    dbms_output.put_line('이름 : ' || uname);
    dbms_output.put_line('국어 : ' || kor);
    dbms_output.put_line('영어 : ' || eng);
    dbms_output.put_line('수학 : ' || mat);
    dbms_output.put_line('평균 : ' || aver);
    dbms_output.put_line('학점 : ' || grade);
end;
/

 

결과값

* 실행결과 *
이름 : 무궁화
국어 : 100
영어 : 95
수학 : 80
평균 : 91.66666666666666666666666666666666666667
학점 : A

 

 

 

예제4) 반복문 - ①

 

declare
    -- 구구단 출력
    dan number := 4;
    i   number default 0;
begin
    loop
        i:=i+1;
        dbms_output.put_line(dan || '*' || i || '=' || (dan*i));
        exit when i=9; -- i값이 9이면 반복문 빠져 나옴
    end loop;
end;
/

 

결과값

4*1=4
4*2=8
4*3=12
4*4=16
4*5=20
4*6=24
4*7=28
4*8=32
4*9=36

 

 

Java의 for문에 해당하는 내용이다.

프로시저에서는 loop ~ end loop; 로 문장을 구분하여 사용한다.

 

loop에서 시작하여 i값을 1씩 더해가며 출력한다.

i값이 9가 되었을 때 exit

 

 

 

예제5) 반복문 - ②

 

declare
    -- 구구단 출력
    dan number := 6;
    i   number default 0;
begin
    while i<10 loop
        i:=i+1;
        exit when i=10;
        dbms_output.put_line(dan || '*' || i || '=' || (dan*i));
    end loop;
end;
/

 

결과값

6*1=6
6*2=12
6*3=18
6*4=24
6*5=30
6*6=36
6*7=42
6*8=48
6*9=54

 

 

Java의 while문에 해당하는 내용이다.

프로시저에서는 while ~ loop ~ end loop; 로 문장을 구분하여 사용한다.

 

while (i값 범위) loop로 시작하여 i값을 1씩 더해가며 출력한다.

해당 식의 경우에는 출력문 앞에서 i값이 반복문에서 빠져 나오므로 10이 되었을 때 exit

 

 

 

예제6) 반복문 - ③

 

declare
    -- 구구단 출력
    dan number := 8;
    i   number default 0;
begin
    for i in 1..9 loop -- for 변수 in 시작값..종료값
        dbms_output.put_line(dan || '*' || i || '=' || (dan*i));
    end loop;
end;
/

 

결과값

8*1=8
8*2=16
8*3=24
8*4=32
8*5=40
8*6=48
8*7=56
8*8=64
8*9=72

 

 

for문이다. 

 

 

 

성적 테이블 관련 프로시저

 

-- sungjuk 테이블 
select count(*) from sungjuk;
select * from sungjuk order by sno;

-- sno=43행을 조회하시오
select * from sungjuk where sno=3;

 

 

예제) 프로시저를 이용해서 sno=3행을 조회하시오.

 

declare
    v_sno   number;
    v_uname varchar2(50);
    v_kor   number;
    v_eng   number;
    v_mat   number;
    v_addr  varchar2(30);
    v_wdate date;
begin
    -- SQL문 작성
    select sno, uname, kor, eng, mat, addr, wdate
    into v_sno, v_uname, v_kor, v_eng, v_mat, v_addr, v_wdate
    from sungjuk
    where sno=3;
    
    -- 출력
    dbms_output.put_line(' * 실행결과 * ');
    dbms_output.put_line('번호 : ' || v_sno);
    dbms_output.put_line('이름 : ' || v_uname);
    dbms_output.put_line('국어 : ' || v_kor);
    dbms_output.put_line('영어 : ' || v_eng);
    dbms_output.put_line('수학 : ' || v_mat);
    dbms_output.put_line('주소 : ' || v_addr);
    dbms_output.put_line('작성일 : ' || v_wdate);
end;
/

 

결과값

번호 : 3
이름 : 진달래
국어 : 90
영어 : 50
수학 : 90
주소 : Jeju
작성일 : 20/10/18

 

 

문제) addr 주소 칼럼을 한글로 출력하시오.

 

declare
    v_sno   number;
    v_uname varchar2(50);
    v_kor   number;
    v_eng   number;
    v_mat   number;
    v_addr  varchar2(30);
    v_wdate date;
    v_juso  varchar2(30);
begin
    -- SQL문 작성
    select sno, uname, kor, eng, mat, addr, wdate
    into v_sno, v_uname, v_kor, v_eng, v_mat, v_addr, v_wdate
    from sungjuk
    where sno=3;
    
    if    v_addr='Seoul' then v_juso:='서울';
    elsif v_addr='Jeju'  then v_juso:='제주';
    elsif v_addr='Suwon' then v_juso:='수원';
    elsif v_addr='Busan' then v_juso:='부산';
    end if;
    
    -- 출력
    dbms_output.put_line(' * 실행결과 * ');
    dbms_output.put_line('번호 : ' || v_sno);
    dbms_output.put_line('이름 : ' || v_uname);
    dbms_output.put_line('국어 : ' || v_kor);
    dbms_output.put_line('영어 : ' || v_eng);
    dbms_output.put_line('수학 : ' || v_mat);
    dbms_output.put_line('주소 : ' || v_addr);
    dbms_output.put_line('주소 : ' || v_juso);
    dbms_output.put_line('작성일 : ' || v_wdate);
end;
/

 

결과값

* 실행결과 * 
번호 : 3
이름 : 진달래
국어 : 90
영어 : 50
수학 : 90
주소 : Jeju
주소 : 제주
작성일 : 20/10/18

 

 

 

 

PL/SQL 참조 변수

- %type

  테이블에서 한 개 칼럼의 데이터 타입 및 사이즈를 참조한다.

  형식) 변수명 테이블명.칼럼명%type

- %rowtype

  테이블의 row 타입과 같다는 의미

  형식) 변수명 테이블명%rowtype

 

 

예제1) %type

 

declare
    v_sno   sungjuk.sno%type; -- v_sno 칼럼은 sungjuk 테이블의 sno 칼럼의 자료형과 같다
    v_uname sungjuk.uname%type;
    v_kor   sungjuk.kor%type;
    v_eng   sungjuk.eng%type;
    v_mat   sungjuk.mat%type;
    v_addr  sungjuk.addr%type;
    v_wdate sungjuk.wdate%type;
begin
    select sno, uname, kor, eng, mat, addr, wdate
    into v_sno, v_uname, v_kor, v_eng, v_mat, v_addr, v_wdate
    from sungjuk
    where sno=3;
    
    -- 출력
    dbms_output.put_line(' * 실행결과 * ');
    dbms_output.put_line('번호 : ' || v_sno);
    dbms_output.put_line('이름 : ' || v_uname);
    dbms_output.put_line('국어 : ' || v_kor);
    dbms_output.put_line('영어 : ' || v_eng);
    dbms_output.put_line('수학 : ' || v_mat);
    dbms_output.put_line('주소 : ' || v_addr);
    dbms_output.put_line('작성일 : ' || v_wdate);
end;
/

 

결과값

* 실행결과 * 
번호 : 3
이름 : 진달래
국어 : 90
영어 : 50
수학 : 90
주소 : Jeju
작성일 : 20/10/18

 

 

예제2) %rowtype

 

declare
    sj sungjuk%rowtype; -- sungjuk 테이블의 모든 칼럼의 자료형을 가져온다
begin
    select *
    into sj
    from sungjuk
    where sno=3;
    
    dbms_output.put_line(' * 실행결과 * ');
    dbms_output.put_line('번호 : ' || sj.sno);
    dbms_output.put_line('이름 : ' || sj.uname);
    dbms_output.put_line('국어 : ' || sj.kor);
    dbms_output.put_line('영어 : ' || sj.eng);
    dbms_output.put_line('수학 : ' || sj.mat);
    dbms_output.put_line('평균 : ' || sj.aver);
    dbms_output.put_line('주소 : ' || sj.addr);
    dbms_output.put_line('작성일 : ' || sj.wdate);
end;
/

 

결과값

* 실행결과 * 
번호 : 3
이름 : 진달래
국어 : 90
영어 : 50
수학 : 90
평균 : 77
주소 : Jeju
작성일 : 20/10/18

 

 

예제3) sys_refcursor

         - cursor값을 담을 수 있는 자료형

         - 한 개 이상의 행을 조회할 때

 

-- sungjuk 테이블 모든 행을 조회하시오
select * from sungjuk order by sno desc;

-- 프로시저를 이용해서 sungjuk 테이블의 모든 행을 조회하시오
declare
    v_cursor sys_refcursor; -- 커서를 담는 변수 선언 및 자료형
    rec sungjuk%rowtype;    -- 한 행 담을 수 있는 변수 선언
begin
    open v_cursor for select * from sungjuk order by sno desc;
    loop
        fetch v_cursor into rec;     -- 한 행 인출해서 rec 변수에 대입
        exit when v_cursor%notfound; -- 자료가 없으면 빠져 나감
        dbms_output.put_line(' * 실행결과 * ');
        dbms_output.put_line('번호 : ' || rec.sno);
        dbms_output.put_line('이름 : ' || rec.uname);
        dbms_output.put_line('국어 : ' || rec.kor);
        dbms_output.put_line('영어 : ' || rec.eng);
        dbms_output.put_line('수학 : ' || rec.mat);
        dbms_output.put_line('평균 : ' || rec.aver);
        dbms_output.put_line('주소 : ' || rec.addr);
        dbms_output.put_line('작성일 : ' || rec.wdate);
    end loop;
end;
/

 

결과값

* 실행결과 * 
번호 : 11
이름 : 무궁화
국어 : 80
영어 : 80
수학 : 90
평균 : 83
주소 : Suwon
작성일 : 20/10/18
 * 실행결과 * 
번호 : 10
이름 : 홍길동
국어 : 90
영어 : 90
수학 : 90
평균 : 90
주소 : Suwon
작성일 : 20/10/18

...

 * 실행결과 * 
번호 : 1
이름 : 솔데스크
국어 : 90
영어 : 85
수학 : 95
평균 : 90
주소 : Seoul
작성일 : 20/10/18

 

 

 

프로시저 생성

create or replace procedure 프로시저명

 

프로시저 삭제

drop procedure 프로시저명

 

프로시저 호출

execute 프로시저명

 

 

예제) test 프로시저 생성, 삭제, 호출

 

-- test 프로시저 생성
create or replace procedure test
is
begin
    dbms_output.put_line('테스트');
end;

-- test 프로시저 호출
execute test;

-- test 프로시저 삭제
drop procedure test;

 

결과값

Procedure TEST이(가) 컴파일되었습니다.

테스트


PL/SQL 프로시저가 성공적으로 완료되었습니다.


Procedure TEST이(가) 삭제되었습니다.

 

 

 

 

CSV변환

- 도로명 주소를 테이블에 저장하기

- 공공데이터 data.go.kr에 업로드된 신우편주소를 이용해 테이블을 작성한다.

- zipdoro.csv 준비

 

 

1) zipdoro.csv 내용을 저장할 zipdoro 테이블 생성하기

 

create table zipdoro (
    zipno        char(5)      -- 우편번호
    ,zipaddress  varchar(255) -- 주소
);

commit;

 

 

2) zipdoro 데이터 임포트하기

 

 

 

3) zipdoro.csv 파일을 업로드 후 헤더 체크 미표기

 

 

 

4) 이 상태로 다음

 

 

 

5) 선택된 열 : 열1, 열2 / 사용 가능한 열 : 열3, 열4

 

 

 

6) 이 상태로 다음

 

 

 

7) 이 상태로 완료

 

 

 

8) 이 화면이 나오면 제대로 데이터 임포트 중

 

 

 

9) 완료 화면

 

 

 

10) zipdoro 테이블의 행의 개수 출력

 

select count(*) from zipdoro;

 

결과값

 

 

위의 과정은 'CSV 문서는 이런 식으로 사용하는 것이다'를 보여준 예시

 

 

 

 

성적프로시저

sungjuk 테이블에서 Create / Read / Update / Delete 관련 프로시저 

 

 

1) 행 추가 프로시저 (Create) 

 

create or replace procedure sungjukInsert
(
    --매개 변수(parameter) 선언
    --in 입력 매개 변수
    v_uname in sungjuk.uname%type
   ,v_kor   in sungjuk.kor%type
   ,v_eng   in sungjuk.eng%type
   ,v_mat   in sungjuk.mat%type
   ,v_addr  in sungjuk.addr%type
)
is
begin
    insert into sungjuk(sno,uname,kor,eng,mat,aver,addr)
    values(sungjuk_seq.nextval, v_uname,v_kor,v_eng,v_mat
           , (v_kor+v_eng+v_mat)/3, v_addr);
    commit;
end;

 

위의 문장을 컴파일한 후, 학생 '리틀보이'의 인적 사항을 추가한다.

 

execute sungjukInsert('리틀보이',100,99,95,'Seoul');

 

프로시저가 완료되었는지 확인한다.

 

select * from sungjuk order by sno desc;

 

결과값

 

첫 줄에 sno값이 12로 학생 '리틀보이'가 추가되었음을 확인할 수 있다.

 

 

 

2) 행 수정 프로시저 (Update)

 

- sno=5 행 수정하기

 

create or replace procedure sungjukUpdate
(
    --매개 변수(parameter) 선언
    --in 입력 매개 변수
    v_uname in sungjuk.uname%type
   ,v_kor   in sungjuk.kor%type
   ,v_eng   in sungjuk.eng%type
   ,v_mat   in sungjuk.mat%type
   ,v_addr  in sungjuk.addr%type
   ,v_sno   in sungjuk.sno%type
)
is
begin
    update sungjuk
    set uname = v_uname
       ,kor = v_kor
       ,eng = v_eng
       ,mat = v_mat
       ,tot = v_kor+v_eng+v_mat
       ,aver = (v_kor+v_eng+v_mat)/3
       ,addr = v_addr
       ,wdate = sysdate
    where sno = v_sno;
end;
/

 

위의 문장을 컴파일한 후, sno=5 학생의 인적 사항을 학생 '슈퍼맨'의 인적 사항으로 수정한다.

 

execute sungjukUpdate('슈퍼맨', 100, 100, 100, 'Suwon', 5);

 

프로시저가 완료되었는지 확인한다.

 

select * from sungjuk;

 

결과값

이전 sungjuk 테이블

 

 

수정 후 sungjuk 테이블

 

 

학생 '봉선화'의 데이터가 사라지고 학생 '슈퍼맨'의 데이터가 업로드되었음을 확인할 수 있다.

 

 

 

3) 행 삭제 프로시저 (Delete)

- sno=3 행 삭제하기

 

create or replace procedure sungjukDelete
(
    v_sno   in sungjuk.sno%type
)
is
begin
    Delete from sungjuk    
    where sno = v_sno;
end;
/

 

위의 문장을 컴파일한 후, sno=3 학생의 인적 사항을 삭제한다.

 

execute sungjukDelete(3);

 

프로시저가 완료되었는지 확인한다.

 

select * from sungjuk;

 

결과값

이전 sungjuk 테이블

 

 

삭제 후 sungjuk 테이블