[DATABASE] PL/SQL syntax 정리

/****************************************
  PL/SQL syntax 정리
*****************************************/

----------------------------------------------------------
-       * syntax 기호                                                          
-                                                                                    
-         [] : 대괄호 안의 내용이 생략가능                              
-          | : 또는                                                                
-         {} : 중괄호 안의 내용이 하나 이상 올 수 있다.              
-        ... : 앞 표기와 같은 형식으로 더 올 수 있다.                  
-          , : 구분자                                                              
----------------------------------------------------------

* 기본구조
  declare : 실행부에서 참조할 모든 변수, 상수, 커서, exception을 선언
       variables, cursor, user_defined, exception

  begin : 데이터베이스의 데이터를 처리할 sql문과 pl/sql블록을 기술
       sql, pl/sql statements;

  exception : 실행부에서 에러가 발생했을때 수행될 문장을 기술
       actions to perform wher erros occur
  end;

* anonymous
syntax : [declare]
            begin
                   statements;
                   statements;
                   statements;
            [exception]
            end;

* procedure
syntax : create procedure procedure_name
            is
            begin
                   statements;
                   statements;
            [exception]
            end;

* function
syntax : create function function_name
            return datatype
            is
            begin
                  statements;
                  statements;
                  return value;
            [exception]
            end;

* PL/SQL 변수
  1) scalar : 주로 단일값을 보유. 주요 데이터형은 오라클 서버 테이블의 열 유형에 대응하는 것들입니다.
  2) composite : 레코드 같은 조합 데이터형은 PL/SQL블럭에서 조작되고 정의되는 필드그룹을 허용합니다.
  3) reference : 참조데이터형은 pointer라 불리며 다른 프로그램 항목을 지시하는 값을 보유합니다.
  4) LOB(Large Objects) : LOB데이터형은 locator라 불리며 라인 밖에서 지정된 큰 객체의 위치를 지정하는 값을 보유

 

* PL/SQL 변수 선언
  syntax : identifier [constant] datatype [not null] [:= | default expression];
  - identifier : 변수의 이름
  - constant : 변수의 값을 변경할 수 없도록 제약 (상수)
  - datatype : scalar, composite, reference, lob(large objects)
  - not null : 값을 포함해야만 하도록 하기 위해 변수를 제약
  - expression : literal, 다른 변수, 연산자나 함수를 포함하는 표현식

* 변수 값 지정
  syntax : identifier := expression;

* 기본 스칼라 데이터 형
  - varchar2(n) : 변수길이 문자 데이터에 대한 기본형은 32767Byte까지 디폴트 크기는 없다.
  - number(p,s) : 고정과 유동포인트 숫자에 대한 기본형 소수점 이상 p자리, 소수점 이하 s자리
  - date : 날짜와 시간에 대한 기본형, date값은 지정 이후의 초 단위로 날에 대한 시간을 포함합니다. 날짜의 범위는 bc4712년 1월1일 부터 ad9999년 12월 31일 사이
  - char(n) : 고정 길이 문자에 대한 기본형은 32767Byte까지. 디폴트길이는 1
  - long : 고정길이 문자에 대한 기본형은 32767Byte까지 long은 최대 2147483647Byte까지
  - long raw : 이진 데이터와 바이트 문자열에 대한 기본형은 32767Byte까지 long raw는 PL/SQL에 의해 해석되지 않음
  - boolean : true, false, null중 하나의 값
  - binary_integer : -2147483647~2147483637사이의 정수
  - pls_integer : -2147483647~2147483637사이의 정수에 대한 기본형 적은 기억장치를 필요로함

* %type 속성: 데이터베이스 테이블의 열이 가진 type으로 변수를 선언
  syntax : variable_name table_name.column_name%type

* table type :배열의 개념 (테이블의 두 컬럼을 인덱스와 데이터로 사용)
  syntax : type table_type_name is table of
              {column_type | variable%type | table.column%type} [not null]
              [index by binary_integer];
              identifier table_type_name;

  - table_type_name : 테이블형의 이름
  - column_type : varchar2, date, number와 같은 스칼라데이터형
  - identifier : 전체 PL/SQL테이블을 나타내는 식별자의 이름

