Mysql oracle 存储过程_mysql + oracle 存储过程 及相应数据库表的创建

package procedures;

import java.sql.*;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import oracle.jdbc.OracleCallableStatement;

import oracle.jdbc.OracleTypes;

import com.mysql.jdbc.*;

public class testProce {

public static void main(String[] args) {

getJDBC_Oracle();

}

public static void getJDBC_Oracle(){

/*

* Oracle 实例

* create or replace package gomepackage AS

TYPE sale_cur IS REF CURSOR;

end gomepackage;

create or replace procedure gome(rresult out gomepackage.sale_cur)

is

begin

open rresult for select * from person;

end gome;

create table person(fid int primary key,username varchar2(10),passwrod varchar2(20))

insert into person values (1,'pang','pang')

insert into person values(2,'li','li')

insert into person values (3,'zh','zh')

select * from person

*

*/

Connection conn = null;

ResultSet rs = null;

String url = "jdbc:oracle:thin:@localhost:1521:xe";

String call = "{call gome(?)}";

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

conn = DriverManager.getConnection(url,"pyl","pyl");

CallableStatement callst = conn.prepareCall(call);

callst.registerOutParameter(1, OracleTypes.CURSOR);

callst.execute();

//这两种方式 都可以

rs = (ResultSet) callst.getObject(1);

//rs = ((OracleCallableStatement)callst).getCursor(1);

while(rs.next()){

System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));

}

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

}

public static void getJDBC_MySql(){

/*Mysql实例

* delimiter //

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql" ,"root","123");

create table proce (id int,username varchar(20),password varchar(20))//

drop procedure if exists gome

create procedure gome(out rnum varchar(20))

begin

select username into rnum from proce where id =1;

end

*/

Connection conn = null;

Statement s =null;

ResultSet rs = null;

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql" ,"root","123");

String call="{call gome(?)}"; //调用语句

CallableStatement proc=conn.prepareCall(call); //调用存储过程

// proc.setString(1,"12345678"); //给输入参数传值

proc.registerOutParameter(1,Types.VARCHAR ); //声明输出参数是什么类型的

proc.execute(); //执行

String address=proc.getString(1); //获得输出参数

p(address);

/* s = conn.createStatement();

rs = s.executeQuery(sql); */

//CallableStatement cs = conn.prepareCall(sql, 1, 2);

/* conn.setAutoCommit(false);

CallableStatement proc = conn.prepareCall("{ ? = call snuffed_it_when(?) }");

proc.registerOutParameter(1, Types.INTEGER);

proc.setString(2, poetName);

int age = proc.getInt(2);*/

/* while(rs.next()){

p(rs.getInt("fuserid")+" ----- ");

p(rs.getString("fusername")+" ----- ");

p(rs.getString("fpasswrod")+" ----- ");

p(rs.getString("fbirday")+" ----- ");

p(rs.getString("fzhuzhi")+" ----- ");

p(rs.getString(""));

p(rs.getString(""));

p("\n");

}*/

} catch (SQLException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

e.printStackTrace();

}finally{

/* try {

rs.close();

s.close();

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}*/

}

}

public static void p(String s){

System.out.print(s);

}

public static void p(int s){

System.out.print(s);

}

}

分享到：

2011-08-20 08:46

浏览 946

评论