一、介绍
函数(Function)为一命名的存储程序,可带参数(有无均可),有返回值
函数和过程的结构类似,但必须有一个RETURN子句,用于返回函数值。
函数说明要指定函数名、返回值的类型,以及参数类型等,如CREATE OR REPLACE FUNCTION access_hel_by_dbws(username in varchar2) RETURN VARCHAR2
二、语法
CREATE [OR REPLACE] FUNCTION 函数名(参数列表) -- 参数类型与函数返回值类型不用标注类型大小,即varchar2即可
RETURN 函数值类型
AS
PLSQL子程序体;
三、简单例子:计算两个数字的和
3.1、函数定义
CREATE
OR
REPLACE
FUNCTION
add_numbers(d1
in
number, d2
in
number)
-- 函数声明,包括名称、参数名称及类型
return
number
-- 返回值类型声明
as
-- 或者写作is,均可
begin
-- begin与end中间为pl sql 代码块,可执行查询、插入、更新、删除操作
return
d1+d2;
-- return子句返回两数字之和
end
;
3.2、调用方式
(1)、执行sql查询语句
1
|
select
add_numbers(1,2)
from
dual;
|
以pl sql客户端为例,打开sql window,在sql栏里粘贴如下代码,在output一栏里即可看到结果
1
2
3
4
5
6
|
declare
-- declare部分可有可无,若无变量需要声明,则可去掉,只留begin、end
sum_ number;
begin
sum_:=add_numbers(1,2);
dbms_output.put_line(
'sum is:'
|| sum_);
end
;
|
(3)、在触发器,存储过程中均可调用函数,即在pl sql代码块范围内、sql语句中均可调用。
(4)、在java程序中调用存储函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
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方式连接oracle数据库
*/
Connection
connection
=
null
;
try {
Class.forName(
"oracle.jdbc.driver.OracleDriver"
);
String url =
"jdbc:oracle:thin:@localhost:1521:orcl"
;
String
user
=
"sys as sysdba"
;
String pwd =
"orcl"
;
connection
= DriverManager.getConnection(url,
user
,pwd);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
/** 调用oracle函数 */
CallableStatement callableStatement=
connection
.prepareCall(
"{?=call add_numbers(?, ?)}"
);
/** 设置第一个问号占位符的类型,第一个问号表示函数输出结果 ,顺序从1开始 */
callableStatement.registerOutParameter(1, oracle.jdbc.OracleTypes.
NUMERIC
);
/** 设置第二个参数与第三个参数的类型及值,即设置两个加数 */
callableStatement.setDouble(2, 3.6566);
callableStatement.setDouble(3, 3);
/** 执行存储函数 */
callableStatement.
execute
();
/** 得到函数执行结果 */
System.
out
.println(callableStatement.getString(1));
}
}
|
4、复杂例子:在存储函数中使用UTL_DBWS调用webservice
4、1前提是oracle数据库导入utl_dbws包,并能正常使用。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
CREATE
OR
REPLACE
FUNCTION
access_hello_by_dbws(username
in
varchar2, age
in
integer
)
RETURN
VARCHAR2
AS
l_service UTL_DBWS.service;
-- 定义service服务
l_call UTL_DBWS.call;
-- 定义调用对象
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;
-- 响应xml
response sys.XMLTYPE;
-- 请求xml
BEGIN
l_namespace :=
'http://test.xiangnan.it/'
;
-- targetNamespace属性的值,最后的/不能少,否则报...does not contains port...
l_service_qname := UTL_DBWS.to_qname(l_namespace,
'HelloWorldService'
);
-- service节点name属性值
l_port_qname := UTL_DBWS.to_qname(l_namespace,
'HelloWorldPort'
);
-- port节点name属性值
l_operation_qname := UTL_DBWS.to_qname(l_namespace,
'hello'
);
-- operation节点name属性值
l_service := UTL_DBWS.create_service (
-- 根据wsdl与service name创建service对象
wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),
service_name => l_service_qname);
l_call := UTL_DBWS.create_call (
-- 创建调用对象
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'
);
-- 设置调用对象的endpoint,也可不设置,
-- 该属性可通过soap UI查看
request := sys.XMLTYPE(
'<test:hello xmlns:test="http://test.xiangnan.it/"> -- 拼接request,可通过soap ui查看request进行拼接
<arg0>'
||username||
'</arg0> -- ||||之间为定义的变量
<arg1>'
||age||
'</arg1>
</test:hello>
'
);
response := utl_dbws.invoke(l_call, request);
-- 发出请求,并接受响应
UTL_DBWS.release_call (call_handle => l_call);
-- 释放call对象
UTL_DBWS.release_service (service_handle => l_service);
-- 释放service对象
-- 获取响应值,可通过soap ui查看response内容
return
response.extract(
'/ns2:helloResponse/return/text()'
,
' xmlns:ns2="http://test.xiangnan.it/"'
).getstringval();
EXCEPTION
WHEN
OTHERS
THEN
dbms_output.put_line(sqlerrm);
-- 输出错误
return
sqlerrm;
END
;
|
4.2、测试
在pl sql 中functions列表下面,选中定义的函数,右键点击,选择Test,如图:
输入对应的测试参数,得到结果,如图:
本文来自:http://www.2cto.com/database/201403/288437.html