PL/SQL

<PL/SQL>
- SQL에 절차적인 프로그래밍 요소(조건문,반복문)를 가미해 만든 것.
- 오라클에서만 사용가능.

형식)
  DECLARE --선언부
     -- 변수선언(실행부에서 사용하고자 하는)을 각각
  BEGIN     --실행부
     -- 변수 초기화
     -- 절차적인 요소
     -- 실행할 DQL,DML
     -- 기능(프로시져, 함수) 호출
  END;
  /

※ 실행부에서의 select 사용

  select a,b into 변수명1,변수명2
  from test;
 

 declare
  vempno number(4);
  vename varchar2(15);
 begin
  vempno := 8000;
  vename := '홍길동';
  dbms_output.put_line('사원번호: '||vempno);
  dbms_output.put_line('사원명: '||vename);
 end;
 /

<조건문>
1. IF~THEN~END IF
  형식)
     IF 조건식
       THEN 실행할문장1
               실행할문장2
     END IF;
     
2. IF~THEN~ELSE~END IF
   형식)
     if 조건식
       then 조건결과 참일때 실행할 문장
     else
       조건결과 거짓일때 실행할 문장
     end if;

    

3. IF~THEN~ELSIF~ELSE~END IF

    형식)
        if 조건식1
          then 조건식1 참일때 실행할 문장
        elsif 조건식2
          then 조건식2 참일때 실행할 문장         
        elsif 조건식3
          then 조건식3 참일때 실행할 문장
        else 조건1,2,3에 거짓일때 실행할문장         
        end if;    
                       
<반복문>
1. loop
 형식)
  loop
   반복실행할 문장1;
   반복실행할 문장2;
   exit where 조건식;
  end loop;
   
2. for
 형식)
  for 인덱스변수
   in [reverse] 최소값..최대값
  loop
   반복실행될 문장;
  end loop;

 

3. while
 형식)
  while 조건식
  loop
   반복 실행할 문장;
  end loop;

<저장프로시저> stored procedure
 - 자주사용하는 SQL문을 모듈화하여 필요할 때마다 호출하여 사용.
 형식)
  create [or replace] procedure 프로시저명[(변수명 [mode] 자료형)]
  is
   변수선언;
  begin
   SQL문장;
  end;
  /
  

사용법)
  execute 프로시저명;
 
SQL> @폴더명/파일이름

SQL> execute 프로시저명

 

<바인드 변수>
variable a varchar2(15);
execute select_empno(7788, :vename,:vsal,:vjob);

print vename;

 

VENAME
--------------------------------
SCOTT

 

<저장함수> sotred function
 형식)
  create or replace function 함수명 (변수명 [mode] 자료형)
  return 자료형;
  is
  begin
   실행할 SQL문;
   return 데이터;
  end;
  /

 

 -- fun_test.sql

SQL> @0507/fun_test.sql

함수가 생성되었습니다.

SQL> variable su number;
SQL> execute :su := cal_bonus(7788);

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> print su

        SU
----------
    600000

 

<커서> Cursor
- 두 개 이상의 결과행(select)을 처리할 때 사용
 형식)
  DECLARE
   CURSOR 커서명 IS select문;
  BEGIN
   OPEN 커서명;
   FETCH 커서명 INTO QUSTN;
   CLOSE 커서명;
  END;
  
<입력 트리거>
- 입고테이블에 입력시 상품테이블에 재고를 수정

create or replace trigger insert_tri
 after insert--발생이벤트
 on 입고--이벤트발생(insert,delete,update)할 테이블
 for each row -- 각각 행에 이벤트를 적용

 

--<바인드bind변수>
-- :old 기존데이터
-- :new 새로입력된데이터

<삭제트리거>
create or replace trigger delete_tri
 --이벤트
 after delete
 --적용테이블
 on 입고
 for each row --여러행 적용
 
<수정트리거>

create or replace trigger update_tri
 --이벤트
 after update
 --적용테이블
 on 입고
 for each row --여러행 적용