* record type: 구조체 개념, 테이블의 row전체를 하나의 데이터형으로 나타냄
  syntax : type type_name is record
              (field_name1 {scalar_datatype|record_type} [not null] [{:=|default} expr],
              (field_name2 {scalar_datatype|record_type} [not null] [{:=|default} expr]
              ...);
              identifier_name type_name;

  - type_name : record형의 이름, 이 식별자는 record를 선언하기 위해 사용한다.
  - field_name : record내의 필드명
  - record를 참조 : record_name.field_name

* %rowtype 속성: 테이블의 열을 하나의 타입으로
  syntax : identifier reference%rowtype
  - 개별필드참조 : record_name.field_name

* LOB 데이터형(각 4GByte까지)
  1) CLOB(Character large object) : 데이터베이스내의 단일 바이트 문자 데이터의 대형블럭을 저장
  2) BLOB(Binary large object) : 데이터베이스 내의 대형 이진객체를 저장
  3) BFILE (Binary file) : 데이터베이스 외부의 운영시스템 파일의 대형 이진객체 저장
  4) NCLOB(national language character large object) : 데이터베이스내의 단일바이트, 또는 고정 길이의 멀티바이트 nchar데이터를 행의 안팎에 저장하기 위해 사용

* 바인드(bind) 변수 :SQL*PLUS에서의 변수를 PL/SQL에서 사용
  syntax : var[iable] [variable [number | char(n) | varchar2(n)]]

* 호스트(host) 변수: 호스트운영체제의 변수를 PL/SQL에서 사용
  syntax : :variable_name

* 구분문자
  + : 덧셈연산자
  - : 뺄셈연산자
  * : 곱셈연산자
  / : 나눗셈 연산자
  = : 관계형연산자
  @ : 원격엑세스지시자
  ; : 문장지시자
  <> : 관계형 연산자 (같지않다)
  != : 관계형 연산자 (같지않다)
  || : 문자열 연결 연산자
  -- : 단일라인 주석
  /* */ : 멀티라인 주석
  := : 지정연산자(대입)

* 데이터형 변환 함수
  1) to_char(value, format) : value를 문자로 전환
  2) to_date(value, format) : value를 날짜형식으로 전환
  3) to_number(value, format) : value를 숫자로 전환

* PL/SQL의 연산자와 우선순위
  1) **, not : 지수승, 논리부정
  2) +, - : 식별, 부정연산자
  3) *, / : 곱셈, 나눗셈
  4) +, -, || : 덧셈, 뺄셈, 연결
  5) =, !=, <, >, <=, >=, is null, like, between, in : 비교연산자
  6) and : 논리곱
  7) or : 논리합

* 코드규약 (권장)
  - 대문자 : sql명령어, pl/sql키워드, 데이터형
  - 소문자 : 식별자, 매개변수, 테이블명, 열명

* 코드명 지정규약 (권장)
  - 변수 : v_name
  - 상수 : c_name
  - 커서 : name_cursor
  - 예외 : e_name
  - 테이블형 : name_table_type
  - 테이블명 : name_table
  - 레코드형 : name_record_type
  - 레코드명 : name_record
  - SQL*PLUS 치환 매개변수 : p_name
  - SQL*PLUS 전역변수 : g_name

* PL/SQL에서의 select
  - 단일행을 리턴하는 select만 사용

  syntax : select select_list
              into {variable_name1[.variable_name2, ...] | record_name}
              from table_name
              where condition;

* cursor 속성
  - sql*rowcount : 가장 최근의 sql문장에 의해 영향을 받은 행의 수
  - sql%found : 가장 최근의 sql문장이 하나 또는 그 이상의 행에 영향을 미친다면 true
  - sql%notfound : 가장 최근의 sqlㄴ문장이 어떤행에도 영향을 미치지 않았다면 true
  - sql%isopen : PL/SQL이 실행된 후에 즉시 암시적 커서를 닫기때문에 항상 true

