无输入参数函数
create or replace function func_test1 return varchar isbegin return '无输入参数函数！';end func_test1;
有一个输入参数和一个输出参数函数
create or replace function func_test2( p_id in number, p_name out varchar)return varchar isbegin select name into p_name from users where id = p_id; return '有一个输入参数和一个输出参数！';end func_test2;
PL/SQL调用函数如下
-- Test statements heredeclare 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(); } }}
