java中存储过程_java中怎么用存储过程啊？-CSDN博客

本文链接：https://blog.csdn.net/weixin_36145472/article/details/114055916

展开全部

创建存储过程 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, ---分页动作，可以使62616964757a686964616fe59b9ee7ad9431333332626639上一页：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_query_page(?,?,?,?,?,?,?,?,?,?,?)}");

cs.setString(1, pageAction);

cs.setString(2, table);

cs.setString(3, queryCondition);

cs.setString(4, resultcol);

cs.setString(5, orderCondition);

cs.setString(6, orderstyle);

cs.setInt(7, pagesize);

cs.setInt(8, currentPage); cs.registerOutParameter(7, OracleTypes.INTEGER);

cs.registerOutParameter(8, OracleTypes.INTEGER);

cs.registerOutParameter(9, OracleTypes.INTEGER);

cs.registerOutParameter(10, OracleTypes.INTEGER);

cs.registerOutParameter(11, OracleTypes.CURSOR); cs.execute(); pageinfo = new PageDTO(); pageinfo.setCurrentPage(cs.getInt(7));

pageinfo.setCurrentPage(cs.getInt(8));

pageinfo.setTotalRecord(cs.getInt(9));

pageinfo.setTotalPage(cs.getInt(10)); ResultSet rs = (ResultSet) cs.getObject(11); List employees = new ArrayList(); EmployeeDTO employee = null;

while (rs.next()) { employee = new EmployeeDTO(); employee.setEmpId(rs.getInt("emp_id")); employee.setEmpName(rs.getString("emp_name")); employee.setSex(rs.getString("sex")); employee.setSal(rs.getDouble("sal")); employees.add(employee); }

pageinfo.setResult(employees); } finally { if (conn != null && !conn.isClosed()) { conn.close(); } }

return pageinfo; } public static void main(String[] args) { String pageAction = "nextPage";

int pagesize = 5; int currentpage = 2;

DBConnection db = new DBConnection(); try {

PageDTO pageinfo = db

.findOnePage(pageAction, pagesize, currentpage); List list = pageinfo.getResult(); System.out.println("总页数：" + pageinfo.getCurrentPage());

System.out.println("总记录数：" + pageinfo.getTotalRecord());

for (int i = 0; i < list.size(); i++) { System.out.println(list.get(i).toString());

}

} catch (SQLException e) {

e.printStackTrace();

}