当前位置:编程学习 > JAVA >>

java中怎么用存储过程啊?

答案:

创建存储过程

create or replace procedure p_find_emp(

i_emp_id in tb_employee.emp_id%type

)

is

v_str_emp_name tb_employee.emp_name%type;

begin

select emp_name into v_str_emp_name from tb_employee where emp_id=i_emp_id;

dbms_output.put_line('该雇员名字叫:'||v_str_emp_name);

end p_find_emp;

oracle 分页

--创建游标

create or replace package pkg_query is

type cur_result_type is ref cursor;--游标的类型

end pkg_query;

create or replace procedure p_query_page(

str_page_action in varchar2, ---分页动作,可以使上一页:previous_page,下一页:next_page,

----首页:first_page,尾页:last_page,指定页:page_number

str_query_table in varchar2, -----需要查询的表,可以以单表或通过连接出来的虚拟表

str_query_condition in varchar2,-----分页的查询条件

str_result_col in varchar2,-----需要输出的字段名

str_order_condition in varchar2,-----查询的排序条件

str_order_style in varchar2,-----排序的风格,升序或降序

i_page_size in out number,-------每页显示的数据条数

i_current_page in out number, ----当前页索引

i_total_record out number,-----当前符合条件的总记录条数

i_total_page out number,-----当前符合条件的总页数

cur_query_result out pkg_query.cur_result_type------查询的结果

)

is

v_str_query_sql varchar2(10000):='';-----查询的sql语句

v_i_start_record number(10):=0;----起始记录位置

v_i_end_record number(10):=0;----终止记录的位置

begin

-------检验指定需要查询的表的参数是否为空

if(str_query_table is null or str_query_table = '') then

raise_application_error(-20001,'需要查询的表不能为空');

end if;

v_str_query_sql:='select count(*) from '||str_query_table;

------当查询的条件不为空时,将相应的查询条件拼接到sql中

if(str_query_condition is not null and str_query_condition <> '')then

v_str_query_sql:=v_str_query_sql||'where '||str_query_condition;

end if;

-----PL/Sq 动态调用sql

execute immediate v_str_query_sql into i_total_record;

--------检测每页数据量,如果小于等于零,把每页数据量设为默认值10;

if ( i_page_size <= 0 )then

i_page_size := 10;

end if;

------求当前符合条件的信息的总页数

if mod(i_total_record,i_page_size)=0 then

i_total_page := (i_total_record/i_page_size);

else

i_total_page := trunc(i_total_record/i_page_size)+1;

end if;

------根据当前的分页动作转换当前页索引

case str_page_action

when 'first_page' then i_current_page := 1;

when 'last_page' then i_current_page := i_total_page;

when 'previous_page' then i_current_page := i_current_page-1;

when 'next_page' then i_current_page := i_current_page+1;

when 'page_number' then i_current_page := i_current_page;

else

i_current_page := 1;

end case;

------求起始记录的索引位置和终止记录的索引位置

v_i_start_record := (i_current_page-1)*i_page_size+1;

v_i_end_record := i_current_page*i_page_size;

-----根据以上结果拼接sql语句。

v_str_query_sql:='select ';

if (str_result_col is null or str_result_col='') then

raise_application_error(-20002,'需要输出的字段不能为空');

end if;

v_str_query_sql:=v_str_query_sql||str_result_col||' from '||str_query_table||' where 1=1 ';

------当查询条件不为空时,把相应的查询条件拼接到sql语句中

if (str_query_condition is not null and str_query_condition <> '') then

v_str_query_sql:=v_str_query_sql||str_query_condition;

end if;

----当查询的条件不等于空,将查询条件拼接到sql中

if (str_order_condition is not null and str_order_condition <> '') then

v_str_query_sql:=v_str_query_sql||' order by '||str_order_condition;

if str_order_style is not null and str_order_style <> '' then

v_str_query_sql:=v_str_query_sql||' '||str_order_style;

end if;

end if;

v_str_query_sql:='select * from ( select A.*,rownum rn from ( '||v_str_query_sql

||' ) A where rownum <='||v_i_end_record||' ) B where rn >= '||v_i_start_record;

dbms_output.put_line(v_str_query_sql);

open cur_query_result for v_str_query_sql;

end p_query_page;

ok 现在看下怎么样调用上面的存储过程

package com.softeem.dbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import oracle.jdbc.driver.OracleTypes;

public class DBConnection {

private final static String DRIVER_CLASS = "oracle.jdbc.driver.OracleDriver";

private final static String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

private final static String USER_NAME = "tangzheng";

private final static String PASSWORD = "123456";

public static Connection getconnection() {

Connection conn = null;
try {
Class.forName(DRIVER_CLASS);
} catch (ClassNotFoundException e) {
System.out.println("驱动加载失败");

e.printStackTrace();
}

try {
conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
} catch (SQLException e) {
System.out.println("获取连接失败");
e.printStackTrace();
}

return conn;

}

public PageDTO findOnePage(String pageAction, int pagesize, int currentPage)
throws SQLException {

String table = "tb_employee emp";
String queryCondition = "emp.emp_id<15";
String resultcol = "*";
String orderCondition = "emp.emp_id";
String orderstyle = "asc";
Connection conn = getconnection();
PageDTO pageinfo = null;
try {

CallableStatement cs = conn
.prepareCall("{call p_qu

上一个:什么是java的回调机制
下一个:java软件开发工程师的前景怎么样?

CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,