函数的一般的格式:
举例应用:
在java代码里去调用函数:
CallableStatement cs;
try {
// 调用一个没有参数的函数; 函数返回 a int
// 预处理callable语句
cs = connection.prepareCall(
"{? = call get_age}");
// 注册返回值类型
cs.registerOutParameter(1, i);
// Execute and retrieve the returned value
cs.execute();
int retValue = cs.getInt(1);
// 调用有一个in参数的函数; the function returns a number
// 调用有一个in/out参数的函数; the function returns a VARCHAR
// Set the value for the IN/OUT parameter
转自:http://jdnis.javaeye.com/blog/219990
1.
CREATE
OR
REPLACE FUNCTUION funcion_name [参数表列]
2. RETURN DATATYPE
3. IS| AS
4. PL/SQL BLOCK
2. RETURN DATATYPE
3. IS| AS
4. PL/SQL BLOCK
举例应用:
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. /
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. /
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);
2. // 注册返回值类型
3. cs.registerOutParameter(1, i);
4. // Execute and retrieve the returned value
5. cs.execute();
6. int retValue = cs.getInt(1);
// 注册返回值类型
cs.registerOutParameter(1, i);
// Execute and retrieve the returned value
cs.execute();
int retValue = cs.getInt(1);
// 调用有一个in参数的函数; the function returns a number
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);
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);
// 调用有一个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
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
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);
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. }
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. }