如何调用以字符串和日期作为输入的Oracle存储过程,并使用Clojure将refcursor作为输出返回？
variable rc refcursor;
exec SOMEDB.PKG.GETPOSITIONS('ABC',to_date('2012-02-07','yyyy-mm-dd'),:rc)
print rc
解决方法
我就是这样做的.我已经包含了一些帮助fn用于将字符串转换为sql日期.
(ns foo
(:require [clojure.java.jdbc :as jdbc]
[clojure.string :as cs]))
(def conn
{:classname "oracle.jdbc.OracleDriver"
:subprotocol "oracle:thin"
:subname "@//host.name.here:port.here/db.here"
:user "user"
:password "pass"})
(defonce ORACLE_CURSOR oracle.jdbc.driver.OracleTypes/CURSOR)
(defn date-to-sql-date [date]
(java.sql.Date. (.getTimeInMillis
(java.util.GregorianCalendar.
(+ (.getYear date) 1900) (.getMonth date) (.getDate date)))))
(defn string-to-sql-date [date]
;; assumes American date formats
(if-not (empty? date)
(date-to-sql-date (java.util.Date. (cs/replace date #"-" "/")))))
(jdbc/with-connection conn
(with-open [stmt (.prepareCall (jdbc/connection) "{ call SOMEDB.PKG.GETPOSITIONS(?,?,?) }")]
(doto stmt
(.setString 1 "ABC")
(.setDate 2 (string-to-sql-date "2012-02-07"))
(.registerOutParameter 3 ORACLE_CURSOR)
(.execute))
(resultset-seq (. stmt getObject 3)))) ;; lazy-sequence
注意：如果您打算从fn返回它,则可能需要使用整个序列,因为一旦fn返回,连接将立即关闭.
