如何使用 JDBC 调用存储在数据库中的函数或存储过程

//存储过程create or replace Procedure countBySal(

p_sal emp.sal%type,

p_count OUT number

)as

begin

select count(*) into p_count from emp where sal >= p_sql

end countBySal//调用步奏import java.sql.CallableStatement//带哦用存储过程所必须的语句借口

import java.sql.Connection

import java.sql.DriverManager

import java.sql.Typespublic class EmpUtil {

public static int countBySal(double sal) throws Exception{

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

String url="jdbc:oracle:thin:@localhost:1521:test"

Connection cn=DriverManager.getConnection(url, "scott", "tiger")

String sql="{call countBySal(?,?)}"//调用存储过程的语句，call后面的就是存储过程名和需要传入的参数

CallableStatement cst=cn.prepareCall(sql)

cst.setDouble(1, sal)//设置in参数的值

cst.registerOutParameter(2, Types.INTEGER)//注册out参数的类型

cst.execute()

int result = cst.getInt(2)

cst.close()

cn.close()

return result

}

public static void main(String[] args) {

int count

try {

count = EmpUtil.countBySal(3000)

System.out.println("工资在3000元以上的人数为："+count)

} catch (Exception e) {

e.printStackTrace()

}

}

//存储过程create

or

replace

Procedure

countBySal(

p_sal

emp.sal%type,

p_count

OUT

number

)as

begin

select

count(*)

into

p_count

from

emp

where

sal

>=

p_sql

end

countBySal

//调用步奏import

java.sql.CallableStatement

//带哦用存储过程所必须的语句借口

import

java.sql.Connection

import

java.sql.DriverManager

import

java.sql.Typespublic

class

EmpUtil

{

public

static

int

countBySal(double

sal)

throws

Exception{

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

String

url="jdbc:oracle:thin:@localhost:1521:test"

Connection

cn=DriverManager.getConnection(url,

"scott",

"tiger")

String

sql="{call

countBySal(?,?)}"//调用存储过程的语句，call后面的就是存储过程名和需要传入的参数

CallableStatement

cst=cn.prepareCall(sql)

cst.setDouble(1,

sal)//设置in参数的值

cst.registerOutParameter(2,

Types.INTEGER)//注册out参数的类型

cst.execute()

int

result

=

cst.getInt(2)

cst.close()

cn.close()

return

result

}

public

static

void

main(String[]

args)

{

int

count

try

{

count

=

EmpUtil.countBySal(3000)

System.out.println("工资在3000元以上的人数为："+count)

}

catch

(Exception

e)

{

e.printStackTrace()

}

}

---