* if
  syntax : if condition then
                 statements;
              [elsif condition then]
                statements;
              else
                 statements;
              end if;

* loop
  syntax : loop
                 statement1;
                 statement2;
                 ...
              exit [when condition];
              end loop;

* exit문
  syntax : exit [when condition];

* for loop
  syntax : for index_counter in [reverse] lower_bound..upper_bound loop
                 statement1;
                 statement2;
                 ...
              end loop;

  - index_counter : upper_bound나 lower_bound에 도달할때까지 loop를 반복함으로써 1씩 자동적으로 증가하거나 감소되는 값을 가진 암시적으로 선언된 정수
  - reverse : upper_bound에서 lower_bound까지 반복함으로써 인덱스가 1씩 감소되도록 합니다.
  - lower_bound : index_counter값의 범위에 대한 하단바운드
  - upper_bound : index_counter 값의 범위에 대한 상단 바운드

* while loop
  syntax : while condition loop
                 statement1;
                 statement2;
                 ...
              end loop;

* cursor
  - 암시적 cursor
    sql%rowcount : 지금까지 리턴된 행의 총 수(number)
    sql%found : 가장 최근의 인출(fetch)가 행을 리턴하면 true (boolean)
    sql%notfound : 가장 최근의 인출(fetch)가 행을 리턴하지 않으면 true (boolean)
    sql%isopen : cursor가 열리면 true

  - 명시적 cursor : 선언, open, fetch into, close
  syntax : cursor cursor_name is
                  select_statement;
              open cursor_name;
              fetch cursor_name into {variable1[,variable2, ...] | record_name};
              close cursor_name;

* cursor와 for loop
  syntax : for record_name in cursor_name loop
                 statement1;
                 statement2;
                 ...
              end loop;

* subquery를 사용한 cursor for loop
  syntax : for record_name in (subquery) loop
                 statement1;
                 statement2;
                 ...
              end loop;

* 매개변수와 cursor
  syntax : cursor cursor_name [(parameter_name1 datatype, ...)] is
                 select_statement;
              cursor_parameter_name [in] datatype [{:= | default} expression]

* for update절
  syntax : cursor cursor_name is
                 select_statement
                 for update [nowait] [of column1[,column2,...]];

* 정의된 oracle server 에러
  - access_into_null(ora-06530) : 초기화 되지 않은 객체의 속성에 대해 값을 지정하려고 함
  - collection_is_null(ora-06531) : 초기화 되지 않은 중첩 테이블에 대해 exists를 제외한 메소드 모음의 적용을 시도합니다.
  - cursor_already_open(ora-06511) : 이미 열린 커서의 열기를 시도합니다.
  - dup_val_on_index(ora-00001) : 중복값의 삽입을 시도합니다.
  - invalid_cursor(ora-01001) : 잘못된 커서연산이 발생합니다.
  - invalid_number(ora-01722) : 수의 문자열 전환은 실패입니다.
  - login_denied(ora-01017) : 잘못된 사용자명과 비밀번호로 오라클에 로그온합니다.
  - no_data_found(ora-01403) : 데이터를 리턴하지 않는 select문장
  - not_logged_on(ora-01012) : PL/SQL은 내부문제를 가지고 있습니다.
  - program_error(ora-06501) : PL/SQL은 내부 문제를 가지고 있습니다.
  - rowtype_mismatch(ora-06504) : 지정문에 포함된 호스트 커서변수와 PL/SQL커서변수의 리턴유형이 다릅니다.
  - storage_error(ora-06500) : PL/SQL이 메모리를 다 써버리거나 또는 메모리가 훼손됨
  - subscript_beyond_count(ora-06533) : 모음의 요소 개수보다 더 큰 인덱스 개수를 사용하는 중첩테이블을 참조합니다.
  - subscript_outside_limit(ora-06532) : 범위 밖의 인덱스 번호를 사용하여 중첩테이블을 참조
  - timeout_on_resource(ora-00051) : oracle이 리소스를 대기하는 동안 시간초과 발생
  - too_many_rows(ora-01422) : 단일행 select는 하나 이상의 행을 리턴합니다.
  - value_error(ora-06502) : 계산, 변환, 절단, 또는 크기제약 오류가 발생
  - zero_devide(ora-01476) : 0으로 배분을 시도

