現在的ORM(如Hibernate)性能一直不是很理想, 一些大型的J2EE項目還是以JDBC為主, 但一直對SP(Stored Procedure)有抵制情緒, 搞得SQL滿天飛, 因最近幾周用PL/SQL弄歷史數據遷移的問題, 順便整理一下JDBC調用SP.
The simple SQL statement will always execute faster than calling a stored procedure. Why? Because with the stored procedure, you not only have the time needed to execute the SQL statement but also the time needed to deal with the overhead of the procedure call itself.
Stored procedures do have their uses. If you have a complex task that requires several SQL statements to complete,
and you encapsulate those SQL statements into a stored procedure that you then call only once, you'll get better performance than if you executed each SQL statement separately from your program. This performance gain is the result of your program not having to move all the related data back and forth over the network, which is often the slowest part of the data manipulation process. This is how stored procedures are supposed to be used with Oracle -- not as a substitute for SQL, but as a means to perform work where it can be done most efficiently.
Function and Procedure
The difference between a procedure and function is that a function returns a value, so it can be used as an evaluated item in an expression. A procedure does not return a value. However, both functions and procedures can have OUT or IN OUT variables that return values.
CREATE [OR_REPLACE] FUNCTION function_name
[(parameter_declaration [, parameter_declaration]...)]
RETURN datatype
[AUTHID {CURRENT_USER | DEFINER}]
[PARALLEL_ENABLE] [DETERMINISTIC] {IS | AS}
......
BEGIN statement [statement]...
[EXCEPTION exception_handler [exception_handler]...]
END [function_name];
[CREATE [OR_REPLACE]] PROCEDURE procedure_name
[(parameter_declaration [, parameter_declaration]...)]
[AUTHID {CURRENT_USER | DEFINER}] {IS | AS}
......
BEGIN statement [statement]...
[EXCEPTION exception_handler [exception_handler]...]
END [procedure_name];
Package
A package is a schema object that groups logically related PL/SQL types, variables, and subprograms.
Packages usually have two parts, a specification and a body; sometimes the body is unnecessary.
package_spec ::=
CREATE [OR_REPLACE] PACKAGE [schema_name .] package_name
[AUTHID {CURRENT_USER | DEFINER}] {IS | AS}
......
END [package_name];
package_body ::=
CREATE [OR_REPLACE] PACKAGE_BODY [schema_name .] package_name
{IS | AS} [PRAGMA SERIALLY_REUSABLE;]
......
[BEGIN statement [statement]...]
END [package_name];
IN, OUT, IN OUT
An IN parameter passes values to the subprogram being called.
An OUT parameter returns values to the caller of the subprogram.
An IN OUT parameter passes initial values to the subprogram being called, and returns updated values to the caller.
JDBC call Stored Procedure
CallableStatement cstmt = conn.prepareCall("{ ? = call md5( ? ) }");
// CallableStatement cstmt = conn.prepareCall("begin ? := md5( ? ); end;"); // oracle syntax
cstmt.registerOutParameter(1, Types.VARCHAR); // set out parameters
cstmt.setString(2, "idea"); // set in parameters
cstmt.execute();
String md5Str = cstmt.getString(1); // Getting OUT Parameter Values
cstmt.close();
參考:
1. Java Programming with Oracle JDBC
2. PL/SQL User's Guide and Reference
BTW: 有人在項目中使用oracle的Object-Relational SQL嗎? 性能如何? 開發效率?