|
안녕하세요, 김일형입니다.
PL/pgSQL 매뉴얼을 번역하다 말고 잠시 옆길로 새어봤습니다.
제가 주로 하는 것이 JAVA이다 보니.... 한번 이 두 개를 붙여보고 싶더군요.
다음은 제가 실행해본 테스트 순서입니다.
1. PL/pgSQL을 사용가능하도록 database 만들기..
이것은 메뉴얼에 나와있습니다만 기본적으로 database에는 PL/pgSQL을 사용할 수 없습니다. 그래서 사용자가 직접 해당 db에 PL/pgSQL을 사용할 수 있도록 해주어야 합니다.
하지만 한줄만 쳐놓으시면 다시는 신경안써도 되죠.
$ createlang plpgsql bbs
라고 합니다. 위의 명령에서 bbs는 PL/pgSQL을 사용할 db명으로 제가 앞으로 테스트하면서 사용할 database이름입니다. 만일 어떤분이 PL/pgSQL을 ABC라는 db에서 사용하기를 원하신다면...
$ createlang plpgsql ABC
라고 입력하시면 됩니다. 아무런 메세지도 안떨어지고 끝납니다.
그러나 이 작업을 하시기 전에 반드시 postmaster 가 떠있어야 합니다. 안그럼 ...
psql: connectDBStart() // connect() failed: No such file or directory
Is the postmaster running locally
and accepting connections on Unix socket '/tmp/.s.PGSQL.5432'?
createlang: external error
이런 에러가 떨어집니다.
2. 작업용 테이블 작성하기
CREATE TABLE test (
id int,
name varchar
);
이 테이블은 사용자 식별 id하고 이름을 갖는 간단한 테이블입니다.
3. PL/pgSQL 함수 작성하기
예전에 올렸던 메뉴얼을 기준으로 함수를 다음과 같이 만들었습니다.
DROP FUNCTION myInsertRow(VARCHAR);
CREATE FUNCTION myInsertRow(VARCHAR) RETURNS int AS '
DECLARE
v_name ALIAS FOR $1;
maxNo int;
BEGIN
SELECT INTO maxNo max(id) FROM test;
IF NOT FOUND OR maxNo IS NULL THEN
RAISE NOTICE ''MAX Value is not found or null'';
maxNo := 0;
END IF;
maxNo := maxNo + 1;
INSERT INTO test VALUES( maxNo, v_name );
RETURN maxNo;
END;
' LANGUAGE 'plpgsql';
이 함수는 일단 첨가할 이름을 입력받고 현재 테이블에 입력된 id값 중에서 최대값을 뽑아낸 다음 거기(maxNo)에 1을 증가시켜 새로운 사용자 id를 만들어 낸다음 이 새 번호와 입력받은 이름으로 새로운 Row를 테이블에 추가합니다.
간단한 것이죠?
한번 코드를 보시면 그리 어렵지 않게 읽으실 수 있을 것입니다.
위의 코드를 myInsert.sql이라는 화일로 저장을 합니다.
4. PL/pgSQL 함수를 db에 넣기
$ psql /f myInsert.sql bbs
이렇게 하면 myInsert.sql에서 bbs라는 db로 위의 코드를 하나하나 집어넣습니다.
이것을 처음 입력시키면 ...
ERROR: RemoveFunction: function 'myinsertrow(varchar)' does not exist
psql:03.sql:1: ERROR: RemoveFunction: function 'myinsertrow(varchar)' does not
exist
CREATE
라고 에러가 발생하는데 당연한 것이 위의 코드 맨 윗줄에 일단 기존의 함수를 drop하는 부분이 있습니다. 당연히 처음 위의 코드를 넣을 때는 기존에 함수가 없으니 에러가 나죠. 이렇게 하는 이유는 일단 코드를 수정해서 입력을 하게되면 기존의 함수를 없애야하니까 그렇습니다.
5. 함수 테스트하기
위에처럼 함수를 일단 생성하시고 나면.... Java하고 붙이기 전에 시험을 해봐야 겠습니다.
$ psql bbs
이렇게 해서 bbs라는 db로 들어갑니다.
그리고
=# \df myinsertrow
라고 입력하면
List of functions
Result | Function | Arguments
/////////+/////////////+///////////////////
integer | myinsertrow | character varying
(1 row)
이렇게 나옵니다. 제대로 함수가 생성이 되었죠?
그럼 이 함수를 psql상에서 시험가동을 시켜보겠습니다.
# SELECT myInsertRow( 'My Name' );
NOTICE: MAX Value not found
myinsertrow
/////////////
1
(1 row)
=# SELECT * FROM test;
id | name
////+/////////
1 | My Name
(1 row)
어떻습니까 잘 되죠? 여기서 주목하실 것은 함수를 작동시키는 명령어가 'SELECT'입니다.
6. JAVA의 JDBC와 연결...
아래는 자바 코드입니다.
import java.sql.*;
public class pltest {
public pltest() {
String url = "jdbc:postgresql://127.0.0.1/bbs";
try {
Class.forName("org.postgresql.Driver");
Connection con = DriverManager.getConnection(url, "id_user", "" );
PreparedStatement ps = con.prepareStatement("SELECT myInsertRow( ? )");
ps.setString(1, "This is Name" );
ResultSet rs = ps.executeQuery();
int result = 0;
while(rs.next()) {
result = rs.getInt(1);
System.out.println( "NEW ID: " + result );
}
rs.close();
ps.close();
con.close();
}
catch( Exception ex ) {
System.out.println( ex.getMessage() );
}
}
public static void main(String[] args) {
pltest pltest1 = new pltest();
}
}
이렇게 그리 길지 않은 코드입니다.
중요한 부분만 간단하게 설명하겠습니다.
Class.forName("org.postgresql.Driver");
PostgreSQL의 JDBC Driver를 가져오는 부분입니다. 이것은 각 DBMS마다 다르게 되어있는 것입니다.
String url = "jdbc:postgresql://127.0.0.1/bbs";
Connection con = DriverManager.getConnection(url, "id_user", "" );
이것은 가져온 드라이버를 바탕으로 해당 address로 실제 DBMS로부터 connection을 가져오는 부분입니다. ip부분을 127.0.0.1로 되어있는 것은 DBMS하고 자바로 실행시키는 application하고 같은 기계안에 있다는 뜻이겠죠... 그리고 url뒷부분의 bbs는 db이름이구요.
PreparedStatement ps = con.prepareStatement("SELECT myInsertRow( ? )");
이 것은 다른 DB의 Stored Procedure를 실행하는 코드와 같은 부분입니다.
그런데 그 안에 들어가는 쿼리 문자열이 아까 전에 보셨던거랑 똑같죠... 매개변수 부분만 틀리고... 일단 이렇게 해서 쿼리문을 준비해 둡니다.
ps.setString(1, "This is Name" );
이렇게 해서 위의 '?'부분을 'This is Name'이라는 문자열로 치환을 시킵니다.
ResultSet rs = ps.executeQuery();
위의 문장은 이렇게 준비된 쿼리문을 실행시키고 반환값을 ResultSet으로 가져옵니다.
while(rs.next()) {
result = rs.getInt(1);
System.out.println( "NEW ID: " + result );
}
그래서 반환값이 있으면 첫번째 반환값을 result 변수로 가져오고 이 값을 표준 출력장치로 출력한다..... 아시지만 작성한 PL/pgSQL함수를 보면 거기에 int값을 반환하게 되있습니다. 그건 새로운 ID값이죠.
그래서 컴팔하고 돌려보면
NEW ID: 2
요렇게 나옵니다. 당연히 아까전 psql에서 하나 생성했으니까 지금은 새 ID가 2이겠죠.
그리고 psql에 가서 쿼리를 해보면....
=# SELECT * FROM test;
id | name
////+//////////////
1 | My Name
2 | This is Name
(2 rows)
이렇게 나옵니다. 아까 전에 입력한 거랑, 지금 자바를 통해 입력한 거랑입력이 되어있죠.
이렇게 함으로써 일반코드에서 2개의 쿼리 및 계산식을 코드로 구현을 해서 불러야 하는데 여기서는 그 것을 단순한 하나의 함수 호출하는 것으로 끝이 났습니다. 당연히 2개의 statement를 만들어 네트워크 넘어로 요청을 해야하는데 여기서는 1개의 요청으로 동일한 작업을 끝냈습니다.
이게 PL/pgSQL을 작성하는 또하나의 이유가 되겠죠....
도움이 되었기를 바랍니다.
그럼 즐겁게 DB를 디비십시요.
김일형.
|
