CREATE OR REPLACE PROCEDURE sp_spname(V_ID IN VARCHAR,V_NAME IN VARCHAR,V_TIME DATE) AS BEGIN SELECT ID,NAME,TIME INTO V_ID,V_NAME,V_TIME FROM TALBE1 WHERE ID=V_ID AND NAME=V_NAME; END;执行时,需要先声明三个变量,然后EXEC sp_spname(变量1,变量2,变量3)最好,使用TODE调试,最方便了. 你尝试一下,请多指教!
V_ID IN VARCHAR 我看有些地方好象是V_ID IN OUT VARCHAR
--创建一个临时表: create table table_tmpt( id number; v_name varchar2(256); v_time number )--创建存储过程 CREATE OR REPLACE PROCEDURE pro_test() AS v_id number; v_name varchar2(256); v_time number; CURSOR C_QUERY IS select ID,NAME,TIME from your_table; BEGIN open C_QUERY; loop fetch C_QUERY into v_id,v_name,v_time; exit when C_QUERY %NotFound;
insert into table_tmpt(id,name,time) values(v_id,v_name,v_time); end loop; close C_QUERY; END;--调用存储过程 call pro_test--得到数据 select * from table_tmpt;注:得到结果集后别忘了commit;
终于找到了正确解决方法! Nickle_Final,你的方法只能用在sql server上,oracle 不行-- /* * This sample shows how to call a PL/SQL function that opens * a cursor and get the cursor back as a Java ResultSet. */import java.sql.*; import java.io.*;// Importing the Oracle Jdbc driver package makes the code more readable import oracle.jdbc.driver.*;class RefCursorExample { public static void main (String args []) throws SQLException { // Load the driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); String url = "jdbc:oracle:oci8:@"; try { String url1 = System.getProperty("JDBC_URL"); if (url1 != null) url = url1; } catch (Exception e) { // If there is any security exception, ignore it // and use the default } // Connect to the database Connection conn = DriverManager.getConnection (url, "scott", "tiger"); // Create the stored procedure init (conn); // Prepare a PL/SQL call CallableStatement call = conn.prepareCall ("{ ? = call java_refcursor.job_listing (?)}"); // Find out all the SALES person call.registerOutParameter (1, OracleTypes.CURSOR); call.setString (2, "SALESMAN"); call.execute (); ResultSet rset = (ResultSet)call.getObject (1); // Dump the cursor while (rset.next ()) System.out.println (rset.getString ("ENAME")); // Close all the resources rset.close(); call.close(); conn.close(); } // Utility function to create the stored procedure static void init (Connection conn) throws SQLException { Statement stmt = conn.createStatement (); stmt.execute ("create or replace package java_refcursor as " + " type myrctype is ref cursor return EMP%ROWTYPE; " + " function job_listing (j varchar2) return myrctype; " + "end java_refcursor;"); stmt.execute ("create or replace package body java_refcursor as " + " function job_listing (j varchar2) return myrctype is " + " rc myrctype; " + " begin " + " open rc for select * from emp where job = j; " + " return rc; " + " end; " + "end java_refcursor;"); stmt.close(); } }
AS
BEGIN
SELECT ID,NAME,TIME INTO V_ID,V_NAME,V_TIME FROM TALBE1
WHERE ID=V_ID AND NAME=V_NAME;
END;执行时,需要先声明三个变量,然后EXEC sp_spname(变量1,变量2,变量3)最好,使用TODE调试,最方便了.
你尝试一下,请多指教!
我看有些地方好象是V_ID IN OUT VARCHAR
create table table_tmpt(
id number;
v_name varchar2(256);
v_time number
)--创建存储过程
CREATE OR REPLACE PROCEDURE pro_test()
AS
v_id number;
v_name varchar2(256);
v_time number;
CURSOR C_QUERY IS
select ID,NAME,TIME
from your_table; BEGIN
open C_QUERY;
loop
fetch C_QUERY into v_id,v_name,v_time;
exit when C_QUERY %NotFound;
insert into table_tmpt(id,name,time) values(v_id,v_name,v_time);
end loop;
close C_QUERY;
END;--调用存储过程
call pro_test--得到数据
select * from table_tmpt;注:得到结果集后别忘了commit;
谁写过这方面的程序?
谢谢各位,这个问题很急,谁解决了,再加50分!
我再加100分!!!!!
cStment.setInt(1,1);
cStment.setString(2,"test");
cStment.execute();然后在通过查询从临时表取数据
/*
* This sample shows how to call a PL/SQL function that opens
* a cursor and get the cursor back as a Java ResultSet.
*/import java.sql.*;
import java.io.*;// Importing the Oracle Jdbc driver package makes the code more readable
import oracle.jdbc.driver.*;class RefCursorExample
{
public static void main (String args [])
throws SQLException
{
// Load the driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); String url = "jdbc:oracle:oci8:@";
try {
String url1 = System.getProperty("JDBC_URL");
if (url1 != null)
url = url1;
} catch (Exception e) {
// If there is any security exception, ignore it
// and use the default
} // Connect to the database
Connection conn =
DriverManager.getConnection (url, "scott", "tiger"); // Create the stored procedure
init (conn); // Prepare a PL/SQL call
CallableStatement call =
conn.prepareCall ("{ ? = call java_refcursor.job_listing (?)}"); // Find out all the SALES person
call.registerOutParameter (1, OracleTypes.CURSOR);
call.setString (2, "SALESMAN");
call.execute ();
ResultSet rset = (ResultSet)call.getObject (1); // Dump the cursor
while (rset.next ())
System.out.println (rset.getString ("ENAME")); // Close all the resources
rset.close();
call.close();
conn.close(); } // Utility function to create the stored procedure
static void init (Connection conn)
throws SQLException
{
Statement stmt = conn.createStatement (); stmt.execute ("create or replace package java_refcursor as " +
" type myrctype is ref cursor return EMP%ROWTYPE; " +
" function job_listing (j varchar2) return myrctype; " +
"end java_refcursor;"); stmt.execute ("create or replace package body java_refcursor as " +
" function job_listing (j varchar2) return myrctype is " +
" rc myrctype; " +
" begin " +
" open rc for select * from emp where job = j; " +
" return rc; " +
" end; " +
"end java_refcursor;");
stmt.close();
}
}