[ORACLE] PL/SQL - 조건문, 반복문 / 함수 / 프로시저 - 커서 , for loop 커서

 

 

※ PL/SQL

(Procedural Language extension to Structured Query Language)

와 ~ 개길다

 

- SQL과 일반 프로그래밍 언어의 특성을 결합한 언어
- 변수, 상수 선언 가능
- 조건문, 반복문 사용 가능

  = PL/SQL 에서는 if 문, for문, while문, case문,loop문 등 사용할 수 있다

 

참조 : https://coding-factory.tistory.com/452

 

 

[1] 선언부 declare 
        - declare키워드 사용
        - 변수나 상수를 선언하는 부분

declare
    i   number;
    result  number;

    
[2] 실행부 begin
        - begin ~ end 키워드 사용
        - 변수에 값 할당, 조건문, 반복문, sql문장 등을 처리
        - 실행해야 할 로직을 넣는 부분

begin
    for i in 1..10 loop
        result := i*2;
        dbms_output.put_line(result);
    end loop;

        
[3] 예외처리부 exception when others then
        - exception 키워드 사용
        - 실행부에서 예외가 발생했을때 처리하는 부분 

        - dbms_output.put_line( '예외발생 시 실행 구문' );

exception when others then
        dbms_output.put_line('error!');

 = > PL/SQL 을 통해 1~10까지 for문을 이용하여 반복처리하기

 

 

▶ 변수, 상수 선언

변수명  데이터타입;  --변수선언
변수명  constant 데이터타입;  --상수선언
변수명  테이블명.컬럼명%type;  --변수선언 : 해당 테이블의 해당 컬럼과 동일한 타입의 변수 선언

 

---

 

▶ PL/SQL 의 조건문과 반복문

 

▷ PL/SQL if 문 

declare 
begin
	if 조건1 then
        문장1;
    elsif 조건2 then
        문장2;
    else
        문장3;
    end if;
  		 exception when others then
end;

declare
    grade   char;
    result  varchar2(50);
begin
    grade := 'B'; -- 변수선언
    
    if grade='A' then
        result := 'Excellent';
    elsif grade='B' then
        result := 'Good';
    else
        result := 'Not found!';
    end if;
            
    dbms_output.put_line(grade || '=>' || result);
    
    exception when others then
        dbms_output.put_line('error!');
end;

 

 

▷ PL/SQL for 문 

  - 인덱스값은 초기값에서 시작해서 최종값까지 1씩 증가함

FOR 인덱스 IN [REVERSE] 초깃값..최종값
LOOP
처리문;
END LOOP;

declare
    i   number;
    result  number;
begin
    for i in 1..10 loop
        result := i*2;
        dbms_output.put_line(result);
    end loop;

    exception when others then
        dbms_output.put_line('error!');
end;

 

▷ PL/SQL while 문 

WHILE 조건
LOOP
처리문;
END LOOP;

declare
    i   number;
    result  number;
begin
    i:=1;
    while i<=10 loop
        result := i*3;
        dbms_output.put_line(i || '*3 =>' || result);
    
        i:=i+1;
    end loop;
        
    exception when others then
        dbms_output.put_line('error!');    
end;

  - i 가 10 보다 같거나 작을때까지 loop 실행

 

▷ PL/SQL case 문 

  case
    when 조건 then
        문장1;
    else
        문장2;
    end case;

DECLARE
    v_grade     CHAR(1) := 'C';
    v_appraisal VARCHAR2(20) ;
BEGIN
  v_appraisal := 
  		CASE v_grade
  			WHEN 'A' THEN 'Excellent'
  			WHEN 'B' THEN 'Very Good'
			WHEN 'C' THEN 'Good'
			ELSE 'No such grade'
      END;

  DBMS_OUTPUT.PUT_LINE ('Grade : '|| v_grade) ;
  DBMS_OUTPUT.PUT_LINE ('Appraisal: '|| v_appraisal);
END;

 

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

 

▷ PL/SQL loop 문 

