如何调用Oracle存储过程

Question

如何使用Clojure调用一个接受字符串和日期作为输入并返回refcursor作为输出的Oracle存储过程?

variable rc refcursor;
exec SOMEDB.PKG.GETPOSITIONS('ABC', to_date('2012-02-07','yyyy-mm-dd'), :rc)
print rc

- TJ_

---

你是否正在使用 java.jdbc：https://github.com/clojure/java.jdbc？ - Arthur Ulfeldt

Answer 1

这就是我的做法。我包含了一些帮助函数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

resultset-seq

注意: 如果你计划从一个fn返回结果集，你可能需要消费整个序列，因为连接会在fn返回后立即关闭。

- Kyle