JDBC call Stored Procedure
来源:互联网 发布:用户画像 数据挖掘 编辑:程序博客网 时间:2024/05/21 10:58
JDBC call Stored Procedure
现在的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("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();
- JDBC call Stored Procedure
- JDBC Call Store Procedure
- jdbc call procedure
- How to call stored procedure in Hibernate
- How to call stored procedure in Hibernate
- JDBC CallableStatement Stored Procedure OUT parameter example
- Stored Procedure
- illustrates how to call a SQL Server stored procedure
- SQL Server 2005: Stored Procedure call activity statistics & execution time
- oracle call stored procedure with schema - PLS-00487 ORA-06550
- mybatis call stored procedure(MyBatis 调用存储过程)
- Spring jdbc call oralce procedure or function
- Understanding Stored Procedure
- SPGen - Stored Procedure Generator
- 存储过程 stored procedure
- MySQL Stored Procedure Programming
- Stored Procedure Tutorial
- DB2LOAD Stored Procedure
- 综合应急平台-数据库系统
- std :: this_thread
- 存储过程和函数的区别
- Git使用指南
- android gdb 调试c++代码
- JDBC call Stored Procedure
- 3幅图让你了解Spring AOP
- 如何优化关键词的质量度
- gcc 链接库的顺序问题
- 正则表达式基本语法
- Camshift算法原理及程序代码
- android缺少grep、cp、awk等命令? 下载安装busybox即可!
- makefile里PHONY的相关介绍
- SSH框架系列:Spring配置多个数据源