loop
exit when 조건;
end loop;

for 변수 in [reverse] 초기값..종료값 loop
처리할 문장;
end loop;

declare
    i   number;
    result  number;
begin
    i:=1; --i 는 1에서 시작
    loop
        result := i*2; 
        
        exit when result>20;  --조건을 '만족'하면 반복문 탈출
        dbms_output.put_line(result);
        
        i:=i+1 -- java : i++
    end loop;

    exception when others then
        dbms_output.put_line('error!');
end;

---

▶ PL/SQL 서브 프로그램

    - 데이터베이스 객체로 저장해서 필요할 때마다 호출하여 사용할 수 있는 PL/SQL블록

 

▷ 함수(Function)

  - 결과값을 반환함

  - 사용자 정의 함수를 말함
  - 특정 기능을 수행한 뒤, 결과값을 반환하는 서브프로그램

 create or replace function 함수명
    (
        파라미터1   데이터타입,
        파라미터2   데이터타입, ...
    )
        return 데이터타입
    is 또는 as
        변수선언
    begin
        처리할 로직
        
        exception when others then
            예외처리할 문장
    end;

 

 

예제 ) 주민번호를 넣으면 성별을 리턴하는 함수 get_gender

 

생성

create or replace function get_gender
(
    --파라미터
    p_ssn   varchar2
)
return varchar2     --반환타입
is
    --변수 선언
    v_gender    varchar2(10);
begin
    --처리할 로직
    select case when substr(p_ssn,7,1) in ('1','3') then '남자'
                else '여자' end
           into v_gender
    from dual;
    
    return v_gender;
    
    exception when others then
        dbms_output.put_line('error');                
end;

 

실행

select get_gender('9901081112222') from dual;

 

 

 

▶▷ 저장 프로시저 (내장 프로시저, Stored Procedure)

  - 결과값을 반환하지 않음

  - 특정 기능을 수행하지만 값을 반환하지는 않는 서브프로그램

 

생성

create or replace procedure 프로시저명
    (
        파라미터1   데이터타입,
        파라미터2   데이터타입, ...
    )
    is [as]
        변수선언
    begin
        처리할 로직
        
        exception when others then
            예외처리
    end;

실행

execute 프로시저이름(파라미터);

exec 프로시저이름(파라미터);

 

 

 

예제 ) pd2테이블에 입력하는 프로시저 pd2_insert

 

생성

create or replace procedure pd2_insert
(
    --파라미터
    --pd2 테이블에 insert할 때 필요한 파라미터들
    p_pdcode    char,
    p_pdname    varchar2,
    p_price     number,
    p_company   varchar2
)
is
    --변수 선언    
begin
    insert into pd2(no, pdcode,pdname, price, company)
    values(pd2_seq.nextval, p_pdcode, p_pdname, p_price, p_company);
    
    commit;  --성공하면 커밋
    
    exception when others then
        dbms_output.put_line('pd2 insert error!');
        rollback;  --실패하면 롤백
end;

 

실행

execute pd2_insert('D01','마이크',300000,'삼성');

 

 

 

예제 ) pd2 테이블 컬럼을 수정하는 프로시저 pd2_update

 

생성

create or replace procedure pd2_update
(
    --파라미터
    p_no    pd2.no%type,  
    p_pdcode pd2.pdcode%type,
    p_pdname pd2.pdname%type,
    p_price pd2.price%type,
    p_company pd2.company%type                     
)
is
    --변수 선언부
    v_cnt   number(3);
begin
    select count(*) into v_cnt
    from pd2
    where no=p_no; -- pd 테이블의 행개수를 세어서 v_cnt 변수에 넣어라

    if v_cnt>0 then --데이터가 존재하면
        update pd2 -- 아래와 같이 업데이트해라
        set pdcode=p_pdcode, pdname=p_pdname, price=p_price,
            company=p_company
        where no=p_no;        
    end if;
    
    commit; --update 하고 저장하셈
    
    exception when others then --데이터가 없으면
        dbms_output.put_line('pd2 update error!'); -- 다음과 같은 에러메세지 출력하고
        rollback; -- rollback 하셈
