oracle之FUNCTION拙见

一、介绍

函数（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;

如何执行上述代码建立函数？当做sql语句在sql window(pl sql)窗口里执行即可，或者其他客户端相应执行sql语句的窗口

3.2、调用方式

（1）、执行sql查询语句

select add_numbers(1,2) from dual;

（2）、执行pl sql代码块

以pl sql客户端为例，打开sql window，在sql栏里粘贴如下代码，在output一栏里即可看到结果

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程序中调用存储函数

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包，并能正常使用。

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_wsdl_url        := 'http://localhost:7878/hello?wsdl'; -- webservice服务的wsdl地址
l_namespace     &