无输入参数函数
有一个输入参数和一个输出参数函数
PL/SQL调用函数如下
JAVA调用函数如下
create or replace function func_test1 return varchar is
begin
return '无输入参数函数!';
end func_test1;
有一个输入参数和一个输出参数函数
create or replace function func_test2(
p_id in number,
p_name out varchar)
return varchar is
begin
select name into p_name from users where id = p_id;
return '有一个输入参数和一个输出参数!';
end func_test2;
PL/SQL调用函数如下
-- Test statements here
declare
v_name varchar2(20);
begin
DBMS_OUTPUT.PUT_LINE(func_test1);
DBMS_OUTPUT.PUT_LINE(func_test2(2,v_name));
DBMS_OUTPUT.PUT_LINE(v_name);
end;
JAVA调用函数如下
package tf;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class TestProc {
private static String sDriver = "oracle.jdbc.driver.OracleDriver";
private static String sUrl = "jdbc:oracle:thin:@tf:1521:orcl";
private static Connection conn;
private static CallableStatement cs;
public static void main(String[] args) {
try {
Class.forName(sDriver);
conn = DriverManager.getConnection(sUrl, "test", "123");
System.out.println("java调用无输入参数函数");
cs = conn.prepareCall("{ ? = call func_test1 }");
cs.registerOutParameter(1, Types.VARCHAR);
cs.execute();
System.out.println("返回值为:" + cs.getString(1));
System.out.println("\njava调用有一个输入参数和一个输出参数函数");
cs = conn.prepareCall("{ ? = call func_test2(?,?) }");
cs.registerOutParameter(1, Types.VARCHAR);
cs.setInt(2, 1);
cs.registerOutParameter(3, Types.VARCHAR);
cs.execute();
System.out.println("返回值为:" + cs.getString(1));
System.out.println("输出参数值为:" + cs.getString(3));
} catch (Exception e) {
e.printStackTrace();
}
}
}