Oracle9i 에서 PL/SQL 의향상된기능 Sandeepan Banerjee, Director of Product Mgmt, Oracle Server Technologies, Chris Racicot, Director, SQL/DSS, Oracle Server Technologies.
소개 PL/SQL은오라클의 SQL에대한프로시저형언어의확장입니다. PL/SQL을사용하면 SQL문을프로시저구조와혼합할수있습니다. PL/SQL로프로시저, 함수및패키지같은 PL/SQL 프로그램단위를정의하고실행할수있습니다. PL/SQL 프로그램단위를처리하는 PL/SQL 엔진은오라클서버를포함하여많은오라클제품의특수한구성요소입니다. 예를들어기존의 Employee 테이블의경우새로운 Employee 레코드를테이블에배치하는프로시저 HIRE_EMP를정의할수있습니다. PROCEDURE HIRE_EMP (name VARCHAR2, job VARCHAR2, mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER, deptno NUMBER) IS BEGIN.. INSERT INTO emp VALUES (emp_sequence.nextval, name, job, mgr, hiredate, sal, comm, deptno);.. END; 프로시저가다음과같이 Oracle SQL*Plus 또는 Oracle Enterprise Manager로부터호출될수있습니다. EXECUTE hire_emp ('TSMITH', 'CLERK', 1037, SYSDATE, 500, NULL, 20); PL/SQL은여러가지방법으로데이타베이스성능을향상시킬수있습니다. 정보는단한번만보내고그후사용될때불러내기때문에, 각각의 SQL문을실행하거나 PL/SQL 블록전체의텍스트를오라클로보내는것과비교하면네트워크를통해보내야하는정보의양이적습니다. 프로시저의컴파일된폼을데이타베이스에서쉽게이용할수있으므로실행시컴파일할필요가없습니다 프로시저가 SGA( 시스템전역영역 ) 의공유풀에이미존재하는경우에는디스크검색없이바로실행에들어갈수있습니다. PL/SQL은오라클의공유메모리기능을활용하기때문에, 다중사용자가실행하려면 PL/SQL 코드의단일카피하나만메모리에로드시키면됩니다. 많은사용자가동일코드를공유하게되면애플리케이션에필요한오라클메모리요구량이상당히줄어들게됩니다. 게다가 PL/SQL 프로시저나함수의일반집합중심의애플리케이션을설계함으로써코딩의중복을피하고생산성을높일수있습니다. 내장프로시저는애플리케이션의무결성과일관성을개선합니다. PL/SQL Enhancements In Oracle9i Page 2
애플리케이션을모두프로시저의일반그룹중심으로개발하면코딩오류의가능성을줄일수있습니다. 예를들어정확한결과의리턴을보장하기위해프로시저나함수를검사할수있으며, 일단검증된다음에는다시검사할필요없이모든애플리케이션에서재사용할수있습니다. 프로시저에의해참조된데이타구조가어떤식으로든수정되는경우에는프로시저만재컴파일하면됩니다. 해당프로시저를호출하는애플리케이션을반드시수정해야하는것은아닙니다. 아래그림 1은오라클서버에포함되는 SQL 및 PL/SQL 엔진을보여줍니다. Oracle Server 그림 1: Oracle SQL 및 PL/SQL 아키텍처 오라클은애플리케이션이데이타베이스에내장된프로시저를호출하는경우컴파일된프로그램단위 ( 즉, 프로시저의파싱된표현 ) 를 SGA( 시스템전역영역 ) 의공유풀로로드합니다. PL/SQL 및 SQL문실행기는프로시저내부에서문을처리하도록함께작업합니다. 프로시저측면이없는질의는 SQL 엔진에의해바로처리됩니다. PL/SQL은실행시간이돼야비로서완전한텍스트를알수있게되는동적 SQL문을실행할수있습니다. 동적 SQL문은실행시에프로그램으로입력되거나프로그램에의해구축되는문자열에저장됩니다. 따라서범용목적프로시저를작성할수있게됩니다. 예를들어동적 SQL을사용하면실행시에나이름을알수있게되는테이블에서작업하는프로시저를작성할수있습니다. 수년에걸쳐오라클은수많은기능을갖춘 PL/SQL을개선시켜, 오늘날과같이전세계의아주다양한애플리케이션에대해가장데이타집약적인작업을처리하는아주뛰어난엔진으로인정받기에이르렀습니다. ORACLE9i FOR PL/SQL 에새로추가된내용은? 최근몇년동안한꺼번에여러경향이나타나면서많은사람들이애플리케이션개발환경의진화를폭넓게느끼게되었습니다. 새로운채널 - 브라우저기반인트라넷애플리케이션, 포탈, 교환또는무선게 PL/SQL Enhancements In Oracle9i Page 3
이트웨이 -이최종사용자에게개방되었습니다. 점점더많은애플리케이션이글로벌, 다국어의 24x7 기반사용자의요구에부응해야만합니다. 이전보다내용은더풍부해지고데이타는더복잡합니다. 압축된개발주기는다양한틈새형솔루션을통합하려는환경에압력을가하고있습니다. 개발자는이러한도전에대처하기위해기술을업그레이드하고또그런새로운기회를활용하려고열심히노력하기때문에, 자기가이미알고있는것을발판으로삼기를바라고있습니다. 오라클의 SQL 및 PL/SQL 기술은폭넓게사용할수있으며이해하기쉽습니다. 위에서언급한것처럼기업애플리케이션의많은부분이이기술을사용하여구축됩니다. 가장인기있는 B2B 및 B2C 애플리케이션은오라클백엔드서버를사용합니다. 오라클의소프트웨어는가장많은노력을요하는컨텐트관리 (CM) 애플리케이션을위한컨텐트를제공합니다. 그러나최종애플리케이션의특징이무엇이든간에성공여부는개발플랫폼이얼마나기능적이고통합되어있으며사용하기쉽고강력한가에달려있습니다. Oracle9i 에서오라클은인터넷과인트라넷모두에서최고의개발플랫폼을유지하도록많은기능을추가했습니다. Oracle9i 에서 PL/SQL 부분에많은기술이향상되었습니다. 아키텍처의기능이향상되어 PL/SQL이이전보다훨씬빨라졌습니다. PL/SQL은효율적생성과 URI 참조액세스같은 XML 관련기능을제공합니다. PL/SQL은오브젝트유형의상속같은 SQL의많은주요향상기능에대한액세스를제공할뿐만아니라 Pipeline된테이블함수, HTTP ' 쿠키 ' 지원을포함하여많은기능적개선을제공하도록개선되어왔습니다. 각각의내용을좀더자세히알아보겠습니다. PL/SQL 고유컴파일및성능 PL/SQL에서는주요성능이많이향상되었습니다. 그중중요한것은 PL/SQL을위한고유컴파일, 벌크바인딩및 Pipeline된테이블함수입니다. PL/SQL의고유컴파일 Oracle9i 의 PL/SQL 고유컴파일러기능은더빠른액세스를위해 PL/SQL 코드를컴파일합니다. 가장큰이점은내장프로시저및트리거등을사용하는, 데이타집약적인애플리케이션에있습니다. PL/SQL이애플리케이션서버나데이타베이스서버에서실행되는경우성능이훨씬많이향상될수있습니다. 하나의로직을실행하는데소요되는주기가줄어들기때문에오라클서버의확장성역시개선될수있습니다. Oracle9i 에서 PL/SQL 라이브러리단위는바이트코드로인터프리트되지않고고유 C 코드로컴파일될수있습니다. 그럴경우라이브러리단위는공유라이브러리형태로파일시스템에저장됩니다. PL/SQL 함수나프로시저를컴파일하는프로세스는매우간단합니다. PL/SQL Enhancements In Oracle9i Page 4
ALTER FUNCTION my_func COMPILE; 컴파일은 PL/SQL 프로그램의실행을더빠르게합니다. 실행속도를향상시키는요인은다음과같습니다. 바이트코드의인터프리트와연관된오버헤드의제거 인터프리트된코드보다고유코드에서의보다효율적이고빠른제어흐름 PL/SQL 프로그램에대응하는컴파일된코드는 SGA와정반대인 PGA에매핑되어보다나은동시액세스를가능하게해줍니다. 여전히성능은많은애플리케이션별요인에좌우되지만, 고유컴파일덕분에 SQL 참조를포함하지않는 PL/SQL은 2-10 배더빨라질수있습니다. 고유컴파일을작동시키려면다음문을실행하면됩니다. ALTER SESSION SET plsql_compiler_switches=native; DBA에의해맨처음에한번설정된후에이렇게만하면오라클이고유코드를안전하게실행할수있습니다. 이설정은영구적이며프로시저가자동으로재컴파일되는경우고유모드에서재컴파일합니다. 벌크바인드 PL/SQL에이루어진많은기능적향상은서버에상주하는핵심비즈니스로직에성능상의이점을추가로제공합니다. 특히벌크바인드향상과 Bulk Dynamic SQL은주목할만합니다. 벌크바인딩은단일 DML문에서행의전체콜렉션을조작할수있게해줍니다. 일반적으로그러한바인딩은데이타베이스의호출횟수를줄이고, 긴트랜잭션을가진애플리케이션실행시내부상호작용을줄여줍니다. 벌크바인딩은성능을개선하면서 PL/SQL 레코드와테이블의사용을촉진해줍니다. Oracle9i 에서벌크바인드기능은더효율적이고편리한벌크바인드작업을지원하도록향상되었습니다. SELECT와 FETCH 절에서콜렉션의사용에대한제한이제거되었습니다. 벌크바인드에서의실패에대한오류처리가제공되었습니다. Oracle8i 에서소개된 Native Dynamic SQL은이제 BULK 작업을지원합니다. Bulk Dynamic SQL 의가장큰이점은성능면에서의개선으로서 SQL 문실행기와 PL/SQL 엔진사이간에문맥교환의횟수가감소합니다. 공동 SQL 파서 (Parser) 기존의 SQL과 PL/SQL은 SQL을위한개별적파서가있습니다. Oracle9i 은일반 SQL 파서가있어,SQL의파싱및분석시중복을피하고, PL/SQL이 SQL의변경사항이있을시모든 SQL 변경사항을알려줍니다. 파이프라인및병렬처리테이블함수 Oracle8i 제품을통해소개한바있는테이블함수는내, 외부의데이타와단순, 복잡한데이타를다양하게저장하여오라클에테이블로표현할수있다. 또한관계형메타포를사용하여이후그테이블에서연산 PL/SQL Enhancements In Oracle9i Page 5
할수있습니다. Oracle9i 는테이블함수의 Pipeline과병렬화를지원합니다. 테이블함수는고유 PL/SQL 인터페이스를사용하여 PL/SQL, 또는 Oracle Data Cartridge Interfaces를사용하여 Java 나 C로정의될수있습니다. 테이블함수는전체데이타소스가메모리에구체화되는것을기다리지않고, 결과가생성되자마자파이프라이닝함으로써응답시간을줄여줍니다. 파이프라인은생성된행을버퍼에넣을필요가없습니다. 테이블함수는호출하는동안각각여러행을리턴할수있습니다. 따라서호출의횟수가줄어들기때문에성능은개선됩니다. 투명한성능향상수많은이면최적화도 Oracle9i 의 PL/SQL 성능에도움을줍니다. 레코드기능과복사기능은훨씬더빨라지며, 패키지간참조는더욱효율적으로처리됩니다. PL/SQL 함수호출은각행을토대로한 SQL으로부터의 8i 보다현재 60% ( 혹은그이상 ) 정도빨라졌습니다. 이로인해현재일반표현식을내장프로시저로요약을하고있는 SQL 프로그래머의생활은훨씬단순해졌습니다. 또한이들은많은성능유형을선택하지않고도 SQL 표현식을사용할수있습니다. Oracle9i 사용하여 PL/SQL은빠르고신뢰할수있으면서데이타집약적인서버상주비즈니스로직을위한 envelope을확장합니다. PL/SQL 및 XML XML은대중적표준제품군일뿐아니라기초적인기술입니다. XML의이러한기술은네트워크자원을효율적으로이용하는느슨하게결합되고의미적으로풍부한차세대애플리케이션의구축을지원합니다. Oracle9i 의중요한설계목적은 XML을 SQL 및 PL/SQL 엔진과통합시키는것이며, XML과관련된몇가지중요한신기능이 Oracle9i 에서소개됩니다. XMLType Oracle9i 는고유 XMLType 데이타유형을도입하여 XML 문서를저장할수있습니다. XMLType 열에저장된데이타 ( 또는오브젝트테이블 ) 는 XPath 지원을받아 SQL로질의할수있습니다. 많은유용한함수가 XMLType을제공하여공동 XML 노드프로세싱연산을할수있습니다. 또한 XMLType은 PL/SQL 프로시저와함수로호출될수도있습니다. 더불어, Oracle9i 는 SQL 연산자및 PL/SQL 패키지폼으로고유 XML 생성및집계를알려줍니다. 이러한서버를기반으로한 XML의연산처리능력은상당히향상되었습니다. XML 생성및집계 Oracle8i 릴리스 3에서오라클은 XML-SQL Utility (XSU) 와이와관련된 XSQL Servlet을도입하여 SQL 질의로부터 XML을생성시킵니다. XSU는 Java 클라이언트프로그램으로서데이타베이스에실행되었습니다. Oracle9i의 SQL 및 PL/SQL은각기비슷한생성기능이있어중요한순서를지정하여 PL/SQL Enhancements In Oracle9i Page 6
XML 생성처리능력을증가시켰습니다. 물론 XSU는 Oracle9i XDK의일부로서계속사용할수있으며, 개발자가이전의 9i 데이타데이타베이스버전에액세스할수있도록도와줍니다. DBMS_XMLGEN DBMS_XMLGEN 패키지는 SQL 질의로부터 XML 문서를작성하고, CLOB로서결과를리턴할수있습니다. 이패키지는흥미로운 ' 인출 (fetch)' 인터페이스를제공하며, 이인터페이스를통하여리턴되는최대행, 건너뛸행을조절하여웹애플리케이션상의쪽수를표시합니다. DBMS_XMLGEN은유연성이매우높고, 리턴된결과에대한태그이름변경옵션을제공하여생성된 XML을소모시키는애플리케이션에맞춥니다. 다음은 DBMS_XMLGEN의예입니다. CREATE TABLE temp_clob_tab(result CLOB); DECLARE qryctx DBMS_XMLGEN.ctxHandle; result CLOB; BEGIN qryctx := dbms_xmlgen.newcontext ('SELECT * from employees;'); DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE'); DBMS_XMLGEN.setMaxRows(qryCtx, 5); LOOP result := DBMS_XMLGEN.getXML(qryCtx); EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed((qryCtx)=0); INSERT INTO temp_clob_tab VALUES(result); END LOOP; END; / 리턴된 XML 결과는다음과같습니다. <?xml version=''1.0''?> <ROWSET> <EMPLOYEE> <EMPLOYEE_ID>30</EMPLOYEE_ID> <LAST_NAME>SCOTT</LAST_NAME> <SALARY>20000<SALARY> PL/SQL Enhancements In Oracle9i Page 7
</EMPLOYEE> <EMPLOYEE> <EMPLOYEE_ID>31</EMPLOYEE_ID> <LAST_NAME>MARY</LAST_NAME> <AGE>25</AGE> </EMPLOYEE> </ROWSET> DBMS_XMLGEN은복잡한 XML을생성하는데사용될수있으며, SQL 오브젝트유형을사용하여중첩구조를나타낼수있습니다. 이러한사용법에관한상세한정보는 XML을위한 Oracle9iApplication Developers Guide를참조하면됩니다. 이와관련하여 SQL으로부터고유하게 XML 생성을지원하는새 SQL 연산자, SYS_XMLGEN 및 SYS_XMLAGG도참조하십시오. URI_References 서버의 XML 문서는서버내외부에존재하는다른 XML문서 ( 또는 Fragment) 를참조합니다. 따라서이러한참조를인텔리전트하게지정하고효율적으로이용하는메커니즘이요구됩니다. URI_Reference는 URL을일반화시킨개념으로 URL부분 ( http://www.oracle.com/xml/doc1#// 같은부분 ) 과 Fragment 부분 (warehouse_spec/custno같은부분 ) 으로나뉩니다. URI_References는두가지방식이있습니다. 첫째 DBURIType은인트라-데이타베이스참조에관한사항으로데이타베이스내부에서오브젝트를 ' 지시 ' 할수있습니다. 예를들어다음과같습니다. /SCOTT/EMPLOYEES/ROW[ADDRESS/STATE='CA']/CITY DBURITypes은스키마, 테이블, 행, 열, 열내부의오브젝트유형속성등과같은서로다른세밀한사항을처리합니다. 둘째, HTTPURIType은데이타베이스외부의문서나프래그먼트에액세스하는데사용할수있습니다. 당사는또한 URIType이라는기본유형도제공하고있습니다. 이 URIType은상기사항중하나에대한인스턴스를저장할수있는추상적유형이며, 참조가로컬이나원격위치에있는경우유용합니다. 다음은 URITypes의용례입니다. CREATE TABLE tax_deductible_tab ( uri_col UriType, max_deduction NUMBER(7,2), description VARCHAR2); INSERT INTO tax_deductible_tab VALUES ( PL/SQL Enhancements In Oracle9i Page 8
UriFactory.getURL('/SCOTT/Warehouses/ROW[Area=2500'), 2500.00, 'Scott's Warehouse Deductible'); INSERT INTO tax_deductible_tab VALUES ( UriFactory.getURL ('http://proxy.oracle.com/webaccts/pos/scott/po1'), 1000.00, 'Scott's Web Deductible'); 위의사례를통해알수있듯이, URITypes은데이타베이스내부든외부든참조를모델링하는일정한메커니즘을제공합니다. 이것은참조의대상이종종약하게유형화되어있고, 대상컨텐트가서로다른종류의리파지토리안과밖으로이동할수있는경우느슨하게결합된 XML 애플리케이션 ( 많은기업포탈에서뿐아니라많은 B2B 또는 B2C 애플리케이션에서일반적인 ) 을구축하는데상당히이롭습니다. 다음과같이 SQL 및 PL/SQL으로부터완벽하게 URITypes에액세스할수있습니다. SELECT e.ur_col.getclob() FROM tax_deductible_tab e; DECLARE V_uri UriType; BEGIN SELECT uri_col into v_uri FROM tax_deductible_tab WHERE Description LIKE 'Scott%'; printdataout(v_uri.getclob()); END; / URITypes은 XML 문서를열에대한매핑을향상시키며, XML 컨텐트의효율적인덱싱, 탐색및질의 (querying) 를돕습니다. PL/SQL의기능향상 PL/SQL에서 HTTP ' 쿠키 (Cookie)' 지원 Oracle8i는 UTL_TCP, UTL_HTTP, UTL_SMTP 등과같은많은인터넷패키지를사용했습니다. 이패키지들은성능을향상시키는더고유한구현으로 Oracle9i에서향상되었습니다. PL/SQL의또다른신기능은표준 UTL_HTTP 패키지와관련되어있습니다. UTL_HTTP의향상으로 HTTP 쿠키 (cookie) 는웹애플리케이션을실행할때에도사용할수있습니다. PL/SQL 프로그램은 UTL_HTTP 패키지에게지시하여웹애플리케이션이설정한 HTTP 쿠키집합을유지할수있습니다. 쿠키는 PL/SQL 프로그램이다른웹페이지에액세스할때상위애플리케이션으로리턴됩니다. 또한쿠키를처리하기위한연결기능도개선되었습니다. 각각의요구는세션일필요가없으며, 대신모든요청이동일한트랜잭션의일부일수있습니다. PL/SQL Enhancements In Oracle9i Page 9
PL/SQL에서의상속지원 Oracle8도입으로서버과관계형테이블의오브젝트는모든층에걸쳐같은데이타모델을지원하고있습니다. Oracle9i 의오라클의 Object-Relational 버전은상속, 다단계콜렉션, 유형진화등과같은기능의도입으로함수와연산기능의완성도를높였습니다. 상속과다단계콜렉션은 Java, C++ 또는 XML 에의해제공된기능과밀접한서버의모델링기능을나타내줍니다. 이러한기능은데이타베이스내의비즈니스오브젝트에대한모델링은쉬워지고, 층을통한데이타의통일성도쉽게이루어지게됩니다.. PL/SQL은가능한변수의개념을지원합니다. 즉수퍼타입 ( 혹은 REF에서 REF로 ) 인변수가하위타입 ( 혹은 REF에서 REF로 ) 으로변수개념이바뀔수있습니다. 과부하된메소드는다형적으로디스패치할수있습니다. 오브젝트에서호출된메소드는런타임유형을기반으로하는특정구현에서가상으로디스패치됩니다. DECLARE person_var person_type; BEGIN person_var := person_type(...); person_var.some_method(); -- invokes some_method() of person person_var := employee_type(...); -- employee_type inherits from -- person type and overrides -- some_method() person_var.some_method(); -- invokes some_method() of employee END; / PL/SQL 은 Oracle9i 의다단계콜렉션과다른객체관계형향상역시지원합니다. PL/SQL CASE 표현식 Oracle9i 에는 SQL에대한 ANSI 표준 CASE 표현식지원이추가되어있습니다. 이기능을이중화한것이 PL/SQL CASE 문이며, 이는 SQL:1999 CASE 표현식의문법규칙을지원합니다. NULLIF는익숙한 NVL 함수와반대인반면, COALESCE는값목록이있다는점을제외하면거의 NVL과유사합니다. Datatype 향상 Oracle9i 의새로운몇가지데이타유형이 SQL에도입되었습니다. 이들중지구촌화된일자-시간데이타유형, TIMESTAMP 및 INTERVAL 데이타유형은단연주목할만합니다. 이데이타유형은글로벌 e-business 애플리케이션이시간과관계된정확한정보를 1초또는 10억분의 1초까지추적할수있습 PL/SQL Enhancements In Oracle9i Page 10
니다. 또한이유형은함수및프로시저에서의파라미터뿐아니라함수의리턴값으로서 PL/SQL 변수선언에서사용될수있습니다. Oracle9i 도 UNICHAR, UNIVARCHAR2 및 UNICLOB 같은유니코드데이타유형을도입하였으며, PL/SQL에서도지원됩니다. 모든 PL/SQL 함수및프로시저는인수로문자열을택하며, 현재 UTF-16 인수를받아들이고리턴할수있습니다. 결론오라클의시간검사 (time-tested) 플랫폼의중심에는 SQL 및 PL/SQL이있습니다. Oracle9i 의아키텍처향상으로 PL/SQL의기능은상당히빨라졌습니다. PL/SQL은과거에는없었던 XML과통합되었습니다. 또한 PL/SQL의기능이향상되어상당한기능적개선이제공되고있습니다. 이러한향상된기능으로오라클개발은다른프레임워크가미래를위해약속하는것을사용할수있도록합니다. PL/SQL Enhancements In Oracle9i Page 11
한국오라클 ( 주 ) 서울특별시강남구삼성동 144-17 삼화빌딩대표전화 : 2194-8000 FAX : 2194-8001 한국오라클교육센타서울특별시영등포구여의도동 23-10 SK증권빌딩 11층, 19층, 20층대표전화 : 3779-4000 FAX : 3779-4100~1 대전사무소대전광역시서구둔산동 929번지대전둔산사학연금회관 18층대표전화 : (042)483-4131~2 FAX : (042)483-4133 대구사무소대구광역시동구신천동 111번지영남타워빌딩 9층대표전화 : (053)741-4513~4 FAX : (053)741-4515 부산사무소부산광역시동구초량동 1211~7 정암빌딩 8층대표전화 : (051)465-9996 FAX : (051)465-9958 울산사무소울산광역시남구달동 1319-15번지정우빌딩 3층대표전화 : (052)267-4262 FAX : (052)267-4267 광주사무소광주광역시서구양동 60-37 금호생명빌딩 8층대표전화 : (062)350-0131 FAX : (062)350-0130 고객에게완전하고효과적인정보관리솔루션을제공하기위하여오라클사는전세계 145 개국에서제품, 기술지원, 교육및컨설팅서비스를제공하고있습니다. http://www.oracle.com http://www.oracle.com/kr