old posting/MySQL

[SQL] PL/SQL 기초

마케팅스프린트 2021. 6. 3. 18:11
  • 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) :변수명
저작자표시 (새창열림)