我给你贴过来了。转自http://blog.csdn.net/liujun999999/archive/2004/12/18/221112.aspx 关于存储过程的知识,很多人好像就熟悉我们所讨论的这些。如果这是存储过程的全部功能,那么存储过程就不是其它远程执行机制的替换方案了。存储过程的功能比这强大得多。 当你执行一个SQL查询时,DBMS创建一个叫做cursor(游标)的数据库对象,用于在返回结果中迭代每一行。ResultSet是当前时间点的游标的一个表示。这就是为什么没有缓存或者特定数据库的支持,你只能在ResultSet中向前移动。 某些DBMS允许从存储过程中返回游标的一个引用。JDBC并不支持这个功能,但是Oracle、PostgreSQL和DB2的JDBC驱动器都支持在ResultSet上打开到游标的指针(pointer)。 设想列出所有没有活到退休年龄的诗人,下面是完成这个功能的存储过程,返回一个打开的游标,同样也使用PostgreSQL的pl/pgsql语言: [pre]create procedure list_early_deaths () return refcursor as ' declare toesup refcursor; begin open toesup for SELECT poets.name, deaths.age FROM poets, deaths -- all entries in deaths are for poets. -- but the table might become generic. WHERE poets.id = deaths.mort_id AND deaths.age < 60; return toesup; end; ' language 'plpgsql';[/pre] 下面是调用该存储过程的Java方法,将结果输出到PrintWriter: PrintWriter: static void sendEarlyDeaths(PrintWriter out) { Connection con = null; CallableStatement toesUp = null; try { con = ConnectionPool.getConnection(); // PostgreSQL needs a transaction to do this... con.setAutoCommit(false); // Setup the call. CallableStatement toesUp = connection.prepareCall("{ ? = call list_early_deaths () }"); toesUp.registerOutParameter(1, Types.OTHER); getResults.execute(); ResultSet rs = (ResultSet) getResults.getObject(1); while (rs.next()) { String name = rs.getString(1); int age = rs.getInt(2); out.println(name + " was " + age + " years old."); } rs.close(); } catch (SQLException e) { // We should protect these calls. toesUp.close(); con.close(); } }因为JDBC并不直接支持从存储过程中返回游标,我们使用Types.OTHER来指示存储过程的返回类型,然后调用getObject()方法并对返回值进行强制类型转换。 这个调用存储过程的Java方法是mapping的一个好例子。Mapping是对一个集上的操作进行抽象的方法。不是在这个过程上返回一个集,我们可以把操作传送进去执行。本例中,操作就是把ResultSet打印到一个输出流。这是一个值得举例的很常用的例子,下面是调用同一个存储过程的另外一个方法实现: public class ProcessPoetDeaths { public abstract void sendDeath(String name, int age); }static void mapEarlyDeaths(ProcessPoetDeaths mapper) { Connection con = null; CallableStatement toesUp = null; try { con = ConnectionPool.getConnection(); con.setAutoCommit(false); CallableStatement toesUp = connection.prepareCall("{ ? = call list_early_deaths () }"); toesUp.registerOutParameter(1, Types.OTHER); getResults.execute(); ResultSet rs = (ResultSet) getResults.getObject(1); while (rs.next()) { String name = rs.getString(1); int age = rs.getInt(2); mapper.sendDeath(name, age); } rs.close(); } catch (SQLException e) { // We should protect these calls. toesUp.close(); con.close(); } }这允许在ResultSet数据上执行任意的处理,而不需要改变或者复制获取ResultSet的方法: static void sendEarlyDeaths(final PrintWriter out) { ProcessPoetDeaths myMapper = new ProcessPoetDeaths() { public void sendDeath(String name, int age) { out.println(name + " was " + age + " years old."); } }; mapEarlyDeaths(myMapper); }这个方法使用ProcessPoetDeaths的一个匿名实例调用mapEarlyDeaths。该实例拥有sendDeath方法的一个实现,和我们上面的例子一样的方式把结果写入到输出流。当然,这个技巧并不是存储过程特有的,但是和存储过程中返回的ResultSet结合使用,是一个非常强大的工具。
ps.registerOutParameter(4,OracleTypes.CURSOR); ps.execute();我也是在这里出错,不能执行。 ORACLE存储过程返回两个结果集。怎么都搞不定。 附存储过程代码: create or replace procedure SP_tyu( V_p_gybh IN int, -- 员工编号 V_p_gymm IN varchar2, -- 员工密码 V_p_czzd IN varchar2, -- 操作站点 V_khbh IN int, -- 客户编号 -1,忽略此条件,三者必输其一 V_jjzh IN varchar2, -- 空忽略此条件,三者必输其一 V_zjhm IN varchar2, -- 证件号码 空忽略此条件 V_fwmm IN varchar2, -- 服务密码 加密传输,加密方式用md5 RC1 IN OUT jz_oraoledb.m_refcur, RC2 IN OUT jz_oraoledb.m_refcur) AS nb_row int; nb_khbh int; nb_jjzh VARCHAR2(12); v_khzt number := -1; BEGIN IF (V_khbh is null or V_khbh = 0 or V_khbh = -1) Then IF V_jjzh is not null THEN select khbh into nb_khbh from t_jjzhxx where jjzh = V_jjzh; IF SQL%rowcount = 0 THEN OPEN RC1 FOR SELECT -26071 AS errorcode, '该客户不存在!' AS errormsg FROM DUAL; RETURN; END IF; ELSE IF V_zjhm is null THEN OPEN RC1 FOR SELECT -26071 AS errorcode, '客户标识参数有误!' AS errormsg FROM DUAL; RETURN; END IF; select khbh into nb_khbh from t_kh where zjhm = V_zjhm; IF SQL%rowcount = 0 THEN OPEN RC1 FOR SELECT -26071 AS errorcode, '该客户不存在!' AS errormsg FROM DUAL; RETURN; END IF; END IF; ELSE BEGIN nb_row := 0; select 1 into nb_row from dual where exists(select 1 from t_kh where (khbh = V_khbh)); exception when others then nb_row := 0; END; IF nb_row = 0 THEN OPEN RC1 FOR SELECT -26071 AS errorcode, '该客户不存在!' AS errormsg FROM DUAL; RETURN; END IF; nb_khbh := V_khbh; END IF; BEGIN nb_row := 0; select 1 into nb_row from dual where exists(select 1 from t_kh where khbh = nb_khbh and khmm = V_fwmm); exception when others then nb_row := 0; END; IF nb_row = 0 THEN OPEN RC1 FOR SELECT -26072 AS errorcode, '客户密码不符!' AS errormsg FROM DUAL; RETURN; END IF; nb_jjzh := ''; select jjzh into nb_jjzh from t_jjzhxx where khbh = nb_khbh; select khzt into v_khzt from t_kh where khbh = nb_khbh; if v_khzt <>1 then BEGIN OPEN RC1 FOR SELECT -26077 as errorcode, '客户状态异常!' as errormsg FROM DUAL; RETURN; END; end if;
OPEN RC1 FOR SELECT 0 as errorcode, '校验客户服务密码成功!' as errormsg FROM DUAL; OPEN RC2 FOR select khbh,nb_jjzh as jjzh,khxm,pybm,'' as zjmc,zjhm,khlx,khzt,khrq,khxb,csrq,email from t_kh where khbh = nb_khbh; EXCEPTION WHEN OTHERS THEN OPEN RC1 FOR SELECT -10006 as errorcode, '校验客户服务密码异常!' as errormsg FROM DUAL; END;
http://blog.csdn.net/liujun999999/archive/2004/12/18/221112.aspx打不开呀。
关于存储过程的知识,很多人好像就熟悉我们所讨论的这些。如果这是存储过程的全部功能,那么存储过程就不是其它远程执行机制的替换方案了。存储过程的功能比这强大得多。
当你执行一个SQL查询时,DBMS创建一个叫做cursor(游标)的数据库对象,用于在返回结果中迭代每一行。ResultSet是当前时间点的游标的一个表示。这就是为什么没有缓存或者特定数据库的支持,你只能在ResultSet中向前移动。
某些DBMS允许从存储过程中返回游标的一个引用。JDBC并不支持这个功能,但是Oracle、PostgreSQL和DB2的JDBC驱动器都支持在ResultSet上打开到游标的指针(pointer)。
设想列出所有没有活到退休年龄的诗人,下面是完成这个功能的存储过程,返回一个打开的游标,同样也使用PostgreSQL的pl/pgsql语言:
[pre]create procedure list_early_deaths () return refcursor as '
declare
toesup refcursor;
begin
open toesup for
SELECT poets.name, deaths.age
FROM poets, deaths
-- all entries in deaths are for poets.
-- but the table might become generic.
WHERE poets.id = deaths.mort_id
AND deaths.age < 60;
return toesup;
end;
' language 'plpgsql';[/pre]
下面是调用该存储过程的Java方法,将结果输出到PrintWriter:
PrintWriter:
static void sendEarlyDeaths(PrintWriter out)
{
Connection con = null;
CallableStatement toesUp = null;
try
{
con = ConnectionPool.getConnection(); // PostgreSQL needs a transaction to do this...
con.setAutoCommit(false); // Setup the call.
CallableStatement toesUp
= connection.prepareCall("{ ? = call list_early_deaths () }");
toesUp.registerOutParameter(1, Types.OTHER);
getResults.execute(); ResultSet rs = (ResultSet) getResults.getObject(1);
while (rs.next())
{
String name = rs.getString(1);
int age = rs.getInt(2);
out.println(name + " was " + age + " years old.");
}
rs.close();
}
catch (SQLException e)
{
// We should protect these calls.
toesUp.close();
con.close();
}
}因为JDBC并不直接支持从存储过程中返回游标,我们使用Types.OTHER来指示存储过程的返回类型,然后调用getObject()方法并对返回值进行强制类型转换。
这个调用存储过程的Java方法是mapping的一个好例子。Mapping是对一个集上的操作进行抽象的方法。不是在这个过程上返回一个集,我们可以把操作传送进去执行。本例中,操作就是把ResultSet打印到一个输出流。这是一个值得举例的很常用的例子,下面是调用同一个存储过程的另外一个方法实现:
public class ProcessPoetDeaths
{
public abstract void sendDeath(String name, int age);
}static void mapEarlyDeaths(ProcessPoetDeaths mapper)
{
Connection con = null;
CallableStatement toesUp = null;
try
{
con = ConnectionPool.getConnection();
con.setAutoCommit(false); CallableStatement toesUp
= connection.prepareCall("{ ? = call list_early_deaths () }");
toesUp.registerOutParameter(1, Types.OTHER);
getResults.execute(); ResultSet rs = (ResultSet) getResults.getObject(1);
while (rs.next())
{
String name = rs.getString(1);
int age = rs.getInt(2);
mapper.sendDeath(name, age);
}
rs.close();
}
catch (SQLException e)
{
// We should protect these calls.
toesUp.close();
con.close();
}
}这允许在ResultSet数据上执行任意的处理,而不需要改变或者复制获取ResultSet的方法:
static void sendEarlyDeaths(final PrintWriter out)
{
ProcessPoetDeaths myMapper = new ProcessPoetDeaths()
{
public void sendDeath(String name, int age)
{
out.println(name + " was " + age + " years old.");
}
};
mapEarlyDeaths(myMapper);
}这个方法使用ProcessPoetDeaths的一个匿名实例调用mapEarlyDeaths。该实例拥有sendDeath方法的一个实现,和我们上面的例子一样的方式把结果写入到输出流。当然,这个技巧并不是存储过程特有的,但是和存储过程中返回的ResultSet结合使用,是一个非常强大的工具。
ps.execute();我也是在这里出错,不能执行。
ORACLE存储过程返回两个结果集。怎么都搞不定。
附存储过程代码:
create or replace procedure SP_tyu(
V_p_gybh IN int, -- 员工编号
V_p_gymm IN varchar2, -- 员工密码
V_p_czzd IN varchar2, -- 操作站点 V_khbh IN int, -- 客户编号 -1,忽略此条件,三者必输其一
V_jjzh IN varchar2, -- 空忽略此条件,三者必输其一
V_zjhm IN varchar2, -- 证件号码 空忽略此条件
V_fwmm IN varchar2, -- 服务密码 加密传输,加密方式用md5 RC1 IN OUT jz_oraoledb.m_refcur,
RC2 IN OUT jz_oraoledb.m_refcur)
AS
nb_row int;
nb_khbh int;
nb_jjzh VARCHAR2(12);
v_khzt number := -1;
BEGIN
IF (V_khbh is null or V_khbh = 0 or V_khbh = -1) Then
IF V_jjzh is not null THEN
select khbh into nb_khbh from t_jjzhxx where jjzh = V_jjzh;
IF SQL%rowcount = 0 THEN
OPEN RC1 FOR SELECT -26071 AS errorcode, '该客户不存在!' AS errormsg FROM DUAL;
RETURN;
END IF;
ELSE
IF V_zjhm is null THEN
OPEN RC1 FOR SELECT -26071 AS errorcode, '客户标识参数有误!' AS errormsg FROM DUAL;
RETURN;
END IF; select khbh into nb_khbh from t_kh where zjhm = V_zjhm;
IF SQL%rowcount = 0 THEN
OPEN RC1 FOR SELECT -26071 AS errorcode, '该客户不存在!' AS errormsg FROM DUAL;
RETURN;
END IF; END IF;
ELSE
BEGIN
nb_row := 0;
select 1 into nb_row from dual
where exists(select 1 from t_kh where (khbh = V_khbh));
exception when others then nb_row := 0;
END; IF nb_row = 0 THEN
OPEN RC1 FOR SELECT -26071 AS errorcode, '该客户不存在!' AS errormsg FROM DUAL;
RETURN;
END IF; nb_khbh := V_khbh;
END IF; BEGIN
nb_row := 0;
select 1 into nb_row from dual
where exists(select 1 from t_kh where khbh = nb_khbh and khmm = V_fwmm);
exception when others then nb_row := 0;
END; IF nb_row = 0 THEN
OPEN RC1 FOR SELECT -26072 AS errorcode, '客户密码不符!' AS errormsg FROM DUAL;
RETURN;
END IF; nb_jjzh := '';
select jjzh into nb_jjzh from t_jjzhxx where khbh = nb_khbh; select khzt into v_khzt from t_kh where khbh = nb_khbh;
if v_khzt <>1 then
BEGIN
OPEN RC1 FOR SELECT -26077 as errorcode, '客户状态异常!' as errormsg FROM DUAL;
RETURN;
END;
end if;
OPEN RC1 FOR SELECT 0 as errorcode, '校验客户服务密码成功!' as errormsg FROM DUAL;
OPEN RC2 FOR
select khbh,nb_jjzh as jjzh,khxm,pybm,'' as zjmc,zjhm,khlx,khzt,khrq,khxb,csrq,email from t_kh
where khbh = nb_khbh; EXCEPTION WHEN OTHERS THEN
OPEN RC1 FOR SELECT -10006 as errorcode, '校验客户服务密码异常!' as errormsg FROM DUAL;
END;