lonelycat's cellar

다음과 같은 자료를 갖는 TABLE을 가정해보자.

Employee Name     Employee Address   City         State     ZIP
-------------     ----------------   -----------  -----    ---------
Donald Duck       101 Cool Pond      Bedford      OR       12345
Bugs Bunny        540 Carrot Way     Atlanta      GA       29292
Elmer Fudd
Taz Devil         999 Wildwoods      Nashville    TN       99999  

REPORT를 생성할 때에는 종종 CITY와 STATE를 COMMA(,)로 연결하여 출력하곤
한다.
이때 다음과 같이 COLUMN 간에 CONCATENATING를 이용하면 가능하다.

select employee_name,
        employee_address,
        city || `, ` || state || ` ` || zip  city_state_zip
   from decode_sample;


위의 문장은 다음과 같은 결과를 갖는다.

EMplOYEE_NAME        EMplOYEE_ADDRESS     CITY_STATE_ZIP
-------------------- -------------------- ------------------------
Donald Duck          101 Cool Pond        Bedford, OR 12345
Bugs Bunny           540 Carrot Way       Atlanta, GA 29292
Elmer Fudd                                ,
Taz Devil            999 Wildwoods        Nashville, TN 99999

위와 같은 경우에서 주소의 자료가 없는 경우 필요없는 COMMA만 출력되게 된다.
이런 경우에 DECODE문을 이용하여 다음과 같이 처리하면 깔끔한 OUTPUT을
받을 수 있게 된다.

select employee_name,
        employee_address,
        city || decode(`, ` || state,`, `,``,`, ` || state) || ` `
             || zip  city_state_zip
   from decode_sample;

EMplOYEE_NAME        EMplOYEE_ADDRESS     CITY_STATE_ZIP
-------------------- -------------------- ------------------------
Donald Duck          101 Cool Pond        Bedford, OR 12345
Bugs Bunny           540 Carrot Way       Atlanta, GA 29292
Elmer Fudd
Taz Devil            999 Wildwoods        Nashville, TN 99999

PURPOSE
---------

절대적인 값이 아닌 A와 B 사이의 값과 같은 range에 따른 값에 따라
DECODE function을 이용할 수 있을까? 이미 BETWEEN A AND B가 사용되지
못함을 경험했을 것이다.


Explanation
-------------

Emp table에서 2000과 4000사이의 값을 `2000~4000`로 4001과 5000
사이의 값을 `4001~5000`로 나머지 값들은 `other`로 display하고자 한다.


범위           Decode
-------------  ------
2000 - 4000     2000~4000
4001 - 5000     4001~5000
default         other


Example
---------

  select  empno, ename, sal,
          DECODE(sal, GREATEST(LEAST(sal, 4000), 2000), `2000~4000`,
                      GREATEST(LEAST(sal, 5000), 4001), `4001~5000`,
                      `other`)
          from emp;

           EMPNO ENAME                  SAL DECODE(SA
--------------- ---------- --------------- ---------
            7369 SMITH                  800 other
            7499 ALLEN                 1600 other
            7521 WARD                  1250 other
            7566 JONES                 2975 2000~4000
            7654 MARTIN                1250 other
            7698 BLAKE                 2850 2000~4000
            7782 CLARK                 2450 2000~4000
            7788 SCOTT                 3000 2000~4000
            7839 KING                  5000 4001~5000
            7844 TURNER                1500 other
            7876 ADAMS                 1100 other
            7900 JAMES                  950 other
            7902 FORD                  3000 2000~4000
            7934 MILLER                1300 other

방법1) SQL*plUS에서 각각의 TABLE에 대하여 ANALYZE를 실행하면
        USER_TABLES Data Dictionary에 각각의 record 건수가 등록된다.

방법2) SQLplUS에서 pl/SQL Program으로 처리한다.
==============================================================
create or replace procedure row_cnt
is
   cursor_name     integer;
   rows_processed  integer;
   rowcnt          number := 0;
   cursor c1 is select table_name from user_tables;
begin
   cursor_name := dbms_sql.open_cursor;
   for i in c1 loop
       dbms_sql.parse(cursor_name,
                      `select count(*) from `|| i.table_name,
                      dbms_sql.v7);
       dbms_sql.define_column(cursor_name,1,rowcnt);
       rows_processed := dbms_sql.execute(cursor_name);
       if dbms_sql.fetch_rows(cursor_name)> 0 then
          dbms_sql.column_value(cursor_name,1,rowcnt);
          dbms_output.put_line(rpad(i.table_name,30,` `)||` `
                               ||lpad(to_char(rowcnt),12,` `));
     end if;
   end loop;
   dbms_sql.close_cursor(cursor_name);
end;
==============================================================

$ sqlplus scott/tiger

SQL>  select trunc(date2-date1) || ` day  ` ||
              trunc(mod((date2-date1),1)*24) || ` hour  ` ||
              trunc(mod((date2-date1)*24,1)*60) || ` minute  ` ||
              trunc(round(mod((date2-date1)*24*60,1)*60)) || ` sec  `
              " Time Interval "
         from dual;

PURPOSE
-------
다음은 특정위치의 문자열이 한글인지 영문인지 즉, 문자유형을 판별하는
방법에 대해 간단한 예와 함께 소개한다.

