JDBC调用PL/SQL函数
发布时间：2010/6/13 10:26:30 来源：城市学习网 编辑：ziteng
函数的一般的格式：
1. CREATE OR REPLACE FUNCTUION funcion_name [参数表列]
2. RETURN DATATYPE
3. IS|AS
4. PL/SQL BLOCK
举例应用：
Java 代码
1. CREATE OR REPLACE FUNCTION get_age(per_id in person_id)
2. return number
3. is
4. v_age person.id%type :=0;
5. begin
6. select age
7. into V_age
8. from person
9. where id=per_id;
10. return v_age;
11. end get_age;
12. /
在java代码里去调用函数：
CallableStatement cs;
try {
// 调用一个没有参数的函数; 函数返回 a int
// 预处理callable语句
1. cs = connection.prepareCall("{? = call get_age}");
2. // 注册返回值类型
3. cs.registerOutParameter(1, i);
4. // Execute and retrieve the returned value
5. cs.execute();
6. int retValue = cs.getInt(1);
cs = connection.prepareCall("{? = call get_age}"); [NextPage] // 注册返回值类型
cs.registerOutParameter(1, i);
// Execute and retrieve the returned value
cs.execute();
int retValue = cs.getInt(1);
// 调用有一个in参数的函数; the function returns a number
cs = connection.prepareCall("{? = call get_age(?)}"); // Register the type of the return value
cs.registerOutParameter(1, Types.number); // Set the value for the IN parameter
cs.setInt(2, 95001); // Execute and retrieve the returned value
cs.execute(); retValue = cs.getInt(1);
1. cs = connection.prepareCall("{? = call get_age(?)}");
2. // Register the type of the return value
3. cs.registerOutParameter(1, Types.number);
4. // Set the value for the IN parameter
5. cs.setInt(2, 95001);
6. // Execute and retrieve the returned value
7. cs.execute();
8. retValue = cs.getInt(1); [NextPage] // 调用有一个out参数的函数; the function returns a VARCHAR
cs = connection.prepareCall("{? = call get_age(?)}");
// Register the types of the return value and OUT parameter
cs.registerOutParameter(1, Types.VARCHAR);
cs.registerOutParameter(2, Types.VARCHAR);
// Execute and retrieve the returned values
cs.execute();
retValue = cs.getString(1); // return value
String outParam = cs.getString(2); // OUT parameter
// 调用有一个in/out参数的函数; the function returns a VARCHAR
cs = connection.prepareCall("{? = call get_age(?)}"); // Register the types of the return value and OUT parameter
cs.registerOutParameter(1, Types.NUMBER);
cs.registerOutParameter(2, Types.NUMBER);
1. cs = connection.prepareCall("{? = call get_age(?)}");
2. // Register the types of the return value and OUT parameter
3. cs.registerOutParameter(1, Types.NUMBER);
4. cs.registerOutParameter(2, Types.NUMBER);
// Set the value for the IN/OUT parameter
1. cs.setInt(2, 95002);
2.
3. // Execute and retrieve the returned values
4. cs.execute();
5. retValue = cs.getInt(1); // return value
6. outParam = cs.getInt(2); // IN/OUT parameter
7. } catch (SQLException e) {
8. }