end;

테이블명.컬럼명%type => 해당 테이블의 해당 컬럼과 동일한 데이터 타입

 

 

실행

exec pd2_update(4,'B03','컴퓨터', 1700000,'hp');

 

 

 

예제 ) 교수번호를 입력하면 교수정보를 한눈에 보여주는 프로시저

 

생성

create or replace procedure prof_info
(
    p_profno    professor.profno%type
)
is
    v_prof_row  professor%rowtype;
    v_result    varchar2(2000);                                        
begin
    select * into v_prof_row  -- 2.모든컬럼(한개의 행) 을 v_prof_row 넣어라
    from professor
    where profno=p_profno; --1.매개변수의 값이 professor 테이블의 profno 와 같은 값을
    
    v_result:=v_prof_row.profno || ' ' || v_prof_row.name || ' ' ||
        v_prof_row.position || ' ' ||
        (v_prof_row.pay + nvl(v_prof_row.bonus,0)); -- 위에서 v_prof_row 에 들어간 값의 컬럼들을 출력할 형태
        
    dbms_output.put_line(v_result);
        
    exception when others then
        raise_application_error(-20002, 'professor 조회 에러!');
end;

%rowtype

 - %type과 유사하나, 한 개 이상의 값에 대해 적용

 - professor테이블의 한 개의 row(레코드)(행)
 -정보를 담을 수 있는 타입

 

 

실행

exec prof_info(1001);

 -- 교수번호 1001 인 조인형 교수의 정보가 profno || ' ' || name || ' ' || position || pay +bonus 형태로 출력됨 

 

---

 

 

▶ PL/SQL 을 이용한 사용자 정의 예외

create or replace procedure member_insert
(
    p_name  member.name%type,
    p_jumin  member.jumin%type,
    p_passwd  member.passwd%type,
    p_id  member.id%type
)
is
    system_check_insert_fail    exception;  --사용자 정의 예외
    
begin
    --일요일 23:00:00 ~ 23:59:59 사이에는 시스템 작업으로 인해 입력 불가
    if to_char(sysdate, 'd')=1 and to_char(sysdate, 'HH24')=23 then
        raise system_check_insert_fail;  --사용자정의 예외 발생시키기
    end if;
    
    --일요일 23시 시간대가 아니면 입력 가능
    insert into member(no, name, jumin, passwd, id)
    values(member_seq.nextval, p_name, p_jumin, p_passwd, p_id); --no 는 시퀀스로 자동입력
    
    commit;
    
    exception when others then
        raise_application_error(-20998, 
        '일요일 23:00:00~23:59:59 사이에는 시스템 점검작업으로 서비스 이용불가');
        rollback;
end;

exec member_insert('홍길동2', '9807081113333','123','hong2'); --실행

 

---

 

▶ PL/SQL  out 매개변수(파라미터)

  - 와 이건진짜 모르겠음 일단 수업내용 복붙해놓고 추후에 이해해보자 .... 

 

생성

create or replace procedure prof_info2
(
    p_profno    in  professor.profno%type,  --in 매개변수
    o_name    out  professor.name%type,  --out 매개변수
    o_pay    out  professor.pay%type  --out 매개변수
)
is
begin
    select name, pay into o_name, o_pay
    from professor
    where profno=p_profno;    

    exception when others then
        raise_application_error(-20003, 'professor 조회 에러!');
end;

 

실행

실행도 PL/SQL 로 하나요 ? 미쳤네요

declare
    v_name  professor.name%type;
    v_pay  professor.pay%type;
begin
    prof_info2(1001, v_name, v_pay);
    dbms_output.put_line('이름:' || v_name || ', 급여:' || v_pay);
    
    exception when others then
        dbms_output.put_line('error!');
end;

 

---

▶ PL/SQL 커서 cursor

