database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Tutorials 9236 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 9236
PL/SQL 요약
작성자
정재익(advance)
작성일
2002-01-05 08:50
조회수
13,310

PL/SQL 요약

 

원본출처 : http://sh.hanarotel.co.kr/~ggola/telecom/ocp/pl-sql.htm

 

>>Access data with PL/SQL (아래 표가 깨지면 왼쪽 메뉴바를 더 왼쪽으로 드래그 해서 최소화 해주시면 되겠슴다)

* access the oracle database from an application through one of two languages : SQL and PL/SQL

                          _________________________________________________________________________
                         |     |sql statements       |    sql statement executor                                                  |
  ___________    |     ------------------|---------------------------------------------------|
 |Application|--|                                     |     [be sent to the ORACLE SERVER at runtime]                |
  -----------    |      ___________ ______|___________________________________________________|
                         |___|pl/sql block            |    pl/sql engine -- procedural statement executor           |
                                 ------------------|                 (compile and execute pl/sql block)                 | 
                                                                |                                                                                         |
                                                                |__ sql statement executor (execute sql statement)         _|
                                                                       ------------------------------------------------

  - PL/SQL Block : 
        DECLARE  (optional)
         >> variable, constant, cursors, user-defined exeception
         procedural |
                    |----> Procedural statement executor
         procedural |
        BEGIN    (mandatory)
         >> sql and pl/sql statements
         procedural |----> Procedural statement executor
         SQL        |----> SQL statement executor
        EXCEPTION (optional)
         =>> specified action for error or some condition arise within the Executable Section
        END ;
        => 예상문제 : 결국 실질적인 P/G실행은 PL/SQL Block도 SQL statement executor 가 담당
        => 예상문제 : PL/SQL 의 동작은 어떻게 이루어지나? 정답은 ------------------ (2)
                      (1) 모든 문장을 procedure excutable area로 보낸다
                      (2) 각각을 분리하여(seperate) SQL excutable area로 보낸다
                      (3) 모든 문을 SQL excutable area로 보낸다
                      (4) 분리하여 procedure excutable area로 보낸다
  - PL/SQL Program은 procedural option이 있어야 사용가능하다
      >> Anonymous Block : unnamed PL/SQL Block (embeded within an application or ..)
      >> Stored Procedure/Function : named PL/SQL Block (parameters, invoke)
      >> Package : named PL/SQL module (groups together related procedures, functions ...)
      >> Database Trigger : PL/SQL Block (associated with a database table)
>>Delvelope a simple PL/SQL block
 1.DECLARE BEGIN (EXCEPTION) END
 * ----------------------------
   DECLARE
       vb_valid      boolean not null := TRUE ;
       vc_input      varchar2(10) := NULL ;
       vc_string1, vc_string2, vc_string3  varchar2(10) := NULL ;
       vd_idate      date not null := SYSDATE ;
       cc_spec       constant varchar2(10) := 'ORACLE' ;
       vn_balance    number(7,3) ;
       vn_min_balance    vn_balance%type := 12.0 ;
       vc_emp_name   emp.emp_name%type ;
       vn_emp_id     emp.emp_id%type ;
       TYPE vt_person_table is table of varchar2(10) [not null]
            index by binary_integer ;
       vt_person      vt_person_table ;
       TYPE vr_person_record
           (last_name    emp.last_name%type
            first_name   emp.first_name%type,
            gender       char(1) not null := 'M' ) ) ;
       vr_person      vr_person_record ;
       vtr_emp        emp%rowtype ;
          ....
       ...
       ..
    BEGIN
       ....
       ...
       .. 
          
          DECLARE
            vb_valid          boolean not null := TRUE ;
            vc_sub_input      varchar2(10) := NULL ;
          BEGIN
            ....
            ..
  
          END ;
      ....
      ...
      .. 
 * ----------------------------
 2.Variable, Assign, Convert Datatypes
  - variables : identifier [constant] datatype [not null] [:= | default plsql_expression] ;
  - PL/SQL Declare에서  NOT NULL column으로 변수 정의 하고 값을 할당 안하면 => not compile
    그러나 ' vc_deptid dept.id%type not null ; ' 형식으로 data type 할당후 
    not null column constraint의 table type으로 값을 정의하고 default value를 할당안하면 
    => NULL값을 초기값으로 갖는다. 즉, ' vc_deptid varchar2(10) not null; ' 처럼 compile error가
       발생하지 않는다.
    => scalra types : binary_integer, positive, natural, number, float, decimal, real, 
                      integer, smallint, char, rowid, long, varchar, varchar2, boolean, date,
                      raw, long raw
    => % type attribute : binary_integer, positive, natural, number, float, decimal, real
    => composite types : pl/sql table 사용 (to store an unconstrained array of values)
                         pl/sql record type 사용 (record type 변수 선언)
       => 예상문제 : record에 value 할당 방법은 ?
          (record name 이 vr_emp, column name이 last_name이면)
                     vr_emp.last_name = 'JOHN'
    => %rowtype attribute : variable according to the data structure of a database table
    => sub block에서 declare된 variable은 그 sub block 에서만 유효하다
  - assign values to variables : 
    => operator : numeric(**,*,/,+,-), character(||), 
                  date(+,-), comparison(=,!=,<,>,<=,>=,is null,like,between,in)
   EX) vn_count := v_count + 1;
       vc_name_addr := v_name || v_addr;
       vd_start_date := v_start_date -7;
       vb_equal boolean := TRUE;
       vb_equal := (v_n1 = v_n2); --> boolean value setting (v_n1과 v_n2의 비교에 따른)
       => 예상문제 : TRUE 와 NULL의 or 비교는 TRUE다
    => function : round, trunc, sqrt, upper, lower, initcap, substr, add_months,
                  months_between, nvl, greatest, lease
   EX) vc_valid := (vc_emp is not null);
       vc_last_name := upper(v_last_name);
       vn_total := sum(vt_amount);
                (--> vt_amount는 vt_amount_table이라는 number type의 pl/sql table)
    => convert datatypes automatically : between character and number
   EX) vc_string  varchar2(10) ;
       vn_number  number(10)   ;
       vc_string := 10 + '10';   ----> 숫자 10 은 자동으로 문자 '10' 으로 = '20'
       vn_number := '10' + 10 ; ----> 문자 '10' 은 자동으로 숫자 10 으로 = 20
       vc_string := vn_number || 'concate' ---> compile error (자동으로 convert 될 수 없다)
 3.Input and Output Varibles
    => SQL*PLUS
 * ----------------------------  
   REM  pl/sql block 밖에서 data input and output   (-- , /*  ..  */ : comment (주석))
   VARIABLE gvn_total number  (-- output을 위한 pl/sql block밖 global variable선언)
    ...
   ACCEPT    ad_sal   PROMPT  'Please enter your salary : ' (-- user가 input할 value)
   DECLARE
       vn_sal     number(12) := &ad_sal ;
    ....
     ....
   BEGIN
       :gvn_total := 12 * vn_sal
     ...    
   END ;
   /
   PRINT   gvn_total  (-- sql*plus상에서 value output)
 * ----------------------------  
       => 예상문제 : Global Variable의 선언및 사용은 ?
                     Declare 절 위에서 선언하고 Block내에서 치환시 ':'를 prefix로 사용한다.
