package com.hh.jdbc.procedure;import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;/** * jdbc操作SQLServer的存储过程 * 创建存储过程 * @author hy * */ public class CreateStoredProceduresOfSQLServer { private static Connection conn = null; private static Statement st = null; public static ResultSet rs = null;
public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=students","sa",""); st = conn.createStatement();
//创建存储过程show_students String createProcedure1 = "create procedure show_students "+"as " +"select id,name,age from students order by id"; st.executeUpdate(getSql("show_students")); st.executeUpdate(createProcedure1);
//创建存储过程onestudent String createProcedure2 = "create procedure onestudent " +"@stu_id int null, @name varchar(20) output," +"@age int output as if @stu_id = null" +"BEGIN " +" PRINT 'ERROR: You must specify a stu_id value.'" +" RETURN " +"END" +"select @name = name,@age = age from coffees where id = @stu_id RETIRN"; st.executeUpdate(getSql("onestudent")); st.executeUpdate(createProcedure2);
//创建函数 String createProcedure3 = "create function pubuse.ageofstu " +"(@stu_name varchar(20)) RETURN int AS BEGIN " +" DECLARE @age int " +" select @age = age from student where stu_name like @stu_name" +" RETURN @age " +" END"; st.executeUpdate("if exists(select name from sysobjects where name ='ageofstu'" + "drop function pubuse.ageofstu"); st.executeUpdate(createProcedure3);
st.close(); conn.close();
}
public static String getSql(String procedureName){ String sql = null; sql = "if exists(select name from sysobjects where name ='"+procedureName+"'" + "and type='p') drop procedure "+procedureName; return sql; }
//调用第一个存储过程 CallableStatement cs = conn.prepareCall(callSQL1); ResultSet rs = cs.executeQuery(); System.out.println("第一个存储过程调用结果:"); while(rs.next()){ String id = rs.getString(1); String name = rs.getString(2); String age = rs.getString(3); System.out.println(id+" "+name+" "+age); }
//调用第二个存储过程 cs = conn.prepareCall(callSQL2); cs.setString(1,"2"); cs.registerOutParameter(2, Types.CHAR); cs.registerOutParameter(3, Types.INTEGER); cs.execute(); String name = cs.getString(2); int age = cs.getInt(3); System.out.println("第二个存储过程调用结果:"); System.out.println("Student's name is "+name +"and age is "+age);
//调用函数 cs = conn.prepareCall(callSQL3); cs.setString(2, "小罗"); cs.registerOutParameter(1, Types.INTEGER); cs.execute(); age = cs.getInt(1); System.out.println("函数的调用结果是:"); System.out.println("This is student age is "+age+"."); cs.close(); conn.close(); } }
是用JDBC来创建存储过程了。还是用java写个存储过程让Oracle来调用了! 第一个问题:已经解决了· 我来给第二个的代码:JIANGWH@TESTDB>create or replace and compile java source named "hello_sp" as 2 package org.bromon.oracle; 3 public class Hello{ 4 public static String say(String name){ 5 return "您好,"+name; 6 } 7 }; 8 /Java 已创建。JIANGWH@TESTDB>create or replace function hello_sp(name varchar2) return varchar2 2 as language java name 3 'org.bromon.oracle.Hello.say(java.lang.String) return java.lang.String'; 4 /函数已创建。 JIANGWH@TESTDB>select hello_sp('jiangwh') from dual;HELLO_SP('JIANGWH') ---------------------------------------------------------------------------------------------------- 您好,jiangwhJIANGWH@TESTDB>spool off;
大家很热心,哈哈,互相学习!我也来说两句 JDBC基本步骤: 1、load the driver(驱动数据库) 2、connect to the database(连接数据库) 3、execute the sql(sql语句) 4、retreve the result data \show the result(从数据库取出结果) 5、close (关掉打开的资源)注意:最好Eception能捕捉的就捕捉,别偷懒写throws exception和记得关掉打开的资源。
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;/**
* jdbc操作SQLServer的存储过程
* 创建存储过程
* @author hy
*
*/
public class CreateStoredProceduresOfSQLServer {
private static Connection conn = null;
private static Statement st = null;
public static ResultSet rs = null;
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=students","sa","");
st = conn.createStatement();
//创建存储过程show_students
String createProcedure1 = "create procedure show_students "+"as "
+"select id,name,age from students order by id";
st.executeUpdate(getSql("show_students"));
st.executeUpdate(createProcedure1);
//创建存储过程onestudent
String createProcedure2 = "create procedure onestudent "
+"@stu_id int null, @name varchar(20) output,"
+"@age int output as if @stu_id = null"
+"BEGIN "
+" PRINT 'ERROR: You must specify a stu_id value.'"
+" RETURN "
+"END"
+"select @name = name,@age = age from coffees where id = @stu_id RETIRN";
st.executeUpdate(getSql("onestudent"));
st.executeUpdate(createProcedure2);
//创建函数
String createProcedure3 = "create function pubuse.ageofstu "
+"(@stu_name varchar(20)) RETURN int AS BEGIN "
+" DECLARE @age int "
+" select @age = age from student where stu_name like @stu_name"
+" RETURN @age "
+" END";
st.executeUpdate("if exists(select name from sysobjects where name ='ageofstu'" +
"drop function pubuse.ageofstu");
st.executeUpdate(createProcedure3);
st.close();
conn.close();
}
public static String getSql(String procedureName){
String sql = null;
sql = "if exists(select name from sysobjects where name ='"+procedureName+"'" +
"and type='p') drop procedure "+procedureName;
return sql;
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;/**
* 调用存储过程
* @author hy
*
*/
public class InvokeStoreProceduresOfSQLServer {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection conn = null;
String callSQL1 ="{call show_students}";
String callSQL2 = "{call onestudent(?,?,?)}";
String callSQL3 = "{? = call ageofstu(?)}";
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=students","sa","");
//调用第一个存储过程
CallableStatement cs = conn.prepareCall(callSQL1);
ResultSet rs = cs.executeQuery();
System.out.println("第一个存储过程调用结果:");
while(rs.next()){
String id = rs.getString(1);
String name = rs.getString(2);
String age = rs.getString(3);
System.out.println(id+" "+name+" "+age);
}
//调用第二个存储过程
cs = conn.prepareCall(callSQL2);
cs.setString(1,"2");
cs.registerOutParameter(2, Types.CHAR);
cs.registerOutParameter(3, Types.INTEGER);
cs.execute();
String name = cs.getString(2);
int age = cs.getInt(3);
System.out.println("第二个存储过程调用结果:");
System.out.println("Student's name is "+name +"and age is "+age);
//调用函数
cs = conn.prepareCall(callSQL3);
cs.setString(2, "小罗");
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
age = cs.getInt(1);
System.out.println("函数的调用结果是:");
System.out.println("This is student age is "+age+".");
cs.close();
conn.close();
}
}
第一个问题:已经解决了·
我来给第二个的代码:JIANGWH@TESTDB>create or replace and compile java source named "hello_sp" as
2 package org.bromon.oracle;
3 public class Hello{
4 public static String say(String name){
5 return "您好,"+name;
6 }
7 };
8 /Java 已创建。JIANGWH@TESTDB>create or replace function hello_sp(name varchar2) return varchar2
2 as language java name
3 'org.bromon.oracle.Hello.say(java.lang.String) return java.lang.String';
4 /函数已创建。
JIANGWH@TESTDB>select hello_sp('jiangwh') from dual;HELLO_SP('JIANGWH')
----------------------------------------------------------------------------------------------------
您好,jiangwhJIANGWH@TESTDB>spool off;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;public class JdbcTest2 { /**
* @param args
*/
public static void main(String[] args) {
// TODO 自动生成方法存根 try { Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mytest", "root", "root"); // Class.forName("oracle.jdbc.driver.OracleDriver");
// Connection conn =
// DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:nbtc2","java91","java91"); String sql = "{ call loopproc(?,?) }"; CallableStatement cs = conn.prepareCall(sql); cs.registerOutParameter(1, java.sql.Types.VARCHAR);// 注册输出参数
cs.setInt(2, 3);// 指出输入参数
cs.execute();
System.out.println("return:" + cs.getString(1)); cs.close();
conn.close(); } catch (Exception e) {
// TODO 自动生成 catch 块
e.printStackTrace();
} }}
String callSQL1 ="{call show_students}";
好像是
String callSQL1 ="[call show_students]";
JDBC基本步骤:
1、load the driver(驱动数据库)
2、connect to the database(连接数据库)
3、execute the sql(sql语句)
4、retreve the result data \show the result(从数据库取出结果)
5、close (关掉打开的资源)注意:最好Eception能捕捉的就捕捉,别偷懒写throws exception和记得关掉打开的资源。