20. PL/SQL, 프로시저(Procedure)

○ PL/SQL(Procedural language) 프로시저

    - 절차적인 데이터베이스 프로그래밍 언어
    - 변수, 조건문, 반복문

    - 콘솔창 출력하기 위한 사전 준비작업

set serveroutput on;

   

 

○ PL/SQL 기본문법

    ① 기본 문법

        - 형식) declare 변수 선언 및 대입

                 begin 실행할 명령어  

                 end;

        - 콘솔창 출력 : dbms_output.put_line(출력할 내용);

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;

    ② 조건문

        - 형식) if 조건1 then 조건1을 만족하면 실행할 명령어  

                  elsif 조건2 then 조건2를 만족하면 실행할 명령어 

                  elsif 조건3 then 조건3을 만족하면 실행할 명령어 

                  else 모두 아닐 때 실행할 명령어 

declare
    uname varchar2(50) := '무궁화';
    kor number := 100;
    eng number := 95;
    mat number := 80;
    aver number := (kor+eng+mat)/3;
    grade varchar2(10) := 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('평균 : ' || round(aver, 2));
    dbms_output.put_line('학점 : ' || grade);
end;

    ③ 반복문

        - 형식) while 조건 loop

                  실행할 명령어

                 (exit when반복을 마무리 할 조건)

                  end loop;

declare
    dan number := 4;
    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;

 

○ sungjuk테이블 관련 프로시저

    - 프로시저를 이용해서 sno=24 행을 조회하시오

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=24;
    --주소 한글로 바꿔서 출력
    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;

○ 프로시저(Procedure)

    ① 프로시저 생성
        - 형식) create or replace prodedure 프로시저명

    ② 프로시저 삭제
        - 형식) drop procedure 프로시저명

    ③ 프로시저 호출
        - 형식) execute 프로시저명

-- sp_test 프로시저 생성
create or replace procedure sp_test
is
begin
    dbms_output.put_line('test프로시저 호출');
end;

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

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