Explanation
-----------
PL/SQL을 사용할 때 문자열의 특정 위치에 있는 문자가 ASCII인지, 한글의 첫번째
바이트인지, 또는 한글의 두번째 바이트인지를 확인해야 할 경우가 있습니다.
이런 경우, 문자 코드 값이 특정 값(예를 들어, KSC5601의 경우에는 0xA1)보다 큰지
작은지를 비교하여 판단하는 루틴을 사용하면 DB CharacterSet이 다른 경우에는
전혀 사용할 수 없습니다.  아래의 PL/SQL 함수는 Oralce WebServer의 NLS 문제를
해결하기 위해 만든 것입니다.  절대값과 비교하는 방식을 사용하지 않았으므로
DB CharacterSet에 관계없이 사용할 수 있습니다.
 
Example
-------
/* determine character type from cbuf of specified position bytepos */
/*   bytepos is zero-based */
/*   return : 0 if ascii, 1 if 1st byte of DBCS, 2 if 2nd byte of DBCS */
 
function nls_getchartype (cbuf in varchar2, bytepos in integer) return
integer
is
    loc integer:=0;
    ret integer:=0;
begin
    if cbuf is NULL then  
       return 0;  
    end if;
    
    while (loc <= bytepos)
    loop
       if (ret = 1 ) then
          ret := 2;
       elsif (length(substrb(cbuf, loc+1, 2)) = 1) then
          ret := 1;
       else
          ret := 0;
       end if;
       loc := loc + 1;
    end loop;
    return ret;
end;


위가 않되는 경우 아래의 function을 실행하십시요.

create or replace function nls (cbuf in varchar2, bytepos in integer) return
integer
is
    loc integer:=1;
    ret integer:=0;
begin
    if cbuf is NULL then  
       return 0;  
    end if;
    
    while (loc <= bytepos)
    loop
       if (ret = 1 ) then
          ret := 2;
       elsif (length(substrb(cbuf, loc, 2)) = 1) then
          ret := 1;
       else
          ret := 0;
       end if;
       loc := loc + 1;
    end loop;
    return ret;
end;

**실행 **


SQL>variable a number;
SQL>  exec :a := nls(`t하t`,1);

PL/SQL procedure successfully completed.

SQL> print a

          A
----------
          0

SQL> exec :a:=nls(`t하t`,2);

PL/SQL procedure successfully completed.

SQL> print a

          A
----------
          1

SQL> exec :a := nls(`t하t`,3);

PL/SQL procedure successfully completed.

SQL> print a

          A
----------
          2

PURPOSE
-------

간단한 xml 문서를 PLSQL을 이용해서 database 에
저장하고 PLSQL parser 를 이용해 validate 하는 방법을
예제를 통해 알아 봅니다.


Example
-------

Step1
-----

xml 문서를 저장할 table 을 생성합니다.

CREATE TABLE xml_doc (
docname    VARCHAR2(100) PRIMARY KEY,
doc        CLOB,
insertdate DATE DEFAULT SYSDATE);


Step 2
------

XML 문서는 os 의 physical directory 에 있으므로 oracle database
에 logical directory 를 만들어 mapping 시켜주어야 합니다.

SQL> CREATE DIRECTORY xmldocs AS `C:XML`;


만일 ORA-1031 에러가 발생할 경우 SYS 또는 SYSTEM user 로 접속해서
다음 명령으로 권한을 줍니다.

SQL> grant create any directory to scott;
Grant succeeded.


Step 3
------

database 에 넣을 XML 문서를 만듭니다.



  
     7844
     Manager
     100000
  
  
     100
     President
     200000
  



위에서 만든 emp.xml 파일을 db 에 insert 할 PLSQL procedure 를 생성합니다.


CREATE OR REPLACE PROCEDURE insertXML (dirname IN VARCHAR2,
                                       filename IN VARCHAR2)
IS
   xmlfile BFILE;
   myclob  CLOB;
BEGIN
   INSERT INTO xml_doc (docname, doc)
   VALUES (filename, empty_clob())
   RETURNING doc into myclob;  
  
   -- get a handle to the xml file on the OS
   xmlfile := Bfilename(dirname,filename);

   -- open the file
   DBMS_LOB.fileOpen(xmlfile);

   -- copy the contents of the file into the empty clob
   DBMS_LOB.loadFromFile(myclob, xmlfile, dbms_lob.getLength(xmlfile));

END insertXML;
/

Execute the Procedure

SQL> EXEC insertXML(`XMLDOCS`, `emp.xml`);

PL/SQL procedure successfully completed.



Step 4
------

insert 된 data 를 확인해 봅니다.

SQL> select * from xml_doc;

DOCNAME         DOC                                      INSERTDATE
--------------- ---------------------------------------- ---------------
emp.xml        
                          27-OCT-00
                                  
                                                  
                     7844

"예상치 못한 이별이 닥쳤을 때, 나를 절망시킨 건 아무것도 할 수 없다는 사실이었어.
그 상실감을, 그리움을, 난 통제할 수가 없지.
그래서 우린, 나이를 먹으면서 조금씩 덜 사랑하는 법을 배운 거야.
원하는 만큼만 아플 수 있도록, 통제할 수 있을 만큼만 그리워할 수 있도록.
어쩔 수 없는 사랑이라는 건 고통스러울 수밖에 없는 거니까."

from [원더 러브] http://www.magazinet.co.kr/Articles/article_view.php?article_id=47158&page=1&mm=004003000