For more information about oracle FUNCTION compute, see

I. Introduction

A Function is a named storage program that can contain parameters (with or without parameters) and return values.

The structure of a function is similar to that of a process, but a RETURN clause must be provided to RETURN the function value.

FUNCTION Description specifies the FUNCTION name, RETURN value type, and parameter type, such as create or replace function access_hel_by_dbws (username in varchar2) RETURN VARCHAR2

Ii. Syntax

CREATE [or replace] FUNCTION name (parameter list) -- The parameter type and FUNCTION return value type do not need to be marked with the type size, that is, varchar2.
RETURN function value type
AS
PLSQL subprogram body;

3. Simple Example: Calculate the sum of two numbers

3.1 Function Definition

Create or replace function add_numbers (d1 in number, d2 in number) -- FUNCTION declaration, including name, parameter name, and type return number -- return Value Type Declaration as -- OR writing is, you can use the pl SQL code block in the middle of -- in -- begin and end. You can perform the query, insert, update, and delete operations. return d1 + d2; -- return clause returns the sum of the two numbers. end;

How to execute the above code to create a function? Execute SQL statements in the SQL window (pl SQL) window, or use other clients to execute SQL statements.

3.2. Call Method

(1) Execute SQL query statements

select add_numbers(1,2) from dual;

(2) execute pl SQL code block

Take the pl SQL client as an example. Open the SQL window and paste the following code in the SQL column. The result is displayed in the output column.

Declare -- declare part is optional. If no variable needs to be declared, it can be removed, leaving only begin and end sum _ number; begin sum _: = add_numbers ); dbms_output.put_line ('sum is: '| sum _); end;

As follows:

(3) functions can be called in triggers and stored procedures, that is, within the range of pl SQL code blocks and in SQL statements.

(4) Calling storage functions in java programs

Package zxn. function. test; import java. SQL. callableStatement; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. SQLException; public class Test {public static void main (String [] args) throws SQLException {/*** jdbc Connection to the oracle database */connection Connection = null; try {Class. forName ("oracle. jdbc. driver. oracleDriver "); String url =" jdbc: oracle: thin: @ localhost: 1521: orcl "; String use R = "sys as sysdba"; String pwd = "orcl"; connection = DriverManager. getConnection (url, user, pwd);} catch (ClassNotFoundException e) {e. printStackTrace ();} catch (SQLException e) {e. printStackTrace ();}/** call oracle function */CallableStatement callableStatement = connection. prepareCall ("{? = Call add_numbers (?, ?)} ");/** Set the type of the first question mark placeholder. The first question mark indicates the function output result, starting from 1 */callableStatement. registerOutParameter (1, oracle. jdbc. oracleTypes. NUMERIC);/** set the type and value of the second and third parameters, that is, set two Addons */callableStatement. setDouble (2, 3.6566); callableStatement. setDouble (3, 3);/** run the storage function */callableStatement.exe cute ();/** get the function execution result */System. out. println (callableStatement. getString (1 ));}}

4. complex example: Use UTL_DBWS in storage functions to call webservice

4. 1. The premise is that the oracle database is imported into the utl_dbws package and can be used normally.

Create or replace function access_hello_by_dbws (username in varchar2, age in integer) RETURN VARCHAR2ASl_service UTL_DBWS.service; -- Define service l_call UTL_DBWS.call; -- Define call object l_wsdl_url VARCHAR2 (32767 ); l_namespace VARCHAR2 (32767); l_service_qname UTL_DBWS.qname; l_port_qname UTL_DBWS.qname; l_operation_qname UTL_DBWS.qname; request sys. XMLTYPE; -- Response xmlresponse sys. XMLTYPE; -- Request xmlBEGINl_wsdl_url: =' http://localhost:7878/hello ? Wsdl '; -- wsdl address of the webservice l_namespace: =' http://test.xiangnan.it/ '; -- Specifies the value of the targetNamespace attribute. The final/value must not be small. Otherwise, a message is returned... does not contains port... rochelle service_qname: = invoke (l_namespace, 'helloworldservice'); -- service node name attribute value Rochelle port_qname: = UTL_DBWS.to_qname (l_namespace, 'helloworldport'); -- port node name attribute value Rochelle oper: = UTL_DBWS.to_qname (l_namespace, 'Hello'); -- operation node name attribute value l_service: = UTL_DBWS.create_service (-- create a service object wsdl_document_location => urifacloud Based on the wsdl and service name. getURI (l_wsdl_url), service_name => l_service_qname); l_call: = invoke (-- create the call object service_handle => l_service, port_name => l_port_qname, operation_name => l_operation_qname); sys. utl_dbws.set_target_endpoint_address (l_call ,' http://localhost:7878/hello '); -- Set the endpoint of the call object. -- You can view the request: = sys. XMLTYPE (' <test: hello xmlns: test = "in the soap UI" http://test.xiangnan.it/ "> -- Splice the request, you can use the soap ui to view the request for splicing <arg0> '| username |' </arg0> -- | defined variable <arg1> '| age | | '</arg1> </test: hello> '); response: = utl_dbws.invoke (l_call, request); -- sends a request and accepts the response UTL_DBWS.release_call (call_handle => l_call ); -- release the call object UTL_DBWS.release_service (service_handle => l_service); -- release the service object -- Obtain the response value. You can view the response content return response through the soap ui. extract ('/int32: helloResponse/return/text ()', 'xmlns: int32 =" http://test.xiangnan.it/ "'). Getstringval (); exception when others then dbms_output.put_line (sqlerrm); -- output error return sqlerrm; END;

4.2 Test

Under the functions List in pl SQL, right-click the defined function and choose Test,

Enter the corresponding test parameters,