Call Oracle Functions

Differences between stored procedures and functions in Oracle

1. You can use the out parameter to return multiple values. Generally, multiple return values are used. If only one return value exists, use the function.

2. The process call itself is a pl/SQL statement, and the function can only be called as part of the expression to obtain the object_name OF THE user_objects table. Because only one field is returned, the Oracle function is used, the first method returns the scalar data type, and the second method returns the cursor type. A scalar contains only one value, and a cursor can represent a query result set.

1. Return scalar functions

1. define functions in Oracle

Create function getObjectsName (obj_type varchar2)

Return varchar2

Is

O_name user_objects.OBJECT_NAME % type;

O_names long;

Cursor objs_cursor (o_type varchar2)

Is

Select object_name from user_objects where object_type = o_type and rownum <11;

Begin

Open objs_cursor (obj_type); -- open the cursor

Loop

Fetch objs_cursor into o_name;

O_names: = o_names | '_' | o_name; -- use a variable to merge all values

Exit when objs_cursor % NOTFOUND;

End loop;

Close objs_cursor;

Return o_names;

End;

2. Call

String function = "{? = Call getObjectsName (?)} "; // It Is a part more than calling the stored procedure:"? ="

Cstmt = conn. prepareCall (function );

// Register the return type (or out type) parameter

Cstmt. registerOutParameter (1, Oracle. jdbc. OracleTypes. VARCHAR );

// Register input (in type) Parameters

Cstmt. setString (2, "TABLE ");

Cstmt.exe cute ();

// Obtain the value from the output parameter

String values = cstmt. getString (1 );

System. out. println (values );

Ii. Returned cursor type

First, define the type of the return class so that the function can return. Some types can be directly defined by create, such as nested tables and varray. However, some types cannot be created, such as index tables and cursors, if you want to return a type that cannot be created, you can first create a package and then create a type that you want to return, such:

1. Create a package specification

Create or replace package yulee is

Type objNames_cur is ref cursor; -- create the type to be returned so that the function can return

Function getObjNames (objType varchar2) return objNames_cur;

End yulee;

2. Create a package

Create or replace package body yulee is

Function getObjNames (objType varchar2) return objNames_cur is

ObjNames objNames_cur;

Begin

OPEN objNames

Select object_name from user_objects

Where object_type = objType and rownum <11;

Return objNames;

Close objNames; -- it cannot be closed before return. Otherwise, java will get an invalid cursor.

End getObjNames;

End yulee;

3. java program call

String procedure = "{? = Call yulee. getObjNames (?)} ";

Cstmt = conn. prepareCall (procedure );

// Register the response parameters

Cstmt. registerOutParameter (1, Oracle. jdbc. OracleTypes. CURSOR );

// Input parameters

Cstmt. setString (2, "TABLE ");

Cstmt.exe cute ();

// Print the return value

Rs = (ResultSet) cstmt. getObject (1 );

While (rs. next ()){

System. out. println (rs. getString ("object_name "));

}