Oracle函数

无输入参数函数

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();
		}
	}
}