Jumat, April 10, 2009

Menjalankan Skrip Prosedural SQL (PSQL) di Firebird 2.x

Sejak Firebird 2.0 ada fitur untuk menjalankan perintah SQL seperti dalam stored procedure dengan perintah EXECUTE BLOCK.

Sintaks umumnya:
EXECUTE BLOCK [()]
[RETURNS ()]
AS
[]
BEGIN
PSQL statement(s)
END

::= paramname type = ? [, ]
::= paramname type [, ]
::= []
::= DECLARE [VARIABLE] varname type [= initvalue];

Tambahan di Firebird 2.5 :
[FOR] EXECUTE STATEMENT  [()]
[ON EXTERNAL [DATA SOURCE]
string-koneksi]
[WITH {AUTONOMOUS | COMMON} TRANSACTION]
[AS USER ]
[PASSWORD ]
[WITH CALLER PRIVILEGES]
[INTO ]
Format untuk string-koneksi
   [server-port]database_path
Unamed input parameter
EXECUTE BLOCK AS
DECLARE S VARCHAR(255);
DECLARE N INT = 100000;
BEGIN
S = 'INSERT INTO TTT VALUES (?, ?, ?)';

WHILE (N > 0) DO
BEGIN
EXECUTE STATEMENT (:S) (CURRENT_TRANSACTION, CURRENT_CONNECTION, CURRENT_TRANSACTION);
N = N - 1;
END
END

Perlu diingat, di beberapa sql client tools kadang memerlukan tambahan SET TERM seperti berikut:
set term #;
execute block (...)
as
begin
statement1;
statement2;
end
#
set term ;#
Dengan perintah EXECUTE BLOCK ini, penanganan, pengolahan, dan pemrosesan data di luar aplikasi dengan memanfaatkan sejumlah perintah SQL bisa dilakukan dengan memanfaatkan perintah PSQL.

Contoh:
Mengakses tabel di database firebird lain (inter-database query / query between firebird database) di server yang sama dan menggabungkan hasilnya:

set term #;
EXECUTE BLOCK
RETURNS (A INT, B INT, C INT, T varchar(10), S date)
AS
BEGIN

---- mengakses tabel di database
127.0.0.1:/opt/db/cobafb25.fdb
EXECUTE STATEMENT ('SELECT FIRST 1 Tran, Conn, ID FROM TTT')
ON EXTERNAL '127.0.0.1:/opt/db/cobafb25.fdb'
AS USER 'Teguh' PASSWORD 'pojokatas'
INTO :A, :B, :C;

---- dari koneksi sekarang - 127.0.0.1:/opt/db/kedua.fdb
SELECT a.A2, a.A3 FROM A a
into :T, :S;

SUSPEND;
END
#
set term;#


SET TERM saya tambahkan karena kode diatas dijalankan di Flamerobin 0.90 / 0.92.

Contoh lain, update suatu tabel firebird dari tabel lain:

set term #;
execute block
as
declare t1 integer;
declare emplcode integer;
declare t2 varchar(100);
declare t3 integer;
declare t4 integer;
declare t5 varchar(100);
begin
for select e.EMPLCODE, e.EMPLSTATUS, e.TAXSTATUS,
e.SEXCODE, e.POSITIONCODE
from MPLOYEE e
into :emplcode, :t1, :t2, :t3, :t4
do
update EVEMPLOYEE v
set v.EMPLOYMENTSTATUS = :t1,
v.TAXSTATUS = :t2,
v.SEXCODE = :t3,
v.POSITIONCODE = :t4
where v.EVALPERIOD=1 and v.EVALYEAR=2009
and v.EMPLCODE = :emplcode;
end;
#
set term ;#

Ini fitur bagus untuk DBA.

1 komentar:

Andiastika Intan Pratiwi Hasan mengatakan...

terimakasih postnya sangat membantu.