>>Manipulate data with PL/SQL
 1.SQL statements available within PL/SQL
  - supports the Oracle Server syntax : select, insert, update, delete, commit, rollback
  - not support DDL (Data Definition Language : create table, alter table)
  - not support DCL (Data Control Language : grant, revoke)
 * ----------------------------  
   DECLARE
       vc_name         emp.emp_name%type ;
       vn_deptid       emp.emp_dep_id%type ;
    ....
     ....
   BEGIN
       select  emp_name,  emp_dept_id
       into    vc_name,  vn_deptid
       from    emp
       where   emp_id = 33 ;
       insert into emp ('11', emp_name, emp_dept_id + 10) ;
       savepoint a ;
       insert into emp ('12', emp_name, emp_dept_id + 11) ;
       savepoint b ;
       rollback to savepoint a ;
       commit ;
     ...    
   END ;
   /
 * ----------------------------
>>Control PL/SQL Flow of Execution
 1.Execute statements conditionally
  - if : elseif : else : end if
  - loop : loop는 언제든지 exit를 만나면 종료되고 다음 statement를 진행한다.
        1. loop ..... end loop ;
        2. for ..... end loop ;
        3. while ..... loop ;
  - goto : 지정된 label로 이동
       => 예상문제 : loop중 반드시 exit를 필요로 하는 것은 ?
                     loop ............ exit ....... end loop ;
 * ----------------------------  
   DECLARE
       vc_name         emp.emp_name%type ;
       vn_deptid       emp.emp_dep_id%type ;
       vn_counter      number() ;
    ....
     ....
   BEGIN
       select  emp_name,  emp_dept_id
       into    vc_name,  vn_deptid
       from    emp
       where   emp_id = 33 ;
       if vc_name = 'JOHN' then
          vn_deptid = '1' 
       elseif vc_name = 'JANG'
          vn_deptid = '2' 
       else
          vn_deptid = '3' 
       end if ;
       Loop
         vn_counter := vn_deptid + 1 ;
         exit when vn_counter > 10 ;
       End Loop ;
       For vn_counter in 1..10 loop
         vn_deptid = vn_deptid + vn_counter ;
       End Loop ;
       <>
       While vn_counter < 10 loop
         vn_counter := vn_deptid + 1 ;
       End Loop ;
       <>
       While vn_counter < 10 loop
         vn_counter := vn_deptid + 1 ;
            <>
            While vn_deptid < 12 loop
               vn_deptid := vn_counter + 2 ;
               exit out_loop when vn_counter = vn_deptid * 2 ;
            End loop in_loop ;
       End Loop out_loop ;
       if vn_counter = 10 then 
          goto start_point ;
       end if ;
     ...    
   END ;
   /
 * ----------------------------
       => 예상문제 : 다음은 procedure를 만드는 문장이다. 틀린곳은?
          create or replace procedure proc_1 is
          ..
          begin
            for cur_...... in cur_1
            ...
            loop ;
            close cur_1 ;
          end ;
          (1)위 문장에서 주의할 점은 end로 끝나는 부분에서 무엇을 end하는 것인가를
             기술해야 한다는 점이다. 즉, end proc_1 ; 이라고 기술해야 한다.
          (2)For Loop의 성격은 implicit cursor를 사용한다는 점에 유의해야 한다.
             즉, cursor close는 for loop가 끝나면 자동으로 이루어진다.
             따라서 manually로 cursor operation을 하려면 for loop에서 사용하지 않는다.
        => 예상문제 : procedure and function 차이는
                 [procedure]                    [function]
                 excute as a pl/sql stmt        called as part of an expression
                 no RETURN datatype             must contain a RETURN datatype
                 can return a value             must return a value
