출처 : http://gurucode.egloos.com/1452887
OS: Microsoft Windows 2000 [Version 5.00.2195]
DBMS: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
작성자: 강명규
작성일: 2004-04-17
이 글은 Oracle and Java Stored Porcedures(www.developer.com/db/article.php/3337411)을 정리한 것임.
이 글에 나오는 예제또한 약간의 첨삭이 있지만, 원문에 충실히 할 것임.
오라클에서 stored procedure/function은 전통적으로 PL/SQL로 작성했었다.
사실, Native언어(C언어등 OS상에서 실행파일을 얻는)나 자바로도 가능하다.
특이하게 자바의 경우 오라클에서 VM을 제공하므로 별다른 복잡한 절자없이
PL/SQL처럼 손쉽게 스토어드 프로시저나 함수를 생성할 수 있다.
그럼, 왜 PL/SQL이 있는데 또 다른 언어를 지원하려는 것일까?
네이티브코드는 성능에 잇점이 있을 수 있다.
몇몇 글을 보면 네이티브코드의 경우, 오라클에 존재하는 PL/SQL로 작성된 패키지들을 전체를
네이티브코드로 변경해야 성능상의 이점을 볼 수 있다고 한다. PL/SQL과 섞어쓰면 성능이
오히려 떨어진다고 한다.
그럼 자바는?
실체 성능적인 부분을 측정해보지는 않았는데 PL/SQL과 혼합해서 써도 무방하다고 한다.
자바를 사용하면 좋은 점은?
어느 정도 데이터베이스와 독립적
PL/SQL을 별도로 배울 필요가 없음
개발팀에서 PL/SQL보다는 자바를 더 능숙하게 다루는 팀원이 많을 경우가 대개일 것임.
자바를 사용하면 찝찝한 점은?
성능이 아무래도 pl/sql로 작성한 것보다는 떨어진다.
오라클 SGA에 자바를 위한 메모리를 할당해줘야 한다.
즉, 성능과 리소스를 더 필요로 한다는 의미.
필자의 생각은?
PL/SQL로 작성하던 놈이라 별다른 것이 없다면 PL/SQL로 작성하고,
PL/SQL로 작성하기 어려우면 JAVA를 고려하는 것이 좋겠다.
들리는 소문에 의하면, 차후 오라클버전에서 PL/SQL을 없애고 자바로 대체한다는
믿거나 말거나 하는 얘기가 떠돈다. 사실 오라클사에서는 현재 PL/SQL은 현상유지 차원에서
계속 지원할 것이라고 하나, 자바쪽으로 비중이 옮겨가는 것은 사실인 것 같다.
결론은?
오라클 8i부터 자바로 stored procedure/function를 사용할 수 있다.
원래 pl/sql을 사용하던 사람은 계속 pl/sql을 쓰고, 처음하는 사람은 자바를 사용하도록 하자.
아무래도 둘이 섞어쓰면 찜찜할 것 같긴하다.
실제 자바소스가 변경되어, 오라클로 reload한후, 호출명세를 다시 설정해주지 않았더니 오라클이 맛가는 현상을 겪었다.
alter system kill session '';으로 어떻게 해보려고 했으나 감감 무소식.. shutdown abort밖에 없었다.
자, 이제 직관적으로 간단명료하게 진행하겠다.
우선 오라클에 테스트용 테이블을 생성한다.
SQL> create table test(id int, name varchar(10), salary float);
테이블이 생성되었습니다.
자바로 로직을 작성하고, loadjava를 이용하여 DB에 집어넣는다.
윈도 c:\oracle\ora92\bin\loadjava.bat
유닉스
[sky@gw1 sky]$ file $ORACLE_HOME/bin/loadjava
/u01/app/oracle/product/9.2.0/bin/loadjava: Bourne shell script text executable
[develope/load class]
D:\temp>type TestDML.java
import java.sql.*;
import oracle.jdbc.*;
public class TestDML
{
// 리턴값이 없으면 오라클 Stored Procedure
public static void addPerson(int id, String name, float salary)
{
System.out.println("새로운 사용자 등록");
try
{
Connection conn = DriverManager.getConnection("jdbc:default:connection:");
String sql = "insert into test values (?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,id);
pstmt.setString(2,name);
pstmt.setFloat(3,salary);
pstmt.executeUpdate();
pstmt.close();
}
catch(SQLException e)
{
System.err.println("ERROR! Adding Employee: " + e.getMessage());
}
}
// 리턴값이 있으면 오라클 Function
public static float getSalary(int id)
{
float salary = 0;
System.out.println(id + "의 급여");
try
{
ResultSet rs;
Connection conn = DriverManager.getConnection("jdbc:default:connection:");
String sql = "select salary from test where id=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,id);
rs = pstmt.executeQuery();
rs.next();
salary = rs.getFloat(1);
rs.close();
pstmt.close();
}
catch(SQLException e)
{
System.err.println("ERROR! Adding Employee: " + e.getMessage());
}
return salary;
}
}
-u : DB계정
-v : verbose feedback
-resolve : .class파일이 아닌 .java파일이므로 컴파일해주라.
D:\temp>loadjava -u kang/xxxxxx -v -resolve TestDML.java
arguments: '-u' 'kang/xxxxxx' '-v' '-resolve' 'TestDML.java'
created : JAVA$CLASS$MD5$TABLE
creating : source TestDML
created : CREATE$JAVA$LOB$TABLE
loading : source TestDML
creating : TestDML
resolving: source TestDML
오라클 kang계정을 보면, 뭔가 생성된 것이 보일 것이다.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
CREATE$JAVA$LOB$TABLE TABLE
JAVA$CLASS$MD5$TABLE TABLE
JAVA$OPTIONS TABLE
TEST TABLE
SQL> select object_name, object_type, status from user_objects
2 where object_name='TestDML';
OBJECT_NAME OBJECT_TYPE STATUS
--------------- ------------------ -------
TestDML JAVA CLASS VALID
TestDML JAVA SOURCE VALID
SQL>
위에서 status필드가 VALID가 아니라면 user_errors를 조회해보라.
loadjava는 .sqlj .properties .ser .jar .zip을 인식한다.
만일, kang계정이외에 maddog계정도 이 놈을 사용하겠다고 하면 다음과 같이 한다.
D:\temp>loadjava -u kang/xxxxxx -resolve -resolver "((* KANG)(* MADDOG)(* PUBLIC))" TestDML.java
[publising classes]
SQL이나 PL/SQL에서 자바로 생성한 클래스파일을 호출하려면 publish를 해야 하는데,
자바 클래스는 호출명세(call specification, call spec 혹은 PL/SQL wrapper이라고 불리기도 함)
를 생성/컴파일함으로써 publish될 수 있다.
호출명세는 자바 메소드의 인자와 리턴형을 오라클 SQL타입과 매핑하는 역활을 한다.
SQL> create or replace procedure add_person(id number, name varchar2, salary number)
2 as language java
3 name 'TestDML.addPerson(int, java.lang.String, float)';
4 /
프로시저가 생성되었습니다.
SQL> create or replace function get_salary(id number) return number
2 as language java
3 name 'TestDML.getSalary(int) return int';
4 /
함수가 생성되었습니다.
즉, 우리는 이제부터 add_person프로시저와 get_salary함수를 사용하면 되는 것이다.
자바출력은 트레이스파일로 쓰여지므로, 출력을 화면에 표시하도록 변경후 프로시저 호출
SQL> set serveroutput on
SQL> call dbms_java.set_output(2000);
호출이 완료되었습니다.
SQL> exec add_person(1,'강명규', 3000.01);
새로운 사용자 등록
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select get_salary(1) from dual;
GET_SALARY(1)
-------------
3000.01
함수는 다음과 같이 해도 된다.
SQL> var salary number;
SQL> call get_salary(1) into :salary;
1의 급여
호출이 완료되었습니다.
SQL> print salary;
SALARY
----------
3000.01
자바에서 PL/SQL을 호출하는 방법
CallableStatement cstmt = conn.prepareCall("{my_plsql_porc}");
[Usage Scenario]
오라클에서 OS상의 파일에 대한 작업을 하려면 UTL_FILE패키지를 이용해야 한다.
이것은 OS상의 파일에 접근할 수 있는 인터페이스를 제공하지만 여러가지로 불편하다.
이 경우 자바의 File I/O를 이용하면 더 효율적으로 작업할 수 있을 것이다.
D:\temp>type FileTest.java
import java.io.*;
class FileTest
{
public static String readFile(String usrFile)
{
String fileStr = new String();
String tmpStr = new String();
try
{
File file = new File(usrFile);
FileReader fr = new FileReader(usrFile);
LineNumberReader lnr = new LineNumberReader(fr);
do{
tmpStr = lnr.readLine();
fileStr += tmpStr;
} while(tmpStr != null);
lnr.close();
fr.close();
}
catch(Exception e)
{
System.err.println("ERROR! Handling File: " + e.getMessage());
}
return fileStr;
}
}
D:\temp>loadjava -u kang/xxxxxx -v -resolve FileTest.java
arguments: '-u' 'kang/xxxxxx' '-v' '-resolve' 'FileTest.java'
creating : source FileTest
loading : source FileTest
creating : FileTest
resolving: source FileTest
SQL> create or replace package my_java_utils is
2 function read_file(file varchar2) return varchar2;
3 end my_java_utils
4 ;
5 /
패키지가 생성되었습니다.
SQL> create or replace package body my_java_utils is
2 function read_file(file varchar2) return varchar2
3 as language java
4 name 'FileTest.readFile(java.lang.String) return java.lang.String';
5 end my_java_utils;
6 /
패키지 본문이 생성되었습니다.
SQL>
D:\temp>type hello
안녕!
나는 강명규라고 해!
SQL> conn system/xxxxxx
연결되었습니다.
SQL> exec dbms_java.grant_permission( 'KANG', 'SYS:java.io.FilePermission','d:\temp\hello', 'read' );
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> conn kang/xxxxxx
연결되었습니다.
SQL> set serveroutput on
SQL> call dbms_java.set_output(2000);
호출이 완료되었습니다.
SQL> select my_java_utils.read_file('d:\temp\hello') from dual;
MY_JAVA_UTILS.READ_FILE('D:\TEMP\HELLO')
--------------------------------------------------------------------------------
안녕!나는 강명규라고 해!null
SQL>
사실 위에서는 hello라는 파일로 했지만, alert.log파일에 대한 처리로 하면 좀 더 현실적일 것이다.
[작업한 내용 제거]
SQL> conn system/xxxxxx
SQL> exec dbms_java.revoke_permission( 'KANG', 'SYS:java.io.FilePermission','d:\temp\hello', 'read');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> connect kang/xxxxxx
연결되었습니다.
SQL> drop package my_java_utils;
패키지가 삭제되었습니다.
SQL> drop function get_salary;
함수가 삭제되었습니다.
SQL> drop procedure add_person;
프로시저가 삭제되었습니다.
D:\temp>dropjava -u kang/xxxxxx TestDML FileTest
끝
This article comes from dbakorea.pe.kr (Leave this line as is)
2012-07-19 (목) [출발시간] 
2012-07-23 (월) [도착시간] 