* 미리 정의되지 않은 오라클 서버 에러
  - 선언
    syntax : exception_name exception;

  - pragma exception_init문장을 사용해 표준에러번호와 선언된 예외를 연결
    syntax : exception_init(exception_name, error_number)

* 사용자 정의 예외
  - 선언
    syntax : exception_name exception;

  - 명시적으로 예외를 발생시킴
    syntax : raise exception_name;

* 예외 트래핑 함수
  - sqlcode : 에러 코드에 대한 숫자를 리턴
  - sqlerrm : 에러 번호에 해당하는 메세지를 리턴

* SQL코드 값
  - 0 : 예외가 없습니다.
  - 1: 사용자정의 에러번호
  - +100 : no_data_found
  - 양의 정수 : 표준 에러번호

* 어플리케이션내에서 예외 발생시키기
  syntax : raise_application_error(error_number, message[,{true|false}]);

* procedure
  syntax : create [or replace] procedure procedure_name
                 [(argument1 [mode1] datatype [{:= | default} expression]
                 [(argument1 [mode1] datatype [{:= | default} expression], ...])]
                 {is | as}
              begin
                 pl/sql block;
              end;

  - or replace : procedure_name이 존재할 경우 procedure의 내용을 지우고 다시 생성
  - procedure_name : procedure명
  - argument : 매개변수의 이름
  - mode : 3가지 (in:입력매개변수로 사용)
                       (out:출력매개변수로 사용)
                       (in out : 입출력매개변수로 사용)
  - pl/sql block : procedure를 구성하는 코드를 구성하는 pl/sql의 블럭

 

* function
  syntax : create [or replace] function function_name
                 [(argument1 [mode1] datatype [{:= | default} expression]
                 [(argument1 [mode1] datatype [{:= | default} expression], ...])]
                 return data_type
                 {is | as}
              begin
                 pl/sql block;
              end;

  - or replace : procedure_name이 존재할 경우 procedure의 내용을 지우고 다시 생성
  - procedure_name : procedure명
  - argument : 매개변수의 이름
  - mode : 3가지 (in:입력매개변수로 사용)
                       (out:출력매개변수로 사용)
                       (in out : 입출력매개변수로 사용)
  - data_type : 반환되는 값의 datatype
  - pl/sql block : procedure를 구성하는 코드를 구성하는 pl/sql의 블럭

* procedure와 function 비교
  - procedure : pl/sql문으로서 실행, 리턴데이터형 없음, 값을 리턴할 수도 있다.
  - function : 식의 일부로서 사용, 리턴데이터형 필수, 반드시 값을 리턴해야함

* trigger
  syntax : create [or replace] trigger trigger_name
                 {before | after} triggering_event [of column1, ...] on table_name
                 [for each row [when trigger_condition]             
                 trigger_body;

  - trigger_name : trigger의 식별자
  - before | after : DML문장이 실행되기 전에 trigger를 실행할 것인지 실행된 후에 trigger를 실행할 것인지를 정의
  - triggering_event : DML(insert, update, delete)
  - of column : trigger가 실행되는 테이블에서 컬럼명을 기술한다.
  - table name : trigger가 실행되는 테이블 명
  - for each row : 이 옵션을 사용하면 행 레벨 트리거가 되어 triggering문자에 영향받은 행에 대해 각각 한번씩 실행하고 사용하지 않으면 문장레벨 트리거가 되어 DML문장당 한번만 실행된다.

* trigger operation결정하는 세가지 함수
  - inserting : 트리거링 문장이 insert이면 true
  - updating : 트리거링 문장이 update이면 true
  - deleting : 트리거링 문장이 delete이면 true

 

* trigger 제거 / 활성화선택
  - 제거
     syntax : drop trigger trigger_name

  - 활성화선택
     syntax : alter trigger trigger_name {disable | enable};

출처 - http://fly32.net/255