>>Control SQL Statement Processing
 1.cursor operation
   - declare cursor
   - open cursor
   - fetch data
   - close cursor
 * ----------------------------  
   DECLARE
       vc_name         emp.emp_name%type ;
       vn_deptid       emp.emp_dep_id%type ;
       cursor cur_item is
              select team_id from emp where team_id <> manager_id  ;
    ....
     ....
   BEGIN
       open cur_item ;
       Loop
           fetch cur_item into vn_deptid ;
           .. (varify using cursor attribute) ....
           ......
           exit when cur_item%notfound;
       End loop ;
       close cur_item ;
       commit ;
     ...    
   END ;
   /
 * ----------------------------
 - cursor attribute : %isopen    : cursor open 상태
                      %notfound  : fetch data 존재여부
                      %found     : fetch data 존재여부
                      %rowcount  : fetched data count
      => 예상문제 : What is a Cursor?
                    private SQL area to execute SQL statment and store processing information
      => 예상문제 : each fetch causes
                    the cursor to move its pointer to the next row in the active set
      => 예상문제 : cursor open시
                    open the cursor to execute the query and identify the active set
                    the cursor will now point to the first row in the active set
                    Although the query returns no row, PL/SQL does not raise an exception
>>Handle runtime execptions
  - PL/SQL Block중 execption은 data 처리중 특정 condition or error 에 부합된 결과가
    나타날때 시스템이 지정하거나 작성자가 임의로 지정한 마무리를 짖고
    Block을 빠져나간다. (goto 처럼 다른 처리를 하는것이 아니다)
  - Raise가 발생 했으나 적절한 exception이 없으면 그 PL/SQL Block은 Failure로 종료된다.
  - exception은 반드시 1 작업에 1회만 발생한다.
    왜냐면 sub block이 아닌이상 exception은 그 PL/SQL Block을 종료하기 때문
    ==> 예를 들어 아래 문제의 경우 위 1회 발생의 의미를 알 수 있다.
        What is the maximum number of handleres processed before PL/SQL block is exited
        when an exception occurs ? --> 당연히 1회 겠죠!
  - exception types
    Predefined Oracle 7 Server error : about 20 errors : do not declare, implicitly raised
    Non-Predefined Oracle 7 Server error : any other standard Oracle7 Server error : declare, implicitly raised
    User-defined error : defined by user : declare, explicitly raised
      => 예상문제 : SQL code is retruned the numeric value for the error code
                    0 - no exception encountered
                    1 - user-defined exception
                    +100 - NO_DATA_FOUND exception
                    negative_number - another Oracle7 Server error number
 * ----------------------------  
   DECLARE
       vc_name         emp.emp_name%type ;
       vn_deptid       emp.emp_dep_id%type ;
     ....
   BEGIN
      select team_id 
      into   vn_deptid
      from emp 
      where team_id <> manager_id  ;
      delete from emp_manager
      where manager_id = team_id ;
      commit ;
     ....
   EXCEPTION --> nested blocks을 가질수 없다
      WHEN  NO_DATA_FOUND THEN   >> data가 없음
         ........ ;
         rollback ;
      WHEN  TOO_MANY_ROWS THEN   >> 1개 이상의 data가 return됨
         rollback ;
      WHEN  OTHERS THEN          >> 그 밖의 errors
         ... error message ....
         rollback ;
 
   END ;
   /
 * ----------------------------
 SQL and PL/SQL 빈도입니다.
[Top]
No.
제목
작성자
작성일
조회
9242오라클 8i 인스톨
정재익
2002-01-05
5902
9238SQL, SQL*Plus (II)
정재익
2002-01-05
12004
9237SQL* SQL*Plus (I)
정재익
2002-01-05
16555
9236PL/SQL 요약
정재익
2002-01-05
13310
9199Oracle 에러별 원인 및 조치사항 몇가지
정재익
2002-01-02
16139
9079DATABASE LINK 사용 방법
정재익
2001-12-25
9658
9077Oracle7 에서 테이블스페이스 확장에 대해서
정재익
2001-12-25
6125
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2022 DSN, All rights reserved.
작업시간: 0.063초, 이곳 서비스는
	PostgreSQL v13.3으로 자료를 관리합니다