存储过程如下
CREATE OR REPLACE FUNCTION "FROWID" RETURN varchar
AS
PRAGMA AUTONOMOUS_TRANSACTION;
i_rowid varchar(100);
i_cnt int;
str varchar(1000);
BEGIN
update oa_keys_tab set keymaxid = keymaxid + 1 where keytable = 'ZFKH_KHXX';
commit;
select keymaxid into i_cnt from oa_keys_tab where keytable = 'ZFKH_KHXX';
select to_char(sysdate,'yyyymm')||lpad(i_cnt,10,0) into i_rowid from dual;
return i_rowid;
END fRowID;
CREATE OR REPLACE FUNCTION "FROWID" RETURN varchar
AS
PRAGMA AUTONOMOUS_TRANSACTION;
i_rowid varchar(100);
i_cnt int;
str varchar(1000);
BEGIN
update oa_keys_tab set keymaxid = keymaxid + 1 where keytable = 'ZFKH_KHXX';
commit;
select keymaxid into i_cnt from oa_keys_tab where keytable = 'ZFKH_KHXX';
select to_char(sysdate,'yyyymm')||lpad(i_cnt,10,0) into i_rowid from dual;
return i_rowid;
END fRowID;
下面接着上面的例子。这次我们查询Dylan Thomas逝世时的年龄。这次的存储过程使用PostgreSQL的pl/pgsql:create function snuffed_it_when (VARCHAR) returns integer 'declare poet_id NUMBER; poet_age NUMBER;begin -- first get the id associated with the poet. SELECT id INTO poet_id FROM poets WHERE name = $1; -- get and return the age. SELECT age INTO poet_age FROM deaths WHERE mort_id = poet_id; return age;end;' language 'pl/pgsql';另外,注意pl/pgsql参数名通过Unix和DOS脚本的$n语法引用。同时,也注意嵌入的注释,这是和Java代码相比的另一个优越性。在Java中写这样的注释当然是可以的,但是看起来很凌乱,并且和SQL语句脱节,必须嵌入到Java String中。
下面是调用这个存储过程的Java代码:connection.setAutoCommit(false);
CallableStatement proc =
connection.prepareCall("{ ? = call snuffed_it_when(?) }");
proc.registerOutParameter(1, Types.INTEGER);
proc.setString(2, poetName);
cs.execute();
int age = proc.getInt(2); 如果指定了错误的返回值类型会怎样?那么,当调用存储过程时将抛出一个RuntimeException,正如你在ResultSet操作中使用了一个错误的类型所碰到的一样。
proc.registerOutParameter(1,Types.VARCHAR);proc.execute();
String returnStr = proc.getString(1);