[SQL] PL/SQL 기초

• PL/SQL
  • Procedural LANGUAGE / SQL
  • 오라클에서 제공하는 프로그래밍 언어
  • 기본 SQL만으로는 데이터 조작이 불편(부족)한 부분을 PL/SQL과 함께 사용하면 효과적으로 다룰 수 있다.
  • PL/SQL은 dbeaver에서 사용할 수 없다.
  • PL/SQL은 기본적으로 처리된 PL/SQL의 결과를 화면에 출력하지 않는다.

 

• PL/SQL Block 구성
  1. DECLARE(선언부)
     • 변수나 상수 선언
  2. EXECUTABLE(실행부)
     • 제어, 반복, 출력, 함수정의 등... ex) BEGIN ~ END
  3. EXCEPTION(예외처리)
     • 실행도중 발생된 에러 처리

 

• CMD에 결과 출력
  • DBMS_OUTPUT.PUT_LINE();
• &을 이용한 변수의 자세한 값 변화 표시를 OFF한다. (기본값 ON)
  • SET VERIFY OFF;

 

• 일반적인 오라클 함수는 블럭내에서 사용가능하지만 그룹함수는 사용 불가능하다.

 

• 변수 선언
  1. test INTEGER := 1;
  2. test VARCHAR2(10) : = 'ABC';
  3. test INTEGER := '&test';
     • 변수 선언과 동시에 입력 받은 값으로 초기화
     • & : 사용자에게 입력 받는 기호
  4. no test.no%TYPE;
     • test 테이블의 no와 같은 타입으로 선언
  5. SQL의 SELECT 결과를 변수에 대입 가능
     • test INTEGER;
     • SELECT 10 INTO test from dual;
• PL/SQL 변수 작명 규칙
  1. 반드시 문자로 시작해야한다.
  2. 문자, 숫자, 특수문자 포함이 가능하다.
  3. 변수명은 30byte 이하이어야 한다.
  4. 예약어 사용이 불가능하다. ex) SELECT
• 중첩 PL/SQL 블럭 사용
  • 프로시져를 작성할 때 블럭 안에 또 다른 블럭을 중첩할 수 있다.
    • 이때 내부에 선언된 변수는 지역변수 개념이 똑같이 작용된다.
• ROWTYPE
  • 테이븡리나 뷰 내부의 컬럼 데이터형, 크기 속성등을 그대로 사용할 수 있다.
    • %ROWTYPE 앞에 오는 것은 데이터베이스 테이블 이름이다.
    • 지정된 테이블의 구조와 동일한 구조를 갖는 변수를 선언할 수 있다.
    • 데이터베이스 컬럼들의 수나 DATETYPE을 알지 못할 때 편리하다.
    • 테이블의 데이터 컬럼의 DATETYPE이 변경 될 경우 프로그램을 재수정할 필요가 없다.
      • test test%ROWTYPE;

 

• 조건문
  • IF ~ END IF;
    • IF(조건식) THEN [참일 떄 수행] END IF;
    • IF(조건식) THEN [참일 때 수행] ELSIF(조건식) THEN [참일 떄 수행] END IF;
  • CASE
    • CASE 기준값 WHEN 조건 THEN [참일 때 수행] ELSE [모든 조건이 거짓일 때 수행] END;
• 반복문
  • WHILE
    • WHILE(조건식) LOOP ~ END LOOP;
      • 조건식이 참(true)인 동안 LOOP 블럭 반복 수행
  • FOR
    • FOR 변수 IN 횟수 LOOP ~ END LOOP;
      • FOR 반복문내에서 사용되는 변수는 DECLARE 하지 않아도 사용 가능하다.
      • 횟수는 0..5, 1..9 이런식으로 선언하며 최종값 ex) 5, 9를 포함한다.(0 <= 5)

 

• 단순 BEGIN ~ END에서 사용한 블록은 익명블록이라 한다.
  • 저장 되지 않으며, 재사용이 불가능하다.
• 자주 사용되는 PL/SQL 블록은 이름을 지정하여 생성 해두었다가 필요할 때 마다 사용(호출)할 수 있다. 이를 서브 프로그램(Sub Programe)이라 한다.
  • 오라클의 서브프로그램의 종류
    1. Procedure
    2. Function
    3. Package
    4. TRIGGER

 

• Procedure
  • 생성하기
    • CREATE (OR REPLACE) PROCEDURE 프로시져명 IS BEGIN 수행블록 END;
  • Parameter의 종류
    1. IN
       • (default) 서브프로그램에 전달되며 형식 파라미터가 상수로 동작한다.
    2. OUT
       • 호출한쪽으로 값이 반환되며 초기화 되지 않은 변수이다.
    3. INOUT
       • 서브프로그램으로 전달 되며 호출한쪽으로 값이 반환된다. 초기화된 변수이다.

 

• 별도로 변수를 선언해서 값을 받은 후 출력도 가능하다.

1. SQL> variable name varchar2(10);
2. SQL> variable pay number;
3. SQL> exec info_prof(1001, name, :name, :pay);
   • Parameter에 꼭 콜론을 붙여야한다. ex) :변수명