答案:创建存储过程
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软件开发工程师的前景怎么样?