- 쿼리에 의해 반환되는 결과는 메모리 상에 위치하게 되는데,
   PL/SQL에서는 커서를 사용하여 이 결과 집합에 접근할 수 있다.
- 커서를 사용하면 결과집합의 각 개별 데이터에 접근이 가능하다

 

[명시적 커서]

- 사용자가 직접 쿼리의 결과에 접근해서 이를 사용하기 위해 명시적으로 선언한 커서

 

▷ 명시적 커서를 사용하기 위한 절차

 

[1] 커서 선언

      - 쿼리 정의

cursor  커서명 is select문장;

[2] 커서 열기(open)

       - 쿼리 실행

open 커서명;

 [3] 패치(fetch)

        - 쿼리의 결과에 접근, 루프를 돌며 개별 값들에 접근

fetch 커서명 is 변수...;

[4] 커서 닫기(close)

     - 메모리상에 존재하는 쿼리의 결과를 소멸시킴

close  커서명;

 

 

예제 ) pd2 테이블을 형식을 지정해서 전부 출력하기 pd2_select

생성

create or replace procedure pd2_select
is
    --[1] 커서 선언
    cursor pd2_csr is
        select no, pdcode,pdname,price 
        from pd2; -- pd2 테이블에서 no, pdcode,pdname,price 컬럼가져오셈
        
    --변수 선언
    pd2_rcd pd2%rowtype; -- 행단위로 담을 수 있는 변수 pd2_rcd
begin
    --[2] 커서 열기
    open pd2_csr; 
    
    --[3] 패치 - 쿼리의 결과에 접근, 루프를 돌며 개별 값들에 접근
    loop
        fetch pd2_csr into pd2_rcd.no, pd2_rcd.pdcode,pd2_rcd.pdname,
            pd2_rcd.price;
        exit when pd2_csr %notfound; -- 뒤에 자료가 없을때까지 커서로 각 정보 추출
        
        dbms_output.put_line(pd2_rcd.no || ' ' || pd2_rcd.pdcode || ' ' 
            || pd2_rcd.pdname || ' ' || pd2_rcd.price );    -- 이 형태로 자료 추출하셈
    end loop; 
    
    --[4] 커서 닫기
    close pd2_csr; 
    
    exception when others then
        raise_application_error(-20006, 'pd2 select error!');
end;

%notfound 

    - 커서에서만 사용 가능한 속성
    - 더 이상 패치(할당)할 로우가 없음을 의미
    - 쿼리의 마지막 결과까지 패치한 후에 자동으로 루프를 빠져나가게 됨

 

 

실행

exec pd2_select();

---

▶ PL/SQL for loop cursor문

 - 커서의 for loop문을 사용하면 커서의 open, fetch, close가 자동적으로 발생 되어지기 때문에

    open, fetch, close문을 기술할 필요가 없다

 

 

▷ 형식

for 변수명 (레코드 정보가 담기는 변수) in 커서명 loop
        실행 문장;
    end loop;

 

생성

create or replace procedure pd2_select2
is
    --커서 선언
    cursor pd2_csr is
        select no, pdcode, pdname, price from pd2;
begin
    for pd2_row in pd2_csr loop
        dbms_output.put_line(pd2_row.no || ' ' || pd2_row.pdcode || ' ' ||
            pd2_row.pdname || ' ' || pd2_row.price);
    end loop;
    
    exception when others then
        raise_application_error(-20007, 'pd2 select error!');
end;

 

  => 훨씬 간단해짐

 

실행

exec pd2_select2;

 

 

---

▶ sys_refcursor

  - 뭔지모르겠음 나 진짜 한계임

  - 저장 프로시저의 select 결과물을 java에서 읽기 위해서는 sys_refcursor 타입을 사용해야 함

create or replace procedure pd2_select3
(
    pd2_cursor out SYS_REFCURSOR
)
is
begin
    open pd2_cursor for
        select no, pdcode, pdname,price, company,regdate
        from pd2;
        
    exception when others then
        raise_application_error(-20009, 'pd2 조회중 에러!');
end;