下面将举一个通过数据库函数来调用存储过程:
创建数据库函数存储过程
CREATE
OR
REPLACE
FUNCTION
stu_proc
(
v_id IN NUMBER
)
RETURN VARCHAR2 IS
v_name VARCHAR2 ( 20 );
BEGIN
SELECT o.sname INTO v_name FROM student o WHERE o.id = v_id;
RETURN v_name;
EXCEPTION
WHEN OTHERS THEN RETURN ' 数据未找到 ' ;
END ;
(
v_id IN NUMBER
)
RETURN VARCHAR2 IS
v_name VARCHAR2 ( 20 );
BEGIN
SELECT o.sname INTO v_name FROM student o WHERE o.id = v_id;
RETURN v_name;
EXCEPTION
WHEN OTHERS THEN RETURN ' 数据未找到 ' ;
END ;
JAVA通过调用数据库函数调用存储过程
package
com.ljq.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
public class ProceTest {
public static void main(String[] args) throws Exception {
Connection conn = null ;
CallableStatement statement = null ;
// ?表示函数return的值, stu_proc是数据库函数名
// 存储过程的out和in都是以参数传进,这就是函数和存储过程的区别之一
String sql = " {?=call stu_proc(?)} " ;
try {
conn = ConnUtils.getConnection();
statement = conn.prepareCall(sql);
statement.registerOutParameter( 1 , Types.VARCHAR);
statement.setInt( 2 , 36 );
statement.execute();
// 具体值或数据未找到
String msg = statement.getString( 1 );
System.out.println(msg);
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnUtils.free( null , statement, conn);
}
}
}
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
public class ProceTest {
public static void main(String[] args) throws Exception {
Connection conn = null ;
CallableStatement statement = null ;
// ?表示函数return的值, stu_proc是数据库函数名
// 存储过程的out和in都是以参数传进,这就是函数和存储过程的区别之一
String sql = " {?=call stu_proc(?)} " ;
try {
conn = ConnUtils.getConnection();
statement = conn.prepareCall(sql);
statement.registerOutParameter( 1 , Types.VARCHAR);
statement.setInt( 2 , 36 );
statement.execute();
// 具体值或数据未找到
String msg = statement.getString( 1 );
System.out.println(msg);
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnUtils.free( null , statement, conn);
}
}
}