程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> oracle之FUNCTION拙見

oracle之FUNCTION拙見

編輯:Oracle教程

一、介紹

函數(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       := '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,如圖:

\

輸入對應的測試參數,得到結果,如圖:

\

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved